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

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

guangcheng0312q 2021-08-02 20:01:44 阅读数:48

本文一共[544]字，预计阅读时长:1分钟~
Mysql 数据 github 子查询 创建数据库

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

0.导语

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

仓库地址：

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

也可以点击阅读原文！

1.创建数据库

mysql> create database pratice;
Query OK, 1 row affected (0.04 sec)
mysql> use pratice;
Database changed


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    •

2.创建数据库表

2.1 表结构

学生表

Student(SId,Sname,Sage,Ssex)

课程表

Course(CId,Cname,TId)

教师表

Teacher(TId,Tname)

成绩表

SC(SId,CId,score)

2.2 表创建

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)


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    • 

  • 6.
    • 

  • 7.
    • 

  • 8.
    • 

  • 9.
    • 

  • 10.
    • 

  • 11.
    •

2.3 数据插入

2.3.1 Student表

导入数据：

load data local infile "/home/light/mysql/practice/student.txt" into table Student fields terminated by ',';


    

  • 1.
    •

查询数据：

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)


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    • 

  • 6.
    • 

  • 7.
    • 

  • 8.
    • 

  • 9.
    • 

  • 10.
    • 

  • 11.
    • 

  • 12.
    • 

  • 13.
    • 

  • 14.
    • 

  • 15.
    • 

  • 16.
    • 

  • 17.
    • 

  • 18.
    •

2.3.2 Course表

导入表：

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


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    •

查询表：

mysql> select * from Course;
| CId | Cname | TId |
+-----+--------+------+
| 01 | 语文 | 02 |
| 02 | 数学 | 01 |
| 03 | 英语 | 03 |
+-----+--------+------+
3 rows in set (0.00 sec)


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    • 

  • 6.
    • 

  • 7.
    • 

  • 8.
    •

2.3.3 Teacher表

导入表：

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


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    •

查询表：

mysql> select * from Course;
| CId | Cname | TId |
+-----+--------+------+
| 01 | 语文 | 02 |
| 02 | 数学 | 01 |
| 03 | 英语 | 03 |
+-----+--------+------+
3 rows in set (0.00 sec)


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    • 

  • 6.
    • 

  • 7.
    • 

  • 8.
    •

2.3.4 SC表

导入表：

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


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    •

查询表：

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)


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    • 

  • 6.
    • 

  • 7.
    • 

  • 8.
    • 

  • 9.
    • 

  • 10.
    • 

  • 11.
    • 

  • 12.
    • 

  • 13.
    • 

  • 14.
    • 

  • 15.
    • 

  • 16.
    • 

  • 17.
    • 

  • 18.
    • 

  • 19.
    • 

  • 20.
    • 

  • 21.
    • 

  • 22.
    • 

  • 23.
    • 

  • 24.
    •

3.查询

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

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)


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    • 

  • 6.
    • 

  • 7.
    • 

  • 8.
    • 

  • 9.
    • 

  • 10.
    • 

  • 11.
    • 

  • 12.
    • 

  • 13.
    • 

  • 14.
    • 

  • 15.
    • 

  • 16.
    • 

  • 17.
    • 

  • 18.
    • 

  • 19.
    • 

  • 20.
    • 

  • 21.
    • 

  • 22.
    • 

  • 23.
    • 

  • 24.
    • 

  • 25.
    •

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

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;


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    • 

  • 6.
    • 

  • 7.
    • 

  • 8.
    • 

  • 9.
    • 

  • 10.
    • 

  • 11.
    • 

  • 12.
    • 

  • 13.
    • 

  • 14.
    • 

  • 15.
    • 

  • 16.
    •

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

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;


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    • 

  • 6.
    • 

  • 7.
    • 

  • 8.
    • 

  • 9.
    • 

  • 10.
    • 

  • 11.
    • 

  • 12.
    • 

  • 13.
    • 

  • 14.
    • 

  • 15.
    • 

  • 16.
    • 

  • 17.
    •

上述的left可以换成right。

+-----+--------+---------------------+------+-------+------+--------+---------------------+------+-------+
| 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)


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    • 

  • 6.
    • 

  • 7.
    •

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

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

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)


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    • 

  • 6.
    • 

  • 7.
    • 

  • 8.
    • 

  • 9.
    • 

  • 10.
    • 

  • 11.
    •

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

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

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)


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    • 

  • 6.
    • 

  • 7.
    • 

  • 8.
    • 

  • 9.
    • 

  • 10.
    • 

  • 11.
    •

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

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

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

左连接：

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)


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    • 

  • 6.
    • 

  • 7.
    • 

  • 8.
    • 

  • 9.
    • 

  • 10.
    • 

  • 11.
    • 

  • 12.
    •

右连接：

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)


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    • 

  • 6.
    • 

  • 7.
    • 

  • 8.
    • 

  • 9.
    • 

  • 10.
    • 

  • 11.
    • 

  • 12.
    •

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

方法一：

in子查询过滤。

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)


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    • 

  • 6.
    • 

  • 7.
    •

方法二：

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

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)


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    • 

  • 6.
    • 

  • 7.
    • 

  • 8.
    • 

  • 9.
    • 

  • 10.
    • 

  • 11.
    • 

  • 12.
    •

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

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)


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    • 

  • 6.
    • 

  • 7.
    •

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

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)


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    • 

  • 6.
    • 

  • 7.
    • 

  • 8.
    • 

  • 9.
    • 

  • 10.
    • 

  • 11.
    •

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

方法一：使用distinct关键字

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)


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    • 

  • 6.
    • 

  • 7.
    • 

  • 8.
    • 

  • 9.
    • 

  • 10.
    • 

  • 11.
    • 

  • 12.
    • 

  • 13.
    •

方法二：使用exists关键字

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)


    

  • 1.
    • 

  • 2.
    • 

  • 3.
    • 

  • 4.
    • 

  • 5.
    • 

  • 6.
    • 

  • 7.
    • 

  • 8.
    • 

  • 9.
    • 

  • 10.
    • 

  • 11.
    • 

  • 12.
    • 

  • 13.
    •

640?wx_fmt=png

版权声明：本文为[guangcheng0312q]所创，转载请带上原文链接，感谢。 https://blog.51cto.com/u_12205414/3251565

《 老大哥要变小鲜肉 试驾北京EU5 PLUS

天津发布7月小客车竞价结果 个人最低成交10000元 》