MYSQL-数据操作DDL，DML，DCL，DQL

前言：MYSQL数据操作语言分为四种

1.DDL（数据定义语言）：用来创建数据库中的表、索引、视图、存储过程、触发器等。

2.DML（数据操作语言）：用来对表内数据的添加、更新、删除等。

3.DCL（数据控制语言）：用于授权/撤销数据库及其字段的权限等。

4.DQL（数据查询语言）：用于对表内数据的关键字逻辑查询等。

一.DDL(Data Definition Language)

数据定义语言

1.数据库

创建数据库：create database db1

删除数据库：drop database db1

切换数据库：use db1

修改数据库：alter database db1 charset utf8;

2.表

创建表:

:MariaDB [db1]> create table t1( 

-> id int, 
-> name varchar(50),
-> sex enum('male','female'),
-> age int(3)
-> );

查看表结构：desc t1

修改表结构：alter table 表名 rename 新表名;

删除表：drop table 表名;

清空表结构：truncate 表名 （delete 是清空字段数据）

（详细的可以看另两篇博客

https://www.cnblogs.com/MJM666/p/10624692.html

https://www.cnblogs.com/MJM666/p/10624781.html）

二.DML（Data Manipulation Language)

在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括

1 插入数据INSERT

1. 插入完整数据（顺序插入）
语法一：
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
语法二：
INSERT INTO 表名 VALUES (值1,值2,值3…值n);
2. 指定字段插入数据
语法：
INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);
3. 插入多条记录
语法：
INSERT INTO 表名 VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
4. 插入查询结果
语法：
INSERT INTO 表名(字段1,字段2,字段3…字段n)
SELECT (字段1,字段2,字段3…字段n) FROM 表2
WHERE …;

2. 更新数据UPDATE

语法：
UPDATE 表名 SET
字段1=值1,
字段2=值2,
WHERE CONDITION;
示例：
UPDATE mysql.user SET password=password(‘123’)
where user=’root’ and host=’localhost’;

3. 删除数据DELETE

语法：
DELETE FROM 表名
WHERE CONITION;
示例：
DELETE FROM mysql.user
WHERE password=’’;

三，DCL(Data Control Language）

数据控制语言

1.创建用户

CREATE USER 用户名@IP地址 IDENTIFIED BY '密码';
·用户只能在指定的IP地址上登陆
CREATE USER 用户名@'%' IDENTIFIED BY '密码';
·用户可以在任意IP地址上登录

# 本地账号
create user "egon1"@"localhost"identified by "123"; #mysql-uegon1 -p123
#远程账号
create user "egon2"@"192.168.31.10"identified by "123"; #mysql-uegon2 -p123 -h 服务端IP
create user "egon2"@"192.168.31.%"identified by "123"; #mysql-uegon2 -p123 -h 服务端IP 能在31任意网络段运行
create user "egon2"@"%"identify by "123"; #mysql-uegon2 -p123 -h 服务端IP 任意网络段

2.给用户授权

级别
user: *.* #该表放行的权限,针对：所有数据，所有库下所有表，以及表下的所有字段
db: db1.* #该表放行的权限，针对：某一数据库，该数据库下的所有表，以及表下的所有字段
table_priv:db1.t1 #该表放行的权限。针对：某一张表，以及该表下的所有字段
columns_priv:id,name #该表放行的权限，针对：某一个字段

GRANT 权限1,...,权限n ON 数据库.* TO 用户名@IP地址
·权限、用户、数据库
·给用户分派在指定的数据库上的指定的权限
GRANT ALL ON 数据库.* TO 用户名@IP地址；
·给用户分派指定数据库上的所有权限

grant all on *.* to "egon1"@"localhost"; #给除了不能grant的所有权限
grant select on *.* to "egon1"@"localhost";#给egon1放权 (egon1状态下不可用，也就是不能给自己放权，必须切换到uroot下

grant select on db1.* to "egon1"@"localhost";
grant select on db1.t2 to "egon1"@"localhost";
select * from mysql.tables_priv\G   #查看（\G是以固定格式表现）
grant select(id,name),update(age) on db1.t2 to "egon1"@"localhost";
select * from mysql.columns_priv\G
revoke select(id,name),update(age) on db1.t2 from "egon1"@"localhost";

3.撤销授权

REVOKE 权限1,...,权限n ON 数据库.* FROM 用户名@IP地址；
撤销指定用户在指定数据库上的指定权限

revoke select on *.* from "egon1"@"localhost";
revoke select on db1.* from "egon1"@"localhost";
revoke select on db1.t2 from "egon1"@"localhost";
revoke select(id,name),update(age) on db1.t2 from "egon1"@"localhost";

4.查看权限

SHOW GRANTS FOR 用户名@IP地址
查看指定用户的权限

5.删除用户

DROP USER 用户名@IP地址；

四.DQL(Data Query Language)

数据查询语言

#创建表
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);

