死磕数据库系列（二十）：MySQL 数据库 DDL、DML、DQL、DCL 语言理论与实践（sql 8.0 版）

今天，民工哥带大家一起来学习一下 MySQL 数据库的 DDL、DML、DQL、DCL 这几种语言的理论知识与实践。如有帮助，请点在看、转发支持一波！！！

DDL（数据库定义语言）

DDL

数据（结构）定义语言 DDL（Data Definition Language），用于创建和修改数据库表结构的语言。

常用的语句：create（创建）、alter（修改）、drop（删除）、rename（重命名）

创建数据库：

CREATE DATABASE 数据库名;

CREATE DATABASE IF NOT EXISTS 数据库名;
# IF NOT EXISTS 意为如果不存在就创建这个数据库，若存在就不创建

CREATE DATABASE IF NOT EXISTS 数据库名 CHARSET utf8;
# CHARSET utf8 给数据库设置一个编码

删除数据库：

DROP DATABASE 数据库名;

修改字符集

ALTER DATABASE 数据库名 CHARSET utf8;

mysql中是不能修改数据库名的。

数据库中的表

数据库存储数据的特点

是将数据放到表中，再将表放到数据库当中。

一个数据库中是由多张表的，每个表都拥有一个名字，用来标识自己。且表名是具有性的。

表具有一定的特性，这些特性定义了数据在表中如何的存储，类似Java中的“类”的设计。

数据库表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列都类似java中的“属性”。

表中的数据时按行存储的，每一行就类似于java中的“对象”。

数据表中的基本概念

数据表

表（table）是数据存储的常见和简单的形式，是构成关系数据库的基本元素。表的简单形式是由行和列组成，分别都包含着数据。每个表都有一个表头和表体组成。表头定义表名和列名。表中的行被看作是文件中的记录，表中的列被看作是这些记录的字段。

记录

记录也被称为一行数据，是表里的一行。在关系型数据库的表里，一行数据时指一条完整的记录。

字段

字段是表里的一列，用于保存每条记录的特定信息。如顾客订单表的字段包括“订单ID”、“姓名”、“客户ID”、“职务”、“上级”、“地区”、“运货商”、“国家”等等。数据表的一列包含了特定字段的全部信息。

在我们需要设计一个表的时候。在创建之初，就应该先确定要设计表的以下特征：

表面（表星系）、表中的字段、字段中的数据类型和长度、都有哪些约束（添加数据的一些限制）

主键： 在一张表中代表的一条记录，不能为空，不呢重复

约束：

• PRIWARY KEY ：设置主键约束（主键约束包含了不能为空和性的约束）
• NOT BULL ：设置不能为空的约束（一个表中可以有多个这种的约束）
• UNIQUE ：设置性约束（一个表中可以有多个这种的约束）
• 检查约束 ：这个需要条件判断的约束（不是所有的数据库都可以设置这个约束）
• 外键约束 ：出现在多表关联时使用。

主键自动增长： AUTO_INCREMENT

默认值： DEFAULT default——value

字段注释： COMMENT ‘注释’

数据表的基本数据类型：

• char(n)长度为n的定长字符串，即使只存入一个字符，它也要站n长的内存
• varchar(n) 大长度为n的可变长字符串，实际存储几个就占几个字符的内存（在n长之下）
• date 日期，包含年月日
• datetime 年月日 时分秒（时间）

在我们应用这些数据类型的时候，就需要注意这些浮点型的小数点位数的控制。

数据类型（M，D）  
M：精度，数据的总长度  
D：标度，小数点后的长度  
Float（6,2）  
Double（6,2）  
===》例如：3245.12 四位整数，两位小数总长为6位

BLOB和TEXT类型

BLOB是一个二进制的大对象，可以容纳可变数量的数据，用于存储图片视频信息。有4中BLOB的具体类型：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。它们只是可容纳值的大长度不同。

TEXT列字符字符串

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。

它们两个的长度都是可变的长度。

建表语句

建立一个基本表，不涉及任何约束

CREATE TABLE t_student{
 `num` INT,
 `number` VARCHAR(4),
 `sex` CHAR(1),
 `birthday` DATE,
 `height` FLOAT(4,1),
 `phone` CHAR(11),
 `register_time` DATETIME
}

加上约束来对数据库表进行创建[ ]表示可以没有，也能创建出表。

