大数据------JavaWeb------MYSQL（完整知识点汇总，建议MYSQL直接看这个）

IT机器猫

已于 2024-05-02 15:39:28 修改

阅读量1k

点赞数 32

分类专栏：大数据第二部分JavaWeb 文章标签： sql mysql Navicat SQLyog java 大数据

于 2024-03-31 23:09:26 首次发布

本文链接：https://blog.csdn.net/cgrs5572/article/details/137211864

版权

大数据第二部分JavaWeb 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

JavaWeb

Web

即全球广域网，也称为万维网（www），能够通过浏览器访问的网站
JavaWeb

用Java技术来解决相关Web互联网领域的技术栈（即用java技术来开发网站）

JavaWeb程序可以将数据从数据库拿出来并利用JavaWeb程序将其放到网页上来展示出来
JavaWeb主要包括三部分
- 网页：展现数据
- 数据库：存储和管理数据
- JavaWeb程序：逻辑处理
JavaWeb学习内容如下：

数据库

数据库（DataBase，简称DB）定义
- 存储数据的仓库，且数据是有组织的存储
- 作用：
  - 存储数据
  - 实现数据持久化
  - 使用完整的管理系统统一管理，易于查询
数据库管理系统（Database Management System，简称DBMS）
- 管理数据库的大型软件，比如：MySQL 、Oracle、SQL Server等等
SQL（Structured Query Language）------结构化查询语言
- 是操作关系型数据库的编程语言
- 它定义了操作所有关系型数据库的统一标准
- 对于同一个需求，每一种是数据库操作的方式可能会存在一些不一样的地方，我们称之为”方言“
- 优点
  - SQL不是某个特定数据库供应商专有的语言，几乎所有的DBMS都支持SQL
  - 简单易学
  - 可以进行非常复杂和和高级的数据库操作
常见的关系型数据库管理系统

MYSQL

MYSQL登录语句

mysql -u用户名 -p密码 -h要连接的mysql服务器的ip地址（默认为本地ip：127.0.0.1） -p端口号(默认3306)

MySQL端口号查询------SHOW GLOBAL VARIABLES LIKE "port";
关系型数据库
- 该数据库是建立在关系模型上的数据库。简单来说，就是由多张能互相连接的二维表组成的数据库
- 优点
  - 都是使用表格是，格式一致，易于维护
  - 使用通用的SQL语言操作，使用方便，可用于复杂查询
  - 数据存储在磁盘中，安全

在这里插入图片描述

MYSQL数据模型解释
- 通过客户端进行本地或远程访问MYSQL数据库管理系统，通过数据库管理系统可创建多个数据库，并可在不同数据库中创建数据表，而数据表中包含数据，关系如图所示

MYSQL数据类型

命名规则------和java一样
MYSQL数据类型分为三种

在这里插入图片描述

数据类型	解释
INT	从-2^31到 2^31-1的整形数据，存储大小为4个字节
CHAR(size)	定长字符数据，若未指定，默认为1个字符，最大长度为255。注意：与java不同，数据库中的char也可以放字符串，但是一般用varchar放字符串
VARCHAR(size)	可变长字符数据，根据字符串实际长度保存，必须指定长度
FLOAT(M,D)	单精度，M = 整数位+小数位， D= 小数位，默认M+D<=6。比如：FLOAT(5,3),5代表整数位+小数位的长度为5，3代表小数位的长度为3，则整数位的长度为2
DOUBLE(M,D)	双精度，M = 整数位+小数位， D= 小数位，默认M+D<=15。与单精度类似
DATE	日期型数据，格式”YYY-MM-DD“
BLOB	二进制形式的长文本数据，最大可达4G。可存视频、图片等的地址
TEXT	长文本数据，最大可达4G。可存文本等的地址

数值类型
- 定义一个年龄：age int
- 定义一个0~100且保留小数点后两位的数字 字段名 double(数字总长度,小数点后保留的位数)：num double (5,2)第一个参数为5的原因：0-100最大的数100有三个数字组成，并且保留小数点后两位，所以5=3+2
日期类型
- DATETIME和TIMESTAMP的区别：
字符串类型
- CHAR与VARCHAR的比较
  
  定义一个名字且字符数最大位为10：name char(10)或name varchar(10)，假设现在存入一个名字为张三，若将其存到CHARA数据类型中：此时虽然张三只有两个字符，但是其会占满10个字符，空余部分用空格补全；若将其存到VARCHAR数据类型中，则其只会占两个字符位置，剩余8个字符为空。
- CHAR特点
  - CHAR类型是固定长度的字符数据类型，它要求所有的数据都被填充到指定的长度。
  - 当存储一个CHAR类型的值时，如果数据长度小于指定的长度，MySQL会在数据的右侧用空格进行填充，使其达到指定长度。
  - 由于CHAR类型的数据是固定长度的，所以在存储和检索时不需要额外的长度前缀或结束符号，这使得存储和检索的过程更加高效。
  - 对于小型的数据和长度固定的数据，使用CHAR类型可以减少存储开销和I/O负载，提高存储性能。
- VARCHAR特点
  - VARCHAR类型是可变长度的字符数据类型，它可以存储长度可变的字符数据，但是需要额外的长度前缀来存储数据的实际长度。
  - 当存储一个VARCHAR类型的值时，MySQL会在数据前面加上一个字节或两个字节的长度前缀来记录数据的实际长度。
  - 由于VARCHAR类型的数据是可变长度的，所以在存储和检索时需要额外的长度前缀，这会增加存储和检索的开销。
  - 对于大量的可变长度数据或长度不固定的数据，使用VARCHAR类型可以节省存储空间，但是可能会牺牲一定的存储性能。
CHAR的存储性能高但浪费空间，VARCHAR的存储性能低但节约空间
char定长字符数据和varchar可变长字符数据的区别？

答：举例说明：假如我现在对char和varchar的长度size都设置为5，此时分别向char和varchar中写了一个字“男”，此时在char中该字符实际占用的长度为5，但在varchar中其实际占用的长度为1。一般情况下，字符用char，字符串用varchar

MySQL5.7安装

根据标记序号依次点击进行安装操作

Step1：
Step2：
Step3：更改MySQL安装路径和数据库安装路径，然后点击OK；随后会跳出一个如图二的警告，仍点击OK即可。
Step4：
Step5：

请添加图片描述

Step6：点击Execute后会弹出一个让你安装需要支撑的环境窗口即Visul C++2013，直接点击install即可。
Step7：
Step8：安装完成后一直点击next，直到如下图所示为止。将圈住的地方改为MINIMAL，然后点击next
Step9：
Step10：设置密码，然后一直next
Step11：点击Execute等待安装成功后点击Finish—>next—>Finsh
Step12：测试是否安装成功cmd打开命令提示符—>cd 进到MySQL的bin目录下，即F:\app\MySQL\MySQL Server 5.7\bin，然后输入mysql -u root -p回车并输入密码。若出现图示圈住内容代表成功进入MySQL即可使用MySQL。为了能够不切盘更简便的使用MySQL，则需配置环境变量
Step13：配置环境变量，注意是MySQL的bin目录路径加入到Path中。
Step14：cmd测试是否成功，此时不需要切盘就可进入mysql

MYSQL卸载

Step1：结束MySQL后台进程：右键点击电脑—>管理—>服务和应用程序—>然后找到MySQL服务名右键—>停止
Step2：卸载MySQL：控制面板—>卸载程序，按顺序将圈住的两个都卸载。
Step3：去对应盘中删除MySQL文件夹中的所有文件
- 注意：目录里有数据库和配置文件，确定不需要在删；若不删除，则在再次安装时选其他目录安装
Step4：删除服务名：以管理员身份运行cmd—>输入sc delete MySQL57回车。MySQL57为MySQL对应版本的服务名，不知道可根据Step1的步骤进行查看。
Step5：清理注册表（详见百度）

经过以上系统即可彻底卸载，若仍有问题，则重装系统，别无他法。

配置MySQL执行日志

该配置信息要添加到MySQL安装目录下的my.ini中（可用记事本打开添加）

作用
- 生成MySQL日志文件（重启MySQL服务后才可生效）
- 帮助 MySQL 数据库管理员监控数据库的查询活动、排查性能问题和优化查询。

代码如下：

log-output=FILE
general-log=0
general_log_file="DESKTOP-KICELNQ.log"
slow-query-log=1
slow_query_log_file="DESKTOP-KICELNQ-slow.log"
long_query_time=10

代码解释
- log-output：指定日志输出的目标。 FILE：表示日志输出到文件；TABLE：输出到数据库表；NONE：禁用日志输出。
- general-log：是否启用一般查询日志。0代表禁用；1代表启用
- general_log_file：一般查询日志的输出文件名及路径。若不指定路径则该一般日志文件保存在MySQL目录下的Data目录下
- slow-query-log：是否启用慢查询日志。0代表禁用；1代表启用
- slow_query_log_file：慢查询日志的输出文件名及路径。若不指定路径则该一般日志文件保存在MySQL目录下的Data目录下
- long_query_time：慢查询的阈值时间（以秒为单位），表示超过阈值时间的查询将会被记录到慢查询日志中

图形化客户端工具

解释

是一类用于管理和操作MySQL数据库的软件，提供了直观友好的图形用户界面（GUI），使用户可以通过可视化的方式进行数据库管理和开发
作用

为数据库的管理、开发和维护提供了一款直观且强大的图形界面
常见的两种MySQL图形化客户端工具及其特点
- Navicat for MySQL：Navicat是一款功能强大的数据库管理工具，支持多种数据库，包括MySQL、MariaDB、Oracle等。它提供了直观的用户界面和丰富的功能，包括数据模型设计、数据同步、备份和恢复、数据可视化等。
- SQLyog：SQLyog是一款简单易用的MySQL管理工具，具有直观的用户界面和丰富的功能，包括SQL编辑器、数据库备份、数据同步、SSH和SSL连接等。SQLyog支持Windows系统。

SQLyog安装

Step1：一路next知道如图所示停下，更改安装路径，然后点击安装–>下一步。完成后不要打开SQLYog，继续Step2操作。
请添加图片描述

Step2：双击SQLyog-11.2.5-0.x64.reg点击是–>确定即可
Step3：打开SQLyog—>新建—>输入名称(英文)—>确定，然后会出现下图。然后输入密码点击连接即可。

注意：“我的SQL主机地址” 为Mysql服务器主机地址，localhost代表本机MySQL服务器，若你想连接其他公司的数据库，你需要将"我的SQL主机地址"改为公司的MySQL服务器主机地址；**“用户名”**为登录MySQL的用户名； “密码” 和 “端口” 分别为MySQL的密码和端口号。

SQLyog导入数据

Step1：
Step2：选择你所导入的sql文件的位置，然后点击执行
Step3：当出现导入成功后点击完成即可。
Step4：然后右键点击MySQL服务器—>刷新，则会出现你导入的sql文件，即myemployees

Navicat安装

Navicat安装省略

SQL

SQL通用语法
- SQL语句可以单行或多行书写，以分号作为结尾标志
- MYSQL数据库的SQL语句不区分大小写，关键字建议使用大写且关键字不能被缩写或分行
- 各子句之间一般要分行写
- 注释
  - 单行注释：--注释内容或#注释内容（MYSQL特有）
  - 多行注释：/*多行注释*/
