资源描述：
《SELECT语句的基本使用》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、SELECT语句的基本使用（单表查询）1)查询每个雇员的姓名、地址和电话。001USEygglSELECTname,address,phonenumberFROMemployees2)查询员工编号为“00001”的姓名与地址。002SELECTname,addressFROMemployeeswhereemployeeid=000013)查询女雇员的姓名与电话，并加标题。（三种方法）selectnameas姓名,phonenumberas年龄fromemployeeswheresex='False'se
2、lectname姓名,phonenumber电话fromemployeeswheresex='False'select姓名=name,电话=phonenumberfromemployeeswheresex='False'SELECTname,phonenumberFROMemployeeswheresex=0SELECTname姓名,phonenumber电话FROMemployeeswheresex=04)用表employees查询员工来自几个不同的部门.(distinct)SELECTdistinc
3、tdepartmentidFROMemployees1)显示每个员工的编号与实际收入。SELECTemployeeid,income-outcome实际收入FROMsalary2)找出所有姓王的员工的部门编号。(别忘了消除查询结果重复的行)（三种方法）selectdistinctdepartmentidfromemployeeswhereLEFT(name,1)='王'selectdistinctdepartmentidfromemployeeswherenamelike'王%'selectdistin
4、ctdepartmentidfromemployeeswhereSUBSTRING(name,1,1)='王'3)找出收入在2000~3456之间的员工的编号。(两种方法表示)selectemployeeidfromsalarywhereoutcomebetween2000and3456selectemployeeidfromsalarywhereoutcome>=2000andoutcome<=34564)找出所有在部门’1’或’2’或’5’工作的员工编号.(两种方法)SELECTemployeeid
5、编号FROMemployeeswheredepartmentidLIKE'[1,2,5]'SELECTemployeeid编号FROMemployeeswheredepartmentidin('1','2','5')1)找出所有地址中含有’中山’的员工的编号及部门号.selectemployeeid,departmentidfromemployeeswhereaddresslike'%中山%'2)找出邮政编码最后一位不是2或6或8的员工的姓名与邮政编码。（三种方法）selectname,zipfrome
6、mployeeswhereziplike'%[^2,6,8]'selectname,zipfromemployeeswhereRIGHT(zip,1)!=2andRIGHT(zip,1)!=6andRIGHT(zip,1)!=8selectname,zipfromemployeeswhereSUBSTRING(zip,6,1)!=2andSUBSTRING(zip,6,1)!=6andSUBSTRING(zip,6,1)!=83)查询各员工的姓名与出生时间，按年龄从大到小排列。selectname,bi
7、rthdayfromemployeesorderbyYEAR(GETDATE())-YEAR(birthday)desc4)查询各雇员的情况先按部门，同一部门再按姓名先后排列。select*fromemployeesorderbydepartmentid,name5)查询employees表中前20%行数据，输出姓名、性别与电话号码。selecttop20percentname,sex,phonenumberfromemployeesselectAVG(income)平均intotempfromsala
8、ryselectMAX(b.income),MIN(b.outcome)fromemployeesa,salaryb,departmentscwherea.employeeid=b.employeeidanda.departmentid=c.departmentIDandc.DepartmentName='经理办公室'selectAVG(b.income-b.outcome)fromemployeesa,salaryb,department
