前言
本文主要讲述MySQL中4中SQL语言的使用及各自特点。
SQL语言总共分四类:DDL、DML、DQL、DCL。
SQL-DDL | Data Definition Language
数据定义语言:用来定义/更改数据库对象(数据库、表、字段)
用途 | 操作数据库
# 查询所有数据库
show databases;
# 创建数据库:[]里面的内容为选写,增加创建数据库时候的补充条件
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
-- create schema [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
# 查看创建当前数据库时候的建库SQL语句
show create table 表明;
# 进入【切换】到某个数据库
use 数据库名;
# 查看当前数据库名字
select database();
# 删除数据库
DROP DATABASE [ IF EXISTS ] 数据库名;
字符集 | utf8、utf8mb4等 |
引擎 | InnoDB(MySQL5.5之后的默认引擎)、Memory、MyISAM |
用途 | 操作数据表
# 查询当前数据库的所有表
show tables;
# 创建数据表
CREATE TABLE 表名(
字段1 字段1类型 [约束] [COMMENT 字段1注释],
字段2 字段2类型 [约束] [COMMENT 字段2注释],
字段3 字段3类型 [约束] [COMMENT 字段3注释],
......
字段n 字段n类型 [约束] [COMMENT 字段n注释]
) ENGINE=引擎 DEFAULT CHARSET=字符集;[COMMENT 表注释] ;
# 查看指定表
desc 表名;
# 查看创建当前数据表时候的建表SQL语句
show create table 表名;
# 修改数据表名字
ALTER TABLE 表名 RENAME TO 新表名;
# 数据表-添加字段
ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
# 数据表-修改字段数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);
# 数据表-修改字段名+数据类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
# 数据表-删除字段
ALTER TABLE 表名 DROP 字段名;
# 删除数据表
DROP TABLE [IF EXISTS] 表名;
# 删除数据表后会再重新创建该数据表
TRUNCATE TABLE 表名;
用途 | 约束
约束,作用于数据表中字段上的规则,用于限制存储在表中每个字段下的数据。保证数据库中数据的正确、有效、完整。
约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。
约束类别如下:
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | NOT NULL |
唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | PRIMARY KEY |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | DEFAULT |
检查约束(8.0.16版本之后) | 保证字段值满足某一个条件 | CHECK |
外键约束 | 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 | FOREIGN KEY |
约束示例 | 常规约束
CREATE TABLE tb_user(
id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
name varchar(10) NOT NULL UNIQUE COMMENT '姓名' ,
age int check (age > 0 && age <= 120) COMMENT '年龄' ,
statues char(1) default '1' COMMENT '状态',
gender char(1) COMMENT '性别'
);
上述建表语句解释:
id | id设置为主键,并且自动增长 |
name | 不为空,并且唯一 |
age | 范围为(0, 120] |
status | 默认为1 |
gender | 没有约束 |
【补充】
AUTO_INCREMENT
是MySQL的一个属性,不是约束。自动递增的意思。
- 假设id有该属性,当插入数据不带有id的时候,会默认在最新的id值基础上+1作为新插入数据的id值;当插入数据带有id的时候,只要id不重复,可以非连续插入数据。
约束示例 | 新建外键约束
外键约束的用法:
外键约束用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性
- 有A和B两张表:A表示部门表;B表示员工表,里面有的部门id字段
- 表A和表B可以建立外键关系:表A为主表;表B为从表,在从表中加入外键约束
外键约束的通用写法 | 建表时添加外键约束
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);
-- []:表示可选项;():必须写
外键约束的通用写法 | 对已有表添加外键约束
# 已有数据表,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 自定义外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;
示例
- 员工表:emp
字段 | 含义 |
---|---|
id | ID |
name | 姓名 |
age | 年龄 |
dept_id | 所属部门id |
- 部门表:dept
字段 | 含义 |
---|---|
id | ID |
name | 部门名称 |
-
解释:
-
为员工表的dept_id字段添加外键约束,关联部门表的主键id
-
dept_id就是外键
-
带有外键的表emp,就是子表;另外一个表是父表
-
-
添加约束
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
约束示例 | 删除外键约束
基本语法
ALTER TABLE 表名 DROP FOREIGN KEY 自定义外键名称;
【补充】如果定义外键的时候,没有指定外键名称,需要按照对应数据库的规则,查找到对应的外键名称,才能对外键进行删除。所以定义外键的时候,最好指定别名。
删除上述示例中的外键
# 删除外键约束
alter table emp drop foreign key fk_emp_dept_id;
表字段约束的行为
添加外键之后,删除父表数据时产生约束的行为,就称为删除/更新行为
行为类别如下:
行为 | 说明 |
---|---|
NO ACTION | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。 (与 RESTRICT 一致) 默认行为 |
RESTRICT | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。 (与 NO ACTION 一致) 默认行为 |
CASCADE | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则也删除/更新外键在子表中的记录 |
SET NULL | 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(这就要求该外键允许取null) |
SET DEFAULT | 父表有变更时,子表将外键列设置成一个默认的值 (Innodb不支持) |
SQL语法
ALTER TABLE 子表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 父表名 (父表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
SQL-DML | Data Manipulation Language
数据操作语言,用来对数据库中表的数据记录进行增、删、改操作
所有的DML语言,在没有条件的时候,默认操作全表数据。
用途 | 添加数据
# 添加表数据--单行--指定字段
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, ...);
用途 | 修改数据
# 修改表数据
UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ];
用途 | 删除数据
# 删除表数据
DELETE FROM 表名 [ WHERE 条件 ] ;
SQL-DQL | Data Query Language
数据查询语言,用来查询数据库中表的记录
基础查询 | 不带查询条件
# 查询多个字段
SELECT 字段1, 字段2, 字段3 ... FROM 表名;
# 查询所有字段
SELECT * FROM 表名 ;
# 查询字段并设置别名(写不写as都一样)
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;
# 去重查询
SELECT DISTINCT 字段列表 FROM 表名;
基础查询 | 带有查询条件
常用的比较运算符:
比较运算符 | 写法示例 | 功能 |
---|---|---|
>、>=、<、<=、= | WHERE id > 10 | 大于、大于等于、小于、小于等于、等于 |
<> 或者 != | WHERE id <> 10 | 不等于 |
BETWEEN ... AND ... | WHERE id BETWEEN 10 AND 100 | 在某个范围之内:[最小值、最大值],是个闭区间 |
IN(...) | WHERE id in (10, 12, 14) | 在in之后的列表中的值,多选一 |
NOT IN(...) | WHERE id NOT IN (1, 5, 9) | 返回值不在列表内的行 |
LIKE 占位符 | WHERE id LikE _ABC | 模糊匹配:_匹配单个字符, %匹配任意个字符) |
IS NULL | WHERE id IS NULL | 判断是否是NULL |
IS NOT NULL | WHERE id IS NOT NULL | 判断是否不是NULL |
上述部分语句解析:
WHERE id LikE _ABC
返回以ABC为结尾,且整体长度为4的数据,例如:1ABC、AABC、ABABC(这个不是,长度不满足)
常用的逻辑运算符:
常用逻辑运算符 | 功能 |
---|---|
AND 或者 && | 并且 |
OR 或者 || | 或者 |
NOT 或者 ! | 非,不是 |
示例
# 基础语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
# 示例--占位符
select * from emp where name like '__'; # 查询姓名为两个字的员工
select * from emp where idcard like '%X'; # 查询身份证结尾为X的员工
select * from emp where idcard like '_________________X'; # 查询身份证结尾为X的员工
聚合查询 | 聚合函数
聚合查询:以一列数据为整体,进行纵向查询。任何值为Null的字段数据,都不参与聚合查询
函数 | 功能 |
---|---|
count(id) | 统计id列的数量 |
max(salary) | 返回salary列的最大值 |
min(salary) | 返回salary列的最小值 |
avg(salary) | 返回salary列的平均值 |
sum(salary) | 返回salary列的总和 |
# 基础语法
SELECT 聚合函数(字段列表) FROM 表名;
聚合查询 | 分组查询GROUP BY
基础语法
# 通用语法
# 分组查询经常和聚合函数一起使用
SELECT 字段列表 FROM 表名 [ WHERE 分组前筛选条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];
where和having的区别?
- 执行时机:where在分组前执行;having在分组后执行
- 判断条件:where不能对聚合函数进行判断,having可以
执行顺序
- where > 聚合函数 > having
示例
# 根据性别分组, 统计男性员工 和 女性员工的数量
select gender, count(*) from emp group by gender;
# 根据性别分组 , 统计男性员工 和 女性员工的平均年龄
select gender, avg(age) from emp group by gender;
# 查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于等于3的工作地址
select workaddress, count(*) AS address_count from emp where age < 45 group by workaddress having address_count >= 3; # AS可省略
# 示例:返回每个role角色下,所有人的薪水总和,然后根据role分组,筛选总和大于1500的结果
select role, sum(salary) as totle from emp group by role having totle > 1500;
排序查询
基础语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 [ASC|DESC] , 字段2 [ASC|DESC];
排序 | |
---|---|
ASC | 升序,默认的排序方式 |
DESC | 降序 |
多字段排序的规则:当第一个字段一样时候,才会根据第二个字段排序,后续字段排序同理
示例
# 根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序
select * from emp order by age, entrydate desc;
分页查询
基础语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
- 分页查询算是数据库的方言,不同的数据库有不同的实现。MySQL中使用的是LIMIT
- 起始索引、待查询页码、每页记录数之间的关系:
起始索引 = (待查询的页码-1)* 每页记录数
多表查询:联合查询、子查询【重要】
参考文章:https://blog.csdn.net/qq_45445505/article/details/137051199
DQL语句的约定编写顺序和执行顺序
编写顺序
SELECT 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY 分段字段列表
HAVING 分组后条件列表
ORDER BY 排序字段列表
LIMIT 分页参数
执行顺序
FROM 表名列表
WHERE 条件列表
GROUP BY 分段字段列表
HAVING 分组后条件列表
SELECT 字段列表
ORDER BY 排序字段列表
LIMIT 分页参数
SQL-DCL | Data Control Language
数据控制语言,用来管理数据库用户、控制数据库的访问权限
MySQL默认的数据库
mysql | 主要存储数据库用户、权限等信息 |
information_schema | 存放了MySQL服务器所维护的所有其他数据库的信息 |
performance_schema | 收集服务器的执行同级信息。通过该库,用户可以查看服务器的运行过程,帮助定位可能存在的性能瓶颈。 |
sys | performance_schema 的简化存在 |
mysql.user表的增删改查
管理用户
# 查询用户
select * from mysql.user;
- Host代表当前用户访问的主机:
localhost
代表只能本机访问,不可以远程访问;若改为%,表示任意ip可访问该数据库 - User代表访问该数据库的用户名:root是默认用户;可以新建自定义的用户
- MySQL中通过Host、User来唯一标识一个用户
# 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
# 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
# 删除用户
DROP USER '用户名'@'主机名';
权限控制
限定用户对特定数据库中特定表的访问权限。
权限列表 | 说明 |
---|---|
ALL、ALL PRIVILEGES | 所有权限 |
SELECT | 查询数据 |
INSERT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库、表、视图 |
CREATE | 创建数据库、表 |
基本语法:
# 查询权限
SHOW GRANTS FOR '用户名'@'主机名' ;
# 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
# 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
示例:
# 查询 'heima'@'%' 用户的权限
show grants for 'heima'@'%';
# 授予 'heima'@'%' 用户itcast数据库所有表的所有操作权限
grant all on itcast.* to 'heima'@'%';
# 撤销 'heima'@'%' 用户的itcast数据库的所有权限
revoke all on itcast.* from 'heima'@'%';
- 用*号表示通配符,表示对数据库的所有表格赋予权限