MySQL篇02-三大范式,多表查询

数据入库时,由于数据设计不合理，会存在数据重复、更新插入异常等情况, 故数据库中表的设计遵循的设计规范：三大范式

1.第一范式(1NF)

要求数据库的每一列都是不可分割的原子数据项，即原子性。强调的是列的原子性，即数据库中每一列的字段都是单一属性，不可再分的。且这个单一属性必须是由基本数据类型所构成，如整数、字符串等.

例:  users表

uid	username	password	tel	address
u001	tmm	111111	15891xxxxxx	陕西省汉中市汉台区xxx

上面表中address字段，可以按照省、市、区进行分割，因此不满足第一范式。

解决办法：将一个字段分割成多个字段                            

uid	username	password	tel	province	city	area	address
u001	tmm	111111	15891xxxxxx	陕西省	汉中市	汉台区	xxxx

2.第二范式(2NF)

在满足第一范式的前提下，非码属性必须完全依赖于候选码，每张表只描述一件事情。一张表必须有一个主键；非主键类必须完全依赖于主键。

例:   users表

uid	username	password	tel	role
u001	tmm1	111111	15891xxxxxx	管理员

上面表中uid可以作为users表的主键，username,password,tel都依赖于uid,但是 role字段不依赖于uid.

解决办法：将一张表拆分成多张表 ( 将管理关系放在多方上边, 一个角色有多名用户, 故用户的外键为rid)

用户表users

uid	username	password	tel	r_id
u001	tmm1	111111	15891xxxxxx	r1001
u002	tmm2	111111	15891xxxxxx	r1002

角色表roles

rid	username
r001	管理员
r002	普通用户

3.第三范式(3NF)

在第二范式前提下,任何非主属性不依赖于其他非主属性(在2NF基础上，消除传递依赖),非主键列必须直接依赖于主键,不能存在传递依赖。即确保数据表中的每一列数据都和主键直接相关，而不能间接相关.

例: 选课表sc

scid	sname	cname	tname	credit	score
001	tmm1	js	a老师	4	90
002	tmm2	css	b老师	3	95

由于sname,cname,tname,credit,score都不依赖于scid，因此，要进行表的拆分

选课表sc

scid	sid	cid	tname	score
001	s01	c001	t01	90
002	s02	c002	t02	95

学生表 students

sid	sname
s01	tmm1
s02	tmm2

课程表 class

cid	cname	credit
c001	js	4
c002	css	3

老师表 teachers

tid	tname
t01	a老师
t02	b老师

多对多的解决办法： 创建一张新表：teacher_class

tcid	cid (课程编号)	tid (老师id)
tc001	c001	t01
tc002	c002	t02

各表关系如图所示: 

注意:在数据库使用可视化工具建表时, 有时不添加外键, 避免数据处理时, 别处占用无法进行处理

4.多表查询

-- 单表查询
-- 查询所有书籍的作者信息
SELECT * FROM `books_author`;
-- 查询所有书籍的标题
SELECT title FROM books;
-- 给表起别名, b为别名, 作用:使用别名.的方式查找字段时更方便,不易出错
SELECT b.`content` FROM books b;
-- 给列(字段)起别名*/`books`
SELECT b.`imgUrl` AS `iu` FROM books b;
-- 1. 每本书涨价10%,保留两位小数, 涨价完成后更新到数据库中   价格不为空时, is not null,不能使用<>不等于
UPDATE books  SET price = ROUND(price*(1.1),2) WHERE price IS NOT NULL;
-- 2. 查询书所对应的评论 
-- 多表查询
SELECT b.bookname,c.comcontent  FROM books_comment c,books b  WHERE b.bid = c._bid;
-- 使用左外连接,主表内容会完全显示,包括NULL的情况  默认左的内容显示的多
SELECT b.`bid`,b.bookname,c.comcontent FROM books b LEFT JOIN books_comment c ON b.`bid` = c.`_bid`;
-- 使用内连接, 当内容为NULL时, 不显示
SELECT b.`bid`,b.bookname,c.comcontent FROM books b INNER JOIN books_comment c ON b.`bid` = c.`_bid`;
-- 3. 统计评分大于等于4.5的书的评论数,并显示书名   分组聚合   统计评分大于等于4.5的书, 统计评论数
-- 多表查询
SELECT b.bookname,c.`comcontent`, COUNT(c.`comcontent`) AS comnums
FROM books_comment c, books b
WHERE c._bid = b.bid  AND c.score >= 4.5
GROUP BY b.bookname ;
-- 子查询  显示书名
SELECT b.bookname FROM books b
WHERE b.`bid` IN (SELECT c.`_bid` FROM books_comment c WHERE c.`score` >= 4.5GROUP BY c.`_bid` = b.`bid`
)
-- 4. limit 起始索引,查询条数
/* 显示第三页的数据,每页10条,   当前页curPage=3, 每页条数pageSize=10select * from books limit (curPage-1)*pageSize,10   */
SELECT * FROM books LIMIT 10,10;
-- 显示书籍评论信息  (books)书id,书名  (books_author) 作者  (books_comment)评论发布时间，评论内容  (users)评论者
-- (1)显示评论者信息    (books_comment, users表)
SELECT c.`_bid`,c.`comcontent`,c.`comtime`,u.`nickname` FROM books_comment c, users u
WHERE c.`_uid` = u.`uid`;
-- (2)显示作者信息 (books,books_author,books_books_category表)
SELECT b.`bid`,b.`bookname`,a.`aname` FROM books b, books_author a, books_books_author ba
WHERE ba.`ba_bid`= b.`bid` AND ba.`ba_aid`= a.`aid`
-- (3)结果综合
SELECT *
FROM (SELECT b.`bid`,b.`bookname`,a.`aname` FROM books b, books_author a, books_books_author ba WHERE ba.`ba_bid`= b.`bid` AND ba.`ba_aid`= a.`aid`) t1 LEFT JOIN(SELECT c.`_bid`,c.`comcontent`,c.`comtime`,u.`nickname` FROM books_comment c, users u WHERE c.`_uid` = u.`uid` ) t2ON t1.bid = t2._bid-- GROUP_CONCAT([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
-- CONCAT_WS('分隔符', 要合并的字段)   合并列
SELECT t1.bid,t1.bookname,t1.aname,t2.`comtime`,t2.`nickname`,
-- group_concat(concat_Ws(',',t1.bookname,t2.`comcontent`,t2.`comtime`,t2.`nickname`) separator ';')
GROUP_CONCAT(t2.`comcontent` SEPARATOR '=====')
FROM  (SELECT b.`bid`,b.`bookname`,a.`aname` FROM books b, books_author a, books_books_author ba WHERE ba.`ba_bid`= b.`bid` AND ba.`ba_aid`= a.`aid`) t1 
LEFT JOIN(SELECT c.`_bid`,c.`comcontent`,c.`comtime`,u.`nickname` FROM books_comment c, users u WHERE c.`_uid` = u.`uid` ) t2
ON t1.bid = t2._bid 
GROUP BY t1.bid,t1.bookname,t1.aname;