MySQL快速入门

MySQL

第一章: 数据库的基本概述

1. 基本概念

1. 数据库「DataBase」, 简称DB

2. 用于存储和查询数据的仓库.

3. 特点

   • 持久化存储数据的。其实数据库就是一个文件系统

   • 方便存储和管理数据

   • 使用了统一的方式操作数据库 (SQL)

2. 常见的数据库

• 排行

  

• 常见的数据库

  

• 各个数据库特点说明

  名称	描述
  mysql	开源免费, 小型的数据库.
  oracle	收费的大型数据库. Oracle公司产品.贵, 一般小企业算了吧.
  DB2	IBM公司产品. 收费, 一般用于银行系统中;
  SQLServer	微软公司产品, 收费. 中型数据库.
  Sybase	市场份额很少了. 但是提供了一个非常专业的数据库建模工具: PowerDesigner
  SQLite	嵌入式数据库,常应用于手机端

• 咱们学习哪个?

  • mysql

    • 开源,免费.

  • oracle「后期学习」

    • 学习的时候不花钱. 商用花钱;

    • 贵

3. 安装&配置「window下」

1. 查看服务

   # win + r , 输出cmd, 回车
   # services.msc, 回车
   # 以管理员权限打开就可以了.

2. 启动

   1. 手动

   2. 命令行「命令行下」

   net start mysql # 启动, 如果报错涉及权限问题.请尝试使用管理员身份打开cmd
   net stop mysql # 关闭

第二章: mysql的基本操作

1. 登录&退出

1. 登录

   1. mysql -uroot -p密码 #  一般的话, 学习阶段可以使用此名字.生产环境肯定不能用这个名字
   # root表示账号, -p, 表示密码
   2. mysql -h ip -uroot -p连接目标的密码, 连接远程数据库
   3. mysql --host=ip --user=root --password=连接目标的密码

2. 退出

   1. exit
   2. quit

2. 目录说明

1. 安装目录

2. 数据目录

• 几个重要的概念

  • 数据库: 文件夹

  • 表: 文件夹下的文件

  • 数据: 数据

第三章: SQL的概述

1. SQL的概念

Structured Query Language：结构化查询语言. 简称为「SQL」;简单来说, 就是定义了操作了关系型==数据库的规则==;

• 每一种数据库操作的方式都不太一样;

2. SQL的通用语法

描述的所有内容,符号,各种注释都是采用英文状态下半角输入符号;

1. SQL 语句可以单行或多行书写，以分号结尾。==「英文状态下的半角分号」==

2. 可使用空格和缩进来增强语句的可读性。

3. MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。

4. 注释

   注释表示, 当前语句不执行了;

   单行注释: -- 注释内容 或 # 注释内容(mysql 特有) 
   多行注释: /* 注释 */

3. SQL的分类

D*L [data * language]

1. DDL(Data Definition Language)数据定义语言

   用来定义数据库对象：数据库，表，列等。关键字：create, drop, alter 等

2. DML(Data Manipulation Language)数据操作语言

   用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等

3. DQL(Data Query Language)数据查询语言

   用来查询数据库中表的记录(数据)。关键字：select, where 等

4. DCL(Data Control Language)数据控制语言(了解)

   用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

第四章: DDL「操作数据库」

mysql数据库中的几种存储结构

ddl -> data define language

最上层的结构 数据库

数据库中包含多个 数据表

每个数据表 包含多个 属性列

1. 创建数据库「Create」

• ==前题条件, 必须连接上数据库才能操作;==

• 建议关键字大写一下;CREATE

# 1. 创建数据库
create database 数据库名称;
​
#如果重复创建 报错信息如下
Can't create database '数据库名称'; database exists
​
# 2. 创建数据库，判断不存在，再创建：
create database if not exists 数据库名称;
​
# 3. 创建数据库，判断不存在，并指定字符集
create database if not exists db4 character set utf8mb4;

2. 查询操作「Retrieve」

# 1. 查询所有数据库的名称
show databases; # databases, 带s的.
​
# 2. 查询某个数据库的字符集:查询某个数据库的创建语句
# 重要
show create database 数据库名称;
​
#performance_schema和mysql和information_schema是mysql的内置核心数据库 这个数据库是维持mysql运行的数据库,改动这里面的内容,可能会导致mysql崩溃
​
​

3. 修改/更新「update」

一般情况下, 不会去主动修改字符集;

# 1. 修改数据库的字符集 utf8, gbk,utf8bom4, iso-8859-1
alter database 数据库名称 character set 字符集名称;
​
-- 修改数据库字符集和排序方式
alter database java21 character set utf8mb4 COLLATE utf8mb4_general_ci;

4. 删除「Delete」

# 1. 删除数据库
drop database 数据库名称;

# 2. 判断数据库存在，存在再删除
drop database if exists 数据库名称;

5. 使用数据库

# 查询当前正在使用的数据库名称
 select database();
 
# 使用数据库
use 数据库名称;

第五章: DDL「操作表」

1. 创建表「Create」

创建表的前提必须先选择一个数据库;
use 「自己的库名称」;
# 语法
create table 表名称(
	  列名1 数据类型1(宽度), 
    列名2 数据类型2(宽度),
    列名3 数据类型3(宽度),
    ...
    列名n 数据类型n
); 

# 创建表示例
create table student(
	  id int,
    name varchar(32),
    age tinyint,
    score double(4,1),
    birthday date, 
    insert_time timestamp
)

# 复制表, 「慎用」

create table 表名 like 被复制的表名;

特别注意:

1. 最后一列不要加逗号;

1. 数据类型

• 数值类型

  

• 时间和日期类型

  

  • timestamp: 如果不给这个字段进行赋值, 或者赋值为null, 则timestamp这个类型的会使用当前的系统时间,自动赋值;

• 字符串类型

  

  必须要求知道的数据类型

  • 整型数据类型

    • int

    • tinyint

    • bigint

    • double

  • 字符串

    • varchar

    • text

    • mediumblob

  • 日期

    • date

    • datetime

    • timestamp

2. 查询表

