mysql数据库基础语句使用方法

1数据库

​

1.1数据库

数据库（Database，DB）是按照数据结构来组织、存储和管理数据的仓库。
每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。

数据库作用：存储数据、管理数据

关系型数据库（Structured Query Language，SQL）：通过表和表之间，行和列之间的关系进行的数据的存储，MySQL、Oracle、Sql Server都属于关系型数据库。关系型数据库通过外键关联来联系表与表之间的关系。

关系型数据库管理系统（RDBMS）是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：
1.数据以表格的形式出现
2.每行为各种记录名称
3.每列为记录名称所对应的数据域
4.许多的行和列组成一张表单
5.若干的表单组成database
​​​​​​​​

1.2术语

数据库：数据库是一些关联表的集合。
数据表：表示数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列：一列（数据元素）包含了相同类型的数据。
行：一行（元组或者记录）是一组相关的数据。
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键，可以使用主键查询数据。
外键：外键用于关联两个表。如果公共关键字在一个关系中是主关键字，那么这个关键字被称为另一个关系的外键。以另一个关系的外键作为主关键字的表被称为主表，具有此外键的表被称为此主表的外表。，实际操作中，将一个表的值放进第二个表进行关联，所使用的值是第一个表的主键值（在必要时可包括复合主键值）。此时，第二个表中保存这些值的属性被称为外键。
（外键的作用：保持数据的一致性，完整性，主要目的是控制存储在外键表中的数据约束，使两张表形成关联，外键只能引用外表中的列的值或者空值。）
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据表中一列或者多列的值进行排序的一组结构，类似于书籍的目录。
参照完整性：参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

2增删查改

2.1创建数据库

使用 create 命令创建数据库，语法如下:

CREATE DATABASE 数据库名;
1

如果担心数据库已经存在，执行CREATE DATABASE 将导致错误。为了避免这种情况，可以在 CREATE DATABASE 语句中添加 IF NOT EXISTS 子句：

CREATE DATABASE [IF NOT EXISTS] 数据库名;
1

[]里面的内容可以依据条件增加或者去除，如IF EXISTS 是一个可选的子句，表示如果数据库不存在才执行创建操作，避免因为数据库存在而引发错误。

如果在创建数据库时希望指定一些选项，可以使用 CREATE DATABASE 语句的其他参数，例如，可以指定字符集和排序规则：

CREATE DATABASE [IF NOT EXISTS] database_name
  [CHARACTER SET charset_name]
  [COLLATE collation_name];
1
2
3

例如：

CREATE DATABASE IF NOT EXISTS Students
	CHARSETR SET utf8mb4
	COLLATE utf8mb4_general_ci;
1
2
3

2.2删除数据库

drop 命令删除数据库：

DROP DATABASE <database_name>;        -- 直接删除数据库，不检查是否存在
或
DROP DATABASE [IF EXISTS] <database_name>;
1
2
3

注意： 在执行删除数据库操作前，请确保确实想要删除数据库及其所有数据，因为该操作是不可逆的。为了避免误操作，通常建议在执行删除之前备份数据库。

2.3选择数据库

使用 USE 语句选择要使用的数据库：

USE database_name;
1

2.4数据类型（字段）

数据库的列类型：
1.数值
mysql数据类型包括（INTEGER、SMALLINT、DECIMAL 和 NUMERIC），以及近似数值数据类型（FLOAT、REAL 和 DOUBLE PRECISION）。
关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词，BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

2.时间日期
TIMESTAMP类型有专有的自动更新特性，将在后面描述。

3.字符串

char(n) 和 varchar(n) 括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

char(n) 和 varchar(n) 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
3.枚举与集合类型
ENUM: 枚举类型，用于存储单一值，可以选择一个预定义的集合。
SET: 集合类型，用于存储多个值，可以选择多个预定义的集合。

2.5数据库的字段类型

unsigned：无符号的；声明了该列不能为负数

zerofill：0填充的；不足位数的用0来填充 , 如int(3),5则为005

Auto_InCrement：通常理解为自增，自动在上一条记录的基础上默认+1；通常用来设计唯一的主键，必须是整数类型；
可定义起始值和步长：
当前表设置步长(AUTO_INCREMENT=100) ；只影响当前表
SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)

NULL 和 NOT NULL：默认为NULL , 即没有插入该列的数值；如果设置为NOT NULL , 则该列必须有值

DEFAULT：默认的；用于设置默认值；例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值

COMMENT：注释

每一个表都必须有以下5个字段：
id：主键
version：乐观锁
is_delete：伪删除
gmt_create：创建时间
gmt_update：修改时间

2.6 创建数据表

创建数据表需要的信息：表名、表字段名、定义每个表字段的数据类型
创建数据表的通用语法：

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
     ...
    -- 完整性约束
    PRIAMRY KEY('column')
    FOREIGN KEY(本表列名) REFERENCES 被参照表(被参照列名)   
   
);
1
2
3
4
5
6
7
8
9

或者

CREATE TABLE IF NOT EXISTS `student`(
	'字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    ......
    '字段名' 列类型 [属性] [索引] [注释]
)[表的类型][字符集设置][注释]
1
2
3
4
5
6

table_name 是要创建的表的名称。
column1, column2, … 是表中的列名。
datatype 是每个列的数据类型。
实例：

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4)	NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
)CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
1
2
3
4
5
6
7
8
9
10

注意：
表名和字段尽量使用``括起来
AUTO_INCREMENT 代表自增
所有的语句后面加逗号，最后一个不加
字符串使用单引号括起来
主键的声明一般放在最后，便于查看
以上代码创建一个使用 utf8mb4 字符集和 utf8mb4_general_ci 排序规则的表。

MySQL数据表以文件方式存储在磁盘中，其中.sql表示查询文件，.frm表示表结构定义文件，.MYD表示数据文件，.MYI表示索引文件。

2.7修改数据库

修改表名：

ALTER TABLE 旧表名 RENAME AS 新表名

ALTER TABLE students RENAME AS student;

1
2
3
4

添加字段：

ALTER TABLE 表名 ADD字段名 列属性[属性]

ALTER TABLE student ADD age INT(11) ; -- 增加字段
1
2
3

