MySQL入门归档

一、数据库简介

​ 1、将数据放到表中，表再放到库中
​ 2、一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
​ 3、表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
​ 4、表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
​ 5、表中的数据是按行存储的，每一行类似于java中的“对象”。

二、MySQL操作说明

1. 服务启停

# windows
net start 服务名（启动服务）
net stop 服务名（停止服务）
# linux
systemctl start mysql
systemctl stop mysql

2. MySQL登录和退出

• 通过mysql自带的客户端 :只限于root用户
• mysql 【-h主机名 -P端口号 】-u用户名 -p密码

3. MySQL的常见命令

--1.查看当前所有的数据库
show databases;
--2.打开指定的库
use 库名
--3.查看当前库的所有表
show tables;
--4.查看其它库的所有表
show tables from 库名;
--5.创建表
create table 表名(
	列名 列类型,
	列名 列类型，
	。。。
);
--6.查看表结构
desc 表名;

# 7.查看服务器的版本
方式一：登录到mysql服务端
select version();
方式二：没有登录到mysql服务端
mysql --version
或
mysql --V

4. MySQL的语法规范

• 不区分大小写,但建议关键字大写，表名、列名小写
• 单行注释：#注释文字
• 单行注释：-- 注释文字
• 多行注释：/* 注释文字 */

5. SQL的语言分类

​ DQL（Data Query Language）：数据查询语言
​ select
​ DML(Data Manipulate Language):数据操作语言
​ insert 、update、delete
​ DDL（Data Define Languge）：数据定义语言
​ create、drop、alter
​ TCL（Transaction Control Language）：事务控制语言
​ commit、rollback

三、DQL语言

1. 机读顺数

select 查询列表     ⑦
distinct 去重 	 ⑧
from 表1 别名       ①
连接类型 join 表2   ③
on 连接条件         ②
where 筛选          ④
group by 分组列表   ⑤  支持select别名
having 筛选         ⑥  支持select别名
order by 排序列表   ⑨  支持select别名
limit 起始条目索引，条目数;  ⑩

特点：
①通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数

2. 条件查询

​ 条件查询：根据条件过滤原始表的数据，查询到想要的数据

分类：
一、条件表达式
	示例：salary>10000
	条件运算符：
	> < >= <= = != <>

二、逻辑表达式
示例：salary>10000 && salary<20000

逻辑运算符：
and（&&）:两个条件如果同时成立，结果为true，否则为false
or(||)：两个条件只要有一个成立，结果为true，否则为false
not(!)：如果条件成立，则not后为false，否则为true

三、模糊查询
示例：last_name like 'a%'

3. 排序查询

select
	要查询的东西
from
	表
where 
	条件
order by 排序的字段|表达式|函数|别名 【asc|desc】

4. 分页查询

应用场景：
实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句
语法：

select 字段|表达式,...
from 表
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引，】条目数;

特点：
1.起始条目索引从0开始
2.limit子句放在查询语句的最后
3.公式：select * from 表 limit （page-1）*sizePerPage,sizePerPage
假如:
每页显示条目数sizePerPage
要显示的页数 page

四、常见函数

1. 单行函数

1.1 字符函数

CONCAT(S1,S2,......,Sn)	--连接S1,S2,......,Sn为一个字符串
CONCAT(s, S1,S2,......,Sn)	--同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上s

char_length('agg哈哈') --获取字符个数
length('aabbcc') 	--获取字节个数，和字符集有关

instr('aaabbccbbcc','cc') -- 返回第一次出现cc子串的索引，从1起始
INSERT(str, index , len, instr)	--将字符串str从第index位置开始，len个字符长的子串替换为字符串instr
UPPER(s) --或 UCASE(s)	将字符串s的所有字母转成大写字母
LOWER(s)  --或LCASE(s)	将字符串s的所有字母转成小写字母

LEFT(s,n)	--返回字符串s最左边的n个字符
RIGHT(s,n)	--返回字符串s最右边的n个字符

LPAD(str, len, pad)	--用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)	--用字符串pad对str最右边进行填充，直到str的长度为len个字符

LTRIM(s)	--去掉字符串s左侧的空格
RTRIM(s)	--去掉字符串s右侧的空格
TRIM(s)	--去掉字符串s开始与结尾的空格
TRIM(【BOTH】s1 FROM s)	--去掉字符串s开始与结尾的s1 默认是both
TRIM(【LEADING】s1 FROM s)	--去掉字符串s开始处的s1
TRIM(【TRAILING】s1 FROM s)	--去掉字符串s结尾处的s1

