随笔02（MYSQL）

MYSQL

数据库基本概念

（1）数据

所谓 数据（Data） 是指对 客观事物进行描述并可以鉴别的符号 ，这些符号是可识别的、抽象的。它不仅仅指狭义上的数字，而是有多种表现形式：字母、文字、文本、图形、音频、视频等。现在计算机存储和处理的数据范围十分广泛，而描述这些数据的符号也变得越来越复杂了。

（2）数据库
数据库（Database，DB） 指的是以 一定格式存放 、能够 实现多个用户共享 、与应用程序彼此独立 的 数据集合 。

（3）数据库管理系统
数据库管理系统（Database Management System，DBMS） 是用来定义和 管理数据 的软件。如何科学的组织和存储数据，如何高效的获取和维护数据，如何保证数据的安全性和完整性，这些都需要靠数据库管理系统完成。目前，比较流行的数据库管理系统有：Oracle、MySQL、SQL Server、DB2等。

（4）数据库应用程序
数据库应用程序（Database Application System，DBAS） 是在数据库管理系统基础上，使用 数据库管理系统的语法 ，开发的直接面对最终用户的 应用程序 ，如学生管理系统、人事管理系统、图书管理系统等。

（5）数据库管理员
数据库管理员（Database Administrator，DBA） 是指 对数据库管理系统进行操作的人员 ，其 主要负责数据库的运营和维护。

（6）最终用户
最终用户（User） 指的是 数据库应用程序 的 使用者。用户面向的是数据库应用程序（通过应用程序操作数据），并不会直接与数据库打交道。

（7）数据库系统
数据库系统（Database System，DBS） 一般是由 数据库 、 数据库管理系统 、数据库应用程序 、数据库管理员 和 最终用户 构成。其中 DBMS是数据库系统的基础和核心。

数据库类型

数据库经过几十年的发展，出现了多种类型。根据数据的组织结构不同，主要分为 网状数据库 、 层次数据库 、关系型数据库* 和 非关系型数据库 四种。目前最常见的数据库模型主要是： 关系型数据库 和 非关系型数据库 。

1. 关系型数据库

关系型数据库模型 是 将复杂的数据结构用较为简单的二元关系（二维表） 来表示，如图1-4所示。在该类型数据库中， 对数据的操作基本上都建立在一个或多个表格上 ，我们可以采用 结构化查询语言（SQL） 对数据库进行操作。关系型数据库是目前主流的数据库技术，其中具有代表性的数据库管理系统有：Oracle、DB2、SQL Server、MySQL等。

PS：关系=二维表

2. 非关系型数据库NOSQL

NOSQL（Not Only SQL） 泛指非关系型数据库。关系型数据库在 超大规模和高并发 的web2.0纯动态网站已经显得力不从心，暴露了很多难以克服的问题。NOSQL数据库的产生就是为了 解决大规模数据集合多重数据种类带来的挑战 ，尤其是 大数据应用难题 。常见的非关系型数据库管理系统有 Memcached、MongoDB，redis，HBase等。

Oracle
- Oracle数据库是由美国的 甲骨文（Oracle）公司 开发的 世界上第一款 支持SQL语言的关系型数据库。经过多年的完善与发展，Oracle数据库已经成为世界上最流行的数据库，也是甲骨文公司的核心产品。
- Oracle数据库具有很好的 开放性，能在所有的主流平台上运行，并且 性能高、安全性高、风险低 ；但是其对硬件的要求很高、管理维护和操作比较复杂而且价格昂贵 ，所以一般用在满足对银行、金融、保险等行业大型数据库的需求上。
DB2
- DB2是 IBM公司 著名的关系型数据库产品。DB2无论 稳定性，安全性，恢复性等等都无可挑剔 ，而且从小规模到大规模的应用都可以使用，但是 用起来非常繁琐 ，比较适合 大型的分布式应用系统 。
SQL Server
- SQL Server是由 Microsoft 开发和推广的关系型数据库，SQL Server的 功能比较全面、效率高，可以作为中型企业或单位的数据库平台 。SQL Server可以与Windows操作系统紧密继承，无论是应用程序开发速度还是系统事务处理运行速度，都能得到大幅度提升。但是，SQL Server只能在 Windows系统下运行，毫无开放性可言 。
MySQL
- MySQL是一种 开放源代码的轻量级关系型数据库 ，MySQL数据库使用最常用的结构化查询语言（SQL）对数据库进行管理。由于MySQL是 开放源代码的，因此任何人都可以在General Public License的许可下下载并根据个人需要对其缺陷进行修改。
- 由于MySQL数据库 体积小、速度快、成本低、开放源码等优点 ，现已被广泛应用于互联网上的 中小型网站 中，并且大型网站也开始使用MySQL数据库，如网易、新浪等。

MYSQL介绍

MySQL数据库最初是由瑞典MySQL AB公司开发，2008年1月16号被Sun公司收购。2009年，SUN又被Oracle收购。MySQL是目前IT行业最流行的开放源代码的数据库管理系统，同时它也是一个支持多线程高并发多用户的关系型数据库管理系统。MySQL之所以受到业界人士的青睐，主要是因为其具有以下几方面优点：

1. 开放源代码

MySQL最强大的优势之一在于它是一个 开放源代码的数据库管理系统 。开源的特点是给予了用户根据自己需要修改DBMS的自由。MySQL采用了 General Public License，这意味着授予用户阅读、修改和优化源代码的权利 ，这样即使是免费版的MySQL的功能也足够强大，这也是为什么MySQL越来越受欢迎的主要原因。

2. 跨平台

MySQL可以在不同的操作系统下运行 ，简单地说，MySQL可以支持Windows系统、UNIX系统、Linux系统等多种操作系统平台。这意味着在一个操作系统中实现的应用程序可以很方便地移植到其他的操作系统下。

3. 轻量级

MySQL的 核心程序完全采用多线程编程 ，这些线程都是轻量级的进程，它在灵活地为用户提供服务的同时，又不会占用过多的系统资源。因此MySQL能够更快速、高效的处理数据。

4. 成本低

MySQL分为社区版和企业版， 社区版是完全免费的 ，而企业版是收费的。即使在开发中需要用到一些付费的附加功能，价格相对于昂贵的Oracle、DB2等也是有很大优势的。其实免费的社区版也支持多种数据类型和正规的SQL查询语言，能够对数据进行各种查询、增加、删除、修改等操作，所以一般情况下社区版就可以满足开发需求了，而对数据库可靠性要求比较高的企业可以选择企业版。

另外，PHP中提供了一整套的MySQL函数，对MySQL进行了全方位的强力支持。
总体来说，MySQL是一款开源的、免费的、轻量级的关系型数据库，其具有体积小、速度快、成本低、开放源码等优点，其发展前景是无可限量的 。

PS：社区版与企业版主要的区别是：

社区版包含所有MySQL的最新功能，而企业版只包含稳定之后的功能。换句话说，社区版可以理解为是企业版的测试版。
MySQL官方的支持服务只是针对企业版，如果用户在使用社区版时出现了问题，MySQL官方是不负责任的。

SQL语言入门

我们都知道，数据库管理人员（DBA）通过数据库管理系统（DBMS）可以对数据库（DB）中的数据进行操作，但具体是如何操作的呢？这就涉及到我们本节要讲的SQL语言。

SQL（Structured Query Language） 是 结构化查询语言 的简称，它是 一种数据库查询和程序设计语言 ，同时也是目前使用 最广泛的关系型数据库操作语言 。在数据库管理系统中，使用SQL语言来实现数据的存取、查询、更新等功能。SQL是一种非过程化语言，只需提出"做什么"，而不需要指明"怎么做"。

SQL是由IBM公司在1974~1979年之间根据E.J.Codd发表的关系数据库理论为基础开发的，其前身是"SEQUEL"，后更名为SQL。由于SQL语言具有集 数据查询、数据操纵、数据定义和数据控制 功能于一体， 类似自然语言、简单易用以及非过程化等特点 ，得到了快速的发展，并于1986年10月，被美国国家标准协会（American National Standards Institute，ANSI）采用为关系数据库管理系统的标准语言，后为国际标准化组织（International Organization for Standardization，ISO）采纳为国际标准。

SQL语言分为五个部分：

数据查询语言（Data Query Language，DQL） ：DQL主要用于数据的查询，其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。
数据操作语言（Data Manipulation Language，DML） ：DML主要用于对数据库中的数据进行增加、修改和删除的操作，其主要包括：
1. INSERT：增加数据
2. UPDATE：修改数据
3. DELETE：删除数据
数据定义语言（Data Definition Language，DDL） ：DDL主要用针对是数据库对象（数据库、表、索引、视图、触发器、存储过程、函数）进行创建、修改和删除操作。其主要包括：
1. CREATE：创建数据库对象
2. ALTER：修改数据库对象
3. DROP：删除数据库对象
数据控制语言（Data Control Language，DCL） ：DCL用来授予或回收访问数据库的权限，其主要包括：
1. GRANT：授予用户某种权限
2. REVOKE：回收授予的某种权限
事务控制语言（Transaction Control Language，TCL） ：TCL用于数据库的事务管理。其主要包括：
1. START TRANSACTION：开启事务
2. COMMIT：提交事务
3. ROLLBACK：回滚事务
4. SET TRANSACTION：设置事务的属性

认识数据库表

表（Table） 是数据库中数据存储最常见和最简单的一种形式，数据库可以 将复杂的数据结构用较为简单的二维表来表示 。二维表是由行和列组成的，分别都包含着数据，如表所示。

每个表都是由若干行和列组成的，在数据库中表中的行被称为记录，表中的列被称为是这些记录的字段。

