从零MySQL笔记，带源码，带可视化数据库工具，带各种资源！！！！！

MySql

1、学习数据库第一步应该需要安装数据库

这个可以在B站搜索mysql安装视频跟着up主学习即可.

（1）使用命令行窗口连接MySQL数据库

• mysql -h 主机名 -P 端口 -u 用户名 -p密码
• 登陆前需要保证服务启动

启动MySQL数据库的常用方式：【Dos命令】

1. net stop mysql服务名
2. net start mysql服务名

（2）数据库三层结构

1. 所谓安装MySQL数据库，就是在主机安装一个数据库管理系统(DBMS)，这个管理程序可以管理多个数据库。DBMS（database manage system）
2. 一个数据库可以创建多个表，以保存数据（信息）
3. 数据库管理系统（DBMS）、数据库和表的关系如下图所示

MySQL数据库-普通表的本质仍然是文件

（3）数据在数据库中的存储方式

（4）SQL语句分类

• DDL：数据定义语句【create 库，表…】
• DML：数据操作语句【增加 insert，修改 update，删除 delete】
• DQL：数据查询语句【select】
• DCL：数据控制语句【管理数据库：比如用户权限 grant revoke】

2、如何使用数据库

（1）创建数据库

以下是创建数据库的模板，其中带[]可以不写，带<>的必须要写。

create database [if not exists] <数据库名>
[
    [default] character set [=] 字符集|
    [default] collate [=] 校对规则名称
];

下面我们来演示一下具体操作：

#创建一个名称为db01的数据库
create database dn01;
#删除数据库指令
drop database db01;
#创建一个使用utf8字符集，并带校对规则的db02数据库
create database db02 character set utf8 collate utf8_bin
#校对规则 utf8_bin 区分大小 默认 utf8_general_ci 不区分大小写

(2)查看、删除数据库

#显示数据库语句
show databases;
#显示数据库创建语句
show create database <数据库名>;
#数据库删除语句【一定要慎重使用】
drop database [if exists] <数据库名>;

练习：

#查看当前数据库服务器中的所有数据库
show databases;
#查看前面创建的db01数据库的定义信息
show create database db01;
#删除前面创建的db01数据库
drop ddatabase db01;

（3）备份恢复数据库

• 备份数据库（注意：在Dos下执行）：mysqldump -u 用户名 -p -B 数据库1 数据库2 … > 文件名.sql(路径)
• 恢复数据库（注意：进入MySQL命令行在执行）：Soursce 文件名.sql(路径)

练习：

#备份 在Dos命令行下
mysqldump -u root -p -B db01 > back.sql

#恢复 进入MySQL命令行在执行
source D:\\back.sql
#第二个回复方法，是直接点开back.sql的内容，复制所有内容，然后粘贴到mysql命令行执行

（4）备份恢复数据库的表

• 备份库的表：mysqldump -u 用户名 -p密码 数据库 表1 表2 表n > D:\文件名.sql

这里我有一个ecshop.sql文件（路径：D:\\ecshop.sql），我会将他恢复到数据库中.

#先将ecshop.sql文件恢复到数据库中，进入mysql命令行
source D:\\ecshop.sql;
#备份（进入到Dos命令下）
mysqldump -u root -p -B ecshop > D:\\beifen\\ecshop.sql;
#删除ecshop表
drop table ecshop;
#恢复表（进入mysql命令下）
source D:\\beifen\\ecshop.sql;

3、MySQL数据表操作

（1）创建表

语句格式如下：

engine字句：可选项，用于指定存储引擎。如省略则为MySQL默认的存储引擎。

create table [if not exists] <数据库表名>
(
    <字段名1> <数据类型>,
    <字段名2> <数据类型>,
    <字段名3> <数据类型>,
    ...
) [engine=table_type] [character set 字符集] [collate 校对规则];

练习：

create database db02;
use db02; //注意创建完数据库一定要指定数据库后在创建表
create table user(
	id int,
    `name` varchar(255), //这里用``是因为可能会与MySQL关键字冲突
    `password` varchar(255),
    `birthday` date)character set utf8 collate utf8_bin engine innodb;

（2）MySQL常用的数据类型

1>数值型（整数）的基本使用

2>数值型（bit）的使用

3>数值型（小数）的基本使用

4>字符串的基本使用

字符串的使用细节：

1. char(4) 这个4表示字符数（最大255），不是字节数，不管是中文还是字母都是放4个，按字符计算。
2. varchar(4) 这个4表示字符数，不管是字母还是中文都已定义好的表的编码来存放数据
3. char(4) 是定长（固定的大小），就是说，即使你插入’aa’，也会占用分配的4个字符的空间
4. varchar(4) 是边长（变化的大小），就是说，如果你插入了’aa’，实际占用空间大小并不是4个字符，而是按照实际占用空间分配（注意：varchar本身还需要占用1-3个字节来记录存放内容长度，实际数据大小+(1-3)字节）
5. 什么时候用char，什么时候用varchar？

​ 如果数据是定长，推荐使用char，比如md5的密码，邮编，手机号，身份证号码等。

​ 如果一个字段的长度是不确定的，我们使用varchar，比如留言，文章。（查询速度：char>varchar）

6. 在存放文本时，也可以使用text数据类型，可以将yexy列视为varchar列，注意text不能有默认值，大小0-2^16字节。如果希望存放更多字符，可以选择MEDIUMTEXT 0-2^24 或者 LONGTEXT0-2^32

4>日期类型的基本使用

（3）创建表练习

create table `emp`(
	id int,
    `name` varchar(32),
    sex char(1),
    birthday date,
    entry_date datetime,
    job varchar(32),
    salary double,
    `resume` text)charset utf8 collate utf8_bin engine innodb;
#添加一条记录
insert into `emp` VALUES(100, '小妖怪', '男', '2000-11-11','2010-11-10 11:11:11', '巡山的', 3000, '大王叫我来巡山');
SELECT * FROM `emp`;

（4）修改表

1、修改表名

alter table <旧表名> rename [to] <新表名>;

2、修改字段的数据类型

alter table <表明> modify <字段名> <数据类型>;

3、修改字段名

alter table <表明> change <旧字段名> <新字段名> <数据类型>;

4、添加字段

first：可选参数，作用是将新添加的字段设置为表的第一个字段。

after：可选参数，作用是将新添加的字段添加到指定的旧字段名的后面。

alter table <表名> add <新字段名> <数据类型> [约束条件] [first|after 旧字段名];

5、删除字段

alter table <表名> drop <字段名>;

6、更改表的存储引擎

alter table <表名> engine=<更改后的存储引擎>;

练习：

alter table `emp` add image varchar(32) after resume;
alter table `emp` modify job varchar(60);
alter table `emp` drop sex;
alter table `emp` rename employee;
alter table employee character set utf8;
alter table emplyee change `name` `user_name` varchar(64);

（5）复制数据表

create table [数据库名.]新表名 as select * from 原表名；//除了复制表结构，还会复制表中数据
create table [数据库名.]新表名 like 原表名;//只复制表结构

注意：使用create table语句快速复制表结构时，不会复制与表关联的其他数据库对象，如索引，主键约束，外键约束，触发器等。

（6）数据库C[create]R[read]U[update]D[delete]语句

1、Insert语句

insert into <表名> (字段1,字段2...) values (对应值1,对应值2...);

2、update语句

