MySQL核心语句--DDL,DML,DQL,DCL,TCL详解

最新推荐文章于 2024-08-19 22:23:29 发布

拨云见日_c

最新推荐文章于 2024-08-19 22:23:29 发布

阅读量520

点赞数 14

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_57547315/article/details/135987481

版权

MySQL

SQL语句分类

structured  query language  结构化查出语言。
使用特定的SQL操作DBMS里面的数据库以及表以及表数据。
SQL的分类:
  1. DDL 数据定义语言  
      创建库/表   CREATE
      删除库/表   DROP
      修改表结构      ALTER
      清空表数据  TRUNCATE
  2. DML 数据操作语言(所有更新操作)
       新增  INSERT
       修改  UPDATE
       删除  DELETE
  3. DQL 数据查询语言
        查询 SELECT (最难的)
  4. DCL 数据控制语言
        系统用户
        用户授权 GRANT 
  5. TCL 事务控制语言(存储引擎必须是InnoDB)
        begin commit rollback

1.DDL

1.1数据库的创建和销毁


create database [if not exists] 数据库名 [default charset 字符集]

if not exists：表示如果不存在才会创建
default charset 字符集：设置数据库字符集

create DATABASE 2302demo2 -- 如果数据库已经存在，会报错
create DATABASE if not exists 2302demo2 -- 如果数据库已经存在，不会报错
create DATABASE if not exists 2302demo2 default charset utf8mb4 -- 直接设置字符集

drop database [if exists] 数据库

if exists：如果存在才会销毁

drop database 2302demo2 -- 如果不存在，报错
drop database if exists 2302demo2 -- 如果不存在，不做操作，不会报错

1.2表的创建、删除、修改、清空

-- 1.1 创建表
-- 表名  t_xxx  tb_xxx  student_info  product  tb_sys_user
CREATE TABLE [IF NOT EXISTS] 表名(
  列名1  数据类型  [约束],
  列名2  数据类型  [约束],
  .....
  列名n  数据类型  [约束]  
);

-- 需求: 存储用户信息
-- 严格规范: 一张表中  必不可少的3个字段   id  create_time update_time
CREATE TABLE sys_user(
   id int unsigned zerofill,
   true_name varchar(255),
   nick_name varchar(30),
   age tinyint(2) unsigned,
   gender tinyint(1),
   phone char(11),
   user_image  varchar(255),
   balance decimal(10,4),
   birthday date, 
   create_time datetime,
   update_time datetime   
);

-- 2. 删除表
mysql> DROP TABLE sys_user;

-- 3.修改表结构 ALTER
-- 修改表名 重命名   rename
mysql> ALTER TABLE sys_user RENAME tb_sys_user;
-- 新增新的列
mysql> ALTER TABLE sys_user ADD address varchar(100);
-- 删除指定的列
mysql> ALTER TABLE sys_user DROP address;
-- 修改列名
mysql> ALTER TABLE sys_user CHANGE age user_age tinyint unsigned;
-- 修改列的数据类型
mysql> ALTER TABLE sys_user CHANGE user_age user_age int  unsigned;
mysql> ALTER TABLE sys_user MODIFY user_age tinyint(3);

-- 4.清空表数据
mysql> TRUNCATE TABLE sys_user;
假设表10条记录: 对于TRUNCATE而言，先drop table ，然后再create table，性能高。
弊端: 属于DDL语句，数据不能回滚。与事务无关。

2.数据类型

2.1整数类型

-- m限定列宽  tinyint(2)  100 -101
tinyint(m)--->byte  -128-127
tinyint(m) unsigned 0-255
tinyint(1)-----> 0 false  1 true  boolean

  年龄  性别 
  
int(m)---->  int(3)  -1000000   等价于int  Integer
bigint(m)----> 等于  long
编号(id)，  时间毫秒数(bigint)

int(5) zerofill  1000383  00123

2.2浮点类型

m:限制小数总位数(.之前与之后的位数和)
n:限制.之后的位数
float(m,n)   java语言float
double(m,n)  java语言double
decimal(m,n) 维护余额。java语言BigDecimal

decimal(8,3) 99999.9994

2.3字符串类型

m: 限定存储的字符个数。  "String"
char(m)----> 定长
varchar(m)--->可变长

