数据库设计
数据库设计步骤
- 明确需求,搜集信息。抽离出数据库表现功能。
- 数据库建模:获取实体对象。
- 数据库建模:标识实体属性。
- 数据库建模:绘制 E-R 图。
- 转成数据库表。
- 规范化(三范式)
E-R绘制、转换:
E-R图语法:
- 矩形:实体对象(名词)
- 椭圆:属性(名词)
- 菱形:关系(动词)1:1、1:N、N:N
转换原则:
- 将实体转成 表, 将属性 转成字段。
- 如果没有合适的字段做主键,添加自动增长列做主键。
三范式:
- 原子性:表中的字段不可再分。
- 非键字段,依赖键字段:一张表只描述一件事。
- 消除传递依赖。非键字段键没有依赖关系。(消除冗余)
实际设计过程中,根据具体情况 适当采用 “反三范式”。保证数据库完整性、高效性。
查询语句
字段表达式 和 别名
-
字段表达式:
- select ‘床前明月光’; select 10 * 3 ;
-
别名:
- 关键字 as (可以省略)、“ ” (可以省略)
select id as "编号", name "姓名", addr "地 址", score '成绩', score + 20 as "加法" from stu;
dual 表
-
伪表
-
凑 “select xxx from xxx” 语法格式
select 10 *20 from dual;
导入SQL脚本文件
- 将 xxx.sql 脚本文件保存 在 Linux中。
- 登录 mysql 客户端。
- 在 mysql> 后,键入 “ source xxx.sql 脚本文件 绝对路径”。
where 查询
运算符:
> >= < <= =(没有 ==) != and(&&) or(||) not
select * from stu where stuage >=22 and stuage<=27;
select * from stu where 0; -- 空记录
select * from stu where 1; -- 全部记录
in 和 not in
- in:在集合中
- not in :不在集合中
select * from stu where stuaddress in('北京','上海','天津','河北');
select * from stu where stuaddress not in('北京','上海','天津','河北');
between … and、not between … and
- 闭区间的 范围 :
select * from stu where stuage >=18 and stuage <=22;
select * from stu where stuage between 18 and 22; -- 必须 小值在前,大值在后。
select * from stu where stuage not between 18 and 22;
is null 和 is not null
- 含有空值的 表达式 ,结果为 null
mysql> select stuno, stuname, ch, math, ch+math 总成绩 from stu;
+--------+-----------+------+------+-----------+
| stuno | stuname | ch | math | 总成绩 |
+--------+-----------+------+------+-----------+
| s25301 | 范建 | 80 | NULL | NULL |
| s25302 | 罗况 | 77 | 76 | 153 |
| s25303 | 申晶冰 | 55 | 82 | 137 |
| s25304 | 杜子腾 | NULL | 74 | NULL |
| s25305 | 史泰香 | 72 | 56 | 128 |
| s25318 | 郭迪辉 | 86 | 92 | 178 |
| s25319 | 拎壶冲 | 74 | 67 | 141 |
| s25320 | Tom | 65 | 67 | 132 |
| s25321 | Tabm | 88 | 77 | 165 |
+--------+-----------+------+------+-----------+
- 不能使用 = 和 != 判断 null。 使用 is 和 is not
mysql> select stuno, stuname,ch, math from stu where ch = null;
Empty set (0.00 sec)
mysql> select stuno, stuname,ch, math from stu where ch is null;
+--------+-----------+------+------+
| stuno | stuname | ch | math |
+--------+-----------+------+------+
| s25304 | 杜子腾 | NULL | 74 |
+--------+-----------+------+------+
- 滤空函数 :ifnull(参1, 参2)。 当参1对应的值为 null 时,返回 参2.
mysql> select stuno, stuname, ch, math, (ifnull(ch,0)+ifnull(math,0)) 总成绩 from stu;
+--------+-----------+------+------+-----------+
| stuno | stuname | ch | math | 总成绩 |
+--------+-----------+------+------+-----------+
| s25301 | 范建 | 80 | NULL | 80 |
| s25302 | 罗况 | 77 | 76 | 153 |
| s25303 | 申晶冰 | 55 | 82 | 137 |
| s25304 | 杜子腾 | NULL | 74 | 74 |
| s25305 | 史泰香 | 72 | 56 | 128 |
| s25318 | 郭迪辉 | 86 | 92 | 178 |
| s25319 | 拎壶冲 | 74 | 67 | 141 |
| s25320 | Tom | 65 | 67 | 132 |
| s25321 | Tabm | 88 | 77 | 165 |
+--------+-----------+------+------+-----------+
select stuno, stuname, ch 语文, math, (ifnull(ch,0)+ifnull(math,0)) as 总成绩 from stu
#where 总成绩 > 120; -- Unknown column '总成绩' in 'where clause'
where (ifnull(ch,0)+ifnull(math,0)) > 120;
通配符 和 模糊查询
-
通配符
- ‘_’ :匹配任意一个字符。
- ‘%’ :匹配 0-N 个任意字符。
-
like: 模糊查询
show variables like 'ch%'; select * from stu where stuname like 'T%';
Order by 排序
- order by : 向后作用。作用于 后面的所有字段。
- asc:升序【默认】. 向前作用。 作用于前面的 一列。
- desc:降序。向前作用。作用于前面的 一列。
# 按语文成绩降序排列
select * from stu order by ch desc;
# 按年龄、语文成绩排序
select * from stu order by stuage, ch; -- 当年龄相同时,按ch成绩排序
# 按年龄升序、语文成绩降序排序
select * from stu order by stuage asc, ch desc; -- 当年龄相同时,按ch成绩降序排序
# 按年龄降序、总成绩升序排序
select *, (ifnull(ch,0)+ifnull(math,0)) 总成绩
from stu
order by stuage desc, 总成绩 asc;
组函数
- 单行函数:对一行数据进行变换,产生一个结果。
- 多行函数(组函数、聚合函数):对多行数据进行变换,产生一个结果。
- 特性:组函数具有自动滤空功能。
- 组函数:
- sum()求和
- avg()求平均值
- max()求最大值
- min()求最小值
- count()统计记录数。count(*) MySQL系统选择 主键 字段统计。
分组查询
语法:在 select 的查询列表中,所有没有包含在组函数中的列,出现 在group by 之后。
# 按性别分组,查询平均年龄
SELECT stusex, avg(stuage)
from stu
group by stusex
# 求各个地区,男性平均年龄 和 女性平均年龄。
SELECT stuAddress, stuSex, avg(stuage)
from stu
group by stuAddress, stuSex
having 过滤:
- having 和 where 区别:
- where 从数据库中的原始数据中过滤。having从 结果集中进行过滤。
- where子句中不能使用组函数。在having子句中 可以使用组函数。
- SQL语句中没有组函数,既可以使用 where 也可以使用 having。where 效率高。
# 求平均年龄大于 25 的地区
select stuAddress, avg(stuAge)
from stu
group BY stuAddress
having avg(stuAge) > 25
或
select stuAddress, avg(stuAge) 平均年龄
from stu
group BY stuAddress
having 平均年龄 > 25
select stuAddress, avg(stuAge) 平均年龄
from stu
where 平均年龄 > 25 -- where子句中不能使用组函数 error
group BY stuAddress
limit 限制
- 语法:limit 起始位置,显示长度。
- 起始位置:从 0 开始计数。 如果起始位置为零,可以省略。
select * from stu limit 0, 2;
select * from stu limit 2; -- 显示结果一致。
# 查询班级总分前3名的学员信息
select *, (ifnull(ch,0) + ifnull(math, 0)) 总分
FROM stu
order by 总分 desc
limit 3;
- limit 除了在 select 子句中可以使用,也可以在 update、delete中使用。
多表查询
- 内连接
- 左外链接
- 右外连接
- 交叉连接
- 自然连接(语法优化)
笛卡尔全集
- 笛卡尔集行 = 表1 的行 x 表2 行
- 笛卡尔集列 = 表1的列 + 表2的 列
- 笛卡尔全集中,一定包含 错误信息。 —— 多表查询,一定要滤除错误信息。
内连接
- 语法一:
select * from 表1, 表2 where 表1.公共字段 = 表2.公共字段
select * from emp, dept where emp.deptno = dept.deptno;
-
语法二:
“,” --> inner join
“where” --> on
select * from 表1 inner join 表2 on 表1.公共字段 = 表2.公共字段
select * from emp inner join dept on emp.deptno = dept.deptno;
练习:
查询 stuinfo 表和 stumarks 表 的 姓名、学号、性别、笔试成绩、上机成绩:
#方法一:
select stuname, i.stuno, stusex, writtenExam, labExam
from stuinfo i, stumarks m
where i.stuno = m.stuno
#方法二:
select stuname, i.stuno, stusex, writtenExam, labExam
from stuinfo i inner join stumarks m
on i.stuno = m.stuno
外链接
-
使用外链接的 场景:
select 的查询结果中,向包含某些 对于 过滤 条件而言,不满足的 场景。
希望保存,左边表的 数据,使用 左外连接。 右表 使用 null 填充。
希望保存,右边表的 数据,使用 右外连接。左表 使用 null 填充
# 左外连接
select * from 表1 left join 表2 on 表1.公共字段 = 表2.公共字段 -- 保存 表1 的全部数据
select stuname, m.stuno, stusex, writtenexam, labexam
from stumarks m left join stuinfo i
on m.stuno = i.stuno -- 保存 stumarks 表中的全部信息。
# 右外连接
select * from 表1 right join 表2 on 表1.公共字段 = 表2.公共字段 -- 保存 表2 的全部数据
select stuname, i.stuno, stusex, writtenexam, labexam
from stumarks m right join stuinfo i
on m.stuno = i.stuno -- 保存 stuinfo 表中的全部信息。
自然连接
自然连接,不是种新的连接方式。是简化的书写形式。
- 自然内连接
- 自然左外连接
- 自然右外连接
#自然内连接
select stuname, stuno, stusex, writtenexam, labexam
from stumarks natural join stuinfo
#自然左外连接
select stuname, stuno, stusex, writtenexam, labexam
from stumarks NATURAL left join stuinfo
#自然右外连接
select stuname, stuno, stusex, writtenexam, labexam
from stumarks NATURAL right join stuinfo
自然连接特性:
-
表连接通过同名的字段来连接的
-
如果没有同名的字段返回笛卡尔全集
-
会对结果进行整理,整理的规则如下
a) 连接字段保留一个
b) Select * 查询时,连接字段放在最前面
c) 左外连接左表在前,右外连接右表在前