update <表名> set 字段1=值1... [where...];

UPDATE employee SET salary = 5000;
-- 可以修改多个列的值
UPDATE employee
SET salary = salary + 1000 , job = '出主意的' WHERE user_name = '老妖怪'

使用细节：

• update语法可以用新值更新原有表行中的各列
• set子句指示要修改哪些和要给予哪些值
• where子句指定应更新哪些行。如没有where子句，则更新所有的行（记录）
• 如果需要修改多个字段，可以通过set 字段1=值1，字段2=值2…

3、delete语句

delete from <表名> [where...]; 

delete from emplyee where user_name='老妖怪';
delete from employee;//注意这条语句会删除所有记录所以一定要小心谨慎。

注意：1、如果不使用where子句，将删除表中所有数据

2、delete语句不能删除某一列的值（可以使用update设为null或者’ '）

3、使用delete语句仅删除记录，不删除表本身。如果要删除表，使用drop table语句。drop table <表名>;

4、select语句[单表]

(1)基本语法

select [distinct] *|[字段1,字段2...] from <表名>;

注意事项（创建测试表学生表）：

1. select 指定查询哪些列的数据
2. *代表查询所有列
3. from指定查询哪张表
4. distinct可选，指显示结果时，是否去掉重复数据

练习：

#创建一个学生表
CREATE TABLE student(
	id INT NOT NULL DEFAULT 1,
    `name` VARCHAR(20) NOT NULL DEFAULT '',
    chinese FLOAT NOT NULL DEFAULT 0.0,
    english FLOAT NOT NULL DEFAULT 0.0,
    math FLOAT NOT NULL DEFAULT 0.0);
#插入数据
INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'韩顺平',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'张飞',67,98,56);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'宋江',87,78,77);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'关羽',88,98,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'赵云',82,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'欧阳锋',55,85,45);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(8,'韩信',45,65,99);
#查询表中所有学生的信息
select * from student;
#查询表中所有学生的姓名和对应的英语成绩
select `name`,english from student;
#过滤表中重复数据
select distinct * from student;

(2)使用表达式对查询的列进行运算

select *|[表达式1,表达式2...] from student;

(3)在select语句中使用as语句

select <字段1> as 别名 from 表名;

练习：

#统计每个学生的总分
select `name`,(chinese+english+math) from student;
#在所有学生总分加10分
select `name`,(chinese+english+math+10) from student;
#使用别名表示学生分数
select `name` as 名字,(chinese+english+math) as 总分 from student;

(4)在where子句中经常使用的运算符

练习：

#查询姓名为赵云的学生成绩
select * from student where `name`='赵云';
#查询英语成绩大于 90 分的同学
select * from student where english>=90;
#查询总分大于 200 分的所有同学
select * from student where (chinese + english + math)>200;
#查询 math 大于 60 并且(and) id 大于 4 的学生成绩
select * from student where math>60 and id>4;
#查询英语成绩大于语文成绩的同学
select * from student where english>chinese;
#查询总分大于 200 分 并且 数学成绩小于语文成绩,的姓赵的学生
select * from student where (chinese + english + math)>200 and math<chinese and `name` like '赵%';

#查询英语分数在 80－90 之间的同学
select * from student where english between 80 and 90;
#查询数学分数为 89,90,91 的同学。
select * from student where math in(89,90,91);
#查询所有姓李的学生成绩
select * from student where `name` like '李%';
#查询数学分>80,语文分>80的同学
select * from student math>80 and chinese<80;
#查询语文分数在70-80之间的同学
select * from student where chinese between 70 and 80;
#查询总分为189,190,191的同学
select * from student where (chinese+math+english) in (189,190,191);
#查询所有姓李或者姓宋的学生成绩
select * from student where `name` like '李%' or `name` like '宋%';
#查询数学比语文多30分的同学
select * from student where math-chinese>=30;

(5)使用order by子句排序查询结果

select <字段1>,<字段2>... from <表名> oder by <字段> [asc|desc];

asc：升序（默认） desc：降序

练习：

select * from student order by math;
select `name`,(chinese+math+english) as 总分 from student orser by 总分 desc;
select `name`,(chinese+math+english) as 总分 from student where `name` like '李%' order by 总分;

5、合计/统计函数

(1)count

select count(*)|count(列名) from <表名> [where...];

练习：

select count(*) from student;
select count(*) from student where math>90;
select count(*) from student where (math + english + chinese)>250;

count(*) 返回满足条件的记录的行数

count(列): 统计满足条件的某列有多少个，但是会排除 为 null

(2)sum

select sum(列名) from <表名> [where...];

select sum(math) from student;
select sum(chinese),sum(english),sum(math) from student;
select sum(chinese+math+english) from student;
select sum(chinese)/count(*) from student;

(3)avg

select avg(列名) from <表名> [where...];

SELECT AVG(math) FROM student;
SELECT AVG(math + english + chinese) FROM student;

(4)max/min

select max(列名) from <表名> [where...];
select min(列名) from <表名> [where...];

6、字符串相关函数

# CHARSET(str) 返回字串字符集
SELECT CHARSET(ename) FROM emp;//utf8
#CONCAT (string2 [,... ]) 连接字串, 将多个列拼接成一列
SELECT CONCAT(ename, ' 工作是 ', job) FROM emp;
#INSTR (string ,substring ) 返回 substring 在 string 中出现的位置,没有返回 0
SELECT INSTR('hanshunping', 'ping') FROM DUAL;//DUAL亚元表, 系统表 可以作为测试表使
#UCASE (string2 ) 转换成大写
SELECT UCASE(ename) FROM emp;
#LCASE (string2 ) 转换成小写
SELECT LCASE(ename) from emp;
#LEFT (string2 ,length )从 string2 中的左边起取 length 个字符
#RIGHT (string2 ,length ) 从 string2 中的右边起取 length 个字符
SELECT LEFT(ename, 2) FROM emp;
# LENGTH (string )string 长度[按照字节]
SELECT LENGTH(ename) FROM emp;
#REPLACE (str ,search_str ,replace_str )
#在 str 中用 replace_str 替换 search_str
#如果是 manager 就替换成 经理
SELECT ename, REPLACE(job,'MANAGER', '经理') FROM emp;
#STRCMP (string1 ,string2 ) 逐字符比较两字串大小
SELECT STRCMP('hsp', 'hsp') FROM DUAL;
# SUBSTRING (str,position,[length])
#从 str 的 position 开始【从 1 开始计算】,取 length 个字符
#从 ename 列的第一个位置开始取出 2 个字符
SELECT SUBSTRING(ename, 1, 2) from emp;
#LTRIM (string2 ) RTRIM (string2 ) TRIM(string)
#去除前端空格或后端空格
SELECT LTRIM(' 韩顺平教育') FROM DUAL;
SELECT RTRIM('韩顺平教育 ') FROM DUAL;
SELECT TRIM(' 韩顺平教育 ') FROM DUAL;

7、数学相关函数

这里就不在赘述了，套用即可。