CREATE TABLE t_user (
 id INT [ PRIMARY KEY NOT NULL AUTO_INCREMENT ],
 number INT (5) [ NOT NULL ],
 `name` VARCHAR (10) [ NOT NULL ],
 sex CHAR (1) [ DEFAULT '男' ] COMMENT '性别，默认为男。comment意为注释说明',
 age INT (3) [ CHECK (age > 18) ],
 phone CHAR (11) [ NOT NULL UNIQUE ],
 birthday DATE,
 weight DOUBLE,
 oper_time DATETIME,
)

删除表语句

DROP TABLE [IF EXISTS] 表名;

修改表名语句

RENAME TABLE 旧表名 TO 新表名;

复制表（对某张表操作时，备份数据）语句

CREATE TABLE 新表名 LIKE 被复制表名;

修改表结构语句

添加/删除表的主键约束

#给表中的某一列添加主键且一张表中只能有一个主键，
#但是可以多个表字段联合作为表的主键
ALTER TABLE 表名 ADD PRIMARY KEY(列名) 

#删除表中的主键
ALTER TABLE 表名 DROP PRIMARY KEY

设置表中的自动增长

#添加列为自增（设置自增的列必须为整数型的数据类型）
ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;

#删除列的自增
ALTER TABLE 表名 MODIFY 列名 数据类型;

设置不能为空

#添加一个列不能为空的约束
ALTER TABLE 表名 MODIFY 列名 类型 NOT NULL;

#取消一个列不能为空的约束
ALTER TABLE 表名 MODIFY 列名 类型 NULL;

设置约束

#添加表中某一列不能为空的约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名);

#删除表中某一列不能为空的约束
ALTER TABLE 表名 DROP INDEX 约束名;

设置检查约束

#添加约束名
ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK (条件);

#删除约束名
ALTER TABLE 表名 DROP CHECK 约束名;

添加列

#添加一个数据表中的列，默认是在后一列进行添加
ALTER TABLE 表名 ADD 列名 数据类型; 

#指定给列前进行一个添加列的操作
ALTER TABLE 表名 ADD 列名 数据类型 FIRST;

#指定给列名1之后添加一列
ALTER TABLE 表名 ADD 列名 数据类型 AFTER 列名1;

删除列

ALTER TABLE 表名 DROP 列名;

修改列名

ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型;

修改列的数据类型

ALTER TABLE 表名 MODIFY 列名 新数据类型;

DML（数据库操纵语言）

DML 数据操纵语言（Data Manipulation Language）

常用语句：INSERT（插入数据）、DELETE（删除数据）、UPDATE（修改数据）

数据库中NOW()表示的是获得当前所在系统的时间

插入数据：

方式1：

INSERT INTO 表名(列1,列2...,列n) VALUES(值1,值2...,值n);

-- 例如：
INSERT INTO t_student (
  NAME,
  sex,
  birthday,
  height,
  mobile,
  reg_time
)
VALUES
  (
    '张三',
    '男',
    '2000-1-1',
    170.1,
    '13333333333',
    NOW()
  );

方式2：

INSERT INTO 表名 set 列名1=值1,..列名n=值n;

-- 例如:
INSERT INTO t_student SET NAME = '李四',
sex = '女',
birthday = '2001-1-1',
height = 170.1,
mobile = '135555555',
reg_time = NOW();

方式3：（一次添加多条数据）

INSERT INTO 表名(列1,列2...,列n) VALUES(值1,值2...,值n),(值1,值2..., 值n),...;


-- 例如：
INSERT INTO t_student (
  NAME,
  sex,
  birthday,
  height,
  mobile,
  reg_time
)
VALUES
  (
    '张三1',
    '男',
    '2000-1-1',
    170.1,
    '13333333333',
    NOW()
  ),
  (
    '李四1',
    '男',
    '2000-1-1',
    170.1,
    '135555555',
    NOW()
  );

方式4：将一个表的数据复制到另一个表的操作（相当于数据备份）就是它得到值来自于另外的一个查询语句。

INSERT INTO 表名(列1,列2……,列n) 查询语句(查询的列数与插入列数匹配)

-- 例子：将t_student表中的数据插入到stu这个表当中去。
INSERT INTO stu (
  NAME,
  sex,
  birthday,
  height,
  mobile,
  reg_time
)
SELECT
  NAME,
  sex,
  birthday,
  height,
  mobile,
  reg_time
