前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >MySQL实战上车，Github仓库Star起来

MySQL实战上车，Github仓库Star起来

作者头像
公众号guangcity
发布于 2019-09-20 17:29:18
6420
发布于 2019-09-20 17:29:18
举报
文章被收录于专栏：光城(guangcity)光城(guangcity)

MySQL实战上车，Github仓库Star起来

0.导语

今天开了个MySQL学习仓库Up-Up-MySQL，这是一个学习MySQL从入门实战到理论完善，再到精通的一个仓库，后面会把MySQL的学习资料上传上去！欢迎大家star与fork起来！

仓库地址：

https://github.com/Light-City/Up-Up-MySQL

也可以点击阅读原文！

1.创建数据库

代码语言：javascript
复制
mysql> create database pratice;
Query OK, 1 row affected (0.04 sec)

mysql> use pratice;
Database changed

2.创建数据库表

2.1 表结构

学生表

Student(SId,Sname,Sage,Ssex) SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

课程表

Course(CId,Cname,TId) CId 课程编号,Cname 课程名称,TId 教师编号

教师表

Teacher(TId,Tname) TId 教师编号,Tname 教师姓名

成绩表

SC(SId,CId,score) SId 学生编号,CId 课程编号,score 分数

2.2 表创建

代码语言：javascript
复制
mysql> create table Student(SId varchar(10) PRIMARY KEY NOT NULL,Sname varchar(10),Sage datetime,Ssex enum('男','女','保密') default '保密');
Query OK, 0 rows affected (0.36 sec)

    mysql> create table Course(CId varchar(10) PRIMARY KEY NOT NULL,Cname varchar(10),TId varchar(10));
Query OK, 0 rows affected (0.37 sec)

mysql> create table Teacher(TId varchar(10)PRIMARY KEY NOT NULL,Tname varchar(10));
Query OK, 0 rows affected (0.37 sec)

mysql> create table SC(SId varchar(10) NOT NULL,CId varchar(10) NOT NULL,score decimal(5,1));
Query OK, 0 rows affected (0.33 sec)

2.3 数据插入

2.3.1 Student表

导入数据：

代码语言：javascript
复制
load data local infile "/home/light/mysql/practice/student.txt" into table Student fields terminated by ',';

查询数据：

代码语言：javascript
复制
mysql> select * from Student;
+-----+--------+---------------------+------+
| SId | Sname  | Sage                | Ssex |
+-----+--------+---------------------+------+
| 01  | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02  | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03  | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04  | 李云   | 1990-12-06 00:00:00 | 男   |
| 05  | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06  | 吴兰   | 1992-01-01 00:00:00 | 女   |
| 07  | 郑竹   | 1989-01-01 00:00:00 | 女   |
| 09  | 张三   | 2017-12-20 00:00:00 | 女   |
| 10  | 李四   | 2017-12-25 00:00:00 | 女   |
| 11  | 李四   | 2012-06-06 00:00:00 | 女   |
| 12  | 赵六   | 2013-06-13 00:00:00 | 女   |
| 13  | 孙七   | 2014-06-01 00:00:00 | 女   |
+-----+--------+---------------------+------+
12 rows in set (0.00 sec)
2.3.2 Course表

导入表：

