mysql中DML DDL DCL DQL

名字	类型	作用的对象	作用
DDL 英文全称 (Data Definition Language)	数据定义语言	库、表、列	创建、删除、修改、库或表结构，对数据库或表的结构操作
DML 英文全称(Data Manipulation Language)	数据操作语言	数据库记录（数据）	增、删、改，对表记录进行更新(增、删、改)
DQL 英文全称(Data Query Language)	数据查询语言	数据库记录（数据）	查、用来查询数据，对表记录的查询
DCL 英文全称(Data Control Language)	数据控制语言	数据库用户	用来定义访问的权限和安全级别，对用户的创建，及授权

DDL

简单来说，就是对数据库内部的对象进行创建、删除、修改的操作语言。它和DML 语言的最大区别是DML 只是对表内部数据的操作，而不涉及到表的定义、结构的修改，更不会涉及到其他对象。DDL 语句更多的被数据库管理员（DBA）所使用，DDL主要负责数据库的创/改/查/删以及表格的创/改/查/删，一般的开发人员很少使用。

1.创建数据库:

create database [数据库名] character set [字符编码];

2.查看数据库:

show databases;

3.查看数据库创建明细:

show create database [数据库名字];

4.修改数据库编码:

alter database [数据库名] character set [字符编码];

5.删除数据库:

drop database [数据库名];

6.使用数据库:

use [数据库名];

7.创建表格:

create table employee(id int,name varchar(20),sex char(2),birthday date,job varchar(50),salary double(7,2));

8.查看表的结构:

desc [表名];

9.在表中插入列:

alter table employee add address varchar(100);

10.修改列字符的属性:

ALTER TABLE employee MODIFY job VARCHAR(20);

注:即将原来varchar(50)改为20;

11.删除某列:

ALTER TABLE employee DROP address;

12.更改表名:

RENAME TABLE employee to user;

13.查看表的创建细节:

SHOW CREATE TABLE user;

14.修改字段名:

ALTER TABLE user CHANGE name username VARCHAR(10);

15.删除表:

DROP TABLE user;

DML

数据操纵语言（Data Manipulation Language, DML）是SQL语言中，负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除，是开发以数据为中心的应用程序必定会使用到的指令。DML是对数据库表中的数据增删改的操作.

1.插入数据:

insert into emp(id,name,sex,birthday,salary) values (1,'dupudupu','男','2011-09-08',1000);

也可以这样写,省略前面括号里面的内容

insert into emp values (2,'Paul','男','2018-09-22',800);

2.批量插入:

insert into emp values 
(3,'Lili','女','2017-06-25',3000),
(4,'Hali','男','2014-09-08',1000),
(5,'Soso','女','1997-07-30',10000),
(6,'Mama','女','1789-03-25',8888);

3.修改数据:当我在Mysql Workbench中使用下面的语句时,出现了错误

update emp set name='Dupu' where id = 1;

首先语句是没有错的,经查询

• 错误原因：是在safe mode下,要强制安全点,update只能跟where

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

• 解决方案 : 执行命令—->SET SQL_SAFE_UPDATES = 0;

4.删除数据

delete from emp where id=6;

DCL

DCL（Data Control Language）是数据库控制语言。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL。

1.创建用户

CREATE USER 用户名@地址 IDENTIFIED BY '密码';
CREATE USER user1@localhost IDENTIFIED BY ‘123’; 
CREATE USER user2@’%’ IDENTIFIED BY ‘123’;

2.用户授权

GRANT 权限1, … , 权限n ON 数据库.* TO 用户名@IP
GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;
GRANT ALL ON mydb1.* TO user2@’%’;

3.撤销授权

REVOKE权限1, … , 权限n ON 数据库.* FORM 用户名
REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;

4.查看权限

SHOW GRANTS FOR 用户名
SHOW GRANTS FOR user1@localhost;

5.删除用户

DROP USER 用户名
DROP USER user1@localhost;

6.修改用户密码

Use mysql;
UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’;
FLUSH PRIVILEGES;
UPDATE USER SET PASSWORD=PASSWORD('1234') WHERE User='user2';
FLUSH PRIVILEGES;

DQL

数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端,查询返回的结果集是一张虚拟表。

1.简单查询

select * from 表名;	#查询所有字段
select 列1,列2,... from 表名;	#查询指定字段
select id as 序号, name as 名字, gender as 性别 from students;	#使用 as 给字段起别名
select s.id,s.name,s.gender from students as s;	#可以通过 as 给表起别名
select distinct 列1,... from 表名;   #消除重复行

2.条件查询**

select * from 表名 where 条件;

where后面支持多种运算符，进行条件的处理

• 比较运算符
• 逻辑运算符
• 模糊查询
• 范围查询
• 空判断

3.排序查询**

SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;

4.聚合查询

SELECT SUM(price) FROM product WHERE category_id = 'c001';
SELECT AVG(price) FROM product WHERE category_id = 'c002';
SELECT MAX(price),MIN(price) FROM product;

count：统计指定列不为NULL的记录行数；

sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

5.分组查询

-- 案例1,：查询每个工种的最高工资  【每个】
SELECT MAX(salary),job_id FROM employees GROUP BY job_id;
 
-- 案例2：查询每个位置上的部门个数   【每个】
SELECT COUNT(*),location_id FROM departments GROUP BY location_id;
 
-- 案例3：查询邮箱中包含a字符的，每个部门的平均工资   【每个】
SELECT AVG(salary),department_id FROM employees WHERE email LIKE '%a%'  GROUP BY department_id;
 
-- 案例4：查询有奖金的每个领导手下员工的最高工资    【添加分组前的筛选】
SELECT MAX(salary), manager_id FROM employees WHERE commission_pct IS NOT NULL  GROUP BY manager_id;
 
-- 案例5：查询哪个部门的员工数>2   【添加分组后的筛选】
-- ①查询每个部门的员工个数
SELECT COUNT(*),department_id FROM employees GROUP BY department_id
-- ②根据①的结果进行筛选，查询哪个部门的员工个数>2
SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;

6.外键约束

1、级联(cascade)方式

2、置空(set null)方式

3、禁止(no action / restrict)方式

7.多表查询

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

select * from emp,department;

内连接：只连接匹配的行

select emp.id,emp.name,emp.age,emp.sex,department.name from emp inner join department on emp.dep_id=department.id;

外链接之左连接：优先显示左表全部记录

select emp.id,emp.name,department.name as depart_name from emp left join department on emp.dep_id=department.id;

外链接之右链接：优先显示右表全部记录

select emp.id,emp.name,department.name as depart_name from emp right join department on emp.dep_id=department.id;

全外连接：显示左右两个表全部记录

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
;

案例来自博客园

8.子查询

1、带IN关键字的子查询

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

#查看技术部员工姓名
select name from employee
    where dep_id in 
        (select id from department where name='技术');

#查看不足1人的部门名
select name from department
    where id in 
        (select dep_id from employee group by dep_id having count(id) <=1);

　2、带比较运算符的子查询

#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
select name,age from emp where age > (select avg(age) from emp);

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

3、带EXISTS关键字的子查询

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。

而是返回一个真假值。True或False

当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询