相关术语
- 数据库: 数据库是一些关联表的集合。
- 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
- 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
- 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
- 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
- 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
- 外键：外键用于关联两个表。
- 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
- 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
- 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
三大范式
数据库逻辑设计之三大范式通俗理解，一看就懂，书上说的太晦涩 - 知乎
第一范式：原子性，字段属性不可再分。如：student表：学号，姓名性别；姓名和性别可再分，所以不符合一范式。
第二范式：必须有主键，非主键字段依赖主键，其余字段不存在相互依赖。如：student表：学号、课程号、姓名、学分；分依赖课程号，姓名依赖与学号，所以不符合二范式。
第三范式：不存在依赖传递，表中每一列都和主键是直接依赖的，不是间接依赖。如：student表: 学号, 姓名, 年龄, 学院名称, 学院电话；存在依赖传递，(学号) → (学生)→(所在学院) → (学院电话)
；正确做法：学生：(学号, 姓名, 年龄, 所在学院)；学院：(学院, 电话)。
数据类型
数值类型
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 Bytes | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 Bytes | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 Bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 Bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 Bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 Bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
日期和时间类型
| 类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | ‘-838:59:59’/’838:59:59’ | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ | YYYY-MM-DD hh:mm:ss | 混合日期和时间值 |
| TIMESTAMP | 4 | ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYY-MM-DD hh:mm:ss | 混合日期和时间值，时间戳 |
字符串类型
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65 535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
注意：
char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) ，无论存放的是数字、字母还是 UTF8 编码的汉字，都可以存50个
修饰符
适用于所有类型的修饰符
| null | 数据列可包含null值，默认值 |
| not null | 数据列不可包含null值 |
| default | 默认值 |
| primary key | 主键，所有记录中此字段的值不能重复，且不能为null |
| unique key | 唯一键，所有记录中此字段的值不能重复，但可以为null |
| character set name | 指定一个字符集 |
适用于数值型的修饰符
| auto_increment | 自动递增，适合整数类型，必须作用于某个key的字段，如primary key |
| unsigned | 无符号，只能存储非负值 |
注释
单行注释：-- 注释内容
多行注释：
/*注释内容
注释内容
*/
字符集
早期MySQL版本默认为 latin1，从MySQL8.0开始默认字符集为 utf8mb4
查看支持的所有字符集：show character set;
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
+----------+---------------------------------+---------------------+--------+
查看当前默认字符集：show variables like 'character%';
mysql> show variables like 'character%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.7.29-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
设置服务器默认字符集
vim /etc/my.cnf
[mysqld]
character-set-server=utf8mb4设置mysql客户端默认字符集
vim /etc/my.cnf
针对mysql客户端
[mysql]
default-character-set=utf8mb4
针对所有MySQL客户端
[client]
default-character-set=utf8mb4排序
SHOW COLLATION;
#注意
utf8_general_ci不区分大小写
utf8_bin 区分大小写
查看当前使用的排序规则
mysql> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.01 sec)
变量
系统变量：MySQL数据库中内置变量，通过 @@var_name 引用
用户自定以变量：
1、普通变量：在当前会话中有效，可用 @var_name 引用
2、局部变量：在函数或存储过程内才有效，可用 delcare 声明，然后通过 @var_name 引用
SQL语言分类
结构化查询语言(Structured Query Language)简称SQL。
DDL(数据定义语言)
数据定义语言（Data Definition Language）简称DDL。是 SQL 语言集中负责数据结构定义与数据库对象定义的语言。
DDL用来创建数据库中的各种对象，创建、删除、修改表的结构，比如表、视图、索引、同义词、聚簇等。其主要功能是定义数据库对象，核心指令为create、drop、alter。
和DML相比，DML是修改数据库表中的数据，而 DDL 是修改数据中表的结构。
库
查询数据库列表
show databases;
创建数据库testone
create database testone;
创建数据库testone并指定字符集和排序规则
create database testdb character set utf8 collate utf8_bin;
查看数据库创建语句
mysql> show create database test;
+----------+---------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------------------------------------------------------------------+
| testdb | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
选择数据库testone
use testone;
修改数据库
alter database cs character set utf8;
删除数据库testone
drop database testone;表
同一库的不同表可以使用不同的存储引擎；同一个库中表建议用同一种存储引擎。
创建
create table table_name (column_name column_type);
create table student (
id int unsigned auto_increment primary key,
name varchar(20) not null,
age tinyint unsigned,
gender enum('M','F') default 'M'
)engine=innodb auto_increment=10 default character set utf8;
create table student：创建一个名为student的表。
id int unsigned auto_increment primary key：
id：这是表的主键列。
int：该列的数据类型是整数。
unsigned：这意味着该列只能存储非负整数。
auto_increment：每当向表中插入新行时，该列的值会自动递增。
primary key：这是表的主键，用于唯一标识表中的每一行。
name varchar(20) not null：
name：这是表的一个列。
varchar(20)：该列的数据类型是可变长度的字符串，最大长度为20个字符。
not null：这意味着该列不能存储NULL值，即每一行都必须有一个值。
age tinyint unsigned：
age：这是表的一个列。
tinyint：该列的数据类型是一个很小的整数。
unsigned：这意味着该列只能存储非负整数。
gender enum('M','F') default 'M'：
gender：这是表的一个列。
enum('M','F')：该列的数据类型是枚举类型，可以存储'M'（男性）或'F'（女性）。
default 'M'：如果未为该列指定值，则默认值为'M'（男性）。
engine=innodb：指定表的存储引擎为InnoDB。InnoDB是MySQL的默认存储引擎之一，提供了事务处理、行级锁定等功能。
auto_increment=10：设置下一个插入的行的id字段的初始自动递增值为10。这意味着如果你向表中插入新行而不指定id值，它的值将自动从10开始递增。
default character set utf8：设置表的默认字符集为UTF-8，这允许存储各种国际字符。
通过查询现存的表创建新表，新表会被直接插入查询而来的数据
create table user select user,host from mysql.user;
通过复制现存的表的结构创建新表，但不复制数据
create table teacher like student;
查看
查看所有表
show tables;
查看创建表的语句
show create table student;
查看表结构
mysql> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
查看表状态
show table status like 'student';
查看库中所有表状态
show table status like 'student' from test;改、删
修改表名
alter table teacher rename s1;
在host列后新加password列
alter table user add password varchar(20) after host;
修改列数据类型
alter table user modify password int;
删除password列
alter table user drop column password;
删除表s1
drop table s1;DML(数据操纵语言)
数据操纵语言（Data Manipulation Language）简称DML。过它可以实现对数据库的基本操作，对数据库其中的对象和数据运行访问工作的语言。
DML的主要功能是访问数据，因此其语法都是以读写数据库为主。
DML的核心指令为insert、update、delete。
insert
表中每一列都新增数据
insert into 表名 values (值1,值2,…);
insert into user(user,host) values ('liubei','127.0.0.1');表中部分列新增数据
insert into 表名(列名1，列名2，…) values (值1,值2,…);
insert into user values ('caocao','127.0.0.1','abcd');
update
修改数据一定要有限制条件，否则将修改所有行的指定字段
update 表名 set 列名1=值A,列名2=值B where 条件;
update user set host='localhost' where user='caocao';可通过配置mysql选项避免此错误
=vim /etc/my.cnf
[mysql]
safe-updates
delete
删除数据一定要有限制条件，否则将清空表中所有数据。
删除数据可通过某个标识字段实现，删除数据即修改标识字段。
delete from table_name 表名 where 条件;
delete from user where user='caocao';DQL(数据查询语言)
数据查询语言（Data Query Language）简称DQL。是SQL语言中，负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。
DQL的核心指令为select。通常与关键字from、where、group by、having、order by等一起使用，组成查询语句。
单表
数据量大时严禁使用select * from tablename;
查询表中部分列数据
select 列名1,列名2,… from 表名;
mysql> select host,host from user;
+-----------+-----------+
| host | host |
+-----------+-----------+
| localhost | localhost |
| localhost | localhost |
| localhost | localhost |
| 127.0.0.1 | 127.0.0.1 |
+-----------+-----------+
SQL查询基本结构
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_CACHE | SQL_NO_CACHE]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]
说明
[ALL | DISTINCT | DISTINCTROW]
`ALL`：选择所有的行，包括重复的行。
`DISTINCT`：选择唯一的行，即去除重复的行。
`DISTINCTROW`：在某些数据库系统中，如MySQL，这个关键字与`DISTINCT`有相同的效果。但在其他数据库中，可能没有这个关键字。
[SQL_CACHE | SQL_NO_CACHE]
`SQL_CACHE`：如果结果之前已经被缓存，则直接从缓存中获取。
`SQL_NO_CACHE`：不使用缓存。
select_expr [, select_expr ...]
从数据库中检索的列的列表。
FROM table_references
从哪些表中检索数据。
[WHERE where_condition]
一个可选的条件，用于过滤你想检索的行。
[GROUP BY {col_name | expr | position}
一个可选的部分，用于根据一个或多个列对结果进行分组。
[ASC | DESC], ...]
对于GROUP BY的结果，你可以指定如何排序。ASC表示升序，DESC表示降序。WITH ROLLUP会为每个分组提供一个总计行。
[HAVING where_condition]
一个可选的条件，用于过滤经过GROUP BY处理的行。
[ORDER BY {col_name | expr | position}
一个可选的部分，用于对结果进行排序。
[ASC | DESC], ...]
指定排序的方向，ASC表示升序，DESC表示降序。
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
限制返回的行数。例如，LIMIT 10会返回前10行，而LIMIT 5,10会跳过前5行并返回接下来的10行。
[FOR UPDATE | LOCK IN SHARE MODE]
加锁，用于控制对检索到的行的并发访问。
for update：写锁
lock in share node：读锁where：条件查询
=：等于
>：大于
>=：大于等于
<：小于
<=：小于等于
!=或<>：不等于
and：且
or：或
not：取反
like：模糊查询关键字
%：表示任意个任意字符
_：表示一个任意字符
between...and...：表示一个连续的范围
in：表示一个不连续的范围
is null：判断为空。null不等于空字符串
is not null：判断非空
多个判断条件若要成为整体可用()
group by：指定条件把查询结果进行"分组"以用于做"聚合"运算
常用聚合函数：count()、sum()、max()、min()、avg()
聚合函数不对null统计；
一旦分组group by，select 语句后只能跟分组的字段，聚合函数
having：对分组聚合运算后的结果指定过滤条件
limit (n-1)*m,m：查询第n页数据，每页显示m条记录
学生表
教师表
练习
字段别名
mysql> select StuID 学生ID,name 姓名 from students;
+----------+---------------+
| 学生ID | 姓名 |
+----------+---------------+
| 1 | Shi Zhongyu |
| 2 | Shi Potian |
| 3 | Xie Yanke |
| 4 | Ding Dian |
| 5 | Yu Yutong |
....
查询StuID小于3学生的StuID、姓名
mysql> select StuID,Name from students where StuID < 3;
+-------+-------------+
| StuID | Name |
+-------+-------------+
| 1 | Shi Zhongyu |
| 2 | Shi Potian |
+-------+-------------+
查询StuID为1,3,5学生的StuID、Name
mysql> select StuID,Name from students where StuID in (1,3,5);
+-------+-------------+
| StuID | Name |
+-------+-------------+
| 1 | Shi Zhongyu |
| 3 | Xie Yanke |
| 5 | Yu Yutong |
+-------+-------------+
查询年龄小于等于20且性别为F学生的姓名、年龄、性别
mysql> select Name,Age,Gender from students where Age <=20 and Gender = 'F';
+--------------+-----+--------+
| Name | Age | Gender |
+--------------+-----+--------+
| Xi Ren | 19 | F |
| Lin Daiyu | 17 | F |
| Ren Yingying | 20 | F |
| Yue Lingshan | 19 | F |
| Wen Qingqing | 19 | F |
| Lu Wushuang | 17 | F |
| Xue Baochai | 18 | F |
| Diao Chan | 19 | F |
| Xiao Qiao | 20 | F |
+--------------+-----+--------+
查询班级ID为null学生的StuID、姓名，班级ID
mysql> select StuID,Name,ClassID from students where ClassId is null;
+-------+-------------+---------+
| StuID | Name | ClassID |
+-------+-------------+---------+
| 24 | Xu Xian | NULL |
| 25 | Sun Dasheng | NULL |
+-------+-------------+---------+
将学生信息分页，每页3个，查询第2页数据。
mysql> select StuID,Name from students limit 3,3;
+-------+-----------+
| StuID | Name |
+-------+-----------+
| 4 | Ding Dian |
| 5 | Yu Yutong |
| 6 | Shi Qing |
+-------+-----------+
查询学生姓名中以S开头学生的StuID、Name 并 以年龄升序排序 且 只返回除去年龄最小的学生
mysql> select StuID,Name,Age from students where Name like 'S%' order by Age asc limit 1,3;
+-------+-------------+-----+
| StuID | Name | Age |
+-------+-------------+-----+
| 2 | Shi Potian | 22 |
| 6 | Shi Qing | 46 |
| 25 | Sun Dasheng | 100 |
+-------+-------------+-----+
计算所有性别为'F'的学生的平均年龄
mysql> select sum(Age)/count(*) from students where Gender = 'F';
+-------------------+
| sum(Age)/count(*) |
+-------------------+
| 19.0000 |
+-------------------+
统计每个非null的班级ID对应的学生数量
mysql> select classid,count(*) 数量 from students where classid is not null group by classid;
+---------+--------+
| classid | 数量 |
+---------+--------+
| 1 | 4 |
| 2 | 3 |
| 3 | 4 |
| 4 | 4 |
| 5 | 1 |
| 6 | 4 |
| 7 | 3 |
+---------+--------+
查询classid大于3且学生平均年龄大于30的班级及其平均年龄
mysql> select classid,avg(age) 平均年龄 from students where classid > 3 group by classid having 平均年龄 > 30;
+---------+--------------+
| classid | 平均年龄 |
+---------+--------------+
| 5 | 46.0000 |
+---------+--------------+
统计每个班级（classid）中男生和女生的数量
mysql> select classid,gender,count(*) 数量 from students group by classid,gender;
+---------+--------+--------+
| classid | gender | 数量 |
+---------+--------+--------+
| NULL | M | 2 |
| 1 | F | 2 |
| 1 | M | 2 |
| 2 | M | 3 |
| 3 | F | 3 |
| 3 | M | 1 |
| 4 | M | 4 |
| 5 | M | 1 |
| 6 | F | 3 |
| 6 | M | 1 |
| 7 | F | 2 |
| 7 | M | 1 |
+---------+--------+--------+多表查询
子查询
SQL语句调用另一个select语句查询结果。
用于比较表达式；
子查询仅能返回单个值
查询students表中年龄大于teachers表中教师平均年龄的学生
mysql> select Name,Age from students where Age>(select avg(Age) from teachers);
+-------------+-----+
| Name | Age |
+-------------+-----+
| Sun Dasheng | 100 |
+-------------+-----+用于IN中；
子查询应该单独查询并返回一个或多个值重新构成列表
查询students表中StuID存在于teachers表中的TID字段中的学生
mysql> select StuID,Name from students where StuID in (select TID from teachers);
+-------+-------------+
| StuID | Name |
+-------+-------------+
| 1 | Shi Zhongyu |
| 2 | Shi Potian |
| 3 | Xie Yanke |
| 4 | Ding Dian |
+-------+-------------+
4 rows in set (0.00 sec)用于EXISTS 和 Not EXISTS；
用在where之后，且后面紧跟(子查询语句)。只关注子查询有没有结果，根据内查询语句的结果集为空或非空，返回一个布尔值，将外查询表的每一行代入内查询作为检验，如果内查询返回结果为非空，则exists子句返回true，外查询的这一行数据便可作为外查询的结果行返回，否则不能作为结果返回。
查询与teachers表中的某个教师相关的学生的所有字段
mysql> select * from students s where exists(select * from teachers t where s.teacherid=t.tid);
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
+-------+-------------+-----+--------+---------+-----------+用于from子句；
平均年龄大于30的班级ID和对应的平均年龄
mysql> SELECT s.ClassID,s.aage FROM (SELECT ClassID,avg(Age) AS aage FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s WHERE s.aage>30;
+---------+---------+
| ClassID | aage |
+---------+---------+
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+---------+联合查询；union
纵向合并，并去除表中任何重复行。不去重可用union all
mysql> SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;
+---------------+-----+
| Name | Age |
+---------------+-----+
| Shi Zhongyu | 22 |
| Shi Potian | 22 |
| Xie Yanke | 53 |
| Ding Dian | 32 |
| Yu Yutong | 26 |
| Shi Qing | 46 |
| Xi Ren | 19 |
| Lin Daiyu | 17 |
| Ren Yingying | 20 |
| Yue Lingshan | 19 |
| Yuan Chengzhi | 23 |
| Wen Qingqing | 19 |
| Tian Boguang | 33 |
| Lu Wushuang | 17 |
| Duan Yu | 19 |
| Xu Zhu | 21 |
| Lin Chong | 25 |
| Hua Rong | 23 |
| Xue Baochai | 18 |
| Diao Chan | 19 |
| Huang Yueying | 22 |
| Xiao Qiao | 20 |
| Ma Chao | 23 |
| Xu Xian | 27 |
| Sun Dasheng | 100 |
| Song Jiang | 45 |
| Zhang Sanfeng | 94 |
| Miejue Shitai | 77 |
| Lin Chaoying | 93 |
+---------------+-----+
交叉连接；cross join
笛卡尔乘积
列数=A表列数 + B表列数；行数=A表行数 * B表行数
mysql> select * from students cross join teachers;
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 1 | Song Jiang | 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 2 | Zhang Sanfeng | 94 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 4 | Lin Chaoying | 93 | F |
| 2 | Shi Potian | 22 | M | 1 | 7 | 1 | Song Jiang | 45 | M |
| 2 | Shi Potian | 22 | M | 1 | 7 | 2 | Zhang Sanfeng | 94 | M |
...
| 25 | Sun Dasheng | 100 | M | NULL | NULL | 3 | Miejue Shitai | 77 | F |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | 4 | Lin Chaoying | 93 | F |
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
100 rows in set (0.00 sec)
内连接；inner join ... on ...
交集
mysql> select * from students inner join teachers on students.teacherid=teachers.tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
左连接（左外连接）；left join ... on ...
select 字段 from tableA A left join tableB B on A.key=B.key
以左表为主，根据条件查询右表数据，如果根据条件查询右表数据不存在使用null值填充
mysql> select teachers.tid,teachers.Name,students.Name from teachers left join students on teachers.tid=students.teacherid;
+-----+---------------+-------------+
| tid | Name | Name |
+-----+---------------+-------------+
| 3 | Miejue Shitai | Shi Zhongyu |
| 4 | Lin Chaoying | Ding Dian |
| 1 | Song Jiang | Yu Yutong |
| 2 | Zhang Sanfeng | NULL |
+-----+---------------+-------------+
select 字段 from tableA A left join tableB B on A.key=B.key where B.key is null
返回 tableA中所有那些在tableB中没有对应key的记录
右连接（右外连接）；right join ... on ...
select 字段 from tableA A right join tableB B on A.key=B.key
以右表为主，根据条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充
mysql> select teachers.tid,teachers.Name,students.Name from teachers right join students on teachers.tid=students.teacherid;
+------+---------------+---------------+
| tid | Name | Name |
+------+---------------+---------------+
| 1 | Song Jiang | Yu Yutong |
| 3 | Miejue Shitai | Shi Zhongyu |
| 4 | Lin Chaoying | Ding Dian |
| NULL | NULL | Shi Potian |
| NULL | NULL | Xie Yanke |
| NULL | NULL | Shi Qing |
| NULL | NULL | Xi Ren |
| NULL | NULL | Lin Daiyu |
| NULL | NULL | Ren Yingying |
| NULL | NULL | Yue Lingshan |
| NULL | NULL | Yuan Chengzhi |
| NULL | NULL | Wen Qingqing |
| NULL | NULL | Tian Boguang |
| NULL | NULL | Lu Wushuang |
| NULL | NULL | Duan Yu |
| NULL | NULL | Xu Zhu |
| NULL | NULL | Lin Chong |
| NULL | NULL | Hua Rong |
| NULL | NULL | Xue Baochai |
| NULL | NULL | Diao Chan |
| NULL | NULL | Huang Yueying |
| NULL | NULL | Xiao Qiao |
| NULL | NULL | Ma Chao |
| NULL | NULL | Xu Xian |
| NULL | NULL | Sun Dasheng |
+------+---------------+---------------+
select 字段 from tableA A right join tableB B on A.key=B.key where A.key is null
返回 tableB中所有那些在tableA中没有对应key的记录
完全外连接；full out join ... on ....
MySQL不支持完全外连接语法
完全外连接会返回左表和右表中的所有记录，无论是否匹配；如果某一边没有与另一边匹配的记录，结果中的那一列将为NULL。
自连接
left join 和 inner join 最常用。
通常用于查找表内相关联的数据。自连接通过在连接条件中使用表的别名来区分表的两个实例。
SELECT e1.employee_id AS 'Employee ID',
e1.employee_name AS 'Employee Name',
e2.employee_name AS 'Manager Name'
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;
在这个查询中，employees表被别名为e1和e2，以便在连接时能够区分表的两个实例。
连接条件e1.manager_id = e2.employee_id表示正在查找每个员工（在e1中）及其
对应的经理（在e2中）。由于使用了LEFT JOIN，所以即使某些员工没有经理
（即manager_id为NULL或不存在匹配的经理记录），它们仍会出现在结果集中，
对应的经理名称将为NULL。select语句执行顺序
FROM 表名 ---> JOIN 关联表 ---> ON 关联条件 ---> WHERE 筛选条件 ---> GROUP BY 分组字段
---> HAVING 分组后筛选条件 ---> SELECT 查询列表 ---> ORDER BY 排序字段 ---> LIMIT 输出限制
TCL(事务控制语言)
事务控制语言（Transaction Control Language）简称TCL。用于管理数据库中的事务。这些用于管理由 DML 语句所做的更改。它还允许将语句分组为逻辑事务。
TCL经常被用于快速原型开发、脚本编程、GUI和测试等方面。
TCL的核心指令为commit、rollback。
DCL(数据控制语言)
数据控制语言 （Data Control Language）简称DCL。是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。
DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。
DCL的核心指令为grant、revoke。
VIEW视图
虚拟表，只是保存了查询的定义，它并不存储数据。
每次查询视图时，都会重新执行定义视图的查询，数据量很大时，可能影响性能。
利用视图，可以隐藏表的真实结构，在程序中利用视图进行查询，可以避免表结构的变化，而修改程序，降低程序和数据库之间的耦合度。
创建视图
CREATE VIEW 视图名 AS 查询语句;
查看视图
SHOW CREATE VIEW 视图名; 查看视图定义
SHOW CREATE TABLE 视图名; 可以查看表和视图
删除视图
DROP VIEW 视图名1,视图名2...;
PROCEDURE存储过程
是一组为了完成特定功能的SQL语句集，可独立执行，存储过程保存在mysql.proc表中
创建存储过程
DELIMITER //
CREATE PROCEDURE 存储过程名()
BEGIN
SQL语句
END //
DELIMITER ;
查看存储过程列表
SHOW PROCEDURE STATUS;
查看存储过程定义
SHOW CREATE PROCEDURE 存储过程名();
调用存储过程
CALL 存储过程名();
删除存储过程
DROP PROCEDURE 存储过程名();
TRIGGER触发器
在特定的数据库表事件发生时自动执行，这些事件通常与INSERT、UPDATE或DELETE操作相关
创建触发器
CREATE TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body
说明：
trigger_name：触发器的名称
trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发
trigger_event:：{ INSERT |UPDATE | DELETE }，触发的具体事件
tbl_name：该触发器作用在表名
查看触发器
SHOW TRIGGERS;
在当前数据库对应目录下可看到新生成的相关文件：trigger_name.TRN，table_name.TRG
删除触发器
DROP TRIGGER trigger_name;
EVENT事件
在MySQL中，事件（Event）是一种定时任务，可以在指定的时间间隔内定期执行某些操作。通过创建事件，您可以安排在特定时间或按照特定时间间隔自动执行SQL语句、存储过程或函数。
MySQL的事件调度器：event_scheduler
此调度器不断监视一个事件是否要调用，要创建事件，必须打开调度器。
event事件存放在mysql.event表中
打开调度器后会自动启动一个event_scheduler线程
查看事件调度器状态
临时开启事件调度器
mysql> set global event_scheduler=1;
持久开启事件调度器
vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
event_scheduler=ON
然后重启 systemctl restart mariadb
扫一扫
2900
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
