MySQL基础学习笔记-CSDN博客

本文链接：https://blog.csdn.net/hesorchen/article/details/115581267

文章目录

课程引入

为什么要学数据库

常见的数据库管理系统有MySQL、Oracle、DB2、SqlServer。程序员在客户端程序，通过数据库管理系统，对数据进行增删改查。其中，MySQL由于开源、免费等因素，是当前最受欢迎的数据库管理系统。淘宝、网易、百度、Facebook等众多互联网公司都在使用MySQL，学精了MySQL，就业市场广阔。

将数据存放在内存中不能持久化存储，存放在普通文件中不方便管理。
数据库管理系统的好处：

实现数据持久化
使用完整的管理系统统一管理,易于查询

数据库相关概念

DB：数据库（database），存储数据的“仓库”，他保存了一系列有组织的数据。

DBMS：数据库管理系统（database management system），数据库是通过DBMS进行管理的。

SQL：结构化查询语言（structure query language）。

SQL的优点

几乎所有的DBMS都支持SQL
简单易学
虽然简单，但是功能强大，灵活使用可以完成诸多复杂、高级的功能。

数据库是如何存储数据的

将数据放在表上，再将表放进库中。
一个数据库可以有多个表，每个表名称具有唯一性。
表有自己的特性，表示表中数据的属性，一张表就类似于C++中的一个类。
表由列组成，列也称为字段。每一个列就类似于C++中类的一个属性。
表中数据按行存储，每一行类似于C++中的一个对象。

MySQL服务的登陆和退出

# MySQL服务的启动
net start 服务名

# MySQL服务的停止
net stop 服务名

# 连接本机
mysql -u root -p

# 退出
exit

MySQL常见命令

# 查看当前所有数据库
SHOW DATABASES;

# 打开指定的库
USE 库名;

# 查看库中的表
SHOW TABLES
FROM
	库名;

# 创建表
creat TABLE 表名 (
	列名 列类型,
	列名 列类型,
	列名 列类型 ,
	......);

# 查看表结构
DESC 表名;

MySQL语法规范

不区分大小写，但是建议关键字大写，其他小写
每条命令用分号结尾
命令可以缩进和换行
单行注释

# 单行注释
-- 单行注释（--后有空格）

多行注释

/*
多行注释
*/

SQL语言的细分

DQL语言（Data Query Language）：查询功能
DML语言（Data Manipulation Language）：增删改功能
tips：也有书籍将上面两种语言统称为DML，也即合并了增删改查四个功能。
DDL语言（Data Define Language）：数据定义语言
TCL语言（Transaction Control Language）：事务控制语言

查询（DQL语言）

基础查询

# 语法
SELECT
	查询列表
FROM
	表名;

# 查询单个字段
SELECT
	first_name
FROM
	employees;

# 查询多个字段 逗号隔开
SELECT
	first_name,
	last_name
FROM
	employees;

# 查询所有字段
SELECT
	*
FROM
	employees;

# 起别名 AS关键字 （空格也行）
SELECT
	first_name AS 名,
	last_name 姓
FROM
	employees

# 去重 Distinct关键字
SELECT DISTINCT
	last_name 姓
FROM
	employees

条件查询

# 语法
SELECT
	查询列表
FROM
	表名
WHERE
	筛选条件;

# 条件运算符
> 
<
<=
>=
<> 或 !=

# 逻辑运算符
and 或 &&
or 或 || 
not 或 !

# 按条件运算符查询
SELECT
	*
FROM
	employees
WHERE
	salary > 12000;

#  逻辑运算符连接条件运算符查询

SELECT
	*
FROM
	employees
WHERE
	salary > 12000
AND salary < 180000;

#  模糊查询

LIKE 
BETWEEN AND 
IN 
IS NULL

/*
like 关键字 查询字符串
需要使用通配符
% 可以匹配若干个字符（包含0）
_ 可以匹配一个字符
*/
SELECT
	last_name
