一、数据库范式
数据库的设计范式是数据库设计所需要满足的规范，满足这些规范的数据库是简洁的、结构明晰的，同时，不会发生插入（insert）、删除（delete）和更新（update）操作异常。反之则是乱七八糟，不仅给数据库的编程人员制造麻烦，而且面目可憎，可能存储了大量不需要的冗余信息。
第一范式（1NF）无重复的列
所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。简而言之，第一范式就是无重复的列。
第二范式（2NF）属性完全依赖于主键 [消除部分子函数依赖]
如果关系模式R为第一范式，并且R中每一个非主属性完全函数依赖于R的某个候选键， 则称为第二范式模式。
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。简而言之，第二范式（2NF）就是非主属性完全依赖于主关键字。
第三范式（3NF）属性不依赖于其它非主属性 [消除传递依赖]
如果关系模式R是第二范式，且每个非主属性都不传递依赖于R的候选键，则称R为第三范式模式。
满足第三范式（3NF）必须先满足第二范式（2NF）。第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
第三范式（3NF）在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。简而言之，第三范式就是属性不依赖于其它非主属性。
现在数据库设计最多满足3NF，普遍认为范式过高，虽然具有对数据关系更好的约束性，但也导致数据关系表增加而令数据库IO更易繁忙，原来交由数据库处理的关系约束现更多在数据库使用程序中完成。
二、MySQL查询语句
MySQL的查询分为以下几类：
1、单表查询：简单查询
2、多表查询：连续查询
3、联合查询：事先将两张或多张表join；根据join的结果进行查询
选择和投影：
-
投影：挑选要显示的字段
select 字段1, 字段2, ... from tb_name；
select * from tb_name；
-
选择：挑选符合条件的行
select 字段1, ... from tb_name where 子句；
布尔条件表达式
select语句：
|
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
|
mysql>
help
select
Name:
'SELECT'
Description:
Syntax:
SELECT
[
ALL
| DISTINCT | DISTINCTROW ]
#DISTINCT 指定的结果重复内容只显示一次
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
#SQL_CACHE|SQL_NO_CACHE 手动指定是否缓存查询语句
select_expr [, select_expr ...]
[FROM table_references
[PARTITION partition_list]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
#指定条件分组
[HAVING where_condition]
#对分组进行聚合
[ORDER BY {col_name | expr | position}
#排序查询结果
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
#截取需要的行
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE
'file_name'
[CHARACTER
SET
charset_name]
export_options
| INTO DUMPFILE
'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
|
常用的几组select语句
|
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
|
select ...
from
...
order by ...
#
#
select ...
from
...
group by ...
having ...
#
#
select ...
from
...
where ...
#
#
select ...
#
#
select ...
from
...
where ...
group by ...
limit ...
#
###select语句的执行流程：
from
clause
-
-
> where clause
-
-
> group by
-
-
> having clause
-
-
> order by ...
-
-
> select
-
-
> limit
|
三、多表查询以及子查询
为了降低数据冗余；把重复需要存入的数据分开为多张表；以某种对应关系联系：
|
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
|
mysql> show tables;
#该表就拆分为多张表
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| Tables_in_hellodb |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
7
rows
in
set
(
0.00
sec)
mysql> select
*
from
students;
+
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
+
|
1
| Shi Zhongyu |
22
| M |
2
|
3
|
|
2
| Shi Potian |
22
| M |
1
|
7
|
|
3
| Xie Yanke |
53
| M |
2
|
16
|
|
4
| Ding Dian |
32
| M |
4
|
4
|
|
5
| Yu Yutong |
26
| M |
3
|
1
|
|
6
| Shi Qing |
46
| M |
5
| NULL |
|
7
| Xi Ren |
19
| F |
3
| NULL |
|
8
| Lin Daiyu |
17
| F |
7
| NULL |
|
9
| Ren Yingying |
20
| F |
6
| NULL |
mysql> select
*
from
classes;
+
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
+
| ClassID | Class | NumOfStu |
+
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
+
|
1
| Shaolin Pai |
10
|
|
2
| Emei Pai |
7
|
|
3
| QingCheng Pai |
11
|
|
4
| Wudang Pai |
12
|
|
5
| Riyue Shenjiao |
31
|
|
6
| Lianshan Pai |
27
|
|
7
| Ming Jiao |
27
|
|
8
| Xiaoyao Pai |
15
|
+
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
+
mysql> select
*
from
coc;
+
-
-
-
-
+
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
+
|
ID
| ClassID | CourseID |
+
-
-
-
-
+
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
+
|
1
|
1
|
2
|
|
2
|
1
|
5
|
|
3
|
2
|
2
|
|
4
|
2
|
6
|
|
5
|
3
|
1
|
mysql> select
*
from
courses;
+
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| CourseID | Course |
+
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
|
1
| Hamo Gong |
|
2
| Kuihua Baodian |
|
3
| Jinshe Jianfa |
|
4
| Taiji Quan |
|
5
| Daiyu Zanghua |
|
6
| Weituo Zhang |
|
7
| Dagou Bangfa |
+
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
#彼此有着对应关系
|
1、联结查询：事先将两张或多张表join；根据join的结果进行查询；
-
cross join：交叉联结
由于需要把多张表交叉联结后载入内存输入；所以用的较少
-
自然联结(内联结)：
等值联结：把两张表中的对应字段做联结
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
mysql> select students.Name,classes.Class
from
students,classes where students.ClassID
=
classes.ClassID;
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| Name | Class |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| Shi Zhongyu | Emei Pai |
| Shi Potian | Shaolin Pai |
| Xie Yanke | Emei Pai |
| Ding Dian | Wudang Pai |
#
#
#等值联结；但是由于表中有相同的字段名；每次选择是都要写表.字段；可以做别名来处理
#
#
mysql> select s.Name,c.Class
from
students as s,classes as c where s.ClassID
=
c.ClassID;
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| Name | Class |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| Shi Zhongyu | Emei Pai |
| Shi Potian | Shaolin Pai |
| Xie Yanke | Emei Pai |
| Ding Dian | Wudang Pai |
| Yu Yutong | QingCheng Pai |
|
-
外联结：
左外联结：只保留出现在左外联结运算之前(左边)的关系中的元组；
left_tb left join right_tb on 连接条件
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql> select Name,Class
from
students as s left join classes as c on s.ClassID
=
c.ClassID;
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| Name | Class |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| Shi Zhongyu | Emei Pai |
| Shi Potian | Shaolin Pai |
| Xie Yanke | Emei Pai |
| Xiao Qiao | Shaolin Pai |
| Ma Chao | Wudang Pai |
....
#中间省略
| Xu Xian | NULL |
#这里显示对应的为空
| Sun Dasheng | NULL |
| Tom | Xiaoyao Pai |
| Jerry | Xiaoyao Pai |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
27
rows
in
set
(
0.00
sec)
|
右外联结：只保留出现在右外联结运算之后(右边)的关系中的元组；
left_tb right join right_tb on 连接条件
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql> select Name,Class
from
students as s right join classes as c on s.ClassID
=
c.ClassID;
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| Name | Class |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| Shi Zhongyu | Emei Pai |
| Shi Potian | Shaolin Pai |
| Xie Yanke | Emei Pai |
...省略部分....
| Huang Yueying | Lianshan Pai |
| Xiao Qiao | Shaolin Pai |
| Ma Chao | Wudang Pai |
| Tom | Xiaoyao Pai |
| Jerry | Xiaoyao Pai |
| NULL | Mo jiao |
#以右表为准；左边没有的留空
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
26
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
|
mysql> select
*
from
students;
+
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
+
|
1
| Shi Zhongyu |
22
| M |
2
|
3
|
|
2
| Shi Potian |
22
| M |
1
|
7
|
|
3
| Xie Yanke |
53
| M |
2
|
16
|
#假设该表中的StuID与TeacherID是相互对应的；既是老师同时也是学生
mysql> select t.Name,s.Name
from
students as s,students as t where s.StuID
=
t.TeacherID;
+
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| Name | Name |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| Shi Zhongyu | Xie Yanke |
| Shi Potian | Xi Ren |
| Xie Yanke | Xu Zhu |
| Ding Dian | Ding Dian |
| Yu Yutong | Shi Zhongyu |
| Tom | Wen Qingqing |
| Jerry | Shi Potian |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
7
rows
in
set
(
0.00
sec)
|
2、子查询：在查询中嵌套的查询
用于where中的子查询
-
用于比较表达式中的子查询
子查询的返回值只能有一个；
-
用于exists中的子查询；
判断存在与否
-
用于in中的子查询；
判断存在于指定列表中
Examples：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> select Name,Age
from
students where Age > (select avg(Age)
from
students);
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
| Name | Age |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
| Xie Yanke |
53
|
| Ding Dian |
32
|
| Shi Qing |
46
|
| Tian Boguang |
33
|
| Xu Xian |
27
|
| Sun Dasheng |
100
|
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
6
rows
in
set
(
0.00
sec)
mysql>
#大于全部平均年龄的同学及其年龄
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> select Name,Age,Gender
from
(select
*
from
students where Gender
=
'M'
) as s where Age >
25
;
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
-
-
-
-
-
-
-
-
+
| Name | Age | Gender |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
-
-
-
-
-
-
-
-
+
| Xie Yanke |
53
| M |
| Ding Dian |
32
| M |
| Yu Yutong |
26
| M |
| Shi Qing |
46
| M |
| Tian Boguang |
33
| M |
| Xu Xian |
27
| M |
| Sun Dasheng |
100
| M |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
-
-
-
-
-
-
-
-
+
7
rows
in
set
(
0.00
sec)
#年龄大于25的性别为男的同学及其年龄性别
|
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> select Name,ClassID
from
students where ClassID
in
(select s.ClassID
from
classes as s left join coc on s.ClassID
=
coc.ClassID where coc.ClassID
is
null);
+
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
+
| Name | ClassID |
+
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
+
| Tom |
8
|
| Jerry |
8
|
+
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
+
2
rows
in
set
(
0.00
sec)
#
#
#查询未开课的班级的学生
|
mysql不擅长于子查询：应该避免使用子查询；可以使用连接查询
用于from中子查询：
-
select clo,... from (select clause) as alias where condition;
3、mysql的联合查询：
-
把两个或多个查询语句的结果合并成一个结果进行输出；
select clause union select clause union ...
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> select Name,Age
from
students union select Name,Age
from
teachers;
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
| Name | Age |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
| Shi Zhongyu |
22
|
| Shi Potian |
22
|
| Xie Yanke |
53
|
| Ding Dian |
32
|
| Yu Yutong |
26
|
| Shi Qing |
46
|
#
#把两个或多个查询语句的结果合并成一个结果进行输出；需要对应的字段
|
四、MySQL视图
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
存储下来的select语句；
mysql>
help
create view
Name:
'CREATE VIEW'
Description:
Syntax:
CREATE
[OR REPLACE]
[ALGORITHM
=
{UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER
=
{ user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
#
#
删除视图
drop view view_name;
|
Examples：
|
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
67
68
|
mysql> create view stu as select StuID,Name,Age,Gender
from
students;
Query OK,
0
rows affected (
0.05
sec)
mysql> show tables;
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| Tables_in_hellodb |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| classes |
| coc |
| courses |
| scores |
| stu |
| students |
| teachers |
| toc |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
8
rows
in
set
(
0.00
sec)
mysql> show table status\G
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
5.
row
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
Name: stu
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
mysql> grant
all
on hellodb.stu to
'testuser'
@
'172.16.%.%'
identified by
'test'
;
Query OK,
0
rows affected (
0.04
sec)
#
[root@Soul ~]
# mysql -utestuser -h172.16.251.85 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ;
or
\g.
Your MySQL connection
id
is
12
mysql>
mysql> use hellodb
Database changed
mysql> show tables;
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| Tables_in_hellodb |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| stu |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
1
row
in
set
(
0.00
sec)
mysql> desc stu;
+
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
+
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
+
| Field |
Type
| Null | Key | Default | Extra |
+
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
+
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
+
| StuID |
int
(
10
) unsigned | NO | |
0
| |
| Name | varchar(
50
) | NO | | NULL | |
| Age | tinyint(
3
) unsigned | NO | | NULL | |
| Gender | enum(
'F'
,
'M'
) | NO | | NULL | |
+
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
+
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
+
4
rows
in
set
(
0.05
sec)
mysql>
|
五、具体实例
1、显示前5位同学的姓名、课程及成绩；
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> select Name,Course,Score
from
students as s,scores as ss,courses as c where s.StuID <
=
5
and
c.CourseID
=
ss.CourseID
and
s.StuID
=
ss.StuID;
+
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
+
| Name | Course | Score |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
+
| Shi Zhongyu | Kuihua Baodian |
77
|
| Shi Zhongyu | Weituo Zhang |
93
|
| Shi Potian | Kuihua Baodian |
47
|
| Shi Potian | Daiyu Zanghua |
97
|
| Xie Yanke | Kuihua Baodian |
88
|
| Xie Yanke | Weituo Zhang |
75
|
| Ding Dian | Daiyu Zanghua |
71
|
| Ding Dian | Kuihua Baodian |
89
|
| Yu Yutong | Hamo Gong |
39
|
| Yu Yutong | Dagou Bangfa |
63
|
+
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
+
10
rows
in
set
(
0.00
sec)
mysql>
|
2、显示其成绩高于80的同学的名称及课程；
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> select Name,Course,Score
from
students as s,scores as ss,courses as c where s.StuID
=
ss.StuID
and
c.CourseID
=
ss.CourseID
and
ss.Score >
80
;
+
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
+
| Name | Course | Score |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
+
| Shi Zhongyu | Weituo Zhang |
93
|
| Shi Potian | Daiyu Zanghua |
97
|
| Xie Yanke | Kuihua Baodian |
88
|
| Ding Dian | Kuihua Baodian |
89
|
| Shi Qing | Hamo Gong |
96
|
| Xi Ren | Hamo Gong |
86
|
| Xi Ren | Dagou Bangfa |
83
|
| Lin Daiyu | Jinshe Jianfa |
93
|
+
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
+
8
rows
in
set
(
0.00
sec)
mysql>
|
3、求前8位同学每位同学自己两门课的平均成绩，并按降序排列；
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> select Name,avg(Score)
from
students as s,scores as ss,courses as c where s.StuID <
=
8
and
c.CourseID
=
ss.CourseID
and
s.StuID
=
ss.StuID group by Name order by avg(Score) desc;
+
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
+
| Name | avg(Score) |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
+
| Shi Qing |
96.0000
|
| Shi Zhongyu |
85.0000
|
| Xi Ren |
84.5000
|
| Xie Yanke |
81.5000
|
| Ding Dian |
80.0000
|
| Lin Daiyu |
75.0000
|
| Shi Potian |
72.0000
|
| Yu Yutong |
51.0000
|
+
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
+
8
rows
in
set
(
0.00
sec)
mysql>
|
4、显示每门课程课程名称及学习了这门课的同学的个数；
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> select Course,count(Name)
from
courses as c,students as s,coc where c.CourseID
=
coc.CourseID
and
coc.ClassID
=
s.ClassID group by Course;
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| Course | count(Name) |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| Dagou Bangfa |
4
|
| Daiyu Zanghua |
8
|
| Hamo Gong |
5
|
| Jinshe Jianfa |
7
|
| Kuihua Baodian |
11
|
| Taiji Quan |
7
|
| Weituo Zhang |
3
|
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
+
7
rows
in
set
(
0.01
sec)
|
5、如何显示其年龄大于平均年龄的同学的名字？
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> select Name,Age
from
students where Age > (select avg(Age)
from
students);
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
| Name | Age |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
| Xie Yanke |
53
|
| Ding Dian |
32
|
| Shi Qing |
46
|
| Tian Boguang |
33
|
| Xu Xian |
27
|
| Sun Dasheng |
100
|
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
6
rows
in
set
(
0.00
sec)
|
6、如何显示其学习的课程为第1、2，4或第7门课的同学的名字？
|
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
|
mysql> select Name
from
students as s,courses as c,coc where c.CourseID
in
(
1
,
2
,
4
,
7
)
and
c.CourseID
=
coc.CourseID
and
coc.ClassID
=
s.ClassID group by Name;
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| Name |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
| Diao Chan |
| Ding Dian |
| Duan Yu |
| Hua Rong |
| Huang Yueying |
| Lin Chong |
| Lin Daiyu |
| Lu Wushuang |
| Ma Chao |
| Ren Yingying |
| Shi Potian |
| Shi Qing |
| Shi Zhongyu |
| Tian Boguang |
| Wen Qingqing |
| Xi Ren |
| Xiao Qiao |
| Xie Yanke |
| Xu Zhu |
| Xue Baochai |
| Yu Yutong |
| Yuan Chengzhi |
| Yue Lingshan |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
23
rows
in
set
(
0.01
sec)
|
7、如何显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学？
|
1
2
3
4
5
6
7
8
9
|
mysql> select Name,Age
from
(select Class,c.ClassID
from
students as s,classes as c where s.ClassID
=
c.ClassID group by Class having count(Name) >
=
3
) as c,students as s where c.ClassID
=
s.ClassID
and
Age > (select avg(Age)
from
students);
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
| Name | Age |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
| Xie Yanke |
53
|
| Ding Dian |
32
|
| Tian Boguang |
33
|
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
3
rows
in
set
(
0.14
sec)
|
8、统计各班级中年龄大于全校同学平均年龄的同学。
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> select Name,Age
from
students as s,classes as c where s.ClassID
=
c.ClassID
and
Age > (select avg(Age)
from
students);
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
| Name | Age |
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
| Xie Yanke |
53
|
| Ding Dian |
32
|
| Shi Qing |
46
|
| Tian Boguang |
33
|
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
+
4
rows
in
set
(
0.00
sec)
mysql>
|
此篇到此结束；后续继续更新。
本文转自Mr_陈 51CTO博客，原文链接：http://blog.51cto.com/chenpipi/1393733，如需转载请自行联系原作者
