SQL语言

关系数据库的常见组件

数据库：database 表的集合，物理上表现为一个目录
表：table，行：row 列：column
索引：index
视图：view，虚拟的表
存储过程：procedure
存储函数：function
触发器：trigger
事件调度器：event scheduler，任务计划
用户：user
权限：privilege

SQL语言规范

在数据库系统中，SQL 语句不区分大小写，建议用大写
SQL语句可单行或多行书写，默认以 " ; " 结尾
关键词不能跨多行或简写
用空格和TAB 缩进来提高语句的可读性
子句通常位于独立行，便于编辑，提高可读性

注释：

SQL标准：

#单行注释，注意有空格
-- 注释内容
#多行注释
/*注释内容
注释内容
注释内容*/

MySQL注释：

# 注释内容

数据库对象和命名

数据库的组件(对象)：

数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等

命名规则：

必须以字母开头，后续可以包括字母,数字和三个特殊字符（# _ $）
不要使用MySQL的保留字

SQL语句分类

DDL: Data Defination Language 数据定义语言
- CREATE，DROP，ALTER
DML: Data Manipulation Language 数据操纵语言
- INSERT，DELETE，UPDATE
- 软件开发：CRUD
DQL：Data Query Language 数据查询语言
- SELECT
DCL：Data Control Language 数据控制语言
- GRANT，REVOKE
TCL：Transaction Control Language 事务控制语言
- COMMIT，ROLLBACK，SAVEPOINT

SQL语句构成

关健字Keyword组成子句clause，多条clause组成语句

示例：

SELECT * 							#SELECT子句
FROM products 						#FROM子句
WHERE price>666 					#WHERE子句

说明：一组SQL语句由三个子句构成，SELECT,FROM和WHERE是关键字

获取 SQL 命令使用帮助：

官方帮助：

https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html

范例：查看SQL帮助

MariaDB [mysql]> help contents

MySQL [(none)]> help Data Types

MySQL [(none)]> help bit

查看SQL帮助

mysql> HELP KEYWORD

字符集和排序

早期MySQL版本默认为 latin1，从MySQL8.0开始默认字符集已经为 utf8mb4

查看支持所有字符集：

SHOW CHARACTER SET;
SHOW CHARSET;

查看当前默认字符集:

show variables like 'character%';

查看支持所有排序规则：

SHOW COLLATION;
#注意
utf8_general_ci不区分大小写
utf8_bin 区分大小写

查看当前使用的排序规则:

SHOW VARIABLES LIKE 'collation%';

设置服务器默认的字符集:

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

范例：字符集和相关文件

mysql> SHOW CHARACTER SET；

[root@centos8 ~]#ll /usr/share/mysql/charsets/

范例：MySQL 8.0 默认的字符集和排序规则

11:18:28(root@localhost) [(none)]> 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.00 sec)

11:20:16(root@localhost) [(none)]> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

管理数据库

创建数据库

CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'
CHARACTER SET 'character set name'
COLLATE 'collate name';

示例：

11:22:09(root@localhost) [(none)]> create database db1
    -> ;
Query OK, 1 row affected (0.02 sec)



11:30:05(root@localhost) [(none)]> show create database db1;\G
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                               |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

范例：指定字符集创建新数据库

11:30:32(root@localhost) [(none)]> create database IF NOT EXISTS db2 CHARACTER SET 'utf8';
Query OK, 1 row affected, 1 warning (0.02 sec)

范例: 手动创建数据库

mysql> create database zabbix character set utf8 collate utf8_bin;

范例: 以容器方式启动并创建数据库

[root@centos8 ~]#docker run --name mysql-server -t \
		-e MYSQL_DATABASE="zabbix" \
		-e MYSQL_USER="zabbix" \
		-e MYSQL_PASSWORD="zabbix_pwd" \
		-e MYSQL_ROOT_PASSWORD="root_pwd" \
		-d mysql:5.7 \
		--character-set-server=utf8 --collation-server=utf8_bin
[root@centos8 ~]#docker run -d -p 3306:3306 --name mysql \
		-e MYSQL_ROOT_PASSWORD=123456 \
		-e MYSQL_DATABASE=jumpserver
		-e MYSQL_USER=jumpserver
		-e MYSQL_PASSWORD=123456
		-v /data/mysql:/var/lib/mysql
		-v
/etc/mysql/mysql.conf.d/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf \
		-v /etc/mysql/conf.d/mysql.cnf:/etc/mysql/conf.d/mysql.cnf
mysql:5.7.30

修改数据库

ALTER DATABASE DB_NAME character set utf8;

范例：

MariaDB [(none)]> ALTER DATABASE db1 character set utf8 COLLATE utf8_bin;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> show create database db1;
+----------+--------------------------------------------------------------------
-----------+
| Database | Create Database
|
+----------+--------------------------------------------------------------------
-----------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE
utf8_bin */ |
+----------+--------------------------------------------------------------------
-----------+
1 row in set (0.000 sec)
[root@centos8 ~]#cat /var/lib/mysql/db1/db.opt
default-character-set=utf8
default-collation=utf8_general_ci

删除数据库

DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';

11:31:25(root@localhost) [(none)]> drop database db1;
Query OK, 0 rows affected (0.03 sec)

[root@mysql ~]# ls /var/lib/mysql/
'#ib_16384_0.dblwr'   binlog.000001   binlog.000006   ca.pem            ibdata1     performance_schema   sys
'#ib_16384_1.dblwr'   binlog.000002   binlog.000007   client-cert.pem   ibtmp1      private_key.pem      undo_001
'#innodb_redo'        binlog.000003   binlog.000008   client-key.pem    mysql       public_key.pem       undo_002
'#innodb_temp'        binlog.000004   binlog.index    debian-5.7.flag   mysql.ibd   server-cert.pem
 auto.cnf             binlog.000005   ca-key.pem      ib_buffer_pool    mysql.pid   server-key.pem

查看数据库列表

SHOW DATABASES；

范例：

12:44:52(root@localhost) [(none)]>  show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

数据类型

数据类型：

数据长什么样
数据需要多少空间来存放

数据类型：

系统内置数据类型
用户定义数据类型

MySQL支持多种内置数据类型

数值类型
日期/时间类型
字符串(字符)类型

数据类型参考链接

https://dev.mysql.com/doc/refman/8.0/en/data-types.html

选择正确的数据类型对于获得高性能至关重要，三大原则：

更小的通常更好，尽量使用可正确存储数据的最小数据类型

简单就好，简单数据类型的操作通常需要更少的CPU周期

尽量避免NULL，包含为NULL的列，对MySQL更难优化

整数型

tinyint(m) 1个字节范围(-128~127)

smallint(m) 2个字节范围(-32768~32767)

mediumint(m) 3个字节范围(-8388608~8388607)

int(m) 4个字节范围(-2147483648~2147483647)

bigint(m) 8个字节范围(+-9.22*10的18次方)

上述数据类型，如果加修饰符unsigned后，则最大值翻倍
如：tinyint unsigned的取值范围为(0~255)

int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的

BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假，非zero值视为真

浮点型(float和double)

