MySQL学习
前期准备
服务启动与停止
# 启动 MySQL 服务命令：
net start MySQL服务名
# 停止 MySQL 服务命令：
net stop MySQL服务名
net start MySQL80
登录与退出
mysql -h 主机名 -P 端口号 -u 用户名 -p密码
mysql -u root -p
quit
exit
- -p与密码之间不能有空格，其他参数名与参数值之间可以有空格也可以没有空格。
- 密码也可以在下一行输入
- 客户端和服务器在同一台机器上，输入localhost或者IP地址127.0.0.1。如果连接本机： -hlocalhost可以省略，如果端口号没有修改：-P 3306也可以省略
数据导入
mysql> source d:\mysqldb.sql
创建与管理表
show databases; #查看所有数据库
“information_schema”是 MySQL 系统自带的数据库，主要保存 MySQL 数据库服务器的系统信息， 比如数据库的名称、数据表的名称、字段名称、存取权限、数据文件 所在的文件夹和系统使用的 文件夹，等等
“performance_schema”是 MySQL 系统自带的数据库，可以用来监控 MySQL 的各类性能指标。
“sys”数据库是 MySQL 系统自带的数据库，主要作用是以一种更容易被理解的方式展示 MySQL 数据 库服务器的各类性能指标，帮助系统管理员和开发人员监控 MySQL 的技术性能。
“mysql”数据库保存了 MySQL 数据库服务器运行时需要的系统信息，比如数据文件夹、当前使用的 字符集、约束检查信息，等等
创建数据库
#创建数据库
CREATE DATABASE 数据库名;
#创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
CREATE DATABASE IF NOT EXISTS test01_library CHARACTER SET 'utf8';
#如果数据库不存在再创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;
create database students; #创建数据库,不可重名
use students; #使用数据库
使用数据库
#查看当前所有的数据库
SHOW DATABASES;
#查看当前正在使用的数据库
SELECT DATABASE();
USE DATABASENAME; #使用数据库
修改数据库
ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等
查表
show tables; #查看数据库中的所有表格,应在使用数据库后
show tables from databasename;
#查看表的数据
select * from 数据库表名称;
select *from info;
select *from students.info;
建表
#方式1
create table 表名称[IF NOT EXISTS](
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);
create table info(
id int,
age int,
name varchar(20),
school varchar(20)
);
#方式2
CREATE TABLE emp1 AS SELECT * FROM employees;
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的emp2是空表
#指定的列和子查询中的列要一一对应
#通过列名和默认值定义列
CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;
查看详细信息
#查看表创建信息
show create table 表名称;
#查看数据库创建信息
show create database 数据库名;
show create table info;
show create database students;
修改表
#增加列
ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;
ALTER TABLE dept80
ADD job_id varchar(15);
#修改列 可以修改列的数据类型，长度、默认值和位置
ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;
ALTER TABLE dept80
MODIFY last_name VARCHAR(30)
ALTER TABLE dept80
MODIFY salary double(9,2) default 1000;
-- 对默认值的修改只影响今后对表的修改
#重命名列
ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;
ALTER TABLE dept80
CHANGE department_name dept_name varchar(15);
#重命名表
RENAME TABLE emp
TO myemp;
ALTER table dept
RENAME [TO] detail_dept; -- [TO]可以省略
清空表
- TRUNCATE TABLE语句：
- 删除表中所有的数据
- 释放表的存储空间
TRUNCATE TABLE detail_dept;
TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚
SET autocommit = FALSE;
DELETE FROM emp2;
#TRUNCATE TABLE emp2;
SELECT * FROM emp2;
ROLLBACK;
SELECT * FROM emp2;
TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句,只有DELETE可以回滚
删除
#删除表格
drop table 表名称;
DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
#删除一个列
ALTER TABLE 表名 DROP 【COLUMN】字段名
ALTER TABLE dept80
DROP COLUMN job_id;
#删除数据库
drop database 数据库名;
#如果存在则删除数据库
DROP DATABASE IF EXISTS 数据库名;
修改字符编码格式
show variables like 'character_%';
show variables like 'collation_%';
# my.ini配置为
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8
collation-server = utf8_unicode_ci
init_connect='SET NAMES utf8'
数据增删改
添加记录
为表的所有字段按默认顺序插入数据,需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同
insert into students.info values(1,18,'张三','一中');
insert into students.info values(2,18,'李四','一中');
insert into students.info values(3,18,'王二','二中');
insert into students.info values(4,18,'李四','二中');
insert into students.info values(5,18,'王二','一中');
insert into students.info values(6,19,'abc_','test');
为表的指定字段插入数据,为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值
INSERT INTO 表名(column1 [, column2, …, columnn])
VALUES (value1 [,value2, …, valuen]);
INSERT INTO departments(department_id, department_name)
VALUES (80, 'IT');
INSERT语句可以同时向数据表中插入多条记录,效率更高,插入时指定多个值列表，每个值列表之间用逗号分隔开
INSERT INTO table_name
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);
INSERT INTO table_name(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);
将查询结果插入到表中
- 不必书写 VALUES 子句
- 子查询中的值列表应与 INSERT 子句中的列名对应
INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]
INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';
更新数据
可以一次更新多条数据。 如果需要回滚数据，需要保证在DML前，进行设置：
SET AUTOCOMMIT = FALSE
UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]
UPDATE employees
SET department_id = 70
WHERE employee_id = 113;
删除数据
“[WHERE ]”为可选参数，指定删除条件，如果没有WHERE子句， DELETE语句将删除表中的所有记录
DELETE FROM table_name
[WHERE <condition>];
DELETE FROM departments
WHERE department_name = 'Finance';
MySQL8新特性 计算列
是某一列的值是通过别的列计算得来的
CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);
INSERT INTO tb1(a,b) VALUES (100,200);
数据类型
基本数据类型
| 类型 | 类型举例 |
|---|---|
| 整数类型 | TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT |
| 浮点类型 | FLOAT、DOUBLE |
| 定点数类型 | DECIMAL |
| 位类型 | BIT |
| 日期时间类型 | YEAR、TIME、DATE、DATETIME、TIMESTAMP |
| 文本字符串类型 | CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT |
| 枚举类型 | ENUM |
| 集合类型 | SET |
| 二进制字符串类型 | BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB |
| 空间数据类型 | 单值类型：GEOMETRY、POINT、LINESTRING、POLYGON； 集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION |
数据类型属性
| MySQL关键字 | 含义 |
|---|---|
| NULL | 数据列可包含NULL值 |
| NOT NULL | 数据列不允许包含NULL值 |
| DEFAULT | 默认值 |
| PRIMARY KEY | 主键 |
| AUTO_INCREMENT | 自动递增，适用于整数类型 |
| UNSIGNED | 无符号 |
| CHARACTER SET name | 指定一个字符集 |
整数类型
可选属性
M :显示宽度,M的取值范围是(0, 255)。例如，int(5)：当数据宽度小于5位的时候在数字前面需要用字符填满宽度。该项功能需要配合“ ZEROFILL ”使用，表示用“0”填满宽度，否则指定显示宽度无效。整型数据类型可以在定义表结构时指定所需要的显示宽度，如果不指定，则系统为每一种类型指定默认的宽度值
CREATE TABLE test_int2( f1 INT, f2 INT(5),#无效 f3 INT(5) ZEROFILL )UNSIGNED : 无符号类型（非负）,无符号整数类型的最小取值为0。如果需要在MySQL数据库中保存非负整数值时，可以将整数类型设 置为无符号类型。 int类型默认显示宽度为int(11)，无符号int类型默认显示宽度为int(10)
CREATE TABLE test_int3( f1 INT UNSIGNED );ZEROFILL : 0填充,（如果某列是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性），如果指定了ZEROFILL只是表示不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可
浮点数类型
MySQL支持的浮点数类型，分别是 FLOAT、DOUBLE、REAL
REAL默认就是 DOUBLE。如果你把 SQL 模式设定为启用“ REAL_AS_FLOAT ”，那 么，MySQL 就认为 REAL 是 FLOAT。如果要启用“REAL_AS_FLOAT”，可以通过以下 SQL 语句实现
SET sql_mode = “REAL_AS_FLOAT”;
FLOAT(M,D) 或 DOUBLE(M,D)。这里，M称为精度 ，D称为标度 。(M,D)中 M=整数位+小数 位，D=小数位。 D<=M<=255，0<=D<=30。 例如，定义为FLOAT(5,2)的一个列可以显示为-999.99-999.99。如果超过这个范围会报错,不指定(M,D)时，默认会按照实际的精度来显示浮点类型，也可以加 UNSIGNED ，但是不会改变数据范围，例如：FLOAT(3,2) UNSIGNED仍然 只能表示0-9.99的范围
误差问题:因为浮点数是不准确的，所以我们要避免使用“=”来 判断两个数是否相等,对精确度要求较高的项目中，不要使用浮点数
定点数
- 使用 DECIMAL(M,D) 的方式表示高精度小数。其中，M被称为精度，D被称为标度。0<=M<=65， 0<=D<=30，D<M。例如，定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99
- DECIMAL(M,D)的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。 DECIMAL 的存储空间并不是固定的，由精度值M决定，总共占用的存储空间为M+2个字节。
- 定点数在MySQL内部是以 字符串 的形式进行存储，这就决定了它一定是精准的
- 当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的 精度范围时，则MySQL同样会进行四舍五入处理
CREATE TABLE test_decimal1(
f1 DECIMAL,#123
f2 DECIMAL(5,2)#123.46
);
#123.456
位类型
CREATE TABLE test_bit1(
f1 BIT,
f2 BIT(5),
f3 BIT(64)
);
#使用SELECT命令查询位字段时，可以用 BIN() 或 HEX() 函数进行读取
SELECT BIN(f2),HEX(f2)-- 二进制,十六进制
FROM test_bit1;
日期时间类型
- 以4位字符串或数字格式表示YEAR类型，其格式为YYYY，最小值为1901，最大值为2155。
- 以2位字符串格式(不推荐)表示YEAR类型，最小值为00，最大值为99。
- 当取值为01到69时，表示2001到2069；
- 当取值为70到99时，表示1970到1999；
- 当取值整数的0或00添加的话，那么是0000年；
- 当取值是日期/字符串的’0’添加的话，是2000年
CREATE TABLE test_year(
f1 YEAR,
f2 YEAR(4)
);
INSERT INTO test_year
VALUES('2020','2021');
INSERT INTO test_year
VALUES('45','71');
INSERT INTO test_year
VALUES(0,'0');
+------+------+
| f1 | f2 |
+------+------+
| 2020 | 2021 |
| 2045 | 1971 |
| 0000 | 2000 |
+------+------+
- 以 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期，其最小取值为1000-01-01，最大取值为 9999-12-03。
- YYYYMMDD格式会被转化为YYYY-MM-DD格式。
- 以 YY-MM-DD 格式或者 YYMMDD 格式表示的字符串日期，此格式中，年份为两位数值或字符串满足 YEAR类型的格式条件为：当年份取值为00到69时，会被转化为2000到2069；当年份取值为70到99 时，会被转化为1970到1999。
- 使用 CURRENT_DATE() 或者 NOW() 函数，会插入当前系统的日期
CREATE TABLE test_date1(
f1 DATE
);
INSERT INTO test_date1
VALUES ('2020-10-01'), ('20201001'),(20201001);
INSERT INTO test_date1
VALUES ('00-01-01'), ('000101'), ('69-10-01'), ('691001'), ('70-01-01'), ('700101'),('99-01-01'), ('990101');
INSERT INTO test_date1
VALUES (000301), (690301), (700301), (990301);
INSERT INTO test_date1
VALUES (CURRENT_DATE()), (NOW());
SELECT *
FROM test_date1;
- 可以使用“HH:MM:SS”格式来表示TIME类型
- 在MySQL中，向TIME类型的字段插入数据时，也可以使用几种不同的格式。
- 可以使用带有冒号的 字符串，比如’ D HH:MM:SS’ 、’ HH:MM:SS ‘、’ HH:MM ‘、’ D HH:MM ‘、’ D HH ‘或’ SS '格式。其中D表示天，其最小值为0，最大值为34。如果使用带有D格式的字符串,插入TIME类型的字段时，D会被转化为小时，计算格式为D*24+HH。当使用带有冒号并且不带D的字符串表示时间时，表示当天的时间，比如12:10表示12:10:00
- 可以使用不带有冒号的 字符串或者数字，格式为’ HHMMSS '或者 HHMMSS 。如果插入一个不合法的字符串或者数字，MySQL在存储数据时，会将其自动转化为00:00:00进行存储。比如1210，MySQL会将最右边的两位解析成秒，表示 00:12:10，
- 使用 CURRENT_TIME() 或者 NOW() ，会插入当前系统的时间。
CREATE TABLE test_time1(
f1 TIME
);
INSERT INTO test_time1
VALUES('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'),('1 05'), ('45');
INSERT
INTO test_time1
VALUES ('123520'), (124011),(1210);
INSERT INTO test_time1
VALUES (NOW()), (CURRENT_TIME());
SELECT DISTINCT * FROM test_time1;
60:30:29
12:35:29
12:40:00
60:40:00
29:00:00
00:00:45
12:35:20
12:40:11
00:12:10
10:34:40
10:37:01
- DATETIME类型为DATE类型和TIME类型的组合，可以表示为 YYYY-MM-DD HH:MM:SS
- 以 YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时， 最小值为1000-01-01 00:00:00，最大值为9999-12-03 23:59:59
CREATE TABLE test_datetime1(
dt DATETIME
);
INSERT INTO test_datetime1
VALUES ('2021-01-01 06:50:30'), ('20210101065030');
INSERT INTO test_datetime1
VALUES ('99-01-01 00:00:00'), ('990101000000'), ('20-01-01 00:00:00'),
('200101000000');
INSERT INTO test_datetime1
VALUES (20200101000000), (200101000000), (19990101000000), (990101000000);
INSERT INTO test_datetime1
VALUES (CURRENT_TIMESTAMP()), (NOW());
- TIMESTAMP类型也可以表示日期时间，其显示格式与DATETIME类型相同，都是 YYYY-MM-DD HH:MM:SS,存储 “1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间
- 存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间
CREATE TABLE test_timestamp1(
ts TIMESTAMP
);
INSERT INTO test_timestamp1
VALUES ('1999-01-01 03:04:50'), ('19990101030405'), ('99-01-01 03:04:05'),
('990101030405');
INSERT INTO test_timestamp1
VALUES ('2020@01@01@00@00@00'), ('20@01@01@00@00@00');
INSERT INTO test_timestamp1
VALUES (CURRENT_TIMESTAMP()), (NOW());
#修改当前的时区
SET time_zone = '+9:00';
TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。
两个日期比较大小或日期计算时，TIMESTAMP更方便、更快
一般存注册时间、商品发布时间等，不建议使用DATETIME存储，而是使用 时间戳 ，因为 DATETIME虽然直观，但不便于计算
文本字符串类型
- CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。
- 如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。
CREATE TABLE test_char1(
c1 CHAR,
c2 CHAR(5)
);
DESC test_char1;
INSERT INTO test_char1
VALUES('a','Tom');
SELECT c1,CONCAT(c2,'***') FROM test_char1;
INSERT INTO test_char1(c2)
VALUES('a ');
SELECT CHAR_LENGTH(c2)
FROM test_char1;
- VARCHAR(M) 定义时， 必须指定 长度M，否则报错。
- MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节） ；MySQL5.0版本以上，varchar(20)：指的是20字符。
- 检索VARCHAR类型的字段数据时，会保留数据尾部的空格。
- VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节
由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键
在保存和查询数据时，并没有删除TEXT类型的数据尾部的空格
枚举类型
- ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时，ENUM 类型只允许从成员中选取单个值，不能一次选取多个值
CREATE TABLE test_enum(
season ENUM('春','夏','秋','冬','unknow')
);
INSERT INTO test_enum
VALUES('春'),('秋');
# 忽略大小写
INSERT INTO test_enum
VALUES('UNKNOW');
# 允许按照角标的方式获取指定索引位置的枚举值
INSERT INTO test_enum
VALUES('1'),(3);
# Data truncated for column 'season' at row 1
INSERT INTO test_enum
VALUES('ab');
# 当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的
INSERT INTO test_enum
VALUES(NULL);
select * from test_enum;
SET类型
SET表示一个字符串对象，可以包含0个或多个成员，但成员个数的上限为 64 。设置字段值时，可以取取值范围内的 0 个或多个值
CREATE TABLE test_set(
s SET ('A', 'B', 'C')
);
INSERT INTO test_set (s) VALUES ('A'), ('A,B');
#插入重复的SET类型成员时，MySQL会自动删除重复的成员
INSERT INTO test_set (s) VALUES ('A,B,C,A');
#向SET类型的字段插入SET成员中不存在的值时，MySQL会抛出错误。
INSERT INTO test_set (s) VALUES ('A,B,C,D');
SELECT *
FROM test_set;
CREATE TABLE temp_mul(
gender ENUM('男','女'),
hobby SET('吃饭','睡觉','打豆豆','写代码')
);
INSERT INTO temp_mul VALUES('男','睡觉,打豆豆'); #成功
# Data truncated for column 'gender' at row 1
INSERT INTO temp_mul VALUES('男,女','睡觉,写代码'); #失败
# Data truncated for column 'gender' at row 1
INSERT INTO temp_mul VALUES('妖','睡觉,写代码');#失败
INSERT INTO temp_mul VALUES('男','睡觉,写代码,吃饭'); #成功
二进制字符串类型
主要存储一些二进制数据，比如可以存储图片、音频和视频等二进制数据
BLOB是一个二进制大对象 ，可以存储一个二进制的大对象，比如图片、音频和视频等,在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据，通常会将图片、音频和视频文件存储到 服务器的磁盘上 ，并将图片音频和视频的访问路径存储到MySQL中
BLOB和TEXT值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的" 空洞 "，以后填入这些"空洞"的记录可能长度不同。为了提高性能，建议定期 使用 OPTIMIZE TABLE 功能对这类表进行碎片整理
如果需要对大文本字段进行模糊查询，MySQL 提供了前缀索引 。但是仍然要在不必要的时候避免检索大型的BLOB或TEXT值。例如，SELECT * 查询就不是很好的想法，除非能够确定作为约束条件的 WHERE子句只会找到所需要的数据行。否则可能毫无目的地在网络上传输大量的值
把BLOB或TEXT列 分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可 以让你把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会 减少主表中的 碎片 ，使你得到固定长度数据行的性能优势。它还使在主数据表上运行 SELECT * 查询的时候不会通过网络传输大量的BLOB或TEXT值
JSON类型
JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式
CREATE TABLE test_json(
js json
);
INSERT INTO test_json (js)
VALUES ('{"name":"abc", "age":18, "address":{"province":"beijing",
"city":"beijing"}}');
检索JSON类型的字段中数据的某个具体值时，可以使用“->”和“->>”符号
SELECT js'$.name' AS NAME,js '$.age' AS age ,js '$.address.province'
AS province, js '$.address.city' AS city
FROM test_json;
空间类型
SQL分类
- DDL(Data Definition Languages、数据定义语言),这些语句定义了不同的数据库,表,视图,索引等数据库对象,还有可以用来创建,删除,修改数据库和数据表结构
- 包括CREATE,DROP,ALTER等
- DML(Data Manipulation Language、数据操作语言),用于添加,删除,更新和查询数据库记录,并检查数据完整性
- 包括INSERT,DELETE,UPDATE,SELECT等
- DCL(Data Control Language、数据控制语言),用于定义数据库,表,字段,用户访问权限和安全级别
- 包括GRANT,REMOVE,COMMIT,ROLLBACK,SAVEPOINT
语言规范
- SQL 可以写在一行或者多行
- 每条命令以 ; 或 \g 或 \G 结束
\g 相当于;
\G 将查询结果进行按列打印，横向表格纵向输出，可以使每个字段打印到单独的行
- **字符串型和日期时间类型的数据可以使用单引号’ '表示 **
- 列的别名，尽量使用双引号"",而且不建议省略as
- MySQL在windows下大小写不敏感,在Linux下大小写敏感
- 数据库名、表名、表的别名、变量名是严格区分大小写的
- 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
注释
#注释内容
-- 注释内容,需有空格
/*
多行注释
*/
命名规则
- 数据库,表名不超过30字符,变量名限制为29个
- 只能包括A-Z,a-z,0-9,_
- 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
- 如果字段与关键字,保留字冲突,可以通过**’ 着重号**引起来
create table 'order'(
onum int
);
基本的SELETE语句
SELECT … FROM
SELECT 标识选择哪些列
FROM 标识从哪个表中选择
select id,age from info;
#选择全部列
SELECT *
FROM students.info
别名
以在列名和别名之间加入关键字AS(可省)，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写
select id as "学号",age as "年龄",name as "姓名" from students.info;
#起别名时，as都可以省略,如果字段别名中没有空格，那么可以省略""
去除重复行
使用关键字DISTINCT去除重复行
select distinct * from students.info;
空值参与运算
- 所有运算符或列值遇见null值,结果都为null
- 空值不等于空字符串,空值占用空间,空字符串不占用空间
查询常数
在查询结果中增加一列固定的常数列,该列取值为指定的,作为标记
select '标记' ,id ,age ,name from info;
select distinct '标记' as tag,id ,age ,name from info;
SELECT执行过程
#方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#方式2：
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页
执行顺序:FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
显示表结构DESCRIBE
describe info;
或
desc info;
- Field：表示字段名称
- Type：表示字段类型，这里 barcode、goodsname 是文本型的，price 是整数类型
- Null：表示该列是否可以存储NULL值
- Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一 部分；MUL表示在列中某个给定值允许出现多次
- Default：表示该列是否有默认值，如果有，那么值是多少
- Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。
过滤数据WHERE
SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件
select distinct * from students.info
where school='一中';
#WHERE一定放在FROM后面
运算符
算数运算符
| 运算符 | 示例 |
|---|---|
| + | select a+b |
| - | select a-b |
| * | select a*b |
| /或DIV | select a/b 或 select a div b |
| %或MOD | select a%b 或 select a mod b |
- 整数加减乘法 ,结果为整数
- 整数与浮点数加减乘法,结果为浮点数
- 一个数除以整数或浮点数,不管能否除尽,结果为浮点数
- 除不尽保留小数点后4位
- 一个数除以0,结果为null
比较运算符
| 运算符 | 作用 | 示例 |
|---|---|---|
| = | 等于 | select c from table where a = b |
| <=> | 安全等与运算符,安全地判断值,字符串,表达式是否相等 | select c from table where a <=> b |
| <> != | 不等于 | select c from table where a <> b |
| < | 小于 | select c from table where a < b |
| <= | 小于等于 | select c from table where a <= b |
| > | 大于 | select c from table where a > b |
| >= | 大于等于 | select c from table where a >= b |
- 等号运算符,相等返回1,不等返回0
- 如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。
- 如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。
- 如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
- 如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。
- 赋值运算符
:= - 安全等于运算符与等于运算符的区别‘<=>’可以用来对NULL进行判断。在两个操作数均为NULL时,其返回值为1,而不为NULL;当一个操作数为NULL 时,其返回值为0,而不为NULL。
- 不等于运算符不能判断NULL值,若两边值为NULL则结果为NULL
非符号类型运算符
| 运算符 | 名称 | 作用 | 示例 |
|---|---|---|---|
| IS NULL | 为空运算符 | 判断值,字符串,表达式是否为空 | select b from table where a is null |
| IS NOT NULL | 不为空运算符 | 判断值,字符串,表达式是否不为空 | select b from table where a is not null |
| LEAST | 最小值运算符 | 多个值返回最小值 | select d from table where c =least(a,b) |
| GREATEST | 最大值运算符 | 多个值返回最大值 | select d from table where c =greatest(a,b) |
| BETWEEN AND | 判断一个值是否在两个值之间 | select distinct id,age ,name from info where id between 2 and 4; | |
| RLIKE | 正则表达式运算符 | 判断一个值是否符合正则表达式规则 | select b from table where a rlike b |
| IN | 属于运算符 | 判断一个值是否为列表内任意一个值 | select d from table where c in (a,b) |
| NOT IN | 不属于运算符 | 判断一个值是否不为列表内任意一个值 | select d from table where c not in (a,b) |
| LIKE | 模糊匹配运算符 | 判断一个值是否符合模糊匹配规则 | select c from table where a like b |
| REGEXP | 正则表达式运算符 | 判断一个值是否符合正则表达式规则 | select b from table where a regexp b |
| ISNULL | 为空运算符 | 判断值,字符串,表达式是否为空 | select b from table where a isnull |
- 空运算符（IS NULL或者ISNULL）判断一个值是否为NULL，如果为NULL则返回1，否则返回 0
- 非空运算符（IS NOT NULL）判断一个值是否不为NULL，如果不为NULL则返回1，否则返 回0
- 最小值运算符 返回最小值。当比较值列表中有NULL时，返回值为NULL。
- 最大值运算符 返回值为最大值。假如任意一个变量为NULL，返回值为NULL。
- IN运算符判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。如果给定的值为NULL，或者IN列表中存在NULL，则结果为NULL。
LIKE运算符
用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回 0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。
“%”：匹配0个或多个字符。
“_”：只能匹配一个字符。
select distinct id,age ,name from info where name like '_b%';
ESCAPE
- 回避特殊符号:替代转义字符
SELECT job_id
FROM jobs
WHERE job_id LIKE ‘IT\_%‘;# 使用\转义,省略escape
WHERE job_id LIKE ‘IT$_%‘ escape ‘$‘
正则表达式
# expr REGEXP 匹配条件 如果expr满足匹配条件，返回1；如果不满足，则返回0。
# 若expr或匹配条件任意一个为NULL，则结果为NULL。
select distinct id,age ,name from info
where name regexp '^a';#以a开头
select distinct id,age ,name from info
where name regexp '\_$';#以_结尾
select distinct id,age ,name from info
where name regexp '...';#匹配三个任意字符
| 模式 | 描述 |
|---|---|
| ^ | 匹配以该字符后面的字符开头的字符串。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ‘\n’ 或 ‘\r’ 之后的位置。 |
| $ | 匹配以该字符前面的字符结尾的字符串。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ‘\n’ 或 ‘\r’ 之前的位置。 |
| . | 匹配除 “\n” 之外的任何单个字符。要匹配包括 ‘\n’ 在内的任何字符，请使用像 ‘[.\n]’ 的模式。 |
| […] | 字符集合。匹配所包含的任意一个字符。例如， ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。 |
| [^…] | 负值字符集合。匹配未包含的任意字符。例如， ‘[^abc] 可以匹配 “plain” 中的’p’。 |
| p1|p2|p3 | 匹配 p1 或 p2 或 p3。例如，‘z|food’ 能匹配 “z” 或 “food”。‘(z|f)ood’ 则匹配 “zood” 或 “food”。 |
| * | 匹配前面的子表达式零次或多次。例如，zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。“[0-9]*”匹配任何数量的数字 |
| + | 匹配前面的子表达式一次或多次。例如，‘zo+’ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}。 |
{n} |
n 是一个非负整数。匹配确定的 n 次。例如，‘o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。 |
{n,m} |
m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。 |
SELECT * FROM fruits WHERE f_name REGEXP 'on';
#符查询f_name字段值为“on”的记录
SELECT * FROM fruits WHERE f_name REGEXP '[ot]';
#查找f_name字段中包含字母‘o’或者‘t’的记录
SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';
#匹配指定字符以外的字符 “[^字符集合]” 匹配不在指定集合中的任何字符
SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';
#使用{n,}或者{n,m}来指定字符串连续出现的次数
SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';
#查询f_name字段值出现字符串“ba”最少1次、最多3次的记录
逻辑运算符
| 运算符 | 作用 | 示例 |
|---|---|---|
| NOT或! | 非 | SELECT NOT A |
| AND或&& | 与 | SELECT A AND B SELECT A && B |
| OR或|| | 或 | SELECT A OR B SELECT A||B |
| XOR | 异或 | SELECT A XOR B |
- 逻辑非运算符表示当给定的值为0时返回1；当给定的值为非0值时返回0； 当给定的值为NULL时，返回NULL
SELECT last_name, job_id
FROM employees
WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');
- 逻辑与运算符是当给定的所有值均为非0值，并且都不为NULL时，返回 1；当给定的一个值或者多个值为0时则返回0；否则返回NULL
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >=10000
AND job_id LIKE '%MAN%';
- 逻辑或运算符是当给定的值都不为NULL，并且任何一个值为非0值时，则返 回1，否则返回0；当一个值为NULL，并且另一个值为非0值时，返回1，否则返回NULL；当两个值都为 NULL时，返回NULL
#查询基本薪资不在9000-12000之间的员工编号和基本薪资
SELECT employee_id,salary FROM employees
WHERE NOT (salary >= 9000 AND salary <= 12000);
SELECT employee_id,salary FROM employees
WHERE salary <9000 OR salary > 12000;
SELECT employee_id,salary FROM employees
WHERE salary NOT BETWEEN 9000 AND 12000;
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
OR job_id LIKE '%MAN%'
- 逻辑异或运算符是当给定的值中任意一个值为NULL时，则返回NULL；如果 两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1。
select last_name,department_id,salary
from employees
where department_id in (10,20) XOR salary > 8000;
位运算符
| 运算符 | 作用 | 示例 |
|---|---|---|
| & | 按位与 | SELECT A & B |
| | | 按位或 | SELECT A | B |
| ^ | 按位异或 | SELECT A ^ B |
| ~ | 按位取反 | SELECT ~ A |
| >> | 按位右移 | SELECT A >>2 |
| << | 按位左移 | SELECT B << 2 |
排序与分页
- 使用 ORDER BY在SELECT语句的结尾排序
- ASC升序 (默认)
- DESC降序
单列排序
select distinct id,age ,name from info
order by id asc;
select distinct id,age ,name from info
order by id desc;
多列排序
select distinct id,age ,name from info
order by age desc,id desc;
分页
分页显示，就是将数据库中的结果集，一段一段显示出来
LIMIT [位置偏移量,] 行数
#位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数,如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，以此类推）；第二个参数“行数”指示返回的记录条数。
--前10条记录：
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
--第11至20条记录：
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录：
SELECT * FROM 表名 LIMIT 20,10;
约束返回结果的数量可以减少数据表的网络传输量 ，也可以提升查询效率 。如果我们知道返回结果只有 1 条，就可以使用 LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需 要扫描完整的表，只需要检索到一条符合条件的记录即可返回。
不同的DBMS关键字可能不同
多表查询
笛卡尔积(交叉连接CROSS JOIN)
可以把任意表进行连接，即使这两张表不相关
在MySQL中如下情况会出现笛卡尔积：
#查询员工姓名和所在部门名称
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;
笛卡尔积的不足
省略多个表的连接条件（或关联条件）
连接条件（或关联条件）无效
所有表中的所有行互相连接
为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件
#案例：查询员工的姓名及其部门名称
#在表中有相同列时，在列名之前加上表名前缀
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
等值连接 非等值连接
等值连接
连接n个表最少需要n-1个连接条件
SELECT employees.employee_id, employees.last_name,
employees.department_id, departments.department_id,
departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
#多个表中有相同列时，必须在列名之前加上表名前缀,在不同表中具有相同列名的列可以用 表名 加以区分
表的别名
使用别名简化查询,提高查询效率,需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替， 不能使用原有的表名，否则就会报错。
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;
非等值连接
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
自连接 非自连接
当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询
SELECT CONCAT(worker.last_name ,' works for ', manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;
concat()函数
功能：将多个字符串连接成一个字符串。
语法：concat(str1, str2,…)
内连接 外连接
- 内连接:合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行
- 外连接:两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)
- 如果是左外连接，则连接条件中左边的表也称为主表 ，右边的表称为从表 。 如果是右外连接，则连接条件中右边的表也称为主表 ，左边的表称为从表 。
主表为匹配项+主表剩余未匹配项
语法
# 使用JOIN...ON子句创建连接
SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1和table2 的连接条件
JOIN table3 ON table2和table3 的连接条件
- 可以使用 ON 子句指定额外的连接条件。
- 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接
内连接(INNER JOIN)
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);
SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;
外连接(OUTER JOIN)
左外连接(LEFT OUTER JOIN)
#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
右外连接(RIGHT OUTER JOIN)
#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
满外连接(FULL OUTER JOIN)
满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
使用FULL JOIN 或 FULL OUTER JOIN来实现。
MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT JOIN代替。
UNION使用
合并查询结果,利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
- UNION 操作符返回两个查询的结果集的并集，去除重复记录
- UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。(使用资源更少)
#方式1
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
#方式2
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;
SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male'
SQL99特性
自然连接(NATURAL JOIN)
#自动查询两张连接表中所有相同的字段,然后进行等值连接
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
#自然连接写法为
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;
USING连接
使用 USING 指定数据表里的同名字段进行等值连接。但是只能配合JOIN一起使用
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);
SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;
小结
- WHERE：适用于所有关联查询
- ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起 写，但分开写可读性更好。
- USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字 段值相等
#关联条件
#把关联条件写在where后面
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;
#把关联条件写在on后面，只能和JOIN一起使用
SELECT last_name,department_name
FROM employees INNER JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees CROSS JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees JOIN departments
ON employees.department_id = departments.department_id;
#把关联字段写在using()中，只能和JOIN一起使用
#而且两个表中的关联字段必须名称相同，而且只能表示=
#查询员工姓名与基本工资
SELECT last_name,job_title
FROM employees INNER JOIN jobs USING(job_id);
#n张表关联，需要n-1个关联条件
#查询员工姓名，基本工资，部门名称
SELECT last_name,job_title,department_name FROM employees,departments,jobs
WHERE employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;
SELECT last_name,job_title,department_name
FROM employees INNER JOIN departments INNER JOIN jobs
ON employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;
子查询
SELECT select_list
FROM table
where expr operator
(SELECT select_list
FROM table
);
子查询（内查询）在主查询之前一次执行完成。
子查询的结果被主查询（外查询）使用 。
子查询要包含在括号内
单行操作符对应单行子查询，多行操作符对应多行子查询
子查询不返回任何行
单行子查询
SELECT last_name, job_id, salary
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141)
AND salary >
(SELECT salary
FROM employees
WHERE employee_id = 143);
#返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
成对比较
#查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id
#不成对比较
SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN
(SELECT manager_id
FROM employees
WHERE employee_id IN (174, 141))
AND department_id IN
(SELECT department_id
FROM employees
WHERE employee_id IN (174, 141))
AND employee_id NOT IN (174, 141);
#成对比较
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (141, 174))
AND employee_id NOT IN (141, 174);
HAVING 子查询
首先执行子查询,然后向主查询中的HAVING子句返回结果
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);
#查询最低工资大于50号部门最低工资的部门id和其最低工资
CASE子查询
SELECT employee_id,
last_name,
(CASE department_id
WHEN
(SELECT department_id
FROM departments
WHERE location_id = 1800)
THEN 'Canada'
ELSE 'USA' END) location
FROM employees;
#显示员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。
非法情况
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
# 多行子查询使用单行比较符,报错
多行子查询
| 操作符 | 作用 |
|---|---|
| IN | 等于列表中任意一个 |
| ANY | 与单行比较操作符一起使用,与子查询返回的某一个值比较 |
| ALL | 与单行比较操作符一起使用,与子查询返回的所有值比较 |
| SOME | 与ANY相同,一般使用ANY |
#返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
select employee_id, last_name,salary
from employees
where salary<ANY(
select salary
from employees
where job_id='IT_PROG'
)
and job_id<>'IT_PROG';
#返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary
select employee_id, last_name,salary
from employees
where salary<ALL(
select salary
from employees
where job_id='IT_PROG'
)
and job_id<>'IT_PROG';
#查询平均工资最低的部门id
#方式一
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(avg_sal)
FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) dept_avg_sal
);
#方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
);
相关子查询
如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询 。 相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。
from型的子查询：子查询是作为from的一部分，子查询要用()引起来，并且要给这个子查询取别 名， 把它当成一张“临时的虚拟的表”来使用
EXISTS NOT EXISTS
关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行
- 如果在子查询中不存在满足条件的行：
- 条件返回 FALSE
- 继续在子查询中查找
- 如果在子查询中存在满足条件的行：
- 不在子查询中继续查找
- 条件返回 TRUE
- NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。
select a from b
where exists (select from where)
相关更新
#使用相关子查询依据一个表中的数据更新另一个表的数据
UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
#在employees中增加一个department_name字段，数据为员工对应的部门名称
# 1）
ALTER TABLE employees
ADD(department_name VARCHAR2(14));
# 2）
UPDATE employees e
SET department_name = (SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);
相关删除
# 使用相关子查询依据一个表中的数据删除另一个表的数据
DELETE FROM table1 alias1
WHERE column operator (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
# 删除表employees中，其与emp_history表皆有的数据
DELETE FROM employees e
WHERE employee_id in
(SELECT employee_id
FROM emp_history
WHERE employee_id = e.employee_id);
WITH AS
mysql8.0后支持,类似视图
#单表
with temp(department_id, salary) as
(select department_id, salary
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000
ORDER BY department_id)
select department_id, salary
FROM temp
GROUP BY department_id
HAVING MAX(salary) > 10000
ORDER BY department_id;
with temp as
(select department_id, salary
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000
ORDER BY department_id)
select department_id, salary
FROM temp
GROUP BY department_id
HAVING MAX(salary) > 10000
ORDER BY department_id;
#多表
with temp1(department_id, salary) as
(select department_id, salary
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000
ORDER BY department_id),
temp2(department_id, salary) as
(select department_id, salary
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000
ORDER BY department_id)
select department_id, salary
FROM temp1,temp2
GROUP BY department_id
HAVING MAX(salary) > 10000
ORDER BY department_id;
约束
约束是表级的强制规定。 可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束。
非空约束NOT NULL
- 限定某个字段,某列值不允许为空
- 默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型
- 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空
- 一个表可以有很多列都分别限定了非空
- 空字符串’'不等于NULL，0也不等于NULL
#设置非空约束
#建表前
CREATE TABLE 表名称(
字段名 数据类型,
字段名 数据类型 NOT NULL,
字段名 数据类型 NOT NULL
);
CREATE TABLE emp(
id INT(10) NOT NULL,
NAME VARCHAR(20) NOT NULL,
sex CHAR NULL
);
#建表后
alter table 表名称 modify 字段名 数据类型 not null;
alter table student modify sname varchar(20) not null;
#删除非空约束
alter table 表名称 modify 字段名 数据类型 NULL;
#去掉not null，相当于修改某个非注解字段，该字段允许为空
alter table 表名称 modify 字段名 数据类型;
#去掉not null，相当于修改某个非注解字段，该字段允许为空
唯一性约束UNIQUE
- 同一个表可以有多个唯一约束
- 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一
- 唯一性约束允许列值为空
- 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同
- MySQL会给唯一约束的列上默认创建一个唯一索引
添加唯一约束
#建表时
create table 表名称(
字段名 数据类型,
字段名 数据类型 unique,
字段名 数据类型 unique key,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] unique key(字段名)
#字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一
)
create table student(
sid int,
sname varchar(20),
tel char(11) unique,
cardid char(18) unique key
);
CREATE TABLE USER(
id INT NOT NULL,
NAME VARCHAR(25),
PASSWORD VARCHAR(16),
-- 使用表级约束语法,表示用户名和密码组合不能重复
CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
);
#建表后
#字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的
#方式1：
alter table 表名称 add unique key(字段列表);
#方式2：
alter table 表名称 modify 字段名 字段类型 unique;
ALTER TABLE USER
ADD UNIQUE(NAME,PASSWORD);
ALTER TABLE USER
ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD);
ALTER TABLE USER
MODIFY NAME VARCHAR(20) UNIQUE;
删除唯一约束
- 添加唯一性约束的列上也会自动创建唯一索引,删除唯一约束只能通过删除唯一索引的方式删除。
- 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。
ALTER TABLE USER
DROP INDEX uk_name_pwd;
可以通过 show index from 表名称; 查看表的索引
PRIMARY KEY 约束
用来唯一标识表中的一行记录,主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值
- 一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建
- 主键约束对应着表中的一列或者多列（复合主键）
- 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复
- MySQL的主键名总是PRIMARY
- 当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了
- 不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的 值，就有可能会破坏数据的完整性。
添加主键约束
#建表前
create table 表名称(
字段名 数据类型 primary key, #列级模式
字段名 数据类型,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] primary key(字段名) #表级模式
);
create table temp(
id int primary key,
name varchar(20)
);
CREATE TABLE emp6(
id INT NOT NULL,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp7_pk PRIMARY KEY(NAME,pwd)
);
#建表后
ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); #字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键
ALTER TABLE emp5 ADD PRIMARY KEY(NAME,pwd);
删除主键约束
删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在.
alter table 表名称
drop primary key;
ALTER TABLE student
DROP PRIMARY KEY;
自增列 AUTO_INCREMENT
- 一个表最多只能有一个自增长列
- 当需要产生唯一标识符或顺序值时，可设置自增长
- 自增长列约束的列必须是键列（主键列，唯一键列）
- 自增约束的列的数据类型必须是整数类型
- 如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值
设置自增约束
#建表前
create table 表名称(
字段名 数据类型 primary key auto_increment,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);
create table 表名称(
字段名 数据类型 default 默认值 ,
字段名 数据类型 unique key auto_increment,
字段名 数据类型 not null default 默认值,,
primary key(字段名)
);
create table employee(
eid int primary key auto_increment,
ename varchar(20)
);
#建表后
alter table 表名称 modify 字段名 数据类型 auto_increment;
alter table employee modify eid int auto_increment;
删除自增约束
alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除
alter table employee modify eid int;
自增示例
CREATE TABLE test1(
id INT PRIMARY KEY AUTO_INCREMENT
);
INSERT INTO test1
VALUES(0),(0),(0),(NULL);
SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
MySQL8.0自增变量的持久化
在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。
DELETE FROM test1 WHERE id = 4;
INSERT INTO test1 VALUES(0);
SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 5 |
+----+
DELETE FROM test1 where id=5;
#重启数据库，重新插入一个空值
INSERT INTO test1 values(0);
SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
在MySQL 8.0版本中，上述测试步骤最后一步的结果如下
SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 6 |
+----+
在MySQL 5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典 内部一个 计数器 来决定的，而该计数器只在 内存中维护 ，并不会持久化到磁盘中。当数据库重启时，该 计数器会被初始化,MySQL 8.0将自增主键的计数器持久化到 重做日志 中。每次计数器发生改变，都会将其写入重做日志 中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。
FOREIGN KEY 约束
限定某个表的某个字段的引用完整性
主表（父表）：被引用的表，被参考的表
从表（子表）：引用别人的表，参考别人的表
员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表
- 从表的外键列，必须引用/参考主表的主键或唯一约束的列
- 在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名，也可以指定外键约束名
- 创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
- 删表时，先删从表（或先删除外键约束），再删除主表
- 当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖 该记录的数据，然后才可以删除主表的数据
- 在“从表”中指定外键约束，并且一个表可以建立多个外键约束
- 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致
- 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束 名。（根据外键查询效率很高）
- 删除外键约束后，必须手动删除对应的索引
- 如果主表主键被子表引用,则不能修改
添加外键约束
#建表前
create table 主表名称(
字段1 数据类型 primary key,
字段2 数据类型
);
create table 从表名称(
字段1 数据类型 primary key,
字段2 数据类型,
[CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样
-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列
create table dept( #主表
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(#从表
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) #在从表中指定外键约束
#emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
);
说明：
（1）主表dept必须先创建成功，然后才能创建emp表，指定外键成功。
（2）删除表时，先删除从表emp，再删除主表dept
ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段) [on update xx][on delete xx];
ALTER TABLE emp1
ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id) REFERENCES dept(dept_id);
约束等级
- Cascade方式 ：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
- Set null方式 ：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
- No action方式 ：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
- Restrict方式 ：同no action， 都是立即检查外键约束
- Set default方式 （在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置 成一个默认的值，但Innodb不能识别
修改外键约束
对于外键约束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式
-- on update cascade on delete set null
create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) on update cascade on delete set null
#把修改操作设置为级联修改等级，把删除操作设置为set null等级
);
#修改主表成功，从表也跟着修改，修改了主表被引用的字段1002为1004，从表的引用字段就跟着修改为1004了
update dept set did = 1004 where did = 1002;
#删除主表的记录成功，从表对应的字段的值被修改为null
delete from dept where did = 1001;
-- on update set null on delete cascade
create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) on update set null on delete cascade
#把修改操作设置为set null等级，把删除操作设置为级联删除等级
);
#修改主表，从表对应的字段设置为null
update dept set did = 1004 where did = 1002;
#删除主表的记录成功，主表的1001行被删除了，从表相应的记录也被删除了
delete from dept where did=1001;
-- on update cascade on delete cascade
create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) on update cascade on delete cascade
#把修改操作设置为级联修改等级，把删除操作也设置为级联删除等级
);
#修改主表，从表对应的字段自动修改
update dept set did = 1004 where did = 1002;
#删除主表的记录成功，主表的1001行被删除了，从表相应的记录也被删除了
delete from dept where did=1001;
删除外键约束
(1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
（2）第二步查看索引名和删除索引。（注意，只能手动删除）
SHOW INDEX FROM 表名称; #查看某个表的索引名
ALTER TABLE 从表名 DROP INDEX 索引名;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';
alter table emp drop foreign key emp_ibfk_1;
show index from emp;
alter table emp drop index deptid;
DEFAULT约束
给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值
增加DEFAULT约束
#建表时
create table 表名称(
字段名 数据类型 primary key,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);
create table 表名称(
字段名 数据类型 default 默认值 ,
字段名 数据类型 not null default 默认值,
字段名 数据类型 not null default 默认值,
primary key(字段名),
unique key(字段名)
);
#默认值约束一般不在唯一键和主键列上加
create table employee(
eid int primary key,
ename varchar(20) not null,
gender char default '男',
tel char(11) not null default '' #默认是空字符串
);
#建表后
alter table 表名称 modify 字段名 数据类型 default 默认值;
#如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被删除了
#同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默认值约束，否则就删除了
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;
alter table employee modify gender char default '男';
#给gender字段增加默认值约束
alter table employee modify tel char(11) default '';
#给tel字段增加默认值约束
删除DEFAULT约束
alter table 表名称 modify 字段名 数据类型 ;#删除默认值约束，也不保留非空约束
alter table 表名称 modify 字段名 数据类型 not null; #删除默认值约束，保留非空约束
alter table employee modify gender char; #删除gender字段默认值约束，如果有非空约束，也一并删除
alter table employee modify tel char(11) not null;#删除tel字段默认值约束，保留非空约束
MySQL8.0 CHECK 约束
检查某个字段的值是否符号xx要求，一般指的是值的范围
MySQL5.7 可以使用check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误或警告
MySQL 8.0中可以使用check约束了。
create table emp11(
eeeid int,
eeeename varchar(5),
eegender char ,
check (eegender ='男' or eegender ='女')
);
insert into emp11 values(1,'张三','男');
CHECK(height>=0 AND height<3)
视图
优缺点
优点:
- 操作简单
- 减少数据冗余
- 数据安全
- 灵活多变
- 分解复杂的查询逻辑
缺点:
实际数据表结构变更后,需要及时对相关的视图进行相应的维护
常见的数据库对象
| 对象 | 描述 |
|---|---|
| 表(TABLE) | 表是存储数据的逻辑单元，以行和列的形式存在，列就是字段，行就是记录 |
| 数据字典 | 就是系统表，存放数据库相关信息的表。系统表的数据通常由数据库系统维护， 程序员通常不应该修改，只可查看 |
| 约束 (CONSTRAINT) | 执行数据校验的规则，用于保证数据完整性的规则 |
| 视图(VIEW) | 一个或者多个数据表里的数据的逻辑显示，视图并不存储数据 |
| 索引(INDEX) | 用于提高查询性能，相当于书的目录 |
| 存储过程 (PROCEDURE) | 用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境 |
| 存储函数 (FUNCTION) | 用于完成一次特定的计算，具有一个返回值 |
| 触发器 (TRIGGER) | 相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理 |
视图是一种虚拟表 ，本身是不具有数据的，占用很少的内存空间，它是 SQL 中的一个重要概念。视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
- 视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然
- 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的SELECT 语句
创建视图
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]
CREATE VIEW 视图名称
AS 查询语句
CREATE VIEW empvu80
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;
SELECT *
FROM empvu80;
CREATE VIEW emp_year_salary (ename,year_salary)
AS
SELECT ename,salary*12*(1+IFNULL(commission_pct,0))
FROM t_employee;
#多表联合视图
CREATE VIEW empview
AS
SELECT employee_id emp_id,last_name NAME,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;
CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS
SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;
利用视图对数据格式化
#想输出员工姓名和对应的部门名，对应格式为emp_name(department_name)
CREATE VIEW emp_depart
AS
SELECT CONCAT(last_name,'(',department_name,')') AS emp_dept
FROM employees e JOIN departments d
WHERE e.department_id = d.department_id
基于视图创建视图
CREATE VIEW emp_dept_ysalary
AS
SELECT emp_dept.ename,dname,year_salary
FROM emp_dept INNER JOIN emp_year_salary
ON emp_dept.ename = emp_year_salary.ename
查看视图
#查看数据库的表对象、视图对象
SHOW TABLES;
#查看视图的结构
DESC / DESCRIBE 视图名称;
#查看视图的属性信息
# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称'\G
#查看视图的详细定义信息
SHOW CREATE VIEW 视图名称;
更新视图
使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的 数据发生变化时，数据表中的数据也会发生变化，反之亦然。
UPDATE emp_tel SET tel = '13789091234' WHERE ename = '孙洪亮';
DELETE FROM emp_tel WHERE ename = '孙洪亮';
要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一 的关系
当视图定义出现如下情况时，视图不支持更新操作：
- 在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；
- 视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；
- 在定义视图的SELECT语句中使用了 JOIN联合查询 ，视图将不支持INSERT和DELETE操作；
- 在定义视图的SELECT语句后的字段列表中使用了 数学表达式 或子查询 ，视图将不支持INSERT，也不支持UPDATE使用了数学表达式、子查询的字段值；
- 在定义视图的SELECT语句后的字段列表中使用 DISTINCT 、 聚合函数 、 GROUP BY 、 HAVING 、 UNION 等，视图将不支持INSERT、UPDATE、DELETE；
- 在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持 INSERT、UPDATE、DELETE；
- 视图定义基于一个不可更新视图
- 常量视图
修改删除视图
# 方式1：使用CREATE OR REPLACE VIEW 子句修改视图
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;
# 方式2：ALTER VIEW
ALTER VIEW 视图名称
AS
查询语句
删除视图只是删除视图的定义，并不会删除基表的数据
DROP VIEW IF EXISTS 视图名称;
DROP VIEW IF EXISTS 视图名称1,视图名称2,视图名称3,...;
函数
单行函数
- 操作数据对象
- 接受参数返回一个结果
- 只对一行进行变换
- 每行返回一个结果
- 可以嵌套
- 参数可以是一列或一个值
数值函数
| 函数 | 作用 |
|---|---|
| ABS(x) | 返回x的绝对值 |
| SIGN(X) | 返回X的符号。正数返回1，负数返回-1，0返回0 |
| PI() | 返回圆周率的值 |
| CEIL(x)，CEILING(x) | 返回大于或等于某个值的最小整数 |
| FLOOR(x) | 返回小于或等于某个值的最大整数 |
| LEAST(e1,e2,e3…) | 返回列表中的最小值 |
| GREATEST(e1,e2,e3…) | 返回列表中的最大值 |
| MOD(x,y) | 返回X除以Y后的余数 |
| RAND() | 返回0~1的随机值 |
| RAND(x) | 返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机 数 |
| ROUND(x) | 返回一个对x的值进行四舍五入后，最接近于X的整数 |
| ROUND(x,y) | 返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位 |
| TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 |
| SQRT(x) | 返回x的平方根。当X的值为负数时，返回NULL |
| RADIANS(x) | 将角度转化为弧度，其中，参数x为角度值 |
| DEGREES(x) | 将弧度转化为角度，其中，参数x为弧度值 |
| SIN(x) | 返回x的正弦值，其中，参数x为弧度值 |
| ASIN(x) | 返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL |
| ATAN2(m,n) | 返回两个参数的反正切值 |
| POW(x,y)，POWER(X,Y) | 返回x的y次方 |
| EXP(X) | 返回e的X次方 |
| LN(X)，LOG(X) | 返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL |
| LOG10(X) /LOG2(X) | 返回以10/2为底的X的对数，当X <= 0 时，返回的结果为NULL |
| BIN(x) | 返回x的二进制编码 |
| HEX(x) | 返回x的十六进制编码 |
| OCT(x) | 返回x的八进制编码 |
| CONV(x,f1,f2) | 返回f1进制数变成f2进制数 |
SELECT
ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5)
FROM DUAL;
SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;
SELECT
ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1)
FROM DUAL;
SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90))
FROM DUAL;
字符串函数
MySQL中，字符串的位置是从1开始的
| 函数 | 作用 |
|---|---|
| ASCII(S) | 返回字符串S中的第一个字符的ASCII码值 |
| CHAR_LENGTH(s) | 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同 |
| LENGTH(s) | 返回字符串s的字节数，和字符集有关 |
| CONCAT(s1,s2,…,sn) | 连接s1,s2,…,sn为一个字符串 |
| CONCAT_WS(x, s1,s2,…,sn) | 同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上x |
| INSERT(str, idx, len, replacestr) | 将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr |
| REPLACE(str, a, b) | 用字符串b替换字符串str中所有出现的字符串a |
| UPPER(s) 或 UCASE(s) | 将字符串s的所有字母转成大写字母 |
| LOWER(s) 或LCASE(s) | 将字符串s的所有字母转成小写字母 |
| LEFT(str,n) | 返回字符串str最左边的n个字符 |
| RIGHT(str,n) | 返回字符串str最右边的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(s1 FROM s) | 去掉字符串s开始与结尾的s1 |
| TRIM(LEADING s1 FROM s) | 去掉字符串s开始处的s1 |
| TRIM(TRAILING s1 FROM s) | 去掉字符串s结尾处的s1 |
| REPEAT(str, n) | 返回str重复n次的结果 |
| SPACE(n) | 返回n个空格 |
| STRCMP(s1,s2) | 比较字符串s1,s2的ASCII码值的大小 |
| SUBSTR(s,index,len) | 返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、 MID(s,n,len)相同 |
| LOCATE(substr,str) | 返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0 |
| ELT(m,s1,s2,…,sn) | 返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如 果m=n，则返回sn |
| FIELD(s,s1,s2,…,sn) | 返回字符串s在字符串列表中第一次出现的位置 |
| FIND_IN_SET(s1,s2) | 返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分 隔的字符串 |
| REVERSE(s) | 返回s反转后的字符串 |
| NULLIF(value1,value2) | 比较两个字符串，如果value1与value2相等，则返回NULL，否则返回 value1 |
日期时间函数
| 函数 | 作用 |
|---|---|
| CURDATE() ，CURRENT_DATE() | 返回当前日期，只包含年、 月、日 |
| CURTIME() ， CURRENT_TIME() | 返回当前时间，只包含时、 分、秒 |
| NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() | 返回当前系统日期和时间 |
| UTC_DATE() | 返回UTC（世界标准时间） 日期 |
| UTC_TIME() | 返回UTC（世界标准时间） 时间 |
| UNIX_TIMESTAMP() | 以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() - >1634348884 |
| UNIX_TIMESTAMP(date) | 将时间date以UNIX时间戳的形式返回 |
| FROM_UNIXTIME(timestamp) | 将UNIX时间戳的时间转换为普通格式的时间 |
| YEAR(date) / MONTH(date) / DAY(date) | 返回具体的日期值 |
| HOUR(time) / MINUTE(time) / SECOND(time) | 返回具体的时间值 |
| MONTHNAME(date) | 返回月份：January，… |
| DAYNAME(date) | 返回星期几：MONDAY，TUESDAY…SUNDAY |
| WEEKDAY(date) | 返回周几，注意，周1是0，周2是1，。。。周日是6 |
| QUARTER(date) | 返回日期对应的季度，范围为1～4 |
| WEEK(date) ， WEEKOFYEAR(date) | 返回一年中的第几周 |
| DAYOFYEAR(date) | 返回日期是一年中的第几天 |
| DAYOFMONTH(date) | 返回日期位于所在月份的第几天 |
| DAYOFWEEK(date) | 返回周几，注意：周日是1，周一是2，。。。周六是 7 |
| EXTRACT(type FROM date) | 返回指定日期中特定的部分，type指定返回的值 |
| TIME_TO_SEC(time) | 将 time 转化为秒并返回结果值。转化的公式为： 小时*3600+分钟 *60+秒 |
| SEC_TO_TIME(seconds) | 将 seconds 描述转化为包含小时、分钟和秒的时间 |
| DATE_ADD(datetime, INTERVAL expr type)， ADDDATE(date,INTERVAL expr type) | 返回与给定日期时间相差INTERVAL时 间段的日期时间 |
| DATE_SUB(date,INTERVAL expr type)， SUBDATE(date,INTERVAL expr type) | 返回与date相差INTERVAL时间间隔的 日期 |
| ADDTIME(time1,time2) | 返回time1加上time2的时间。当time2为一个数字时，代表的是 秒 ，可以为负数 |
| SUBTIME(time1,time2) | 返回time1减去time2后的时间。当time2为一个数字时，代表的 是 秒 ，可以为负数 |
| DATEDIFF(date1,date2) | 返回date1 - date2的日期间隔天数 |
| TIMEDIFF(time1, time2) | 返回time1 - time2的时间间隔 |
| FROM_DAYS(N) | 返回从0000年1月1日起，N天以后的日期 |
| TO_DAYS(date) | 返回日期date距离0000年1月1日的天数 |
| LAST_DAY(date) | 返回date所在月份的最后一天的日期 |
| MAKEDATE(year,n) | 针对给定年份与所在年份中的天数返回一个日期 |
| MAKETIME(hour,minute,second) | 将给定的小时、分钟和秒组合成时间并返回 |
| PERIOD_ADD(time,n) | 返回time加上n后的时间 |
| DATE_FORMAT(date,fmt) | 按照字符串fmt格式化日期date值 |
| TIME_FORMAT(time,fmt) | 按照字符串fmt格式化时间time值 |
| GET_FORMAT(date_type,format_type) | 返回日期字符串的显示格式 |
| STR_TO_DATE(str, fmt) | 按照字符串fmt对str进行解析，解析为一个日期 |
非GET_FORMAT 函数中fmt参数常用的格式符
GET_FORMAT函数中date_type和format_type参数取值如下：
SELECT
CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0
FROM DUAL;
SELECT UNIX_TIMESTAMP(now());
SELECT UNIX_TIMESTAMP(CURDATE());
SELECT UNIX_TIMESTAMP('2011-11-11 11:11:11')
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;
SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;
SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW())
FROM DUAL;
SELECT SEC_TO_TIME(78774);
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL
1 SECOND) AS col2,
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;
SELECT
ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10-
01'),
TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,
10)
FROM DUAL;
#：查询 7 天内的新增用户数有多少
SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7
SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s')
FROM DUAL;
SELECT DATE_FORMAT(NOW(), '%H:%i:%s');
流程控制函数
| 函数 | 作用 |
|---|---|
| IF(value,value1,value2) | 如果value的值为TRUE，返回value1， 否则返回value2 |
| IFNULL(value1, value2) | 如果value1不为NULL，返回value1，否 则返回value2 |
| CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 … [ELSE resultn] END (END值为列名) | 相当于Java的if…else if…else… |
| CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END | 相当于Java的switch…case… |
SELECT IF(1 > 0,'正确','错误')
SELECT IFNULL(null,'Hello Word')
SELECT CASE
WHEN 1 > 0
THEN '1 > 0'
WHEN 2 > 0
THEN '2 > 0'
ELSE '3 > 0'
END
SELECT CASE 1
WHEN 1 THEN '我是1'
WHEN 2 THEN '我是2'
ELSE '你是谁'
SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪'
WHEN salary>=10000 THEN '潜力股'
WHEN salary>=8000 THEN '屌丝'
ELSE '草根' END "描述"
FROM employees;
SELECT oid,`status`, CASE `status`
WHEN 1 THEN '未付款'
WHEN 2 THEN '已付款'
WHEN 3 THEN '已发货'
WHEN 4 THEN '确认收货'
ELSE '无效订单' END
FROM t_order;
SELECT last_name, job_id, salary,
CASE job_id
WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;
加密解密函数
| 函数 | 作用 |
|---|---|
| PASSWORD(str) | 返回字符串str的加密版本，41位长的字符串。加密结果 不可 逆 ，常用于用户的密码加密 |
| MD5(str) | 返回字符串str的md5加密后的值，也是一种加密方式。若参数为 NULL，则会返回NULL |
| SHA(str) | 从原明文密码str计算并返回加密后的密码字符串，当参数为 NULL时，返回NULL。 SHA加密算法比MD5更加安全 |
| ENCODE(value,password_seed) | 返回使用password_seed作为加密密码加密value |
| DECODE(value,password_seed) | 返回使用password_seed作为加密密码解密value |
ENCODE(value,password_seed)函数与DECODE(value,password_seed)函数互为反函数
SELECT PASSWORD('mysql'), PASSWORD(NULL);
SELECT md5('123')
SELECT ENCODE('mysql', 'mysql');
SELECT DECODE(ENCODE('mysql','mysql'),'mysql');
MySQL信息函数
| 函数 | 作用 |
|---|---|
| VERSION() | 返回当前MySQL的版本号 |
| CONNECTION_ID() | 返回当前MySQL服务器的连接数 |
| DATABASE()，SCHEMA() | 返回MySQL命令行当前所在的数据库 |
| USER()，CURRENT_USER()、SYSTEM_USER()， SESSION_USER() | 返回当前连接MySQL的用户名，返回结果格式为 “主机名@用户名” |
| CHARSET(value) | 返回字符串value自变量的字符集 |
| COLLATION(value) | 返回字符串value的比较规则 |
其他函数
| 函数 | 作用 |
|---|---|
| FORMAT(value,n) | 返回对数字value进行格式化后的结果数据。n表示 四舍五入 后保留 到小数点后n位 |
| CONV(value,from,to) | 将value的值进行不同进制之间的转换 |
| INET_ATON(ipvalue) | 将以点分隔的IP地址转化为一个数字 |
| INET_NTOA(value) | 将数字形式的IP地址转化为以点分隔的IP地址 |
| BENCHMARK(n,expr) | 将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费 的时间 |
| CONVERT(value USING char_code) | 将value所使用的字符编码修改为char_code |
SELECT INET_ATON('192.168.1.100');
3232235876
# 以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100
SELECT CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8'));
聚合函数
聚合函数作用于一组数据，并对一组数据返回一个值
| 函数 | 作用 |
|---|---|
| AVG(x) | 均值 |
| SUM(X) | 求和 |
| MAX(X) | 最大值 |
| MIN(X) | 最小值 |
| COUNT() | 计数 |
# 可以对数值型数据使用AVG 和 SUM 函数
SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
#可以对任意数据类型的数据使用 MIN 和 MAX 函数
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;
#COUNT(*) COUNT(1)返回表中记录总数，适用于任意数据类型,会统计值为 NULL 的行
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;
#COUNT(expr) 返回expr不为空的记录总数
#COUNT(distinct expr) 返回expr不为空的去重记录数
SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;
GROUP BY
可以使用GROUP BY子句将表中的数据分成若干组
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;
使用多个列分组
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;
WITH ROLLUP,使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的聚合，即统计记录数量,ROLLUP和ORDER BY是互相排斥 的
SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;
#ROLLUP求出总体均值
HAVING
过滤分组：HAVING子句
- 行已经被分组
- 使用了聚合函数
- 满足HAVING 子句中条件的分组将被显示
- HAVING 不能单独使用，必须要跟GROUP BY一起使用
- 不能在 WHERE 子句中使用聚合函数
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000
ORDER BY department_id;
WHERE和HAVING的对比
- WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件； HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件
- 如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选,在关联查询中，WHERE 比 HAVING 更高效
事务
DCL用来控制数据库访问
- GRANT 授予访问权限
- REMOVE 撤销访问权限
- COMMIT 提交事务处理
- ROLLBACK 事务处理回退
- SAVEPOINT 设置保存点
- LOCK 对数据库特定部分进行锁定
事务的ACID特性
- 原子性:数据库事务不可再分的特性,组成事务的所有查询,要么全部执行,要么全部取消
- 一致性:数据的原则,在事务前后保持一致
- 持久性:当事务提交完成后,影响应该保留下来,不能撤销
- 隔离性:某个事务的操作对其他事务不可见
事务用法
开启事务(start transaction)
执行sql操作
提交/回滚(commit/rollback)
建表时,使用Innodb引擎才支持事务
默认状态下,MySQL自动提交事务,每次执行一个SQL语句,执行成功,则自动提交,不能回滚,如果一组操作需要在一个事务中,那么需要使用start transaction,一旦rollback或commit就结束当前事务,之后操作继续自动提交
如果需要整个会话过程中取消自动提交事务,进行手动提交事务,需要设置
set autocommit =false或set autocommit = 0之后每一句SQL都需要手动commit提交才会真正生效,rollback或commit之前所有操作视为一个事务,之后的操作为另一个事务,还需要手动提交或回滚MySQL中DDL不能回滚,并且部分DDL会造成隐式提交,因此事务中最好不要涉及DDL
# 开启手动处理事务模式
set autocommit =false;
#开始事务
start transaction;
#查看表的数据
select *from stu_info;
#删除数据
delete from stu_info;
#查询该表数据,发现并显示删除后的结果
select *from stu_info;
#回滚
rollback;
#查询该表数据,发现数据恢复
select *from stu_info;
#删除数据
delete from stu_info;
#提交事务
commit;
#查询该表数据,发现删除
select *from stu_info;
#插入数据
insert into stu_info
values(1,2,3);
#保存点1
savepoint point1;
#插入数据
insert into stu_info
values(4,5,6);
#保存点2
savepoint point2;
#查询该表数据
select *from stu_info;
#回滚到保存点1
rollback to point1;
#提交事务
commit;
#清空表
truncate stu_info;
#回滚,对truncate无法回滚
rollback;
#修改表结构
alter table stu_info add tes varchar(20);
#回滚,对修改表结构语句无法回滚
rollback;
数据库隔离级别
对同时运行的多个事务(多线程并发),当这些事务访问数据库中相同的数据时,如果没有采用必备的隔离机制,就会导致各种并发问题(线程安全,共享数据问题)
对两个事务t1,t2
- 脏读: t1读取了一个已经被t2更新但还未提交的字段,之后如果t2回滚,t1读取的内容就是临时且无效的
- 不可重复读:t1读取了一个字段然后t2更新并提交了该字段,之后t1在读一次,值不同
- 幻读: t1从一个表读取了一个字段,然后t2在该表插入删除了一些新的行,之后t1再读取一次同一个表,发现多出,少了几行
事务的隔离性:数据库系统必须具有隔离并发运行各个事务的能力,使他们不受影响,避免各种并发问题一个事务与其他事务的隔离程度称为隔离级别,隔离级别越高,数据一致性越好,并发性越弱
| 隔离级别 | 描述 |
|---|---|
| READ-UNCOMMITTED | 允许事务读取其他未提交的数据,脏读,不可重复读,幻读都有可能出现 |
| READ-COMMITTED | 只允许事务读取其他已经提交的数据,可以避免脏读,但是不可重复读,幻读仍然会出现 |
| REPEATABLE-READ | 确保事务可以多次从一个字段中读取相同的值,类似于在事务开始时对现有数据快照,其他事务对数据的修改,不管事务是否天及哦啊,不影响读取 可以避免脏读和不可重复读 |
| SERIALIZABLE | 确保事务可以从一个表中读取相同的行,在这个事务持续期间,禁止其他事务对该表执行插入,更新,删除操作,所有并发问题都可以避免,但是性能十分低下 |
- MySQL默认为REPEATABLE-READ,没启动一个mysql程序,都会获得一个单独的数据库连接,每个数据库连接都有一个变量
@@tx_isolation,表示当前事务隔离级别
#查看当前的隔离级别
select @@tx_isolation;
#查看全局的隔离级别
select @@global.tx_isolation;
#设置当前MySQL连接的隔离级别
set tx_isolation='REPEATABLE-READ';
#设置当数据库系统的全局的隔离级别
set global tx_isolation='REPEATABLE-READ';
存储过程
简介
存储过程的英文是 Stored Procedure 。它的思想很简单，就是一组经过预先编译的SQL 语句的封装。
执行过程：存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用 存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。
好处：
- 简化操作，提高了sql语句的重用性，存储过程可以一次编译多次使用
- 减少操作过程中的失误，提高效率
- 减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）
- 减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性
- 良好的封装性
坏处:
- 可移植性差
- 调试困难
- 不适合高并发
和视图、函数的对比： 它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。不过它和视图不同，视图是虚拟表 ， 通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以直接操作底层数据表 ，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。 一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于函数，存储过程是没有返回值的。
定义
存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下：
- 没有参数（无参数无返回）
- 仅仅带 IN 类型（有参数无返回）
- 仅仅带 OUT 类型（无参数有返 回）
- 既带 IN 又带 OUT（有参数有返回）
- 带 INOUT（有参数有返回）
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
存储过程体
END
- IN ：当前参数为输入参数，也就是表示入参； 存储过程只是读取这个参数的值。如果没有定义参数种类， 默认就是 IN ，表示输入参数。
- OUT ：当前参数为输出参数，也就是表示出参； 执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
- INOUT ：当前参数既可以为输入参数，也可以为输出参数
- characteristics 表示创建存储过程时指定的对存储过程的约束条件,其取值信息如下
- LANGUAGE SQL ：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
- [NOT] DETERMINISTIC ：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定 的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定 的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。
- { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使 用SQL语句的限制。
- CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
- NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
- READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
- MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
- 默认情况下，系统会指定为CONTAINS SQL。
- SQL SECURITY { DEFINER | INVOKER } ：执行当前存储过程的权限，即指明哪些用户能够执 行当前存储过程。
- DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
- INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
- 如果没有设置相关的值，则MySQL默认指定值为DEFINER。
- COMMENT ‘string’ ：注释信息，可以用来描述存储过程
- 存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END
1. BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
2. DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。
3. SET：赋值语句，用于对变量进行赋值。
4. SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。
DELIMITER 新的结束标记
因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用 DELIMITER改变存储过程的结束符。 比如：“DELIMITER //”语句的作用是将MySQL的结束符设置为//，并以“END //”结束存储过程。
存储过程定 义完毕之后再使用“DELIMITER ;”恢复默认结束符。
DELIMITER也可以指定其他符号作为结束符。
DELIMITER $
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
sql语句1;
sql语句2;
END $
#创建存储过程select_all_data()，查看 emps 表的所有数据
DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
SELECT * FROM emps;
END $
DELIMITER ;
#创建存储过程show_max_salary()，用来查看“emps”表的最高薪资值
DELIMITER //
CREATE PROCEDURE show_max_salary()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '查看最高薪资'
BEGIN
SELECT MAX(salary) FROM emps;
END //
DELIMITER ;
调用存储过程
CALL 存储过程名(实参列表)
#调用in模式的参数：
CALL sp1('值');
#调用out模式的参数：
SET @name;
CALL sp1(@name);
SELECT @name;
#调用inout模式的参数：
SET @name=值;
CALL sp1(@name);
SELECT @name;
#该存储过程返回了指定 s_id=101 的水果商提供的水果种类，
#返回值存储在num变量中，使用SELECT查看，返回结果为3
DELIMITER //
CREATE PROCEDURE CountProc(IN sid INT,OUT num INT)
BEGIN
SELECT COUNT(*) INTO num FROM fruits
WHERE s_id = sid;
END //
DELIMITER ;
#调用存储过程：
CALL CountProc (101, @num);
#查看返回结果：
SELECT @num;
存储函数
LENGTH、SUBSTR、CONCAT等
CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
函数体 #函数体中肯定有 RETURN 语句
END
#调用
SELECT 函数名(实参列表)
#创建存储函数，名称为email_by_name()，参数定义为空，
#该函数查询Abel的email，并返回，数据类型为字符串型。
DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //
DELIMITER ;
SELECT email_by_name();
- 参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参 数。
- RETURNS type 语句表示函数返回数据的类型； RETURNS子句只能对FUNCTION做指定，对函数而言这是 强制 的。它用来指定函数的返回类型，而且函 数体必须包含一个 RETURN value 语句。
- characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同，这里不再赘述。
- 函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略 BEGIN…END。
存储过程和函数的查看、修改、删除
#使用SHOW CREATE语句查看存储过程和函数的创建信息
SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名
SHOW CREATE FUNCTION test_db.CountProc
#使用SHOW STATUS语句查看存储过程和函数的状态信息
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
-- [LIKE 'pattern']：匹配存储过程或函数的名称，可以省略。
-- 当省略不写时，会列出MySQL数据库中存在的所有存储过程或函数的信息。
SHOW PROCEDURE STATUS LIKE 'SELECT%'
#从information_schema.Routines表中查看存储过程和函数的信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名'
[AND ROUTINE_TYPE {'PROCEDURE|FUNCTION'}];
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='count_by_id' AND ROUTINE_TYPE = 'FUNCTION'
ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]
#修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性
ALTER PROCEDURE CountProc
MODIFIES SQL DATA
SQL SECURITY INVOKER ;
# 删除存储过程和函数，可以使用DROP语句
DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名
DROP PROCEDURE CountProc;
DROP FUNCTION CountProc;
触发器
有 2 个或者多个相互关联的表，如 商品信息 和 库存信息 分 别存放在 2 个不同的数据表中，我们在添加一条新商品记录的时候，为了保证数据的完整性，必须同时 在库存表中添加一条库存记录。两个关联的操作步骤写到程序里面，而且要用 事务 包裹起来，确保这两个操作成为一个原子操作 ，要么全部执行，要么全部不执行。要是遇到特殊情况，可能还需要对数据进行手 动维护，这样就很 容易忘记其中的一步 ，导致数据缺失。
可以创建一个触发器，让商品信息数据的插入操作自动触发库存数据的插入操作。
创建触发器
CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;
- 表名 ：表示触发器监控的对象。
- BEFORE|AFTER ：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。
- INSERT|UPDATE|DELETE ：表示触发的事件。 INSERT 表示插入记录时触发； UPDATE 表示更新记录时触发； DELETE 表示删除记录时触发。
- 触发器执行的语句块 ：可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块
CREATE TABLE test_trigger (
id INT PRIMARY KEY AUTO_INCREMENT,
t_note VARCHAR(30)
);
CREATE TABLE test_trigger_log (
id INT PRIMARY KEY AUTO_INCREMENT,
t_log VARCHAR(30)
);
#创建名称为before_insert的触发器，向test_trigger数据表插入数据之前
#向test_trigger_log数据表中插入before_insert的日志信息
DELIMITER //
CREATE TRIGGER before_insert
BEFORE INSERT ON test_trigger
FOR EACH ROW
BEGIN
INSERT INTO test_trigger_log (t_log)
VALUES('before_insert');
END //
DELIMITER ;
查看删除触发器
#查看当前数据库的所有触发器的定义
SHOW TRIGGERS
#查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名
#从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息
SELECT * FROM information_schema.TRIGGERS;
#删除触发器
DROP TRIGGER IF EXISTS 触发器名称
优缺点
优点:
- 保证数据完整性
- 记录操作日志
- 合法检查
缺点:
- 可读性差
- 相关数据的变更，可能会导致触发器出错
如果在子表中定义了外键约束，并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子表的UPDATE和DELETE语句定义的触发器并不会被激活
变量
在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。 在 MySQL 数据库中，变量分为系统变量以及用户自定义变量 。
系统变量
变量由系统定义，不是用户定义，属于服务器层面。系统变量分为全局系统变量(需要添加 global 关键字)以及会话系统变量（需要添加 session 关键 字），有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为local变量。如果不写，默认 会话级别。静态变量（在 MySQL 服务实例运行期间它们的值不能使用 set 动态修改）属于特殊的全局系统变量。
- 全局系统变量针对于所有会话（连接）有效，但不能跨重启
- 会话系统变量仅针对于当前会话（连接）有效。会话期间，当前会话对某个会话系统变量值的修改，不会影响其他会话同一个会话系统变量的值
- 会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改
在MySQL中有些系统变量只能是全局的，例如max_connections 用于限制服务器的最大连接数；有些系统变量作用域既可以是全局又可以是会话，例如character_set_client 用于设置客户端的字符集；有些系统变量的作用域只能是当前会话，例如pseudo_thread_id 用于标记当前会话的 MySQL连接ID
#查看所有全局变量
SHOW GLOBAL VARIABLES;
#查看所有会话变量
SHOW SESSION VARIABLES;
SHOW VARIABLES;
#查看满足条件的部分系统变量。
SHOW GLOBAL VARIABLES LIKE '%标识符%';
#查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%标识符%';
SHOW GLOBAL VARIABLES LIKE 'admin_%';
#查看指定系统变量
#作为 MySQL 编码规范，MySQL 中的系统变量以 两个“@” 开头，其中“@@global”仅用于标记全局系统变量，“@@session”仅用于标记会话系统变量。“@@”首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量
#查看指定的系统变量的值
SELECT @@global.变量名;
#查看指定的会话变量的值
SELECT @@session.变量名;
#或者
SELECT @@变量名;
#修改系统变量的值
#为某个系统变量赋值
#方式1：
SET @@global.变量名=变量值;
#方式2：
SET GLOBAL 变量名=变量值;
#为某个会话变量赋值
#方式1：
SET @@session.变量名=变量值;
#方式2：
SET SESSION 变量名=变量值;
SELECT @@global.autocommit;
SET GLOBAL autocommit=0;
SELECT @@session.tx_isolation;
SET @@session.tx_isolation='read-uncommitted';
SET GLOBAL max_connections = 1000;
SELECT @@global.max_connections;
用户变量
用户变量是用户自己定义的，作为 MySQL 编码规范，MySQL 中的用户变量以 一个“@” 开头。根据作用 范围不同
- 会话用户变量：作用域和会话变量一样，只对当前连接会话有效。
- 局部变量：只在 BEGIN 和 END 语句块中有效。局部变量只能在存储过程和函数中使用。
-- 会话变量的定义
#方式1：“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;
#方式2：“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];
-- 查看用户变量的值 （查看、比较、运算等）
SELECT @用户变量
SET @a = 1;
SELECT @a;
SELECT @num := COUNT(*) FROM employees;
SELECT @num;
SELECT AVG(salary) INTO @avgsalary FROM employees;
SELECT @avgsalary;
SELECT @big; #查看某个未声明的变量时，将得到NULL值
-- 定义：可以使用 DECLARE 语句定义一个局部变量
-- 作用域：仅仅在定义它的 BEGIN ... END 中有效
-- 位置：只能放在 BEGIN ... END 中，而且只能放在第一句
BEGIN
#声明局部变量
DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];
#为局部变量赋值
SET 变量名1 = 值;
SELECT 值 INTO 变量名2 [FROM 子句];
#查看局部变量的值
SELECT 变量1,变量2,变量3;
END
#定义变量
DECLARE 变量名 类型 [default 值]; # 如果没有DEFAULT子句，初始值为NULL
DECLARE myparam INT DEFAULT 100;
#变量赋值
SET 变量名=值;
SET 变量名:=值;
SELECT 字段名或表达式 INTO 变量名 FROM 表;
#使用变量
SELECT 局部变量名;
#声明局部变量，并分别赋值为employees表中employee_id为102的last_name和salary
DELIMITER //
CREATE PROCEDURE set_value()
BEGIN
DECLARE emp_name VARCHAR(25);
DECLARE sal DOUBLE(10,2);
SELECT last_name,salary INTO emp_name,sal
FROM employees
WHERE employee_id = 102;
SELECT emp_name,sal;
END //
DELIMITER ;
定义条件与处理程序
定义条件是事先定义程序执行过程中可能遇到的问题， 处理程序定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行.定义条件和处理程序在存储过程、存储函数中都是支持的。
定义条件
定义条件就是给MySQL中的错误码命名。它将一个错误名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的DECLARE HANDLER 语句中
#定义条件使用DECLARE语句
DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）
#使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
#使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';
- MySQL_error_code 和 sqlstate_value 都可以表示MySQL的错误。
- MySQL_error_code是数值类型错误代码。
- sqlstate_value是长度为5的字符串类型错误代码。
- 在ERROR 1418 (HY000)中，1418是MySQL_error_code，'HY000’是sqlstate_value。
定义处理程序
可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句
- 处理方式：处理方式有3个取值：CONTINUE、EXIT、UNDO。
- CONTINUE ：表示遇到错误不处理，继续执行。
- EXIT ：表示遇到错误马上退出。
- UNDO ：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。
- 错误类型（即条件）可以有如下取值：
- SQLSTATE ‘字符串错误码’ ：表示长度为5的sqlstate_value类型的错误代码；
- MySQL_error_code ：匹配数值类型错误代码；
- 错误名称 ：表示DECLARE … CONDITION定义的错误条件名称。
- SQLWARNING ：匹配所有以01开头的SQLSTATE错误代码；
- NOT FOUND ：匹配所有以02开头的SQLSTATE错误代码；
- SQLEXCEPTION ：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；
- 处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是 像“ SET 变量 = 值 ”这样的简单语句，也可以是使用 BEGIN … END 编写的复合语句。
#方法1：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
#方法2：捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
#方法3：先定义条件，再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';
#方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
#方法5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
#方法6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';
-- 在存储过程中，定义处理程序，捕获sqlstate_value值，
-- 当遇到MySQL_error_code值为1048时，执行CONTINUE操作
-- 并且将@proc_value的值设置为-1
DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
#定义处理程序
DECLARE CONTINUE HANDLER FOR 1048 SET @proc_value = -1;
SET @x = 1
UPDATE employees SET email = NULL WHERE last_name = 'Abel';
SET @x = 2;
UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
SET @x = 3;
END //
DELIMITER ;
# 调用过程：
CALL UpdateDataWithCondition();
流程控制
分支结构之 IF
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF
# 这里“[]”中的内容是可选的
IF val IS NULL
THEN SELECT 'val is null';
ELSE SELECT 'val is not null';
END IF;
分支结构之 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后面不需要）
#使用CASE流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于0
CASE
WHEN val IS NULL THEN SELECT 'val is null';
WHEN val < 0 THEN SELECT 'val is less than 0';
WHEN val > 0 THEN SELECT 'val is greater than 0';
ELSE SELECT 'val is 0';
END CASE;
循环结构之LOOP
LOOP循环语句用来重复执行某些语句。LOOP内的语句一直重复执行直到循环被退出(使用LEAVE子句),跳出循环过程。
[loop_label:] LOOP
循环执行的语句
END LOOP [loop_label]
#使用LOOP语句进行循环操作，id值小于10时将重复执行循环过程
DECLARE id INT DEFAULT 0;
add_loop:LOOP
SET id = id +1;
IF id >= 10 THEN LEAVE add_loop;
END IF;
END LOOP add_loop;
循环结构之WHILE
WHILE语句创建一个带条件判断的循环过程。WHILE在执行语句执行时，先对指定的表达式进行判断，如 果为真，就执行循环内的语句，否则退出循环
[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];
#i值小于10时，将重复执行循环
DELIMITER //
CREATE PROCEDURE test_while()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 10 DO
SET i = i + 1;
END WHILE;
SELECT i;
END //
DELIMITER ;
#调用
CALL test_while();
循环结构之REPEAT
REPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是，REPEAT 循环首先会执行一次循 环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。
[repeat_label:] REPEAT
循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]
跳转语句之LEAVE语句
LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出 程序体的操作(break)
LEAVE 标记名
DELIMITER //
CREATE PROCEDURE leave_begin(IN num INT)
begin_label: BEGIN
IF num<=0
THEN LEAVE begin_label;
ELSEIF num=1
THEN SELECT AVG(salary) FROM employees;
ELSEIF num=2
THEN SELECT MIN(salary) FROM employees;
ELSE
SELECT MAX(salary) FROM employees;
END IF;
SELECT COUNT(*) FROM employees;
END //
DELIMITER ;
跳转语句之ITERATE语句
ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环(continue)
ITERATE label
DELIMITER //
CREATE PROCEDURE test_iterate()
BEGIN
DECLARE num INT DEFAULT 0;
my_loop:LOOP
SET num = num + 1;
IF num < 10
THEN ITERATE my_loop;
ELSEIF num > 15
THEN LEAVE my_loop;
END IF;
SELECT '尚硅谷：让天下没有难学的技术';
END LOOP my_loop;
END //
DELIMITER ;
游标
游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用 ，我们可以通过操作游标来对数据行进行操作,逐条读取 结果集中的数据
#声明游标
DECLARE cursor_name CURSOR FOR select_statement;#返回一个用于创建游标的结果集
DECLARE cur_emp CURSOR FOR
SELECT employee_id,salary FROM employees;
#打开游标
-- 定义好游标之后，如果想要使用游标，必须先打开游标。
-- 打开游标的时候 SELECT 语句的查询结果集就会送到游标工作区
-- 为后面游标的逐条读取 结果集中的记录做准备
OPEN cursor_name
OPEN cur_emp ;
#使用游标（从游标中取得数据）
FETCH cursor_name INTO var_name [, var_name] ...
FETCH cur_emp INTO emp_id, emp_sal ;
#关闭游标
-- 如果不及时关闭，游标会一直保持到存储过程结束
CLOSE cursor_name
CLOSE cur_emp;
授权
