MySQL之多表连接查询、AS别名、扩展内容(information_schema的基本应用)
)
文章目录
- 前言
- 一、引入多表连接查询
- 二、多表连接查询案例
- 1.准备对应的库表
- 2.案例
- 三、AS别名用法
- 示例
- 四、扩展内容
- 1、information_schema的基本应用
- 2、创建视图示例
- 3、information_schema.tables视图的应用
- 3.1、示例
- 五、show命令总结
- 总结
前言
第三章内容主要描述了mysql使用select进行单表查询的示例，并对常用的参数也做出了使用说明和示例。但这在实际操作中往往是不够的,因此本篇文件将是上篇文章的一个进阶，因为在生产环境中，表与表之间有一定的关联性,开发人员并不会将所有数据写入到一张表中,多处还是让表实现高内聚、低耦合。本文主要结合案例实现多表连接查询该怎么使用。
一、引入多表连接查询
作用:单表数据不能满足查询需求时使用
格式SELECT A.name,B.address FROMA JOIN BON A.id=B.idWHERE A.name='xxx'
多张表关联格式: 一组 xx join xx on xx=xx 为 一组关联A join b on A.x=b.y join c on b.m=c.h
基本要求最核心的是找到多张表之前的关联条件列列书写时必须是 表名.列名所有涉及到的查询列,都放在select后将所有的过滤、分组、排序等条件按顺序写在on后面
引入多表连接查询示例:查询世界上小于100w人的城市,所在的国家名、国土面积、城市名、人口数第一步: 先查城市表，得到城市名、城市对应的国家code，城市人口数select countrycode, name, popilation from city where population <1000000第二步: 然后在使用第一步查出来的countrycode去 查国家表，得到国家名、国土面积select name, surfacearea from country where code='PCN'连接查询写法 在这两张表中关联的条件列是countrycodeselect country.name,country.surfacearea,city.name.city.populartion from city join country on city.countrycode=country.code where city.population <1000000;
二、多表连接查询案例
1.准备对应的库表
use school
student ：学生表
sno： 学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别teacher ：教师表
tno： 教师编号
tname：教师名字course ：课程表
cno： 课程编号
cname：课程名字
tno： 教师编号score ：成绩表
sno： 学号
cno： 课程编号
score：成绩-- 项目构建
drop database school;
CREATE DATABASE school CHARSET utf8;
USE schoolCREATE TABLE student(
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(20) NOT NULL COMMENT '姓名',
sage TINYINT UNSIGNED NOT NULL COMMENT '年龄',
ssex ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
)ENGINE=INNODB CHARSET=utf8;CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
cname VARCHAR(20) NOT NULL COMMENT '课程名字',
tno INT NOT NULL COMMENT '教师编号'
)ENGINE=INNODB CHARSET utf8;CREATE TABLE sc (
sno INT NOT NULL COMMENT '学号',
cno INT NOT NULL COMMENT '课程编号',
score INT NOT NULL DEFAULT 0 COMMENT '成绩'
)ENGINE=INNODB CHARSET=utf8;CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(20) NOT NULL COMMENT '教师名字'
)ENGINE=INNODB CHARSET utf8;INSERT INTO student(sno,sname,sage,ssex)
VALUES (1,'zhang3',18,'m');INSERT INTO student(sno,sname,sage,ssex)
VALUES
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f');INSERT INTO student
VALUES
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f');INSERT INTO student(sname,sage,ssex)
VALUES
('oldboy',20,'m'),
('oldgirl',20,'f'),
('oldp',25,'m');INSERT INTO teacher(tno,tname) VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');DESC course;
INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);DESC sc;
INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;
2.案例
1、统计张3学习了几门课程mysql> select student.sname,count(sc.cno) from student join sc on student.sno=sc.sno where student.sname='zhang3' group by student.sno;+--------+---------------+| sname | count(sc.cno) |+--------+---------------+| zhang3 | 2 |+--------+---------------+
2、查询张3学习的课程名称有哪些分析:首先根据要求确认了学生表、课程表,根据表中的列发现这两张表并没有对应关系则引入第三张成绩表作为中间表，构成多表关联查询。然后在根据姓名分组,引入聚合函数group_concat()对课程名进行列转行，让结果在一行展示mysql> select student.sname,group_concat(course.cname) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno where student.sname='zhang3' group by student.sname;+--------+----------------------------+| sname | group_concat(course.cname) |+--------+----------------------------+| zhang3 | linux,python |+--------+----------------------------+1 row in set (0.00 sec)
3、查询oldguo老师教的学生名和个数分析:首先根据要求确认了学生表、老师表,根据表中的列发现这两张表并没有对应关系则引入第三张成绩表和课程表作为中间表，构成多表关联查询。mysql> select teacher.tname,group_concat(student.sname),count(student.sname) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno where teacher.tname='oldguo' group by teacher.tname;+--------+---------------------------------------------+----------------------+| tname | group_concat(student.sname) | count(student.sname) |+--------+---------------------------------------------+----------------------+| oldguo | zhang4,li4,wang5,zh4,zhao4,ma6,oldgirl,oldp | 8 |+--------+---------------------------------------------+----------------------+1 row in set (0.00 sec)
4、查询oldguo老师所教课程的平均分数mysql> select teacher.tname,avg(sc.score) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno where teacher.tname='oldguo';+--------+---------------+| tname | avg(sc.score) |+--------+---------------+| oldguo | 76.7500 |+--------+---------------+1 row in set (0.00 sec)
5、每位老师所教课程的平均分,并按平均分排序mysql> select teacher.tname, course.cname,avg(sc.score) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno group by teacher.tname,course.cname order by avg(ssc.score) desc;+--------+--------+---------------+| tname | cname | avg(sc.score) |+--------+--------+---------------+| oldboy | linux | 80.6667 || oldguo | mysql | 76.7500 || hesw | python | 70.0000 |+--------+--------+---------------+3 rows in set (0.00 sec)
6、查询oldguo所教的不及格的学生的姓名mysql> select teacher.tname, group_concat(student.sname) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno where sc.score<<60 and teacher.tname='oldguo';+--------+-----------------------------+| tname | group_concat(student.sname) |+--------+-----------------------------+| oldguo | li4,zh4 |+--------+-----------------------------+1 row in set (0.00 sec)
7、查询所有老师所教学生不及格的信息mysql> select teacher.tname,student.sname,student.ssex,student.sage,course.cname,sc.score from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno where sc.score<60;+--------+--------+------+------+--------+-------+| tname | sname | ssex | sage | cname | score |+--------+--------+------+------+--------+-------+| hesw | zhang3 | m | 18 | python | 59 || oldguo | li4 | m | 18 | mysql | 40 || oldguo | zh4 | m | 18 | mysql | 40 |+--------+--------+------+------+--------+-------+3 rows in set (0.00 sec)
三、AS别名用法
示例
1、表别名示例mysql> select T.tname,Stu.sname as 不及格人员姓名,Stu.ssex as 不及格人员性别,Stu.sage as 不及格人员年龄,C.cname as 不及格人员科目,S.score as 不及格人员分数 from teacher as T join course as C on T.tno=C.tno join sc as S on C.cno=S.cno join student as Stu on S.sno=Stu.sno where S.score<60;+--------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+| tname | 不及格人员姓名 | 不及格人员性别 | 不及格人员年龄 | 不及格人员科目 | 不及格人员分数 |+--------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+| hesw | zhang3 | m | 18 | python | 59 || oldguo | li4 | m | 18 | mysql | 40 || oldguo | zh4 | m | 18 | mysql | 40 |+--------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+3 rows in set (0.00 sec)
2、列别名示例mysql> select teacher.tname,student.sname as 不及格人员姓名,student.ssex as 不及格人员性别,student.sage as 不及格人员年龄,course.cname as 不及格人员科目,sc.score as 不及格人员分数 from teachher join course on teacher.tno=course.tno join sc on course.cno=sc.cn+--------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+| tname | 不及格人员姓名 | 不及格人员性别 | 不及格人员年龄 | 不及格人员科目 | 不及格人员分数 |+--------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+| hesw | zhang3 | m | 18 | python | 59 || oldguo | li4 | m | 18 | mysql | 40 || oldguo | zh4 | m | 18 | mysql | 40 |+--------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+3 rows in set (0.00 sec)
四、扩展内容
1、information_schema的基本应用
1、视图在information_schema中存放的是大量的视图
2、什么是视图?通过下方的例子来看一下视图可以让我们后续查询更加快速便捷
2、创建视图示例
mysql> select T.tname,Stu.sname as 不及格人员姓名,Stu.ssex as 不及格人员性别,Stu.sage as 不及格人员年龄,C.cname as 不及格人员科目,S.score as 不及格人员分数 from teacher as T join course as C on T.tno=C.tno join sc as S on C.cno=S.cno join student as Stu on S.sno=Stu.sno where S.score<60;+--------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+| tname | 不及格人员姓名 | 不及格人员性别 | 不及格人员年龄 | 不及格人员科目 | 不及格人员分数 |+--------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+| hesw | zhang3 | m | 18 | python | 59 || oldguo | li4 | m | 18 | mysql | 40 || oldguo | zh4 | m | 18 | mysql | 40 |+--------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+3 rows in set (0.00 sec)##创建视图
mysql> create view aa as select T.tname,Stu.sname as 不及格人员姓名,Stu.ssex as 不及格人员性别,Stu.sage as 不及格人员年龄,C.cname as 不及格人员科目,S.score as 不及格人员分数 from teacher as T join course as C on T.tno=C.tno join sc as S on C.cno=S.cno join student as Stu on S.sno=Stu.sno where S.score<60;Query OK, 0 rows affected (0.00 sec)mysql> select * from aa;+--------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+| tname | 不及格人员姓名 | 不及格人员性别 | 不及格人员年龄 | 不及格人员科目 | 不及格人员分数 |+--------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+| hesw | zhang3 | m | 18 | python | 59 || oldguo | li4 | m | 18 | mysql | 40 || oldguo | zh4 | m | 18 | mysql | 40 |+--------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+3 rows in set (0.00 sec)
3、information_schema.tables视图的应用
该结构下常用的表结构TABLE_SCHEMA 表所在的库名TABLE_NAME 表名ENGINE 存储引擎TABLE_ROWS 数据行AVG_ROW_LENGTH 平均行长度INDEX_LENGTH 索引长度data_length 数据长度
3.1、示例
1、查看所有库下的所有表的信息mysql> select table_schema,group_concat(table_name) from tables group by table_schema;
2、查询所有innodb引擎的表mysql> select table_schema,table_name from tables where engine='innodb';
3、统计某个库下某个表占用空间大小第一种:注意: 平均行长度*行数 +索引长度 select table_name,concat((avg_row_length*table_rows+index_length)/1024,'KB')as size from tables where table_schema='school' and table_name='student';第二种:SELECT CONCAT(SUM(data_length)/1024/1024, ' MB') AS size FROM information_schema.tables WHERE table_schema = upper('foreigntrade_db') AND table_name = 'for_access_manifest'; #将获得一个名为'size'的结果列，其中包含数据大小以MB为单位的字符串。例如，结果可能类似于"10.5 MB"。注意事项:#首先进入到information_schema库中#注意替换库名和表名#该语句出来是MB单位,如果要为KB,则为/1024 如果要为GB,则为/1024/1024/1024#upper()函数对数据库名进行了转换，将其转换为大写形式,如果库名本身是小写,则此处不需要该函数#CONCAT()是在SQL中用于将多个字符串连接在一起的函数。它接受两个或多个字符串作为参数，并返回一个将这些字符串连接在一起的结果。
4、统计某个库空间大小第一种:mysql> select table_schema,SUM(concat((avg_row_length*table_rows+index_length)/1024,'KB'))as size from tables where table_schema='school';+--------------+---------+| table_schema | size |+--------------+---------+| school | 63.9814 |+--------------+---------+1 row in set (0.01 sec)第二种:mysql> select sum(concat((data_length)/1024,'KB')) as size from tables where table_schema='school';+------+| size |+------+| 64 |+------+1 row in set (0.00 sec)
5、查看所有库空间大小mysql> SELECT table_schema AS `Database Name`, CONCAT(ROUND(SUM((data_length + index_length) / (1024 * 1024)), 2), ' MB') AS `Size in MB` FROM information_schema.tables GROUP BY table_schema;+--------------------+------------+| Database Name | Size in MB |+--------------------+------------+| information_schema | 0.16 MB || mysql | 2.43 MB || performance_schema | 0.00 MB || school | 0.06 MB || sys | 0.02 MB || test | 0.05 MB |+--------------------+------------+6 rows in set (0.01 sec)mysql> select table_schema, sum(concat((data_length)/1024,'KB')) as size from tables group by table_schema;+--------------------+-----------+| table_schema | size |+--------------------+-----------+| information_schema | 160 || mysql | 2265.4912 || performance_schema | 0 || school | 64 || sys | 16 || test | 16 |+--------------------+-----------+6 rows in set (0.01 sec)
6、查看所有表空间大小并排序mysql> SELECT table_schema AS `Database Name`, table_name AS `Table Name`, CONCAT(ROUND(((data_length + index_length) / 1024 / 1024), 2), ' MB') AS `Size in MB` FROM information_schema.tables ORDER BY (data_length + index_length) DESC;mysql> SELECT table_schema,table_name,CONCAT(SUM(data_length)/1024/1024, ' MB') AS size FROM information_schema.tables group by TABLE_SCHEMA,TABLE_NAME ;
五、show命令总结
show databases; #查看所有数据库show tables; #查看当前库的所有表SHOW TABLES FROM #查看某个指定库下的表show create database world #查看建库语句show create table world.city #查看建表语句show grants for root@'localhost' #查看用户的权限信息show charset； #查看字符集show collation #查看校对规则show processlist; #查看数据库连接情况show index from #表的索引情况show status #数据库状态查看SHOW STATUS LIKE '%lock%'; #模糊查询数据库某些状态SHOW VARIABLES #查看所有配置信息SHOW variables LIKE '%lock%'; #查看部分配置信息show engines #查看支持的所有的存储引擎show engine innodb status\G #查看InnoDB引擎相关的状态信息show binary logs #列举所有的二进制日志show master status #查看数据库的日志位置信息show binlog evnets in '' #查看二进制日志事件show slave status \G #查看从库状态SHOW RELAYLOG EVENTS #查看从库relaylog事件信息show status like '' #查看数据库整体状态信息desc (show colums from city) #查看表的列定义信息详细的show命令见该地址: http://dev.mysql.com/doc/refman/5.7/en/show.html
总结
结合第三章内容,已经基本对DDL、DQL、DCL、DML四种类型的操作语句有了大体的掌握和了解,希望熟能生巧,sql语句还是得抽空好好练习,先把基础夯实然后再往上建高层!!