mysql dql dml_mysql中DML DDL DCL DQL

DML 英文全称(Data Manipulation Language)数据操作语言数据库记录(数据)增、删、改，对表记录进行更新(增、删、改)

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

DCL 英文全称(Data Control Language)数据控制语言数据库用户用来定义访问的权限和安全级别，对用户的创建，及授权

DDL

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

1.创建数据库:1create database [数据库名] character set [字符编码];

2.查看数据库:1show databases;

3.查看数据库创建明细:1show create database [数据库名字];

4.修改数据库编码:1alter database [数据库名] character set [字符编码];

5.删除数据库:1drop database [数据库名];

6.使用数据库:1use [数据库名];

7.创建表格:1create table employee(id int,name varchar(20),sex char(2),birthday date,job varchar(50),salary double(7,2));

8.查看表的结构:1desc [表名];

9.在表中插入列:1alter table employee add address varchar(100);

10.修改列字符的属性:1ALTER TABLE employee MODIFY job VARCHAR(20);

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

11.删除某列:1ALTER TABLE employee DROP address;

12.更改表名:1RENAME TABLE employee to user;

13.查看表的创建细节:1SHOW CREATE TABLE user;

14.修改字段名:1ALTER TABLE user CHANGE name username VARCHAR(10);

15.删除表:1DROP TABLE user;

DML

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

1.插入数据:1insert into emp(id,name,sex,birthday,salary) values (1,'dupudupu','男','2011-09-08',1000);

也可以这样写,省略前面括号里面的内容1insert into emp values (2,'Paul','男','2018-09-22',800);

2.批量插入:1

2

3

4

5insert 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中使用下面的语句时,出现了错误1update emp set name='Dupu' where id = 1;

首先语句是没有错的,经查询错误原因：是在safe mode下,要强制安全点,update只能跟where1Error 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.删除数据1delete from emp where id=6;

DCL

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

1.创建用户1

2

3CREATE USER [email protected] IDENTIFIED BY '密码';

CREATE USER [email protected] IDENTIFIED BY ‘123’;

CREATE USER [email protected]%’ IDENTIFIED BY ‘123’;

2.用户授权1

2

3GRANT 权限1, … , 权限n ON 数据库.* TO [email protected]

GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO [email protected];

GRANT ALL ON mydb1.* TO [email protected]%’;

3.撤销授权1

2REVOKE权限1, … , 权限n ON 数据库.* FORM 用户名

REVOKE CREATE,ALTER,DROP ON mydb1.* FROM [email protected];

4.查看权限1

2SHOW GRANTS FOR 用户名

5.删除用户1

2DROP USER 用户名

6.修改用户密码1

2

3

4

5Use mysql;

UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’;

FLUSH PRIVILEGES;

UPDATE USER SET PASSWORD=PASSWORD('1234') WHERE User='user2';

FLUSH PRIVILEGES;

DQL

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

1.简单查询1

2

3

4

5select * 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.条件查询1select * from 表名 where 条件;

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

逻辑运算符

模糊查询

范围查询

空判断

3.排序查询**1SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;

4.聚合查询1

2

3SELECT 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

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17-- 案例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.多表查询

交叉连接：不适用任何匹配条件。生成笛卡尔积1select * from emp,department;

内连接：只连接匹配的行1select emp.id,emp.name,emp.age,emp.sex,department.name from emp inner join department on emp.dep_id=department.id;

外链接之左连接：优先显示左表全部记录1select emp.id,emp.name,department.name as depart_name from emp left join department on emp.dep_id=department.id;

外链接之右链接：优先显示右表全部记录1select emp.id,emp.name,department.name as depart_name from emp right join department on emp.dep_id=department.id;

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

2

3

4select * 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关键字的子查询1

2

3

4

5

6

7

8

9

10

11

12

13

14#查询平均年龄在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、带比较运算符的子查询1

2

3

4

5

6

7

8

9

10#比较运算符：=、!=、>、>=、

#查询大于所有人平均年龄的员工名与年龄

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关键字的子查询1

2

3

4

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

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

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