MySQL多表查询中如何运用函数

下文主要给大家带来MySQL多表查询中如何运用函数，希望这些内容能够带给大家实际用处，这也是我编辑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)

三张表如何链接

创建成绩表grade2

mysql> 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: 0mysql> 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 查看最高分数及其对应的sid

mysql> 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: 0mysql> 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 existmysql> select * from tbdate;+------+------------+| name | birthday   |+------+------------+| HA   | 2017-11-17 |+------+------------+1 row in set (0.00 sec)

对于以上关于MySQL多表查询中如何运用函数，大家是不是觉得非常有帮助。如果需要了解更多内容，请继续关注我们的行业资讯，相信你会喜欢上这些内容的。