浮点型为近似值,不精确,分为单精度和双精度

float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位, 注意: 小数点不占用总个数
double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位, 注意: 小数点不占用总个数

示例:

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位 float(总位数,小数占总位数多少)

定点数

在数据库中存放的是精确值,存为十进制

格式 decimal(m,d) 表示最多 m 位数字,其中 d 个小数,小数点不算在长度内

比如: DECIMAL(6,2) 总共能存6位数字，末尾2位是小数，字段最大值 9999.99 （小数点不算在长度内）

参数m<65 是总个数，d<30且 d<m 是小数位

MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。

例如: decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：其中，小数点前的9个数字用4个字节，小数点后的9个数字用4个字节，小数点本身占1个字节

浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal，例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal

字符串(char,varchar,text)

char(n) 固定长度，最多255个字符,注意不是字节
varchar(n) 可变长度，最多65535个字符
tinytext 可变长度，最多255个字符
text 可变长度，最多65535个字符
mediumtext 可变长度，最多2的24次方-1个字符
longtext 可变长度，最多2的32次方-1个字符
BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节
VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节
内建类型：ENUM枚举, SET集合

char和varchar的比较：
参考：https://dev.mysql.com/doc/refman/8.0/en/char.html

价值	`CHAR(4)`	需储存	`VARCHAR(4)`	需储存
`''`	`' '`	4字节	`''`	1 字节
`'ab'`	`'ab '`	4字节	`'ab'`	3 字节
`'abcd'`	`'abcd'`	4字节	`'abcd'`	5字节
`'abcdefgh'`	`'abcd'`	4字节	`'abcd'`	5字节

char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符串末尾不能有空格（可能被误删），varchar不限于此

char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节

char类型的字符串检索速度要比varchar类型的快

varchar 和 text：

varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n< n>255)，text是实际字符数+2个字节。

text类型不能有默认值

varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text

二进制数据BLOB

BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob以二进制方式存储，不分大小写

BLOB存储的数据只能整体读出

TEXT可以指定字符集，BLOB不用指定字符集

日期时间类型

date 日期 '2008-12-2'
time 时间 '12:25:36'
datetime 日期时间 '2008-12-2 22:06:44'
timestamp 自动存储记录修改时间
YEAR(2), YEAR(4)：年份
timestamp 此字段里的时间数据会随其他字段修改的时候自动刷新这个数据类型的字段可以存放这条记录最后被修改的时间

修饰符

适用所有类型的修饰符：

NULL 数据列可包含NULL值，默认值
NOT NULL 数据列不允许包含NULL值，相当于网站注册表中的 * 为必填选项
DEFAULT 默认值
PRIMARY KEY 主键，所有记录中此字段的值不能重复，且不能为NULL
UNIQUE KEY 唯一键，所有记录中此字段的值不能重复，但可以为NULL
CHARACTER SET name 指定一个字符集

适用数值型的修饰符：

AUTO_INCREMENT 自动递增，适用于整数类型, 必须作用于某个 key 的字段,比如primary key
UNSIGNED 无符号

范例：关于AUTO_INCREMENT

13:53:11(root@localhost) [(none)]> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

# auto_increment_offset 定义初始值
# auto_increment_increment 定义步进

范例：

13:53:17(root@localhost) [(none)]> create database test;
Query OK, 1 row affected (0.02 sec)

13:54:11(root@localhost) [(none)]> create table test.t1(id int unsigned auto_increment primary key);
Query OK, 0 rows affected (0.07 sec)

13:54:30(root@localhost) [(none)]> show table status from test like "t1" \G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2026-02-26 13:54:30
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.02 sec)

13:55:55(root@localhost) [(none)]> insert into test.t1 values(null);
Query OK, 1 row affected (0.02 sec)

13:56:03(root@localhost) [(none)]> insert into test.t1 values(null);
Query OK, 1 row affected (0.02 sec)

13:56:17(root@localhost) [(none)]> select * from test.t1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)


#上面表的数据类型无法存放所有数据,修改过数据类型实现
13:59:34(root@localhost) [(none)]> alter table test.t1 modify id bigint auto_increment ;


13:59:34(root@localhost) [(none)]> desc test.t1
    -> ;
+-------+--------+------+-----+---------+----------------+
| Field | Type   | Null | Key | Default | Extra          |
+-------+--------+------+-----+---------+----------------+
| id    | bigint | NO   | PRI | NULL    | auto_increment |
+-------+--------+------+-----+---------+----------------+
1 row in set (0.00 sec)

14:00:37(root@localhost) [(none)]> insert test.t1 values(null);
Query OK, 1 row affected (0.02 sec)

14:01:01(root@localhost) [(none)]> select * from test.t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

DDL语句

表：二维关系

设计表：遵循规范

定义：字段，索引

字段：字段名，字段数据类型，修饰符
约束，索引：应该创建在经常用作查询条件的字段上

创建表

参考文档:

https://dev.mysql.com/doc/refman/8.0/en/create-table.html

创建表：

CREATE TABLE

获取帮助：

HELP CREATE TABLE

外键管理参考文档

https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

创建表的方法

(1) 直接创建

CREATE TABLE [IF NOT EXISTS] 'tbl_name' (col1 type1 修饰符, col2 type2 修饰符,...)
#字段信息
col type1
PRIMARY KEY(col1,...)
INDEX(col1, ...)
UNIQUE KEY(col1, ...）
#表选项：
ENGINE [=] engine_name
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

注意：

Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎
同一库中不同表可以使用不同的存储引擎
同一个库中表建议要使用同一种存储引擎类型

范例：创建表

CREATE TABLE student (
id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age tinyint UNSIGNED,
#height DECIMAL(5,2),
gender ENUM('M','F') default 'M'
)ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
#id字段以10初始值

DESC student;
14:58:18(root@localhost) [test]> DESC student;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int unsigned     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)      | NO   |     | NULL    |                |
| age    | tinyint unsigned | YES  |     | NULL    |                |
| gender | enum('M','F')    | YES  |     | M       |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
insert student (name,age)values('xiaoming',20);
Query OK, 1 row affected (0.002 sec)

select * from student;
+----+----------+------+--------+
| id | name     | age  | gender |
+----+----------+------+--------+
| 10 | xiaoming |   20 | M      |
+----+----------+------+--------+
1 row in set (0.00 sec)

15:02:22(root@localhost) [test]> insert student (name,age,gender)values('xiaohong',18,'f');
Query OK, 1 row affected (0.02 sec)

15:08:35(root@localhost) [test]> select * from student;
+----+----------+------+--------+
| id | name     | age  | gender |
+----+----------+------+--------+
| 10 | xiaoming |   20 | M      |
| 11 | xiaohong |   18 | F      |
+----+----------+------+--------+
2 rows in set (0.00 sec)


CREATE TABLE employee (
id int UNSIGNED NOT NULL ,
name VARCHAR(20) NOT NULL,
age tinyint UNSIGNED,
PRIMARY KEY(id,name))

范例：auto_increment 属性

SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.001 sec)

SET @@auto_increment_increment=10;
Query OK, 0 rows affected (0.001 sec)