FROM
	employees
WHERE
	last_name LIKE '_r%'
/*
结果
Ernst
Greenberg
Urman
... ...
*/

# 转义字符：反斜杠 或 escape关键字

SELECT
	last_name
FROM
	employees
WHERE
	last_name LIKE '\_%' #查询以下划线开头的字符串
  	last_name LIKE 'a_%' escape 'a' #与上面语句等价

# between and 查找区间范围内的数字

SELECT
	last_name,
	salary
FROM
	employees
WHERE
	salary BETWEEN 10000
AND 15000

# in 判断字段值是否属于列表中的某一项

SELECT
	last_name,
	salary
FROM
	employees
WHERE
	salary IN (12000, 13000)

# is null 判断字段值是否为null
# =和!=不能判断null

SELECT
	last_name,
	salary
FROM
	employees
WHERE
	commission_pct IS NULL;
#	commission_pct is not null;

排序查询

# order by 排序列表（asc升序，desc降序）
# 注意先筛选（where）后排序

SELECT
	employee_id,
	salary,
	manager_id
FROM
	employees
WHERE
	salary BETWEEN 10000
AND 20000
ORDER BY
	salary DESC;

# 多关键字排序

SELECT
	employee_id,
	salary,
	manager_id
FROM
	employees
WHERE
	salary BETWEEN 10000
AND 20000
ORDER BY
	manager_id ASC,
	salary DESC;
# 从前往后排序，先按manager_id升序，再按salary降序。

函数

# 调用
SELECT
	函数名 (实参列表)

常见的字符函数

注意，SQL语言中的索引都从1开始。

# 获取字符串长度（字节）
SELECT
	length("abc123");

# 字符串拼接
SELECT
	concat(first_name, ' ', last_name) '姓名'
FROM
	employees;

# 将last_name中第一个字母大写，其余小写
SELECT
	concat(
		upper(substr(first_name, 1, 1)),
		lower(substr(first_name, 3))
	) '姓名'
FROM
	employees;

常见的数学函数

# round 四舍五入
SELECT
	round(1.456, 2);

# ceil 向上取整
SELECT
	ceil(1.1);

# floor 向下取整
SELECT
	floor(1.9);

常见的日期函数

# 返回当前系统时间
SELECT
	now();

流程控制函数

# if函数 类似于C语言中的三目运算
SELECT
IF (10 > 5, 'yes', 'no');

# case函数 类似于C语言中的switch case
CASE 表达式
WHEN 常量1 THEN
	语句1
WHEN 常量2 THEN
	语句2
WHEN 常量3 THEN
	语句3
ELSE
	默认语句
END

分组（统计）函数

单行函数一般作用于一个字段的一个值，而分组函数一般作用于一个字段的所有值。

# SUM(求和)
SELECT
	SUM(salary)
FROM
	employees;

# SUM(distinct )去重求和
SELECT
	SUM(DISTINCT salary)
FROM
	employees;

# AVG(求平均值n)
SELECT
	AVG(salary)
FROM
	employees;

# COUNT(计数)
SELECT
	COUNT(DISTINCT salary)
FROM
	employees;

分组查询

# group by 按照字段值分组
SELECT
	manager_id
FROM
	employees
GROUP BY
	manager_id;

# 分组函数结合分组查询
# 每个经理底下所有员工的最大工资
SELECT
	manager_id,
	max(salary)
FROM
	employees
GROUP BY
	manager_id;

# 分组前筛选
# 底下所有员工的最大工资大于5000的所有经理
SELECT
	manager_id,
	max(salary)
FROM
	employees
WHERE
	manager_id > 105
GROUP BY
	manager_id;

# 分组后筛选
# 底下所有员工的最大工资大于5000的所有经理
SELECT
	manager_id,
	max(salary)
FROM
	employees
GROUP BY
	manager_id
