MySQL基础语法

DDL(Data Definition Language)

数据库定义语言，用来定义数据库对象（数据库、表、字段）

数据库操作

1、创建

CREATE DATABASE 数据库名 [DEFAULT CHARSET SET 字符集] [COLLATE 排序规则];
-- 例如
CREATE DATABASE 数据库名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

2、使用

USE 数据库名;

3、删除

DROP DATABASE [IF EXISTS] 数据库名;

4、查询所有

SHOW DATABASES;

表操作

1、创建

CREATE TABLE 表名(
字段1 字段1类型 [COMMENT 字段1注释],
字段2 字段2类型 [COMMENT 字段2注释],
字段3 字段3类型 [COMMENT 字段3注释],
......
字段n 字段n类型 [COMMENT 字段n注释]
)[COMMENT 表注释];

2、查询

• 查询当前数据库所有表

SHOW TABLES;

• 查询表结构

DESC 表名;

• 查询指定表的建表语句

SHOW CREATE TABLE 表名;

3、修改

• 添加字段

ALTER TABLE 表名 ADD 字段名 类型（长度）[COMMENT 注释] [约束] ;

• 修改字段数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型（长度）;

• 修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 （长度）[COMMENT 注释] [约束];

• 修改表名

ALTER TABLE 表名 RENAME TO 新表名;

• 删除表中字段

ALTER TABLE 表名 DROP 字段名

4、删除

• 删除表

DROP TABLE [IF EXISTS] 表名;

• 删除指定表，并重新创建该表（即删除表中所有数据保留字段），截断

TRUNCATE TABLE 表名;

---

DML(Data Manipulation Language)

数据操作语言，用来对数据库表中的数据进行增删改

1、添加数据

• 给指定字段添加是数据

INSERT INTO 表名(字段1,字段2,......) VALUES (值1,值2,......);

• 给全部字段添加数据

INSERT INTO 表名 VALUES (值1,值2,......);

• 批量添加数据

INSERT INTO 表名(字段1,字段2,...) VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);

INSERT INTO 表名 VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);

注意：

• 插入数据时，指定字段顺序需要与值的顺序是一一对应的。
• 字符串和日期类型需要包含在引号中（’ ')，注意中英文切换。
• 插入的数据大小，应在字段的规定范围内。

2、修改数据

UPDATE 表名  SET 字段名1=值1,字段名2=值2,... [WHERE 条件]; 

注意

• 修改语句的条件可有可无，若没有条件，则会修改整张表的指定字段的所有数据。

3、删除数据

不加where条件只是删除数据，清空

DELETE FROM 表名 [WHERE 条件]; 

注意

• DELETE 语句的条件可有可无，若没有，则删除整张表的数据。
• DELETE 语句不能删除某一个字段的值（可以使用UPDATE）。

---

DQL(Data Query Language)

数据查询语言，用来查询数据库中表的记录
一个完整的select语句
select 字段 from 表名 where… group by… having… order by… limit…
执行顺序
1、首先执行where语句过滤原始数据
2、执行group by进行分组
3、执行having对分组数据进行操作
4、执行select语句，选出数据
5、执行order by排序
6、执行limit
语法

SELECT       字段列表
FROM         表名列表
WHERE 		 条件列表
GROUP BY     分组字段列表
HAVING       分组后条件列表
ORDER BY     排序字段列表
LIMIT  	     分页参数

基础查询

• 查询多个字段

SELECT 字段1,字段2,字段3,... FROM 表名;

• 查询整张表(* 效率较低)

SELECT * FROM 表名;

• 设置别名（AS可以省略）

SELECT 字段1 [AS 别名1],字段2 [AS 别名2],字段3 [AS 别名3],... FROM 表名;

• 去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

条件查询

1、语法

SELECT 字段列表 FROM 表名 WHERE 条件列表; 

2、条件

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<>或 !=	不等于
BETWEEN … ADD …	在某个范围内（含）
IN(…)	在in之后的列表中的值，多选一
LIKE 占位符	模糊匹配（_匹配单个字符，%匹配多个字符）
IS NULL	是NULL
IS NOT NULL	不是NULL

---

逻辑运算符	功能
AND 或 &&	并且
OR 或 ||	或者
NOT 或 !	非，不是

---

聚合查询

1、聚合函数
将一列数据作为一个整体。纵向计算。
2、常见聚合函数

函数	含义
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

3、语法

SELECT 聚合函数(字段列表) FROM 表名;

注意

• 聚合函数不统计null值。

分组查询

1、语法

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段 [HAVING 分组后过滤条件]

注意
1、where和having的区别

• 执行时机不同：where是分组前进行过滤，不满足where条件，不参与分组；而having是在分组之后对结果进行过滤。
• 判断条件不同：where不能对聚合函数进行判断，而having可以。

