MySQL练习题

一、部门员工表练习题

-- -- 部门表
-- create table dept(
--         deptno int primary key auto_increment, -- 部门编号
--         dname varchar(14) ,      -- 部门名字
--         loc varchar(13)   -- 地址
-- ) ;
-- -- 员工表
-- create table emp(
--         empno int primary key auto_increment,-- 员工编号
--         ename varchar(10), -- 员工姓名                                        -
--         job varchar(9),    -- 岗位
--         mgr int,     -- 直接领导编号
--         hiredate date, -- 雇佣日期，入职日期
--         sal int, -- 薪水/工资
--         comm int,  -- 提成
--         deptno int not null, -- 部门编号
--         foreign key (deptno) references dept(deptno)
-- );
-- 
-- insert into dept values(10,'财务部','北京');
-- insert into dept values(20,'研发部','上海');
-- insert into dept values(30,'销售部','广州');
-- insert into dept values(40,'行政部','深圳');
-- insert into emp values(7369,'刘一','职员',7902,'1980-12-17',800,null,20);
-- insert into emp values(7499,'陈二','推销员',7698,'1981-02-20',1600,300,30);
-- insert into emp values(7521,'张三','推销员',7698,'1981-02-22',1250,500,30);
-- insert into emp values(7566,'李四','经理',7839,'1981-04-02',2975,null,20);
-- insert into emp values(7654,'王五','推销员',7698,'1981-09-28',1250,1400,30);
-- insert into emp values(7698,'赵六','经理',7839,'1981-05-01',2850,null,30);
-- insert into emp values(7782,'孙七','经理',7839,'1981-06-09',2450,null,10);
-- insert into emp values(7788,'周八','分析师',7566,'1987-06-13',3000,null,20);
-- insert into emp values(7839,'吴九','总裁',null,'1981-11-17',5000,null,10);
-- insert into emp values(7844,'郑十','推销员',7698,'1981-09-08',1500,0,30);
-- insert into emp values(7876,'郭十一','职员',7788,'1987-06-13',1100,null,20);
-- insert into emp values(7900,'钱多多','职员',7698,'1981-12-03',950,null,30);
-- insert into emp values(7902,'大锦鲤','分析师',7566,'1981-12-03',3000,null,20);
-- insert into emp values(7934,'木有钱','职员',7782,'1983-01-23',1300,null,10);

-- 1．列出至少有一个员工的所有部门。
select deptno from emp group by deptno having count(deptno) > 0;
-- 2．列出薪金比"刘一"多的所有员工。
select ename from emp where sal > (select sal from emp where ename = '刘一');
-- *3．列出所有员工的姓名及其直接上级的姓名。(自连接:连接自己)
select e1.ename as 员工,e2.ename as 上级 from emp as e1 inner join emp as e2 on e1.mgr = e2.empno;

-- *4．列出受雇日期早于其直接上级的所有员工。
select e1.ename from emp as e1 inner join emp as e2 on e1.hiredate < e2.hiredate and e1.mgr = e2.empno;

-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select d.dname,e.ename from dept as d left join emp as e on e.deptno = d.deptno;
-- 6．列出所有job为“职员”的姓名及其部门名称。
select d.dname,e.ename from dept as d left join emp as e on e.deptno = d.deptno where e.job = '职员';
-- 7．列出最低薪金大于1500的各种工作。
select job from emp where sal > 1500 group by job;
-- 8．列出在部门 "销售部" 工作的员工的姓名，假定不知道销售部的部门编号。
select e.ename from emp as e left join dept as d on e.deptno = d.deptno where d.dname = '销售部';
-- 9．列出薪金高于公司平均薪金的所有员工。
select ename from emp where sal > (select avg(sal) from emp);
-- 10．列出与"周八"从事相同工作的所有员工。
select ename from emp where job = (select job from emp where ename = '周八') and ename != '周八';
-- 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select ename,sal from emp where sal in (select sal from emp where deptno = 30);
-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select ename,sal from emp where sal > (select max(sal) from emp where deptno = 30);

-- 13．列出在每个部门工作的员工数量、平均工资。
select count(*) as 员工数量,avg(sal) as 平均工资 from emp GROUP BY deptno;

-- 14．列出所有员工的姓名、部门名称和工资。
select e.ename,d.dname,e.sal from emp as e,dept as d where e.deptno = d.deptno;

-- 15．列出各种工作的最低工资。
select min(sal) from emp GROUP BY job;

