mysql入门之多实例实现和增删改查

mysql多实例实现

此实验是在基于源码编译安装mariadb之后进行的，如果不知道如何安装，请移步mysql入门之安装篇
数据库安装目录为/app/mysql/

创建多实例数据库的数据库目录和配置目录等相关目录

[root@ansible data]#mkdir /mysql/330{6,7,8}/{etc,data,log,socket,pid} -p
[root@ansible data]#tree /mysql/
/mysql/
├── 3306
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   └── socket
├── 3307
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   └── socket
└── 3308
    ├── data
    ├── etc
    ├── log
    ├── pid
    └── socket

准备数据库文件

[root@ansible ~]#cd /app/mysql/
[root@ansible mysql]#./scripts/mysql_install_db --datadir=/mysql/3306/data/ --user=mysql
[root@ansible mysql]#./scripts/mysql_install_db --datadir=/mysql/3307/data/ --user=mysql
[root@ansible mysql]#./scripts/mysql_install_db --datadir=/mysql/3308/data/ --user=mysql

准备配置文件

将配置文件放到上述规划的etc目录下，并将端口分别改为相应的端口，将socket文件地址指向相应的socket
以使用3307端口的实例配置为例

[root@ansible mysql]#sed -n '/^[^#]/p' 3307/etc/my.cnf 
[mysqld]
port=3307
datadir=/mysql/3307/data/
socket=/mysql/3307/socket/mysql.sock
innodb_file_per_table
symbolic-links=0
[mysqld_safe]
log-error=/mysql/3307/log/mariadb.log
pid-file=/mysql/3307/pid/mariadb.pid
!includedir /etc/my.cnf.d

将/mysql目录的所有者和所属组改为mysql

1	[root@ansible ~]#chown -R mysql.mysql /mysql

准备启动脚本

以使用3307端口的实例配置为例

[root@ansible ~]#cat /mysql/3306/mysqld 
#!/bin/bash
#chkconfig: 345 80 2
port=3307
mysql_user="root"
mysql_pwd="137226"
cmd_path="/app/mysql/bin"
mysql_basedir="/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
      printf "Starting MySQL...\n"
      ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf  &> /dev/null  &
    else
      printf "MySQL is running...\n"
      exit
    fi
}


function_stop_mysql()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
   fi
}


function_restart_mysql()
{
    printf "Restarting MySQL...\n"
    function_stop_mysql
    sleep 2
    function_start_mysql
}

case $1 in
start)
    function_start_mysql
;;
stop)
    function_stop_mysql
;;
restart)
    function_restart_mysql
;;
*)
    printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac

将服务脚本加入系统管理服务

[root@ansible ~]#cp /mysql/3306/mysqld /etc/init.d/mysqld3306
[root@ansible ~]#cp /mysql/3307/mysqld /etc/init.d/mysqld3307
[root@ansible ~]#cp /mysql/3308/mysqld /etc/init.d/mysqld3308
[root@ansible ~]#chkconfig --add mysqld3306
[root@ansible ~]#chkconfig --add mysqld3307
[root@ansible ~]#chkconfig --add mysqld3308
[root@ansible ~]#chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysqld         	0:off	1:off	2:on	3:on	4:on	5:on	6:off
mysqld3306     	0:off	1:off	2:off	3:on	4:on	5:on	6:off
mysqld3307     	0:off	1:off	2:off	3:on	4:on	5:on	6:off
mysqld3308     	0:off	1:off	2:off	3:on	4:on	5:on	6:off
netconsole     	0:off	1:off	2:off	3:off	4:off	5:off	6:off
network        	0:off	1:off	2:on	3:on	4:on	5:on	6:off

开启服务并连接

以3307端口实例演示

[root@ansible ~]#service mysqld3307 start
Starting MySQL...
[root@ansible ~]#mysql -S /mysql/3307/socket/mysql.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.19-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

mysql安全加固

以3307端口实例为例

1	[root@ansible mysql]#mysql_secure_installation -S /mysql/3307/socket/mysql.sock

此时，mysql数据库的多实例已经完成。

mysql数据库之增删改查

以下内容只展示命令，不展示执行结果，不然显得太过臃肿，结果可以在测试机自行测试

数据库和表管理

关于字符集，强烈推荐使用utf8mb4字符集
修改数据库实例的字符集方法为：在配置文件mysqld语句块中添加character-set-server=utf8mb4
修改客户端字符集的方法为：在配置文件mysql语句块添加default-character-set=utf8mb4
建议在配置文件中mysqld语句块中写入innodb_file_per_table语句，将每个表的数据单独存放，降低风险。

创建数据库create database

语法：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

