(selectbirthday
fromemployees
wheredepartmentidin
(selectdepartmentid
fromdepartments
wheredepartmentname='研发部'
)
)
20、查询每个员工的情况及其薪水的情况:
selectemployees.*,departments.departmentname
fromemployees,departments
whereemployees.departmentid=departments.departmentid
21、使用内连接方法查找不在财务部工作的所有员工信息:
selectemployees.*
fromemployeesinnerjoindepartmentsonemployees.departmentid=departments.departmentid
wheredepartmentname!
='财务部'
22、使用外连接方法查找出所有员工的月收入:
selectemployees.*,salary.income
fromemployeesjoinsalaryonemployees.employeeid=salary.employeeid
23、查找财务部雇员的最高收入:
selectmax(income)
fromsalary
whereemployeeidin
(selectemployeeid
fromemployees
wheredepartmentidin
(selectdepartmentid
fromdepartments
wheredepartmentname='财务部'
)
)
24、查询财务部雇员的最高实际收入:
selectmax(income-outcome)
fromsalary
whereemployeeidin
(selectemployeeid
fromemployees
wheredepartmentidin
(selectdepartmentid
fromdepartments
wheredepartmentname='财务部'
)
)
25、统计财务部收入在2500元以上的雇员人数:
selectcount(employeeid)
fromemployees
wheredepartmentidin
(selectdepartmentidfromdepartments
wheredepartmentname='财务部')
andemployeeidin
(
selectemployeeid
fromsalary
whereincome>2500)
26、按部门列出在该部门工作的员工的人数:
selectdepartmentid,count(*)as人数
fromemployees
groupbydepartmentid
27、按员工的学历分组:
selecteducation,count(*)as人数
fromemployees
groupbyeducation
28、按员工的工作年份分组,统计年份人数:
selectworkyear,count(*)as人数
fromemployees
groupbyworkyear
29、按各雇员的情况收入由低到高排列:
selectemployees.*,salary.income
fromemployees,salary
whereemployees.employeeid=salary.employeeid
orderbyincome
30、将员工信息按出生时间从小到大排列:
select*
fromemployees
orderbybirthday
31、在orderby字句中使用子查询,查询员工姓名,性别和工龄信息,要求按实际收入从大到小排列:
selectname,sex,workyear,income-outcome
fromsalary,employees
wheresalary.employeeid=employees.employeeid
orderbyincome-outcomedesc
视图部分
1、创建view1:
Createviewview1
as
selectemployees.employeeid,name,departmentname,(income-outcome)ascome
fromemployees,departments,salary
whereemployees.departmentid=departments.departmentidandemployees.employeeid=salary.employeeid
2、查询视图employeeid:
3、向视图view1中插入一行数据:
insertintoview1values('111111','谎言','1','30000')
4、查看视图(没有影响)基本表:
实验5
1、定义一个变量,用于描述YGGL数据库的salary表中000001号员工的实际收入,然后查询该变量:
declare@hyint
set@hy=(selectincome-outcome
fromsalary
whereemployeeid='000001')
select@hy
2、使用运算符“>”:
selectname
fromemployees
wherebirthday>'1974-10-10'
3、判断姓名为“王林”的员工实际收入是否高于3000元,如果是则显示“高收入”,否则显示“收入不高于3000”:
if((selectincome
fromsalary,employees
wheresalary.employeeid=employees.employeeidandemployees.name='刘明')>3000)
selectincomeas'高收入'
fromsalary,employees
wheresalary.employeeid=employees.employeeidandemployees.name='刘明'
else
select'收入不高于'
4、使用循环输出一个“*”三角形:
declare@iint
declare@jint
set@j=20
set@i=1
while@i<@j
begin
print(space((@j-@i)/2)+replicate('*',@i))
set@i=@i+2
end
4、按部门进行分类,使用if语句实现:
Createfunctionhy1(@departmentid1char(3))
returnschar(10)as
begin
declare@hy1char(10)
if((selectdepartmentidfromdepartmentswhere@departmentid1=departmentid)='1')
set@hy1='财务部'
if((selectdepartmentidfromdepartmentswhere@departmentid1=departmentid)='2')
set@hy1='人力资源部'
if((selectdepartmentidfromdepartmentswhere@departmentid1=departmentid)='3')
set@hy1='经理办公室'
if((selectdepartmentidfromdepartmentswhere@departmentid1=departmentid)='4')
set@hy1='研发部'
if((selectdepartmentidfromdepartmentswhere@departmentid1=departmentid)='5')
set@hy1='市场部'
return@hy1
end
selectemployeeid,name,address,dbo.hy1(departmentid)fromemployees
selectemployeeid,name,address,
casedepartmentid
when1then'财务部'
when2then'人力资源部'
when3then'经理办公室'
when4then'研发部'
when5then'市场部'
endas部门号
fromemployees
6、自定义一个函数,计算一个数的阶层:
createfunctionhy(@hy2int)returnsint
as
begin
declare@iint
set@i=@hy2
declare@jint
set@j=1
while@i>1
begin
set@j=@j*@i
set@i=@i-1
end
return(@j)
end
declare@hint
exec@h=dbo.hy4
select@has'jiecheng'
7、/*生成随机数*/
selectrand()
8、/*平方*/
selectsquare(12)
9、/*求财务部收入最高的员工姓名*/
selectmax(name)
fromemployees
whereemployeeidin
(selectemployeeid
fromsalary
whereemployeeidin
(selectemployeeid
fromemployees
wheredepartmentidin
(selectdepartmentid
fromdepartments
wheredepartmentname='财务部')
)
)
selectavg(income)as'平均收入'
fromsalary
/*聚合函数与groupby一起使用*/
selectworkyear,count(*)as人数
fromemployees
groupbyworkyear
/*将字符组成字符串*/
selectchar(123)
/*返回字符串左边开始的个字符*/
selectleft('abcdef',2)
/*返回指定日期时间的天数*/
selectday(birthday)
fromemployees
whereemployeeid='010000'
/*获取当前时间*/
selectgetdate()
实验6
1、创建索引:
createuniqueindexhuangyan
onemployees(employeeid)
2、/*用createindex语句创建主键*/
3、重建表employees中employeeid列上的索引
alterindexhuangyan
onemployeesrebuild
4、删除索引:
5、创建一个新表,使用一个复合列作为主键,作为表的约束,并为其命名:
createtableemployees5
(employeeidchar(6)notnull,
namechar(5)notnull,
sextinyint,
educationchar(4),
constraintyanprimarykey(employeeid,name)
)
为新表添加一列:
altertableemployees5
addaddresschar(10)
6、创建新表student,性别只能包含男或女:
createtablestudent
(号码char(6)notnull,
性别char
(2)notnull
check(性别in('男','女'))
)
7、创建新表:
createtableemployees7
(学号char(10)notnull,
出生日期datetimenotnull
check(出生日期>'1980-01-01')
)
8、创建一个规则:
9,创建salary2:
createtablesalary2
(employeeidchar(6)notnullprimarykey,
incomefloatnotnull,
outcomefloatnotnull,
foreignkey(employeeid)
referencessalary(employeeid)
onupdatecascade
ondeletecascade
)
10、添加一个外键,salary与employees有相关记录,则拒绝更新employees:
altertablesalary
addconstraintkc_for
foreignkey(employeeid)
referencesemployees(employeeid)
ondeletenoaction
onupdatenoaction
实验7
1、工作年份大于6时,跟换科室到经理办公室(根据员工):
CreatePROCUpdateDeptByYear
(@EmpIdchar(6))
AS
BEGIN
DECLARE@yearint
SELECT@year=WorkYearFromEmployeesWHEREEmployeeID=@EmpId
IF(@year>6)
UPDATEEmployees
SETDepartmentID='3'
WHEREEmployeeID=@EmpId
END
EXECUpdateDeptByYear'020010'
SELECT*FROMEmployeesWHEREEmployeeid='020010'
2、根据每个员工的学历将收入提高元:
CREATEPROCUpdateInComeByEdu@Employeeidchar(6)
AS
BEGIN
UPDATESalary
SETInCome=InCome+500
FROMSalary
LEFTJOINEmployees
ONSalary.EmployeeID=Employees.EmployeeID
WHERESalary.Employeeid=@Employeeid
END
EXECUpdateInComeByEdu'020010'
SELECT*FROMSalarywhereEmployeeID='020010'
3、游标:
CREATEPROCEDUREEmployees_bili
AS
BEGIN
DECLARE@iFLOAT
DECLARE@jFLOAT
DECLARE@EducationCHAR(10)
DECLAREEmployees_cursorCURSOR
FORSELECTEducationFROMEmployees
SET@i=0
SET@j=0
OPENEmployees_cursor
FETCHEmployees_cursorINTO@Education
WHILE(@@FETCH_STATUS=0)
BEGIN
IF(@Education!
='大专')
SET@i=@i+1
SET@j=@j+1
FETCHEmployees_cursorINTO@Education
END
CLOSEEmployees_cursor
SELECT@iAS'本科及以上员工所占员工数'
SELECT@jAS'员工总数'
SELECT@i/@jAS'本科及以上员工所占比例'
CLOSEEmployees_cursor
END
EXECEmployees_bili
4、使用命令的方式修改存储过程的定义:
5、对于YGGL数据库,