1
思考：一对一两个表完全可以用一个表实现，为什么还要分成两个表？
2

3
答：在字段数量很多情况下，数据量也就很大，每次查询都需要检索大量数据，这样效率低下。我们可以将所有字段分成两个部分，“常用字段”和“不常用字段”，这样对大部分查询者来说效率提高了。【表的垂直分割】

1
BBS论坛的基本功能：
2
用户注册和登录，后台数据库需要存放用户的注册信息和在线状态信息；
3
用户发贴，后台数据库需要存放贴子相关信息，如贴子内容、标题等；
4
用户可以对发帖进行回复；
5
论坛版块管理：后台数据库需要存放各个版块信息，如版主、版块名称、贴子数等；

1
参与的对象有：用户、发的帖子、跟帖、板块

1
多学一招：上面的设计不满足第三范式，但是高考分数表就是这样设计的，为什么？
2

3
答：高考分数峰值访问量非常大，这时候就是性能更重要。当性能和规范化冲突的时候，我们首选性能。这就是“反三范式”。

1
公司承担多个工程项目，每一项工程有：工程号、工程名称、施工人员等
2
公司有多名职工，每一名职工有：职工号、姓名、性别、职务（工程师、技术员）等
3
公司按照工时和小时工资率支付工资，小时工资率由职工的职务决定（例如，技术员的小时工资率与工程师不同）

1
A：新人入职需要虚拟一个项目
2

3
B：职务更改，小时工资率可能会忘记更改，造成数据不完整
4

5
C：有人离职，删除记录后，工程也没有了

1
第一步：这个表满足第一范式
2

3
第二步：这个表不是描述了一件事情

1
第三步：是否满足第三范式

1
mysql> select '锄禾日当午';
2
+------------+
3
| 锄禾日当午          |
4
+------------+
5
| 锄禾日当午          |
6
+------------+
7

8
mysql> select 10*10;
9
+-------+
10
| 10*10 |
11
+-------+
12
|   100 |
13
+-------+

1
mysql>  select '锄禾日当午' as content;
2
+------------+
3
| content    |
4
+------------+
5
| 锄禾日当午          |
6
+------------+
7
1 row in set (0.00 sec)
8

9
mysql> select 10*10 as result;
10
+--------+
11
| result |
12
+--------+
13
|    100 |
14
+--------+
15
1 row in set (0.00 sec)

1
mysql> select 10*10  result;
2
+--------+
3
| result |
4
+--------+
5
|    100 |
6
+--------+
7
1 row in set (0.00 sec)

1
mysql> create table t1(
2
    -> id int,
3
    -> name varchar(10)
4
    -> );
5
Query OK, 0 rows affected (0.05 sec)
6

7
mysql> create table t2(
8
    -> field1 varchar(10),
9
    -> field2 varchar(10)
10
    -> );
11
Query OK, 0 rows affected (0.00 sec)
12

13
mysql> insert into t1 values (1,'tom'),(2,'berry');
14
Query OK, 2 rows affected (0.00 sec)
15
Records: 2  Duplicates: 0  Warnings: 0
16

17
mysql> insert into t2 values ('333','333'),('444','444');
18
Query OK, 2 rows affected (0.02 sec)
19
Records: 2  Duplicates: 0  Warnings: 0

1
mysql> select * from t1,t2;              # 返回笛卡尔积
2
+------+-------+--------+--------+
3
| id   | name  | field1 | field2 |
4
+------+-------+--------+--------+
5
|    1 | tom   | 333    | 333    |
6
|    2 | berry | 333    | 333    |
7
|    1 | tom   | 444    | 444    |
8
|    2 | berry | 444    | 444    |
9
+------+-------+--------+--------+
10
4 rows in set (0.00 sec)

1
mysql> select 10*10 as result from dual;  #dual表是用来保证select语句的完整性。
2
+--------+
3
| result |
4
+--------+
5
|    100 |
6
+--------+

1
mysql> select * from stu where stusex='男';    # 查找性别是男的记录
2
mysql> select * from stu where stuage>=20;    # 查找年龄不低于20的记录

1
select * from stu where 1      # 返回所有数据库
2
select * from stu where 0    #返回空记录