FROM
  t_student;

修改数据

UPDATE 表名 SET 列名 = '新值' WHERE 条件;

-- 例子：修改在t_student表中的num为1的数据中name为张三 sex为女的操作
UPDATE
  t_student
SET
  NAME = '张三',
  sex = '女'
WHERE num = 1;

修改当中也是可以不用添加条件的，不添加条件的话，就会将每条数据对应的列都做修改。

所以修改的时候一定要注意条件。

删除语句

#删除与修改同样，也是可以不用添加条件的，不添加条件的话就会删除当前表中的所有数据。
DELETE FROM 表名 WHERE 条件;

-- 例子：
DELETE
FROM
  t_student
WHERE num = 1
#指定在t_student表中的num为1的这一行数据进行删除。

在要删除一张表的时候，也可以使用下面这条查询语句：

TRUNCATE TABLE 表名;
-- 清空整张表

DQL（数据库查询语言）

DQL（Data Query Language）数据查询语言

数据查询语句是使用频率高的一个操作，是可以从一个表中查询数据，也可以从多张表中进行关联查询数据。

基础语法：

SELECT 查询列表 FROM 表名 [WHERE 条件];

特点：

• 查询列表可以是：表中的字段、常量、表达式、函数
• 查询的结果是一个虚拟出的表格。

查询结果的处理

1.查询常量值（了解一下就行了，没啥用）

SELECT 100;

2.查询表达式

在SQL中可用的表达式有：+、-、*、/

SELECT 100*2

SELECT 列+5 FROM 表;
--例如：
SELECT height+5 FROM t_student;
--给查出的每一列数据都加上一个5

3.查询函数

SELECT 函数;

-- 例如:
SELECT VERSION();
-- 查看当前SQL的版本是多少

4.特定列查询

SELECT 列名1,列名2，... FROM 表名;

5.全部列查询

SELECT * FROM 表名;

6.去除重复行查询

将查询出来的重复数据去掉，针对查询出来的结果，要求是查询出的所有列数据都要一样，才会去掉。

SELECT DISTINCT 列名1,列名2，... FROM 表名;

使用函数对查询结果的处理

函数：类似于Java中的方法，将一组逻辑语句事先在数据库中定义好，在需要使用的时候直接调用就好了，想调用now()函数一样。

优点：

• 1.隐藏了实现的细节
• 2.提高了代码的重用性

调用方式：

SELECT 函数名（实参列表）[from 表];

分类：

• 单行函数：如concat、lengthifnull等。
• 就是查询出来的结果是多少行，这个函数就会对每一行的数据都进行处理操作。
• 分组函数：做统计使用，又称为统计函数、集合函数、组函数。
• 也叫聚合函数；多行转为一行。

单行函数

字符函数

● length()：获取参数值的字节个数。一个中文3个字节。

-- 获得当前列名的字节各处
SELECT LENGTH(列名),列名 FROM 表名;

● char_length()：获取参数值的字符个数。

-- 获取当前列名的字符个数。
SELECT CHAR_LENGTH(列名),列名 FROM 表名;

● concat(str1，str2，…)：拼接字符串。

它可以将两个字符接在一起，以一个列进行发送。

SELECT CONCAT(str1,str2...) FROM 表名;

-- 例如:
-- 将两个列，通过字符的连接，以一个列进行了发送。
SELECT CONCAT(列名1,':', 列名2)AS name FROM 表名;
-- AS name 是对定义了一个别名（AS也是可以省略的）

● upper()：将字符串变成大写。

SELECT UPPER(列名) FROM 表名;

● lower()：将字符串变为小写。

SELECE LOWER(列名) FROM 表名;

● substring(str，pos，length)：截取字符串，从位置pos位置开始。

SELECT SUBSTRING(列名,开始截取位置（从1开始）,截取长度) FROM 表名;

● instr(str，指定字符)：返回子串次出现的索引，如果找不到就返回0类似于java中的indexof();

SELECT INSTR(列名,指定的字符) FROM 表名;

-- 例子:
SELECT INSTR('abcd','a');

● trim(str)：去掉字符串前后的空格或子串。trim（指定子串from字符串）

-- 去掉字符前后的空格。
SELECT TRIM(列名) FROM 表名;

