MySQL数据库二

1.2 实体之间的关系

1.2.1 一对多(1:n)

主表中的一条记录对应从表中的多条记录

实现一对多的方式：主键和非主键关系

问题：说出几个一对多的关系？

班主任表--学生表
品牌表--商品表

1.2.2 多对一（N：1）

多对一就是一对多

1.2.3 一对一(1:1)

如何实现一对一:主键和主键建关系

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

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

1.2.4 多对多（N:M）

主表中的一条记录对应从表中的多条记录，从表中的一条记录，对应主表中的多条记录

如何实现多对多：利用第三张关系表
问题：说出几个多对多的关系？

讲师表 --- 学生表
课程表 --- 学生表
商品表 -- 订单表

小结：

如何实现一对一：主键和主键建关系
如果实现一对多：主键和非主键建关系
如何实现多对多：引入第三张关系表

1.3 数据库设计

1.3.1 数据库设计的步骤

1、收集信息：与该系统有关人员进行交流、坐谈、充分理解数据库需要完成的任务
2、标识对象（实体-Entity）:标识数据要管理的关键对象或实体
3、标识每个实体的属性（Attribute）
4、标识对象之间的关系(Relationship)
5、将模型转换成数据库
6、规范化

1.3.2 例题

第一步：收集信息

第二步：标识对象

第三步：标识每个实体的属性

第四步：标识对象之间的关系

1.3.3 绘制E-R图

E-R（（Entity-Relationship）实体关系图）

1.3.4 将E-R图转成表

1、实体转成表，属性转成字段
2、如果没有合适的字段做主键，给表添加一个自动增长列做主键。

1.4 数据规范化

1.4.1 第一范式：确保每列原则性

第一范式：的目标是确保每列的原子性，一个字段表示一个含义

思考：地址包含省、市、县、地区是否需要拆分？
答：如果仅仅起地址的作用，不需要统计，可以不拆分；如果有按地区统计的功能需要拆分。在实际项目中，建议拆分。

1.4.2 第二范式：非关键字段必须依赖于键字段

第二范式（2nd NF）在满足第一范式的前提下，要求每个表只描述一件事件

1.4.3第三范式：消除传递依赖

第三范式：（3rd NF）在满足第二范式的前提下，除了主键以外的其他列消除传递依赖。

学号	姓名	语文	数学	总分

不满足第三范式，总分依赖于语文和数学

1.4.4 反3NF

范式越高，数据冗余越少，但是效率有时就越低下，为了提高运行效率，可以适当让数据冗余。

多学一招：上面的设计不满足第三范式，但是高考分数表就是这样设计的，为什么？
答：高考分数峰值访问量非常大，这时候就是性能更重要。当性能和规范化冲突的时候，我们首选性能。这就是“反三范式”。

小结：
1、第一范式约束的所有字段
2、第二范式约束的主键和非主键的关系
3、第三范式约束的非主键之间的关系
4、范式越高，冗余越少，但表也越多
5、规范化和性能的关系：性能比规范化更重要

1.4.5 例题

需求

假设某建筑公司要设计一个数据库。公司的业务规则概括说明如下：
公司承担多个工程项目，每一项工程有：工程号、工程名、施工人员等
公司有多名职工，每一名职工有：职工号、姓名、性别、职务（工程师、技术员）等
公司按照工时和小时工资率支付工资，小时工资率由职工的职务决定（例如，技术员的小时工资率与工程师不同）

标识实体

1、工程
2、职工
3、职务
4、小时工资率

1.5 查询语句

语法：select [选项] 列名 [from 表名] [where 条件] [group by 分组] [order by 排序] [having 条件][limt 限制]

1.5.1 字段表达式

-- 可以直接输出内容
mysql>select '锄禾日当午'；
锄禾日当午
锄禾日当午

---输出表达式
mysql>select 10*10
10*10
100

mysql>select ch,math,ch+math from stu;
ch   math  ch+math
80   NULL  NULL
77   76      153
55    82     137
NULL  74  NULL
--表达式部分可以用函数
mysql>select rand();
rand()
0.665454545545

通过as给字段取别名

mysql>select '锄禾日当午' as ‘标题’; --取别名
标题
锄禾日当午

mysql>select ch,math,ch+math as '总分' from stu;
ch    math   总分
80  NULL   NULL
77   76      153
55   82      137
NULL  74    NULL

多学一招：as可以省略