SQL语句功能分类
- DDL（Data Definition Language）数据定义语言，用来定义数据库对象，比如：数据库、表、列等
- DML（Data Manipulation Language）数据操作语言，用来对数据库中表的数据进行增删改
- DQL（Data Query Language）数据查询语言，用来查询数据库中表的记录（数据）
- DCL（Data Control Language）数据控制语言，用来定义数据库的访问权限和安全级别，以及创建用户（即管理用户，授权）

运算符

符号	解释
`=`	等于
`>`	大于
`>=`	大于等于
`<`	小于
`<=`	小于等于
`<>`	不等于（也可以是!=，但不推荐）
`AND`	且（也可以是&&，但不推荐）
`OR`	或（也可以是\|\|,但不推荐）
`NOT`	非（也可以是！但是不推荐）
`BETWEEN a AND b`	在a和b范围内（包含a和b）等同于 `字段 <=a AND 字段>= b`（a,b顺序不能变）
`IN(a,b,...)`	多选一，等同于`字段 = a OR 字段 = b OR ...`
`IS NULL`	是NULL
`IS NOT NULL`	不是NULL
`LIKE 占位符`	模糊查询 `_`：单个任意字符 `%`：多个任意字符
`\`	转义字符，将具有特殊含义的符号转化为不具有特殊含义的普通符号，详见三-4-3
`ESCAPE`	指定转义字符，详见三-4-3

例2-1：查询薪水在6000到10000之间的员工

#方式一
SELECT first_name,last_name,salary
FROM employees
WHERE salary >= 6000 AND salary <=10000;
#方式二
SELECT first_name,last_name,salary
FROM employees
WHERE salary BETWEEN 6000 AND 10000;

#注意：方式二中BETWEEN 6000 AND 10000相当于大于等于6000并且小于等于10000

请添加图片描述

例2-2：查询30或50号部门中的所有员工

#方式一
SELECT first_name,last_name,department_id
FROM employees
WHERE department_id = 30 OR department_id = 50;
#方式二
SELECT first_name,last_name,department_id
FROM employees
WHERE department_id IN(30,50);

请添加图片描述

DDL操作数据库

查询当前MYSQL下的所有数据库并显示出来------SHOW DATABASE;

如图所示，以上四个数据库为MYSQL安装好之后自带的数据库，解释如下：

information_schema：这个数据库包含了关于MySQL数据库中的所有其他数据库、表、列、索引、权限等元数据（metadata）信息。通过查询information_schema数据库，可以获取关于数据库结构和配置的详细信息，比如表的名称、列的数据类型、索引的定义等。

mysql：这个数据库存储了MySQL数据库系统的内部数据和系统级别的信息。包括用户账号信息、权限信息、密码规则、连接控制等。

performance_schema：这个数据库提供了MySQL数据库内部的性能监控和统计信息，用于帮助管理员分析数据库的性能瓶颈、优化查询和调整配置。

sys：这个数据库是MySQL 5.7版本及以上版本新增的，它是对performance_schema数据库的进一步封装和提供了更加易于理解和使用的视图（views）和存储过程（stored procedures）。sys数据库中的对象可以帮助管理员更直观地监控和分析数据库的性能指标，并提供了一些用于性能优化的工具和函数。

注意：

information_schema：与其他三个数据库不同，其存储数据是通过视图表存储的，而视图表是一种逻辑表，不存在物理文件，所以在MYSQL的data目录中不会存在该数据库的目录
创建
- 创建数据库------CREATE DATABASE [IF NOT EXISTS] 数据库名称 [CHARACTER SET '编码集'];
- 创建数据库，若不存在则创建------CREATE DATABASE IF NOT EXISTS 数据库名称;
删除
- 删除数据库------DROP DATABASE 数据库名称;
- 删除数据库，若存在则删除------DROP DATABASE IF EXISTS 数据库名称;
使用数据库
- 使用数据库------USE 数据库名称;
- 查看所有（可用的）库------SHOW DATABASES;
- 查看当前使用的数据库------SELECT DATABASE();
- 查看库的信息------SHOW CREATE DATABASE 库名;
编辑库
- 修改库的编码集------ALTER DATABASE 库名 CHARACTER SET '编码集';
查看系统中的属性和值------SHOW VARIABLES LIKE '%char%';

DDL操作表

表的默认编码集和库的编码集一致

创建表方式一

CREATE TABLE [IF NOT EXISTS] 表名 (
		字段名1 数据类型1，
		字段名2 数据类型2，
		...
		字段名n 数据类型n
)[CHARACTER SET '编码集'];

注意：创建表时，最后一行末尾不加逗号

例题：
在这里插入图片描述
代码实现：

CREATE TABLE student(
	id int,
    name varchar(10),#由于姓名有长有短，所以用变长字符串
    gender char(1),#由于性别固定是1个字，所以用定长字符串
    birthday date,
    score double(5,2),
    email varchar(64),
    tel varchar(15),
    status tinyint#题中已说明用数字表示状态，假设状态一共小于10种，所以可以用一个字节的TINYINT数据类型
);

创建表方式二：将查询结果保存在新创建的表中
```
CREATE TABLE IF NOT EXISTS 表名
AS
查询语句;
```
将查询结果放在创建的表中
- 解释示例
当你在选中db1库时输入一下代码来查询myemployees库中的employees表中的信息时会报错，因为此时db1中不存在employees表，该表是在myemployees库中的。
```
SELECT first_name, salary
FROM employees;
```
报错是 由于此时选中的是db1库，而不是myemployees库 ，所以会报错，解决办法：FROM后写为：库名.表名，如下所示
```
SELECT first_name, salary
FROM myemployees.employees;
```
现在我想要将该查询结果保存到一张新表emp1中则代码如下：
```
CREATE TABLE IF EXISTS emp1;
CREATE TABLE emp1
AS
SELECT first_name, salary
FROM myemployees.`employees`;
```

在这里插入图片描述

注意：由于选中的是db1库，所以表emp1是在db1库下的，如图所示

在这里插入图片描述

创建表方式三：基于现有的表结构创建一张新表（表中没有内容）

CREATE TABLE [IF NOT EXISTS] 新建表名 LIKE 库名.表名;
# 如下所示
CREATE TABLE emp2 LIKE myemployees.`employees`;

在这里插入图片描述

创建表方式四：右键表—>创建（新建）表，如图所示，然后填入相应内容点击保存即可。
- SQLyog
- Navicat
查询表
- 查询当前数据库下所有表的名称------SHOW TABLES;
- 查询表结构------DESC 表名称;
注意：

查询表之前，要先确定查找的是哪个数数据库中的表，所以要先利用USE 数据库名称;来进入到指定的数据库中然后在进行查询，但是在图形化客户端工具中不需要使用该代码，只需点击数据库即可进入到该数据库中然后直接输入查询表结构的SQL语句即可
删除表
- 删除表------DROP TABLE 表名;
- 删除表，若该表存在则删除------DROP TABLE IF EXISTS 表名;
修改表
- 修改表名------ALTER TABLE 表名 RENAME TO 新表名;
- 添加一列------ALTER TABLE 表名 ADD 列名数据类型;
- 修改数据类型------ALTER TABLE 表名 MODIFY 列名新数据类型;
- 修改列名和数据类型------ALTER TABLE 表名 CHANGE 列名新列名新数据类型;
- 删除列------ALTER TABLE 表名 DROP 列名;
注意：在图形化客户端工具中修改表时可以不使用SQL语句，直接右键要修改的表然后单击”修改（该表）表“即可对其进行各种修改

DML添加数据

SQL语句如下：

语句	解释
`INSERT INTO 表名(列名1, 列名2,...) VALUE(值1,值2,...);`	给指定的列添加数据（即插入单行数据）
`INSERT INTO 表名 VALUE (值1,值2,...);`	给全部的列添加数据（即插入单行数据）
`INSERT INTO 表名(列名1, 列名2,...) VALUES(值1, 值2,...),(值1, 值2,...)...;`	批量给指定的列添加数据（即插入多行数据）
`INSERT INTO 表名 VALUES(值1, 值2,...),(值1, 值2,...)...;`	批量给表中所有的列添加数据（即插入多行数据）
`INSERT INTO 表名(字段名1,字段名2,...) SELECT 字段名1, 字段名2, ... FROM ...;`	将查询的结果插入表中（注意：查询的字段的类型和个数要和被插入的字段的类型和个数保持一致，否则会报错）

注意：

（1）在实际的开发过程中，若要给全部的列添加数据建议使用INSERT INTO 表名(列名1,列名2,...) VALUE(值1,值2,...);该代码，这样可以清楚知道每一列的字段名

（2）批量给指定的列添加数据时用VALUES，单行添加数据时用VALUE

（3）在使用以上四种SQL语句进行数据添加时，之前添加的数据并不会被之后添加的数据给替换掉（即会保留之前利用SQL语句添加的数据），如下列示例截图所示

以创建的空学生表为例进行代码示例，如图为空的学生表

在这里插入图片描述

给指定的列添加数据（即插入单行数据）

INSERT INTO student(id, name) VALUES(01, '张三');
SELECT * FROM student;

在这里插入图片描述

给全部的列添加数据（即插入单行数据）

#给全部的列添加数据
INSERT INTO student VALUES(01, '张三', '男', '2015-02-03' , 130, '3033894112.@qq.com', '132-2023-3316',1);
SELECT * FROM student;

在这里插入图片描述

批量给指定的列添加数据（即插入多行数据）

INSERT INTO student(id, name, gender) VALUES(03, '李四', '男'),(04, '李四',  '男'),(05, '李四', '男');
SELECT * FROM student;

在这里插入图片描述

批量给表中所有的列添加数据（即插入多行数据）

INSERT INTO student
VALUES
	( 06, '王五', '男', '2016-05-06', 120, '10195423359.@qq.com', '132-2965-3719', 2 ),
	( 07, '王五', '男', '2016-05-06', 120, '10195423359.@qq.com', '132-2965-3719', 2 ),
	( 08, '王五', '男', '2016-05-06', 120, '10195423359.@qq.com', '132-2965-3719', 2 );
SELECT * FROM student;

在这里插入图片描述

将查询结果插入到表中

INSERT INTO student(id, NAME)
SELECT employee_id, first_name
FROM myemployees.`employees`;

在这里插入图片描述

DML修改、删除数据

SQL语句如下：

语句	解释
`UPDATE 表名 SET 字段名1 = 值1,字段名2 = 值2,...[WHERE 条件];`	修改表中数据(若不写筛选条件则会修改表中的所有数据)
`DELETE FROM 表名 [WHERE 条件];`	删除表中数据（若不加筛选条件则会删除表中所有数据）
`TRUNCATE TABLE 表名;`	删除表中的所有数据，该SQL语句无法开启事务（注意：该语句是一个DDL语句，它直接操作表的结构，而不是逐行操作数据，因此执行速度更快。）
`ALTER TABLE 表名 ADD COLUMN 字段名数据类型;`	向表中添加列
`ALTER TABLE 表名 CHANGE COLUMN 原字段名新字段名原数据类型;`	修改列的名字
`ALTER TABLE 表名 MODIFY COLUMN 字段名新数据类型;`	修改字段的类型
`ALTER TABLE 表名 DROP COLUMN 字段名;`	删除字段

使用语句TRUNCATE TABLE 表名;时无法开启数据库事务。即在数据库事务情况下利用该SQL代码进行删除数据时，无法进行事务回滚（即无法恢复删除的数据）；而数据库事务对“DELETE FROM 表名;”是生效的。

DELETE FROM 表名;和TRUNCATE TABLE 表名;的区别？
- DELETE FROM 可以进行事务回滚（因为它在执行时会生成事务日志，可以记录在事务日志中，从而可以在事务回滚时恢复已删除的数据。），而TRUNCATE TABLE则不可以进行事务回滚（即无法恢复已删除的数据。因为 TRUNCATE TABLE 是自动提交的，它不会生成事务日志，也不记录在事务日志中，无法进行回滚操作。）
- 若确定删除后不需要回滚则可以使用TRUNCATE TABLE（效率高）

修改删除表中数据语句以DML添加数据中最终的student表（如图所示）为例

在这里插入图片描述

修改表中数据

UPDATE student SET name = '李1',gender = '女' WHERE id = 1;
SELECT * FROM student;

在这里插入图片描述

删除表中数据

DELETE FROM student WHERE id = 1;
SELECT * FROM student;

在这里插入图片描述

向表中添加、删除、修改列以emp1表为例，如下，可看出表中现在只有两列，字段名分别为id和ename

在这里插入图片描述

向表中添加列
```
ALTER TABLE emp1 ADD COLUMN child_num INT;
DESC emp1;#查看表修改后的信息
```
查看表的信息，可看出此时表中多了一列，其字段名为child_num，如图所示

修改列的名字

ALTER TABLE emp1 CHANGE COLUMN child_num age INT;
DESC emp1;#查看表修改后的信息

由图可看出，已将字段名child_name改为age

在这里插入图片描述

修改字段的类型

-- 将age的数据类型从int改为字符串类型
ALTER TABLE emp1 MODIFY COLUMN age VARCHAR(20);
DESC emp1;#查看表修改后的信息

在这里插入图片描述

删除字段

ALTER TABLE emp1 DROP COLUMN age;
DESC emp1;#查看表修改后的信息

在这里插入图片描述

DQL查询

SQL语法如下：

SELECT [DISTINCT] -- DISTINCT关键字：去除字段中的重复记录，可根据实际需求判断是否写此关键字
	字段1 [AS 别名1], 字段2 [AS 别名2],... 
    [聚合函数1, 聚合函数2,...]
FROM
	表名列表
WHERE<!--条件查询-->
	条件列表
GROUP BY<!--分组查询-->
	字段1 [排序方式1], 字段2 [排序方式2], ...
HAVING
	分组后条件
ORDER BY<!--排序查询-->
	排序字段
LIMIT<!--分页查询-->
	分页限定

注意：

查询表中的所有数据就相当于查询该表的所有字段，sql语句为：SELECT * FROM stu;，在实际的项目中一般不用*号来代替所有的字段，一般会将所有的字段均写出来
若在查询表中数据时，想去除表中重复数据则可在SELECT后加上关键字DISTINCT
在查询表中的字段对应的内容时，可以利用字段 AS 别名或字段别名（即AS可省略）来为字段起一个能看懂的别名

针对排序查询：

排序方式：ASC------升序排列（默认） DESC------降序排列
若有多个排序条件，按照最前面字段的排序条件进行排序，排序后若存在相同的值才会依次按照后面字段的排序条件进行排序

例题：创建一个stu表，并以该表为例，如下：

#创建stu表
CREATE TABLE stu (
	id INT,-- 编号
	name VARCHAR(20),-- 姓名
	age INT,-- 年龄
	sex VARCHAR(5),-- 性别
	address VARCHAR(100),-- 地址
	math DOUBLE(5,2),-- 数学成绩
	english DOUBLE(5,2),-- 英语成绩
	hire_date DATE-- 入学时间
);

#给表中添加数据
INSERT INTO stu(id,name,age,sex,address,math,english,hire_date)
VALUES
(1, '马云', 55, '男', '杭州', 66, 78, '1995-09-01'),
(2, '马花疼', 45, '女', '深圳', 98, 87, '1998-09-01'),
(3, '马斯克', 55, '男', '香港', 56, 77, '1999-09-02'),
(4, '柳白', 20, '女', '湖南', 76, 65, '1997-09-05'),
(5, '柳青', 28, '男', '湖南', 86, NULL, '1998-09-01'),
(6, '刘德花', 57, '男', '香港', 99, 99, '1998-09-01'),
(7, '张学右', 22, '女', '香港', 99, 99, '1998-09-01'),
(8, '德玛西亚', 18, '男', '南京', 56, 65, '1994-09-02');
#查看stu表
SELECT * FROM stu;

在这里插入图片描述

基础查询

#查询name、age两列
SELECT name,age FROM stu ;

在这里插入图片描述

#查询地址信息并去除重复信息 
SELECT DISTINCT address FROM stu;

在这里插入图片描述

#查询学生的数学和英语成绩并将字段名改为中文
SELECT name,math AS '数学成绩',english AS '英语成绩' FROM stu;
#等同于
SELECT name,math '数学成绩',english '英语成绩' FROM stu;

在这里插入图片描述

条件查询

#查询年龄>20的信息
SELECT * FROM stu WHERE age > 20;

#查询年龄>=20的信息
SELECT * FROM stu WHERE age >= 20;

#查询年龄>=20且年龄<=30随的信息
SELECT * FROM stu WHERE age >= 20 AND age <= 30;
#等同于
SELECT * FROM stu WHERE age BETWEEN 20 AND 30;

#查询入学日期在'1998-09-01'到'1999-09-01'之间的学员信息
SELECT * FROM stu WHERE hire_date >= '1998-09-01' AND hire_date <= '1999-09-01';
#等同于
SELECT * FROM stu WHERE hire_date BETWEEN '1998-09-01' AND '1999-09-01';

#查询年龄等于18的学员信息
SELECT * FROM stu WHERE age = 18;

#查询年龄不等于18的学员信息
SELECT * FROM stu WHERE age <> 18;

#查询年龄等于18或20或22的学员信息
SELECT * FROM stu WHERE age = 18 OR age = 20 OR age = 22;
#等同于
SELECT * FROM stu WHERE age IN (18,20,22);

#查询英语成绩为null的学院信息
SELECT * FROM stu WHERE english IS NULL;

#查询英语成绩不为null的学院信息
SELECT * FROM stu WHERE english IS NOT NULL;

条件查询------模糊查询

#查询姓‘马’的学员信息
SELECT * FROM stu WHERE NAME LIKE '马%';

#查询第二个字是‘花’的学员信息
SELECT * FROM stu WHERE NAME LIKE '_花%';

#查询名字中包含‘德’的学员信息
SELECT * FROM stu WHERE NAME LIKE '%德%';

排序查询

#查询学生信息，按照年龄升序排列
SELECT * FROM stu ORDER BY age;
#等同于
SELECT * FROM stu ORDER BY age ASC;

#查询学生信息，按照数学成绩降序排列
SELECT * FROM stu ORDER BY math DESC;

#查询学生信息，按照数学成绩降序排列,若数学成绩一样则按照英语成绩升序排列
SELECT * FROM stu ORDER BY math DESC, english;
#等同于
SELECT * FROM stu ORDER BY math DESC, english ASC;

补充内容

查看表中内容

（1）查看表中部分字段
- SELECT 字段名1,字段名2,...... FROM 表名;，字段名就是表中的列索引名
```
SELECT last_name,salary FROM employees;
#等同于（可分行写）
SELECT last_name,salary
FROM employees;
```
（2）查询表中所有字段
- SELECT * FROM 表名;*表示所有的字段。

查询所有员工薪水加1000后的薪水(不会改变原表中的数据)

SELECT salary,salary+1000 FROM employees;
#等同于
SELECT salary,salary+1000
FROM employees;

请添加图片描述

任何类型的数据和Null做运算，结果均为Null，如下：

SELECT salary,salary+NULL FROM employees;
#等同于
SELECT salary,salary+NULL
FROM employees;

请添加图片描述

输出语句
- DUAL：伪表（即虚拟表），可以省略不写

SELECT 2+1;
#等同于
SELECT 2+1
FROM DUAL

请添加图片描述

更改字段名

语句实现------SELECT 字段名1 别名,字段名2 AS 别名,... FROM 表名;

注意：有无AS都可。且执行时先执行FROM后的代码

SELECT employee_id,employee_id+10 new_id,salary,salary+1000 new_salary1 
FROM employees;
#等同于
SELECT employee_id,employee_id+10 AS new_id,salary,salary+1000 AS new_salary1
FROM employees;

请添加图片描述

更改别名时，若别名中有空格，则需要用单引号或双引号将其包裹，如下：

SELECT employee_id,employee_id+10 "new id",salary,salary+1000 "new salary1"
FROM employees;

请添加图片描述

过滤

SELECT 字段名1,字段名2,......
FROM 表名
WHERE 过滤条件;

例3-1：查询薪资大于6000的员工

SELECT first_name,last_name,salary
FROM employees
WHERE salary > 6000;

请添加图片描述

例3-2：查询部门为30和50的员工有哪些

#方式一
SELECT first_name,last_name,department_id
FROM employees
WHERE department_id = 30 OR department_id = 50;
#方式二
SELECT first_name,last_name,department_id
FROM employees
WHERE department_id IN(30,50);

请添加图片描述

例3-3：查询除80号部门的所有员工

#SQL中<>为不等于(推荐),也可以写!=
SELECT first_name,last_name,department_id
FROM employees
WHERE department_id <> 80;

请添加图片描述

例3-4：查询薪水在6000到10000之间的员工

SELECT first_name,last_name,salary
FROM employees
WHERE salary >= 6000 AND salary <=10000;
#也可以用
SELECT first_name,last_name,salary
FROM employees
WHERE salary BETWEEN 6000 AND 10000;
#若改为不在6000到10000则加个NOT即可，详见例6

请添加图片描述

例3-5：查询奖金率为null的员工，查询奖金率不为null的员工

注意：

（1）判断值是NULL，用IS判断，不能用=判断

（2）判断值不是NULL，用IS NOT判断，不能用<>判断

#查询奖金率为null的员工
SELECT first_name,last_name
FROM employees
WHERE commission_pct IS NULL;
#查询奖金率不为null的员工
SELECT first_name,last_name
FROM employees
WHERE commission_pct IS NOT NULL;

例3-6：查询员工的薪水不在6000到10000之间的员工

SELECT first_name, salary
FROM employees
WHERE NOT salary >= 6000 AND salary <= 10000;

#等同于
SELECT first_name,last_name,salary
FROM employees
WHERE salary NOT BETWEEN 6000 AND 10000;

在这里插入图片描述

模糊查询

LIKE------模糊查询

用到的一些符号
- %------表示任意个数的任意字符，详见例4-1
- _------表示任意一个字符，详见例4-2
- \ ------转义字符，表示将具有特殊含义的符号转化为普通符号，不具特殊含义，详见4-3

例4-1：查询姓名中包含f的员工

SELECT first_name
FROM employees
WHERE first_name LIKE '%f%';
#f左边的%代表f前可有任意个数的任意字符
#f右边的%代表f后可有任意个数的任意字符

请添加图片描述

例4-2：查询姓名中的第二个字符为d的员工

SELECT first_name
FROM employees
#_代表任意一个字符
WHERE first_name LIKE '_d%';

请添加图片描述

例4-3：查询姓名中第二个字符为_的员工

#方式一：使用转义字符\
SELECT first_name
FROM employees
#\_表示内容为_
WHERE first_name LIKE '_\_%';
#方式二：利用ESCAPE指定转义字符
SELECT first_name
FROM employees
#将$作为转义字符，此时$_表示内容为_
WHERE first_name LIKE '_$_%' ESCAPE '$';

在这里插入图片描述

例4-4：查询姓名中既包含a又包含e的员工

#方式一
SELECT first_name
FROM employees
WHERE first_name LIKE '%a%e%' OR '%e%a%';
#方式二
SELECT first_name
FROM employees
WHERE first_name LIKE '%a%' AND first_name LIKE '%e%';

在这里插入图片描述

排序

**升序—ASC **------缺省默认为升序
降序—DESC

SELECT first_name,last_name,salary
FROM employees
ORDER BY salary DESC;

在这里插入图片描述

例5-2：查询80号部门员工的姓名和薪水，且薪水按照升序排序

SELECT first_name,last_name,department_id,salary
FROM employees
WHERE department_id = 80
ORDER BY salary;

在这里插入图片描述

例5-3：将所有员工的薪水+1000，并对加薪后的薪水进行降序排序

SELECT first_name,last_name,salary,salary+1000 AS "new salary"
FROM employees
#注意：排序时用双引号如果报错的话就用飘号即esc下面的那个键，推荐飘号
ORDER BY `new salary` DESC;

在这里插入图片描述

例5-4：查询所有员工的姓名、薪水、部门号，并对部门号进行降序排序，若部门号相同再按照薪水进行升序排序

SELECT first_name,last_name,department_id,salary
FROM employees
ORDER BY department_id DESC,salary ASC;

在这里插入图片描述

分组查询（分组函数）包括聚合函数（组函数）

以stu表为例，stu表如图所

在这里插入图片描述

聚合函数定义

将一列数据作为一个整体进行纵向计算

聚合函数分类

函数名	解释
`count(列名)`	统计数量（count统计的字段对应的列中不能为null，即一般选用不为null的列）
`max(列名)`	最大值
`min(列名)`	最小值
`sum(列名)`	求和
`avg(列名)`	平均值

注意：

（1）利用count(列名)统计数量时可以用count(*)来统计，原因：用*时只要一行中有不为null的值就可将该行统计进去。也可以使用count(数值)来统计，其作用与count(*)一样，详见代码

（2）求平均值时不包括该列中的null值。举例说明：假如一个字段的列共有20个数据，其中5个null。该字段总和为sum，则平均值为sum/（20-15）

（4）AVG()和SUM()两个函数只能对数值类型做运算

SELECT COUNT(commission_pct), COUNT(*), COUNT(1), COUNT(100)
FROM employees;

在这里插入图片描述

聚合函数语法------SELECT 聚合函数名(列名) FROM 表;
- null值不参与所有聚合函数的运算（即null值会自动被聚合函数忽略）

#统计班级一共多少个学生
SELECT COUNT(id) FROM stu;
#等同于
SELECT COUNT(*) FROM stu;

#查询数学成绩的最高分
SELECT MAX(math) FROM stu;

#查询数学成绩的最低分
SELECT MIN(math) FROM stu;

#查询数学成绩总分
SELECT SUM(math) FROM stu;

#查询数学成绩平均分
SELECT AVG(math) FROM stu;

#查询英语成绩的最低分
SELECT MIN(english) FROM stu; -- 结果为65而不是null

#求所有员工薪水的最大值、最小值、平均值、总和
SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary) 
FROM employees;

分组查询语法

SELECT 字段列表聚合函数列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤]

注意：

分组之后，查询(SELECT)的字段只能为聚合函数和分组的字段（即GROUP BY后的分组字段名），查询其他字段无任何意义，即此时SELECT 字段列表聚合函数列表中的字段列表一般是分组字段列表，作用：清楚知道聚合函数统计的是什么分组的内容，详见例子
WHERE和HAVING的区别
执行时机不同：WHERE是分组之前进行限定，不满足WHERE条件则不参与分组；HAVING是分组之后对结果进行过滤
可判断的条件不一样：WHERE不能对组（聚合）函数进行判断，而HAVING可以。（即WHERE后不允许出现组（聚合）函数，否则会报错；而HAVING后可使用组函数，且HAVING一般不单独用，常和GROUP BY一起使用）

执行顺序：WHERE>聚合函数>HAVING

错误示例

SELECT department_id, AVG(salary)
FROM employees;
/*错误原因：
select后同时出现在字段和组函数
*/

正确示例

SELECT department_id, AVG(salary)
FROM employees
WHERE department_id IN(10,20,30)
GROUP BY department_id
HAVING AVG(salary) > 5000;

#查询男同学和女同学各自的数学平均分
SELECT sex, AVG(math) FROM stu GROUP BY sex;

#查询男同学和女同学各自的数学平均分以及各自人数
SELECT sex, COUNT(*) persons_sum, AVG(math) FROM stu GROUP BY sex;

#查询男同学和女同学各自的数学平均分以及各自人数,要求：分数低于70分的不参与分组
SELECT sex, COUNT(*), AVG(math) FROM stu WHERE math > 70 GROUP BY sex;

#查询男同学和女同学各自的数学平均分以及各自人数,要求：分数低于70分的不参与分组,且分组之后人数大于2
SELECT sex, COUNT(*), AVG(math) FROM stu WHERE math > 70 GROUP BY sex HAVING COUNT(*) > 2;

分页查询

语句格式

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询条目数

注意：

起始索引：从0开始，起始索引=（当前页码-1）*每页显示的条数
分页查询limit是MySQL数据库的方言，Oracle分页查询使用rownumber，SQL Server分页查询使用top

#从0开始查询，查询3条数据
SELECT * FROM stu LIMIT 0, 3;
#每页显示3条数据，查询第1页数据
SELECT * FROM stu LIMIT 0, 3;
#每页显示3条数据，查询第2页数据
SELECT * FROM stu LIMIT 3, 3;
#每页显示3条数据，查询第3页数据
SELECT * FROM stu LIMIT 6, 3;

DCL管理用户

DCL管理用户的功能如下：
- 查询用户
  - 公司所有的员工（用户）信息都被存放在MYSQL自带的数据库mysql中的user表中
  - 步骤：
    - 切换到mysql数据库------USE mysql;
    - 查询user表------SELECT * FROM user;
```
-- 切换到mysql数据库
USE mysql;
-- 查询user表
SELECT * FROM user;
```
    注意：其中的%为通配符，表示可以在任意主机使用root用户登录数据库
- 添加（创建）用户------CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
```
 CREATE USER '张三'@'localhost' IDENTIFIED BY '123';-- 在当前主机上创建一个名为张三的用户
 CREATE USER '李四'@'%' IDENTIFIED BY '123';-- 创建一个在任意电脑上均可登录的用户
 SELECT * FROM user;-- 查询USER表
