本文共 7852 字，大约阅读时间需要 26 分钟。
安装MySQL数据库:mysql --version
创建mydb1数据库:create database mydb1;查看数据库:show databases;显示数据库创建语句:show create database mydb1;创建mydb2数据库，指定字符编码:create database mydb2 character set gb2312 collate gb2312_bin;创建mydb3数据库，指定字符编码并设置校验规则:create database mydb3 character utf8 collate utf8_bin;
删除部门表：drop table if exists dept;创建部门表：create table dept ( deptno int primary key, name varchar(14) comment '部门名称', loc varchar(13) comment '部门地址');插入部门数据：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');删除员工表：drop table if exists emp;创建员工表：create table emp ( empno int primary key comment '员工编号', ename varchar(10) comment '员工姓名', job varchar(9) comment '员工工作', mgr int comment '员工直属领导编号', hiredate date comment '入职时间', sal double comment '工资', comm double comment '奖金', deptno int references dept(deptno));插入员工数据：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-07-03', 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-07-13', 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, '1981-01-23', 1300, null, 10);创建工资等级表：drop table if exists salgrade;create table salgrade ( grade int comment '等级', losal double comment '最低工资', hisal double comment '最高工资');插入工资等级数据：insert into salgrade values (1, 700, 1200);insert into salgrade values (2, 1201, 1400);insert into salgrade values (3, 1401, 2000);insert into salgrade values (4, 2001, 3000);insert into salgrade values (5, 3001, 9999);
1. 查找部门30中员工的详细信息：select * from emp where deptno = 30;2. 找出从事CLERK工作的员工编号、姓名、部门号：select empno, ename, deptno from emp where job = 'CLERK';3. 检索奖金多于基本工资的员工信息：select * from emp where comm > sal;4. 检索奖金多于基本工资60%的员工信息：select * from emp where comm > sal * 0.6;5. 找出10部门的经理和20部门的职员的员工信息：select * from emp where deptno = 10 and job = 'MANAGER' or deptno = 20 and job = 'CLERK';6. 找出10部门的经理、20部门的职员或工资高于2000元的员工信息：select * from emp where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK') or (sal > 2000);7. 找出获得奖金的员工的工作：select * from emp where comm is not null;8. 找出奖金少于100或没有获得奖金的员工信息：select * from emp where comm < 100 or comm is null;9. 找出名字以A、B、S开始的员工信息：select * from emp where ename like 'A%' or ename like 'B%' or ename like 'S%';10. 找到名字长度为7个字符的员工信息：select * from emp where length(ename) = 7;11. 找出名字中不包含R字符的员工信息：select * from emp where ename not like '%R%';12. 查询所有姓李的员工英语成绩：select name, english from emp where name like '李%';13. 查询数学分数大于80并且语文分数大于80的同学：select * from emp where math > 80 and chinese > 80;14. 查询英语成绩大于80或总分大于200的员工信息：select * from emp where english > 80 or (chinese + math + english) > 200;
以下是对练习的详细解答：
查找部门30中员工的详细信息：
select * from emp where deptno = 30;
找出从事CLERK工作的员工编号、姓名、部门号：
select empno, ename, deptno from emp where job = 'CLERK';
检索奖金多于基本工资的员工信息：
select * from emp where comm > sal;
检索奖金多于基本工资60%的员工信息：
select * from emp where comm > sal * 0.6;
找出10部门的经理和20部门的职员的员工信息：
select * from emp where deptno = 10 and job = 'MANAGER' or deptno = 20 and job = 'CLERK';
找出10部门的经理、20部门的职员或工资高于2000元的员工信息：
select * from emp where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK') or (sal > 2000);
找出获得奖金的员工的工作：
select * from emp where comm is not null;
找出奖金少于100或没有获得奖金的员工信息：
select * from emp where comm < 100 or comm is null;
找出名字以A、B、S开始的员工信息：
select * from emp where ename like 'A%' or ename like 'B%' or ename like 'S%';
找出名字长度为7个字符的员工信息：
select * from emp where length(ename) = 7;
找出名字中不包含R字符的员工信息：
select * from emp where ename not like '%R%';
查询所有姓李的员工英语成绩：
select name, english from emp where name like '李%';
查询数学分数大于80并且语文分数大于80的员工信息：
select * from emp where math > 80 and chinese > 80;
查询英语成绩大于80或总分大于200的员工信息：
select * from emp where english > 80 or (chinese + math + english) > 200;
按数学成绩排序输出：
select * from emp order by math;
按总分排序输出，按从高到低的顺序：
select * from emp order by (chinese + math + english) desc;
查询姓名为'李一'的学生成绩：
select name, chinese, math, english from emp where name = '李一';
查询英语成绩大于90分的同学：
select name from emp where english > 90;
查询总分大于200分的所有同学：
select name from emp where (chinese + math + english) > 200;
查询英语分数在80－90之间的同学：
select name from emp where english >= 80 and english <= 90;
查询数学分数为89,90,91的同学：
select name from emp where math in (89, 90, 91);
查询所有姓李的学生英语成绩：
select name, english from emp where name like '李%';
查询数学分数大于80并且语文分数大于80的同学：
select name from emp where math > 80 and chinese > 80;
查询英语成绩大于80或者总分大于200的同学：
select name from emp where english > 80 or (chinese + math + english) > 200;
按总分排序后输出，按从高到低的顺序：
select name, (chinese + math + english) as Total from emp order by Total desc;
查询总分大于200的同学，并按总分排序：
select name, (chinese + math + english) as Total from emp where Total > 200 order by Total desc;
查询英语成绩大于80或总分大于200的同学：
select name from emp where english > 80 or (chinese + math + english) > 200;
按数学成绩排序后输出：
select * from emp order by math desc;
查询部门号、部门名、部门所在位置及其每个部门的员工总数：
select deptno, dname, loc, count(*) from emp ee, dept dd where ee.deptno = dd.deptno;
返回员工的姓名、所在部门名及其工资：
select ee.ename, dd.dname, ee.sal from emp ee, dept dd where ee.deptno = dd.deptno;
返回员工的详细信息：
select * from emp, dept, salgrade where dept.deptno = emp.deptno;
返回工资水平多于smith的员工信息：
select * from emp where sal > (select sal from emp where ename = 'smith');
返回与SCOTT从事相同工作的员工：
select * from emp where job = (select job from emp where ename = 'SCOTT') and ename != 'SCOTT';
返回工资高于30部门所有员工工资水平的员工信息：
select * from emp where sal > (select max(sal) from emp where deptno = 30);
返回工资为二等级的职员名字、部门所在地、二等级员工工资的最低工资和最高工资：
select ename, loc, losal, hisal from emp, salgrade where grade = 2 and sal between losal and hisal;
返回工资处于第四级别的员工姓名：
select ename from emp, salgrade where grade = 4 and sal between losal and hisal;
返回工资为二等级的职员名字、部门所在地、二等级员工工资的最低工资和最高工资：
select ename, loc, (select min(sal) from emp, salgrade where grade = 2 and sal between losal and hisal), (select max(sal) from emp, salgrade where grade = 2 and sal between losal and hisal) from emp, salgrade, dept where emp.deptno = dept.deptno and grade = 2;
返回工资为二等级的职员名字、部门所在地、二等级员工工资的最低工资和最高工资：
select ename, loc, (select min(sal) from emp, salgrade where grade = 2 and sal between losal and hisal), (select max(sal) from emp, salgrade where grade = 2 and sal between losal and hisal) from emp, dept where emp.deptno = dept.deptno and grade = 2;
返回工资为二等级的职员名字、部门所在地、二等级员工工资的最低工资和最高工资：
select ename, loc, (select min(sal) from emp, salgrade where grade = 2 and sal between losal and hisal), (select max(sal) from emp, salgrade where grade = 2 and sal between losal and hisal) from emp, dept where emp.deptno = dept.deptno and grade = 2;
返回工资高于30部门所有员工工资水平的员工信息：
select * from emp where sal > (select avg(sal) from emp where deptno = 30);
通过以上SQL语句，用户可以完成各种数据库操作和查询练习，包括创建、插入、更新、删除、查询等操作，同时也涉及多表查询和聚合函数的使用。
转载地址：http://cvdfk.baihongyu.com/