-- 去掉子串
SELECT TRIM(指定的子串 FROM 列名) FROM 表名;

-- 列子
SELECT TRIM('d' FROM 'abcd');

子串和空格一样，只能去掉字符串前后的子串，中间的不行。

● lpad(str，length，填充字符)：用指定的字符实现左填充将str填充为指定长度。

SELECT LPAD(列名,指定总长度,填充字符) FROM 表名;

-- 例子:
SELECT LPAD('wasd',6,'a') ;

指定的总长就是后结果的总长，若需要填充的字符串长度就高于指定总长度，就会截取这么长的字符串。

SELECT LPAD('wasd',3,'a') ;

● rpad(str，length，填充字符)：用指定的字符实现右填充将str填充为指定长度。

SELECT RPAD(列名,指定总长度,填充字符) FROM 表名;

● replace(str，old，new)：替换，替换所有的子串。没有的就不操作了。

SELECT REPLACE(列名,指定旧字符,新字符) FROM 表名;

-- 例子:
SELECT REPLACE('wwaa','a','w');

逻辑函数

case when 条件 then 结果1 else 结果2 end；（可以有多个when）,当条件满足的时候就是结果1，当条件不满足的时候就是结果2。

SELECT
  (CASE WHEN 列名 条件 THEN 结果1 ELSE 结果2 END)
FROM
  表名;

-- 例子:使用多个when来生成结果。
SELECT
  (CASE WHEN 140>=180 THEN '偏胖'
 WHEN 140>100 AND 140<180 THEN '正常'
 ELSE '偏瘦' END)weight;

2.IFNULL（被检测的值，自己设置的默认值）

ifnull函数就是检测当前字符是否为null；如果为null，就返回自己设置的默认值；若不是，则正常返回原本的值

这里要注意一下，在数据库表中：
左边的不为null，表示的是空字符串（它是有值的），右边的才为null。

SELECT IFNULL(列名,自己定义的默认值) FROM 表名;

举例：

SELECT IFNULL('','当前没有值') ;

不错输出的，它是一个空字符串，而不是为null；

再举一例：（当前表名为student）

SELECT IFNULL(address,'当前没有值') FROM student WHERE id = 1

3.IF函数：

• 像java中的if…else…的效果。
• if（条件，结果1，结果2）
• 条件成立走结果1，不成立的话就结果2；

SELECT IF(条件,结果1,结果2) FROM 表名;

-- 举例:
SELECT IF(5>1,'true','false');

数学函数

1.round(数值)：对传入的数值进行四舍五入（四舍五入的时候只看小数点之后的位）

2.ceil(数值)：对数值进行向上取整，返回>=该参数的小整数

3.floor(数值)：对数值进行向下取整，返回<=该参数的大整数

4.truncate(数值，保留的小数位数)：截断，小数点后截断到几位；

SELECT ROUND(3.4),ROUND(3.5),CEIL(3.5),FLOOR(3.5),TRUNCATE(3.12345,2);

5.mod(被除始，除数)：取余，被除数为正，则结果为正；被除数为否，则为否；与除数是无关的

SELECT MOD(6,4),MOD(-6,4),MOD(6,-4),MOD(-6,-4);

6.rand()：获取随机数，返回0-1之间的小数。

SELECT RAND();

日期函数

• NOW()：返回当前系统的日期+时间；
• CURDATE()：返回当前系统日期；（不包含时间）
• CURTIME()：返回当前时间，不包含日期；

SELECT NOW(),CURDATE(),CURTIME();

也可以获取指定时间里面的具体哪一个部分，有：年，月，日，时，分，秒。

YEAR()：年
MONTH()：月
DAY()：日
HOUR()：时
MINUTE()：分
SECOND()：秒

SELECT 
NOW(),YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());

● STR_TO_DATE()：将日期的字符串型转换为指定格式的日期；

● DATE_FORMAT()：将数据库中日期格式转换为字符串形式；

● DATEDIFF(big,small)：返回两个日期相差的天数；

SELECT
STR_TO_DATE('2022-1-1','%Y-%m-%d'),DATE_FORMAT(NOW(),'%Y/%m/%d'),DATEDIFF(NOW(),'2022-1-1');

将三者结合而出一个比较准确的数据库语言