SET @@auto_increment_offset=3;
Query OK, 0 rows affected (0.000 sec)

SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 3 |
+--------------------------+-------+
2 rows in set (0.001 sec)

CREATE TABLE autoinc1 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.004 sec)

INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.001 sec)
Records: 4 Duplicates: 0 Warnings: 0

SELECT col FROM autoinc1;
+-----+
| col |
+-----+
| 3 |
| 13 |
| 23 |
| 33 |
+-----+

范例：时间类型

create table testdate (id int auto_increment primary key,date
timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL);
insert testdate ()values()()();
select * from testdate;
+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2020-09-23 08:41:43 |
| 2 | 2020-09-23 08:41:43 |
| 3 | 2020-09-23 08:41:43 |
+----+---------------------+
3 row in set (0.000 sec)

（2）通过查询现存表创建；新表会被直接插入查询而来的数据

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options]
[partition_options] select_statement

范例：

create table user select user,host,password from mysql.user;
Query OK, 4 rows affected (0.008 sec)
Records: 4 Duplicates: 0 Warnings: 0

show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| student |
| user |
+---------------+
2 rows in set (0.000 sec)

desc user;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| user | char(80) | NO | | | |
| host | char(60) | NO | | | |
| password | char(41) | NO | | | |
+----------+----------+------+-----+---------+-------+

(3) 通过复制现存的表的表结构创建，但不复制数据

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }

范例：

esc 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 | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)

create table teacher like student;
Query OK, 0 rows affected (0.006 sec)

desc teacher;
+--------+---------------------+------+-----+---------+----------------+
| 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 | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)

范例: 创建外键表

create table school ( id int primary key auto_increment,name
varchar(10));


create table teacher(id int primary key auto_increment,name varchar(10),school_id int,foreign key(school_id) references school(id));

表查看

查看表：

SHOW TABLES [FROM db_name]

查看表创建命令：

SHOW CREATE TABLE tbl_name

查看表结构：

DESC [db_name.]tb_name
SHOW COLUMNS FROM [db_name.]tb_name

查看表状态：

SHOW TABLE STATUS LIKE 'tbl_name'

查看支持的engine类型

SHOW ENGINES;

查看库中所有表状态

SHOW TABLE STATUS FROM db_name

修改表删除表

修改表

ALTER TABLE 'tbl_name'
#字段：
#添加字段：add
ADD col1 data_type [FIRST|AFTER col_name]
#删除字段：drop
#修改字段：
alter（默认值）, change（字段名）, modify（字段属性）

查看修改表帮助

Help ALTER TABLE

删除表

DROP TABLE [IF EXISTS] 'tbl_name';

修改表范例

#修改表名
ALTER TABLE students RENAME s1;

#添加字段
ALTER TABLE s1 ADD phone varchar(11) AFTER name;

#修改字段类型
ALTER TABLE s1 MODIFY phone int;

#修改字段名称和类型
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);

#删除字段
ALTER TABLE s1 DROP COLUMN mobile;

#修改字符集
ALTER TABLE s1 character set utf8;

#修改数据类型和字符集
ALTER TABLE s1 change name name varchar(20) character set utf8;

#添加字段
ALTER TABLE students ADD gender ENUM('m','f');
alter table student modify is_del bool default false;

#修改字段名和类型
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;

#删除字段
ALTER TABLE students DROP age

#查看表结构
DESC students;

#新建表无主键，添加和删除主键
CREATE TABLE t1 SELECT * FROM students;
ALTER TABLE t1 add primary key (stuid);
ALTER TABLE t1 drop primary key ;

#添加外键
ALTER TABLE students add foreign key(TeacherID) references teachers(tid);

#删除外键
SHOW CREATE TABLE students #查看外键名
ALTER TABLE students drop foreign key <外键名>;

DML 语句

DML: INSERT, DELETE, UPDATE

INSERT 语句

功能：一次插入一行或多行数据
语法

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
        [ ON DUPLICATE KEY UPDATE #如果重复更新之
        col_name=expr
        [, col_name=expr] ... ]
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
		[INTO] tbl_name
		SET col_name={expr | DEFAULT}, ...
		[ ON DUPLICATE KEY UPDATE
		col_name=expr
		[, col_name=expr] ... ]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
		[INTO] tbl_name [(col_name,...)]
		SELECT ...
		[ ON DUPLICATE KEY UPDATE
		col_name=expr
		[, col_name=expr] ... ]

简化写法：

INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)

范例: 全值插入

mysql> insert student values(0,'wang',18,default);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+------+--------+
| id | name | age | gender |
+----+------+------+--------+
| 1 | wang | 18 | M |
+----+------+------+--------+
1 row in set (0.00 sec)

范例: 部分列插入

ysql> insert student(name,age)values('zhang',20);
Query OK, 1 row affected (0.01 sec)
mysql> insert student(id,name,age)values(default,'li',19);
Query OK, 1 row affected (0.00 sec)
mysql> insert student(id,name,gender)values(null,'zhao','F');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-------+------+--------+
| id | name | age | gender |
+----+-------+------+--------+
| 1 | wang | 18 | M |
| 2 | zhang | 20 | M |
| 3 | li | 19 | M |
| 4 | zhao | NULL | F |
+----+-------+------+--------+
4 rows in set (0.00 sec)

UPDATE 语句

语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

注意：一定要有限制条件，否则将修改所有行的指定字段

可利用mysql 选项避免此错误：

mysql -U | --safe-updates| --i-am-a-dummy

[root@centos8 ~]#vim /etc/my.cnf
[mysql]
safe-updates

DELETE 语句

删除表中数据，但不会自动缩减数据文件的大小。

语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
#可先排序再指定删除的行数

DELETE FROM student WHERE id = 10;
DELETE FROM student WHERE age > 20;

注意：一定要有限制条件，否则将清空表中的所有数据

如果想清空表，保留表结构，也可以使用下面语句，此语句会自动缩减数据文件的大小。

TRUNCATE TABLE tbl_name;

缩减表大小

OPTIMIZE TABLE tb_name

范例: 删除数据可以使用逻辑删除，添加一个标识字段实现,删除数据即修改标识字段

mysql> alter table student add is_del bool default false;
#mysql> alter table student add is_del tinyint(1) default 0;
mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
| is_del | tinyint(1) | YES | | 0 | |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

DQL 语句

单表操作

语法：

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]

说明：

字段显示可以使用别名：
col1 AS alias1, col2 AS alias2, ...
WHERE子句：指明过滤条件以实现"选择"的功能：
过滤条件：布尔型表达式
算术操作符：+, -, *, /, %
比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=
范例查询: BETWEEN min_num AND max_num
不连续的查询: IN (element1, element2, ...)
空查询: IS NULL, IS NOT NULL
DISTINCT 去除重复行，范例：SELECT DISTINCT gender FROM students;
模糊查询: LIKE 使用 % 表示任意长度的任意字符 _ 表示任意单个字符
RLIKE：正则表达式，索引失效，不建议使用
REGEXP：匹配字符串可用正则表达式书写模式，同上
逻辑操作符：NOT，AND，OR，XOR
GROUP BY：根据指定的条件把查询结果进行"分组"以用于做"聚合"运算
常见聚合函数： count(), sum(), max(), min(), avg(),注意:聚合函数不对null统计
HAVING: 对分组聚合运算后的结果指定过滤条件
一旦分组 group by ，select语句后只跟分组的字段，聚合函数
ORDER BY: 根据指定的字段对查询结果进行排序
升序：ASC
降序：DESC
LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制,跳过offset,显示row_count行,offset默为值为0
对查询结果中的数据请求施加"锁"
FOR UPDATE: 写锁，独占或排它锁，只有一个读和写操作
LOCK IN SHARE MODE: 读锁，共享锁，同时多个读操作