mysql>select ch,math,ch+math '总分' from stu;
ch   math  总分
80  NULL   NULL
77   76      153
55    82      137
NULL   74   NULL

1.5.2 from子句

from：来自。from后面跟的是数据源数据源可以有多个。返回笛卡尔积。
插入测试表

create table t1(
		str char(2)
);
insert into t1 values('aa'),('bb');
create table t2(
		num int
);
insert into t2 values (10),(20);

测试

-- from子句
mysql>select * from t1;
str
aa
bb
-- 测试多个数据源，返回笛卡尔积
mysql>select * from t1,t2;
str   num
aa   10
bb    10
aa    20
bb    20

1.5.3 dual表

dual表是一个伪表，在有些特定情况下，没有具体的表的参与，但是为了保证select语句的完整又必须要一个表名，这时候就使用伪表。

select 10*10 as 结果 from dual;

1.5.4 where子句

where后面跟的是条件，在数据源中进行筛选。返回条件为真记录 MySQL支持的运算符

> 大于
< 小于
>= 大于等于
<= 小于等于
= 等于
！= 不等于
and 与
or 或
not 非
in | not in
between_and | not between_and
is null | is not null

例题：

--查找语文成绩及及格的学生
mysql>select * from stu where ch>=60;
--查找语文和数学都及格的学生
mysql>select * from stu where ch>=60 and math>=60;
--查找语文或数学不及格的学生
mysql>select * from stu where ch<60 or math<60;

思考：如下语句输出什么？

mysql>select * from stu where 1;--输出所有数据
mysql>select * from stu where 0;--不输出数据，为空

思考：如何查找北京和上海的学生

mysql>select * from stu where stuaddress='北京' or stuaddress='上海';

-- 通过in语句实现
mysql>select * from stu where stuaddress in ('北京','上海');

--查询不是北京和上海的学生
mysql>select * from stu where stuaddress not in ('北京','上海')；

思考：查找年龄在20~25之间

-- 方法一：
mysql>select * from stu where stuage>=20 and stuage<=25;
--方法二：
mysql>select * from stu where not(stuage<20 or stuage>25);
--方法三：between...and...
mysql>select * from stu where stuage between20 and 25;
--年龄不在20~25之间
mysql>select * from stu where stuage not between 20and 25;

思考：查找缺考的考生

mysql>select * from stu where ch is null or math is null;
-- 查找没有缺考的学生
mysql>select * from stu where ch is not null and math is not null;

练习：

--1、查找学号是s25301,s25302,s25303的学生
mysql>select * from stu where stuno in ('s25301','s25302','s25303');
-- 2、查找年龄是18~20的学生
mysql>select * from stu where stuage between 18 and 20;

1.5.5 group by [分组查询]

将查询的结果分组，分组查询目的在于统计数据。

--查询男生和女生的各自语文平均分
select stusex,avg(ch) '平均分' from stu group by stusex;
stusex  平均分
女		72.5
男        80
--查询男生和女生各自多少人

stusex  人数
女  		5
男		6
--查询每个地区多少人
mysql>select stuaddress.count(*) from stu group by stuaddress;
stuaddress   count(*)
上海             1
北京				2
河南			8
河北				3
--每个地区的数学平均分
select stuaddress,avg(math) from stu group by stuaddress;
stuaddress  avg(amth)
上海				76.5500
北京				85.1555
...

查询字段是普通字段，只取第一个值

通过group_concat()函数将同一组的值连接起来显示

select group_concat(stuname),stusex,avg(math) from stu group by stusex;

多学一招：【了解】

1、分组后的结果默认会按升序排列显示
2、也是可以使用desc实现分组后的排序

多列分组

select stuaddress,stusex,avg(math) from stu group by stuaddress,stusex;
stuaddress  stusex  ang(math)
上海				男			76.5500
北京				女  			82.5100
北京				男			72.6600
....

小结：
1、如果是分组查询，查询字段必须是分组字段和聚合函数。
2、查询字段是普通字段，只取第一个值
3、group_concat()将同一组的数据连接起来

1.5.6 order by排序

asc:升序【默认】
desc:降序

--按年龄的升序排列
mysql>select *from stu order by stuage asc;
mysql>selcet * from stu order by stuage; --默认是升序排列

--按总分降序
mysql>select *,ch+math '总分' from stu order by ch+math desc

多列排序