```
- 删除用户------DROP USER '用户名'@'主机名';
```
 DROP USER '张三'@'localhost';-- 删除用户张三
 DROP USER '李四'@'%';-- 删除用户李四
SELECT * FROM user;-- 查询USER表
```
- 修改用户密码
  - MYSQL8.0版本之前
    - UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
    - 简化版：SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
  - MYSQL8.0版本之后------ALTER USER '用户名'@'主机名' IDENTIFIED BY '新密码';
  - 若忘记root用户密码则步骤如下
    - cmd以管理员身份运行—>net stop mysql 作用：停止MYSQL服务
    - 在刚的cmd中继续输入命令：mysqld --skip-grant-tables 作用：使用无验证方式启动MYSQL服务
    - 打开一个新的cmd窗口—>输入命令mysql即可登录成功–>利用USE mysql;切换到mysql数据库然后用修改用户密码来修改root用户密码即可
    - 更改完密码后关闭所有cmd窗口，然后打开任务管理器，在进程中找到mysqld.exe并结束该进程
    - 再次以管理员身份打开cmd—>输入命令net start mysql,若出现MySQL服务正在启动和MySQL服务已经启动成功就在该cmd窗口中输入命令mysql -uroot -proot即可启动成功

DCL权限管理

DCL权限管理功能
- 查询权限------SHOW GRANTS FOR '用户名'@'主机名';
- 授予权限------GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
  - 授予所有权限------GRANT ALL ON *.* TO '用户名'@'主机名';
- 撤销权限------REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
  - 撤销所有权限------REVOKE ALL ON *.* FROM '用户名'@'主机名';

飘号和去重

飘号：
- 用来区分关键字和字段名

举例说明：假如此时你需要从一个表中选取一个名为“select”的字段列，而该字段列与关键字select重名，所以此时为了让系统知道字段名是字段名则需要为该字段名加上飘号，如下：

SELECT `select`
from 表名

**去重（distinct）： ** 示例说明

例子：查询所有有员工的部门的部门号

如果按照此代码写则最后结果会出现重复的部门号，如下：
```
SELECT department_id
FROM employees
WHERE department_id IS NOT NULL;
```
为了让其不重复显示则加上distinct，如下：
```
SELECT DISTINCT department_id
FROM employees
WHERE department_id IS NOT NULL;
```
= 和 IS的区别
在 SQL 中，使用 = 和 IS 都可以进行条件过滤。但它们的使用方式和意义是不同的。当需要比较两个确定的值时，应该使用 =，而当需要判断一个值是否为 NULL 时，应该使用 IS。

通用函数

IFNULL(字段名，默认值)：若字段的内容为null，则用默认值替换

例题1：#查询所有员工的工资（工资+奖金）

SELECT salary + IFNULL(commission_pct, 0) * salary 工资
FROM employees;

case表达式

语句格式一

case表达式
case 字段名
when 值1 then 返回值1
when 值2 then 返回值2
when 值3 then 返回值3
else 返回值4
end
/*解释
当字段名的值是值1时返回返回值1
当字段名的值是值2时返回返回值2
当字段名的值是值3时返回返回值3
若都不满足则返回返回值4
*/

例题1：查询部门号为10，20，30的员工信息，若部门号为10，则打印其工资的1.1倍；20号部门则打印其工资的1.2倍；30号部门则打印其工资的1.3倍

SELECT department_id, salary, 
	CASE(department_id)
	WHEN 10 THEN salary * 1.1
	WHEN 20 THEN salary * 1.2
	WHEN 30 THEN salary * 1.3
	ELSE salary
	END new_salary
FROM employees
WHERE department_id IN(10, 20, 30);

在这里插入图片描述

语句格式二

CASE 
WHEN 表达式1 THEN 返回值1
WHEN 表达式2 THEN 返回值2
WHEN 表达式3 THEN 返回值3
ELSE 返回值4
END
/*解释：
表达式可以是条件句
*/

例题2：查询所有员工的薪水，如果大于10000则显示富翁，如果小于10000则显示平民，如果等于10000则显示好家伙

SELECT department_id, salary, 
	CASE
	WHEN salary > 10000 THEN "富翁"
	WHEN salary < 10000 THEN "平民"
	ELSE "好家伙"
	END AS 等级
FROM employees
WHERE department_id IN(10, 20, 30);

在这里插入图片描述

单行函数

定义：进去一条函数出来一条函数

大小写控制函数

LOWER('SQL'):将字符串内容全部变为小写
UPPER('SQL')：将字符串内容全部变为大写

例题1：将员工的first_name全部变为小写字母，将last_name全部变为大写字母

SELECT LOWER(first_name), UPPER(last_name)
FROM employees;

在这里插入图片描述

字符控制函数

语句	解释
`CONCAT('HELLO', 'WORLD')`	字符串拼接。结果：HELLOWORLD
`SUBSTR('HELLOWORLD', 1, 5)`	显示出从第1个位置开始长度为5的字符串。结果：HELLO
`LENGTH('HELLOWORLD')`	显示内容的长度。结果：10
`INSTR('HELLOWORLD', 'W')`	显示某个字符在字符串中首次出现的位置，结果：6
`LPAD(salary, 10, '*')`	向右对齐，若内容长度不够10用`*`补
`RPAD(salary, 10, '*')`	向左对齐，若内容长度不够10用`*`补
`TRIM('H' FROM 'HELLOWORLD')`	去除字符串两端指定的字符。结果：ELLOWORLD
`REPLACE('abcd', 'b', 'm')`	将字符串中所有的b替换为m

例题1：将员工的first_nam e和last_name拼接起来

SELECT CONCAT(first_name, '-', last_name)
FROM employees;

在这里插入图片描述

例题2：显示出”asdfghjkl“的”ghj“

SELECT SUBSTR('asdfghjkl', 5, 3);

在这里插入图片描述

例题3：显示所有员工first_name的长度

SELECT first_name,LENGTH(first_name)
FROM employees;

在这里插入图片描述

例题4：将员工薪水分别向左对齐和向右对齐，内容长度为10，若内容长度不够则用*补充

SELECT LPAD(salary, 10, '*'), RPAD(salary, 10, '*')
FROM employees;

在这里插入图片描述

例题5：TRIM

SELECT TRIM('H' FROM 'HHHHHHHHAHHHBHHHHH');

在这里插入图片描述

约束

定义
- 约束是作用于表中列上的规则，用于限制加入表的数据
- 约束的存在保证了数据库中数据的正确性、有效性和完整性

约束种类

约束名称	解释	关键字
非空约束	保证列中的所有数据不能有null值	`NOT NULL`
唯一约束	保证列中的所有数据各不相同或某个字段在整个表中是唯一的	`UNIQUE`
主键约束	主键是一行数据的唯一标识，要求非空且唯一，相当于非空和唯一约束的结合	`PRIMARY KEY`
检查约束	保证列中的值满足某一条件(MySQL不支持检查约束)	`CHECK`
默认约束	保存数据时，未指定值则采用默认值约束	`DEFAULT 默认值`
外键约束	外键用来让两个表的数据之间建立连接，保证数据的一致性和完整性	`FOREIGN KEY`
自增长	自动为每个新插入的记录分配一个唯一的值，该值会自动递增。这意味着你不需要手动为该列指定值，数据库会在插入新记录时自动为该列分配下一个可用的唯一值。（注意：当跟约束一起使用时该关键字跟在约束关键字后面，该关键字不属于约束分类中的一种，只是写到这里了）	`AUTO_INCREMENT`

注意：

主键约束可以看作是非空约束和唯一约束的结合，但是在创建数据库表时，若某个字段要求非空且唯一，并未要求该字段是主键时，就不要用PRIMARY KEY关键字，而是使用NOT NULL UNIQUE来代表非空且唯一
AUTO_INCREMENT:当列是数字类型且由唯一约束修饰时则可以用该关键字。主键约束中包括唯一约束，所以当主键约束修饰的字段取值为数字类型时也可以使用该关键字
当自增长关键字与含非空约束的约束组合在一起时可以为null，此时会自动为该表中字段自增添加数据，详见普通约束+自增长代码
可以在创建表时规定约束（通过CREATE TABLE语句），或者在表创建之后也可以（通过ALTER TABLE 语句）
非空约束和默认值约束只有列级约束中有，而表级约束中没有
一个表中只能有一个主键约束，若有多个代表是表级约束，详见“创建表时添加表级约束”

约束分类
- 列级约束：同时只能约束一列
- 表级约束：同时可以约束多列。
举例对两种约束分类进行说明：

id age
1 10
1 20
如上表所示，若对id进行列级唯一约束，则此时第一行的id和第二行的id不能一样，此处一样则为错误；若对id和age进行表级唯一约束，则此时第一行中的id=1，age=10和第二行中的id=1，age=20并未完全一样，此时则为正确。
添加约束的方式
- 创建表时添加约束
  - 创建表时添加列级约束
  - 创建表时添加表级约束
- 创建表后添加约束

id	age
1	10
1	20

普通约束（创建表时添加列级约束）

添加约束语法

创建表时添加约束(直接在字段名后)------适用于所有约束

CREATE TABLE 表名(
	字段名 数据类型 约束,
    ...
);

创建表时添加约束------唯一约束

CREATE TABLE 表名(
	字段名 数据类型,
    ...
    [CONSTRAINT] [约束名称] UNIQUE(列名)
);

创建表时添加约束------主键约束

CREATE TABLE 表名(
	字段名 数据类型,
    ...
    [CONSTRAINT] [约束名称] PRIMARY KEY(列名)
);

建表完成后添加约束

1.非空约束------ALTER TABLE 表名 MODIFY 字段名数据类型约束;

 2. 唯一约束方式一------`ALTER TABLE 表名 MODIFY 字段名 数据类型 约束;`
 3. 唯一约束方式二------`ALTER TABLE 表名 ADD UNIQUE(字段名);`
 4. 唯一约束方式三------`ALTER TABLE 表名 ADD CONSTRAINT 自己指定的索引名 UNIQUE(字段名);`
 5. 主键约束-----`ALTER TABLE 表名 ADD PRIMARY KEY(字段名);` 
 6. 默认约束------`ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;`

注意：使用第二种方法添加唯一约束时，则在删除约束时的索引名默认为字段名，示例如下

将student中的字段sid添加唯一约束

ALTER TABLE student ADD UNIQUE(sid);
DESC student;

在这里插入图片描述

删除student中的字段sid的唯一约束

ALTER TABLE student DROP INDEX sid;
DESC student;

在这里插入图片描述

删除约束语法
- 非空约束------ALTER TABLE 表名 MODIFY 字段名数据类型;
- 唯一约束------ALTER TABLE 表名 DROP INDEX 字段名;
- 主键约束------ALTER TABLE 表名 DROP PRIMARY KEY;
- 默认约束-----ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;
修改约束
- 修改为主键约束------ALTER TABLE 表名 MODIFY 字段名类型 PRIMARY KEY;—修改约束
- 修改为唯一约束------ALTER TABLE 表名 MODIFY 字段名类型 UNIQUE;

代码示例

普通约束

#-----------------------------------------根据需求为表添加合适约束-----------------------------
DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
	id INT PRIMARY KEY,-- 员工id，主键
	ename VARCHAR ( 50 ) NOT NULL UNIQUE,-- 员工姓名，非空且唯一
	joindate DATE NOT NULL,-- 入职日期，非空
	salary DOUBLE ( 7, 2 ) NOT NULL,-- 工资， 非空
	bonus DOUBLE ( 7, 2 ) DEFAULT 0-- 奖金，若没有奖金则默认为0
);
SELECT * FROM emp;

#添加员工信息
INSERT INTO emp(id, ename, joindate, salary, bonus) VALUE(1, '张三', '1999-11-11', 8800, 5000);

#----------------------演示主键约束：非空且唯一-------------------------------------
INSERT INTO emp ( id, ename, joindate, salary, bonus ) 
VALUE
	( NULL, '张三', '1999-11-11', 8800, 5000 );#报错：Column 'id' cannot be null

INSERT INTO emp ( id, ename, joindate, salary, bonus ) 
VALUE
	( 1, '张三', '1999-11-11', 8800, 5000 );#报错：Duplicate entry '1' for key 'PRIMARY'  主键条目1重复
	
INSERT INTO emp ( id, ename, joindate, salary, bonus ) 
VALUE
	(2, '李四', '1999-11-11', 8800, 5000)

#----------------------演示非空约束-------------------------------------
INSERT INTO emp ( id, ename, joindate, salary, bonus ) 
VALUE
	( 1, NULL, '1999-11-11', 8800, 5000 );#报错：Column 'ename' cannot be null
	
#----------------------演示唯一约束-------------------------------------
INSERT INTO emp ( id, ename, joindate, salary, bonus ) 
VALUE
	( 3, '张三', '1999-11-11', 8800, 5000 );#报错：Duplicate entry '张三' for key 'ename'  ename键条目'张三'重复

#----------------------演示默认约束-------------------------------------
INSERT INTO emp ( id, ename, joindate, salary) 
VALUE
	( 3, '王五', '1999-11-11', 8800);
SELECT * FROM emp;

INSERT INTO emp ( id, ename, joindate, salary, bonus ) 
VALUE
	( 4, '赵六', '1999-11-11', 8800, NULL );
SELECT * FROM emp;

普通约束+自增长

DROP TABLE IF EXISTS empp;
CREATE TABLE empp (
	id INT PRIMARY KEY  AUTO_INCREMENT,-- 员工id，主键且自增长
	ename VARCHAR ( 50 ) NOT NULL UNIQUE,-- 员工姓名，非空且唯一
	joindate DATE NOT NULL,-- 入职日期，非空
	salary DOUBLE ( 7, 2 ) NOT NULL,-- 工资， 非空
	bonus DOUBLE ( 7, 2 ) DEFAULT 0-- 奖金，若没有奖金则默认为0
);
SELECT * FROM empp;

#----------------------演示自增长-------------------------------------
-- AUTO_INCREMENT:当列是数字类型且由唯一约束修饰时则可以用该关键字
INSERT INTO empp (ename, joindate, salary, bonus ) 
VALUE
	('张三', '1999-11-11', 8800, NULL );


INSERT INTO empp (id, ename, joindate, salary, bonus ) 
VALUE
	( NULL, '李四', '1999-11-11', 8800, NULL );


INSERT INTO empp (id, ename, joindate, salary) 
VALUE
	( NULL, '王五', '1999-11-11', 8800);
SELECT * FROM empp;

在这里插入图片描述

自增（自增长）

定义

利用MySQL提供的自动增长功能来自动生成主键的值，防止插入的值重复导致插入失败。自动增长功能通过auto_increment来实现，基本语法格式如下：字段名数据类型 auto_increment；

注意事项

1、一个表中只能有一个自动增长字段，该字段的数据类型是整数类型，且必须定义为键，如unique key、primary key。

2、如果为自动增长字段插入NULL、0、DEFAULT或在插入时省略该字段，则该字段就会使用自动增长值；如果插入的是一个具体的值，则不会使用自动增长值。
3、自动增长值从1开始自增，每次加1。如果插入的值大于自动增长的值，则下次插入的自动增长值会自动使用最大值加1；如果插入的值小于自动增长值，则不会对自动增长值产生影响。
4、使用DELETE删除记录时，自动增长值不会减小或填补空缺。

5、自增一般用在主键约束中

6、自增时不管代码是否执行成功，被赋予自增的那个字段的数据都会自增一次

举例说明

创建一个空表emp4，并将id设置为主键约束

CREATE TABLE emp4(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(20)
);

向表中加入数据并执行该代码多次（此处假定为5次），结果如图所示

INSERT INTO emp4(NAME) VALUES('aa');

在这里插入图片描述

问题： 对字段名id设置为主键约束后，其值应该是非空且唯一的，但此处代码并未给它赋值，为什么不会报错？

答：因为字段id已经被设置为自增。如果为自动增长字段插入NULL、0、DEFAULT或在插入时省略该字段，则该字段就会使用自动增长值，所以不能认为在插入数据时id的数据是空的，所以不会报错，正常执行。

创建表时添加表级约束

表级约束中可进行主键约束和唯一约束
- 主键约束在表级约束中的使用方法------CONSTRAINT 索引名 PRIMARY KEY(字段名1,字段名2,...)
- 唯一约束在表级约束中的使用方法------CONSTRAINT 索引名 UNIQUE(字段名1,字段名2,...)

表级约束中的主键约束

在db3中添加新表emp2

#创建表emp2
CREATE TABLE emp2(
id INT,
sid INT,
NAME VARCHAR(20),
CONSTRAINT emp2_id_sid PRIMARY KEY(id, sid)
);
DESC emp2;#创建完成后查看表的结构

在这里插入图片描述

在空表emp2中插入数据

INSERT INTO emp2(id,sid) VALUES(1,10);
INSERT INTO emp2(id,sid) VALUES(1,20);

在这里插入图片描述

注意：此时id虽一样但是在表级约束中id和sid组合起来不一样，所以满足主键约束的条件

表级约束中的唯一约束

在db3中添加新表emp3

CREATE TABLE emp3(
id INT,
sid INT,
NAME VARCHAR(20),
CONSTRAINT emp3_id_sid UNIQUE(id, sid)
);
DESC emp3;#创建完成后查看表的结构

在这里插入图片描述

在空表emp3中插入数据

INSERT INTO emp3(id,sid) VALUES(1,10);
INSERT INTO emp3(id,sid) VALUES(1,20);

在这里插入图片描述

外键约束

定义

外键是来让两个表的数据之间建立链接，保证数据的一致性和完整性

MySQL的外键约束用来在两个表数据之间建立链接，其中一张表的一个字段被另一张表中对应的字段约束。也就是说，设置外键约束至少要有两种表，被约束的表叫做从表（子表），另一张叫做主表（父表），属于主从关系。其中主表中的关联字段叫做主键，外表中的关联字段叫做外键。

主键中已经存在的数据才能在从表中的外键中添加，否则会出错
添加外键约束语法
- 建表时添加外键约束
```
CREATE TABLE 从表名(
	列名 数据类型,
    ...
    外键字段名 数据类型
    [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表字段名)
);
```
- 建完表后添加外键约束
```
CREATE TABLE 从表名(
	列名 数据类型,
    ...
    外键字段名 数据类型
);
ALTER TABLE 从表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表字段名)
```
- 查看外键约束是否成功：
- 注意
  - 主表字段名就是被建立约束的表的字段名
  - 在从表中加入外键（即外键字段名需要提前在创建从表的语法中定义出来）
  - 自定义的外键名称一般以fk开头，格式为：fk_从表名_主表名
  - 设置外键约束至少要有两种表，被约束的表（即添加外键约束的表）叫做从表（子表），另一张叫做主表（父表），属于主从关系。其中主表中的关联字段叫做主键，外表中的关联字段叫做外键。
  - 创建表时要先创建主表在创建从表
  - 添加数据时要先添加主表数据再添加从表数据
  - 删除数据时要先删除从表数据再删除主表数据，反之则会报错，如图所示

删除外键约束语法

ALTER TABLE 从表名 DROP FOREIGN KEY 外键名称;

代码示例

-- 先创建主表：部门表
DROP TABLE IF EXISTS dept;
CREATE TABLE dept (
	id int PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(20),
	addr VARCHAR(20)
);


-- 再创建从表：员工表
-- -------------------------------创建从表时添加外键约束---------------------------------
/*
DROP TABLE IF EXISTS emppp;
CREATE TABLE emppp(
	id int PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20),
	age INT,
	dep_id INT,   -- 从表中定义外键字段名
	-- 添加外键约束:添加外键dep_id，关联dept表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
);
*/
-- ---------------------------创建从表后添加外键约束---------------------------------
DROP TABLE IF EXISTS emppp;
CREATE TABLE emppp(
	id int PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20),
	age INT,
	dep_id INT -- 从表中定义外键字段名
);
ALTER TABLE emppp ADD CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id);

-- 先添加主表（部门表）数据：添加2个部门
INSERT INTO dept(dep_name, addr) VALUES ('研发部', '广州'), ('销售部', '深圳');
SELECT * FROM dept;
-- 再添加从表（员工表）数据：添加员工信息
INSERT INTO emppp(name, age, dep_id) VALUES
('张三', 20, 1),
('李四', 20, 1), 
('王五', 20, 1),
('赵六', 20, 2),
('孙七', 22, 2),
('周八', 18, 2);
SELECT * FROM emppp;

级联删除(ON DELETE CASCADE)–慎用

解释：

不用在删除数据时先删除从表中的数据再删除主表中的数据，可以直接删除主表和从表中的相关数据，

当主表中的列被删除时，从表中相应的列也被删除

语句格式：

CONSTRAINT 索引名 FOREIGN KEY(本表的字段名) REFERENCES 建立约束的表的表名(建立约束的表的字段名) ON DELETE CASCADE

例子说明

创建主表和从表

#部门---主表
CREATE TABLE dept1(
dept_id INT AUTO_INCREMENT PRIMARY KEY,#部门id
dept_name VARCHAR(20)
);
#员工---从表
CREATE TABLE emp1(
emp_id INT AUTO_INCREMENT PRIMARY KEY,#员工id
last_name VARCHAR(15),
dept_id INT,
#添加外键约束及级联删除
CONSTRAINT emp1_dept_id_fk FOREIGN KEY(dept_id)
REFERENCES dept(dept_id) ON DELETE CASCADE
);

向主表和从表中添加数据，添加完后的主表如图一所示，从表如图二所示
删除10号部门的所有员工,此时会将主表和从表中10号部门的数据删除，主表如图一所示，从表如图二所示
```
DELETE FROM dept1 WHERE dept_id = 10;
```

数据库设计

定义
- 根据业务系统的具体需求，结合我们所选用的DBMS（即数据库管理系统），为这个业务系统构造出最优的数据存储模型。（即建立数据库中的表结构以及表与表之间的关联关系的过程）
数据库设计步骤
- 需求分析（数据是什么？数据有哪些属性？数据与属性的特点是什么）
- 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）
- 物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）
- 维护设计（1.对新的需求进行建表 2.对表进行优化）
表与表之间的关系分类
- 一对一
  - 一个人只有一个身份证
  - 一对一关系多用于表的拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能
- 一对多（或多对一）------一是1多是M
  - 如：一个部门对应多个员工，多个员工只能对应一个部门
- 多对多
  - 一个订单包含多个商品，一个商品对应多个订单

多表（表与表之间的）关系实现

一对一

在这里插入图片描述

定义
- 一对一关系多用于表的拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能
实现方式
- 如上图所示，将用户表一分为二，其中tb_user表放经常的字段信息，tb_user_desc放不经常使用的字段信息
- 在任一方添加外键(即定义外键字段名,在上图中是在用户表中添加了外键desc_id)，关联另一方主键，并且设置外键为唯一约束（UNIQUE）
代码实现

create table tb_user_desc (
	id int primary key auto_increment,
	city varchar(20),
	edu varchar(10),
	income int,
	status char(2),
	des varchar(100)
);

create table tb_user (
	id int primary key auto_increment,
	photo varchar(100),
	nickname varchar(50),
	age int,
	gender char(1),
	desc_id int unique,
	-- 添加外键
	CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)
);

在这里插入图片描述

一对多

在这里插入图片描述

实现方式
- 在多的一方建立外键（即定义外键字段名），指向一的一方的主键（即多的一方属于从表，一的一方属于主表）
代码实现

# 创建主表：部门表
DROP TABLE IF EXISTS tb_dept;
CREATE TABLE tb_dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20),
	addr VARCHAR(20)
);
SELECT * FROM tb_dept;

#创建从表：员工表
DROP TABLE IF EXISTS tb_emp;
CREATE TABLE tb_emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20),
	age INT,
	dep_id INT,
	CONSTRAINT fk_tbemp_tbdept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)
);
SELECT * FROM tb_emp;

在这里插入图片描述

由以上图形化界面可知创建成功

多对多

在这里插入图片描述

实现方式
- 建立第三章中间表，中间表至少含两个外键，分别关联两方主键。
注意
- 多对多关系时中间表为从表，除中间表之外的两个表为主表
代码实现

-- ---------------------------------多对多---------------------------------------------
DROP TABLE IF EXISTS tb_order;
DROP TABLE IF EXISTS tb_goods;
DROP TABLE IF EXISTS tb_order_goods;

# 创建主表：订单表
CREATE TABLE tb_order(
	id INT PRIMARY KEY AUTO_INCREMENT,
	payment DOUBLE(10,2),
	payment_type TINYINT,
	status TINYINT
);
#创建主表：商品表
CREATE TABLE tb_goods(
	id INT PRIMARY KEY AUTO_INCREMENT,
	title VARCHAR(100),
	price DOUBLE(10,2)
);
#创建从表：订单商品中间表
CREATE TABLE tb_order_goods(
	id INT PRIMARY KEY AUTO_INCREMENT,
	order_id INT,
	goods_id INT,
	count INT
);
-- 建完表后添加外键
ALTER TABLE tb_order_goods ADD CONSTRAINT fk_order_id FOREIGN KEY(order_id) REFERENCES tb_order(id);
ALTER TABLE tb_order_goods ADD CONSTRAINT fk_goods_id FOREIGN KEY(goods_id) REFERENCES
tb_goods(id);

在这里插入图片描述

多表查询

代码示例用到的表

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;

#创建部门表
CREATE TABLE dept(
	did INT PRIMARY KEY AUTO_INCREMENT,
	dname VARCHAR(20)
);
#创建员工表
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(10),
	gender CHAR(1),-- 性别
	salary DOUBLE,-- 工资
	join_date DATE,-- 入职日期
	dep_id INT,
	FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表（部门表的主键）
)
-- 添加部门数据
INSERT INTO dept(dname) VALUES('研发部'),('市场部'),('财务部'),('销售部');
-- 添加员工数据
INSERT INTO emp (name, gender, salary, join_date, dep_id) VALUES
('孙悟空', '男', 7200, '2013-02-24', 1),
('猪八戒', '男', 600, '2010-12-02', 2),
('唐僧', '男', 9000, '2008-08-08', 2),
('白骨精', '女', 5000, '2015-10-07', 3),
('蜘蛛精', '女', 4500, '2011-03-14', 1),
('小白龙', '男', 2500, '2011-02-14', NULL);

在这里插入图片描述

定义
- 从多张表中获取数据
作用
- 查询的多个字段不在同一个表中时用到多表查询将不同的表连接到一起
注意
- 在进行多表查询时不能使用SELECT * FROM 表A, 表B，...;来查询，因为会产生笛卡尔积（即若有A和B两个集合则会取A和B的所有组合情况，比如：表A中有3条数据，表B中有4条数据则此时利用该语句查询时则会出现12条数据，这12条数据中包括无效数据，所以我们就要想办法消除无效数据）
多表查询分类
- 连接查询
  - 内连接：相当于查询A和B的交集数据
  - 外连接
    - 左外连接：相当于查询A表所有数据和交集部分数据
    - 右外连接：相当于查询B表所有数据和交集部分数据
- 子查询
语法注意事项
- 在多表查询中若查询的字段是唯一的则可以在字段名之前不加表名；若查询的字段不是唯一的，则该字段名之前需要加表名。
- 建议不管字段是否是唯一的，都加上表名。目的是为了避免数据库对多个表的字段进行多次寻找，加上之后数据库对字段只需要在对应表中寻找一次即可。而且这样效率更高！

连接查询

JOIN操作

在这里插入图片描述

JOIN操作包括：
	1.(INNER) JOIN:内连接
	2.LEFT (OUTER) JOIN：左外连接
	3.RIGHT (OUTER) JOIN:右外连接
	4.FULL (OUTER) JOIN:满外连接

内连接------(INNER) JOIN

解释
- 相当于查询A和B的交集数据
内连接查询语法
- 隐式内连接------SELECT 字段1, 字段2,.. FROM 表1 [[AS]别名1], 表2 [[AS]别名2]... WHERE 条件;
- 显示内连接------SELECT 字段1, 字段2,.. FROM 表1 [[AS]别名1] [INNER] JOIN 表2 [[AS]别名2] ON 条件;
注意：显示内连接中的ON关键字与隐式内连接中的WHERE作用相同，都是用于指定连接条件

示例1

-- -----------------------------多表查询-------------------------------
-- 查询emp和dept数据
SELECT * FROM emp, dept; -- 禁用：因为会产生笛卡尔积
#方法：员工表中加入外键，并让该外键与部门表中的did相等,此时查询出的人员会与对应的部门正确对应
SELECT * FROM emp, dept WHERE emp.dep_id = dept.did;#隐式内连接
-- ----------------------隐式内连接-----------------------
#查询emp的name，gender，dept表的dname
SELECT emp.name, emp.gender, dept.dname FROM emp, dept WHERE emp.dep_id = dept.did;
#查询emp的name，gender，dept表的dname，并给表起别名,注意：AS可省略
SELECT t1.name, t1.gender, t2.dname FROM emp AS t1, dept AS t2 WHERE t1.dep_id = t2.did;
-- ----------------------显式内连接-----------------------
#查询emp的name，gender，dept表的dname
SELECT emp.name, emp.gender, dept.dname FROM emp INNER JOIN dept ON emp.dep_id = dept.did;
#查询emp的name，gender，dept表的dname，并给表起别名,注意：AS可省略
SELECT t1.name, t1.gender, t2.dname FROM emp AS t1 INNER JOIN dept AS t2 ON t1.dep_id = t2.did;

示例2

有两张表card和newstu，如图一所示，内连接后如图二所示
在这里插入图片描述

外连接

左外连接------LEFT (OUTER) JOIN------解释

相当于查询A表所有数据和交集部分数据
左外连接查询语法
- SELECT 字段1, 字段2,... FROM 表A [[AS]别名1] LEFT [OUTER] JOIN 表B [[AS]别名2] ON 条件;
- 等同于SELECT 字段1, 字段2,... FROM 表B [[AS]别名2] RIGHT [OUTER] JOIN 表A [[AS]别名1] ON 条件;
右外连接解释

相当于查询B表所有数据和交集部分数据
右外连接查询语法
- SELECT 字段1, 字段2,... FROM 表A [[AS]别名1] RIGHT [OUTER] JOIN 表B [[AS]别名2] ON 条件;
- 等同于SELECT 字段1, 字段2,... FROM 表B [[AS]别名2] LEFT [OUTER] JOIN 表A [[AS]别名1] ON 条件;
注意
- 左外连接和右外连接可以相互替换（将表A和表B位置换一下即可）
- 实际情况中常用左外连接来实现右外连接的语句

左外连接示例1

-- ------------------------左外连接--------------------------------
#查询emp表所有数据和对应部门信息
SELECT * FROM emp LEFT JOIN dept ON emp.dep_id = dept.did;
#查询emp表所有数据和对应部门信息并给表起别名,注意：AS可省略
SELECT * FROM emp AS t1 LEFT JOIN dept AS t2 ON t1.dep_id = t2.did;

在这里插入图片描述

左外连接示例2

有两张表card和newstu，如图一所示

方式一：card为左表，newstu为右表，左外连接后如图所示

方式二：newstu为左表，card为右表，左外连接后如图所示

例题1：查询所有员工姓名及员工对应的部门的名称

SELECT e.first_name, d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;


![在这里插入图片描述](https://img-blog.csdnimg.cn/ee5eea844caf42a1982bcbf0d01f3411.png)

> 例题2：查询员工的姓名及对应的部门名称以及部门所在城市的名称

```sql
SELECT e.`first_name`, d.`department_name`, l.`city`
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
LEFT JOIN locations l
ON d.`location_id` = l.`location_id`;

