MySQL基础学习笔记（持续更新中）

一、MySQL基础

1. 数据库概念

1.1 为什么要学MySQL

个人理解&＃xff1a;随着互联网的发展&＃xff0c;数据变得烦杂&＃xff0c;冗余&＃xff0c;量大&＃xff0c;为了保证数据的持久性以及健壮性等等&＃xff0c;同时也为了方便人们很好的处理数据&＃xff0c;这就发明了数据库DB。

• 实现数据的持久性
• 拥有专门管理的数据软件&＃xff1a;数据库管理系统DBMS

1.2 数据库相关概念&＃xff1a;

DB∶数据库&＃xff0c;保存一组有组织的数据的容器。

DBMS∶数据库管理系统&＃xff0c;又称为数据库软件(产品&＃xff09;&＃xff0c;用于管理DB中的数据。

SQL&＃xff1a;结构化查询语言&＃xff1a;专门用来与数据库管理系统&＃xff08;DBMS&＃xff09;通信的语言。

• 不是某个特定数据库供应商专有的语言&＃xff0c;几乎所有DBMS都支持SQL
• 简单易学
• 一种强有力的语言&＃xff0c;灵活使用其语言元素&＃xff0c;可以进行非常复杂和高级的数据库操作。

2. DBMS的分类&＃xff1a;

• 基于共享文件系统的DBMS&＃xff08;Access&＃xff09;
• 基于客户机——服务器&＃xff08;C/S 架构&＃xff09;的DBMS&＃xff08;MySQL、Oracle、SQLServer&＃xff09;服务端比较重要。

2.1 RDBMS 与非 RDBMS

2.1.1 关系型数据库

实质&＃xff1a;

• 二维表的格式、二元关系。
• 数据库由多个表组成
• 可以支持单表、多表复杂查询&＃xff0c;同时有事务支持&＃xff08;安全性很高的数据访问要求得以实现&＃xff09;

2.1.2 非关系型数据库

• 可以看成传统关系型数据库的阉割版本舍得&＃xff0c;舍弃了一些功能&＃xff0c;获得某些高性能的功能。
• 键值型数据库&＃xff1a;内存中。Redis
• 文档型数据库&＃xff1a;MongoDB
• 搜索引擎数据库&＃xff1a;Solr
• 列式数据库&＃xff1a;降低系统的I/O&＃xff0c;适用于分布式文件系统。
• 图形数据库。

3. 关系型数据库的设计规则

• 典型数据结构就是数据表&＃xff0c;这些数据表的组成都是结构化的。
• 将数据库放到表中&＃xff0c;表再放到库中。
• 一个数据库中可以有多个表&＃xff0c;每个表都有一个的名字&＃xff0c;用来标识这个表。表名具有唯一性。
• 表具有一些特性&＃xff0c;这些特性定义了数据在表中如何存储&＃xff0c;类似java中“类”的设计。
• 表由列组成&＃xff0c;我们也称为字段。所有表都是由一个列或多个列组成的&＃xff0c;每一列类似java中的“属性”。
• 表中的数据是按行存储的&＃xff0c;每一行类似java中的“对象”

3.1 表、记录、字段

• E-R模型&＃xff1a;实体集、属性、联系集
• 实体集对应一个表&＃xff0c;实体对应表中的一行、也称为一条记录。一个属性对应于数据库中的一列&＃xff0c;也称为一个字段。

3.2 表的关联关系

• 表与表之间的数据记录有关系。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。
• 四种关系&＃xff1a;一对一关联、一对多关联、多对多关联、自我引用。

3.2.1一对一关系&＃xff08;one-to-one&＃xff09;

• 实际运用不多&＃xff0c;因为一对一的关系可以创建成一张表。
• 一张表拆分为两张表&＃xff1a;两个表的记录是一一对应的关系。
  • 但是表的字段太多时&＃xff0c;我们可以将表拆分为常用和不常用的两张表。
• 两种建标原则&＃xff1a;
  • 外键唯一&＃xff1a;主表的主键和从表的外键唯一。形成主外关系&＃xff0c;外键唯一。
  • 外键是主键&＃xff1a;主表的主键和从表的主键&＃xff0c;形成主外键关系。

3.2.2 一对多关系(one-to-many)

• 常见实例场景&＃xff1a;客户表和订单表&＃xff0c;分类表和商品表&＃xff0c;部门表和员工表
  • 也就是说一个表里面的一条记录可以对应到另一个表中的多条记录。
• 一对多建表原则&＃xff1a;在从表&＃xff08;多方&＃xff09;创建一个字段&＃xff0c;字段作为外键指向主表&＃xff08;一方&＃xff09;的主键

3.2.3 多对多关系&＃xff08;many-to-many&＃xff09;

• 要表示多对多关系&＃xff0c;必须创建第三个表&＃xff0c;该表通常称为联接表&＃xff0c;它将多对多关系划分为两个一对多关系。将这两个表的主键都插入到第三个表中。
  

• 多对多关系建表原则:需要创建第三张表&＃xff0c;中间表中至少两个字段&＃xff0c;这两个字段分别作为外键指向各自一方的主键。

3.2.4 自我引用&＃xff08;Self reference&＃xff09;

• 一个表里面有对应关系

二、MySQL环境搭建

位置信息&＃xff08;删除时都要删&＃xff09;&＃xff1a;

• 软件安装的位置
• 数据库数据的位置
• 服务的位置&＃xff08;确保每次服务都有开启&＃xff0c;这样才能使用MySQL&＃xff09;
• 环境变量的位置
• 注册表

1. MySQL的卸载

卸载方式&＃xff1a;

• 控制面板下卸载程序

• 通过360、电脑管家卸载

• 通过MySQL安装包本身卸载

• 最后要重启电脑

2. MySQL的下载、安装、配置

2.1 下载安装&＃xff1a;

下载通过msi文件安装。

要在管理员模式下打开cmd&＃xff1a;

• net start mysql80 开启MySQL服务

• net stop mysql80 关闭MySQL服务

• 也可在服务中手动开启手动关闭

注意&＃xff1a;后面要加版本号80

安装好后登录uroot用户&＃xff1a;

• mysql -uroot -p
• 再输入密码进入mysql
• quit、exit指令为退出mysql
• 如果有多个版本的MySQL&＃xff0c;访问时得注意端口号
  • mysql -uroot -P 3306 -p
  • 3306为你安装MySQL时进程的端口号
• 访问本机或者其他地址的MySQL
  • mysql -uroot -P 3306 -h localhost (访问本地的MySQL)

2.2 配置

• 配置环境变量
  • 在path路径下配置
  • 路径为MySQL安装路径下bin文件的位置

3. MySQL的基础使用

3.1 基本使用指令

show databases; #展示现在所有的数据库
create databases dbtest1; #创建一个数据库
use dbtest1; #使用这个数据库进行操作。&＃xff08;以下操作在这个数据库中进行&＃xff09;* create table students(id int,name varchar[15]); #——创建一个表&＃xff08;关系模式&＃xff09;show tables; #在这个数据库下显示所有的表select * from students #在这个student表中筛选信息&＃xff0c;*代表全部的意思。&＃xff08;正则表达式&＃xff09;insert into students values(1001,&＃39;Tom&＃39;);insert into students values(1002,&＃39;Jeff&＃39;);#往表里插入信息&＃xff08;用这个语句插入中文字符串会报错&＃xff09;insert into students values(1002,&＃39;汤姆&＃39;); #报错&＃xff0c;但是在8.0版本中是可以的&＃xff01;因为以前版本的CHARSET &＃61; latin1 &＃xff08;latin中没有汉字中文编码&＃xff0c;mysql最初是在瑞典被创建的&＃xff0c;所以采用Latin码&＃xff09;show create table students; #战士创建表时的相关信息show create database dbtest1; #展示创建数据库时的相关信息
show variables like &＃39;character_%&＃39;; #展示此版本下所使用的字符集
show variables like &＃39;collation_%&＃39;; #展示字符集的比较规则
drop database dbtest1; #把数据库删除
alter database dbtest1 charset utf8; #修改修改数据库的字符编码为utf8


修改字符集通过MySQL数据目录下的my.ini文件配置修改&＃xff08;5.7以前&＃xff08;包括5.7&＃xff09;版本才需要&＃xff09;

3.2 MySQL图形化管理工具

• MySQL Workbench
• Navicat Premunim &＃xff08;已安装&＃xff09;
• SQLyog &＃xff08;已安装&＃xff09;
• dbeaver(利于做大数据的操作)

三、初识SQL语言

1. SQL的分类

SQL&＃xff1a;数据查询语言

/*
DDL&＃xff1a;数据定义语言。
CREATE\ALTER\DROP\RENAME\TRUNCATEDML数据操作语言。(使用频率最高)
INSERT\DELETE\UPDATE\SELECT
增 删 改 查DCL&＃xff1a;数据控制语言。
COMMIT\ROLLBACK\SAVEPOINT\GRANT\REVOKE
COMMIT\ROLLBACK(与事物相关的操作&＃xff0c;单独取出称为TCL&＃xff1a;事物控制语言)因为查询语句使用的非常频繁&＃xff0c;所以很多人把查询语句单拎出来一类&＃xff1a;DQL(数据查询语言)学习技巧&＃xff1a;大处着手、小处着手。
*/


2. SQL语言的规则与规范

2.1 基本规则

• 可以写在一行或者多行&＃xff0c;可读性&＃xff0c;各子句分行写&＃xff0c;必要时使用缩进。
• 每条命令以 ; 或 \g 或 \G 结束
• 关键字不能被缩写也不能分行
• 关于标点符号
  • 必须保证所有的()、单引号、双引号是成对结束的。
  • 必须使用英文状态下的半角输入方式。
  • 字符串型和日期时间类型的数据可以使用单引号 (’ &＃39;) 表示
  • 列的表名&＃xff0c;尽量使用双引号 (" ")&＃xff0c;而且不建议省略as

2.2 SQL大小写规范

• MySQL在windows环境下是大小写不敏感的

  • windows操作系统本身不区分大小写

• MySQL在Linux环境下是大小写敏感的

  • 数据库名、表名、表的别名、变量名是严格区分大小写的。
  • 关键字、函数名、列名&＃xff08;或字段名&＃xff09;、列的别名&＃xff08;字段的别名&＃xff09;是忽略大小写的。

• 推荐采用统一的书写规范

  • 数据库名、表名、表别名、字段名、字段别名等都小写
  • SQL关键字、函数名、绑定变量等都大写

2.3 注释

#单行注释 (MySQL注释独有的方式)
-- 单行注释&＃xff08;--后空格&＃xff09;
/*
多行注释
*/


2.4 命名规则

• pass to do

2.5 数据导入指令

• 在命令客户端登录mysql&＃xff0c;使用source指令导入

mysql> source d:\mysqldb.sql
#source &＃43; 数据库的绝对路径


• 直接基于图形化管理界面导入数据库&＃xff0c;Navicat直接拉文件进去。
  SQLyog中选择 “工具“ —— ”执行sql脚本“ —— 选中xxx.sql即可。

3. 基本的SELECT语句

SELECT 1; #没有任何子句**
SELECT 9/2; #没有任何子句**
SELECT 1&＃43;1 FROM DUAL; #dual是一个伪表**


3.1 SELECT … FROM …

这个就相当于投影π

SELECT 字段1,字段2,... FROM 表名; #字段就是属性名
SELECT * FROM 表名; # *代表所有的字段&＃xff0c;把所有的属性列显示出来。选择语句会返回给我们一个结果集。


3.2 列的别名

SELECT employee_id emp_id,last_name AS lname,department_id FROM employees;
#表中字段名原本为employee_id&＃xff0c;查询时加一个空格或者AS后面跟上另外一个名字&＃xff08;我们自己取的&＃xff09;&＃xff0c;查询返回结果集时&＃xff0c;字段名就是我们定义的名字。但这并不会本质上改变我们表的字段名。
#as:全称为alias&＃xff08;别名&＃xff09;&＃xff08;可以省略不写、默认存在&＃xff09;
#列的别名同样也可以用一对双引号引起来。避免别名有空格而产生歧义性。


3.3 去除重复行

去除冗余的信息&＃xff0c;获取不重复的数据

SELECT DISTINCT depatment_id FROM employees; #去除重复的department_id信息
SELECT DISTINCT depatment_id,salary FROM employees;
#给 department_id,salary 两个字段整体去重&＃xff01;
SELECT salary,DISTINCT depatment_id FROM employees
#错误写法&＃xff01;&＃xff01;先选了所有的salary再去重id&＃xff0c;得到的结果集合表的记录条数不一致&＃xff01;


3.4 空值参与运算

**空值&＃xff1a;null **

#空值null不等同于0、&＃39;&＃39;、&＃39;null&＃39;&＃xff0c;null是一个未知的。
SELECT employee_id, salary "月工资",salary * (1 &＃43; commission_pct) * 12 "年工资" FROM employees;SELECT employee_id, salary "月工资",salary * (1 &＃43; IFNULL(commission_pct,0)) * 12 "年工资" FROM employees;#表示的意思就是&＃xff0c;判断commission_pct是否为null&＃xff0c;若为null则用0代替。


空值参与运算&＃xff0c;结果一定为null
解决方法&＃xff1a;引入IFNULL(,)

3.5 着重号

&＃96; 这就是着重号&＃xff08;英文输入下1左边的按键&＃xff09;
也就是你所用的字段名、表名与关键字或者保留字冲突&＃xff08;重名&＃xff09;&＃xff0c;就必须要用&＃96;&＃96;引起来。

3.6 查询常数

SELECT &＃39;尚硅谷&＃39;,employee_id,last_name FROM employees;
#前面的常数&＃39;尚硅谷&＃39;将会作为一个单独的字段出现在所查询到的结果集里面的每一行。


4. 显示表结构

使用DESCRIBE或DESC命令&＃xff0c;表示表结构。

DESCRIBE employees;
or
DESC employees;
#显示了表中字段的详细信息。


5. 过滤数据

只筛选出满足某些条件的数据
相当于SIGMA选择&＃xff0c;遵循先选择后投影的原则

SELECT *
FROM employees
#过滤条件&＃xff0c;只选择出部门号为90的
WHERE department_id &＃61; 90;#WHERE要声明在FROM结构后面。SELECT *
FROM employees
#过滤条件&＃xff0c;只选择出名字为&＃39;King&＃39;的
WHERE last_name &＃61; &＃39;King&＃39;;#SQL 是在windows下是不区分大小写的。


四、运算符

1. 算数运算符

1.1 四则运算

**&＃43; - * /(div) **

SELECT 100, 100 &＃43; 0, 100 - 0, 100 &＃43; 50, 100 &＃43; 50 -30, 100 &＃43; 35.5, 100 - 35.5;
#结果就是一个表&＃xff0c;属性列为这些运算的值&＃xff0c;同时只有一个元组&＃xff0c;内容也为这些运算值。#在SQL中&＃xff0c;&＃43;没有连接作用&＃xff0c;就表示加法运算。此时会将字符串转换为数值&＃xff08;隐式转换&＃xff09;
SELECT 100 * &＃39;1&＃39; FROM DUAL;
#结果&＃xff1a;属性名为 100 &＃43; &＃39;1&＃39;,元组值为101。#此时将&＃39;a&＃39;看做0处理。
SELECT 100 * &＃39;a&＃39; FROM DUAL;#此时结果为null
SELECT 100 * null FROM DUAL;#100 div 0 结果为null


在SQL中&＃xff0c;除法默认的就是浮点数。比如100/2 结果就为 50.000000

1.2 取模运算

%(mod)

#取模运算的结果符号取决于被模数的符号。
SELECT 12 % 3, 12 % 5, 12 MOD -5, -12 % 5, -12 % -5
FROM employees;#练习&＃xff1a;查询员工id为偶数的员工信息
SELECT employee_id, last_name, salary
FROM employees
WHERE employee_id % 2 &＃61; 0;

2. 比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较&＃xff0c;比较的结果为真则返回1&＃xff0c;比较的结果为假则返回0&＃xff0c;其他情况则返回NULL。

比较运算符经常被用来作为SELECT查询语句的条件来使用&＃xff0c;返回符合条件的结果记录。

2.1 &＃61;

SELECT 1 &＃61; 2, 1 !&＃61; 2, 1 &＃61; &＃39;1&＃39;, 1 &＃61; &＃39;a&＃39;, 0 &＃61; &＃39;a&＃39; FROM employees;
#结果 0 1 1 0 1
#字符串存在隐式转换。如果转换数值不成功&＃xff0c;则看做0。 SELECT &＃39;a&＃39; &＃61; &＃39;a&＃39;, &＃39;ab&＃39; &＃61; &＃39;ab&＃39;, &＃39;a&＃39; &＃61; &＃39;b&＃39; FROM employees;
#结果 1 1 0
#两边都是字符串的话&＃xff0c;则按照ANSI的比较规则进行比较。SELECT 1 &＃61; NULL, NULL &＃61; NULL FROM employees;
#结果 NULL NULL
#只要有NULL参与判断&＃xff0c;结果一定为NULL。SELECT last_name, salary, commission_pct
FROM employess
WHERE commission_pct &＃61; NULL;
#此时有NULL参与运算&＃xff0c;不会有任何结果。


2.2 <&＃61;>

SELECT 1 &＃61; NULL, NULL &＃61; NULL FROM employees;
#结果 0 1
#安全等于&＃xff1a;若两个比较值&＃xff0c;只有一个为NULL那么结果为NULL&＃xff0c;如果两个为NULL那么结果为1。#练习&＃xff0c;查询表中commission_pic属性值NULL的元组
SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pic <&＃61;> NULL;

2.3 非符号比较运算符

• IS NULL / IS NOT NULL / ISNULL( )

#练习1 查询表中commission_pic属性值为NULL的元组
SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pic IS NULL;
#或以下这种写法
SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pic ISNULL(commission_pct);#练习2 查询表中commission_pic属性值不为NULL的元组
SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pic IS NOT NULL;
#或
SELECT last_name, salary, commission_pct
FROM employees
WHERE NOT commission_pic <&＃61;> NULL;


注意:运算表达书是有值的&＃xff0c;只有当值为1时才输出&＃xff0c;有的有NULL参与运算可能直接使运算结果为NULL从而不显示数据。

• LEAST( )从多个属性 / 值 中&＃xff0c;值的比较&＃xff0c;返回较小的那个。
• GREATEST( )从多个属性 / 值 中&＃xff0c;值的比较&＃xff0c;返回较大的那个。

SELECT LEAST(&＃39;g&＃39;,&＃39;b&＃39;,&＃39;c&＃39;,&＃39;d&＃39;), GREATEST(&＃39;g&＃39;,&＃39;b&＃39;,&＃39;c&＃39;,&＃39;d&＃39;);
SELECT LEAST(last_name,first_name), LEAST(LENGTH(last_name),LENGTH(first_name)) FROM employees;


• BETWEEN…AND… &＃xff08;包括边界值&＃xff09;

#查询工资在6000到8000之间的数据
SELECT last_name, salary, commission_pct
FROM employees
WHERE salary BETWEEN 6000 AND 8000;
#运用逻辑操作符等价&＃xff1a;
SELECT last_name, salary, commission_pct
FROM employees
WHERE salary >&＃61; 6000 AND salary <&＃61; 8000;#查询工资不在6000 到 8000 之间
SELECT last_name, salary, commission_pct
FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000;
#运用逻辑操作符等价&＃xff1a;
SELECT last_name, salary, commission_pct
FROM employees
WHERE salary >&＃61; 6000 AND salary <&＃61; 8000;


• IN (set) / NOT IN (set) &＃xff1a;离散型数据查找&＃xff0c;查找一个集合里面的数据

#查找部门id为 10,20,30部门的员工信息。
SELECT last_name,salary,department_id
FROM employees
WHERE department_id IN (10,20,30);#以下这种写法是错误的&＃xff0c;10,20,30本身非0&＃xff0c;作或运算就为真&＃xff0c;所以压根就没有选出数据。
SELECT last_name,salary,department_id
FROM employees
#WHERE department_id &＃61; 10 OR 20 OR 30; 错误
WHERE department_id &＃61; 10 OR department_id &＃61; 20 OR department_id &＃61; 30;#查询工资不是6000,7000,8000的员工信息。
SELECT last_name,salary,department_id
FROM employees
WHERE salary NOT IN (6000,7000,8000);


• LIKE&＃xff1a;模糊匹配查询&＃xff08;正则表达式&＃xff09;

#练习1&＃xff1a;查询last_name中包含字符&＃39;a&＃39;的员工信息。
SELECT last_name
FRoM employees
WHERE last_name LIKE &＃39;%a%&＃39;;
# % 代表不确定个数的字符&＃xff08;0, 1或者多个&＃xff09;#练习2&＃xff1a;查询last_name中以&＃39;a&＃39;开头的员工信息。
SELECT last_name
FRoM employees
WHERE last_name LIKE &＃39;a%&＃39;;#练习3&＃xff1a;查询last_name中包含字符&＃39;a&＃39;且包含字符&＃39;e&＃39;的员工信息。
#写法1&＃xff1a;
SELECT last_name
FRoM employees
WHERE last_name LIKE &＃39;%a%&＃39; AND last_name LIKE &＃39;%e%&＃39;;
#写法2&＃xff1a;
SELECT last_name
FRoM employees
WHERE last_name LIKE &＃39;%a%e%&＃39; OR last_name LIKE &＃39;%e%a%&＃39;;##练习4&＃xff1a;查询last_name中第二个字符为&＃39;a&＃39;的员工信息。
SELECT last_name
FRoM employees
WHERE last_name LIKE &＃39;_a%&＃39;;
#下划线代表一个不确定的字符。#练习4&＃xff1a;第二个字符为&＃39;_&＃39;且第3个字符是&＃39;a&＃39;的员工信息。
SELECT last_name
FRoM employees
WHERE last_name LIKE &＃39;_\_a%&＃39;;#练习5(了解即可)&＃xff1a;第二个字符为&＃39;_&＃39;且第3个字符是&＃39;a&＃39;的员工信息。
SELECT last_name
FRoM employees
WHERE last_name LIKE &＃39;_$_a%&＃39; ESCAPE &＃39;$&＃39;;
#使用ESCAPE让美元符号$变成转移符号。

• REGEXP / RLIKE &＃xff1a;正则表达式

REGEXP运算符在进行匹配时&＃xff0c;常用的有下面几种通配符&＃xff1a;

(1)&＃39;^&＃39;匹配以该字符后面的字符开头的字符串。
(2)&＃39;$&＃39;匹配以该字符前面的字符结尾的字符串。
(3)&＃39;.&＃39;匹配任何一个单字符。
(4)"[...]"匹配在方括号内的任何字符。例如&＃xff1a;"[abc]"匹配"a"或"b"或"c"。为了命名字符的范围&＃xff0c;使用一个&＃39;-&＃39;。"[a-z]"匹配任何字母&＃xff0c;而"[0-9]"匹配任何数字。
(5)&＃39;*&＃39;匹配零个或多个在它前面的字符。例如&＃xff0c;"x*"匹配任何数量的&＃39;x&＃39;字符&＃xff0c;"[0-9]*"匹配任何数量的数字&＃xff0c;而"*"匹配任何数量的任何字符。


SELECT &＃39;atguigu&＃39; REGEXP &＃39;gu.gu&＃39;
FROM exployees;
#找出&＃39;atguigu&＃39;这个字符中有没有&＃39;gu....gu&＃39;这个字符&＃xff0c;...表示中间无论有多少字符。

3.逻辑运算符