4. MySQL 多表查询

重点：

MySQL 的 三种安装方式：包安装，二进制安装，源码编译安装。

MySQL 的 基本使用

MySQL 多实例

DDLcreate alter drop

DML insert update delete

DQL select

3.5）DDL 语句

表：二维关系

设计表：遵循规范

定义：字段，索引

字段：字段名，字段数据类型，修饰符

约束，索引：应该创建在经常用作查询条件的字段上

3.5.1）创建表

参考文档：MySQL :: MySQL 8.0 Reference Manual :: 13.1.20 CREATE TABLE Statement

// 创建表

CREATE TABLE


// 获取帮助

HELP CREATE TABLE

外键管理参考文档：MySQL :: MySQL 8.0 Reference Manual :: 13.1.20.5 FOREIGN KEY Constraints

创建表的方法

（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 是指表类型，也即在表创建时指明其使用的存储引擎

同一库中不同表可以使用不同的存储引擎

同一个库中表建议要使用同一种存储引擎类型

范例：创建表

创建一个名为 student 的数据表

定义一个名为 id 的字段

SMALLINT整数数据类型 最大值 65535

UNSIGNED仅存储正整数

PRIMARY KEY表的主键，每个主键值都必须是唯一且不为空。

AUTO_INCREMENT值会自动递增

定义一个名为 name 的字段

VARCHAR(10)字符串类型存储的最大字符数为 10。

NOT NULL 不允许存储空值

定义一个名为 age 的字段

TINYINT整数数据类型最大值 255

UNSIGNED仅存储正整数

创建一个名为 gender 的字段

ENUM('M','F')数据类型为 ENUM 枚举类型

DEFAULT 'M'默认值将被设置为 'M'

创建表的 SQL 语句的结尾部分

ENGINE=InnoDB指定存储引擎为 InnoDB

UTO_INCREMENT=10指定自增列的初始值为 10。这表示在向具有自增功能的列中插入新行时，首个自增值将为 10，接下来的自增值将逐次增加 1。

EFAULT CHARSET=utf8指定表的默认字符集为 utf8。（不默认使用数据库的默认字符集）

CREATE TABLE student (

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(10) NOT NULL,

age TINYINT UNSIGNED,

gender ENUM('M','F') DEFAULT 'M'

)ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

// 创建数据库

MariaDB [(none)]> create database db1;

Query OK, 1 row affected (0.00 sec)


// 进入数据库

MariaDB [(none)]> use db1;

Database changed


// 创建数据表

MariaDB [db1]> CREATE TABLE student (

    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

    -> name VARCHAR(10) NOT NULL,

    -> age TINYINT UNSIGNED,

    -> gender ENUM('M','F') DEFAULT 'M'

    -> )ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.00 sec)


// 验证

MariaDB [db1]> DESC student;

+--------+----------------------+------+-----+---------+----------------+

| Field  | Type                 | Null | Key | Default | Extra          |

+--------+----------------------+------+-----+---------+----------------+

| id     | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |

| name   | varchar(10)          | NO   |     | NULL    |                |

| age    | tinyint(3) unsigned  | YES  |     | NULL    |                |

| gender | enum('M','F')        | YES  |     | M       |                |

+--------+----------------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)



'第一个注意点'    // 字段定义的重要性

// 插入数据  ( 注: 插入的值超过了 10 位, 有 12 位 '会被截断插入' )

MariaDB [db1]> insert student(name,age) values('wangxiaochun',20);

Query OK, 1 row affected, 1 warning (0.01 sec)    ( 报错有一个 warning )


// 查看 warning 信息

MariaDB [db1]> show warnings;

+---------+------+-------------------------------------------+

| Level   | Code | Message                                   |

+---------+------+-------------------------------------------+

| Warning | 1265 | Data truncated for column 'name' at row 1 |

+---------+------+-------------------------------------------+

1 row in set (0.00 sec)


// 查询表

MariaDB [db1]> select * from student;

+----+------------+------+--------+

| id | name       | age  | gender |

+----+------------+------+--------+

| 10 | wangxiaoch |   20 | M      |                # 多的字符没有被插入完全

+----+------------+------+--------+

1 row in set (0.00 sec)


// 插入数据

MariaDB [db1]> insert student(name,age,gender) values('xiaohong',18,'f');

Query OK, 1 row affected (0.001 sec)


// 查询表

MariaDB [db1]> select * from student;

+----+------------+------+--------+

| id | name       | age  | gender |

+----+------------+------+--------+

| 10 | wangxiaoch |   20 | M      |


| 11 | xiaohong   |   18 | F      |

+----+------------+------+--------+

2 rows in set (0.001 sec)


'第二个注意点'    // 数据库字符集的重要性

// 插入数据    ( 插入汉字 )

// 如果数据库字符集为 latin1, 会报错 ( 数值会乱码 )

MariaDB [db1]> insert student(name,age) values('王俊',18);

Query OK, 1 row affected, 1 warning (0.01 sec)


// 查询表

MariaDB [db1]> select * from student;

+----+------------+------+--------+