修改字段：

ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
-- 后面一定要跟新的属性

ALTER TABLE student MODIFY age VARCHAR(11) ;       -- 修改字段约束
ALTER TABLE student CHANGE age age1 INT(11) DEFAULT NULL COMMENT '年龄';          -- 字段重命名
1
2
3
4
5
6

删除字段：

ALTER TABLE 表名 DROP 字段名

ALTER TABLE student DROP age1;           -- 删除字段
1
2
3

2.8删除数据表

删除数据表时要非常小心，因为执行删除命令胡所有数据都会消失。
如果该表和其他表后外键约束。可能需要先删除外键约束，或者确保依赖关系被处理好。
删除数据表的通用语法：

DROP TABLE table_name;     -- 直接删除表，不检查是否存在
-- 或者
DROP TABLE [IF EXISTS] table_name;  -- 会检查是否存在，如果存在则删除
-- 例如
DROP TABLE IF EXISTS student;
1
2
3
4
5

2.9插入数据

插入数据的注意点：
1.字段和字段之间使用英文逗号隔开；
2.字段是可以省略的，但是值必须完整且一一对应；
3.可以同时插入多条数据，VALUES后面的值需要使用逗号隔开。

INSERT INTO语句插入数据通用语法：

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
1

column1, column2, column3, … 是表中的列名，value1, value2, value3, … 是要插入的具体数值。如果数据是字符型，必须使用单引号 ’ 或者双引号 "，如： ‘value1’, “value1”。

举例：

-- 插入一条字段
INSERT INTO student (`id`, `name`, `pwd`, `sex`,  `birthday`, `address`, `email`, `age`) 
	VALUES(0001, 'wcq', '654321', '男', '20000504', '北京', '111111111@11.com', 24)
	
-- 插入多条字段
INSERT INTO student (`id`, `name`, `pwd`, `sex`,  `birthday`, `address`, `email`, `age`) 
	VALUES(0002, 'wcw', '654321', '男', '20000505', '北京', '111131111@11.com', 32),
		  (0003, 'syss', '654300', '女', '20000510', '河北', '111131123@11.com', 23)

-- 省略列名，（如果要插入所有列的数据的话）
INSERT INTO student 
VALUES(0004, 'qqq', 'yuiopy', '女', '20100101', '陕西省西安市xxx小区', 'qqqjdlm@163.com', 14 )

-- NULL 是用于自增长列的占位符，表示系统将为 id 列生成一个唯一的值。
INSERT INTO student 
VALUES(NULL, 'qww', '1weqww', '女', '20000101', '陕西省西安市漠北二小区', '123456@163.com', 24 )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2.10 更新数据

更新Mysql中的数据，使用UPDATE命令操作。
注意点：
1.可以同时更新一个或多个字段；
2.可以在where子句中指定任何条件；
3.可以在一个单独表中同时更新数据。

更新数据通用语法：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
1
2
3

column1, column2, … 是要更新的列的名称，WHERE condition 是一个可选的子句，用于指定更新的行。如果省略 WHERE 子句，将更新表中的所有行。

实例：

-- 修改学生名字，指定条件
UPDATE `student` SET `name` = 'sys' where id = 3;
 
-- 不指定条件的情况下，会修改所有表
UPDATE `student` SET `sex` = '女';

-- 修改多个属性
UPDATE `student` SET `pwd` = 'sys..11', `birthday` = 20001104 where `name` = 'sys'

-- 通过多个条件定位数据
UPDATE `student` SET `sex` = '男' where `id` = 1 or `id` = 2 or `id` = 5
1
2
3
4
5
6
7
8
9
10
11

2.10.1 where子句

WHERE 子句用于在 MySQL 中过滤查询结果，只返回满足特定条件的行。
WHERE的通用句法：

SELECT column1, column2, ...  --  SELECT查询语句
FROM table_name
WHERE condition;
1
2
3

注意：
1.查询语句中可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
2.可以在 WHERE 子句中指定任何条件。
3.可以使用 AND 或者 OR 指定一个或多个条件。
4.WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
5.WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

以下操作符列表可用作WHERE子句中：

实例：

SELECT * from student Where `age` >= 18
1

2.11 删除数据

普通用户登录mysql服务器需要特定权限创建或者删除数据库，root用户则拥有最高权限。
DELETE FROM命令删除数据的通用语法：

-- 删除符合条件的行
DELETE FROM table_name
WHERE condition;    

-- 删除所有行
DELETE FROM orders;
1
2
3
4
5
6

实例

DELETE from student where `name` = 'qww'
1

注意：
DELETE语句不会影响自增；
DELETE是数据操作语言（DML - Data Manipulation Language），操作时原数据会被放到 rollback segment中，可以被回滚；怎么通过查询语句回滚呢，暂时还没有研究清楚。

2.12 查询数据

使用SELETE查询数据的通用语句：

SELECT column1, column2, ...
FROM table_name
[left | right | inner join table_name2]  -- 联合查询
[WHERE condition]
[GROUP BY ...]  -- 指定结果按照哪几个字段来分组
[HAVING]  -- 过滤分组的记录必须满足的次要条件
[ORDER BY column_name [ASC | DESC]]   -- 指定结果集的排序顺序，默认是升序（ASC）
[LIMIT number];     -- 限制返回的行数
1
2
3
4
5
6
7
8

查询前需要先创建数据：

-- 创建学生数据库

CREATE TABLE IF NOT EXISTS `students`(
	`id` INT(4)	NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
)CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

ALTER TABLE students RENAME AS student;
ALTER TABLE student CHANGE `id` `StudentNo` INT(4)	NOT NULL AUTO_INCREMENT COMMENT '学号';

ALTER TABLE student ADD age INT(11) ; -- 增加字段
ALTER TABLE student MODIFY age VARCHAR(11) ;       -- 修改字段约束
ALTER TABLE student CHANGE age age1 INT(11) DEFAULT NULL COMMENT '年龄';          -- 字段重命名
ALTER TABLE student DROP age1;           -- 删除字段
ALTER TABLE student ADD age INT(11) DEFAULT NULL COMMENT '年龄'; 

DROP TABLE IF EXISTS student;