SELECT DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d'),STR_TO_DATE('2022-1-1','%Y-%m-%d'))

分组函数

功能：用于统计使用，又称为聚合函数或统计函数或组函数。

分类

SUM()求和、AVG()平均值、MAX()大值、MIN()小值、COUNT()计数

• 1.sum、avg一般用于处理数值型；max、min、count是可以处理任何类型的。
• 2.这些分组函数都是可以忽略null值的。
• 3.count函数的一般使用count(*) 来用作统计行数（括号里也可以写主键/指定列）。
• 4.和分组函数一同查询的字段要求必须在group by后的字段。（GROUP BY就是要求分组是按什么进行分组）sql5.0版之前不需要加。

因为是分组函数，所以我在这先建立一个student的表：后面的演示都采用这张表进行
代码演示：

SELECT 
 SUM(height),AVG(height),MAX(height),MIN(height),COUNT(*) 
FROM student;

按性别对表数据进行一个分组：

SELECT 
 sex,SUM(height),AVG(height),MAX(height),MIN(height),COUNT(*) 
FROM student 
GROUP BY sex;

条件查询

使用WHERE子句，将不满足条件的行过滤掉，WHERE子句在书写的时候是紧随FROM子句。

语法：

SELECT <结果> FROM <表名> WHERE <条件>

比较符：[=、！=或<>、>、<、>=、<=]  
逻辑符：and（与）、or（或）、not（非）

举例：

SELECT * FROM student WHERE sex = '男';

SELECT * FROM student WHERE sex <> '男';

加逻辑符：

SELECT * FROM student WHERE sex = '男' AND height>180;

SELECT * FROM student WHERE NOT sex = '男' AND NOT weight>100;

其他的都一样，就不一 一演示了。

模糊查询

LIKE：是否匹配于一个模式，一般情况下是和统配符搭配一起使用的，可以判断字符型或者数值型。

统配符：

% 表示前或后可以有任意多个（包含个字符）；

_ 前或后只能表示一个，单个字符；（有几个下划线就只能有几个字符，多了少了都不行）

BETWEEN 条件1 AND 条件2：表示两者之间，包含临界值；（相当于条件查询中的 >= 条件1 AND <= 条件2）；

IN：判断某字段的值是否值属于IN列表中的某一项  
（列名 IN (条件1，条件2…) <===> 列名 = 条件1 OR 列名 = 条件2）；

IS NULL（为空）或IS NOT NULL（不为空）  
表示当数据表中的数据为空（注意区分空和空字符的两者区别。空字符意为它是有值的，空意为没有任何数据。空字符可以理解它为只输入了一个空格）；

举例：

SELECT * FROM student WHERE weight LIKE '%1%'

SELECT
 * FROM student WHERE weight LIKE '_2___'; #这里前有1个下划线，后有3个

between…and…

SELECT * FROM student WHERE height BETWEEN 178.6 AND 185;

IN（类似or）

SELECT * FROM student WHERE NAME IN('张三','zhangsan');

SELECT * FROM student WHERE NAME NOT IN('张三','zhangsan');

IS NULL

给student表中再加一条数据
使用IS NULL进行查询：

SELECT * FROM student WHERE weight IS NULL;

SELECT * FROM student WHERE weight IS NOT NULL;

UNION 和UNION ALL语法

UNION语法就是将两个表中的数据合二为一，并且会将结果中的重复数据进行删除。

[查询语句1] UNION [查询语句2]

UNION ALL语法也是将两个表中的数据合二为一，但不会再对这些数据做任何的操作。也可以看出UNION ALL的效率是高于UNION的效率的。

[查询语句1] UNION ALL [查询语句2]

在使用的时候要注意两条查询到返回的数据类型和个数必须完全一样。

举例：

SELECT * FROM student WHERE sex = '女' 
UNION 
SELECT * FROM student WHERE weight > 100;

SELECT * FROM student WHERE sex = '女' 
UNION ALL
SELECT * FROM student WHERE weight > 100;

对查出的数据进行排序和数量限制

1.排序

就是对结果进行排序，使用ORDER BY对子句进行排序。

语法：ORDER BY 列名 ASC/DESC

ASC代表的是升序，DESC表示降序，如果不写，默认的是进行升序。

ORDER BY子句中可以支持单个字段、多个字段、表达式、函数、别名。

举例：

