标题：DDL-DCL-DML语句
实践：
1.技能框架-思维导图
2.清单-规划好时间
3.操作-习题的方式
4.文档-历史，部署，功能介绍，基本管理，原理
5.原理叙述，模拟讲课场景，录制视频
6.整理数据以及小结
要求：
- 练习题，要不看答案敲出来，三遍以上
- 文档原理，要看着图说出来
- 对历史介绍要能说出来，读三遍，回想一边
目录
@[toc]
1.技能框架
2.清单-规划
5.文档
前提：
-
DDL：数据定义语言
库的定义：库名+库的属性
表的定义：表名+表属性+列名+列属性
-
DCL：数据控制语言
GRANT 权限定义
REVOKE 回收权限
-
DML：数据操作语言
库的增删改查
表的增删改查
-
DQL：数据的查询语言
表的查询
标题：-----DDL语句
DDL语句大纲
![]()
在这里插入图片描述
【1】库的定义
定义库的时候，需要定义库的字符集，以及校对规则，校对规则就是字符排序规则，是否遵循大小写字母的规则。
1.查看字符集
mysql> SHOW CHARSET;
utf8mb4(4个字节)
utf8(3字节)
2.校对规则(排序规则):
mysql> SHOW COLLATION;
utf8mb4_general_ci 不敏感(默认)
utf8mb4_bin 敏感
1.1 规范
(1) 库名是小写
(2) 不能是数字开头
(3) 限制在16个字符以内
(5) 库名和业务有关
(6) 必须要加字符集
(7) 不能用预留字符
(8) 校对规则可选，根据业务选择，是否需要支持大小写
1.1 库的操作
-
建库
1.语句格式 CREATE DATABASE 库名 charset 字符集 collate 校对规则； 2.规范建库，校对规则可选 CREATE DATABASE guifan CHARSET utf8mb4 ; -
删库(不代表生产操作)
1.命令格式 DROP DATABASE 库名； 2.不代表生产操作的语句 DROP DATABASE guifan; -
修改库
1.命令格式 ALTER DATABASE 库名 CHARSET 字符集 COLLATE 校对规则； 2.语句操作 ALTER DATABASE linux CHARSET utf8mb4; 注意: 目标字符集是原字符集的严格超集，以及注意修改库属性的时候，回全部回收属性，需要重新指定库的 所有属性！！！ -
查看库
1.命令格式 show databases 库名; ### 查看有什么库 show create database 库名； ### 查看建库的属性 2.语句操作 show databases linux ; show create database linux; -
总结
1.建库
2.删库（不代表生产操作）
3.修改库的属性
4.查询库以及库的属性
【2】表的定义
创建表的时候，要定义表的属性，也就是这个表用的是那个存储引擎和字符集，还有就是要对建立表进行说明，这是个什么表，方便业务调查。
表的属性：
存储引擎 【ENGINE=INNODB】
字符集及校对规则 【CHARSET=utf8mb4/COLLATE=utf8mb4_bin】
表的注释 【COMMENT '表的说明】
1.查看存储引擎种类
2.查看表的属性
1.语句格式
show create tables 表名；
2.查看表的属性
show create table t1 ;
--- 建表规范总结
(1) 表名要和业务有关
(2) 表名不能有大写
(3) 表名不能有数字开头
(4) 表必须设置存储引擎
(5) 表必须设置数据类型
(6) 选择合适并简短的数据类型
(7) 每个表要有主键,一般可以无关列
(8) 每个列设置非空约束,设置默认值
(9) 每个列和表要有注释
(10) 不使用预留字符
2.1 列的定义
建立表的时候，一般还会定义列，需要对列进行约束，所有要定义一些列的属性，而由于建表的时候，会随之表的结构给定义，也就是列定义属性。
列的属性种类：
(1) 数字类型
1.tinyint ： -128~127
2.int ：-2^31~2^31-1
3.说明：手机号是无法存储到int的。一般是使用char类型来存储收集号
（2） 字符类型
1.char(11) ：
定长 的字符串类型,在存储字符串时，最大字符长度11个，立即分配11个字符长度的存储空间，如果存不满，空格填充。
2.varchar(11):
变长的字符串类型看，最大字符长度11个。在存储字符串时，自动判断字符长度，按需分配存储空间。
3.enum('bj','tj','sh')：
枚举类型，比较适合于将来此列的值是固定范围内的特点，可以使用enum,可以很大程度的优化我们的索引结构。
（3） 时间类型
1.datetime
储值范围 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
2.timestamp
1970- ??? 2037 ???? 受到时区影响
（4） JSON
(1) 约束
primary key 主键 : 非空且唯一,一个表只能有一个主键
not null 非空 : 列值必填
unique key 唯一 : 列值不能重复
unsigned 无符号: 对于数字列,无符号
(2) 其他属性
comment : 注释
default : 默认值
auto_increment : 自动增长
建表：
1.存储引擎
2.字符集
3.校对规则
列的定义:
1.数据类型
2.长度
3.默认值
4.主键
5.非空‘
6. 无符号: 对于数字列,无符号
7.是否自增
8. 注释
2.2 表的操作
-
建表
1.语句格式 create table 表名( 列名1 列属性1.., , 列名2 列属性2... , 列名3 列属性3..., )NGINE=INNODB CHARSET=utf8mb4 COLLATE=校对规则 COMMENT '表的说明'; 2.建表操作 CREATE TABLE stu( id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号', NAME VARCHAR(64) NOT NULL COMMENT '姓名', age TINYINT UNSIGNED NOT NULL DEFAULT 99 COMMENT '年龄', gender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别', telnum CHAR(11) NOT NULL DEFAULT '0' COMMENT '手机号', intime DATETIME NOT NULL DEFAULT NOW() COMMENT '入学时间' ) ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表'; -
删除表
1.语句格式 drop table 表; ### 表结构+数据全部(物理)删除 truncate table 表; ### 数据全部清空(物理),清空是区 DELETE FROM 表 WHERE 列=行; ### 逐行删除表中所有数据,逻辑删除 UPDATE 表 SET state='1' WHERE 列=行; ### 伪删除，用状态列标记一个行是否存在. 2.语句操作 drop table stu; truncate table stu; DELETE FROM stu WHERE id='12'; update stu set state='1' where id='12'; 查看表
1.语句格式
desc 表； ### 查看表的列
show create tables 表； ### 查看表的属性
2.3 列的操作
--- 修改表结构
(1) 在stu表中添加qq列
1.命令
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL DEFAULT 0 COMMENT 'QQ号';
2.语句格式
ALTER TABLE 表 ADD 列 列属性；
注意:
- 大表结构变更,会长时间锁表,肯定会影响到线上业务
- 8.0以后就自动解决了,8.0以前需要业务不繁忙期间或者使用PT-OSC工具
(2) 在gender后加一个wechat列
1.语句
ALTER TABLE stu ADD wecht VARCHAR(20) NOT NULL DEFAULT '0' COMMENT '微信号' AFTER gender ;
2.语句格式
ALTER TABLE 表 ADD 列 列属性 AFTER 列；
(3) 在首列添加sid列
1.语句
ALTER TABLE stu ADD sid VARCHAR(20) NOT NULL DEFAULT '0' COMMENT 'sid' FIRST;
2.语句格式
ALTER TABLE 表 ADD 列 列属性 FIRST;
(4) 删除列
1.语句
ALTER TABLE stu DROP sid;
2.语句格式
ALTER TABLE 表 drop 列；
(5) 修改列的属性
1.语句格式
ALTER TABLE 表 MODIFY 列名 列属性； #### 修改列属性
ALTER TABLE 表 CHANGE 列名 改变的列名 列属性 ### 修改列名以及列属性
注意：MODIFY只能修改列属性，CHANGE 不仅仅能修改列属性，还能修改列名。
2.语句操作
ALTER TABLE stu MODIFY NAME VARCHAR(100) NOT NULL COMMENT '姓名';
ALTER TABLE stu CHANGE NAME sname VARCHAR(64) NOT NULL COMMENT '姓名';
（6）清空行内容(危险)
1.语句格式
TRUNCATE TABLE 表；
2.语句操作
TRUNCATE TABLE stu;
3.说明
此删除，是物理删除，删除表的内容，但是不会删除表结构。
标题：-----DCL语句
-----DCL语句分类
1.GRANT
2.revoke
【1】 权限的定义-GRANT
Linux中用户有其对应的权限，能够限制用户能够做些什么，限制了用户的行为规范，也就是保证了数据库的安全。而数据库中同样，对于用户也有其对应的权限，通过权限的管理能够最大程度的保护好数据库中的数据。
【2】权限的种类-GRANT
1.全部-普通用户
ALL
2.类型-普通用户
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE,
REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES,
LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT,
CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER,
EVENT, TRIGGER, CREATE TABLESPACE
3.超级管理员权限-给用户授权
with grant option
【3】权限的作用-GRANT
- 控制用户,能够对数据库对象做哪些操作
【4】DCL-授权命令说明-GRANT
（1）命令
grant all on *.* to Admin@'%' identified by '123';
（2）格式
grant 权限 on 对象 to 用户 identified by '密码';
（3）说明
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0qPv1iyH-1573206841424)(标题：DDL-DCL-DML语句.assets/权限命令.jpg)]
----权限管理命令-DCL-GRANT
【1】授权
grant all on *.* to user@'10.0.1.%' identified by '123';
【2】查询权限
show grants for user@'10.0.1.%';
【3】授权超级管理员用户
grant all on *.* to user@'10.0.1.%' with grant option;
----权限管理命令-DCL-REVOKE
【1】权限定义-DCL-REVOKE
在MySQL中对于权限的移除，并不是直接覆盖，因为在数据库的权限是叠加的，授权A权限和授权B权限，最终权限是A+B权限。所以数据库的权限是回收权限。
所以，一定要注意，在移除MySQL权限的时候，是回收，而不是覆盖，切记！！！
【2】权限管理命令-DCL-REVOKE
1.语句格式
REVOKE 权限 ON 库/表 FROM 用户@'白名单'；
2.语句操作
revoke select on *.* from user@'10.0.1.0';
标题：-----DML语句
-----DML语句分类
1.INSTER
2.UPDATE
3.DELETE
【1】 INSERT语句
1.语句格式
INSERT INTO 表(列1，列2，列3) VALUES(v1,v2,v3,....)
2.语句操作
DESC stu;
INSERT INTO
stu(sname,age,gender,telnum)
VALUES
('张三',19,'m','110'),
('张四',17,'f','119'),
('张五',18,'m','120');
3.注意
MySQL中INTO可以省略，但是ORACLE不可以省略，所以规范SQL语句，还是加上。
【2】UPDATE语句
1.语句格式
UPDATE 表 SET 列=cc WHERE 条件
2.语句操作
UPDATE stu SET age=29 WHERE sname='张三';
3.注意
一定要有where条件指定是那个行，否则默认修改所有列。
【3】DELETE 语句
1.语句格式
DELETE FROM 表 WHERE 条件；
2.语句操作
DELETE FROM stu WHERE sname='张五';
3.注意
一定要有where条件指定是那个行，否则默认删除所有列。
标题：各类删除语句的区别
DROP TABLE , TRUNCATE TABLE ,DELETE FROM TABLE 区别
- DROP TABLE : 表结构+数据全部(物理)删除
- TRUNCATE TABLE: 数据全部清空(物理),清空是区
- DELETE FROM TABLE : 逐行删除表中所有数据,逻辑删除
标题：项目-伪删除
前言：
用状态列标记一个行是否存在，用UPDATE 替代 DELETE，防止数据误删除。
(1) 添加一个状态列(1代表删除,0代表未删除)
1.语句格式
ALTER TABLE 表 ADD 状态列 列属性；
2.语句操作
ALTER TABLE stu ADD state CHAR(1) NOT NULL DEFAULT '0' COMMENT '状态列:1代表删除,0代表未删除';
(2) 删除张四数据行
1.原语句:
DELETE FROM stu WHERE sname='张四';
2.改写为:
UPDATE stu SET state='1' WHERE sname='张四';
(3) 查询数据
1.原查询语句:
SELECT * FROM stu;
2.改写为:
SELECT * FROM stu WHERE state='0';
（4）注意
添加状态列之后，交给开发的伪删除的时候，需要在查询语句中加上WHERE条件语句，过滤状态为1的删除列。