-- 16．列出各个部门的'经理'的最低薪金。
select deptno,job,min(sal) from emp where job = '经理' group by deptno;

-- 17. 显示出薪水最高人的职位。
select job from emp where sal = (select max(sal) from emp);

-- 18. 查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。
select empno,ename,sal from emp where sal >= 3000;

-- 19. 查询出所有薪水在'陈二'之上的所有人员信息。
select * from emp where sal > (select sal from emp where ename = '陈二');

-- 20.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水
select empno,ename,sal from emp where deptno = 20 and sal > 2000;

-- 21.查询出emp表中所有的工作种类（无重复）
select distinct job from emp;
-- 22.查询出所有奖金（comm）字段不为空的人员的所有信息。
select * from emp where comm is not null;
-- 23.查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）
select * from emp where sal between 800 and 2500;
select * from emp where sal >= 800 and sal <= 2500;
-- 24.查询出最早工作的那个人的名字、入职时间和薪水。
select ename,hiredate,sal from emp where hiredate = (select min(hiredate) from emp);
-- 25.查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）
select * from emp where empno in (7521,7900,7782);
select * from emp where empno = 7521 or empno = 7900 or empno = 7782;
-- 26.查询出名字中有“张”字符，并且薪水在1000以上（不包括1000）的所有员工信息。
select * from emp where ename like '%张%' and sal > 1000;
-- 27.查询出名字第三个汉字是“多”的所有员工信息。
select * from emp where ename like '__多';
-- 28.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。
select * from emp order by sal,hiredate desc;

二、学生选课表练习

-- 创建学院表
-- create table tb_college
-- (
--         collid         int auto_increment comment '编号',
--         collname     varchar(50) not null comment '名称',
--         collintro     varchar(500) default '' comment '介绍',
--         primary key (collid)
-- );
-- 
-- -- 创建学生表
-- create table tb_student
-- (
--         stuid         int not null comment '学号',
--         stuname     varchar(20) not null comment '姓名',
--         stusex         boolean default 1 comment '性别',
--         stubirth     date not null comment '出生日期',
--         stuaddr     varchar(255) default '' comment '籍贯',
--         collid         int not null comment '所属学院',
--         primary key (stuid),
--         foreign key (collid) references tb_college (collid)
-- );
-- 
-- -- 创建教师表
-- create table tb_teacher
-- (
--         teaid         int not null comment '工号',
--         teaname     varchar(20) not null comment '姓名',
--         teatitle     varchar(10) default '助教' comment '职称',
--         collid         int not null comment '所属学院',
--         primary key (teaid),
--         foreign key (collid) references tb_college (collid)
-- );
-- 
-- -- 创建课程表
-- create table tb_course
-- (
--         couid         int not null comment '编号',
--         couname     varchar(50) not null comment '名称',
--         coucredit     int not null comment '学分',
--         teaid         int not null comment '授课老师',
--         primary key (couid),
--         foreign key (teaid) references tb_teacher (teaid)
-- );
-- 
-- -- 创建选课记录表
-- create table tb_record
-- (
--         recid         int auto_increment comment '选课记录编号',
--         sid         int not null comment '选课学生',
--         cid         int not null comment '所选课程',
--         seldate     datetime default now() comment '选课时间日期',
--         score         decimal(4,1) comment '考试成绩',
--         primary key (recid),
--         foreign key (sid) references tb_student (stuid),
--         foreign key (cid) references tb_course (couid),
--         unique (sid, cid)
-- );
-- 

