# 创建数据库 renda01
CREATE DATABASE renda01 CHARACTER SET utf8;
# 使用数据库
USE renda01;
# 创建商品表:
CREATE TABLE product(
pid INT, # 主键 ID
pname VARCHAR(20), # 商品名称
price DOUBLE, # 商品价格
category_name VARCHAR(32) # 商品分类名称
);
# 导入数据
INSERT INTO product(pid,pname,price,category_name) VALUES(1,'联想电脑',5000,'电脑办公');
INSERT INTO product(pid,pname,price,category_name) VALUES(2,'海尔电脑',3000,'电脑办公');
INSERT INTO product(pid,pname,price,category_name) VALUES(3,'雷神电脑',5000,'电脑办公');
INSERT INTO product(pid,pname,price,category_name) VALUES(4,'JACK JONES',800,'服装');
INSERT INTO product(pid,pname,price,category_name) VALUES(5,'真维斯',200,'服装');
INSERT INTO product(pid,pname,price,category_name) VALUES(6,'花花公子',440,'服装');
INSERT INTO product(pid,pname,price,category_name) VALUES(7,'劲霸',2000,'服装');
INSERT INTO product(pid,pname,price,category_name) VALUES(8,'香奈儿',800,'女士用品');
INSERT INTO product(pid,pname,price,category_name) VALUES(9,'相宜本草',200,'女士用品');
INSERT INTO product(pid,pname,price,category_name) VALUES(10,'面霸',5,'女士用品');
INSERT INTO product(pid,pname,price,category_name) VALUES(11,'雪碧',56,'饮料饮品');
INSERT INTO product(pid,pname,price,category_name) VALUES(12,'香飘飘奶茶',1,'饮料饮品');
INSERT INTO product(pid,pname,price,category_name) VALUES(13,'iPhone9',8000,NULL);
-- 查询练习
#1.查询所有的商品
#2.查询商品名和商品价格
#3.别名查询.使用的关键字是 as(as 可以省略的)
#4.查询商品价格,对价格去除重复
#5.查询结果是表达式(运算查询):将所有商品的价格 +10 元进行显示
#6.查询商品名称为“花花公子”的商品所有信息
#7.查询价格为 800 商品
#8.查询价格不是 800 的所有商品
#9.查询商品价格大于 60 元的所有商品信息
#10.查询商品价格在 200 到 1000 之间所有商品
#11.查询商品价格是 200 或 800 的所有商品
#12.查询含有'霸'字的所有商品
#13.查询以'香'开头的所有商品
#14.查询第二个字为'想'的所有商品
#15.商品没有分类的商品
#16.查询有分类的商品
-- 排序练习
#1.使用价格对商品信息排序(降序)
# 提示:使用 order by 语句
#2.在价格排序(降序)的基础上,以主键排序(降序)
# 提示:若价格相同,相同价格的数据以 pid 降序排序
#3.显示商品的价格(去重复),并排序(降序)
# 提示:DISTINCT 关键字去重
-- 聚合函数/分组函数练习
#1.查询商品的总条数(两种方式)
# 提示:使用 count()
#2.查看 price 商品的总价格
# 提示:使用 sum();
#3.查看 price 的最大值和最小值
# 提示:使用 max() min()
#4.查看 price 的平均值
# 提示:使用 AVG()
#5.查询价格大于 200 商品的总条数
# 提示:使用 COUNT(*) 统计条数
-- 分组练习
#1.统计各个分类商品的个数
#2.统计各个分类商品的个数,且只显示个数大于 1 的信息
答案
### 作者:张人大
-- 查询练习
#1.查询所有的商品
SELECT * FROM product;
#2.查询商品名和商品价格
SELECT pname, price FROM product;
#3.别名查询.使用的关键字是 as(as 可以省略的)
SELECT pname '商品名字', price '商品价格' FROM product;
#4.查询商品价格,对价格去除重复
SELECT DISTINCT price '商品价格' FROM product;
#5.查询结果是表达式(运算查询):将所有商品的价格 +10 元进行显示
SELECT price+10 '商品价格+10元' FROM product;
#6.查询商品名称为“花花公子”的商品所有信息
SELECT * FROM product WHERE pname = '花花公子';
#7.查询价格为 800 商品
SELECT * FROM product WHERE price = 800;
#8.查询价格不是 800 的所有商品
SELECT * FROM product WHERE price <> 800;
#9.查询商品价格大于 60 元的所有商品信息
SELECT * FROM product WHERE price > 60;
#10.查询商品价格在 200 到 1000 之间所有商品
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;
#11.查询商品价格是 200 或 800 的所有商品
SELECT * FROM product WHERE price IN (200, 800);
#12.查询含有'霸'字的所有商品
SELECT * FROM product WHERE pname LIKE '%霸%';
#13.查询以'香'开头的所有商品
SELECT * FROM product WHERE pname LIKE '香%';
#14.查询第二个字为'想'的所有商品
SELECT * FROM product WHERE pname LIKE '_想%';
#15.商品没有分类的商品
SELECT * FROM product WHERE category_name IS NULL;
#16.查询有分类的商品
SELECT * FROM product WHERE category_name IS NOT NULL;
-- 排序练习
#1.使用价格对商品信息排序(降序)
# 提示:使用 order by 语句
SELECT * FROM product ORDER BY price DESC;
#2.在价格排序(降序)的基础上,以主键排序(降序)
# 提示:若价格相同,相同价格的数据以 pid 降序排序
SELECT * FROM product ORDER BY price DESC, pid DESC;
#3.显示商品的价格(去重复),并排序(降序)
# 提示:DISTINCT 关键字去重
SELECT DISTINCT price '商品价格'
FROM product
ORDER BY price DESC;
-- 聚合函数/分组函数练习
#1.查询商品的总条数(两种方式)
# 提示:使用 count()
SELECT COUNT(*) FROM product;
SELECT COUNT(1) FROM product;
#2.查看 price 商品的总价格
# 提示:使用 sum();
SELECT SUM(price) '总价格' FROM product;
#3.查看 price 的最大值和最小值
# 提示:使用 max() min()
SELECT
MAX(price) '最大价格',
MIN(price) '最小价格'
FROM product;
#4.查看 price 的平均值
# 提示:使用 AVG()
SELECT AVG(price) '平均价格' FROM product;
#5.查询价格大于 200 商品的总条数
# 提示:使用 COUNT(*) 统计条数
SELECT COUNT(*) FROM product WHERE price > 200;
-- 分组练习
#1.统计各个分类商品的个数
SELECT
category_name '分类名称',
COUNT(*) '个数'
FROM product
WHERE category_name IS NOT NULL
GROUP BY category_name;
#2.统计各个分类商品的个数,且只显示个数大于 1 的信息
SELECT
category_name '分类名称',
COUNT(*) '个数'
FROM product
GROUP BY category_name
HAVING COUNT(*) > 1;
#1.创建部门表(id,name)
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
#2.创建员工表(id, 姓名,性别,工资,入职日期,部门ID)
CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), # 性别
salary DOUBLE, # 工资
join_date DATE, # 入职日期
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES dept(id) # 外键,关联部门表(部门表的主键)
);
INSERT INTO employee(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO employee(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO employee(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO employee(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO employee(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
-- 按要求实现下面的查询功能
#1.查询工资最高的员工是谁?
#2.查询工资小于平均工资的员工有哪些?
#3.查询大于 5000 的员工,来至于哪些部门,输出部门的名字
#4.查询开发部与财务部所有的员工信息,分别使用子查询和表连接实现
#5.查询 2011 年以后入职的员工信息和部门信息,分别使用子查询和表连接实现
### 作者:张人大
-- 按要求实现下面的查询功能
#1.查询工资最高的员工是谁?
SELECT *
FROM employee
WHERE salary = (
SELECT MAX(salary)
FROM employee
);
#2.查询工资小于平均工资的员工有哪些?
SELECT *
FROM employee
WHERE salary < (
SELECT AVG(salary)
FROM employee
);
#3.查询大于 5000 的员工,来至于哪些部门,输出部门的名字
SELECT NAME
FROM dept
WHERE id IN (
SELECT dept_id
FROM employee
WHERE salary > 5000
);
#4.查询开发部与财务部所有的员工信息,分别使用子查询和表连接实现
## exists 型子查询
SELECT *
FROM employee
WHERE dept_id IN (
SELECT id
FROM dept
WHERE NAME IN ('开发部', '财务部')
);
## 表连接
SELECT e.*
FROM employee e JOIN dept d ON e.`dept_id` = d.`id`
WHERE d.`NAME` IN ('开发部', '财务部');
#5.查询 2011 年以后入职的员工信息和部门信息,分别使用子查询和表连接实现
## from 型子查询
SELECT e.*, d.`NAME` 'department'
FROM
dept d
JOIN (
SELECT *
FROM employee
WHERE YEAR(join_date) > 2011
) e
ON
d.`id` = e.`dept_id`;
## 表连接
SELECT e.*, d.`NAME` 'department'
FROM employee e JOIN dept d ON e.`dept_id` = d.`id`
WHERE YEAR(e.`join_date`) > 2011;
# 学生表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20), # 姓名
city VARCHAR(10), # 城市
age INT # 年龄
);
# 老师表
CREATE TABLE teacher(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) # 姓名
);
# 课程表
CREATE TABLE course(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20), # 课程名
teacher_id INT, # 外键对应老师表主键 id
FOREIGN KEY (teacher_id) REFERENCES teacher(id)
);
# 学生与课程中间表
CREATE TABLE student_course(
student_id INT, # 外键对应学生表主键
course_id INT, # 外键对应课程表主键
score INT, # 某学员某科的考试分数
FOREIGN KEY (student_id) REFERENCES student(id),
FOREIGN KEY (course_id) REFERENCES course(id)
);
INSERT INTO teacher VALUES(NULL,'关羽');
INSERT INTO teacher VALUES(NULL,'张飞');
INSERT INTO teacher VALUES(NULL,'赵云');
INSERT INTO student VALUES(NULL,'小王','北京',20);
INSERT INTO student VALUES(NULL,'小李','上海',18);
INSERT INTO student VALUES(NULL,'小周','北京',22);
INSERT INTO student VALUES(NULL,'小刘','北京',21);
INSERT INTO student VALUES(NULL,'小张','上海',22);
INSERT INTO student VALUES(NULL,'小赵','北京',17);
INSERT INTO student VALUES(NULL,'小蒋','上海',23);
INSERT INTO student VALUES(NULL,'小韩','北京',25);
INSERT INTO student VALUES(NULL,'小魏','上海',25);
INSERT INTO student VALUES(NULL,'小明','北京',20);
INSERT INTO course VALUES(NULL,'语文',1);
INSERT INTO course VALUES(NULL,'数学',1);
INSERT INTO course VALUES(NULL,'生物',2);
INSERT INTO course VALUES(NULL,'化学',2);
INSERT INTO course VALUES(NULL,'物理',2);
INSERT INTO course VALUES(NULL,'英语',3);
INSERT INTO student_course VALUES(1,1,80);
INSERT INTO student_course VALUES(1,2,90);
INSERT INTO student_course VALUES(1,3,85);
INSERT INTO student_course VALUES(1,4,78);
INSERT INTO student_course VALUES(2,2,53);
INSERT INTO student_course VALUES(2,3,77);
INSERT INTO student_course VALUES(2,5,80);
INSERT INTO student_course VALUES(3,1,71);
INSERT INTO student_course VALUES(3,2,70);
INSERT INTO student_course VALUES(3,4,80);
INSERT INTO student_course VALUES(3,5,65);
INSERT INTO student_course VALUES(3,6,75);
INSERT INTO student_course VALUES(4,2,90);
INSERT INTO student_course VALUES(4,3,80);
INSERT INTO student_course VALUES(4,4,70);
INSERT INTO student_course VALUES(4,6,95);
INSERT INTO student_course VALUES(5,1,60);
INSERT INTO student_course VALUES(5,2,70);
INSERT INTO student_course VALUES(5,5,80);
INSERT INTO student_course VALUES(5,6,69);
INSERT INTO student_course VALUES(6,1,76);
INSERT INTO student_course VALUES(6,2,88);
INSERT INTO student_course VALUES(6,3,87);
INSERT INTO student_course VALUES(7,4,80);
INSERT INTO student_course VALUES(8,2,71);
INSERT INTO student_course VALUES(8,3,58);
INSERT INTO student_course VALUES(8,5,68);
INSERT INTO student_course VALUES(9,2,88);
INSERT INTO student_course VALUES(10,1,77);
INSERT INTO student_course VALUES(10,2,76);
INSERT INTO student_course VALUES(10,3,80);
INSERT INTO student_course VALUES(10,4,85);
INSERT INTO student_course VALUES(10,5,83);
-- 1. 查询平均成绩大于 70 分的同学的学号, 姓名和平均成绩
#1.1 分组查询每个学生的学号, 姓名, 平均分
#1.2 增加条件:平均成绩大于 70
-- 2. 查询所有同学的学号、姓名、选课数、总成绩
#2.1 需要查询两张表 student 表和 student_course 表
#2.2 需要使用 student_id 学号字段,进行分组
#2.3 需要使用到 count 函数 sum 函数
-- 3. 查询学过赵云老师课程的同学的学号、姓名
#3.1 查询赵云老师的 id
#3.2 根据老师 ID,在课程表中查询所教的课程编号
#3.3 将上面的子查询作为 where 后面的条件
-- 4. 查询选课少于三门学科的学员
#4.1 查询每个学生学了几门课 条件 1:小于等于三门
#4.2 查询学号和姓名, 将 4.1 作为临时表
### 作者:张人大
-- 1. 查询平均成绩大于 70 分的同学的学号, 姓名和平均成绩
#1.1 分组查询每个学生的学号, 姓名, 平均分
SELECT
s.`id` '学号',
s.`NAME` '姓名',
AVG(sc.`score`) '平均分'
FROM
student s
JOIN
student_course sc
ON
s.`id` = sc.`student_id`
GROUP BY s.`id`;
#1.2 增加条件:平均成绩大于 70
SELECT
s.`id` '学号',
s.`NAME` '姓名',
AVG(sc.`score`) '平均分'
FROM
student s
JOIN
student_course sc
ON
s.`id` = sc.`student_id`
GROUP BY s.`id`
HAVING AVG(sc.`score`) > 70;
-- 2. 查询所有同学的学号、姓名、选课数、总成绩
#2.1 需要查询两张表 student 表和 student_course 表
SELECT *
FROM student s, student_course sc
WHERE s.`id` = sc.`student_id`;
#2.2 需要使用 student_id 学号字段,进行分组
SELECT s.*
FROM student s, student_course sc
WHERE s.`id` = sc.`student_id`
GROUP BY s.`id`;
#2.3 需要使用到 count 函数 sum 函数
SELECT
s.`id` '学号',
s.`NAME` '姓名',
COUNT(sc.`course_id`) '选课数',
SUM(sc.`score`) '总成绩'
FROM student s, student_course sc
WHERE s.`id` = sc.`student_id`
GROUP BY s.`id`;
-- 3. 查询学过赵云老师课程的同学的学号、姓名
#3.1 查询赵云老师的 id
SELECT t.`id`
FROM teacher t
WHERE t.`NAME` = '赵云';
#3.2 根据老师 ID,在课程表中查询所教的课程编号
SELECT c.`id`
FROM course c
WHERE c.`teacher_id` IN (
SELECT t.`id`
FROM teacher t
WHERE t.`NAME` = '赵云'
);
#3.3 将上面的子查询作为 where 后面的条件
SELECT s.`id` '学号', s.`NAME` '姓名'
FROM student s
WHERE s.`id` IN (
SELECT sc.`student_id`
FROM student_course sc
WHERE sc.`course_id` IN (
SELECT c.`id`
FROM course c
WHERE c.`teacher_id` IN (
SELECT t.`id`
FROM teacher t
WHERE t.`NAME` = '赵云'
)
)
);
-- 4. 查询选课少于三门学科的学员
#4.1 查询每个学生学了几门课 条件 1:小于等于三门
SELECT s.*
FROM
student s
JOIN
student_course sc
ON
s.`id` = sc.`student_id`
GROUP BY s.`id`
HAVING COUNT(sc.`course_id`) < 3;
#4.2 查询学号和姓名, 将 4.1 作为临时表
## 创建视图作为临时表
CREATE VIEW student_view
AS
SELECT s.*
FROM
student s
JOIN
student_course sc
ON
s.`id` = sc.`student_id`
GROUP BY s.`id`
HAVING COUNT(sc.`course_id`) < 3;
## 使用临时表查询
SELECT sv.`id`, sv.`NAME` FROM student_view sv;
以下是某教育平台数据库中的某几张表,为了降低难度,已经简化的表中字段。
请看图分析出三张表之间的关系
1) 请设计三张表,要求如下
-- 讲师表
# 讲师 ID 主键 int 类型
# 讲师姓名 VARCHAR 类型
# 讲师简介 VARCHAR 类型
# 讲师级别 char 类型 (比如高级讲师、首席讲师)
# 为讲师姓名添加索引
-- 课程分类表
# 课程分类 ID 主键 int 类型
# 课程分类名称 VARCHAR类型(比如前端开发、后端开发、数据库、DBA)
# 课程分类描述 VARCHAR 类型
# 创建时间 datetime 类型
# 更新时间 datetime 类型
-- 课程表
# 课程 ID 主键 int 类型
# 课程讲师 ID 外键(用于描述课程的授课老师)
# 课程分类 ID 外键(用于描述课程所属的分类。比如 Java 课程就属于后端分类)
# 课程标题 VARCHAR 类型(比如 Java、VUE、PHP)
# 总课时 int 类型
# 浏览数量 bigint 类型
# 课程状态 char 类型(默认为 0 表示未发布,1 表示已发布)
# 为课程标题字段添加索引
# 为 teacher_id 和 subject_id 添加外键约束
2) 查询刘德华老师所教的课程属于哪个课程分类
-- 向讲师表插入两条数据
INSERT INTO xxx_teacher VALUES (1, '刘德华', '毕业于清华大学,主攻前端技术,授课风格生动活泼,深受学员喜爱', '高级讲师');
INSERT INTO xxx_teacher VALUES (2, '郭富城', '毕业于北京大学,多年的IT经验,研发多项Java课题,授课经验丰富', '首席讲师');
-- 向课程分类表中插入两条数据
INSERT INTO xxx_subject VALUES (1, '后端开发', '后端课程包括 Java PHP Python', '2020-03-27 00:44:04', '2020-03-27 00:44:04');
INSERT INTO xxx_subject VALUES (2, '前端开发', '前端课程包括 JQuery VUE angularJS', '2020-02-27 10:00:04', '2020-02-27 18:44:04');
-- 向课程表中插入两条数据
# 插入 Java 课程
INSERT INTO xxx_course VALUES (1,1,1 ,'Java', 300,250000, '1');
# 插入 VUE 课程
INSERT INTO xxx_course VALUES (2,2,2, 'VUE', 400,200000,'1');
### 作者:张人大
#### 关系分析:讲师表和课程分类表是多对多的关系,课程表是它们之间的中间表。
-- 需求
-- 讲师表
DROP TABLE IF EXISTS xxx_teacher;
CREATE TABLE xxx_teacher(
# 讲师 ID 主键 int 类型
tid INT PRIMARY KEY,
# 讲师姓名 VARCHAR 类型
tname VARCHAR(20),
# 讲师简介 VARCHAR 类型
tdesc VARCHAR(50),
# 讲师级别 char 类型 (比如高级讲师、首席讲师)
rank CHAR(4)
);
# 为讲师姓名添加索引
CREATE INDEX tname_index ON xxx_teacher(tname);
DESC xxx_teacher;
-- 课程分类表
DROP TABLE IF EXISTS xxx_subject;
CREATE TABLE xxx_subject(
# 课程分类 ID 主键 int 类型
sid INT PRIMARY KEY,
# 课程分类名称 VARCHAR 类型(比如前端开发、后端开发、数据库、DBA)
sname VARCHAR(20),
# 课程分类描述 VARCHAR 类型
sdesc VARCHAR(50),
# 创建时间 datetime 类型
create_time DATETIME,
# 更新时间 datetime 类型
update_time DATETIME
);
DESC xxx_subject;
-- 课程表
DROP TABLE IF EXISTS xxx_course;
CREATE TABLE xxx_course(
# 课程 ID 主键 int 类型
cid INT PRIMARY KEY,
# 课程讲师 ID 外键(用于描述课程的授课老师)
teacher_id INT,
# 课程分类 ID 外键(用于描述课程所属的分类。比如 Java 课程就属于后端分类)
subject_id INT,
# 课程标题 VARCHAR 类型(比如 Java、VUE、PHP)
title VARCHAR(20),
# 总课时 int 类型
class_hours INT,
# 浏览数量 bigint 类型
page_views BIGINT,
# 课程状态 char 类型(默认为 0 表示未发布,1 表示已发布)
course_status CHAR(1) DEFAULT 0,
# 为课程标题字段添加索引
INDEX(title),
# 为 teacher_id 和 subject_id 添加外键约束
FOREIGN KEY(teacher_id) REFERENCES xxx_teacher(tid),
FOREIGN KEY(subject_id) REFERENCES xxx_subject(sid)
);
DESC xxx_course;
-- 插入数据
-- 向讲师表插入两条数据
INSERT INTO xxx_teacher
VALUES (1, '刘德华', '毕业于清华大学,主攻前端技术,授课风格生动活泼,深受学员喜爱', '高级讲师');
INSERT INTO xxx_teacher
VALUES (2, '郭富城', '毕业于北京大学,多年的IT经验,研发多项Java课题,授课经验丰富', '首席讲师');
-- 向课程分类表中插入两条数据
INSERT INTO xxx_subject
VALUES (1, '后端开发', '后端课程包括 Java PHP Python', '2020-03-27 00:44:04', '2020-03-27 00:44:04');
INSERT INTO xxx_subject
VALUES (2, '前端开发', '前端课程包括 JQuery VUE angularJS', '2020-02-27 10:00:04', '2020-02-27 18:44:04');
-- 向课程表中插入两条数据
# 插入 Java 课程
INSERT INTO xxx_course VALUES (1,1,1 ,'Java', 300,250000, '1');
# 插入 VUE 课程
INSERT INTO xxx_course VALUES (2,2,2, 'VUE', 400,200000,'1');
-- 查询
-- 查询刘德华老师所教的课程属于哪个课程分类
SELECT *
FROM xxx_subject s
WHERE s.`sid` = (
SELECT c.`subject_id`
FROM xxx_course c
WHERE c.`teacher_id` = (
SELECT t.`tid`
FROM xxx_teacher t
WHERE t.`tname` = '刘德华'
)
);