上一章内容简单介绍了对单个数据库表的操作，本章内容将详细介绍关于多表查询的方法和技乔，以及关于MySQL查询语句中关于的一些函数的用法。
一、多表组合查询
1.1 外连接
SQL语句可以通过外连接的方式对多表进行联合查找，外连接右分为做链接和有链接之分，其使用方法如下；
左外连接语法：select 字段 from tb_a left join tb_b on 链接条件
注：tb_a表是主表，tb_b是从表，其中tb_a作为主表显示全部内容，若表中无数据显示则显示为null。
右链接
语法：select 字段 from tb_a right join tb_b on 条件
注：tb_a表是从表，tb_b则为主表，都显示。
例：首先创建数据库jiaowu，并创建学生表和成绩表mysql> create database jiaowu;
mysql> create table student(sid int(10),name varchar(48),id int(11));
mysql> create table grade(sid int(10),score int(5));
mysql> insert into student values(1,'孙悟空',1),(2,'猪八戒',2),(3,'沙悟净',3),(4,'小白龙',4),(5,'唐三藏',5),(6,'红孩儿'，6),(7,'哪吒',7););
mysql> insert into grade(sid,score) values(1,1234),(2,1235),(4,1423),(5,1120),(6,1354),(6,1367);mysql> select * from student left join grade on student.sid=grade.sid;
+------+-----------+------+------+-------+
| sid | name | id | sid | score |
+------+-----------+------+------+-------+
| 1 | 孙悟空 | 1 | 1 | 1234 |
| 2 | 猪八戒 | 2 | 2 | 1235 |
| 4 | 小白龙 | 4 | 4 | 1423 |
| 5 | 唐三藏 | 5 | 5 | 1120 |
| 6 | 红孩儿 | 6 | 6 | 1354 |
| 6 | 红孩儿 | 6 | 6 | 1367 |
| 3 | 沙悟净 | 3 | NULL | NULL |
| 7 | 哪吒 | 7 | NULL | NULL |
+------+-----------+------+------+-------+
8 rows in set (0.00 sec)使用别名查询
mysql> select * from student as s left join grade as g on s.sid=g.sid;
mysql> select * from grade as g right join student as s on g.sid=s.sid;
mysql> select * from grade as g left join student as s on g.sid=s.sid;
+------+-------+------+-----------+------+
| sid | score | sid | name | id |
+------+-------+------+-----------+------+
| 1 | 1234 | 1 | 孙悟空 | 1 |
| 2 | 1235 | 2 | 猪八戒 | 2 |
| 4 | 1423 | 4 | 小白龙 | 4 |
| 5 | 1120 | 5 | 唐三藏 | 5 |
| 6 | 1354 | 6 | 红孩儿 | 6 |
| 6 | 1367 | 6 | 红孩儿 | 6 |
+------+-------+------+-----------+------+
6 rows in set (0.00 sec)三张表如何链接
创建成绩表
grade2mysql> create table grade2(sid int(10),score int(5));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into grade2(sid,score) values(1,1234),(2,1235),(4,1423),(5,1120),(6,1354),(6,1367);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> show tables;
+----------------+
| Tables_in_book |
+----------------+
| books |
| category |
| grade |
| grade2 |
| student |
| tbdate |
+----------------+
6 rows in set (0.00 sec)mysql> select s.*,g1.*,g2.* from student as s right join grade as g1 on s.sid=g1.sid right join grade2 as g2 on s.sid=g2.sid;
+------+-----------+------+------+-------+------+-------+
| sid | name | id | sid | score | sid | score |
+------+-----------+------+------+-------+------+-------+
| 1 | 孙悟空 | 1 | 1 | 1011 | 1 | 1234 |
| 2 | 猪八戒 | 2 | 2 | 1012 | 2 | 1235 |
| 4 | 小白龙 | 4 | 4 | 1162 | 4 | 1423 |
| 5 | 唐三藏 | 5 | 5 | 920 | 5 | 1120 |
| 6 | 红孩儿 | 6 | 6 | 1107 | 6 | 1354 |
| 6 | 红孩儿 | 6 | 6 | 1107 | 6 | 1367 |
| 6 | 红孩儿 | 6 | 6 | 1118 | 6 | 1354 |
| 6 | 红孩儿 | 6 | 6 | 1118 | 6 | 1367 |
+------+-----------+------+------+-------+------+-------+或者：
mysql> select s.*,g1.*,g2.* from student s,grade g1,grade2 g2 where s.sid=g1.sid and g1.sid=g2.sid;
mysql> select s.*,g1.score,g2.score from student s,grade g1,grade2 g2 where s.sid=g1.sid=g2.sid;
mysql> select s.*,g1.score,g2.score from student s,grade g1,grade2 g2 where s.sid=g1.sid=g2.sid;
+------+-----------+------+-------+-------+
| sid | name | id | score | score |
+------+-----------+------+-------+-------+
| 1 | 孙悟空 | 1 | 1011 | 1234 |
| 2 | 猪八戒 | 2 | 1012 | 1234 |
| 4 | 小白龙 | 4 | 1162 | 1234 |
| 5 | 唐三藏 | 5 | 920 | 1234 |
| 6 | 红孩儿 | 6 | 1107 | 1234 |
| 6 | 红孩儿 | 6 | 1118 | 1234 |
+------+-----------+------+-------+-------+
6 rows in set (0.00 sec)二、MySQL下的聚合函数
函数：被封装成特定功能的代码块
2.1 求和函数
查看总分数
mysql> select sum(score) from grade;
+------------+
| sum(score) |
+------------+
| 7733 |
+------------+
1 row in set (0.01 sec)
2.2 查看平均分mysql> select avg(score) from grade;
+------------+
| avg(score) |
+------------+
| 1288.8333 |
+------------+
1 row in set (0.00 sec)
2.3 查看最高分数mysql> select max(score) from grade;
+------------+
| max(score) |
+------------+
| 1423 |
+------------+
1 row in set (0.00 sec)2.4 查看最高分数及其对应的
sidmysql> select sid,score from grade where score=(select max(score) from grade);
+------+-------+
| sid | score |
+------+-------+
| 4 | 1423 |
+------+-------+2.5 查看最低分及对应的sid；
mysql> select sid,score from grade where score=(select min(score) from grade);
+------+-------+
| sid | score |
+------+-------+
| 5 | 1120 |
+------+-------+
1 row in set (0.00 sec)2.6 统计分数大于1300的人数
mysql> select count(*) from grade where score > 1300;
+--------------+
| count(score) |
+--------------+
| 3 |
+--------------+
1 row in set (0.00 sec)2.7 算术运算
由于算法不同，没人在原本基础上各加30分；
mysql> update grade set score=score+30;
Query OK, 6 rows affected (0.01 sec)
Rows matched: 6 Changed: 6 Warnings: 0
mysql> select * from grade;
+------+-------+
| sid | score |
+------+-------+
| 1 | 1264 |
| 2 | 1265 |
| 4 | 1453 |
| 5 | 1150 |
| 6 | 1384 |
| 6 | 1397 |
+------+-------+
6 rows in set (0.00 sec)分数设置为原来的80%mysql> update grade set score=score*0.8;
2.8 字符串函数substr(string,start,len)截取：从start开始，长度为len，。start从1开始算。
mysql> select substr(name,1,2) from student where sid=1;
+------------------+
| substr(name,1,2) |
+------------------+
| 孙悟 |
+------------------+
1 row in set (0.00 sec)
concat(str1,str2,str3,...)拼接
mysql> select concat(sid,name,id) from student;
+---------------------+
| concat(sid,name,id) |
+---------------------+
| 1孙悟空1 |
| 2猪八戒2 |
| 3沙悟净3 |
| 4小白龙4 |
| 5唐三藏5 |
| 6红孩儿6 |
| 7哪吒7 |
+---------------------+
7 rows in set (0.00 sec)
大小写切换mysql> select upper(name) from student where sid=10;
+------------------+
| upper(name) |
+------------------+
| CAPTAION AMERICA |
+------------------+
1 row in set (0.00 sec)将大写字母改成小写
mysql> select lower(name) from student where sid=9;
+-------------+
| lower(name) |
+-------------+
| green giant |
+-------------+
1 row in set (0.00 sec)
日期查询mysql> select curdate(),now(),curtime();
+------------+---------------------+-----------+
| curdate() | now() | curtime() |
+------------+---------------------+-----------+
| 2017-11-17 | 2017-11-17 00:12:42 | 00:12:42 |
+------------+---------------------+-----------+
1 row in set (0.00 sec)
mysql> create table tbdate(name char(13),birthday date);
Query OK, 0 rows affected (0.04 sec)创建日期及使用
mysql> insert into tbdate values('HA',now());
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from tbdae;
ERROR 1146 (42S02): Table 'book.tbdae' doesn't exist
mysql> select * from tbdate;
+------+------------+
| name | birthday |
+------+------------+
| HA | 2017-11-17 |
+------+------------+
1 row in set (0.00 sec)本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