INSERT INTO student (`id`, `name`, `pwd`, `sex`,  `birthday`, `address`, `email`, `age`) 
	VALUES(0001, 'wcq', '654321', '男', '20000504', '北京', '111111111@11.com', 24);

INSERT INTO student (`id`, `name`, `pwd`, `sex`,  `birthday`, `address`, `email`, `age`) 
	VALUES(0002, 'wcw', '654321', '男', '20000505', '北京', '111131111@11.com', 32),
				(0003, 'syss', '654300', '女', '20000510', '河北', '111131123@11.com', 23);
	
INSERT INTO student 
VALUES(0004, 'qqq', 'yuiopy', '女', '20100101', '陕西省西安市xxx小区', 'qqqjdlm@163.com', 14 );

INSERT INTO student 
VALUES(NULL, 'qww', '1weqww', '女', '20000101', '陕西省西安市漠北二小区', '123456@163.com', 24 );

UPDATE `student` SET `name` = 'sys' where id = 3;

UPDATE `student` SET `sex` = '女';

UPDATE `student` SET `pwd` = 'sys..11', `birthday` = 20001104 where `name` = 'sys'

UPDATE `student` SET `sex` = '男' where `id` = 1 or `id` = 2 or `id` = 5

SELECT * from student Where `age` >= 18

DELETE from student where `name` = 'wcq'



-- 创建学校数据库
-- CREATE DATABASE IF NOT EXISTS `school`;

-- 用school数据库
-- USE `school`;

-- 创建年级表
CREATE TABLE `grade`(
	`GradeId` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
	`GradeName` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`GradeId`)
	)CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
	
-- 给grade表插入数据
INSERT INTO `grade`
	VALUES(7,'初一'),
				(8,'初二'),
				(9,'初三');
	
-- 创建成绩result表
CREATE TABLE IF NOT EXISTS `result`(
	`StudentNo` INT(4) NOT NULL COMMENT '学号',
	`SubjectNo` INT(4) NOT NULL COMMENT '考试编号',
	`ExamDate` DATETIME  NOT NULL COMMENT '考试日期',
	`StudentResult` INT(4) NOT NULL COMMENT '考试成绩',
	KEY `StudentNo` (`StudentNo`)
)CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;


-- 给result表插入数据
INSERT INTO `result`
	VALUES (1, 1, 20240718, 88),
				 (1, 2, 20240719, 72),
				 (3, 1, 20240718, 91),
				 (3, 2, 20240719, 98);
				 
-- 创建科目表
CREATE TABLE `subject`(
	`SubjectNo` INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
	`SubjectName` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
	`ClassHour` INT(4) DEFAULT NULL COMMENT '学时',
	`GradeId` INT(4) DEFAULT NULL COMMENT '年级编号',
	PRIMARY KEY (`SubjectNo`)
)CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- -- 给科目表subject插入数据
INSERT INTO `subject`(`SubjectNo`,`SubjectName`,`ClassHour`,`GradeID`) 
VALUES(1,'物理','96',7),
			(2,'数学','112',7),
			(3,'英语',64,9);	
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101

2.12.1 基础查询

基础查询语法：

-- 查询多个字段
select 字段1, 字段2, ... , 字段n from 表名;
SELECT `name`, `birthday` FROM student;

-- 查询全部字段
select * from 表名;
SELECT * from result;

-- 去除重复记录
select distinct 字段列表 from 表名;
SELECT DISTINCT name from student;

-- 起别名操作(可给字段起别名，也可给表起别名)
select 字段名 as 字段别名 from 表名 as 表别名;
SELECT `StudentNo` as 学号, `SubjectNo` as 考试编号, `ExamDate` as 考试日期,`StudentResult` as 学生成绩 from `result` as 考试结果;

-- 查询系统版本
SELECT VERSION()

-- 计算
SELECT 计算公式 as 计算结果;
SELECT 1+2 as a;

-- 查询自增步长(变量)
SELECT @@auto_increment_increment;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

2.12.2 条件查询

条件查询需要配合运算符使用：

where查询基本语法：

select 查询列表 from 表名 where 筛选条件;

-- 查询考试成绩在95-100的同学
SELECT * from result WHERE `StudentResult`>=95 AND `StudentResult`<=100;   -- AND
SELECT * from result WHERE `StudentResult`BETWEEN 95 AND 100;         -- BETWEEN AND     
SELECT * from result WHERE `StudentResult`>=95 && `StudentResult`<=100;    -- &&

-- 查询学号不为1的学生
SELECT * from student WHERE `StudentNo` != 1;    -- ！=
SELECT * from student WHERE NOT `StudentNo` = 1;    -- NOT

-- 查询姓名带q的学生信息
SELECT * from student where `name` LIKE '%q%';
-- 查询姓名带q的学生信息
SELECT * from student where `name` LIKE '%q%';
-- 查询姓名第二个字为q的学生信息
SELECT * from student where `name` LIKE '_q%';
-- 查询姓名倒数第三个字为q的学生信息
SELECT * from student where `name` LIKE '%q__';
-- 查询姓名最后一个字为q的学生信息
SELECT * from student where `name` LIKE '%q';

-- 查询住址为北京和河北的学生信息
SELECT * from student where `address` IN('北京', '河北');   -- IN
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

2.12.3 分组查询

GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上可以使用 COUNT, SUM, AVG,等函数。
GROUP BY 语句是 SQL 查询中用于汇总和分析数据的重要工具，尤其在处理大量数据时，它能够提供有用的汇总信息。
GROUP BY 语句的通用语法：

SELECT column1, aggregate_function(column2)    -- column1：指定分组的列aggregate_function(column2)对分组后的每个组执行聚合函数。
FROM table_name
[WHERE condition]
GROUP BY column1;
[HAVING 分组后的条件过滤]
[ORDER BY 排序列表]
1
2
3
4
5
6

区分：

实例：

-- 查询不同科目的平均分，最高分，最低分且平均分大于80，最后降序排序
SELECT SubjectName, AVG(`StudentResult`),MAX(`StudentResult`),MIN(`StudentResult`)
FROM `result` AS 成绩
INNER JOIN `subject` AS 科目          -- 内连接查询
ON 成绩.SubjectNo = 科目.SubjectNo
GROUP BY 成绩.SubjectNo
HAVING AVG(StudentResult) > 80
ORDER BY AVG(`StudentResult`) DESC;     -- 降序排序
1
2
3
4
5
6
7
8