记录也被称为一行数据 ，是表里的一行。在关系型数据库的表里，一行数据是指一条完整的记录。

字段是表里的一列 ，用于 保存每条记录的特定信息 。如上表所示的学生信息表中的字段包括"学号"、"姓名"、"性别"和"年龄"。数据表的一列包含了某个特定字段的全部信息。

创建数据库表 t_student

##这是一个单行注释
/*
多行注释
多行注释
多行注释
*/
/*
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
*/
-- 创建数据库表：
create table t_student(
        sno int(6), -- 6显示长度 
        sname varchar(5), -- 5个字符
        sex char(1),
        age int(3),
        enterdate date,
        classname varchar(10),
        email varchar(15)
);
-- 查看表的结构：展示表的字段详细信息
desc t_student; 
-- 查看表中数据：
select * from t_student;
-- 查看建表语句：
show create table t_student;
/*
CREATE TABLE `t_student` (
  `sno` int DEFAULT NULL,
  `sname` varchar(5) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `enterdate` date DEFAULT NULL,
  `classname` varchar(10) DEFAULT NULL,
  `email` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

数据库表列类型

1.整数类型

MySQL支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如，INT(4))。显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示

主键自增：不使用序列，通过auto_increment，要求是整数类型

2.浮点数类型

需要注意的是与整数类型不一样的是，浮点数类型的宽度不会自动扩充。 score double(4,1)

score double(4,1)--小数部分为1位，总宽度4位，并且不会自动扩充。

3.字符串类型

CHAR和VARCHAR类型相似，均用于存于较短的字符串，主要的不同之处在于存储方式。CHAR类型长度固定，VARCHAR类型的长度可变。

因为VARCHAR类型能够根据字符串的实际长度来动态改变所占字节的大小，所以在不能明确该字段具体需要多少字符时推荐使用VARCHAR类型，这样可以大大地节约磁盘空间、提高存储效率。

CHAR和VARCHAR表示的是字符的个数，而不是字节的个数

4.日期和时间类型

TIMESTEMP类型的数据指定方式与DATETIME基本相同，两者的不同之处在于以下几点：

(1) 数据的取值范围不同，TIMESTEMP 类型的 取值范围更小。

(2) 如果我们对TIMESTAMP类型的字段没有明确赋值，或是被赋与了NULL值，MySQL会 自动将该字段赋值为系统当前的日期与时间。

(3) TIMESTEMP类型还可以 使用CURRENT_TIMESTAMP来获取系统当前时间。

(4) TIMESTEMP类型有一个很大的特点，那就是 时间是根据时区来显示的 。例如，在东八区插入的TIMESTEMP数据为2017-07-11 16:43:25，在东七区显示时，时间部分就变成了15:43:25，在东九区显示时，时间部分就变成了17:43:25。

DML

DML_添加数据

注意事项
1. int 宽度是显示宽度，如果超过，可以自动增大宽度 int底层都是4个字节
2. 时间的方式多样 '1256-12-23' "1256/12/23" "1256.12.23"
3. 字符串不区分单引号和双引号
4. 如何写入当前的时间 now() , sysdate() , CURRENT_DATE()
5. char varchar 是字符的个数，不是字节的个数，可以使用binary，varbinary表示定长和不定长的字节个数。
6. 如果不是全字段插入数据的话，需要加入字段的名字

-- 查看表记录：
select * from t_student;
-- 在t_student数据库表中插入数据：
insert into t_student values (1,'张三','男',18,'2022-5-8','软件1班','123@126.com');
insert into t_student values (10010010,'张三','男',18,'2022-5-8','软件1班','123@126.com');
insert into t_student values (2,'张三','男',18,'2022.5.8','软件1班','123@126.com');
insert into t_student values (2,"张三",'男',18,'2022.5.8','软件1班','123@126.com');
insert into t_student values (7,"张三",'男',18,now(),'软件1班','123@126.com');
insert into t_student values (9,"易烊千玺",'男',18,now(),'软件1班','123@126.com');
insert into t_student (sno,sname,enterdate) values (10,'李四','2023-7-5');

DML_修改，删除数据

注意事项
1. 关键字，表名，字段名不区分大小写
2. 默认情况下，内容不区分大小写
3. 删除操作from关键字不可缺少
4. 修改，删除数据别忘记加限制条件

-- 修改表中数据
update t_student set sex = '女' ;
update t_student set sex = '男' where sno = 10 ;
UPDATE T_STUDENT SET AGE = 21 WHERE SNO = 10;
update t_student set CLASSNAME = 'java01' where sno = 10 ;
update t_student set CLASSNAME = 'JAVA01' where sno = 9 ;
update t_student set age = 29 where classname = 'java01';

-- 删除操作：
delete from t_student where sno = 2;

DDL

-- 查看数据：
select * from t_student;
-- 修改表的结构：
-- 增加一列：
alter table t_student add score double(5,2) ; -- 5:总位数  2：小数位数 
update t_student set score = 123.5678 where sno = 1 ;
-- 增加一列（放在最前面）
alter table t_student add score double(5,2) first;
-- 增加一列（放在sex列的后面）
alter table t_student add score double(5,2) after sex;


-- 删除一列：
alter table t_student drop score;
-- 修改一列：
alter table t_student modify score float(4,1); -- modify修改是列的类型的定义，但是不会改变列的名字
alter table t_student change score score1 double(5,1); -- change修改列名和列的类型的定义
-- 删除表：
drop table t_student;

表的完整性约束

为防止不符合规范的数据存入数据库，在用户对数据进行插入、修改、删除等操作时，MySQL提供了一种机制来检查数据库中的数据是否满足规定的条件，以保证数据库中数据的准确性和一致性，这种机制就是完整性约束。完整性----数据的准确性

MySQL中主要支持以下几种种完整性约束，如表所示。其中Check约束是MySQL8中提供的支持。

约束条件	约束描述
PRIMARY KEY	主键约束，约束字段的值可唯一地标识对应的记录
NOT NULL	非空约束，约束字段的值不能为空
UNIQUE	唯一约束，约束字段的值是唯一的
CHECK	检查约束，限制某个字段的取值范围
DEFAULT	默认值约束，约束字段的默认值
AUTO_INCREMENT	自动增加约束，约束字段的值自动递增
FOREIGN KEY	外键约束，约束表与表之间的关系

非外键约束

一、代码演示非外键约束：

/*
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
约束：
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
【1】学号是主键 = 不能为空 +  唯一 ，主键的作用：可以通过主键查到唯一的一条记录【2】如果主键是整数类型，那么需要自增
【3】姓名不能为空
【4】Email唯一
【5】性别默认值是男
【6】性别只能是男女
【7】年龄只能在18-50之间
*/
-- 创建数据库表：
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        sex char(1) default '男' check(sex='男' || sex='女'),
        age int(3) check(age>=18 and age<=50),
        enterdate date,
        classname varchar(10),
        email varchar(15) unique
);
-- 添加数据：
--  1048 - Column 'sname' cannot be null 不能为null
-- 3819 - Check constraint 't_student_chk_1' is violated. 违反检查约束
insert into t_student values (1,'张三','男',21,'2023-9-1','java01班','zs@126.com');
-- 1062 - Duplicate entry '1' for key 't_student.PRIMARY' 主键重复
-- > 1062 - Duplicate entry 'ls@126.com' for key 't_student.email' 违反唯一约束
insert into t_student values (2,'李四','男',21,'2023-9-1','java01班','ls@126.com');
insert into t_student values (3,'露露','男',21,'2023-9-1','java01班','ls@126.com');
-- 如果主键没有设定值，或者用null.default都可以完成主键自增的效果
insert into t_student (sname,enterdate) values ('菲菲','2029-4-5');
insert into t_student values (null,'小明','男',21,'2023-9-1','java01班','xm@126.com');
insert into t_student values (default,'小刚','男',21,'2023-9-1','java01班','xg@126.com');
-- 如果sql报错，可能主键就浪费了，后续插入的主键是不连号的，我们主键也不要求连号的
insert into t_student values (null,'小明','男',21,'2023-9-1','java01班','oo@126.com');
-- 查看数据：
select * from t_student;

二、约束从作用上可以分为两类：

(1) 表级约束：可以约束表中任意一个或多个字段。与列定义相互独立，不包含在列定义中；与定义用'，'分隔；必须指出要约束的列的名称；

(2) 列级约束：包含在列定义中，直接跟在该列的其它定义之后，用空格分隔；不必指定列名；

-- 删除表：
drop table t_student;
-- 创建数据库表：
create table t_student(
        sno int(6) auto_increment, 
        sname varchar(5) not null, 
        sex char(1) default '男',
        age int(3),
        enterdate date,
        classname varchar(10),
        email varchar(15),
        constraint pk_stu primary key (sno),  -- pk_stu 主键约束的名字
        constraint ck_stu_sex check (sex = '男' || sex = '女'),
        constraint ck_stu_age check (age >= 18 and age <= 50),
        constraint uq_stu_email unique (email)
);
-- 添加数据：
insert into t_student values (1,'张三','男',21,'2023-9-1','java01班','zs@126.com');
-- > 3819 - Check constraint 'ck_stu_sex' is violated.
-- > 3819 - Check constraint 'ck_stu_age' is violated.
-- > 1062 - Duplicate entry 'zs@126.com' for key 't_student.uq_stu_email'
insert into t_student values (3,'李四','男',21,'2023-9-1','java01班','zs@126.com');
-- 查看数据：
select * from t_student;

三、在创建表以后添加约束：

