通辽做网站制作,品牌策划咨询,电子商务以后能干什么,设计案例展示网站-- ########## 01、ER关系 ##########-- ER关系#xff08;逻辑描述#xff09;
-- A#xff1a;E---Entity简写#xff0c;实体#xff0c;具有相同属性#xff08;特征#xff09;的对象归为同一实体
-- Attribute属性#xff0c;描述实体具有的特征#xff… -- ########## 01、ER关系 ##########-- ER关系逻辑描述
-- AE---Entity简写实体具有相同属性特征的对象归为同一实体
-- Attribute属性描述实体具有的特征一个实体可以有若干个属性来描述
-- BR---Relationship简写关系/联系数据对象彼此之间的互相关联的关系-- ER关系常见的三种形式
-- 1、一对一关系11例如夫妻关系、一个国家只有一个最高领导人、向日葵...
-- 2、一对多关系1n例如一把锁有多把钥匙、一个房子有多个门、一个皇帝有多个太监、一个部门有多个员工...
-- 3、多对多关系mn例如参加招聘会的企业和同学、学生选课、饭桌上的菜和吃菜的人...-- ########## 02、数据库设计 ##########
-- 设计的过程不是一步到位的也不可能一步到位都是一个反复迭代的过程-- 需求商城里会员顾客购买若干数量的商品
-- 提供的数据如下
-- 顾客编号为1的顾客张三购买了3个商品编号为123的A商品
-- 顾客编号为2的顾客李四购买了4个商品编号为456的B商品
-- 顾客编号为1的顾客张三购买了3个商品编号为123的A商品数据来自不同渠道
-- 顾客编号为3的顾客王五购买了5个商品编号为123的A商品
-- 顾客编号为1的顾客张三购买了2个商品编号为456的B商品
-- .....-- 分析这可能就是业务提供方描述的一个场景数据库设计时会从中挖掘出很多信息
-- 数据整理去除无效的数据比如去除重复的数据可能来自不同的渠道但是其实描述的是相同的数据内容-- 广大开发人员在长期的数据库设计过程中归纳整理了几个称为【范式】的东西
-- 【第1范式1NF】为了保证表中的行的数据是唯一的属性是原子的。
-- 也就是说行的唯一性通过在表中定义一个唯一的主键来实现
-- 注意这里说的唯一的主键没有说主键只能建立在一个字段上CREATE TABLE order_1nf
(orderid INT,productid INT,quantity INT,customerid INT,customername VARCHAR(10) NOT NULL,productname VARCHAR(20) NOT NULL,PRIMARY KEY(orderid, productid)
);
DESC order_1nf;-- 【第2范式2NF】首先数据必须满足第1范式其次要求非键属性Nonkey Attribute和候选键之间必须满足一定的条件。
-- 也就是说一个非键属性不能只完全函数依赖于候选键的一部分。
-- 分析order_1nf表中customerid这个非键属性只依赖于主键候选键(orderid, productid)的一部分orderid所以需要把order_1nf表进行分解
CREATE TABLE order_2nf
(orderid INT,customerid INT,customername VARCHAR(10) NOT NULL,PRIMARY KEY(orderid)
);
CREATE TABLE orderdetail_2nf
(orderid INT,productid INT,quantity INT,PRIMARY KEY(orderid)
);
CREATE TABLE product_2nf
(productid INT,productname VARCHAR(20) NOT NULL,PRIMARY KEY(productid)
);-- 【第3范式3NF】首先数据必须满足第2范式其次要求所有的非键属性必须非传递依赖于候选键。
-- 也就是说一个非键属性不能依赖于其他的非键属性。
-- 分析order_2nf表中customername依赖于customerid这个非键属性所以需要把order_2nf表进行分解
CREATE TABLE order_3nf
(orderid INT,customerid INT,PRIMARY KEY(orderid)
);
CREATE TABLE customer_3nf
(customerid INT,customername VARCHAR(10) NOT NULL,PRIMARY KEY(customerid)
);
CREATE TABLE orderdetail_3nf
(orderid INT,productid INT,quantity INT,PRIMARY KEY(orderid)
);
CREATE TABLE product_3nf
(productid INT,productname VARCHAR(20) NOT NULL,PRIMARY KEY(productid)
);-- 注意实际开发中一般必须达到第2范式及以上不过有时处于操作数据效率的考虑设计时也会使用第2范式的设计即包含了一些冗余-- 需求设计 学生选课的数据库设计
-- 学生可以选择多门课程一门课程可以有多个学生一个老师可以带多门课程一门课程只能由一个老师来带每个学生选的课程有一个相应的课程成绩
-- 分析
-- 实体学生、课程、老师、成绩这四个名词拿出来作为候选实体
-- 实体间的关系一个老师可以带多门课程一门课程只能由一个老师来带这句话描述了课程 和 老师 之间的关系是多对一的关系
-- 学生可以选择多门课程一门课程可以有多个学生这句话描述了 学生 和 课程之间的关系是多对多的关系
-- 每个学生选的课程有一个相应的课程成绩这句话读起来 成绩 像是 课程的一个属性并且是一个学生的一门课程的一个成绩
-- 结合上述应该要先解决多对多的关系再看这个成绩怎么设计-- 首先设计较为独立的实体
-- 老师信息实体
CREATE TABLE teacherinfo
(-- 老师编号teacherid INT AUTO_INCREMENT PRIMARY KEY,-- 老师姓名teachername VARCHAR(10) NOT NULL,-- 老师性别teachergender ENUM(男, 女) NOT NULL,-- 老师年龄teacherage INT NOT NULL
);
-- 学生信息实体
CREATE TABLE studentinfo
(-- 学生编号studentid INT AUTO_INCREMENT PRIMARY KEY,-- 学生姓名studentname VARCHAR(10) NOT NULL,-- 学生性别studentgender ENUM(男, 女) NOT NULL,-- 学生年龄studentage INT NOT NULL
);-- 接着设计一对多的关系可以在作为多的实体中添加作为一的实体的主键作为非键属性的值
-- 也可以在作为一的实体中添加作为多的实体的主键作为非键属性的值
-- 显然前者操作起来比较简单也是一对多关系的经典设计方式-- 课程信息实体
CREATE TABLE courseinfo
(-- 课程编号courseid INT AUTO_INCREMENT PRIMARY KEY,-- 课程名称coursename VARCHAR(10) NOT NULL,-- 老师编号teacherid INT NOT NULL
);-- 最后考虑多对多的关系首先考虑套用一下一对多的关系的设计在courseinfo表中放入studentinfo表的主键在studentinfo表中放入courseinfo表的主键
-- 这样的设计有一个问题作为学生信息并不确定有多少课程被选即无法确定多个课程主键的数量当然写在一个字段中也可以但是操作又很麻烦
-- 同样作为课程信息也不确定有多少学生选择
-- 既然考虑这两个实体中都要放入对方的主键作为属性使用这里不妨制作一个独立的关系实体包含有着两个实体的主键作为属性即可轻松实现多对多的关系
-- 而且这个独立的关系实体还可以包含有和多对多关系相关的其他属性比如成绩-- 成绩信息实体学生 和 课程 多对多 关联关系表
CREATE TABLE scoreinfo
(-- 成绩编号scoreid INT AUTO_INCREMENT PRIMARY KEY,-- 学生编号studentid INT NOT NULL,-- 课程编号courseid INT NOT NULL,-- 成绩分数score DECIMAL(4, 1) NOT NULL
);-- ########## 03、汇总数据进阶 ##########-- 模拟数据
INSERT INTO scoreinfo VALUES(NULL, 1, 1, 60), (NULL, 1, 2, 90), (NULL, 1, 3, 80), (NULL, 1, 4, 70), (NULL, 1, 5, 40),
(NULL, 2, 1, 70), (NULL, 2, 2, 50), (NULL, 2, 3, 70), (NULL, 2, 4, 30), (NULL, 2, 5, 90),
(NULL, 3, 1, 55), (NULL, 3, 2, 65), (NULL, 3, 3, 75),
(NULL, 4, 1, 87), (NULL, 4, 2, 63), (NULL, 4, 4, 28);SELECT * FROM scoreinfo;-- 需求获取每个同学的成绩总分和成绩平均分
-- 思路因为每个同学可能有多门课程的成绩所以这里需要基于学生编号进行分组并使用聚合函数
SELECT studentid AS 学生编号, SUM(score) AS 成绩总分, AVG(score) AS 成绩平均分
FROM scoreinfo GROUP BY studentid;-- 需求列出每门功课的最高分、最低分该门功课的总分、该门功课的平均分
-- 思路因为每门功课可能对应多个学生的成绩所以这里需要基于课程编号进行分组并使用聚合函数
SELECT courseid AS 课程编号, MAX(score) AS 最高分, MIN(score) AS 最低分, SUM(score) AS 总分, AVG(score) AS 平均分
FROM scoreinfo GROUP BY courseid;-- 需求列出平均分高于65分的同学的学生编号及其平均分
-- 思路首先想到的是使用分组子句GROUP BY 和 筛选条件子句WHERE-- 1、语义错误先对scoreinfo这个集合进行了条件筛选把65分以上的成绩都留下来了再按学生编号进行分组
SELECT studentid AS 学生编号, AVG(score) AS 平均分
FROM scoreinfo WHERE score 65 GROUP BY studentid;-- 2、语法错误参照顺序图因为WHERE子句先于GROUP BY子句执行所以在WHERE子句中无法提前使用聚合函数
-- 错误代码 1111 Invalid use of group function
SELECT studentid AS 学生编号, AVG(score) AS 平均分
FROM scoreinfo WHERE AVG(score) 65 GROUP BY studentid;-- 正确写法引入 HAVING 子句对于分组后的数据进行条件筛选
SELECT studentid AS 学生编号, AVG(score) AS 平均分
FROM scoreinfo
GROUP BY studentid
HAVING AVG(score) 65;-- 注意
-- 1、WHERE子句关注的是行的记录的条件筛选
-- 2、HAVING子句关注的是组的记录的条件筛选SELECT * FROM scoreinfo HAVING score 70;
-- 上句可以理解为
SELECT * FROM scoreinfo GROUP BY scoreid HAVING score 70;
-- 下面两句可以理解为因为按照scoreid这个主键进行分组每组就是一条记录对这一条记录使用聚合函数还是它自身
SELECT * FROM scoreinfo GROUP BY scoreid HAVING AVG(score) 70;
SELECT * FROM scoreinfo GROUP BY scoreid HAVING SUM(score) 70;
-- 也就等价于
SELECT * FROM scoreinfo WHERE score 70;-- 需求列出单科课程课程编号对应的所有同学多行显示同学编号
-- 如下写法不正确因为这样分组后select获取的是每一组的第一条记录
-- 思考为什么到处使用分组
SELECT courseid AS 课程编号, studentid AS 学生编号
FROM scoreinfo
GROUP BY courseid;-- 正确写法
SELECT courseid AS 课程编号, studentid AS 学生编号
FROM scoreinfo
ORDER BY courseid;
-- 或者
SELECT courseid AS 课程编号, studentid AS 学生编号
FROM scoreinfo
ORDER BY courseid, studentid;-- 需求列出单科课程课程编号对应的所有同学单行显示同学编号多个编号之间以逗号连接
-- 思路首先考虑分组分组后select时使用字符函数CONCAT但是这样拼接的是每一组的第一条记录中studentid
SELECT courseid AS 课程编号, CONCAT(studentid, ,) AS 学生编号
FROM scoreinfo
GROUP BY courseid;-- 到MySQL手册中找灵感查找了一下和GROUP BY子句相关的内容看到GROUP_CONCAT(expr) 该函数返回带有来自一个组的连接的非NULL值的字符串结果。
SELECT courseid AS 课程编号, GROUP_CONCAT(studentid) AS 学生编号
FROM scoreinfo
GROUP BY courseid;-- 需求列出单科课程课程编号对应的学生人数
SELECT courseid AS 课程编号, COUNT(studentid) AS 选择该门课程的学生人数
FROM scoreinfo
GROUP BY courseid;-- 需求列出单科课程课程编号对应的学生人数并进行总计
-- 思路总计操作使用 WITH ROLLUP 子句
SELECT courseid AS 课程编号, COUNT(studentid) AS 选择该门课程的学生人数
FROM scoreinfo
GROUP BY courseid
WITH ROLLUP;-- 需求列出单科课程课程编号对应的所有学生单行显示学生编号并进行总人数的统计单行显示学生编号
SELECT courseid AS 课程编号, GROUP_CONCAT(studentid) AS 选择该门课程的学生编号
FROM scoreinfo
GROUP BY courseid
WITH ROLLUP; 转载于:https://www.cnblogs.com/java-le/p/6443404.html
