练习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