2.12.4 连接查询

从多个数据表中读取数据：JOIN连接查询

根据上图，使用较多的JOIN链接按照功能分大致分为两类：
内连接：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
  外连接：
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

INNER JOIN内连接的通用语法：

-- 通用语法

-- 隐式内连接查询
SELECT column1, column2, ...
FROM table1, table2...
where 条件l

-- 显示内连接查询
SELECT column1, column2, ...
FROM table1
INNER JOIN table2 
ON table1.column_name = table2.column_name;   -- on后面为连接条件
1
2
3
4
5
6
7
8
9
10
11
12

内连接：

-- 查询学生所在年级（学号，学生姓名，年级名称）
SELECT `StudentNo`, `name`, `GradeName`
FROM student
INNER JOIN grade
ON student.GradeId = grade.GradeId;
1
2
3
4
5

多表连接查询：

-- 查询学生考试成绩
SELECT `student`.`StudentNo`, `name`, `SubjectName`, `StudentResult`
	FROM `student`
	INNER JOIN `result`
	ON `student`.`StudentNo` = `result`.`StudentNo`
	INNER JOIN `subject`
	ON `result`.SubjectNo = `subject`.SubjectNo
	ORDER BY StudentNo ASC;
1
2
3
4
5
6
7
8

自连接
这是一个父类子类的表，自己的表和自己的表连接、