SELECT * FROM student WHERE weight > 100 ORDER BY height ASC;

SELECT * FROM student WHERE weight > 100 ORDER BY height DESC;

当一个列排序的时候，应为重复性不能做出明确排序，可以再加一个字段进行排序：（就是先用个排，当个遇到相同的时候，再用第二个排）

SELECT 
 * 
FROM student WHERE weight > 100 
ORDER BY height DESC,weight DESC;

函数同理；

数量限制

limit子句：可以理解为就是对查询出来的数据进行一个行数限制
（书写在SQL语句的末尾位置）。

语法：SELECT * FROM 表名 LIMIT 初始位置，几行数据

初始位置默认初为0

举例：

查询身高高的两名同学：这条语句正常数据是由三条，使用了limit 设置语句，表示它从头开始，查出两行数据。

SELECT
  *
FROM
  student
WHERE weight > 100
ORDER BY height DESC
LIMIT , 2;

分组查询

语法：

SELECT 分组函数，列名（所要分组的要求列） FROM 表名 [WHERE 分组前的筛选条件] GROUP BY 分组的列名 [HAVING 分组后的筛选]

查询条件 WHERE和HAVING的区别：举例：

-- 意为将数据在student表内按sex进行分组，然后对没一组进行就数据量、数据求和、大值。
SELECT sex,COUNT(*),SUM(height),MAX(height) FROM student GROUP BY sex;

SELECT
  sex,
  COUNT(*)
FROM
  student
WHERE height IS NOT NULL #分组前将height为空的信息去除
GROUP BY sex  #按照sex对数据进行分组
HAVING sex = '男' #分组后只要sex为‘男’的数据
ORDER BY COUNT(*) #对数据进行一个排序 默认为升序
LIMIT ,1 #对查出的数据进行数量限制

子查询

含义：出现在其他语句中的SELECT语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询。

分类：按子查询出现的位置：

• 1.SELECT后面：仅仅支持标量子查询；
• 2.FROM后面：支持表子查询；
• 3.WHERE或HAVING后面：支持标量子查询，列子查询，行子查询；

在修改、删除表中数据的语句中使用子查询，注意的是，子查询中不能使用当前正在操作的表。

按功能、结果集的行列数不同：

• 1.标量子查询（结果集只有一行一列）
• 2.列子查询（结果集只有一列多行）
• 3.行子查询（结果集有一行多列）
• 4.表子查询（结果集一般为多行多列）

子查询在SELECT语句内部可以出现SELECT语句。

语句结果可以作为外部语句中条件子句的一部分，也可以作为外部查询时的临时表。

在一条SQL语句中，如果出现2个以上的表名时，我们可以为这个表名定义别名

举例：SELECT后面

SELECT
  st.sex,
  (SELECT s.name FROM student s WHERE s.`name` = st.name)
FROM
  student st 

在FROM后面：

SELECT
  * 
FROM (SELECT sex,NAME FROM student)s
WHERE s.sex = '男'

在WHERE或HAVING之后：

-- 标量子查询
SELECT
  *
FROM
  student
WHERE height =(SELECT MAX(height) FROM student);

-- 列子查询
SELECT
  *
FROM
  student
WHERE height IN (SELECT height FROM student WHERE height > 170);

-- 行子查询
SELECT
  *
FROM
  student
WHERE (height, weight) =(SELECT MAX(height),MAX(weight) FROM student);

多表关联

在设计表的时候，我们有时候是需要多表关联的，主要为了减少数据的冗余，对表进行拆分。

数据库设计范式：为了建立冗余较少，结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则称为范式。范式是符合某一种设计要求的总结。

目前关系数据库有5种范式：

• 范式（1NF）
• 第二范式（2NF）就可以
• 第三范式（3NF）
• 第四范式（4NF）
• 第五范式（5NF）又称器完美范式

满足低要求的范式就是范式。在范式的基础上进一步满足更多规范要求的称为第二范式，其余范式依次类推，一般来说，数据库只要到达第三范式就可以了。

范式（确保每列都保持原子性）

范式是对基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了范式。

2.第二范式（包含了主键，要求其他字段都要依赖于主键）

没有主键就没有性，没有性在集合中就定位不到这行数据记录，所以就要有主键。

