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 数据库名;

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

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

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

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

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

例如：

CREATE DATABASE IF NOT EXISTS StudentsCHARSETR SET utf8mb4COLLATE utf8mb4_general_ci;

2.2删除数据库

drop 命令删除数据库：

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

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

2.3选择数据库

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

USE database_name;

2.4数据类型（字段）

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

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	十分小的数据
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	较小数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	中等大小数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	标准整数
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大数据
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	字符串形式的浮点数，一般用于金融计算

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

类型	大小（bytes）	范围	格式	用途
date	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
time	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
year	1	1901/2155	YYYY	年份值
datetime	8	‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’	YYYY-MM-DD hh:mm:ss	混合日期和时间值
timestamp	4	‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳

3.字符串

类型	大小(bytes)	用途
char	0-255	定长字符串
varchar	0-65535	可变字符串
tinyblob	0-255	不超过255字符的二进制字符串
blob	0-65535	二进制形式的长文本
mediumblob	0-16 777 215	二进制形式的中等长度文本数据
longblob	0-4 294 967 295	二进制形式的极大文本数据
tinytext	2^8-1	微型文本
text	0-65535	长文本数据
mediumtext	0-16 777 215	中等长文本数据
longtext	0-4 294 967 295	极长文本数据

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 被参照表(被参照列名)   );

或者

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

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;

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

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

2.7修改数据库

修改表名：

ALTER TABLE 旧表名 RENAME AS 新表名ALTER TABLE students RENAME AS student;

添加字段：

ALTER TABLE 表名 ADD字段名 列属性[属性]ALTER TABLE student ADD age INT(11) ; -- 增加字段

修改字段：

ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
-- 后面一定要跟新的属性ALTER TABLE student MODIFY age VARCHAR(11) ;       -- 修改字段约束
ALTER TABLE student CHANGE age age1 INT(11) DEFAULT NULL COMMENT '年龄';          -- 字段重命名

删除字段：

ALTER TABLE 表名 DROP 字段名ALTER TABLE student DROP age1;           -- 删除字段

2.8删除数据表

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

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

2.9插入数据

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

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

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

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 )

2.10 更新数据

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

更新数据通用语法：

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

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

2.10.1 where子句

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

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

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

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

操作符	描述	实例
=	等号，返回两个值是否相等，如果相等，返回True	(A = B) 返回True
<>, !=	不等于，检测两个值是否相等，如果不相等返回true	(A != B) 返回 true。
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B) 返回Ture。
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true。
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回True。
<=	小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回 true。
LIKE	模糊匹配条件	WHERE name LIKE ‘s’ ，如果name中有带有s字符，返回True
IN	IN 条件:	country_code IN (‘US’, ‘CA’, ‘MX’)，国家里面有这三个其中一个，则返回True
NOT	非	NOT A = ‘B’，当A不为B时，返回True
BETWEEN AND	闭合区间	birthday BETWEEN ‘2023-01-01’ AND ‘2023-12-31’ ，生日在这两个日期之间，则返回True
IS NULL	为空
IS NOT NULL	不为空
AND	和
OR	或

实例：

SELECT * from student Where `age` >= 18

2.11 删除数据

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

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

实例

DELETE from student where `name` = 'qww'

注意：
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];     -- 限制返回的行数

查询前需要先创建数据：