范例:

MariaDB [hellodb]> select password("hello world") ;
+-------------------------------------------+
| password("hello world") |
+-------------------------------------------+
| *67BECF85308ACF0261750DA1075681EE5C412F05 |
+-------------------------------------------+
1 row in set (0.000 sec)

MariaDB [hellodb]> select md5("hello world") ;
+----------------------------------+
| md5("hello world") |
+----------------------------------+
| 5eb63bbbe01eeed093cb22bb8f5acdc3 |
+----------------------------------+
1 row in set (0.000 sec)

范例：字段别名

MariaDB [hellodb]> select stuid 学员ID,name as 姓名,gender 性别 from students;

范例：简单查询

DESC students;
INSERT INTO students VALUES(1,'tom'，'m'),(2,'alice','f');
INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen');
SELECT * FROM students WHERE id < 3;
SELECT * FROM students WHERE gender='m';
SELECT * FROM students WHERE gender IS NULL;
SELECT * FROM students WHERE gender IS NOT NULL;
SELECT * FROM students ORDER BY name DESC LIMIT 2;
SELECT * FROM students ORDER BY name DESC LIMIT 1,2;
SELECT * FROM students WHERE id >=2 and id <=4
SELECT * FROM students WHERE BETWEEN 2 AND 4
SELECT * FROM students WHERE name LIKE 't%'
SELECT * FROM students WHERE name RLIKE '.*[lo].*';
SELECT id stuid,name as stuname FROM students
select * from students where classid in (1,3,5);
select * from students where classid not in (1,3,5);

范例：判断是否为NULL

select * from students where classid is null;

select * from students where classid <=> null

select * from students where classid is not null;

#ifnull函数判断指定的字段是否为空值,如果空值则使用指定默认值
select stuid,name,ifnull(classID,'无班级') from students where classid is null;

范例：记录去重

select distinct gender from students ;

#将age和gender多个字段重复的记录去重
select distinct age,gender from students;

范例：SQL 注入攻击

create table user (id int auto_increment primary key,name varchar(20),password
varchar(30));
insert into user (name,password) values('admin','123456'),('wu','654321');
select * from user where name='admin' and password='' or '1'='1';
select * from user where name='admin' and password='' or '1=1';
select * from user where name='admin'; -- ' and password='ayaka123';
select * from user where name='admin'; # ' and password='ayaka123';

范例: 分页查询

#只取前3个
mysql> select * from students limit 0,3;
mysql> select * from students limit 3;

# 查询第n页的数据,每页显示m条记录
mysql> select * from students limit (n-1) * m,m;

范例: 聚合函数

mysql> select sum(age)/count(*) from students where gender ='M';
+-------------------+
| sum(age)/count(*) |
+-------------------+
| 33.0000 |
+-------------------+
1 row in set (0.00 sec)

mysql> select sum(age)/count(*) from students where gender ='F';
+-------------------+
| sum(age)/count(*) |
+-------------------+
| 19.0000 |
+-------------------+
1 row in set (0.00 sec)

范例：排序

select classid,sum(age) from students where classid is not null group by
classid order by classid;
select classid,sum(age) from students group by classid having classid is not
null order by classid;
select classid,sum(age) from students where classid is not null group by
classid order by classid limit 2,3;
#必须先过滤，再排序
select * from students where classid is not null order by gender desc, age asc ;
#多列排序
select * from students order by gender desc, age asc;

范例：正序排序时将NULL记录排在最后

#对classid 正序排序，NULL记录排在最后
select * from students order by -classid desc ;

范例: 分组和排序的次序

#顺序: group by,having,order by
mysql> select classid,count(*) from students group by classid having classid is
not null order by classid;
+---------+----------+
| classid | count(*) |
+---------+----------+
| 1 | 4 |
| 2 | 3 |
| 3 | 4 |
| 4 | 4 |
| 5 | 1 |
| 6 | 4 |
| 7 | 3 |
+---------+----------+
7 rows in set (0.00 sec)

#以下顺序会出错,group by,order by,having
mysql> select classid,count(*) from students group by classid order by classidhaving classid is not null;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near'having classid is not null' at line 1

#以下顺序会出错,order by,group by,having
mysql> select classid,count(*) from students order by classid group by classid having classid is not null;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by classid having classid is not null' at line 1

范例：时间字段进行过滤查询,并且timestamp可以随其它字段的更新自动更新

select * from testdate where date between '2020-06-03 15:21:12' and '2020-06-03 18:27:40';
select * from testdate where date >= '2020-06-03 15:21:12' and date <= '2020-06-03 18:27:40';

#修改其它字段,会自动更新timestamp字段
mysql> update testdate set id=10 where id=1;
mysql> select * from testdate3;
+----+---------------------+
| id | date |
+----+---------------------+
| 2 | 2020-06-03 15:21:12 |
| 3 | 2020-06-03 15:21:14 |
| 4 | 2020-06-03 15:21:17 |
| 5 | 2020-06-03 18:27:39 |
| 6 | 2020-06-03 18:27:44 |
| 10 | 2026-02-26 18:34:51 |
+----+---------------------+
6 rows in set (0.001 sec)

多表查询

多表查询，即查询结果来自于多张表

子查询：在SQL语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询
联合查询：UNION
交叉连接：笛卡尔乘积 CROSS JOIN
内连接：
- 等值连接：让表之间的字段以"等值"建立连接关系
- 不等值连接
- 自然连接：去掉重复列的等值连接 , 语法: FROM table1 NATURAL JOIN table2;
外连接：
- 左外连接：FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
- 右外连接：FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
- 完全外连接: FROM tb1 FULL OUTER JOIN tb2 ON tb1.col=tb2.col 注意:MySQL 不支持此SQL语法
自连接：本表和本表进行连接查询

子查询

子查询 subquery 即SQL语句调用另一个SELECT子句,可以是对同一张表,也可以是对不同表,主要有以下四种常见的用法

用于比较表达式中的子查询；子查询仅能返回单个值

SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM teachers);
update students set Age=(SELECT avg(Age) FROM teachers) where stuid=25;

用于IN中的子查询：子查询应该单独查询并返回一个或多个值重新构成列表

SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);

用于EXISTS 和 Not EXISTS