其他字段为什么要依赖于主键？因为不依赖于主键，就找不到他们。更重要的是，其他字段组成的这行记录和主键表示的是同一个东西，而主键是的，它们值需要依赖于主键，也就成了的。

3.第三范式就是要消除传递依赖，方便理解，可以看作是“消除冗余”。

外键

就是“引用”另外一个数据表的某条记录。

外键类类型必须和主键列类型保持一致。

数据表之间的关联/引用关系是依靠具体的主键（PRIMARY KEY）和外键（FOREIGN KEY）建立起来的。

建表时就增加外键：

CREATE TABLE 表名(
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 sid INT,
 CONSTRAINT 约束名 FOREIGN KEY(sid) REFERENCES 关联表(主键)
);

添加外键约束名

ALTER TABLE 表名 ADD [CONSTRAINT 约束名] FOREIGN KEY（外键列） REFERENCES 关联表（主键）;

删除外键约束：

ALTER TABLE 表名 DROP FOREIGH KEY 外键约束名；

举例：

先建两张表
像这种有关系但没有关联的关系我们也叫做弱关联。没有实际之间的约束。

有外键的也就叫做强关联。（添加外键约束）

ALTER TABLE student
  ADD CONSTRAINT fk_gradeId FOREIGN KEY (gId) REFERENCES grade(id);

意为将student表中的gId列与grade表中的id列进行关联。

进行关联后，表头也就有了一些的变化。
删除外键：

ALTER TABLE student DROP FOREIGN KEY fk_gradeId;

注意：

• 1.当主表中没有对应的记录时，是不能将记录添加到从表中的。
• 2.不能更改主表中的值而导致从表中的记录孤立。
• 3.表存在与主表对应的记录，不能从主表中删除该行。
• 4.删除主表前，先删从表中的数据。

关联查询

含义：又称为多表查询，当查询的字段来自与多个表时，就会用到连接查询
（一对多、多对一、一对一、多对多）

笛卡尔乘积现象：表1有m行，表2有n行，结果 = m*n；

发生原因：没有有效的连接条件

如何避免：添加有效的连接条件

代码演示：

 SELECT * FROM student,grade ;

解决办法：添加条件，多表时，为表定义别名，通过别名去调用表中的列，这样就不会重复了。

SELECT
  *
FROM
  student s,
  grade g
WHERE s.gId = g.id  -- 先合并表,后筛选

按功能分类：

• 1.内连接：等值连接、非等值连接、自连接
• 2.外连接: 左外连接、右外连接

内连接（INNER JOIN）

主要通过设置连接条件的方式，来移除查询结果中某些数据行的交叉连接。

说人话就是：是利用条件表达式来消除交叉连接的某些数据行。

格式：

SELECT 列名 FROM 表1 INNER JOIN 表2 [ON子句]

等值连接：

使用等于号（=）比较被连接列的列值，在查询结果中列出被连接表中的所有列，包括其中的重复列。

SELECT
  *
FROM
  student s
  INNER JOIN grade g
    ON s.gId = g.id 

不等连接：

在连接条件中，可以使用其他比较运算符，比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!< 和 <>。

SELECT
  *
FROM
  student s
  INNER JOIN grade g
    ON s.height BETWEEN 170
    AND 180

自关联

所谓自关联是指，一个数据表中的某个字段关联了该数据表中的另外一个字段。就是自己关联自己

在这我们再创建一个表来说明这个问题：

CREATE TABLE t_area(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(10),
 pid INT 
)

加入数据：

SELECT
  ta.name,
  tp.name pname
FROM
  t_area ta
  INNER JOIN t_area tp
    ON ta.pid = tp.id
WHERE ta.id = 5

外连接

左外连接（LEFT JOIN）

无论关联条件是否成立，都会将左边表的数据全部查询出来。语法：

SELECT 列名 FROM 表名1 LEFT JOIN 表名2 ON 表1.column1 = 表2.column

右外连接（RIGHT JOIN）

无论关联条件是否成立，都会将右边表的数据全部查询出来。
语法：

SELECT 列名 FROM 表名1 RIGHT JOIN 表名2 ON 表1.column1 = 表2.column

举例：

将内关联，右关联，左关联放在一起举例：

student表数据做了一些修改（先看表数据）：
grade表：
内连接SQL语句：

SELECT
  *
FROM
  student s
  INNER JOIN grade g
    ON s.gId = g.id;

