MySQL复习(一)

前言

最近准备将自己所学的知识好好的复习一遍,看到了MySQL,对于MySQL,很多东西还是停留在知识点,实际应用还是差一大截,刚好是在网上看到一篇博文,本文主要是根据这篇博文,加上一些关于MySQL的知识点交叉串起来,方便自己复习。

常见的MySQL操作的命令大全

以下是一些比较常用的MySQL的操作命令,并非查询语句。当然可以使用一些可视化的工具来代替这些命令,比如比较好用的Navicat,这里使用的是IDEA自带的database工具。

命令如下:

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
# 创建名为mysqlpractice的数据库
create database mysqlpractice;

# 显示所有的数据库
show databases;

# 删除数据库mysqlpractice
drop database mysqlpractice;
# 一般会在删除之前进行判断,判断是否存在该数据库,如果对不存在的数据库进行删除会报错,对表的删除同理。
drop database if exists mysqlpractice;

# 选择名为mysqlpractice的数据库,只有选择了某个数据库,才能对数据库中的表进行操作
use mysqlpractice;

#显示当前数据库下的course表的字段信息,以下两种方式均可
show columns from course;
describe course;

# 显示创建数据库的mysql语句
show create database mysqlpractice;

# 显示创建表的sql语句
show create table course;

# 删除表
drop table course;
#一般会在删除数据库表之前进行判断,如果存在就进行删除
drop table if exists course;

# 删除表中的数据,不删除表delete方式
delete
from course;
# 删除表中的数据,不删除表truncate方式
truncate table course;

# 以上两种方式都可以删除表中的数据,但是还是由区别的
# 1、truncate是不可以rollback的,delete可以rollback。
# 2、truncate删除后会重置索引(自增类型的id会从0开始),而delete不会删除索引。
# 3、delete可以返回受影响的行数,且delete可以按照条件删除。
# 4、truncate不会触发任何的delete触发器。


# 表结构的修改
## 增加一个字段int类型的test1
alter table course
add column (test1 int);

## 指定字段test1之后插入一个int类型的字段test2
## 需要和上面一个命令区别开来,上面的命令是必须要带括号的,下面这个命令是不能带括号的
alter table course
add column test2 int
after test1;

## 删除一个test1字段
alter table course
drop test1;

## 修改字段的名称test1为newtest1,类型为char

alter table course
change test1 newtest1 char(32);

## 修改表的名字,将course表的名称修改为newCourse

alter table course
rename to newCourse;

学生-课程-教师-成绩SQL练习题

表的架构

ps:

原文是采用的S#这种写法来表示id的,在navicat以及idea自带的一些插件中是不能识别的,所以以下全部改为sno这种形式。

Student表

Student表具有学号sno,学生姓名sname,年龄sage以及性别ssex。

1
2
3
4
5
6
7
8
-- auto-generated definition
create table student
(
sno int null,
sname varchar(32) null,
sage int null,
ssex varchar(8) null
);

对于Student表插入的测试数据如下:

1
2
3
4
5
6
7
8
9
10
11
12
insert into Student
select 1, N'刘一', 18, N'男'
union all
select 2, N'钱二', 19, N'女'
union all
select 3, N'张三', 17, N'男'
union all
select 4, N'李四', 18, N'女'
union all
select 5, N'王五', 17, N'男'
union all
select 6, N'赵六', 19, N'女';

插入6条学生数据信息,数据显示如下:

QQ截图20180908114254-201898

course表

course有三个属性,分别是课程的编号,课程的名称,以及该课程的老师id
创表语句:

1
2
3
4
5
6
CREATE TABLE course
(
cno INT,
cname nvarchar(32),
tno INT
);

插入数据如下:

1
2
3
4
5
6
7
8
insert into Course
select 1, N'语文', 1
union all
select 2, N'数学', 2
union all
select 3, N'英语', 3
union all
select 4, N'物理', 4;

数据插入完成后的表如下:

QQ截图20180908115655-201898

Teacher表

创表语句如下:

1
2
3
4
5
create table teacher
(
tno int null,
tname varchar(16) null
);

向表中插入数据:

1
2
3
4
5
6
7
8
insert into Teacher
select 1, N'叶平'
union all
select 2, N'贺高'
union all
select 3, N'杨艳'
union all
select 4, N'周磊';

教师表创建完成后的表如下:
QQ截图20180908115853-201898

