第五章-DQL操作表记录-查询【重点】
5.1 基本查询语法
select 要查询的字段名 from 表名 [where 条件]
5.2 简单查询
5.2.1 查询所有行和所有列的记录
- 语法
select * form 表
- 查询商品表里面的所有的列
select * from product;
5.2.2 查询某张表特定列的记录
- 语法
select 列名,列名,列名... from 表
- 查询商品名字和价格
select pname, price from product;
5.2.3 去重查询 distinct
- 语法
SELECT DISTINCT 字段名 FROM 表名; //要数据一模一样才能去重
- 去重查询商品的名字
SELECT DISTINCT pname,price FROM product
注意点: 去重针对某列, distinct前面不能先出现列名
5.2.4 别名查询
- 语法
select 列名 as 别名 ,列名 from 表 //列别名 as可以不写 select 别名.* from 表 as 别名 //表别名(多表查询, 明天会具体讲)
- 查询商品信息，使用别名
SELECT pid ,pname AS '商品名',price AS '商品价格',num AS '商品库存' FROM product
5.2.5 运算查询(+,-,*,/,%等)
- 把商品名，和商品价格+10查询出来:我们既可以将某个字段加上一个固定值，又可以对多个字段进行运算查询
select pname ,price+10 as 'price' from product; select name,chinese+math+english as total from student
注意
- 运算查询字段,字段之间是可以的
- 字符串等类型可以做运算查询，但结果没有意义
5.3 条件查询(很重要)
5.3.1语法
select ... from 表 where 条件 //取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回
5.3.2 运算符
1、比较运算符
大于：> 小于：< 大于等于：>= 小于等于：<= 等于：= 不能用于null判断 不等于：!= 或 <> 安全等于: <=> 可以用于null值判断
2、逻辑运算符（建议用单词，可读性来说）
逻辑与：&& 或 and 逻辑或：|| 或 or 逻辑非：! 或 not 逻辑异或：^ 或 xor
3、范围
区间范围：between x and y not between x and y 集合范围：in (x,x,x) not in (x,x,x)
4、模糊查询和正则匹配（只针对字符串类型，日期类型）
like 'xxx' 模糊查询是处理字符串的时候进行部分匹配 如果想要表示0~n个字符，用% 如果想要表示确定的1个字符，用_
regexp '正则'
5、特殊的null值处理
#（1）判断时 xx is null xx is not null xx <=> null #（2）计算时 ifnull(xx,代替值) 当xx是null时，用代替值计算
5.3.3 练习
- 查询商品价格>3000的商品
select * from product where price > 3000;
- 查询pid=1的商品
select * from product where pid = 1;
- 查询pid<>1的商品（！=）
select * from product where pid <> 1;
- 查询价格在3000到6000之间的商品
select * from product where price between 3000 and 6000;
查询pid在1，5，7，15范围内的商品
select * from product where id = 1; select * from product where id = 5; select * from product where id = 7; select * from product where id = 15; select * from product where id in (1,5,7,15);
- 查询商品名以iPho开头的商品(iPhone系列)
select * from product where pname like 'iPho%';
- 查询商品价格大于3000并且数量大于20的商品 (条件 and 条件 and…)
select * from product where price > 3000 and num > 20;
- 查询id=1或者价格小于3000的商品
select * from product where pid = 1 or price < 3000;
5.4 排序查询
排序是写在查询的后面，代表把数据查询出来之后再排序
5.4.1 环境的准备
# 创建学生表(有sid,学生姓名,学生性别,学生年龄,分数列,其中sid为主键自动增长) CREATE TABLE student( sid INT PRIMARY KEY auto_increment, sname VARCHAR(40), sex VARCHAR(10), age INT, score DOUBLE ); INSERT INTO student VALUES(null,'zs','男',18,98.5); INSERT INTO student VALUES(null,'ls','女',18,96.5); INSERT INTO student VALUES(null,'ww','男',15,50.5); INSERT INTO student VALUES(null,'zl','女',20,98.5); INSERT INTO student VALUES(null,'tq','男',18,60.5); INSERT INTO student VALUES(null,'wb','男',38,98.5); INSERT INTO student VALUES(null,'小丽','男',18,100); INSERT INTO student VALUES(null,'小红','女',28,28); INSERT INTO student VALUES(null,'小强','男',21,95);
5.4.2 单列排序
- 语法: 只按某一个字段进行排序，单列排序
SELECT 字段名 FROM 表名 [WHERE 条件] ORDER BY 字段名 [ASC|DESC]; //ASC: 升序，默认值; DESC: 降序
- 案例: 以分数降序查询所有的学生
SELECT * FROM student ORDER BY score DESC
5.4.3 组合排序
- 语法: 同时对多个字段进行排序，如果第1个字段相等，则按第2个字段排序，依次类推
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];
- 练习: 以分数降序查询所有的学生, 如果分数一致,再以age降序
SELECT * FROM student ORDER BY score DESC, age DESC
5.5 聚合函数
聚合函数用于统计，通常会和分组查询一起使用，用于统计每组的数据
5.5.1 聚合函数列表
| 聚合函数 | 作用 |
| max(列名) | 求这一列的最大值 |
| min(列名) | 求这一列的最小值 |
| avg(列名) | 求这一列的平均值 |
| count(列名) | 统计这一列有多少条记录 |
| sum(列名) | 对这一列求总和 |
- 语法
SELECT 聚合函数(列名) FROM 表名 [where 条件];
- 案例
-- 求出学生表里面的最高分数 SELECT MAX(score) FROM student -- 求出学生表里面的最低分数 SELECT MIN(score) FROM student -- 求出学生表里面的分数的总和(忽略null值) SELECT SUM(score) FROM student -- 求出学生表里面的平均分 SELECT AVG(score) FROM student -- 求出学生表里面的平均分（缺考了当成0分处理） SELECT AVG(IFNULL(score,0)) FROM student -- 统计学生的总人数 (忽略null) SELECT COUNT(sid) FROM student SELECT COUNT(*) FROM student
注意: 聚合函数会忽略空值NULL
我们发现对于NULL的记录不会统计，建议如果统计个数则不要使用有可能为null的列，但如果需要把NULL也统计进去呢？我们可以通过 IFNULL(列名，默认值) 函数来解决这个问题. 如果列不为空，返回这列的值。如果为NULL，则返回默认值。
-- 求出学生表里面的平均分（缺考了当成0分处理） SELECT AVG(IFNULL(score,0)) FROM student;
5.6 分组查询
GROUP BY将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。分组的目的就是为了统计，一般分组会跟聚合函数一起使用
5.6.1分组
- 语法
SELECT 字段1,字段2... FROM 表名 [where 条件] GROUP BY 列 [HAVING 条件];
案例
-- 根据性别分组, 统计每一组学生的总人数 SELECT sex '性别',COUNT(sid) '总人数' FROM student GROUP BY sex -- 根据性别分组，统计每组学生的平均分 SELECT sex '性别',AVG(score) '平均分' FROM student GROUP BY sex -- 根据性别分组，统计每组学生的总分 SELECT sex '性别',SUM(score) '总分' FROM student GROUP BY sex
5.6.2 分组后筛选 having
分组后的条件，不能写在where之后，where关键字要写在group by之前
- 根据性别分组, 统计每一组学生的总人数> 5的(分组后筛选)
SELECT sex, count(*) FROM student GROUP BY sex HAVING count(sid) > 5
- 根据性别分组，只统计年龄大于等于18的，并且要求组里的人数大于4
SELECT sex '性别',COUNT(sid) '总人数' FROM student WHERE age >= 18 GROUP BY sex HAVING COUNT(sid) > 4
5.6.3 where和having的区别【面试】
5.7 分页查询
5.7.1 语法
select ... from .... limit a ,b
| LIMIT a,b; |
| a 表示的是跳过的数据条数 |
| b 表示的是要查询的数据条数 |
5.7.2 案例
-- 分页查询 -- limit 关键字是使用在查询的后边，如果有排序的话则使用在排序的后边 -- limit的语法: limit offset,length 其中offset表示跳过多少条数据，length表示查询多少条数据 SELECT * FROM product LIMIT 0,3 -- 查询product表中的前三条数据(0表示跳过0条，3表示查询3条) SELECT * FROM product LIMIT 3,3 -- 查询product表的第四到六条数据(3表示跳过3条，3表示查询3条) -- 分页的时候，只会告诉你我需要第几页的数据，并且每页有多少条数据 -- 假如，每页需要3条数据，我想要第一页数据: limit 0,3 -- 假如,每页需要3条数据，我想要第二页数据: limit 3,3 -- 假如，每页需要3条数据，我想要第三页数据: limit 6,3 -- 结论: length = 每页的数据条数，offset = (当前页数 - 1)*每页数据条数 -- limit (当前页数 - 1)*每页数据条数, 每页数据条数
5.8 查询的语法小结
select...from...where...group by...order by...limit select...from...where... select...from...where...order by... select...from...where...limit... select...from...where...order by...imit
第六章 数据库三范式
好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。
6.1 第一范式: 确保每列保持原子性
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。
如果不遵守第一范式，查询出数据还需要进一步处理（查询不方便）。遵守第一范式，需要什么字段的数据就查询什么数据（方便查询）
6.2 第二范式: 确保表中的每列都和主键相关
第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示
这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。
而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示
<img src="imgs/tu_13.png" style="zoom: 67%;" />
这样设计，在很大程度上减小了数据库的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可
6.3 第三范式: 确保每列都和主键列直接相关,而不是间接相关
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。
<img src="imgs/tu_14.png" style="zoom:67%;" />
这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余
第七章 外键约束
7.1 外键约束的概念
在遵循三范式的前提下，很多时候我们必须要进行拆表，将数据分别存放在多张表中，以减少冗余数据。但是拆分出来的表与表之间是有着关联关系的，我们必须得通过一种约束来约定表与表之间的关系，这种约束就是外键约束
7.2 外键约束的作用
外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
7.3 创建外键约束的语法
7.3.1 在建表时指定外键约束
create table [数据名.]从表名( 字段名1 数据类型 primary key , 字段名2 数据类型 , ...., [constraint 外键约束名] foreign key (从表字段) references 主表名(主表字段) [on update 外键约束等级][on delete 外键约束等级] #外键只能在所有字段列表后面单独指定 #如果要自己命名外键约束名，建议 主表名_从表名_关联字段名_fk );
7.8.2 在建表后指定外键约束
alter table 从表名称 add [constraint 外键约束名] foreign key (从表字段名) references 主表名(主表被参照字段名) [on update xx][on delete xx];
7.4 删除外键约束的语法
ALTER TABLE 表名称 DROP FOREIGN KEY 外键约束名; #查看约束名 SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称'; #删除外键约束不会删除对应的索引，如果需要删除索引，需要用ALTER TABLE 表名称 DROP INDEX 索引名; #查看索引名 show index from 表名称;
7.5 外键约束的要求
- 在从表上建立外键，而且主表要先存在。
- 一个表可以建立多个外键约束
- 通常情况下，从表的外键列一定要指向主表的主键列
- 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样
7.6 外键约束等级
Cascade方式：在主表上update/delete记录时，同步update/delete掉从表的匹配记录
Set null方式：在主表上update/delete记录时，将从表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
No action方式：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
Restrict方式：同no action, 都是立即检查外键约束
Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时,子表将外键列设置成一个默认的值，但Innodb不能识别
如果没有指定等级，就相当于Restrict方式
7.7 外键约束练习
-- 部门表 create table dept( id int primary key, dept_name varchar(50), dept_location varchar(50) ); -- 员工表 CREATE TABLE emp( eid int primary key, name varchar(50) not null, sex varchar(10), dept_id int ); -- 给员工表表的dept_id添加外键指向部门表的主键 alter table emp add foreign key(dept_id) references dept(id)
