CREATE TABLE student(id INT,
name VARCHAR(20),
gender VARCHAR(20),
chinese INT,
english INT,
math INT
);
INSERT INTO student(id, name, gender, chinese, english, math) VALUES (1,'张明','男',89,78,90);
INSERT INTO student(id, name, gender, chinese, english, math) VALUES (2,'李进','男',67,53,95);
INSERT INTO student(id, name, gender, chinese, english, math) VALUES (3,'王五','女',87,78,77);
INSERT INTO student(id, name, gender, chinese, english, math) VALUES (4,'李一','女',88,98,92);
INSERT INTO student(id, name, gender, chinese, english, math) VALUES (5,'李财','男',82,84,67);
INSERT INTO student(id, name, gender, chinese, english, math) VALUES (6,'张宝','男',55,85,45);
INSERT INTO student(id, name, gender, chinese, english, math) VALUES (7,'黄蓉','女',75,65,30);
INSERT INTO student(id, name, gender, chinese, english, math) VALUES (7,'黄蓉','女',75,65,30);
练习题一:
查询表中所有学生的信息
查询表中所有学生的姓名和对应的英语成绩
过滤表中重复数据
统计每个学生的总分
在所有学生总分数上加上10分特长分
使用别名表示学生分数
查询英语成绩大于90分的同学
查询总分大于200分的所有同学
查询英语分数在80-90之间的同学
查询英语分数不在80-90之间的同学
查询数学分数为89,90,91的同学
查询所有姓李的学生的英语成绩
查询数学分80并且语文分80的同学
查询英语80或者总分200的同学
对数学成绩降序排序后输出
对总分排序后输出,然后再按从高到低的顺序输出
对姓李的学生总成绩排序输出
查询男生和女生分别由多少人,并将人数降序排序输出
答案一:
-- 查询表中所有学生的信息
SELECT * FROM student;-- 查询表中所有学生的姓名和对应的英语成绩
SELECT name,english FROM student;-- 过滤表中重复数据
SELECT DISTINCT * FROM student;-- 统计每个学生的总分
SELECT name,chinese+english+math as total_score FROM student;-- 在所有学生总分数上加上10分特长分
SELECT name,chinese+english+math+10as total_score FROM student;-- 使用别名表示学生分数
SELECT name,chinese '语文',english '英语',math '数学' FROM student;-- 查询英语成绩大于90分的同学
SELECT * FROM student WHERE english >90;-- 查询总分大于200分的所有同学
SELECT * FROM student WHERE (chinese+english+math)>200;
SELECT name,chinese+english+math+10as total_score FROM student WHERE (chinese+english+math)>200;-- 查询英语分数在80-90之间的同学
SELECT * FROM student WHERE english >=80 AND english<90;-- 查询英语分数不在80-90之间的同学
SELECT * FROM student WHERE NOT (english >=80 AND english<90);
SELECT * FROM student WHERE english not BETWEEN 80and90;
SELECT * FROM student WHERE english>=80and english<=90;-- 查询数学分数为89,90,91的同学
SELECT * FROM student WHERE math=89or math=90or math =91;
SELECT * FROM student WHERE math in(89,90,91);-- 查询所有姓李的学生的英语成绩
SELECT * FROM student WHERE name LIKE '李%';-- 查询数学分80并且语文分80的同学
SELECT name,math,chinese FROM student WHERE math =80 AND chinese=80;-- 查询英语80或者总分200的同学
SELECT * FROM student WHERE english =80and(chinese+math+english)=200;-- 对数学成绩降序排序后输出
SELECT * FROM student ORDER BY math DESC;-- 对总分排序后输出,然后再按从高到低的顺序输出
SELECT * FROM student ORDER BY (chinese+math+english) DESC;-- 对姓李的学生总成绩排序输出
SELECT * FROM student WHERE name like'李%' ORDER BY (chinese+math+english) DESC;-- 查询男生和女生分别由多少人,并将人数降序排序输出
SELECT gender,COUNT(*) FROM student GROUP BY gender ORDER BY COUNT(*) DESC;
练习题二用的表以及数据:
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate date,
sal INT,
comm INT,
deptno INT
);
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800, NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975, NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850, NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450, NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'7987-04-19',3000, NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT', NULL,'1981-11-17',5000, NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'1987-05-23',1100, NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950, NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000, NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300, NULL,10);
练习题二:
按员工编号升序排列不在10号部门工作的员工信息
查询姓名第二个字母不是“A”且薪水大于1000元的员工信息,按年薪降序排列
求每个部门的平均薪水
求各个部门的最高薪水
求每个部门每个岗位的最高薪水
求平均薪水大于2000的部门编号
将部门平均薪水大于1500的部门编号列出来,按部门平均薪水降序排列
选择公司中有奖金的员工姓名,工资
查询员工最高工资和最低工资的差距
答案二
-- 按员工编号升序排列不在10号部门工作的员工信息
SELECT * FROM emp WHERE deptno !=10 ORDER BY empno ASC;-- 查询姓名第二个字母不是“A”且薪水大于1000元的员工信息,按年薪降序排列
SELECT * FROM emp WHERE ename NOT LIKE'_A%' AND sal >1000 ORDER BY (12*sal+IFNULL(comm,0)) DESC;-- 注:ifnull(comm,0)如果comm的值为null,则当做0,否则还是原来的值。
-- 求每个部门的平均薪水
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;-- 求各个部门的最高薪水
SELECT deptno,MAX(sal) FROM emp GROUP BY deptno;-- 求每个部门每个岗位的最高薪水
SELECT deptno,job,MAX(sal) FROM emp GROUP BY deptno,job;-- 求平均薪水大于2000的部门编号
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>2000;-- 将部门平均薪水大于1500的部门编号列出来,按部门平均薪水降序排列
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>1500 ORDER BY AVG(sal) DESC;-- 选择公司中有奖金的员工姓名,工资
SELECT * FROM emp WHERE comm is NOT NULL;-- 查询员工最高工资和最低工资的差距
SELECT MAX(sal)-MIN(sal) FROM emp;