char(3)  'ab_'  查询的时候 trim (去除左右2端的空格) 再展示
"手机号码" 
varchar(3) 'ab' 存储2个字符   使用最多 
"姓名"

text
longtext

2.4日期类型

date: 维护的是年月日  2020-01-01   对应的java.util.Date  或者的 java.time.LocalDate
datetime: 维护的是年月日 时分秒  2020-01-01 13:00:00  
          对应的java.util.Date 或者的 java.time.LocalDateTime
          
          使用最多
timestamp: 维护的是年月日 时分秒   2038  不用。

year(2/4) 2024

3.DML

3.1 INSERT

1条或者多条记录受影响。一般一次新增1行记录。

-- 新增的语法
-- 1. 新增: 对所有的列赋值
-- INSERT INTO 表名 VALUES (数据1,...,数据n);

-- INSERT INTO sys_user VALUES (1001,'张三','zhangsan',20,1,'110','/user/a.jpg',1000,'2000-01-01','2024-01-17 14:00:00',NULL);
-- INSERT INTO sys_user VALUES (1002,'张三','zhangsan',20,1,'110','/user/a.jpg',1000,'2000-01-01','2024-01-17 14:00:00',NULL);
-- INSERT INTO sys_user VALUES (1003,'张三','zhangsan',20,1,'110','/user/a.jpg',1000,'2000-01-01','2024-01-17 14:00:00',NULL);
-- INSERT INTO sys_user VALUES (1004,'张三','zhangsan',20,1,'110','/user/a.jpg',1000,'2000-01-01','2024-01-17 14:00:00',NULL);
-- INSERT INTO sys_user VALUES (1005,'张三','zhangsan',20,1,'110','/user/a.jpg',1000,'2000-01-01','2024-01-17 14:00:00',NULL);
-- INSERT INTO sys_user VALUES (1006,'张三','zhangsan',20,1,'110','/user/a.jpg',1000,'2000-01-01','2024-01-17 14:00:00',NULL);

-- 获得当前此刻时间
-- SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP();

-- INSERT INTO sys_user VALUES (1009,'王文英','zhangsan',10,0,'110','/user/a.jpg',1000,'2000-01-01',NOW(),NULL); 
-- ALTER TABLE sys_user MODIFY id int UNSIGNED;

-- 2. 新增--指定具体的列进行新增(最常用的)
-- INSERT INTO 表名 (列名1,...,列名n) VALUES (数据1,...,数据n);
INSERT INTO sys_user (id,true_name,user_age,phone,create_time) VALUES (1,'aaa',20,'21727',NOW());
INSERT INTO sys_user (id,true_name,user_age,phone,create_time) VALUES (2,'bbb',20,'21727',SYSDATE());
INSERT INTO sys_user (id,true_name,user_age,phone,create_time) VALUES (3,'ccc',20,'21727',CURRENT_TIMESTAMP());

-- 3. 批处理---> 一次新增多条   一般操作中间表(维护多表关系的)。
-- INSERT INTO 表名 VALUES (数据1,...,数据n),(数据1,...,数据n),(数据1,...,数据n)....
-- INSERT INTO sys_user VALUES (10,'张三1','zhangsan',20,1,'110','/user/a.jpg',1000,'2000-01-01','2024-01-17 14:00:00',NULL),(11,'张三2','zhangsan',20,1,'110','/user/a.jpg',1000,'2000-01-01','2024-01-17 14:00:00',NULL);
-- INSERT INTO 表名 (列名1,...,列名n) VALUES (数据1,...,数据n),(数据1,...,数据n),....;
INSERT INTO sys_user (id,true_name,user_age,phone,create_time) VALUES 
(131,'aaa1',20,'21727',NOW()),(132,'aaa2',20,'21727',NOW()),(133,'aaa3',20,'21727',NOW());

3.2DELETE

先查询

删除所有批量删除删除1个

-- DELETE
-- 语法:
-- DELETE FROM 表名;  -- 清空所有
-- DELETE FROM 表名 [WHERE 条件(与列的数据) and or ]; -- 删除满足条件的记录
-- 需求1: 删除id=1001的用户
-- DELETE FROM sys_user WHERE id=1001;
-- 需求2: 删除id=1002 1003 1004的用户
-- DELETE FROM sys_user WHERE id=1002 OR id=1003 OR id=1004;
-- DELETE FROM sys_user WHERE id IN (1005,1006,1007);
-- 需求2: nick_name=zhangsan  id=1
-- DELETE FROM sys_user WHERE nick_name='zhangsan' OR id=1;
-- DELETE FROM sys_user; -- 遍历式删除  数据量大的情况下  性能低  相比较truncate
-- TRUNCATE TABLE sys_user; -- drop table  create table