代码语言：javascript
复制
mysql> load data local infile "/home/light/mysql/practice/course.txt" into table Course fields terminated by ',';
Query OK, 3 rows affected (0.09 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

查询表：

代码语言：javascript
复制
mysql> select * from Course;                                                 
| CId | Cname  | TId  |
+-----+--------+------+
| 01  | 语文   | 02   |
| 02  | 数学   | 01   |
| 03  | 英语   | 03   |
+-----+--------+------+
3 rows in set (0.00 sec)
2.3.3 Teacher表

导入表：

代码语言：javascript
复制
mysql> load data local infile "/home/light/mysql/practice/teacher.txt" into table Teacher fields terminated by ',';
Query OK, 3 rows affected (0.09 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

查询表：

代码语言：javascript
复制
mysql> select * from Course;                                                 
| CId | Cname  | TId  |
+-----+--------+------+
| 01  | 语文   | 02   |
| 02  | 数学   | 01   |
| 03  | 英语   | 03   |
+-----+--------+------+
3 rows in set (0.00 sec)
2.3.4 SC表

导入表：

代码语言：javascript
复制
mysql> load data local infile "/home/light/mysql/practice/sc.txt" into table SC fields terminated by ',';
Query OK, 18 rows affected (0.14 sec)
Records: 18  Deleted: 0  Skipped: 0  Warnings: 0

查询表：

代码语言：javascript
复制
mysql> select * from SC;
+-----+-----+-------+
| SId | CId | score |
+-----+-----+-------+
| 01  | 01  |  80.0 |
| 01  | 02  |  90.0 |
| 01  | 03  |  99.0 |
| 02  | 01  |  70.0 |
| 02  | 02  |  60.0 |
| 02  | 03  |  80.0 |
| 03  | 01  |  80.0 |
| 03  | 02  |  80.0 |
| 03  | 03  |  80.0 |
| 04  | 01  |  50.0 |
| 04  | 02  |  30.0 |
| 04  | 03  |  20.0 |
| 05  | 01  |  76.0 |
| 05  | 02  |  87.0 |
| 06  | 01  |  31.0 |
| 06  | 03  |  34.0 |
| 07  | 02  |  89.0 |
| 07  | 03  |  98.0 |
+-----+-----+-------+
18 rows in set (0.01 sec)

3.查询

查询出课程编号为'01'的学生信息与成绩

代码语言：javascript
复制
mysql> select s.*,sc.score from Student s,Course c,SC sc where c.CId='01' and s.SId=sc.SId and c.CId=sc.CId;
+-----+--------+---------------------+------+-------+
| SId | Sname  | Sage                | Ssex | score |
+-----+--------+---------------------+------+-------+
| 01  | 赵雷   | 1990-01-01 00:00:00 | 男   |  80.0 |
| 02  | 钱电   | 1990-12-21 00:00:00 | 男   |  70.0 |
| 03  | 孙风   | 1990-12-20 00:00:00 | 男   |  80.0 |
| 04  | 李云   | 1990-12-06 00:00:00 | 男   |  50.0 |
| 05  | 周梅   | 1991-12-01 00:00:00 | 女   |  76.0 |
| 06  | 吴兰   | 1992-01-01 00:00:00 | 女   |  31.0 |
+-----+--------+---------------------+------+-------+
6 rows in set (0.02 sec)

mysql> select s.*,sc.score from SC sc join Student s on sc.SId=s.SId join Course c on sc.CId=c.CId where c.CId='01';
+-----+--------+---------------------+------+-------+
| SId | Sname  | Sage                | Ssex | score |
+-----+--------+---------------------+------+-------+
| 01  | 赵雷   | 1990-01-01 00:00:00 | 男   |  80.0 |
| 02  | 钱电   | 1990-12-21 00:00:00 | 男   |  70.0 |
| 03  | 孙风   | 1990-12-20 00:00:00 | 男   |  80.0 |
| 04  | 李云   | 1990-12-06 00:00:00 | 男   |  50.0 |
| 05  | 周梅   | 1991-12-01 00:00:00 | 女   |  76.0 |
| 06  | 吴兰   | 1992-01-01 00:00:00 | 女   |  31.0 |
+-----+--------+---------------------+------+-------+
6 rows in set (0.00 sec)

查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

代码语言：javascript
复制
SELECT s1.*
FROM (
    SELECT s.*, sc.score
    FROM SC sc
        JOIN Student s ON sc.SId = s.SId
        JOIN Course c ON sc.CId = c.CId
    WHERE c.CId = '01'
) s1, (
        SELECT s.*, sc.score
        FROM SC sc
            JOIN Student s ON sc.SId = s.SId
            JOIN Course c ON sc.CId = c.CId
        WHERE c.CId = '02'
    ) s2
WHERE s1.SId = s2.SId
    AND s1.score > s2.score;

由于要查询出学生的所有信息，所以上述的join要改为left/right join。

代码语言：javascript
复制
SELECT *
FROM (
    SELECT s.*, sc.score
    FROM SC sc
        JOIN Student s ON sc.SId = s.SId
        JOIN Course c ON sc.CId = c.CId
    WHERE c.CId = '01'
) s1
    LEFT JOIN (
        SELECT s.*, sc.score
        FROM SC sc
            JOIN Student s ON sc.SId = s.SId
            JOIN Course c ON sc.CId = c.CId
        WHERE c.CId = '02'
    ) s2
    ON s1.SId = s2.SId
WHERE s1.score > s2.score;

上述的left可以换成right。

代码语言：javascript
复制
+-----+--------+---------------------+------+-------+------+--------+---------------------+------+-------+
| SId | Sname  | Sage                | Ssex | score | SId  | Sname  | Sage                | Ssex | score |
+-----+--------+---------------------+------+-------+------+--------+---------------------+------+-------+
| 02  | 钱电   | 1990-12-21 00:00:00 | 男   |  70.0 | 02   | 钱电   | 1990-12-21 00:00:00 | 男   |  60.0 |
| 04  | 李云   | 1990-12-06 00:00:00 | 男   |  50.0 | 04   | 李云   | 1990-12-06 00:00:00 | 男   |  30.0 |
+-----+--------+---------------------+------+-------+------+--------+---------------------+------+-------+
2 rows in set (0.00 sec)

查询同时存在" 01 "课程和" 02 "课程的情况

我们先通过两张子表得到课程01和课程02的成绩信息，然后笛卡儿积合并两张表，最后WHERE筛选

代码语言：javascript
复制
mysql> select s1.*,s2.CId,s2.score as s2_score from (select * from SC sc where sc.CId='01') s1,(select * from SC sc where sc.CId='02') s2 where s1.SId=s2.SId;
+-----+-----+-------+-----+----------+
| SId | CId | score | CId | s2_score |
+-----+-----+-------+-----+----------+
| 01  | 01  |  80.0 | 02  |     90.0 |
| 02  | 01  |  70.0 | 02  |     60.0 |
| 03  | 01  |  80.0 | 02  |     80.0 |
| 04  | 01  |  50.0 | 02  |     30.0 |
| 05  | 01  |  76.0 | 02  |     87.0 |
+-----+-----+-------+-----+----------+
5 rows in set (0.00 sec)

分析：先分别查找出这个学生选修'01'或'02'的信息，然后通过学生的SId进行筛选，得到了这个学生同时存在" 01 "课程和" 02 "课程的情况。

如果对结果要求不高的，可以用in子查询。

代码语言：javascript
复制
mysql> select * from SC sc where sc.CId='01' and sc.SId in (select sc.SId from SC sc where sc.CId='02');
+-----+-----+-------+
| SId | CId | score |
+-----+-----+-------+
| 01  | 01  |  80.0 |
| 02  | 01  |  70.0 |
| 03  | 01  |  80.0 |
| 04  | 01  |  50.0 |
| 05  | 01  |  76.0 |
+-----+-----+-------+
5 rows in set (0.00 sec)

查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

首先要明确查询的表是成绩表（SC）的信息，这里的主要难点是：不存在显示为NULL。

这一道就是明显需要使用join的情况了，02可能不存在，即为left join的右侧或right join 的左侧即可。

左连接：

代码语言：javascript
复制
mysql> select * from (select * from SC sc where sc.CId='01')s1 left join (select * from SC sc where sc.CId='02')s2 on s1.SId=s2.SId;
+-----+-----+-------+------+------+-------+
| SId | CId | score | SId  | CId  | score |
+-----+-----+-------+------+------+-------+
| 01  | 01  |  80.0 | 01   | 02   |  90.0 |
| 02  | 01  |  70.0 | 02   | 02   |  60.0 |
| 03  | 01  |  80.0 | 03   | 02   |  80.0 |
| 04  | 01  |  50.0 | 04   | 02   |  30.0 |
| 05  | 01  |  76.0 | 05   | 02   |  87.0 |
| 06  | 01  |  31.0 | NULL | NULL |  NULL |
+-----+-----+-------+------+------+-------+
6 rows in set (0.00 sec)

右连接：

代码语言：javascript
复制
mysql> select * from (select * from SC sc where sc.CId='02')s1 right join (select * from SC sc where sc.CId='01')s2 on s1.SId=s2.SId;
+------+------+-------+-----+-----+-------+
| SId  | CId  | score | SId | CId | score |
+------+------+-------+-----+-----+-------+
| 01   | 02   |  90.0 | 01  | 01  |  80.0 |
| 02   | 02   |  60.0 | 02  | 01  |  70.0 |
| 03   | 02   |  80.0 | 03  | 01  |  80.0 |
| 04   | 02   |  30.0 | 04  | 01  |  50.0 |
| 05   | 02   |  87.0 | 05  | 01  |  76.0 |
| NULL | NULL |  NULL | 06  | 01  |  31.0 |
+------+------+-------+-----+-----+-------+
6 rows in set (0.00 sec)

查询不存在" 01 "课程但存在" 02 "课程的情况

方法一：

in子查询过滤。

代码语言：javascript
复制
mysql> select * from SC sc where sc.CId='02' and sc.SId not in (select sc.SId from SC sc where sc.CId='01');
+-----+-----+-------+
| SId | CId | score |
+-----+-----+-------+
| 07  | 02  |  89.0 |
+-----+-----+-------+
1 row in set (0.00 sec)

方法二：

首先查询存在" 02 "课程但可能不存在" 01 "课程的情况(不存在时显示为 null )

代码语言：javascript
复制
mysql> select * from (select * from SC sc where sc.CId='02')s1 left join (select * from SC sc where sc.CId='01')s2 on s1.SId=s2.SId;
+-----+-----+-------+------+------+-------+
| SId | CId | score | SId  | CId  | score |
+-----+-----+-------+------+------+-------+
| 01  | 02  |  90.0 | 01   | 01   |  80.0 |
| 02  | 02  |  60.0 | 02   | 01   |  70.0 |
| 03  | 02  |  80.0 | 03   | 01   |  80.0 |
| 04  | 02  |  30.0 | 04   | 01   |  50.0 |
| 05  | 02  |  87.0 | 05   | 01   |  76.0 |
| 07  | 02  |  89.0 | NULL | NULL |  NULL |
+-----+-----+-------+------+------+-------+
6 rows in set (0.00 sec)

然后使用WHERE过滤出不存在的01课程，也就是最后一条数据即可：

代码语言：javascript
复制
mysql> select * from (select * from SC sc where sc.CId='02')s1 left join (select * from SC sc where sc.CId='01')s2 on s1.SId=s2.SId where s2.SId is NULL;
+-----+-----+-------+------+------+-------+
| SId | CId | score | SId  | CId  | score |
+-----+-----+-------+------+------+-------+
| 07  | 02  |  89.0 | NULL | NULL |  NULL |
+-----+-----+-------+------+------+-------+
1 row in set (0.00 sec)

查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

代码语言：javascript
复制
mysql> select s.SId,s.Sname,avg(sc.score) as AVG from Student s join SC sc on s.SId=sc.SId group by sc.SID having AVG>=60;
+-----+--------+----------+
| SId | Sname  | AVG      |
+-----+--------+----------+
| 01  | 赵雷   | 89.66667 |
| 02  | 钱电   | 70.00000 |
| 03  | 孙风   | 80.00000 |
| 05  | 周梅   | 81.50000 |
| 07  | 郑竹   | 93.50000 |
+-----+--------+----------+
5 rows in set (0.00 sec)

查询在 SC 表存在成绩的学生信息

方法一：使用distinct关键字

代码语言：javascript
复制
mysql> select distinct s.* from Student s, SC sc where s.SId=sc.SId;
+-----+--------+---------------------+------+
| SId | Sname  | Sage                | Ssex |
+-----+--------+---------------------+------+
| 01  | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02  | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03  | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04  | 李云   | 1990-12-06 00:00:00 | 男   |
| 05  | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06  | 吴兰   | 1992-01-01 00:00:00 | 女   |
| 07  | 郑竹   | 1989-01-01 00:00:00 | 女   |
+-----+--------+---------------------+------+
7 rows in set (0.00 sec)

方法二：使用exists关键字

代码语言：javascript
复制
mysql> select * from Student s where exists(select sc.SId from SC sc where s.SId=sc.SId);
+-----+--------+---------------------+------+
| SId | Sname  | Sage                | Ssex |
+-----+--------+---------------------+------+
| 01  | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02  | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03  | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04  | 李云   | 1990-12-06 00:00:00 | 男   |
| 05  | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06  | 吴兰   | 1992-01-01 00:00:00 | 女   |
| 07  | 郑竹   | 1989-01-01 00:00:00 | 女   |
+-----+--------+---------------------+------+
7 rows in set (0.00 sec)
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2019-06-22，如有侵权请联系 cloudcommunity@tencent.com 删除
云数据库 SQL Server
数据库
sql

本文分享自 光城 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ，欢迎热爱写作的你一起参与！

云数据库 SQL Server
数据库
sql
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
目录
  • MySQL实战上车，Github仓库Star起来
    • 0.导语
      • 1.创建数据库
        • 2.创建数据库表
          • 2.1 表结构
          • 2.2 表创建
          • 2.3 数据插入
        • 3.查询
        相关产品与服务
        云数据库 SQL Server
        腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
        产品介绍产品文档
        云数据库采购特惠，高性能、高可靠、高安全，超值优惠等你享！
        领券

        腾讯云开发者

        扫码关注腾讯云开发者

        扫码关注腾讯云开发者

        领取腾讯云代金券

        热门产品

        热门推荐

        更多推荐

        Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

        深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

        腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

        问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

        Copyright © 2013 - 2024 Tencent Cloud.

        All Rights Reserved. 腾讯云 版权所有

        登录 后参与评论