数据库基础练习

dept、emp结构以及表中的记录，如下表所示。

dept表结构

字段名	字段说明		数据类型	主键	外键	非空	唯一	自增
d_no	部门编号	int(11)		是	否	是	是	否
d_name	部门名称	varchar(50)		否	否	是	否	否

emp表结构

字段名	字段说明	数据类型	主键	外键	非空	唯一	自增
e_no	员工编号	int(11)	是	否	是	是	否
e_name	员工姓名	varchar(50)	否	否	是	否	否
e_gender	员工性别	varchar(20)	否	否	否	否	否
dept_no	部门编号	int(11)	否	是	是	否	否
e_job	职位	varchar(50)	否	否	是	否	否
e_salary	薪水	float	否	否	是	否	否
hireDate	入职日期	DATE	否	否	是	否	否

dept表中的记录

d_no	d_name	d_location
10	财务部门	ShangHai
20	研发部门	BeiJing
30	销售部门	ShenZhen
40	作业部门	FuJian

emp 表记录

e_no	e_name	e_gender	dept_no	e_salary	hireDate
1001	SMITH	男	20	8500	2005-11-12
1002	ALLEN	女	30	1600	2003-05-12
1003	WARD	女	30	1250	2003-05-12
1004	JONES	男	20	5000	1998-05-18
1005	MARTIN	男	30	1250	2001-06-12
1006	BLAKE	女	30	2850	1997-02-15
1007	CLARK	男	10	2450	2002-09-12
1008	SCOTT	男	20	7400	2003-05-12
1009	KING	女	10	5000	1995-01-01
1010	TRRNER	女	30	1500	1997-10-12
1011	ADAMS	男	20	4500	1999-10-05
1012	JAMES	女	30	950	2008-06-15

1.创建数据库dbtest

CREATE DATABASE dbtest;

2.使用数据库dbtest

USE dbtest;

3.根据描述创建表dept

CREATE TABLE dept

(

d_no INT(11) PRIMARY KEY,

d_name VARCHAR(50) NOT NULL

);

 

4.根据描述创建表emp

CREATE TABLE emp

(

e_no INT(11) PRIMARY KEY,

e_name VARCHAR(50) NOT NULL,

e_gender VARCHAR(20),

dept_no INT(11) NOT NULL REFERENCES dept(d_no),

e_job VARCHAR(50) NOT NULL,

e_salary FLOAT NOT NULL,

hireDate DATE NOT NULL

#CONSTRAINT emp­_fk FOREIGN KEY(dept_no) REFERENCES dept(d_no)

);

# ALTER TABLE emp ADD CONSTRAINT fk_dept_no FOREIGN KEY(dept_no) REFERENCES dept(d_no);

 

5.将emp表中的e_gender数据类型修改为char(2);

ALTER TABLE emp MODIFY e_gender char(2);

6.在dept表中新增字段d_location，数据类型为varchar(50)

ALTER TABLE dept ADD d_location VARCHAR(50);

7.删除emp表的e_job字段

ALTER TABLE emp DROP e_job;

8.批量添加dept表中的数据

INSERT INTO dept VALUES

(10, '财务部门', 'ShangHai'),

(20, '研发部门', 'BeiJing'),

(30, '销售部门', 'ShenZhen'),

(40, '作业部门', 'FuJian');

 

9.使用指定字段名方式，添加emp表中编号为1001-1006的数据

INSERT INTO emp(e_no,e_name,e_gender,dept_no,e_salary,hireDate) VALUES

(1001, 'SMITH', '男',20 ,8500 , '2005-11-12'),

(1002, 'ALLEN', '女',30 ,1600 , '2003-05-12'),

(1003, 'WARD', '女',30 ,1250 , '2003-05-12'),

(1004, 'JONES', '男',20 ,5000 , '1998-05-18'),

(1005, 'MARTIN', '男',30 ,1250 , '2001-06-12'),

(1006, 'BLAKE', '女',30 ,2850 , '1997-02-15'),
);

 

