数据库收集的好的练习：

SET NAMES UTF8;

DROP DATABASE IF EXISTS gongsi ;

CREATE DATABASE gongsi CHARSET=UTF8;

USE gongsi;

CREATE TABLE bumen(

 b_id  INT PRIMARY KEY AUTO_INCREMENT,

 b_name VARCHAR(8) NOT NULL

);

INSERT INTO bumen VALUES(1,'运营部');

INSERT INTO bumen VALUES(2,'人事部');

INSERT INTO bumen VALUES(3,'后勤部');

INSERT INTO bumen VALUES(4,'财务部');

 

CREATE TABLE yuangong(

 y_id  INT PRIMARY KEY AUTO_INCREMENT,

 y_name VARCHAR(8),

 y_sex BOOL,

 y_age SMALLINT,

 y_address VARCHAR(64),

 b_id INT,

 #FOREIGN KEY (familyid)REFERENCES gongsi(b_id)

 FOREIGN KEY (b_id) REFERENCES bumen(b_id)

);

INSERT INTO yuangong VALUES(1,'小明',1,30,'河南省',1);

INSERT INTO yuangong VALUES(2,'小花',0,36,'河南省',4);

INSERT INTO yuangong VALUES(3,'王红',0,20,'河南省',4);

INSERT INTO yuangong VALUES(4,'芳芳',0,26,'河南省',1);

INSERT INTO yuangong VALUES(5,'小北',1,20,'河南省',4);

INSERT INTO yuangong VALUES(6,'王林',1,20,'河南省',3);

INSERT INTO yuangong VALUES(7,'红红',0,26,'河南省',2);

INSERT INTO yuangong VALUES(8,'小刚',1,46,'河南省',4);

INSERT INTO yuangong VALUES(9,'王超',1,80,'河南省',3);

INSERT INTO yuangong VALUES(10,'东东',1,55,'河南省',4);

SELECT * FROM bumen;

SELECT*FROM yuangong;

 select y_name,y_address ,y_sex from yuangong where y_age>=25 and y_age<=30 and y_sex=1;

 select*from yuangong where b_id=4 and y_sex=1 and y_age<40;

 select y_name ,MAX(y_age),y_sex from yuangong where y_sex=0 and b_id=2;

insert into yuangong values(11,'丽丽',1,25,'河南省',1);

update yuangong set b_id=3 where y_sex=0 and y_age>30 and b_id=2;

select b_id ,y_name,y_age from yuangong;

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

select b_name,y_age from bumen,yuangong where bumen.b_id=yuangong.b_id group by b_name having max(y_age);

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

select b_name,count(*)from bumen,yuangong where bumen.b_id=yuangong.b_id group by bumen.b_id order by count(*) desc,bumen.b_id asc;

#将张三的名字改为李四，并调到财务部

 update yuangong set y_name="wang",b_id=(select b_id from bumen where b_name="财务部") where y_name="丽丽";

#将后勤部年龄大于60的员工删除；

delete from yuangong where y_age>60 and b_id in(select b_id from bumen where b_name="后勤部");

#查询财务部年龄不在20-30之间的男生信息；

select *from bumen,yuangong where bumen.b_id=yuangong.b_id and b_name="财务部"

and y_sex=1 and y_age not between 20 and 30;