Mysql基础练习:
2.1. 基础练习一
Cd 到mysql安装目录（bin下）
输入mysql –u root –p
选择数据库/自己创建数据库,比如 use s4（会显示change s4）
创建student表，如下开始练习
Create table student...(表示创建好字段)
CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5))
CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL)
CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL)
CREATE TABLE TEACHER
(TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL,
TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL)
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108,'曾华'
,'男' ,19770901,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105,'匡明'
,'男' ,19751002,95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107,'王丽'
,'女' ,19760123,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101,'李军'
,'男' ,19760220,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109,'王芳'
,'女' ,19750210,95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103,'陆君'
,'男' ,19740603,95031);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');
创建好数据见下图：
2.2. 基础练习一及参考sql语句
1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select sname,ssex,class from student;
2、 查询教师所有的单位即不重复的Depart列。
Select distinct depart from teacher;
3、 查询Student表的所有记录。
Select * from student;
4、 查询Score表中成绩在60到80之间的所有记录。
Select degree from score where degree between 60 and 80;
5、 查询Score表中成绩为85，86或88的记录。
Select degree from score where degree in (85,86,88); 或者 Select * from score where degree in (85,86,88);
6、 查询Student表中“95031”班或性别为“女”的同学记录。
Select * from student where class ='95031'or ssex ='女';
7、 以Class降序查询Student表的所有记录。
Select class from student where class order by class desc;
8、 以Cno升序、Degree降序查询Score表的所有记录。
Select cno from score where cno order by cno;
Select degree from score where degree order by degree desc;
9、 查询“95031”班的学生人数。
select count(class) from student where class=95033;
10、查询Score表中的最高分的学生学号和课程号。
select sno,degree from score where degree = (select max(degree) from score );
11、查询‘3-105’号课程的平均分。
select avg(cno) from score where cno=‘3-105’;
12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select avg(degree) from score where cno='3-105';
13、查询最低分大于70，最高分小于90的Sno列。
select sno from score where degree between 70 and 90;
14、查询所有学生的Sname、Cno和Degree列。（两张表查询）
Select A.sname,B.cno,B.degree from student as A join score as B on A.sno=B.sno;
Left/right/inner在实际用时可以不加，as也可以省略，如下
Select A.sname,B.cno,B.degree from student (as) A (left/right/inner) join score (as) B on A.sno=B.sno;
(NTOE:要选出不同表中的数据，需要将多个表进行并联，sname在studetn，cno在course和score，degree在score，所以sno为student和degree共有的，则在这两张表上进行并联，首先从student中选出sname，其次将A加入到score表，将B加入到A表，同时A表的sno和B表的sno相等)
15、查询所有学生的Sno、Cname和Degree列。
Select A.cname,B.sno,B.degree from course as A join score as B on A.cno=B.cno;
16、查询所有学生的Sname、Cname和Degree列。(三张表查询)
select A.sname,B.cname,c.degree from student as A join (course B,score C) on A.sno=C.sno and B.cno=C.cno;
17、查询“95033”班所选课程的平均分。
Select avg(A.degree) from score as A join student B on A.sno=B.sno where B.class='95033';
18、假设使用如下命令建立了一个grade表：
create table grade(number(3,0), number(3),char(1));
insert into grade values(90,100,’A’);
insert into grade values(80,89,’B’);
insert into grade values(70,79,’C’);
insert into grade values(60,69,’D’);
insert into grade values(0,59,’E’);
commit;
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
commit
现查询所有同学的Sno、Cno和rank列。（两表查询）
select sno,cno,level from score,grade where score.degree between grade.low and grade.upp order by level;
19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select A.* from score as A join score B where A.cno='3-105' and A.degree>B.degree and B.sno='109' and B.cno='3-105';
20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
Step1 非最高分：degree not in (select max(degree) from score )
select * from score where sno not in (select s.sno from score s join (select cno,max(degree) degree from score group by cno) sc on (sc.cno=s.cno and sc.degree=s.degree));
21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select degree from score where degree>(select degree from score where sno='109' and cno='3-105');
22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select sno,sname,sbirthday from student where year(sbirthday)=(select year(sbirthday) from student where sno='108');
23、查询“张旭“教师任课的学生成绩。
select A.tno,B.cno,C.degree from teacher as A join (course B,score C) on A.tno=B.tno and B.cno=C.cno where A.tname=‘张旭’;
24、查询选修某课程的同学人数多于5人的教师姓名。
这样写（报错）：X.tno,Y.cno只能取其中一个
select tname from teacher where tno in (select X.tno,Y.cno from course X,score Y where X.cno=y.cno group by x.tno having count(x.tno)>5);
ERROR 1241 (21000): Operand should contain 1 column(s)
正确写法：
select tname from teacher where tno in (select X.tno from course X,score Y where X.cno=y.cno group by x.tno having count(x.tno)>5);
25、查询95033班和95031班全体学生的记录。
select * from student where class='95033'or class='95031';
26、查询存在有85分以上成绩的课程Cno.
select distinct cno from score where degree>85;
27、查询出“计算机系“教师所教课程的成绩表。(三表相联)
select cno,degree from score where cno in (select cno from course where tno in (select tno from teacher where depart='计算机系'));
select a.* from score a join (teacher b,course c) on a.cno=c.cno and b.tno=c.tno where b.depart='计算机系';
select * from score where cno in (select A.cno from Course A join teacher B on B.tno=A.tno and B.depart='计算机系');
28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
select tname,prof from teacher where depart='计算机系'and prof not in (select prof from teacher where depart='电子工程系');
select tname,prof from teacher where depart='电子工程系'and prof not in (select prof from teacher where depart='计算机系')
29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
select cno,sno,degree from score where cno='3-105'and degree>= any(select degree from score where cno='3-245') order by degree desc;
30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
select cno,sno,degree from score where cno='3-105'and degree>any(select degree from score where cno='3-245') order by degree desc;
31、查询所有教师和同学的name、sex和birthday.
select A.sname as name,A.ssex as sex, sbirthday as birthday from student A union select B.tname as name,B.tsex as sex,B.tbirthday as birthday from teacher B;
32、查询所有“女”教师和“女”同学的name、sex和birthday.
select A.sname as name,A.ssex as sex, sbirthday as birthday from student A where ssex='女' union select B.tname as name,B.tsex as sex,B.tbirthday as birthday from teacher B where tsex='女';
33、查询成绩比该课程平均成绩低的同学的成绩表。
select A.* from score A where degree <(select avg(degree) from Score B where B.CNO=A.CNO);
34、查询所有任课教师的Tname和Depart.
选项两个参数以上，防止备选项出现重复，比如出现两个“电子工程系”
select tname,depart from teacher; ❌
select A.tname,A.depart from teacher A join Course B on B.Tno=A.Tno;✔
select tname,depart from teacher where tno in (select tno from course); ✔
35 查询所有未讲课的教师的Tname和Depart.
select tname,depart from teacher where tno not in (select tno from course);
select tname,depart from teacher where not exists (select * from course where teacher.tno=course.tno);
36、查询至少有2名男生的班号。
select class from student where ssex='男' group by class having count(ssex)>=2;
37、查询Student表中不姓“王”的同学记录。
select sname from student where sname not like '王%';
38、查询Student表中每个学生的姓名和年龄。
select sname as name,(year(now())-year(sbirthday)) as age from student;
39、查询Student表中最大和最小的Sbirthday日期值。
select sname,sbirthday as maxbirthday from student where sbirthday=(select min(sbirthday) from student) union select sname,sbirthday as minbirthday from student where sbirthday=(select max(sbirthday) from student);
40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select class,(year(now())-year(sbirthday)) as age from student order by class desc,age desc;
41、查询“男”教师及其所上的课程。
select A.tname,B.cname from teacher A join course B using(TNO) where A.tsex='男';
42、查询最高分同学的Sno、Cno和Degree列。
Select sno,cno,degree from score where degree=(select max(degree) from score);
43、查询和“李军”同性别的所有同学的Sname.
Select A.sname from student A where A.ssex=(select B.ssex from student B where B.sname='李军');
44、查询和“李军”同性别并同班的同学Sname.
select sname from student a where ssex=(select ssex from student b where b.sname='李军' ) and class=(select class from student c where c.sname='李军')
45、查询所有选修“计算机导论”课程的“男”同学的成绩表（三表关联）
select * from score A where A.cno in (select B.cno from course B where B.cname='计算机导论') and A.sno in (select C.sno from student C where C.ssex='男');
select A.* from score A join (Course B,Student C) using(cno,sno) where B.cname='计算机导论' and C.ssex='男';
2.3. 基础练习二及参考sql语句
数据库中有三张表，分别为student,course,SC（即学生表，课程表，选课表）
//三张表截图如下：
创建表:
CREATE TABLE STUDENT(
SNO VARCHAR(8) NOT NULL,
SNAME VARCHAR(5) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SAGE VARCHAR(3) NOT NULL,
SDEPT VARCHAR(7) NOT NULL);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES (9512101,’李勇’,’男’,19,’计算机系’);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES (9512102,'刘晨','男' ,20,’计算机系’)
INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES (9512103,'王敏' ,'女' ,20,’计算机系’);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES (9521101,'张立' ,'男' ,22,’信息系’);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES (9521102,'吴宾' ,'女' ,21,’信息系’);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES (9521103,'张海' ,'男' ,20,’信息系’);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES (9531101,'钱小力' ,'女' ,18,’数学系’);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES (9531102,'王大力' ,'男' ,19,’数学系);
CREATE TABLE COURSE(
CNO VARCHAR(4) NOT NULL,
CNAME VARCHAR(7) NOT NULL,
HOURS VARCHAR(3) NOT NULL);
insert into course (cno,cname,hours) values ('c01','计算机文化 学,70);
insert into course (cno,cname,hours) values ('c01','VB',90);
insert into course (cno,cname,hours) values ('c03','计算机网络',80);
insert into course (cno,cname,hours) values ('c04','数据库基础',108);
insert into course (cno,cname,hours) values ('c05','高等数学',180);
insert into course (cno,cname,hours) values ('c06','数据结构',72);
CREATE TABLE SC(
SNO VARCHAR(8) NOT NULL,
CNO VARCHAR(4) NOT NULL,
GRADE VARCHAR(6) NOT NULL)
insert into sc (sno,cno,grade) values ('9512101','c01',90);
insert into sc (sno,cno,grade) values ('9512101','c02',86);
insert into sc (sno,cno,grade) values ('9512101','c06','<NULL>');
insert into sc (sno,cno,grade) values ('9512102','c02',78);
insert into sc (sno,cno,grade) values ('9512102','c04',66);
insert into sc (sno,cno,grade) values ('9521102','c01',82);
insert into sc (sno,cno,grade) values ('9521102','c02',75);
insert into sc (sno,cno,grade) values ('9521102','c04',92);
insert into sc (sno,cno,grade) values ('9521102','c05',50);
insert into sc (sno,cno,grade) values ('9521103','c02',68);
insert into sc (sno,cno,grade) values ('9521103','c06','<NULL>');
insert into sc (sno,cno,grade) values ('9531101','c01',80);
insert into sc (sno,cno,grade) values ('9531101','c05',95);
insert into sc (sno,cno,grade) values ('9531102','c05',85);
1、分别查询学生表和学生修课表中的全部数据。
Select * from student;
Select * from sc;
2、查询成绩在70到80分之间的学生的学号、课程号和成绩。
select sno,cno,grade from sc where grade between 70 and 80;
3、查询C01号课程成绩最高的分数。
select grade from sc where cno='c01' order by grade desc limit 1;
4、查询学生都选修了哪些课程,要求列出课程号。
select cno as 课程号,cname as 课程名称 from course where cno in (select cno from sc);
5、查询修了C02号课程的所有学生的平均成绩、最高成绩和最低成绩。
select avg(grade) as 平均成绩,max(grade) as 最高成绩,min(grade) as 最低成绩 from sc where cno='c02';
6、统计每个系的学生人数。
Select count(学生id) from 表 group by 系字段；
select sdept,count(sno) from student group by sdept;
7、统计每门课程的修课人数和考试最高分。
select cname,count(*) as 修课人数,max(grade) from course,sc where course.cno=sc.cno group by cname;
8、统计每个学生的选课门数,并按选课门数的递增顺序显示结果。
select sname,count(sc.sno) as 选课人数 from sc,student where student.sno=sc.sno group by sname order by count(sc.sno) asc;
9、统计选修课的学生总数和考试的平均成绩。
select count(distinct sno) as 学生总数,avg(grade) as 平均成绩 from sc;
10、查询选课门数超过2门的学生的平均成绩和选课门数。
Group by 一般最后用作求数或者排名用
select sname,avg(sc.grade),count(sc.cno) from student,sc where sc.sno=student.sno group by sname having count(sc.sno)>2;
11、列出总成绩超过200分的学生,要求列出学号、总成绩。
Select sno as 学号,sum(grade) as 总成绩 from sc group by sno having sum(grade)>200;
12、查询选修了c02号课程的学生的姓名和所在系。
select sname as 姓名,sdept as 所在系 from student where sno in (select sno from sc where sc.cno='c02');
select sname,sdept from student join sc using (sno) where sc.cno='c02';
13、查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果。
select A.sname,B.sno,B.grade from student A,sc B where A.sno=B.sno and B.grade>80 order by grade desc;
使用using匹配相同字段必须加()
select A.sname,B.sno,B.grade from student A join sc B using (sno) where B.grade>80 order by grade desc;
14、查询计算机系男生修了"数据库基础"的学生的姓名、性别、成绩。
select A.sname,A.ssex,B.grade,C.cname from student A,sc B,course C where A.sno=B.sno and B.cno=C.cno and A.sdept='计算机系' and C.cname='数据库基础';
15、查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。
select A.sname as 年龄相同的学生姓名, A.sage as 年龄 from student A join student B on A.sage=B.sage group by A.sname,A.sage order by A.sage;
这种做法看似正确，其实多过滤出来三组数据；
select A.sname as 年龄相同的学生姓名, A.sage as 年龄 from student A join student B on A.sage in (select sage from student where A.sage=B.sage and A.sname!=B.sname) group by A.sname,A.sage order by A.sage;
16、查询哪些课程没有人选,要求列出课程号和课程名。
Select cno as 课程号,cname as 课程名 from course where cno not in (select cno from sc);
17、查询有考试成绩的所有学生的姓名、修课名称及考试成绩要求将查询结果放在一张新的永久表(假设新表名为new-sc)中。
Select student.sname,course.cname,sc.grade from student,course,sc where student.sno =sc.sno=course.cno=sc.cno and sc.grade is not null order by sname;
18、分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,并要求将这两个查询结果合并成一个结果集,并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。(Union)
Select student.ssame,student.ssex,course.cname,sc.grade from student,course,sc where student.sdept=’计算机系’ and student.sno=sc.sno and course.cno=sc.cno;
19、用子查询实现如下查询:
查询选修了C01号课程的学生的姓名和所在系。
Select distinct student.sname,student.sdept from student,sc where student.sno in (select sc.sno from sc where sc.cno=’c01’);
查询数学系成绩80分以上的学生的学号、姓名。
Select sno,sname from student where sno in (select sno from sc where sc.grade>80) and sno in (select sno from student where sdept =’数学系’);
查询计算机系学生所选的课程名.
Select course.cname from course where cno in (select cno from sc sc.sno in (select sno from student where sdept=’计算机系’));
-- 创建数据库
CREATE DATABASE jing_dong;
-- 切换数据库
USE jing_dong;
-- 创建用户表
CREATE TABLE users
(
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(30),
password VARCHAR(30)
);
-- 查询表数据
SELECT *
FROM users;
/*
SQL注入攻击相关:
概述:
实际开发中, 如果SQL中的部分内容是要求用户键盘录入的, 当用户录入一些非法字符的时候, 就改变了SQL语句的结构,
从而引发一些列的安全问题, 这些问题, 就是 SQL注入攻击问题.
例如: 登陆账号的校验SQL如下
select * from users where username = '用户录入的账号' and password = '用户录入的密码';
听话的用户:
录入的账号: sdkfsd
录入的密码: weor
小屌丝:
录入的账号: slkkjlsd
录入的密码: 2321sfsd ' or '1=1
老屌丝:
录入的账号: slkkjlsd
录入的密码: 2321sfsd ' or '1=1'; drop database jing_dong;'
*/
-- 校验登陆
SELECT *
FROM users
WHERE username = 'slkkjlsd' AND password = '2321sfsd '
OR '2=2';
SELECT *
FROM users
WHERE username = 'slkkjlsd' AND password = '2321sfsd '
OR '1=1';
DROP DATABASE jing_dong;
'';
-- **************************** 以下是SQL加强练习相关 ****************************
-- 创建数据库, 报表库
CREATE DATABASE record_form;
-- 切换数据库
USE record_form;
-- 查询所有的数据表
SHOW TABLES;
SELECT *
FROM customers;
-- 导入表数据
-- 直接运行 报表项目数据.sql 文件即可, 我的在: ..教案-报表项目数据.sql
-- 以下是各种需求, 通过的代码实现即可.
-- 需求1: 选中employees 表的所有数据
SELECT *
FROM employees;
-- 需求2: 查询每个客户的 ID, company name, contact name, contact title, city, 和 country.并按照国家名字排序
SELECT company_name,
contact_name,
contact_title,
city,
country
FROM customers
ORDER BY country;
-- 需求3: 查询每一个商品的product_name，category_name，quantity_per_unit，unit_price，units_in_stock 并且通过 unit_price 字段排序
SELECT product_name, category_name, quantity_per_unit, unit_price, units_in_stock
FROM categories c,
products p
WHERE c.category_id = p.category_id
ORDER BY unit_price;
-- 需求4: 列出所有提供了4种以上不同商品的供应商列表所需字段：supplier_id, company_name, and products_count (提供的商品种类数量).
SELECT p.supplier_id,
company_name,
COUNT(*) AS products_count
FROM products p,
suppliers s
WHERE p.supplier_id = s.supplier_id
GROUP BY p.supplier_id, company_name
HAVING products_count > 4;
-- 需求5: 提取订单编号为10250的订单详情，显示如下信息：
-- product_name, quantity, unit_price （ order_items 表), discount ，order_date 按商品名字排序
SELECT o.order_id, -- 订单编号
product_name, -- 商品名字
quantity, -- 商品数量
oi.unit_price, -- 商品单价
discount, -- 折扣
o.order_date -- 订单时间
FROM products p,
order_items oi,
orders o
WHERE p.product_id = oi.product_id
AND oi.order_id = o.order_id
AND o.order_id = 10250
ORDER BY product_name;
-- 需求6: 收集运输到法国的订单的相关信息，包括订单涉及的顾客和员工信息，下单和发货日期等.
SELECT c.customer_id, -- 顾客id
c.company_name, -- 顾客公司名字
e.employee_id, -- 员工id
e.first_name, -- 员工名
e.last_name, -- 员工姓
o.order_date, -- 订单时间
o.shipped_date, -- 装船时间
o.ship_country -- 收件人国家
FROM employees e,
orders o,
customers c
WHERE e.employee_id = o.employee_id
AND o.customer_id = c.customer_id
AND ship_country = 'France';
-- 需求7: 提供订单编号为10248的相关信息，包括product name, unit price (在 order_items 表中), quantity（数量）,company_name（供应商公司名字 ，起别名 supplier_name).
SELECT oi.order_id,
p.product_name,
oi.unit_price,
oi.quantity,
s.supplier_id,
s.company_name AS supplier_name
FROM order_items oi,
products p,
suppliers s
WHERE oi.product_id = p.product_id
AND p.supplier_id = s.supplier_id
AND order_id = 10248;
-- 需求8: 提取每件商品的详细信息，包括 商品名称（product_name）, 供应商的公司名称 (company_name，在 suppliers 表中),
-- 类别名称 category_name, 商品单价unit_price, 和每单位商品数量quantity per unit
SELECT product_name, -- 商品名字
s.company_name AS supplier_name, -- 供应商的公司名称
c.category_name, -- 类别名称
unit_price, -- 商品单价
quantity_per_unit -- 每单位商品数量, 例如, 一箱20瓶
FROM categories c,
products p,
suppliers s
WHERE c.category_id = p.category_id
AND p.supplier_id = s.supplier_id;
-- 需求9: 另一种常见的报表需求是查询某段时间内的业务指标, 我们统计2016年7月的订单数量，
SELECT COUNT(*)
FROM orders
WHERE order_date BETWEEN '2016-07-01' AND '2016-07-31';
-- 需求10: 统计2013年入职的员工数量，统计字段起别名 number_of_employees
SELECT COUNT(*) number_of_employees
FROM employees
WHERE hire_date BETWEEN '2013-01-01' AND '2013-12-31';
-- 需求11: 统计每个供应商供应的商品种类数量, 结果返回供应商IDsupplier_id
-- ，公司名字company_name ，商品种类数量（起别名products_count )使用 products 和 suppliers 表.
SELECT p.supplier_id, s.company_name, COUNT(*) AS products_count
FROM products p,
suppliers s
WHERE p.supplier_id = s.supplier_id
GROUP BY p.supplier_id, s.company_name;
-- 需求12: 我们要查找ID为10250的订单的总价（折扣前），SUM(unit_price * quantity)
SELECT order_id, SUM(unit_price * quantity)
FROM order_items
WHERE order_id = 10250;
-- 需求13: 统计每个员工处理的订单总数, 结果包含员工IDemployee_id，姓名first_name 和 last_name，处理的订单总数(别名 orders_count)
SELECT e.employee_id, e.first_name, e.last_name, COUNT(*) AS orders_count
FROM employees e
INNER JOIN orders o ON e.employee_id = o.employee_id
GROUP BY e.employee_id, e.first_name, e.last_name;
-- 需求14: 统计每个类别中的库存产品值多少钱？显示三列：category_id, category_name, 和 category_total_value, 如何计算库存商品总价：SUM(unit_price * units_in_stock)。
SELECT c.category_id,
category_name,
SUM(unit_price * units_in_stock) AS category_total_value -- 库存商品总和
FROM products p,
categories c
WHERE c.category_id = p.category_id
GROUP BY c.category_id, category_name;
-- 需求15: 计算每个员工的订单数量
SELECT e.employee_id, e.first_name, e.last_name, COUNT(*) AS orders_count
FROM employees e
INNER JOIN orders o ON e.employee_id = o.employee_id
GROUP BY e.employee_id, e.first_name, e.last_name;
-- 需求16: 计算每个客户的下订单数 结果包含：用户id、用户公司名称、订单数量（customer_id, company_name, orders_count ）
SELECT c.customer_id, c.company_name, COUNT(*) orders_count
FROM orders o,
customers c
WHERE o.customer_id = c.customer_id
GROUP BY c.customer_id, c.company_name;
-- 需求17: 统计2016年6月到2016年7月用户的总下单金额并按金额从高到低排序
-- 结果包含：顾客公司名称company_name 和总下单金额（折后实付金额）total_paid
-- 提示：
-- 计算实际总付款金额： SUM(unit_price quantity (1 - discount))
-- 日期过滤 WHERE order_date >= '2016-06-01' AND order_date < '2016-08-01'
SELECT company_name,
SUM(unit_price * quantity * (1 - discount)) total_paid -- 实际总付款金额
FROM orders o,
customers c,
order_items oi
WHERE o.order_id = oi.order_id
AND o.customer_id = c.customer_id
AND order_date >= '2016-06-01'
AND order_date < '2016-08-01'
GROUP BY company_name
ORDER BY total_paid DESC;
-- 需求18: 统计客户总数和带有传真号码的客户数量
-- 需要字段：all_customers_count 和 customers_with_fax_count
SELECT COUNT(*) all_customers_count, -- 客户总数
COUNT(fax) customers_with_fax_count -- 带有传真号码的客户数量
FROM customers;
-- case.when简介: 类似于Python中的 if.else if, 做分支的.
-- case.when语法: case when 条件1 then 值1 when 条件2 then 值2 ... else 值n end
-- 需求19: 我们要在报表中显示每种产品的库存量，但我们不想简单地将“ units_in_stock”列放在报表中。报表中只需要一个总体级别，例如低，高：
-- 库存大于100 的可用性为高(high)
-- 50到100的可用性为中等(moderate)
-- 小于50的为低(low)
-- 零库存 为 (none)
SELECT product_id,
product_name,
units_in_stock,
CASE
WHEN units_in_stock > 100 THEN 'high'
WHEN units_in_stock >= 50 THEN 'moderate'
WHEN units_in_stock > 0 THEN 'low'
WHEN units_in_stock = 0 THEN 'none'
END '库存量等级'
FROM products;
-- 需求20: 创建一个报表，统计员工的经验水平
-- 显示字段：first_name, last_name, hire_date, 和 experience
-- 经验字段（experience ）：
-- 'junior' 2014年1月1日以后雇用的员工
-- 'middle' 在2013年1月1日之后至2014年1月1日之前雇用的员工
-- 'senior' 2013年1月1日或之前雇用的员工
SELECT first_name,
last_name,
hire_date,
CASE
WHEN hire_date > '2014-01-01' THEN 'junior'
WHEN hire_date > '2013-01-01' THEN 'middle'
WHEN hire_date <= '2013-01-01' THEN 'senior'
END experience
FROM employees;
-- 需求21: 我们的商店要针对北美地区的用户做促销活动：任何运送到北美地区（美国，加拿大) 的包裹免运费。 创建报表，查询订单编号为10720~10730 活动后的运费价格
SELECT order_id,
customer_id,
ship_country,
freight '促销活动前的运费',
CASE
WHEN ship_country IN ('USA', 'Canada') THEN 0.0
ELSE freight
END '促销活动后的运费'
FROM orders
WHERE order_id BETWEEN '10720' AND '10730';
-- 需求22: 需求：创建客户基本信息报表, 包含字段：客户id customer_id, 公司名字 company_name
-- 所在国家 country, 使用语言language, 使用语言language 的取值按如下规则
-- Germany, Switzerland, and Austria 语言为德语 'German', UK, Canada, the USA, and Ireland -- 语言为英语 'English', 其他所有国家 'Other'
SELECT customer_id,
company_name,
country,
CASE
WHEN country IN ('Germany', 'Switzerland', 'Austria') THEN 'German_德语'
WHEN country IN ('UK', 'Canada', 'USA', 'Ireland') THEN 'English_英语'
ELSE 'Other_其它语言'
END language
FROM customers;
-- 需求23: 需求：创建报表将所有产品划分为素食和非素食两类
-- 报表中包含如下字段：产品名字 product_name, 类别名称 category_name
-- 膳食类型 diet_type:
-- 非素食 'Non-vegetarian' 商品类别字段的值为 'Meat/Poultry' 和 'Seafood'.
-- 素食
SELECT product_name,
category_name,
CASE
WHEN category_name IN ('Meat/Poultry', 'Seafood') THEN '非素食'
ELSE '素食' END '膳食类型'
FROM categories c,
products p
WHERE c.category_id = p.category_id;
-- 需求24: 在引入北美地区免运费的促销策略时，我们也想知道运送到北美地区和其它国家地区的订单数量
-- 促销策略, 参见需求21的代码.
SELECT CASE
WHEN ship_country IN ('USA', 'Canada') THEN '北美地区'
ELSE '其它国家地区' END country,
COUNT(*) AS order_count -- 订单总数
FROM orders
GROUP BY CASE
WHEN ship_country IN ('USA', 'Canada') THEN '北美地区'
ELSE '其它国家地区' END;
-- 简化写法
SELECT CASE
WHEN ship_country IN ('USA', 'Canada') THEN '北美地区'
ELSE '其它国家地区' END country,
COUNT(*) AS order_count -- 订单总数
FROM orders
GROUP BY country;
-- 需求25: 创建报表统计供应商来自那个大洲, 报表中包含两个字段：供应商来自哪个大洲（supplier_continent ）和 供应产品种类数量（product_count）
-- 供应商来自哪个大洲（supplier_continent ）包含如下取值：
-- 'North America' （供应商来自 'USA' 和 'Canada'.）
-- 'Asia' （供应商来自 'Japan' 和 'Singapore')
-- 'Other' (其它国家)
SELECT CASE
WHEN country IN ('USA', 'Canada') THEN 'North America'
WHEN country IN ('Japan', 'Singapore') THEN 'Asia'
ELSE '其它国家' END newCountry,
COUNT(*)
FROM products p,
suppliers s
WHERE p.supplier_id = s.supplier_id
GROUP BY newCountry;
-- 需求26: 需求：创建一个简单的报表来统计员工的年龄情况
-- 报表中包含如下字段
-- 年龄（ age ）：生日大于1980年1月1日 'young' ，其余'old'
-- 员工数量 （ employee_count）
SELECT CASE
WHEN birth_date > '1980-01-01' THEN 'young'
ELSE 'old' END age
, COUNT(*)
FROM employees
GROUP BY age;
-- 需求27: 统计客户的contact_title 字段值为 ’Owner' 的客户数量
-- 查询结果有两个字段：represented_by_owner 和 not_represented_by_owner
SELECT CASE
WHEN contact_title = 'Owner' THEN 'represented_by_owner'
ELSE 'not_represented_by_owner' END title,
COUNT(*)
FROM customers
GROUP BY title;
-- case.when的另一种写法: case 字段 when 值1 then 结果1 when 值2 then 结果2... else 结果n end
SELECT CASE contact_title
WHEN 'Owner' THEN 'represented_by_owner'
ELSE 'not_represented_by_owner' END title,
COUNT(*)
FROM customers
GROUP BY title;
-- 需求28: Washington (WA) 是 Northwind的主要运营地区，统计有多少订单是由华盛顿地区的员工处理的，
-- 多少订单是有其它地区的员工处理的
-- 结果字段： orders_wa_employees 和 orders_not_wa_employees
SELECT COUNT(CASE WHEN region = 'WA' THEN order_id END) AS orders_wa_employees,
COUNT(CASE WHEN region != 'WA' THEN order_id END) orders_not_wa_employees
FROM orders o,
employees e
WHERE e.employee_id = o.employee_id;
-- 需求29: 创建报表，统计不同类别产品的库存量，将库存量分成两类 >30 和 <=30 两档分别统计数量
-- 报表包含三个字段, 类别名称 category_name, 库存充足 high_availability, 库存紧张 low_availability
SELECT c.category_id,
c.category_name,
COUNT(CASE WHEN units_in_stock > 30 THEN product_id END) high_availability,
COUNT(CASE WHEN units_in_stock <= 30 THEN product_id END) low_availability
FROM categories c,
products p
WHERE c.category_id = p.category_id
GROUP BY c.category_id, c.category_name;
-- 需求30: 创建报表统计运输到法国的的订单中，打折和未打折订单的总数量
-- 结果包含两个字段：full_price （原价）和 discounted_price（打折）
SELECT SUM(CASE WHEN discount = 0 THEN 1 END) 'full_price_原价',
SUM(CASE WHEN discount != 0 THEN 1 END) 'discounted_price_打折'
FROM orders o,
order_items oi
WHERE o.order_id = oi.order_id
AND ship_country = 'France';
-- 需求31: 输出报表，统计不同供应商供应商品的总库存量，以及高价值商品的库存量（单价超过40定义为高价值）
-- 结果显示四列：
-- 供应商ID supplier_id
-- 供应商公司名 company_name
-- 由该供应商提供的总库存 all_units
-- 由该供应商提供的高价值商品库存 expensive_units
SELECT s.supplier_id,
s.company_name,
SUM(units_in_stock) AS '供应商品的总库存量',
SUM(CASE WHEN unit_price > 40 THEN units_in_stock END) AS '高价值商品的库存量'
FROM suppliers s,
products p
WHERE p.supplier_id = s.supplier_id
GROUP BY s.supplier_id, s.company_name;
-- 需求32: 创建报表来为每种商品添加价格标签，贵、中等、便宜
-- 结果包含如下字段：product_id, product_name, unit_price, 和 price_level
-- 价格等级price_level的取值说明：
-- 'expensive' 单价高于100的产品
-- 'average' 单价高于40但不超过100的产品
-- 'cheap' 其他产品
SELECT product_id,
product_name,
unit_price,
CASE
WHEN unit_price > 100 THEN 'expensive'
WHEN unit_price > 40 THEN 'average'
ELSE 'cheap'
END AS price_level
FROM products;
-- 需求33: 制作报表统计所有订单的总价（不计任何折扣）对它们进行分类。
-- 包含以下字段：
-- order_id
-- total_price（折扣前）
-- price_group
-- 字段 price_group 取值说明：
-- 'high' 总价超过2000美元
-- 'average'，总价在$ 600到$ 2,000之间，包括两端
-- 'low' 总价低于$ 600
SELECT o.order_id,
SUM(unit_price * quantity) total_price,
CASE
WHEN SUM(unit_price * quantity) > 2000 THEN 'high'
WHEN SUM(unit_price * quantity) > 600 THEN 'average'
ELSE 'low' END price_group
FROM orders o,
order_items oi
WHERE o.order_id = oi.order_id
GROUP BY o.order_id;
-- 需求34: 统计所有订单的运费，将运费高低分为三档
-- 报表中包含三个字段
-- low_freight freight值小于“ 40.0”的订单数
-- avg_freight freight值大于或等于“ 40.0”但小于“ 80.0”的订单数
-- high_freight freight值大于或等于“ 80.0”的订单数
SELECT COUNT(CASE WHEN freight >= 80.0 THEN order_id END) high_freight,
COUNT(CASE WHEN freight >= 40.0 AND freight < 80.0 THEN order_id END) avg_freight,
COUNT(CASE WHEN freight < 40.0 THEN order_id END) low_freight
FROM orders;
MySQLSQL练习.zip