MySQL
1. 数据库简述
数据库是存储数据与管理的地方
DB 数据库
DBMS 数据库管理系统
RDBMS 关系数据库管理系统
DBMS 必须提供1.数据的安全性保护，2.数据的完整性保护，3.并发控制，4.数据库恢复。
数据独立：数据库的数据与应用程序的互不依赖，分为物理独立性和逻辑独立性
- 物理独立性：用户的应用程序和存储在磁盘的数据库数据是相互独立
- 逻辑独立性：用户应用程序与数据库的逻辑结构相互独立，
数据独立有利于数据库改变时应用程序尽可能不改变或者小改变。
三级模式俩级映像 ， 模式也被称作概念模式和逻辑模式
-
外模式（子模式/用户视图）
用户看见的逻辑数据模型描述的数据，是模式的子集，根据用户的不同需求，用户视图就不一样，一个模式可以拥有许多外模式，意外模式建立的数据库为用户数据库。用户只能看见与自己有关的数据，做到了保护数据安全的作用。
-
模式（逻辑模式/概念模型）
是数据库中全部数据的逻辑结构和特征的描述、数据结构和属性的描述。系统为了减少数据冗余，实现数据共享，对所有用户数据综合抽象得到的全局数据视图。
-
内模式（存储模式/物理模式）
是数据物理结构和储存方式的描述，对数据内部表示或底层描述的表示，把系统的模式（全局逻辑模式）组织成最优的物理模式；物理数据是概念数据库的具体实现，物理模式建立的数据库为物理数据库。
物理模式是实际存在的，逻辑模式是对物理模式的抽象化，物理模式是对逻辑模式的具体实现，用户数据库（外模式）是逻辑模式的子集。
其中每俩层模式有一层映像
-
外模式<–外模式/模式映像–>模式
定义外模式与模式之间的对应关系。每一个外模式对应一个映像，
映象定义（外模式与模式之间对应关系）包含在各自外模式的描述中即呈现给用户的视图中。
用途是：保证数据逻辑独立性ex.模式变了，修改外模式/模式映象映像即可，不会影响应用程序。
保证了数据的逻辑独立性
-
模式<—模式/内模式映像—>内模式
一个数据库只有一个此映象。映象定义包含在模式描述中。
用途是：保证数据物理独立性ex.存储方式变了，修改映象就好，模式、应用程序不受影响。
保证了数据的物理独立性
概念模型
概念模式是现实社会到信息世界的第一层抽象，是设计数据库的有力工具，是数据库设计人员和用户交流的语言。数据的范畴：现实世界，信息世界和计算机世界。现实世界是客观世界，存在各种事物及其关系。信息世界（观念世界）是现实世界在人们头脑的抽象化，客观事物在信息世界内是实体，实体之间的联系是实体模型或概念模型。计算机世界是信息世界里的信息在计算机的数字化处理后，在计算机硬件系统和DBMS中呈现的数据。
实体 - 联系模型（E-R模型）
ER模型是概念模型的一种，由实体集、属性和联系组成。
-
实体是现实世界的对象，客观存在并相互区分的事物。例如，班级上的每一个人，一台电脑，早餐的豆浆等等
-
属性：实体拥有的某一特征称为属性，一个实体由若干个属性来描述。比如一个人的身高，体重等等组成一个人，而身高体重是一个人的属性。
-
- 属性必须是不可分的最小数据项，属性不可包含其他属性。
- 同一属性不能与其他多个实体具有联系
-
实体集具有相同属性的集合是实体集，人类是一个实体集，班级是一个实体集，班级里有很多同学。
-
键唯一标识实体的不同。学生的学号，中国公民的身份证号。
-
实体型是用实体型和属性名的集合抽象一个实体，实体名(属性1，属性2，属性3…)
例如 班级(学号，名字，年龄，身高，体重) ,其中下划线标明键
-
联系：实体集内部属性与实体集的关系，实体集之间的关系。
在E-R图中，实体集用矩形表示，属性用椭圆表示，无向边连接实体集和属性。下划线标识键。联系用菱形表示，无向图连接实体集与实体集，每个实体集之间的联系分为1对多，多对1，多对多的关系在无向边上用1:n、n:1、n:m表示
例如：
多值属性的椭圆用两个圈，完全参与两条无向边
派生属性，动态的，是数据库中的衍生数据，是一种特殊属性。例如人的出生日期可以派生出人的年龄这个属性，在e-r图用虚线画椭圆，用虚线无向图连接实体集。
数据模型分为
-
层次模型
┌─────┐ │ │ └─────┘ │ ┌───────┴───────┐ │ │ ┌─────┐ ┌─────┐ │ │ │ │ └─────┘ └─────┘ │ │ ┌───┴───┐ ┌───┴───┐ │ │ │ │ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ │ │ │ │ │ │ │ │ └─────┘ └─────┘ └─────┘ └─────┘ -
网状模型
┌─────┐ ┌─────┐ ┌─│ │──────│ │──┐ │ └─────┘ └─────┘ │ │ │ │ │ │ └──────┬─────┘ │ │ │ │ ┌─────┐ ┌─────┐ ┌─────┐ │ │─────│ │─────│ │ └─────┘ └─────┘ └─────┘ │ │ │ │ ┌─────┴─────┐ │ │ │ │ │ │ ┌─────┐ ┌─────┐ │ └──│ │─────│ │──┘ └─────┘ └─────┘ -
关系模型
┌─────┬─────┬─────┬─────┬─────┐
│ │ │ │ │ │
├─────┼─────┼─────┼─────┼─────┤
│ │ │ │ │ │
├─────┼─────┼─────┼─────┼─────┤
│ │ │ │ │ │
├─────┼─────┼─────┼─────┼─────┤
│ │ │ │ │ │
└─────┴─────┴─────┴─────┴─────┘
在 长期使用中，关系模型获得 了市场的认可，原因是理解起来简单，使用起来方便
关系模型
关系模型分为三个组成：数据结构、数据操作和完整性规则
关系模型建立在数学概念的基础上，用二维表描述实体与实体之间的关系。
关系模型的术语：
- 关系：一张表是一个关系
- 元组：表格一行是一个元组
- 属性：一列是一个属性
- 主键：一个属性，是唯一的，可以区分其他元组的
- 域：列的取值范围
- 分量： 元组的一个属性
- 关系模式：对关系的描述，表示为关系名(属性1，属性2！……)例如 学生（班级，年龄，姓名，性别，学号）
键：关键码，用来标识行（元组）一个或几个列（属性），键唯一叫唯一键否则为复合键
- 超键：一个关系里面，唯一标识元组的属性或属性集为关系的超键
- 候选键：一个属性集标识唯一元组，且不含多余的属性，称为关系的候选键
- 主键：一个关系的候选键中，选择一个候选键为主键，实现表中“两个元组不完全相同”
- 外键：一个关系中有一个键在另外一个键上，作为连接俩个关系的键
注意注意SQL不区分大小写，当有些数据库会区分大小写，建议关键字大写。
关系运算
交集、并集、差、笛卡尔积
并( U )、交( ∩ )、差(-)、笛卡尔积( × )
比较运算（>,<,>=,<=,≠,=）
大于( ＞)、小于(＜)、等于( = )、大于等于( ≥ )、小于等于( ≤ )、不等于( ≠ )
逻辑运算
与( ^ )、或( V )、非( ┐);
关系运算符
选择( σ ) 、投影( π )、∞ (连接)、 除( ÷ )
-
选择( σ )与where一个意思 σ (选择条件) (表名)
-
投影( π )与select一个意思 π投影名 (表名)
-
连接( ∞ )
-
基本语法是： π列名1[，列名2…]（取别名）σ (选择条件（表1（∞表2…）)
在连接符下边可以写连接条件
特殊连接
数据类型
| 名称 | 类型 | 说明 |
|---|---|---|
| int | 整型 | 4个字节范围在±21亿 |
| bigint | 长整型 | 8字节±922亿 |
| real | 浮点型 | 4字节±1038 |
| float | 浮点型 | 4字节±1038 |
| double | 浮点型 | 8字节±10308 |
| decimal(m,n) | 高精度小数 | m代表一个有m位数，其中n代表n位小数，整数部分m-n默认(10,0) |
| char(n) | 定长字符串 | 存储指定长度的字符串，char(10)表示固定储存100位字符 |
| varchar(n) | 变字符串 | 储存可变的字符串，varchar(20)可以储存0~20位字符 |
| boolean | 布尔类型 | Ture或False |
| date | 日期类型 | 存储日期，2022-06-14 |
| time | 时间类型 | 存储时间，20:18:19 |
| datetime | 日期和时间类型 | 存储时间+日期,2022-06-14 20:18:19 |
| enum | 枚举类型 | 例如enum(‘男’,‘女’)no null;可以选择性别 |
日期类型的区别
| 类型名称 | 日期格式 | 日期范围 | 存储需求 |
|---|---|---|---|
| YEAR | YYYY | 1901 ~ 2155 | 1 个字节 |
| TIME | HH:MM:SS | -838:59:59 ~ 838:59:59 | 3 个字节 |
| DATE | YYYY-MM-DD | 1000-01-01 ~ 9999-12-3 | 3 个字节 |
| DATETIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | 8 个字节 |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC | 4 个字节 |
对于字符类型的补充
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| TINYTEXT | 非常小的非二进制字符串 | L+1字节，在此，L<2^8 |
| TEXT | 小的非二进制字符串 | L+2字节，在此，L<2^16 |
特殊字符串需要用到\转义，比如 ’ ’ \ " "
SQL语言
结构化查询语言，使用sql语言操作数据库系统，添加，删除，修改，查询数据，对数据库进行修改，维护。sql语言在不同的数据库中有许多不同的扩展 ^因为MySQL是本人课程，文中都是MySQL的标准^
SQL语言定义了这么几种操作数据库的能力 ：
DDL : 数据定义语言(建表,定义数据类型以及修改)
DML : 数据操作语言(insert,delete,update进行数据的修改)
DQL ： 数据查询语言(select,查询数据等等)
TCL : 事务控制语言
DCL : 数据数据语言
CCL : 指针控制语言
后面三种后面补，好像还没学
数据库中表的每一行称为记录（Record），记录是一个逻辑意义上的数据。
表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。
NULL不等于””
关系数据库中，一张表是一个关系，关系是通过==主键和外键==来维护的。
每张表之间存在一对多，多对多,一对一的关系，例如一个学校有很多学生，一个老师教很多班级，一个学生在一个班级里面，有 很多老师教这个班的不同课程
完整性校验
DBMS插入语句时会进行规则完整性校验
-
域完整性
字段规定的数据类型，格式，值域范围，是否允许为空
-
实体完整性
主关键字对应的主属性不为空不重复
-
参照完整性
-
-
插入约束
插入记录的时候，检查外键是否在主表存在，存在允许插入，不存在则拒绝插入
-
删除约束
删除主表的记录时，表中被删除的记录作为外键的值，相关表的记录也被删除，级联删除。
-
更新约束
更新主表中关键字的值时，相关表中作为外键的值随之修改，级联更新。
-
-
用户定义的完整性
-
- 对于not null约束是否是非空
- 对于unique约束是否唯一
- 对于有值域约束的属性值是否在值域范围内
关系模型数据依赖以及范式
第一范式
- 数据组的每个属性只包含一个值
- 关系中的每个数组包含相同数量的值
- 关系中每个数组的一定不能相同
第二范式
第二范式(2NF)规定关系在第一范式中，关系的所有属性依赖于整个候选键
第三范式
第三范式(3NF)关系在第二范式中,非键属性相互之间无关，必须依赖于键。
理想的设计目标是规范话规则存储数据，实际工作中却经常要违反第二第三范式，解规范化。
2. DDL
comment 语句是注释的意思
例如
comment="这是注释内容"#创建表或字段说明文字
CAHRSET=字符集;
2.1创建库与删库
create database datebase_name;#创建一个数据库
create database database_name default charset utf8_general collate utf8_general_ci;#设置编码字符集
#删除数据库
DROP DATABASE database_name;
#修改字符集
alter database schooldb CHARSET utf8;
2.2建表与删表
建表前先了解一下完整性约束
| 约束 | 说明 |
|---|---|
| PRIMARY KEY | 标识该属性为主键，唯一标识对应的元组 |
| POREIGN KEY | 标识该属性为外键，是与之联系与其他的表主键 |
| NOT NULL | 标识属性不为空 |
| UNIQUE | 标识属性唯一 |
| AUTO_INCREMENT | 标识该属性的值自动增加，是mysql的特色 |
| DEFAULT | 设置属性默认值 |
完整性约束对字段现在，要求用户对属性进行操作符合特定要求，不符合则不执行用户操作。保证数据库的数据完整性。
在命令行执行sql语句的时候先转到要使用的数据库
use database_name;
创建表
create table table_name (
字段1 类型1 约束1.....,
字段2 类型2 约束2......,
..........
);
create table table_name(
字段名1 字段类型 not null ,
字段名 字段类型 DEFAULT 值,
字段名 字段类型 PRIMARY KEY，#主键
字段名 字段类型 UNIQUE,#唯一
CONSTRAINT 约束名 FOREIGN KEY(字段名) REFERENCES 主表(引用列名)
);
create table [if not exists] table_name(……);
#例子
create table student(
name varchar(20) NOT NULL ,
id int(10) NOT NULL PRIMARY KEY,
age INT(5) NOT NULL
)
#其中外键约束是这样的
ALTER TABLE student #在表格student中修改
add constraint st_class_id ##添加一个字段
FOREIGN KEY (class_id) #外键约束class_id
REFERENCES classes(id); #关联到classes表格的id字段上
#删除外键
ALTER TABLE student
DROP FOREIGN KEY st_class_id;#(仅仅是删除外键约束并没有删除这个字段)
#主键约束
PRIMARYKEY(id)#可以放到建表后边约束
复制的方式建表
#只复制结构以及约束不复制数据
CREATE TABLE new_table LIKE old_table;
#只复制数据和结构不复制约束
CREATE TABLE new_table AS SELECT * FROM old_table;
#两个表结构一样
insert into new_table select * from old_table;
#结构不一样
insert into new_table(字段1，字段2……) select 字段1，字段2…… from old_table;
| name | id | sex | age | class_id |
|---|---|---|---|---|
| yxq | 10001 | 男 | 19 | 16 |
| yee | 10002 | 男 | 20 | 16 |
| kkk | 10003 | 女 | 21 | 16 |
| 小明 | 10004 | 男 | 20 | 24 |
| 小红 | 10005 | 女 | 19 | 15 |
CREATE table new_stu like students;
insert into new_stu SELECT * from students where class_id=16;
| name | id | sex | age | class_id |
|---|---|---|---|---|
| yxq | 10001 | 男 | 19 | 16 |
| yee | 10002 | 男 | 20 | 16 |
| kkk | 10003 | 女 | 21 | 16 |
删除表
DROP TABLE [IF EXISTS] table_name;
修改表名
RENAME TABLE old_name to new_name;
2.3 修改表的结构
2.3.1 修改表名
ALTER TABLE old_name RENAME [To] new_name;
#仅修改表名
#to为可选参数，不影响结果
2.3.2 添加字段
ALTER TABLE table_name add 字段名 约束;
ALTER TABLE table_name add [column] 字段名 字段约束;
#例如
ALTER table student add sex enum('男','女') not null;
ALTER table student add name varchar(11) not null;
alter table student add brith date not null;
#也可以添加多个字段
alter table table_name add(
new_name varchar(10),
new_id int(10) NOT NULL
);
2.3.3修改字段
#修改字段名
#设置新的字段约束#可以设置相同的约束不改变结构
ALTER TABLE table_name CHANGE [column] old_name new_name varchar(11) DEFAULT NULL;#(设置默认空值)
#更改数据类型
ALTER TABLE table_name ALTER COLUMN lie_name int(10);#把lie_name 的类型改为int(10);
2.3.4 删除字段
#删除列名的列
ALTER TABLE table_name DROP COLUMN lie_name;
ALTER TABLE table_name DROP lie_name;
2.3.5 约束添加
约束分为俩种，一个是列级约束，在定义列的后面添加约束，支持:非空、默认、主键、唯一,不能起约束名，可追加多个空格隔开，无顺序要求。表级约束是在定义所有字段后，在末尾，括号钱定义的约束支持:主键、唯一、外键，可以去约束名，mysql对主键无效
#其中外键约束是这样的
ALTER TABLE student #在表格student中修改
add [constraint st_class_id] #外键名
FOREIGN KEY (class_id) #外键约束class_id
REFERENCES classes(id); #关联到classes表格的id字段上
#删除外键
ALTER TABLE student
DROP FOREIGN KEY st_class_id;#(仅仅是删除外键约束并没有删除这个字段)
#主键约束 #表级
ALTER TABLE table_name add [constraint 约束名] PRIMARY KEY table_name(lie_name)
#列级
alter table table_name modify column 字段名 字段类型 PRIMARY KEY;
#删除主键 不是删除字段
alter table table_name drop PRIMARY KEY;
#修改列的类型或约束
alter table table_name modify [column] lie_name 新类型 [新约束] #不输入约束则无约束
#设置默认值
alter table table_name modify [column] lie_name int(10) default 1;
#alter table table_name modify [column] lie_name 数据类型 default 值;
2.3.6 索引
为了提高查找效率，可以设置索引
alter table table_name add index idx_name(lie_name);
alter table table_name add index idx_name(lie1_name,lie2_name);
#主键索引效率最高，主键唯一
#使用唯一做索引的效率和主键一样
#唯一索引
alter table table_name add unique uni_name(lie_name);
#创建唯一约束
alter table table_name add [constraint uni_name ] unique (lie_name);
#删除索引
drop index table_name.index_name;
#索引创建
create index index_name on table_name;
#单列索引
create index index_name on tabel_name (lie_name);
#唯一索引
create unique index index_anem on table_name (lie_name);
#聚簇索引####表中两个或者更多的列
create index index_naem on table_name(lie1,lie2...);
2.3.7 指令
show databases;#所有数据库
show create database ku_name;#查看定义
select database();#查看当前使用的数据库
create database 数据库名;#创建库
use 库名;#转移使用库名
drop database 库名;#删库跑路，慎用
show tables;#查看所有表
#查看表相关信息
DESC table_name ;
DESCRIBE table_name ;
#查看建表sql语句
show create table table_name;
#修改表
#添加首列
alter table_name add column lie_name 类型 FIRST;
#添加在字段名1之后
alter table_name add column lie_name 类型 字段名1;
#删表
DROP table if exists table_name ;
TRUNCATE table if exists table_name ;
3.DML
数据操作语言，修改、添加，删除数据库数据，检查数据完整性
3.1 INSERT
添加语句，往数据库添加数据
INSERT INTO table_name (lie_name1,lie_name2,……)values (值1,值2……);
INSERT INTO table_name (lie_name1,lie_name2,……)values (值1,值2……)[where 语句];
#不写列名的话
INSERT INTO table_name values(value1,value2....);#需要列出所有列的信息
3.2 UPDATE
修改数据
UPDATE table_name SET lie_name1=值1,lie_name2=值2....where...(筛选语句)..;
#若无where条件则所有数据全变
UPDATE table_name SET lie_name1=值1,lie_name2=值2....[where 条件 ] [order by ][ limit n,m]
#修改学习成绩<60的成绩为60分
update students set score=60 where score<60;
#修改三个最接近60分的为60分(捞人)
update students set score=60
where score<60
order by score desc
limit 0,3
3.3 DELETE
删除数据(元组)
DELETE FROM table_name where ....;
#无where则全部删除
delete from table_name [where 条件 ] [order by ][ limit n,m]
#比如我想删除学生成绩最差的三个人
delete from students order by score desc limit 0,3;
#删除成绩小于60分的
delete from students where score <60;
#删除小于60分的三个人
delete from students where score <60 order by score desc limit 0，3;
4.DQL
数据查询语言
运算符
| 算数运算符 | 说明 |
|---|---|
| + | 加号 |
| - | 减号 |
| * | 乘号 |
| / | 除号 |
| % | 求余 |
sql语言支持位运算(如>>,<<,&,|,~,^);
| 比较运算符 | 说明 |
|---|---|
| = | 相等 |
| <=> | 安全等于（等于号跟赋值语句一样我是真的没见过）js甚至有===号 |
| != | 不等于 |
| <> | 不等于 |
| > | 大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| !> | 不大于 |
| !< | 不小于 |
| LIKE | 表示操作数与模式匹配 |
| IS NULL | 判断是否是NULL |
| LEAST | 存在俩个以上多个参数时返回最小值 |
| GREATEST | 存在俩个以上多个参数时返回最大值 |
| REGEXP | 正则表达式匹配 |
基本查询
SELECT语句顺序
SELECT 字段1,字段2.... #如果是全选则可以用 *
FROM table_name[,table_name2] #选择表
[where ...[or] [and][between]]
[group by ...]
[order by [ASC][DESC]]#ASC升#DESC降
[having ]
这里是一个学生数据表格
create table classes(
id int(5) PRIMARY KEY AUTO_INCREMENT,
class_name varchar(20),
kemu enum('文科','理科','艺术','体育')
);
insert into classes (id,class_name,kemu) values (16,'十六班','理科'),(15,'十五班 ','文科'),(24,'二十四班','艺术'),(8,'八班','体育');
create table students(
name varchar(20) not null,
id int(10) PRIMARY KEY AUTO_INCREMENT,
sex enum('男','女'),
age int(5),
class_id int(5),
FOREIGN KEY(class_id) REFERENCES classes(id)
);
#或用语句单独设置外键
#alter table students add FOREIGN KEY class_id REFERENCES classes(id);
insert into students (name,id,sex,age,class_id) values('yxq',10001,'男',19,16),('yee',10002,'男',20,16),('kkk',10003,'女',21,16),('小明',10004,'男',20,24),('小红',10005,'女',19,15);
create table kechen(
k_id int(10) not null,
k_name varchar(20) not null,
k_score int(5)
);
alter table kechen add foreign key(k_id) references students(id);
insert into kechen(k_id,k_name,k_score)values(10001,'C语言',97),(10001,'英语',54);
insert into kechen(k_id,k_name,k_score)values(10003,'英语',99),(10003,'高等数学',59),(10001,'Python',79),(10001,'数据结构',81),(10002,'体育',61),(10004,'美术','90');
班级表
| id | class_name | kemu |
|---|---|---|
| 15 | 十五班 | 文科 |
| 16 | 十六班 | 理科 |
| 24 | 二十四班 | 艺术 |
| 8 | 八班 | 体育 |
学生信息表
| name | id | sex | age | class_id |
|---|---|---|---|---|
| yxq | 10001 | 男 | 19 | 16 |
| yee | 10002 | 男 | 20 | 16 |
| kkk | 10003 | 女 | 21 | 16 |
| 小明 | 10004 | 男 | 20 | 24 |
| 小红 | 10005 | 女 | 19 | 15 |
分数表
| k_id | k_name | k_score |
|---|---|---|
| 10001 | C语言 | 97 |
| 10001 | 英语 | 54 |
| 10003 | 英语 | 99 |
| 10003 | 高等数学 | 59 |
| 10001 | Python | 79 |
| 10001 | 数据结构 | 81 |
| 10002 | 体育 | 61 |
| 10004 | 美术 | 90 |
select DISTINCT *
from students,classes,kechen
WHERE students.class_id=classes.id AND kechen.k_id=students.id;#三张表连接查询
测试一下
| name | id | sex | age | class_id | id | class_name | kemu | k_id | k_name | k_score |
|---|---|---|---|---|---|---|---|---|---|---|
| yxq | 10001 | 男 | 19 | 16 | 16 | 十六班 | 理科 | 10001 | C语言 | 97 |
| yxq | 10001 | 男 | 19 | 16 | 16 | 十六班 | 理科 | 10001 | 英语 | 54 |
| yxq | 10001 | 男 | 19 | 16 | 16 | 十六班 | 理科 | 10001 | Python | 79 |
| yxq | 10001 | 男 | 19 | 16 | 16 | 十六班 | 理科 | 10001 | 数据结构 | 81 |
| yee | 10002 | 男 | 20 | 16 | 16 | 十六班 | 理科 | 10002 | 体育 | 61 |
| kkk | 10003 | 女 | 21 | 16 | 16 | 十六班 | 理科 | 10003 | 英语 | 99 |
| kkk | 10003 | 女 | 21 | 16 | 16 | 十六班 | 理科 | 10003 | 高等数学 | 59 |
| 小明 | 10004 | 男 | 20 | 24 | 24 | 二十四班 | 艺术 | 10004 | 美术 | 90 |
seltct语句
stlect 字段1(列名1).....
form table_name1[,table_name2,.....];
#例子
select *
from students;
| name | id | sex | age | class_id |
|---|---|---|---|---|
| yxq | 10001 | 男 | 19 | 16 |
| yee | 10002 | 男 | 20 | 16 |
| kkk | 10003 | 女 | 21 | 16 |
| 小明 | 10004 | 男 | 20 | 24 |
| 小红 | 10005 | 女 | 19 | 15 |
#例子2
select name,sex,age
from students;
| name | sex | age |
|---|---|---|
| yxq | 男 | 19 |
| yee | 男 | 20 |
| kkk | 女 | 21 |
| 小明 | 男 | 20 |
| 小红 | 女 | 19 |
CONCAT函数
将字段连在一起
SELECT class_name,CONCAT(id,class_name,kemu)
from classes
DISTINCT关键字
去掉重复的列
stlect DISTINCT *
from classes,students
where ....
#即有一些时候连接俩个表的时候，有一些列是一摸一样的，这个时候我们并不需要显示它，可以通过选择投影的方式去选择不需要的列，也可以在select后面加上distinct，去掉重复的列.
**WHERE**关键字
来点高级的，比如我想看到男生的信息
select *
from students
where sex='男';
| name | id | sex | age | class_id |
|---|---|---|---|---|
| yxq | 10001 | 男 | 19 | 16 |
| yee | 10002 | 男 | 20 | 16 |
| 小明 | 10004 | 男 | 20 | 24 |
逻辑运算
学过c\c++,java、python等等编程语言的都了解逻辑运算
| 关键字 | 含义 |
|---|---|
| AND | 表示且，同c的&&和python的and |
| OR | 表示或,同c的||和python的or ///and的优先级高于or |
| IN | 表示存在一个或一个数列里面，同python的in |
| NOT | 表示非 |
| ANY | 表示任意一个 |
| ALL | 表示所有 |
| EXISTS | 表示存在 |
| BETWEEN | 表示一定范围内 |
| SOME | 表示某些为真 |
| UNIQUE | 搜索唯一性（无重复项目） |
**AND和OR和IN**关键字
如果只想看到10002号到10004号的学生信息
select *
from students
where id<=10004 and id>=10002;
select *
from students
where id=10002 or id=10003 or id=10004;
select *
from students
where id IN (10002,10003,10004);
select *
from students
where id NOT IN (10001,10005,10006);
select *
from students
where id IN (
SELECT id
FROM students
WHERE id between 10002 and 10004
);
select *
from students
where id<>10001 or id!=10005 or id<>10006;
| name | id | sex | age | class_id |
|---|---|---|---|---|
| yee | 10002 | 男 | 20 | 16 |
| kkk | 10003 | 女 | 21 | 16 |
| 小明 | 10004 | 男 | 20 | 24 |
**BETWEEN AND**关键字
写两个小于大于号加一个and有点麻烦，有一种方式可以快速去区间值
select *
from students
where id between 10002 and 10004;#效果和上表一样，一般小的放在前面
如果想查询19岁到21岁的学生
insert into students(name,id,sex,age,class_id)values('xt',10006,'女',18,8);
select *
from students
where age BETWEEN 19 and 21;
| name | id | sex | age | class_id |
|---|---|---|---|---|
| yee | 10002 | 男 | 20 | 16 |
| kkk | 10003 | 女 | 21 | 16 |
| 小明 | 10004 | 男 | 20 | 24 |
新插入进来的数据没有显示，因为她 的年龄小于19岁
select *
from students
where age <=18;
| name | id | sex | age | class_id |
|---|---|---|---|---|
| xt | 10006 | 女 | 18 | 8 |
LIKE 关键字
#语法
[not]like 'str'[escape'换码字符']
%号匹配任意数目的字符
_号匹配一个字符
例如匹配一个姓张的同学名字，名字可能有俩个或者三个
where like '张%'或者where like '张 _ _'
查询一段话里面有我喜欢你
where str like '%我喜欢你%'
查询第二个字是哈
where str like '_哈%'
如果想匹配_和%的话，需要加上转义符\，比如\_或者\%。
指定一个转义字符可以使用escape
where str like '我^_哈哈哈哈' escape'^'匹配我_哈哈哈哈这一串字符。
EXISTS运算符
类似IN，测试子查询中有没有这个值
select *
from students
where EXISTS (
SELECT id
FROM students
WHERE id between 10002 and 10004
);#若存在则输出，不存在则不输出
函数
sql语句里面内置了许多函数，可以在查询或者写存储过程的时候使用
函数均可嵌套使用
| 函数 | 说明 |
|---|---|
| AVG | 求平均数 |
| SUM | 求和 |
| count | 计数 |
| MAX | 计算最大值 |
| MIN | 计算最小值 |
| MOD | 两个参数求a/b余数 |
| ROUND | 单个参数，四舍五入整数，两个参数，第二个参数保留位数 |
| SQRT | 二次方根 |
类似的函数还有
concat(str1,str2) 连接 拼接两个字符串
upper(str) 大写 字符串大写
lower(str) 小写 字符串小写
LENGTH(str) 长度 字符串长度
SUBSTRING(str,start,end) 截取 截取字符串,start开始,end结束。
LEFT(str,len) 截取 从左边开始截取字符串
RIGHT(str,len) 截取 从右边开始截取字符串
函数使用例子
select avg(age)
from students;
#输出
#avg(age)
#19.5000
select sum(k_score)
from kechen;
#sum(k_score)
#620
select students.name,sum(k_score)
from kechen,students
where kechen.k_id=students.id
group BY k_id;
#计算每个人的总分
name sun(k_score)
yxq 311
kkk 158
yee 61
小明 90
select students.name,kechen.k_score
from students,kechen
where students.id=kechen.k_id and kechen.k_score=(
select max(kechen.k_score)
from kechen );
#求最大值的姓名和分数
name k_score
kkk 99
时间函数
| 时间函数 | 用法 | 说明 |
|---|---|---|
| NOW | 无参数 | 显示现在的时间，日期加时分秒 |
| CURDATE | 无参数 | 返回当前日期 |
| ADDDATE | 增加时间，两个参数，前是日期，后输入数字默认天数，可重载时，分，月 | 返回增加后的时间 |
| ADDTIME | 添加到expr2 到 expr1 并返回结果。 expr1 是一个时间或日期时间表达式，expr2是一个时间表达式。 | 返回增加后的时间 |
| CURTIME | 无参数 | 返回时间，时分秒 |
| DATE | 输入一个参数是日期和时分秒 | 提取日期 |
| DATEDIFF | 输入两个时间参数，有日期 | 返回两者的差值 |
| YESR | 输入一个日期 | 返回年份 |
| YESRWEEK | 输入一个日期 | 返回年份加星期 |
| WEEK | 输入日期 | 返回星期数 |
| WEEKDAY | 输入日期 | 返回星期几 |
| WEEKOFYEAR | 输入日期 | 返回第几周 |
| TIME | 输入时间 | 返回时间部分 |
| MONTH | 输入时间 | 返回月份 |
| MONTHNAME | 输入时间 | 返回月份名 |
| MINUTE | 输入时间 | 返回分钟数 |
| HOUR | 输入时间 | 返回小时 |
SELECT ADDDATE(NOW(),55);
>2022-08-10 20:52:28
分组
在查询的时候，可能需要对数据进行分组显示，让数据直观显示，或者进行sum和count等等计算。
#例如我想查询各个班的人数
select class_id,count(class_id)
from students
group by class_id
除了count外，max，min，avg,sum等函数都可以在分组中实现.
#对多个字段进行分组
select class_id,age,count(class_id)
from students
group by class_id,age
HAVING关键字
有时候我们分完组后，有一些数据不想要，我们可以使用having关键字实现筛选
#比如上面的分组中我不想看到20岁以下的人
select class_id,age,count(class_id)
from students
group by class_id,age
HAVING( not age<20)
#或者
select class_id,age,count(class_id)
from students
group by class_id,age
HAVING( age>=20)
#效果都是一样的
GROUP_CONCAT()函数
当我们想分完组显示所有信息的时候，可以使用GROUP_CONCAT()函数将所有的信息拼接成一个字符串
select class_id,count(class_id),GROUP_CONCAT(name)
from students
group by class_id
WITH ROLLUP函数
统计整和元素的最大值和最小值在新的元组显示
这个我不好说 ，我不太明白，详细的话可以去查查使用
select max(k_name),max(k_id),max(k_score),GROUP_CONCAT(k_id),min(k_score)
from kechen
group by k_name
with ROLLUP
WITH CUBE函数
CUBE生成的结果集显示了所选列中值的所有组合的聚合。
ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合。
排序
排序基本语法是这样的
select *
from table[,tbale2,...]
order by 条件[,条件2.....] [DESC/ASC]#前者降序，后者升序，后者可省略，默认后者
#比如查找课程分数，一条一条记录有些乱，想让不同的学生的分数在一起显示
select *
from kechen
order by k_id;
#效果一样
select *
from kechen
order by k_id asc;
#如果我想反着顺序来看
select *
from kechen
order by k_id desc;
#多个排序条件查询
select *
from kechen
order by k_id ,k_score DESC;#id正序，分数降序
如果我想查看每个科目的最高分
select *
from kechen
group by k_name
order by k_score desc;
上面的是错误示例以示警戒，我决定不删除
select max(k_score),max(k_id)
from kechen
group by k_name
AS关键字
AS关键字是更改名字的关键字
有时候查询用到了分组求平均值等等，会另外取一列查看，此时的列名是空白，这个时候就要修改列名
SELECT k_id,AVG(k_score)
from kechen
group by k_id;
看列名有些不合适，使用as可以更改别名
SELECT k_id,AVG(k_score) as 平均值
from kechen
group by k_id;
#或者省略as
SELECT k_id,AVG(k_score) 平均值
from kechen
group by k_id;
#效果是一样的
也可以修改表名，在from语句后，一般用于子查询
LIMIT关键字
limit关键字可以选择查询的第几条到第几条的数据，一般运用于数据量太大把他分开几页查询，例如有一个十万信息的数据库，我每次只显示10条，点下一页显示下一个十条
select * from table limit n,m;#查找n+1数据开始的m条数据如n=5,m=10，查询6~15
select * from table limit 22,-1;#此时查询从23 开始一直到结束23~last
#查询前n条数据
select * from table limit n;
select * from table limit 0 , n；
#以上 四条查询语句也可以变成下面的方式呈现
select * from table limit m offset n;
select * from table limit -1 offset 22;
select * from table limit n offset 0;
#仅仅是调换位置而已.
#这样就能实现比如看网络小说，章节动不动几千章，很难翻到，使用这条语句能把几千章的分为几十章节分页查询，方便得多
连接
连接是指把两张表或者多张表连接在一起查询
一般连接的表里都会有相同的字段才能连接，不然就是笛卡尔积连接，即交叉连接
连接分为条件连接、等值连接/内连接，自然连接，外连接这几种。
如果在连接的时候遇到了同名列，则用table.name的方式去使用列名
笛卡尔积(交叉连接)
select *
from kechen,students
select *
from kechen INNER join students
select *
from kechen CROSS join students
select *
from kechen join students
#以上四种方法皆可行
#笛卡尔积的意思是把每张表的每一行（元组）全部拼接起来成为一行新的元组。//在大多数实际生产中会产生大量无效数据，连接后的行数=连接前的每张表的行数相乘，比如三张表行数分别为：3，4，5，连接后的行数为3*4*5=60
外连接
左外连接
示例
select *
from students
LEFT JOIN kechen on students.id=kechen.k_id ;
右外连接
示例
select *
from kechen
RIGHT JOIN students on students.id=kechen.k_id ;
全外连接
MySQL不支持完全外连接
select kechen.*,students.*
from students
FULL JOIN kechen on students.id=kechen.k_id ;
你麻麻的我找了半天bug结果是因为你不支持
#用其他办法去实现，union 合并
(select kechen.*,students.*
from students
left JOIN kechen on students.id=kechen.k_id )
UNION(
select kechen.*,students.*
from students
left JOIN kechen on students.id=kechen.k_id )
;
等值连接/内连接
语法
select *
from table_name1
INNER JOIN table_name2 ON 条件
[INNER JOIN table_name3 ON 条件....]
[where.....order by....group by...limit]
#例子
select *
from students
INNER JOIN kechen
ON kechen.k_id=students.id;
#上面的方法和这个是一样的结果
select *
from students ,kechen
where k_id=id
自然连接
select *
from students
NATURAL JOIN kechen ;
找出相同的值去连接表，当我把kechen表里的k_id 改成了id的时候
连接效果如下
如果没有相同的值，那么自然连接就是笛卡尔积.
子查询
子查询查询语句嵌套另一个查询语句，一般用于自身查询，select中先计算子查询，子查询是外查询的条件
可嵌套select ，detele，update语句。可以多层嵌套
ANY，SOME关键字
表示any后的子查询与any前的值至少有一项匹配，返回true，否则为flase。
some和any的意思一样
select *
from student
where score> ANY(select score from student where class=2)
ALL关键字
与any对立，满足所有条件
#查询成绩最高的人且为2班
select *
from student
where score> all(select score from student where class=2)
EXISTS关键字
存在的意思,存在则true否则false ，可以与not 使用 not exists
select *
from kechen
where k_id exists (select k_id from kechen where k_score=90);
IN关键字
意思也是存在，in返回一个列表存在则true否则false ，可以与not 使用 not in;
select *
from kechen
where k_id in(select k_id from kechen where k_score>90);
带比较符的子查询（>,>=,<,<=,<>,!=,=）
select *
from kechen
where k_score>(select avg(k_score) from kechen where k_score=90);#举的例子不太好，就差不多这个意思
合并查询
mysql只支持UNION
#不保留重复元组
select lie_name1[,lie_name2..] from table1 [where....group by...order by....limit...h]
UNION
select lie_name1[,lie_name2..] from table2 [where....group by...order by....limit...h]
#保留重复的元组
select lie_name1[,lie_name2..] from table1 [where....group by...order by....limit...h]
UNION ALL
select lie_name1[,lie_name2..] from table2 [where....group by...order by....limit...h]
#与之 union 相同的有，全外连接
#mysql不支持
select lie_name1[,lie_name2..] from table1
full join table2 on
[where....group by...order by....limit...h]
EXCEPT形成差集mysql不支持
select lie_name1[,lie_name2..] from table1 [where....group by...order by....limit...h]
EXCEPT
select lie_name1[,lie_name2..] from table2 [where....group by...order by....limit...h]
#与之相同的有
select lie_name1[,lie_name2..] from table1 [where....group by...order by....limit...h]
NOT IN
(
select lie_name1[,lie_name2..] from table2 [where....group by...order by....limit...h]
)
交集查询INTERSECTmysql不支持
select lie_name1[,lie_name2..] from table1 [where....group by...order by....limit...h]
INTERSECT
select lie_name1[,lie_name2..] from table2 [where....group by...order by....limit...h]
#可以用内连接替代
select lieming from table1
join table2 on 连接条件 [where...order...group..limit..]
#或
select lieming from table1
inner join table2 on 连接条件 [where...order...group..limit..]
对称差(去掉交集的部分)
SELECT table1.* FROM table1
LEFT JOIN table2
ON table1.model = table2.model
WHERE table2.model is NULL
UNION
SELECT table2.* FROM table1
RIGHT JOIN table2
ON table1.model = table2.model
WHERE table1.model is NULL
正则表达式查询
属性名 REGEXP ‘正则表达式’
select *
from table
where lie_name regexp '正则表达式'
[group by ...order by... limit]
关于正则表达式正则表达式快速入门_Yee_1819的博客-CSDN博客
用户变量
声明一个全局变量在存储过程或者触发器，存储函数中使用
set @name {=|:=} value [,@name2 {=|:=} value....];
value的值可以来这存储函数或者select语句或系统函数
select @name:=value[,@name2=value2...]
#例如
set @pi=3.1415;
select @p=3.1415926;
存储过程
当需要大量重复修改、删除、查询数据的时候，一步一步的进行同样的操作实在是太浪费时间和精力，所以有了存储过程和函数。
存储过程是一组为了完成特定功能的sql语句，使用存储过程的目的是将常用或复杂的sql语句写出来，使用的时候直接调用就能达到目的。存储过程有着运行效率高，降低网络通信量的优点，还有着类似封装的优点，用户的规则发生改变时，只需要改变存储过程，不需要修改其他程序，易于维护。
#创建存储过程
create procedure cc_name([in/out/inout 变量名 变量type [in/out/inout 变量名 变量type,...... ] ])
[characteristic ....]#指明存储过程的特性
;
begin
select语句 [#和 流程控制语句]
end;
#in 表示输入参数
#out 表示输出参数
#inout 表示输入输出参数
DELIMITER new_end
#设定新的语句结束符
默认结束符为 ;,避免产生冲突，在创建存储过程前修改结束符，以新设定的结束符结束存储过程，在修改回来存储过程结束符。
DELIMITER ;
[characteristic ....]#指明存储过程的特性
create procedure ccgc_name(in a char(10),out b varchar(20),inout c int(20))
language sql #指明存储过程由sql组成
[not] deterministic #确认执行的结果是否确认，默认not deterministic 即输入相同的值，是否输出相同的值，有not是不确定输出的值相同
contains sql/// no sql ///read sql data///modifies sql data
#表示sql语言的限制，1.包含sql，不包含读写sql语句，2表示不包含sql语句，3表示包含读sql语句，4表示包含写sql的语句 默认1
sql security {definer|invoker}指明存储过程的许可是用户（1）还是调用者（2），默认1
comment 'string'#注释
显示存储过程
show create procedure cc_name;
显示存储过程状态
show procedure status like cc_name;
删除存储过程
DROP PROCEDURE [if exists] cc_name;#if exists 防止删除不存在的存储过程出现错误
修改存储过程的特性
ALTER procedure cc_name [characteristic...];
使用存储过程
call cc_name([字段1,字段2.....])
局部变量
DECLARE var_name[,....] type [DEFAULE value]
-
DECLARE声明局部变量
-
var_name 指定变量名
-
type 指定变量类型
-
DEFAULT语句，指定默认初始值，未指定默认NULL
局部函数仅在begin…end中使用，必须在存储过程开头声明,命名不需要@开头
#赋值语句
set var_name=value[,var_name2=value2....]
value和用户变量一样可以是多种形式
select 字段1[,字段2...] into var_name1[,var_name2....] table_name [where 筛选语句];
#例如
declare p_name varchar(20);
select table_字段 into p_name from table_name where table_id=8;
游标
执行完一句selecte…into 语句只返回一行数据，可以很好的被存储函数处理 。但运用select语句的时候，返回的是一组数据集合，这个数据集合可能拥有多行数据，这些数据不能很好的被处理，人们为了处理这个情况，创建了游标这个概念，使用游标，根据需要滚动或者浏览其中的数据。游标是一个被select检索出来的数据集。
游标只能运用于存储过程和存储函数中，不能单独的在查询操作中使用。
每个存储过程或存储函数可以拥有多个游标，但 每个游标的名字不能相同
游标是一个被select检索出来的数据集，并不是一个select语句。
声明游标
DECLARE 游标name CURSOR FOR select语句
#例如
declare stu_name_and_age cursor for select name,age from students;
打开游标
在使用游标前，需要打开游标，实际是将游标连接到select返回的结果集中语法如下
open 游标name;
#例如
open stu_name_and_age;
每次打开游标的时候，由于用户或者应用程序更新了数据，每次打开游标的时候可能会改变结果集
读取数据
FETCH 游标name INTO 字段1[,字段2....]
#例如
fetch stu_name_and_age into name,age;
#打开游标的时候的变量数目与类型必须与声明游标的选择列数相同
#游标相当于一个指针，指向当行的数据
关闭游标
CLOSE 游标name;
结束使用游标时，必须关闭游标。再次使用不用重新打开,若未关闭游标，在end时mysql会自动关闭
流程控制
流程控制
IF条件控制语句
IF 判断语句 THEN
select语句
[ELSEIF [判断语句] select语句]
[ELSE select语句]
END IF;
#例如
set @a=1;
IF @a=1 THEN
@a=@a+1
select * from students;
ELSE select * classes;
END IF;
sql里面的if语句不像c/c++用括号控制语句也不想python用缩进来判断结束，而是用了一个结束符 END来判断IF语句是否结束.
CASE条件控制语句
CASE case_value #表示判断的值或表达式##类似switch...case
WHEN 判断1 THEN select语句 ;
[WHEN 判断2 THEN select语句 ;]
[.....]
[ELSE select语句 ;]#若每次结果都不对则执行这段语句
END CASE ;
#例如
CASE var
when 1 select '1';
when 2 select '2';
else select '都不是'
END CASE;
CASE 的另外一种表达形式
CASE
WHEN 判断语句或表达式 THEN SELECT语句;
[ WHEN 判断语句或表达式 THEN SELECT语句;]
ELSE select语句;
END CASE;
存储过程的case语句与存储函数的case的不同之处
存储过程case语句中不能有else null子句，用end case替代end表示终止.
WHERE循环控制语句
[begin_label:] while 判断语句 DO
select语句+流程过程
END while [end_label]
#[begin_label:]和[end_label]是while循环的标注，名字必须相同并成对出现。相当于给循环起名
REPEAT循环控制语句
[begin_label:] REPEAT select语句+流程过程
UNTIL 判断语句
END REPEAT [end_label];#类似while和do...while的区别repeat循环会先执行后判断，while会先判断后执行。
LOOP循环控制语句
loop循环与其他循环不一样的是没有判断退出循环的语句。
[begin_label:] LOOP
select语句+流程过程
END LOOP [end_label];
你以为没有条件判断会直接死循环吗？不可能！
LEAVE和ITERATE语句
学过c/c++或者python或者java的都知道循环语句中会有break;和continue;语句，那么
LEAVE
LEAVE label;
#类似 break label;
label是循环语句的标识,举例
this_xunhuan: LOOP
DECLARE i int DEFAULT 0;
set i=i+1;
if i=100 then leave this_xunhuan;
end if;
end loop this_xunhuan;
当i循环加到了100的时候就通过label退出了循环.ps:在while循环和REPEAT循环中也可以使用LEAVE和下面的ITERTE;
ITERATE
既然LEAVE是break；那么ITERTE 就是continue;
iterate label_name;
作用与continue一样，有着跳出当前循环，进入下一循环的 作用
this_xunhuan: LOOP
DECLARE i int DEFAULT 0;
set i=i+2;
if i=50 then set i=i+20;
iterate this_xunhuan;
if i=70 then leave this_xunhuan;
end if;
end loop this_xunhuan;
当i到了50的时候，加到了70，执行了iterate语句跳到下一个循环又加了2变成了72，所以这个循环变成了死循环。
存储函数
存储过程与存储函数的区别：
- 存储函数不能拥有输出参数，因为函数本身就是输出函数。
- 调用函数不需要使用call语句。
- 函数必须有return语句，而return不允许出现在存储过程。
创建存储 函数
create function func_name(参数名 参数type[,参数2 参数type...])
RETURNS type#返回类型
begin
select语句+流程控制
return value;
end;
调用直接使用
func_name([value1....[value2....]])
select func_name([value1....[value2....]])
#投影返回内容
显示存储函数
show create function cc_name;
显示存储函数状态
show function status like cc_name;
删除存储函数
DROP function [if exists] cc_name;#if exists 防止删除不存在的存储过程出现错误
修改存储函数的特性
ALTER function cc_name [characteristic...];
触发器
触发器是一个可以根据表的改变去修改本身表或者相关表的工具，使用触发器可以实现数据的完整性。
触发器是一个被指定关联到另一个表的数据库对象，当表的特定事件出现时就会被激活。
触发器针对永久表而不是临时表。
触发器实现表数据的级联更改，保证数据的完整性吗，可以记录某些事件，记录可当作日志使用。
一张表只能拥有6个触发器：
其中insert、update、delete语句都拥有alter和before两个条件总共2*3=6个触发器
基础语法
create trigger tr_name
{after|before} {delete|update|insert} ON table_name
for each row
begin
DML+DQL+流程控制
end;
其中{after|before}表示表中当sql语句发生时，表中的数据状态
- before 表中数据发生改变前的状态,即sql语句使用前
- after 表中数据发生改变后的状态,即sql语句使用后
当表中before触发器失败，不执行after语句
#查看全部触发器
show triggers;
###
#查看触发器创建语句
show create trigger tr_name;
在mysql中所有触发器存储在information_schema的trigger表中
SELECT * from information_schema.triggers WHERE trigger_name= '触发器名';
删除触发器
drop trigger[if exists] [schema_naem.] tr_name;
#[if exists]判断是否存在，存在则删除，避免不存在删除导致的错误
#[schema_naem.]指定触发器所在的数据库，不指定则当前使用库
触发器调用过程中会出现两种状态分别是new和old，使用这个可以控制不同状态时候的数据，old表示sql语句使用前的数据，new表示sql语句使用后的语句。
用法
create trigger tr_name
{after|before} {delete|update|insert} ON table_name
for each row
begin
DML+DQL+流程控制
set old.字段1=new.字段1+10;
end;
不是 每个类型的触发器都支持old和new
| 触发器类型 | 支持 |
|---|---|
| INSERT触发器 | 只有new |
| UPDATE触发器 | 有new和old |
| DELETE触发器 | 只有old |
MySQL 的触发器中不能对本表进行 insert、update 和 delete 操作，否则会报错
触发器处理本表数据时，使用new.lie_name或old.lie_name
视图
创建一个视图类似c++的封装,隐藏底层表结构，简化数据访问操作，客户端不用了解底层表结构之间的关系。提供一个访问数据的接口，用户不能改变底层表结构和数据，加强安全性，还可以选定用户可以看到的数据，让一些重要信息在表格里但是视图中没有一些重要隐私信息。视图还可以被嵌套，一个视图中可以嵌套另一个视图。
create [or replace] [algorithm={UNDEFINED|MEGRE|TEMPTABLE]
view view_name
as
select 语句(即select 字段1[,字段2....] from table1[,table2,.....] [where 条件1[and/or 条件2 [and/or 条件....]]] [group by 分组条件[having 筛选条件]] order by [desc/ASC] [LIMIT n,m])
[or replace]#表示视图若是存在 则替代，如果没有这段，视图存在则创建失败。
[algorithm={UNDEFINED|MEGRE|TEMPTABLE]#表示试图选择的算法.1表示自动选择，2表示先将视图的select语句生成结果集，利用结果集创建查询，但要求与原表对应，不可使用min，max，sum等函数或distinct，group by，having，limit，union，子查询等不可使用2算法。3算法表示生成临时表，用临时表执行语句。
#例如
CREATE view v_table12
as
select *
from kechen
#删除视图
DROP VIEW view_name；
#例如
drop view v_table;
更新视图
alter view view_name
as
select语句;
#例如上面的那个视图中我不想看到编号
ALTER view v_table12
as
select k_name,k_score
from kechen
查看视图
select 字段1[,字段2...]
from view_name
[where...group by.having ..order by.....]
#例子
select *
from v_table12;
select k_name 名字, k_score 分数
from v_table12
where k_score>60
limit 1,4
通过视图对表的更新，视图是一个虚拟的表，通过表映射出来的，所以对视图进行更新删除的时候，其实是对表进行更新删除，当表格内容更新以后，视图内容也会更新。
视图修改表的内容是这样的
UPDATE v_table12
set k_score=66
where k_name='英语'
create view vvv as select * from kechen
insert into vvv(k_id,k_name,k_score) values(10002,'数据库',99);
DELETE from vvv where k_id=10004
当出现以下情况的时候不能修改视图
某些视图是可更新的。也就是说，可以使用 UPDATE、DELETE 或 INSERT 等语句更新基本表的内容。对于可更新的视图，视图中的行和基本表的行之间必须具有一对一的关系。
还有一些特定的其他结构，这些结构会使得视图不可更新。更具体地讲，如果视图包含以下结构中的任何一种，它就是不可更新的：
- 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
- DISTINCT 关键字。
- GROUP BY 子句。
- HAVING 子句。
- UNION 或 UNION ALL 运算符。
- 位于选择列表中的子查询。
- FROM 子句中的不可更新视图或包含多个表。
- WHERE 子句中的子查询，引用 FROM 子句中的表。
- ALGORITHM 选项为 TEMPTABLE（使用临时表总会使视图成为不可更新的）的时候。
视图计算
create view s_view AS
select name,id,sex,year(NOW())-age 出生年份,age
from students;#加减乘除都可以
视图嵌套
with check option
创建视图的时候把这段东西放在select语句的末尾
遵循where语句的，在进行插入删除或者修改的时候，如果产生了在视图看不见的操作，即where语句之外的操作，MySQL将拒接使用详细看MySQL with check option确保视图一致性 - MySQL教程 (yiibai.com)
视图还有很多东西，有时间再去看了考试应该考不到这里😩
条件
当mysql运行过程中，总是免不了错误，类似python的捕获异常，sql也有一个捕获异常的方式。
定义条件
DECLARE 条件名 condition FOR 条件类型
条件类型:
SQLSTATE [VALUE] 错误代码{字符型|数字型}
例如遇到错误ERROR 1120(43000)
DECLARE err_1120 condition for 1120;
DECLARE err_1120 condition for '43000';
定义处理程序
DECLARE {continue|exit|undo} #表示处理办法||1表示继续运行|2表示退出|3表示撤销操作
HANDLER FOR
condition_value[,...]sp_statement
执行自定义的语句
condition_value为：
- SQLSTATE[VALUE]sqlstate_value: 字符串错误值
- condition——name：使用declare定义的错误名
- SQL WARNING：匹配所有01开头的sqlstate错误代码
- NOT FOUND：匹配以02开头的错误代码
- SQLEXCEPION 匹配所有未被SQL warning和NOTFOUND匹配的错误代码
事务
每次执行一个sql语句是一种隐式事务
而同时执行多个sql语句就是一种显式事务
在一个事务里的sql语句必须全部执行成功才能更改数据，否则与未执行一样不会对数据进行改变
查看/设置事务提交方式
select @@autocommit;#默认1，自动
set @@autocommit=0;#设置为0，手动
提交事务
begin;/START TRANSACTION;#开启事务
sql语句1
sql语句2....
commit;
回退事务
begin;/start TRANSACTION;
sql语句1
sql语句2....
ROLLBACK;
数据库事务具有ACID这4个特性：
- A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
- C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
- I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
- D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。
当有多个事务并发执行的时候，事务执行过程中会造成数据的修改不正确
脏读：当一个事务还未完成，另一个事务读取到了未完成的事务没有提交的数据
不可重复读：当一个事务需要重复读取同一个记录，却俩次读取的数据不一样，因为另一个事务提交了数据
幻读：查询数据时不存在，想插入数据时，另外一个事务已经插入了，导致插入失败，再次 查询还是查询不到
隔离级别
隔离级别与效率成反比
查看隔离级别
SELECT @@TRANSACTION_ISOLATION;
select @@tx_isolation;
show variables like '%tx_isolation%'
mysql默认第三个级别，只会发生幻读。
修改事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
[SESSION | GLOBAL]表示修改的隔离级别的范围，前者应用于当前窗口所有事务，后者是全局事务，省略的话，则应用于当前窗口未执行的事务
未完成:锁、索引、引擎
扫一扫
3407
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
