mysql_练习题

注：以下内容可直接复制到数据库文件使用
– 一、创建表stu
CREATE TABLE stu (
sid CHAR(6),
sname VARCHAR(50),
age INT,
gender VARCHAR(50)
);
#添加数据
INSERT INTO stu VALUES(‘S_1001’, ‘liuYi’, 35, ‘male’);
INSERT INTO stu VALUES(‘S_1002’, ‘chenEr’, 15, ‘female’);
INSERT INTO stu VALUES(‘S_1003’, ‘zhangSan’, 95, ‘male’);
INSERT INTO stu VALUES(‘S_1004’, ‘liSi’, 65, ‘female’);
INSERT INTO stu VALUES(‘S_1005’, ‘wangWu’, 55, ‘male’);
INSERT INTO stu VALUES(‘S_1006’, ‘zhaoLiu’, 75, ‘female’);
INSERT INTO stu VALUES(‘S_1007’, ‘sunQi’, 25, ‘male’);
INSERT INTO stu VALUES(‘S_1008’, ‘zhouBa’, 45, ‘female’);
INSERT INTO stu VALUES(‘S_1009’, ‘wuJiu’, 85, ‘male’);
INSERT INTO stu VALUES(‘S_1010’, ‘zhengShi’, 5, ‘female’);
INSERT INTO stu VALUES(‘S_1011’, ‘xxx’, NULL, NULL);

– 1、查询学号不是S_1001，S_1002，S_1003的记录
select * from stu where sid not in(‘S_1001’, ‘S_1002’, ‘S_1003’);
– 在已经排序的前提下 纯属联系 不适用（也可以自己排序）
select count(1) from stu;
select * from stu limit 3,11;

– 2、查询年龄为null的记录
select * from stu where age is null;

– 3、查询年龄在20到40之间的学生记录
select * from stu where age between 20 and 40;

– 4、查询性别非男的学生记录
select * from stu where gender not in(‘male’); – 此语句没有性别为空的记录
select * from stu where gender != ‘male’; – 此语句没有性别为空的记录
select * from stu where gender != ‘male’ or gender is null;

– 5、查询姓名不为null的学生记录
select * from stu where sname != ‘null’;
select * from stu where sname <> ‘null’;
select * from stu where sname is not null;

– 6、查询姓名由5个字母构成的学生记录
select * from stu where sname like ‘_____’;

– 7、查询姓名由5个字母构成，并且第5个字母为“i”的学生记录
select * from stu where sname like ‘____i’;
select * from stu where sname like ‘_____’ and sname like ‘%i’;

– 8、查询姓名以“z”开头的学生记录
select * from stu where sname like ‘z%’;

– 9、查询姓名中第2个字母为“i”的学生记录
select * from stu where sname like ‘_i%’;

– 10、查询姓名中包含“a”字母的学生记录
select * from stu where sname like ‘%a%’;

– 二、创建雇员表
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm decimal(7,2),
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,‘1987-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);

创建部门表

CREATE TABLE dept(
deptno INT,
dname varchar(14),
loc varchar(13)
);

添加数据

INSERT INTO dept values(10, ‘ACCOUNTING’, ‘NEW YORK’);
INSERT INTO dept values(20, ‘RESEARCH’, ‘DALLAS’);
INSERT INTO dept values(30, ‘SALES’, ‘CHICAGO’);
INSERT INTO dept values(40, ‘OPERATIONS’, ‘BOSTON’);

– 1、根据员工表查询有几个部门
select deptno as ‘部门编号’ from emp group by deptno;

– 2、查看雇员的月薪与佣金之和
select sum(sal+IFNULL(comm, 0)) as ‘总工资’ from emp; – 公司总工资
select empno as ‘编号’, ename as ‘姓名’, sum(sal+IFNULL(comm, 0)) as ‘雇员工资’ from emp group by empno; – 每个人总工资

– 3、给列名添加别名
select empno as ‘员工编号’, ename as ‘员工姓名’, job as ‘工作’, mgr as ‘上级编号’, hiredate as ‘受雇日期’, sal as ‘薪金’, comm as ‘佣金’, deptno as ‘部门编号’ from emp;

– 6、查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
select * from emp order by sal desc, empno asc;

– 7、查询emp表中记录数
select count(1) from emp;
select count(empno) from emp;
select count(*) from emp;

– 8、查询emp表中有佣金的人数
select count(1) from emp where comm != 0 and comm is not null;

– 9、查询emp表中月薪大于2500的人数
select count(1) as ‘数量’ from emp where sal > 2500;
– 10、统计月薪与佣金之和大于2500元的人数
select count(*) as ‘数量’ from emp group by empno having sum(sal+IFNULL(comm,0)) > 2500; – 6个1
select count(1) as ‘数量’ from emp having sum(sal+IFNULL(comm, 0)); – 14
select count(1) as ‘数量’ from emp where sal+IFNULL(comm, 0) > 2500; – 6

– 11、查询有佣金的人数，以及有领导的人数
select count(comm) as ‘有佣金人数’, count(mgr) as ‘有领导人数’ from emp;

– 12、查询所有雇员月薪和
select sum(sal) as ‘雇员月薪和’ from emp;

– 13、查询所有雇员月薪和，以及所有雇员佣金和
select sum(sal) as ‘雇员月薪和’,sum(IFNULL(comm, 0)) as ‘雇员佣金和’ from emp;

– 14、查询所有雇员月薪+佣金和
select sum(sal+IFNULL(comm, 0)) as ‘雇员月薪+佣金和’ from emp;

– 15、统计所有员工平均工资
select avg(sal+IFNULL(comm, 0)) as ‘雇员平均工资’ from emp;

– 16、查询最高工资和最低工资
select max(sal+IFNULL(comm, 0)) as ‘最高工资’, min(sal+IFNULL(comm, 0)) as ‘最低工资’ from emp;

– 17、查询每个部门的部门编号和每个部门的工资和
select deptno as ‘部门编号’, sum(sal+IFNULL(comm, 0)) as ‘部门的工资和’ from emp group by deptno;

– 18、查询每个部门的部门编号以及每个部门的人数
select deptno as ‘部门编号’, count(1) as ‘部门人数’ from emp group by deptno;

– 19、查询每个部门的部门编号以及每个部门工资大于1500的人数
select deptno as ‘部门编号’, count(1) as ‘工资大于1500的人数’ from emp where sal+IFNULL(comm, 0) > 1500 group by deptno;

– 20、查询工资总和大于9000的部门编号以及工资和
select deptno as ‘部门编号’, sum(sal+IFNULL(comm, 0)) as ‘部门人数’ from emp group by deptno having sum(sal+IFNULL(comm, 0)) > 9000;

– 21、查询5行记录，起始行从0开始
select * from emp limit 5;
select * from emp limit 0,5;

– 22、查询10行记录，起始行从3开始
select * from emp limit 3,10;