3.3 UPDATE

先查询在查询的状态下执行删除/修改。记录肯定是存在的

一次修改一行。

-- UPDATE（对指定的列赋予新的数据 赋值 set）
-- UPDATE 表名 SET 列名1=新数据1,...,列名n=新数据n;  修改表的所有的记录
-- UPDATE 表名 SET 列名1=新数据1,...,列名n=新数据n WHERE 条件; 
-- UPDATE sys_user SET gender=0,balance=2000,true_name='张三丰';
-- 根据id修改
UPDATE sys_user SET true_name='张三',user_age=30,balance=3000,update_time=NOW() WHERE id=131;

-- 使用DDL: 创建员工表 部门表 学生表 成绩表  商品表  商品类型表
-- 使用DML语句完成数据操作

CREATE TABLE employee(
 id BIGINT(10) UNSIGNED,
 `name` VARCHAR(20),
 age TINYINT(2) UNSIGNED,
 dept VARCHAR(10),
 phone CHAR(11),
 job VARCHAR(20),
 leader VARCHAR(20),
 create_time DATETIME,
 update_time DATETIME
);

INSERT INTO employee (id,name,dept,job,leader,create_time) VALUES (1,'张三','研发部','普通员工','king',NOW()); 
INSERT INTO employee (id,name,dept,job,leader,create_time) VALUES (2,'李四','销售部','普通员工','boss1',NOW()); 
INSERT INTO employee (id,name,dept,job,leader,create_time) VALUES (3,'王五','后勤部','普通员工','boss2',NOW());

4.DQL

实现各种查询

4.1语法

SELECT [DISTINCT]
 列名1,....,列名n
FROM
 表1,表2...表n
[WHERE 条件]   -- 过滤记录/数据  OR AND 
[GROUP BY 列1,...,列n]  -- 分组查询 一般都是根据1个列
[HAVING 条件]  -- 过滤分组之后的记录/数据
[ORDER BY 列1,列2 desc,...]  -- 排序查询 默认升序 ASC 降序 DESC
[LIMIT ?/?,?]   -- 限定结果集   分页查询

4.2基础查询

SELECT * FROM stu;
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;

-- * 代表表里面所有的列  通配符
-- 在开发中  不太建议写* 
-- 站在sql优化角度  表列的数量>10

-- 指定列查询(推荐)
SELECT sid,sname,age,gender FROM  stu; 
-- 只要是查询语句  都会查到一张新的表(查询的数据在一张新的表里面)
-- 虚拟表/临时表(内存)  结果集

4.3WHERE

过滤记录。根据条件(列参与)

条件查询就是在查询时给出WHERE⼦句，在WHERE⼦句中可以使⽤如下运算符及关键字：
=、!=、<>不等于、<、<=、>、>=；
BETWEEN…AND；是否满⾜⼀个区间范围 >= <=
IN(set)；条件的集合
IS NULL；
AND； 连接多个条件的查询
OR；or 满⾜其中⼀个条件就可以
NOT；

-- WHERE
-- 1. 查询学生性别为男，并且年龄50的记录
SELECT * FROM stu WHERE gender='male' OR age=35;
SELECT * FROM stu WHERE gender='male' AND age=35;

-- 2、查询学号为S_1001，S_1002，S_1003的记录
SELECT * FROM stu WHERE sid='s_1001' OR sid='s_1002' OR sid='s_1003';
SELECT * FROM stu WHERE sid IN ('s_1001','s_1002','s_1003');

-- 3、查询学号不是S_1001，S_1002，S_1003的记录
SELECT * FROM stu WHERE sid!='s_1001' AND sid!='s_1002' AND sid!='s_1003';
SELECT * FROM stu WHERE sid<>'s_1001' AND sid<>'s_1002' AND sid<>'s_1003';
SELECT * FROM stu WHERE sid NOT IN ('s_1001','s_1002','s_1003');

-- 4、查询年龄为null的记录
SELECT * FROM stu WHERE age IS NULL;
SELECT * FROM stu WHERE age IS NOT NULL;