-- 创建学生数据库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` = 5SELECT * from student Where `age` >= 18DELETE 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);	

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;

2.12.2 条件查询

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

操作符	描述	实例
=	等号，返回两个值是否相等，如果相等，返回True	(A = B) 返回True
<>, !=	不等于，检测两个值是否相等，如果不相等返回true	(A != B) 返回 true。
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B) 返回Ture。
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true。
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回True。
<=	小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回 true。
LIKE	模糊匹配条件	WHERE name LIKE ‘s’ ，如果name中有带有s字符，返回True
IN	IN 条件:	country_code IN (‘US’, ‘CA’, ‘MX’)，国家里面有这三个其中一个，则返回True
NOT	非	NOT A = ‘B’，当A不为B时，返回True
BETWEEN AND	闭合区间	birthday BETWEEN ‘2023-01-01’ AND ‘2023-12-31’ ，生日在这两个日期之间，则返回True
IS NULL	为空
IS NOT NULL	不为空
AND	和
OR	或

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

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 排序列表]

区分：

	使用关键字	筛选的表	位置
分组前筛选	where	原始表	group by的前面
分组后筛选	having	分组后的结果	group by的后面

实例：

-- 查询不同科目的平均分，最高分，最低分且平均分大于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;     -- 降序排序

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后面为连接条件

内连接：

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

多表连接查询：

-- 查询学生考试成绩
SELECT `student`.`StudentNo`, `name`, `SubjectName`, `StudentResult`FROM `student`INNER JOIN `result`ON `student`.`StudentNo` = `result`.`StudentNo`INNER JOIN `subject`ON `result`.SubjectNo = `subject`.SubjectNoORDER BY StudentNo ASC;

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

-- （自连接）创建一个表
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, '办公信息');

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

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

2.12.5 排序和分页

排序通用语句：

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

分页通用语法：

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

实例：

-- 分页查询
select * FROM resultlimit 0, 3;
select * FROM resultlimit 3, 3;
select * FROM resultlimit 6, 3;

2.12.6 子查询

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

-- 查询‘数学’的所有考试结果（学号，科目编号，成绩）降序排列
-- 方式1：使用连接查询
SELECT `StudentNo`, result.`SubjectNo`, `StudentResult`FROM resultINNER JOIN `subject`ON `result`.SubjectNo = `subject`.SubjectNowhere `subject`.SubjectName = '数学'ORDER BY StudentResult DESC;-- 方式2：使用子查询（由里到外）
SELECT `StudentNo`, `SubjectNo`, `StudentResult`FROM resultWHERE SubjectNo	= (SELECT `SubjectNo` FROM `subject`WHERE `SubjectName` = '数学')ORDER BY StudentResult DESC;

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 student5.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;  -- 此行为不会更新student9.LOCATE(s1,s)
从字符串 s 中获取 s1 的开始位置
-- 获取xy在字符串中的位置
SELECT LOCATE('xy','gfuisnsuiuixybuissdfhksdj');  --返回1210.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')    -- 显示1234abc14. RPAD(s1,len,s2)
（右侧）在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len
-- 将字符串'12'填充到abc字符串的结尾处
SELECT RPAD('abc',7,'12')    -- 显示abc121215. 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);   --  显示eijin19. 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');   -- 输出axc22. 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;  -- 输出：北二小区

3.1.2数学运算函数

1. ABS(x)
返回 x 的绝对值
-- 返回 -1 的绝对值
SELECT ABS(-1) -- 返回12. ACOS(x)
求 x 的反余弦值（单位为弧度），x 为一个数值
SELECT ACOS(0.5);   -- 显示1.04719755119659793. ASIN(x)
求反正弦值（单位为弧度），x 为一个数值4. ATAN(x)
求反正切值（单位为弧度），x 为一个数值5. ATAN2(n, m)
求反正切值（单位为弧度）
SELECT ATAN2(-0.8, 2);   -- -0.38050637711236496. 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);      --显示18. COS(x)
求余弦值(参数是弧度)
SELECT COS(20);   -- 显示0.408082061813391969. COT(x)
求余切值(参数是弧度)10.COUNT(expression)
返回查询的记录总数，expression 参数是一个字段或者 * 号
-- 查询参加考试的人数  
SELECT COUNT(DISTINCT StudentNo) FROM result;11. DEGREES(x)
将弧度转换为角度　　
SELECT DEGREES(3.1415926535898) -- 18012. n DIV m
整除，n 为被除数，m 为除数,,,n÷m
-- 计算10÷2
SELECT 10 DIV 2;   --显示513. EXP(x)
返回 e 的 x 次方
SELECT EXP(3) -- 20.08553692318814. FLOOR(x)
[向下取整]返回小于或等于 x 的最大整数
SELECT FLOOR(0.5);    --显示015. GREATEST(expr1, expr2, expr3, ...)
返回列表中的最大值
SELECT GREATEST(1,30,0,5,74,6)    -- 显示74
SELECT GREATEST('Google', 'Runoob', 'Apple');   -- Runoob16.LEAST(expr1, expr2, expr3, ...)
返回列表中的最小值
SELECT LEAST(1,30,0,5,74,6)    -- 显示0
SELECT LEAST('Google', 'Runoob', 'Apple');   -- Apple17. LN(x)
返回x的自然对数，以e为底
SELECT LN(1);   -- 显示018. LOG(x) 或 LOG(base, x)
返回自然对数(以 e 为底的对数)，如果带有 base 参数，则 base 为指定带底数。　
SELECT LOG(2,8);  -- 显示319. LOG10(x)
返回以 10 为底的对数　20. LOG2(x)
返回以 2 为底的对数21. MAX(expression)
返回字段 expression 中的最大值
-- 返回数学最高成绩
SELECT MAX(StudentResult)    -- 显示98FROM `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);    -- 显示124. PI()
返回圆周率(3.141593）
SELECT PI();   -- 显示3.14159325.POW(x,y) = POWER(x,y)
返回 x 的 y 次方　
SELECT POW(2,3) -- 826. RADIANS(x)
将角度转换为弧度　
SELECT RADIANS(180) -- 3.141592653589827. RAND()
返回 0 到 1 的随机数　
SELECT RAND();   -- 0.02395791096981420428. ROUND(x [,y])
返回离 x 最近的整数，可选参数 y 表示要保留的小数位数，如果省略，则返回整数。（四舍五入）
SELECT ROUND(1.6674123);    -- 2
SELECT ROUND(1.6674123, 2);    -- 1.6729. SIGN(x)
返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1　
SELECT SIGN(-10) -- -130. SQRT(x)
返回x的平方根　
SELECT SQRT(25) -- 531. SUM(expression)
返回指定字段的总和
-- 返回数学成绩总和
SELECT SUM(StudentResult)    -- 显示262FROM `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

3.1.3日期函数

1. ADDDATE(d,n)
计算起始日期 d 加上 n 天的日期、
SELECT ADDDATE('2024-08-12', 10);     -- 显示2024-08-222. ADDTIME(t,n)
n 是一个时间表达式，时间 t 加上时间表达式 n
SELECT ADDTIME('2024-08-12 11:11:56', 5);   -- 2024-08-12 11:12:013. CURDATE() = CURRENT_DATE()
返回当前日期
SELECT CURDATE();   -- 显示当前日期2024-08-12
SELECT CURRENT_DATE();4. CURRENT_TIME() = CURTIME()
返回当前时间
SELECT CURRENT_TIME();   -- 显示17:13:195. CURRENT_TIMESTAMP()
返回当前日期和时间
SELECT CURRENT_TIMESTAMP();  -- 2024-08-12 17:15:126. DATE()
从日期或日期时间表达式中提取日期值
SELECT DATE("2024-06-15 00:00:00");    -- 显示2024-06-157. DATEDIFF(d1,d2)
计算日期 d1->d2 之间相隔的天数
SELECT DATEDIFF('2024-08-12','2024-08-16');    -- 显示-48. 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:009. 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 PM11. DAY(d)
返回日期值 d 的日期部分
SELECT DAY('2024-08-12');          -- 1212. DAYNAME(d)
返回日期 d 是星期几，如 Monday,Tuesday
SELECT DAYNAME('2024-08-12');      -- Monday13.DAYOFMONTH(d)
计算日期 d 是本月的第几天
SELECT DAYOFMONTH('2024-08-12')    -- 1214. DAYOFWEEK(d)
日期 d 今天是星期几，1 星期日，2 星期一，以此类推
SELECT DAYOFWEEK('2024-08-12');     -- 215. DAYOFYEAR(d)
计算日期 d 是本年的第几天
SELECT DAYOFYEAR('2024-08-12');     -- 22516. EXTRACT(type FROM d)
从日期 d 中获取指定的值，type 指定返回的值。
SELECT EXTRACT(DAY FROM '2024-08-12');   -- 1217. FROM_DAYS(n)
计算从 0000 年 1 月 1 日开始 n 天后的日期
SELECT FROM_DAYS(999);        -- 0002-09-2618. 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-3122. LOCALTIME() = LOCALTIMESTAMP() = CURRENT_TIMESTAMP()
返回当前日期和时间
SELECT LOCALTIME();    -- 2024-08-12 17:51:4323. MAKEDATE(year, day-of-year)
基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期
SELECT MAKEDATE(2024,225);      -- 2024-08-1224. MAKETIME(hour, minute, second)
组合时间，参数分别为小时、分钟、秒
SELECT MAKETIME(12, 6, 8);      -- 12:06:0825. MICROSECOND(date)
返回日期参数所对应的微秒数
SELECT MICROSECOND('2024-08-12 05:15:12.000056');     -- 56

3.1.3系统信息函数

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

3.2聚合函数

1. max(列名)   最大值
2. min(列名)   最小值
3. sum(列名)   和
4. avg(列名)   平均值
5. count(列名) 计算个数SELECT 聚合函数 FROM 表名;

4视图

4.1视图概述

4.1.1视图的概念

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

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

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

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

4.1.2视图的作用

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

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

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

4.2视图的基本操作

操作指令	代码
创建视图	CREATE VIEW 视图名(列1，列2...) AS SELECT (列1，列2...) FROM ...;
使用视图	当成表使用就好
修改视图	CREATE OR REPLACE VIEW 视图名 AS SELECT [...] FROM [...];
查看数据库已有视图	>SHOW TABLES [like...];（可以使用模糊查找）
查看视图详情	DESC 视图名或者SHOW FIELDS FROM 视图名
视图条件限制	[WITH CHECK OPTION]

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创建视图即可

创建视图的通用语法：

CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW 视图名[(属性清单)]
AS SELECT语句
[WITH [CASCADED|LOCAL] CHECK OPTION];-- ALGORITHM：可选项，表示视图选择的算法。
-- 视图名：表示要创建的视图名称。
-- 属性清单：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。
-- SELECT语句：表示一个完整的查询语句，将查询记录导入视图中。
-- WITH CHECK OPTION：可选项，表示更新视图时要保证在该视图的权限范围之内。

实例：

-- 创建视图
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）运行创建视图的语句需要用户具有创建视图（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

实例：

-- 修改视图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`);

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