#  查询某个数据库中所有的表名称
show tables;
​
# 查询表结构
desc 表名;
# 显示如下: 
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| useranme | varchar(64)  | YES  |     | NULL    |       |
| gender   | varchar(1)   | YES  |     | NULL    |       |
| address  | varchar(128) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
# Field, 表示字段,或者称为列;
# Type, 建表填写的数据类型
# Null, 该字段「列」是否可以为null;
# Key, 该字段「列」是否是主键或者是外键.后边说;
# Default, 表示该字段「列」的默认值;
# Extra, 扩展信息, 暂时没有用;
​
# 查看建表语句及该所用的字符集
show create table 表名称;
# 可以看到具体的建表语句和表的字符集;
| t1    | 
​
CREATE TABLE `student` (
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
​
​
# 系统自动添加的部分: 
DEFAULT NULL, # defualt表示默认的, 
 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
# ENGINE=InnoDB, 表示存储引擎. 默认是这个;
# CHARSET=utf8mb4, 表示表的字符集,如果建表的时候没有指明字符集,默认采用当前数据库的字符集

3. 修改/更新「update」

==alter 对库/表 库名/表名 + 具体执行的动作;==

# 1. 修改表名
alter table 表名 rename to 新的表名;
# 示例: 
mysql> alter table t1 rename to t_classmate;
Query OK, 0 rows affected (0.01 sec)
​
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| t2                |
| t_classmate       |
+-------------------+
​
​
# 2. 修改表的字符集
# 一般来说, 不会去修改;
alter table 表名 character set 字符集名称;
# 示例: ut8mb4
alter table t_classmate character set utf8;
​
# 3. 添加一列
alter table 表名 add 列名 数据类型;
​
mysql> alter table t2 add score int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
mysql> desc t2;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| useranme | varchar(64)  | YES  |     | NULL    |       |
| gender   | varchar(1)   | YES  |     | NULL    |       |
| address  | varchar(128) | YES  |     | NULL    |       |
| score    | int          | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
​
# 4. 修改列名称 类型, 「可以修改列名称」
alter table 表名 change 列名 新列别 新数据类型;
mysql> alter table t2 change gender sex int;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
mysql> desc t2;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| useranme | varchar(64)  | YES  |     | NULL    |       |
| sex      | int          | YES  |     | NULL    |       |
| address  | varchar(128) | YES  |     | NULL    |       |
| score    | int          | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
​
# 修改列的数据类型, 不能修改列的名称
alter table 表名 modify 列名 新数据类型; 「只能修改列的数据类型,不能修改列的名称」
​
mysql> alter table t2 modify sex varchar(1);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
mysql> desc t2;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| useranme | varchar(64)  | YES  |     | NULL    |       |
| sex      | varchar(1)   | YES  |     | NULL    |       |
| address  | varchar(128) | YES  |     | NULL    |       |
| score    | int          | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
​
#drop 删除 alter 修改, create 创建, show 查看,  这四个增删改查 都是针对结构的增删改查
# 5. 删除列
alter table 表名 drop 列名;
mysql> alter table t2 drop score;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
mysql> desc t2;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| useranme | varchar(64)  | YES  |     | NULL    |       |
| sex      | varchar(1)   | YES  |     | NULL    |       |
| address  | varchar(128) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------

4. 删除表操作「Delete」

==drop 库名称/表名称 库名/表名;==

drop table 表名;
drop table  if exists 表名 ; 

第六章: DML 增删改表中的数据

1. 数据的添加

==insert into + 表名(所有列名)values(值...)==

# 1. 向已经存在的表中插入数据
insert into 表的名称(列名称1, 列名称2, 列名称3, ...列名称n) values (值1,值2, 值3, ... 值n)
# 2. 此种方式, 可以只对某几个字段进行赋值操作;
insert into 表的名称(第一列, 第二列)values(值1 ,值2);
# 3. 可以一次性多条记录「批量插入」. 
insert into 表的名称(列名称1, 列名称2, 列名称3, ...列名称n)values(值1,值2, 值3, ... 值n),(值1,值2, 值3, ... 值n), (值1,值2, 值3, ... 值n) ....

# 特别注意: 
# 1. 列名称的值必须一一对应;
# 2. 如果表名称后边, 没有写列名称,则默认会给所有的列赋值; 「不写等于全写」
		insert into 表名称 values(值1, 值2, 值3, ... 值n);
# 3. 除了数字类型之外的值, 其它类型的值需要使用引号引起来「单引号或者双引号都可以」

注意: 插入语句中insert into 表的名称(第一列, 第二列)values(值1 ,值2); 第二列,也就是最后一列是不需要加逗号的

2. 删除表中的数据

==delete form 表名称 约束条件==

# 基础语法
delete from 表名称 「约束条件, where」

# 从t1表小,删除id=1的数据;
mysql> select * from t1;
+------+------+------+
| id   | name | note |
+------+------+------+
|    1 | yun  | NULL |
|    2 | dong | NULL |
|    3 | qian | NULL |
+------+------+------+
mysql> delete from t1 where id = 1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+------+------+
| id   | name | note |
+------+------+------+
|    2 | dong | NULL |
|    3 | qian | NULL |
+------+------+------+
2 rows in set (0.00 sec)
# 特别注意: 
# 1. 如果删除的时候没有添加约束条件,则默认删除表中所有的数据;
# 2. 如果不加约束条件删除,基本上是枪毙的罪; --> 慎重;

有一些情况下需要删除表中的所有数据, 那么可以使用如下两种方式;

1. delete from 表名称;

   • 不推荐使用, 有多少条记录就会执行多少次删除操作. 是一条一条的删除的.效率低.

   • 尽量不要去使用;

2. ==truncate table 表名; 删除所有数据,使用此种方式;==

   • 推荐使用, 效率高. 是先将整个表删除,然后再创建一张一样的表;

   • 下边未说;

3. 修改表中的数据「更新操作」

# 基础语法
update 表名 set 列名1 = 值1, 列名2 = 值2, 列名n = 值n, ... 「约束条件」

# 示例: 
mysql> update t1 set note='dong jie';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql>
mysql> select * from t1;
+------+------+----------+
| id   | name | note     |
+------+------+----------+
|    2 | dong | dong jie |
|    3 | qian | dong jie |
+------+------+----------+
2 rows in set (0.00 sec)

mysql> update t1 set note ='yunfeng' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+------+------+----------+
| id   | name | note     |
+------+------+----------+
|    2 | dong | yunfeng  |
|    3 | qian | dong jie |
+------+------+----------+

# 如果不加约束条件,则默认更新整张表;
mysql> select * from t1;
+------+------+----------+
| id   | name | note     |
+------+------+----------+
|    2 | dong | yunfeng  |
|    3 | qian | dong jie |
+------+------+----------+
2 rows in set (0.00 sec)

mysql> update t1 set id = 10,name='liyao', note='qian liyao' where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+------+-------+------------+
| id   | name  | note       |
+------+-------+------------+
|    2 | dong  | yunfeng    |
|   10 | liyao | qian liyao |
+------+-------+------------+
2 rows in set (0.00 sec)

如果不加约束条件, 则默认修改表中的所有记录;

不管是更新或者是删除,如果不添加约束条件则,那么 更新/删除整表数据;危险操作.

==必须添加约束条件;==

where 条件;

• ddl

  • 对数据库/表进行操作.

• dml「重点内容」

  • 针对表中的数据的新增/更新/删除操作;

第七章: 查询表中的记录

1. 基本语法

select * from 表名;
    select 
        字段列表
    from
    	表名列表
    where
    	条件列表
    group by
    	分组字段
    having 
    	分组之后的条件
    order by
    	排序
    limit
    	分页限定

2. 基础查询

• 多个字段进行查询

  1. 多个字段进行查询
  	`select 字段名1, 字段名2, ... from 表名`

  • 如果要查询出所有的字段,则可以使用*来替代字段列表

  • 实际工作当中,不要使用select * ;即使查询的是所有的列「字段」, 那么也应该去把所有的字段写上;

  # 推荐做法
  mysql> select id,name, note from t1;
  +------+-------+------------+
  | id   | name  | note       |
  +------+-------+------------+
  |    2 | dong  | yunfeng    |
  |   10 | liyao | qian liyao |
  +------+-------+------------+
  

小结:

• 概述

  • mysql

  • sql

• ddl操作

  • 对库, 表本身进行操作;不针对数据;

• dml操作

  • 针对数据进行增加/删除/修改

• dql操作

  select * from 表名 where 约束条件;
  select * from 表名;

• 去除重复

  distinct

• 别名

  as 或者空格都可以的.

3. 条件查询

where条件 可以跟在 dml中的update和delete语句后面, 也可以跟在dql查询语句后面

• where子句后跟限定条件

• 运算符

   > 、< 、<= 、>= 、= 、<> !=
  
  # 推荐使用and or not, 不推荐&&, || , !,但是它可以使用;
   and  或 &&
  or  或 || 
  not  或 !
  ------------
  BETWEEN...AND  # 范围查询. 某一个范围,可以取边界值;[Between , and], 闭区间;
  IN( 集合) # 写在括号里,出现一个即满足条件;
  
  LIKE：模糊查询
  	占位符：
  		_:单个任意字符,有一个_, 就是有一符号;严格匹配;
  		%：多个任意字符 , 0个1个或者多个都可以;
  IS NULL 
  	是一个整体,不能拆分使用.除了 is not null. 

4. 综合练习

1. 新建一个数据库,名称为dbstudy
2. 创建一张表, 名称为student;
	1. 要求字段名称是: 
		name
		age
		gender
		英文成绩
		语文成绩
3. 根据条件查询
	1. 查询所有数据
	2. 查询年龄大于21岁的学生信息;
	3. 查询年龄等于20岁的学生信息;
	4. 查询年龄不等于20岁的学生信息;
	5. 查询年龄大于等于20, 小于等于30岁的学生信息;
	6. 查询年龄20岁, 22岁, 25岁的学生信息;
	7. 查询英文成绩为null的学生信息;
	8. 查询英文成绩不为null的学生信息;
	9. 查询姓王的学生信息;
	10. 查询名字包含衣的学生信息;
	

• sql示例

SELECT  * FROM student;

# 注释
-- 注释
/*
	多行注释
*/
insert into student(id, username, english, computer,chinese)VALUES(5, "董杰", 
40, 60, 100);

# 累加列
SELECT username, english,computer, chinese, english + computer + chinese
FROM student;

DESC student;

insert into student(id, username, english, computer,chinese)VALUES(6, "刘刚", 
NULL, 76, 98);

SELECT  * FROM student;

# ifnull
# if(exp1, exp2)
# 	exp1:表示的是可能为null字段
# 	exp2: 表示如果exp1 = null, 将Exp1值替换为啥
SELECT
	username,
	english,
	computer,
	chinese,
	IFNULL(english,0) + IFNULL(computer,0) + IFNULL(chinese,0)
FROM	
	student;
	
-- 使用别名
-- AS 关键字. xxx AS 起个名称
-- 使用空格隔开. xxx 起个名称
SELECT
	username AS 姓名, # 使用AS
	english AS 英语成绩,
	computer 计算机成绩, # 使用空格隔开
	chinese 汉语成绩,
	IFNULL(english,0) + IFNULL(computer,0) + IFNULL(chinese,0) AS 总分
FROM	
	student;
	
-- 给表起别名
SELECT
	s.username AS 姓名, # 使用AS
	s.english AS 英语成绩,
	s.computer 计算机成绩, # 使用空格隔开
	s.chinese 汉语成绩,
	s.id as 用户id,
	IFNULL(s.english,0) + IFNULL(s.computer,0) + IFNULL(s.chinese,0) AS 总分
FROM	
	student AS s;
# 综合练习
########## 条件查询
DESC student;

# 删除一列
alter TABLE student drop computer;
TRUNCATE TABLE student;
SELECT * FROM student;
# 插入一些数据
DESC student;

-- 查询所有数据
INSERT INTO student VALUES
(1, '云峰', 25, '男', 50, 80),
(2, '振源', 23, '男', 70, 50),
(3, '佳琪', 21, '男', 90, 60),
(4, '丕琪', 20, '男', 90, 60),
(5, '董杰', 21, '女', 70, 70),
(6, '乔石', 23, '男', 90, 80);

-- 查询年龄大于21岁的学生信息;
-- 查询年龄大于21岁的学生信息;
--  > 、< 、<= 、>= 、= 、<>
-- <>, 表示不等于的意思;
SELECT	
	* 
FROM	
	student as s
WHERE
	s.age > 21;
	
SELECT	
	* 
FROM	
	student as s
WHERE
	s.age <> 23;	
	
-- 	5. 查询年龄大于等于20, 小于等于30岁的学生信息;
/*
 连接where后边的子句操作;
 and  或 &&
 or  或 || 
 not  或 !
 
 &&, ||, !不建议写在sql当中, 建议使用and or not.
*/

SELECT
 * 
FROM
	student as s
WHERE
	s.age >= 21
	&&
	s.age <= 23;
	
SELECT
 * 
FROM
	student as s
WHERE
	s.age >= 21
	AND
	s.age <= 23;
	
SELECT
	s.username as 姓名,
	s.english 英文成绩
FROM
	student AS s
WHERE
	s.english >= 80
	AND
	s.gender = '男';
	
SELECT
	s.username as 姓名,
	s.english 英文成绩,
	s.chinese AS 语文成绩
FROM
	student AS s
WHERE
	s.english >= 80
	OR
	s.chinese >= 60;

SELECT * FROM student;

SELECT
	*
FROM
	student AS s
WHERE
	s.english >= 60
	AND
-- 	NOT s.gender = '男';
-- 	s.gender != '男';
	s.gender = '女';

------------------------------
-- 	6. 查询年龄20岁, 22岁, 25岁的学生信息;
--  in(....)
SELECT
	*
FROM
	student AS s
WHERE
	s.age = 20
	OR
	s.age = 22
	OR
	s.age = 25;
	
SELECT
	*
FROM
	student AS s
WHERE
	s.age IN(20, 22, 25); # 表示当前的Age 出现20, 22, 25中的一值就可以了;
	
/*
	7. 查询英文成绩为null的学生信息;
	8. 查询英文成绩不为null的学生信息;
	IS NULL --> 判断某一个字段「列」的值为null; 
*/
SELECT * FROM student;
INSERT into student VALUES
(21, '董小姐', 28, '女', 90, 90),
(22, '董二千', 26, '男',78 , 70),
(23, '董不懂啊', 26, '男',78 , 70),
(24, '西门吹雪', 23, '男',98 , 60),
(25, '董', 23, '男',98 , 60),
(26, '易阳千喜', 21, '男',98 , 60);

SELECT
	*
FROM
	student as s
WHERE
	s.english = NULL; # 不能这样写;

SELECT
	*
FROM
	student as s
WHERE
	s.english IS NULL;

# 不为null
# is not null, 某个字段「列」的值不是null的数据;
# 顺序不能更改. 只能使用is not null;

SELECT
	*
FROM
	student as s
WHERE
	s.english IS NOT NULL;
	
/*
	9. 查询姓王的学生信息;
		LIKE：模糊查询
	占位符：
			_:单个任意字符, _, 表示一个字符, __,表示两个字符.匹配的是字符的个数
			%：多个任意字符
	10. 查询名字第二个字是小的学生信息;
	11. 查询名字包含衣的学生信息;
	13. 查询名字是四个字的学生信息;
		LIKE '____' , 表示名字是四个字的. 
*/

SELECT * FROM student;
SELECT
	*
FROM	
	student as s
WHERE
	s.username LIKE '___';

SELECT
	*
FROM
	student as s
WHERE
	s.username LIKE '_琪';

SELECT	
	*
FROM
	student as s
WHERE
	s.username LIKE '董_'
	OR
	s.username LIKE '董__'
	OR
	s.username LIKE '董___'
	OR
	s.username LIKE '董____'
	OR
	s.username LIKE '董_____';
	
	SELECT
		*
	FROM
		student AS s
	WHERE
		s.username LIKE '_董%';
		
		
	-- BETWEEN ... AND ... 
	-- 	5. 查询年龄大于等于20, 小于等于30岁的学生信息;
	
SELECT
	*
FROM
	student as s 
WHERE
	s.age BETWEEN 23 AND 28;

alter TABLE student RENAME TO stu1;
show tables;

第八章: DQL 查询语句

超级重点内容;

0. 建表语句

create database itszt22 character set utf8mb4;

CREATE TABLE student(
	id int ,
	username VARCHAR(32),
	age int,
    sex varchar(1) DEFAULT "男",
	math DOUBLE(4,1),
	java DOUBLE(4,1),
	create_time TIMESTAMP
);

-- DEFAULT "男" 如果插入这张表中没有填上sex这个值, 将会默认为 男
-- TINYINT 1个字节  范围(-128~127)
-- DOUBLE(m,d) m总个数，d小数位 
--  TIMESTAMP 时间戳的意思, 等同于java中的System.currentMills()方法返回的那个数字

-- CURRENT_TIMESTAMP mysql识别到这句话会获取当前时刻的时间戳 相当于java中的 System.currentMills


-- mysql特有的语法, 一条sql语句插入多行数据
INSERT INTO student(id, username, age ,math, java, create_time) VALUE
(1, "张三丰", 120, 39, 65, CURRENT_TIMESTAMP),
(2, "张无忌", 22, 23, 38, CURRENT_TIMESTAMP),
(3, "赵敏", 21, 67, 89, CURRENT_TIMESTAMP),
(4, "小招", 24, 32, 80, CURRENT_TIMESTAMP),
(5, "金毛狮王", 98, 33, 76, CURRENT_TIMESTAMP),
(6, "紫衫龙王", 45, 89, 67, CURRENT_TIMESTAMP),
(7, "齐天大圣", 89, 43, 12, CURRENT_TIMESTAMP),
(8, "宝儿姐", 100, 96, 78, CURRENT_TIMESTAMP),
(9, "古月娜", 34, 65, 33, CURRENT_TIMESTAMP),
(10, "唐三", 98, 64, 77, CURRENT_TIMESTAMP),
(11, "云韵", 21, 90, 29, CURRENT_TIMESTAMP),
(12, "腊笔小新", 12, 98, 32, CURRENT_TIMESTAMP),
(13, "唐僧", 125, 45, 45, CURRENT_TIMESTAMP),
(14, "唐小红", 32, 12, 65, CURRENT_TIMESTAMP),
(15, "唐三藏", 36, 56, 98, CURRENT_TIMESTAMP);

--  DEFAULT "男"  这个表示默认值 为 '男'
-- 设置id为偶数的学生的性别为女
UPDATE student SET sex = "女" WHERE id % 2 = 0;

1. 排序查询

• 语法

  order by 子句
  	order by 排序字段,排序方式, 排序字段2, 排序方式2, ....

• 排序方式

  • ASC: 升序排序. 默认值;

  • DESC: 降序

• 特别注意:

  如果有多个排序条件,则当前边的条件值一样时, 才会判断第二个条件;

  每一个字段都可以单独指定排序方式

  SELECT	
  	* 
  FROM
  	student as s
  ORDER BY
  	age DESC, math asc;

2 . 聚合函数

• 将一列数据作为一个整体，进行纵向的计算

• 常用聚合函数

  • count 计算个数

    • 一般选择非空的列

    • count(*)

  • max

  • min

  • sum

  • avg

• 特别注意:

  • 聚合函数的计算，排除null值。

3. 分组查询

• 语法

  group by 分组字段;

• 特别注意:

  • 分组之后查询的字段：分组字段、聚合函数

• where & having的区别

  • where在分组之前进行限定,如果不符合条件则不进行分组; having 在分组之后对结果集进行限定, 如果不满足限定条件,则不会返回结果集,也就是不会被查询出来;

  • where 后不可以跟聚合函数，having可以进行聚合函数的判断。

注意 : group by需要写在where条件的后面

• 示例

  1. 按照性别分组. 分别查询出男/女同学的数学平均分,数学总分. 
  2. 按照性别分组, 分别查询出, 男女同学的数学平均分, 人数' 
  3. 按照性别分组。分别查询男、女同学的数学平均分,人数 要求：分数低于70分的人，不参与分组;
  4. 按照性别分组。分别查询男、女同学的数学平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人;

4. 分页查询

• 基础语法

  limit 开始索引「需要计算的」, 每页查询的条数「定值」

• 公式

  ==开始的索引 = (当前页码 - 1) * 每页显示的条数==

  • 当前页码是固定值,

  • 每页显示的条数也是固定的.

  • 一般当前页码会由前端传递过来. 这样可以计算出来开始的索引值.

• 示例

• limit是mysql中特有的.其它的数据库都有自己的实现;

第九章: 约束

1. 基本概念

约束: 对表中的数据进行限定, 保证数据的正确性、有效性和完整性;

2. 分类

• 主键约束: ==primary key==

• 非空约束 ==not null==

• 唯一约束 ==unique==

• 外键约束 ==foreign key==

3. 主键约束「primary key」

1. 概念

• 非空且唯一

  • 主键不能为null, 主键不能重复;

• 一张表只能有一个字段为主键

• 主键就是表中唯一的标识 例如每个人都有自己身份的唯一标识(身份证号码, 学号)

2. 创建表时添加主键约束

create table 表名(
	id int primary key # 给id添加主键约束
)

3. 删除主键约束 (用的比较少)

alter table 表名 drop primary key

4. 主键自动增长「auto_increment」

• 概念: 如果表中的某一个字段是数值类型的, 使用auto_increment可以来实现值的自动增长;

• 在创建表中, 添加主键约束, 可以设置主键自增

  create table 表名(
  	id int primary key auto_increment # 给id添加主键约束, 并且设置主键自增长
  )

• 删除主键的自增长(基本用不到 了解)

  alter table 表名 modify id int

4. 非空约束「not null」

1. 概念

   设置某一列的值不能为null;

2. 创建表的时候添加非空约束

   create table 表名(
   	id int primary key auto_increment, # 给id添加主键约束, 并且设置主键自增长
       name varchar(32) not null # 设置非空约束
   )

3. 创建表完后，添加非空约束(了解)

   ALTER TABLE 表名 MODIFY NAME VARCHAR(32) not null;
   # 删除非空约束
   ALTER TABLE t6 MODIFY name VARCHAR(32);

5. 唯一约束「unique」

• 设置某一列的值不能重复

• 在创建表的时候可以添加唯一约束

  create table 表名(
      id int primary key auto_increment, # 给id添加主键约束, 并且设置主键自增长
      name varchar(32) not null, # 设置非空约束
      phone_number varchar(20) unique # 添加非空约束 
  )

• 在表创建完成之后添加唯一约束

  alter table 表名 modify phone_number varchar(20) unique;

• 删除唯一约束

  alter table 表名 drop INDEX phone_number;

• 代码示例

  SELECT
      * 
  FROM
      student;
      
  DESC student;
  ​
  SELECT
      COUNT(id) as 总量,
      s.gender
  FROM
      student as s
  WHERE
      s.age > 20
  GROUP BY
      s.gender;
      
  SELECT
      COUNT(id) as 总人数,
      SUM(s.age) 总年龄数,
      AVG(s.age) 平均年龄,
      s.gender
  FROM
      student as s
  WHERE
      s.age > 20
  GROUP BY
      s.gender
  HAVING 
      COUNT(id) > 80;
      
  # 分页
  SELECT
      *
  FROM
      student as s
  WHERE
      email LIKE '%qq%'
  LIMIT 0, 20;
  DESC student1;
  ​
  INSERT INTO student1 VALUES(234, null, 18, 89, 23, '2021-05-11', '男');
  INSERT INTO student1 VALUES(234, '建宁', 18, 89, 23, '2021-05-11', '女');
  ​
  ​
  SELECT * FROM student1;
  ​
  # 创建一张表, 并且指定主键
  CREATE TABLE score(
      id int PRIMARY key, # 添加主键primary KEY
      math INT ,
      chineses int
  );
  ​
  INSERT INTO score VALUES(1, 40, 50), (2, 30, 20);
  SELECT * from score;
  ​
  SHOW tables;
  DESC score;
  ​
  CREATE TABLE t1(
      id int, 
      name VARCHAR(16),
      age INT
  );
  ​
  DESC t1;
  INSERT INTO t1 VALUES(1, '小红', 20), (1, '小明', 20);
  ​
  # 给已经创建好的表,添加主键约束;
  ALTER TABLE t1 MODIFY id int PRIMARY key;
  ALTER TABLE t1 MODIFY id int; # 无法删除主键约束;错误姿势;
  ALTER TABLE t1 DROP PRIMARY KEY;
  ​
  CREATE TABLE t2(
      id int PRIMARY KEY auto_increment, # 添加主键, 并且设置主键自增;
      username VARCHAR(32),
      age int
  );
  ​
  desc  t2;
  insert INTO t2(username, age) VALUES('小红', 20), ('小明', 21);
  ​
  SELECT * FROM t2;
  ​
  CREATE TABLE t3(
      id int PRIMARY KEY auto_increment, # 添加主键, 并且设置主键自增;
      username VARCHAR(32),
      age int auto_increment #  不是每个字段都可以使用auto_increment的. 必须有字段上有key;
  );
  ​
  CREATE TABLE t4(
      id int PRIMARY KEY, # 
      username VARCHAR(32),
      age int #
  );
  ​
  DESC t4;
  ​
  ALTER TABLE t4 MODIFY id int auto_increment; # 添加自增;
  ​
  ALTER TABLE t4 MODIFY id int ; #  删除自增长
  ​
  # 非空约束
  CREATE TABLE t5(
      id int PRIMARY KEY auto_increment,  
      username VARCHAR(32) NOT NULL,
      age int 
  );
  ​
  DESC t5;
  INSERT INTO t5(username, age) VALUES('小红', 20), ('小明', 22);
  ​
  SELECT * FROM t5;
  ​
  CREATE TABLE t6(
      id int PRIMARY KEY auto_increment,  
      username VARCHAR(32),
      age int 
  );
  ​
  DESC t6;
  # 建完表之后添加非空约束
  ALTER TABLE t6 MODIFY username VARCHAR(32) NOT NULL;
  # 删除非空约束
  ALTER TABLE t6 MODIFY username VARCHAR(32);
  ​
  # 唯一约束,就是字段「列的值」 的值不能重复;
  CREATE TABLE t7(
      id int PRIMARY KEY auto_increment,  
      username VARCHAR(32) NOT NULL,
      phone INT(5) UNIQUE # unique , 表示唯一约束, 加了它,字段的值不能重复了;
  );
  ​
  DESC t7;
  INSERT INTO t7(username, phone)VALUE('小红', 12345), ('小明', 12345);
  ​
  INSERT INTO t7(username, phone)VALUE('小红', 12345), ('小明', 12343);
  SELECT * FROM t7;
  ​
  CREATE TABLE t8(
      id int PRIMARY KEY auto_increment,  
      username VARCHAR(32) NOT NULL,
      phone INT(5) 
  );
  ​
  DESC t8;
  ALTER table t8 MODIFY phone int(5) UNIQUE;
  ​
  # 删除唯一约束;
  ALTER table t8 MODIFY phone int(5); # 错误的姿势,此种方式虽然不报错,但是无法删除掉唯一约束;
  ALTER TABLE t8 DROP INDEX phone; -- 删除唯一约束的正确姿势;

6. 外键约束「foreign key」

• 让表于表产生关系，从而保证数据的正确性。

• 在创建表时，可以添加外键

  create table 表名(
  	id int primary key auto_increment, # 给id添加主键约束, 并且设置主键自增长
      name varchar(32) not null, # 设置非空约束
      phone_number varchar(20) unique # 添加非空约束 
      # 外键字段
    	外键字段 int;
    	# []表示可选, 可以不写;
      [constraint 外键名称] foreign key(外键的字段名称) references 主表(主键的名称「主键的字段名称」)

• 创建表之后，添加外键

  ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

• 删除外键

  alter table 表名 drop foreign key 外键名称;

• 场景

  • 创建一个员工表, 包括(id, name, age, dep_name, dep_location)字段;

    # 创建一张员工表. 
    CREATE TABLE emp(
    	id int PRIMARY KEY AUTO_INCREMENT,
    	account VARCHAR(32) NOT NULL,
    	age int,
    	dep_name VARCHAR(32), # 部门名称
    	dep_location VARCHAR(32) # 部门地址
    );
    
    # 插入一些测试数据
    INSERT INTO emp (account, age, dep_name, dep_location) VALUES ('张小三', 20, '研发部', '北京');
    INSERT INTO emp (account, age, dep_name, dep_location) VALUES ('李小四', 21, '研发部', '北京');
    INSERT INTO emp (account, age, dep_name, dep_location) VALUES ('王小五', 20, '研发部', '北京');
    INSERT INTO emp (account, age, dep_name, dep_location) VALUES ('马小六', 20, '销售部', '燕郊');
    INSERT INTO emp (account, age, dep_name, dep_location) VALUES ('田小七', 22, '销售部', '燕郊');
    INSERT INTO emp (account, age, dep_name, dep_location) VALUES ('王小八', 18, '销售部', '燕郊');
    
    SELECT * FROM emp;

  • 解决办法

    拆分成两部分, 这里涉有了数据库设计的范式. 后面讲;

    • 拆分表

      • 部门表, department

      • 员工表, employee

    • 部门表, 主表.

      # 创建部门表
      CREATE TABLE department(
      	id int PRIMARY KEY AUTO_INCREMENT,
      	dep_name VARCHAR(32),
      	dep_location VARCHAR(32)
      );
      
      # 测试测试数据
      insert into department values(1, '研发部','北京'),(2, '销售部', '燕郊');
      
      SELECT * FROM department;

    • 员工表, 从表

      # 创建员工表
      CREATE TABLE employee(
      	id int PRIMARY KEY AUTO_INCREMENT,
      	account VARCHAR(32) not null,
      	age TINYINT,
      	dep_id int # 外键对应的主键
      );

      问题:

      • 在员工表中可以添加部门表中没有的部门id. 实际开发当中不应该出现此种问题.

        • employee中的dep_id中的数据只能是department表中的已经存在的id值;

      解决办法:

      • 必须让两张表产生某种关系才可以.

      • 使用==外键约束==

• 使用外键约束

  • 删除掉员工表:

    drop table employee;

  • 在创建从表employee的时候添加外键约束.

    # 创建员工表
    CREATE TABLE employee(
        id int PRIMARY KEY AUTO_INCREMENT,
        account VARCHAR(32) not null,
        age TINYINT,
        dep_id int # 外键对应的主键
        # 员工表employee创建外键约束. 
      constraint emp_depid_fk foreign key (dep_id) references department(id);
    );
    ​
    # 正常添加数据
    INSERT INTO employee (account, age, dep_id) VALUES ('张小三', 20, 1);
    INSERT INTO employee (account, age, dep_id) VALUES ('李小四', 21, 1);
    INSERT INTO employee (account, age, dep_id) VALUES ('王小五', 20, 1);
    INSERT INTO employee (account, age, dep_id) VALUES ('马小六', 20, 2);
    INSERT INTO employee (account, age, dep_id) VALUES ('田小七', 22, 2);
    INSERT INTO employee (account, age, dep_id) VALUES ('王小八', 18, 2);
    ​
    # 查询一下
    select * from employee;
    ​
    # 添加一些在department表中id列中不存在的数据;
    INSERT INTO employee (account, age, dep_id) VALUES ('苏小九', 18, 3);
    ​
    #INSERT INTO employee (account, age, dep_id) VALUES ('苏小九', 18, 3)
    #> 1452 - Cannot add or update a child row: a foreign key constraint fails (`db0`.`employee`, CONSTRAINT `emp_depid_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))

7. 级联操作「自己看看即可」

• 概念: 在修改或者删除主表的主键时, 同时更新或者修改副表外键的值. 称为级联操作;

1. 添加级联操作

   # 语法
   ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;

2. 分类

   • 级联更新: ON UPDATE CASCADE

   • 级联删除: ON DELETE CASCADE

8. 总结一波

约束名称	关键字	描述
主键	primary key	唯一,且非空,每张表只能由一个主键
默认	default	如果这一列没有值,则使用设置的默认值
非空	not null	这一列必须有值
唯一	unique	这一列不能有重复的值
外键	foreign	主表中的主键列, 副表中的外键列

第十章: 数据库的表之间的关系

1. 分类

1. 一对一

举例: 像人和身份证的关系. 
一个人只能有一个身份证号, 一个身份证号只能对应一个人;

2. 一对多「多对一」

举例: 部门和员工 班级和学生
一个部门可有多个员工, 但是一个员工只能对应一个部门;

3. 多对多

举例: 学生和课程
一个学生可以选择多门课程, 一个课程也可以对应多个学生;

2. 实现关系「重点」

1. 一对多「多对一」

部门「和」和员工
实现方式: ==在多的一方建立外键 指向一的一方的主键==

2. 多对多

学生和课程
多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键

3. 一对一「了解」

人和身份证号
实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。

第十一章: 数据库的备份和还原

1. 后期知识点 使用docker数据卷备份与还原   === mysqldump -- 不推荐使用

第十二章 : 数据库设计「j」(了解前3范式)

1. 范式的概述

范式来自英文Normal form，简称NF。要想设计—个好的关系，必须使关系满足一定的约束条件，此约束已经形成了规范，分成几个等级，一级比一级要求得严格。满足这些规范的数据库是简洁的、结构明晰的，同时，不会发生插入(insert)、删除(delete)和更新(update)操作异常。反之则是乱七八糟，不仅给数据库的编程人员制造麻烦，而且面目可憎，可能存储了大量不需要的冗余信息。

2. 三大范式

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般来说，数据库只需满足第三范式(3NF）就行了

3. 1NF

数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。即表中的某个列有多个值时，必须拆分为不同的列。==简而言之，第一范式每一列不可再拆分，称为原子性==

4. 2NF

在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）. 简单来说. ==在满足第一范式的前提下，表中的每一个字段都完全依赖于主键。==

1. 几个重要的概念

• 函数依赖

  函数依赖：A-->B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A. 例如：学号-->姓名。 （学号，课程名称） --> 分数. 身份证号和人的关系. 身份证号 --> 人

• 完全函数依赖, ==针对于属性组==

  完全函数依赖：A-->B， 如果A是一个属性组「多个属性组合的集合」，则B属性值的确定需要依赖于A属性组中所有的属性值. 例如：（学号，课程名称） -- > 姓名. (身份证号, 男) -> 人. (A+B)-> C,

• 部分函数依赖：==针对于属性组.==

  A-->B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可. 例如：（学号，课程名称） -- > 姓名

• 传递函数依赖

  A-->B, B -- >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A

  例如：学号-->系名，系名-->系主任

• 码

  如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码

  例如：该表中码为：（学号，课程名称）

  主属性：码属性组中的所有属性

  非主属性：除过码属性组的属性

5. 3NF

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

• 要求:

  • 理解到码, 主属性, 非主属性

  • 完全依赖, 部分依赖, 传递依赖

  • 属性组

第十三章: 多表查询

1. 前置准备

• 知识回顾

  select 
  	需要查询的字段
  from 
  	表名 别名
  where 
  	各种条件
  group by
  	分组字段
  order by
  	排序字段
  limit 
  
  

  • DQL查询

    • 主/外键

    • 非空

    • 唯一

  • 约束

  • 数据库的三大范式(了解)

  • 查询语法

2. 数据准备

SHOW TABLES;

# 创建部门表department
CREATE TABLE department(
	id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(32) NOT NULL
);

# 插入一些数据
INSERT INTO department(name) VALUES
("市场部"), ("研发部"), ("公关部");

# 创建员工表 employee
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT, 
	name VARCHAR(32) NOT NULL,
	gender VARCHAR(1) NOT NULL,  -- 性别
	salary DOUBLE NOT NULL, -- 工资
	create_date DATE, -- 入职日期
	dept_id INT NOT NULL, -- 外键字段
	FOREIGN KEY(dept_id) REFERENCES department(id) -- 添加外键, 关联department表中的主键
);

# 添加一些数据
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('张无忌','男',7200,'2013-02-24',1);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('张三丰','男',3600,'2010-12-02',2);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('赵敏','女',9000,'2008-08-08',2);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('周芷若','女',5000,'2015-10-07',3);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('唐三','男',4020,'2017-03-14',1);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('唐舞麟','男',4530,'2016-04-14',1);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('唐舞桐','女',5700,'2014-08-14',3);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('古月','女',5100,'2016-03-12',3);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('伍六七','男',6300,'2014-04-15',2);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('云韵','女',7500,'2011-03-16',1);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('美杜莎','女',2500,'2010-03-14',2);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('孙悟空','男',9500,'2020-05-14',2);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('朱八戒','男',5500,'2019-03-11',1);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('西门吹雪','男',2500,'2013-03-14',3);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('天山童姥','女',4300,'2011-05-14',1);

# 查看数据
SELECT * FROM employee;

3. 笛卡尔积

4. 多表查询分类

1. 内连接查询

   1. 隐式内连接

   2. 显示内连接

2. 外连接查询

   1. 左外连接查询

   2. 右外连接查询

3. 子查询

   1. 查询中嵌套查询，称嵌套查询为子查询。

      ## 1. 查询最高工资是是多少钱
      ## 2. 查询出最高工资的是谁. 
      ## 3. 合并成一条. 

5. 内连接查询 - 隐式内连接(用的很少)

• 使用where条件消除无用数据

  -- 查询所有员工的信息和对应部门的信息;
  -- 查询员工的姓名,性别和部门名称

6. 内连接查询 - 显示内连接

• 语法格式

  select 字段列表 from 表名1 inner join 表名2 on 查询条件;
  -- 查询所有员工的信息和对应部门的信息;
  -- 查询员工的姓名,性别和部门名称
  
  # 内连接操作步骤
  # 1. 先确定从哪些表中查询数据
  # 2. 给定查询的条件
  # 3. 最后找出查询的字段即可;

7. 外连接查询 - 左外连接查询

• 语法格式

  # 1. 左外连接
  # 语法: SELECT 字段列表 FROM 表1 LEFT [outer] JOIN 表2 on 条件;
  # 查询结果是: 左边的所有数据 + 与连接表交集的部分;
  
  -- 例: 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称;

8. 外连接查询 - 右外连接查询

• 语法格式

  # 2. 右外连接
  # 语法: SELECT 字段列表 FROM 表1 RIGHT [outer] JOIN 表2 on 条件;
  # 查询结果是: 右边的所有数据 + 与连接表交集的部分;
  
  # 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称; 

9. 子查询 - 结果是单行单列

• 子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =

   -- 查询员工最高工资的人
   -- 查询员工工资小于平均工资的人

10. 子查询 - 结果是多行单列

• 子查询可以作为条件，使用运算符in来判断

   -- 查询'财务部'和'市场部'所有的员工信息

11. 子查询 - 结果是多行多列

• 子查询可以作为一张虚拟表参与查询

  -- 查询员工入职日期是2013--03-14日之后的员工信息和部门信息

12. 参考代码

-- 1. 查询所有员工的信息和对应部门的信息;
SELECT * FROM employee AS emp, department AS dep WHERE emp.dept_id = dep.id;

-- 2. 查询员工的姓名,性别和部门名称
SELECT
	emp.`name` 姓名, -- 员工姓名
	emp.gender 性别, -- 员工性别,
	dep.`name` 所在部门-- 部门名称
FROM
	employee AS emp, -- 员工表
	department AS dep -- 部门表
WHERE
	emp.dept_id = dep.id;
	
#############显示内连接################
-- 1. 查询所有员工的信息和对应部门的信息;
SELECT
*
FROM
	employee AS emp
INNER JOIN
	department AS dep
ON
	emp.dept_id = dep.id;
-- 2. 查询员工的姓名,性别和部门名称
SELECT
	emp.`name` 姓名, -- 员工姓名
	emp.gender 性别, -- 员工性别,
	dep.`name` 所在部门-- 部门名称
FROM
	employee AS emp
INNER JOIN
	department AS dep
ON
	emp.dept_id = dep.id;
	
# 内连接操作步骤
# 1. 先确定从哪些表中查询数据
# 2. 给定查询的条件
# 3. 最后找出查询的字段即可;
	
################## 外连接查询#################
# 1. 左外连接
# 语法: SELECT 字段列表 FROM 表1 LEFT [outer] JOIN 表2 on 条件;
# 查询结果是: 左边的所有数据 + 与连接表交集的部分;
# 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称; 
SELECT
	emp.*,
	dep.`name`
FROM
	employee AS emp
LEFT OUTER JOIN
	department AS dep
ON
	emp.dept_id = dep.id;

# 2. 右外连接
# 语法: SELECT 字段列表 FROM 表1 RIGHT [outer] JOIN 表2 on 条件;
# 查询结果是: 右边的所有数据 + 与连接表交集的部分;

# 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称; 
SELECT
	*
FROM
	employee AS emp
RIGHT OUTER JOIN
	department AS dep
ON
	emp.dept_id = dep.id;
	
	
SELECT
	*
FROM
	department AS dep
RIGHT OUTER JOIN
	employee AS emp
ON
	emp.dept_id = dep.id;
############$$$$$$$$$$$$$$$$$$$$$$# 子查询###########
--  查询员工工资小于平均工资的人
# 查出平均工资
SELECT
	MAX(emp.salary)
FROM
	employee AS emp;
	
# 查询最高工资的人
SELECT
	emp.`name`
FROM
	 employee AS emp
WHERE
	emp.salary = 9500;
	
# 合并成一句
SELECT
	emp.`name`
FROM
	employee AS emp
WHERE
	emp.salary = (SELECT MAX(emp.salary) FROM employee AS emp)
	
## 查询员工工资小于平均工资的人
# 1. 查询到平均工资. 
# 返回的是一行一列. 可以把查询结果用作查询条件;
SELECT 
	AVG(emp.salary)
FROM
	employee AS emp;

# 2. 根据获取的查询条件. 获取最终的查询结果;
SELECT
	emp.`name`,
	emp.salary
FROM
	employee AS emp
WHERE emp.salary < (SELECT 
	AVG(emp.salary)
FROM
	employee AS emp);


# 查询'财务部'和'市场部'所有的员工信息
# 获取id, 查询结果是多行单列的.可以用作查询条件.使用in来判断;
SELECT 
	dep.id
FROM
	department as dep
WHERE 
	dep.`NAME` = '市场部' 
OR
	dep.`NAME` = '研发部';
	
SELECT
*
FROM
	employee AS emp
WHERE 
	emp.dept_id
IN
	(SELECT 
	dep.id
FROM
	department as dep
WHERE 
	dep.`NAME` = '市场部' 
OR
	dep.`NAME` = '研发部');

# 子查询的结果是多行多列的
# 查询员工入职日期是2013-03-14日之后的员工信息和部门信息
# 查询入职日期2013-03-14之后的员工信息
# 返回结果是多行多列的. 子查询可以作为一张虚拟表参与查询
SELECT
	*
FROM
	employee AS emp
WHERE
	emp.create_date > "2013-03-14";
SELECT
	*
FROM
	department AS dep,
	(SELECT * FROM employee AS emp WHERE emp.create_date > "2013-03-14") AS t
WHERE
	t.dept_id = dep.id;
	
# 使用普通内连接
# 查询员工入职日期是2013-03-14日之后的员工信息和部门信息
SELECT
	*
FROM
	employee AS emp,
	department AS dep
WHERE
	emp.dept_id = dep.id
AND
	emp.create_date > "2013-03-14";

• 二次参考

# 创建部门表department
DROP TABLE IF EXISTS department;
CREATE TABLE department(
    id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(32) NOT NULL
);
​
# 插入一些数据
INSERT INTO department(name) VALUES
("市场部"), ("研发部"), ("公关部");
​
# 创建员工表 employee
DROP TABLE IF EXISTS employee;
CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(32) NOT NULL,
    gender CHAR(1) NOT NULL,  -- 性别
    salary DOUBLE NOT NULL, -- 工资
    create_date DATE, -- 入职日期
    dept_id INT NOT NULL, -- 外键字段
    FOREIGN KEY(dept_id) REFERENCES department(id) -- 添加外键, 关联department表中的主键
);
​
# 添加一些数据
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('张无忌','男',7200,'2013-02-24',1);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('张三丰','男',3600,'2010-12-02',2);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('赵敏','女',9000,'2008-08-08',2);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('周芷若','女',5000,'2015-10-07',3);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('唐三','男',4020,'2017-03-14',1);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('唐舞麟','男',4530,'2016-04-14',1);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('唐舞桐','女',5700,'2014-08-14',3);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('古月','女',5100,'2016-03-12',3);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('伍六七','男',6300,'2014-04-15',2);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('云韵','女',7500,'2011-03-16',1);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('美杜莎','女',2500,'2010-03-14',2);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('孙悟空','男',9500,'2020-05-14',2);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('朱八戒','男',5500,'2019-03-11',1);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('西门吹雪','男',2500,'2013-03-14',3);
INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('天山童姥','女',4300,'2011-05-14',1);
​
SELECT * FROM employee;
SELECT * FROM department;
​
# 查询多张表. 
SELECT
    * 
FROM    
    department as d,
    employee as e;
​
SELECT
    * 
FROM    
        employee as e,
        department as d;
​
# 笛卡尔基
​
# 内连接之隐式内连接查询
-- 查询所有员工的信息和对应部门的信息;
-- 查询员工的姓名,性别和部门名称
SELECT
    *
FROM
    employee as e,
    department as d
WHERE
# 员式表中的dept_id和部门表中的主键是对应的.换句话说, 员式表中填写的dept_id都是来自于部门表的主键;
    e.dept_id = d.id; 
​
-- 查询员工的姓名,性别和部门名称
SELECT
    e.name AS 员工姓名,
    e.gender AS 员工性别,
    d.name AS 部门信息
FROM
    employee e,
    department AS d
WHERE
    e.dept_id = d.id;
​
# 内连接, 显示的内连接
#  []是可以省略掉的;
# select 字段列表 from 表名1 [inner] join 表名2 on 查询条件;
# 内连接操作步骤
# 1. 先确定从哪些表中查询数据
# 2. 给定查询的条件
# 3. 最后找出查询的字段即可;
​
-- 查询所有员工的信息和对应部门的信息;
-- 查询员工的姓名,性别和部门名称
SELECT 
*
FROM
    employee as e
INNER JOIN  
    department AS d
ON
    e.dept_id = d.id;
    
# inner可以省略掉;
SELECT 
*
FROM
    employee as e
JOIN    
    department AS d
ON
    e.dept_id = d.id;
    
# 查询指定的字段
SELECT 
    e.name AS 员工姓名,
    e.gender AS 员工性别,
    d.name AS 部门信息
FROM
    employee as e
INNER JOIN  
    department AS d
ON
    e.dept_id = d.id;
​
######################### 外连接 #######################
# 1. 左外连接
# 语法: SELECT 字段列表 FROM 表1 LEFT [outer] JOIN 表2 on 条件;
# 查询结果是: 左边的所有数据 + 与连接表交集的部分;
DESC employee;
DESC department;
INSERT INTO department(name) VALUES('销售部');
SELECT * FROM department;
SELECT
    *
FROM
    department as d
LEFT OUTER JOIN
    employee as e
ON
    e.dept_id = d.id;
​
-- 例: 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称;
-- 反过来. 试一下;
-- INSERT INTO employee(NAME,gender,salary,create_date,dept_id) VALUES('云锋','男',4300,'2021-05-14',NULL);
SELECT
    *
FROM
    employee as e
LEFT JOIN
    department as d
ON
    e.dept_id = d.id;
​
​
# 右外连接
SELECT
*
FROM
    employee AS e
RIGHT JOIN
    department as d
ON
    e.dept_id = d.id;
​
    
    
## 子查询
-- 查询员工工资小于平均工资的人
SELECT * from employee;
​
SELECT
    AVG(e.salary)
FROM 
    employee as e; #  结果是单行单列的.才可以做为一个查询条件;
    
# 5483.333333333333 
SELECT
 *
FROM
    employee as e
WHERE
    e.salary < 5483.333333333333;   
​
# 使用子查询,就是把单行单列的查询结果, 作为另一个查询的条件;
SELECT
    * 
FROM
    employee as e
WHERE
    e.salary < (SELECT AVG(e1.salary) FROM employee as e1);
​
    
# -- 查询'财务部'和'市场部'所有的员工信息
​
# 查询出市场部的id和公关部的id
SELECT
    d.id 
FROM
    department as d
WHERE
    d.name = '市场部'
    OR
    d.name = '公关部';
    
SELECT
    * 
FROM
    employee as e
WHERE
    e.dept_id = 1
    OR
    e.dept_id = 3;
    
SELECT
    *  
FROM
    employee as e
WHERE
    e.dept_id IN(1,3);
​
​
SELECT
    *  
FROM
    employee as e
WHERE
    e.dept_id IN(
            SELECT
                d.id 
            FROM
                department as d
            WHERE
                d.name = '市场部'
                OR
                d.name = '公关部' # 查询出的结果是单列多行的,可以作为一个in ()
    );
​
#### 多行多列的情况
-- 查询员工入职日期是2013--03-14日之后的员工信息和部门信息
DESC employee;
SELECT   * FROM employee;
-- 先查询出, 入职日期是2013年3月14号之后的所有员工信息
SELECT
    * 
FROM    
    employee as e
WHERE
    e.create_date > '2013-3-14';
    
# 查询员工所对应的部门信息
SELECT
    *
FROM
    (
        SELECT
            * 
        FROM    
            employee as e
        WHERE
            e.create_date > '2013-3-14'
    ) as e, 
    department as d
WHERE
    e.dept_id = d.id;
​
# 子查询
SELECT
    *
FROM
    employee as e, 
    department as d
WHERE
    e.dept_id = d.id;
​
###############
# 强烈推荐此种写法;
SELECT
    *
FROM
    employee as e,
    department as d
WHERE
    e.dept_id = d.id
    AND
    e.create_date > '2013-3-14';
    
# 子查询
# 能不用就不用子查询.
# 可以把子查询拆分成两长或者多条查询语句, 最终把结果查询出来;
​
# 分三种情况: 
# 1. 结果是单行单列的,可以作为查询条件,可以使用 >=, and or 进行查询操作;
# 2. 结果是单列多行的,可以作为 IN(子查询结果是单列多行的)
# 3. 结果是多行多列的可以作为一张虚拟表继续查询;
​
# 外边接
# 记住一种, 左外连接. 
# SELECT * FROM tab1 left [outter] join tab2 on 条件;
​
# 内连接
# 显示/隐匿
# SELECT * FROM tab1 [inner]join tab2 on 条件;
​
##########################
# dql查询. 「非常重要的内容」
# 约束

13.练习题

==第十四章: 事务「重要但是简单易学」==

1. 概念

• 如果一个包含多个步骤的业务操作事务管理,那么这些操作要么同时成功,要么同时失败;

2. 基本操作

# 1. 开启事务: start transaction;
# 2. 回滚事务: rollback
# 3. 提交: commit

3. 使用举例

1. 转账的例子;

4. 事务提交的两种方式

1. 自动提交, 这也是mysql当中默认的方式;

   1. 一条dml语句(增删改)会自动提交一次事务.

2. 手动提交

   1. 需要先手动开启事务, 再进行提交;

3. 修改事务的提交方式

   • 查看事务的提交方式

     查看事务的默认提交方式：SELECT @@autocommit; -- 1 代表自动提交  0 代表手动提交

   • 修改提交方式

     set @@autocommit = 0;

4. 事务的原理

   事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表中，其他任何情况都会清空事务日志(rollback，断开连接)

   注意: 在start transaction之后立即执行select 查表数据, 可以查到未commit的记录, 除此之外的任何地方都查不到了

   

• 事务的步骤

  1. 客户端连接数据库服务器，创建连接时创建此用户临时日志文件

  2. 开启事务以后，所有的操作都会先写入到临时日志文件中

  3. 所有的查询操作从表中查询，但会经过日志文件加工后才返回

  4. 如果事务提交则将日志文件中的数据写到表中，否则清空日志文件

  注意: START TRANSACTION;其后执行的dml语句都会被写入到临时日志文件中,无论此时@@autocommit被设置为1或者0

• 回滚点

  在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。

  • 操作语句

    回滚点的操作语句	语句
    设置回滚点	savepoint 名字
    回到回滚点	rollback 名字

  • 实际操作

    # 转账的案例
    # 1. 将所有的数据恢复到1000块钱;
    # 2. 开启事务
    # 3. 操作张小三的账号, 每次减10块钱. 操作2次;
    # 4. 设置回滚点. savepoint two_times;
    # 5. 继续操作张小三的账号, 操作2次. 
    # 6. 手动事务的回滚, rollback to two_times 回滚到回滚点.

    示例代码:

    建表account, 列为id和money
    
    SELECT * FROM account;
    UPDATE account set money = 1000;
    START TRANSACTION;
    UPDATE account set money = money - 10 WHERE id = 1;
    UPDATE account set money = money - 10 WHERE id = 1;
    # 设置回滚点
    SAVEPOINT two_times;
    UPDATE account set money = money - 10 WHERE id = 1;
    UPDATE account set money = money - 10 WHERE id = 1;
    # 回滚到回滚点
    ROLLBACK TO two_times;

    ==设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。==

5. 事务的四大特征「了解」

1. 原子性「Atomicity」：每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功，要么都失败。

2. 持久性「Durability」：当事务提交或回滚后，数据库会持久化的保存数据。

3. 隔离性「Isolation」：事务与事务之间不应该相互影响，执行时保持隔离的状态。

4. 一致性「Consistency」：事务操作前后，数据总量不变

6. 事务的隔离级别「了解」

概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

1. 存在的问题

   1. 脏读：一个事务，读取到另一个事务中没有提交的数据

   2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。

   3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改;

7. 隔离级别

1. read uncommitted：读未提交

   1. 产生的问题：脏读、不可重复读、幻读

2. read committed：读已提交 （Oracle）

   1. 产生的问题：不可重复读、幻读

3. repeatable read：可重复读 （MySQL默认）

   1. 产生的问题：幻读

4. serializable：串行化

   1. 可以解决所有的问题

==注意：隔离级别从小到大安全性越来越高，但是效率越来越低==;

MySQL 中事务的隔离级别一共分为四种，分别如下：

• 序列化（SERIALIZABLE）

• 可重复读（REPEATABLE READ）

• 提交读（READ COMMITTED）

• 未提交读（READ UNCOMMITTED）

四种不同的隔离级别含义分别如下：

1. SERIALIZABLE

如果隔离级别为序列化，则用户之间通过一个接一个顺序地执行当前的事务，这种隔离级别提供了事务之间最大限度的隔离。

2.REPEATABLE READ

在可重复读在这一隔离级别上，事务不会被看成是一个序列。不过，当前正在执行事务的变化仍然不能被外部看到，也就是说，如果用户在另外一个事务中执行同条 SELECT 语句数次，结果总是相同的。（因为正在执行的事务所产生的数据变化不能被外部看到）。

3.READ COMMITTED

READ COMMITTED 隔离级别的安全性比 REPEATABLE READ 隔离级别的安全性要差。处于 READ COMMITTED 级别的事务可以看到其他事务对数据的修改。也就是说，在事务处理期间，如果其他事务修改了相应的表，那么同一个事务的多个 SELECT 语句可能返回不同的结果。

4.READ UNCOMMITTED

READ UNCOMMITTED 提供了事务之间最小限度的隔离。除了容易产生虚幻的读操作和不能重复的读操作外，处于这个隔离级的事务可以读到其他事务还没有提交的数据，如果这个事务使用其他事务不提交的变化作为计算的基础，然后那些未提交的变化被它们的父事务撤销，这就导致了大量的数据变化。

在 MySQL 数据库种，默认的事务隔离级别是 REPEATABLE READ

查询隔离级别:

select @@tx_isolation; # 5.7版本
select @@transaction_isolation; # 8.0版本的命令;

2.1 查看隔离级别

通过如下 SQL 可以查看数据库实例默认的全局隔离级别和当前 session 的隔离级别：

MySQL8 之前使用如下命令查看 MySQL 隔离级别：

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

可以看到，默认的隔离级别为 REPEATABLE-READ，全局隔离级别和当前会话隔离级别皆是如此。

MySQL8 开始，通过如下命令查看 MySQL 默认隔离级别：

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;

就是关键字变了，其他都一样。

通过如下命令可以修改隔离级别（建议开发者在修改时修改当前 session 隔离级别即可，不用修改全局的隔离级别）：

设置数据库的隔离级别:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

上面这条 SQL 表示将当前 session 的数据库隔离级别设置为 READ UNCOMMITTED，设置成功后，再次查询隔离级别，发现当前 session 的隔离级别已经变了

注意，如果只是修改了当前 session 的隔离级别，则换一个 session 之后，隔离级别又会恢复到默认的隔离级别，所以我们测试时，修改当前 session 的隔离级别即可。

8. 演示事务的隔离级别

2.2.1 准备测试数据

READ UNCOMMITTED 是最低隔离级别，这种隔离级别中存在脏读、不可重复读以及幻象读问题，所以这里我们先来看这个隔离级别，借此大家可以搞懂这三个问题到底是怎么回事。

下面分别予以介绍。

首先创建一个简单的表，预设两条数据，如下：

CREATE TABLE account (
	id BIGINT UNSIGNED primary key,
	name varchar(100) NULL,
	balance BIGINT UNSIGNED NULL,
	CONSTRAINT account_un UNIQUE KEY (name)
);

表的数据很简单，有 javaboy 和 itboyhub 两个用户，两个人的账户各有 1000 人民币。现在模拟这两个用户之间的一个转账操作。

注意，如果读者使用的是 dbeaver的话，不同的查询窗口就对应了不同的 session

2.2.2 脏读

一个事务读到另外一个事务还没有提交的数据，称之为脏读。具体操作如下：

1. 首先打开两个SQL操作窗口，假设分别为 A 和 B，在 A 窗口中输入如下几条 SQL （输入完成后不用执行）：

A窗口

START TRANSACTION; 
UPDATE account set balance=balance+100 where name='javaboy'; 
UPDATE account set balance=balance-100 where name='itboyhub'; 
COMMIT;

.在 B 窗口执行如下 SQL，修改默认的事务隔离级别为 READ UNCOMMITTED，如下：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

接下来在 B 窗口中输入如下 SQL，输入完成后，首先执行第一行开启事务（注意只需要执行一行即可）：

START TRANSACTION; 
SELECT * from account; 
COMMIT;

接下来执行 A 窗口中的前两条 SQL，即开启事务，给 javaboy 这个账户添加 100 元。

5.进入到 B 窗口，执行 B 窗口的第二条查询 SQL（SELECT * from account;），结果如下：

可以看到，A 窗口中的事务，虽然还未提交，但是 B 窗口中已经可以查询到数据的相关变化了。

2.2.3 不可重复读

不可重复读是指一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。具体操作步骤如下（操作之前先将两个账户的钱都恢复为1000）：

1. 首先打开两个查询窗口 A 和 B ，并且将 B 的数据库事务隔离级别设置为 READ UNCOMMITTED。具体 SQL 参考上文，这里不赘述。

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

1. 在 B 窗口中输入如下 SQL，然后只执行前两条 SQL 开启事务并查询 javaboy 的账户：

START TRANSACTION; 
SELECT * from account where name='javaboy'; 
COMMIT;

前两条 SQL 执行结果如下：

3.在 A 窗口中执行如下 SQL，给 javaboy 这个账户添加 100 块钱，如下：

START TRANSACTION; 
UPDATE account set balance=balance+100 where name='javaboy'; 
COMMIT;

4.再次回到 B 窗口，执行 B 窗口的第二条 SQL 查看 javaboy 的账户，结果如下：

javaboy 的账户已经发生了变化，即前后两次查看 javaboy 账户，结果不一致，这就是不可重复读。

和脏读的区别在于，脏读是看到了其他事务未提交的数据，而不可重复读是 同一次事务中查询两次得到的结果不相同。

2.2.4 幻象读

幻象读和不可重复读非常像，看名字就是产生幻觉了。

我举一个简单例子。

在 A 窗口中输入如下 SQL：

START TRANSACTION; 
insert into account(id,name,balance) values(3,'zhangsan',1000); 
COMMIT;

然后在 B 窗口输入如下 SQL：

START TRANSACTION; 
SELECT * from account; 
delete from account where name='zhangsan'; 
COMMIT;

我们执行步骤如下：

1. 首先执行 B 窗口的前两行，开启一个事务，同时查询数据库中的数据，此时查询到的数据只有 javaboy 和 itboyhub。

2. 执行 A 窗口的前两行，向数据库中添加一个名为 zhangsan 的用户，注意不用提交事务。

3. 执行 B 窗口的第二行，由于脏读问题，此时可以查询到 zhangsan 这个用户。

4. 执行 B 窗口的第三行，去删除 name 为 zhangsan 的记录，这个时候删除就会出问题，虽然在 B 窗口中可以查询到 zhangsan，但是这条记录还没有提交，是因为脏读的原因才看到了，所以是没法删除的。此时就产生了幻觉，明明有个 zhangsan，却无法删除。

这就是幻读。

看了上面的案例，大家应该明白了脏读、不可重复读以及幻读各自是什么含义了。

2.3 READ COMMITTED

和 READ UNCOMMITTED 相比，READ COMMITTED 主要解决了脏读的问题，对于不可重复读和幻象读则未解决。

将事务的隔离级别改为 READ COMMITTED 之后，重复上面关于脏读案例的测试，发现已经不存在脏读问题了；重复上面关于不可重复读案例的测试，发现不可重复读问题依然存在。

上面那个案例不适用于幻读的测试，我们换一个幻读的测试案例。

还是两个窗口 A 和 B，将 B 窗口的隔离级别改为 READ COMMITTED，

b窗口执行如下语句

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

然后在 A 窗口输入如下测试 SQL：

START TRANSACTION; 
insert into account(id,name,balance) values(3,'zhangsan',1000); 
COMMIT;

在 B 窗口输入如下测试 SQL：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
​
START TRANSACTION; 
SELECT * from account; 
insert into account(id,name,balance) values(4,'zhangsan',1000); 
COMMIT;

测试方式如下：

1. 首先执行 B 窗口的前两行 SQL，开启事务并查询数据，此时查到的只有 javaboy 和 itboyhub 两个用户。

2. 执行 A 窗口的前两行 SQL，插入一条记录，但是并不提交事务。

3. 执行 B 窗口的第二行 SQL，由于现在已经没有了脏读问题，所以此时查不到 A 窗口中添加的数据。

4. 执行 B 窗口的第三行 SQL，由于 name 字段唯一，因此这里会无法插入。此时就产生幻觉了，明明没有 zhangsan 这个用户，却无法插入 zhangsan。

2.4 REPEATABLE READ

和 READ COMMITTED 相比，REPEATABLE READ 进一步解决了不可重复读的问题，但是幻象读则未解决。

REPEATABLE READ 中关于幻读的测试和上一小节基本一致，不同的是第二步中执行完插入 SQL 后记得提交事务。

由于 REPEATABLE READ 已经解决了不可重复读，因此第二步即使提交了事务，第三步也查不到已经提交的数据，第四步继续插入就会出错。

注意，REPEATABLE READ 也是 InnoDB 引擎的默认数据库事务隔离级别

2.5 SERIALIZABLE

SERIALIZABLE 提供了事务之间最大限度的隔离，在这种隔离级别中，事务一个接一个顺序的执行，不会发生脏读、不可重复读以及幻象读问题，最安全。

如果设置当前事务隔离级别为 SERIALIZABLE，那么此时开启其他事务时，就会阻塞，必须等当前事务提交了，其他事务才能开启成功，因此前面的脏读、不可重复读以及幻象读问题这里都不会发生。

\3. 总结

总的来说，隔离级别和脏读、不可重复读以及幻象读的对应关系如下：

第十五章: DCL操作(了解)

1. 概念

dcl: 管理用户, 授权访问;

2. 管理用户

# 1. 添加用户：
# 语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
​
# 2. 删除用户
#  语法：DROP USER '用户名'@'主机名'
​
# 3. 修改用户密码：
# 语法: UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
# 举例: UPDATE USER SET PASSWORD = PASSWORD('000') WHERE USER = 'tom';
​
# SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
# SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

3. 忘了用户密码怎么办?

1. cmd -- > net stop mysql 停止mysql服务. * 需要管理员运行该cmd
2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables
3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
4. use mysql;
5. update user set password = password('你的新密码') where user = 'root';
6. 关闭两个窗口
7. 打开任务管理器，手动结束mysqld.exe 的进程
8. 启动mysql服务
9. 使用新密码登录

4. 查询用户

1. 切换到mysql数据库, `use mysql;`
2. 查询user表. `serlect * from user;`
# 通配符： % 表示可以在任意主机使用用户登录数据库

5. 权限管理

# 1. 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'root'@'%';
​
# 2. 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
# 给张三用户授予所有权限，在任意数据库任意表上
GRANT ALL ON *.* TO 'zhangsan'@'localhost'
​
# 3.撤销权限：
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';

第十六章: 重点内容

• DQL

• DML

• DDL(了解)

• 事务(了解)

  • 四在特征

  • 隔离级别

    • 每种隔离级别所产生的问题

    • 脏读, 不可重复读「虚读」, 幻读