-- 5、查询年龄在20到40之间的学生记录
SELECT * FROM stu WHERE (age>=20 AND age<=40);
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;

-- 6、查询性别非男的学生记录
SELECT * FROM stu WHERE (gender!='male') OR (gender IS NULL);

4.4分组函数

也可以称为"聚合函数"。不使用group by, 运算结果只有1行

-- 根据某个列实现纵向运算  结果只有1行
COUNT(字段/列)：统计指定列不为NULL的记录⾏数；⼀般使⽤count（*）统计⾏数
MAX(字段/列)：计算指定列的最⼤值，如果指定列是字符串类型，那么使⽤字符串排序运算；
MIN(字段/列)：计算指定列的最⼩值，如果指定列是字符串类型，那么使⽤字符串排序运算；
SUM(字段/列)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG(字段/列)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
SUM、AVG⼀般处理数值型
MAX、MIN、COUNT可以处理任意数据类型
分组函数都忽略了NULL值，可以和DISTINCT搭配使⽤


-- 聚合函数
-- 将全表数据看成一组统计
-- 1.统计stu表里面学生的数量
SELECT COUNT(sid),COUNT(*),COUNT(gender) FROM stu;

-- 2.统计stu表里面年龄的最大值  年龄最小值  平均年龄
SELECT MAX(age),MIN(age),AVG(age),SUM(age)/COUNT(age) FROM stu;

-- 3.统计emp表里面有奖金的员工数量
SELECT COUNT(comm) FROM emp;

-- 4.统计emp表里面30号部门的所有员工的薪资总和
SELECT SUM(sal) FROM emp WHERE deptno=30;

-- 5.统计emp表里面10号部门最高薪资以及最低薪资
SELECT MAX(sal),MIN(sal) FROM emp WHERE deptno=10;c

4.5GROUP BY

执行顺序：from–>where–>group by–>select

分组查询：将全表的数据分成指定的n组进行查询

-- 1、查询每个部门的部门编号和每个部门的员工工资和
-- 2、查询每个部门的部门编号以及每个部门的人数
-- GROUP BY 隐含着去重
-- 一般分组查询  我们都会展示GROUP BY后面的列的数据
SELECT deptno,SUM(sal),COUNT(*) FROM emp GROUP BY deptno;

-- 3、查询每个部门的部门编号以及每个部门员薪资资大于1500的人数：
SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;

-- 4、查询每个性别的学生的数量，根据gender统计学生的数量
SELECT gender,count(*) FROM stu GROUP BY gender;

4.6字段控制查询

-- 发工资。计算每个员工的实发薪资
SELECT * FROM emp;
-- 多个列的数据执行运算  有1个列的为NULL 最后结果就是NULL
-- 需要对特定的列执行特殊的处理
-- 1. IFNULL(列名,value);  判断指定的列 这个列的数据为null 使用value参与运算  否则还是自身的数据
SELECT empno,ename,sal,comm, sal+IFNULL(comm,0) AS total_sal from emp;

-- 2. AS  对复杂的列,表都可以进行别名(中文,英文)查询  AS可以省略  不建议
-- 查询sys_user表的数据
SELECT t1.balance,t1.id FROM mydb.sys_user AS t1;

4.7distinct

SELECT DISTINCT * FROM emp;

-- 去重
-- SELECT job FROM emp GROUP BY job;
-- SELECT job FROM emp 
-- UNION 
-- SELECT job FROM emp;

SELECT DISTINCT job FROM emp;
SELECT DISTINCT gender FROM stu;

-- DISTINCT 针对的还是行记录
SELECT DISTINCT empno,job FROM emp;

4.8LIKE模糊查询/模糊搜索

-- LIKE
-- 数字  字母  汉字 特殊符号
-- _ 一个_就匹配任意一个字符。
-- % 匹配任意数量的字符
-- 1、查询姓名由5个字符构成的学生记录
SELECT * FROM stu WHERE sname LIKE '_____';
-- 2、查询姓名以“z”开头的学生记录
SELECT * FROM stu WHERE sname LIKE 'z%';

-- 3、查询姓名中第2个字母为“i”的学生记录
SELECT * FROM stu WHERE sname LIKE '_i%';

-- 4、查询姓名中包含“a”字符的学生记录
SELECT * FROM stu WHERE sname LIKE '%a%';

4.9 HAVING

