mysql——入门3

数据库设计

数据库设计步骤

1. 明确需求，搜集信息。抽离出数据库表现功能。
2. 数据库建模：获取实体对象。
3. 数据库建模：标识实体属性。
4. 数据库建模：绘制 E-R 图。
5. 转成数据库表。
6. 规范化（三范式）

E-R绘制、转换：

E-R图语法：

1. 矩形：实体对象（名词）
2. 椭圆：属性（名词）
3. 菱形：关系（动词）1:1、1：N、N：N

转换原则：

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

三范式：

1. 原子性：表中的字段不可再分。
2. 非键字段，依赖键字段：一张表只描述一件事。
3. 消除传递依赖。非键字段键没有依赖关系。（消除冗余）

实际设计过程中，根据具体情况 适当采用 “反三范式”。保证数据库完整性、高效性。

查询语句

字段表达式 和 别名

• 字段表达式：

  • 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脚本文件

1. 将 xxx.sql 脚本文件保存 在 Linux中。
2. 登录 mysql 客户端。
3. 在 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;

组函数

• 单行函数：对一行数据进行变换，产生一个结果。
• 多行函数（组函数、聚合函数）：对多行数据进行变换，产生一个结果。
• 特性：组函数具有自动滤空功能。
• 组函数：
  1. sum（）求和
  2. avg（）求平均值
  3. max（）求最大值
  4. min（）求最小值
  5. 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 区别：
  1. where 从数据库中的原始数据中过滤。having从 结果集中进行过滤。
  2. 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. 内连接
2. 左外链接
3. 右外连接
4. 交叉连接
5. 自然连接（语法优化）

笛卡尔全集

• 笛卡尔集行 = 表1 的行 x 表2 行
• 笛卡尔集列 = 表1的列 + 表2的 列
• 笛卡尔全集中，一定包含 错误信息。 —— 多表查询，一定要滤除错误信息。

内连接

1. 语法一：

select * from 表1, 表2 where 表1.公共字段 = 表2.公共字段

select * from emp, dept where emp.deptno = dept.deptno;

2. 语法二：

   “,” --> 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 表中的全部信息。

自然连接

自然连接，不是种新的连接方式。是简化的书写形式。

1. 自然内连接
2. 自然左外连接
3. 自然右外连接

#自然内连接
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

自然连接特性：

1. 表连接通过同名的字段来连接的

2. 如果没有同名的字段返回笛卡尔全集

3. 会对结果进行整理，整理的规则如下

   a) 连接字段保留一个

   b) Select * 查询时，连接字段放在最前面

   c) 左外连接左表在前，右外连接右表在前