1
mysql> select * from stu where stuaddress='上海' or stuaddress='北京';
2
+--------+---------+--------+--------+---------+------------+------+------+
3
| stuNo  | stuName | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
4
+--------+---------+--------+--------+---------+------------+------+------+
5
| s25301 | 张秋丽        | 男       |     18 |       1 | 北京           |   80 | NULL |
6
| s25302 | 李文才       | 男       |     31 |       3 | 上海          |   77 |   76 |
7
| s25303 | 李斯文       | 女      |     22 |       2 | 北京           |   55 |   82 |
8
| s25320 | Tom     | 男       |     24 |       8 | 北京           |   65 |   67 |
9
+--------+---------+--------+--------+---------+------------+------+------+

1
mysql> select * from stu where stuaddress in ('北京','上海');

1
mysql> select * from stu where stuno in ('s25301','s25302','s25303');

1
mysql> select * from stu where stuage in(18,19,20);

1
mysql> select * from stu where stuaddress not in ('北京','上海');

1
mysql> select * from stu where stuage>=18 and stuage<=20;   # 方法一
2

3
mysql> select * from stu where stuage between 18 and 20;   # 方法二

1
mysql> select * from stu where stuage<18 or stuage>20;    #方法一
2

3
mysql> select * from stu where not (stuage>=18 and stuage<=20);
4

5
mysql> select * from stu where stuage not between 18 and 20;

1
mysql> select * from stu where ch is null or math is null; # 查找缺考的人
2
+--------+----------+--------+--------+---------+------------+------+------+
3
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
4
+--------+----------+--------+--------+---------+------------+------+------+
5
| s25301 | 张秋丽         | 男       |     18 |       1 | 北京           |   80 | NULL |
6
| s25304 | 欧阳俊雄        | 男       |     28 |       4 | 天津           | NULL |   74 |
7
+--------+----------+--------+--------+---------+------------+------+------+

1
mysql> select * from stu where ch is not null and math is not null;

1
#求语文总分、语文平均分、语文最高分、语文最低分、总人数
2

3
mysql> select sum(ch) '语文总分',avg(ch) '语文平均分', max(ch) '语文最高分',min(ch) '语文最低分',count(*) '总人数' from stu;
4
+----------+------------+------------+------------+--------+
5
| 语文总分        | 语文平均分          | 语文最高分          | 语文最低分          | 总人数       |
6

7
+----------+------------+------------+------------+--------+
8
|      597 |    74.6250 |         88 |         55 |      9 |
9
+----------+------------+------------+------------+--------+
10
1 row in set (0.00 sec)

1
# 查找姓张的同学
2
mysql> select * from stu where stuname like '张%';
3
+--------+---------+--------+--------+---------+------------+------+------+
4
| stuNo  | stuName | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
5
+--------+---------+--------+--------+---------+------------+------+------+
6
| s25301 | 张秋丽        | 男       |     18 |       1 | 北京           |   80 | NULL |
7
+--------+---------+--------+--------+---------+------------+------+------+
8
1 row in set (0.00 sec)
9
#例题
10
mysql> select * from stu where stuname like 'T_m';
11
+--------+---------+--------+--------+---------+------------+------+------+
12
| stuNo  | stuName | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
13
+--------+---------+--------+--------+---------+------------+------+------+
14
| s25320 | Tom     | 男       |     24 |       8 | 北京           |   65 |   67 |
15
+--------+---------+--------+--------+---------+------------+------+------+
16
1 row in set (0.00 sec)

1
mysql> select * from stu order by ch desc;    # 语文成绩降序排列
2

3
mysql> select * from stu order by math asc;     # 数学成绩升序排列
4

5
mysql> select * from stu order by math;       # 默认升序排列

1
#年龄升序,成绩降序
2
mysql> select *,(ch+math) as '总分' from stu order by stuage asc,(ch+math) desc;

1
select * from stu order by stuage desc,ch desc;     #年龄降序，语文降序
2
select * from stu order by stuage desc,ch asc;    #年龄降序，语文升序
3
select * from stu order by stuage,ch desc;          #年龄升序、语文降序
4
select * from stu order by stuage,ch;         #年龄升序、语文升序

1
# 按性别分组，显示每组的平均年龄
2
mysql> select avg(stuage) as '年龄',stusex from stu group by stusex;
3
+---------+--------+
4
| 年龄        | stusex |
5
+---------+--------+
6
| 22.7500 | 女      |
7
| 25.4000 | 男       |
8
+---------+--------+
9
2 rows in set (0.00 sec)
10
# 按地区分组，每个地区的平均年龄
11
mysql> select avg(stuage) as '年龄',stuaddress from stu group by stuaddress;
12
+---------+------------+
13
| 年龄        | stuaddress |
14
+---------+------------+
15
| 31.0000 | 上海          |
16
| 21.3333 | 北京           |
17
| 27.0000 | 天津           |
18
| 23.0000 | 河北          |
19
| 23.0000 | 河南           |
20
+---------+------------+
21
5 rows in set (0.00 sec)

