MySQL基础-2

1、 SQL-DML语句

• DML，用来对数据库中表的数据记录进行增删改操作。

  • 添加数据（INSERT）

  • 修改数据（UPDATE）

  • 删除数据（DELETE）

DML-添加数据

1. 给指定字段添加数据

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

2. 给全部字段添加数据

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

3. 批量添加数据

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

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

注意：

• 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
• 字符串和日期型数据应该包含在引号中。
• 插入的数据大小，应该在字段的规定范围内。

DML-修改数据

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

注意： 修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

DML-删除数据

DELETE FROM 表名 [WHERE 条件];

注意：

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

​

2、SQL-DQL语句

• DQL，数据查询语言，用来查询数据库中表的记录。

  查询关键字：SELECT

DQL-语法（编写顺序）

SELECT

​ 字段列表

FROM

​ 表名列表

WHERE

​ 条件列表

GROUP BY

​ 分组字段列表

HAVING

​ 分组后条件列表

ORDER BY

​ 排序字段列表

LIMIT

​ 分页参数

• 基本查询（SELECT)
• 条件查询(WHERE)
• 聚合函数(count、max、min、avg、sum)
• 分组查询(GROUP BY)
• 排序查询(ORDER BY)
• 分页查询(LIMIT)

DQL-基本查询

• 查询多个字段

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

  SELECT * FROM 表名;

• 设置别名

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

• 去除重复记录

  SELECT DISTINCT 字段列表 FROM 表名;

DQL-条件查询

• 语法

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

• 条件

  

  

  • 案例：

    1. 查询年龄等于88的员工

       select * from employee where age = 88;

    2. 查询年龄小于20的员工信息

       select * from employee where age < 20;

    3. 查询年龄小于等于20的员工信息

       select * from employee where age < 20;

    4. 查询没有身份证号的员工信息

       select * from employee where idcard is null;

    5. 查询有身份证号的员工信息

       select * from employee where idcard is not null;

    6. 查询年龄不等于88的员工信息

       select * from employee where age != 88;

       或者 select * from employee where age <> 88;

    7. 查询年龄在15岁（包含）到20岁（包含）之间的员工信息

       select * from employee where age >= 15 && age <=20;

       或者 select * from employee where age >= 15 and age <=20;

       或者 select * from employee where age between 15 and 20;

    8. 查询性别为女且年龄小与25岁的员工信息

       select * from employee where gender = '女' and age < 25;

    9. 查询年龄等于18或20或40的员工信息

       select * from employee where age = 18 or age = 20 or age = 40;

       或者 select * from employee where age in(18,20,40);

    10. 查询姓名为两个字的员工信息

        select * from employee where name like '__';

    11. 查询身份证号最后一位是X的员工信息

        select * from employee where idcard like '%X';

DQL-聚合函数

• 介绍：将一列数据作为一个整体，进行纵向计算。

• 常见聚合函数

  

• 语法

  SELECT 聚合函数（字段列表） FROM 表名;

  注意： null值不参与所有聚合函数运算

  • 案例：

    1. 统计该企业员工数量

       select count(*) from employee;

    2. 统计该企业员工的平均年龄

       select avg(age) from employee;

    3. 统计该企业员工的最大年龄

       select max(age) from employee;

    4. 统计该企业员工的最小年龄

       select min(age) from employee;

    5. 统计北京地区员工的年龄之和

       select sum(age) from employee where workaddress = '北京';

DQL-分组查询

• 语法

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

  • 案例：

    1. 根据性别分组，统计男性员工和女性员工的数量

       select gender,count(*) from employee group by gender;

    2. 根据性别分组，统计男性员工和女性员工的平均年龄

       select gender,avg(age) from employee group by gender;

    3. 查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址

       select workaddress,count(*) from employee where age < 45 group by workaddress having count(*) >= 3;

• where与having区别

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

注意：

1. 执行顺序：where > 聚合函数 > having。
2. 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

DQL-排序查询

• 语法

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

  • 案例：

    1. 根据年龄对公司的员工进行升序排序

       select * from employee order by age asc;

       或者 select * from employee order by age;

    2. 根据入职时间，对员工进行降序排序

       select * from employee order by entrydate desc;

    3. 根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序

       select * from employee order by age asc,entrydate desc;

• 排序方式

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

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

DQL-分页查询

• 语法

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

  • 案例：

    1. 查询第1页员工数据，每页展示10条记录

       select * from employee limit 0,10;

       或者 select * from employee limit 10;

    2. 查询第2页员工数据，每页展示10条记录

       select * from employee limit 10,10;

注意：

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

DQL-案例：

1. 查询年龄为20,21,22,23岁的员工信息。

   select * from employee where gender = '女' and age in(20,21,22,23);

2. 查询性别为男，并且年龄在20~40岁（含）以内的姓名为三个字的员工。

   select * from employee where gender = '男' and age between 20 and 40 and name like '___';

3. 统计员工表中，年龄小于60岁的，男性员工和女性员工的人数。

   select gender,count(*) from employee where age < 60 group by gender;

4. 查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。

   select name,age from employee where age <= 35 order by age asc,entrydate desc;

5. 查询性别为男，且年龄在20~40岁（含）以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。

   select * from employee where gender = '男' and age between 20 and 40 order by age asc,entrydate asc limit 5;

DQL-执行顺序

FROM

​ 表名列表

WHERE

​ 条件列表

GROUP BY

​ 分组字段列表

HAVING

​ 分组后条件列表

SELECT

​ 字段列表

ORDER BY

​ 排序字段列表

LIMIT

​ 分页参数

3、SQL-DCL语句

• DCL，数据控制语言，用来管理数据库用户、控制数据库的访问权限。

DCL-用户管理

• 查询用户

  USE mysql；

  SELECT * FROM user;

• 创建用户

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

• 修改用户密码

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

• 删除用户

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

案例：

1. 创建用户 itcast，只能够在当前主机localhost访问，密码123456。

   create user 'itcast'@'localhost' identified by '123456';

2. 创建用户niubi，可以在任意主机访问该数据库，密码123456。

   create user 'niubi'@'%' identified by '123456';

3. 修改用户niubi的访问密码为1234。

   alter user 'niubi'@'%' identified with mysql_native_password by '1234';

4. 删除niubi@%用户。

   drop user 'niubi'@'%';

注意：

1. 主机名可以使用%通配。
2. 这类SQL开发人员操作的比较少，主要是DBA（Datebase Administrator 数据库管理员）使用。

DCL-权限控制

MySQL中定义了很多种权限，但是常用的就以下几种：

• 查询权限

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

• 授予权限

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

• 撤销权限

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

注意：

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