-- 演示数学相关函数
-- ABS(num) 绝对值
SELECT ABS(-10) FROM DUAL; -- BIN (decimal_number )十进制转二进制
SELECT BIN(10) FROM DUAL; -- CEILING (number2 ) 向上取整, 得到比 num2 大的最小整数
SELECT CEILING(-1.1) FROM DUAL; -- CONV(number2,from_base,to_base) 进制转换
-- 下面的含义是 8 是十进制的 8, 转成 2 进制输出
SELECT CONV(8, 10, 2) FROM DUAL; -- 下面的含义是 8 是 16 进制的 8, 转成 2 进制输出
SELECT CONV(16, 16, 10) FROM DUAL; -- FLOOR (number2 ) 向下取整,得到比 num2 小的最大整数
SELECT FLOOR(-1.1) FROM DUAL; -- FORMAT (number,decimal_places ) 保留小数位数(四舍五入)
SELECT FORMAT(78.125458,2) FROM DUAL; -- HEX (DecimalNumber ) 转十六进制
-- LEAST (number , number2 [,..]) 求最小值
SELECT LEAST(0,1, -10, 4) FROM DUAL; -- MOD (numerator ,denominator ) 求余
SELECT MOD(10, 3) FROM DUAL; -- RAND([seed]) RAND([seed]) 返回随机数 其范围为 0 ≤ v ≤ 1.0
-- 老韩说明
-- 1. 如果使用 rand() 每次返回不同的随机数 ，在 0 ≤ v ≤ 1.0
-- 2. 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果 seed 不变，
-- 该随机数也不变了
SELECT RAND() FROM DUAL;
SELECT CURRENT_TIMESTAMP() FROM DUAL;

8、时间日期相关函数

-- 日期时间相关函数
-- CURRENT_DATE ( ) 当前日期
SELECT CURRENT_DATE() FROM DUAL; -- CURRENT_TIME ( )当前时间
SELECT CURRENT_TIME() FROM DUAL; -- CURRENT_TIMESTAMP ( ) 当前时间戳
SELECT CURRENT_TIMESTAMP() FROM DUAL; -- 创建测试表 信息表
CREATE TABLE mes(
	id INT,
	content VARCHAR(30),
    send_time DATETIME);
-- 添加一条记录
INSERT INTO mes VALUES(1, '北京新闻', CURRENT_TIMESTAMP());
INSERT INTO mes VALUES(2, '上海新闻', NOW());
INSERT INTO mes VALUES(3, '广州新闻', NOW());
SELECT * FROM mes;
SELECT NOW() FROM DUAL; -- 上应用实例
-- 显示所有新闻信息，发布日期只显示 日期，不用显示时间. 
SELECT id, content, DATE(send_time) FROM mes; 
-- 请查询在 10 分钟内发布的新闻, 思路一定要梳理一下.
SELECT *FROM mes WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW();
SELECT * FROM mes WHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE);
-- 请在 mysql 的 sql 语句中求出 2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL;
-- 请用 mysql 的 sql 语句求出你活了多少天? [练习] 1986-11-11 出生
SELECT DATEDIFF(NOW(), '1986-11-11') FROM DUAL;
-- 如果你能活 80 岁，求出你还能活多少天.[练习] 1986-11-11 出生
-- 先求出活 80 岁 时, 是什么日期 X
-- 然后在使用 datediff(x, now()); 1986-11-11->datetime
-- INTERVAL 80 YEAR ： YEAR 可以是 年月日，时分秒
-- '1986-11-11' 可以 date,datetime timestamp
SELECT DATEDIFF(DATE_ADD('1986-11-11', INTERVAL 80 YEAR), NOW()) FROM DUAL;
SELECT TIMEDIFF('10:11:11', '06:10:10') FROM DUAL;
-- YEAR|Month|DAY| DATE (datetime )
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT MONTH('2013-11-10') FROM DUAL; 
-- unix_timestamp() : 返回的是 1970-1-1 到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;
-- FROM_UNIXTIME() : 可以把一个 unix_timestamp 秒数[时间戳]，转成指定格式的日期
-- %Y-%m-%d 格式是规定好的，表示年月日
-- 意义：在开发中，可以存放一个整数，然后表示时间，通过 FROM_UNIXTIME 转换
SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d %H:%i:%s') FROM DUAL;

9、加密和系统函数

