SQL实验2实验4.docx
《SQL实验2实验4.docx》由会员分享,可在线阅读,更多相关《SQL实验2实验4.docx(24页珍藏版)》请在冰豆网上搜索。
![SQL实验2实验4.docx](https://file1.bdocx.com/fileroot1/2023-2/2/7c670224-09da-40c4-bbee-28fb770c5298/7c670224-09da-40c4-bbee-28fb770c52981.gif)
SQL实验2实验4
/*实验2~4*/
/*删除YGGL*/
USEmaster
GO
DROPDATABASEYGGL
DROPDATABASEYGGL1
/*创建YGGL*/
CREATEDATABASEYGGL
ON
(
NAME='YGGL_Data',
FILENAME='c:
\YGGL\YGGL.mdf',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=5%
)
LOGON
(
NAME='YGGL_Log',
FILENAME='c:
\YGGL\YGGL_Log.ldf',
SIZE=2MB,
MAXSIZE=5MB,
FILEGROWTH=1MB
)
GO
/*创建表*/
USEYGGL
GO
CREATETABLEEmployees
(
EmployeeIDchar(6)NOTNULLPRIMARYKEY,
Namechar(10)NOTNULL,
Educationchar(4)NOTNULL,
BirthdaydateNOTNULL,
SexbitNOTNULLDEFAULT1,
WorkYeartinyintNULL,
Addressvarchar(40)NULL,
PhoneNumberchar(12)NULL,
DepartmentIDchar(3)NOTNULL
)
GO
USEYGGL
GO
CREATETABLEDepartments
(
DepartmentIDchar(3)NOTNULLPRIMARYKEY,
DepartmentNamechar(20)NOTNULL,
Notevarchar(100)NULL
)
GO
USEYGGL
GO
CREATETABLESalary
(
EmployeeIDchar(6)NOTNULLPRIMARYKEY,
InComefloatNOTNULL,
OutComefloatNOTNULL
)
GO
/*创建YGGL1*/
CREATEDATABASEYGGL1
ON
(
NAME='YGGL1_Data',
FILENAME='c:
\YGGL\YGGL1.mdf',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=5%
)
LOGON
(
NAME='YGGL1_Log',
FILENAME='c:
\YGGL\YGGL1_Log.ldf',
SIZE=2MB,
MAXSIZE=5MB,
FILEGROWTH=1MB
)
GO
/*修改YGGL1数据库逻辑文件的初始大小*/
ALTERDATABASEYGGL1
MODIFYFILE
(
NAME='YGGL1_Data',
SIZE=16MB
)
GO
/*在YGGL1中创建表Salary1,多一列ActIncome,由InCome-OutCome*/
USEYGGL1
GO
CREATETABLESalary1
(
EmployeeIDchar(6)NOTNULLPRIMARYKEY,
InComefloatNOTNULL,
OutComefloatNOTNULL,
ActIncomeASIncome-OutComePERSISTED
)
GO
/*在YGGL1中创建表Employees1,将Address删除,将Sex的默认值修改为0*/
USEYGGL1
GO
CREATETABLEEmployees1
(
EmployeeIDchar(6)NOTNULLPRIMARYKEY,
Namechar(10)NOTNULL,
Educationchar(4)NOTNULL,
BirthdaydateNOTNULL,
SexbitNOTNULLDEFAULT1,
WorkYeartinyintNULL,
Addressvarchar(40)NULL,
PhoneNumberchar(12)NULL,
DepartmentIDchar(3)NOTNULL
)
GO
ALTERTABLEEmployees1
DROPCOLUMNAddress
GO
/*输入数据*/
USEYGGL
GO
INSERTINTOEmployeesVALUES('000001','王林','大专','1966-01-23',1,8,'中山路32-1-508','83355668','2'),
('010008','伍容华','本科','1976-03-28',1,3,'北京东路100-2','83321321','1'),
('020010','王向容','硕士','1982-12-09',1,2,'四牌楼10-0-108','83792361','1'),
('020018','李丽','大专','1960-07-30',0,6,'中山东路102-2','83413301','1'),
('102201','刘明','本科','1972-10-18',1,3,'虎距路100-2','83606608','5'),
('102208','朱俊','硕士','1965-09-28',1,2,'牌楼巷5-3-106','84708817','5'),
('108991','钟敏','硕士','1979-08-10',0,4,'中山路10-3-105','83346722','3'),
('111006','张石兵','本科','1974-10-01',1,1,'解放路34-1-203','84563418','5'),
('210678','林涛','大专','1977-04-02',1,2,'中山北路24-35','83467336','3'),
('302566','李玉珉','本科','1968-09-20',1,3,'热和路209-3','58765991','4'),
('308759','叶凡','本科','1978-11-18',1,2,'北京西路3-7-52','83308901','4'),
('504209','陈林琳','大专','1969-09-03',0,5,'汉中路120-4-12','84468158','4')
GO
USEYGGL
GO
INSERTINTODepartmentsVALUES('1','财务部',NULL),
('2','人力资源部',NULL),
('3','经理办公室',NULL),
('4','研发部',NULL),
('5','市场部',NULL)
GO
USEYGGL
GO
INSERTINTOSalaryVALUES('000001',2100.8,123.09),
('010008',1582.62,88.03),
('102201',2569.88,185.65),
('111006',1987.01,79.58),
('504209',2066.15,108.0),
('302566',2980.7,210.2),
('108991',3259.98,281.52),
('020010',2860.0,198.0),
('020018',2347.68,180.0),
('308759',2531.98,199.08),
('210678',2240.0,121.0),
('102208',1980.0,100.0)
GO
USEYGGL
GO
CREATETABLEEmployees2
(
EmployeeIDchar(6)NOTNULLPRIMARYKEY,
Namechar(10)NOTNULL,
Educationchar(4)NOTNULL,
BirthdaydateNOTNULL,
SexbitNOTNULLDEFAULT1,
WorkYeartinyintNULL,
Addressvarchar(40)NULL,
PhoneNumberchar(12)NULL,
DepartmentIDchar(3)NOTNULL
)
GO
/*这一行有错误*/
USEYGGL
GO
INSERTINTOEmployees2SELECT*FROMEmployees
GO
UPDATESallary
SETInCome=2890
WHEREEmployeeID='000001'
UPDATESalary
SETInCome=InCome+100;
DELETEFROMEmployees
WHEREEmployeeID='000001'
DELETEFROMEmployees
WHERESex=0
TRUNCATETABLESalary/*清空表内容*/
USEYGGL
GO
CREATETABLEEmployees3
(
EmployeeIDchar(6)NOTNULLPRIMARYKEY,
Namechar(10)NOTNULL,
Educationchar(4)NOTNULL,
BirthdaydateNOTNULL,
SexbitNOTNULLDEFAULT1,
WorkYeartinyintNULL,
Addressvarchar(40)NULL,
PhoneNumberchar(12)NULL,
DepartmentIDchar(3)NOTNULL
)
GO
MERGEINTOEmployees3
USINGEmployeesONEmployees3.EmployeeID=Employees.EmployeeID
WHENMATCHED
THENUPDATESETEmployees3.Name=Employees.Name,
Employees3.Education=Employees.Education,
Employees3.Birthday=Employees.Birthday,
Employees3.Sex=Employees.Sex,
Employees3.WorkYear=Employees.WorkYear,
Employees3.Address=Employees.Address,
Employees3.PhoneNumber=Employees.PhoneNumber,
Employees3.DepartmentID=Employees.DepartmentID
WHENNOTMATCHED
THENINSERTVALUES(Employees.EmployeeID,Employees.Name,Employees.Education,
Employees.Birthday,Employees.Sex,Employees.WorkYear,
Employees.Address,Employees.PhoneNumber,
Employees.DepartmentID)
WHENNOTMATCHEDBYSOURCE
THENDELETE;
/*用SELECT语句查询Departments,Salary*/
USEYGGL
GO
SELECT*
FROMEmployees
GO
USEYGGL
GO
SELECT*
FROMDepartments
USEYGGL
GO
SELECT*
FROMSalary
/*查询Employees表中每个雇员的地址和电话*/
USEYGGL
GO
SELECTName,Address,PhoneNumber
FROMEmployees
/*查询Salary中的OutCome*/
USEYGGL
GO
SELECTOutCome
FROMSalary
/*查询Employees中的部门号和性别,要求使用DISTINCT消除重复行*/
USEYGGL
GO
SELECTDISTINCTDepartmentID,Sex
FROMEmployees
/*查询月收入高于2000元的员工号码*/
USEYGGL
GO
SELECTEmployeeID
FROMSalary
WHEREInCome>2000
/*查询1970年以后出生的员工的姓名和住址*/
USEYGGL
GO
SELECTEmployeeID,Name
FROMEmployees
WHEREBirthday>'1970-1-1'
/*查询所有财务部员工的号码和姓名*/
USEYGGL
GO
SELECTEmployeeID,Name
FROMEmployees
WHEREDepartmentID=
(
SELECTDepartmentID
FROMDepartments
WHEREDepartmentName='财务部'
)
/*查询Employees表中男员工的姓名和出生日期,要求将各列标题用中文表示*/
USEYGGL
GO
SELECTNameAS'姓名',BirthdayAS'出生日期'
FROMEmployees
WHERESex='1'
/*查询Employees员工的姓名,住址和收入水平,2000元以下显示为低收入,
2000~3000元显示为中等收入,
3000元以上为高收入*/
USEYGGL
GO
SELECTNameAS'姓名',AddressAS'地址',
CASE
WHENInCome<2000THEN'低收入'
WHENInComeBETWEEN2000AND3000THEN'中等收入'
WHENInCome>3000THEN'高收入'
ENDAS'收入水平'
FROMEmployees,Salary
/*使用SELECT语句进行简单的计算*/
USEYGGL
GO
SELECTEmployeeID,总收入=InCome-OutCome
FROMSalary
/*计算Salary表中员工月收入的平均数*/
USEYGGL
GO
SELECT员工月收入平均=AVG(InCome)
FROMSalary
/*获得Employees表中最大的员工号码*/
USEYGGL
GO
SELECT最大员工号码=MAX(EmployeeID)
FROMEmployees
/*计算Salary表中所有员工的总支出*/
USEYGGL
GO
SELECTSUM(OutCome)AS所有员工总支出
FROMSalary
GO
/*查询财务部雇员的最高和最低实际收入*/
USEYGGL
GO
SELECTMAX(InCome)AS最高收入,MIN(InCome)AS最低收入
FROMEmployees,Salary
WHEREDepartmentID=
(
SELECTDepartmentID
FROMDepartments
WHEREDepartmentName='财务部'
)
GO
/*找出所有其地址中含有“中山”的雇员的号码及部门号*/
USEYGGL
GO
SELECTEmployeeIDAS号码,DepartmentIDAS部门号
FROMEmployees
WHEREAddressLIKE'%中山%'
GO
/*查找员工号码中倒数第二个数字为0的员工的姓名,地址和学历*/
USEYGGL
GO
SELECTName,Address,Education
FROMEmployees
WHEREEmployeeIDLIKE'%0_'
GO
/*找出所有在部门“1”或“2”工作的雇员的号码*/
USEYGGL
GO
SELECTEmployeeID
FROMEmployees
WHEREDepartmentIDBETWEEN1AND2/*WHEREDepartmentID='1'ORDepartmentID='2'*/
GO
/*使用INTO子句,由表Employees创建“男员工”表,包括编号和姓名*/
USEYGGL
GO
SELECTEmployeeID,Name
INTO男员工
FROMEmployees
WHERESex='1'
GO
USEYGGL
GO
SELECT*
FROM男员工
GO
/*用子查询的方法查找所有收入在2500元以下的雇员的情况*/
USEYGGL
GO
SELECT*
FROMEmployees
WHEREEmployeeIDIN/*不能用等于,否则出错*/
(
SELECTEmployeeID
FROMSalary
WHEREInCome<=2500
)
GO
/*用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的姓名*/
USEYGGL
GO
SELECTName
FROMEmployees
WHEREEmployeeIDIN
(
SELECTEmployeeID
FROMSalary
WHEREInComeIN
(
SELECTInCome
FROMSalary
WHEREEmployeeIDIN
(
SELECTEmployeeID
FROMEmployees
WHEREDepartmentIDIN
(
SELECTDepartmentID
FROMDepartments
WHEREDepartmentName='研发部'
)
)
AND
InCome>ALL
(
SELECTInCome
FROMSalary
WHEREEmployeeIDIN
(
SELECTEmployeeID
FROMEmployees
WHEREDepartmentIDIN
(
SELECTDepartmentID
FROMDepartments
WHEREDepartmentName='财务部'
)
)
)
)
)
/*用子查询的方法查找所有年龄比研发部雇员年龄都大的雇员的姓名*/
USEYGGL
GO
SELECTNAME
FROMEmployees
WHEREBirthdayIN
(
SELECTBirthday
FROMEmployees
WHEREDepartmentIDIN
(
SELECTDepartmentID
FROMDepartments
WHEREDepartmentName!
='研发部'
AND
Birthday<
(
SELECTMIN(Birthday)
FROMEmployees
WHEREDepartmentIDIN
(
SELECTDepartmentID
FROMDepartments
WHEREDepartmentName='研发部'
)
)
)
)
/*查询每个雇员的情况及其工作部门的情况*/
USEYGGL
GO
SELECTEmployees.*,Departments.*
FROMEmployees,Departments
WHEREEmployees.DepartmentID=Departments.DepartmentID
/*使用内连接方法查找出不在财务部工作的所有员工信息*/
USEYGGL
GO
SELECTEmployees.*
FROMEmployeesINNERJOINDepartments
ONEmployees.DepartmentID=Departments.DepartmentID
WHEREDepartments.DepartmentName!
='财务部'
/*使用外连接方法查找出所有员工的月收入*/
USEYGGL
GO
SELECTEmployees.NameAS姓名,Salary.InComeAS收入
FROMEmployeesLEFTOUTERJOINSalary
ONEmployees.EmployeeID=Salary.EmployeeID
/*查询研发部在1976年以前出生的雇员姓名及其薪水详情*/
USEYGGL
GO
SELECTName,InCome
FROM(EmployeesJOINSalaryONEmployees.EmployeeID=Salary.EmployeeID)
JOINDepartmentsONEmployees.DepartmentID=Departments.DepartmentID