HAVING
	max(salary) > 5000;

# 分组前筛选+分组后筛选
# 底下所有员工的最大工资大于5000的所有经理
# 分组函数作为筛选条件肯定是放在having子句中。
SELECT
	manager_id,
	max(salary)
FROM
	employees
WHERE
	manager_id > 105
GROUP BY
	manager_id
HAVING
	max(salary) > 5000;

连接查询

# 笛卡尔乘积现象：表1有m行，表2有n行，结果总共有n*m行。

#需要使用链接查询避免笛卡尔乘积现象
SELECT
	boyname,
	NAME 
FROM
	boys,
	beauty
WHERE
	boys.id = beauty.boyfriend_id;

为表起别名

# 提高语句的简洁度
# 给表起别名之后，不能再使用原表名

SELECT 
	FIRST_name,
	department_name
FROM
	employees,
	departments
WHERE 
	departments.department_id=employees.department_id;


SELECT
	FIRST_name,
	department_name
FROM
	employees e,	# 起别名
	departments d	# 起别名
WHERE 
	d.department_id=e.department_id;

等值连接

只有两个表的字段值相同时才会被查询到。

SELECT
	d.department_name 部门名,
	l.city 所在城市
FROM
	locations l,
	departments d  
WHERE
	l.location_id = d.location_id

非等值连接

# 不用等于号作为链接
SELECT
	grade_level,
	employee_id
FROM
	job_grades,
	employees
WHERE
	salary <= highest_sal && salary >= lowest_sal

自连接

# 一张表连接自己
SELECT
	e1.last_name 员工名字,
	e2.last_name 此员工的经理名字,
	e1.employee_id 员工id,
	e2.employee_id 此员工的经理id
FROM
	employees e1,
	employees e2
WHERE
	e1.manager_id = e2.employee_id

sql99语法_等值连接

SELECT
	[查询列表]
FROM
	[表1],
	[连接类型] JOIN
	[表2]
ON 
	[连接条件]
WHERE
	[筛选条件]
GROUP BY
	[分组条件]
ORDER BY
	[排序列表]

/*
连接类型
	内连接：inner

	外连接：
		左外：left (outer)
		右外：right（outer）
		全外：full（outer）
	
	交叉连接：cross
*/

等值连接

SELECT
	count(*),l.city
FROM
	departments d
INNER JOIN
	locations l 
ON
	d.location_id=l.location_id
GROUP BY
	l.city
HAVING
	count(*)>=3
ORDER BY
	count(*)

SELECT
	count(*),
	department_name
FROM
	departments d
INNER JOIN  
	employees e
ON 
	d.department_id=e.department_id
GROUP BY
	d.department_name
HAVING
	count(*)>3
ORDER BY
	count(*)
ASC

非等值连接、自连接同理。

左（右）外连接

/*
应用场景：用于查询一个表中有，而另一个表中没有的记录。

1.外连接的查询结果为主表中的所有记录
	如果从表中有和它匹配的记录，则显示它的值。
	如果从表中没有和它匹配的记录，则显示NULL。

2.左外连接，left join左边的表是主表
  左外连接，right join右边的表是主表
*/

SELECT
	boyName,
	name
FROM
	boys
RIGHT JOIN
	beauty
ON 
	boys.id=beauty.boyfriend_id

全外连接

/*
全外连接的结果为：内连接+表1有表2没有+表2有表1没有
*/
SELECT
	b1.*,
	b2.*
FROM
	boys b1
FULL OUTER JOIN
	beauty b2
ON
	b1.id = b2.boyfriend_id

交叉连接

/*
交叉连接结果为笛卡尔积
*/

子查询

where后面的标量子查询

SELECT
	first_name
FROM
	employees e
WHERE
	e.salary > (
		SELECT
			sum(salary) / count(*)
		FROM
			employees
	)
ORDER BY
	salary

SELECT
	first_name,
	job_id,
	salary