1	MariaDB [(none)]> create database db_utf8mb4 character set utf8mb4;

查看mariadb支持的字符集

1	MariaDB [(none)]> show character set;

删除数据库

1	MariaDB [(none)]> drop database testdb;

创建表 CREATE TABLE

(1) 直接创建
(2) 通过查询现存表创建；新表会被直接插入查询而来的数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,…)] [table_options] [partition_options] select_statement
(3) 通过复制现存的表的表结构创建，但不复制数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }
注意：

1
2

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

字段的数据类型
1、整型

tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~32767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
bigint(m) 8个字节 范围(+-9.22x10的18次方)

2、浮点型(float和double)，近似值

1
2
3

float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位
设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位

3、定点数

在数据库中存放的是精确值,存为十进制
decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位
MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如，decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节
浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal——例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal

4、字符串(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：
• 1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，
所以char类型存储的字符串末尾不能有空格，varchar不限于此
• 2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar
是存入的实际字符数+1个字节（n< n>255)，所以varchar(4),存入3个字符将
占用4个字节
• 3.char类型的字符串检索速度要比varchar类型的快
varchar和text：
• 1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个
字节（n< n>255)，text是实际字符数+2个字节。
• 2.text类型不能有默认值
• 3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速
度快于text

5.二进制数据：BLOB
• BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，
而Blob是以二进制方式存储，不分大小写
• BLOB存储的数据只能整体读出
• TEXT可以指定字符集，BLOB不用指定字符集
6.日期时间类型
• 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 主键
• UNIQUE KEY 唯一键
• CHARACTER SET name 指定一个字符集
数值型
• AUTO_INCREMENT 自动递增，适用于整数类型
• UNSIGNED 无符号

创建表
直接创建

1	create table student (id int unsigned auto_increment primary key ,name varchar(10),sex enum('f','m') default 'm',age tinyint unsigned,mobile char(11),address varchar(50));

克隆另一张表，并且会将模板表的数据一起克隆

1	MariaDB [studentdb]> create table employee select * from student;

克隆另一张表的表结构，但不克隆表中数据

1	MariaDB [studentdb]> create table custom like student;

查看表结构

1 2	MariaDB [studentdb]> desc student; MariaDB [studentdb]> show columns from student;

查看表定义

1	MariaDB [studentdb]> show create table student;

查看表的信息

1	MariaDB [studentdb]> show table status like 'student'\G;

查看某个数据库中的所有表的信息

1	MariaDB [studentdb]> show table status from database_name\G;

删除表

1	MariaDB [studentdb]> drop table student;

表操作

修改表结构alter

ALTER TABLE ‘tbl_name’
字段：
添加字段：add
ADD col1 data_type [FIRST|AFTER col_name]
删除字段：drop
修改字段：
alter（默认值）, change（字段名）, modify（字段属性）
索引:
添加索引：add index
删除索引：drop index
表选项
修改:
查看表上的索引：SHOW INDEXES FROM [db_name.]tbl_name;
查看帮助：Help ALTER TABLE

更改字符集
更改库/表字符集

1	MariaDB [db_utf8mb4]> alter database/table name character set = utf8mb4;

更改字段的字符集

1	MariaDB [db_utf8mb4]> alter table table_name change column_name column_name_new character set utf8mb4

查看表状态

1	MariaDB [db_utf8mb4]> show table status like 'student'\G;

为表增加记录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 ...fffffffffffffffffffffffffffffffffffffffffffffffffffffff
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

查看表中的记录数

1	MariaDB [studentdb]> select count(*) from student;

向表中添加记录
使用第一种方法添加

1	MariaDB [studentdb]> insert into student (name,age,mobile,adress)values('msq',26,15244059931,'shandong'),('yfq',27,15065002205,'shandong');

使用第二种方法添加（不常用）

1	MariaDB [studentdb]> insert into student set name='mjc',age=1,address='shandong';

使用第三种方法添加，批量查询并将查询的结果插入另一个表

1	MariaDB [studentdb]> insert into custom select * from student;

删除表中的记录 delete

删除表中的记录时，一定要加限定条件，否则会清空整个表

1	MariaDB [studentdb]> delete from student where id=4;

清空表中的数据，但保留表的结构

1	MariaDB [studentdb]> truncate table student;

更改表中的记录 update

语法

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

例如

1	MariaDB [studentdb]> update student(table_name) set name='zsf' where id=2;

由于更改表中的记录时，容易出错，建议在客户端连接时加上-U|–safe-updates|–i-am-a-dummy选项，或者直接在配置文件mysql语句块中机上safe-updates

查找数据库的内容

语法

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]