-- 演示加密函数和系统函数
-- USER() 查询用户
-- 可以查看登录到 mysql 的有哪些用户，以及登录的 IP
SELECT USER() FROM DUAL;
-- 用户@IP 地址
-- DATABASE()查询当前使用数据库名称
SELECT DATABASE();
-- MD5(str) 为字符串算出一个 MD5 32 的字符串，常用(用户密码)加密
-- root 密码是 hsp -> 加密 md5 -> 在数据库中存放的是加密后的密码
SELECT MD5('hsp') FROM DUAL;
SELECT LENGTH(MD5('hsp')) FROM DUAL;
-- 演示用户表，存放密码时，是 md5
CREATE TABLE hsp_user
(id INT , `name` VARCHAR(32) NOT NULL DEFAULT '', pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO hsp_user
VALUES(100, '韩顺平', MD5('hsp'));
SELECT * FROM hsp_user; -- csdn
SELECT * FROM hsp_user -- SQL 注入问题
WHERE `name`='韩顺平' AND pwd = MD5('hsp')
-- PASSWORD(str) -- 加密函数, MySQL 数据库的用户密码就是 PASSWORD 函数加密
SELECT PASSWORD('hsp') FROM DUAL;
-- 数据库的 *81220D972A52D4C51BB1C37518A2613706220DAC
-- select * from mysql.user \G 从原文密码 str 计算并返回密码字符串
-- 通常用于对 mysql 数据库的用户密码加密
-- mysql.user 表示 数据库.表
SELECT * from mysql.user;//数据库名.表名，这样就不用切换数据库了

10、流程控制函数

# 演示流程控制语句
# IF(expr1,expr2,expr3) 如果 expr1 为 True ,则返回 expr2 否则返回 expr3
SELECT IF(TRUE, '北京', '上海') FROM DUAL;
# IFNULL(expr1,expr2) 如果 expr1 不为空 NULL,则返回 expr1,否则返回 expr2
SELECT IFNULL( NULL, '韩顺平教育') FROM DUAL;
# SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.]
# 如果 expr1 为 TRUE,则返回 expr2,如果 expr2 为 t, 返回 expr4, 否则返回 expr5
SELECT CASE
WHEN TRUE THEN 'jack' -- jack
WHEN FALSE THEN 'tom' ELSE 'mary' END
-- 1. 查询 emp 表, 如果 comm 是 null , 则显示 0.0
-- 老师说明，判断是否为 null 要使用 is null, 判断不为空 使用 is not
SELECT ename, IF(comm IS NULL , 0.0, comm) FROM emp;
SELECT ename, IFNULL(comm, 0.0)
FROM emp; -- 2. 如果 emp 表的 job 是 CLERK 则显示 职员， 如果是 MANAGER 则显示经理
-- 如果是 SALESMAN 则显示 销售人员，其它正常显示
SELECT ename, (SELECT CASE
WHEN job = 'CLERK' THEN '职员' WHEN job = 'MANAGER' THEN '经理' WHEN job = 'SALESMAN' THEN '销售人员' ELSE job END) AS 'job' FROM emp;
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;

4、MySQL表查询-加强

(1)引入

#如何查找1992.1.1后入职的员工
select * from emp where hiredate > '1992.1.1';
#如何显示首字符为S的员工姓名和工资
select * from emp where `name` like 'S%';
#如何显示第三个字符为大写O的所有员工的姓名和工资
select `name`,sal from emp where `name` like '__O%';
#如何显示上级没有雇员的情况
select * from emp where mgr is null;
#查询表结构
desc emp;
#如何按照工资的从低到高的顺序显示雇员的信息
select * from emp order by sal;
#按照部门号升序而雇员的工资降序排序，显示雇员信息
select * from emp order by deptno,sal desc;

(2)分页查询

select * from emp order by empno limit 0,3; //第一页，3条记录
select * from emp order by empno limit 3,3; //第二页，3条记录
select * from emp order by empno limit 6,3; //第三页，3条记录
#公式
select * from emp order by empno limit 每页显示记录数 * (第几页-1),每页显示记录数; 

(3)使用分组函数和分组子句 group by

#显示每种岗位的雇员总数、平均工资
select job,count(*),avg(sal) from emp group by job;
#显示雇员总数，以及获取补助的雇员数
select count(*),count(comm) from emp;
#获得补助的雇员数 就是 comm 列为非 null, 就是 count(列)，如果该列的值为 null, 是不会统计 , SQL 非常灵活，需要我们动脑筋.

#显示管理者的总人数
select count(distinct mgr) from emp;
#显示雇员工资的最大差额
select max(sal)-min(sal) from emp;

#统计各个部门的平均工资，并且是大于1000的，按照平均工资从高到低排序，取出前两行记录
select deptno,avg(sal) from emp group by deptno having avg(sal)>1000 order by avg(sal) desc limit 0,2;

总结：如果select语句同时包含有group by，having，limit，order by那么他们的顺序是group by，having，order by，limit。

5、MySQL连接查询

(1)多表查询/内连接查询

多表查询是指基于两个和两个以上的表查询，在实际应用中，查询单个表可能不能满足需求，就需要多表查询了。

语法格式：

select <列表字段> from <表名1>,<表名2> where <表名1.列名=表名2.列名>;

例如：

#显示雇员名，雇员工资及所在部门名字
#因为雇员名，雇员工资在emp表中，部门名字在dept表中，所以需要多表查询
select ename,sal,dname from emp,dept where emp.deptno=dept.deptno;//一定要添加emp.deptno=dept.deptno限制条件

#如何显示部门号为10的部门名、员工名和工资
select dname,ename,sal from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;

#显示各个员工的姓名，工资，及其工资的级别
#员工的姓名，工资在emp表中，工资的级别在salgrade表中
select ename,sal,grade from emp,salgrade where sal between losal and hisal;

#显示雇员名，雇员工资及所在部门名字，并按部门排序【降序排】
select ename,sal,dname from emp,dept where emp.deptno=dept.deptno order by emp.deptno desc;

(2)自连接查询

语法格式：

select <列表字段> from <表名> as <别名1>,<表名> as <别名2> where <别名1.列名=别名2.列名>;

自连接是指在同一张表的连接查询【将同一张表看作两张表】

练习：

#显示公司员工和他的上级名字
#公司员工和他的上级名字都在emp表上
select worker.ename as '员工',boss.ename as '上级' from emp worker,emp boss where worker.mgr=boss.empno;

6、mysql子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

（1）单行子查询

单行子查询是指只返回一行数据的子查询语句

练习：

-- 如何显示与SMITH同一部门的所有员工
SELECT * FROM emp
	WHERE deptno=(
		SELECT deptno FROM emp
			WHERE ename='SMITH'
	)

（2）多行子查询

多行子查询是指返回多行数据的子查询，使用关键字in

-- 如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号，但是不包含10自己的雇员
SELECT ename,job,sal,deptno FROM emp 
	WHERE job IN (
		SELECT DISTINCT job FROM emp
			WHERE deptno=10
	) AND deptno!=10;

（3）子查询当作临时表使用

-- 查询ecshop库中各个类别中，价格最高的商品

-- 查询商品表
-- 先的到各个类别中，价格最高的商品当作临时表,可以解决很多复杂的问题

SELECT cat_id,MAX(shop_price)
	FROM ecs_goods
	GROUP BY cat_id;
	
-- 最终结果	
SELECT goods_id,ecs_goods.`cat_id`,goods_name,shop_price
	FROM(
		SELECT cat_id,MAX(shop_price) AS max_price
		FROM ecs_goods
		GROUP BY cat_id
	) temp,ecs_goods
	WHERE temp.cat_id=ecs_goods.`cat_id`
	AND temp.max_price=ecs_goods.`shop_price`;

（4）all和any

all：

-- ALL
-- 显示工资比部门30的所有员工的工资高的员工姓名的姓名、工资和部门号
SELECT ename,sal,deptno FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30);
-- 另一种方法
SELECT ename,sal,deptno FROM emp WHERE sal>(SELECT max(sal) FROM emp WHERE deptno=30);

any:

-- ANY
-- 显示工资比部门30的其中一个员工的工资高的员工姓名、工资和部门号
SELECT ename,sal,deptno FROM emp WHERE sal> ANY(SELECT sal FROM emp WHERE deptno=30);
-- 另一种方法
SELECT ename,sal,deptno FROM emp WHERE sal> (SELECT min(sal) FROM emp WHERE deptno=30);

（5）多列子查询

多列子查询是指查询返回多个列数据的子查询语句

(字段1,字段2…)=(select 字段1,字段2 from …)

-- 多列子查询

-- 查询与allen的部门和岗位完全相同的所有雇员（并且不含smith本人）

-- 分析：1、先得到allen的部位和岗位
SELECT deptno,job FROM emp WHERE ename='ALLEN';
-- 分析：2、把上面的查询当作子查询来使用，并且使用多列的语法进行匹配
SELECT * FROM emp WHERE (deptno,job)=(SELECT deptno,job FROM emp WHERE ename='ALLEN') AND ename!='ALLEN';

-- 查询和宋江数学、英语、语文成绩完全相同的学生
SELECT * FROM student;
SELECT * FROM student WHERE (chinese,english,math)=(SELECT chinese,english,math FROM student WHERE `name`='宋江');

（6）在from子句中使用子查询

思路：先将人员信息和部门信息关联显示，然后统计。

-- 查找每个部门工资高于本部门平均工资的人的资料
-- 先查询每个部门的部门号，平均工资，当作子查询，和emp进行多表查询
SELECT deptno,AVG(sal) AS avg_sal FROM emp GROUP BY deptno;
SELECT * FROM emp,(
	SELECT deptno,AVG(sal) AS avg_sal FROM emp GROUP BY deptno
) temp WHERE emp.`deptno`=temp.deptno AND emp.`sal`>temp.avg_sal;

-- 查找每个部门工资最高的人的详细资料
SELECT deptno,MAX(sal) FROM emp GROUP BY deptno;
SELECT * FROM emp,(
	SELECT deptno,MAX(sal) AS max_sal FROM emp GROUP BY deptno
) temp WHERE emp.`deptno`=temp.deptno AND emp.`sal`=temp.max_sal;

-- 查询每个部门的信息（包括：部门名，编号，地址）和人员数量
-- 各个部门的人员数量->构成一张临时表
SELECT COUNT(*),deptno FROM emp GROUP BY deptno;
SELECT dept.deptno,dname,loc,temp.人数 FROM dept,(
	SELECT COUNT(*) AS 人数,deptno FROM emp GROUP BY deptno
) temp WHERE dept.`deptno`=temp.deptno;