-- having的作用与where是一致的
-- 查询sal=1500的员工信息
-- SELECT * FROM emp WHERE sal>1500 AND ename LIKE '%a%';
-- SELECT * FROM emp HAVING sal>1500 AND ename LIKE '%a%';

-- 相同点: 都是过滤数据
-- WHERE 不能与组函数一起使用 在GROUP BY之前
-- HAVING 可以与组函数一起使用 在GROUP BY之后
-- HAVING 一般是用于对分组之后的数据进行过滤

-- GROUP BY
-- 查询每个部门的薪资总和>9000的部门编号与薪资总和
SELECT deptno,SUM(sal) AS total FROM emp  GROUP BY deptno HAVING total>9000;

4.10ORDER BY

排序查询。

查询的记录可以根据指定的列进行升序或者降序排列。默认升序 ASC 降序DESC

-- 1、查询所有学生记录，按年龄升序/降序排序
SELECT * FROM stu ORDER BY age ;
SELECT * FROM stu ORDER BY age ASC;
SELECT * FROM stu ORDER BY age DESC;

-- 2. 查询所有员工信息，按照入职时间升序/降序排序
SELECT * FROM emp ORDER BY hiredate ASC;
SELECT * FROM emp ORDER BY hiredate DESC;

-- 3. 查询每个部门的薪资总额，薪资总额按照升序/降序排序
SELECT deptno,SUM(sal) AS total FROM emp GROUP BY deptno ORDER BY total DESC;

-- 4. 查询每个部门薪金总额大于2000的人数，人数按照升序/降序排列
SELECT deptno,COUNT(empno) FROM emp WHERE sal+IFNULL(comm,0)>2000 GROUP BY deptno 
ORDER BY COUNT(empno) ASC, deptno ASC;

-- 5、查询所有雇员，按月薪降序排序，如果月薪相同时，按编号降序排序
SELECT * from emp ORDER BY sal DESC,empno DESC;

4.11LIMIT分页查询

限定结果集查询。分页查询。

开发中，一张表中数据量是庞大的，几乎不会查询所有的记录。都是分页展示信息。

SELECT * FROM 表 LIMIT  ?;
SELECT * FROM 表 LIMIT pageSize;
SELECT * FROM 表 LIMIT 5;
-- 默认查询第一页数据。从第一条记录开始，查询pageSize,查询5条。

SELECT * FROM 表 LIMIT ?,?;
SELECT * FROM 表 LIMIT rowIndex,pageSize;
-- rowIndex 指定查询第rowIndex+1行记录开始
-- pageSize 查询的数据量
SELECT * FROM 表 LIMIT 0,5;
-- 分页查询emp表的信息
-- 总记录数:  totalCount=14
SELECT COUNT(*) FROM emp;
-- pageSize=5
-- 总页数: totalPage = totalCount/pageSize
-- totalPage = totalCount%pageSize==0?totalPage:totalPage+1


-- 第一页数据  page=1
SELECT * FROM emp LIMIT 0,5;
-- 第二页数据  page=2
SELECT * FROM emp LIMIT 5,5;
-- 第三页数据  page=3
SELECT * FROM emp LIMIT 10,5;


SELECT * FROM 表名 LIMIT (page-1)*pageSize,pageSize;

集合查询

前提: 用户的信息基于多张表进行存储。

分表存储特定的数据。

-- 对于用户信息存储: 分成3张表存储
-- user1  user2   user3
-- 1条sql  查询所有用户信息
-- 需求: 将多个结果集合并成一个结果集
-- 集合运算 :  UNION  /  UNION ALL
-- SELECT * FROM 表1 UNION SELECT * FROM 表2......
-- 规则
-- 1.1 查询的列的数量统一 查询的不同的一个列 没有价值（相同数量 相同类型）
-- 
SELECT id,name,age FROM user1
UNION 
SELECT id,name,age FROM user2
UNION 
SELECT id,name,age FROM user3;

-- SELECT * FROM user1,user2,user3;

-- 总结:
-- UNION ALL(不去重)  union(针对于行记录实现去重)
-- GROUP BY 也可以去重  列

-- SELECT name FROM user1
-- UNION 
-- SELECT ename FROM emp;

4.12子查询

称为"嵌套查询"。条件未知的时候。必须要带 ()