-- -- 插入学院数据
-- insert into tb_college (collname, collintro) values 
-- ('计算机学院', '创建于1956年是我国首批建立计算机专业。学院现有计算机科学与技术一级学科和网络空间安全一级学科博士学位授予权，其中计算机科学与技术一级学科具有博士后流动站。计算机科学与技术一级学科在2017年全国第四轮学科评估中评为A；2019 U.S.News全球计算机学科排名26名；ESI学科排名0.945‰，进入全球前1‰，位列第43位。'),
-- ('外国语学院', '1998年浙江大学、杭州大学、浙江农业大学、浙江医科大学四校合并，成立新的浙江大学。1999年原浙江大学外语系、原杭州大学外国语学院、原杭州大学大外部、原浙江农业大学公外部、原浙江医科大学外语教学部合并，成立浙江大学外国语学院。2003年学院更名为浙江大学外国语言文化与国际交流学院。'),
-- ('经济管理学院', '四川大学经济学院历史悠久、传承厚重，其前身是创办于1905年的四川大学经济科,距今已有100多年的历史。已故著名经济学家彭迪先、张与九、蒋学模、胡寄窗、陶大镛、胡代光，以及当代著名学者刘诗白等曾先后在此任教或学习。在长期的办学过程中，学院坚持以马克思主义的立场、观点、方法为指导，围绕建设世界一流经济学院的奋斗目标，做实“两个伟大”深度融合，不断提高党的建设质量与科学推进一流事业深度融合。');
-- 
-- -- 插入学生数据
-- insert into tb_student (stuid, stuname, stusex, stubirth, stuaddr, collid) values
-- (1001, '杨逍', 1, '1990-3-4', '四川成都', 1),
-- (1002, '任我行', 1, '1992-2-2', '湖南长沙', 1),
-- (1033, '王语嫣', 0, '1989-12-3', '四川成都', 1),
-- (1572, '岳不群', 1, '1993-7-19', '陕西咸阳', 1),
-- (1378, '纪嫣然', 0, '1995-8-12', '四川绵阳', 1),
-- (1954, '林平之', 1, '1994-9-20', '福建莆田', 1),
-- (2035, '东方不败', 1, '1988-6-30', null, 2),
-- (3011, '林震南', 1, '1985-12-12', '福建莆田', 3),
-- (3755, '项少龙', 1, '1993-1-25', null, 3),
-- (3923, '杨不悔', 0, '1985-4-17', '四川成都', 3),
-- (4040, '隔壁老王', 1, '1989-1-1', '四川成都', 2);
-- 
-- 
-- -- 插入老师数据
-- insert into tb_teacher (teaid, teaname, teatitle, collid) values 
-- (1122, '张三丰', '教授', 1),
-- (1133, '宋远桥', '副教授', 1),
-- (1144, '杨逍', '副教授', 1),
-- (2255, '范遥', '副教授', 2),
-- (3366, '韦一笑', '讲师', 3);
-- 
-- -- 插入课程数据
-- insert into tb_course (couid, couname, coucredit, teaid) values 
-- (1111, 'Python程序设计', 3, 1122),
-- (2222, 'Web前端开发', 2, 1122),
-- (3333, '操作系统', 4, 1122),
-- (4444, '计算机网络', 2, 1133),
-- (5555, '编译原理', 4, 1144),
-- (6666, '算法和数据结构', 3, 1144),
-- (7777, '经贸法语', 3, 2255),
-- (8888, '成本会计', 2, 3366),
-- (9999, '审计学', 3, 3366);
-- 
-- -- 插入选课数据
-- insert into tb_record (sid, cid, seldate, score) values 
-- (1001, 1111, '2017-09-01', 95),
-- (1001, 2222, '2017-09-01', 87.5),
-- (1001, 3333, '2017-09-01', 100),
-- (1001, 4444, '2018-09-03', null),
-- (1001, 6666, '2017-09-02', 100),
-- (1002, 1111, '2017-09-03', 65),
-- (1002, 5555, '2017-09-01', 42),
-- (1033, 1111, '2017-09-03', 92.5),
-- (1033, 4444, '2017-09-01', 78),
-- (1033, 5555, '2017-09-01', 82.5),
-- (1572, 1111, '2017-09-02', 78),
-- (1378, 1111, '2017-09-05', 82),
-- (1378, 7777, '2017-09-02', 65.5),
-- (2035, 7777, '2018-09-03', 88),
-- (2035, 9999, default, null),
-- (3755, 1111, default, null),
-- (3755, 8888, default, null),
-- (3755, 9999, '2017-09-01', 92);

-- 1.查询所有学生信息
select * from tb_student;

-- 2.查询所有课程名称及学分
select couname,coucredit from tb_course;

-- 3.查询所有学生的姓名和性别
select stuname,stusex from tb_student;

-- 4.查询所有女学生的姓名和出生日期
select stuname,stubirth from tb_student where stusex = 0;

-- 5.查询所有80后学生的姓名、性别和出生日期
select stuname,stusex,stubirth from tb_student where stubirth like '198%';

-- 6.查询姓"杨"的学生姓名和性别
select stuname,stusex from tb_student where stuname like '杨%';

-- 7.查询姓"杨"名字两个字的学生姓名和性别
select stuname,stusex from tb_student where stuname like '杨_';