左连接SQL语句：

SELECT
  *
FROM
  student s
  LEFT JOIN grade g
    ON s.gId = g.id;

右连接SQL语句：

SELECT
  *
FROM
  student s
  RIGHT JOIN grade g
    ON s. gId = g.id;

DCL（数据库控制语言）

DCL 数据控制语言 (Data Control Language ) 。

在SQL语言中，是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、存储程序、用户自定义函数等数据库对象的控制权，由 GRANT 和 REVOKE 两个指令组成。

DCL 语句主要是 DBA 用来管理系统中的对象权限时所使用，一般的开发人员很少使用。DCL中主要包括创建用户、给用户授权、对用户撤销授权、查询用户授权和删除用户等操作。

用户

创建用户

create user '用户名'@'IP地址' identified WITH mysql_native_password by '密码';
flush privileges;

比如：

CREATE USER 'alian'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

MySQL8开始使用 caching_sha2_password ，如果你使用sqlyog工具无法连接，有两种方式处理：

• 升级sqlyog到 SQLyog-13.1.6-0.x64 以上
• 修改mysql默认身份验证插件为 mysql_native_password

修改用户名

rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

修改密码

#切换到mysql库
use mysql;
#更新密码
UPDATE user SET password=password('新密码') WHERE user='用户名' AND host='IP地址';
#刷新权限
FLUSH PRIVILEGES;

或者

ALTER USER '用户名'@'IP地址' IDENTIFIED WITH mysql_native_password BY '新密码';
flush privileges;

或者

#普通用户登录后
SET PASSWORD=password('新密码');
FLUSH PRIVILEGES;

删除用户

#注意这里的IP地址，一个用户可能会有多个
drop user '用户名'@'IP地址';
#比如
drop user 'Alian'@'192.168.0.100';

权限管理

授权

基本语法如下：

grant 权限1, 权限2, 权限3,… ,权限n on 数据库名.表名 to 用户名@地址;

常用的权限

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

关于 数据库名.表名 的说明：

*.* #表示任意库的任意表（不建议）
mysql.* #表示mysql库的任意表
mysql.user #表示mysql库的user表

关于 用户名@地址 的说明(这里都是英文的单引号)：

’alian’@'localhost’：#表示只允许本机登录
’alian’@’%'：#表示任意地址登录
’alian’@'192.168..100’：#表示只允许ip为192.168..100的地址登录
’alian’@'192.168.*.*'：#表示只允许ip为192.168网段的地址登录

把数据库的所有库的所有权限都给alian,并且可以指定ip地址

#把数据库的所有库的所有权限都给alian,并且是任意ip地址都可以操作
grant all privileges on *.* to 'alian'@'%';
flush privileges;

把mysql数据库的所有权限都给alian

#把mysql数据库的所有权限都给alian,并且是任意ip地址都可以操作
grant all privileges on mysql.* to 'alian'@'%';
flush privileges;

把mysql数据库的user表的所有权限都给alian,并且是任意ip地址都可以操作

#把mysql数据库的user表的所有权限都给alian,并且是只能通过192.138.0.10才可以操作
grant all privileges on mysql.user to 'alian'@'192.138.0.10';
flush privileges;

把mysql数据库的user表的（查询，插入，更新，删除）的权限都给alian,并且是任意ip地址都可以操作

#把mysql数据库的user表的（查询，插入，更新，删除）的权限都给alian,并且是任意ip地址都可以操作
grant SELECT, INSERT, UPDATE, DELETE on mysql.user to 'alian'@'%';
flush privileges;

查看权限

show grants for 'alian'@'%';

回收权限

基本语法如下：

revoke 权限1, 权限2…权限n on 数据库名.表名 from 用户名@地址;

比如把用户alian对mysql（默认的库）库的更新和删除权限收回

#回收用户的更新和删除mysql（默认的库）数据库的权限
revoke update,delete on mysql.user from 'alian'@'%';

容器中中文显示

#交互模式设置语言并进入mysql容器（419413b9d276 是mysql的容器id）
docker exec -it 419413b9d276 env LANG=C.UTF-8 /bin/bash

来源：https://blog.csdn.net/weixin_45970271/article/details/124082175 https://blog.csdn.net/weixin_45970271/article/details/124159900 https://blog.csdn.net/Alian_1223/article/details/123153573

相关文章