REPEAT(str, n)	--返回str重复n次的结果
REPLACE（str, a, b）	--用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2)	--比较字符串s1,s2 字典序  -1 0 1
SUBSTRING(s,index,len)	--返回从字符串s的index位置其len个字符
substr('abc',1,3) 	--截取子串上面的简略写法 索引从1开始，3为截取长度，不是截取到第几个

1.2 数学函数

ABS（x）	--返回x的绝对值
CEIL（x）	--返回大于x的最小整数值  向上取整 1.09 ==》 2
FLOOR（x）	--返回大于x的最大整数值
MOD(x,y)	--返回x/y的模   取模公式 x%y = x-x/y*y
RAND(x)	--返回0~1的随机值
ROUND(x,y)	--返回参数x的四舍五入的有y位的小数的值
TRUNCATE(x,y)	--返回数字x截断为y位小数的结果
SQRT(x)	--返回x的平方根
POW(x,y)	--返回x的y次方

1.3 日期函数

-- 常用日期函数
NOW() --返回当前时间
CURDATE() 或 CURRENT_DATE()	--返回当前日期
DATEDIFF(date1,date2)  -- 日期相减  得到天数
	select datediff('1970-1-1','2020-5-7')  -- -18389天 返回time1 - time2的时间间隔
DATE_FORMAT(datetime ,fmt)	--按照字符串fmt格式化日期datetime值
STR_TO_DATE(str, fmt)	--按照fmt格式对str进行解析，解析为一个日期

--返回当前时间
CURTIME() 或 CURRENT_TIME()	

-- 返回当前系统日期时间
SYSDATE()
CURRENT_TIMESTAMP()
LOCALTIME()
LOCALTIMESTAMP()	

--返回具体的时间值
YEAR(date)
MONTH(date)
DAY(date)
HOUR(time)
MINUTE(time)
SECOND(time)	

-- 返回一年中的第几周
WEEK(date)
WEEKOFYEAR(date)	

DAYOFWEEK()	返回周几，注意：周日是1，周一是2，。。。周六是7
WEEKDAY(date)	返回周几，注意，周1是0，周2是1，。。。周日是6
DAYNAME(date)	返回星期：MONDAY,TUESDAY.....SUNDAY
MONTHNAME(date)	返回月份：January，。。。。。
TIMEDIFF(time1, time2)	返回date1 - date2的日期间隔
DATE_ADD(datetime, INTERVALE  expr  type)	返回与给定日期时间相差INTERVAL时间段的日期时间

格式符	说明	格式符	说明
%Y	4位数字表示年份	%y	表示两位数字表示年份
%M	月名表示月份（January,…）	%m	两位数字表示月份（01,02,03。。。）
%b	缩写的月名（Jan.，Feb.，…）	%c	数字表示月份（1,2,3,…）
%D	英文后缀表示月中的天数（1st,2nd,3rd,…）	%d	两位数字表示月中的天数(01,02…)
%e	数字形式表示月中的天数（1,2,3,4,5…）
%H	两位数字表示小数，24小时制（01,02…）	%h和%I	两位数字表示小时，12小时制（01,02…）
%k	数字形式的小时，24小时制(1,2,3)	%l	数字形式表示小时，12小时制（1,2,3,4…）
%i	两位数字表示分钟（00,01,02）	%S和%s	两位数字表示秒(00,01,02…)
%W	一周中的星期名称（Sunday…）	%a	一周中的星期缩写（Sun.，Mon.,Tues.，…）
%w	以数字表示周中的天数(0=Sunday,1=Monday…)
%j	以3位数字表示年中的天数(001,002…)	%U	以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天
%u	以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天
%T	24小时制	%r	12小时制
%p	AM或PM	%%	表示%

1.4 流程控制函数

if(100>9,'对','错')  --处理双分支
IFNULL(value1, value2)	--如果value1不为空，返回value1，否则返回value2

-- case函数
-- 使用一： switch语句
case 表达式
when 值1 then 结果1
when 值2 then 结果2
....
else 结果n
end
-- 使用二：
case 
when 条件1 then 结果1
when 条件2then 结果2
....
else 结果n
end

1.5 其他函数

version		版本
database	当前库
user	当前连接用户

五、分组

1. 分组函数

sum --求和
max --最大值
min --最小值
avg --平均值
count --计数 统计指定列不为NULL的记录行数；