#插入记录
#三个部门：教学，销售，运营
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;

1.简单查询

#简单查询
SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id
FROM employee;
SELECT * FROM employee;
SELECT name,salary FROM employee;
#避免重复DISTINCT
SELECT DISTINCT post FROM employee;
#通过四则运算查询
SELECT name, salary*12 FROM employee;
SELECT name, salary*12 AS Annual_salary FROM employee;
SELECT name, salary*12 Annual_salary FROM employee;
#定义显示格式
CONCAT() 函数用于连接字符串
SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary
FROM employee;
CONCAT_WS() 第一个参数为分隔符
SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary
FROM employee;

2.单表关键字查询

(1) where 约束

where字句中可以使用：
比较运算符：><>= <= <> !=
between 80 and 100 值在10到20之间
in(80,90,100) 值是10或20或30
like 'egon%'
pattern可以是%或_，
%表示任意多字符
_表示一个字符
逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

#1:单条件查询
    SELECT name FROM employee
        WHERE post='sale';

#2:多条件查询
    SELECT name,salary FROM employee
        WHERE post='teacher' AND salary>10000;

#3:关键字BETWEEN AND
    SELECT name,salary FROM employee 
        WHERE salary BETWEEN 10000 AND 20000;

    SELECT name,salary FROM employee 
        WHERE salary NOT BETWEEN 10000 AND 20000;

#4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
    SELECT name,post_comment FROM employee 
        WHERE post_comment IS NULL;

    SELECT name,post_comment FROM employee 
        WHERE post_comment IS NOT NULL;

    SELECT name,post_comment FROM employee 
        WHERE post_comment=''; 注意''是空字符串，不是null
    ps：
        执行
        update employee set post_comment='' where id=2;
        再用上条查看，就会有结果了

#5:关键字IN集合查询
    SELECT name,salary FROM employee 
        WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;

    SELECT name,salary FROM employee 
        WHERE salary IN (3000,3500,4000,9000) ;

    SELECT name,salary FROM employee 
        WHERE salary NOT IN (3000,3500,4000,9000) ;

#6:关键字LIKE模糊查询
    通配符’%’
    SELECT * FROM employee 
            WHERE name LIKE 'eg%';

    通配符’_’
    SELECT * FROM employee 
            WHERE name LIKE 'al__';

(2)group by 分组查询

*为何要分组

#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的
#2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等
#3、为何要分组呢？
取每个部门的最高工资
取每个部门的员工数
取男人数和女人数
小窍门：‘每’这个字后面的字段，就是我们分组的依据
#4、大前提：
可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数

*ONLY_FULL_GROUP_BY

mysql >set global sql_mode="ONLY_FULL_GROUP_BY";#只能取分组的字段
分组之后，只能取分组的字段，以及每个组聚合结果

ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，
简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，
要么是来自于group by list中的表达式的值。

*使用group by

单独使用GROUP BY关键字分组
SELECT post FROM employee GROUP BY post;
注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

GROUP BY关键字和GROUP_CONCAT()函数一起使用
SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名
SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;
select post,group_concat(name) from employee group by post;   #concat()函数用于连接字符串

GROUP BY与聚合函数一起使用
select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人

#没有group by则默认整体算为一体
select max(salary) from employee;

 

* 聚合函数

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组