-- （自连接）创建一个表
CREATE TABLE `course`(
	`CourseId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程id',
	`pid` INT(10) NOT NULL COMMENT '父课程id',
	`CourseName` VARCHAR(50) NOT NULL COMMENT '课程名',
	PRIMARY KEY (`CourseId`)
)CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 插入数据

INSERT INTO `course`
	VALUES(2, 1, '信息技术'),
				(3, 1, '软件开发'),
				(4, 3, '数据库'),
				(5, 1, '美术设计'),
				(6, 3, 'web开发'),
				(7, 5, 'ps技术'),
				(8, 2, '办公信息');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

表中的父类与子类关系如下图所示：

-- 查询父类对应的子类关系
SELECT a.`CourseName` as '父课程', b.`CourseName` as '子课程'
	FROM course AS a, course AS b
	where a.CourseId = b.pid;
1
2
3
4

2.12.5 排序和分页

排序通用语句：

select 查询列表
from 表
where 筛选条件
order by 排序列表 asc/desc  -- asc升序  desc降序  （不写默认升序）

1
2
3
4
5

分页通用语法：

select 查询列表
from 表
limit offset,pagesize;   --offset（开始位置），pagesize(一页装的条目数)
1
2
3

实例：

-- 分页查询
select * 
	FROM result
	limit 0, 3;
select * 
	FROM result
	limit 3, 3;
select * 
	FROM result
	limit 6, 3;
1
2
3
4
5
6
7
8
9
10

2.12.6 子查询

子查询的本质：在where子句中再嵌套一个查询语句，以下几种情况通常使用嵌套查询：
子查询语句结果是单行单列，子查询语句作为条件值，使用= ！= > <等条件进行判断。

-- 查询‘数学’的所有考试结果（学号，科目编号，成绩）降序排列
-- 方式1：使用连接查询
SELECT `StudentNo`, result.`SubjectNo`, `StudentResult`
	FROM result
	INNER JOIN `subject`
	ON `result`.SubjectNo = `subject`.SubjectNo
	where `subject`.SubjectName = '数学'
	ORDER BY StudentResult DESC;

-- 方式2：使用子查询（由里到外）
SELECT `StudentNo`, `SubjectNo`, `StudentResult`
	FROM result
	WHERE SubjectNo	= (
		SELECT `SubjectNo` FROM `subject`
		WHERE `SubjectName` = '数学'
	)
	ORDER BY StudentResult DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3函数

3.1常用函数

3.1.1字符串函数

1.ASCII(s)
返回字符串 s 的第一个字符的 ASCII 码。
-- 查询学生姓名姓名第一个字母的ASCII码：	
SELECT ASCII(`name`) as ASCIIFirstN FROM student;

2.CHAR_LENGTH(s) = CHARACTER_LENGTH(s)
返回字符串 s 的字符数
-- 查询学生地址的字符数
SELECT CHAR_LENGTH(`address`) as address_lenght FROM student;

3.CONCAT(s1,s2...sn)
字符串 s1,s2 等多个字符串合并为一个字符串
-- 合并多个字符串
SELECT CONCAT('北京','奥运会','2008年')

4.CONCAT_WS(x, s1,s2...sn)
同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符
-- 合并学生信息
SELECT CONCAT_WS('，',`StudentNo`, `name`,`address`) FROM student

5.FIELD(s,s1,s2...)
返回第一个字符串 s 在字符串列表(s1,s2...)中的位置
-- 返回sys在学生名字中的位置
SELECT FIELD('sys',`name`) from `student`;
-- 返回字符串qqq在列表值中的位置
SELECT FIELD('qqq','hsu',';','qqq','lll')

6.FIND_IN_SET(s1,s2)
返回在字符串s2中与s1匹配的字符串的位置
-- 查询字符串xy在指定字符串中的位置
SELECT FIND_IN_SET('xy', 'ah,xy,11,11,ji,ko');
-- 返回sys在学生名字中的位置
SELECT FIND_IN_SET('sys',`name`) from student;

7.FORMAT(x,n)
函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入
-- 格式化数字,保存小数点后3位
SELECT FORMAT(123456789.123456,3)

8.INSERT(s1,x,len,s2)
字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串
-- 从字符串第一个位置开始替换为runoob.com
SELECT INSERT('1234567890',1,6,'runoob.com');
-- 将学号为5的学生的address前6位修改为北京市
SELECT INSERT(address,1,6,'北京市') FROM `student` WHERE `StudentNo` = 5;  -- 此行为不会更新student

9.LOCATE(s1,s)
从字符串 s 中获取 s1 的开始位置
-- 获取xy在字符串中的位置
SELECT LOCATE('xy','gfuisnsuiuixybuissdfhksdj');  --返回12

10.LCASE(s) = LOWER(s)
将字符串 s 的所有字母变成小写字母
-- 转换为小写
SELECT LCASE('ABC');
SELECT LOWER('ABC');

11. UCASE(s) = UPPER(s)
将字符串转换为大写
-- 转换为大写
SELECT UCASE('abc');
SELECT UPPER('abc');

12. LEFT(s,n)
返回字符串 s 的前 n 个字符
--查询student的省份
SELECT `name`, LEFT(address,2) FROM student;

13. LPAD(s1,len,s2)
（左侧）在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len
-- 将字符串'12'填充到abc字符串的开始处
SELECT LPAD('abc',7,'12')    -- 显示1212abc
SELECT LPAD('abc',7,'123456')    -- 显示1234abc

14. RPAD(s1,len,s2)
（右侧）在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len
-- 将字符串'12'填充到abc字符串的结尾处
SELECT RPAD('abc',7,'12')    -- 显示abc1212

15. LTRIM(s)
去掉字符串 s 开始处的空格
-- 去掉字符串开始与结尾处的空格
SELECT LTRIM('  beijing huanjing ni .  ')  -- 显示'beijing huanjing ni .'

16. RTRIM(s)
去掉字符串 s 结尾处的空格
17. TRIM(s)
去掉字符串 s 开始和结尾处的空格

18. MID(s,n,len) = SUBSTRING(s,n,len)
从字符串 s 的 n 位置截取长度为 len 的子字符串
-- 从字符串 s 的 n 位置截取长度为 len 的子字符串
SELECT MID('beijing huanjing ni',2,5);     --  显示eijin
SELECT SUBSTRING('beijing huanjing ni',2,5);   --  显示eijin

19. POSITION(s1 IN s)
从字符串 s 中获取 s1 的开始位置
-- 返回名字字符串中s的位置
SELECT POSITION('s' IN `name`) FROM student;

20. REPEAT(s,n)
将字符串 s 重复 n 次
-- 将这个名字重复三次
SELECT REPEAT(`name`, 3) from student WHERE StudentNo = 1;

21. REPLACE(s,s1,s2)
用字符串 s2 替代字符串 s 中的字符串 s1
-- 将abc中的b换成x
SELECT REPLACE('abc','b','x');   -- 输出axc

22. REVERSE(s)
将字符串s的顺序反过来
-- 将某个学生的地址翻转
SELECT REVERSE(address) FROM student WHERE StudentNo = 5;   -- 输出：区小二北漠市安西省西陕

23. RIGHT(s,n)
返回字符串s的后n个字符
-- 将某个学生的地址后4位显示
SELECT RIGHT(address,4) FROM student WHERE StudentNo = 5;  -- 输出：北二小区
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119

3.1.2数学运算函数

1. ABS(x)
返回 x 的绝对值
-- 返回 -1 的绝对值
SELECT ABS(-1) -- 返回1

2. ACOS(x)
求 x 的反余弦值（单位为弧度），x 为一个数值
SELECT ACOS(0.5);   -- 显示1.0471975511965979

3. ASIN(x)
求反正弦值（单位为弧度），x 为一个数值

4. ATAN(x)
求反正切值（单位为弧度），x 为一个数值

5. ATAN2(n, m)
求反正切值（单位为弧度）
SELECT ATAN2(-0.8, 2);   -- -0.3805063771123649

6. AVG(expression)
返回一个表达式的平均值，expression 是一个字段
-- 返回数学平均成绩
SELECT AVG(StudentResult) 
	FROM `result`
	INNER JOIN `subject`
	ON `result`.SubjectNo = `subject`.`SubjectNo`
	WHERE `subject`.`SubjectName` = '数学';

7. CEIL(x) = CEILING(x)
返回大于或等于 x 的最小整数[向上取整]
SELECT CEIL(0.5);    --显示1
SELECT CEIL(1);      --显示1

8. COS(x)
求余弦值(参数是弧度)
SELECT COS(20);   -- 显示0.40808206181339196

9. COT(x)
求余切值(参数是弧度)

10.COUNT(expression)
返回查询的记录总数，expression 参数是一个字段或者 * 号
-- 查询参加考试的人数  
SELECT COUNT(DISTINCT StudentNo) FROM result;

11. DEGREES(x)
将弧度转换为角度　　
SELECT DEGREES(3.1415926535898) -- 180

12. n DIV m
整除，n 为被除数，m 为除数,,,n÷m
-- 计算10÷2
SELECT 10 DIV 2;   --显示5

13. EXP(x)
返回 e 的 x 次方
SELECT EXP(3) -- 20.085536923188

14. FLOOR(x)
[向下取整]返回小于或等于 x 的最大整数
SELECT FLOOR(0.5);    --显示0

15. GREATEST(expr1, expr2, expr3, ...)
返回列表中的最大值
SELECT GREATEST(1,30,0,5,74,6)    -- 显示74
SELECT GREATEST('Google', 'Runoob', 'Apple');   -- Runoob

16.LEAST(expr1, expr2, expr3, ...)
返回列表中的最小值
SELECT LEAST(1,30,0,5,74,6)    -- 显示0
SELECT LEAST('Google', 'Runoob', 'Apple');   -- Apple

17. LN(x)
返回x的自然对数，以e为底
SELECT LN(1);   -- 显示0

18. LOG(x) 或 LOG(base, x)
返回自然对数(以 e 为底的对数)，如果带有 base 参数，则 base 为指定带底数。　
SELECT LOG(2,8);  -- 显示3

19. LOG10(x)
返回以 10 为底的对数　

20. LOG2(x)
返回以 2 为底的对数

21. MAX(expression)
返回字段 expression 中的最大值
-- 返回数学最高成绩
SELECT MAX(StudentResult)    -- 显示98
	FROM `result`
	INNER JOIN `subject`
	ON `result`.SubjectNo = `subject`.`SubjectNo`
	WHERE `subject`.`SubjectName` = '数学';

22. MIN(expression)
返回字段 expression 中的最小值

23. MOD(x,y)
返回 x 除以 y 以后的余数　
SELECT MOD(5,2);    -- 显示1

24. PI()
返回圆周率(3.141593）
SELECT PI();   -- 显示3.141593

25.POW(x,y) = POWER(x,y)
返回 x 的 y 次方　
SELECT POW(2,3) -- 8

26. RADIANS(x)
将角度转换为弧度　
SELECT RADIANS(180) -- 3.1415926535898

27. RAND()
返回 0 到 1 的随机数　
SELECT RAND();   -- 0.023957910969814204

28. ROUND(x [,y])
返回离 x 最近的整数，可选参数 y 表示要保留的小数位数，如果省略，则返回整数。（四舍五入）
SELECT ROUND(1.6674123);    -- 2
SELECT ROUND(1.6674123, 2);    -- 1.67

29. SIGN(x)
返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1　
SELECT SIGN(-10) -- -1

30. SQRT(x)
返回x的平方根　
SELECT SQRT(25) -- 5

31. SUM(expression)
返回指定字段的总和
-- 返回数学成绩总和
SELECT SUM(StudentResult)    -- 显示262
	FROM `result`
	INNER JOIN `subject`
	ON `result`.SubjectNo = `subject`.`SubjectNo`
	WHERE `subject`.`SubjectName` = '数学';

32. TAN(x)
求正切值(参数是弧度)

33. TRUNCATE(x,y)
返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）
SELECT TRUNCATE(1.6674123, 0);    -- 1
SELECT TRUNCATE(1.6674123, 2);    -- 1.66
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147

3.1.3日期函数

1. ADDDATE(d,n)
计算起始日期 d 加上 n 天的日期、
SELECT ADDDATE('2024-08-12', 10);     -- 显示2024-08-22

2. ADDTIME(t,n)
n 是一个时间表达式，时间 t 加上时间表达式 n
SELECT ADDTIME('2024-08-12 11:11:56', 5);   -- 2024-08-12 11:12:01

3. CURDATE() = CURRENT_DATE()
返回当前日期
SELECT CURDATE();   -- 显示当前日期2024-08-12
SELECT CURRENT_DATE();

4. CURRENT_TIME() = CURTIME()
返回当前时间
SELECT CURRENT_TIME();   -- 显示17:13:19

5. CURRENT_TIMESTAMP()
返回当前日期和时间
SELECT CURRENT_TIMESTAMP();  -- 2024-08-12 17:15:12

6. DATE()
从日期或日期时间表达式中提取日期值
SELECT DATE("2024-06-15 00:00:00");    -- 显示2024-06-15

7. DATEDIFF(d1,d2)
计算日期 d1->d2 之间相隔的天数
SELECT DATEDIFF('2024-08-12','2024-08-16');    -- 显示-4

8. DATE_ADD(d，INTERVAL expr type)
计算起始日期 d 加上一个时间段后的日期，expr是一个表达式，用于指定从开始日期添加过减去的时间间隔值，type为关键词，指示了表达式被解释的方式。
type值可以是：MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、SECOND_MICROSECOND、MINUTE_MICROSECOND等等
SELECT DATE_ADD("2024-08-12", INTERVAL 10 DAY);    -- 2024-08-22
SELECT DATE_ADD("2024-08-12", INTERVAL -10 DAY);    -- 2024-08-02
SELECT DATE_ADD("2024-06-15 00:00:00", INTERVAL 15 MINUTE);   -- 2024-06-15 00:15:00
SELECT DATE_ADD("2024-06-15 00:00:00", INTERVAL 30 HOUR);     -- 2024-06-16 06:00:00

9. DATE_SUB(date,INTERVAL expr type)
 函数从日期减去指定的时间间隔。
 
10. DATE_FORMAT(d,f)
按表达式 f的要求显示日期 d
SELECT DATE_FORMAT('2024-08-12 17:15:12','%Y-%m-%d %r');         -- 2024-08-12 05:15:12 PM

11. DAY(d)
返回日期值 d 的日期部分
SELECT DAY('2024-08-12');          -- 12

12. DAYNAME(d)
返回日期 d 是星期几，如 Monday,Tuesday
SELECT DAYNAME('2024-08-12');      -- Monday

13.DAYOFMONTH(d)
计算日期 d 是本月的第几天
SELECT DAYOFMONTH('2024-08-12')    -- 12

14. DAYOFWEEK(d)
日期 d 今天是星期几，1 星期日，2 星期一，以此类推
SELECT DAYOFWEEK('2024-08-12');     -- 2

15. DAYOFYEAR(d)
计算日期 d 是本年的第几天
SELECT DAYOFYEAR('2024-08-12');     -- 225

16. EXTRACT(type FROM d)
从日期 d 中获取指定的值，type 指定返回的值。
SELECT EXTRACT(DAY FROM '2024-08-12');   -- 12

17. FROM_DAYS(n)
计算从 0000 年 1 月 1 日开始 n 天后的日期
SELECT FROM_DAYS(999);        -- 0002-09-26

18. HOUR(t)
返回 t 中的小时值
SELECT HOUR('2024-08-12 05:15:12');   -- 5
19. MINUTE(t)
返回 t 中的分钟值
SELECT MINUTE('2024-08-12 05:15:12');           -- 15
20. SECOND(t)
返回 t 中的秒钟值

21. LAST_DAY(d)
返回给给定日期的那一月份的最后一天
SELECT LAST_DAY('2024-08-12 05:15:12');    -- 2024-08-31

22. LOCALTIME() = LOCALTIMESTAMP() = CURRENT_TIMESTAMP()
返回当前日期和时间
SELECT LOCALTIME();    -- 2024-08-12 17:51:43

23. MAKEDATE(year, day-of-year)
基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期
SELECT MAKEDATE(2024,225);      -- 2024-08-12

24. MAKETIME(hour, minute, second)
组合时间，参数分别为小时、分钟、秒
SELECT MAKETIME(12, 6, 8);      -- 12:06:08

25. MICROSECOND(date)
返回日期参数所对应的微秒数
SELECT MICROSECOND('2024-08-12 05:15:12.000056');     -- 56
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

3.1.3系统信息函数

1. SYSTEM_USER() = USER()
返回当前MySQL会话的MySQL用户名和主机名。
SELECT SYSTEM_USER();

2. VERSION()
返回当前MySQL版本
SELECT VERSION();
1
2
3
4
5
6
7

3.2聚合函数

1. max(列名)   最大值
2. min(列名)   最小值
3. sum(列名)   和
4. avg(列名)   平均值
5. count(列名) 计算个数

SELECT 聚合函数 FROM 表名;
1
2
3
4
5
6
7

4视图

4.1视图概述

4.1.1视图的概念

视图是由数据库中一个表或多个表导出的虚拟表，其作用是方便用户对数据进行操作。

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的行和列数据。但是==数据库中只存放了视图的定义，并没有存放视图中的数据，这些数据存放在原来的表中。==使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。同样，对视图进行更新也会影响到原表的数据。

视图是存储在数据库中的查询的sql语句，他主要出于两种原因：

1. 安全原因：视图可以隐藏一些数据，例如，员工信息表可以用视图只显示姓名、工龄、地址而不显示社会保险号和工资数等；
2. 视图可使复杂的查询易于理解和使用：视图就像一个“窗口”，从中只能看到想看的数据，意味着可以在这个视图上使用SELECT *，看到的是视图定义给出的那些列。

4.1.2视图的作用

视图是在原有表或者视图的基础上重新定义的虚拟表，可以从原有的表上选取对用户有用的信息，忽略次要信息，其作用类似于筛选。

视图的作用归纳为如下3点：

1. 使操作简单化：视图需要达到的目的就是所见即所需。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
2. 增加数据的安全性：通过视图，用户只能查询和修改指定的数据。指定数据之外的信息，用户根本接触不到。这样可以防止敏感信息被未授权的用户查看，增强机密信息的安全性。
3. 提高表的逻辑独立性：视图可以屏蔽原有表结构变化带来的影响。例如原有表增加列和删除未被引用的列，对视图不会造成影响。同样，如果修改表中的某些列，可以使用修改视图来解决这些列带来的影响。

4.2视图的基本操作

4.2.1创建视图

创建视图是指在已经存在的数据库表上建立视图。视图可以建立在一张表中，也可以建立在多张表中。
查看创建视图的权限
创建视图需要具有CREATE VIEW的权限。同时应该具有查询涉及的列的SELECT权限。可以使用SELECT语句来查询这些权限信息。查询语法如下：

SELECT Select_priv,Create_view_priv FROM mysql.user WHERE user='用户名';
-- Select_priv：属性表示用户是否具有SELECT权限，Y表示拥有SELECT权限，N表示没有。
-- Create_view_priv：属性表示用户是否具有CREATE VIEW权限；
-- mysql.user：表示MySQL数据库下面的user表。
-- 用户名：参数表示要查询是否拥有权限的用户，该参数需要用单引号引起来。

使用root创建视图即可
1
2
3
4
5
6
7

创建视图的通用语法：

CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW 视图名[(属性清单)]
AS SELECT语句
[WITH [CASCADED|LOCAL] CHECK OPTION];

-- ALGORITHM：可选项，表示视图选择的算法。
-- 视图名：表示要创建的视图名称。
-- 属性清单：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。
-- SELECT语句：表示一个完整的查询语句，将查询记录导入视图中。
-- WITH CHECK OPTION：可选项，表示更新视图时要保证在该视图的权限范围之内。
1
2
3
4
5
6
7
8
9
10

实例：

-- 创建视图
CREATE VIEW view_student AS SELECT `StudentNo`, `name`, `GradeId` FROM `student`; 

-- 创建视图且改变列名称
CREATE VIEW view_student_1(`id`, `name`, `grade`) AS SELECT `StudentNo`, `name`, `GradeId` FROM `student`; 
1
2
3
4
5

创建视图时需要注意的点：
（1）运行创建视图的语句需要用户具有创建视图（create view）的权限，若加了[or replace]时，还需要用户具有删除视图（drop view）的权限；

（2）select语句不能包含from子句中的子查询；

（3）select语句不能引用系统或用户变量；

（4）select语句不能引用预处理语句参数；

（5）在存储子程序内，定义不能引用子程序参数或局部变量；

（6）在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用check table语句；

（7）在定义中不能引用temporary表，不能创建temporary视图；

（8）在视图定义中命名的表必须已存在；

（9）不能将触发程序与视图关联在一起；

（10）在视图定义中允许使用order by，但是，如果从特定视图进行了选择，而该视图使用了具有自己order by的语句，它将被忽略。

4.2.2修改视图

修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。MySQL中通过CREATE OR REPLACE VIEW语句和ALTER VIEW语句来修改视图。
修改视图的通用语句：

ALTER VIEW
1

实例：

-- 修改视图view_student_1
ALTER VIEW view_student_1(`id`, `name`, `grade`) 
	AS SELECT `StudentNo`, `name`, `GradeId` FROM `student` 
	WHERE `StudentNo` in (SELECT `StudentNo` FROM `result`);
1
2
3
4

说明：ALTER VIEW语句改变了视图的定义，该语句与CREATE OR REPLACE VIEW语句有着同样的限制，如果删除并重新创建一个视图，就必须重新为它分配权限。

4.2.3删除视图

删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。MySQL中，使用DROP VIEW语句来删除视图。但是，用户必须拥有DROP权限。
删除视图通用语句：

DROP VIEW 视图名;

-- 删除视图
DROP VIEW IF EXISTS view_student_1;
1
2
3
4

4.2.4查看视图

查看视图是指查看数据库中已存在的视图的定义。
查看视图通用语句

DESC 视图名;
或者
SHOW FIELDS FROM 视图名;
1
2
3

实例：

-- 查看视图
DESC view_student_1;
SHOW FIELDS FROM view_student_1;
1
2
3

5触发器

5.1触发器概念

定义
MySQL 的触发器和存储过程一样，都是嵌入到 MySQL 中的一段程序，是 MySQL 中管理数据的有力工具。通过对数据表的相关操作来触发、激活从而实现执行。比如当对 student 表进行操作（ INSERT， DELETE 或 UPDATE）时就会激活它执行。
作用
触发器与数据表关系密切，主要用于保护表中的数据。特别是当有多个表具有一定的相互联系的时候，触发器能够让不同的表保持数据的一致性、日志记录 , 数据校验等操作。

5.2触发器优缺点

5.2.1 触发器优点

1. 触发器的执行是自动的，当对触发器相关表的数据做出相应的修改后立即执行。
2. 触发器可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作。
3. 触发器可以实现表数据的级联更改，在一定程度上保证了数据的完整性。

5.2.2 触发器缺点

• 使用触发器实现的业务逻辑在出现问题时很难进行定位，特别是涉及到多个触发器的情况下，会使后期维护变得困难。
• 大量使用触发器容易导致代码结构被打乱，增加了程序的复杂性。
• 如果需要变动的数据量较大时，触发器的执行效率会非常低。

5.3触发器类型

在实际使用中， MySQL 所支持的触发器有三种：

• INSERT 触发器
• UPDATE 触发器
• DELETE 触发器

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

5.3.1INSERT触发器

在 INSERT 语句执行之前或之后响应的触发器，使用 INSERT 触发器需要注意以下几点：

1. 更多操作在 INSERT 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问被插入的行。
2. 在 BEFORE INSERT 触发器中， NEW 中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）。
3. 对于 AUTO_INCREMENT 列， NEW 在 INSERT 执行之前包含的值是 0，在 INSERT 执行之后将包含新的自动生成值。

5.3.2UPDATE触发器

在 UPDATE 语句执行之前或之后响应的触发器，使用 UPDATE 触发器需要注意以下几点：

1. 在 UPDATE 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问更新的值。
2. 在 UPDATE 触发器代码内，可引用一个名为 OLD（不区分大小写）的虚拟表来访问 UPDATE 语句执行前的值。
3. 在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新，即允许更改将要用于 UPDATE 语句中的值（只要具有对应的操作权限）。
4. OLD 中的值全部是只读的，不能被更新。

当触发器设计对触发表自身的更新操作时，只能使用 BEFORE 类型的触发器，AFTER 类型的触发器将不被允许。

5.3.3DELETE触发器

DELETE 语句执行之前或之后响应的触发器，使用 DELETE 触发器需要注意以下几点：

1. 在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表来访问被删除的行。
2. OLD 中的值全部是只读的，不能被更新。

总体来说，触发器使用的过程中，MySQL 会按照以下方式来处理错误。

对于事务性表，如果触发程序失败，以及由此导致的整个语句失败，那么该语句所执行的所有更改将回滚；对于非事务性表，则不能执行此类回滚，即使语句失败，失败之前所做的任何更改依然有效。
若 BEFORE 触发程序失败，则 MySQL 将不执行相应行上的操作。
若在 BEFORE 或 AFTER 触发程序的执行过程中出现错误，则将导致调用触发程序的整个语句失败。
仅当 BEFORE 触发程序和行操作均已被成功执行，MySQL 才会执行 AFTER 触发程序。

5.4触发器相关语法

5.4.1创建触发器

创建触发器的通用语法：

CREATE TIGGER <触发器名>       -- 每当触发事件发生时，该触发器被激活
< BEFORE | AFTER > <触发事件 >  ON <表名>   -- 指明触发器的激活时间是在执行触发事件前或后
REFERENCING NEW|OLD ROW AS <变量>         -- REFERENCING指出被引用的变量
FOR EACH {ROW|STATEMENT}                 -- 定义触发器的类型，指明动作执行的频率
[WHEN<触发条件>] <触发动作体>              -- 仅当触发条件为真时才执行触发动作体

1
2
3
4
5
6

对触发器各部分语法的详细说明：
（1）只有表的拥有者，即创建表的用户才可以在表上创建触发器。
（2）触发器名：同一模式下，触发器名必须是唯一的，并且触发器名和表明必须在统一模式下。
（3）表名：触发器只能定义在基本表上，不能定义在视图上
（4）触发事件：触发事件可以是INSERT、UPDATE、或者DELETE，也可以是这几个事件的组合；AFTER/BEFORE是出发时机。
（5）触发器类型：触发器按照所触发动作的建个尺寸可以分为行级触发器（FOR EACH ROW）和语句级触发器（FOR EACH STATEMENT）.
（6）触发动作体：触发动作体既可以是一个匿名的PL/SQL过程快，也可以是对已创建存储过程的调用。如果触发动作体执行失败，激活触发器的事件（即对数据库的增删改操作）就会终止执行，触发器的目标表或触发器可能影响的其他对象不发生任何变化。

INSERT实例：每次对表student增加行时后，记录加入学生的数量。

CREATE TRIGGER student_count
	AFTER INSERT ON student
	FOR EACH ROW
	INSERT INTO student_insert_log(numbers)
		VALUES(NULL);
1
2
3
4
5

UPDATE实例：每次更新学生年级后，在表student_t_1中插入更新前和更新后的年级

CREATE TRIGGER result_t_1
AFTER UPDATE ON student
FOR EACH ROW
BEGIN
	INSERT INTO student_t_1(StudentNo, `name`, oldgradeid, newgradeid)
	VALUES(new.StudentNo, new.`name`, old.GradeId, new.GradeId);
END;
1
2
3
4
5
6
7

DELETE实例：删除学生后，记录学生的学号和名字

-- delete型触发器
CREATE TRIGGER DELETE_tigger
	AFTER DELETE ON student 
	FOR EACH ROW 
	BEGIN
		INSERT INTO student_delete(id, `name`)
		VALUES(old.StudentNo, old.`name`);
	END;
1
2
3
4
5
6
7
8

不知道为什么navicat中不能使用statement和referencing关键字。解决这个问题的大佬帮帮我吧。

5.4.2查看触发器

方式1：在创建触发器的表中右键点击“设计表”：


方式2：

-- 查看所有触发器的基本信息
SHOW TRIGGERS;
1
2

在 MySQL 中，所有触发器的信息都存在 information_schema 数据库的 triggers 表中，可以通过查询命令 SELECT 来查看，具体的语法如下：

SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名';

-- 实例
SELECT * FROM information_schema.triggers WHERE trigger_name= 'DELETE_trigger';
1
2
3
4

-- 查看所有触发器
SELECT * FROM information_schema.TRIGGERS
1
2

5.4.3删除触发器

使用 DROP TRIGGER 语句可以删除 MySQL 中已经定义的触发器：

DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>

--实例
DROP TRIGGER result_t_1 ;
1
2
3
4