FROM
	employees e
WHERE
	e.job_id = (
		SELECT
			job_id
		FROM
			employees
		WHERE
			employee_id = 141
	) && e.salary > (
		SELECT
			salary
		FROM
			employees
		WHERE
			employee_id = 143
	)

having后面的标量子查询

SELECT
	min(salary)
FROM
	employees
GROUP BY
	department_id
HAVING
	min(salary) > (
		SELECT
			min(salary)
		FROM
			employees
		WHERE
			department_id = 50
	)

where后面的列子查询

SELECT
	first_name
FROM
	employees
WHERE
	department_id IN (
		SELECT
			department_id
		FROM
			departments
		WHERE
			location_id = 1400 || location_id = 1700
	)

where后面的行子查询

SELECT
	*
FROM
	employees
WHERE
	(employee_id, salary) = (
		SELECT
			min(employee_id),
			max(salary)
		FROM
			employees
	)

select后面的标量子查询

SELECT
	d.department_name,
	(
		SELECT
			count(*)
		FROM
			employees e
		WHERE
			e.department_id = d.department_id
	)
FROM
	departments d

from后面的子查询

SELECT
	grade_level
FROM
	(
		SELECT
			AVG(salary) ag,
			department_id
		FROM
			employees
		GROUP BY
			department_id
	) s
JOIN job_grades j ON s.ag <= j.highest_sal && s.ag >= j.lowest_sal;

exists后面的子查询（相关子查询）

# exists（是否存在） 返回 0 或 1 
SELECT
	EXISTS (
		SELECT
			*
		FROM
			employees
		WHERE
			salary > 10000
	)

SELECT
	department_name
FROM
	departments d
WHERE
	EXISTS (
		SELECT
			*
		FROM
			employees e
		WHERE
			e.department_id = d.department_id
	)

limit 分页查询

# limit [begin] [size] 
# 索引从0开始
SELECT
	*
FROM
	employees
LIMIT 10,5;
# 查询11-15

union 联合查询

/*
union 会自动 去重，不想去重可以使用union all
union 的两个查询结果字段数量必须一样多
*/
SELECT
	employee_id
FROM
	employees
UNION
SELECT
	department_id
FROM
	departments

增删改（DML语言）

插入记录

/*
语法：
INSERT INTO 表名（列名1,...）
VALUE（值1,...）

插入的值的类型要和列的类型一致或兼容。
列的数量和顺序可以改变，但要和值一一对应上。
*/

写法一

INSERT  INTO boys(id,boyname,usercp) 
value(5,'hesorchen',520)

# 列名可以省略，默认为表中的字段。
INSERT  INTO boys 
value(6,'hesor',52013)

写法二：

INSERT INTO boys
SET id = 10,
 boyname = 'chenyan',
 usercp = 1314;

写法三：

INSERT INTO temp SELECT
	*
FROM
	copy;

修改记录

UPDATE boys
SET 列1=值1,	
    列2=值2,
     ...
WHERE
    筛选条件

UPDATE boys
SET id = 9,
 boyname = 'zhangsan',
 userCP = 2
WHERE
	id = 3

删除记录

# 删除一定是一行
# 语法
DELETE
FROM
	表名
WHERE
	筛选条件

DELETE
FROM
	boys
WHERE
	id = 2

# truncate删除记录，删除所有记录
 TRUNCATE TABLE t1;

delete删除记录可以回滚，truncate删除记录不可以回滚。

数据定义语言（DDL语言）

库的管理

# 创建库
CREATE DATABASE 库名

# 没有才创建库
CREATE DATABASE IF NOT EXISTS 库名

库的删除

# 删除库
DROP DATABASE 库名

# 没有才删除库
DROP DATABASE IF EXISTS 库名

表的创建

# 语法：
CREATE TABLE 表名（
	列名 列的类型【（长度） 约束】,
	列名 列的类型【（长度） 约束】, 
	列名 列的类型【（长度） 约束】
