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 Students
CHARSETR SET utf8mb4
COLLATE 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` = 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);
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的后面 |
where不可使用聚合函数
having可使用聚合函数
实例：
-- 查询不同科目的平均分，最高分，最低分且平均分大于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`.SubjectNo
ORDER 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 b
where 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 result
limit 0, 3;
select *
FROM result
limit 3, 3;
select *
FROM result
limit 6, 3;
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;
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; -- 输出：北二小区
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
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
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语句，他主要出于两种原因：
- 安全原因：视图可以隐藏一些数据，例如，员工信息表可以用视图只显示姓名、工龄、地址而不显示社会保险号和工资数等；
- 视图可使复杂的查询易于理解和使用：视图就像一个“窗口”，从中只能看到想看的数据，意味着可以在这个视图上使用
SELECT *，看到的是视图定义给出的那些列。
4.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 触发器优点
- 触发器的执行是自动的，当对触发器相关表的数据做出相应的修改后立即执行。
- 触发器可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作。
- 触发器可以实现表数据的级联更改，在一定程度上保证了数据的完整性。
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 触发器需要注意以下几点：
- 更多操作在 INSERT 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问被插入的行。
- 在 BEFORE INSERT 触发器中， NEW 中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）。
- 对于 AUTO_INCREMENT 列， NEW 在 INSERT 执行之前包含的值是 0，在 INSERT 执行之后将包含新的自动生成值。
5.3.2UPDATE触发器
在 UPDATE 语句执行之前或之后响应的触发器，使用 UPDATE 触发器需要注意以下几点：
- 在 UPDATE 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问更新的值。
- 在 UPDATE 触发器代码内，可引用一个名为 OLD（不区分大小写）的虚拟表来访问 UPDATE 语句执行前的值。
- 在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新，即允许更改将要用于 UPDATE 语句中的值（只要具有对应的操作权限）。
- OLD 中的值全部是只读的，不能被更新。
当触发器设计对触发表自身的更新操作时，只能使用 BEFORE 类型的触发器，AFTER 类型的触发器将不被允许。
5.3.3DELETE触发器
DELETE 语句执行之前或之后响应的触发器，使用 DELETE 触发器需要注意以下几点：
- 在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表来访问被删除的行。
- 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_count
AFTER INSERT ON student
FOR EACH ROW
INSERT INTO student_insert_log(numbers)
VALUES(NULL);
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;
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;
不知道为什么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 ;
6345
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
