mysql查询练习题.doc

1、（机试）建库建表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=INNODBc.建立一张员工表(员工编号y_id,姓名y_name,性别y_sex,年龄y_age,住址y_address,部门编号b_id), 其中y_id为主键自增长。住址默认为不详b_id依赖

2、于部门表的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_

3、sex=男 AND y_age BETWEEN 25 AND 302、查询财务部所有40岁以下男员工的所有信息 SELECT * FROM bumen,yg WHERE bumen.b_id=yg.b_id ANDb_name=财务部 AND y_age30) 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：查询每个部门各有多少人，显示部门名字和人数，按人数倒序，如果人数相同，

4、按部门编号正序。SELECT b_name,COUNT(*) FROM bumen,yg WHERE bumen.b_id=yg.b_id GROUP BY bumen.b_idORDER BY COUNT(*)DESC,bumen.b_id ASC8：将张三的的名字改为李四，并调到财务部。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_age60 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_idAND b_name=财务部 AND y_sex=男AND y_age NOT BETWEEN 20 AND 30