）

CREATE TABLE books (
	id INT,
	#编号
	bname VARCHAR (20),
	#书名
	author VARCHAR (20),
	#作者
	price DOUBLE #价格
);

表的修改

ALTER TABLE 表名 [ADD/DROP/change/MODIFY] COLUMN 列名

修改列名

# 语法：
ALTER TABLE 表名 
CHANGE COLUMN 原列名 要改成的列名 改后的列类型

ALTER TABLE books 
CHANGE COLUMN author writer VARCHAR (15)

修改列的类型和约束

# 语法：
ALTER TABLE 表名 
MODIFY COLUMN  要改的列名 改后的列类型

ALTER TABLE books 
MODIFY COLUMN  id TIMESTAMP

增加列

# 语法：
ALTER TABLE 表名 
ADD COLUMN  要增加的列名 列类型

ALTER TABLE books 
ADD COLUMN  test INT

删除列

# 语法：
ALTER TABLE 表名 
DROP COLUMN  要删除的列名

ALTER TABLE books
DROP COLUMN  test

修改表名

# 语法：
ALTER TABLE 表名 
RENAME TO  要改成的表名

ALTER TABLE books
RENAME TO  book

表的复制

# 只复制表结构
CREATE TABLE copy LIKE temp;

# 复制结构及数据
CREATE TABLE copy SELECT
	*
FROM
	temp
WHERE
	a1 = 1;

表的删除

# 删除表
DROP TABLE 表名

# 没有才删除表
DROP TABLE IF EXISTS 表名

数据类型

整形

类型	字节	范围
Tinyint	1	-128~127
Smallint	2	-32768~32767
Mediumintint	3	-8388608~8388607
Int	4	-2147483648~2147483647
Bigint	8	-9223372036854775808~9223372036854775807

CREATE TABLE temp (
	a1 INT,
	a2 INT UNSIGNED,
	a3 BIGINT
);

小数

类型	字节
Float	1
Double	2

CREATE TABLE temp (
	a1 FLOAT(10【整数位+小数位总位数】,2【小数位数】),
	a2 DOUBLE(10,2),
	a3 DECIMAL(10,2)
);

字符类型

类型	最多字符数	描述及存储需求
Char(M)	M	M为0~255的整数
Varchar(M)	M	M为0~65535的整数

Char为固定长度字符，Varchar为可变长度字符，因此Char更耗费空间，但是效率更高。

还有一些不太常用的字符类型：

Binary和Varbinary，类似于Char和Varchar，不过它们是用来存储二进制串。
Enum(‘a’,‘b’,‘c’)，括号中是列表值，最终选择的值只能是在列表中的。
Set(‘a’,‘b’,‘c’)，类似于Enum，不过Enum是单选，Set可以多选。

CREATE TABLE temp (a CHAR(10));

INSERT INTO temp
VALUES
	('01hello陈');

SELECT
	*
FROM
	temp;

CREATE TABLE temp (a BINARY(10));

INSERT INTO temp
VALUES
	('0111001');

SELECT
	*
FROM
	temp;

CREATE TABLE temp (a SET('1', '2', '3'));

INSERT INTO t1
VALUES
	('1'),
	('1,3');

SELECT
	*
FROM
	t1;

日期型

数据类型	用处
date	保存日期
time	保存时间
year	保存年份
datetime	保存日期和时间
timestamp	保存日期和时间

约束

常见约束

约束类型	用处
NOT NULL	字段值非空
DEFAULT	字段值有默认值
PRIMARY KEY	主键，字段值具有唯一性，并且非空
UNIQUE	字段值具有唯一性
FOREIGN KEY	外键，改字段值必须来自于主表的关联列的值【比如学生的专业，必须来自于专业表】

创建约束

列级约束

CREATE TABLE temp1 (
	id INT PRIMARY KEY,
	majorNAME VARCHAR (20)
);