-- 删除表：
drop table t_student;
-- 创建数据库表：
create table t_student(
        sno int(6), 
        sname varchar(5) not null, 
        sex char(1) default '男',
        age int(3),
        enterdate date,
        classname varchar(10),
        email varchar(15)
);
-- > 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
-- 错误的解决办法：就是auto_increment去掉
-- 在创建表以后添加约束：
alter table t_student add constraint pk_stu primary key (sno) ; -- 主键约束
alter table t_student modify sno int(6) auto_increment; -- 修改自增条件
alter table t_student add constraint ck_stu_sex check (sex = '男' || sex = '女');
alter table t_student add constraint ck_stu_age check (age >= 18 and age <= 50);
alter table t_student add constraint uq_stu_email unique (email);
-- 查看表结构：
desc t_student;

外键约束

一、什么是外键约束？
外键约束（FOREIGN KEY，缩写FK） 是用来实现数据库表的参照完整性的。外键约束可以使两张表紧密的结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性。

外键是指 表中某个字段的值依赖于另一张表中某个字段的值 ，而 被依赖的字段必须具有主键约束或者唯一约束 。被依赖的表我们通常称之为父表或者主表，设置外键约束的表称为子表或者从表。

举个例子：如果想要表示学生和班级的关系，首先要有学生表和班级表两张表，然后学生表中有个字段为stu_clazz（该字段表示学生所在的班级），而该字段的取值范围由班级表中的主键cla_no字段（该字段表示班级编号）的取值决定。那么班级表为主表，学生表为从表，且stu_clazz字段是学生表的外键。通过stu_clazz字段就建立了学生表和班级表的关系。

主表（父表）：班级表 - 班级编号 - 主键
从表（子表）：学生表 - 班级编号 - 外键

二、sql展示：

-- 先创建父表：班级表：
create table t_class(
        cno int(4) primary key auto_increment,
        cname varchar(10) not null,
        room char(4)
)
-- 添加班级数据：
insert into t_class values (null,'java001','r803');
insert into t_class values (null,'java002','r416');
insert into t_class values (null,'大数据001','r103');
-- 可以一次性添加多条记录：
insert into t_class values (null,'java001','r803'),(null,'java002','r416'),(null,'大数据001','r103');
-- 查询班级表：
select * from t_class;
-- 学生表删除：
drop table t_student;
-- 创建子表,学生表：
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        classno int(4)  -- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
);
-- 添加学生信息：
insert into t_student values (null,'张三',1),(null,'李四',1),(null,'王五',2);
-- 查看学生表：
select * from t_student;
-- 出现问题：
-- 1.添加一个学生对应的班级编码为4：
insert into t_student values (null,'丽丽',4);
-- 2.删除班级2：
delete from t_class where cno = 2;
-- 出现问题的原因：
-- 因为你现在的外键约束，没用语法添加进去，现在只是逻辑上认为班级编号是外键，没有从语法上定义
-- 解决办法，添加外键约束：
-- 注意：外键约束只有表级约束，没有列级约束：
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        classno int(4),-- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
                                constraint fk_stu_classno foreign key (classno) references t_class (cno)
);
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        classno int(4)
);
-- 在创建表以后添加外键约束：
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno)
-- 上面的两个问题都解决了：
-- 添加学生信息：
-- > 1452 - Cannot add or update a child row: a foreign key constraint fails (`mytestdb`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
insert into t_student values (null,'张三',1),(null,'李四',1),(null,'王五',2);
-- 删除班级1：
-- 2.删除班级2：
insert into t_student values (null,'张三',3),(null,'李四',3),(null,'王五',3);
-- > 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`mytestdb`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
delete from t_class where cno = 3;

三、外键策略

-- 学生表删除：
drop table t_student;
-- 班级表删除：
drop table t_class;
-- 注意：先删除从表，再删除主表。（视频中这个位置笔误，笔记现在已经更正）
-- 先创建父表：班级表：
create table t_class(
        cno int(4) primary key auto_increment,
        cname varchar(10) not null,
        room char(4)
)
-- 可以一次性添加多条记录：
insert into t_class values (null,'java001','r803'),(null,'java002','r416'),(null,'大数据001','r103');
-- 添加学生表，添加外键约束：
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        classno int(4),-- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
                                constraint fk_stu_classno foreign key (classno) references t_class (cno)
);
-- 可以一次性添加多条记录：
insert into t_student values (null,'张三',1),(null,'李四',1),(null,'王五',2),(null,'朱六',3);
-- 查看班级表和学生表：
select * from t_class;
select * from t_student;
-- 删除班级2：如果直接删除的话肯定不行因为有外键约束：
-- 加入外键策略：
-- 策略1：no action 不允许操作
-- 通过操作sql来完成：
-- 先把班级2的学生对应的班级 改为null 
update t_student set classno = null where classno = 2;
-- 然后再删除班级2：
delete from t_class where cno = 2;
-- 策略2：cascade 级联操作：操作主表的时候影响从表的外键信息：
-- 先删除之前的外键约束：
alter table t_student drop foreign key fk_stu_classno;
-- 重新添加外键约束：
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete cascade;
-- 试试更新：
update t_class set cno = 5 where cno = 3;
-- 试试删除：
delete from t_class where cno = 5;
-- 策略3：set null  置空操作：
-- 先删除之前的外键约束：
alter table t_student drop foreign key fk_stu_classno;
-- 重新添加外键约束：
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update set null on delete set null;
-- 试试更新：
update t_class set cno = 8 where cno = 1;
-- 注意：
-- 1. 策略2 级联操作  和  策略2 的  删除操作  可以混着使用：
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete set null ;
-- 2.应用场合：
-- （1）朋友圈删除，点赞。留言都删除  --  级联操作
-- （2）解散班级，对应的学生 置为班级为null就可以了，-- set null

DQL

准备四张表：dept(部门表),emp(员工表),salgrade(薪资等级表),bonus(奖金表)

create table DEPT(  
  DEPTNO int(2) not null,  
  DNAME  VARCHAR(14),  
  LOC    VARCHAR(13)  
);  
alter table DEPT  
  add constraint PK_DEPT primary key (DEPTNO); 
        
create table EMP  
(  
  EMPNO    int(4) primary key,  
  ENAME    VARCHAR(10),  
  JOB      VARCHAR(9),  
  MGR      int(4),  
  HIREDATE DATE,  
  SAL      double(7,2),  
  COMM     double(7,2),  
  DEPTNO   int(2)  
);  
alter table EMP  
  add constraint FK_DEPTNO foreign key (DEPTNO)  
  references DEPT (DEPTNO);  
        
create table SALGRADE  
(  
  GRADE int primary key,  
  LOSAL double(7,2),  
  HISAL double(7,2)  
);  
create table BONUS  
(  
  ENAME VARCHAR(10),  
  JOB   VARCHAR(9),  
  SAL   double(7,2),  
  COMM  double(7,2)  
);  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (10, 'ACCOUNTING', 'NEW YORK');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (20, 'RESEARCH', 'DALLAS');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (30, 'SALES', 'CHICAGO');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (40, 'OPERATIONS', 'BOSTON');  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (1, 700, 1200);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (2, 1201, 1400);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (3, 1401, 2000);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (4, 2001, 3000);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (5, 3001, 9999);  
-- 查看表：
select * from dept; 
-- 部门表：dept:department 部分 ，loc - location 位置
select * from emp;
-- 员工表：emp:employee 员工   ,mgr :manager上级领导编号，hiredate 入职日期  firedate 解雇日期 ，common：补助
-- deptno 外键 参考  dept - deptno字段
-- mgr 外键  参考  自身表emp - empno  产生了自关联
select * from salgrade;
-- losal - lowsal
-- hisal - highsal
select * from bonus;

一、单表查询

最简单的SQL查询

-- 对emp表查询：
select * from emp; -- *代表所有数据
-- 显示部分列：
select empno,ename,sal from emp;
-- 显示部分行：where子句
select * from emp where sal > 2000;
-- 显示部分列，部分行：
select empno,ename,job,mgr from emp where sal > 2000;
-- 起别名：
select empno 员工编号,ename 姓名,sal 工资 from emp; -- as 省略，''或者""省略了
-- as alias 别名
select empno as 员工编号,ename as 姓名,sal as 工资 from emp;
select empno as '员工编号',ename as "姓名",sal as 工资 from emp;
-- > 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '编号,ename as "姓 名",sal as 工资 from emp' at line 1
-- 错误原因：在别名中有特殊符号的时候，''或者""不可以省略不写
select empno as 员工 编号,ename as "姓 名",sal as 工资 from emp;
-- 算术运算符：
select empno,ename,sal,sal+1000 as '涨薪后',deptno from emp where sal < 2500;
select empno,ename,sal,comm,sal+comm from emp;  -- ？？？后面再说
-- 去重操作：
select job from emp;
select distinct job from emp;
select job,deptno from emp;
select distinct job,deptno from emp; -- 对后面的所有列组合 去重 ，而不是单独的某一列去重
-- 排序：
select * from emp order by sal; -- 默认情况下是按照升序排列的
select * from emp order by sal asc; -- asc 升序，可以默认不写
select * from emp order by sal desc; -- desc 降序
select * from emp order by sal asc ,deptno desc; -- 在工资升序的情况下，deptno按照降序排列

使用函数
- MySQL中提供了大量函数来简化用户对数据库的操作，比如字符串的处理、日期的运算、数值的运算等等。使用函数可以大大提高SELECT语句操作数据库的能力，同时也给数据的转换和处理提供了方便。（在sql中使用函数）
- 函数只是对查询结果中的数据进行处理， 不会改变数据库中数据表的值 。MySQL中的函数主要分为 单行函数和多行函数 两大类，下面我们将详细讲解这两大类函数。
- 单行函数
  - 单行函数是指对 每一条记录输入值进行计算，并得到相应的计算结果，然后返回给用户 ，也就是说，每条记录作为一个输入参数，经过函数计算得到每条记录的计算结果。
  - 常用的单行函数主要包括 字符串函数、数值函数、日期与时间函数、流程函数以及其他函数 。
