工作信息,
create table if not exists Employees(
EmployeeID char(6) not null primary key,
name varchar(6) character set utf8 collate
utf8_unicode_ci not null ,
Fducation char(4) character set utf8 collate
utf8_unicode_ci not null ,
Birthday date not null,
Sex char(2) character set utf8 collate
utf8_unicode_ci not null ,
WarkYear tinyint ,
Address varchar(20) character
set utf8 collate utf8_unicode_ci ,
PhoneNumber char(12) character set utf8 collate
utf8_unicode_ci ,
DeparmentID char(3) character set utf8 collate
utf8_unicode_ci not null ,
foreign key (DeparmentID) references Deparments(DeparmentID)
)ENGINE=MYISAM character set utf8 collate utf8_unicode_ci ;
insert into Employees(
EmployeeID,name,Fducation,Birthday,Sex,WarkYear,Address,PhoneNumber,DeparmentID)
values('010002','王林','大专','1966-01-23',1,8,'五一路32-1-508','83355668',2)
insert into Employees(
EmployeeID,name,Fducation,Birthday,Sex,WarkYear,Address,PhoneNumber,DeparmentID)
values('020013','张兵','硕士','1982-12-09',1,2,'八一路166-4-102','83414282',1)
insert into Employees(
EmployeeID,name,Fducation,Birthday,Sex,WarkYear,Address,PhoneNumber,DeparmentID)
values('020018','李丽','大专','1960-07-30',1,2,'八一路166-4-102','84232283',1)
insert into Employees(
EmployeeID,name,Fducation,Birthday,Sex,WarkYear,Address,PhoneNumber,DeparmentID)
values('102201','刘明','本科','1972-07-30',1,2,'八一路166-4-102','84232283',5)
insert into Employees(
EmployeeID,name,Fducation,Birthday,Sex,WarkYear,Address,PhoneNumber,DeparmentID)
values('102208','朱経','硕士','1960-07-30',1,2,'中山西路102-4','84232283',5)
insert into Employees(
EmployeeID,name,Fducation,Birthday,Sex,WarkYear,Address,PhoneNumber,DeparmentID)
values('108991','钟敏','硕士','1960-07-30',0,4,'中山西路102-4','84232283',3)
insert into Employees(
EmployeeID,name,Fducation,Birthday,Sex,WarkYear,Address,PhoneNumber,DeparmentID)
values('111006','张勇','本科','1960-07-30',1,2,'中山西路102-4','84232283',5)
insert into Employees(
EmployeeID,name,Fducation,Birthday,Sex,WarkYear,Address,PhoneNumber,DeparmentID)
values('210678','林义','大专','1960-07-30',1,2,'中山西路102-4','84232283',3)
insert into Employees(
EmployeeID,name,Fducation,Birthday,Sex,WarkYear,Address,PhoneNumber,DeparmentID)
values('302566','陈平','本科','1960-07-30',1,3,'中山西路102-4','84232283',4)
insert into Employees(
EmployeeID,name,Fducation,Birthday,Sex,WarkYear,Address,PhoneNumber,DeparmentID)
values('504209','王芳','大专','1960-07-30',0,5,'中山西路102-4','84232283',4)
部门表
create table if not exists Deparments(
DeparmentID char(3) not null primary key,
DepartName char(30) not null ,
Note text
)ENGINE=MYISAM character set utf8 collate
utf8_unicode_ci;
insert into Deparments( DeparmentID,DepartName)
values('1','财务部')
insert into Deparments( DeparmentID,DepartName)
values('2','人力资源部')
insert into Deparments( DeparmentID,DepartName)
values('3','经理办公室')
insert into Deparments( DeparmentID,DepartName)
values('4','研发部')
insert into Deparments( DeparmentID,DepartName)
values('5','市场部')
薪水表
create table if not exists Salary(
EmployeeID char(6) not null primary key,
Income float not null ,
OutCome float not null
)ENGINE=MYISAM character set utf8 collate utf8_unicode_ci;
insert into Salary( EmployeeID,Income,OutCome)
values('000001','2100.8','123.09')
insert into Salary( EmployeeID,Income,OutCome)
values('010008','1582.62','88.03')
insert into Salary( EmployeeID,Income,OutCome)
values('102201','2569.88','185.65')
insert into Salary( EmployeeID,Income,OutCome)
values('504209','1987.01','79.58')
insert into Salary( EmployeeID,Income,OutCome)
values('203566','2980.7','210.2')
insert into Salary( EmployeeID,Income,OutCome)
values('108991','3259.98','281.52')
insert into Salary( EmployeeID,Income,OutCome)
values('020010','2860.0','798.0')
insert into Salary( EmployeeID,Income,OutCome)
values('020018','2347.68','180.0')
insert into Salary( EmployeeID,Income,OutCome)
values('308759','2531.98','199.08')
insert into Salary( EmployeeID,Income,OutCome)
values('210678','2240.0','121.0')
insert into Salary( EmployeeID,Income,OutCome)
values('102208','1980.0','100.0')
4.delete from Employees where EmployeeID='000001'
delete from Salary where
EmployeeID='000001'
5.update Employees set DeparmentID='4' where
EmployeeID='020018';
6.
insert into Salary( EmployeeID,Income,OutCome)
values('000001','2100.8','123.09')
7replace INTO `deparments`(`DeparmentID`, `DepartName`,
`Note`)
VALUES ('1','广告部','负责广告业务')
8.update Salary set Income=2860.0 where
EmployeeID='0020010';
9.select EmployeeID as 员工编号 ,(Income +100) as 收入 from Salary where Income>2000;
10.DELETE FROM `salary` WHERE `Income`>2500;
11.select * from Employees ;
12.select name as 姓名,Address as 地址,PhoneNumber as 电话 from Employees
;
13.select name as 姓名,Address as 地址,PhoneNumber as 电话 from Employees
where EmployeeID='000001';
14.select EmployeeID as 员工编号 ,Income as 收入 from Salary where
Income>2000;
15.select EmployeeID as 员工编号, name as 姓名,Address as 地址
,Birthday from Employees where
Birthday>'1970-01-01';
16.select EmployeeID as 员工编号, name as 姓名 from
Employees where DeparmentID='1';
17.select name as 姓名,Address as 地址,PhoneNumber as 电话 from Employees
where Sex='0';
18.select name as 姓名,Birthday 出生日期 from Employees
where Sex='1';
19.select count(*) as 员工人数 from Employees ;
20.select avg(Income) as 平均分 from Salary ;
21.select max(EmployeeID) as 员工编号 from Employees
;
22.select sum(OutCome) as 员工总支出 from Salary
;
23.select EmployeeID as 员工编号, name as 姓名 from
Employees where name like('王%');
24.select name as 姓名,Address as 地址,PhoneNumber as 电话 from
Employees where EmployeeID like '%2__' ;
25.select EmployeeID as 员工编号 ,Income as 收入 from Salary where
Income>2000 and Income<3000 ;
26.1.select DeparmentID as 部门编号 , name as 姓名,Address as
地址,PhoneNumber as
电话 from Employees
where DeparmentID='1' or DeparmentID='2'
2.select * from employees
where deparmentID in(1,2)
27.select * from employees
where deparmentID='1';
28.select * from Salary a
,employees b where
Income<2500 and a.EmployeeID =
b.EmployeeID ;
29.select * from Employees where DeparmentID=1 or DeparmentID=4 ;
31.
SELECT a.`DeparmentID` , `PhoneNumber`
, `Address` ,
`WarkYear` , `Sex` , `Birthday`
, `Fducation` , `name` ,
b.`EmployeeID` ,`Income`,`OutCome`
FROM Employees a, Salary b
WHERE a.EmployeeID = b.EmployeeID
32.SELECT `EmployeeID` , `name` , `Fducation` , `Birthday` , `Sex` , `WarkYear`
, `Address` , `PhoneNumber`
, `DepartName` , `Note` ,
b.`DeparmentID`
FROM deparments AS a, employees b
WHERE a.`DeparmentID` = b.`DeparmentID`
33.SELECT *
FROM Employees a, Deparments b
WHERE a.DeparmentID = b.DeparmentID
AND name = '王林'
34.select group_COUNT(*) as 员工人数 from Employees
where Sex ='1' ;
select
COUNT(*) as 员工人数 from Employees
where Sex ='0' ;
35.select * from Employees a where (select * from Salary b where
b.DeparmentID=a.DeparmentID )
select * from salary where (select * from
salary)
36.select `Fducation` ,count(* ) from employees group by
`Fducation` order by `Fducation`
37.SELECT `DeparmentID`, COUNT( * )
FROM employees
GROUP BY `DeparmentID`
ORDER BY `DeparmentID`
38.select * from salary order
by income;
39.select * from employees order by Birthday ;
40.SELECT a.`DeparmentID` , `PhoneNumber`
, `Address` ,
`WarkYear` , `Sex` , `Birthday`
, `Fducation` , `name` ,
b.`EmployeeID` ,`Income`,`OutCome`
FROM Employees a, Salary b
WHERE a.EmployeeID = b.EmployeeID
order by income desc limit 5