CREATE TABLE temp (
	id INT PRIMARY KEY,
	stuNAME VARCHAR (20) NOT NULL,
	gender CHAR (1) DEFAULT '男',
	seat INT UNIQUE
);

表级约束

语法格式:
#【constraint 约束名】 约束类型(字段名)
# 【】中的内容可以省略，省略后约束名会自动生成。

CREATE TABLE temp1 (
	id INT PRIMARY KEY,
	majorNAME VARCHAR (20)
);

CREATE TABLE temp (
	id INT,
	majorid INT,
	stuNAME VARCHAR (20),
	gender CHAR (1),
	seat INT,
	CONSTRAINT con1 PRIMARY KEY(id),
	CONSTRAINT con2 UNIQUE(seat),
	CONSTRAINT fk_temp_temp1 FOREIGN KEY(majorid) REFERENCES temp1(id)
);

一般来说，外键使用表级约束，其他使用列级约束。

组合主键以及组合唯一键。功能就是当组合起来冲突时才算冲突。

CREATE TABLE temp (
	id INT,
	majorid INT,
	stuNAME VARCHAR (20),
	gender CHAR (1),
	seat INT,
	PRIMARY KEY (id, majorid),
	UNIQUE (seat, gender)
);

外键的特点

必须在从表创建外键。
主从表关联列的类型要一致。
主表中的关联列必须是key（一般是主键或唯一键）
插入数据时，先插入主表，再插入从表。删除数据时，先删除从表，再删除主表。

修改表时添加约束

语法：
1. 添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 列名 字段类型 新约束
2. 添加列级约束
ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】 约束类型(字段名)

# 添加非空约束
ALTER TABLE t1 MODIFY COLUMN id INT NOT UNIQUE;

# 添加默认约束
ALTER TABLE t1 MODIFY COLUMN id INT default 100;

# 添加主键约束
# 1.列级约束写法
ALTER TABLE t1 MODIFY COLUMN id INT PRIMARY KEY;
# 2.表级约束写法
ALTER TABLE t1 ADD PRIMARY KEY(id);

# 添加唯一约束
# 1.列级约束写法
ALTER TABLE t1 MODIFY COLUMN id INT UNIQUE;
# 2.表级约束写法
ALTER TABLE t1 ADD UNIQUE(id);

# 添加外键约束
ALTER TABLE t1 ADD FOREIGN KEY(id) REFERENCES t2(id);

修改表时删除约束

语法：
1. 添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 列名 字段类型 新约束
2. 添加列级约束
ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】 约束类型(字段名)

# 删除非空约束
ALTER TABLE t1 MODIFY COLUMN id INT;

# 删除默认约束
ALTER TABLE t1 MODIFY COLUMN id INT;

# 删除主键约束
ALTER TABLE t1 DROP PRIMARY KEY;

# 删除唯一约束
ALTER TABLE t1 DROP INDEX 约束名;

# 删除外键约束
ALTER TABLE t1 DROP FOREIGN KEY 约束名;

标识列

又称自增长列，可以不用手动插入值，系统提供默认的序列值，默认从1开始。

标识列的特点：

必须是一个key
一个表最多一个标识列
标识必须是数值类型（整数或小数）

添加标识列

# 创建表时添加标识列
CREATE TABLE
IF NOT EXISTS t1 (
	id INT UNIQUE AUTO_INCREMENT
);

# 修改表时设置标识列
ALTER TABLE t1 MODIFY COLUMN id INT UNIQUE AUTO_INCREMENT;

删除标识列

ALTER TABLE t1 MODIFY COLUMN id INT;

事务控制语言（TCL）

事务：一个或一组sql组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

事务的属性（ACID）

原子性（Atomicity）：事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态。
隔离性（Isolation）：隔离性是当多个用户并发访问数据库时，比如同时操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
持久性（Durability）：持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

事务的创建