-- 子查询分类
-- 1.1 标量子查询(子查询结果为1行1列)
-- 使用: = >= != <=
-- 1.查询'销售部'的所有员工信息
-- SELECT * FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='sales');
-- 2.查询smith入职之后的员工信息
-- SELECT * FROM emp WHERE hiredate>(SELECT hiredate FROM emp WHERE ename='smith');
-- 3.查询员工的薪资= 20号部门的平均的薪资

-- 1.2 列子查询
-- 子查询返回的结果是1列(可以是多行)。
-- IN , NOT IN, ANY, SOME, ALL
-- ANY/SOME 子查询的结果集 满足一个即可
-- ALL 子查询的结果集 必须全部都要满足
-- 1. 查询销售部，研发部所有员工信息
-- SELECT * FROM emp WHERE deptno NOT IN  (SELECT deptno FROM dept WHERE dname='sales' OR dname='RESEARCH');

-- 2. 查询比ACCOUNTING所有人工资都高的员工信息
-- SELECT * FROM emp WHERE sal> (SELECT max(sal) FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='SALES'));
-- SELECT * FROM emp WHERE sal >ALL (SELECT sal FROM emp  WHERE deptno=(SELECT deptno FROM dept WHERE dname='SALES'));


-- 3.查询比销售部任意一人工资高的员工信息
SELECT * FROM emp WHERE sal> ANY (SELECT sal FROM emp  WHERE deptno=(SELECT deptno FROM dept WHERE dname='SALES')) 
AND deptno!=(SELECT deptno FROM dept WHERE dname='SALES');



-- 1.3 行子查询(子查询的结果集是一行多列)
-- =  <>  IN  NOT IN
-- 查询与smith薪资与直属上级相同的员工信息。
SELECT sal,mgr FROM emp WHERE ename='smith';
-- SELECT * FROM emp WHERE sal=(SELECT sal FROM emp WHERE ename='smith') AND mgr=(SELECT mgr FROM emp WHERE ename='smith');
SELECT * FROM emp WHERE (sal,mgr)=(SELECT sal,mgr FROM emp WHERE ename='smith') AND ename!='smith';


-- 1.4 表子查询(多行多列)
-- 将子查询查询的结果看成临时表进行操作

-- 查询与smith，ford职位，薪资相同的员工信息。
SELECT sal,job FROM emp WHERE ename IN ('smith','ford');
SELECT * FROM emp WHERE (sal,job) IN (SELECT sal,job FROM emp WHERE ename IN ('smith','ford')) AND ename!='smith' AND ename!='FORd' ;

-- 2.查询入职日期是‘1981-12-03’之后的员工信息，及其部门信息

SELECT * FROM emp WHERE hiredate>'1981-12-03';

SELECT
 t.*,d.dname
FROM
dept AS d,
(SELECT empno,ename,deptno FROM emp WHERE hiredate>'1981-12-03') AS t
WHERE 
d.deptno=t.deptno;

5.DCL

> data control language   数据控制语言

> 操作系统用户，权限管理相关的内容。