-- 8.查询姓"杨"名字三个字的学生姓名和性别
select stuname,stusex from tb_student where stuname like '杨__';

-- 9.查询名字中有"不"字或"嫣"字的学生的姓名
select stuname from tb_student where stuname like '%不%' or stuname like '%嫣%';

-- 10.查询没有录入家庭住址的学生姓名
select stuname from tb_student where stuaddr is null;

-- 11.查询录入了家庭住址的学生姓名
select stuname from tb_student where stuaddr is not null;

-- 12.查询学生选课的所有日期
select seldate from tb_record;

-- 13.查询学生的家庭住址
select stuname,stuaddr from tb_student;

-- 14.查询男学生的生日按年龄从大到小排列
select stuname,stubirth from tb_student where stusex = 1 order by stubirth desc;

-- 15.查询年龄最大的学生的出生日期
select stuname,stubirth from tb_student where stubirth = (select min(stubirth) from tb_student);

-- 16.查询年龄最小的学生的出生日期
select stuname,stubirth from tb_student where stubirth = (select max(stubirth) from tb_student);

-- 17.分别查询男学生和女学生的人数
select stusex,count(*) from tb_student group by stusex;

-- 18.查询课程编号为1111的课程的平均成绩
select avg(score) from tb_record where cid = '1111';

-- 19.查询学号为1001的学生所有课程的平均分
select avg(score) from tb_record where sid = '1001';

-- 20.查询每个学生的学号和平均成绩
select stuname,sid,avg(score) from tb_student,tb_record where tb_student.stuid = tb_record.sid group by tb_record.sid;

-- 21.查询平均成绩大于等于90分的学生的学号和平均成绩
select sid,avg(score) from tb_record group by sid having avg(score) >= 90;

-- 22.查询年龄最大的学生的姓名
select stuname from tb_student where stubirth = (select min(stubirth) from tb_student);

-- 23.查询选了两门以上的课程的学生姓名
select stuname from tb_student,tb_record where stuid = sid group by sid having count(*) > 2;

-- 24.查询学生姓名、课程名称以及成绩
select stuname,couname,score from tb_student,tb_course,tb_record where stuid = sid and cid = couid;

-- 25.查询学生姓名、课程名称以及成绩按成绩从高到低查询第11-15条记录
select stuname,couname,score from tb_student,tb_course,tb_record where stuid = sid and cid = couid order by score desc limit 10,5;

-- 26.查询每个学生的姓名和选课数量
select stuname,count(*) as 选课数量 from tb_student,tb_record where stuid = sid group by sid;

三、综合练习