在这里插入图片描述

右外连接示例1

-- ------------------------右外连接--------------------------------
#查询dept表所有数据和对应的员工信息
SELECT * FROM emp RIGHT JOIN dept ON emp.dep_id = dept.did;
#查询dept表所有数据和对应的员工信息并给表起别名,注意：AS可省略
SELECT * FROM emp AS t1 RIGHT JOIN dept AS t2 ON t1.dep_id = t2.did;

在这里插入图片描述

右外连接示例2

有两张表card和newstu，如图一所示

方式一：newstu为左表，card为右表，右外连接后如图所示

例题：查询所有的部门名称及部门中员工的姓名
```
SELECT e.`first_name`, d.`department_name`
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
```

满外连接

解释
- 除了匹配的内容还有左表和右表中不匹配的内容
注意： MySQL不支持满外连接代码，所以在执行操作时换种思路来实现满外连接

语句格式

#UNOIN ALL代表不去除重复的行
左外连接
UNOIN ALL
右外连接

#UNOIN代表去除重复的行
左外连接
UNOIN
右外连接

例子：查询所有员工和所有部门名称

SELECT e.`first_name`, d.`department_name`
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION
SELECT e.`first_name`, d.`department_name`
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

在这里插入图片描述

子查询

定义
- 查询中嵌套查询，称嵌套查询为子查询。子查询通常被用作一个表达式，用于从一个查询的结果集中提取另一个子集。
- 子查询可以嵌套在 SELECT、FROM、WHERE 或 HAVING 子句中，用于执行更复杂的查询操作