--年龄升序，如果年龄一样，按ch降序排列
mysql>select * from stu order by stuage asc,ch desc;

思考如下代码表示什么含义

select * from stu order by stuage desc,ch desc; # 年龄降序  语文降序
select *from stu order by stuage desc,ch asc; #年龄降序 语文升序
select * from stu order by stuage,ch desc;  #年龄升序 语文降序
select * from stu order by stuage,ch;  #默认升序排列  年龄升序、语文升序

1.5.7 having条件

having：是在结果集上进行筛选

例题：

--查询女生
select * from stu where stusex='女';

--查询女生
select * from stu having stusex='女';

--查询女生姓名
select stuname from stu where stusex='女';
-- 使用having报错，因为结果采集中没有stusex字段
select  tuname from stu having stusex='女'

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

小结：
having和where的区别：
where是对原始数据进行筛选，having是对记录进行筛选。

1.5.8 limit

语法：limit起始位置，显示长度

--从第0个位置开始取，取3条记录
select * from stu limit 0,3;
--从第二个位置开始取，取3条记录
mysql>select * from stu limit 2,3;

起始位置可以省略，默认从0开始

select * from stu limit 3;

例题：找出班级总分前三名

select *,ch+math total from stu order by (ch+math) desc limit 0,3;

多学一招：limit 在update和delete语句中也是可以使用的

-- 前三名语文成绩加1分
update stu set ch=ch+1 order by ch+math desc limit 3;
--前三名删除
delete from stu order by ch+math desc limit 3;

1.5.9查询语句中的选项

查询语句中的选项有两个：
1、all:显示所有数据【默认】
2、distinct:去除结果集中重复的数据

select all stuaddress from stu;
--去除重复的项
select distinct stuaddress from stu;

1.6 聚合函数

1.sum() 求和
2.avg() 求平均值
3.max() 求最大值
4.min() 求最小值
5.count() 求记录数

#求语文总分、语文平均分、语文最高分、语文最低分、总人数
# 语文最高分
select max(ch) '语文最高分' ,min(ch) 语文最低分，sum(ch) 语文总分,avg(ch) 语文平均分,count(*) 总人数 from stu;

语文最高分	语文最低分	语文总分	语文平均分	总人数
88	55	597	74.6250	9

1.7 模糊查询

1.7.1 通配符

1._[下划线] 表示任意一个字符
2.%表示任意字符

1、满足“T_m”的有（AC）
A：Tom   B：Toom    C:Tam     D:Tm		E:Tmo

2、满足“T_m_”的有（BC）
A：Tmom   B：Tmmm    C:T1m2    D:Tmm		E:Tm

3、满足“张%”的是（ABCD）
A：张三   B：张三丰   C:张牙舞爪     D:张		E:小张

4、满足“%诺基亚%”的是（ABCD）
A：诺基亚2100  B：2100诺基亚    C:把我的诺基亚拿过来     D:诺基亚	

1.7.2 模糊查询（like）

select * from stu where stuname like 'T_m';
--查询姓张的学生
select * from stu where stuname like '张%'

1.8 union（联合）

作用：将多个表的数据组合到一起
插入测试数据

create table emp(
		id tinyint unsigned auto_increment primary key,
		name varchar(20) not null,
		skill set('PHP','mysql','java')
);
insert into emp values (null,'李白',1),(null,'杜甫',2),(null,'白居易',4);
insert into emp  values (null,'争霸小子',2);

1.8.1 union的使用

作用：将多个select语句结果集纵向联合起来

语法：select 语句 union【选项】 select 语句 union【选项】 select 语句

--查询stu表中的姓名和emp表中姓名，结果自动合并掉重复记录
select stuname from stu union select name from emp;

例如：查询上海的男生和北京的女生

--方法一：
select * from stu where (stuaddress='上海' and stusex='男') or (stuaddress='北京' and stus '女');

--方法二：
select * from stu where stuaddress='上海' and stusex='男' union select * from stu where stuaddress='北京' and stusex='女';
结论：union可以将一个复杂的条件转成两个简单的条件

1.8.2 union的选项

union的选项有两个
1、all：显示所有数据
2、distinct:去除重复的数据【默认】

select  stuname from stu union all select name from emp;

1.8.3 union的注意事项

1、union两边的select语句的字段个数必须一致
2、union两边的select语句字段名可以不一致，最终按第一个select语句的字段名
3、union两边的select语句中的数据类型可以不一致。