1
脚下留心：
2
1、如果是分组查询，查询字段必须是分组字段和聚合函数。
3
2、查询字段是普通字段，只取第一个值

1
mysql> select group_concat(stuname),stusex from stu group by stusex;
2
+-------------------------------------+--------+
3
| group_concat(stuname)               | stusex |
4
+-------------------------------------+--------+
5
| 李斯文,诸葛丽丽,梅超风,Tabm                           | 女      |
6
| 张秋丽,李文才,欧阳俊雄,争青小子,Tom                          | 男       |
7
+-------------------------------------+--------+
8
2 rows in set (0.00 sec)

1
多学一招：【了解】
2
1、分组后的结果默认会按升序排列显示
3
2、也是可以使用desc实现分组后的降序

1
mysql> select stuaddress,stusex,avg(stuage) from stu group by stuaddress,stusex;
2
+------------+--------+-------------+
3
| stuaddress | stusex | avg(stuage) |
4
+------------+--------+-------------+
5
| 上海          | 男       |     31.0000 |
6
| 北京           | 女      |     22.0000 |
7
| 北京           | 男       |     21.0000 |
8
| 天津           | 男       |     27.0000 |
9
| 河北          | 女      |     23.0000 |
10
| 河南           | 女      |     23.0000 |
11
+------------+--------+-------------+
12
6 rows in set (0.00 sec)

1
思考：数据库中的表是一个二维表，返回的结果是一张二维表，既然能在数据库的二维表中进行查询，能否在结果集的二维表上继续进行查询？
2

3
答：可以，having条件就是在结果集上继续进行筛选。

1
mysql> select * from stu where stusex='男';   # 从数据库中查找
2
+--------+----------+--------+--------+---------+------------+------+------+
3
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
4
+--------+----------+--------+--------+---------+------------+------+------+
5
| s25301 | 张秋丽         | 男       |     18 |       1 | 北京           |   80 | NULL |
6
| s25302 | 李文才        | 男       |     31 |       3 | 上海          |   77 |   76 |
7
| s25304 | 欧阳俊雄        | 男       |     28 |       4 | 天津           | NULL |   74 |
8
| s25318 | 争青小子        | 男       |     26 |       6 | 天津           |   86 |   92 |
9
| s25320 | Tom      | 男       |     24 |       8 | 北京           |   65 |   67 |
10
+--------+----------+--------+--------+---------+------------+------+------+
11
5 rows in set (0.00 sec)
12

13
mysql> select * from stu having stusex='男';   # 从结果集中查找
14
+--------+----------+--------+--------+---------+------------+------+------+
15
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
16
+--------+----------+--------+--------+---------+------------+------+------+
17
| s25301 | 张秋丽         | 男       |     18 |       1 | 北京           |   80 | NULL |
18
| s25302 | 李文才        | 男       |     31 |       3 | 上海          |   77 |   76 |
19
| s25304 | 欧阳俊雄        | 男       |     28 |       4 | 天津           | NULL |   74 |
20
| s25318 | 争青小子        | 男       |     26 |       6 | 天津           |   86 |   92 |
21
| s25320 | Tom      | 男       |     24 |       8 | 北京           |   65 |   67 |
22
+--------+----------+--------+--------+---------+------------+------+------+
23
5 rows in set (0.00 sec)

1
mysql> select * from stu limit 0,2;    # 从0的位置开始，取两条数据
2
+--------+---------+--------+--------+---------+------------+------+------+
3
| stuNo  | stuName | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
4
+--------+---------+--------+--------+---------+------------+------+------+
5
| s25301 | 张秋丽        | 男       |     18 |       1 | 北京           |   80 | NULL |
6
| s25302 | 李文才       | 男       |     31 |       3 | 上海          |   77 |   76 |
7
+--------+---------+--------+--------+---------+------------+------+------+
8
2 rows in set (0.00 sec)
9

