MYSQL练习06

#创建数据库db_mydatabase2
CREATE DATABASE IF NOT EXISTS `db_mydatabase2`;

#使用数据库db_mydatabase2
USE `db_mydatabase2`;

#创建表tb_dept
CREATE TABLE IF NOT EXISTS `tb_dept`(
`dno` INT PRIMARY KEY AUTO_INCREMENT,
`dname` VARCHAR(50) NOT NULL,
`dlocal` VARCHAR(100)
);

#创建表tb_employee
CREATE TABLE IF NOT EXISTS `tb_employee`(
`eno` INT PRIMARY KEY AUTO_INCREMENT,
`ename` VARCHAR(50) NOT NULL,
`egender` CHAR(2),
`dno` INT NOT NULL,
`ejob` VARCHAR(50) NOT NULL,
`esalary` INT NOT NULL,
`hiredate` DATE NOT NULL 
);

#设置tb_employee自增长起始值和设置外键
ALTER TABLE tb_employee AUTO_INCREMENT=1001;
ALTER TABLE tb_employee ADD CONSTRAINT fk_dept_dno FOREIGN KEY tb_employee(dno) REFERENCES tb_dept(dno);

#向tb_dept表中插入数据
INSERT INTO tb_dept(`dno`,`dname`,`dlocal`)VALUES
(10,'accounting','上海'),
(20,'research','北京'),
(30,'sales','深圳'),
(40,'operations','福建'); 

#向tb_employee表中插入数据
INSERT INTO tb_employee(`eno`,`ename`,`egender`,`dno`,`ejob`,`esalary`,`hiredate`)VALUES
(1001,'smith','m',20,'elerk',800,'2015-11-12'),
(1002,'allen','f',30,'salesman',1600,'2003-05-12'),
(1003,'ward','m',30,'salesman',1250,'2003-05-12'),
(1004,'jones','m',20,'manager',2975,'1998-05-18'),
(1005,'martin','m',30,'salesman',1250,'2001-06-12'),
(1006,'blake','f',30,'manager',2850,'1997-02-15'),
(1007,'clark','m',10,'manager',2450,'2002-09-12'),
(1008,'scott','m',20,'analyst',3000,'2003-05-12'),
(1009,'king','f',10,'presider',5000,'1995-01-01'),
(1010,'turner','f',30,'salesman',1500,'1997-10-12'),
(1011,'adams','m',20,'clerk',1100,'1999-10-05'),
(1012,'james','f',30,'clerk',950,'2008-06-15');

#1. 查询所有记录的e_no、e_name和e_salary字段值
SELECT eno,ename,esalary FROM tb_employee;

#2. 查询dept_no等于10和20的所有记录
SELECT * FROM tb_dept WHERE dno IN (10,20);

#3. 查询工资范围在800~2500之间的员工信息
SELECT * FROM tb_employee WHERE esalary BETWEEN 800 AND 2500;

#4. 查询部门编号为20的部门中的员工信息
SELECT * FROM tb_employee WHERE dno=20;

#5. 查询每个部门最高工资的员工信息
SELECT * FROM tb_employee WHERE esalary in (SELECT MAX(esalary) FROM tb_employee GROUP BY dno);

#6. 查询员工BLAKE所在部门和部门所在地
SELECT dname,dlocal
FROM tb_employee e
JOIN tb_dept d ON e.dno=d.dno
WHERE e.ename='BLAKE';

#7. 查询所有员工的部门和部门信息
SELECT ename, d.*
FROM tb_employee e
JOIN tb_dept d ON e.dno=d.dno;

#8. 计算每个部门各有多少名员工
SELECT dno, COUNT(*) '人数'
FROM tb_employee
GROUP BY dno;

#9. 计算不同类型职工的总工资数
SELECT ejob,SUM(esalary) '总工资数'
FROM tb_employee
GROUP BY ejob;

#10. 计算不同部门的平均工资
SELECT dno,AVG(esalary) '平均工资'
FROM tb_employee
GROUP BY dno;

#11. 查询工资低于1500的员工信息
SELECT * FROM tb_employee WHERE esalary<1500;

#12. 将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列
SELECT *
FROM tb_employee
ORDER BY dno DESC,esalary DESC;

#13. 查询员工姓名以字母‘A’或‘S’开头的员工的信息
SELECT *
FROM tb_employee
WHERE ename LIKE 'A%' OR ename LIKE 'S%';

#14. 查询到目前为止工龄大于等于18年的员工信息
SELECT * FROM tb_employee WHERE hiredate<'2005-06-10';

#16. 使用LIMIT查询从第3条记录开始到第6条记录的结果
SELECT * FROM tb_employee LIMIT 3,3;

#17. 查询销售人员（SALSEMAN）的最低工资
SELECT MIN(esalary)  '最低工资' 
FROM tb_employee 
WHERE ejob = 'SALESMAN';

#18. 查询名字以字母N或者S结尾的记录
SELECT * 
FROM tb_employee
WHERE ename LIKE '%N' OR ename LIKE '%S';

#19. 查询在BeiJing工作的员工的姓名和职务
SELECT ename,ejob
FROM tb_dept d 
INNER JOIN tb_employee e ON e.dno=d.dno 
where d.dlocal='北京';

#20. 使用左连接方式查询employee和dept表
SELECT e.*,d.* 
FROM tb_dept d 
LEFT JOIN tb_employee e ON e.dno=d.dno;

#21. 查询所有2001~2005年入职的员工的信息，查询部门编号为20和30的员工信息并使用UNION合并 两个查询结果
SELECT * 
FROM tb_employee 
WHERE hiredate BETWEEN '2001-01-01' AND '2005-12-31'
UNION
SELECT * 
FROM tb_employee 
WHERE dno IN (20,30);

#22. 使用LIKE查询员工姓名中包含字母a的记录
SELECT * 
FROM tb_employee 
WHERE ename LIKE '%a%';

#23. 查询员工姓名中包含T、C或者M这3个字母中任意1个的记录
SELECT * 
FROM tb_employee 
WHERE ename LIKE '%t%' OR '%c%' OR '%m%';