示例：
SELECT COUNT(*) FROM employee;
SELECT COUNT(*) FROM employee WHERE depart_id=1;
SELECT MAX(salary) FROM employee;
SELECT MIN(salary) FROM employee;
SELECT AVG(salary) FROM employee;
SELECT SUM(salary) FROM employee;
SELECT SUM(salary) FROM employee WHERE depart_id=3;

(3)having 过滤查询

HAVING与WHERE不一样的地方在于!!!!!!

#！！！执行优先级从高到低：where > group by > having
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

练习题

2. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
3. 查询各岗位平均薪资大于10000的岗位名、平均工资
4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资

select post,group_concat(name),count(id) from employee group by post;
select post,group_concat(name),count(id) from employee group by post having count(id)<2;
select post,avg(salary) from employee group by post having avg(salary)>10000;
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;

(4)查询排序order by

select * from employee order by age asc; #升序
select *from employee order by age desc; #降序

select * from employee order by age asc,id desc # 先按照age升序，如果age相同则按照ID降序

(5)限制查询的记录数:LIMIT

select * from employee limit 3;
select * from employee order by salary desc limit 1;

select * from employee limit 0,5;
#从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
select * from employee limit 5,5;
#从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

(6)使用正则表达式查询

#正则表达式
select * from employee where name like "jin%";

select *from employee where name regexp "^jin";
select * from employee where name regexp "^jin.*(g|n)$"; # 以jin开头，以g或者n结尾的姓名

小结：对字符串匹配的方式
WHERE name = 'egon';
WHERE name LIKE 'yua%';
WHERE name REGEXP 'on$';

3.多表连接查询

建表

create table department(
id int,
name varchar(20) 
);

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;

 

(1)多表连接查询

#交叉连接：不适用任何匹配条件。生成笛卡尔积

mysql> select * from employee,department;

#内连接：只连接匹配的行,只取两张表的共同部分

select * from employee,department where employee.dep_id = department.id;
select * from employee inner join department on employee.dep_id = department.id;

左连接：在内连接的基础上保留左表的记录
select * from employee left join department on employee.dep_id = department.id;

右连接：在内连接的基础上保留右表的记录
select * from employee right join department on employee.dep_id = department.id;

全外连接： 在内连接的基础上保留左右两表没有对应关系的记录
select * from employee full join department on employee.dep_id = department.id;
但因为MYSQL不支持full jion 的操作。

所以必须把左连接的和右连接进行联合（union）去重。
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id;

(2)符合条件连接查询

#查询平均年龄大于30岁的部门名
select department.name,avg(age) from employee inner join department on employee.dep_id = department.id
group by department.name
having avg(age)>30;

(3)子查询

#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等

*带IN关键字的子查询

查询平均年龄在25岁以上的部门名
select * from department where id in
(select dep_id from employee
group by dep_id
having avg(age)>25);

*带比较运算符的子查询

查询大于所以人平均年龄的员工名与年龄
select name,age from employee where age>
(select avg(age) from employee);
#查询大于部门内平均年龄的员工名与年龄
select t1.name,t1.age from employee as t1 inner join
(select dep_id,avg(age)as avg_age from employee
group by dep_id)as t2 on t1.dep_id =t2.dep_id where t1.age >t2.avg_age;

*带EXISTS关键字的子查询

select * from employee
where exists
(select id from department where id =200);

五.关键字逻辑查询语句

*SELECT语句关键字的定义顺序

SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

*SELECT语句关键字的执行顺序

(7)     SELECT 
(8)     DISTINCT <select_list>
(1)     FROM <left_table>
(3)     <join_type> JOIN <right_table>
(2)     ON <join_condition>
(4)     WHERE <where_condition>
(5)     GROUP BY <group_by_list>
(6)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

1.先从库,表里找
2.on 后面加两表连接的限制条件
3.将两表连接起来（内左右全等）
4.从约束条件where里过滤出数据
5.然后交给group by 进行分组，
6.分完组后 用having 过滤
7.之后才是运行select 后面的语句，
8.distinct进行去重
9.接着轮到order by 排序
10.limit 最后运行

 看完，可以点击下面找练习题练练手，迎接审判吧,哈哈哈！

https://www.cnblogs.com/MJM666/p/10757979.html

谢谢观看，如果有不对的地方，一定要指出来，小木在这里感谢了