数据库练习

练习24

 

create database k24 character set utf8

use  k24

create table guyuan (
gid int,
gname varchar(200),
gsex varchar(50),
gtitle varchar (200),
gbithday date,
bid int
)
create  table bumen(
bid int PRIMARY KEY,
bumen varchar(200)
)
create table salary(
gid int,
basesalary int,
titlesalary int,
deduction int
)
insert into guyuan  values
(1001,'张三','男','高级工程师','1975-1-1',1111),
(1002,'李四','女','助工','1975-1-1',1111),
(1003,'王五','男','工程师','1975-1-1',2222),
(1004,'赵六','男','工程师','1975-1-1',2222)

insert into bumen values 
(1111,'生成部'),
(2222,'销售部'),
(3333,'人事部')

insert into salary values
(1001,2200,1100,200),
(1002,1200,200,100),
(1003,1900,700,200),
(1004,19500,700,150)

update salary  set  salary.basesalary = 2700 , salary.titlesalary = 800 
inner join guyuan 
on salary.gid = guyuan.bid 
where guyuan.gname = '李四'

delete  from bumen where bumen.bumen = '人事部'

select *from bumen 

select *from  salary 

select guyuan.gname from guyuan  where gbithday  >1979 and gname like '李%'
 select count(*) from  guyuan inner join bumen on  guyuan.gid = bumen.bid group by bumen.bid