| id | name       | age  | gender |

+----+------------+------+--------+

| 10 | wangxiaoch |   20 | M      |


| 11 | xiaohong   |   18 | F      |

| 12 | ??         |   18 | M      |    // 如果数据库字符集为 latin1 ( 数值会乱码 )

+----+------------+------+--------+

3 rows in set (0.001 sec)


//

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


// 查询 'student' 表的信息

// 这样的查询通常用于查看表的基本信息以及一些统计数据

// 以便于数据库管理员了解表的使用情况和性能特征

SHOW TABLE STATUS LIKE 'student';

SHOW TABLE STATUS LIKE 'student'\G

范例：auto_increment 属性

MariaDB [hellodb]> SHOW VARIABLES LIKE 'auto_inc%';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| auto_increment_increment | 1     |

| auto_increment_offset    | 1     |

+--------------------------+-------+

2 rows in set (0.001 sec)


MariaDB [hellodb]> SET @@auto_increment_increment=10;

Query OK, 0 rows affected (0.001 sec)


MariaDB [hellodb]> SET @@auto_increment_offset=3;

Query OK, 0 rows affected (0.000 sec)


MariaDB [hellodb]> SHOW VARIABLES LIKE 'auto_inc%';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| auto_increment_increment | 10    |

| auto_increment_offset    | 3     |

+--------------------------+-------+

2 rows in set (0.001 sec)


MariaDB [hellodb]> CREATE TABLE autoinc1 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

Query OK, 0 rows affected (0.004 sec)


MariaDB [hellodb]> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);

Query OK, 4 rows affected (0.001 sec)

Records: 4 Duplicates: 0  Warnings: 0


MariaDB [hellodb]> SELECT col FROM autoinc1;

+-----+

| col |

+-----+

|   3 |

|  13 |

|  23 |

|  33 |

+-----+

4 rows in set (0.000 sec)

范例：

MariaDB [db1]> create table testdate (id int auto_increment primary key,date timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL);


MariaDB [db1]> insert testdate()values()()();

MariaDB [db1]> 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

范例：

MariaDB [db1]> create table user select user,host,password from mysql.user;

Query OK, 4 rows affected (0.008 sec)

Records: 4 Duplicates: 0  Warnings: 0


MariaDB [db1]> show tables;

+---------------+

| Tables_in_db1 |

+---------------+

| student       |

| user          |

+---------------+

2 rows in set (0.000 sec)


MariaDB [db1]> 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) }

范例：

// 查看表结构

MariaDB [db1]> 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       |                |

+--------+---------------------+------+-----+---------+----------------+

4 rows in set (0.001 sec)


MariaDB [db1]> create table teacher like student;

Query OK, 0 rows affected (0.006 sec)


MariaDB [db1]> 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)

范例：创建外键表

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


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


mysql> desc school;

+-------+-------------+------+-----+---------+----------------+

| Field | Type        | Null | Key | Default | Extra          |

+-------+-------------+------+-----+---------+----------------+

| id    | int         | NO   | PRI | NULL    | auto_increment |

| name  | varchar(10) | YES  |     | NULL    |                |

+-------+-------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)


mysql> desc teacher;

+-----------+-------------+------+-----+---------+----------------+

| Field     | Type        | Null | Key | Default | Extra          |

+-----------+-------------+------+-----+---------+----------------+

| id        | int         | NO   | PRI | NULL    | auto_increment |

| name      | varchar(10) | YES  |     | NULL    |                |

| school_id | int         | YES  | MUL | NULL    |                |

+-----------+-------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)


mysql> show create table teacher;


mysql> insert school values(0,'magedu'),(0,'wangedu');

mysql> select * from school;

+----+---------+

| id | name    |

+----+---------+

|  1 | magedu  |

|  2 | wangedu |

+----+---------+

2 rows in set (0.00 sec)


mysql> insert teacher values(0,'xiaoming',1);

mysql> insert teacher values(0,'xiaohong',2);

Query OK, 1 row affected (0.00 sec)

mysql> select * from teacher;

+----+----------+-----------+

| id | name     | school_id |

+----+----------+-----------+

|  1 | xiaoming |         1 |

|  2 | xiaohong |         2 |

+----+----------+-----------+

2 rows in set (0.00 sec)


mysql> insert teacher values(0,'xiaobai',3);

3.5.2）表查看

查看表：

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;

范例：

MariaDB [db1]> show tables;

+---------------+

| Tables_in_db1 |

+---------------+

| student       |

+---------------+

1 row in set (0.000 sec)

范例：

// 查看表结构 方法 1

MariaDB [db1]> 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       |                |

+--------+---------------------+------+-----+---------+----------------+

4 rows in set (0.001 sec)


// 查看表结构 方法 2

MariaDB [db1]> SHOW COLUMNS FROM 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)

范例：

MariaDB [db1]> show create table student;

范例：

// 查询 'student' 表的信息

MariaDB [db1]> SHOW TABLE STATUS LIKE 'student'\G