参考链接：https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html EXISTS (包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS 内部有一个子查询语句(SELECT... FROM...)，将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果为非空值，则EXISTS子句返回TRUE，外查询的这一行数据便可作为外查询的结果行返回，否则不能作为结果

MariaDB [hellodb]> 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 |
+-------+-------------+-----+--------+---------+-----------+

#说明：
1、EXISTS (或 NOT EXISTS) 用在 where之后，且后面紧跟子查询语句（带括号）
2、EXISTS (或 NOT EXISTS) 只关心子查询有没有结果,并不关心子查询的结果具体是什么
3、上述语句把students的记录逐条代入到Exists后面的子查询中，如果子查询结果集不为空，即说
明存在，那么这条students的记录出现在最终结果集，否则被排除

MariaDB [hellodb]> select * from students s where NOT EXISTS (select * from
teachers t where s.teacherid=t.tid);
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
22 rows in set (0.001 sec)

用于FROM子句中的子查询

使用格式：

SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;

范例：

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;

范例：子查询

#子查询：select 的执行结果，被其它SQL调用
MariaDB [hellodb]> select stuid,name,age from students where age > (select
avg(age) from students);
+-------+--------------+-----+
| stuid | name | age |
+-------+--------------+-----+
| 3 | Xie Yanke | 53 |
| 4 | Ding Dian | 32 |
| 6 | Shi Qing | 46 |
| 13 | Tian Boguang | 33 |
| 25 | Sun Dasheng | 100 |
+-------+--------------+-----+
5 rows in set (0.00 sec)

范例：子查询用于更新表

MariaDB [hellodb]> update teachers set age=(select avg(age) from students) where tid=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> select * from teachers;

+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 27 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

子查询优化

子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。子查询虽然可以使查询语句很灵活，但执行效率不高。执行子查询时，需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。
可以使用连接(JOIN)查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用到索引的话，性能会更好。

联合查询

联合查询 Union 实现的条件,多个表的字段数量相同,字段名和数据类型可以不同,但一般数据类型是相同的

SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;

范例：联合查询

#多表纵向合并union
MariaDB [hellodb]> select * from teachers union select * from students;
MariaDB [hellodb]> select tid as id,name,age,gender from teachers union select stuid,name,age,gender from students;
+----+---------------+-----+--------+
| id | name | age | gender |
+----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 26 | F |
| 1 | Shi Zhongyu | 22 | M |
| 2 | Shi Potian | 22 | M |
| 3 | Xie Yanke | 53 | M |
| 4 | Ding Dian | 32 | M |
| 5 | Yu Yutong | 26 | M |
| 6 | Shi Qing | 46 | M |
| 7 | Xi Ren | 19 | F |
| 8 | Lin Daiyu | 17 | F |
| 9 | Ren Yingying | 20 | F |
| 10 | Yue Lingshan | 19 | F |
| 11 | Yuan Chengzhi | 23 | M |
| 12 | Wen Qingqing | 19 | F |
| 13 | Tian Boguang | 33 | M |
| 14 | Lu Wushuang | 17 | F |
| 15 | Duan Yu | 19 | M |
| 16 | Xu Zhu | 21 | M |
| 17 | Lin Chong | 25 | M |
| 18 | Hua Rong | 23 | M |
| 19 | Xue Baochai | 18 | F |
| 20 | Diao Chan | 19 | F |
| 21 | Huang Yueying | 22 | F |
| 22 | Xiao Qiao | 20 | F |
| 23 | Ma Chao | 23 | M |
| 24 | Xu Xian | 27 | M |
| 25 | Sun Dasheng | 100 | M |
| 26 | xietingfeng | 23 | M |
| 27 | liudehua | 18 | F |
| 28 | mahuateng | 20 | M |
| 29 | wuyanzu | 19 | M |
| 30 | wuxin | 21 | M |
| 31 | Song Jiang | 45 | M |
| 32 | Zhang Sanfeng | 18 | M |
| 33 | Miejue Shitai | 18 | F |
| 34 | Lin Chaoying | 18 | F |
| 35 | 巴西可 | 20 | M |
| 36 | abc | 20 | M |
+----+---------------+-----+--------+
40 rows in set (0.001 sec)

MariaDB [hellodb]> select * from teachers union select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

#UNION ALL不去重 UNION去重
MariaDB [hellodb]> select * from teachers union all select *from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |

MariaDB [hellodb]> select * from user union select * from user;
+------+----------+----------+
| id | username | password |
+------+----------+----------+
| 1 | admin | aki |
| 2 | ayaka| aki |
| 3 | aika | centos |

#DISTINCT合并相同的内容	DISTINCT 必须放在 SELECT 关键字后第一位
MariaDB [hellodb]> select distinct * from user ;
+------+----------+----------+
| id | username | password |
+------+----------+----------+
| 1 | admin | aki |
| 2 | ayaka| aki |
| 3 | aika | centos |

交叉连接

cross join 即多表的记录之间做笛卡尔乘积组合，并且多个表的列横向合并相加, "雨露均沾"
比如: 第一个表3行4列,第二个表5行6列,cross join后的结果为3*5=15行,4+6=10列
交叉连接生成的记录可能会非常多,建议慎用
范例：交叉连接

#横向合并，交叉连接（横向笛卡尔）
MariaDB [hellodb]> select * from students cross join teachers;
MariaDB [hellodb]> select * from teachers , students;
+-----+---------------+-----+--------+-------+---------------+-----+--------+---
------+-----------+
| TID | Name | Age | Gender | StuID | Name | Age | Gender |
ClassID | TeacherID |
+-----+---------------+-----+--------+-------+---------------+-----+--------+---
------+-----------+
| 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 | 26 | F | 1 | Shi Zhongyu | 22 | M |
2 | 3 |
| 1 | Song Jiang | 45 | M | 2 | Shi Potian | 22 | M |
1 | 7 |
| 2 | Zhang Sanfeng | 94 | M | 2 | Shi Potian | 22 | M |
1 | 7 |
| 3 | Miejue Shitai | 77 | F | 2 | Shi Potian | 22 | M |
1 | 7 |
| 4 | Lin Chaoying | 26 | F | 2 | Shi Potian | 22 | M |
1 | 7 |
| 1 | Song Jiang | 45 | M | 3 | Xie Yanke | 53 | M |
2 | 16 |
| 2 | Zhang Sanfeng | 94 | M | 3 | Xie Yanke | 53 | M |
2 | 16 |
| 3 | Miejue Shitai | 77 | F | 3 | Xie Yanke | 53 | M |
2 | 16 |
| 4 | Lin Chaoying | 26 | F | 3 | Xie Yanke | 53 | M |
......
#建议别用 我复制都复制到恶心了

MariaDB [hellodb]> select stuid,students.name
student_name,students.age,tid,teachers.name teacher_name,teachers.age from
teachers cross join students ;
+-------+---------------+-----+-----+---------------+-----+
| stuid | student_name | age | tid | teacher_name | age |
+-------+---------------+-----+-----+---------------+-----+
| 1 | Shi Zhongyu | 22 | 1 | Song Jiang | 45 |
| 1 | Shi Zhongyu | 22 | 2 | Zhang Sanfeng | 94 |
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 77 |
| 1 | Shi Zhongyu | 22 | 4 | Lin Chaoying | 26 |
| 2 | Shi Potian | 22 | 1 | Song Jiang | 45 |
| 2 | Shi Potian | 22 | 2 | Zhang Sanfeng | 94 |
| 2 | Shi Potian | 22 | 3 | Miejue Shitai | 77 |
| 2 | Shi Potian | 22 | 4 | Lin Chaoying | 26 |
| 3 | Xie Yanke | 53 | 1 | Song Jiang | 45 |
| 3 | Xie Yanke | 53 | 2 | Zhang Sanfeng | 94 |
| 3 | Xie Yanke | 53 | 3 | Miejue Shitai | 77 |
| 3 | Xie Yanke | 53 | 4 | Lin Chaoying | 26 |
| 4 | Ding Dian | 32 | 1 | Song Jiang | 45 |
| 4 | Ding Dian | 32 | 2 | Zhang Sanfeng | 94 |
| 4 | Ding Dian | 32 | 3 | Miejue Shitai | 77 |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying | 26 |
| 5 | Yu Yutong | 26 | 1 | Song Jiang | 45 |
| 5 | Yu Yutong | 26 | 2 | Zhang Sanfeng | 94 |
| 5 | Yu Yutong | 26 | 3 | Miejue Shitai | 77 |
| 5 | Yu Yutong | 26 | 4 | Lin Chaoying | 26 |
| 6 | Shi Qing | 46 | 1 | Song Jiang | 45 |
| 6 | Shi Qing | 46 | 2 | Zhang Sanfeng | 94 |
| 6 | Shi Qing | 46 | 3 | Miejue Shitai | 77 |
| 6 | Shi Qing | 46 | 4 | Lin Chaoying | 26 |
| 7 | Xi Ren | 19 | 1 | Song Jiang | 45 |
| 7 | Xi Ren | 19 | 2 | Zhang Sanfeng | 94 |
| 7 | Xi Ren | 19 | 3 | Miejue Shitai | 77 |
| 7 | Xi Ren | 19 | 4 | Lin Chaoying | 26
......

MariaDB [hellodb]> select stuid,s.name student_name,s.age
student_age,tid,t.name teacher_name,t.age teacher_age from teachers t cross
join students s ;

+-------+---------------+-------------+-----+---------------+-------------+
| stuid | student_name | student_age | tid | teacher_name | teacher_age |
+-------+---------------+-------------+-----+---------------+-------------+
| 1 | Shi Zhongyu | 22 | 1 | Song Jiang | 45 |
| 1 | Shi Zhongyu | 22 | 2 | Zhang Sanfeng | 94 |
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 77 |
| 1 | Shi Zhongyu | 22 | 4 | Lin Chaoying | 26 |
| 2 | Shi Potian | 22 | 1 | Song Jiang | 45 |
| 2 | Shi Potian | 22 | 2 | Zhang Sanfeng | 94 |
| 2 | Shi Potian | 22 | 3 | Miejue Shitai | 77 |
| 2 | Shi Potian | 22 | 4 | Lin Chaoying | 26 |
| 3 | Xie Yanke | 53 | 1 | Song Jiang | 45 |
| 3 | Xie Yanke | 53 | 2 | Zhang Sanfeng | 94 |
| 3 | Xie Yanke | 53 | 3 | Miejue Shitai | 77 |
| 3 | Xie Yanke | 53 | 4 | Lin Chaoying | 26 |
| 4 | Ding Dian | 32 | 1 | Song Jiang | 45 |
| 4 | Ding Dian | 32 | 2 | Zhang Sanfeng | 94 |
| 4 | Ding Dian | 32 | 3 | Miejue Shitai | 77 |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying | 26 |
| 5 | Yu Yutong | 26 | 1 | Song Jiang | 45 |
| 5 | Yu Yutong | 26 | 2 | Zhang Sanfeng | 94 |
......

内连接

inner join 内连接取多个表的交集

范例：内连接

#内连接inner join
MariaDB [hellodb]> select * from students inner join teachers on
students.teacherid=teachers.tid;

+-------+-------------+-----+--------+---------+-----------+-----+--------------
-+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name
| Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+--------------
-+-----+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang
| 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai
| 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying
| 93 | F |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+

#如果表定义了别名，原表名将无法使用
MariaDB [hellodb]> select stuid,s.name as student_name ,tid,t.name as
teacher_name from students as s inner join teachers as t on s.teacherid=t.tid;
+-------+--------------+-----+---------------+
| stuid | student_name | tid | teacher_name |
+-------+--------------+-----+---------------+
| 5 | Yu Yutong | 1 | Song Jiang |
| 1 | Shi Zhongyu | 3 | Miejue Shitai |
| 4 | Ding Dian | 4 | Lin Chaoying |
+-------+--------------+-----+---------------+
3 rows in set (0.00 sec)

MariaDB [hellodb]> select s.name 学生姓名,s.age 学生年龄,s.gender 学生性别,t.name 老师姓名,t.age 老师年龄,t.gender 老师性别 from students s inner join teachers t on s.gender <> t.gender;
+---------------+--------------+--------------+---------------+--------------+--------------+
| 学生姓名 | 学生年龄 | 学生性别 | 老师姓名 | 老师年龄 | 老师性别
|
+---------------+--------------+--------------+---------------+--------------+--
------------+
| Shi Zhongyu | 45 | M | Miejue Shitai | 77 | F
|
| Shi Zhongyu | 45 | M | Lin Chaoying | 29 | F
|
| Shi Potian | 22 | M | Miejue Shitai | 77 | F
|
| Shi Potian | 22 | M | Lin Chaoying | 29 | F
|
| Xie Yanke | 77 | M | Miejue Shitai | 77 | F
|
| Xie Yanke | 77 | M | Lin Chaoying | 29 | F
|
| Ding Dian | 32 | M | Miejue Shitai | 77 | F
|
| Ding Dian | 32 | M | Lin Chaoying | 29 | F
|
| Yu Yutong | 26 | M | Miejue Shitai | 77 | F
|
| Yu Yutong | 26 | M | Lin Chaoying | 29 | F
|
| Shi Qing | 46 | M | Miejue Shitai | 77 | F
|
| Shi Qing | 46 | M | Lin Chaoying | 29 | F
|
| Xi Ren | 19 | F | Song Jiang | 45 | M
|
| Xi Ren | 19 | F | Zhang Sanfeng | 94 | M
|
| Lin Daiyu | 17 | F | Song Jiang | 45 | M
|
| Lin Daiyu | 17 | F | Zhang Sanfeng | 94 | M
|

MariaDB [hellodb]> select stuid,s.name,tid,t.name from students s,teachers t where s.teacherid=t.tid;
+-------+-------------+-----+---------------+
| stuid | name | tid | name |
+-------+-------------+-----+---------------+
| 5 | Yu Yutong | 1 | Song Jiang |
| 1 | Shi Zhongyu | 3 | Miejue Shitai |
| 4 | Ding Dian | 4 | Lin Chaoying |
+-------+-------------+-----+---------------+
3 rows in set (0.00 sec)

内连接后过滤数据
MariaDB [hellodb]> select * from students s inner join teachers t on
s.teacherid=t.tid and s.age > 30 ;
+-------+-------------+-----+--------+---------+-----------+-----+--------------
+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name
| Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+--------------
+-----+--------+
| 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang
| 45 | M |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying
| 26 | F |
+-------+-------------+-----+--------+---------+-----------+-----+--------------
+-----+--------+
2 rows in set (0.002 sec)

自然连接

当源表和目标表共享相同名称的列时，就可以在它们之间执行自然连接，而无需指定连接列。
在使用纯自然连接时，如没有相同的列时，会产生交叉连接（笛卡尔乘积）

语法：

SELECT table1.column, table2.column FROM table1 NATURAL JOIN table2;

范例:

MariaDB [db1]> create table t1 ( id int,name char(20));
MariaDB [db1]> create table t2 ( id int,title char(20));
MariaDB [db1]> insert t1 values(1,'mage'),(2,'wang'),(3,'zhang');
MariaDB [db1]> insert t2 values(1,'ceo'),(2,'cto');

MariaDB [db1]> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | mage |
| 2 | wang |
| 3 | zhang |
+------+-------+
3 rows in set (0.00 sec)

MariaDB [db1]> select * from t2;
+------+-------+
| id | title |
+------+-------+
| 1 | ceo |
| 2 | cto |
+------+-------+
2 rows in set (0.00 sec)

MariaDB [db1]> select * from t1 NATURAL JOIN t2;
+------+------+-------+
| id | name | title |
+------+------+-------+
| 1 | mage | ceo |
| 2 | wang | cto |
+------+------+-------+
2 rows in set (0.00 sec)

MariaDB [db1]> select t1.name,t2.title from t1 NATURAL JOIN t2;
+------+-------+
| name | title |
+------+-------+
| mage | ceo |
| wang | cto |
+------+-------+
2 rows in set (0.00 sec)

MariaDB [db1]> select t1.name,t2.title from t1 NATURAL JOIN t2;
+------+-------+
| name | title |
+------+-------+
| mage | ceo |
| wang | cto |
+------+-------+
2 rows in set (0.00 sec)

左和右外连接

左连接: 以左表为主根据条件查询右表数据﹐如果根据条件查询右表数据不存在使用null值填充

右连接: 以右表为主根据条件查询左表数据﹐如果根据条件查询左表数据不存在使用null值填充

范例：左，右外连接

#左外连接
MariaDB [hellodb]> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age
from students as s left outer join teachers as t on s.teacherid=t.tid;

+-------+---------------+-----+-----------+------+---------------+------+
| stuid | name | age | teacherid | tid | name | age |
+-------+---------------+-----+-----------+------+---------------+------+
| 1 | Shi Zhongyu | 22 | 3 | 3 | Miejue Shitai | 77 |
| 2 | Shi Potian | 22 | 7 | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | 16 | NULL | NULL | NULL |
| 4 | Ding Dian | 32 | 4 | 4 | Lin Chaoying | 93 |
| 5 | Yu Yutong | 26 | 1 | 1 | Song Jiang | 45 |
| 6 | Shi Qing | 46 | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | NULL | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | NULL | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | NULL | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | NULL | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | NULL | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | NULL | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | NULL | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | NULL | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | NULL | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | NULL | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | NULL | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | NULL | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | NULL | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | NULL | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | NULL | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | NULL | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | NULL | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | NULL | NULL | NULL | NULL |
+-------+---------------+-----+-----------+------+---------------+------+
25 rows in set (0.00 sec)

#左外连接扩展
MariaDB [hellodb]> select * from students s left outer join teachers t on
s.teacherid=t.tid where t.tid is null;

+-------+---------------+-----+--------+---------+-----------+------+------+----
--+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age
| Gender |
+-------+---------------+-----+--------+---------+-----------+------+------+----
--+--------+
| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL |
NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL |
NULL | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL |
NULL | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL |
NULL | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL |
NULL | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL |
NULL | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL |
NULL | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL |
NULL | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL |
NULL | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL |
NULL | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL |
NULL | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL |
NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL |
NULL | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL |
NULL | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL |
NULL | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL |
NULL | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL |
NULL | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL |
NULL | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL |
NULL | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL |
NULL | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL |
NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+------+------+----
--+--------+
21 rows in set (0.001 sec)


#多个条件的左外连接
#最终效果： * 由于连接条件永远无法匹配成功，teachers 表的所有字段在结果集中都将显示为 NULL。因为是 LEFT JOIN，students 表的所有行都会被列出来。但右侧关联的老师信息全部是空的。
MariaDB [hellodb]> select * from students s left outer join teachers t on
s.teacherid=t.tid and s.teacherid is null;

+-------+---------------+-----+--------+---------+-----------+------+------+----
--+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age
| Gender |
+-------+---------------+-----+--------+---------+-----------+------+------+----
--+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | NULL | NULL |
NULL | NULL |
| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL |
NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL |
NULL | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 | NULL | NULL |
NULL | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 | NULL | NULL |
NULL | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL |
NULL | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL |
NULL | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL |
NULL | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL |
NULL | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL |
NULL | NULL |
......



#先左外连接，再过滤
MariaDB [hellodb]> select * from students s left outer join teachers t on
s.teacherid=t.tid where s.teacherid is null;
+-------+---------------+-----+--------+---------+-----------+------+------+----
--+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age
| Gender |
+-------+---------------+-----+--------+---------+-----------+------+------+----
--+--------+
| 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL |
NULL | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL |
NULL | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL |
NULL | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL |
NULL | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL |
NULL | NULL |
........


#右外连接
MariaDB [hellodb]> select * from students s right outer join teachers t on
s.teacherid=t.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
| 26 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang
| 45 | M |
| 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang
| 45 | M |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang
Sanfeng | 94 | M |
+-------+-------------+------+--------+---------+-----------+-----+-------------
--+-----+--------+
5 rows in set (0.001 sec)



#右外连接的扩展用法
MariaDB [hellodb]> select * from students s right outer join teachers t on
s.teacherid=t.tid where s.teacherid is null;
+-------+------+------+--------+---------+-----------+-----+---------------+----
-+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age
| Gender |
+-------+------+------+--------+---------+-----------+-----+---------------+----
-+--------+
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94
| M |
+-------+------+------+--------+---------+-----------+-----+---------------+----
-+--------+
1 row in set (0.000 sec)

完全外连接

MySQL 不支持完全外连接full outer join语法

范例：完全外连接

#MySQL不支持完全外连接 full outer join,利用以下方式法代替
MariaDB [hellodb]> select * from students left join teachers on
students.teacherid=teachers.tid
-> union 	#（联合）
-> select * from students right join teachers on
students.teacherid=teachers.tid;

MariaDB [hellodb]> select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s left join teachers as t on s.teacherid=t.tid
-> union
-> select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s right
join teachers as t on s.teacherid=t.tid;
+-------+---------------+------+------+---------------+------+
| stuid | name | age | tid | name | age |
+-------+---------------+------+------+---------------+------+
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 77 |
| 2 | Shi Potian | 22 | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | NULL | NULL | NULL |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying | 93 |
| 5 | Yu Yutong | 26 | 1 | Song Jiang | 45 |
| 6 | Shi Qing | 46 | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | NULL | NULL | NULL |
| NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 |
+-------+---------------+------+------+---------------+------+
26 rows in set (0.01 sec)



#完全外连接的扩展示例
MariaDB [hellodb]> select * from students s left outer join teachers t on
s.teacherid=t.tid where t.tid is null union select * from students s right outer join teachers t on s.teacherid=t.tid where s.teacherid is null;
+-------+---------------+------+--------+---------+-----------+------+----------
-----+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name
| Age | Gender |
+-------+---------------+------+--------+---------+-----------+------+----------
-----+------+--------+
| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL
| NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL
| NULL | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL
| NULL | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL
| NULL | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL
| NULL | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL
| NULL | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL
| NULL | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL
| NULL | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL
| NULL | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL
| NULL | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL
| NULL | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL
| NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL
| NULL | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL
| NULL | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL
| NULL | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL
| NULL | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL
| NULL | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL
| NULL | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL
| NULL | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL
| NULL | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL
| NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | NULL | NULL
| NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang
Sanfeng | 94 | M |
+-------+---------------+------+--------+---------+-----------+------+----------
-----+------+--------+
23 rows in set (0.001 sec)


MariaDB [hellodb]> select * from (select s.stuid,s.name
s_name,s.teacherid,t.tid,t.name t_name from students s left outer join teachers t on s.teacherid=t.tid union    selects.stuid,s.name,s.teacherid,t.tid,t.name
from students s right outer join teachers t on s.teacherid=t.tid) as a where a.teacherid is null or a.tid is null;
+-------+---------------+-----------+------+---------------+
| stuid | s_name | teacherid | tid | t_name |
+-------+---------------+-----------+------+---------------+
| 2 | Shi Potian | 7 | NULL | NULL |
| 3 | Xie Yanke | 16 | NULL | NULL |
| 6 | Shi Qing | NULL | NULL | NULL |
| 7 | Xi Ren | NULL | NULL | NULL |
| 8 | Lin Daiyu | NULL | NULL | NULL |
| 9 | Ren Yingying | NULL | NULL | NULL |
| 10 | Yue Lingshan | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | NULL | NULL | NULL |
| 12 | Wen Qingqing | NULL | NULL | NULL |
| 13 | Tian Boguang | NULL | NULL | NULL |
| 14 | Lu Wushuang | NULL | NULL | NULL |
| 15 | Duan Yu | NULL | NULL | NULL |
| 16 | Xu Zhu | NULL | NULL | NULL |
| 17 | Lin Chong | NULL | NULL | NULL |
| 18 | Hua Rong | NULL | NULL | NULL |
| 19 | Xue Baochai | NULL | NULL | NULL |
| 20 | Diao Chan | NULL | NULL | NULL |
| 21 | Huang Yueying | NULL | NULL | NULL |
| 22 | Xiao Qiao | NULL | NULL | NULL |
| 23 | Ma Chao | NULL | NULL | NULL |
| 24 | Xu Xian | NULL | NULL | NULL |
| NULL | NULL | NULL | 2 | Zhang Sanfeng |
| NULL | NULL | NULL | 5 | abc |
+-------+---------------+-----------+------+---------------+
23 rows in set (0.00 sec)

自连接

自连接, 即表自身连接自身

范例：自连接

#自连接
MariaDB [hellodb]>create table emp (id int,name varchar(10),leaderid int);
MariaDB [hellodb]>insert emp values (1,'aya',null),(2,'zhangsir',1),
(3,'wang',2),(4,'zhang',3);

MariaDB [hellodb]> select * from emp;
+------+----------+----------+
| id | name | leaderid |
+------+----------+----------+
| 1 | aya | NULL |
| 2 | zhangsir | 1 |
| 3 | wang | 2 |
| 4 | zhang | 3 |
+------+----------+----------+
4 rows in set (0.00 sec)

MariaDB [hellodb]> select e.name,l.name from emp as e inner join emp as l on e.leaderid=l.id;
+----------+----------+
| name | name |
+----------+----------+
| zhangsir | aya |
| wang | zhangsir |
| zhang | wang |
+----------+----------+
3 rows in set (0.00 sec)

MariaDB [hellodb]> select e.name,IFNULL(l.name,'无上级') from emp as e left
join emp as l on e.leaderid=l.id;
+----------+----------+
| name | name |
+----------+----------+
| zhangsir | aya |
| wang | zhangsir |
| zhang | wang |
| aya | NULL |
+----------+----------+
4 rows in set (0.00 sec)

MariaDB [hellodb]> select e.name emp,IFNULL(l.name,'无上级') leader from emp as e left join emp as l on e.leaderid=l.id;
+----------+----------+
| emp | leader |
+----------+----------+
| zhangsir | aya |
| wang | zhangsir |
| zhang | wang |
| aya | NULL |
+----------+----------+
4 rows in set (0.000 sec)

范例：三表连接

#三张表连接示例
MariaDB [hellodb]> select name,course,score from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.CourseID;
+-------------+----------------+-------+
| name | course | score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian | 77 |
| Shi Zhongyu | Weituo Zhang | 93 |
| Shi Potian | Kuihua Baodian | 47 |
| Shi Potian | Daiyu Zanghua | 97 |
| Xie Yanke | Kuihua Baodian | 88 |
| Xie Yanke | Weituo Zhang | 75 |
| Ding Dian | Daiyu Zanghua | 71 |
| Ding Dian | Kuihua Baodian | 89 |
| Yu Yutong | Hamo Gong | 39 |
| Yu Yutong | Dagou Bangfa | 63 |
| Shi Qing | Hamo Gong | 96 |
| Xi Ren | Hamo Gong | 86 |
| Xi Ren | Dagou Bangfa | 83 |
| Lin Daiyu | Taiji Quan | 57 |
| Lin Daiyu | Jinshe Jianfa | 93 |
+-------------+----------------+-------+
15 rows in set (0.000 sec)


MariaDB [hellodb]> select st.name,co.Course,sc.score from courses co inner
join scores sc on co.courseid=sc.courseid inner join students st on
sc.stuid=st.stuid;
+-------------+----------------+-------+
| name | Course | score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian | 77 |
| Shi Zhongyu | Weituo Zhang | 93 |
| Shi Potian | Kuihua Baodian | 47 |
| Shi Potian | Daiyu Zanghua | 97 |
| Xie Yanke | Kuihua Baodian | 88 |
| Xie Yanke | Weituo Zhang | 75 |
| Ding Dian | Daiyu Zanghua | 71 |
| Ding Dian | Kuihua Baodian | 89 |
| Yu Yutong | Hamo Gong | 39 |
| Yu Yutong | Dagou Bangfa | 63 |
| Shi Qing | Hamo Gong | 96 |
| Xi Ren | Hamo Gong | 86 |
| Xi Ren | Dagou Bangfa | 83 |
| Lin Daiyu | Taiji Quan | 57 |
| Lin Daiyu | Jinshe Jianfa | 93 |
+-------------+----------------+-------+
15 rows in set (0.001 sec)
MariaDB [hellodb]>