10.使用不指定字段名方式，添加emp表中编号为1007-1012的数据

INSERT INTO emp VALUES 
(1007, 'CLARK', '男',10 ,2450 , '2002-09-12'),

(1008, 'SCOTT', '男',20 ,7400 , '2003-05-12'),

(1009, 'KING', '女',10 ,5000 , '1995-01-01'),

(1010, 'TRRNER', '女',30 ,1500 , '1997-10-12'),

(1011, 'ADAMS', '男',20 ,4500 , '1999-10-05'),

(1012, 'JAMES', '女',30 ,950 , '2008-06-15'),
);

 

11.将d_name值为’销售部门’的d_location值修改为’BeiHai’

UPDATE dept SET d_location= 'BeiHai' WHERE d_name= '销售部门';

12.将研发部门修改为开发部门

UPDATE dept SET d_name = '开发部门' WHERE d_name= '研发部门';

13.将编号为1010的员工删除

DELETE FROM emp WHERE e_no=1010;

14.将编号为1012的员工调到40部门

UPDATE emp SET dept_no =40 WHERE e_no= 1012;

进阶：

UPDATE emp SET dept_no = (SELECT d_no FROM dept WHERE d_name= '作业部门') WHERE e_no= 1012;

 

15.将20部门的工资上调100

UPDATE emp SET e_salary = e_salary + 100 WHERE dept_no= 20;

进阶：

UPDATE emp SET e_salary = e_salary + 100 WHERE dept_no=(SELECT d_no FROM dept WHERE d_name= '开发部门');

 

16.将工资低于1000的员工删除

DELETE FROM emp WHERE e_salary < 1000;

17.在emp表中，查询工资范围在800-2500的员工信息

SELECT * FROM emp WHERE e_salary BETWEEN 800 AND 2500;

或者

SELECT * FROM emp WHERE e_salary >=800 AND e_salary <=2500;

 

18.在emp表中，查询dept_no等于10 和20 的所有记录

SELECT * FROM emp WHERE dept_no IN (10,20);
或者

SELECT * FROM emp WHERE dept_no = 10 OR dept_no = 20;

 

19.查询姓名为BLAKE的员工所在部门和部门所在地

SELECT e.e_name,d.d_name,d.d_location FROM emp e, dept d WHERE e.dept_no = d.d_no AND e.e_name = 'BLAKE';

20.在emp表中，计算不同部门的平均工资

SELECT dept_no,AVG(e_salary) FROM emp GROUP BY dept_no;

21.在emp表中，查询到目前为止工龄大于等于20年的员工信息

SELECT * FROM emp WHERE YEAR(CURDATE()) - YEAR(hireDate) >= 20;

或者

SELECT * FROM emp WHERE ADDDATE(hireDate, INTERVAL 20 YEAR) <= CURDATE();

或者

SELECT * FROM emp WHERE SUBDATE(CURDATE(), INTERVAL 20 YEAR) >= hireDate;

 

22.使用limit 查询emp表中第3条记录到第6条记录

SELECT * FROM emp LIMIT 2,4;

23.查询所有部门名称以及部门对应的员工姓名

SELECT d.d_name,e.e_name FROM emp e RIGHT JOIN dept d ON e.dept_no = d.d_no;

24.查询姓名A开头和S开头的员工信息

SELECT * FROM emp WHERE e_name LIKE 'A%' OR e_name LIKE 'S%';

25.查询男女员工人数

SELECT e_gender,COUNT(*) FROM emp GROUP BY e_gender;

26.使用子查询，查询与SCOTT同一个部门的其他员工信息

SELECT * FROM emp WHERE dept_no =

(SELECT dept_no FROM emp WHERE e_name='SCOTT') AND e_name != 'SCOTT';

 

27.使用子查询，查询工资大于3000的员工所属部门

SELECT * FROM dept WHERE d_no IN

(SELECT dept_no FROM emp WHERE e_salary > 3000);