-- 还有一种写法 表.* 表示将该表所有列都显示出来，可以简化sql语句
-- 在多表查询中，当多个表的列不重复是，才可以直接写列名
SELECT dept.*,temp.人数 FROM dept,(
	SELECT COUNT(*) AS 人数,deptno FROM emp GROUP BY deptno
) temp WHERE dept.`deptno`=temp.deptno;

7、表复制

(1)自我复制数据（蠕虫复制）

-- 表复制

-- 自我复制数据（蠕虫复制）->创建海量数据
-- 创建my_tab01表
CREATE TABLE my_tab01(
	id INT,
	`name` VARCHAR(32),
	sal DOUBLE,
	job VARCHAR(32),
	deptno INT);
DESC my_tab01;
SELECT * FROM my_tab01;
-- 演示自我复制
-- 1、把emp表的记录复制到my_tab01
INSERT INTO my_tab01 (id,`name`,sal,job,deptno) 
	SELECT empno,ename,sal,job,deptno FROM emp;
-- 2、自我复制	这样就会存在相同的数据
INSERT INTO my_tab01 SELECT * FROM my_tab01;
-- 删除一张表重复记录
-- 1、先创建一张表my_tab02
-- 2、让my_tab02有重复记录
CREATE TABLE my_tab02 LIKE emp;-- 这条语句只会复制emp表结构
DESC my_tab02;
SELECT * FROM my_tab02;
INSERT INTO my_tab02 SELECT * FROM emp;-- 这条语句执行2遍
-- 3、考虑去重my_tab02的记录
/*
	思路
	（1）先创建一张临时表my_tmp，该表的结构和my_tab02一样
	（2）把my_tab02的记录通过distinct关键字处理后，把记录复制到my_tmp
	（3）清除掉my_tab02记录
	（4）把my_tmp表的记录复制到my_tab02
	（5）drop掉临时表my_tmp
*/
-- （1）先创建一张临时表my_tmp，该表的结构和my_tab02一样
CREATE TABLE my_tmp LIKE my_tab02;
-- （2）把my_tab02的记录通过distinct关键字处理后，把记录复制到my_tmp
INSERT INTO my_tmp SELECT DISTINCT * FROM my_tab02;
SELECT * FROM my_tmp;
-- （3）清除掉my_tab02记录
DELETE FROM my_tab02;
-- （4）把my_tmp表的记录复制到my_tab02
INSERT INTO my_tab02 SELECT * FROM my_tmp;
-- （5）drop掉临时表my_tmp
DROP TABLE my_tmp;

8、合并查询

• union all -> 该操作用于取得两个结果集的并集，不会取消重复行
• union -> 该操作与union all类似，但是会自动去掉结果集中重复行

-- 合并查询

-- union all 不会去重(其实就是将两个查询到结果进行拼接)
SELECT ename,sal,job FROM emp WHERE sal>2500 
	UNION ALL SELECT ename,sal,job FROM emp WHERE job='MANAGER';

-- union 会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 
	UNION SELECT ename,sal,job FROM emp WHERE job='MANAGER';

9、mysql表外连接

-- 外连接

-- 引入：列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门
-- 部门名称在dept表，员工名称和工作在emp表
SELECT dname,ename,job FROM emp,dept WHERE dept.`deptno`=emp.`deptno` ORDER BY dname;
-- 这里会出现一个问题显示不出那些没有员工的部门，因为两个表是根据关联条件显示所有匹配记录，匹配不上的就不显示
-- 因此就需要使用外连接

-- 创建stu表
CREATE TABLE stu(
	id INT,
	`name` VARCHAR(32));
