【示例】MySQL-4类SQL语言-DDL-DML-DCL

前言

本文主要讲述MySQL中4中SQL语言的使用及各自特点。

SQL语言总共分四类：DDL、DML、DQL、DCL。

DQL涉及内容角度，单独讲解，本章主要讲述DDL、DML、DCL。

DDL | Data Definition Language

数据定义语言：用来定义/更改数据库对象（数据库、表、字段）

用途 | 操作数据库

# 查询所有数据库
show databases;

# 创建数据库：[]里面的内容为选写，增加创建数据库时候的补充条件
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
-- create schema [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];

# 查看创建当前数据库时候的建库SQL语句
show create table 表明;

# 进入【切换】到某个数据库
use 数据库名;

# 查看当前数据库名字
select database();

# 删除数据库
DROP DATABASE [ IF EXISTS ] 数据库名;

字符集	utf8、utf8mb4等
引擎	InnoDB（MySQL5.5之后的默认引擎）、Memory、MyISAM

用途 | 操作数据表

# 查询当前数据库的所有表
show tables;

# 创建数据表
CREATE TABLE 表名(
	字段1 字段1类型 [约束] [COMMENT 字段1注释],
	字段2 字段2类型 [约束] [COMMENT 字段2注释],
	字段3 字段3类型 [约束] [COMMENT 字段3注释],
	......
	字段n 字段n类型 [约束] [COMMENT 字段n注释]
) ENGINE=引擎 DEFAULT CHARSET=字符集;[COMMENT 表注释] ;

# 查看指定表
desc 表名;

# 查看创建当前数据表时候的建表SQL语句
show create table 表名;

# 修改数据表名字
ALTER TABLE 表名 RENAME TO 新表名;

# 数据表-添加字段
ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

# 数据表-修改字段数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);

# 数据表-修改字段名+数据类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

# 数据表-删除字段
ALTER TABLE 表名 DROP 字段名;

# 删除数据表
DROP TABLE [IF EXISTS] 表名;

# 删除数据表后会再重新创建该数据表
TRUNCATE TABLE 表名; 

用途 | 表字段约束 | 总述

约束，作用于数据表中字段上的规则，用于限制存储在表中每个字段下的数据。保证数据库中数据的正确、有效、完整。

约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

约束类别如下：

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束(8.0.16版本之后)	保证字段值满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

约束示例 | 常规约束

CREATE TABLE tb_user(
	id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
	name varchar(10) NOT NULL UNIQUE COMMENT '姓名' ,
	age int check (age > 0 && age <= 120) COMMENT '年龄' ,
	statues char(1) default '1' COMMENT '状态',
	gender char(1) COMMENT '性别'
);

上述建表语句解释：

id	id设置为主键，并且自动增长
name	不为空，并且唯一
age	范围为(0, 120]
status	默认为1
gender	没有约束

【补充】

AUTO_INCREMENT是MySQL的一个属性，不是约束。自动递增的意思。

• 假设id有该属性，当插入数据不带有id的时候，会默认在最新的id值基础上+1作为新插入数据的id值；当插入数据带有id的时候，只要id不重复，可以非连续插入数据。

约束示例 | 外键约束 | 新建外键

新建外键

外键约束用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性

• 有A和B两张表：A表示部门表；B表示员工表，里面有的部门id字段
• 表A和表B可以建立外键关系：表A为主表；表B为从表，在从表中加入外键约束

外键约束的通用写法1：建表时添加外键约束

CREATE TABLE 表名(
	字段名 数据类型,
	...
	[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);

-- []：表示可选项；()：必须写

外键约束的通用写法2：对已有表添加外键约束

# 已有数据表，添加外键
ALTER TABLE 表名 ADD CONSTRAINT 自定义外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;

示例

• 员工表：emp

字段	含义
id	ID
name	姓名
age	年龄
dept_id	所属部门id

• 部门表：dept

字段	含义
id	ID
name	部门名称

• 解释：

  • 为员工表的dept_id字段添加外键约束，关联部门表的主键id

  • dept_id就是外键

  • 带有外键的表emp，就是子表；另外一个表是父表

• 添加约束

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

约束示例 | 外键约束 | 删除外键

基本语法

ALTER TABLE 表名 DROP FOREIGN KEY 自定义外键名称;

【补充】如果定义外键的时候，没有指定外键名称，需要按照对应数据库的规则，查找到对应的外键名称，才能对外键进行删除。所以定义外键的时候，最好指定别名。

删除上述示例中的外键

# 删除外键约束
alter table emp drop foreign key fk_emp_dept_id;

约束示例 | 外键约束 | 父表数据更新对子表数据的约束

添加外键之后，删除父表数据时产生约束的行为，就称为删除/更新行为

行为类别如下：

行为	说明
NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。 (与 RESTRICT 一致) 默认行为
RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。 (与 NO ACTION 一致) 默认行为
CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录
SET NULL	当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）
SET DEFAULT	父表有变更时，子表将外键列设置成一个默认的值 (Innodb不支持)

SQL语法

ALTER TABLE 子表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 父表名 (父表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

DML | Data Manipulation Language

数据操作语言，用来对数据库中表的数据记录进行增、删、改操作

所有的DML语言，在没有条件的时候，默认操作全表数据。

用途 | 添加数据

# 添加表数据--单行--指定字段
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);

# 添加表数据--单行--全部字段
INSERT INTO 表名 VALUES (值1, 值2, ...);

# 添加表数据--批量--指定字段
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

# 添加表数据--批量--全部字段
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

用途 | 修改数据

# 修改表数据
UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ];

用途 | 删除数据

# 删除表数据
DELETE FROM 表名 [ WHERE 条件 ] ;

DCL | Data Control Language

数据控制语言，用来管理数据库用户、控制数据库的访问权限

概念 | MySQL默认的数据库类别

mysql	主要存储数据库用户、权限等信息
information_schema	存放了MySQL服务器所维护的所有其他数据库的信息
performance_schema	收集服务器的执行同级信息。通过该库，用户可以查看服务器的运行过程，帮助定位可能存在的性能瓶颈。
sys	performance_schema的简化存在

DCL示例 | mysql.user表的增删改查

管理用户

# 查询用户
select * from mysql.user;

• Host代表当前用户访问的主机：localhost代表只能本机访问，不可以远程访问；若改为%，表示任意ip可访问该数据库
• User代表访问该数据库的用户名：root是默认用户；可以新建自定义的用户
• MySQL中通过Host、User来唯一标识一个用户

# 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

# 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

# 删除用户
DROP USER '用户名'@'主机名';

DCL示例 | 权限控制

限定用户对特定数据库中特定表的访问权限。

权限列表	说明
ALL、ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库、表、视图
CREATE	创建数据库、表

基本语法：

# 查询权限
SHOW GRANTS FOR '用户名'@'主机名' ;

# 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

# 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

示例：

# 查询 'heima'@'%' 用户的权限
show grants for 'heima'@'%';

# 授予 'heima'@'%' 用户itcast数据库所有表的所有操作权限
grant all on itcast.* to 'heima'@'%';

# 撤销 'heima'@'%' 用户的itcast数据库的所有权限
revoke all on itcast.* from 'heima'@'%';

• 用*号表示通配符，表示对数据库的所有表格赋予权限