- 多行函数
  - 多行函数是指对 一组数据进行运算，针对这一组数据（多行记录）只返回一个结果 ，也称为 分组函数 。

-- 函数举例：
select empno,ename,lower(ename),upper(ename),sal from emp;
-- 函数的功能：封装了特定的一些功能，我们直接拿过来使用，可以实现对应的功能
-- 函数作用：为了提高select的能力
-- 注意：函数没有改变数据自身的值，而是在真实数据的上面进行加工处理，展示新的结果而已。
select max(sal),min(sal),count(sal),sum(sal),avg(sal) from emp;
-- 函数的分类：
-- lower(ename),upper(ename) ：改变每一条结果，每一条数据对应一条结果  -- 单行函数
-- max(sal),min(sal),count(sal),sum(sal),avg(sal):多条数据，最终展示一个结果  -- 多行函数

PS：除了多行函数（max,min,count,sum,avg），都是单行函数

单行函数

字符串函数（String StringBuilder）

函数	描述
CONCAT(str1, str2, ···, strn)	将str1、str2···strn拼接成一个新的字符串
INSERT(str, index, n, newstr)	将字符串str从第index位置开始的n个字符替换成字符串newstr
LENGTH(str)	获取字符串str的长度
LOWER(str)	将字符串str中的每个字符转换为小写
UPPER(str)	将字符串str中的每个字符转换为大写
LEFT(str, n)	获取字符串str最左边的n个字符
RIGHT(str, n)	获取字符串str最右边的n个字符
LPAD(str, n, pad)	使用字符串pad在str的最左边进行填充，直到长度为n个字符为止
RPAD(str, n, pad)	使用字符串pad在str的最右边进行填充，直到长度为n个字符为止
LTRIM(str)	去除字符串str左侧的空格
RTRIM(str)	去除字符串str右侧的空格
TRIM(str)	去除字符串str左右两侧的空格
REPLACE(str,oldstr,newstr)	用字符串newstr替换字符串str中所有的子字符串oldstr
REVERSE(str)	将字符串str中的字符逆序
STRCMP(str1, str2)	比较字符串str1和str2的大小
SUBSTRING(str,index,n)	获取从字符串str的index位置开始的n个字符

数值函数（Math）

函数	描述
ABS(num)	返回num的绝对值
CEIL(num)	返回大于num的最小整数（向上取整）
FLOOR(num)	返回小于num的最大整数（向下取整）
MOD(num1, num2)	返回num1/num2的余数（取模）
PI()	返回圆周率的值
POW(num,n)/POWER(num, n)	返回num的n次方
RAND(num)	返回0~1之间的随机数
ROUND(num, n)	返回x四舍五入后的值，该值保留到小数点后n位
TRUNCATE(num, n)	返回num被舍去至小数点后n位的值

日期与时间函数

函数	描述
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
SYSDATE()	返回该函数执行时的日期和时间
DAYOFYEAR(date)	返回日期date为一年中的第几天
WEEK(date)/WEEKOFYEAR(date)	返回日期date为一年中的第几周
DATE_FORMAT(date, format) 格式化符号年：%Y（四位年份）、%y（两位年份）。月：%m（两位数字）、%M（完整月份名称）、%b（缩写月份名称）。日：%d（两位数字）、%e（一位数字）、%D（英文序数后缀）。	返回按字符串format格式化后的日期date
DATE_ADD(date, INTERVAL expr unit) /ADDDATE(date, INTERVAL expr unit)	返回date加上一个时间间隔后的新时间值
DATE_SUB(date, INTERVAL expr unit) /SUBDATE(date, INTERVAL expr unit)	DATE_SUB(date, INTERVAL expr unit) /SUBDATE(date, INTERVAL expr unit)
DATEDIFF(date1, date2)	返回起始日期date1与结束日期date2之间的间隔天数

select DATE_ADD(NOW(),INTERVAL 3 MONTH)  from dual

-- 单行函数包含：
-- 1.字符串函数
select ename,length(ename),substring(ename,2,3) from emp;
-- substring字符串截取，2:从字符下标为2开始，3：截取长度3    （下标从1开始）
-- 2.数值函数
select abs(-5),ceil(5.3),floor(5.9),round(3.14) from dual; -- dual实际就是一个伪表 
select abs(-5) 绝对值,ceil(5.3) 向上取整,floor(5.9) 向下取整,round(3.14) 四舍五入;  -- 如果没有where条件的话，from dual可以省略不写
select ceil(sal) from emp;
select 10/3,10%3,mod(10,3) ;
-- 3.日期与时间函数 
select * from emp;
select curdate(),curtime() ; -- curdate()年月日 curtime()时分秒
select now(),sysdate(),sleep(3),now(),sysdate() from dual; -- now(),sysdate() 年月日时分秒
insert into emp values (9999,'lili','SALASMAN',7698,now(),1000,null,30);
-- now()可以表示年月日时分秒，但是插入数据的时候还是要参照表的结构的
desc emp;

流程函数（ IF SWITCH）

间隔类型	描述
IF(condition, t, f)	如果条件condition为真，则返回t，否则返回f
IFNULL(value1, value2)	如果value1不为null，则返回value1，否则返回value2
NULLIF(value1, value2)	如果value1等于value2，则返回null，否则返回value1
CASE value WHEN [value1] THEN result1 [WHEN [value2] THEN result2 ...] [ELSE result] END	如果value等于value1，则返回result1，···，否则返回result
CASE WHEN [condition1] THEN result1 [WHEN [condition2] THEN result2 ...] [ELSE result] END	如果条件condition1为真，则返回result1，···，否则返回result

JSON函数

函数	描述
JSON_APPEND()	在JSON文档中追加数据
JSON_INSERT ()	在JSON文档中插入数据
JSON_REPLACE ()	替换JSON文档中的数据
JSON_REMOVE ()	从JSON文档的指定位置移除数据
JSON_CONTAINS()	判断JSON文档中是否包含某个数据
JSON_SEARCH()	查找JSON文档中给定字符串的路径

其他函数

函数	描述
DATABASE()	返回当前数据库名
VERSION()	返回当前MySQL的版本号
USER()	返回当前登录的用户名
INET_ATON(IP)	返回IP地址的数字表示
INET_NTOA	返回数字代表的IP地址
PASSWORD(str)	实现对字符串str的加密操作
FORMAT(num, n)	实现对数字num的格式化操作，保留n位小数
CONVERT(data, type)	实现将数据data转换成type类型的操作

-- 4.流程函数
-- if相关
select empno,ename,sal,if(sal>=2500,'高薪','底薪') as '薪资等级' from emp; -- if-else 双分支结构
select empno,ename,sal,comm,sal+ifnull(comm,0) from emp; -- 如果comm是null，那么取值为0 -- 单分支
select nullif(1,1),nullif(1,2) from dual; --  如果value1等于value2，则返回null，否则返回value1  
-- case相关：
-- case等值判断

select empno,ename,job,
case job 
 when 'CLERK' then '店员'
 when 'SALESMAN'  then '销售'
 when 'MANAGER' then '经理'
 else '其他'
end '岗位',
sal from emp;
-- case区间判断:
select empno,ename,sal,
case 
 when sal<=1000 then 'A'
 when sal<=2000 then 'B'
 when sal<=3000 then 'C'
 else 'D'
end '工资等级',
deptno from emp;
from emp;
-- 5.JSON函数  
-- 6.其他函数
select database(),user(),version() from dual;

多行函数

对一组数据进行运算，针对一组数据（多行记录）只返回一个结果，也称分组函数

多行函数包含：

函数	描述
COUNT()	统计表中记录的数目
SUM()	计算指定字段值的总和
AVG()	计算指定字段值的平均值
MAX()	统计指定字段值的最大值
MIN()	统计指定字段值的最小值

-- 多行函数：
select max(sal),min(sal),count(sal),sum(sal),sum(sal)/count(sal),avg(sal) from emp;
select * from emp;
-- 多行函数自动忽略null值
select max(comm),min(comm),count(comm),sum(comm),sum(comm)/count(comm),avg(comm) from emp;
-- max(),min(),count()针对所有类型   sum(),avg() 只针对数值型类型有效
select max(ename),min(ename),count(ename),sum(ename),avg(ename) from emp;
-- count --计数   
-- 统计表的记录数：方式1：
select * from emp;
select count(ename) from emp;
select count(*) from emp;
-- 统计表的记录数：方式2
select 1 from dual;
select 1 from emp;
select count(1) from emp;

group_by分组

group by : 用来进行分组
sql展示：

select * from emp;
-- 统计各个部门的平均工资 
select deptno,avg(sal) from emp; -- 字段和多行函数不可以同时使用
select deptno,avg(sal) from emp group by deptno; -- 字段和多行函数不可以同时使用,除非这个字段属于分组
select deptno,avg(sal) from emp group by deptno order by deptno desc;
-- 统计各个岗位的平均工资
select job,avg(sal) from emp group by job;
select job,lower(job),avg(sal) from emp group by job;

having分组后筛选

-- 统计各个部门的平均工资 ,只显示平均工资2000以上的  - 分组以后进行二次筛选 having
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000;
select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000 order by deptno desc;
-- 统计各个岗位的平均工资,除了MANAGER
-- 方法1：
select job,avg(sal) from emp where job != 'MANAGER' group by job;
-- 方法2：
select job,avg(sal) from emp group by job having job != 'MANAGER' ;
-- where在分组前进行过滤的，having在分组后进行后滤。