• 特点：

  1、以上五个分组函数都忽略null值，除了count(*)
  2、sum和avg一般用于处理数值型 ；max、min、count可以处理任何数据类型
  3、都可以搭配distinct(某一列)使用，用于统计去重后的结果
  4、count的参数可以支持：字段、*、常量值，一般放1 ，建议使用 count(*)

2. 分组查询

语法：

select 查询的字段，分组函数
from 表
group by 分组的字段

特点：
1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选

针对的表	位置	关键字
分组前筛选	原始表	group by的前面
分组后筛选	分组后的结果集	group by的后面

4、可以按多个字段分组，字段之间用逗号隔开 ，没有顺序要求
5、可以支持排序
6、having后可以支持别名

六、多表查询

1. 连接查询

传统模式下的连接 ：等值连接——非等值连接

1. 等值连接的结果 = 多个表的交集
2. n表连接，至少需要n-1个连接条件
3. 多个表不分主次，没有顺序要求
4. 一般为表起别名，提高阅读性和性能

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ybY7AAIh-1594858963827)(MySQL%E5%9F%BA%E7%A1%80.assets/image-20200507105251024.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jPVSxKI1-1594858963831)(MySQL%E5%9F%BA%E7%A1%80.assets/image-20200507105225329.png)]

注意第二张图的差集部分的条件

二、sql99语法：通过join关键字实现连接

含义：1999年推出的sql语法
支持：
等值连接、非等值连接 （内连接）
外连接
交叉连接

语法：

select 字段，...
from 表1
【inner|left outer|right outer|cross】join 表2 on  连接条件
【inner|left outer|right outer|cross】join 表3 on  连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】

好处：语句上，连接条件和筛选条件实现了分离，简洁明了！

三、自连接

案例：查询员工名和直接上级的名称

sql99
	SELECT e.last_name,m.last_name
	FROM employees e
	JOIN employees m ON e.`manager_id`=m.`employee_id`;
sql92
	SELECT e.last_name,m.last_name
	FROM employees e,employees m 
	WHERE e.`manager_id`=m.`employee_id`;

2. 子查询

子查询位置：

• 子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧

按子查询出现的位置：

1. select后面：仅支持标量子查询
-- 案例：查询每个部门的员工个数
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`
 ) 个数
FROM departments d;

2. from后面：支持表子查询
--将子查询结果充当一张表，要求必须起别名
--案例：查询每个部门的平均工资的工资等级
SELECT  ag_dep.*,g.`grade_level`
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

3. where或having后面：★
	标量子查询（单行） √
	列子查询  （多行） √
	行子查询

3. exists后面（相关子查询） -- 判断查询结果是否有值
   exists(完整的查询语句)
--案例1：查询有员工的部门名 可以使用exists也可以使用in代替
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id`
);

• 按结果集的行列数不同：

  • 标量子查询（一行一列，一个结果）
  • 列子查询（一列多行）
  • 行子查询（一行多列）
  • 表子查询（任意行任意列）

• 单行子查询
  结果集只有一行
  一般搭配单行操作符使用：> < = <> >= <=
  非法使用子查询的情况：
  a、子查询的结果为一组值
  b、子查询的结果为空

• 多行子查询
  结果集有多行
  一般搭配多行操作符使用：any、all、in、not in
  in： 属于子查询结果中的任意一个就行
  any和all往往可以用其他查询代替

3. 联合查询

• 如果有关联键，联合查询一般可以用多表联结加or代替
• 要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

语法：

select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union  【all】
.....
select 字段|常量|表达式|函数 【from 表】 【where 条件】

特点：
1、多条查询语句的查询的列数必须是一致的
2、多条查询语句的查询的列的类型几乎相同
3、union代表去重，union all代表不去重

---

七、DML语言

1. 插入

语法：

insert into 表名(字段名，...)
values(值1，...),(值1，...);

-- 把查询的结果插入一个新表
insert into table_name
select id,name
from test
where id<20;

特点：
1、字段类型和值类型一致或兼容，而且一一对应
2、可以为空的字段，可以不用插入值，或用null填充
3、不可以为空的字段，必须插入值
4、字段个数和值的个数必须一致
5、字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致

2. 修改

修改单表语法：

update 表名 
set 字段=新值,字段=新值
【where 条件】

修改多表的记录(级联更新)

-- sql92语法：
update 表1 别名,表2 别名
set 列=值,...
where 连接条件
and 筛选条件;

