MySQL基本基础练习

1、学生选课信息查询

teacher 教师表

student 学生表

cource 课程表

studentcource 选课表 学生和课程的关系表

准备工作：

CREATE TABLE teacher (

id int(11) NOT NULL primary key auto_increment,

name varchar(20) not null unique

);

CREATE TABLE student (

id int(11) NOT NULL primary key auto_increment,

name varchar(20) NOT NULL unique,

city varchar(40) NOT NULL,

age int

) ;

CREATE TABLE course(

id int(11) NOT NULL primary key auto_increment,

name varchar(20) NOT NULL unique,

teacher_id int(11) NOT NULL,

FOREIGN KEY (teacher_id) REFERENCES teacher (id)

);

CREATE TABLE studentcourse (

student_id int NOT NULL,

course_id int NOT NULL,

score double NOT NULL,

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,'小魏','上海',18);

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 studentcourse values(1,1,80);

insert into studentcourse values(1,2,90);

insert into studentcourse values(1,3,85);

insert into studentcourse values(1,4,78);

insert into studentcourse values(2,2,53);

insert into studentcourse values(2,3,77);

insert into studentcourse values(2,5,80);

insert into studentcourse values(3,1,71);

insert into studentcourse values(3,2,70);

insert into studentcourse values(3,4,80);

insert into studentcourse values(3,5,65);

insert into studentcourse values(3,6,75);

insert into studentcourse values(4,2,90);

insert into studentcourse values(4,3,80);

insert into studentcourse values(4,4,70);

insert into studentcourse values(4,6,95);

insert into studentcourse values(5,1,60);

insert into studentcourse values(5,2,70);

insert into studentcourse values(5,5,80);

insert into studentcourse values(5,6,69);

insert into studentcourse values(6,1,76);

insert into studentcourse values(6,2,88);

insert into studentcourse values(6,3,87);

insert into studentcourse values(7,4,80);

insert into studentcourse values(8,2,71);

insert into studentcourse values(8,3,58);

insert into studentcourse values(8,5,68);

insert into studentcourse values(9,2,88);

insert into studentcourse values(10,1,77);

insert into studentcourse values(10,2,76);

insert into studentcourse values(10,3,80);

insert into studentcourse values(10,4,85);

insert into studentcourse values(10,5,83);

根据上述的4张表练习：

-- 1、查询平均成绩大于70分的同学的学号和平均成绩

SELECT s.student_id,ROUND(avg(s.score),2) FROM studentcourse s GROUP BY s.student_id HAVING avg(s.score) > 70;

-- 2、查询所有同学的学号、姓名、选课数、总成绩

SELECT s.id '学号', s.`name` '姓名', COUNT(t.course_id) '选课数',ROUND(SUM(t.score),2) '总成绩' FROM student s LEFT JOIN studentcourse t ON s.id = t.student_id GROUP BY s.id;

-- 3、查询学过赵云老师所教课的同学的学号、姓名

SELECT s.id, s.`name` FROM student s LEFT JOIN studentcourse t ON s.id = t.student_id LEFT JOIN course c ON t.course_id = c.id LEFT JOIN teacher e ON c.teacher_id = e.id WHERE e.`name` = '赵云' GROUP BY s.id;

-- 4、查询没学过关羽老师课的同学的学号、姓名

SELECT * FROM student WHERE id NOT in (SELECT student_id FROM studentcourse WHERE course_id in (SELECT c.id FROM course c LEFT JOIN teacher t ON c.teacher_id = t.id WHERE t.`name` = '关羽') GROUP BY student_id);

-- 5、查询没有学三门课以上的同学的学号、姓名

SELECT s.id, s.`name` FROM student s LEFT JOIN studentcourse st ON s.id = st.student_id GROUP BY st.student_id HAVING COUNT(st.course_id)<3

-- 6、查询各科成绩最高和最低的分

SELECT c.name, MAX(score),MIN(score) FROM studentcourse s LEFT JOIN course c ON s.course_id = c.id GROUP BY s.course_id;

-- 7、查询学生信息和平均成绩

SELECT s.*, ROUND(AVG(st.score),2) FROM student s LEFT JOIN studentcourse st ON s.id = st.student_id GROUP BY st.student_id

-- 8、查询上海和北京学生数量

SELECT city, COUNT(*) FROM student GROUP BY city HAVING city in ('上海','北京');

-- 9、查询不及格的学生信息和课程信息

SELECT s.*, c.* FROM studentcourse st LEFT JOIN student s ON st.student_id = s.id LEFT JOIN course c ON st.course_id = c.id WHERE st.score < 60

-- 10、统计每门课程的学生选修人数（超过四人的进行统计）

SELECT c.`name`,COUNT(st.student_id) FROM studentcourse st LEFT JOIN course c ON st.course_id = c.id GROUP BY st.course_id HAVING COUNT(st.student_id) > 4

2、部门员工查询练习

准备工作：

-- 部门表

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 DISTINCT deptno FROM emp;

-- 执行

SELECT * FROM dept WHERE deptno in(SELECT DISTINCT deptno FROM emp);

-- 2．列出薪金比"刘一"多的所有员工。

-- 刘一薪金

SELECT sal FROM emp WHERE ename='刘一';

-- 执行

SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='刘一');

-- 3．列出所有员工的姓名及其直接上级的姓名。

-- 查出员工的姓名和年龄

SELECT ename,mgr FROM emp ;

-- 执行

SELECT temp.ename 员工名,emp.ename 领导 from emp,(SELECT ename,mgr FROM emp) as temp WHERE temp.mgr = emp.empno;

-- 4．列出受雇日期早于其直接上级的所有员工。