*************************** 1. row ***************************

           Name: student

          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: 10

     Create_time: 2020-02-17 11:35:29

     Update_time: NULL

     Check_time:  NULL

       Collation: latin1_swedish_ci

        Checksum: NULL

 Create_options:

         Comment:

Max_index_length: 0

       Temporary: N

1 row in set (0.001 sec)

查看库中所有表状态

SHOW TABLE STATUS FROM db_name

范例：

// 查询 'student' 表的信息

MariaDB [db1]> SHOW TABLE STATUS FROM db1\G

*************************** 1. row ***************************

           Name: employee

          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: 2020-02-17 11:43:21

     Update_time: NULL

     Check_time:  NULL

       Collation: latin1_swedish_ci

        Checksum: NULL

 Create_options:

         Comment:

Max_index_length: 0

       Temporary: N

       

*************************** 2. row ***************************

           Name: student

          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: 10

     Create_time: 2020-02-17 11:35:29

     Update_time: NULL

     Check_time:  NULL

       Collation: latin1_swedish_ci

        Checksum: NULL

 Create_options:

         Comment:

Max_index_length: 0

       Temporary: N

2 rows in set (0.001 sec)

3.5.3）修改和删除表

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: 指定要修改的表名为 s1

// ADD: 表明要添加一个新的列

// phone: 这是新列的名称

// char(11): 指定新列的数据类型为字符型, 长度为 11.

// NOT NULL: 这是一个约束, 表示这个字段不能为 NULL, 即这个字段必须有值.

ALTER TABLE s1 ADD phone char(11) NOT NULL;


修改字段类型

// ALTER TABLE s1: 指定要修改的表名为 s1

// MODIFY: 表明要修改现有列的数据类型

// phone: 这是要修改的列的名称

// int: 这是要将列修改为的新数据类型

ALTER TABLE s1 MODIFY phone int;


修改字段名称和类型

// ALTER TABLE s1: 指定要修改的表名为 s1

// CHANGE COLUMN: 表明要修改现有的列

// phone: 这是要修改的列的当前名称

// mobile: 这是要将列修改为的新名称

// varchar(11): 这是要将列修改为的新数据类型, 即字符型, 长度为 11

ALTER TABLE s1 CHANGE COLUMN phone mobile varchar(11);


删除字段

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 <外键名>;

3.6）DML 语句

DML：INSERT，DELETE，UPDATE

3.6.1）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,...)

范例：全值插入

字段赋值需完整排序

// id 字段定义 0 没有关系 ( 该字段会自动递增 )

// gender 字段定义 default 没有关系 ( 该字段会默认定义为 M )

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)

范例：部分列插入

字段名与字段赋值一一对应

mysql> insert student(name,age) values('zhang',20);

Query OK, 1 row affected (0.01 sec)


// id 字段定义 default 没有关系 ( 该字段会自动递增 )

mysql> insert student(id,name,age) values(default,'li',19);

Query OK, 1 row affected (0.00 sec)


// id 字段定义 null 也没有关系 ( 该字段会自动递增 )

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)

3.6.2）UPDATE 语句

语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_reference

SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

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

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

update student set gender='F';

'整数型'类型: 无需使用引号包含

'字符串'类型: 需要使用引号包含

update student set gender='F' where id=5;

update student set gender='F' where name='小红';

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

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

[root@centos8 ~] vim /etc/my.cnf

[mysql]

safe-updates

4.6.3）DELETE 语句

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

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

# 可先排序再指定删除的行数

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

// 注意: 一定要有限制条件, 否则将清空表中的所有数据.

delete from student where id=5;

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

TRUNCATE TABLE tbl_name;

缩减表大小

OPTIMIZE TABLE tb_name

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

mysql> alter table student add is_del bool default false;


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)

3.7）DQL 语句

3.7.1）单表查询

MySQL 基本总结（学习ing ）

MySQL 常用命令：开放平台

MySQL 单表查询：开放平台

MySQL 多表查询：开放平台

status        // 查询 MYSQL 版本

show databases;    // 查看所有数据库

use xxx;           // 使用数据库

show tables;       // 查看所有数据表


// 查询    ( 能查询部分字段的, 千万不要查询 *, 这样会增加查询时间 )

select * from 表名;        // 查询一个表中的所有数据

select 字段1,字段2,字段3 from 表名;    // 查询指定字段的数据


// 给字段取别名

select name from students;

select name as 姓名 from students;


// 给表取别名

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：读锁，共享锁，同时多个读操作

( '简单查询' )

// 查询 student 表所有字段的数据

select * from student;


// 查询 student 表所有字段的数据

// 然后过滤出那些性别字段值为 'F' 的记录

select * from student where gender='F';


// 查询 student 表 name,age,phone 字段的数据

// 然后过滤出那些性别字段值为 'F' 的记录

select name,age,phone from student where gender='F';

实验环境

MariaDB [(none)]> source /root/hellodb_innodb.sql


MariaDB [hellodb]> show tables;

+-------------------+

| Tables_in_hellodb |

+-------------------+

| classes           |

| coc               |

| courses           |

| scores            |

| students          |

