mysql学习——SQL中的DQL和DCL

学习黑马MySQL课程，记录笔记，用于复习。

DQL

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。

基本查询

#1.查询多个字段
select 字段1, 字段2, 字段3 ... from 表名 ;
select * from 表名 ;
#2.字段设置别名
select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ] ... from 表名;
select 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... from 表名;
#去重
select distinct 字段列表 from 表名;

条件查询

select 字段列表 from 表名 where 条件列表;

条件列表(除大于小于)：

比较运算符	功能
<> 或 !=	不等于
between … and …	在某个范围之内
in()	in后列表内任一值
like 占位符	_单个字符 ， % 任意字符
is null	为空

#查询姓名为两个字的员工信息
select * from emp where name like '__';
#查询身份证号最后一位是X的员工信息
select * from emp where idcard like '%X';

聚合函数

将一列数据作为一个整体，进行纵向计算
常见的聚合函数（null值不参与计算）：

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

select 聚合函数(字段列表) from 表名;
select count(*) from emp; -- 统计总记录数
select count(idcard) from emp; -- 统计idcard字段不为null的记录数

分组查询

select 字段列表 from 表名 [ where 条件 ] group by 分组字段名 [having 分组后过滤条件];

where与having区别：

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；having是分组之后对结果进行过滤。
• 判断条件不同：where不能对聚合函数进行判断，having可以。
  注意事项:
  • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
  • 执行顺序: where > 聚合函数 > having 。
  • 支持多字段分组, 具体语法为 : group by columnA,columnB

#根据性别分组 , 统计男性员工 和 女性员工的数量
select gender, count(*) from emp group by gender ;
#查询年龄小于45的员工, 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
#1.查询年龄小于45的员工数量
select count(*) from emp where age < 45;
#2.根据工作地址分组,并把count(*)起别名address_count
select workaddress,count(*) address_count from emp where age < 45 group by workaddress;
#3.获取员工数量大于等于3的工作地址
select workaddress,count(*) address_count from emp where age < 45 group by workaddress having address_count >= 3;

排序查询

#多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;
select 字段列表 from 表名 order by 字段1 排序方式1 , 字段2 排序方式2 ;

排序方式：

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

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

分页查询

select 字段列表 from 表名 limit 起始索引, 查询记录数 ;

• 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

#查询第1页员工数据, 每页展示10条记录
select * from emp limit 10;
#查询第2页员工数据, 每页展示10条记录 -----> (页码-1)*页展示记录数=(2-1)*10=10
select * from emp limit 10,10;

#查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。
select name , age from emp where age <= 35 order by age asc , entrydate desc;
#查询性别为男，且年龄在20-40 岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序
#1.查询性别为男，且年龄在20-40 岁(含)以内的员工
select * from emp where gender = '男' and age between 20 and 40
#2.对查询的结果按年龄升序排序，年龄相同按入职时间升序排序，前5个员工信息
select * from emp where gender = '男' and age between 20 and 40 order by age asc, entrydate asclimit 5;

执行顺序： 表–>条件–>分组–>返回字段–>排序–>分页

DCL

DCL英文全称是Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限。

管理用户

#查询用户
select * from mysql.user;

Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问，是不可以远程访问的。
User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一个用户。

#创建用户
create user '用户名'@'主机名' identified by '密码';
create user 'sxl'@'%' identified by '123456';
#修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password BY '新密码' ;
#删除用户
drop user '用户名'@'主机名' ;

权限控制

权限	说明
all	所有权限
select	查询数据
insert	插入数据
update	修改数据
delete	删除数据
alter	修改表
drop	删除数据库/表/视图
create	创建数据库/表

#查询权限
show grants for '用户名'@'主机名' ;
#授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
grant all on test.* to 'sxl'@'%';
#撤销权限
reveke 权限列表 on 数据库名.表名 from '用户名'@'主机名';