隐式事务：事务没有明显的开启和结束

比如insert、update、delete语句

显式事务：事务具有明显的开启和结束
前提：必须先设置自动提交功能为禁用

SET autocommit = 0;
START TRANSACTION;

事务中的sql语句（select INSERT UPDATE delete）;

COMMIT;#提交事务
ROLLBACK;#回滚事务

事务并发可能导致的问题

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：

问题	描述
脏读	对于两个事务T1，T2，T1读取了已经被T2更新但还没被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的。
不可重复读	对于两个事务T1，T2，T1读取了一个字段，然后T2更新了该字段之后，T1再次读取同一个字段，值就不同了。
幻读	对于两个事务T1，T2，T1读取了一个字段，然后T2在该表中插入了一些新的行，T1再次读取同一个表，表就多了几行。

事务的隔离性

数据库系统必须具有隔离并发运行各个事务的能力，使它们不会互相影响，避免各种并发问题。

一个事务与其他事务隔离的程度称为隔离级别，数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但并发性越弱。

事务的隔离级别

隔离级别	描述
READ UNCOMMITTED（读未提交数据）	允许事务读取未被其他事务提交的变更。脏读、不可重复读、幻读都可能出现。
READ COMMITTED（读已提交数据）	只允许事务读取已被其他事务提交的变更。可以避免脏读，但不可重复读、幻读仍可能出现。
REPEATABLE READ（可重复读）	确保事务可以多次从一个字段读取相同的值。在这个事务持续期间，不允许其他事务对这个字段进行更新。可能出现幻读。
Serializable（串行化）	在这个事务持续期间，不允许其他事务对这个字段进行更新、删除、插入。可以避免所有并发问题，但是性能十分低下。

mysql中默认第三种隔离级别：repeatable read
oracle中默认第二种隔离级别：read committed

查看隔离级别

select @@transaction_isolation;

设置隔离级别

set session【global】 transaction isolation level 隔离级别;

savepoint的使用

SET autocommit = 0;
INSERT INTO t1 VALUES(1);
SAVEPOINT p1;# 设置保存点
INSERT INTO t1 VALUES(2);
ROLLBACK TO p1;# 回滚到保存点
# 最后只提交了第一条insert语句。

视图

视图：一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。

应用场景：

多个地方用到同样的查询结果
该查询结果使用的sql语句比较复杂

视图的创建

CREATE VIEW V1 
AS 
SELECT
	studentname,
	majorname
FROM
	students
INNER JOIN major ON students.marjorid = major.majorid
WHERE
	studnets.majorid = 1;

SELECT * FROM V1 WHERE studentname LIKE '张%';

视图的修改

# 方式一 CREATE OR REPLACE VIEW 视图名

CREATE OR REPLACE VIEW V1 
AS 
SELECT
	studentname,
	majorname
FROM
	students
INNER JOIN major ON students.marjorid = major.majorid
WHERE
	studnets.majorid = 1;

# 方式二 ALTER VIEW 视图名

ALTER VIEW V1 
AS 
SELECT
	studentname,
	majorname
FROM
	students
INNER JOIN major ON students.marjorid = major.majorid
WHERE
	studnets.majorid = 1;

视图的删除

DROP VIEW 视图名;

视图的查看

DESC VIEW 视图名;

视图的更新

对视图进行insert、update、delete等操作时，会在原表中做对应的更新，不在视图中的字段值为NULL。但是以下情况不能对视图进行更新：

包含以下关键字的sql语句：分组函数、distinct、group by、having、union、union all
常量视图
select中包含子查询
连接查询
from一个不能更新的表
where子句中的子查询引用了from子句中的表

存储过程和函数

变量

一、系统变量：

全局变量
会话变量

二、自定义变量：

用户变量
局部变量

一、系统变量

系统变量由系统提供，不是用户定义，属于服务器层面使用的语法：
注意：如果是全局级别，使用关键字GLOBAL，如果是会话级别，使用关键字SESSION，都不使用默认SESSION。

