@[TOC](第二十三章 MySQL数据库 手册3 单表查询和多表查询)
实验三：单表查询和多表查询
⽬的
单表查询和多表查询，管理数据库。
单表查询和多表查询，管理数据库。
前提
linux系统，已安装好数据库。
安装mariadb
7 ~]# vim /etc/yum.repos.d/mariadb-10.2.repo
# MariaDB 10.2 CentOS repository list - created 2022-05-14 05:44 UTC
# https://mariadb.org/download/
[mariadb]
name = MariaDB
baseurl = https://mirrors.aliyun.com/mariadb/yum/10.2/centos7-amd64
gpgkey=https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
7 ~]# yum install -y mariadb-server
7 ~]# systemctl start mariadb
#查看端口
7 ~]# ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
...
LISTEN 0 80 :::3306 :::*
...
安全加固，root⼝令为空。
设置数据库管理员root口令
禁止root远程登录
删除anonymous用户帐号
删除test数据库
7 ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): #回车
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] n
... skipping.
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
空⼝令，登录。
7 ~]# mysql -uroot -p
Enter password: #回车即可
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.2.43-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
准备测试数据库： 下载：hellodb_innodb.sql
从windows导⼊centos：
7 ~]# rz
#查看hellodb_innodb.sql是否导入
7 ~]# [root@centos7 ~]# ls
anaconda-ks.cfg Documents hellodb_innodb.sql Music Public Videos
Desktop Downloads initial-setup-ks.cfg Pictures Templates
导⼊数据库：
7 ~]# mysql < hellodb_innodb.sql
7 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.2.43-MariaDB MariaDB Server
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)]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
测试数据库：hellodb
MariaDB [(none)]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
测试表：student
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)
命令介绍
- 单表查询，DQL语句 SELECT
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：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算avg(), max(), min(), count(), sum()
HAVING: 对分组聚合运算后的结果指定过滤条件
ORDER BY: 根据指定的字段对查询结果进行排序
升序：ASC
降序：DESC
LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制
对查询结果中的数据请求施加“锁”
FOR UPDATE: 写锁，独占或排它锁，只有一个读和写
LOCK IN SHARE MODE: 读锁，共享锁，同时多个读
【例1】数据库查询select。
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)
MariaDB [hellodb]> select stuid,name,age from students;
+-------+---------------+-----+
| stuid | name | age |
+-------+---------------+-----+
| 1 | Shi Zhongyu | 22 |
| 2 | Shi Potian | 22 |
| 3 | Xie Yanke | 53 |
| 4 | Ding Dian | 32 |
| 5 | Yu Yutong | 26 |
| 6 | Shi Qing | 46 |
| 7 | Xi Ren | 19 |
| 8 | Lin Daiyu | 17 |
| 9 | Ren Yingying | 20 |
| 10 | Yue Lingshan | 19 |
| 11 | Yuan Chengzhi | 23 |
| 12 | Wen Qingqing | 19 |
| 13 | Tian Boguang | 33 |
| 14 | Lu Wushuang | 17 |
| 15 | Duan Yu | 19 |
| 16 | Xu Zhu | 21 |
| 17 | Lin Chong | 25 |
| 18 | Hua Rong | 23 |
| 19 | Xue Baochai | 18 |
| 20 | Diao Chan | 19 |
| 21 | Huang Yueying | 22 |
| 22 | Xiao Qiao | 20 |
| 23 | Ma Chao | 23 |
| 24 | Xu Xian | 27 |
| 25 | Sun Dasheng | 100 |
+-------+---------------+-----+
25 rows in set (0.00 sec)
【例2】给字段加别名。
MariaDB [hellodb]> select stuid as 学员编号,name 姓名,age 年龄 from students;
+--------------+---------------+--------+
| 学员编号 | 姓名 | 年龄 |
+--------------+---------------+--------+
| 1 | Shi Zhongyu | 22 |
| 2 | Shi Potian | 22 |
| 3 | Xie Yanke | 53 |
| 4 | Ding Dian | 32 |
| 5 | Yu Yutong | 26 |
| 6 | Shi Qing | 46 |
| 7 | Xi Ren | 19 |
| 8 | Lin Daiyu | 17 |
| 9 | Ren Yingying | 20 |
| 10 | Yue Lingshan | 19 |
| 11 | Yuan Chengzhi | 23 |
| 12 | Wen Qingqing | 19 |
| 13 | Tian Boguang | 33 |
| 14 | Lu Wushuang | 17 |
| 15 | Duan Yu | 19 |
| 16 | Xu Zhu | 21 |
| 17 | Lin Chong | 25 |
| 18 | Hua Rong | 23 |
| 19 | Xue Baochai | 18 |
| 20 | Diao Chan | 19 |
| 21 | Huang Yueying | 22 |
| 22 | Xiao Qiao | 20 |
| 23 | Ma Chao | 23 |
| 24 | Xu Xian | 27 |
| 25 | Sun Dasheng | 100 |
+--------------+---------------+--------+
25 rows in set (0.00 sec)
说明：as可以不写。
【例3】给表加别名。单表，意义不⼤，主要⽤于多表查询。
MariaDB [hellodb]> select stuid as 学员编号,name 姓名,age 年龄 from students as s;
+--------------+---------------+--------+
| 学员编号 | 姓名 | 年龄 |
+--------------+---------------+--------+
| 1 | Shi Zhongyu | 22 |
| 2 | Shi Potian | 22 |
| 3 | Xie Yanke | 53 |
| 4 | Ding Dian | 32 |
| 5 | Yu Yutong | 26 |
| 6 | Shi Qing | 46 |
| 7 | Xi Ren | 19 |
| 8 | Lin Daiyu | 17 |
| 9 | Ren Yingying | 20 |
| 10 | Yue Lingshan | 19 |
| 11 | Yuan Chengzhi | 23 |
| 12 | Wen Qingqing | 19 |
| 13 | Tian Boguang | 33 |
| 14 | Lu Wushuang | 17 |
| 15 | Duan Yu | 19 |
| 16 | Xu Zhu | 21 |
| 17 | Lin Chong | 25 |
| 18 | Hua Rong | 23 |
| 19 | Xue Baochai | 18 |
| 20 | Diao Chan | 19 |
| 21 | Huang Yueying | 22 |
| 22 | Xiao Qiao | 20 |
| 23 | Ma Chao | 23 |
| 24 | Xu Xian | 27 |
| 25 | Sun Dasheng | 100 |
+--------------+---------------+--------+
25 rows in set (0.00 sec)
【例4】查询⾏，20岁以下的学⽣和⼥⽣。
MariaDB [hellodb]> select * from students where age<=20;
+-------+--------------+-----+--------+---------+-----------+
| 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 |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
+-------+--------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
⼥⽣：
MariaDB [hellodb]> select * from students 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 |
+-------+---------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
【例5】查询⾏，名字以s开头。
MariaDB [hellodb]> select * from students where name like 's%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+-------------+-----+--------+---------+-----------+
4 rows in set (0.00 sec)
【例6】查询⾏，班级为空的。
有独特的语法：
MariaDB [hellodb]> select * from students where classid=null;
Empty set (0.00 sec)
#查看classid为空
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.00 sec)
#查看classid不为空
MariaDB [hellodb]> select * from students where classid is not null;
+-------+---------------+-----+--------+---------+-----------+
| 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 |
+-------+---------------+-----+--------+---------+-----------+
23 rows in set (0.00 sec)
【例7】查询⾏，年龄区间。
MariaDB [hellodb]> select * from students where age>=20 and age<=30;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 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 |
+-------+---------------+-----+--------+---------+-----------+
12 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students where age between 20 and 30;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 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 |
+-------+---------------+-----+--------+---------+-----------+
12 rows in set (0.00 sec)
【例8】登录测试。
创建表users：
MariaDB [hellodb]> create table users (id int primary key,name char(20),password char(30));
Query OK, 0 rows affected (0.01 sec)
添加记录：
MariaDB [hellodb]> insert users values (1,'admin','magedu');
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> insert users values (2,'root','centos');
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from users;
+----+-------+----------+
| id | name | password |
+----+-------+----------+
| 1 | admin | magedu |
| 2 | root | centos |
+----+-------+----------+
2 rows in set (0.00 sec)
测试⽤户名和密码：
MariaDB [hellodb]> select * from users where name='admin' and password='magedu';
+----+-------+----------+
| id | name | password |
+----+-------+----------+
| 1 | admin | magedu |
+----+-------+----------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from users where name='admin' and password='mageduss';
Empty set (0.00 sec)
黑客，绕过密码！著名攻击⽅式：sql注入！
MariaDB [hellodb]> select * from users where name='admin' and password='' or '1'='1';
+----+-------+----------+
| id | name | password |
+----+-------+----------+
| 1 | admin | magedu |
| 2 | root | centos |
+----+-------+----------+
2 rows in set (0.00 sec)
【例9】简单运算。
MariaDB [hellodb]> select 1+2;
+-----+
| 1+2 |
+-----+
| 3 |
+-----+
1 row in set (0.00 sec)
MariaDB [hellodb]> select 1*2;
+-----+
| 1*2 |
+-----+
| 2 |
+-----+
1 row in set (0.00 sec)
【例10】逻辑操作，三个年龄段。
#查看年龄是（18，20，22）的信息
MariaDB [hellodb]> select * from students where age in (18,20,22);
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
+-------+---------------+-----+--------+---------+-----------+
6 rows in set (0.00 sec)
【例11】查询操作，姓名以s开头和包含s的。
#查询：以s开头
MariaDB [hellodb]> select * from students where name rlike '^s.*';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+-------------+-----+--------+---------+-----------+
4 rows in set (0.00 sec)
#查询：包含s
MariaDB [hellodb]> select * from students where name rlike 's.*';
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+--------------+-----+--------+---------+-----------+
6 rows in set (0.00 sec)
【例12】数据库查询,去重distinct。
MariaDB [hellodb]> select distinct gender from students;
+--------+
| gender |
+--------+
| M |
| F |
+--------+
2 rows in set (0.00 sec)
MariaDB [hellodb]> select distinct age from students;
+-----+
| age |
+-----+
| 22 |
| 53 |
| 32 |
| 26 |
| 46 |
| 19 |
| 17 |
| 20 |
| 23 |
| 33 |
| 21 |
| 25 |
| 18 |
| 27 |
| 100 |
+-----+
15 rows in set (0.00 sec)
【例13】数据库查询,聚合函数，group by。
MariaDB [hellodb]> select avg(age) from students;
+----------+
| avg(age) |
+----------+
| 27.4000 |
+----------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select avg(age) from students where gender='f';
+----------+
| avg(age) |
+----------+
| 19.0000 |
+----------+
1 row in set (0.00 sec)
先分类，再统计。
MariaDB [hellodb]> select gender,avg(age) from students group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| F | 19.0000 |
| M | 33.0000 |
+--------+----------+
2 rows in set (0.00 sec)
【例14】数据库查询,聚合函数，group by。统计数量。
每个性别年龄的最⼤值：
MariaDB [hellodb]> select gender,max(age) from students group by gender;
+--------+----------+
| gender | max(age) |
+--------+----------+
| F | 22 |
| M | 100 |
+--------+----------+
2 rows in set (0.00 sec)
不同性别的⼈数：
MariaDB [hellodb]> select gender,count(*) from students group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| F | 10 |
| M | 15 |
+--------+----------+
2 rows in set (0.00 sec)
别名：
MariaDB [hellodb]> select gender 性别,count(*) 数量 from students group by gender;
+--------+--------+
| 性别 | 数量 |
+--------+--------+
| F | 10 |
| M | 15 |
+--------+--------+
2 rows in set (0.00 sec)
【例15】统计数量。
学⽣的⼈数：count统计不为空的。
MariaDB [hellodb]> select count(stuid) from students;
+--------------+
| count(stuid) |
+--------------+
| 25 |
+--------------+
1 row in set (0.00 sec)
男⽣的数量：
MariaDB [hellodb]> select gender,count(*) from students group by gender having gender='m';
+--------+----------+
| gender | count(*) |
+--------+----------+
| M | 15 |
+--------+----------+
1 row in set (0.00 sec)
说明：必须⽤having
MariaDB [hellodb]> select gender,count(*) from students where gender='m';
+--------+----------+
| gender | count(*) |
+--------+----------+
| M | 15 |
+--------+----------+
1 row in set (0.00 sec)
说明：先分组，再过滤。
【例16】统计数量，每个班级男⽣和⼥⽣的平均年龄。
MariaDB [hellodb]> select classid,gender,avg(age) from students group by classid,gender;
+---------+--------+----------+
| classid | gender | avg(age) |
+---------+--------+----------+
| NULL | M | 63.5000 |
| 1 | F | 19.5000 |
| 1 | M | 21.5000 |
| 2 | M | 36.0000 |
| 3 | F | 18.3333 |
| 3 | M | 26.0000 |
| 4 | M | 24.7500 |
| 5 | M | 46.0000 |
| 6 | F | 20.0000 |
| 6 | M | 23.0000 |
| 7 | F | 18.0000 |
| 7 | M | 23.0000 |
+---------+--------+----------+
12 rows in set (0.00 sec)
【例17】数据库查询,排序升序：ASC，降序：DESC。
按年龄排序：
MariaDB [hellodb]> select age from students;
+-----+
| age |
+-----+
| 22 |
| 22 |
| 53 |
| 32 |
| 26 |
| 46 |
| 19 |
| 17 |
| 20 |
| 19 |
| 23 |
| 19 |
| 33 |
| 17 |
| 19 |
| 21 |
| 25 |
| 23 |
| 18 |
| 19 |
| 22 |
| 20 |
| 23 |
| 27 |
| 100 |
+-----+
25 rows in set (0.00 sec)
按年龄升序：
MariaDB [hellodb]> select age from students order by age;
+-----+
| age |
+-----+
| 17 |
| 17 |
| 18 |
| 19 |
| 19 |
| 19 |
| 19 |
| 19 |
| 20 |
| 20 |
| 21 |
| 22 |
| 22 |
| 22 |
| 23 |
| 23 |
| 23 |
| 25 |
| 26 |
| 27 |
| 32 |
| 33 |
| 46 |
| 53 |
| 100 |
+-----+
25 rows in set (0.00 sec)
按年龄降序：
MariaDB [hellodb]> select age from students order by age desc;
+-----+
| age |
+-----+
| 100 |
| 53 |
| 46 |
| 33 |
| 32 |
| 27 |
| 26 |
| 25 |
| 23 |
| 23 |
| 23 |
| 22 |
| 22 |
| 22 |
| 21 |
| 20 |
| 20 |
| 19 |
| 19 |
| 19 |
| 19 |
| 19 |
| 18 |
| 17 |
| 17 |
+-----+
25 rows in set (0.00 sec)
【例18】对班级的平均年龄排序：
MariaDB [hellodb]> select classid,gender,avg(age) from students group by classid,gender order by avg(age);
+---------+--------+----------+
| classid | gender | avg(age) |
+---------+--------+----------+
| 7 | F | 18.0000 |
| 3 | F | 18.3333 |
| 1 | F | 19.5000 |
| 6 | F | 20.0000 |
| 1 | M | 21.5000 |
| 7 | M | 23.0000 |
| 6 | M | 23.0000 |
| 4 | M | 24.7500 |
| 3 | M | 26.0000 |
| 2 | M | 36.0000 |
| 5 | M | 46.0000 |
| NULL | M | 63.5000 |
+---------+--------+----------+
12 rows in set (0.00 sec)
倒序：
MariaDB [hellodb]> select classid,gender,avg(age) from students group by classid,gender order by avg(age) desc;
+---------+--------+----------+
| classid | gender | avg(age) |
+---------+--------+----------+
| NULL | M | 63.5000 |
| 5 | M | 46.0000 |
| 2 | M | 36.0000 |
| 3 | M | 26.0000 |
| 4 | M | 24.7500 |
| 7 | M | 23.0000 |
| 6 | M | 23.0000 |
| 1 | M | 21.5000 |
| 6 | F | 20.0000 |
| 1 | F | 19.5000 |
| 3 | F | 18.3333 |
| 7 | F | 18.0000 |
+---------+--------+----------+
12 rows in set (0.00 sec)
别名：
MariaDB [hellodb]> select classid,gender,avg(age) avg_age from students group by classid,gender order by avg_age desc;
+---------+--------+---------+
| classid | gender | avg_age |
+---------+--------+---------+
| NULL | M | 63.5000 |
| 5 | M | 46.0000 |
| 2 | M | 36.0000 |
| 3 | M | 26.0000 |
| 4 | M | 24.7500 |
| 6 | M | 23.0000 |
| 7 | M | 23.0000 |
| 1 | M | 21.5000 |
| 6 | F | 20.0000 |
| 1 | F | 19.5000 |
| 3 | F | 18.3333 |
| 7 | F | 18.0000 |
+---------+--------+---------+
12 rows in set (0.00 sec)
【例19】多项排序，⼀个班的正序排年龄。
MariaDB [hellodb]> select classid,age from students order by classid,age;
+---------+-----+
| classid | age |
+---------+-----+
| NULL | 27 |
| NULL | 100 |
| 1 | 19 |
| 1 | 20 |
| 1 | 21 |
| 1 | 22 |
| 2 | 22 |
| 2 | 33 |
| 2 | 53 |
| 3 | 17 |
| 3 | 19 |
| 3 | 19 |
| 3 | 26 |
| 4 | 19 |
| 4 | 23 |
| 4 | 25 |
| 4 | 32 |
| 5 | 46 |
| 6 | 18 |
| 6 | 20 |
| 6 | 22 |
| 6 | 23 |
| 7 | 17 |
| 7 | 19 |
| 7 | 23 |
+---------+-----+
25 rows in set (0.00 sec)
班级倒序，年龄正序。
MariaDB [hellodb]> select classid,age from students order by classid desc,age;
+---------+-----+
| classid | age |
+---------+-----+
| 7 | 17 |
| 7 | 19 |
| 7 | 23 |
| 6 | 18 |
| 6 | 20 |
| 6 | 22 |
| 6 | 23 |
| 5 | 46 |
| 4 | 19 |
| 4 | 23 |
| 4 | 25 |
| 4 | 32 |
| 3 | 17 |
| 3 | 19 |
| 3 | 19 |
| 3 | 26 |
| 2 | 22 |
| 2 | 33 |
| 2 | 53 |
| 1 | 19 |
| 1 | 20 |
| 1 | 21 |
| 1 | 22 |
| NULL | 27 |
| NULL | 100 |
+---------+-----+
25 rows in set (0.00 sec)
【例20】数据库查询,对查询的结果进⾏输出⾏数数量限制limit。
查询前3个：
MariaDB [hellodb]> 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)
查询跳过前2个的前3个：
MariaDB [hellodb]> select * from students order by age desc limit 2,3;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
+-------+--------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
- 多表查询SQL JOINS
交叉连接：笛卡尔乘积
内连接：
等值连接：让表之间的字段以“等值”建立连接关系；
不等值连接
自然连接:去掉重复列的等值连接
自连接
外连接：
左外连接：FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
右外连接：FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
子查询：在查询语句嵌套着查询语句，性能较差
基于某语句的查询结果再次进行的查询
用在WHERE子句中的子查询
用于比较表达式中的子查询；子查询仅能返回单个值
SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);
用于IN中的子查询：子查询应该单键查询并返回一个或多个值从构成列表
SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
用于EXISTS
用于FROM子句中的子查询
使用格式：SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;
联合查询：UNION
SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;
【例21】多表查询inner join，纵向合并，字段数必须⼀样，应该⼀⼀对应。
MariaDB [hellodb]> select stuid,name,age from students union select tid,name,age from teachers;
+-------+---------------+-----+
| stuid | name | age |
+-------+---------------+-----+
| 1 | Shi Zhongyu | 22 |
| 2 | Shi Potian | 22 |
| 3 | Xie Yanke | 53 |
| 4 | Ding Dian | 32 |
| 5 | Yu Yutong | 26 |
| 6 | Shi Qing | 46 |
| 7 | Xi Ren | 19 |
| 8 | Lin Daiyu | 17 |
| 9 | Ren Yingying | 20 |
| 10 | Yue Lingshan | 19 |
| 11 | Yuan Chengzhi | 23 |
| 12 | Wen Qingqing | 19 |
| 13 | Tian Boguang | 33 |
| 14 | Lu Wushuang | 17 |
| 15 | Duan Yu | 19 |
| 16 | Xu Zhu | 21 |
| 17 | Lin Chong | 25 |
| 18 | Hua Rong | 23 |
| 19 | Xue Baochai | 18 |
| 20 | Diao Chan | 19 |
| 21 | Huang Yueying | 22 |
| 22 | Xiao Qiao | 20 |
| 23 | Ma Chao | 23 |
| 24 | Xu Xian | 27 |
| 25 | Sun Dasheng | 100 |
| 1 | Song Jiang | 45 |
| 2 | Zhang Sanfeng | 94 |
| 3 | Miejue Shitai | 77 |
| 4 | Lin Chaoying | 93 |
+-------+---------------+-----+
29 rows in set (0.00 sec)
【例22】多表查询inner join，纵向合并，⾃动去重。
MariaDB [hellodb]> select tid,name,age from teachers union select tid,name,age from teachers;
+-----+---------------+-----+
| tid | name | age |
+-----+---------------+-----+
| 1 | Song Jiang | 45 |
| 2 | Zhang Sanfeng | 94 |
| 3 | Miejue Shitai | 77 |
| 4 | Lin Chaoying | 93 |
+-----+---------------+-----+
4 rows in set (0.00 sec)
【例23】多表查询，横向合并cross join。
MariaDB [hellodb]> select * from students cross join teachers;
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 1 | Song Jiang | 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 2 | Zhang Sanfeng | 94 | M
...
| 24 | Xu Xian | 27 | M | NULL | NULL | 4 | Lin Chaoying | 93 | F |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | 1 | Song Jiang | 45 | M |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | 3 | Miejue Shitai | 77 | F |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | 4 | Lin Chaoying | 93 | F |
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
100 rows in set (0.00 sec)
【例24】多表查询，横向合并cross join，过滤交集，内连接inner join。
students.teacherid=teachers.tid条件：
MariaDB [hellodb]> select * from students inner join teachers on students.teacherid=teachers.tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.00 sec)
【例25】别名。
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)
【例26】左外链接，left outer join。
MariaDB [hellodb]> select * from students left outer join teachers on students.teacherid=teachers.tid;
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 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 | NULL | NULL | NULL | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
25 rows in set (0.00 sec)
【例27】右外链接，right outer join。
MariaDB [hellodb]> select * from students right outer join teachers on students.teacherid=teachers.tid;
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
【例28】完全外链接，full outer joion.
MariaDB [hellodb]> select * from students left outer join teachers on students.teacherid=teachers.tid union select * from students right outer join teachers on students. teacherid=teachers.tid;
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 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 | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
26 rows in set (0.00 sec)
【例29】第三张表，每个学⽣的姓名每个科⽬的考试成绩。
两张表：
MariaDB [hellodb]> select st.stuid,st.name,sc.score,sc.courseid from students as st inner join scores as sc on st.stuid=sc.stuid;
+-------+-------------+-------+----------+
| stuid | name | score | courseid |
+-------+-------------+-------+----------+
| 1 | Shi Zhongyu | 77 | 2 |
| 1 | Shi Zhongyu | 93 | 6 |
| 2 | Shi Potian | 47 | 2 |
| 2 | Shi Potian | 97 | 5 |
| 3 | Xie Yanke | 88 | 2 |
| 3 | Xie Yanke | 75 | 6 |
| 4 | Ding Dian | 71 | 5 |
| 4 | Ding Dian | 89 | 2 |
| 5 | Yu Yutong | 39 | 1 |
| 5 | Yu Yutong | 63 | 7 |
| 6 | Shi Qing | 96 | 1 |
| 7 | Xi Ren | 86 | 1 |
| 7 | Xi Ren | 83 | 7 |
| 8 | Lin Daiyu | 57 | 4 |
| 8 | Lin Daiyu | 93 | 3 |
+-------+-------------+-------+----------+
15 rows in set (0.00 sec)
三张表：
MariaDB [hellodb]> select st.stuid,st.name,sc.score,sc.courseid,co.course from students as st inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.courseid;
+-------+-------------+-------+----------+----------------+
| stuid | name | score | courseid | course |
+-------+-------------+-------+----------+----------------+
| 1 | Shi Zhongyu | 77 | 2 | Kuihua Baodian |
| 1 | Shi Zhongyu | 93 | 6 | Weituo Zhang |
| 2 | Shi Potian | 47 | 2 | Kuihua Baodian |
| 2 | Shi Potian | 97 | 5 | Daiyu Zanghua |
| 3 | Xie Yanke | 88 | 2 | Kuihua Baodian |
| 3 | Xie Yanke | 75 | 6 | Weituo Zhang |
| 4 | Ding Dian | 71 | 5 | Daiyu Zanghua |
| 4 | Ding Dian | 89 | 2 | Kuihua Baodian |
| 5 | Yu Yutong | 39 | 1 | Hamo Gong |
| 5 | Yu Yutong | 63 | 7 | Dagou Bangfa |
| 6 | Shi Qing | 96 | 1 | Hamo Gong |
| 7 | Xi Ren | 86 | 1 | Hamo Gong |
| 7 | Xi Ren | 83 | 7 | Dagou Bangfa |
| 8 | Lin Daiyu | 57 | 4 | Taiji Quan |
| 8 | Lin Daiyu | 93 | 3 | Jinshe Jianfa |
+-------+-------------+-------+----------+----------------+
15 rows in set (0.00 sec)
删除courseid
MariaDB [hellodb]> select st.stuid,st.name,sc.score,co.course from students as st inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.courseid;
+-------+-------------+-------+----------------+
| stuid | name | score | course |
+-------+-------------+-------+----------------+
| 1 | Shi Zhongyu | 77 | Kuihua Baodian |
| 1 | Shi Zhongyu | 93 | Weituo Zhang |
| 2 | Shi Potian | 47 | Kuihua Baodian |
| 2 | Shi Potian | 97 | Daiyu Zanghua |
| 3 | Xie Yanke | 88 | Kuihua Baodian |
| 3 | Xie Yanke | 75 | Weituo Zhang |
| 4 | Ding Dian | 71 | Daiyu Zanghua |
| 4 | Ding Dian | 89 | Kuihua Baodian |
| 5 | Yu Yutong | 39 | Hamo Gong |
| 5 | Yu Yutong | 63 | Dagou Bangfa |
| 6 | Shi Qing | 96 | Hamo Gong |
| 7 | Xi Ren | 86 | Hamo Gong |
| 7 | Xi Ren | 83 | Dagou Bangfa |
| 8 | Lin Daiyu | 57 | Taiji Quan |
| 8 | Lin Daiyu | 93 | Jinshe Jianfa |
+-------+-------------+-------+----------------+
15 rows in set (0.00 sec)
【例30】⾃连接：查找员⼯的姓名和上司的姓名。员⼯和上司在同⼀张表⾥。
创建员⼯表：
MariaDB [hellodb]> create table employee select stuid id,name from students;
Query OK, 25 rows affected (0.00 sec)
Records: 25 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> select * from employee;
+----+---------------+
| id | name |
+----+---------------+
| 1 | Shi Zhongyu |
| 2 | Shi Potian |
| 3 | Xie Yanke |
| 4 | Ding Dian |
| 5 | Yu Yutong |
| 6 | Shi Qing |
| 7 | Xi Ren |
| 8 | Lin Daiyu |
| 9 | Ren Yingying |
| 10 | Yue Lingshan |
| 11 | Yuan Chengzhi |
| 12 | Wen Qingqing |
| 13 | Tian Boguang |
| 14 | Lu Wushuang |
| 15 | Duan Yu |
| 16 | Xu Zhu |
| 17 | Lin Chong |
| 18 | Hua Rong |
| 19 | Xue Baochai |
| 20 | Diao Chan |
| 21 | Huang Yueying |
| 22 | Xiao Qiao |
| 23 | Ma Chao |
| 24 | Xu Xian |
| 25 | Sun Dasheng |
+----+---------------+
25 rows in set (0.01 sec)
加⼊领导的列：
MariaDB [hellodb]> alter table employee add leaderid int;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> select * from employee;
+----+---------------+----------+
| id | name | leaderid |
+----+---------------+----------+
| 1 | Shi Zhongyu | NULL |
| 2 | Shi Potian | NULL |
| 3 | Xie Yanke | NULL |
| 4 | Ding Dian | NULL |
| 5 | Yu Yutong | NULL |
| 6 | Shi Qing | NULL |
| 7 | Xi Ren | NULL |
| 8 | Lin Daiyu | NULL |
| 9 | Ren Yingying | NULL |
| 10 | Yue Lingshan | NULL |
| 11 | Yuan Chengzhi | NULL |
| 12 | Wen Qingqing | NULL |
| 13 | Tian Boguang | NULL |
| 14 | Lu Wushuang | NULL |
| 15 | Duan Yu | NULL |
| 16 | Xu Zhu | NULL |
| 17 | Lin Chong | NULL |
| 18 | Hua Rong | NULL |
| 19 | Xue Baochai | NULL |
| 20 | Diao Chan | NULL |
| 21 | Huang Yueying | NULL |
| 22 | Xiao Qiao | NULL |
| 23 | Ma Chao | NULL |
| 24 | Xu Xian | NULL |
| 25 | Sun Dasheng | NULL |
+----+---------------+----------+
25 rows in set (0.00 sec)
修改领导列的值：如有⾮主键不能修改的保护，请执⾏命令：
MariaDB [hellodb]> update employee set leaderid=1 where id<=5;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without
a WHERE that uses a KEY column
MariaDB [hellodb]> SET SQL_SAFE_UPDATES=0;
Query OK, 0 rows affected (0.00 sec)
修改领导列的值：
MariaDB [hellodb]> update employee set leaderid=1 where id<=5;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0
MariaDB [hellodb]> update employee set leaderid=2 where id>=6 and id<=16;
Query OK, 11 rows affected (0.00 sec)
Rows matched: 11 Changed: 11 Warnings: 0
MariaDB [hellodb]> update employee set leaderid=3 where id>=17;
Query OK, 9 rows affected (0.00 sec)
Rows matched: 9 Changed: 9 Warnings: 0
修改后的表：
MariaDB [hellodb]> select * from employee;
+----+---------------+----------+
| id | name | leaderid |
+----+---------------+----------+
| 1 | Shi Zhongyu | 1 |
| 2 | Shi Potian | 1 |
| 3 | Xie Yanke | 1 |
| 4 | Ding Dian | 1 |
| 5 | Yu Yutong | 1 |
| 6 | Shi Qing | 2 |
| 7 | Xi Ren | 2 |
| 8 | Lin Daiyu | 2 |
| 9 | Ren Yingying | 2 |
| 10 | Yue Lingshan | 2 |
| 11 | Yuan Chengzhi | 2 |
| 12 | Wen Qingqing | 2 |
| 13 | Tian Boguang | 2 |
| 14 | Lu Wushuang | 2 |
| 15 | Duan Yu | 2 |
| 16 | Xu Zhu | 2 |
| 17 | Lin Chong | 3 |
| 18 | Hua Rong | 3 |
| 19 | Xue Baochai | 3 |
| 20 | Diao Chan | 3 |
| 21 | Huang Yueying | 3 |
| 22 | Xiao Qiao | 3 |
| 23 | Ma Chao | 3 |
| 24 | Xu Xian | 3 |
| 25 | Sun Dasheng | 3 |
+----+---------------+----------+
25 rows in set (0.00 sec)
查询：定义为两张表。
MariaDB [hellodb]> select emp.name as emp_name,leader.name as leader_name from employee as emp inner join employee as leader on emp.leaderid=leader.id;
+---------------+-------------+
| emp_name | leader_name |
+---------------+-------------+
| Shi Zhongyu | Shi Zhongyu |
| Shi Potian | Shi Zhongyu |
| Xie Yanke | Shi Zhongyu |
| Ding Dian | Shi Zhongyu |
| Yu Yutong | Shi Zhongyu |
| Shi Qing | Shi Potian |
| Xi Ren | Shi Potian |
| Lin Daiyu | Shi Potian |
| Ren Yingying | Shi Potian |
| Yue Lingshan | Shi Potian |
| Yuan Chengzhi | Shi Potian |
| Wen Qingqing | Shi Potian |
| Tian Boguang | Shi Potian |
| Lu Wushuang | Shi Potian |
| Duan Yu | Shi Potian |
| Xu Zhu | Shi Potian |
| Lin Chong | Xie Yanke |
| Hua Rong | Xie Yanke |
| Xue Baochai | Xie Yanke |
| Diao Chan | Xie Yanke |
| Huang Yueying | Xie Yanke |
| Xiao Qiao | Xie Yanke |
| Ma Chao | Xie Yanke |
| Xu Xian | Xie Yanke |
| Sun Dasheng | Xie Yanke |
+---------------+-------------+
25 rows in set (0.00 sec)