子查询分类

单行子查询------查询结果为单行单列

多行子查询------查询结果为多行单列、多行多列

使用的部分运算符

运算符		举例	举例
in	in(子代码结果)	代表可取子查询的多个结果	salary in (1000,2000,3000)
any	any(子代码结果)	代表any中的任何一个。注意：any前要有> < >= <= <>等符号的任一个，当为=时，其与in意思一样	salary>any(1000,2000,3000)
all	all(子代码结果)	代表all中的所有。注意：all前要有> < >= <= <> =等符号的任一个	salary > all(1000,2000,3000)

子查询的查询结果分类（注意：子查询根据查询结果不同，作用不同）

单行单列

此时子查询可以作为条件，并使用运算符去判断来进行多表查询
示例1

-- 查询工资高于猪八戒的员工信息
#Step1:查询猪八戒的工资
SELECT salary FROM emp WHERE name = '猪八戒';-- 该嵌套查询结果为单行单列
#Step2：查询工资高于猪八戒的员工信息
SELECT * FROM emp WHERE salary > 600;
#总步骤：将其合到一块
SELECT * FROM emp WHERE salary > (SELECT salary FROM emp WHERE name = '猪八戒');

在这里插入图片描述

示例2：查询谁的工资比last_name = 'Abel’的高

