mysql查询练习题

a.建立一个公司数据库(gongsi) CREATE DATABASE gongsi

b.建立一张部门表(部门编号b_id,部门名称b_name) ,

其中b_id为主键,自增长，b_name不允许为空

CREATE TABLE bumen (

b_id INT PRIMARY KEY AUTO_INCREMENT, b_name VARCHAR(10) NOT NULL )ENGINE=INNODB

c.建立一张员工表(员工编号y_id,姓名y_name,性别y_sex,年龄y_age,住址y_address,部门编号b_id),

其中y_id为主键自增长。 住址默认为’不详’

b_id依赖于部门表的b_id

CREATE TABLE yg(

y_id INT PRIMARY KEY AUTO_INCREMENT, y_name VARCHAR(10), y_sex ENUM('男','女'), y_age INT,

y_address VARCHAR(18) DEFAULT '不详', b_id INT,

FOREIGN KEY (b_id) REFERENCES bumen(b_id) )ENGINE=INNODB

1、查询年龄在25至30岁之间的男员工的姓名和住址。 SELECT y_name,y_address FROM bumen,yg WHERE bumen.b_id=yg.b_id AND y_sex=’男’ AND y_age BETWEEN 25 AND 30

2、查询财务部所有40岁以下男员工的所有信息

SELECT * FROM bumen,yg WHERE bumen.b_id=yg.b_id AND b_name='财务部' AND y_age<=40 AND y_sex=’男’

3、查询人事部年龄最大的女员工姓名 Select * from yg where y_age=(

Select max（y_age） from yg，bumen where bumen.b_id=yg.b_id And y_sex=’女’ AND b_name=’人事部’ )

And y_sex=’女’ AND b_id=(Select b_id from bumen where b_name=’人事部’)

4、2号新到一名员工，已知姓名，性别，年龄，将此员工加入到员工表

INSERT INTO yg (y_name,y_sex,y_age)VALUES('小灰灰','男',13)

5、在员工表中，将人事部年龄大于30岁的女同事，调到后勤部 UPDATE yg SET b_id=(SELECT b_id FROM bumen WHERE b_name='后勤部') WHERE y_id IN( SELECT * FROM

(SELECT y_id FROM yg WHERE b_id=(SELECT b_id FROM bumen WHERE b_name='人事部') AND y_age>30) xx )AND y_sex='女'

6：查询每个部门年龄最大的员工，显示部门名字和年龄。

SELECT b_name,y_age FROM bumen,yg WHERE bumen.b_id=yg.b_id GROUP BY b_name HAVING MAX(y_age)

7：查询每个部门各有多少人，显示部门名字和人数，按人数倒序，如果人数相同，按部门编号正序。

SELECT b_name,COUNT(*) FROM bumen,yg WHERE bumen.b_id=yg.b_id GROUP BY bumen.b_id

ORDER BY COUNT(*)DESC,bumen.b_id ASC 8：将张三的的名字改为李四，并调到财务部。

UPDATE yg SET y_name='李四',b_id=(

SELECT b_id FROM bumen WHERE b_name='财务部') WHERE y_name='张三'

9：将后勤部年龄大于60岁的员工删除。

DELETE FROM yg WHERE y_age>60 AND b_id IN( SELECT b_id FROM bumen WHERE b_name='后勤部') 10：查询财务部年龄不在20-30之间的男生信息。 SELECT * FROM bumen,yg WHERE bumen.b_id=yg.b_id AND b_name='财务部' AND y_sex=’男’ AND y_age NOT BETWEEN 20 AND 30