sc表-成绩、课程及学生的关联表

创建表的语句如下:

1
2
3
4
5
6
7
-- auto-generated definition
create table sc
(
sno int null,
cno int null,
score int null
);

插入测试数据:

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
insert into SC
select 1, 1, 56
union all
select 1, 2, 78
union all
select 1, 3, 67
union all
select 1, 4, 58
union all
select 2, 1, 79
union all
select 2, 2, 81
union all
select 2, 3, 92
union all
select 2, 4, 68
union all
select 3, 1, 91
union all
select 3, 2, 47
union all
select 3, 3, 88
union all
select 3, 4, 56
union all
select 4, 2, 88
union all
select 4, 3, 90
union all
select 4, 4, 93
union all
select 5, 1, 46
union all
select 5, 3, 78
union all
select 5, 4, 53
union all
select 6, 1, 35
union all
select 6, 2, 68
union all
select 6, 4, 71;

插入完成后的数据如下:
QQ截图20180908120101-201898

SQL练习题

1、查询课程号为1比课程号为2的成绩高的所有学生的学号:
解决思路:
使用两个子表,一个字表装课程号为1的信息,一个表装课程号为2的信息。

1
2
3
4
5
6
7
8
SELECT
a.sno
FROM
( SELECT sno, score FROM sc WHERE cno = '1' ) a,
( SELECT sno, score FROM sc WHERE cno = '2' ) b
WHERE
a.score > b.score
AND a.sno = b.sno;

QQ截图20180908171025-201898
2、查询平均成绩大于60分的同学的学号和平均成绩:
解决思路:
使用group by 将数据按照学号sno进行分组,这样就能和聚合函数avg一起,算出平均分,再使用having将得到的数据进行筛选。

1
2
3
4
5
6
7
8
9
SELECT
sno,
avg( score )
FROM
sc
GROUP BY
sno
HAVING
avg( score ) > 60;

QQ截图20180908170644-201898

3、查询所有同学的学号、姓名、选课数、总成绩:

解决思路:
因为需要对每一个同学进行单独处理,所以一定要用到分组,所以group by 是必须的,选课数和总成绩这些就需要拥挤聚合函数。

1
2
3
4
5
6
7
8
9
10
SELECT
student.sno,
student.sname,
count( sc.sno ),
sum( score )
FROM
student
LEFT JOIN sc ON student.sno = sc.sno
GROUP BY
student.sno

结果如图:
QQ截图20180908170614-201898

4、查询姓“李”的老师的个数:

解决思路:

使用聚合函数count,需要注意的是需要对结果进行distinct处理。

1
2
3
4
5
6
SELECT
count( DISTINCT ( tname ) )
FROM
teacher
WHERE
tname LIKE '李%'

QQ截图20180908171738-201898

5、查询没有学过”叶平”老师课的同学学号,姓名:

解决思路:
首先找出所有选修的叶平老师的学生的学号,这个可以通过sc表来进行查找。然后从student表中找出学号不在上面表中的学号。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
student.sno,
student.sname
FROM
student
WHERE
student.sno NOT IN (
SELECT
sno
FROM
sc,
teacher,
course
WHERE
teacher.tname = "叶平"
AND teacher.tno = course.tno
AND course.cno = sc.cno
)

结果如下:

QQ截图20180908175856-201898

6、查询学过课程号为1同时课程号为2的同学的学号、姓名:

关于exists的使用参看:https://www.cnblogs.com/V1haoge/p/6385312.html

1
2
3
4
5
6
7
8
9
10
SELECT
student.sno,
student.sname
FROM
student,
sc
WHERE
student.sno = sc.sno
AND sc.cno = "1"
AND EXISTS ( SELECT * FROM sc AS sc2 WHERE sc2.sno = sc.sno AND sc2.cno = "2" )

7、查询学过“叶平”老师所教的课的同学的学号、姓名:

鸣谢

本文主要是参考一些很优秀的博文,链接如下:

https://www.cnblogs.com/shenqz/p/6962493.html

-------------The End-------------

本文标题:MySQL复习(一)

文章作者:Dimple

发布时间:2018年09月07日 - 21:09

最后更新:2018年09月28日 - 17:09

原始链接:http://www.bianxiaofeng.com/2018/09/07/2018-9-7-21-12-48/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

na,给我一个棒棒糖!
0%