查看所有系统变量

SHOW GLOBAL VARIABLES; # 查看所有系统变量
SHOW SESSION VARIABLES; # 查看所有会话变量

查看满足条件的部分系统变量

SHOW GLOBAL VARIABLES LIKE ‘%char%’; # 查看名称中包含‘char’的所有系统变量

查看指定的系统变量的值

SELECT @@GLOBAL.系统变量名; # 查看系统变量名的值
SELECT @@global.autocommit;

为指定的系统变量赋值

# 方式一
SET global|SESSION 系统变量名 = 值;
# 方式二
SET @@global|SESSION.系统变量名 = 值;

二、自定义变量

用户变量

作用域：针对于当前会话有效，同于会话变量的作用域。

声明并初始化

# 三种方式
SET @用户变量名 = 值;
SET @用户变量名 := 值;
SELECT @用户变量名 := 值;

赋值

# 方式一 
SET @用户变量名 = 值;
SET @用户变量名 := 值;
SELECT @用户变量名 := 值;

# 方式二
SELECT 字段 INTO @用户变量名 FROM 表;

查看

SELECT @用户变量名;

局部变量

作用域：仅仅在定义它的begin end中有效。

声明

DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;

赋值

# 方式一 
SET 局部变量名 = 值;
SET 局部变量名 := 值;
SELECT @局部变量名 := 值;

# 方式二
SELECT 字段 INTO 局部变量名 FROM 表;

查看

SELECT 局部变量名;

存储过程

含义：一组预先编译好的SQL语句的集合，理解成批处理语句。

优点：

提高代码的重用性
简化操作
减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。

存储过程的创建

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体
END

存储过程的创建

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体
END

注意：

参数列表包含三个部分：参数模式参数名参数类型
参数模式有三种：
IN：该参数作为输入
OUT：该参数可以作为输出（返回值）
INOUT：该参数即作为输入，又可以作为输出
如果存储过程体只有一句话，可以省略BEGIN END。存储过程体中的每条SQL语句的结尾必须加分号;
默认情况下，delimiter是分号，可以用DELIMITER重新设置结尾符号。如果delimiter不重新设置，会和存储过程中的分号冲突，因此需要事先将delimiter设置成其他符号：

DELIMITER $$

存储过程的调用

CALL 存储过程名(实参列表);

存储过程使用实例

无参的存储过程实例：

CREATE TABLE
IF NOT EXISTS t1 (num INT);

DELIMITER $$

CREATE PROCEDURE ins1 ()
BEGIN
	INSERT INTO t1
	VALUES
		(1),
		(3) ;
END $$

CALL ins1 () $$

SELECT
	*
FROM
	t1 $$

带IN模式参数的存储过程实例：

CREATE TABLE
IF NOT EXISTS t1 (num INT);

DELIMITER $$

CREATE PROCEDURE ins2 (IN val INT)
BEGIN
	INSERT INTO t1
	VALUES
		(val);
END $$

CALL ins2 (10086) $$

SELECT
	*
FROM
	t1 $$

带OUT模式参数的存储过程实例：

CREATE TABLE
IF NOT EXISTS t1 (num INT);

DELIMITER $$

CREATE PROCEDURE get_sum (OUT s INT)
BEGIN
	SELECT
		sum(num) INTO s
	FROM
		t1 ; 
END$$


SET @answer = 0$$

CALL get_sum (@answer)$$

SELECT
	@answer$$

带INOUT模式参数的存储过程实例：

# 使两个变量翻倍
DELIMITER $$
CREATE PROCEDURE doub (INOUT a INT, INOUT b INT)
BEGIN
	SET a := a * 2 ;
	SET b := b * 2 ; 
END$$


SET @a = 1 ,@b = 2$$

CALL doub (@a ,@b)$$

SELECT
	@a ,@b$$