-- 方式一：自连接（内连接）
SELECT e1.`last_name`, e1.`salary`
FROM employees e1 JOIN employees e2
ON e1.`salary` > e2.`salary` AND e2.`last_name` = 'Abel';
-- 方式二：子查询
SELECT last_name, salary
FROM employees
WHERE salary >	(SELECT salary
				FROM employees
				WHERE last_name = 'Abel');

在这里插入图片描述

示例3：返回job_id与141号员工相同，salary比143号员工多的员工姓名、job_id和工资

SELECT first_name, job_id, salary
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
) AND salary > (
	SELECT salary
	FROM employees
	WHERE employee_id = 143
);

在这里插入图片描述

示例4：查询最低工资大于50号部门最低工资的部门id和其他最低工资

SELECT department_id, MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
HAVING MIN(salary) > (
	SELECT	MIN(salary)
	FROM employees
	WHERE department_id = 50
);

在这里插入图片描述

多行单列

此时子查询可以作为条件，使用运算符IN来判断来进行多表查询
示例1：查询’财务部’和’市场部’所有的员工信息

-- 查询'财务部'和'市场部'所有的员工信息
#Step1：查询'财务部'对应的部门号
SELECT did FROM dept WHERE dname IN ('财务部', '市场部');#查询结果为2和3
#Step2：查询部门号为3的员工信息
SELECT * FROM emp WHERE dep_id IN (2, 3);
#总步骤：将其合到一块
SELECT * FROM emp WHERE dep_id IN (SELECT did FROM dept WHERE dname IN ('财务部', '市场部'));