SELECT temp.ename 员工名,temp.hiredate 员工如期, emp.ename 领导,emp.hiredate 领导日期 from emp,(SELECT ename,mgr,hiredate FROM emp) as temp WHERE temp.mgr = emp.empno AND temp.hiredate < emp.hiredate;

-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

-- 使用左外链接显示左表中的全部信息

SELECT * FROM dept LEFT OUTER JOIN emp ON dept.deptno = emp.deptno;

-- 6．列出所有job为“职员”的姓名及其部门名称。

SELECT emp.ename,dept.dname FROM dept,emp WHERE job = '职员' AND emp.deptno = dept.deptno;

-- 7．列出最低薪金大于1500的各种工作。

SELECT job,sal from emp GROUP BY job HAVING sal > 1500;

-- 8．列出在部门 "销售部" 工作的员工的姓名，假定不知道销售部的部门编号。

-- 销售部id

SELECT deptno FROM dept WHERE dname = '销售部';

SELECT ename FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE dname = '销售部');

-- 9．列出薪金高于公司平均薪金的所有员工。

-- 平均薪金

SELECT avg(sal) FROM emp;

SELECT * FROM emp WHERE sal > (SELECT avg(sal) FROM emp);

-- 10．列出与"周八"从事相同工作的所有员工。

SELECT job FROM emp WHERE ename = '周八';

SELECT * FROM emp WHERE job = (SELECT job FROM emp WHERE ename = '周八');

-- 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

-- 部门30最高薪金

SELECT MAX(sal) FROM emp WHERE deptno = 30;

SELECT ename,sal FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);

-- 13．列出在每个部门工作的员工数量、平均工资。

SELECT count(*),avg(sal) FROM emp GROUP BY deptno

-- 14．列出所有员工的姓名、部门名称和工资。

SELECT emp.ename,emp.sal,dept.dname FROM dept,emp WHERE dept.deptno = emp.deptno;

-- 15．列出所有部门的详细信息和部门人数。

-- 部门人数

SELECT COUNT(*) FROM emp GROUP BY deptno;

-- 执行

SELECT * FROM dept,(SELECT deptno,COUNT(*) 员工总人数 FROM emp GROUP BY deptno) AS temp WHERE dept.deptno = temp.deptno;

-- 16．列出各种工作的最低工资。

SELECT MIN(sal) FROM emp GROUP BY job;

-- 17．列出各个部门的 经理 的最低薪金。

-- 部门最低薪金

SELECT job,MIN(sal),deptno FROM emp where job = '经理' GROUP BY deptno;

-- 18．列出所有员工的年工资,按年薪从低到高排序。

SELECT ename,sal*12 FROM emp ORDER BY sal DESC

-- 19.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。

SELECT empno,ename,sal FROM emp WHERE sal >= 3000;

-- 20.查询出所有薪水在'陈二'之上的所有人员信息。

SELECT sal FROM emp WHERE ename = '陈二';

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = '陈二');

-- 21.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号 员工名字 薪水

SELECT empno 员工编号,ename 员工名字,sal 薪水 from emp WHERE deptno = 20 and sal > 2000 ;

-- 22.查询出emp表中所有的工作种类（无重复）

SELECT DISTINCT job 工作种类 FROM emp

-- 23.查询出所有奖金（comm）字段不为空的人员的所有信息。

SELECT * FROM emp WHERE comm is not null;

-- 24.查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）

SELECT * FROM emp WHERE sal BETWEEN 800 AND 2500;

-- 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 sal > 1000 AND ename LIKE '%张%'

-- 27.查询出名字第三个汉字是“忌”的所有员工信息。

SELECT * FROM emp WHERE ename LIKE '__忌';

-- 28.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。

SELECT * FROM emp ORDER BY sal ASC,hiredate DESC;

-- 29.将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序。 order by convert(name using gbk) asc;

SELECT * FROM emp order by convert(ename using gbk) asc,sal DESC;

-- 30.查询出最早工作的那个人的名字、入职时间和薪水。

SELECT MIN(hiredate) FROM emp;

SELECT ename 名字,hiredate 入职时间,sal 薪水 FROM emp WHERE hiredate = (SELECT MIN(hiredate) FROM emp);

-- 31.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.

SELECT ename,sal,(case when comm is null then 100 else comm end ) FROM emp;

-- 32.显示出薪水最高人的职位。

SELECT job FROM emp WHERE sal = (SELECT MAX(sal) FROM emp );

-- 33.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。

SELECT deptno,MAX(sal),MIN(sal) FROM emp GROUP BY deptno HAVING deptno != 10;

-- 34.删除10号部门薪水最高的员工。

-- 10号部门薪水最高的员工

SELECT empno,MAX(sal) FROM emp WHERE deptno = 10;

-- 删除

delete from emp where empno = (select empno from (select empno from emp where deptno = 10 ORDER BY sal desc limit 1) t);

-- 35.将薪水最高的员工的薪水降30%。

SELECT MAX(sal) FROM emp

UPDATE emp SET sal = (SELECT sal FROM (SELECT MAX(sal) FROM emp) tt)*(1-0.3) WHERE empno = (SELECT empno FROM (SELECT empno FROM emp ORDER BY sal DESC LIMIT 1) t);

-- 36.查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)

-- 语法：case when ... then ... when ... then ... else ... end

SELECT ename 员工姓名, sal 工资,(CASE WHEN sal > 3000 THEN '3级' WHEN sal <= 2000 THEN '1级' ELSE '2级'END) 工资级别 FROM emp WHERE 1 = 1;

3、在sql中获取当前时间

INSERT INTO tb_user (id,user_name,PASSWORD,created,updated)VALUES(NULL,'zs',111111,NOW(),NOW())

4、添加随机uuid

insert into table1"(id,name,age) value(uuid(),''张三“,18)