2、执行顺序：where > 聚合函数 >having。

3、分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无意义。

排序查询

1、语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2... 

2、排序方式

• ASC：升序（默认）
• DESC：降序

注意

• 如果是多字段排序，当第一个字段相同时，才会根据第二个字段进行排序。

分页查询

1、语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

注意

• 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页记录数。
• 分页查询是数据库的方言，不同数据库有不同的实现，MySQL中是LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为limit n。

---

DCL(Data Control Language)

数据控制语言，用来创建数据库用户、控制数据库的访问权限

管理用户

1、查询用户

USE mysql;
SELECT * FROM user;

2、创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

3、修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

4、删除用户

DROP USER  '用户名'@'主机名' ;

权限控制

常用

权限	说明
ALL,ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

1、查询权限

SHOW GRANTS FOR '用户名'@'主机名'

2、授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名' 

3、撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'

注意

• 多个权限之间，使用逗号分隔。
• 授权时，数据库名和表名可以使用* 进行通配，代表所有，例如：* .*。

---

函数

MySQL数据库中除了聚合函数，还内置了很多其它函数。

字符串函数

函数	含义
CONCAT(S1,S2,…,Sn)	字符串拼接，将S1,S2…Sn拼接成一个字符串
LOWER(str)	将字符串str全部转为小写
UPPER(str)	将字符串str全部转为大写
LPAD(str,n,pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str,n,pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)	去掉字符串头部和尾部的空格(不去除tab)
trim(str1 from str2)	去除str2两端的str1
SUBSTRING(str,start,len)	返回从字符串str从start位置起的len个长度的字符串(从1开始)
replace(str1,str2,str3)	用str3替换str1中的str2
length(str)	返回str的长度
instr(str1,str2)	返回str2在str1中的位置，找不到返回0

数值函数

函数	含义
CELI(X)	向上取整
FLOOR(X)	向下取整
MOD(x,y)	返回x/y的模
RAND()	返回0~1内的随机数
ROUND(x,y)	求参数x的四舍五入的值，保留y位小数
truncate(a,b)	截取a小数点b位

日期函数

函数	含义
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date,INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2)	返回起始时间date1和结束时间date2之间的天数（date1-date2）
DATE_FORMAT(date,format)	返回指定格式的日期，参考w3school

流程函数

函数	含义
IF(value,t,f)	如果value为true，则返回t，否则返回f
IFNULL(value1,value2)	如果value不为空，返回value1，否则返回value2
CASE WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END	如果val1为true，返回res1，… 否则返回default默认值
CASE [ expr ] WHEN [ val1 ] THEN [res1] … ELSE [ default ] END	如果expr的值等于val1，返回res1，… 否则返回default默认值

---

约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
目的：保证数据库中数据的正确性、有效性和完整性。
分类：

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束（8.0.16版本之后）	保证字段值满足某一个条件	CHECK
外键约束	用来让两张的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

非空约束：not null

-- 建表时，添加非空约束
create table 表名(字段名 字段类型 not null,字段名 字段类型,.....);
-- 建表后，添加非空约束
alter table 表名 modify 字段名 字段类型 not null;
--建表后删除约束
alter table 表名 modify 字段名 字段类型;

注意：如果记录中的记录已经是null了，那么这个字段不能添加非空约束

2、唯一约束：unique

-- 建表时，添加唯一约束
create table 表名(字段名 字段类型 unique,字段名 字段类型,.....);
-- 建表后，添加唯一约束
alter table 表名 modify 字段名 字段类型 unique;
--建表后，删除唯一约束
alter table 表名 drop index 字段名;

注意：如果字段值已经重复，那么不能添加唯一约束

注意：

• 一个字段可以同时被设置成非空且唯一
• 如果表中的某一个字段同时非空且唯一，那么这个字段就是主键字段
• 添加了唯一约束的字段值可以是null，并且其他记录的这个字段值也可以为null

3、主键约束：primary key

单一主键：paimary key(字段名)
联合主键：primary key (字段名1，字段名2)

-- 建表时，添加主键约束
create table 表名(字段名 字段类型 primary key,字段名 字段类型,...);
create table 表名(字段名 字段类型,字段名 字段类型,...,[CONSTRAINT] primary key(字段名));
-- 建表后，添加主键约束
alter table 表名 add primary key(字段名)；
--删除主键约束
alter table 表名 drop  primary key;

注意：删除主键约束后，相当于删除了唯一约束，留下了not null

主键自增长primary key auto_increment

-- 建表时，添加主键自增长
create table 表名(字段名 字段类型 primary key auto_increment,字段名 字段类型,...);
-- 建表后，添加主键且自增长
alter table 表名 modify 字段名 字段类型 primary key auto_increment;
-- 建表后，为主键添加自增长
alter table 表名 modify 字段名 字段类型 auto_increment;
-- 建表后，删除主键的自增长
alter table 表名 modify 字段名 字段类型;

