MySql
1.数据库基础
1.1常见的数据模型
（1）层次模型：用树状结构表示实体类型及实体间联系的数据模型称为层次模型
（2）网状模型：用有向图结构表示实体类型及实体间联系的数据模型称为网状模型
（3）关系模型：以二维表来描述数据。关系模型中，每个表有多个字段列和记录行，每个字段列有固定的属性（数字、字符、日期等）
1.2 实体与关系
实体是指客观存在并可相互区别的事物，实体既可以是实际的事物，也可以是抽象的概念或关系。
实体之间有3种关系，分别如下。
（1）一对一关系：是指表A中的一条记录在表B中有且只有一条相匹配的记录。在一对一关系中，大部分相关信息都在一个表中。
（2）一对多关系：是指表A中的行可以在表B中有许多匹配行，但是表B中的行只能在表A中有一个匹配行。
（3）多对多关系：是指关系中每个表的行在相关表中具有多个匹配行。在数据库中，多对多关系的建立是依靠第三个表（称作连接表）实现的，连接表包含相关的两个表的主键列，然后从两个相关表的主键列分别创建与连接表中的匹配列的关系。
1、一对多：比如说一个班级有很多学生，可是这个班级只有一个班主任。在这个班级中随便找一个人，就会知道他们的班主任是谁；知道了这个班主任就会知道有哪几个学生。这里班主任和学生的关系就是一对多。
3、一对一：比如说一个班级有很多学生，他们分别有不同的学号。一个学生对应一个学号，一个学号对应一个学生；通过学号能找到学生，通过学生也能得到学号，不会重复。这里学生和学号的关系就是一对一。
4、多对多：比如说一个班级有很多学生，他们有语文课、数学课、英语课等很多课。一门课有很多人上，一个人上很多门课。这里学生和课程的关系就是多对多
2.数据库操作
2.1链接数据库
[root@host]# mysql -u root -p
Enter password:****** # 登录后进入终端
2.2创建数据库
CREATE DATABASE 数据库名;
create database db_admin;
create schema db_admin1;
2.3创建指定字符集的数据库
CREATE DATABASE db_test
CHARACTER SET = GBK;
2.4 查看数据库
SHOW DATABASES;
//筛选以db_开头的数据库名称
SHOW DATABASES LIKE 'db_%';
2.5选择数据库
USE 数据库名
USE db_admin;
2.6修改数据库
//修改数据库db_admin，设置默认字符集和校对规则
ALTER DATABASE db_admin
DEFAULT CHARACTER SET gbk
DEFAULT COLLATE gbk_chinese_ci;
2.7删除数据库
DROP {DATABASE|SCHEMA} [IF EXISTS] 数据库名;
DROP DATABASE db_admin;
注意
删除数据库的操作应该谨慎使用，一旦执行该操作，数据库的所有结构和数据都会被删除，没有恢复的可能，除非数据库有备份。
2.8mysql数据类型
- 整数类型：
- TINYINT：小整数，范围从-128到127（有符号）或0到255（无符号）。
- SMALLINT：小整数，范围从-32768到32767（有符号）或0到65535（无符号）。
- INT：整数，通常使用最广泛，范围从-2147483648到2147483647（有符号）或0到4294967295（无符号）。
- BIGINT：大整数，范围非常大，适合存储非常大的整数值。
- 浮点数类型：
- FLOAT：单精度浮点数。
- DOUBLE：双精度浮点数。
- DECIMAL：精确的十进制数，用于存储精确小数值。
- 字符串类型：
- CHAR：定长字符串。
- VARCHAR：可变长度字符串。
- TEXT：用于存储大文本数据的字符串。
- 日期和时间类型：
- DATE：日期（年、月、日）。
- TIME：时间（时、分、秒）。
- DATETIME：日期和时间。
- TIMESTAMP：时间戳，通常用于记录数据的修改时间。
- 二进制数据类型：
- BINARY：定长二进制数据。
- VARBINARY：可变长度二进制数据。
- BLOB：用于存储大二进制对象（如图像、音频、视频等）的数据。
- 枚举和集合类型：
- ENUM：用于定义一个字符串列表，列值必须从列表中选择。
- SET：与ENUM类似，但可以选择多个值。
- 布尔类型：
- BOOLEAN或BOOL：用于存储布尔值（true或false）。
这些是一些常见的MySQL数据类型。你可以根据你的数据需求选择合适的数据类型来定义表中的列。不同的数据类型具有不同的存储需求和性能特性，因此选择合适的数据类型对于数据库设计非常重要。
3.数据表操作
3.1创建数据表
CREATE TABLE 数据表名 (列名1 属性,列名2 属性…);
USE db_admin;
CREATE TABLE tb_admin(
id int auto_increment primary key,
user varchar(30) not null,
password varchar(30) not null,
createtime datetime);
3.2查看表结构
查看数据库里面多有的表
SHOW TABLES;
查看表的具体结构（字段，类型）
SHOW COLUMNS FROM tb_admin FROM db_admin;
使用DESCRIBE语句查看，DESCRIBE可以简写成DESC。在查看表结构时，也可以只列出某一列的信息
DESCRIBE 数据表名;
DESCRIBE 数据表名列名;
USE db_admin;
DESC tb_admin;
DESC tb_admin user;
3.3修改表结构
ALTER TABLE语句允许指定多个动作，其动作间使用逗号分隔，每个动作表示对表的一个修改。
ADD [COLUMN] create_definition [FIRST | AFTER column_name ] //添加新字段
| ADD INDEX [index_name] (index_col_name,...) //添加索引名称
| ADD PRIMARY KEY (index_col_name,...) //添加主键名称
| ADD UNIQUE [index_name] (index_col_name,...) //添加唯一索引
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} //修改字段默认值
| CHANGE [COLUMN] old_col_name create_definition //修改字段名/类型
| MODIFY [COLUMN] create_definition //修改子句定义字段
| DROP [COLUMN] col_name //删除字段名称
| DROP PRIMARY KEY //删除主键名称
| DROP INDEX index_name //删除索引名称
| RENAME [AS] new_tbl_name //更改表名
1.添加字段
ALTER TABLE tb_admin ADD email varchar(50) not null ,
modify user varchar(40);
2.修改字段
ALTER TABLE table_name
CHANGE old_column_name new_column_name data_type;
ALTER TABLE db_admin.tb_usernew1
CHANGE COLUMN user username VARCHAR(30) NULL DEFAULT NULL ;
3.删除字段
ALTER TABLE tb_admin DROP email;
3.4修改表名
ALTER TABLE tb_usernew1 RENAME AS tb_userOld;
RENAME TABLE 数据表名1 To 数据表名2
RENAME TABLE tb_admin TO tb_user;
3.5复制表
CREATE TABLE [IF NOT EXISTS] 数据表名
{LIKE 源数据表名 | (LIKE 源数据表名)}
CREATE TABLE tb_userNew
LIKE tb_user;
使用该语法复制数据表时，将创建一个与源数据表相同结构的新表，该数据表的列名、数据类型空指定和索引都将被复制，但是表的内容是不会复制的。因此，新创建的表是一张空表。如果想要复制表中的内容，可以通过使用AS(查询表达式)子句来实现。
3.6删除表
DROP TABLE [IF EXISTS] 数据表名;
DROP TABLE tb_user;
4.表数据的增删改
4.1 添加数据
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
INSERT INTO tb_admin VALUES(1,'mr','mrsoft','2014-09-05 10:25:20','[email protected]');
查看表内的数据
SELECT * FROM 数据表名称;
SELECT * FROM tb_admin;
插入查询结果
INSERT INTO db_database08.tb_admin
(username,password)
SELECT username,password FROM tb_usernew;
4.2更新数据
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
update tb_usernew set password='123' where username='xiafang';
4.3删除数据
DELETE FROM table_name
WHERE condition;
delete from tb_usernew where id=3;
由于TRUNCATE TABLE语句会删除数据表中的所有数据，并且无法恢复
TRUNCATE [TABLE] 数据表名
5.数据的查询
5.1 基本查询语句
select selection_list //要查询的内容，选择哪些列
from 数据表名 //指定数据表
where primary_constraint //查询时需要满足的条件，行必须满足的条件
group by grouping_columns //如何对结果进行分组
order by sorting_cloumns //如何对结果进行排序
having secondary_constraint //查询时满足的第二条件
limit count //限定输出的查询结果
5.2 单表查询
5.2.1 查询指定字段
SELECT 字段名 FROM 表名;
5.2.2查询指定字段
SELECT 字段名 FROM 表名;
select username,email from tb_usernew;
+----------+---------------------+
| username | email |
+----------+---------------------+
| xiafang | [email protected] |
| mr | NULL |
+----------+---------------------+
5.2.3查询指定数据
如果要从很多记录中查询出指定的记录，那么就需要一个查询的条件。设定查询条件应用的是WHERE子句。通过它可以实现很多复杂的条件查询。在使用WHERE子句时，需要使用一些比较运算符来确定查询的条件
select * from tb_usernew where id=2;
+----+----------+----------+------------+-------+
| id | username | password | createtime | email |
+----+----------+----------+------------+-------+
| 2 | mr | mrsoft | NULL | NULL |
+----+----------+----------+------------+-------+
5.2.4带关键字IN的查询
关键字IN可以判断某个字段的值是否在指定的集合中。如果字段的值在集合中，则满足查询条件，该记录将被查询出来；如果不在集合中，则不满足查询条件。其语法格式如下。
SELECT * FROM 表名 WHERE 条件 [NOT] IN(元素1,元素2,...,元素n);
（1）[NOT]：是可选项，加上NOT表示不在集合内满足条件；
（2）元素：表示集合中的元素，各元素之间用逗号隔开，字符型元素需要加上单引号。
select * from tb_usernew where id in (1,2);
+----+----------+----------+---------------------+---------------------+
| id | username | password | createtime | email |
+----+----------+----------+---------------------+---------------------+
| 1 | xiafang | 123 | 2014-09-05 10:25:20 | [email protected] |
| 2 | mr | mrsoft | NULL | NULL |
+----+----------+----------+---------------------+---------------------+
5.2.5带关键字BETWEEN AND的范围查询
键字BETWEEN AND可以判断某个字段的值是否在指定的范围内。如果字段的值在指定范围内，则满足查询条件，该记录将被查询出来。如果不在指定范围内，则不满足查询条件。其语法如下。
SELECT * FROM 表名 WHERE 条件 [NOT] BETWEEN 取值1 AND 取值2;
（1）[NOT]：可选项，表示不在指定范围内满足条件。
（2）取值1：表示范围的起始值。
（3）取值2：表示范围的终止值。
select * from tb_usernew where id between 2 and 3;
+----+----------+----------+---------------------+-------------+
| id | username | password | createtime | email |
+----+----------+----------+---------------------+-------------+
| 2 | mr | mrsoft | NULL | NULL |
| 3 | xiaobai | 444 | 2018-02-03 00:00:00 | [email protected] |
+----+----------+----------+---------------------+-------------+
5.2.6带LIKE的字符匹配查询
LIKE属于较常用的比较运算符，通过它可以实现模糊查询。它有两种通配符：“%”和下划线“_”。
（1）“%”可以匹配一个或多个字符，可以代表任意长度的字符串，长度可以为0。例如，“明%技”表示以“明”开头，以“技”结尾的任意长度的字符串。该字符串可以代表“明日科技”“明日编程科技”“明日图书科技”等字符串。
（2）“”只匹配一个字符。例如，m_n表示以m开头，以n结尾的3个字符。中间的“”可以代表任意一个字符。
select * from tb_usernew where username like 'x%a'; 以x开头 a结尾 中间任意
select * from tb_usernew where username like 'x_a%'; 以x开头 后一位任意 a 后面任意
5.2.7用关键字IS NULL查询空值
关键字IS NULL可以用来判断字段的值是否为空值（NULL）。如果字段的值是空值，则满足查询条件，该记录将被查询出来。如果字段的值不是空值，则不满足查询条件。其语法格式如下。
IS [NOT] NULL
select * from tb_usernew where username is null;
其中，“NOT”是可选项，表示字段不是空值时满足条件。
5.2.8带关键字AND的多条件查询
关键字AND可以用来联合多个条件进行查询。使用关键字AND时，只有同时满足所有查询条件的记录会被查询出来。如果不满足这些查询条件的其中一个，这样的记录将被排除掉。关键字AND的语法格式如下。
select * from 数据表名 where 条件1 and 条件2 [...AND 条件表达式n];
关键字AND连接两个条件表达式，可以同时使用多个关键字AND来连接多个条件表达式。
| id | username | password | createtime | email |
+----+-----------+----------+---------------------+---------------------+
| 1 | xiafang | 123 | 2014-09-05 10:25:20 | [email protected] |
| 2 | mr | mrsoft | NULL | NULL |
| 3 | xiaobai | 444 | 2018-02-03 00:00:00 | [email protected] |
| 4 | xiadsada | 444 | 2018-02-03 00:00:00 | [email protected] |
| 5 | ffiadsada | fafaf | 2018-02-03 00:00:00 | [email protected] |
| 6 | xiaobai | fafaf | 2018-02-03 00:00:00 | [email protected]
select * from tb_usernew where username='xiaobai' and password='444';
+----+----------+----------+---------------------+-------------+
| id | username | password | createtime | email |
+----+----------+----------+---------------------+-------------+
| 3 | xiaobai | 444 | 2018-02-03 00:00:00 | [email protected] |
+----+----------+----------+---------------------+-------------+
5.2.9带关键字OR的多条件查询
关键字OR也可以用来联合多个条件进行查询，但是与关键字AND不同，关键字OR只要满足查询条件中的一个，那么此记录就会被查询出来；如果不满足这些查询条件中的任何一个，这样的记录将被排除掉。关键字OR的语法格式如下。
select * from 数据表名 where 条件1 OR 条件2 [...OR 条件表达式n];
关键字OR可以用来连接两个条件表达式。而且，可以同时使用多个关键字OR连接多个条件表达式。
select * from tb_usernew where username='xiaobai' or password='444';
+----+----------+----------+---------------------+-------------+
| id | username | password | createtime | email |
+----+----------+----------+---------------------+-------------+
| 3 | xiaobai | 444 | 2018-02-03 00:00:00 | [email protected] |
| 4 | xiadsada | 444 | 2018-02-03 00:00:00 | [email protected] |
| 6 | xiaobai | fafaf | 2018-02-03 00:00:00 | [email protected] |
+----+----------+----------+---------------------+-------------+
5.2.10用关键字DISTINCT去除结果中的重复行
使用关键字DISTINCT可以去除查询结果中的重复记录，语法格式如下。
select distinct 字段名 from 表名;
mysql> select username from tb_usernew;
+-----------+
| username |
+-----------+
| xiafang |
| mr |
| xiaobai |
| xiadsada |
| ffiadsada |
| xiaobai |
+-----------+
mysql> select distinct username from tb_usernew;
+-----------+
| username |
+-----------+
| xiafang |
| mr |
| xiaobai |
| xiadsada |
| ffiadsada |
+-----------+
5.2.11用关键字ORDER BY对查询结果排序
使用关键字ORDER BY可以对查询的结果进行升序（ASC）和降序（DESC）排列，在默认情况下，ORDER BY按升序输出结果。如果要按降序排列可以使用DESC来实现。语法格式如下。
ORDER BY 字段名 [ASC|DESC];
（1）ASC表示按升序进行排序。
（2）DESC表示按降序进行排序。
说明对含有NULL值的列进行排序时，如果是按升序排列，NULL值将出现在最前面，如果是按降序排列，NULL值将出现在最后。
mysql> select * from tb_usernew order by id desc;
+----+-----------+----------+---------------------+---------------------+
| id | username | password | createtime | email |
+----+-----------+----------+---------------------+---------------------+
| 6 | xiaobai | fafaf | 2018-02-03 00:00:00 | [email protected] |
| 5 | ffiadsada | fafaf | 2018-02-03 00:00:00 | [email protected] |
| 4 | xiadsada | 444 | 2018-02-03 00:00:00 | [email protected] |
| 3 | xiaobai | 444 | 2018-02-03 00:00:00 | [email protected] |
| 2 | mr | mrsoft | NULL | NULL |
| 1 | xiafang | 123 | 2014-09-05 10:25:20 | [email protected] |
+----+-----------+----------+---------------------+---------------------+
如果列中出现相同的值，也可以使用多个orderby，比较不同字段
mysql> select * from tb_usernew order by createtime desc,id desc;
+----+-----------+----------+---------------------+---------------------+
| id | username | password | createtime | email |
+----+-----------+----------+---------------------+---------------------+
| 6 | xiaobai | fafaf | 2018-02-03 00:00:00 | [email protected] |
| 5 | ffiadsada | fafaf | 2018-02-03 00:00:00 | [email protected] |
| 4 | xiadsada | 444 | 2018-02-03 00:00:00 | [email protected] |
| 3 | xiaobai | 444 | 2018-02-03 00:00:00 | [email protected] |
| 1 | xiafang | 123 | 2014-09-05 10:25:20 | [email protected] |
| 2 | mr | mrsoft | NULL | NULL |
+----+-----------+----------+---------------------+---------------------+
5.2.12 用关键字GROUP BY分组查询
通过关键字GROUP BY可以将数据划分到不同的组中，实现对记录进行分组查询。在查询时，所查询的列必须包含在分组的列中，目的是使查询到的数据没有矛盾。
1．使用关键字GROUP BY来分组
单独使用关键字GROUP BY查询结果只显示每组的一条记录。
mysql> select * from tb_usernew group by password;
+----+-----------+----------+---------------------+---------------------+
| id | username | password | createtime | email |
+----+-----------+----------+---------------------+---------------------+
| 1 | xiafang | 123 | 2014-09-05 10:25:20 | [email protected] |
| 3 | xiaobai | 444 | 2018-02-03 00:00:00 | [email protected] |
| 5 | ffiadsada | fafaf | 2018-02-03 00:00:00 | [email protected] |
| 2 | mr | mrsoft | NULL | NULL |
+----+-----------+----------+---------------------+---------------------+
2．关键字GROUP BY与GROUP_CONCAT()函数一起使用
使用关键字GROUP BY和GROUP_CONCAT()函数查询，可以将每个组中的所有字段值都显示出来。
mysql> select id,GROUP_CONCAT(username),GROUP_CONCAT(password) from tb_usernew GROUP BY password;
+----+------------------------+------------------------+
| id | GROUP_CONCAT(username) | GROUP_CONCAT(password) |
+----+------------------------+------------------------+
| 1 | xiafang | 123 |
| 3 | xiaobai,xiadsada | 444,444 |
| 5 | ffiadsada,xiaobai | fafaf,fafaf |
| 2 | mr | mrsoft |
+----+------------------------+------------------------+
使用 GROUP BY 关键字可以对结果集进行分组查询，通常与聚合函数（如SUM、COUNT、AVG等）一起使用，以便对每个分组的数据进行统计或计算。以下是 GROUP BY 的一般语法：
sqlCopy codeSELECT column1, column2, aggregate_function(column)
FROM table_name
WHERE condition
GROUP BY column1, column2;
其中：
column1, column2是你希望按其进行分组的列。aggregate_function(column)是用于对每个分组执行聚合计算的函数（如SUM、COUNT、AVG等）。table_name是要查询的表的名称。condition是可选的筛选条件，用于过滤数据。
以下是一个示例，假设你有一个名为 sales 的表，其中包含销售数据，你想按销售区域分组，并计算每个区域的销售总额：
sqlCopy codeSELECT region, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region;
在这个示例中，我们按 region 列进行分组，并使用 SUM 函数计算每个区域的销售总额。AS total_sales 用于为结果列指定别名。
5.2.13用关键字LIMIT限制查询结果的数量
关键字LIMIT可以对查询结果的记录条数进行限定，控制它输出的行数
mysql> select * from tb_usernew order by createtime desc limit 3;
+----+-----------+----------+---------------------+-------------+
| id | username | password | createtime | email |
+----+-----------+----------+---------------------+-------------+
| 6 | xiaobai | fafaf | 2018-02-03 00:00:00 | [email protected] |
| 5 | ffiadsada | fafaf | 2018-02-03 00:00:00 | [email protected] |
| 3 | xiaobai | 444 | 2018-02-03 00:00:00 | [email protected] |
+----+-----------+----------+---------------------+-------------+
使用关键字LIMIT还可以从查询结果的中间部分取值。首先要定义两个参数，参数1是开始读取的第一条记录的编号（在查询结果中，第一个结果的记录编号是0，而不是1）；参数2是要查询记录的个数。
mysql> select * from tb_usernew order by id asc limit 1,3;
+----+----------+----------+---------------------+-------------+
| id | username | password | createtime | email |
+----+----------+----------+---------------------+-------------+
| 2 | mr | mrsoft | NULL | NULL |
| 3 | xiaobai | 444 | 2018-02-03 00:00:00 | [email protected] |
| 4 | xiadsada | 444 | 2018-02-03 00:00:00 | [email protected] |
+----+----------+----------+---------------------+-------------+
5.3聚合函数查询
COUNT()函数
COUNT()函数，对于除“”以外的任何参数，返回所选择集合中非NULL值的行的数目；对于参数“”，返回选择集合中所有行的数目，包含NULL值的行。没有WHERE子句的COUNT(*)是经过内部优化的，能够快速返回表中所有的记录总数。
mysql> select count(*) from tb_usernew;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
mysql> select count(*) from tb_usernew where password='444';
+----------+
| count(*) |
+----------+
| 2 |
+----------+
SUM()函数
SUM()函数可以求出表中某个字段取值的总和。
mysql> select sum(id) from tb_usernew;
+---------+
| sum(id) |
+---------+
| 21 |
+---------+
AVG()函数
AVG()函数可以求出表中某个字段取值的平均值。
mysql> select avg(id) from tb_usernew;
+---------+
| avg(id) |
+---------+
| 3.5000 |
+---------+
MAX()函数
MAX()函数可以求出表中某个字段取值的最大值。
mysql> select max(id) from tb_usernew;
+---------+
| max(id) |
+---------+
| 6 |
+---------+
MIN()函数
MAX()函数可以求出表中某个字段取值的最小值。
mysql> select min(id) from tb_usernew;
+---------+
| min(id) |
+---------+
| 1 |
+---------+
5.4连接查询
下面有两张表，employees和departments
mysql> select * from employees;
+-------------+------------+-----------+---------------+
| employee_id | first_name | last_name | department_id |
+-------------+------------+-----------+---------------+
| 1 | John | Doe | 1 |
| 2 | Jane | Smith | 2 |
| 3 | Bob | Johnson | 1 |
| 4 | Alice | Brown | 2 |
| 5 | xiaoming | djjdf | NULL |
| 6 | xiaohong | fujjda | 4 |
+-------------+------------+-----------+---------------+
mysql> select * from departments;
+---------------+-----------------+
| department_id | department_name |
+---------------+-----------------+
| 1 | HR |
| 2 | IT |
| 3 | Finance |
| 4 | NULL |
+---------------+-----------------+
5.4.1交叉连接
交叉连接（CROSS JOIN）是一种关系型数据库查询操作，它返回两个表的笛卡尔积，即将一个表的每一行与另一个表的每一行进行组合。
mysql> select * from employees cross join departments;
+-------------+------------+-----------+---------------+---------------+-----------------+
| employee_id | first_name | last_name | department_id | department_id | department_name |
+-------------+------------+-----------+---------------+---------------+-----------------+
| 1 | John | Doe | 1 | 1 | HR |
| 1 | John | Doe | 1 | 2 | IT |
| 1 | John | Doe | 1 | 3 | Finance |
| 1 | John | Doe | 1 | 4 | NULL |
| 2 | Jane | Smith | 2 | 1 | HR |
| 2 | Jane | Smith | 2 | 2 | IT |
| 2 | Jane | Smith | 2 | 3 | Finance |
| 2 | Jane | Smith | 2 | 4 | NULL |
| 3 | Bob | Johnson | 1 | 1 | HR |
| 3 | Bob | Johnson | 1 | 2 | IT |
| 3 | Bob | Johnson | 1 | 3 | Finance |
| 3 | Bob | Johnson | 1 | 4 | NULL |
| 4 | Alice | Brown | 2 | 1 | HR |
| 4 | Alice | Brown | 2 | 2 | IT |
| 4 | Alice | Brown | 2 | 3 | Finance |
| 4 | Alice | Brown | 2 | 4 | NULL |
| 5 | xiaoming | djjdf | NULL | 1 | HR |
| 5 | xiaoming | djjdf | NULL | 2 | IT |
| 5 | xiaoming | djjdf | NULL | 3 | Finance |
| 5 | xiaoming | djjdf | NULL | 4 | NULL |
| 6 | xiaohong | fujjda | 4 | 1 | HR |
| 6 | xiaohong | fujjda | 4 | 2 | IT |
| 6 | xiaohong | fujjda | 4 | 3 | Finance |
| 6 | xiaohong | fujjda | 4 | 4 | NULL |
+-------------+------------+-----------+---------------+---------------+-----------------+
5.4.2内连接查询
内连接是最普遍的连接类型，而且是最匀称的，因为它们要求构成连接的每一部分的每个表的匹配，不匹配的行将被排除。
内连接的最常见的例子是相等连接，也就是连接后的表中的某个字段与每个表中的都相同。这种情况下，最后的结果集只包含参加连接的表中与指定字段相符的行。
隐式内连接
select * from employees,departments where employees.department_id = departments.department_id;
显示内连接
select * from employees join departments on employees.department_id = departments.department_id;
+-------------+------------+-----------+---------------+---------------+-----------------+
| employee_id | first_name | last_name | department_id | department_id | department_name |
+-------------+------------+-----------+---------------+---------------+-----------------+
| 1 | John | Doe | 1 | 1 | HR |
| 2 | Jane | Smith | 2 | 2 | IT |
| 3 | Bob | Johnson | 1 | 1 | HR |
| 4 | Alice | Brown | 2 | 2 | IT |
| 6 | xiaohong | fujjda | 4 | 4 | NULL |
+-------------+------------+-----------+---------------+---------------+-----------------+
5.4.3外连接
外连接分为左外连接（LEFT JOIN）、右外连接（RIGHT JOIN）和全外连接3种类型。
左外连接（LEFT JOIN）是指将左表中的所有数据分别与右表中的每条数据进行连接组合，返回的结果除内连接的数据外，还包括左表中不符合条件的数据，并在右表的相应列中添加NULL值。
mysql> select * from employees left join departments on employees.department_id = departments.department_id;
+-------------+------------+-----------+---------------+---------------+-----------------+
| employee_id | first_name | last_name | department_id | department_id | department_name |
+-------------+------------+-----------+---------------+---------------+-----------------+
| 1 | John | Doe | 1 | 1 | HR |
| 3 | Bob | Johnson | 1 | 1 | HR |
| 2 | Jane | Smith | 2 | 2 | IT |
| 4 | Alice | Brown | 2 | 2 | IT |
| 6 | xiaohong | fujjda | 4 | 4 | NULL |
| 5 | xiaoming | djjdf | NULL | NULL | NULL |
+-------------+------------+-----------+---------------+---------------+-----------------+
右外连接（RIGHT JOIN）是指将右表中的所有数据分别与左表中的每条数据进行连接组合，返回的结果除内连接的数据外，还包括右表中不符合条件的数据，并在左表的相应列中添加NULL。
mysql> select * from employees right join departments on employees.department_id = departments.department_id;
+-------------+------------+-----------+---------------+---------------+-----------------+
| employee_id | first_name | last_name | department_id | department_id | department_name |
+-------------+------------+-----------+---------------+---------------+-----------------+
| 1 | John | Doe | 1 | 1 | HR |
| 2 | Jane | Smith | 2 | 2 | IT |
| 3 | Bob | Johnson | 1 | 1 | HR |
| 4 | Alice | Brown | 2 | 2 | IT |
| 6 | xiaohong | fujjda | 4 | 4 | NULL |
| NULL | NULL | NULL | NULL | 3 | Finance |
+-------------+------------+-----------+---------------+---------------+-----------------+
全连接（FULL JOIN 或 FULL OUTER JOIN）是一种关系型数据库查询操作，它返回两个表中的所有行，包括匹配的行和不匹配的行。全连接结合了左外连接和右外连接的结果，因此它可以显示两个表中的所有数据，无论是否存在匹配的关联。
全连接的语法通常需要使用 UNION 运算符来合并左外连接和右外连接的结果，因为大多数关系型数据库管理系统（RDBMS）不直接支持 FULL JOIN 操作。
以下是一个使用 UNION 来模拟全连接的示例：
sqlCopy codeSELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column
UNION
SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
mysql> select * from employees left join departments on employees.department_id = departments.department_id union select * from employees right join departments on employees.department_id = departments.department_id;
+-------------+------------+-----------+---------------+---------------+-----------------+
| employee_id | first_name | last_name | department_id | department_id | department_name |
+-------------+------------+-----------+---------------+---------------+-----------------+
| 1 | John | Doe | 1 | 1 | HR |
| 3 | Bob | Johnson | 1 | 1 | HR |
| 2 | Jane | Smith | 2 | 2 | IT |
| 4 | Alice | Brown | 2 | 2 | IT |
| 6 | xiaohong | fujjda | 4 | 4 | NULL |
| 5 | xiaoming | djjdf | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | 3 | Finance |
+-------------+------------+-----------+---------------+---------------+-----------------+
UNION ALL 是将两个查询的结果直接合并起来，而UNION会去重
mysql> select * from employees left join departments on employees.department_id = departments.department_id union all select * from employees right join departments on employees.department_id = departments.department_id;
+-------------+------------+-----------+---------------+---------------+-----------------+
| employee_id | first_name | last_name | department_id | department_id | department_name |
+-------------+------------+-----------+---------------+---------------+-----------------+
| 1 | John | Doe | 1 | 1 | HR |
| 3 | Bob | Johnson | 1 | 1 | HR |
| 2 | Jane | Smith | 2 | 2 | IT |
| 4 | Alice | Brown | 2 | 2 | IT |
| 6 | xiaohong | fujjda | 4 | 4 | NULL |
| 5 | xiaoming | djjdf | NULL | NULL | NULL |
| 1 | John | Doe | 1 | 1 | HR |
| 2 | Jane | Smith | 2 | 2 | IT |
| 3 | Bob | Johnson | 1 | 1 | HR |
| 4 | Alice | Brown | 2 | 2 | IT |
| 6 | xiaohong | fujjda | 4 | 4 | NULL |
| NULL | NULL | NULL | NULL | 3 | Finance |
+-------------+------------+-----------+---------------+---------------+-----------------+
12 rows in set (0.10 sec)
5.4.3自连接查询
自连接是一种数据库查询操作，它指的是在同一个表内创建两个或多个表的副本，并使用不同的表别名来关联它们，以便在同一表中查找相关数据。自连接通常用于处理包含层次结构或父子关系的数据
自连接的典型场景包括：
- 组织结构：在一个员工表中，使用自连接可以查找员工的上级或下级，构建组织结构图。
- 评论回复：在一个评论表中，使用自连接可以查找评论的回复以及回复的回复，创建评论线程。
- 朋友关系：在一个社交网络中，使用自连接可以查找用户之间的朋友关系，包括一度、二度、三度等关系。
自连接的一般语法如下：
SELECT a.column1, b.column2
FROM table AS a
JOIN table AS b ON a.related_column = b.related_column;
让我们考虑一个名为 employees 的表，其中包含员工的信息，每个员工都有一个唯一的ID，以及一个指向其直接上级的经理ID。我们可以使用自连接来查找每个员工的直接上级的姓名。以下是一个示例：
假设 employees 表的数据如下：
+----+--------+-----------+
| ID | Name | ManagerID |
+----+--------+-----------+
| 1 | John | 3 |
| 2 | Alice | 3 |
| 3 | Bob | 4 |
| 4 | Carol | NULL |
| 5 | David | 4 |
+----+--------+-----------+
在这个示例中，员工表中包含了员工的ID、姓名和经理ID。注意，ManagerID 列包含 NULL 值，表示顶级经理没有上级。
现在，我们可以使用自连接来查找每个员工的直接上级的姓名：
SELECT e1.Name AS EmployeeName, e2.Name AS ManagerName
FROM employees AS e1
LEFT JOIN employees AS e2 ON e1.ManagerID = e2.ID;
查询的结果将如下所示：
+-------------+-------------+
| EmployeeName| ManagerName |
+-------------+-------------+
| John | Bob |
| Alice | Bob |
| Bob | Carol |
| Carol | NULL |
| David | Carol |
+-------------+-------------+
在这个结果中，我们得到了每个员工的姓名以及他们的直接上级的姓名。这个查询通过自连接将 employees 表与自身关联，使用别名 e1 和 e2 分别表示员工和经理，然后通过员工的 ManagerID 列关联了他们。通过自连接，我们可以轻松地查找相关的数据，例如员工和他们的上级之间的关系。
5.5子查询
创建两张表用来做演示
+-------+---------+----------------------------+-------+
| em_id | em_name | em_time | dp_id |
+-------+---------+----------------------------+-------+
| 1 | 张三丰 | 2023-09-12 18:17:35.000000 | 1 |
| 2 | 李四 | 2023-07-13 18:17:55.000000 | 2 |
| 3 | 李弘毅 | 2022-06-15 18:18:12.000000 | 2 |
| 4 | 孙小梅 | 2019-10-15 18:18:47.000000 | 3 |
| 5 | 李菲 | 2023-02-15 18:19:06.000000 | 4 |
| 6 | 赵飞燕 | 2020-07-15 18:19:29.000000 | 3 |
| 7 | 裴钰 | 2023-06-28 18:20:08.000000 | 4 |
+-------+---------+----------------------------+-------+
+---------------+-----------------+
| department_id | department_name |
+---------------+-----------------+
| 1 | 设计部 |
| 2 | 研发部 |
| 3 | 采购部 |
| 4 | 运营部 |
+---------------+-----------------+
5.5.1 标量子查询
标量子查询：标量子查询返回的是单个值（数字，字符串，日期等），可以作为查询语句中的一个常量使用
常用的操作符：= <> > >= < <=
(1)查询研发部的所有员工信息
- 首先从部门表中查询出研发部所对应的id
- 在根据id从员工信息表中查出所对应的人员
mysql> select * from employee where dp_id =(select department_id from departments where department_name ='研发部');
+-------+---------+----------------------------+-------+
| em_id | em_name | em_time | dp_id |
+-------+---------+----------------------------+-------+
| 2 | 李四 | 2023-07-13 18:17:55.000000 | 2 |
| 3 | 李弘毅 | 2022-06-15 18:18:12.000000 | 2 |
+-------+---------+----------------------------+-------+
(2)查询在李弘毅 入职之前的所有员工信息
- 首先查出李弘毅 的入职日期
- 在查询出小于该日期的所有员工关系信息
mysql> select * from employee where em_time <(select em_time from employee where em_name ='李弘毅 ');
+-------+---------+----------------------------+-------+
| em_id | em_name | em_time | dp_id |
+-------+---------+----------------------------+-------+
| 4 | 孙小梅 | 2019-10-15 18:18:47.000000 | 3 |
| 6 | 赵飞燕 | 2020-07-15 18:19:29.000000 | 3 |
+-------+---------+----------------------------+-------+
5.5.2 列子查询
列子查询：列子查询返回的是一列数据（可以是多行） 列子查询返回的是一列数据，可以和主查询的结果进行比较或者连接
（1)查询研发部和采购部的所有员工信息
- 首先从部门表中查询出研发部和采购部所对应的id
- 在根据两个id从员工信息表中查出所对应的人员
mysql> select * from employee where dp_id in (select department_id from departments where department_name ='研发部' or department_name ='采购部' );
+-------+---------+----------------------------+-------+
| em_id | em_name | em_time | dp_id |
+-------+---------+----------------------------+-------+
| 2 | 李四 | 2023-07-13 18:17:55.000000 | 2 |
| 3 | 李弘毅 | 2022-06-15 18:18:12.000000 | 2 |
| 4 | 孙小梅 | 2019-10-15 18:18:47.000000 | 3 |
| 6 | 赵飞燕 | 2020-07-15 18:19:29.000000 | 3 |
+-------+---------+----------------------------+-------+
（2）查询入职比采购部门所有员工都晚的人员信息
- 查询采购部门的入职时间
- 查询所有大于该时间的人员信息
mysql> select * from employee where em_time > all (select em_time from employee where dp_id = (select department_id from departments where department_name ='采购部') );
+-------+---------+----------------------------+-------+
| em_id | em_name | em_time | dp_id |
+-------+---------+----------------------------+-------+
| 1 | 张三丰 | 2023-09-12 18:17:35.000000 | 1 |
| 2 | 李四 | 2023-07-13 18:17:55.000000 | 2 |
| 3 | 李弘毅 | 2022-06-15 18:18:12.000000 | 2 |
| 5 | 李菲 | 2023-02-15 18:19:06.000000 | 4 |
| 7 | 裴钰 | 2023-06-28 18:20:08.000000 | 4 |
+-------+---------+----------------------------+-------+
（3）查询入职比采购部门任一员工都晚的人员信息
mysql> select * from employee where em_time > any (select em_time from employee where dp_id = (select department_id from departments where department_name ='采购部') );
+-------+---------+----------------------------+-------+
| em_id | em_name | em_time | dp_id |
+-------+---------+----------------------------+-------+
| 1 | 张三丰 | 2023-09-12 18:17:35.000000 | 1 |
| 2 | 李四 | 2023-07-13 18:17:55.000000 | 2 |
| 3 | 李弘毅 | 2022-06-15 18:18:12.000000 | 2 |
| 5 | 李菲 | 2023-02-15 18:19:06.000000 | 4 |
| 6 | 赵飞燕 | 2020-07-15 18:19:29.000000 | 3 |
| 7 | 裴钰 | 2023-06-28 18:20:08.000000 | 4 |
+-------+---------+----------------------------+-------+
5.5.3 行子查询
行子查询：行子查询返回的是一行数据，（可以是多列）行子查询返回的是一行数据，可以作为一个整体进行处理或者与其他行进行比较
改一下数据表结构
+-------+---------+----------------------------+-------+-----------+
| em_id | em_name | em_time | dp_id | em_salary |
+-------+---------+----------------------------+-------+-----------+
| 1 | 张三丰 | 2023-09-12 18:17:35.000000 | 1 | 3000.00 |
| 2 | 李四 | 2023-07-13 18:17:55.000000 | 2 | 5000.00 |
| 3 | 李弘毅 | 2022-06-15 18:18:12.000000 | 2 | 8899.00 |
| 4 | 孙小梅 | 2019-10-15 18:18:47.000000 | 3 | 7000.00 |
| 5 | 李菲 | 2023-02-15 18:19:06.000000 | 4 | 8777.00 |
| 6 | 赵飞燕 | 2020-07-15 18:19:29.000000 | 3 | 7000.00 |
| 7 | 裴钰 | 2023-06-28 18:20:08.000000 | 4 | 9889.00 |
+-------+---------+----------------------------+-------+-----------+
（1）查询和赵飞燕同一部门且薪水相同的人
- 查询赵飞燕的薪水和部门id
- 查询同时满足两个条件的员工信息
mysql> select * from employee where (dp_id,em_salary)=(select dp_id,em_salary from employee where em_name='赵飞燕');
+-------+---------+----------------------------+-------+-----------+
| em_id | em_name | em_time | dp_id | em_salary |
+-------+---------+----------------------------+-------+-----------+
| 4 | 孙小梅 | 2019-10-15 18:18:47.000000 | 3 | 7000.00 |
| 6 | 赵飞燕 | 2020-07-15 18:19:29.000000 | 3 | 7000.00 |
+-------+---------+----------------------------+-------+-----------+
5.5.4表子查询
表子查询返回的是多行多列，表子查询返回的是一个表或视图，可以嵌套在另一个查询语句中使用
常用的操作符 IN
（1）查询和“李宏毅”，“赵飞燕”薪资和部门相同的员工信息
mysql> select * from employee where (dp_id,em_salary) in(select dp_id,em_salary from employee where em_name='赵飞燕' or em_name='李弘毅');
+-------+---------+----------------------------+-------+-----------+
| em_id | em_name | em_time | dp_id | em_salary |
+-------+---------+----------------------------+-------+-----------+
| 2 | 李四 | 2023-07-13 18:17:55.000000 | 2 | 5000.00 |
| 3 | 李弘毅 | 2022-06-15 18:18:12.000000 | 2 | 5000.00 |
| 4 | 孙小梅 | 2019-10-15 18:18:47.000000 | 3 | 7000.00 |
| 6 | 赵飞燕 | 2020-07-15 18:19:29.000000 | 3 | 7000.00 |
+-------+---------+----------------------------+-------+-----------+
4 rows in set (0.11 sec)
5.5.5带关键字EXISTS的子查询
使用关键字EXISTS时，内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句查询到满足条件的记录，就返回一个真值（true），否则将返回一个假值（false）。当返回的值为true时，外层查询语句将进行查询；当返回的值为false时，外层查询语句不进行查询或者查询不出任何记录
练习
统计每个部门所用用的员工数量
mysql> SELECT department_id, department_name, COUNT(*) AS employee_count
FROM employee INNER JOIN departments ON employee.dp_id = departments.department_id GROUP BY department_id, department_name;
+---------------+-----------------+----------------+
| department_id | department_name | employee_count |
+---------------+-----------------+----------------+
| 1 | 设计部 | 1 |
| 2 | 研发部 | 2 |
| 3 | 采购部 | 2 |
| 4 | 运营部 | 2 |
+---------------+-----------------+----------------+