-- sql99语法：
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件;

-- 示例 修改没有男朋友的女神的男朋友编号都为2号
UPDATE beauty be
LEFT OUTER JOIN boys bo ON be.boyfriend_id = bo.id 
SET be.boyfriend_id = 2 
WHERE bo.id IS NULL;

3. 删除

方式1：delete语句

-- 单表的删除：
delete from 表名 
[where 筛选条件]
[limit 条目数]

-- 多表的删除(级联删除)
-- delete后面指定删除的表：
-- delete 表1的别名   代表只删除满足条件的表1的记录，
-- delete 表1的别名,表2的别名   代表条件满足两个表的记录都删除
--sql92语法：
delete [表1的别名],[表2的别名]  
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;

--sql99语法：
delete [表1的别名],[表2的别名]
from 表1 别名 inner|left|right join 表2 别名 
on 连接条件
where 筛选条件;

方式2：truncate语句

truncate table 表名

两种方式的区别【面试题】

1.truncate不能加where条件，而delete可以加where条件
2.truncate的效率高一丢丢
3.truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始
4.truncate 删除不能回滚，delete删除可以回滚

---

八、DDL语句

1. 库的管理

库的管理：
一、创建库
create database 库名
二、删除库
drop database 库名

2. 表的管理

-- 1.创建表
CREATE TABLE IF NOT EXISTS stuinfo(
	stuId INT,
	stuName VARCHAR(20),
	gender CHAR,
	bornDate DATETIME
);

-- 更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;

2.修改表 alter
语法：ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE OLUMN 字段名 【字段类型】;
①修改字段名
alter table 表名 change column 旧列名 新列名 [类型];
②修改表名
ALTER TABLE 表名 RENAME [TO] studentinfo;
③修改字段类型和列级约束
ALTER TABLE 表名 MODIFY COLUMN borndate DATE ;
④添加字段
ALTER TABLE 表名 ADD COLUMN email VARCHAR(20) [first|after 字段名];
⑤删除字段
ALTER TABLE 表名 DROP COLUMN email;
3.删除表
DROP TABLE [IF EXISTS] 表名;

3. 表的复制

• 复制可以跨库，格式：库.表名

--1.仅复制表的结构
CREATE TABLE 新表名 LIKE 旧表名;

--2.复制表的结构+数据
CREATE TABLE copy2 
SELECT * FROM author;

--只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author 
WHERE nation='中国';

--仅复制某些字段
CREATE TABLE copy4 
SELECT id,au_name
FROM author
WHERE 0;

4. 常见数据类型

数字类型

--整型: 
tinyint (0~255/-128~127) 
smallint (0~65535/-32768~32767) 
mediumint 
int 
bigint (参考手册11.2)

--参数解释:
unsigned 无符号(不能为负)  强行插入负数为0
zerofill 0填充  M 填充后的宽度
举例:
tinyint unsigned;
tinyint(6) zerofill;   

--数值型
1.浮点型
float(M,D)
double(M,D)
2.定点型
dec(M，D)
decimal(M,D)

--格式:float(M,D)  unsigned\zerofill;
M：整数部位+小数部位
D：小数部位，决定保留几位小数 如果小数部分超出则四舍五入保留D位


M和D都可以省略
如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度

定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用
整数和浮点：如果超过范围，则插入临界值

特点：
① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
③ 如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！

-- 串与数字之间自动转换
create TABLE num_test(
	n1 int,
	n2 FLOAT,
	n3 DOUBLE
);

INSERT into num_test 
VALUES
('123','123','123'),
('123.1','123.12','123.12');
-- 结果：
n1 	n2 	n3
123	123	123
123	123.12	123.12

字符型

char(m)  定长  m默认1
varchar(m) 变长 必须指定m
text

列          实存字符i       实占空间            利用率
char(M)      0<=i<=M          M             i/m<=100%
varchar(M)    0<=i<=M       i+1,2         i/i+1/2<100%

--binary和varbinary用于保存较短的二进制
--较长的文本：
text
blob(较大的二进制)

enum枚举

-- 限制插入内容，实例只能插入abc，不区分大小写
CREATE TABLE tab_char(
	c1 ENUM('a','b','c')
);
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('m');  -- 失败
INSERT INTO tab_char VALUES('A');  -- 不区分大小写，转换小写

set集合

