函数 外连接 多表查询
上一章内容简单介绍了对单个数据库表的操作,本章内容将详细介绍关于多表查询的方法和技乔,以及关于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: 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 查看最高分数及其对应的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: 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)
小熊运维http://maoxiaoxiong.blog.51cto.com/11705634/1982665
5、MySQL多表查询
函数 外连接 多表查询
原文:http://maoxiaoxiong.blog.51cto.com/11705634/1982665
来源: http://www.bubuko.com/infodetail-2397890.html