```sql
-- 用户管理
-- 所有用户相关的信息都存储在 mysql.user表中。  
-- 1.用户管理

-- 所有用户相关的信息都存储在 mysql.user表中。  
-- 目前都是使用的超级用户 root 实现数据库/表操作。
-- DBA、运维一般都会创建指定的某些用户  授予不同权限。
-- 创建用户，程序员很少干这个事情

-- 1.1 创建用户
CREATE USER 'lisa'@'localhost' IDENTIFIED BY '1234'; 
CREATE USER 'admin'@'%' IDENTIFIED BY '1234'; 

-- 1.2 删除用户
DROP USER 'lisa'@'localhost';

-- 1.3 修改用户密码
ALTER USER 'lisa'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

-- 1.4 查询用户
SELECT * FROM mysql.`user`;

-- 权限管理
-- 2.权限控制
-- 2.1 查看用户权限
SHOW GRANTS FOR 'lisa'@'localhost';
SHOW GRANTS FOR 'admin'@'%';
SHOW GRANTS FOR 'root'@'%';

-- 2.2 授权
GRANT CREATE,SELECT,INSERT ON dql.* TO 'lisa'@'localhost';
GRANT ALL ON *.* TO 'admin'@'%';

-- 2.3 撤销权限
REVOKE INSERT ON dql.* FROM 'lisa'@'localhost';

REVOKE CREATE,SELECT,INSERT ON dql.stu FROM 'lisa'@'localhost';

6.数据库事务

在功能开发中，一般可能需要多条sql语句共同执行。

-- 实现新增用户(选择用户对应角色)
select * from role;
select * from dept;
select * from job;
INSERT INTO sys_user 
 (name,password,image,phone,rid,dept_id,job_id) 
 values 
 ();
 
-- 新增新的角色（分配菜单权限）
select * from menu;
INSERT INTO role (role_name,role_desc) values ('','');
INSERT INTO ROLE_MENU (rid,mid) values (),(),(),().....

-- 转账
-- 张三 给 李四 转账  1000
SELECT balance from user where name='张三';
update user set balance=balance-1000 where name='张三'
update user set balance=balance+1000 where name='李四'

6.1事务概念

-- 转账
-- jack 给 pony 转账1000
SELECT balance FROM user WHERE name='jackMa';
UPDATE `user` SET balance=balance-1000 WHERE name='jackMa';
断电了,程序报错了.....
UPDATE `user` SET balance=balance+1000 WHERE name='ponyMa';

-- 转账应该是失败的。 jackMa余额还是2000  ponyMa余额还是1000
-- 实际:
-- 转账里面涉及到多条sql语句，只有他们全部执行成功了，转账才算成功
-- 有一条sql语句执行失败，这个功能就是失败的。
-- 失败，数据不应该更新，还是处于执行功能最初的状态。

-- 与数据库事务有关。

数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列(一系列sql语句)

这些操作要么全部执行,要么全部不执行

是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

本质上：一系列sql在一个事务内进行执行。

6.2事务特性

ACID
A： 
 atomic 原子性
 一系列sql要么全部执行成功，要么全部失败。不能出现部分sql成功，或者部分sql失败的问题。
 
C:
 consistency 一致性
 事务开始之前 与事务结束(提交)之后  数据必须是一致的。
 转账:  
   马云  2000    1000
   马化腾 1000   2000
I：
 isolation 隔离性
 多个客户端并发多个事务， 事务之间是相互隔离的。
 转账：马云可以给 马化腾转账  他人也可以对马化腾转账
 并发: 
    线程(数据)安全的问题。
    DBMS使用的隔离级别不一样的话，造成问题不一的。
    "脏读,不可重复读,幻读"
D:
 durability 持久性
 事务提交，数据就会从内存转储到物理磁盘中。数据可以永久化存储。

7.TCL

transaction control language

-- 解决:  应该把3条sql语句放在一个事务内进行执行。
-- 根本原因: mysql服务器自动提交事务的(事务是自动提交的)
-- 查询mysql事务是否开启
SHOW VARIABLES LIKE '%autocommit%';
-- 关闭mysql事务的自动提交
SET autocommit=off;
SET autocommit=0;

-- 关闭之后  再次执行转账功能里面涉及的3条sql
SELECT balance FROM user WHERE name='jackMa';
UPDATE `user` SET balance=balance-1000 WHERE name='jackMa';
断电了,程序报错了.....
UPDATE `user` SET balance=balance+1000 WHERE name='ponyMa';

-- 这个地方 sql报错了  事务不应该提交
-- 事务就应该回滚
-- 回滚到开启事务最初
ROLLBACK; -- 释放内存

-- 在多条sql没有问题前提下
SELECT balance FROM user WHERE name='jackMa';
UPDATE `user` SET balance=balance-1000 WHERE name='jackMa';
UPDATE `user` SET balance=balance+1000 WHERE name='ponyMa';

-- 多条sql执行ok  功能成功
-- 手动提交事务
COMMIT;

SHOW VARIABLES LIKE '%autocommit%';
-- mysql事务默认开启的 一般我们不修改
-- SET autocommit=1|true|ON;
-- SET autocommit=0|false|off;

-- 如果在事务自动提交的状态下  执行转账功能  依然存在问题的
-- 需要手动控制整体事务
-- 1. 手动开启事务
START TRANSACTION;
-- BEGIN;
 SELECT * FROM emp;
 DELETE FROM emp WHERE empno IS NULL;
 INSERT INTO emp (empno,ename) VALUES (1,'张三');
 SELECT * FROM emp;
-- 2. 手动提交事务
COMMIT;

-- 3. sql里面存在任何报错问题  都应该回滚事务
ROLLBACK;