INSERT INTO stu VALUES(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
SELECT * FROM stu;
-- 创建exam表
CREATE TABLE exam(
	id INT,
	grade INT);
INSERT INTO exam VALUES(1,56),(2,76),(11,8);
SELECT * FROM exam;
-- 显示所有人成绩，如果没有成绩，也要显示该人的姓名和id号，成绩显示为空
SELECT * FROM stu,exam WHERE stu.`id`=exam.`id`;-- 这样是完不成以上要求的
-- 左外连接
SELECT `name`,stu.`id`,grade FROM stu LEFT JOIN exam ON stu.`id`=exam.`id`;
-- 使用右外连接显示所有成绩，如果没有名字匹配，则显示空
-- 右外连接 
SELECT `name`,stu.`id`,grade FROM stu RIGHT JOIN exam ON stu.`id`=exam.`id`;

-- 练习 列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门
SELECT dname,ename,job FROM dept LEFT JOIN emp ON emp.`deptno`=dept.`deptno`;	-- 左外连接
SELECT dname,ename,job FROM emp RIGHT JOIN dept ON emp.`deptno`=dept.`deptno`; -- 右外连接

-- 在实际的开发中，我们绝大多数使用的还是内连接

10、约束

利用约束可以保证数据的正确性、有效性和相容性，约束一般分为主键约束、非空约束、唯一性约束、默认值约束、外键约束的检查约束六类。

（1）主键约束 primary key

在表中定义一个主键来唯一确定表中每一行数据的标识符。通过主键约束可以实现实体完整性约束，主键可以是表中某一列或者多列组合，其中由多列组合的主键称为复合主键。

主键应该遵循以下规则：

• 每个表只能定义一个主键
• 主键值必须唯一标识表中每一行，且不能为null值，即表中不可能存在两行数据有相同的主键值，这也是唯一性原则
• 一个列名只能在复合主键列表中出现一次
• 复合主键不能包含不必要的多于列。当把复合主键的某一列删除后，剩下的列应不能满足唯一性原则，这是最小化原则

创建主键约束的方法：

-- 创建表时创建主键约束
-- 方法一 “列级别”
create table <表名>(
	<字段1> <数据类型> primary key,
    .
    .
    .
)
-- 方法二 “表级别”
create table <表名>(
	<字段1> <数据类型>,
    .
    .
    .
    primary key(字段1,字段2...)
);

-- 修改表时添加主键约束
alter table <数据表名> add primary key (<列名>);

删除主键约束的方法：

alter table <数据表名> drop primary key;

（2）唯一性约束 UNIQUE

要求该列唯一，允许为空，但只能出现一个空值。唯一性约束可以确保一列或几列不出现重复值。

注意：唯一性约束与主键约束的区别在于：

​ 一个表中可以有多个字段声明为唯一的，但只能有一个主键声明；

​ 声明为主键的字段不允许有空值，声明为唯一性的字段允许有一个空值

创建唯一性约束的方法：

-- 创建表时添加默认值约束
create table <表名>(
	<字段1> <数据类型> default <默认值>,
    .
    .
    .
)

-- 修改表时添加默认值约束
alter table <数据表名> change column <字段名> <字段名> <数据类型> default <默认值>;

删除默认值约束：

alter table <数据表名> change column <字段名> <字段名> <数据类型> default null;

-- 练习 删除library数据库中读者表reader的sex字段的默认值约束
use library;
alter table reader change column sex sex char(2) default null;

（3）非空约束 NOT NULL

如果未设置非空约束，默认状态下，数据表中的字段默认值都是可以为NULL的。非空约束是指字段的值不能为空，对于设置了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。

添加非空约束的方法：

-- 创建表时添加非空约束
create table <表名>(
	<字段1> <数据类型> NOT NULL,
    .
    .
    .
)

-- 修改表时添加非空约束
alter table <数据表名> change column <字段名> <字段名> <数据类型> not null;

删除非空约束：

alter table <数据表名> change column <字段名> <字段名> <数据类型> null;

-- 练习 修改library数据库中系部表department，取消系部名称dept字段的非空约束
alter table department change column dept dept varchar(20) null;

（4）检查约束 CHECK

检查约束用于检查用户提交的数据是否符合用户定义完整性约束的要求。在MySQL中，提供了check检查约束用来指定某列的可取值的范围，他通过限制输入到列中的值来强制实现域的完整性。

添加检查约束的方法：

-- 创建表时添加
-- “列级别”
create table <表名>(
	<字段1> <数据类型> check(<检查约束>),
    .
    .
    .
)
-- "表级别"
create table <表名>(
	<字段1> <数据类型>,
    .
    .
    .
    check(<检查约束>)
)

-- 修改表时添加检查约束
alter table <数据表名> add constraint <检查约束名> check(<检查约束>);

-- 练习：修改student数据库中成绩表score中成绩字段score。设置字段的取值介于0-100范围内。
alter table socre add constraint c_s check(score>=0 and score<=100);

删除检查约束：

alter table <数据表名> drop check <检查约束名>;

-- 练习：修改student数据库中成绩表score，删除字段score的检查约束
alter table score drop check c_s;

（5）外键约束 FOREIGN KEY

外键由表的一个列或多个列组成，用来维护两个表之间数据一致性。外键约束用来建立和强调两个表之间的关系，一个表的主键属性在另一个表中出现，此时该主键就是另一个表的外键。外键约束用来实现参照完整性约束。

添加外键约束的方法：

参数说明：

• 外键名为定义的外键约束的名称，一个表中不能出现相同名称的外键
• 字段名表示子表需要添加外键约束的字段列
• 主表名即被子表外键所依赖的表的名称
• 主键列表示主表定义的主键列或者列组合

-- 创建表时添加外键约束
create table <表名>(
	<字段1> <数据类型>,
    .
    .
    .
    [constraint <外键名>] foreign key 字段名 [字段名2,...] references <主表名> 主键列1 [主键列2,...]
)

-- 修改表时添加外键约束
alter table <数据表名> add constraint <外键约束名> foreign key(<列名>) references <主表名> (<列名>);

-- 练习：修改library数据库中读者表reader，添加外键deptid字段，与系别表department的deptid字段进行关联
alter table reader add constraint r_d foreign key deptid references department (deptid);

删除外键约束：

alter table <数据表名> drop foreign key<外键约束名>;

外键约束细节说明：

• 外键指向的表的字段，要求是primary key或者是unique
• 表的类型是innodb，这样的表才支持外键
• 外键字段的类型要和主键字段的类型一致（长度可以不同）
• 外键字段的值，必须在主键字段中出现过，或者为null[前提是外键字段允许为null]
• 一旦建立主外键关系，数据就不能随意删除了！

（6）商店售货系统表设计案例

11、自增长

当希望某张表中存在一个id列（整数），我们希望再添加记录时该列从1开始，自动的增长，怎么处理？

-- 自增长

-- 添加自增长的字段方式
insert into XXX(字段1,字段2...) values (null,...);
insert into XXX values(null,...)

-- 创建表 test_01
CREATE TABLE test_01(
	id INT PRIMARY KEY AUTO_INCREMENT,
	email VARCHAR(32) NOT NULL DEFAULT '',
	`name` VARCHAR(32) NOT NULL DEFAULT '');
INSERT INTO test_01 VALUES(NULL,'jack@email','jack');
SELECT * FROM test_01;	
INSERT INTO test_01 VALUES(NULL,'tom@email','tom');
-- 修改自增长默认初始值
CREATE TABLE test_02(
	id INT PRIMARY KEY AUTO_INCREMENT,
	email VARCHAR(32) NOT NULL DEFAULT '',
	`name` VARCHAR(32) NOT NULL DEFAULT '');
ALTER TABLE test_02 AUTO_INCREMENT=100;-- 修改默认初始值
INSERT INTO test_02 VALUES(NULL,'jack@email','jack');
INSERT INTO test_02 VALUES(NULL,'tom@email','tom');	
SELECT * FROM test_02;

自增长的使用细节：

• 一般来说自增长是和primary key配合使用的
• 自增长也可以单独使用【前提是需要配合一个unique】
• 自增长修饰的字段为整数型（也可以是小数，但很少这样使用）
• 自增长默认是从1开始，也可以用下列命令修改：

​ alter table <表名> auto_increment=新值;

• 如果添加数据时，给自增长字段（列）指定了值，则以指定的值为准（优先级会更高，例如600），但是再添加新记录时会从上一个指定值继续自增（从601开始）

12、MySQL索引

MySQL中常见的索引在物理上分为B+树索引和哈希索引两类。

MySQL的逻辑分类：

• 普通索引
• 唯一性索引
• 空间索引
• 单列索引
• 多列索引
• 全文索引
• 主键索引

（1）创建索引

-- 使用create index语句创建索引
create [unique|fulltext|spatial] index <索引名> on <表名> (<列名>...);

-- 使用create table语句创建索引
create table <表名>(
	.
    .
    .
    [unique|fulltext|spatial] index <索引名> () (<列名>...);
);

-- 添加索引
-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON test_index(id);
-- 添加普通索引
CREATE INDEX id_index ON test_index (id);
-- 如何选择
-- 1、如果某列的值，是不会重复的，那么优先考虑使用unique索引，否则使用普通索引

-- 添加普通索引方式2
ALTER TABLE test_index ADD INDEX id_index (id);

-- 添加主键索引 其实就是将字段设置为主键即可
ALTER TABLE test_index ADD PRIMARY KEY (id);

（2）查看索引

-- 查询索引
-- 1、方式
SHOW INDEX FROM <表名>;
-- 2、方式
SHOW INDEXES FROM <表名>;
-- 3、方式
SHOW KEYS FROM <表名>;
-- 4、方式
DESC <表名>;

（3）删除索引

-- 删除主键索引
ALTER TABLE test_index DROP PRIMARY KEY;

（4）修改索引

-- 修改索引 其实就是先删除索引，在添加新索引

练习：

-- 索引练习

-- 创建一张订单表order（id号，商品名，订购人，数量）要求id为主键，请使用2种方式来创建主键。
CREATE TABLE order1(
	id INT PRIMARY KEY,
	product_name VARCHAR(32),
	orderer VARCHAR(32),
	num INT);
DESC order1; 
CREATE TABLE order2(
	id INT,
	product_name VARCHAR(32),
	orderer VARCHAR(32),
	num INT,
	PRIMARY KEY (id));
DESC order2;
SHOW INDEX FROM order1;
SHOW INDEX FROM order2;

-- 创建一张特价菜谱表menu（id号，菜谱名，厨师，点餐人身份证，价格）要求id号为主键，点餐人身份证时unique请使用两种方式创建unique
CREATE TABLE menu(
	id INT PRIMARY KEY,
	menu VARCHAR(32),
	cooker VARCHAR(32),
	card_id VARCHAR(32) UNIQUE,
	price DOUBLE);
CREATE UNIQUE INDEX card_id_index ON menu (card_Id);
DESC menu;
SHOW INDEX FROM menu;

-- 创建一张运动员表sportman（id号，名字，特长）要求id号为主键，名字为普通索引。
CREATE TABLE sportman(
	id INT PRIMARY KEY,
	`name` VARCHAR(32),
	Specialties VARCHAR(32));
CREATE INDEX name_index ON sportman (`name`);
DESC sportman;
SHOW INDEX FROM sportman;

（5）那些列上适合使用索引

1. 较频繁的作为查询条件字段应该创建索引
2. 唯一性太差的字段不适合单独创建索引，即使频繁的作为查询条件
3. 更新非常频繁的字段不适合创建索引
4. 不会出现在where子句中字段不该创建索引

13、MySQL事务

-- 事务理解
-- 1、需求：将tom的100元，转到jack就会执行两条sql语句
CREATE TABLE balance(
id INT,
`name` VARCHAR(32),
money INT);
INSERT INTO balance VALUES (100,'tom',3000),(200,'jack',6000);
-- 
UPDATE balance SET money=money-100 WHERE id=100;
UPDATE balance SET money=money+100 WHERE id=200;
-- 如果第1条语句执行成功，但是第2条语句执行失败...
-- 这里就会引出一个需求，将多个dml(update,insert,delete)当作一个整体，要么全部成功，要么全部失败
-- 这里就会用事务来解决这样的问题

（1）什么是事务

事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。

事务操作示意图：

（2）事务和锁

当执行事务操作（dml语句），MySQL会在表上加锁，防止其他用户改表的数据。这对用户来说是非常重要的。

（3）MySQL数据库控制台事务的几个重要操作

1. start transaction – 开始一个事务
2. savepoint 保存点名 --设置保存点
3. rollback to 保存点名 – 回退事务
4. rollback – 回退全部事务
5. commit – 提交事务，所有操作生效，不能回退

-- 演示
-- 1、创建一张测试表
CREATE TABLE test_transaction(
	id INT,
	`name` VARCHAR(32));
-- 2、开始事务
START TRANSACTION;
-- 3、设置保存点
SAVEPOINT a;
-- 执行dml操作
INSERT INTO test_transaction VALUES(100,'tom');
SELECT * FROM test_transaction;

SAVEPOINT b;
-- 执行dml操作
INSERT INTO test_transaction VALUES(200,'jack');
SELECT * FROM test_transaction;

-- 回退到b
ROLLBACK TO b;
-- 继续回退到a
ROLLBACK TO a;
-- 如果这样写，表示直接回退到事务开始阶段
ROLLBACK;
COMMIT; -- 提交

（4）回退事务

在介绍回退事务前，先介绍一下保存点（savepoint），保存点是事务中的点，用于取消部分事务，当结束事务时（commit），会自动的删除该事务所定义的所有保存点。当执行回退事务时，通过指定保存点可以回退到指定的点。如上面的图。

（5）提交事务

使用commit语句可以提交事务，当执行了commit语句之后，会确认事务的变化，结束事务，删除保存点、释放锁、数据生效。当使用commit语句结束事务之后，其他会话[其他连接]将可以看到事物变化后的新数据[所有数据正式生效]

（6）事务细节讨论

1. 如果不开始事务，默认情况下，dml操作时自动提交的，不能回滚
2. 如果开始一个事务，你没有创建保存点，你可以执行rollback，默认就是回滚到事务开始的状态
3. 你也可以在这个事务中（还没有提交时）创建多个保存点，比如savepoint aaa;执行dml操作，savepoint bbb;
4. 你可以在事务没有提交前，选择回滚到哪个保存点
5. MySQL的事务机制需要innodb的存储引擎才可以使用，myisam不好使
6. 开始一个事务 start transaction， set autocommit=off;

14、MySQL事务隔离级别

（1）事务隔离级别介绍

1. 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性
2. 如果不考虑隔离性，可能会引发如下问题：

• 脏读：当一个事务读取另一个事务尚未提交的改变。
• 不可重复读：同一查询在同一事物中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
• 幻读：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。

MySQL隔离级别定义了事务与事务之间的隔离程度。

（2）设置事务隔离级别

-- 查看当前MySQL的隔离级别
-- 查看系统隔离级别：select @@global.tx_isolation;
-- 查看会话隔离级别(5.0以上版本)：select @@tx_isolation;
-- 查看会话隔离级别(8.0以上版本)：select @@transaction_isolation;
SELECT @@tx_isolation;
-- +-------------------------+
-- | @@transaction_isolation |
-- +-------------------------+
-- | REPEATABLE-READ         |
-- +-------------------------+

-- 把其中一个控制台的隔离级别(当前会话的隔离级别)设置为 read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 把其中系统的隔离级别设置为 read uncommitted
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- mysql默认的事务隔离级别是repeatable read,一般情况下，没有特殊要求，没必要修改
-- （因为该级别可以满足绝大部分项目需求）

-- 全局修改，修改my.ini配置文件，在最后加上
#可选参数有：READ-UNCOOMITTED,READ-COOMITTED,REPEATABLE-READ,SERIALIZABLE.
[mysqld]
transaction-isolation=REPEATABLE-READ

（3）MySQL的事务隔离级别案例

先创建account表：

-- 创建表
CREATE TABLE `account`(
	id INT,
	`name` VARCHAR(32),
	money INT);

1、演示读未提交

开启两个dos窗口都连接上MySQL；其中一个连接设置隔离级别为读未提交（read uncommitted）

两个连接选中account表所在的数据库并全部开启事务。在第一个连接上进行插入sql语句，然后在第二个连接上进行查询可以看到能查询到刚刚插入的记录，这就是脏读。

继续在第一个连接进行数据修改，然后提交事务，在第二个连接进行查询，发现更改的数据全部能看到，这就是不可重复读（其他提交事务所做的修改或删除）和幻读（其他提交事务所做的插入操作）。

2、演示读已提交

开启两个dos窗口都连接上MySQL；其中一个连接设置隔离级别为读已提交（read committed）,两个连接选中account表所在的数据库并全部开启事务。在第一个连接上进行插入sql语句，然后在第二个连接上进行查询不可以看到刚刚插入的记录，这就是设置的隔离级别起到了防止脏读的作用。

继续在第一个连接进行数据修改，然后提交事务，在第二个连接进行查询，发现更改的数据全部能看到，这就是不可重复读（其他提交事务所做的修改或删除）和幻读（其他提交事务所做的插入操作）。这说明设置隔离级别为读未提交（read committed）不能防止不可重复读和幻读。

3、演示可重复读

开启两个dos窗口都连接上MySQL；其中一个连接设置隔离级别为可重复读（repeatable read）,两个连接选中account表所在的数据库并全部开启事务。在第一个连接上进行插入sql语句，然后在第二个连接上进行查询不可以看到刚刚插入的记录，这就是设置的隔离级别起到了防止脏读的作用。

继续在第一个连接进行数据修改，然后提交事务，在第二个连接进行查询，发现更改的数据不能看到，这说明设置隔离级别为可重复读（repeatable read）能防止不可重复读和幻读。

当第二个连接提交事务，发现刚刚更改的数据就可以查询到了。

4、演示可串行化

开启两个dos窗口都连接上MySQL；其中一个连接设置隔离级别为可串行化（serializable）,两个连接选中account表所在的数据库并全部开启事务。在第一个连接上进行插入sql语句，然后在第二个连接上进行查询发现语句卡住不动了。这就是可串行化隔离的特点他会进行加锁，防止其他连接进行操作。

15、MySQL表类型和存储引擎

（1）基本介绍

• MySQL的表类型由存储引擎决定，主要包括innodb、myisam、memory等。
• MySQL数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、InnoBDB。
• 这六种又分为两类，一类是“事务安全型”，比如Innodb，其余属于第二类，称为“非事务安全型”，例如：myisam、memory。

（2）主要的存储引擎/表类型的特点

• MyISAM：不支持事务，也不支持外键，但是访问速度快，对事物完整性没有要求
• Innodb：提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM，Innodb的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
• memory：使用存在内存的内容来创建表。每个memory表只实际对应一个磁盘空间。memory类型的表访问非常快（因为是在内存中），并且默认使用哈希索引。但是一旦Mysql服务关闭，表中的数据就会丢失掉，但是表的结构还在。

（3）如何选择（按要求选择即可）

（4）修改存储引擎

alter table <表名> engine=<存储引擎>;

16、视图

（1）视图的引入

emp表的列信息很多，有些信息是个人重要信息（比如sal，comm，mgr，hiredate），如果我们希望某个用户只能查询emp表的（empno，ename，job，deptno）信息，有什么办法？—>这样的需求可以用视图来解决！!

（2）基本概念

1. 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真是表（基表）
2. 视图和基表的示意图

（3）视图的基本使用

-- 创建视图
create view <视图名> as select语句;
-- 修改视图
alter ciew <视图名> as select语句; -- 更新成新的视图
-- 查看视图
show create view 视图名;
-- 删除视图
drop view 视图名1,视图名2;

练习：

-- 创建一个emp_view01,只能查询emp表的（emptno、ename、job和deptno）信息
create view emp_view01 as select emptno,ename,job,deptno from emp;
-- 查看视图
desc emp_view01;
select * from emp_view01;

-- 查看创建视图的指令
show create view emp_view01;
-- 删除视图
drop view emp_view01;

-- 视图的细节
-- 1、创建视图后，到数据库去看，对应试图只有一个视图结构文件（形式：视图名.frm）
-- 2、视图的数据变化会影响到基表，基表的数据变化会影响到视图[insert update delete]

-- 修改视图
UPDATE emp_view01 SET job='MANAGER' WHERE empno=7369;

-- 查询基表
SELECT * FROM emp;
-- 查询视图
SELECT * FROM emp_view01;

-- 修改基本表，会影响到视图
UPDATE emp_view01 SET job='SALESMAN' WHERE empno=7369;
-- 查询视图
SELECT * FROM emp_view01;

-- 视图中可以再使用视图,比如从emp_view01视图中，选出empno和ename做出新视图
DESC emp_view01;
CREATE VIEW emp_view02 AS SELECT empno,ename FROM emp_view01;
SELECT * FROM emp_view02;
SELECT * FROM salgrade;

-- 针对emp，dept和salgrade，创建一个视图emp_view03,可以显示雇员编号，雇员名，雇员部门名称和薪水级别[即使用三张表，构建一个视图]
CREATE VIEW emp_view03 
	AS SELECT empno,ename,dname,grade FROM emp,dept,salgrade 
		WHERE emp.`deptno`=dept.`deptno` AND sal BETWEEN losal AND hisal;
SELECT * FROM emp_view03;

17、MySQL管理

（1）MySQL用户

1、创建用户

create user '用户名'@'允许登录的位置' identified by '密码';
-- 说明：创建用户，同时指定密码

2、删除用户

drop user '用户名'@'允许登录的位置';

3、用户修改密码

-- 修改自己的密码
set password = password('密码');
-- 修改其他用户密码
set password for '用户名'@'允许登录的位置'=password('密码');

（2）MySQL中的权限

1、给用户授权

-- 基本语法
grant 权限列表 on 库.对象名 to '用户名'@'允许登录的位置' [identified by '密码']

2、回收用户授权

-- 基本语法
revoke 权限列表 on 库.对象名 from '用户名'@'允许登录的位置';

3、权限生效指令

-- 如果权限没有生效，可以执行下列指令
flush privileges;

4、练习

-- 演示 用户权限的管理

-- 创建用户 djt 密码：123
CREATE USER 'djt'@'localhost' IDENTIFIED BY '123';

-- 使用root用户创建testdb数据库，表news
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE news(
	id INT,
	content VARCHAR(32));
	
-- 添加一条数据
INSERT INTO news VALUES(100,'北京新闻');
SELECT * FROM news;	

-- 给djt用户赋予查看和插入news表的权限
GRANT SELECT,INSERT ON testdb.news TO 'djt'@'localhost';
-- 给djt用户追加修改权限
GRANT UPDATE ON testdb.news TO 'djt'@'localhost';
-- 给djt用户修改密码为123456
SET PASSWORD FOR 'djt'@'localhost'=PASSWORD('123456');
-- 回收djt用户在testdb.news表中的所有权限
REVOKE ALL ON testdb.news FROM 'djt'@'localhost';
-- 删除djt用户
DROP USER 'djt'@'localhost';

5、细节说明

44)]

