MySQL-DML、DQL、DCL

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,···);

• 插入数据，要按照指定的顺序与值一一对应；
• 字符串与日期型数据要在引号''之中；
• 插入数据值的大小，要在字段规定的范围之类。

修改数据

update 表名 set 字段名1=值1,字段名2=值2,...[where 条件];

修改的是符合条件的那一行的数据；没有条件代表修改整张表的数据。

update employee set department='人事部' where age=18;
update employee set enterdate='2002-08-08';

删除数据

delete from 表名 [where 条件]

where条件与修改数据同；

不能删除字段的值(可以使用update，设置为null)

注意删除的是表中的部分数据，而不是表结构。

delete from employee where gender='女';

DQL

查询的频次更高。

select语法：

# 编写顺序
select 
	字段列表# 基本查询
from
	表名列表# 基本查询
where
	条件列表# 条件查询
group by
	分组字段列表# 分组查询，一般配合聚合函数使用
having
	分组后条件列表# 分组查询，一般配合聚合函数使用
order by
	排序字段列表# 排序查询
limit
	分页参数# 分页查询
	
# 执行顺序
from->where->group by->select->order by->limit

基本查询

1. 查询多个字段：select 字段1,字段2,... from 表名;
2. 查询表中全部字段（的数据）：select * from 表名;
3. 设置字段的别名：select 字段1[as 别名1],字段2[as 别名2],... from 表名;
4. 去除重复记录：select distinct 字段列表 from 表名

条件查询

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

条件包含比较运算符和逻辑运算符。

在比较运算符中，比较新的是：

比较运算符	功能
between...and...	在某范围之内，闭区间[,]
in(...)	在in之后的列表中的值，多选1
like 占位符	模糊匹配(占位符：_匹配单个字符、%匹配任意个字符)
is null	是null值

分组查询

聚合函数

聚合函数指将一列数据作为一个整体，进行纵向计算。

常见函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

语法：select 聚合函数(字段列表) from 表名

null不参与聚合函数运算，不是0

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

where与having的区别：

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

同样，使用having也可以达到相同的结果。（当然，where与having还是有不同的）

排序查询

select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;

支持多字段进行排序：第1个字段相同时，使用字段2进行排序。

排序方式有：ASC(升序，默认)、DESC(降序)

分页查询

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

MySQL中起始索引从0开始，其分页查询关键字为limit。

DCL

管理用户

1. 查询用户：use mysql、select * from user
2. 创建用户：create user '用户名'@'主机名' identified by '密码'
3. 修改用户密码：alter user 用户名'@'主机名' identified with mysql_native_password by '新密码'
4. 删除用户：drop user '用户名'@'主机名'

DCL主要由DBA控制捏。

权限控制

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

1. 查询权限：show grants for '用户名'@'主机名'
2. 授予权限：grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'
3. 撤销权限：revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'

相关代码

DCL

insert into 信贷企业 values
('E1','3390939','2017-7-18','A00297',-943.4,-56.6,-1000,'有效发票'),
('E1','3390940','2017-7-18','A00297',-4780.24,-286.81,-5067.05,'有效发票'),
('E1','3390941','2017-7-18','A00297',943.4,56.6,1000,'有效发票'),
('E1','3390942','2017-7-18','A00297',4780.24,286.81,5067.05,'有效发票'),
('E1','9902669','2017-8-7','A05061',326.21,9.79,336,'有效发票'),
('E1','40826107','2017-8-8','A05991',170.94,29.06,200,'有效发票'),
('E1','4420531','2017-8-9','A03142',37735.85,2264.15,40000,'有效发票'),
('E1','4420532','2017-8-9','A03142',4716.98,283.02,5000,'有效发票'),
('E1','15040454','2017-8-11','A02994',46153.85,7846.15,54000,'作废发票'),
('E1','40829320','2017-8-14','A05991',162.39,27.61,190,'有效发票'),
('E1','10769077','2017-8-25','A01798',969.9,29.1,999,'有效发票'),
('E1','15462965','2017-8-27','A03775',466.02,13.98,	480,'有效发票'),
('E1','18308605','2017-8-27','A08485',619.81,37.19,	657,'有效发票'),
('E1','9279217','2017-8-28','A01709',283.02	,16.98,300,'有效发票'),
('E1','15459892','2017-8-28','A06754',728.16,21.84,750,'作废发票'),
('E1','15459893','2017-8-28','A06754',640.78,19.22,660,'有效发票');

# 完成基本查询需求
# 1.查询多个字段设置别名
select 发票号码 as 'haoma',发票状态 from 信贷企业;

# 2.查询所有字段
select * from 信贷企业;

# 3.查询销方企业编号（去除重复）
select distinct 销方单位  from 信贷企业;

# 完成条件查询
# 1.查询价税合计金额大于1k的销方单位
select 销方单位 from 信贷企业 where 价税合计>1000;
# 2.查询发票号码为三位数的信息,数字等变量也可以
select * from 信贷企业 where 价税合计 like '___';

# 聚合函数
# 1. 统计销方单位数量
select count(*) from 信贷企业 where 价税合计 like '___';
# 2. 计算价税合计的sum、avg、min、max
select sum(价税合计),avg(价税合计),min(价税合计),max(价税合计) from 信贷企业;

# 分组查询
# 1.根据发票状态分组，分为有效发票和作废发票
select 发票状态,count(*) from 信贷企业 group by 发票状态;
# 2.选择有效发票且价税合计>0的发票，根据价税金额分组并显示如下
select * from 信贷企业 where 价税合计>0 group by 价税合计 having 发票状态='有效发票';

# 排序查询
# 1.按照金额排序
select * from 信贷企业 where 发票状态='有效发票' order by 价税合计 asc;

# 分页查询
# 1.查询第2页的数据，每页展示5条记录
select * from 信贷企业 limit 1,5;

DCL

# 查询用户
select * from user;
# 创建用户：只能在当前主机访问
create user 'user001'@'localhost' identified by '123456';
# 创建用户：可以在任意主机访问
create user 'user002'@'%' identified by '123456';
# 修改user001密码：
alter user 'user001'@'localhost' identified with mysql_native_password by '1111111111';
# 删除用户
drop user 'user002'@'%';

# 查询权限
# GRANT USAGE ON *.* TO `user001`@`localhost`表示仅仅可以连接数据库并登录。
show grants for 'user001'@'localhost';
# 授予权限
grant all on demo.* to 'user001'@'localhost';
# 撤销权限
revoke all on demo.* from 'user001'@'localhost';