MySQL-基础篇 ( SQL-DML + DQL + DCL )
目录SQL
DML
- 数据操作语言,用来对数据库表中的数据进行增删改
添加数据 INSERT
- 给指定字段添加数据
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
- 修改数据
UPDATE 表名 SET 字段名1=值1, 字段名2=值2, ... [WHERE 条件];
- 没有 WHERE 的话就是修改整张表的所有数据
删除数据 DELETE
DELETE FROM 表名 [WHERE 条件];
- 不加 WHERE 就是删除整张表的所有数据
- 注意:想要删除某一字段值不能用 DELETE,可以用 UPDATE
DQL
- 数据查询语言,用来查询数据库中表的记录
语句编写顺序
-
SELECT 字段列表 FROM 表名列表
WHERE 条件列表
GROUP BY 分组字段列表 HAVING 分组后条件列表
ORDER BY 排序字段列表
LIMIT 分页参数
基本查询
- 查询多个字段
SELECT 字段1, 字段2 ... FROM 表名;
SELECT * FROM 表名;
- 在实际开发时一般不写星号,不直观
- 设置别名
SELECT 字段1[AS 别名1], 字段2[AS 别名2] ... FROM 表名;
- AS 是可以省略的,有的会选择将别名部分都用单引号引起来
- 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
条件查询 ( WHERE )
-
SELECT 字段列表 FROM 表名 WHERE 条件列表;
-
条件中可用符号
比较运算符 功能 >,>=,<,<=,= ... <> 或 != 不等于 BETWEEN ... AND ... 在某个范围内 ( 含最大、最小值 ) IN( ... ) 在 in 之后的列表中的值,多选一 LIKE 占位符 模糊查询 ( _ 匹配单个字符,% 匹配任意个字符 ) IS NULL 是 NULL 逻辑运算符 功能 AND 或 && 并且 ( 多个条件同时成立 ) OR 或 || 或者 ( 多个条件任意一个成立 ) NOT 或 ! 非,不是 -
练习
select name from user where address is null; -- 地址为空null的 select name from user where address is not null; -- 地址不为空的 select name from user where age >= 15 && age <= 20; -- 年龄大于等于15小于等于20的 select name from user where age >= 15 and age <= 20; select name from user where age between 15 and 20; -- 前小后大,包含型 select name from user where age = 18 or age = 20; -- 年龄为18或者20的 select name from user where age in(18, 20); select * from user where name like '__'; -- 模糊查询name为两个字的员工信息 select * from user where name like '%宇'; -- 模糊查询最后一字为宇
聚合函数 ( count、max、min、avg、sum )
-
将一列数据作为一个整体,进行纵向计算
-
常见聚合函数
函数 功能 count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和 -
使用:
SELECT 聚合函数(字段列表) FROM 表名;
-
注意:null 值不参与所有聚合函数运算
-
练习
-- 统计所有人数的数量 select count(*) from user; select count(id) from user; -- 也可以写具体的字段,但如果是具体字段的话只统计有值的总数量 -- 统计所有人的平均年龄 select avg(age) from user; -- 统计所有人的最大年龄 select max(age) from user; -- 统计所有人的最小年龄 select min(age) from user; -- 统计中国地区所有人的年龄之和 # select * from user where address = '中国'; -- 所有在中国的人 select sum(age) from user where address = '中国'; -- 然后加上函数进行求和
分组函数 ( GROUP BY )
-
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
-
条件相关:where 与 having 区别
- 执行时机不同:where 是分组之前进行过滤,不满足 where 条件,不参与分组;而 having 是分组之后对结果进行过滤
- 判断条件不同:where 不能对聚合函数进行判断,而 having 可以
-
group by 是根据字段里不重复数据分组
-
执行顺序:
- where > 聚合函数 > having
- count 统计统计出来结果之后再通过 having 对这个结果进行二次过滤
-
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义 ( 即 having 的操作主要是针对 where 和 group by 指定的 )
- 聚合函数:拿到地址 ( 地址分组 ) 和各个地址的总人数后,再获取总人数大于一百的
- 分组字段:拿到地址 ( 地址分组 ) 和各个地址的总人数后,再获取地址名为两个字的
-
练习
-- 根据性别分组,统计男性和女性的数量 # select count(*) from user group by gender; -- gender里只有男和女的话,所有数据就只分成了两组,但只是两个数 select gender, count(*) from user group by gender; -- 为了能看出各个数字所表示的含义,select后还要再加上所需字段 -- 根据性别分组,统计男性和女性的平均年龄 select gender, avg(age) from user group by gender; -- 查询年龄小于45的用户,并根据居住地址分组,获取用户数量大于等于3的居住地址 # 1. 小于45的用户 # 2. 居住地分组,拿到各个居住地总人数 # 3. 再对总人数进行筛选 select address, count(*) from user where age < 45 group by address having count(*) >= 3;
排序查询 ( ORDER BY )
-
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
-
排序方式
- ASC:升序 ( 默认值 )
- DESC:降序
-
注意:如果是多字段排序,第一个字段值相同的才会根据第二个字段进行排序
-
练习
-- 根据年龄对用户进行升序排序 select * from user order by age asc; select * from user order by age; -- asc默认也可不写 -- 根据表的填写时间,对用户进行降序排序 select * from user order by read_time desc; -- 根据年龄对用户进行升序排序,年龄相同的,再按照填写时间进行降序排序 select * from user order by age asc, read_time desc; select * from user order by age, read_time desc; -- 多字段的asc也是可省的
分页查询 ( LIMIT )
-
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
-
注意:
- 起始索引从0开始,起始索引 = ( 查询页码 - 1 ) * 每页显示记录数
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL 中是 LIMIT
- 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10
-
练习
-- 查询第一页用户数据,每页展示十条记录 select * from user limit 0, 10; select * from user limit 10; -- 查询第二页用户数据,每页展示五条记录 select * from user limit 5, 5; -- (页码-1)*也展示记录数,因为起始索引是0开始
DQL 执行顺序
-
执行顺序不同于编写顺序,为:
-
FROM
WHERE
GROUP BY,HAVING
SELECT
ORDER BY
LIMIT
-
DCL
- 数据控制语言,用来管理数据库用户、控制数据库的访问权限
- 控制数据库有哪些用户可以访问
- 控制用户有怎样的访问权限 ( 可以访问哪些库 )
管理用户
-
查询用户
-
USE mysql;
SELECT * FROM user;
-
因为数据库的用户都存储在自带的 mysql 库中的 user 表中,Host 字段的 localhost 表示只允许本机访问,默认使用的用户名为 root
-
-
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
-
修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
-
删除用户
DROP USER '用户名'@'主机名';
-
注意:
- 主机名可以使用 % 通配,表示任意主机
- 这部分主要是 DBA ( Database Administrator 数据库管理员 ) 使用
-
练习
-- 创建用户laozhu,只能在当前主机localhost访问,密码123456; create user 'laozhu'@'localhost' identified by '123456'; -- 默认的能访问的库只有两个,其余的没有权限 -- 创建用户zyz,可以在任意主机访问该数据库,密码123456; create user 'zyz'@'%' identified by '123456'; -- 修改用户zyz的访问密码为112233; alter user 'zyz'@'%' identified with mysql_native_password by '112233'; -- 删除laozhu用户 drop user 'laozhu'@'localhost';
权限控制
-
MySQL 定义了很多种权限,但常用的就以下几种:
权限 说明 ALL,ALL PRIVILEGES 所有权限 SELECT 查询数据 INSERT 插入数据 UPDATE 修改数据 DELETE 删除数据 ALTER 修改表 DROP 删除数据库 / 表 / 视图 CREATE 创建数据库 / 表 - 若是 USAGE 就表示只有登录的权限,其余啥权限也没有
-
查询权限
SHOW GRANTS FOR '用户名'@'主机名';
-
授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
-
撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
-
注意:
- 多个权限之间,用逗号分隔
- 授权时,数据库名和表名可以使用 * 进行通配,代表所有
-
练习
-- zyz用户获取对test库中所有表的所有操作权限 grant all on test.* from 'zyz'@'%'; -- *.* 就是所有库的所有表