总结：

select语句总结

select column, group_function(column) 
from table 
[where condition] 
[group by  group_by_expression] 
[having group_condition] 
[order by column];

注意：顺序固定，不可以改变顺序

select语句的执行顺序

from--where -- group by-- having- select - order by
单表查询练习：

-- 单表查询练习：
-- 列出工资最小值小于2000的职位
select job,min(sal)
from emp
group by job
having min(sal) < 2000 ;
-- 列出平均工资大于1200元的部门和工作搭配组合
select deptno,job,avg(sal)
from emp
group by deptno,job
having avg(sal) > 1200
order by deptno;
-- 统计[人数小于4的]部门的平均工资。 
select deptno,count(1),avg(sal)
from emp
group by deptno
having count(1) < 4
-- 统计各部门的最高工资，排除最高工资小于3000的部门。
select deptno,max(sal)
from emp 
group by deptno
having max(sal) < 3000;

二、多表查询

99语法：交叉连接，自然连接，内连接查询

多表查询引入：

实际开发中往往需要针对两张甚至更多张数据表进行操作，而这多张表之间需要使用主键和外键关联在一起，然后使用 连接查询来查询多张表中满足要求的数据记录 。

一条SQL语句查询多个表，得到一个结果，包含多个表的数据。效率高。在SQL99中， 连接查询需要使用join关键字实现 。

提供了多种连接查询的类型： cross natural using on

交叉连接（CROSS JOIN） 是对 两个或者多个表进行笛卡儿积操作 ，所谓笛卡儿积就是关系代数里的一个概念，表示两个表中的每一行数据任意组合的结果。比如：有两个表，左表有m条数据记录，x个字段，右表有n条数据记录，y个字段，则执行交叉连接后将返回m*n条数据记录，x+y个字段。笛卡儿积示意图如图所示。

sql展示：

-- 查询员工的编号，姓名，部门编号：
select * from emp;
select empno,ename,deptno from emp;
-- 查询员工的编号，姓名，部门编号,部门名称：
select * from emp; -- 14条记录
select * from dept; -- 4条记录 
-- 多表查询 ：
-- 交叉连接：cross join
select * 
from emp
cross join dept; -- 14*4 = 56条 笛卡尔乘积 ： 没有实际意义，有理论意义
select * 
from emp
join dept; -- cross 可以省略不写，mysql中可以，oracle中不可以
-- 自然连接：natural join 
-- 优点：自动匹配所有的同名列 ,同名列只展示一次 ，简单
select * 
from emp
natural join dept;
select empno,ename,sal,dname,loc 
from emp
natural join dept;
-- 缺点： 查询字段的时候，没有指定字段所属的数据库表，效率低
-- 解决： 指定表名：
select emp.empno,emp.ename,emp.sal,dept.dname,dept.loc,dept.deptno
from emp
natural join dept;
-- 缺点：表名太长
-- 解决：表起别名
select e.empno,e.ename,e.sal,d.dname,d.loc,d.deptno
from emp e
natural join dept d;
-- 自然连接 natural join 缺点：自动匹配表中所有的同名列，但是有时候我们希望只匹配部分同名列：
-- 解决： 内连接 - using子句：
select * 
from emp e
inner join dept d -- inner可以不写
using(deptno) -- 这里不能写natural join了 ,这里是内连接
-- using缺点：关联的字段，必须是同名的 
-- 解决： 内连接 - on子句：
select * 
from emp e
inner join dept d
on (e.deptno = d.deptno);
-- 多表连接查询的类型： 1.交叉连接  cross join  2. 自然连接  natural join  
-- 3. 内连接 - using子句   4.内连接 - on子句
-- 综合看：内连接 - on子句
select * 
from emp e
inner join dept d
on (e.deptno = d.deptno)
where sal > 3500;
-- 条件：
-- 1.筛选条件  where  having
-- 2.连接条件 on,using,natural 
-- SQL99语法 ：筛选条件和连接条件是分开的

99语法：外连接

-- inner join - on子句： 显示的是所有匹配的信息
select * 
from emp e
inner join dept d
on e.deptno = d.deptno;
select * from emp;
select * from dept;
-- 问题：
-- 1.40号部分没有员工，没有显示在查询结果中
-- 2.员工scott没有部门，没有显示在查询结果中
-- 外连接：除了显示匹配的数据之外，还可以显示不匹配的数据
-- 左外连接： left outer join   -- 左面的那个表的信息，即使不匹配也可以查看出效果
select * 
from emp e
left outer join dept d
on e.deptno = d.deptno;
-- 右外连接： right outer join   -- 右面的那个表的信息，即使不匹配也可以查看出效果
select * 
from emp e
right outer join dept d
on e.deptno = d.deptno;
-- 全外连接  full outer join -- 这个语法在mysql中不支持，在oracle中支持 -- 展示左，右表全部不匹配的数据 
-- scott ，40号部门都可以看到
select * 
from emp e
full outer join dept d
on e.deptno = d.deptno;
-- 解决mysql中不支持全外连接的问题：
select * 
from emp e
left outer join dept d
on e.deptno = d.deptno
union -- 并集 去重 效率低
select * 
from emp e
right outer join dept d
on e.deptno = d.deptno;
select * 
from emp e
left outer join dept d
on e.deptno = d.deptno
union all-- 并集 不去重 效率高
select * 
from emp e
right outer join dept d
on e.deptno = d.deptno;
-- mysql中对集合操作支持比较弱，只支持并集操作，交集，差集不支持（oracle中支持）
-- outer可以省略不写

99语法：三表联合查询

-- 查询员工的编号、姓名、薪水、部门编号、部门名称、薪水等级  
select * from emp;  
select * from dept;  
select * from salgrade;  
select e.ename,e.sal,e.empno,e.deptno,d.dname,s.*  
from emp e  
right outer join dept d  
on e.deptno = d.deptno  
inner join salgrade s  
on e.sal between s.losal and s.hisal

99语法：自连接查询

-- 查询员工的编号、姓名、上级编号,上级的姓名  
select * from emp;  
  
select e1.empno 员工编号,e1.ename 员工姓名,e1.mgr 领导编号,e2.ename 员工领导姓名  
from emp e1  
inner join emp e2  
on e1.mgr = e2.empno;  
  
-- 左外连接：  
select e1.empno 员工编号,e1.ename 员工姓名,e1.mgr 领导编号,e2.ename 员工领导姓名  
from emp e1  
left outer join emp e2  
on e1.mgr = e2.empno;

子查询

什么是子查询？

一条SQL语句含有多个select，

-- 引入子查询：  
-- 查询所有比“CLARK”工资高的员工的信息  
-- 步骤1：“CLARK”工资  
select sal from emp where ename = 'CLARK';  -- 2450  
  
-- 步骤2：查询所有工资比2450高的员工的信息  
select * from emp where sal > 2450;  
  
-- 两次命令解决问题 --》效率低 ，第二个命令依托于第一个命令，第一个命令的结果给第二个命令使用，但是  
-- 因为第一个命令的结果可能不确定要改，所以第二个命令也会导致修改  
-- 将步骤1和步骤2合并 --》子查询：  
select * from emp where sal > (select sal from emp where ename = 'CLARK');  
-- 一个命令解决问题 --》效率高

执行顺序：

先执行子查询，再执行外查询；

不相关子查询：

子查询可以独立运行，称为不相关子查询。

不相关子查询分类：

根据子查询的结果行数，可以分为单行子查询和多行子查询。

单行子查询

-- 单行子查询：
-- 查询工资高于平均工资的雇员名字和工资。
select ename,sal
from emp
where sal > (select avg(sal) from emp);
-- 查询和CLARK同一部门且比他工资低的雇员名字和工资。
select ename,sal
from emp
where deptno = (select deptno from emp where ename = 'CLARK') 
      and 
      sal < (select sal from emp where ename = 'CLARK')
-- 查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息  
select * 
from emp
where job = (select job from emp where ename = 'SCOTT') 
      and 
      hiredate < (select hiredate from emp where ename = 'SCOTT')

多行子查询

-- 多行子查询：  
-- 【1】查询【部门20中职务同部门10的雇员一样的】雇员信息。  
-- 查询雇员信息  
select * from emp;  
-- 查询部门20中的雇员信息  
select * from emp where deptno = 20;-- CLERK,MANAGER,ANALYST  
-- 部门10的雇员的职务：  
select job from emp where deptno = 10; -- MANAGER,PRESIDENT,CLERK  
  
-- 查询部门20中职务同部门10的雇员一样的雇员信息。  
select * from emp  
where deptno = 20  
and job in (select job from emp where deptno = 10);  
  
-- > Subquery returns more than 1 row  
select * from emp  
where deptno = 20  
and job = any(select job from emp where deptno = 10);  
  
-- 【2】查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。  
-- 查询雇员的编号、名字和工资  
select empno,ename,sal from emp;  
  
-- “SALESMAN”的工资：  
select sal from emp where job = 'SALESMAN';  
  
-- 查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。  
-- 多行子查询：  
select empno,ename,sal  
from emp  
where sal > all(select sal from emp where job = 'SALESMAN');  
  
-- 单行子查询：  
select empno,ename,sal  
from emp  
where sal > (select max(sal) from emp where job = 'SALESMAN');  
  
-- 【3】查询工资低于任意一个“CLERK”的工资的雇员信息。  
-- 查询雇员信息  
select * from emp;  
  