| teachers          |

| toc               |

+-------------------+

7 rows in set (0.00 sec)


MariaDB [hellodb]> show create table students;

+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                    |

+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| students | CREATE TABLE `students` (

  `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `Name` varchar(50) NOT NULL,

  `Age` tinyint(3) unsigned NOT NULL,

  `Gender` enum('F','M') NOT NULL,

  `ClassID` tinyint(3) unsigned DEFAULT NULL,

  `TeacherID` int(10) unsigned DEFAULT NULL,

  PRIMARY KEY (`StuID`)

) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 |

+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)


MariaDB [hellodb]> select * from students;

+-------+---------------+-----+--------+---------+-----------+

| StuID | Name          | Age | Gender | ClassID | TeacherID |

+-------+---------------+-----+--------+---------+-----------+

|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |

|     2 | Shi Potian    |  22 | M      |       1 |         7 |

|     3 | Xie Yanke     |  53 | M      |       2 |        16 |

|     4 | Ding Dian     |  32 | M      |       4 |         4 |

|     5 | Yu Yutong     |  26 | M      |       3 |         1 |

|     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 |

+-------+---------------+-----+--------+---------+-----------+

25 rows in set (0.00 sec)

案例演示

// 查询年龄为 20 以下的行信息

select * from students where age <=20;


// 查询年龄为  20 - 30 之间的行信息

select * from students where age >=20 and age <=30;

select * from students where age between 20 and 30;


// 查询年龄为  18,20,22 的行信息

select * from students where age in (18,20,22);


// 只查看年龄列的行信息

select age from students;

select distinct age from students;    # 只查看不同值 ( 去重操作 )


// 查看 classid 为 NULL 值的行信息

select * from students where classid is null;

select * from students where classid is not null;    # 不为 NULL 值


// LIKE 模糊匹配

select * from students where name = 'Xi Ren';    # 精确查找

select * from students where name like 'Xi%';    # 模糊查找

select * from students where name like '%Xi%';   # 模糊查找

范例：

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;

+----------+---------------+--------+

| 学员ID   | 姓名          | 性别    |

+----------+---------------+--------+

|        1 | Shi Zhongyu   | M      |

|        2 | Shi Potian    | M      |

|        3 | Xie Yanke     | M      |

|        4 | Ding Dian     | M      |

|        5 | Yu Yutong     | M      |

|        6 | Shi Qing      | M      |

|        7 | Xi Ren        | F      |

|        8 | Lin Daiyu     | F      |

|        9 | Ren Yingying  | F      |

|       10 | Yue Lingshan  | F      |

|       11 | Yuan Chengzhi | M      |

|       12 | Wen Qingqing  | F      |

|       13 | Tian Boguang  | M      |

|       14 | Lu Wushuang   | F      |

|       15 | Duan Yu       | M      |

|       16 | Xu Zhu        | M      |

|       17 | Lin Chong     | M      |

|       18 | Hua Rong      | M      |

|       19 | Xue Baochai   | F      |

|       20 | Diao Chan     | F      |

|       21 | Huang Yueying | F      |

|       22 | Xiao Qiao     | F      |

|       23 | Ma Chao       | M      |

|       24 | Xu Xian       | M      |

|       25 | Sun Dasheng   | M      |

+----------+---------------+--------+

25 rows in set (0.000 sec)

范例：简单查询

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

MariaDB [hellodb]> select * from students where classid is null;

+-------+-------------+-----+--------+---------+-----------+

| StuID | Name        | Age | Gender | ClassID | TeacherID |

+-------+-------------+-----+--------+---------+-----------+

|    24 | Xu Xian     |  27 | M      |    NULL |      NULL |

|    25 | Sun Dasheng | 100 | M      |    NULL |      NULL |

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (0.002 sec)


MariaDB [hellodb]> select * from students where classid <=> null;

+-------+-------------+-----+--------+---------+-----------+

| StuID | Name        | Age | Gender | ClassID | TeacherID |

+-------+-------------+-----+--------+---------+-----------+

|    24 | Xu Xian     |  27 | M      |    NULL |      NULL |

|    25 | Sun Dasheng | 100 | M      |    NULL |      NULL |

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (0.000 sec)


MariaDB [hellodb]> select * from students where classid is not null;


# ifnull 函数判断指定的字段是否为空值,如果空值则使用指定默认值

mysql> select stuid,name,ifnull(classID,'无班级') from students where classid is null;

+-------+-------------+-----------------------------+

| stuid | name        | ifnull(classID,'无班级')    |

+-------+-------------+-----------------------------+

|    24 | Xu Xian     | 无班级                      |

|    25 | Sun Dasheng | 无班级                      |

+-------+-------------+-----------------------------+

2 rows in set (0.00 sec)

范例： 记录去重

MariaDB [hellodb]> select distinct gender from students ;

+--------+

| gender |

+--------+

| M      |

| F      |

+--------+

2 rows in set (0.001 sec)


// 将 age 和 gender 多个字段重复的记录去重

mysql> select distinct age,gender from students;

范例：SQL 注入攻击

// 创建表

create table user (id int primary key auto_increment ,name varchar(20) not null ,password varchar(30) not null );


// 查看表结构

desc user;


// 插入数据

insert user values(null,'admin','123456');

insert user values(null,'wangj','456789');


// 查询表内容

select * from user;


// 类比登录认证

select * from user where name='admin' and password='123456';    # 可以查询到结果代表账户密码正确.( 登录成功 )

// 实际上我们用如下方法也可以查询到内容 ( 类似 SQL 注入 )

// 为什么可以成功 ( 因为该 SQL 语句增加了一个 or 或者 1=1 )

// 1=1 成立, 就可以查询到数据 ( 黑客就可以使用该 SQL 注入方法基于任何身份登录系统 )

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='xxxxxx';

select * from user where name='admin'; # ' and password='xxxxx';

范例：分页查询

# 只取前 3 个

mysql> select * from students limit 0,3;

mysql> select * from students limit 3;

+-------+-------------+-----+--------+---------+-----------+

| StuID | Name       | Age | Gender | ClassID | TeacherID |

+-------+-------------+-----+--------+---------+-----------+

|     1 | Shi Zhongyu |  22 | M     |       2 |         3 |

|     2 | Shi Potian |  22 | M     |       1 |         7 |

|     3 | Xie Yanke   |  53 | M     |       2 |        16 |

+-------+-------------+-----+--------+---------+-----------+

3 rows in set (0.00 sec)


mysql> select * from students limit 1,3;

+-------+------------+-----+--------+---------+-----------+

| StuID | Name       | Age | Gender | ClassID | TeacherID |

+-------+------------+-----+--------+---------+-----------+

|     2 | Shi Potian |  22 | M     |       1 |         7 |

|     3 | Xie Yanke |  53 | M     |       2 |        16 |

|     4 | Ding Dian |  32 | M     |       4 |         4 |

+-------+------------+-----+--------+---------+-----------+

3 rows in set (0.00 sec)


# 查询第 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)

范例：分组统计

// 统计每个 classid 出现的数量

mysql> select classid 班级,count(*) 学员数量 from students group by classid;

+--------+--------------+

| 班级   | 学员数量     |

+--------+--------------+

|   NULL |            2 |

|      1 |            4 |

|      2 |            3 |

|      3 |            4 |

|      4 |            4 |

|      5 |            1 |

|      6 |            4 |

|      7 |            3 |

+--------+--------------+

8 rows in set (0.00 sec)



MariaDB [hellodb]> select classid 班级,gender 性别,count(*) 数量 from students group by 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 |

+--------+--------+--------+

12 rows in set (0.00 sec)


// 分组统计

select classid,avg(age) as 平均年龄 from students where classid > 3 group by classid having 平均年龄 >30 ;

select gender,avg(age) 平均年龄 from students group by gender having gender='M';


// 多个字段分组统计

select classid,gender,count(*) 数量 from students group by classid,gender;

select classid,gender,count(*) 数量 from students group by gender,classid;


// 平均值

MariaDB [hellodb]> select gender 性别,avg(age) 平均年龄 from students group by gender;

+--------+--------------+

| 性别   | 平均年龄     |

+--------+--------------+

| F      |      19.0000 |

| M      |      33.0000 |

+--------+--------------+

2 rows in set (0.00 sec)

范例：group_concat 函数实现分组信息的集合

mysql> select gender,group_concat(name) from students group by gender;

范例：分组统计

// with rollup 分组后聚合函数统计后再做汇总

mysql> select gender,count(*) from students group by gender with rollup;

+--------+----------+

| gender | count(*) |

+--------+----------+

| F      |       10 |

| M      |       15 |

| NULL   |       25 |

+--------+----------+

3 rows in set (0.00 sec)


mysql> select gender,group_concat(name) from students group by gender with rollup;

范例：分组统计

// 注意: 一旦使用分组 group by

// 我们在 select 后面的只跟 采用分组的列 或 聚合函数

// 不要将其它的列放在 select

 后面, 否则根据系统变量 SQL_MODE 的值不同而不同的结果


// 以下为 MySQL8.0.17 的执行结果

mysql> use hellodb

mysql> select classid,count(*) 数量 from students group by classid;

+---------+--------+

| classid | 数量   |

+---------+--------+

|       2 |      3 |

|       1 |      4 |

|       4 |      4 |

|       3 |      4 |

|       5 |      1 |

|       7 |      3 |

|       6 |      4 |

|    NULL |      2 |

+---------+--------+

8 rows in set (0.00 sec)


mysql> select classid,count(*),stuid 数量 from students group by classid;

mysql> select @@sql_mode;

// 以下是 Mariadb10.3.17 的执行结果

MariaDB [hellodb]> select classid, count(*), stuid  from students group by classid;

+---------+----------+-------+

| classid | count(*) | stuid |

+---------+----------+-------+

|    NULL |        2 |    24 |

|       1 |        4 |     2 |

|       2 |        3 |     1 |

|       3 |        4 |     5 |

|       4 |        4 |     4 |

|       5 |        1 |     6 |

|       6 |        4 |     9 |

|       7 |        3 |     8 |

+---------+----------+-------+

8 rows in set (0.001 sec)


MariaDB [hellodb]> select @@sql_mode;

范例：排序

// 只取前 3 个

mysql> select * from students order by age desc limit 3;

+-------+-------------+-----+--------+---------+-----------+

| StuID | Name       | Age | Gender | ClassID | TeacherID |

+-------+-------------+-----+--------+---------+-----------+

|    25 | Sun Dasheng | 100 | M     |   NULL  |     NULL  |

|     3 | Xie Yanke   |  53 | M     |       2 |        16 |

|     6 | Shi Qing    |  46 | M     |       5 |     NULL  |

+-------+-------------+-----+--------+---------+-----------+

3 rows in set (0.00 sec)


// 跳过前 3 个只显示后续的 2 个

mysql> select * from students order by age desc limit 3,2;

+-------+--------------+-----+--------+---------+-----------+

| StuID | Name         | Age | Gender | ClassID | TeacherID |

+-------+--------------+-----+--------+---------+-----------+

|    13 | Tian Boguang |  33 | M      |       2 |      NULL |

|     4 | Ding Dian    |  32 | M      |       4 |         4 |

+-------+--------------+-----+--------+---------+-----------+

2 rows in set (0.00 sec)


// 排序 ( 默认从小到大 )

select * from students order by age;


// 倒序

select * from students order by age desc;

范例：排序

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;

范例：分组和排序

mysql> select classid,count(*) 数量  from students group by classid order by 数量;

+---------+--------+

| classid | 数量   |

+---------+--------+

|       5 |      1 |

|    NULL |      2 |

|       2 |      3 |

|       7 |      3 |

|       1 |      4 |

|       4 |      4 |

|       3 |      4 |

|       6 |      4 |

+---------+--------+

8 rows in set (0.00 sec)


# 分组后再排序

MariaDB [hellodb]> select gender,classid,avg(age) from students where classid is not null group by gender,classid order by gender,classid;

+--------+---------+----------+

| gender | classid | avg(age) |

+--------+---------+----------+

| F      |       1 |  19.0000 |

| F      |       3 |  18.3333 |

| F      |       6 |  20.0000 |

| F      |       7 |  18.0000 |

| F      |      77 |  18.0000 |

| F      |      93 |  18.0000 |

| M      |       1 |  21.5000 |

| M      |       2 |  35.2000 |

| M      |       3 |  23.0000 |

| M      |       4 |  23.6000 |

| M      |       5 |  46.0000 |

| M      |       6 |  23.0000 |

| M      |       7 |  23.0000 |

| M      |      94 |  18.0000 |

+--------+---------+----------+

14 rows in set (0.001 sec)


MariaDB [hellodb]> select * from students order by age limit 10;

+-------+---------------+-----+--------+---------+-----------+

| StuID | Name          | Age | Gender | ClassID | TeacherID |

+-------+---------------+-----+--------+---------+-----------+

|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |

|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |

|    33 | Miejue Shitai |  18 | F      |      77 |      NULL |

|    32 | Zhang Sanfeng |  18 | M      |      94 |      NULL |

|    27 | liudehua      |  18 | F      |       1 |      NULL |

|    34 | Lin Chaoying  |  18 | F      |      93 |      NULL |

|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |

|     7 | Xi Ren        |  19 | F      |       3 |      NULL |

|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |

|    20 | Diao Chan     |  19 | F      |       7 |      NULL |

+-------+---------------+-----+--------+---------+-----------+

10 rows in set (0.001 sec)


MariaDB [hellodb]> select * from students order by age limit 3,10;

+-------+---------------+-----+--------+---------+-----------+

| StuID | Name          | Age | Gender | ClassID | TeacherID |

+-------+---------------+-----+--------+---------+-----------+

|    34 | Lin Chaoying  |  18 | F      |      93 |      NULL |

|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |

|    32 | Zhang Sanfeng |  18 | M      |      94 |      NULL |

|    27 | liudehua      |  18 | F      |       1 |      NULL |

|    20 | Diao Chan     |  19 | F      |       7 |      NULL |

|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |

|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |

|    15 | Duan Yu       |  19 | M      |       4 |      NULL |

|     7 | Xi Ren        |  19 | F      |       3 |      NULL |

|    29 | wuyanzu       |  19 | M      |       4 |      NULL |

+-------+---------------+-----+--------+---------+-----------+

10 rows in set (0.000 sec)


MariaDB [hellodb]> select distinct age  from students order by age limit 3;

+-----+

| age |

+-----+

|  17 |

|  18 |

|  19 |

+-----+

3 rows in set (0.001 sec)


MariaDB [hellodb]> select distinct age  from students order by age limit 3,5;

+-----+

| age |

+-----+

|  20 |

|  21 |

|  22 |

|  23 |

|  25 |

+-----+

5 rows in set (0.001 sec)

范例：分组和排序的次序

// 顺序: 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 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 '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 可以随其它字段的更新自动更新

MariaDB [testdb]>  create table testdate (id int auto_increment primary key,date timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

MariaDB [testdb]>  insert testdate () values();

MariaDB [testdb]>  insert testdate values(),(),();


MariaDB [testdb]> select * from testdate;

+----+---------------------+

| id | date                |

+----+---------------------+

|  1 | 2020-06-03 15:21:03 |

|  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 |

+----+---------------------+

6 rows in set (0.001 sec)


MariaDB [testdb]> select * from testdate where date between '2020-06-03 15:21:12' and '2020-06-03 18:27:40';

+----+---------------------+

| 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 |

+----+---------------------+

4 rows in set (0.000 sec)


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

+----+---------------------+

| 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 |

+----+---------------------+

4 rows in set (0.001 sec)


# 修改其它字段, 会自动更新 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 | 2020-06-03 18:34:51 |

+----+---------------------+

6 rows in set (0.001 sec)

3.7.2）多表查询

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

子查询：在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 语法

自连接：本表和本表进行连接查询

3.7.2.1）子查询

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

// 举例

select * from students where age > (select avg(age) from students);

// 举例

update students set age= (select avg(age) from students) where stuid=25;

update teachers set age= (select avg(age) from students) where tid=4;

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

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

参考链接：MySQL :: MySQL 8.0 Reference Manual :: 13.2.15.6 Subqueries with EXISTS or NOT EXISTS

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)


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)

3.7.2.2）联合查询

联合查询 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)


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    | magedu   |

|    2 | mage     | magedu   |

|    3 | wang     | centos   |

+------+----------+----------+

3 rows in set (0.00 sec)


MariaDB [hellodb]> select distinct  * from user ;

+------+----------+----------+

| id   | username | password |

+------+----------+----------+

|    1 | admin    | magedu   |

|    2 | mage     | magedu   |

|    3 | wang     | centos   |

+------+----------+----------+

3 rows in set (0.00 sec)

范例：去重记录

mysql> select * from emp;

+-----+---------------+-----+--------+

| TID | Name          | Age | Gender |

+-----+---------------+-----+--------+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | mage          |  20 | M      |

|   4 | li            |  22 | F      |

|   3 | mage          |  20 | M      |

+-----+---------------+-----+--------+

5 rows in set (0.00 sec)


mysql> select distinct * from emp;

+-----+---------------+-----+--------+

| TID | Name          | Age | Gender |

+-----+---------------+-----+--------+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | mage          |  20 | M      |

|   4 | li            |  22 | F      |

+-----+---------------+-----+--------+

4 rows in set (0.00 sec)


mysql> select * from emp  union  select * from emp;

+-----+---------------+-----+--------+

| TID | Name          | Age | Gender |

+-----+---------------+-----+--------+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | mage          |  20 | M      |

|   4 | li            |  22 | F      |

+-----+---------------+-----+--------+

4 rows in set (0.00 sec)


# union all 不去重

mysql> select * from emp  union all select * from emp;

+-----+---------------+-----+--------+

| TID | Name          | Age | Gender |

+-----+---------------+-----+--------+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | mage          |  20 | M      |

|   4 | li            |  22 | F      |

|   3 | mage          |  20 | M      |

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | mage          |  20 | M      |

|   4 | li            |  22 | F      |

|   3 | mage          |  20 | M      |

+-----+---------------+-----+--------+

10 rows in set (0.00 sec)

3.7.2.3）交叉连接

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;

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

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 ;

3.7.2.4）内连接

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

范例：内连接

// 内连接 inner join

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


// 表定义别名

// 如果表定义了别名, 原表名将无法使用

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 stuid,s.name studentname,s.age studentage,tid,t.name as teachername,t.age teacherage from students as s inner join teachers t on s.teacherid=t.tid;

+-------+-------------+------------+-----+---------------+------------+

| stuid | studentname | studentage | tid | teachername   | teacherage |

+-------+-------------+------------+-----+---------------+------------+

|     5 | Yu Yutong   |         26 |   1 | Song Jiang    |         45 |

|    25 | Sun Dasheng |        100 |   1 | Song Jiang    |         45 |

|     1 | Shi Zhongyu |         22 |   3 | Miejue Shitai |         77 |

|     4 | Ding Dian   |         32 |   4 | Lin Chaoying  |         93 |

+-------+-------------+------------+-----+---------------+------------+

4 rows in set (0.00 sec)


MariaDB [hellodb]> select * from students , teachers where students.teacherid=teachers.tid;


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;


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 ;

MariaDB [hellodb]> select * from students s inner join teachers t on

 s.teacherid=t.tid where s.age > 30 ;

自然连接

当源表和目标表共享相同名称的列时，就可以在它们之间执行自然连接，而无需指定连接列。

在使用纯自然连接时，如没有相同的列时，会产生交叉连接（笛卡尔乘积）

语法：(SQL:1999)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)

3.7.2.4）左和右外连接

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

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

范例：左，右外连接

// 左外连接 left outer join

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;


// 多个条件的左外连接

MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid=t.tid and s.teacherid is null;


// 先左外连接, 再过滤

MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid=t.tid where s.teacherid is null;


// 右外连接

MariaDB [hellodb]> select * from students s right outer join teachers t on s.teacherid=t.tid;


// 右外连接的扩展用法

MariaDB [hellodb]> select * from students s right outer join teachers t on s.teacherid=t.tid  where s.teacherid is null;

3.7.2.5）完全外连接

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;


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 select s.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)

3.7.2.6）自连接

自连接，即表自身连接自身

范例：自连接

# 自连接

MariaDB [hellodb]> select * from emp;

+------+----------+----------+

| id   | name     | leaderid |

+------+----------+----------+

|    1 | mage     |     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 | mage     |

| 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 | mage     |

| wang     | zhangsir |

| zhang    | wang     |

| mage     | 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 | mage     |

| wang     | zhangsir |

| zhang    | wang     |

| mage     | 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)

3.7.3）SELECT 语句处理的顺序

查询执行路径中的组件：查询缓存、解析器、预处理器、优化器、查询执行引擎、存储引擎

SELECT 语句的执行流程：

FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause -->SELECT --> ORDER BY --> LIMIT

练习题：（重要）

导入 hellodb.sql 生成数据库

在 students 表中，查询年龄大于25岁，且为男性的同学的名字和年龄

SELECT name,age FROM students WHERE age > 25 AND gender = 'M';

以 ClassID 字段为分组依据，显示各组的平均年龄

SELECT ClassID,AVG(age) AS avg_age

FROM students

GROUP BY ClassID;

显示第 2 题中平均年龄大于 30 的分组及平均年龄

要显示第 2 题中平均年龄大于 30 的分组及其平均年龄，您可以使用第 2 题的查询作为子查询，并在外部查询中筛选出平均年龄大于 30 的分组。

SELECT ClassID, avg_age

FROM (

SELECT ClassID, AVG(age) AS avg_age

FROM students

GROUP BY ClassID

) AS avg_per_group

WHERE avg_age > 30;

显示以 L 开头的名字的同学的信息

SELECT * FROM students

WHERE name LIKE 'L%';

显示 TeacherID 非空的同学的相关信息

SELECT * FROM students

WHERE TeacherID IS NOT NULL;

以年龄排序后，显示年龄最大的前 10 位同学的信息

SELECT * FROM students

ORDER BY age DESC

LIMIT 10;

查询年龄大于等于 20 岁，小于等于 25 岁的同学的信息

SELECT * FROM students WHERE age >= 20 AND age <= 25;

以 ClassID 分组，显示每班的同学的人数

SELECT ClassID, COUNT(*) AS student_count

FROM students

GROUP BY ClassID;

以 Gender 分组，显示其年龄之和

SELECT Gender, SUM(age) AS total_age

FROM students

GROUP BY Gender;

以 ClassID 分组，显示其平均年龄大于 25 的班级

SELECT ClassID, AVG(age) AS avg_age

FROM students

GROUP BY ClassID

HAVING AVG(age) > 25;

以 Gender 分组，显示各组中年龄大于 25 的学员的年龄之和

SELECT Gender, SUM(age) AS total_age

FROM students

GROUP BY Gender

HAVING SUM(CASE WHEN age > 25 THEN age ELSE 0 END) > 0;

显示前 5 位同学的姓名、课程及成绩

显示其成绩高于 80 的同学的名称及课程

取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩

显示每门课程课程名称及学习了这门课的同学的个数

显示其年龄大于平均年龄的同学的名字

显示其学习的课程为第 1、2，4 或第 7 门课的同学的名字

显示其成员数最少为 3 个的班级的同学中年龄大于同班同学平均年龄的同学

统计各班级中年龄大于全校同学平均年龄的同学

扩展：（多表查询）

Mysql 多表查询详解_mysql多表查询-CSDN博客

// 多表查询 ( "两张表查询" )

MariaDB [hellodb]> select st.name,sc.courseid,sc.score from students st inner join scores sc on st.stuid=sc.stuid;

+-------------+----------+-------+

| name        | courseid | score |

+-------------+----------+-------+

| Shi Zhongyu |        2 |    77 |

| Shi Zhongyu |        6 |    93 |

| Shi Potian  |        2 |    47 |

| Shi Potian  |        5 |    97 |

| Xie Yanke   |        2 |    88 |

| Xie Yanke   |        6 |    75 |

| Ding Dian   |        5 |    71 |

| Ding Dian   |        2 |    89 |

| Yu Yutong   |        1 |    39 |

| Yu Yutong   |        7 |    63 |

| Shi Qing    |        1 |    96 |

| Xi Ren      |        1 |    86 |

| Xi Ren      |        7 |    83 |

| Lin Daiyu   |        4 |    57 |

| Lin Daiyu   |        3 |    93 |

+-------------+----------+-------+

15 rows in set (0.01 sec)

// 多表查询 ( "三张表查询" )

MariaDB [hellodb]> select st.name,co.course,sc.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.00 sec)