10
mysql> select * from stu limit 2,2;    # 从2的位置开始，取两条数据
11
+--------+----------+--------+--------+---------+------------+------+------+
12
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
13
+--------+----------+--------+--------+---------+------------+------+------+
14
| s25303 | 李斯文        | 女      |     22 |       2 | 北京           |   55 |   82 |
15
| s25304 | 欧阳俊雄        | 男       |     28 |       4 | 天津           | NULL |   74 |
16
+--------+----------+--------+--------+---------+------------+------+------+

1
mysql> select * from stu limit 2;
2
+--------+---------+--------+--------+---------+------------+------+------+
3
| stuNo  | stuName | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
4
+--------+---------+--------+--------+---------+------------+------+------+
5
| s25301 | 张秋丽        | 男       |     18 |       1 | 北京           |   80 | NULL |
6
| s25302 | 李文才       | 男       |     31 |       3 | 上海          |   77 |   76 |
7
+--------+---------+--------+--------+---------+------------+------+------+
8
2 rows in set (0.00 sec)

1
mysql> select *,(ch+math) total from stu order by total desc limit 0,3;
2
+--------+----------+--------+--------+---------+------------+------+------+-------+
3
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress | ch   | math | total |
4
+--------+----------+--------+--------+---------+------------+------+------+-------+
5
| s25318 | 争青小子        | 男       |     26 |       6 | 天津           |   86 |   92 |   178 |
6
| s25321 | Tabm     | 女      |     23 |       9 | 河北          |   88 |   77 |   165 |
7
| s25302 | 李文才        | 男       |     31 |       3 | 上海          |   77 |   76 |   153 |
8
+--------+----------+--------+--------+---------+------------+------+------+-------+

1
mysql> select distinct stuaddress from stu;
2
+------------+
3
| stuaddress |
4
+------------+
5
| 上海          |
6
| 天津           |
7
| 河南           |
8
| 河北          |
9
| 北京           |
10
+------------+
11
5 rows in set (0.00 sec)

1
语法：select 语句 union [选项] select 语句 union [选项] select 语句

1
mysql> select stuno,stuname from stu union select id,name from Go1;
2
+--------+----------+
3
| stuno  | stuname  |
4
+--------+----------+
5
| s25301 | 张秋丽         |
6
| s25302 | 李文才        |
7
| s25303 | 李斯文        |
8
| s25304 | 欧阳俊雄        |
9
| s25305 | 诸葛丽丽         |
10
| s25318 | 争青小子        |
11
| s25319 | 梅超风        |
12
| s25320 | Tom      |
13
| s25321 | Tabm     |
14
| 1      | 李白         |
15
| 2      | 张秋丽         |
16
+--------+----------+

1
mysql> select stuname,stuaddress,stusex from stu where (stuaddress='上海' and stusex='男') or (stuaddress='北京' and stusex='女');
2
+---------+------------+--------+
3
| stuname | stuaddress | stusex |
4
+---------+------------+--------+
5
| 张秋丽        | 上海          | 男       |
6
| 梅超风       | 北京           | 女      |
7
+---------+------------+--------+
8
2 rows in set (0.00 sec)
9

10
mysql> select stuname,stuaddress,stusex from stu where stuaddress='上海' and stusex='男' union select stuname,stuaddress,stusex from stu where stuaddress='北京' and stusex='女';
11
+---------+------------+--------+
12
| stuname | stuaddress | stusex |
13
+---------+------------+--------+
14
| 张秋丽        | 上海          | 男       |
15
| 梅超风       | 北京           | 女      |
16
+---------+------------+--------+
17
2 rows in set (0.02 sec)

1
mysql> select name from go1 union select stuname from stu;
2
+----------+
3
| name     |
4
+----------+
5
| 李白         |
6
| 张秋丽         |
7
| 李文才        |
8
| 李斯文        |
9
| 欧阳俊雄        |
10
| 诸葛丽丽         |
11
| 争青小子        |
12
| 梅超风        |
13
| Tom      |
14
| Tabm     |
15
+----------+

1
mysql> select name from go1 union all select stuname from stu;  # all不去重复记录
2
+----------+
3
| name     |
4
+----------+
5
| 李白         |
6
| 张秋丽         |
7
| 张秋丽         |
8
| 李文才        |
9
| 李斯文        |
10
| 欧阳俊雄        |
11
| 诸葛丽丽         |
12
| 争青小子        |
13
| 梅超风        |
14
| Tom      |
15
| Tabm     |
16
+----------+