-- 存储范围0-64个成员 与enum类似，区别：可以一次插入多，个不区分大小写
CREATE TABLE tab_set(
	s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('A,B');
INSERT INTO tab_set VALUES('a,c,d');

日期时间类型:

date只保存日期
time 只保存时间
year只保存年

datetime保存日期+时间
timestamp保存日期+时间  受时区和数据库版本影响插入的数据会根据时区而变化

year       YYYY	范围:1901~2155. 可输入值2位和4位(如98,2012)
date       YYYY-MM-DD 如:2010-03-14
time       HH:MM:SS	如:19:26:32
datetime   YYYY-MM-DD  HH:MM:SS 如:2010-03-14 19:26:32
timestamp  YYYY-MM-DD  HH:MM:SS 特性:不用赋值,该列会为自己赋当前的具体时间 

		   字节		范围			时区等的影响
datetime    8		1000——9999年	     不受
timestamp	4	    1970-2038年	      受

CREATE TABLE tab_date(
	t1 DATETIME,
	t2 TIMESTAMP
);
INSERT INTO tab_date VALUES(NOW(),NOW());
SELECT * FROM tab_date;
SHOW VARIABLES LIKE 'time_zoe';
SET time_zone='+9:00';

5. 约束及其操作

NOT NULL：非空，用于保证该字段的值不能为空
比如姓名、学号等
DEFAULT:默认，用于保证该字段有默认值
比如性别
PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
比如学号、员工编号等
UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
比如座位号
CHECK:检查约束【mysql中不支持】
比如年龄、性别
FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
	在从表添加外键约束，用于引用主表中某列的值
比如学生表的专业编号，员工表的部门编号，员工表的工种编号

• 约束的添加分类：
  列级约束：六大约束语法上都支持，但外键约束没有效果
  表级约束：除了非空、默认，其他的都支持

• 查看stuinfo中的所有索引，包括主键、外键、唯一
  SHOW INDEX FROM stuinfo;

表级约束

【constraint 约束名】 约束类型(字段名)

CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

修改表时添加约束

1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;

示例：

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT
)
DESC stuinfo;
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

#4.添加唯一

#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);


#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 

#三、修改表时删除约束

#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;

#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

SHOW INDEX FROM stuinfo;

自增长列

• 标识列必是一个key(唯一，主键)
• 一个表只能有一个自增列
• 标识列的类型只能是数值型(int ,double ···)
• 标识列可以通过 SET auto_increment_increment=3;设置步长可以通过 手动插入值，设置起始值
• 查看自增信息：SHOW VARIABLES LIKE '%auto_increment%';

6. 外键级联操作

级联删除： 删除主表同时删除外键约束的从表的字段

-- 定义外键时，声明级联删除
-- 删除主表的字段时，连着从表被约束的字段一起删除
ALTER TABLE 从表 add CONSISTENT fk_从_主 FOREIGN key (从表被约束字段) REFERENCES 主表(约束字段) ON DELETE CASCADE;

级联置空：删除主表同时置空外键约束的从表的字段

ALTER TABLE 从表 add CONSISTENT fk_从_主 FOREIGN key (从表被约束字段) REFERENCES 主表(约束字段) ON DELETE SET NULL;

---

九、TCL数据库事务

含义：通过一组逻辑操作单元（一组DML——sql语句），将数据从一种状态切换到另外一种状态

1. 特点（ACID）

• 原子性：要么都执行，要么都回滚
• 一致性：保证数据的状态操作前和操作后保持一致 如：转账人和被转账人的钱数总和，转账前后都是一样的，数据还是准确可靠的。
• 隔离性：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰
• 持久性：一个事务一旦提交，则数据将持久化到本地，除非其他事务对其进行修改

相关步骤：
1、开启事务
2、编写事务的一组逻辑操作单元（多条sql语句）
3、提交事务或回滚事务

2. 事务的分类：

1. 隐式事务，没有明显的开启和结束事务的标志,insert、update、delete语句本身就是一个事务
2. 显式事务，具有明显的开启和结束事务的标志

• 开启事务
  取消自动提交事务的功能
• 编写事务的一组逻辑操作单元（多条sql语句）
  insert
  update
  delete

3、提交事务或回滚事务

3. 事务语法

-- 查看事务是否开启
SHOW VARIABLES LIKE 'autocommit';
-- 开启事务
set autocommit=0;  --默认开启事务，当前会话有效
start transaction; --可选
-- 提交与回滚
commit;
rollback;

savepoint  断点
commit to 断点
rollback to 断点