在这里插入图片描述

示例2：返回其它部门中比job_id = 'IT PROG’部门任意一个工资低的员工的员工号、姓名、job_id、salary

SELECT employee_id, first_name, job_id, salary
FROM employees
WHERE salary < ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';

#DISTINCT作用是去重，去除薪水一样的行

在这里插入图片描述

示例3：返回其它部门中比job_id = 'IT PROG’部门的所有工资低的员工的员工号、姓名、job_id、salary

SELECT employee_id, first_name, job_id, salary
FROM employees
WHERE salary < ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';

在这里插入图片描述

注意：嵌套的子查询运行结果如下

SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'

在这里插入图片描述

多行多列
- 此时子查询可以作为一张虚拟表来参与查询

-- ------------------------------子查询结果为多行多列-------------------------------------
-- 查询员工入职日期是2011-11-11之后的员工信息和部门信息
#Step1：查询员工入职日期是2011-11-11之后的员工信息---所得的表作为一个虚拟表使用
SELECT  * FROM emp WHERE join_date > '2011-11-11';
#Step2：将虚拟表与部门表内连接得到员工入职日期是2011-11-11之后的员工信息和部门信息
SELECT * FROM (SELECT  * FROM emp WHERE join_date > '2011-11-11') t1, dept t2 WHERE t1.dep_id = t2.did;