-- 查询工资低于任意一个“CLERK”的工资的雇员信息  
select *  
from emp  
where sal < any(select sal from emp where job = 'CLERK')  
and job != 'CLERK';  
  
-- 单行子查询：  
select *  
from emp  
where sal < (select max(sal) from emp where job = 'CLERK')  
and job != 'CLERK';

相关子查询

不相关的子查询引入：

不相关的子查询：子查询可以独立运行，先运行子查询，再运行外查询。
相关子查询：子查询不可以独立运行，并且先运行外查询，再运行子查询

相关的子查询优缺点：

好处：简单功能强大（一些使用不相关子查询不能实现或者实现繁琐的子查询，可以使用相关子查询实现）
缺点：稍难理解

sql展示：

-- 【2】查询本部门最高工资的员工   （相关子查询）  
-- 方法1：通过不相关子查询实现：  
select * from emp where deptno = 10 and sal = (select max(sal) from emp where deptno = 10)  
union  
select * from emp where deptno = 20 and sal = (select max(sal) from emp where deptno = 20)  
union  
select * from emp where deptno = 30 and sal = (select max(sal) from emp where deptno = 30);  
-- 缺点：语句比较多，具体到底有多少个部分未知  
  
-- 方法2： 相关子查询  
select * from emp e where sal = (select max(sal) from emp where deptno = e.deptno) order by deptno;  
  
-- 【3】查询工资高于其所在岗位的平均工资的那些员工  （相关子查询）  
-- 不相关子查询：  
select * from emp where job = 'CLERK' and sal >= (select avg(sal) from emp where job = 'CLERK')  
union ......;  
  
-- 相关子查询：  
select * from emp e where sal >= (select avg(sal) from emp e2 where e2.job = e.job);

数据库对象

事务

事务及其特征

事务（Transaction） 是 用来维护数据库完整性的 ，它能够保证一系列的MySQL操作（DML）要么全部执行，要么全不执行 。

例子1：
- 举一个例子来进行说明，例如转账操作：A账户要转账给B账户，那么A账户上减少的钱数和B账户上增加的钱数必须一致，也就是说A账户的转出操作和B账户的转入操作要么全部执行，要么全不执行；如果其中一个操作出现异常而没有执行的话，就会导致账户A和账户B的转入转出金额不一致的情况，为而事实上这种情况是不允许发生的，所以为了防止这种情况的发生，需要使用事务处理。
例子2：
- 在淘宝购物下订单的时候，商家库存要减少，订单增加记录，付款我的账号少100元...操作要么全部执行，要么全不执行

事务的概念
- 事务（Transaction） 指的是 一个操作序列 ， 该操作序列中的多个操作要么都做，要么都不做 ，是 一个不可分割的工作单位 ，是 数据库环境中的逻辑工作单位 ，由DBMS（数据库管理系统）中的 事务管理子系统负责事务的处理 。
- 目前常用的存储引擎有InnoDB（MySQL5.5以后默认的存储引擎）和MyISAM（MySQL5.5之前默认的存储引擎），其中InnoDB支持事务处理机制，而MyISAM不支持。
事务的特性
- 事务处理可以确保除非事务性序列内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的序列，可以简化错误恢复并使应用程序更加可靠。
- 但并不是所有的操作序列都可以称为事务，这是因为一个操作序列要成为事务，必须满足事务的 原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability） 。这四个特性简称为 ACID特性 。【必问】

原子性
- 原子是自然界最小的颗粒，具有不可再分的特性。事务中的所有操作可以看做一个原子， 事务是应用中不可再分的最小的逻辑执行体 。
- 使用事务对数据进行修改的 操作序列 ，要么 全部执行，要么全不执行 。通常，某个事务中的操作都具有共同的目标，并且是相互依赖的。如果数据库系统只执行这些操作中的一部分，则可能会破坏事务的总体目标，而原子性消除了系统只处理部分操作的可能性。
一致性
- 一致性是指事务执行的结果必须使数据库 从一个一致性状态，变到另一个一致性状态 。当数据库中只包含事务成功提交的结果时，数据库处于一致性状态。 一致性是通过原子性来保证的 。
- 例如：在转账时，只有 保证转出和转入的金额一致才能构成事务 。也就是说 事务发生前和发生后，数据的总额依然匹配 。
隔离性
- 隔离性是指 各个事务的执行互不干扰 ，任意 一个事务的内部操作对其他并发的事务，都是隔离的 。也就是说： 并发执行的事务之间既不能看到对方的中间状态，也不能相互影响 。
- 例如：在转账时，只有当A账户中的转出和B账户中转入操作都执行成功后才能看到A账户中的金额减少以及B账户中的金额增多。并且其他的事务对于转账操作的事务是不能产生任何影响的。
持久性
- 持久性指事务 一旦提交，对数据所做的任何改变，都要记录到永久存储器中 ，通常是保存进物理数据库，即使数据库出现故障，提交的数据也应该能够恢复。但如果是由于外部原因导致的数据库故障，如硬盘被损坏，那么之前提交的数据则有可能会丢失。

sql展示：使用事务保证转账安全

-- 创建账户表：  
create table account(  
        id int primary key auto_increment,  
        uname varchar(10) not null,  
        balance double  
);  
-- 查看账户表：  
select * from account;  
  
-- 在表中插入数据：  
insert into account values (null,'丽丽',2000),(null,'小刚',2000);  
  
-- 丽丽给小刚 转200元：  
update account set balance = balance - 200 where id = 1;  
update account set balance = balance + 200 where id = 2;  
  
-- 默认一个DML语句是一个事务，所以上面的操作执行了2个事务。  
update account set balance = balance - 200 where id = 1;  
update account set balance = balance + 200 where id = 2;  
  
-- 必须让上面的两个操作控制在一个事务中：  
-- 手动开启事务：  
start transaction;  
update account set balance = balance - 200 where id = 1;  
update account set balance = balance + 200 where id = 2;  
-- 手动回滚：刚才执行的操作全部取消：  
rollback;  
-- 手动提交：  
commit;  
-- 在回滚和提交之前，数据库中的数据都是操作的缓存中的数据，而不是数据库的真实数据

事务并发问题

脏读（Dirty read）

当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是"脏数据"，依据"脏数据"所做的操作可能是不正确的。

读到了别人事务中没提交的数据，脏数据 ，本次读取叫脏读。

不可重复读（Unrepeatableread）

指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。 这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读 。

读到了别人事务提交后的数据(修改操作)，不可重复读 。

幻读（Phantom read）

幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

读到了别事务提交后的数据（添加/删除操作），幻读。

不可重复度和幻读区别：

不可重复读的重点是修改，幻读的重点在于 新增或者删除 。
解决不可重复读的问题只需 锁住满足条件的行 ，解决幻读需要锁表
例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导致A再读自己的工资时工资变为 2000；这就是不可重复读。
例2（同样的条件, 第1次和第2次读出来的记录数不一样）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读

事务的隔离级别

事务的隔离级别用于 决定如何控制并发用户读写数据的操作 。数据库是 允许多用户并发访问的 ，如果多个用户同时开启事务并对同一数据进行读写操作的话，有可能会出现脏读、不可重复读和幻读问题，所以MySQL中提供了 四种隔离级别 来解决上述问题。

事务的隔离级别 从低到高依次为READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ （可重复读）以及SERIALIZABLE ， 隔离级别越低，越能支持高并发的数据库操作 。

READ UNCOMMITTED（读未提交）
- 最低隔离级别
- 允许事务读取其他事务尚未提交的数据
- 主要问题：会产生"脏读"（Dirty Read）
- 性能最好，但数据一致性最差
READ COMMITTED（读已提交）
- 只允许读取已提交的数据
- 解决了：脏读问题
- 仍存在：不可重复读（Non-repeatable Read）
- Oracle数据库的默认隔离级别
REPEATABLE READ（可重复读）
- 保证在同一事务中多次读取同一数据时结果一致
- 解决了：脏读、不可重复读问题
- 仍存在：幻读（Phantom Read）
- MySQL InnoDB存储引擎的默认隔离级别
SERIALIZABLE（串行化）
- 最高隔离级别
- 通过强制事务串行执行避免并发问题
- 解决了：脏读、不可重复读、幻读问题
- 性能最差，但数据一致性最高

隔离级别	脏读	不可重复读	幻读	性能
READ UNCOMMITTED	√	√	√	最高
READ COMMITTED	×	√	√	较高
REPEATABLE READ 默认	×	×	√	中等
SERIALIZABLE	×	×	×	最低

PS：√ 代表会出现问题， ×代表不会出现问题 = 解决问题

表的准备：

create table account(
        id int primary key auto_increment,
        uname varchar(10) not null,
        balance double check (balance >=0)
);

insert into account values(null,'张三',400);
insert into account values(null,'李四',400);

## 不使用事务：
update account set balance =balance+500 where uname='李四';
update account set balance =balance-500 where uname='张三';

select * from account; ## 多出500 元，对数据的操作存在问题！

## 使用事务：
start TRANSACTION;
update account set balance =balance-1000 where uname='李四';
update account set balance =balance-500 where uname='张三'; ROLLBACK;

jdbc代码：

public static void main(String[] args) throws SQLException {
        Connection con=null;
            try {
                BDHelper db = new BDHelper();
                con= BDHelper.getCon();
                con.setAutoCommit(false);
                PreparedStatement ps = con.prepareStatement("update account set balance =balance+100 where uname='李四'");
                ps.execute();
                ps.execute("update account set balance =balance-100 where uname='张三'");
                con.commit();
                System.out.println("提交");
            }catch (Exception e){
                con.rollback();
                System.out.println("回滚");
            }
    }

视图