--演示savepoint 的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点

4. 事务隔离级别

在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题

• 丢失修改：T1和T2两个事务都对一-个数据进行修改, T1 先修改，T2 随后修改，T2的修改覆盖了T1的修改。

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-m6K3R2zT-1594858963836)(MySQL%E5%9F%BA%E7%A1%80.assets/image-20200508221041046.png)]

• 脏读：(读到回滚数据)T1修改一个数据，T2 随后读取这个数据。如果T1撤销了这次修改,那么T2读取的数据是脏数据。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aPiHS6HW-1594858963839)(MySQL%E5%9F%BA%E7%A1%80.assets/image-20200508221111259.png)]

• 不可重复读：(两次查询结果不一致)T2读取一个数据，T1 对该数据做了修改。如果T2再次读取这个数据，此时读取的结果和第一次读取的结果不同。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Uws9VXj5-1594858963841)(MySQL%E5%9F%BA%E7%A1%80.assets/image-20200508220918769.png)]

• 幻读：(一般针对插入)T1读取某个范围的数据, T2在这个范围内插入新的数据，T1再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qm8FeAMs-1594858963843)(MySQL%E5%9F%BA%E7%A1%80.assets/image-20200508221011223.png)]

事务的隔离级别	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed	X	√	√
repeatable read	X	X	√
serializable	X	X	X

设置隔离级别：

set session|global transaction isolation level 隔离级别名;

查看隔离级别：

select @@tx_isolation; -- 5.x
select @@transaction_isolation --8.0

十、视图

含义：理解成一张虚拟的表

• 视图和表的区别：

	使用方式	占用物理空
视图	完全相同	不占用，仅仅保存的是sql逻辑
表	完全相同	占用

• 视图的好处：
  1、sql语句提高重用性，效率高
  2、和表实现了分离，提高了安全性

1. 视图的创建

CREATE VIEW  视图名
AS
DQL语句;

2. 视图的增删改查

1、查看视图的数据

​ 和查询表一样

2、插入视图的数据

INSERT INTO my_v4(last_name,department_id) VALUES('虚竹',90);

3、修改视图的数据

UPDATE my_v4 SET last_name ='梦姑' WHERE last_name='虚竹';

4、删除视图的数据

DELETE FROM my_v4;

3. 不能更新的视图

• 包含以下关键字的sql语句：

  • 分组函数、distinct、group by、having、union或者union all
  • 常量视图
  • Select中包含子查询
  • join
  • from一个不能更新的视图
  • where子句的子查询引用了from子句中的表

  -- where子句的子查询引用了from子句中的表 示例： 
  -- from表和子查询的from表来自同一个表
  CREATE OR REPLACE VIEW myv6
  AS
  
  SELECT last_name,email,salary
  FROM employees
  WHERE employee_id IN(
  	SELECT  manager_id
  	FROM employees
  	WHERE manager_id IS NOT NULL
  );
  
  #更新
  SELECT * FROM myv6;
  UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';
  

4. 视图逻辑的更新

方式一：

CREATE OR REPLACE VIEW test_v7
AS
SELECT last_name FROM employees
WHERE employee_id>100;

方式二:

ALTER VIEW test_v7
AS
SELECT employee_id FROM employees;
SELECT * FROM test_v7;

5. 删除视图

DROP VIEW test_v1,test_v2,test_v3;

6. 视图结构的查看

DESC test_v7;
SHOW CREATE VIEW test_v7;

---

十一、变量

@表示局部变量: 用户自定义的变量
**@@表示全局系统变量变量: 用户不可以自定义，可以修改 **

1. 系统变量

一、全局变量

作用域：针对于所有会话（连接）有效，但不能跨重启

--查看所有全局变量
SHOW GLOBAL VARIABLES;
--查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
--查看指定的系统变量的值
SELECT @@global.autocommit;
--为某个系统变量赋值 不跨重启
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;

二、会话变量

作用域：针对于当前会话（连接）有效

--查看所有会话变量
SHOW SESSION VARIABLES;
--查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
--查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
--为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';

2. 自定义变量

一、用户变量

• 会话有效
• 弱类型
• 变量名前必须加@

声明、初始化并赋值一起操作 ：

#赋值操作符：=或:=
#①声明并初始化
SET @变量名=值;
SET @变量名:=值;  --推荐
SELECT @变量名:=值;

