526互联

MySQL-基础篇 ( SQL-DML + DQL + DCL )

发布时间 2023-08-17 19:29:37作者: 朱呀朱~

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'@'%'; -- *.* 就是所有库的所有表