-- create table students (
--     sno varchar(3) not null, 
--     sname varchar(4) not null, 
--     ssex varchar(2) not null, 
--     sbirthday datetime,
--     class varchar(5)
-- );
-- 
-- create table courses (
--     cno varchar(5) not null, 
--     cname varchar(10) not null, 
--     tno varchar(10) not null
-- );
-- create table scores (
--     sno varchar(3) not null, 
--     cno varchar(5) not null, 
--     degree int not null
-- );
-- create table teachers (
--     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 students(sno,sname,ssex,sbirthday, class) values (108 ,'曾华' ,'男' ,'1977-09-01',95033);
-- insert into students(sno,sname,ssex,sbirthday, class) values (105 ,'匡明' ,'男' ,'1975-10-02',95031);
-- insert into students(sno,sname,ssex,sbirthday, class) values (107 ,'王丽' ,'女' ,'1976-01-23',95033);
-- insert into students(sno,sname,ssex,sbirthday, class) values (101 ,'李军' ,'男' ,'1976-02-20',95033);
-- insert into students(sno,sname,ssex,sbirthday, class) values (109 ,'王芳' ,'女' ,'1975-02-10',95031);
-- insert into students(sno,sname,ssex,sbirthday, class) values (103 ,'陆君' ,'男' ,'1974-06-03',95031);

-- insert into  courses(cno,cname,tno) values('3-105' ,'计算机导论',825); 
-- insert into  courses(cno,cname,tno) values('3-245' ,'操作系统' ,804); 
-- insert into  courses(cno,cname,tno) values('6-166' ,'数据电路' ,856); 
-- insert into  courses(cno,cname,tno) values('9-888' ,'高等数学' ,100);
-- 
-- insert into  scores(sno,cno,degree) values(103,'3-245',86); 
-- insert into  scores(sno,cno,degree) values(105,'3-245',75); 
-- insert into  scores(sno,cno,degree) values(109,'3-245',68); 
-- insert into  scores(sno,cno,degree) values(103,'3-105',92); 
-- insert into  scores(sno,cno,degree) values(105,'3-105',88); 
-- insert into  scores(sno,cno,degree) values(109,'3-105',76); 
-- insert into  scores(sno,cno,degree) values(101,'3-105',64); 
-- insert into  scores(sno,cno,degree) values(107,'3-105',91); 
-- insert into  scores(sno,cno,degree) values(108,'3-105',78); 
-- insert into  scores(sno,cno,degree) values(101,'6-166',85); 
-- insert into  scores(sno,cno,degree) values(107,'6-106',79); 
-- insert into  scores(sno,cno,degree) values(108,'6-166',81);
-- 
-- insert into  teachers (tno,tname,tsex,tbirthday, prof,depart) values(804,'李诚','男','1958-12-02','副教授','计算机系');
-- insert into  teachers (tno,tname,tsex,tbirthday, prof,depart) values(856,'张旭','男','1969-03-12','讲师','电子工程系');
-- insert into  teachers (tno,tname,tsex,tbirthday, prof,depart) values(825,'王萍','女','1972-05-05','助教','计算机系');
-- insert into  teachers (tno,tname,tsex,tbirthday, prof,depart) values(831,'刘冰','女','1977-08-14','助教','电子工程系');

-- 简单难度:
-- 1、 查询 students 表中的所有记录的 sname、ssex 和 class 列。
select sname,ssex,class from students;

-- 2、 查询教师所有的单位即不重复的 depart 列。
select distinct depart from teachers;

-- 3、 查询 students 表的所有记录。
select * from students;

-- 4、 查询scores 表中成绩在 60 到 80 之间的所有记录。
select * from scores where degree between 60 and 80;

-- 5、 查询 scores  表中成绩为 85，86 或 88 的记录。
select * from scores where degree in (85,86,88);

-- 6、 查询students 表中“95031”班或性别为“女”的同学记录。
select * from students where class = "95031" or ssex = "女";

-- 7、 以 class 降序查询students  表的所有记录。
select * from students order by class desc;

-- 8、 以  cno 升序、  degree   降序查询 scores表的所有记录。
select * from scores order by cno,degree desc;

-- 9、 查询“95031”班的学生人数。
select count(*) as “95031”班的学生人数 from students where class = "95031";

-- 10、查询 scores 表中的最高分的学生学号和课程号。
select sno,cno from scores where degree = (select max(degree) from scores);

-- 11、查询‘3-105’号课程的平均分。
select avg(degree) from scores where cno = "3-105";

-- 12、查询 students  表中不姓“王”的同学记录。 
select * from students where sname not like '王%';

-- 13、查询 students 表中最大和最小的 sbirthday 日期值。
select min(sbirthday) as 最大,max(sbirthday) as 最小 from students;

-- 14、以班号和年龄从大到小的顺序查询 students 表中的全部记录。
select * from students order by class desc,sbirthday asc;


-- 中等难度:
-- 15、查询至少有 2名男生的班号。
select class from students group by class having count(*) >= 2;

-- 16、查询 scores 表中至少有 5 名学生选修的并以 3 开头的课程的平均分数。
select avg(degree) as 平均分数 from scores where cno like "3%" group by cno having count(*) >= 5;

-- 17、查询最低分大于70，最高分小于90 的 sno列。
select sno from scores group by sno having min(degree) > 70 and max(degree) < 90;

-- 18、查询所有学生的sname、cno 和degree列。
select sname,cno,degree from students,scores where students.sno = scores.sno;

-- 19、查询所有学生的 sno 、cname 和degree 列。
select sno,cname,degree from scores,courses where scores.cno = courses.cno;

-- 20、查询95033班所选课程的平均分。
select avg(degree) from students,scores where students.sno = scores.sno and class = "95033";

-- 21、查询95033班和 95031 班全体学生的记录。
select stu.*,sco.* from students as stu,scores as sco where stu.sno = sco.sno and stu.class in ("95033","95031");

-- 22、查询存在有 85 分以上成绩的课程cno.
select distinct cno from scores where degree > 85;

-- 23、查询“男”教师及其所上的课程。
select tname,cname from courses as c,teachers as t where c.tno = t.tno and t.tsex = '男';

-- 24、查询最高分同学的sno、cno和degree列。
select students.sno,cno,degree from students,scores where students.sno = scores.sno and degree = (select max(degree) from scores);

-- 25、查询和“李军”同性别的所有同学的sname.
select sname,ssex from students where ssex = (select ssex from students where sname = '李军') and sname != '李军';

-- 26、查询和“李军”同性别并同班的同学sname.
select sname,ssex,class from students where ssex = (select ssex from students where sname = '李军') and class = (select class from students where sname = '李军') and sname != '李军';

-- 27、查询所有任课教师的tname和depart. (没代课的不统计)
select tname,depart from teachers,courses where teachers.tno = courses.tno;

-- 28、查询所有未讲课的教师的tname和depart.
select distinct tname,depart from teachers,courses where teachers.tno not in (select courses.tno from courses);

-- 高等难度:
-- 29、查询“李诚“教师任课的学生成绩。
select sname,degree from students,scores where students.sno = scores.sno and cno = (select cno from courses,teachers where courses.tno = teachers.tno and tname = '李诚');

-- 30、查询选修某课程的同学人数多于 5 人的教师姓名。
select tname,cno from teachers,courses where teachers.tno = courses.tno and courses.cno = (select cno from scores group by cno having count(*) > 5);

-- 31、查询出“计算机系“教师所教课程的成绩表。
select sname,degree from students,scores where students.sno = scores.sno and cno in (select cno from courses,teachers where courses.tno = teachers.tno and depart = '计算机系');

-- 32、查询所有教师和同学的name、sex 和 birthday. (使用 union    )
select sname,ssex,sbirthday from students
union
select tname,tsex,tbirthday from teachers;

-- 33、查询所有“女”教师和“女”同学的 name 、sex 和 birthday. . (使用 union        )
select sname,ssex,sbirthday from students where ssex = '女'
union
select tname,tsex,tbirthday from teachers where tsex = '女';

-- 34、查询所有学生的 sname  、 cname   和  degree   列。
select sname,cname,degree from students,courses,scores where students.sno = scores.sno and scores.cno = courses.cno;

-- 35、查询 scores 中选学一门以上课程的同学中分数为非最高分成绩的记录。
select sno from scores where degree != ( select max(degree) from scores) group by sno having count(*) > 1;

四、SQL练习

-- 要求：
-- 1, 使用sql语句创建category表和goods表,其中categoryid为外键
create table category(
id int primary key auto_increment,
name varchar(5)
);
create table goods(
id int PRIMARY key auto_increment,
name varchar(4),
price float,
categoryid int,
CONSTRAINT `category_id` FOREIGN KEY (`categoryid`) REFERENCES `category` (`id`)
);

-- 插入数据
INSERT INTO category(name) values('水果'),('零食'),('饮料'),('牛奶'),('饼干'),('生活用品');
INSERT INTO goods(name, price, categoryid) values('苹果', 2, 1),('香蕉', 3, 1),('梨子', 4.5, 1),('口香糖', 5, 2),('泡泡堂', 3, 2),('可乐', 3, 3),('红牛', 6, 3),('娃哈哈', 3, 4),('特仑苏', 8, 4),('益力多',2, 4);

-- 2, 找出所有分类
select * from category;

-- 3, 找出价格>=5的商品名称有哪些
select name from goods where price >= 5;

-- 4, 找出分类名称为‘水果’的所有商品
select g.name from category as c,goods as g where c.id = g.categoryid and c.name = '水果';

-- 5, 找出泡泡堂商品所属的分类名称
select c.name from category as c , goods as g where c.id = g.categoryid and g.name = '泡泡堂';

-- 6, 找出每个分类的分类名称及分类的商品数量
select c.name,count(*) from category as c,goods as g where c.id = g.categoryid GROUP BY c.name;

-- 7, 将所有商品按价格降序排列
select * from goods order by price desc;

-- 8, 找出每个分类的名称及分类的商品数量，并按商品数量降序排列
select c.name,count(*) from category as c,goods as g where c.id = g.categoryid GROUP BY c.name order by count(*) desc;

-- 9, 找出比'娃哈哈'价格低的商品
select * from goods where price < (select price from goods where name = '娃哈哈');

-- 10, 找出比平均价格低的商品
select * from goods where price < (select avg(price) from goods);

-- 11, 列出分类名称和这些分类的商品信息，同时也列出那些没有商品的分类
select c.name as 分类,g.name as 商品 from category as c left join goods as g on c.id = g.categoryid;