--方式二：将表中查询的结果赋给一个变量，注意必须是标量查询
SELECT 字段名或表达式 INTO @变量名,@变量名,·····
FROM 表;
-- 示例
SELECT COUNT(*) INTO @num
from employees;

使用：

select @变量名;

二、存储过程中的局部变量

• 仅在定义它的begin end块中有效
• 应用在 begin end中的第一句话
• 声明和赋值分开，不用@起始

声明：

declare 变量名 类型 [default 值];

赋值：

DELIMITER $
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	DECLARE 局部变量名 数据类型 DEFAULT 0; -- 声明并初始化
	存储过程体（一组合法的SQL语句）;
END $

使用：

DELIMITER $
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	····
	select 变量名
END $

二者的区别：

	作用域	定义位置	语法
用户变量	当前会话	会话的任何地方	加@符号，不用指定类型
局部变量	定义它的BEGIN END中	BEGIN END的第一句话	一般不用加@,需要指定类型

​

---

十二、存储过程

含义：一组经过预先编译的sql语句的集合

• 优点：

  提高了sql语句的重用性，减少了开发程序员的压力
  提高了效率
  减少了传输次数

参数模式：

• in：该参数可以作为输入，也就是该参数需要调用方传入值
• out：该参数可以作为输出，也就是该参数可以作为返回值
• inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要入值，又可以返回值

1. 创建存储过程

create procedure 存储过程名(in|out|inout 参数名  参数类型,...)
begin
	存储过程体;
end

• 如果存储过程体仅仅只有一句话，begin end可以省略
• 存储过程体中的每条sql语句的结尾要求必须加分号。
• 存储过程的结尾可以使用 delimiter (定界符)重新设置

-- 定界符使用示例
delimiter $
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型,...)
BEGIN
	sql语句1;
	sql语句2;
END $

--调用存储过程
call 存储过程名(实参列表)

2. 删除存储过程

-- sql#语法：drop procedure 存储过程名
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;

3. 查看存储过程的信息

DESC myp2; -- 不支持此方式
SHOW CREATE PROCEDURE 存储过程名;

4. 存储过程示例

--案例2：根据输入的女神名，返回对应的男神名和魅力值
delimiter $
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT) 
BEGIN
	SELECT boys.boyname ,boys.usercp INTO boyname,usercp
	FROM boys 
	RIGHT JOIN
	beauty b ON b.boyfriend_id = boys.id
	WHERE b.name=beautyName ;
END $


#调用
CALL myp7('小昭',@name,@cp)$
SELECT @name,@cp$


--4.创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回
CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END $

#调用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$

---

十三、函数

1. 语法

CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
BEGIN
	函数体
	RETURN 变量;
END

注意： 函数必须有返回值，如不需要返回值用存储过程
1.参数列表 包含两部分：
参数名 参数类型

2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议

return 值;
3.函数体中仅有一句话，则可以省略begin end
4.使用 delimiter语句设置结束标记

2. 操作

• 查看函数：SHOW CREATE FUNCTION myf3;

• 删除函数：DROP FUNCTION myf3;

• 调用函数：SELECT 函数名（实参列表）

3. 示例

-- 1.无参有返回
# 案例：返回公司的员工个数
delimiter $
CREATE FUNCTION myf1() RETURNS INT -- 注意返回值
BEGIN
	DECLARE c INT DEFAULT 0;#定义局部变量
	SELECT COUNT(*) INTO c#赋值
	FROM employees;
	RETURN c; -- 注意return
END $

SELECT myf1()$

-- 2.有参有返回
#案例1：根据员工名，返回它的工资

CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0; -- 定义用户变量 非局部变量也可以使用
	SELECT salary INTO @sal   #赋值
	FROM employees
	WHERE last_name = empName;
	RETURN @sal; -- 注意返回
END $

4. 函数和存储过程的区别

关键字	调用语法	返回值	应用场景
函数 FUNCTION	SELECT 函数()	只能是一个	一般用于查询©结果为一个值并返回时，当有返回值而且仅仅一个
存储过程 PROCEDURE	CALL 存储过程()	可以有0个或多个	一般用于更新 (RUD)

---

十四、流程控制结构

1. 分支

一、if函数

语法：if(条件，值1，值2)
特点：可以用在任何位置

二、case语句

-- 情况一：类似于switch
case 表达式
when 值1 then 结果1或语句1(如果是语句，需要加分号) 
when 值2 then 结果2或语句2(如果是语句，需要加分号)
...
else 结果n或语句n(如果是语句，需要加分号)
end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要）

