MySQL数据库查询操作 练习

create database mydb1;
use mydb1;
create table emp(
empno int, – 工号
ename varchar(10), – 员工姓名
job varchar(10), – 部门
mgr int, – 部门编号
hiredate varchar(10),
salary double, – 薪水
comm double,
deptno int
)charset utf8;
insert into emp values(1001,‘甘宁’,‘文员’,1013,‘2000-12-17’,8000.00,0.0,20);

insert into emp values(1002,‘黛绮丝’,‘销售员’,1006,‘2001-02-20’,16000.00,3000.00,30);

insert into emp values(1003,‘殷天正’,‘销售员’,1006,‘2001-02-22’,12500.00,5000.00,30);

insert into emp values(1004,‘刘备’,‘经理’,1009,‘2001-04-02’,29750.00,0.0,20);

insert into emp values(1005,‘谢逊’,‘销售员’,1006,‘2001-09-28’,12500.00,14000.00,30);

insert into emp values(1006,‘关羽’,‘经理’,1009,‘2001-05-01’,28500.00,0.0,30);

insert into emp values(1007,‘张飞’,‘经理’,1009,‘2001-09-01’,24500.00,0.0,10);

insert into emp values(1008,‘诸葛亮’,‘分析师’,1004,‘2007-04-19’,30000.00,0.0,20);

insert into emp values(1009,‘曽阿牛’,‘董事长’,0,‘2001-11-17’,50000.00,0.0,10);

insert into emp values(1010,‘韦一笑’,‘销售员’,1006,‘2001-09-08’,15000.00,0.0,30);

insert into emp values(1011,‘周泰’,‘文员’,1008,‘2007-05-23’,11000.00,0.0,20);

insert into emp values(1012,‘程曽’,‘文员’,1006,‘2001-12-03’,9500.00,0.0,30);

insert into emp values(1013,‘庞统’,‘分析师’,1004,‘2001-12-03’,30000.00,0.0,20);

insert into emp values(1014,‘黄盖’,‘文员’,1007,‘2002-01-23’,13000.00,0.0,10);

insert into emp values(1015,‘张三’,‘保洁员’,1001,‘2013-05-01’,80000.00,50000.00,50);

– 1.查询出部门编号为30的所有员工:
select ename,deptno from emp where deptno=30;

– 2.查询所有销售员的姓名、编号和部门编号:
select ename,empno,deptno from emp where job =‘销售员’;

– 3.找出奖金高于工资的员工。
select ename,comm,salary from emp where comm>salary;

– 4.找出奖金高于工资60%的员工:
select ename,comm,salary from emp where comm>salary*0.6;

– 5. 找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料
select ename,job from emp where deptno=10 && job=‘经理’;
select * from emp where deptno=20&&job=‘销售员’;

/6.找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。/
select * from emp where (deptno=10 and job=‘经理’) or (deptno=20 and job=‘销售员’ ) or (job!=‘经理’ and job!=‘销售员’ and salary>=20000);

– 7.找出无奖金或奖金低于1000的员工:
select ename from emp where comm=0 or comm<1000;

– 8. 查询名字由三个字组成的员工。
select ename from emp where char_length(ename)=3;
select ename from emp where ename like ‘___’;

– 9.查询2000年入职的员工。
select ename from emp where hiredate like’2000%’;

– 10.查询所有员工详细信息，用编号升序排序
select * from emp order by empno;

– 11. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序
select * from emp order by salary desc,hiredate asc;