4.2.3删除视图

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

DROP VIEW 视图名;-- 删除视图
DROP VIEW IF EXISTS view_student_1;

4.2.4查看视图

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

DESC 视图名;
或者
SHOW FIELDS FROM 视图名;

实例：

-- 查看视图
DESC view_student_1;
SHOW FIELDS FROM view_student_1;

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来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器类型	NWE和OLD
INSERT型触发器	NEW 表示将要或者已经新增的数据
UPDATE型触发器	OLD 表示修改之前的数据，NEW 表示将要或已经修改后的数据
DELETE 触发器	OLD 表示将要或者已经删除的数据

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）触发事件：触发事件可以是INSERT、UPDATE、或者DELETE，也可以是这几个事件的组合；AFTER/BEFORE是出发时机。
（5）触发器类型：触发器按照所触发动作的建个尺寸可以分为行级触发器（FOR EACH ROW）和语句级触发器（FOR EACH STATEMENT）.
（6）触发动作体：触发动作体既可以是一个匿名的PL/SQL过程快，也可以是对已创建存储过程的调用。如果触发动作体执行失败，激活触发器的事件（即对数据库的增删改操作）就会终止执行，触发器的目标表或触发器可能影响的其他对象不发生任何变化。

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

CREATE TRIGGER student_countAFTER INSERT ON studentFOR EACH ROWINSERT INTO student_insert_log(numbers)VALUES(NULL);

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

CREATE TRIGGER result_t_1
AFTER UPDATE ON student
FOR EACH ROW
BEGININSERT INTO student_t_1(StudentNo, `name`, oldgradeid, newgradeid)VALUES(new.StudentNo, new.`name`, old.GradeId, new.GradeId);
END;

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

-- delete型触发器
CREATE TRIGGER DELETE_tiggerAFTER DELETE ON student FOR EACH ROW BEGININSERT INTO student_delete(id, `name`)VALUES(old.StudentNo, old.`name`);END;

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

5.4.2查看触发器

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


方式2：

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

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

SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名';-- 实例
SELECT * FROM information_schema.triggers WHERE trigger_name= 'DELETE_trigger';

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

5.4.3删除触发器

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

DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>--实例
DROP TRIGGER result_t_1 ;