注意：

1、只有主键才能自增长
2、主键类型必须是数值类型
3、添加自增长的主键时，值可以为空
4、从存在过的最大值开始自增

4、检查约束：check()

--创建表时设置检查约束
create table 表名(字段名 字段类型 check(检查约束条件),字段名 字段类型,...);
create table 表名(字段名 字段类型,字段名 字段类型,..., check(检查约束田间));
-- 创建表后，设置检查约束
ALTER TABLE 表名 ADD CONSTRAINT <检查约束名> CHECK(<检查约束条件>)
-- 删除检查约束
ALTER TABLE 表名 DROP CONSTRAINT <检查约束名>;

5、外键约束：foreign key

单一外键：
联合外键：

-- 创建表时，添加外键
CREATE TABLE 表名(字段名 数据类型,...,[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名));
-- 创建表后，添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
-- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

-- 创建表时，添加外键
CREATE TABLE emp(
id int PRIMARY KEY,
sname varchar(10),
dept_id int,
CONSTRAINT FK_dept_emp FOREIGN KEY (dept_id) REFERENCES dept(id)
);
--创建表后，添加外键
ALTER TABLE emp ADD CONSTRAINT FK_dept_emp FOREIGN KEY (dept_id) REFERENCES dept(id);
-- 删除外键
ALTER TABLE emp DROP FOREIGN KEY FK_dept_emp;

删除/更新行为

行为	说明
NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与RESTRICT 一致）
RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与NO ACTION 一致）
CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录
SET NULL	当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）
SET DEFAULT	父表有变更时，子表将外键列设置成一个默认值（Innodb不支持）

---

多表查询

内连接

语法
1、隐式内连接

SELECT 字段列表 FROM 表1，表2 WHERE 条件...;

2、显式内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...WHERE ...;

注意：

• 内连接查询的是两张表交集部分

外连接

语法
1、左外连接

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件...;

2、右外连接

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件;

左外连接，相当于查询表1的全部数据与表1和表2交集部分的数据；右外连接类似。

自连接

语法

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

自连接查询，可以是内连接查询，也可以是外连接查询。

联合查询

对于联合查询（union,union all）就是把多次查询的结果合并起来，形成一个新的查询结果集。
语法

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表A ...;

子查询

概念：SQL语句中嵌套SELECT语句，称为嵌套查询，即子查询。

SELECT * FROM 表1 WHERE column1 = (SELECT column1 FROM 表2);

子查询外部查询的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。
根据子查询的结果不同，分为：

• 标量子查询（子查询结果为单个值）
• 列子查询（子查询结果为一列）
• 行子查询（子查询结果为一行）
• 表子查询（子查询结果为多行多列）

根据子查询的位置不同，分为：WHERE之后、FROM之后、SELECT之后。

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等）。
常用的操作符：=、<>、>、>=、<、<=

列子查询

子查询返回的结果是一列（可以是多行）。
常用操作符：

操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有返回值都必须满足

行子查询

子查询返回的结果是一行（可以是多列）。
常用操作符：= 、<>、IN、NOT IN

表子查询

返回结果是多行多列。
常用操作符：IN

脏读、幻读、不可重复读

1、脏读
脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。

2、不可重复读
不可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据出现不一致的情况。

3、幻读
查的时候明明没有这条记录，但插入的时候 却告诉我 主键冲突，这就好像幻觉一样。这才是所有的幻读。

事务

事务是一组操作的集合，不可分割的工作单位，要么同时成功要么同时失败。
MySQL的事务是自动提交的，隔离级别默认是REPEATABLE READ;
Orcale默认隔离级别是READ COMMITTED;

事务操作

• 查看/设置事务提交方式

SELECT @@autocommit;
SET @@autocommit = 0;  //自动提交autocommit 的值为 1 ；手动提交为 0 ；

• 提交事务

COMMIT;

• 回滚事务

ROLLBACK;

事务的四大特性（ACID）

• 原子性
  事务是不可分割的最小操作单元，要么全部成功，要么全部失败
• 一致性
  事务完成时，必须使所有的数据都保持一致状态
• 隔离性
  数据库系统提交的隔离事务机制，保证事务在不受外部并发操作影响的独立环境下运行
• 持久性
  事务一旦提交或回滚，他对数据库中的数据操作时永久的

并发事务问题

问题	描述
脏读	一个事务读到另一个事务还没有提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，发现这行数据已经存在，好像存在了“幻觉”

事务隔离级别

隔离级别从上至下越来越高

隔离级别	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable Read	×	×	√
Serializable （串行化）	×	×	×

查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVAL { Read uncommitted | Read committed | Repeatable Read | Serializable |};

注意事务隔离级别越高，效率越低，安全性越高