-- 也可使用隐式内连接来查询员工入职日期是2011-11-11之后的员工信息和部门信息
SELECT * FROM emp t1, dept t2 WHERE t1.dep_id = t2.did AND t1.join_date > '2011-11-11';

-- 也可将利用隐式内连接得到的表作为虚拟表进行查询
SELECT * FROM (SELECT * FROM emp t1, dept t2 WHERE t1.dep_id = t2.did) t1 WHERE t1.join_date > '2011-11-11';

在这里插入图片描述

连接方式的不同分类

方式一：自连接 vs 非自连接
方式二：等值连接 vs 非等值连接
方式三：内连接 vs外连接

自连接 vs 非自连接

自连接：连接的两张表是同一张表（即将一张表看成两张表）

指在一个表中连接该表本身，从而产生一种类似于嵌套查询的效果。自连接通常用于处理层次结构或树状结构的数据，例如员工的管理层次、组织结构等。

举例说明

假设我们有一个名为 employees 的表，它包含了公司员工的信息，其中包括每个员工的ID、姓名和经理ID：

在这个例子中，每个员工都有一个经理，除了公司的最高管理层之外。如果我们想要查找每个员工及其经理的姓名，我们可以使用自连接来完成。如下

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e JOIN employees m 
ON e.manager_id = m.id;

在上述查询中，我们使用了两个表别名 e 和 m，分别表示员工和经理。通过 JOIN 子句将表连接起来，我们将 e.manager_id 列与 m.id 列进行比较，以查找每个员工的经理。执行上述查询，将会返回以下结果：
在这里插入图片描述

注意：

SELECT *
FROM employees e JOIN employees m 
ON e.manager_id = m.id;
#该代码块的结果如下：

在这里插入图片描述

例6-2-1-1：查询员工姓名以及该员工所对应的管理者

  #sql99
SELECT e.first_name, m.first_name
FROM employees e JOIN employees m
ON e.employee_id = m.manager_id;

在这里插入图片描述

==非自连接：==连接的两张表不是同一张表

例6-2-1-2：查询员工的姓名和部门名称

/*sql192*/
SELECT first_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id

/*sql99*/
SELECT e.first_name,d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id

在这里插入图片描述

例6-2-1-3：查询员工的姓名、部门号和部门名称

注意：因为字段department_id在employees和departments两个表中都存在，所以在你查询部门号时，需要让数据库知道你的部门号用的是哪个表中的部门号，所以第一行代码中写department_id时需要加上表名，为employees.department_id

/*sql192*/
#方式一
SELECT first_name,employees.department_id,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id

#方式二：都加上表名（推荐）
SELECT employees.first_name,employees.department_id,departments.department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id

#方式三：给表名定义别名以增加都加上表名时的代码输入效率
SELECT e.first_name,e.department_id,d.department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id

/*sql99：*/
SELECT e.first_name,e.department_id,d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id

等值连接 vs 非等值连接

等值连接

定义：连接条件用的是等号

则在6.1.1中所举的例子均为等值连接

非等值连接
定义：

多表查询语句中的连接条件不是使用等号的连接

薪水等级表如下
在这里插入图片描述

例6-2-2-1：查询员工的薪水以及薪水的等级,并按薪水升序排列

SELECT e.first_name, e.`salary`,g.GRADE
FROM employees e JOIN job_grades g
ON e.`salary` BETWEEN g.`LOWEST_SAL` AND g.`HIGHEST_SAL`
ORDER BY e.salary;

在这里插入图片描述

事务（数据库的事务）

定义
- 数据库的事务是一种机制、一个操作序列，包含了一组数据库操作命令
- 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败（即若一个包含多个步骤的业务操作被事务管理则这些操作要么同时成功，要么同时失败。）
- 事务是一个不可分割的工作逻辑单元
解释
- 假设李四要跟张三借500，此时第一步要先查询张三的账户余额看是否大于500，第二步：假设张三账户余额大于500则转出500，第三步李四账户余额多500，如图。将这三步当作一个整体来看，那么转账操作要么同时成功，要么同时失败。
- 那么如何将其当作一个整体或事务：在进行转账操作之前要先开启事务，开启事务成功之后在所有操作成功完成之后要提交事务，若中间出现异常则要回滚事务
数据库事务组成
- 一个或多个DML语句（数据操作语言）
- 一个DDL语句（数据定义语言）
- 一个DCL语句（数据控制语言）
什么时候用事务
- 当进行多个操作且需要将多个操作绑在一块，要么都成功，要么都失败。此时才可以用事务。

事务操作

代码示例用到的表

DROP TABLE IF EXISTS account;
#创建账户表
CREATE TABLE account (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(10),
	money DOUBLE(10,2)
);
#添加数据
INSERT INTO account(name, money) VALUES ('张三', 1000), ('李四', 1000);
SELECT * FROM account;

在这里插入图片描述

事务操作有三个
- 开启事务：START TRANSACTION或BEGIN
  - 开始一个新的数据库操作序列，该序列将作为一个原子单元执行。在开启事务后，数据库会记录所有的数据操作，但这些操作不会立即影响数据库的持久状态。
- 回滚事务：ROLLBACK
  - 回滚事务是指在事务执行过程中出现了错误或者需要撤销之前的操作时，将事务中的所有操作撤销，使数据库回到事务开始前的状态。回滚事务可以通过显式的回滚命令来执行，也可以在发生错误时自动触发。
- 提交事务：COMMIT
  - 提交事务是指在事务执行成功后，将事务中的所有操作永久性地应用到数据库中，使之成为数据库的一部分。一旦提交事务，数据库将不再允许对其进行回滚，并且所有操作将会对其他事务可见。
- 解释
  - 开启事务就相当于打一个标记，告诉数据库这后面的语句执行都是一些临时的更改，并不会真的对数据库中的数据进行更改（此时只有在开启事务的这个查询页面能看到数据的更改，若在新创建个查询页面就不能看到数据的更改），只有在提交事务后才会真正持久化进行更改，若在整体中某个地方存在异常或出现错误就会撤销之前的临时更改，回滚到开启事务之前的数据库状态
注意：

以上三个操作共同构成了事务的基本生命周期。开启事务标志着一个新的事务开始，回滚事务允许事务的部分或全部操作被撤销，而提交事务则将事务的操作永久地应用到数据库中。通过合理地使用这些操作，可以确保数据库操作的原子性、一致性和持久性。

在数据库中，原子单元指的是事务中的最小操作单元，也就是事务中不可分割的操作。原子单元是指在事务内部，要么所有的操作都成功执行，要么所有的操作都不执行，不会出现部分操作成功而部分操作失败的情况。这种性质被称为原子性。

事务执行过程中中只有出现了错误或者需要撤销之前的操作时，才需要将回滚事务的语句写出并单独执行

代码示例

出错示例

-- 开启事务
BEGIN;
#Step1：查询张三余额
SELECT money FROM account WHERE name = '张三';

#Step2：张三金额-500
UPDATE account SET money = money - 500 WHERE name = '张三';
此为出错
#Step3：李四金额+500
UPDATE account SET money = money + 500 WHERE name = '李四';

在这里插入图片描述

由上述运行截图可知，张三少了500而李四并没有多500，开启事务后运行出错，此时即可执行回滚操作，让数据库回到开启事务之前的状态，执行如下代码

-- 回滚事务
ROLLBACK;
SELECT * FROM account;

在这里插入图片描述

由以上运行截图可知，在执行完回滚事务之后，数据库回到开启事务之前的状态

正确示例

-- 开启事务
BEGIN;

#Step1：查询张三余额
SELECT money FROM account WHERE name = '张三';
#Step2：张三金额-500
UPDATE account SET money = money - 500 WHERE name = '张三';
#Step3：李四金额+500
UPDATE account SET money = money + 500 WHERE name = '李四';

-- 提交事务
COMMIT;

在这里插入图片描述

由以上运行截图可知，由于事务开启后未出错，所以在提交事务之后数据库会被永久更改

事务提交的两种方式
- 自动提交：MYSQL就是自动提交的
- 手动提交：需要先开启事务再提交
注意：

Oracle数据库默认是手动提交事务的
MYSQL中事务默认自动提交—一条DML（增删改）语句会自动提交一次事务
- 解释
我创建account账户表初始状态下张三和李四都是1000，若在初始状态（即未显示开执行BEGIN语句来开启事务）下我执行一条语句UPDATE account SET money = money - 500 WHERE name = '张三';则张三的账户金额会被真正永久更改（即：数据库中的数据被永久更改），原因是，MYSQL默认情况是事务自动提交的（即在你执行这条语句之后会自动隐式的执行COMMIT），如下代码所示：
```
UPDATE account SET money = money - 500 WHERE name = '张三';
-- COMMIT; -- COMMIT是隐式执行的，这里写出来只是为了理解
```
若我们将MYSQL中事务提交方式更改为手动提交后，此时执行语句UPDATE account SET money = money - 500 WHERE name = '张三';则数据库不会被真正更改，除非你手动在该语句后写上COMMIT才会被永久真正的更改，如下语句所示
```
SET @@autocommit = 0;-- 设置事务提交方式为手动提交
UPDATE account SET money = money - 500 WHERE name = '张三';-- 更改数据库中数据
COMMIT;-- 手动提交事务
```
问题：事务手动提交时不是应该先开启事务再提交吗？这里并没有开启事务

有些数据库管理系统（DBMS）（eg：Navicat、SQLyog）会自动地为每个 SQL 语句开启一个隐式的事务，所以此处不需要先开启事务，写完语句后直接手动提交事务即可
查询事务的默认提交方式（自动提交为1，手动提交为0）------SELECT @@autocommit;
修改事务的提交方式
- 手动提交------SET @@autocommit = 0;或SET autocommit = FALSE;
- 自动提交------SET @@autocommit = 1;或SET autocommit = TRUE;
1、一旦事务结束一定要允许自动提交，否则后续的操作会仍处在事务中;

2、一旦进行事务提交则就不可以进行回滚了
事务的四大特征（ACID）
- 原子性（Atomicity）：事务是不可分割的最小单位，要么同时成功，要么同时失败
- 一致性（Consistency）：事务操作完成前后，数据总量不变
- 隔离性（Isolation）：多个事务之间，相互独立
- 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

事务的隔离级别

定义
- 多个事务之间是隔离的、相互独立的，。但是若多个事务操作同一批数据则会引发一些问题，为解决这些问题，可以设置不同的隔离级别
多个事务操作同一批数据会引发的问题
- 脏读：一个事务读取到另一个事务中没有提交的数据
- 不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样
- 幻读：一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改
隔离级别分类、语句及对应会发生的问题
- 读未提交------READ UNCOMMITTED
  - 产生的问题：脏读、不可重复读、幻读
- 读已提交------READ COMMITTED（Oracle默认）
  - 产生的问题：不可重复读、幻读
- 可重复读------REPEATABLE READ（MYSQL默认）
  - 产生的问题：幻读
- 串行化------SERIALIZABLE------可以解决所有问题
注意：

以上隔离级别是从小到大，安全性越来越高，但是效率越来越低
数据库查询隔离级别------SELECT @@transaction_isolation;
数据库设置隔离级别------SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别;
- 设置好隔离级别后需要将与MYSQL的当前连接断开并重新连接才会生效