字段显示可以使用别名：

1	col1 AS alias1, col2 AS alias2, ...

WHERE子句：指明过滤条件以实现“选择”的功能：

过滤条件：布尔型表达式
算术操作符：+, -, **, /, % 
比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=
BETWEEN min_num AND max_num ：在min_num与max_num之间的范围
IN (element1, element2, ...)：单个散列值
IS NULL：找出空值
IS NOT NULL：找出非空值

模糊匹配：
搜索名字列以m开头的记录，但是尽可能不要使用模糊匹配，会降低服务器性能。

1	MariaDB [studentdb]> select * from student where name like 'm%';

GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算
  avg(), max(), min(), count(), sum()
HAVING: 对分组聚合运算后的结果指定过滤条件
ORDER BY: 根据指定的字段对查询结果进行排序
  升序：ASC
  降序：DESC
LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制
对查询结果中的数据请求施加“锁”
  FOR UPDATE: 写锁，独占或排它锁，只有一个读和写
  LOCK IN SHARE MODE: 读锁，共享锁，同时多个读

select单表查询

使用distinct可以对查找到的结果进行去重，之后再显示。

MariaDB [studentdb]> select distinct sex  from student;
+------+
| sex  |
+------+
| m    |
| f    |
+------+
2 rows in set (0.00 sec)

MariaDB [studentdb]> select  sex  from student;
+------+
| sex  |
+------+
| m    |
| m    |
| m    |
| f    |
+------+
4 rows in set (0.00 sec)

导入测试数据库用于练习

1	[root@ansible ~]#mysql -p -S /mysql/3307/socket/mysql.sock < hellodb_innodb.sql

用于测试的表格如下

