MySQL
什么是数据库管理系统
数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件,用于建立,使用和维护数据库,简称DBMS.它对数据库进行统一的管理和控制,以保证数据库的安全性和完整性.
为什么使用数据库
数据存储方式比较:
| 存储方式 | 优点 | 缺点 |
|---|---|---|
| 内存 | 速度快 | 不能永久保存,数据是临时状态 |
| 文件 | 数据是永久保存 | 使用IO流操作文件;效率低,一般只能保存小量数据,只能保存文本数据. |
| 数据库 | 1.数据可以永久保存；2.方便存储和管理；3.使用统一的方式操作数据库（SQL）。 | 占用资源，有些数据库需要付费（比如Oracle数据库） |
使用数据库存储数据，用户可以非常方便对数据库中的数据进行增加、删除、修改及查询操作。
常见的数据库软件
DB-Engines Ranking根据数据库管理系统的受欢迎程度对它们进行排名。排名每月更新一次。
DB-Engines排名官网：DB-Engines Ranking - popularity ranking of database management systems。
开发中常见的数据库：
| 数据库名 | 介绍 |
|---|---|
| MySQL | 开源免费的数据库，因为开源免费、运作简单的特点，常作为中小型的项目的数据库首选。MySQL 1996年开始运作，目前已经被Oracle公司收购（2008年被SUN公司收购，2009年SUN公司被Oracle公司收购），MySQL 6.x开始收费 |
| Oracle | 收费的大型数据库， Oracle公司的核心产品，安全性高 |
| DB2 | IBM公司的数据库产品，收费的超大型数据库，常在银行系统中使用 |
| SQL Server | MicroSoft微软公司收费的中型的数据库。C#、.NET等语言常使用。但该数据库只能运行在windows机器上，扩展性、稳定性、安全性等性能都表现平平 |
| SQLite | 嵌入式的小型数据库，应用在手机端，如：Android |
为什么选择MySQL数据库？
-
- 开源、免费 - 功能强大、足以应付Web应用开发
MySQL的安装及配置
连接数据库:在终端窗口输入命令.
mysql -uroot -p
SQL
什么是SQL
结构化查询语言（Structured Query Language）简称SQL，是一种特殊的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
SQL的作用
1.是所有关系型数据库的统一查询规范，不同的关系型数据库都支持SQL。 2.所有的关系型数据库都可以使用SQL。 3.不同数据库之间的SQL有一些区别（方言）。
SQL通用语法
1.SQL语句可以单行或者多行书写，以";"分号结尾。 2.可以使用空格和缩进来增加语句的可读性。 3.MySQL中使用SQL不区分大小写，一般关键字大写，数据库名、表名、列名小写。
注释方式：
| 注释语法 | 说明 |
|---|---|
| -- 空格 | 单行注释 |
| /* */ | 多行注释 |
| # | MySQL特有的单行注释 |
SQL的分类
说明：我们重点学习DML与DQL。
| 分类 | 说明 |
|---|---|
| 数据定义语言 | DDL，Data Definition Language，用来定义数据库对象：数据库、表、列等，包括：create、drop、alter、truncate，不支持事物 |
| 数据操作语言 | DML，Data Manipulation Language，用来对数据库中表的记录进行更新，包括：insert、delete、update、select（DQL），支持事务 |
| 数据查询语言 | DQL，Data Query Language，用来查询数据库中表的记录，只包括select |
| 事务控制语言 | TCL，Transaction Control Language，包括：begin、commit、rollback、savepoint 回滚点、rollback to 回滚点 |
| 数据控制语言 | DCL，Data Control Language，用来定义数据库的访问权限和安全级别， 及创建用户 |
数据库管理系统、数据库和表的关系如图所示：
MySQL存储引擎
1.MyISAM
-
- 优势：速度快，磁盘空间占用少；某个库或表的磁盘占用状况既能够经过操作系统查相应的文件（夹）的大小得知，也能够经过SQL语句`SHOW TABLE STATUS`查得。 - 缺点：没有数据完整性机制，即不支持事务和外键。
2.InnoDB
-
- 优势：支持事务和外键，数据完整性机制比较完备；能够用`SHOW TABLE STATUS`查得某个库或表的磁盘占用。 - 缺点：速度超慢，磁盘空间占用多；全部库都存于一个（一般状况）或数个文件中，没法经过操作系统了解某个库或表的占用空间。
MysQL操作
创建数据库
| 命令 | 说明 |
|---|---|
| CREATE DATABASE 数据库名； | 创建指定名称数据库 |
| CREATE DATABASE IF NOT EXISTS 数据库名； | 判断数据库是否存在，不存在则创建数据库 |
| CREATE DATABASE 数据库名 CHARACTER SET 字符集； | 创建指定名称的数据库，并且指定字符集（一般都指定utf8） |
查看数据库
| 命令 | 说明 |
|---|---|
| SHOW DATABASES; | 查看MySQL中都有哪些数据库 |
| SHOW CREATE DATABASE 数据库名； | 查看一个数据库的定义信息 |
修改数据库
修改数据库默认字符集。
| 命令 | 说明 |
|---|---|
| ALTER DATABASE 数据库名 CHARACTER SET 字符集； | 数据库的字符集修改操作 |
删除数据库
| 命令 | 说明 |
|---|---|
| DROP DATABASE 数据库名； | 从MySQL中永久的删除某个数据库 |
使用数据库
| 命令 | 说明 |
|---|---|
| USE 数据库； | 使用/切换数据库 |
| SELECT DATABASE(); | 查看当前正在使用的数据库 |
操作数据表
MySQL数据类型
常见MySQL数据类型
类型 描述 int 整数 double 浮点数 varchar 字符串型 date 日期类型，格式为yyyy-MM-dd，只有年月日，没有时分秒
char与varchar区别：
MySQL中的char类型与var插入类型，都对应了java中字符串类型，区别在于：
创建表
语法格式
CREATE TABLE 表名( 字段名称 字段长度(长度), 字段名称 字段长度(长度) );
注意：最后一列不要加逗号。
关键字说明
关键字 说明 CREATE 创建 TABLE 表
快速创建一个表结构相同的表(复制表结构)
语法格式
CREATE TABLE 新表名 LIKE 旧表明;
查看表结构
DESC 表明;
COMMENT注释
语法格式
CREATE TABLE 表明( 字段名称 字段类型 COMMENT '字段的注释' )COMMENT = '表注释';
查看表
| 命令 | 说明 |
|---|---|
| SHOW TABLES; | 查询当前数据库中的所有表名 |
| DESC 表明; | 查看数据表结构 |
| SHOW CREATE TABLE 表名； | 查看创建表的SQL语句 |
删除表
| 命令 | 说明 |
|---|---|
| DROP TABLE 表名； | 删除表(从数据库中永久删除某一张表) |
| DROP TABLE IF EXISTS 表名； | 判断表是否存在，存在的话就删除，不存在就不执行删除 |
修改表
修改表名
RENAME TABLE 旧表名 TO 新表名;
修改表的引擎和字符集
ALTER TABLE 表名 CHARACTER SET 字符集; ALTER TABLE 表名 ENGINE=引擎类型 CHARASET=字符集;
向表中添加列，关键字ADD
#添加在最后面 ALTER TABLE 表名 ADD 字段名称 字段类型; #添加在最前面 ALTER TABLE 表名 ADD 字段名称 字段类型 FIRST; #添加在xxx字段的后面 ALTER TABLE 表名 ADD 字段名称 字段类型 AFTER xxx;
修改表中列的数据类型或长度或字段位置，关键字MODIFY。
# 默认字段追加在最后 ALTER TABLE 表名 MODIFY 字段名称 新字段类型; # 将字段添加到第一个位置 ALTER TABLE 表名 MODIFY 字段名称 新字段类型 FIRST; # 将字段添加到指定字段后面 ALTER TABLE 表名 MODIFY 字段名称 新字段类型 AFTER xxx;
修改列名称，关键子CHANGE。
ALTER TABLE 表名 CHANGE 旧列名 新列名 新类型;
删除列，关键字DROP。
ALTER TABLE 表名 DROP 列名;
修改表和列的注释，关键字COMMENT。
# 修改表注释 ALTER TABLE 表名 COMMENT '表的注释内容'; # 修改列注释 ALTER TABLE 表名 MODIFY COLUMN 字段名称 字段类型 COMMENT '字段的注释内容';
DML操作表中的数据
插入数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (字段值1, 字段值2, ...);
表名：student 表中字段： 学员id，sid int 姓名， sname varchar(20) 年龄， age int 性别， sex char(1) 地址， address varchar(40) # 创建学生表 CREATE TABLE student( sid INT, sname VARCHAR(20), age INT, sex CHAR(1), address VARCHAR(40) );1.插入全部字段， 将所有字段名都写出来。要求值的数量和顺序必须和表中字段的数量和顺序保持一致。
INSERT INTO student (sid, sname, age, sex, address) VALUES (1, '孙悟空', 20, '男', '花果山');2.插入全部字段，不写字段名。
INSERT INTO student VALUES (2, '孙悟饭', 10, '男', '地球');3.插入指定字段的值。
INSERT INTO category (sid, cname) VALUES (3, '白骨精');4.批量插入数据。
INSERT INTO student VALUES (4, '张三', 10, '男', '陕西'), (5, '李四', 20, '女', '湖南');
插入语法说明：
编码问题
1.如果在终端执行包含中文SQL语句报错。解决方案：
set names gbk;2.如果执行SQL插入中文没有报错，但是查询的结果出现乱码。解决方案：把数据库删除，重新创建数据库并确保数据库字符集为utf8，数据库中表的字符集为utf8，然后再次测试；如果还是解决不了数据库编码问题，则重新安装数据库。
更改数据
语法格式1：不带条件的修改。
UPDATE 表名 SET 列名=值语法格式2：带条件的修改。
UPDATE 表名 SET 列名=值 [WHERE 条件表达式]1.不带条件修改，将所有的性别改为女（慎用）。
UPDATE student SET sex='女';2.带条件的修改，将sid为3的学生，性别改为男。
UPDATE student SET sex='男' WHERE sid=3;3.一次修改多个列，将sid为2的学员，年龄改为20，地址改为“北京”。
UPDATE student SET age=20, address='北京' WHERE sid=2;
删除数据
语法格式1：删除所有数据。
DELETE FROM 表名语法格式2： 指定条件删除数据。
DELETE FROM 表名 [WHERE 条件表达式]1.删除sid为1的数据。
DELETE FROM student WHERE sid=1;2.删除所有数据。
# 方式1 DELETE FROM student; # 方式2 TRUNCATE TABLE student;3.如果要删除表中的所有数据，有两种做法。
命令 说明 delete from 表名; 不推荐，有多少条记录就执行多少次删除操作，效率低 truncate table 表名; 推荐，先删除整张表，然后再重新创建一张一模一样的表，效率高
DQL查询表中的数据
简单查询
查询表所有行和列的数据
使用*表示所有列。
SELECT * FROM 表名;说明：你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据。
查询指定列
查询指定列的数据，多个列之间以逗号分隔。
SELECT 字段名 1, 字段名 2, 字段名 3, ... FROM 表名;
指定列的别名进行查询
使用别名的好处：显示的时候使用新的名字，并不修改表的结构。
语法格式：别名查询，使用AS关键字。
对列指定别名：
SELECT 字段名1 AS 别名1, 字段名2 AS 别名2... FROM 表名;对列和表同时指定别名：
SELECT 字段名1 AS 别名1, 字段名2 AS 别名2... FROM 表名 AS 表别名;
清除重复值
查询指定列并且结果不出现重复数据。
SELECT DISTINCT 字段名 FROM 表名;
查询结果参与运算
某列数据和固定值运算。
SELECT 列名1+固定值 FROM 表名;
某列数据和其他列数据参与运算。
SELECT 列名1+列名2 FROM 表名;
注意：参与运算的必须是数值类型。
条件查询
为什么要条件查询
如果查询语句中没有设置条件，就会查询所有的行信息，在实际应用中，一定要指定查询条件，对记录进行过滤。
条件查询的语法
语法格式：
SELECT 列名 FROM 表名 WHERE 条件表达式;说明：先取出表中的每条数据，满足条件的数据就返回，不满足的就过滤掉。
比较运算符
运算符 说明 > < <= >= = <> != 大于、小于、大于（小于）等于、不等于；<>在SQL中表示不等于，在MySQL中也可以使用!=，没有== IS NULL 查询某一列为NULL的值，注意不能写成=NULL
NULL运算符
运算符 说明 IS NULL 查询某一列为NULL的值，注意不能写成 字段名称=NULLIS NOT NULL 查询某一列不为NULL的值
逻辑运算符
运算符 说明 AND 或 && 与，多个条件同时成立；SQL中建议使用前者，后者并不通用 OR 或 || 或，多个条件任一成立 NOT 或 ! 非，不成立，即取反
IN关键字
语法格式：
SELECT 字段名 FROM 表名 WHERE 字段名 IN (数据1, 数据2, ...);IN里面的每个数据都会作为一次条件，只要满足条件的就会显示。
范围查询
语法格式：表示从“值1”到“值2”范围，包头又包尾。
BETWEEN 值1 AND 值2
LIKE关键字
LIKE表示模糊查询。
SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';
模糊查询通配符。
通配符 说明 % 表示匹配任意多个字符（替代0个或多个字符） _ 表示匹配一个字符（替代一个字符）
MySQL单表&约束&事务
学习目标
-
- 掌握单表的查询操作（基于一张表） - 掌握数据库的约束 - 掌握数据库的事务（控制安全）
DQL操作单表
数据库间表复制
1.创建一个新的数据库db2。
CREATE DATABASE db2 CHARACTER SET utf8;2.将db1数据库中的emp表复制到当前db2数据库中。
3.在Navicat Premium窗口中，选择【工具】选项下的【数据传输】，在新打开的窗口中将“源”数据库中的表，通过“连接”复制到“目标”数据库中。
排序
通过order by 关键字来完成。指定对应的的查询结果按照升序或者降序的方式来展示数据，不影响数据库中的数据。语法结构：
select * from 表名 [where 字段=条件] order by 字段名称 asc或者desc解释说明：
排序方式
单列排序
只按照某一个字段进行排序，就是单列排序。
需求：使用salary字段，对emp表数据进行排序（升序/降序）。
-- 默认升序排序ASC SELECT * FROM emp ORDER BY salary; -- 降序排序 SELECT * FROM emp ORDER BY salary DESC;组合排序
同时对多个字段进行排序，如果第一个字段相同就按照第二个字段进行排序，以此类推。
需求：在薪水salary排序的基础上，再使用eid进行排序，如果薪水相同就以eid做降序排序。
-- 组合排序 SELECT * FROM emp ORDER BY salary DESC, eid DESC;如果第一个字段排序能够确定数据的先后顺序，则第二个字段不参与排序，反之则参与排序。
聚合函数
之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对某一列的值进行计算，然后返回一个单一的值（另外聚合函数会忽略NULL空值）。
聚合函数
语法结构：
select 聚合函数(字段名称) from 表名;常用的聚合函数：
聚合函数 作用 count(字段) 统计指定列不为NULL的记录行数 sum(字段) 计算指定列的数值和 max(字段) 计算指定列的最大值 min(字段) 计算指定列的最小值 avg(字段) 计算指定列的平均值 instr() 函数返回字符串中子字符串第一次出现的位置。如果在str中找不到子字符串，则instr()函数返回零 group_concat() 将字符串从分组中连接成具有各种选项（如DISTINCT，ORDER BY和SEPARATOR）的字符串 对多条数据进行统计查询，统计平均值、最大值、最小值、求和、计数。
instr()函数
INSTR函数简介
有时，想要在字符串中查找子字符串或检查字符串中是否存在子字符串。在这种情况下，可以使用字符串内置INSTR()函数。 INSTR()函数返回字符串中子字符串第一次出现的位置。如果在str中找不到子字符串，则INSTR()函数返回零。INSTR函数不区分大小写。INSTR(str, substr);INSTR函数接受两个参数：
INSTR函数示例
返回"MySQL INSTR"字符串中的子字符串"SQL"的位置。
# 返回结果为3 SELECT INSTR('MySQL INSTR', 'SQL'); # 返回结果为3，INSTR函数不区分大小写 SELECT INSTR('MySQL INSTR', 'sql');要强制INSTR函数根据以区分大小写的方式进行搜索，请按如下所示使用BINARY运算符。
# 返回结果为0 SELECT INSTR('MySQL INSTR', BINARY 'sql');
INSTR函数与LIKE运算符
1.假设要查找名称包含Car关键字的产品，可以使用INSTR函数。
SELECT productName FROM products WHERE INSTR(productName, 'Car') > 0;2.除了INSTR函数，可以使用LIKE运算符来匹配Car模式。
SELECT productName FROM products WHERE productname LIKE '%Car%';两个查询返回相同的结果。那么哪一个更快，INSTR还是LIKE操作符？答案是它们是一样的，它们都区分大小写，并执行全表扫描。
3.对products表的productName列创建一个索引。
CREATE INDEX idx_products_name ON products(productName);如果使用具有前缀搜索的LIKE运算符，则在此索引列上，LIKE运算符的执行速度要比INSTR函数快。
4.使用LIKE关键字执行查询分析。
EXPLAIN SELECT productName FROM products WHERE productName LIKE '1900%';5.使用INSTR函数执行查询分析。
EXPLAIN SELECT productName FROM products WHERE instr(productName, '1900');即使productName列具有索引，INSTR函数也执行表扫描。 这是因为MySQL不能对INSTR函数的语义做任何假设，MySQL可以利用其对LIKE运算符语义的理解。 测试字符串中是否存在子字符串的最快方法是使用全文索引。 但是，需要正确配置和维护索引。
group_concat()函数
MySQL的GROUP_CONCAT()函数将组中的字符串连接成为具有各种选项的单个字符串。
下面说明了GROUP_CONCAT()函数的语法：
GROUP_CONCAT(DISTINCT expression ORDER BY expression SEPARATOR sep);
DISTINCT子句用于在连接分组之前消除组中的重复值。 ORDER BY子句允许您在连接之前按升序或降序排序值。默认情况下，它按升序排序值。 如果要按降序对值进行排序，则需要明确指定DESC选项。 SEPARATOR指定在组中的值之间插入的文字值。如果不指定分隔符，则GROUP_CONCAT函数使用逗号（,）作为默认分隔符。 GROUP_CONCAT函数忽略NULL值，如果找不到匹配的行，或者所有参数都为NULL值，则返回NULL。 GROUP_CONCAT函数返回二进制或非二进制字符串，这取决于参数。 默认情况下，返回字符串的最大长度为1024。如果您需要更多的长度，可以通过在SESSION或GLOBAL级别设置group_concat_max_len系统变量来扩展最大长度。
GROUP_CONCAT函数常见错误
GROUP_CONCAT函数返回单个字符串，而不是值列表。 这意味着不能在IN操作符中使用GROUP_CONCAT函数的结果，
分组
GROUP BY
根据一个或者多个列的结果进行数据的分组，可以使用MySQL关键字group by来实现分组查询。通常结合聚合函数来使用。
语法格式：
select 分组字段名称/聚合函数 from 表名 group by 分组字段1,分组字段2....;注意：分组时可以查询要分组的字段，或者使用聚合函数进行统计操作。使用*查询其他字段没有意义。
HAVING
having表示条件的过滤（功能和where类似），需要和group by结合一块儿使用，having是将分完组后的数据进行过滤。
select 分组字段/聚合函数 from 表名 group by 字段1,字段2,... having 多个过滤条件;
函数：extract用来截取字符串的内容。
extract(截取的内容 from 目标字段)
where和having区别
通常在WHERE后面写普通字段的条件，而HAVING后面也可以写普通字段的条件，但是不推荐。WHERE与HAVING的区别：
过滤方式 特点 Where 在分组前进行过滤，where关键字中不可以声明聚合函数 having 在分组后进行过滤，having关键字中可以声明聚合函数
limit分页
limit关键字的作用：
语法结构：
select * from 表名 limit offset, length； 分页公式：起始索引(offset取值) = (当前页-1) * 每页条数参数说明：
各种关键字的顺序：
select 字段列表,... from 表名 where 条件表达式 group by 分组字段 having 条件表达式 order by 字段的排序 limit offset, length
SQL约束
约束（constaint）：表示表中的数据添加条件限制，只有符合条件的数据才能够进行存储。
SQL约束介绍
1.约束的作用：
对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性。违反约束的不正确数据，将无法插入到表中。2.常见的约束：
约束名 约束关键字 含义 默认值约束 default 某列的默认值，如在数据库里有一项数据很多重复，可以设为默认值 非空约束 not null 限定某个字段/某列的值不允许为空 唯一性约束 unique 值不可重复，允许为空，但只能出现一个空值 检查约束 check 某列取值范围限制，格式限制等，如有关年龄、邮箱（必须有@）的约束 主键约束 primary key 要求主键列数据唯一，并且不允许为空 外键约束 foreign key 外键是另一表的主键，常用来和其他表建立联系
默认值约束
默认值约束对指定的列设置默认数据的取值，如果数据库没有指定该字段的值，它就会将默认值插入到数据库中。语法格式：
字段名称 数据类型 default 默认值
主键约束
主键：就是表中用来唯一标记一条记录的，可以通过这个主键来获取指定的记录。 主键约束：要求主键列的数据取值唯一（是不能够重复取值），并且不能够为null。
添加主键约束
语法格式：
# 1.字段定义添加主键 字段名称 字段类型 primary key # 2.在表结构定义末尾位置添加主键 primary key(字段名称) # 3.在已存在的表通过修改表结构添加主键 alter table 表名 add primary key (字段名称)哪些字段可以作为主键 ?
通常针对业务去设计主键，每张表都设计一个主键id。主键是给数据库和程序使用的，跟最终的客户无关，所以主键没有意义没关系，只要能够保证不重复就好，比如身份证号就可以作为主键。
删除主键约束
语法结构：
alter table 表名 drop primary key;删除表中的主键约束：
-- 使用DDL语句删除表中的主键 ALTER TABLE emp3 DROP PRIMARY KEY; DESC emp3;语法解析：
主键的自增
主键如果让我们自己添加很有可能重复，我们通常希望在每次插入新记录时，数据库自动生成主键字段的值。主键自增介绍
当主键定义为自增长后，那么这个主键的值就不需要用户输入数据，而由数据库系统根据定义自动赋值。当增加一条记录时，主键自动的以相同的步长来进行增长，这个步长是在建表的时候用户自己定义的。一般自增长数据只能是数值类型。语法格式：关键字AUTO_INCREMENT表示自动增长（字段类型必须是整数类型）。
字段名称 字段类型 primary key auto_increment;
修改主键自增起始值
默认地AUTO_INCREMENT的开始值是1，如果希望修改起始值，请使用下面的方式。
-- 创建主键自增的表 ,自定义自增其实值 CREATE TABLE emp5( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), sex CHAR(1) ) AUTO_INCREMENT=100; -- 插入数据 ,观察主键的起始值 INSERT INTO emp5 (ename, sex) VALUES ('张百万', '男'); INSERT INTO emp5 (ename, sex) VALUES ('艳秋', '女');
DELETE和TRUNCATE对自增的影响
删除表中所有数据有两种方式。
清空表数据的方式 特点 Delete from 表名; 只删除数据，自增没有影响 truncate table 表名; 删除表，然后重新创建表，自增重新开始计数
非空约束
非空约束表示某一列的字段取值不允许为空（null）。语法格式：
字段名称 字段类型 not null
唯一约束
唯一约束：表示表中的字段取值不能够重复（null不参与判断）。语法格式：
字段名称 字段类型 unique主键约束与唯一约束的区别：
外键约束
什么是外键
1.外键指的是在从表中与主表的主键对应的那个字段；比如员工表的dept_id，就是外键。
2.使用外键约束可以让两张表之间产生一个对应关系，从而保证主从表的引用的完整性。
3.多表关系中的主表和从表：
4.外键约束关系建立好之后，被关联的数据不能先删除，被关联的表不能先删除。
创建外键约束
1.语法格式
1.新建表时添加外键约束：
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表 (主键字段名);2.在已有表添加外键约束：
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主键字段名);
添加外键约束，就会产生强制性的外键数据检查，从而保证了数据的完整性和一致性。
删除外键约束
1.语法格式：
ALTER TABLE 从表 DROP FOREIGN KEY 外键约束名称;2.删除外键约束案例。
-- 删除employee表中的外键约束，外键约束名emp_dept_fk ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;外键约束注意事项
1.从表外键类型必须与主表主键类型一致否则创建失败。
错误代码：1215 Cannot add foreign key constraint2.添加数据时，应该先添加主表中的数据。
-- 添加一个新的部门 INSERT INTO department (dep_name, dep_location) VALUES ('市场部', '北京'); -- 添加一个属于市场部的员工 INSERT INTO employee (ename, age, dept_id) VALUES ('老胡', 24, 3);3.删除数据时，应该先删除从表中的数据。
-- 删除数据时应该先删除从表中的数据 -- 报错Cannot delete or update a parent row: a foreign key constraint fails -- 报错原因不能删除主表的这条数据，因为在从表中有对这条数据的引用 DELETE FROM department WHERE id = 3; -- 先删除从表的关联数据 DELETE FROM employee WHERE dept_id = 3; -- 再删除主表的数据 DELETE FROM department WHERE id = 3;
数据库事务
事务
什么是事务
在数据库中事务是一个整体，是由多条SQL语句组成的一个整体，这些SQL语句，要么全部之行成功，要么全部执行失败。整个操作中，如果有任意提条SQL出现异常，整个业务就视为失败，提交成功的SQL语句要进行回滚。 数据库的引擎分为两种类型：MyISAM（不支持事务）、InnoDB（支持事务，默认值）。 在MySQL中只有使用了InnoDB数据库引擎的数据库才支持事务。事务用来管理INSERT、UPDATE、DELETE语句。事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。
什么是回滚
即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，滚回到事务开始时的状态（在提交之前执行）。
MySQL事务操作
数据库事务的操作：
手动提交事务
语法格式
功能 语句 BEGIN或START TRANSACTION 开启事务 COMMIT 提交事务 ROLLBACK 回滚事务 SAVEPOINT identifier savepoint允许在事务中创建一个保存点，一个事务中可以有多个savepoint RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常 ROLLBACK TO identifier 把事务回滚到标记点 SET transaction 用来设置事务的隔离级别 START TRANSACTION：这条语句用于显式地标记一个事务的起始点。 COMMIT：表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。 ROLLBACK：表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态。
手动提交事务流程
执行成功的情况：开启事务 -> 执行多条SQL语句 -> 成功提交事务 执行失败的情况：开启事务 -> 执行多条SQL语句 -> 事务的回滚
案例演示**
需求：模拟张三给李四转500元钱。
1.执行以下SQL。
-- 1.使用db2数据库 USE db2; -- 2.开启事务 start transaction; -- 3.tom账户-500 update account set money = money - 500 where name = 'tom'; -- 4.jack账户+500 update account set money = money + 500 where name = 'jack';2.此时我们使用Navicat Premium查看表，发现数据并没有改变。
3.在控制台执行commit提交事务。
commit;4.再次使用Navicat Premium查看，发现数据在事务提交之后发生改变。
4.事务回滚演示
如果事务中，有某条SQL语句执行时报错了，我们没有手动的commit，那整个事务会自动回滚。1.命令行开启事务。
start transaction;2.插入两条数据。
INSERT INTO account VALUES (NULL, '张百万', 3000); INSERT INTO account VALUES (NULL, '有财', 3500);3.不去提交事务，直接关闭窗口，发生回滚操作，数据没有改变。
注意：如果事务中SQL语句没有问题，commit提交事务，会对数据库数据的数据进行改变。如果事务中SQL语句有问题， rollback回滚事务，会回退到开启事务时的状态。5.回滚点演示
1.保存回滚点。
BEGIN; UPDATE account SET money=money+1000 WHERE id=2; SAVEPOINT sp1; UPDATE account SET money=money+1000 WHERE id=2; SAVEPOINT sp2; UPDATE account SET money=money+1000 WHERE id=2;2.回滚到指定的回滚点。
ROLLBACK TO sp2;3.提交事务。观察id为2的账户资金取值。
# 账户资金+2000 COMMIT;
自动提交事务
MySQL默认每一条DML（增删改）语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕自动提交事务。MySQL默认是自动提交事务。
1.自动提交事务
1.将tom账户金额+500元。
# MySQL中默认每一条DML语句，都是一个独立的事务，默认自动开启事务，默认自动提交事务 update account set money = money + 500 where name='tom';2.使用Navicat Premium查看数据库，发现数据已经改变。
2.取消自动提交
MySQL默认是自动提交事务，可通过autocommit变量名设置事务的默认提交状态。
autocommit变量名取值含义：
属性值 功能描述 on 事务自动提交 off 事务手动提交 1.登录MySQL，通过autocommit查看事务默认提交状态。
SHOW VARIABLES LIKE 'autocommit';2.把autocommit改成OFF，即手动提交。
SET @@autocommit=OFF;3.再次修改jack账户金额-500元，需要提交之后才能生效。
-- 选择数据库 use db2; -- 修改数据 update account set money = money - 500 where name = 'jack'; -- 手动提交（不提交数据不会改变） commit;
事务的四大特性ACID
特 性 含义 原 子 性 Atomicity，每个事务都是一个整体，不可再拆分，事务中所有的SQL语句要么都执行成功，要么都失败 一 致 性 Consistency，事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2个人的总金额是2000，转账后2个人总金额也是2000 隔 离 性 Isolation，数据库允许多个并发事务同时对其数据进行读写和修改的操作，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致 持 久 性 Durability，一旦事务执行成功，对数据库的修改是持久的。就算关机，数据也是要保存下来的
MySQL事务隔离级别
数据并发访问
一个数据库可能拥有多个访问客户端，这些客户端都可以并发方式访问数据库。数据库的相同数据可能被多个事务同时访问，如果不采取隔离措施，就会导致各种问题，破坏数据的完整性。
并发访问会产生的问题
事务在操作时的理想状态：所有的事务之间保持隔离、互不影响。但是，因为并发操作，多个用户同时访问同一个数据。可能引发并发访问的问题。
并发访问问题 说明 脏读 一个事务读取到了另一个事务中尚未提交的数据 不可重复读 一个事务中两次读取的数据内容不一致，要求的是在一个事务中多次读取时数据是一致的。这是进行update操作时引发的问题 幻读 一个事务中，某一次的select操作得到的结果所表征的数据状态，无法支撑后续的业务操作。查询得到的数据状态不准确，导致幻读
四种隔离级别
通过设置隔离级别，可以防止上面的三种并发问题。
MySQL数据库有四种隔离级别，上面的级别最低，下面的级别最高。
✔ 会出现问题
✘ 不会出现问题
级 别 名字 隔离级别 脏 读 不可重复读 幻 读 数据库的默认隔离级别 1 读未提交 read uncommitted ✔ ✔ ✔ 2 读已提交 read committed ✘ ✔ ✔ Oracle和SQL Server 3 可重复读 repeatable read ✘ ✘ ✔ MySQL 4 串行化 serializable ✘ ✘ ✘
隔离级别相关命令
1.查看隔离级别。
select @@tx_isolation;说明：MySQL的默认隔离级别是可重复读（REPEATABLE-READ），可以防止脏读、不可重复读，但不能防止幻读。
2.设置事务隔离级别。需要退出MySQL，再重新登录才能看到隔离级别的变化。
set global transaction isolation level 事务隔离级别; read uncommitted 读未提交 read committed 读已提交 repeatable read 可重复读 serializable 串行化3.例如：修改隔离级别为读未提交。
set global transaction isolation level read uncommitted;
隔离性问题演示
脏读演示
脏读：一个事务读取到了另一个事务中尚未提交的数据。
1.打开窗口登录MySQL，设置全局的隔离级别为最低。
# 1.登录MySQL数据库 # 2.使用db2数据库 use db2; # 3.设置隔离级别为最低，读未提交 set global transaction isolation level read uncommitted;2.关闭窗口，开一个新的窗口A，再次查询隔离级别。
# 1.开启新的窗口A # 2.查询隔离级别 select @@tx_isolation; -- 已修改为读未提交：READ-UNCOMMITTED3.再开启一个新的窗口B。
# 1.登录MySQL数据库 # 2.选择数据库 use db2; # 3.开启事务 start transaction; # 4.查询 select * from account; -- 查询数据，本次查询用户的账户余额都为10004.窗口A执行SQL语句。
# 1.选择数据库 use db2; # 2.开启事务 start transaction; # 3.执行修改操作 -- tom账户-500元 UPDATE account SET money = money - 500 WHERE NAME = 'tom'; -- jack账户+500元 UPDATE account SET money = money + 500 WHERE NAME = 'jack'; # 4.窗口A开启事务，对数据进行修改后，但是不提交事务5.窗口B查询数据。
# 查询账户信息 select * from account; -- 窗口B中，查询到了窗口A未提交的数据，出现了脏读6.窗口A转账异常，进行回滚。
# 窗口A的事务执行，出现异常，进行回滚 rollback;7.窗口B再次查询账户。
# 由于窗口A执行出现异常，对事务进行了回滚，窗口B再次查询时，发现和上次查询结果不同 select * from account;
解决脏读问题
脏读是非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入500块，然后打电话给李四说钱已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。解决方案：将全局的隔离级别进行提升为：read committed。
1.在窗口A设置全局的隔离级别为read committed。
set global transaction isolation level read committed;2.重新开启窗口A，查看设置是否成功。
select @@tx_isolation;3.开启窗口B，A和B窗口选择数据库后，都开启事务。
# 窗口A -- 1.选择数据库 use db2; -- 2.开启事务 start transaction; # 窗口B -- 1.选择数据库 use db2; -- 2.开启事务 start transaction;4.窗口A只是更新两个人的账户，不提交事务。
-- tom账户-500元 UPDATE account SET money = money - 500 WHERE NAME = 'tom'; -- jack账户+500元 UPDATE account SET money = money + 500 WHERE NAME = 'jack';5.窗口B进行查询，没有查询到未提交的数据。
# 窗口B事务未读取到窗口A事务中尚未提交的数据 select * from account;6.窗口A通过commit提交事务的数据。
commit;7.窗口B再次进行数据的查询。
# 窗口B事务在窗口A事务提交之后，才能看到修改后的数据 select * from account;
不可重复读演示
不可重复读：同一个事务中，进行查询操作，但是每次读取的数据内容是不一样的。
1.不可重复读问题演示
1.恢复数据（把数据改回初始状态）。
update account set money=1000;2.打开两个窗口A和窗口B，分别进行选择数据库后并开启事务。
# 窗口A use db2; start transaction; # 窗口B use db2; start transaction;3.窗口B开启事务后，先进行一次数据查询。
# 窗口B的事务第一次查询结果（tom账户金额为1000、jack账户金额为1000） select * from account;4.在窗口A开启事务后，将用户tom的账户+500，然后提交事务。
-- 修改数据 update account set money = money + 500 where name = 'tom'; -- 提交事务 commit;5.窗口B再次查询数据。
# 窗口B的事务第二次查询结果（tom账户金额为1500、jack账户金额为1000） select * from account; # 同一个事务中，两次查询的结果不一致，出现了不可重复读的问题6.两次查询输出的结果不同，到底哪次是对的？
解决不可重复读问题
将全局的隔离级别进行提升为：repeatable read。
1.恢复数据。
UPDATE account SET money = 1000;2.打开窗口A，设置隔离级别为：repeatable read。
-- 查看事务隔离级别 select @@tx_isolation; -- 设置事务隔离级别为repeatable read set global transaction isolation level repeatable read;3.重新开启A、B窗口，选择数据库，同时开启事务。
# 窗口A use db2; start transaction; # 窗口B use db2; start transaction;4.窗口B事务先进行第一次查询。
# 窗口B的事务第一次查询结果（tom账户金额为1000、jack账户金额为1000） select * from account;5.窗口A更新数据，然后提交事务。
-- 修改数据 update account set money = money + 500 where name = 'tom'; -- 提交事务 commit;6.窗口B再次查询数据。
# 窗口B的事务第二次查询结果（tom账户金额为1000、jack账户金额为1000） select * from account; -- 窗口B再次查询，数据跟上次保持一致，没有出现不可重复读问题同一个事务中为了保证多次查询数据一致，必须使用repeatable read隔离级别。MySQL的默认隔离级别就是repeatable read。
幻读演示
幻读：SELECT查询某条记录是否存在，不存在，则准备插入此条记录；但执行INSERT插入时，发现此条记录已经存在，无法插入，此时就发生了幻读。
1.幻读问题演示
1.打开A、B窗口，选择数据库并开启事务。
# 窗口A use db2; start transaction; # 窗口B use db2; start transaction;2.窗口A先执行一次查询操作。
# 假设要再添加一条id为3的数据，在添加之前先判断是否存在 select * from account where id = 3; -- 查询结果为：不存在此条记录，则表示可以插入3.窗口B插入一条数据，然后提交事务。
# 窗口B插入一条，主键id为3的数据 INSERT INTO account VALUES(3, 'lucy', 1000); commit;4.窗口A执行插入操作，发现报错，出现幻读。
INSERT INTO account VALUES(3, 'lucy', 1000);见鬼了，我刚才读到的结果应该可以支持我这样操作才对啊，为什么现在不可以了呢？
解决幻读问题
将事务隔离级别设置到最高SERIALIZABLE ，以挡住幻读的发生。如果一个事务使用了SERIALIZABLE——可串行化隔离级别时，在这个事务没有被提交之前 , 其他的线程，只能等到当前操作完成之后，才能进行操作，这样会非常耗时，而且影响数据库的性能，数据库不会使用这种隔离级别。1.恢复数据。
DELETE FROM account WHERE id = 3; UPDATE account SET money = 1000;2.打开窗口A将数据隔离级别提升到最高。
set global transaction isolation level SERIALIZABLE;3.打开A、B窗口，选择数据库并开启事务。
# 窗口A use db2; start transaction; # 窗口B use db2; start transaction;4.窗口A先执行一次查询操作。
SELECT * FROM account WHERE id = 3;5.窗口B插入一条数据。
# 这条操作无法进行，光标一直在闪烁 INSERT INTO account VALUES(3, 'lucy', 1000);6.窗口A执行插入操作，提交事务数据插入成功。
# 窗口A数据插入成功，没有出现幻读 INSERT INTO account VALUES(3, 'lucy', 1000); commit;7.窗口B在窗口A提交事务之后，再执行，但是主键冲突出现错误。
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'总结：serializable串行化可以彻底解决幻读，但是事务只能排队执行，严重影响效率，数据库不会使用这种隔离级别。
MySQL单表&约束&事务小结
DQL
Database Query Language，数据查询语言。
排序：order by
聚合函数：用来操作数据库中的列。提供了很多内置函数：
分组：
group by，根据字段来进行分组。结合聚合函数使用。having：
表示对分组的结果进行过滤操作。结合group by分组来一块儿使用。limit: 分页查询。
limit (页码数-1)*页的大小, 页的大小SQL约束
SQL约束分类：
约束的作用：
MySQL数据的事务
事务：表示将多条SQL语句作为一个整体，要么全部执行成功，要么全部执行失败。
事务控制方式：开启事务、提交事务、回滚事务。
事务的三大经典问题：
解决的方法：设置数据库的访问隔离级别。
# MySQL数据库默认数据库的访问隔离级别：repeatable read # 读未提交：read uncommitted # 读已提交：read committed # 可重复读（MySQL默认访问隔离级别）：repeatable read # 串行化：serializable设置语法：
# 语法 set global TRANSACTION ISOLATION LEVEL 事务个隔离级别;
MySQL多表、外键和数据库设计
学习目标
-
- 学习和掌握MySQL的多表设计 - 掌握MySQL的外键：概念、外键约束 - 设计表与表之间的关系：一对一、一对多、多对多 - 子查询：表连接查询 - 数据库设计的三范式
单表设计问题
创建一个数据库db3。
CREATE DATABASE db3 CHARACTER SET utf8;2.数据库中创建一个员工表emp。
包含如下eid、ename、age、dep_name、dep_location字段信息；
eid作为主键并自动增长，然后添加5条数据。
-- 创建emp表，=主键自增 CREATE TABLE emp( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), age INT , dept_name VARCHAR(20), dept_location VARCHAR(20) ); -- 添加数据 INSERT INTO emp (ename, age, dept_name, dept_location) VALUES ('张百万', 20, '研发部', '广州'); INSERT INTO emp (ename, age, dept_name, dept_location) VALUES ('赵四 ', 21, '研发部', '广州'); INSERT INTO emp (ename, age, dept_name, dept_location) VALUES ('广坤 ', 20, '研发部', '广州'); INSERT INTO emp (ename, age, dept_name, dept_location) VALUES ('小斌', 20, '销售部', '深圳'); INSERT INTO emp (ename, age, dept_name, dept_location) VALUES ('艳秋', 22, '销售部', '深圳'); INSERT INTO emp (ename, age, dept_name, dept_location) VALUES ('大玲子', 18, '销售部', '深圳');1.1.2 单表设计问题
数据冗余，同一个字段中出现大量的重复数据。
SELECT * FROM emp;
多表
多表简述
实际项目开发中，一个项目通常需要有很多张表才能完成。例如，一个商城项目的数据库，需要有很多张表：用户表、分类表、商品表、订单表等。
1.多表方式设计：
2.删除emp表，重新创建两张表。
# 创建部门表。一方，主表 CREATE TABLE department( id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(30), dept_location VARCHAR(30) ); # 创建员工表。多方，从表 CREATE TABLE employee( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), age INT, dept_id INT );3.添加部门表数据。
-- 添加两个部门 INSERT INTO department VALUES (NULL, '研发部', '广州'), (NULL, '销售部', '深圳'); SELECT * FROM department;4.添加员工表数据。
-- 添加员工，dep_id表示员工所在的部门 INSERT INTO employee (ename, age, dept_id) VALUES ('张百万', 20, 1); INSERT INTO employee (ename, age, dept_id) VALUES ('赵四', 21, 1); INSERT INTO employee (ename, age, dept_id) VALUES ('广坤', 20, 1); INSERT INTO employee (ename, age, dept_id) VALUES ('小斌', 20, 2); INSERT INTO employee (ename, age, dept_id) VALUES ('艳秋', 22, 2); INSERT INTO employee (ename, age, dept_id) VALUES ('大玲子', 18, 2); SELECT * FROM employee;由于表设计不够合理，导致出现大量重复数据，称为数据冗余，通过合理拆分表的形式解决冗余问题。
表关系分析
多表设计上的问题
1.数据的准确性问题。
(1) 当我们在员工表的dept_id里面输入不存在的部门id，数据依然可以添加，显然这是不合理的。
-- 插入一条 不存在部门的数据 INSERT INTO employee (ename, age, dept_id) VALUES ('小燕', 24, 3);(2) 实际上我们应该保证，员工表所添加的dept_id，必须在部门表中存在。
2.解决方案：使用外键约束，约束dept_id必须是部门表中存在的id。
级联删除
如果主键表中的记录被删除，则外键表中该行数据也相应会被删除。1.语法格式：
-- 添加级联删除 ON DELETE CASCADE#添加级联删除实列 CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ); CREATE TABLE customers ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), email VARCHAR(50) );注意：在使用ON DELETE CASCADE时，关键字ON前不需要添加逗号。
级联更新
如果主键表中被参考字段的值更新，外键表中对应的字段值也会被更新。语法格式：
ON UPDATE CASCADE##
多表关系设计
多表关系设计介绍
单表设计弊端
把所有数据都存放于一张表的弊端： 1.组织结构不清晰； 2.浪费硬盘空间； 3.扩展性极差。多表关系设计
实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要分类表（category）、 商品表（products）、订单表（orders）等多张表。且这些表的数据之间存在一定的关系。
表与表三种关系
一对一关系
1.一对一关系定义
表。
一对多关系
一对多关系定义
有两张表，第一个表中的一行可以与第二个表中的多个行相关联，但是第二个表中的一行只能与第一个表中的一行相关联，我们称这两张表为一对多关系。 一对多（1:n）关系：班级和学生，部门和员工，客户和订单，分类和商品。 一对多关系建表原则：在从表（多方）创建一个字段，字段作为外键指向主表（一方）的主键。
多对多关系
多对多关系定义
有两张表，第一张表的一行可以与第二张表中的一到多个行相关联，同时，第二张表中的一行可以与第一张表中的一到多个行相关联，我们称这两张表为多对多关系。 多对多（m:n）关系：老师和学生，学生和课程，图书和作者，用户和角色。 多对多关系建表原则：这种方式可以按照类似一对多方式来建表，但这样做会导致冗余信息太多；好的方式是实体和关系分离并单独建表；即需要创建三张表，中间关系表中至少声明两个字段，这两个字段分别作为外键指向两张实体表的主键。 例如：学生选课，一个学生可以选修多门课程，每门课程可供多个学生选择。实体表为学生表和课程表，关系表为选修表，其中关系表采用联合主键的方式（由学生表主键和课程表主键组成）建表。
总结：三种外键关系都是用foreign key，区别在于如何使用以及其他条件限制来实现表与表之间的三种关系。
多表查询
多表查询概念
在之前所做的全部查询里面都有一个共同特点，在FROM子句里面只设置了一张数据表，如果现在需要从多个数据表里取出数据，那么就属于多表查询，在FROM子句后面要设置多张数据表。 例如，我们要查询家电分类下都有哪些商品，那么我们就需要查询分类与商品这两张表。
笛卡尔积
笛卡尔积介绍
交叉连接（CROSS JOIN）又可称为笛卡尔积，将左表中每一行与右表中每一行分别连接形成新记录。实际业务中运用较少，需要大量运算成本，但它是其他连接的基础。 假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。 如果说现在要想消除掉笛卡儿积的问题，那么必须想办法为两张数据表设置关系。1.统计category表中的数据量。
SELECT COUNT(*) FROM category;2.统计products表中的数据量。
SELECT COUNT(*) FROM products;3.实现多表查询（交叉连接查询）。
SELECT * FROM products, category;4.观察查询结果，产生了笛卡尔积（得到的结果是无法使用的）。
多表查询的分类
内连接查询
内连接（INNER JOIN）使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新记录。简而言之，查找出同时存在在不同表中的关联数据形成结果表。例如，使用`从表的外键=主表的主键`的方式进行匹配。隐式内连接
在进行多表查询时，使用WHERE指定连接条件的这种连接方式，就称之为隐式内连接（等值连接）。使用WHERE条件过滤无用的数据。语法格式
SELECT 字段1, 字段2, 字段3, ... FROM 表名1, 表名2 WHERE 关联条件;
显式内连接
在进行多表查询时，使用INNER JOIN...ON这种连接方式，就称之为显式内连接。
语法格式
-- INNER关键字可以省略 SELECT 字段1, 字段2, 字段3, ... FROM 表名1 INNER JOIN 表名2 ON 关联条件;
注意事项
1.如果某字段在多表中都存在，则以“表名.列名”来进行限定； 2.与INNER JOIN组合使用的是ON子句，而不是WHERE子句。ON和WHERE后面的指定条件相同，WHERE子句定义条件更简单明了，但某些时候会影响查询性能，而INNER JOIN语法是ANSI SQL的标准规范，能够确保不忘记连接条件。
外连接查询
左外连接
左连接（LEFT JOIN）以左表为基准，将右表中的信息匹配进去，如果右表无对应值，则以空值（NULL值）显示。
语法格式
# 左外连接使用LEFT OUTER JOIN关键字，OUTER可以省略 SELECT 字段1, 字段2, 字段3,... FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 关联条件;
右外连接
右连接（RIGHT JOIN）以右表为基准，将左表中的信息匹配进去，如果左表无对应值，则以空值（NULL值）显示。
语法格式
# 右外连接使用RIGHT OUTER JOIN关键字，OUTER可以省略 SELECT 字段1, 字段2, 字段3, ... FROM 表名1 RIGHT JOIN 表名2 ON 关联条件;
连接方式总结
子查询
子查询概念
当一个查询（SELECT）是另一个查询（SELECT）的条件时，称之为子查询。 在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE或HAVING子句的条件中时，该查询块称为子查询或内层查询。上层的查询块称为父查询或外层查询。子查询的结果作为输入传递回“父查询”或“外部查询”。父查询将这个值结合到计算中，以便确定最后的输出。 SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。以层层嵌套的方式来构造程序正是SQL中"结构化"的含义所在。
使用规则
1.子查询必须“自身就是一个完整的查询”。即它必须至少包括一个SELECT子句和FROM子句。 2.子查询SELECT语句不能包括在ORDER BY子句中。因为ORDER BY字句只能对最终查询结果排序，如果显示的输出需要按照特定顺序显示，那么ORDER BY子句应该作为外部查询的最后一个子句列出。 3.子查询“必须包括在一组括号中”，以便将它与外部查询分开。 4.如果将子查询放在外部查询的WHERE或HAVING子句中，那么该子查询只能位于比较运算符的“右边”。
子查询语法结构
# 比较运算符 comparison (<、=、>=、<=、!=或<>) # comparison比较，SOME是与ANY等效的ISO标准 comparison [ANY | ALL | SOME] (sqlstatement) # expression表达式 expression [NOT] IN (sqlstatement) # EXISTS存在 [NOT] EXISTS (sqlstatement)
子查询分类
按照使用结构
WHERE型子查询：将内层查询结果当做外层查询的比较条件。也可以在HAVING后面当做查询条件的值。
SELECT 字段列表 FROM 表名 WHERE 字段名 = (SELECT 字段列表 FROM 表名 WHERE ...); SELECT 字段列表 FROM 表名 WHERE 字段名 IN (SELECT 字段列表 FROM 表名 WHERE ...);FROM型子查询：把内层的查询结果当成临时表，供外层SQL再次查询。查询结果集可以当成表看待。临时表要使用一个别名。
SELECT 字段列表 FROM (SELECT 字段列表 FROM 表名 WHERE ...) AS 表别名 WHERE ...;EXISTS型子查询：把外层SQL的结果，拿到内层SQL去测试，如果内层的SQL成立，则该行取出。内层查询是exists后的查询。
SELECT 字段列表 FROM 表名 WHERE EXISTS (SELECT 字段列表 FROM 表名 WHERE ...);
按照返回结果
标量子查询：子查询返回的是一行一列（一个数据） 列子查询：子查询返回的结果是一列多行（一列数据） 行子查询：子查询返回的结果是一行多列（一行数据） 表子查询：子查询返回的结果是多行多列（二维表）
子查询是单列多行
子查询的结果类似一个数组，父层查询使用IN函数，包含子查询的结果。
语法格式：
SELECT 字段列表 FROM 表名 WHERE 字段名 IN (子查询);
子查询作为一张表
语法格式：
SELECT 字段列表 FROM (子查询) 表别名 WHERE 查询条件;注意：当子查询作为一张表的时候，需要起别名，否则无法访问表中的字段。
子查询建表
语法格式：
# 创建一个真实存在的表，而且表中有自己独立的数据 CREATE TABLE 表名 AS (子查询SQL语句);
数据库设计
数据库三范式
数据库三范式简介
三范式就是设计数据库的规则。 为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。 满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以此类推。一般说来，数据库只需满足第三范式(3NF）就行了。
第一范式
概念：列具有原子性，设计列要做到列不可拆分。 第一范式是最基本的范式。数据库表里面字段都是单一属性的，不可再分的。如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式。
第二范式
第三范式
数据库反三范式
反三范式概念
1.反三范式指的是通过增加冗余或重复的数据来提高数据库的读性能。 2.浪费存储空间，节省查询时间（以空间换时间）。
什么是冗余字段
设计数据库时，某一个字段属于一张表，但它同时出现在另一个或多个表中，且完全等同于它在其本来所属表的意义表示，那么这个字段就是一个冗余字段。
数据库设计总结
MySQL索引&视图&存储过程
MySQL索引
什么是索引
索引是数据库中为提⾼查询效率（检索速度）的技术，数据量越⼤索引效果越明显。拿汉语字典的⽬录⻚（索引） 打⽐⽅，我们可以按拼⾳、笔画、偏旁部⾸等排序的⽬录（索引）快速查找到需要的字。索引类似于⽬录。 数据库中的数据会零散的保存在磁盘中的每个磁盘块中，如果不使⽤索引，查找数据只能挨个遍历每⼀个磁盘块进 ⾏查找；如果使⽤了索引，磁盘块会以树桩结构进⾏保存，查找数据时⼤⼤降低了磁盘块的访问量，从⽽达到了提 ⾼查询效率的⽬的。 如果合理的设计且使⽤索引的MySQL看作是⼀辆兰博基尼的话，那么没有设计和使⽤索引的MySQL就是⼀个⼈⼒ 三轮⻋。
索引使⽤
MySQL⽬录介绍
MySQL将⼀个表的索引都保存在同⼀个索引⽂件中，如果对表中数据进⾏增、删、改操作，MySQL都会⾃动的更 新索引
MySQL⽬录结构介绍，下⾯以Mac OS操作系统为例进⾏讲解
1.打开终端，输⼊命令。将在命令窗⼝中输出MySQL的安装路径。
1.打开终端，输⼊命令。将在命令窗⼝中输出MySQL的安装路径。2.切换到MySQL安装路径。
cd /opt/homebrew/var/mysql3.查看MySQL的详细⽬录结构。
ls -l4.选中某个⼦库，切换到该⼦库中；并查看指定⼦库中的⽂件。
# 切换到⼦库db1中 cd db1 # 查看当前⽬录下的⽂件 ls5.⽂件说明：
frm：⽂件扩展名后缀为.frm的⽂件，是表结构⽂件； ibd：⽂件扩展名后缀为.ibd的⽂件，是数据和索引⽂件。
常⻅索引分类
常⻅索引分类⻅下表。
索引分类⽅式⼀：
索引名称 说明 普通索引(index) 最常见的索引，作用是对数据的获取进行加速处理 唯一索引(unique) 表示索引列对应的数据只能出现一次，值必须唯一（唯一性约束） 主键索引(primary key) 主键是一种特殊的唯一索引(值必须唯一)，用于标记数据库一条记录，同时通过主键可以快速对应字段的数据信息（默认添加了索引） 索引分类方式二：
索引名称 说明 聚集索引 表示通过主键字段创建的索引，给表添加主键约束时会自动创建聚集索引；在聚集索引的树桩结构中的磁盘块里面保存：主键值+地址+数据 非聚集索引 通过非主键字段创建的索引，在索引的树桩结构中没有数据 单列索引 即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引 组合索引 即一个索引包含多个列 显示索引信息：
show index from 表名;
索引存储结构
普通索引
普通索引，通过index关键字来创建。通过在where条件或者在order by排序，通常会创建为普通索引。语法格式
(1) 创建表的时候直接添加普通索引。
create table 表名( 字段名称 数据类型, ...., index 索引名称 字段名称 );(2) 使用CREATE INDEX语句创建，即在已有的表上创建普通索引
create index 索引名称 on 表名 (字段名称);(3) 修改表结构添加普通索引
alter table 表名 add index 索引名称 (字段名称);
主键索引
特点：主键的值必须唯一、主键的值不能为空，主键自动添加了索引。快速获取一条记录。一张表可以没有主键，最多只能添加一个主键，并且不能为null。语法格式
(1) 创建表的时候直接添加主键索引（最常用）。
create table 表名( 字段名称 字段类型 primary key );(2) 修改表结构，添加主键索引。
alter table 表名 add primary key (主键名称);
唯一索引
索引的列的值不能够重复，必须取值唯一，允许为null。唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。语法格式
(1) 创建表的时候直接添加唯一索引。
create table 表名 ( 字段名称 字段类型, ...., unique index 索引名称 (字段名称) );(2) 使用CREATE在已有表上添加索引。
create unique index 索引名称 on 表名 (字段名称);(3) 修改表结构添加索引。
alter table 表名 add unique index 索引名称 (字段名称);
删除索引
ibd扩展名的文件中，保存了索引的相关数据。所创建的索引是需要占用存储空间。语法格式
(1) 删除普通索引/唯一索引语法格式
alter table 表名 drop index 索引名称;(2) 删除主键索引语法格式。
# 1.如果主键是非自增类型，则直接删除主键索引 ALTER TABLE 表名 DROP PRIMARY KEY; # 2.如果主键是自增类型，则需先取消主键自增，然后再删除主键索引 -- 重新定义主键类型 ALTER TABLE 表名 MODIFY 主键字段名 字段类型; -- 删除主键索引 ALTER TABLE 表名 DROP PRIMARY KEY;
索引性能测试
添加索引首先应考虑在WHERE及ORDER BY涉及的列上建立索引。
导入数据表
1.选中新建连接MyMariaDB，然后【右键】选择【运行SQL文件】选项。
2.通过【文件】选项，定位选择【index_test_data.sql】数据库脚本文件；然后点击【开始】运行SQL脚本文件。
3.如果运行SQL文件提示"[ERR] 2006 - Server has gone away"错误。
错误原因：在我们使用MySQL导入大文件sql时可能会报"[ERR] 2006 - Server has gone away"错误，该问题是max_allowed_packet配置的默认值设置太小所导致，只需要相应调大该项的值之后再次导入便能成功。该项的作用是限制MySQL服务端接收到的包的大小，因此如果导入的文件过大则可能会超过该项设置的值从而导致导入不成功。
4.查看max_allowed_packet的值，单位为字节。
-- 默认16777216字节，即16MB。系统不同，max_allowed_packet的默认值可能有所不同 show global variables like 'max_allowed_packet';5.接下来将该值设置成200MB（200x1024x1024=209715200），因为index_test_data.sql文件数据的大小近200MB。
set global max_allowed_packet=209715200;6.如果上述3、4、5步骤没有问题，则执行第6步操作。查询test_index表中的总记录数。
-- 结果显示表中有500万条数据 SELECT COUNT(*) FROM test_index;
索引测试
1.在没有添加索引的情况下，使用dname字段进行分组查询。
# 未添加索引，进行分组查询 SELECT * FROM test_index GROUP BY dname;2.上述查询需消耗一定时间，耐心等待查询结果；最终显示分组查询耗时为78秒。
3.为dname字段添加索引。
# 添加索引 ALTER TABLE test_index ADD INDEX dname_indx (dname);注意：一般我们都是在创建表的时候，就确定需要添加索引的字段。
4.然后重新执行分组查询；显示耗时为0.003秒。由查询结果可看出添加索引后查询速率提升26000倍（78/0.003=26000）。
SELECT * FROM test_index GROUP BY dname;5.注意：无特殊需求不要修改max_allowed_packet系统的默认值。做完测试后恢复max_allowed_packet的默认初始值。
# 将max_allowed_packet恢复值初始状态的16MB大小（16*1024*1024=16777216） set global max_allowed_packet=16777216;
索引的优缺点
1.索引的优点
- 大大的提高查询速度。 - 可以显著的减少查询中分组和排序的时间。2.索引的缺点：
- 创建索引和维护索引需要时间，而且数据量越大时间越长。 - 当对表中的数据进行增加，删除，修改的时候，索引也要同时进行维护，降低了数据的维护速度。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。3.有索引就一定好吗？不是，如果数据量比较小的话使用索引反而会降低查询效率，只有数据量比较大时才使用索引。
4.索引是越多越好吗？不是，因为建立索引会占用磁盘空间的索引文件，只针对查询数据时频繁使用的字段创建索引
MySQL视图
什么是视图
1.视图是一种虚拟表。
2.视图建立在已有表的基础上，视图赖以建立的这些表称为基表。
3.向视图提供数据内容的语句为SELECT语句，可以将视图理解为存储起来的SELECT语句。
4.视图向用户提供基表数据的另一种表现形式
视图的作用
1.权限控制时可以使用：
例如，某几个列可以运行用户查询，其他列不允许，可以开通视图查询特定的列，起到权限控制的作用。
2.简化复杂的多表查询：
视图本身就是一条SQL查询语句，我们可以将一次复杂的查询构建成一张视图，用户只要查询视图就可以获取想要得到的信息（不需要再编写复杂的SQL语句）。
视图主要就是为了简化多表的查询。
视图的使用
创建视图
1.语法格式
1.创建视图语法格式
CREATE VIEW 视图名 [column_list] AS SELECT语句;2.语法解析
CREATE VIEW：表示创建视图
column_list：可选参数，表示字段清单，指定视图中各个字段的名称，默认情况下与SELECT语句中查询的字段名称相同
AS：表示视图要执行的操作
SELECT语句：向视图提供数据内容
视图分类
1.简单视图：创建视图的子查询SQL语句中不包含，去重、聚合函数、分组查询、关联查询的视图称为简单视图。可以对简单视图中的数据进行增删改查操作。
2.复杂视图：与简单视图相反。只能对复杂视图中的数据进行查询操作。
视图查询语句
视图查询语句
语法结构
SELECT 字段列表 FROM 视图名;
视图增删改语句
对视图数据进行增删改查的操作方式与TABLE表操作一样。
1.向视图中插入数据。
CREATE VIEW emp_view_01 AS (SELECT * FROM emp WHERE dept_name='财务部'); INSERT INTO emp_view_01 VALUES (12, '如来佛祖', '男', 20000, '2000-02-04', '财务部');2.如果向视图中插入一条在视图中不可见但是在原表中却可见的数据称为
数据污染。如何禁止出现数据污染的情况：在创建视图时使用with check option关键字。CREATE VIEW emp_view_02 AS (SELECT * FROM emp) with check option; INSERT INTO emp_view_02 VALUES (13, '张小三', '女', 20000, '2000-02-04', '市场部');3.对视图中数据进行删除和修改时，只能对视图中存在的数据进行相应操作。
DELETE FROM emp_view WHERE eid=1; -- 原表中数据删除成功 DELETE FROM v_emp where eid=10; -- 原表中数据删除失败4.创建或替换视图。
CREATE OR REPLACE VIEW emp_view AS (SELECT * FROM emp WHERE dept_name='教学部');5.删除视图。
DROP VIEW emp_view_02;6.视图别名：如果创建视图的子查询SQL语句中使用了别名，则对视图进行操作时也只能使用别名。
CREATE VIEW emp_view_03 AS (SELECT eid, ename name, sex FROM emp WHERE dept_name='财务部'); # 报错：ERROR 1054 (42S22): Unknown column 'ename' in 'field list' SELECT ename FROM emp_view_03;
视图与表的区别
1.视图是建立在表的基础上，表存储数据库中的数据，而视图只是做一个数据的展示。
2.通过视图不能改变表中数据（一般情况下视图中的数据都是表中的列经过计算得到的结果，不允许更新）。
3.删除视图，表不受影响；而删除表，视图不再起作用。
MySQL存储过程
什么是存储过程
MySQL 5.0版本开始支持存储过程。
存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数（需要时）来调用执行。
简单理解，存储过程其实就是一堆SQL语句的合并。中间加入了一些逻辑控制。
存储过程的优缺点
存储过程优点
存储过程一旦调试完成后，就可以稳定运行（前提是，业务需求要相对稳定，没有变化）；存储过程减少业务系统与数据库的交互，降低耦合，数据库交互更加快捷（应用服务器与数据库服务器不在同一个地区）。存储过程缺点
1.在互联网行业中，大量使用MySQL，MySQL的存储过程与Oracle的相比较弱，所以较少使用，并且互联网行业需求变化较快也是原因之一。 2.尽量在简单的逻辑中使用，存储过程移植十分困难，数据库集群环境，保证各个库之间存储过程变更一致也十分困难。 3.阿里的代码规范里也提出了禁止使用存储过程，存储过程维护起来的确麻烦。
存储过程的创建方式
创建无参存储过程
1.语法格式
1.创建简单的存储过程
DELIMITER $$ -- 声明语句结束符，可以自定义，一般使用$$ CREATE PROCEDURE 存储过程名() -- 声明存储过程 BEGIN -- 开始编写存储过程 -- 要执行的操作 END $$ -- 存储过程结束2.调用无参存储过程
CALL 存储过程名; # 或 CALL 存储过程名();
创建输入参数存储过程
1.语法格式
1.IN输入参数：表示调用者向存储过程传入值
DELIMITER $$ CREATE PROCEDURE 存储过程名(IN 参数名 参数类型) BEGIN -- 要执行的操作 END $$2.调用有参存储过程
CALL 存储过程名(参数值);
创建输出参数存储过程
1.语法格式
1.变量赋值
SET @变量名 = 值;2.OUT输出参数：表示存储过程向调用者传出值
OUT 变量名 数据类型3.创建带有输出参数存储过程语法格式
DELIMITER $$ CREATE PROCEDURE 存储过程名(IN 参数名 参数类型, ..., OUT 变量名 数据类型) BEGIN -- 要执行的操作 -- 设置变量的值 SET @变量名 = 值; -- 返回变量名的值 SELECT @变量名; END $$4.调用带有输出参数值的存储过程
CALL 存储过程名(参数值1, ..., @变量名);
MySQL触发器
触发器简介
什么是触发器
触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（INSERT/DELETE/UPDATE）时就会激活它执行。 简单来说，当我们执行一条SQL语句的时候，这条SQL语句的执行会自动去触发执行其他的SQL语句。
触发器的四要素
创建触发器
语法格式
1.创建触发器的语法如下：
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt2.创建有多个执行语句的触发器：
DELIMITER $ -- 将MySQL的结束符号从";"改为"$"，避免执行出现错误 CREATE TRIGGER 触发器名 触发时间 触发事件 ON 表名 FOR EACH ROW BEGIN 执行语句列表 END $ -- $表示结束标记3.语法说明：
其中，BEGIN与END之间的执行语句列表参数表示需要执行的多个语句，不同语句用分号隔开；
一般情况下，MySQL默认是以";"作为结束执行语句，与触发器中需要的分行起冲突；为解决此问题可以使用DELIMITER分隔符，例如，
DELIMITER $作为开始符号，可以将结束符号变成$。
语法说明
trigger_name
触发器的名称，触发器在当前数据库中必须具有唯一的名称。如果要在某个特定数据库中创建，名称前面应该加上数据库的名称。tirgger_time
触发时间，取值BEFORE或AFTER。触发器被触发的时刻，表示触发器是在激活它的语句之前或之后触发。若希望验证新数据是否满足条件，则使用BEFORE选项；若希望在激活触发器的语句执行之后完成几个或更多的改变，则通常使用AFTER选项。trigger_event
触发事件，用于指定激活触发器的语句的种类，取值INSERT、DELETE或UPDATE。
FOR EACH ROW
一般是指行级触发，对于受触发事件影响的每一行都要激活触发器的动作。例如，使用INSERT语句向某个表中插入多行数据时，触发器会对每一行数据的插入都执行相应的触发器动作。注意：每个表都支持INSERT、UPDATE和DELETE的BEFORE与AFTER，因此每个表最多支持6个触发器。每个表的每个事件每次只允许有一个触发器。单一触发器不能与多个事件或多个表关联。
tbl_name
与触发器相关联的表名，此表必须是永久性表，不能将触发器与临时表或视图关联起来。在该表上触发事件发生时才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器。例如，对于一张数据表，不能同时有两个BEFORE UPDATE触发器，但可以有一个BEFORE UPDATE触发器和一个BEFORE INSERT触发器，或一个 BEFORE UPDATE 触发器和一个AFTER UPDATE触发器。trigger_stmt
是当触发程序激活时执行的SQL语句。如果你打算执行多个语句，可使用BEGIN...END复合语句结构。
触发器操作
1.在MySQL中，若需要查看数据库中已有的触发器，则可以使用
SHOW TRIGGERS语句。SHOW TRIGGERS;2.删除触发器语法。
(1) 直接删除触发器。
-- 删除触发器 drop trigger 触发器名称;(2) 先判断触发器是否存在，如果存在则删除。
-- 判断触发器是否存在 if (object_id('触发器名') is not null) -- 删除触发器 drop trigger 触发器名 go
触发器的限制
1.一个表最多只能有三个触发器（INSERT/DELETE/UPDATE）
2.每个触发器只能用于一个表
3.不能对视图、临时表创建触发器
4.truncate table能删除表，但不能触发触发器
5.不能将触发器用于系统表
触发器案例
需求：在下订单的时候，对应的商品的库存量要相应的减少，即卖出商品之后减少库存量。
1.编写触发器。
-- 1.修改结束标识 DELIMITER $ -- 2.创建触发器 CREATE TRIGGER t1 -- 3.指定触发的时机和要监听的表 AFTER INSERT ON orders -- 4.行触发器，固定写法 FOR EACH ROW -- 5.触发后具体要执行的事件 BEGIN -- 订单+1，库存-1 UPDATE goods SET num = num - 1 WHERE gid = 1; END $2.查询goods表中gid=1的商品的库存量。
-- 输出gid=1的商品库存为20 SELECT * FROM goods WHERE gid = 1;3.向订单表orders中添加一条订单数据。
INSERT INTO orders VALUES(1, 1, 15);4.再次查看goods表中gid=1的商品的库存量。
-- 输出gid=1的商品库存为19，商品的库存储量减1 SELECT * FROM goods WHERE gid = 1;
DCL
数据控制语言（Data Control Language）在SQL语言中，是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、存储程序、用户自定义函数等数据库对象的控制权。由GRANT和REVOKE两个指令组成。
MySQL默认使用的都是root用户，超级管理员，拥有全部的权限。除了root用户以外，我们还可以通过DCL语言来定义一些权限较小的用户，分配不同的权限来管理和维护数据库。
创建用户
语法结构
语法格式
CREATE USER 'username'@'host' IDENTIFIED BY 'password';参数说明
参数 说明 username 创建新用户名称，即MySQL数据库登录名称 host 指定该用户在哪个主机上可以登陆，本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%（ %表示用户可以在任意电脑登录MySQL服务器） password 该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器
用户授权
创建好的用户，需要进行用户授权。
语法结构
1.语法格式
GRANT 权限1, 权限2... ON 数据库名.表名 TO '用户名'@'主机名';2.参数说明
参数 说明 权限 授予用户的权限，如CREATE、ALTER、SELECT、INSERT、UPDATE等。如果要授予所有的权限则使用ALL ON 用来指定权限针对哪些库和表 TO 表示将权限赋予某个用户
查看权限
1.语法格式
SHOW GRANTS FOR '用户名'@'主机名';2.查看root用户权限
-- 查看root用户的权限 SHOW GRANTS FOR 'root'@'localhost';说明：显示的结果中GRANT ALL PRIVILEGES关键字表示拥有所有权限。
删除用户
1.语法格式
DROP USER '用户名'@'主机名';2.删除admin01用户
-- 删除admin01用户 DROP USER 'admin01'@'localhost';2.删除admin02用户
# 1.删除admin02用户，出现如下异常： -- 1396 - Operation DROP USER failed for 'admin02'@'localhost' DROP USER 'admin02'@'localhost'; # 2.如果主机为“％”，每当尝试使用localhost删除用户时，就会产生1396错误。因此需使用主机“％”删除用户 DROP USER 'admin02'@'%';
查询用户
选择名为mysql的数据库，直接查询user表即可。
-- 查询所有用户 # SELECT * FROM USER; SELECT * FROM mysql.user;
数据库备份与还原
备份的应用场景，在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。 这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。
Navicat备份与还原
Navicat数据备份
1.选中要备份的数据库（db4），右键选择【转储SQL文件】-【结构和数据】选项。
2.指定文件位置，选择导出即可。
Navicat数据还原
1.先删除db4数据库。
DROP DATABASE db4;2.选中新建连接MyMariaDB，然后【右键】选择【运行SQL文件】选项。
3.通过【文件】选项，定位选择【db4.sql】数据库脚本文件；然后点击【开始】运行SQL脚本文件。
命令行备份与还原
命令行备份
进入到MySQL安装目录的bin目录下，打开DOS命令行。
1.语法格式。
mysqldump -u 用户名 -p 密码 数据库 > 文件路径2.执行备份，备份db4中的数据到桌面的db4.sql文件中。
mysqldump -uroot -p123456 db4 > /Users/yuanxin/Desktop/db4.sql
命令行还原
1.先删除db4数据库，在对应的数据库上右键，选择【删除数据库】选项。
2.还原的时候需要先创建一个db4数据库；或者在sql脚本文件中包含有db4数据库的创建语句。
CREATE DATABASE db4 CHARACTER SET utf8;3.恢复数据，还原db4数据库中的数据。
# 语法格式 source sql文件地址; # 1.Windows系统 -- 直接进入Command Line Client终端 -- 选中所创建的db4数据库 MariaDB> USE db4; -- 通过命令执行db4.sql数据库脚本文件 MariaDB> source C:\User\yuanxin\Desktop\db4.sql; # 2.Mac系统 -- 在Mac终端输入登录MySQL数据库的命令(回车后需要输入数据库的登录登录密码) mysql -u root -p -- 选中所创建的db4数据库 MariaDB [(none)]> USE db4; -- 通过命令执行db4.sql数据库脚本文件 MariaDB [(none)]> source /Users/yuanxin/Desktop/db4.sql;
5576
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