-- 情况二：类似于多重if
case 
when 条件1 then 结果1或语句1(如果是语句，需要加分号) 
when 条件2 then 结果2或语句2(如果是语句，需要加分号)
...
else 结果n或语句n(如果是语句，需要加分号)
end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要）

三、if elseif语句 只能~用在begin end中

if 情况1 then 语句1;
elseif 情况2 then 语句2;
...
else 语句n;
end if;

示例

-- 案例1：独立使用if-else
-- 创建函数，实现传入成绩，返回评级
delimiter $
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
	DECLARE ch CHAR DEFAULT 'A';
	IF score>90 THEN SET ch='A';
	ELSEIF score>80 THEN SET ch='B';
	ELSEIF score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END IF; --注意 结尾加end if
	RETURN ch;
END $

-- 案例2：独立使用CASE-WHEN
--创建函数，实现传入成绩，返回评级
CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN 
	DECLARE ch CHAR DEFAULT 'A';
	CASE 
	WHEN score>90 THEN SET ch='A';
	WHEN score>80 THEN SET ch='B';
	WHEN score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END CASE; --注意 在存储过程或函数中分支结构结尾加case
	RETURN ch;
END $
SELECT test_case(56)$

三者比较：

	应用场合
if函数	简单双分支
case结构	等值判断 的多分支
if结构	区间判断 的多分支

2. 循环

• 只能放在BEGIN END里面

分类：

while、loop、repeat

循环控制：

• iterate类似于 continue，继续，结束本次循环，继续下一次
• leave 类似于 break，跳出，结束当前所在的循环

语法：

-- while 循环
[标签:]while 循环条件  do
	循环体
end while [标签:];

-- loop循环 没有结束判断，需要配合leave跳出
[标签:]loop
	循环体;
end loop [标签:];

-- repeat 循环
[标签:]repeat
	循环体;
until 结束循环的条件
end repeat [标签:];

特点：

• 如果要搭配leave跳转语句，需要使用标签，否则可以不用标签

3. 示例

-- 1.没有添加循环控制语句
#案例：批量插入，根据次数插入到admin表中多条记录
delimiter $
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');
		SET i=i+1;
	END WHILE;
END $
CALL pro_while1(100)$

-- 2.添加leave语句
#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		IF i>=20 THEN LEAVE a;
		END IF;
		SET i=i+1;
	END WHILE a;
END $
CALL test_while1(100)$


-- 3.添加iterate语句
#案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i<=insertCount DO
		SET i=i+1;
		IF MOD(i,2)!=0 THEN ITERATE a;
		END IF;
		
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		
	END WHILE a;
END $
CALL test_while1(100)$

---

十五、索引

概念

• MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。

• 数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

• mysql索引底层的数据结构是B+树

1. 索引分类

1.1 单值索引

• 即一个索引只包含单个列，一个表可以有多个单列索引

随表一起建索引：
CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT ,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
  	PRIMARY KEY(id),
 	KEY (customer_name)
);
  
单独建单值索引：
CREATE INDEX idx_customer_name ON customer(customer_name); 
 
删除索引：
DROP INDEX idx_customer_name  on customer;

1.2 唯一索引

• 索引列的值必须唯一，但允许有空值

随表一起建索引：
CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT ,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
  	PRIMARY KEY(id),
  	KEY (customer_name),
 	UNIQUE (customer_no)
);
  
单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
 

1.3 主键索引

• 设定为主键后数据库会自动建立索引，innodb为聚簇索引
• 新表可以任意更改主键，使用长的旧表不推荐修改，可能有很多其他表进行外键约束

随表一起建索引：
CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
  	PRIMARY KEY(id) 
);

单独建主键索引：
ALTER TABLE customer 
add PRIMARY KEY customer(customer_no);  
 
删除建主键索引：
ALTER TABLE customer drop PRIMARY KEY ;  
 
修改建主键索引：
必须先删除掉(drop)原索引，再新建(add)索引

1.4 复合索引

• 即一个索引包含多个列
• 复合索引有顺序

随表一起建索引：
CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT ,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
  	PRIMARY KEY(id),
    KEY (customer_name),
  	UNIQUE (customer_name),
  	KEY (customer_no,customer_name)
);
 
单独建索引：
CREATE INDEX idx_no_name ON customer(customer_no,customer_name); 

本文地址：https://blog.csdn.net/qq_39711439/article/details/107374902