MySQL之多表连接查询、AS别名、扩展内容(information_schema的基本应用)

发布时间:2024年01月14日


前言

第三章内容主要描述了mysql使用select进行单表查询的示例,并对常用的参数也做出了使用说明和示例。但这在实际操作中往往是不够的,因此本篇文件将是上篇文章的一个进阶,因为在生产环境中,表与表之间有一定的关联性,开发人员并不会将所有数据写入到一张表中,多处还是让表实现高内聚、低耦合。本文主要结合案例实现多表连接查询该怎么使用。


一、引入多表连接查询

作用:
    单表数据不能满足查询需求时使用
格式
	SELECT A.name,B.address FROM
	A JOIN  B
	ON A.id=B.id
	WHERE 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'
                        
       连接查询写法  在这两张表中关联的条件列是countrycode
              select 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 school

CREATE 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语句还是得抽空好好练习,先把基础夯实然后再往上建高层!!

文章来源:https://blog.csdn.net/weixin_50902636/article/details/135586008
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。