视图的概念
- 视图（view） 是 一个从单张或多张基础数据表或其他视图中构建出来的虚拟表 。同基础表一样，视图中也包含了一系列带有名称的列和行数据，但是数据库中只是存放视图的定义，也就是动态检索数据的查询语句，而并不存放视图中的数据，这些数据依旧存放于构建视图的基础表中，只有当用户使用视图时才去数据库请求相对应的数据，即视图中的数据是在引用视图时动态生成的。因此视图中的数据依赖于构建视图的基础表，如果基本表中的数据发生了变化，视图中相应的数据也会跟着改变。

PS:视图本质上就是：一个查询语句，是一个虚拟的表，不存在的表，你查看视图，其实就是查看视图对应的sql语句

视图的好处
- 简化用户操作 ：视图可以使用户将注意力集中在所关心地数据上，而不需要关心数据表的结构、与其他表的关联条件以及查询条件等。
- 对机密数据提供安全保护 ：有了视图，就可以在设计数据库应用系统时，对不同的用户定义不同的视图，避免机密数据（如，敏感字段"salary"）出现在不应该看到这些数据的用户视图上。这样视图就自动提供了对机密数据的安全保护功能
SQL展示：

-- 创建/替换单表视图：  
create or replace view myview01  
as  
select empno, ename,job,deptno  
from emp  
where deptno = 20  
with check option;  
  
-- 查看视图：  
select * from myview01;  
  
-- 在视图中插入数据：  
insert into myview01 (empno,ename,job,deptno) values (9999,'lili','CLERK',20);  
insert into myview01 (empno,ename,job,deptno) values (8888,'nana','CLERK',30);  
insert into myview01 (empno,ename,job,deptno) values (7777,'feifei','CLERK',30);  
-- > 1369 - CHECK OPTION failed 'mytestdb.myview01'  
  
-- 创建/替换多表视图：  
create or replace view myview02  
as  
select e.empno,e.ename,e.sal,d.deptno,d.dname  
from emp e  
join dept d  
on e.deptno = d.deptno  
where sal > 2000 ;  
  
select * from myview02;  
  
-- 创建统计视图：  
create or replace view myview03  
as  
select e.deptno,d.dname,avg(sal),min(sal),count(1)  
from emp e  
join dept d  
using(deptno)  
group by e.deptno ;  
  
select * from myview03;  
  
-- 创建基于视图的视图：  
create or replace view myview04  
as  
select * from myview03 where deptno = 20;  
  
select * from myview04;

存储过程

什么是 存储过程(Stored Procedure)
- 通过前面章节的学习，我们已经知道SQL是一种非常便利的语言。从数据库抽取数据，或者对特定的数据集中更新时，都能通过简洁直观的代码实现。
- 但是这个所谓的"简洁"也是有限制，SQL基本是一个命令实现一个处理，是所谓的非程序语言。
- 在不能编写流程的情况下，所有的处理只能通过一个个命令来实现。当然，通过使用连接及子查询，即使使用SQL的单一命令也能实现一些高级的处理，但是，其局限性是显而易见的。例如，在SQL中就很难实现针对不同条件进行不同的处理以及循环等功能。
- 这个时候就出现了存储过程这个概念，简单地说，存储过程就是数据库中保存(Stored)的一系列SQL命令（Procedure）的集合。也可以将其看作相互之间有关系的SQL命令组织在一起形成的一个小程序。
存储过程的优点
1. 提高执行性能 。存储过程执行效率之所高，在于普通的SQL语句，每次都会对语法分析，编译，执行，而存储过程只是在第一次执行语法分析，编译，执行，以后都是对结果进行调用。
2. 可减轻网络负担 。使用存储过程，复杂的数据库操作也可以在数据库服务器中完成。只需要从客户端(或应用程序)传递给数据库必要的参数就行，比起需要多次传递SQL命令本身，这大大减轻了网络负担。
3. 可将数据库的处理黑匣子化 。应用程序中完全不用考虑存储过程的内部详细处理，只需要知道调用哪个存储过程就可以了。
图解

展示存储过程：

-- 定义一个没有返回值 存储过程  
-- 实现：模糊查询操作：  
select * from emp where ename like '%A%';  
  
create procedure mypro01(name varchar(10))  
begin  
    if name is null or name = "" then  
        select * from emp;  
    else  
        select * from emp where ename like concat('%', name, '%');  
    end if;  
end;  
  
-- 删除存储过程：  
drop procedure mypro01;  
  
-- 调用存储过程：  
call mypro01(null);  
  
call mypro01('R');  
  
-- 定义一个  有返回值的存储过程：  
-- 实现：模糊查询操作：  
-- in 参数前面的in可以省略不写  
-- found_rows()mysql中定义的一个函数，作用返回查询结果的条数  
create procedure mypro02(in name varchar(10),out num int(3))  
begin  
        if name is null or name = "" then  
                select * from emp;  
        else  
    select * from emp where ename like concat('%',name,'%');  
        end if;  
        select found_rows() into num;  
end;  
-- -- 调用存储过程：  
call mypro02(null,@num);  
  
select @num;  
  
call mypro02('R',@aaa);  
  
select @aaa;

准备存储过程：

/*  
创建存储过程：  
*/  
##例子1:根据部门编号参数，完成查询。  
create PROCEDURE pro_queryempbydeptno(dno int)  
begin  
        if dno is null  or dno ='' then  
           select * from emp;  
        else  
           select * from emp where deptno=dno;  
        end if;  
end;  
  
## 调用存储过程：  
call pro_queryempbydeptno(20);  
  
## 给指定部门低于2000 ##的员工涨薪500，统计最后的涨薪人数是多少？  
## 创建存储过程：  
create PROCEDURE pro_updateSal( in dno int,out num int)  
begin  
    update emp set sal=sal+500 where sal<2000 and deptno=dno;  
    select FOUND_ROWS() into num;  
end;  
  
## 删除存储过程：  
drop PROCEDURE pro_updateSal;  
  
## mysql调用存储过程  
set @num := '';  
  
call pro_updateSal (30,@num);  
  
select @num;  
  
## 例子3:先对数据进行修改，修改后 在继续查询：  
create PROCEDURE pro_updateSelect(in dno int)  
begin  
  update emp set sal=sal+500  where deptno=dno and sal<2000;  
        select * from emp where deptno=dno;  
end;  
  
call pro_updateSelect(10);

jdbc代码：

@Test  
public void ProcedureTest() throws SQLException {  
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/my_db?" +  
            "useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai" +  
            "&allowPublicKeyRetrieval=true", USERNAME, PASSWORD);  
    String sql = "{call pro_updateSal(?,?)}";  
    CallableStatement cs = con.prepareCall(sql);  
    cs.setInt(1, 20); //给输入参数赋值  
    cs.registerOutParameter(2, Types.INTEGER); //给输出参数确定类型  
    cs.execute();  
    System.out.println("本次涨薪人数是:" + cs.getInt(2));  
    cs.close();  
    con.close();  
}  
  
@Test  
public void ProcedureTest1() throws SQLException {  
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/my_db?" +  
            "useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai" +  
            "&allowPublicKeyRetrieval=true", USERNAME, PASSWORD);  
    String sql = "{call pro_queryempbydeptno(?)}";  
    CallableStatement cs = con.prepareCall(sql);  
    cs.setInt(1, 20);  
    ResultSet resultSet = cs.executeQuery();  
    while (resultSet.next()) {  
        System.out.println(resultSet.getInt(1) + "\t"  
                + resultSet.getString(2) + "\t"  
                + resultSet.getString(3) + "\t"  
                + resultSet.getInt(8));  
    }  
    resultSet.close();  
    cs.close();  
    con.close();  
}

DCL

创建一个用户同时设置密码：

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';  
  
create user 'user01'@'127.0.0.1_' IDENTIFIED BY '123456';  
  
create user 'user02'@'localhost' IDENTIFIED BY '123456';

授权:

-- GRANT privileges ON databasename.tablename TO 'username'@'host';  
-- privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL  
GRANT SELECT, INSERT ON my_db.emp TO 'user01'@'127.0.0.1_';  
  
GRANT ALL PRIVILEGES ON my_db.* TO 'user02'@'localhost';

撤回：revoke
回顾：
- SQL=DQL（select）+DML（update，delete，insert）+TCL(commit,rollback)+DDL(create ,drop,alter,)+DCL(GRANT,revoke)

函数

准备函数：

##DETERMINISTIC 单词一定要加上！  
CREATE  
    DEFINER = `root`@`localhost` FUNCTION `NewProc`(`a` int, `b` int) RETURNS int  
    DETERMINISTICBEGIN  
    RETURN a + b;  
END

jdbc代码使用：直接在sql语句中使用！

select NewProc(sal*12,ifnull(comm,0)) from emp

索引

SQL索引是什么？
- 定义：索引（Index）是帮助MySQL高效获取数据的数据结构，就好比书的目录，加快数据库的查询速度。
SQL索引的作用是什么？优点是什么？
- 提高查询效率
- 消除数据分组、排序
- 避免"回表"查询（索引覆盖）
- 优化聚合查询
- 用于多表JOIN关联查询
- 利用唯一性约束，保证数据唯一性
- InnDB行锁实现
使用索引的优点
1. 可以通过 建立唯一索引 或者 主键索引 ,保证数据库表中每一行数据的 唯一性.
2. 建立索引可以 大大提高检索的数据 ,以及 减少表的检索行数
3. 在表连接的连接条件，可以 加速表与表直接的相连
4. 在分组和排序字句进行数据检索,可以减少查询时间中 分组和排序时所消耗的时间 (数据库的记录会重新排序)
5. 建立索引,在查询中使用索引可以 提高性能
SQL索引的缺点是什么？
1. 增加I/O成本。在创建索引和维护索引会耗费时间,随着数据量的增加而增加
2. 增加磁盘空间。索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
3. 不合适的索引或索引过多，会降低增删改的效率。.当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。
  >一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。