-- 针对emp，dept和salgrade，创建一个视图emp_view03,可以显示雇员编号，雇员名，雇员部门名称和薪水级别[即使用三张表，构建一个视图]
CREATE VIEW emp_view03 
	AS SELECT empno,ename,dname,grade FROM emp,dept,salgrade 
		WHERE emp.`deptno`=dept.`deptno` AND sal BETWEEN losal AND hisal;
SELECT * FROM emp_view03;

17、MySQL管理

（1）MySQL用户

1、创建用户

create user '用户名'@'允许登录的位置' identified by '密码';
-- 说明：创建用户，同时指定密码

2、删除用户

drop user '用户名'@'允许登录的位置';

3、用户修改密码

-- 修改自己的密码
set password = password('密码');
-- 修改其他用户密码
set password for '用户名'@'允许登录的位置'=password('密码');

（2）MySQL中的权限

[外链图片转存中…(img-S879NKrT-1715655687244)]

1、给用户授权

-- 基本语法
grant 权限列表 on 库.对象名 to '用户名'@'允许登录的位置' [identified by '密码']

[外链图片转存中…(img-L7oItxz0-1715655687245)]

2、回收用户授权

-- 基本语法
revoke 权限列表 on 库.对象名 from '用户名'@'允许登录的位置';

3、权限生效指令

-- 如果权限没有生效，可以执行下列指令
flush privileges;

4、练习

[外链图片转存中…(img-noWuUj07-1715655687245)]

-- 演示 用户权限的管理

-- 创建用户 djt 密码：123
CREATE USER 'djt'@'localhost' IDENTIFIED BY '123';

-- 使用root用户创建testdb数据库，表news
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE news(
	id INT,
	content VARCHAR(32));
	
-- 添加一条数据
INSERT INTO news VALUES(100,'北京新闻');
SELECT * FROM news;	

-- 给djt用户赋予查看和插入news表的权限
GRANT SELECT,INSERT ON testdb.news TO 'djt'@'localhost';
-- 给djt用户追加修改权限
GRANT UPDATE ON testdb.news TO 'djt'@'localhost';
-- 给djt用户修改密码为123456
SET PASSWORD FOR 'djt'@'localhost'=PASSWORD('123456');
-- 回收djt用户在testdb.news表中的所有权限
REVOKE ALL ON testdb.news FROM 'djt'@'localhost';
-- 删除djt用户
DROP USER 'djt'@'localhost';

5、细节说明