yggl数据库的创建
2014-05-06 07:56阅读：
工作信息，
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