索引的分类？
1. 按存储结构分类：
  - BTREE：InnoDB & MyISAM
    - HASH：HEAP，NDB，InnoDB AHI
    - Fractal Tree：TokuDB
    - RTREE
    - FULLTEXT
2. 按数据的存储方式（物理结构）分类：
  - 聚集索引：聚簇索引的顺序就是 数据的物理存储顺序 ，索引与数据存放在同一个文件中。（表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个）
  - 非聚集索引 ：非聚簇索引的顺序与 数据的物理存储顺序不同 ，索引与数据存放在不同的文件。（非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个）
3. 按应用层次分类：
  1) 普通索引
  - 最基本的索引，它没有任何限制，用于加速查询。
  2) 唯一索引
  - 索引列的值必须唯一 ，但 允许有空值 。如果是组合索引，则列值的组合必须唯一。
  3) 主键索引
  - 是 一种特殊的唯一索引 ， 一个表只能有一个主键 ， 不允许有空值 。一般是在建表的时候同时创建主键索引。
  4) 组合索引
  - 指 多个字段上创建的索引 ， 只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用 。使用组合索引时遵循 最左前缀集合。
  5) 全文索引
  - 主要用来 查找文本中的关键字 ，而不是直接与索引中的值相比较。
  - fulltext索引跟其它索引大不相同，它更像是一个 搜索引擎 ，而不是简单的where语句的参数匹配。
  - fulltext索引配合match against操作使用，而不是一般的where语句加like。
  - 它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。
主键与唯一索引的区别
- 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。唯一性索引列允许空值，而主键列不允许为空值。主键列在创建时，已经默认为 空值 + 唯一索引 了。
- 主键可以被其他表引用为外键，而唯一索引不能。一个表最多 只能创建一个主键 ，但可以创建多个唯一索引。主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。在 RBO 模式下，主键的执行计划优先级要高于唯一索引。两者可以提高查询的速度。
什么情况下该创建索引？
- 频繁作为查询条件的字段应该创建索引；
- 查询中与其他表有关联的字段，例如外键关系；
- 在经常需要排序(order by),分组(group by)和的distinct 列上加索引，可以加快排序查询的时间,
什么情况下不创建索引？
1. 查询中 很少使用到 的字段不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
2. 值重复率高的字段 不适合建索引（比如性别、百万级数据时比如26个字母），数据很少的字段也不应该建立索引,比如一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
3. 定义为text和image和bit数据类型的列不应该增加索引,
4. 当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系
为什么性别不适合创建索引？
- 因为你访问索引需要付出额外的IO开销，你从索引中拿到的只是地址，要想真正访问到数据还是要对表进行一次IO。
- 假如你要从表的100万行数据中取几个数据，那么利用索引迅速定位，访问索引的这IO开销就非常值了。
- 但如果你是从100万行数据中取50万行数据，就比如性别字段，那你相对需要访问50万次索引，再访问50万次表，加起来的开销并不会比直接对表进行一次完整扫描小。

sql准备，创建索引：

触发器

触发器是 与 MySQL 数据表有关的数据库对象 ，在 满足定义条件时触发 ，并执行触发器中定义的语句集合 。触发器的这种特性可以 协助应用在数据库端确保数据的完整性 。

基本语法

在 MySQL 5.7 中，可以使用 CREATE TRIGGER 语句创建触发器。

语法格式如下：

CREATE <触发器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> FOR EACH Row<触发器主体>

语法说明如下。

触发器名

触发器的名称，触发器在当前数据库中必须具有唯一的名称。如果要在某个特定数据库中创建，名称前面应该加上数据库的名称。

INSERT | UPDATE | DELETE

触发事件，用于指定激活触发器的语句的种类。
- 注意：三种触发器的执行时间如下。
- INSERT：将新行插入表时激活触发器。例如，INSERT 的 BEFORE 触发器不仅能被 MySQL 的 INSERT 语句激活，也能被 LOAD DATA 语句激活。
- DELETE：从表中删除某一行数据时激活触发器，例如 DELETE 和 REPLACE 语句。
- UPDATE：更改表中某一行数据时激活触发器，例如 UPDATE 语句。
1. BEFORE | AFTER
BEFORE 和 AFTER， 触发器被触发的时刻 ， 表示触发器是在激活它的语句之前或之后触发 。若希望验证新数据是否满足条件，则使用 BEFORE 选项；若希望在激活触发器的语句执行之后完成几个或更多的改变，则通常使用 AFTER 选项。

表名

与触发器相关联的表名， 此表必须是永久性表 ，不能将触发器与临时表或视图关联起来。在该表上触发事件发生时才会激活触发器。 同一个表不能拥有两个具有相同触发时刻和事件的触发器 。例如，对于一张数据表，不能同时有两个 BEFORE UPDATE 触发器，但可以有一个 BEFORE UPDATE 触发器和一个 BEFORE INSERT 触发器，或一个 BEFORE UPDATE 触发器和一个 AFTER UPDATE 触发器。

触发器主体

触发器动作主体， 包含触发器激活时将要执行的 MySQL 语句 。如果要执行多个语句，可使用 BEGIN...END 复合语句结构。

FOR EACH ROW
- 一般是指行级触发，对于受触发事件影响的每一行都要激活触发器的动作。例如，使用 INSERT 语句向某个表中插入多行数据时，触发器会对每一行数据的插入都执行相应的触发器动作。
注意：
- 每个表都支持 INSERT、UPDATE 和 DELETE 的 BEFORE 与 AFTER，因此每个表 最多支持 6 个触发器 。每个表的每个事件每次只允许有一个触发器。单一触发器不能与多个事件或多个表关联。
- 另外，在 MySQL 中，若需要查看数据库中已有的触发器，则可以使用 SHOW TRIGGERS 语句。

创建 BEFORE 类型触发器

在 test_db 数据库中，数据表 tb_emp8 为员工信息表，包含 id、name、deptId 和 salary 字段，数据表 tb_emp8 的表结构如下所示。

mysql> SELECT * FROM tb_emp8;
Empty set (0.07 sec)
mysql> DESC tb_emp8;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(22) | YES  | UNI | NULL    |       |
| deptId | int(11)     | NO   | MUL | NULL    |       |
| salary | float       | YES  |     | 0       |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.05 sec)

【实例 1】创建一个名为 SumOfSalary 的触发器，触发的条件是向数据表 tb_emp8 中插入数据之前，对新插入的 salary 字段值进行求和计算。输入的 SQL 语句和执行过程如下所示。

mysql> CREATE TRIGGER SumOfSalary
    -> BEFORE INSERT ON tb_emp8
    -> FOR EACH ROW
    -> SET @sum=@sum+NEW.salary;
Query OK, 0 rows affected (0.35 sec)

触发器 SumOfSalary 创建完成之后，向表 tb_emp8 中插入记录时，定义的 sum 值由 0 变成了 1500，即插入值 1000 和 500 的和，如下所示。

SET @sum=0;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO tb_emp8
    -> VALUES(1,'A',1,1000),(2,'B',1,500);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT @sum;
+------+
| @sum |
+------+
| 1500 |
+------+
1 row in set (0.03 sec)

创建 AFTER 类型触发器

在 test_db 数据库中，数据表 tb_emp6 和 tb_emp7 都为员工信息表，包含 id、name、deptId 和 salary 字段，数据表 tb_emp6 和 tb_emp7 的表结构如下所示。

mysql> SELECT * FROM tb_emp6;
Empty set (0.07 sec)
mysql> SELECT * FROM tb_emp7;
Empty set (0.03 sec)
mysql> DESC tb_emp6;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  | MUL | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> DESC tb_emp7;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | 0       |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.04 sec)

【实例 2】创建一个名为 double_salary 的触发器，触发的条件是向数据表 tb_emp6 中插入数据之后，再向数据表 tb_emp7 中插入相同的数据，并且 salary 为 tb_emp6 中新插入的 salary 字段值的 2 倍。输入的 SQL 语句和执行过程如下所示。

mysql> CREATE TRIGGER double_salary
    -> AFTER INSERT ON tb_emp6
    -> FOR EACH ROW
    -> INSERT INTO tb_emp7
    -> VALUES (NEW.id,NEW.name,deptId,2*NEW.salary);
Query OK, 0 rows affected (0.25 sec)

触发器 double_salary 创建完成之后，向表 tb_emp6 中插入记录时，同时向表 tb_emp7 中插入相同的记录，并且 salary 字段为 tb_emp6 中 salary 字段值的 2 倍，如下所示。

mysql> INSERT INTO tb_emp6
    -> VALUES (1,'A',1,1000),(2,'B',1,500);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM tb_emp6;
+----+------+--------+--------+
| id | name | deptId | salary |
+----+------+--------+--------+
|  1 | A    |      1 |   1000 |
|  2 | B    |      1 |    500 |
+----+------+--------+--------+
3 rows in set (0.04 sec)
mysql> SELECT * FROM tb_emp7;
+----+------+--------+--------+
| id | name | deptId | salary |
+----+------+--------+--------+
|  1 | A    |      1 |   2000 |
|  2 | B    |      1 |   1000 |
+----+------+--------+--------+
2 rows in set (0.06 sec)

posted @ 2025-12-13 16:45 窥光卿阅读(2) 评论(0) 收藏举报

刷新页面返回顶部

窥光卿