ariaDB [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 |
+-------+---------------+-----+--------+---------+-----------+

从中选出性别为女的人

MariaDB [hellodb]> select * from students where gender <> 'm'; #或者where gender = f;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     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 |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      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 |
+-------+---------------+-----+--------+---------+-----------+

统计表中记录的总数

MariaDB [hellodb]> select gender,count(gender) from students;
+--------+---------------+
| gender | count(gender) |
+--------+---------------+
| M      |            25 |
+--------+---------------+

分组：group by

1
2
3

GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算
avg(), max(), min(), count(), sum()
HAVING: 对分组聚合运算后的结果指定过滤条件

统计表中男性和女性人数

MMariaDB [hellodb]> select gender 性别,count(*) 人数 from students group by gender;
+--------+--------+
| 性别   | 人数   |
+--------+--------+
| F      |     10 |
| M      |     15 |
+--------+--------+

分组：统计每个班的人数

MariaDB [hellodb]> select classid,count(*) from students group by classid;
+---------+----------+
| classid | count(*) |
+---------+----------+
|    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,max(age) from students group by classid,gender;
+---------+--------+----------+
| classid | gender | max(age) |
+---------+--------+----------+
|    NULL | M      |      100 |
|       1 | F      |       20 |
|       1 | M      |       22 |
|       2 | M      |       53 |
|       3 | F      |       19 |
|       3 | M      |       26 |
|       4 | M      |       32 |
|       5 | M      |       46 |
|       6 | F      |       22 |
|       6 | M      |       23 |
|       7 | F      |       19 |
|       7 | M      |       23 |
+---------+--------+----------+

分别统计男女平均年龄

MariaDB [hellodb]> select gender 性别,avg(age) 平均年龄 from students group by gender;
+--------+--------------+
| 性别   | 平均年龄     |
+--------+--------------+
| F      |      19.0000 |
| M      |      33.0000 |
+--------+--------------+

只统计女生的平均年龄

MariaDB [hellodb]> select gender,avg(gender) from students group by gender having gender = 'M';
+--------+-------------+
| gender | avg(gender) |
+--------+-------------+
| M      |      2.0000 |
+--------+-------------+

MariaDB [hellodb]> select gender,avg(gender) from students where gender = 'm' group by gender;
+--------+-------------+
| gender | avg(gender) |
+--------+-------------+
| M      |      2.0000 |
+--------+-------------+

MariaDB [hellodb]> select gender,avg(age) from students where gender='M';
+--------+----------+
| gender | avg(age) |
+--------+----------+
| M      |  33.0000 |
+--------+----------+

统计班级号大于3的班级每个班年龄大于30岁的学生的人数

MariaDB [hellodb]> select classid,count(*) from students where age > 30 group by classid having classid > 3;
+---------+----------+
| classid | count(*) |
+---------+----------+
|       4 |        1 |
|       5 |        1 |
+---------+----------+
2 rows in set (0.00 sec)

ORDER BY: 根据指定的字段对查询结果进行排序

1 2	升序：ASC 降序：DESC

对学生表中的学生排序,并显示年龄最小的前三个学生

MariaDB [hellodb]> select * from students order by age limit 3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|    14 | Lu Wushuang |  17 | F      |       3 |      NULL |
|     8 | Lin Daiyu   |  17 | F      |       7 |      NULL |
|    19 | Xue Baochai |  18 | F      |       6 |      NULL |
+-------+-------------+-----+--------+---------+-----------+

对学生表中的学生排序,跳过年龄最小的前三个学生，并显示之后的四个学生

MariaDB [hellodb]> select * from students order by age limit 3,4;+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|    15 | Duan Yu      |  19 | M      |       4 |      NULL |
|    12 | Wen Qingqing |  19 | F      |       1 |      NULL |
|    10 | Yue Lingshan |  19 | F      |       3 |      NULL |
|     7 | Xi Ren       |  19 | F      |       3 |      NULL |
+-------+--------------+-----+--------+---------+-----------+

对表中班级排序后对年龄倒序

1	MariaDB [hellodb]> select * from students order by -classid desc,age desc;

select多表查询

两个表交叉查询

新增一张表

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  |  93 | F      |
+-----+---------------+-----+--------+

使用union将students表和teachers表纵向合并
union具有自动去重的效果，类似distinct取唯一值。

1	MariaDB [hellodb]> select stuid,name,age,gender from students union select * from teachers;

将s、t两张表交叉连接，cross join

1	MariaDB [hellodb]> select * from students cross join teachers;

取两个表的交集:内连接

MariaDB [hellodb]> select * from students s,teachers t where  s.teacherid=t.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 * from students s inner join teachers t on s.teacherid=t.tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
|    25 | Sun Dasheng | 100 | M      |    NULL |         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 classid,s.name,stuid,t.name,tid from students s inner join teachers t on t.tid=s.teacherid;
+---------+-------------+-------+---------------+-----+
| classid | name        | stuid | name          | tid |
+---------+-------------+-------+---------------+-----+
|       3 | Yu Yutong   |     5 | Song Jiang    |   1 |
|    NULL | Sun Dasheng |    25 | Song Jiang    |   1 |
|       2 | Shi Zhongyu |     1 | Miejue Shitai |   3 |
|       4 | Ding Dian   |     4 | Lin Chaoying  |   4 |
+---------+-------------+-------+---------------+-----+

左外连接：取出A表的全部记录以及B表中符合条件的记录。

MariaDB [hellodb]> select * from students s left 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      |
|     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 |    4 | Lin Chaoying  |   93 | F      |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |    1 | Song Jiang    |   45 | M      |
|     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 |         1 |    1 | Song Jiang    |   45 | M      |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+

右外连接：取出B表的全部记录以及A表中符合条件的记录。

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

取出只在A表不在B表中的记录

MariaDB [hellodb]> select * from students s left join teachers t on s.teacherid=t.tid where 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   |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+

取出只在B中不在A中的记录

MariaDB [hellodb]> select t.* from students s right join teachers t on s.teacherid=t.tid where s.teacherid is null;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   2 | Zhang Sanfeng |  94 | M      |
+-----+---------------+-----+--------+

子查询

选出年龄大于学生平均年龄的老师的记录

MariaDB [hellodb]> select * from teachers where age > (select avg(age) from students);
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+

完全外连接:左外连接union右外连接

MariaDB [hellodb]> select * from students s left outer join teachers t  on s.teacherid=t.tid union 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      |
|     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 |    4 | Lin Chaoying  |   93 | F      |
|     5 | Yu Yutong     |   26 | M      |       3 |         1 |    1 | Song Jiang    |   45 | M      |
|     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 |         1 |    1 | Song Jiang    |   45 | M      |
|  NULL | NULL          | NULL | NULL   |    NULL |      NULL |    2 | Zhang Sanfeng |   94 | M      |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+

取A、B并集去除交集的部分

MariaDB [hellodb]> select * from (select stuid,s.name student_name,s.age student_age,s.gender student_gender,classid,teacherid,tid,t.name,t.age,t.gender from students s left outer join teachers t on s.teacherid=t.tid union select * from students s right outer join teachers t on s.teacherid=t.tid) f where f.teacherid is null or f.tid is null;
+-------+---------------+-------------+----------------+---------+-----------+------+---------------+------+--------+
| stuid | student_name  | student_age | student_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   |
|  NULL | NULL          |        NULL | NULL           |    NULL |      NULL |    2 | Zhang Sanfeng |   94 | M      |
+-------+---------------+-------------+----------------+---------+-----------+------+---------------+------+--------+