SQL示例宝典.doc

SQL示例-注释标记,(use 数据库名称)指定当前要操作的数据库对象use AdventureWorks-SQL语言不区分大小写-DQL语句的应用-从数据表中获取所有数据行记录-语法select * from 表名,select,from是关键字,*是通配符-from关键字只能和select关键字一起应用,select可单独应用select * from HumanResources.Employeeselect * from HumanResources.Department-获取数据表中指定列的数据-语法: select 列名,列名. from 表名以,号分割列名select EmployeeID,Title,BirthDate from HumanResources.Employeeselect DepartmentID,Name from HumanResources.Department-抽取列时定制自定义列名,掩盖原表列名-格式在SQL中文本格式代表字符串select 员工编号=EmployeeID,职位=Title,生日=BirthDatefrom HumanResources.Employee-格式在列名后直接声明自定义列名select AddressID 地址编号,AddressLine1 详细住址,City 城市 from Person.Address-格式使用as关键字显示声明select DepartmentID as 部门编号,Name as 部门名称 from HumanResources.Department-抽取记录时对数据列进行附加描述-格式: 描述文本,列,.select This Person Name is ,FirstName,LastName,Email is ,EmailAddress from Person.Contactselect Shift Name is ,Name,Begin Time is,StartTimefrom HumanResources.Shift-查询时组合多列的数据形成新列-使用+号组合多列数据,类似字符串组合-被组合的列只能是文本列,数字或其它列必须转换select The Department Name is +Name+ Group in +GroupName部门信息描述 from HumanResources.Departmentselect FirstName+ +LastNameName,Phone from Person.Contact-运算符操作-算术运算符select 100+1000select 1000*10/10select 500%5=,500%5Resultselect (100-50)*50%(2+50)-可运用于数字列之间的运算操作select SalesOrderID订单编号,ProductID产品编号,OrderQty数量,UnitPrice单价,OrderQty*UnitPrice小计 from Sales.SalesOrderDetailselect EmployeeID员工编号,Rate时薪,Rate*8+10日薪 from HumanResources.EmployeePayHistory-比较运算符-一般使用在where条件语句中-where是查询过滤条件声明关键字,必须跟在from语句后-语法: where 列名比较运算符比较值(对应列的数据类型)select EmployeeID,Title from HumanResources.Employeewhere Title=Stockerselect DepartmentID,Name from HumanResources.Departmentwhere DepartmentID = 100-逻辑运算符应用(and,not,or)-也必须和where条件结合,实现多条件过滤-逻辑与(and)select EmployeeID,Title,HireDate,Gender from HumanResources.Employee where HireDate=2001-7-1 and Gender Mselect ProductID,Name,ListPrice,Color from Production.Product where ListPrice =1000 andColor=red and ProductID=300-逻辑或(or)select SalesOrderID,OrderQty,UnitPrice,LineTotal from Sales.SalesOrderDetail where OrderQty 5 or LineTotal ! 10000select EmployeeID,Title,Gender,VacationHours from HumanResources.Employee where Gender=F or VacationHours = 100-逻辑非(not)select EmployeeID,Title,Gender,VacationHours from HumanResources.Employee where not Title=Buyerselect ProductID,Name,ListPrice from Production.Productwhere not ListPrice ! and orselect EmployeeID,HireDate,Gender,SickLeaveHours from HumanResources.Employee where HireDate=1999-4-4 or Gender=F and not SickLeaveHours 1000-is not null抽取列值不为null的数据列select SalesPersonID,SalesQuota,Bonus from Sales.SalesPersonwhere SalesQuota is not null and Bonus3000select EmployeeID,DepartmentID,StartDate,EndDate from HumanResources.EmployeeDepartmentHistory where EndDateis not null and DepartmentID=10-列举运算符(in|not in)-语法: 列名in (值(对应列数据类型),值.)-相当于or条件的组合-列举值可以存在或不存在,存在则显示,不存在则忽略select ContactID,Title,FirstName+ +LastNameName fromPerson.Contact where FirstName in (Liu,Tom,James,Josh)-OrderQty=5 or OrderQty=6 or OrderQty=7.select SalesOrderID,ProductID,OrderQty from Sales.SalesOrderDetail where OrderQty in (5,6,7,8)-not in获取不在列举值范围内的数据select * from HumanResources.Department where Name not in (Production,Sales,Inventory Control)select * from HumanResources.EmployeeDepartmentHistorywhere StartDate not in(1999-1-15,1999-1-16,1999-1-17)-查询时判断值如完整则称为精确查询-模糊查询操作符应用(like)-%通配符-语法: 列名like 条件通配符-%在后代表只需%前方文本吻合就默认为合格记录select ContactID,FirstName,LastName from Person.Contactwhere FirstName like jam%-%在前代表只需%后方文本吻合就默认为合格记录select DepartmentID,Name from HumanResources.Departmentwhere Name like %es-%前后都放置代表搜索文本中只要有任意文字符合则为trueselect EmployeeID,Title from HumanResources.Employeewhere Title like %ol% and EmployeeID between 20 and 100-使用_通配符-_代表任意字符或数字,只要符合前后文本则为tueselect ProductID,Name from Production.Product where Name like st_m-_查询中数量不限,每个_代表一个任意字符,空格也算一个字符select ProductID,Name,ProductModelID from Production.Productwhere ProductModelID like 1_-称为范围通配符-使用范围通配时必须确定模糊字符是数字还是字符-字母范围select AddressID,City from Person.Address where Citylike Ba-zthell-数字范围select AddressID,City,StateProvinceID from Person.Addresswhere StateProvinceID like 0-99-列举范围select DepartmentID,Name from HumanResources.Departmentwhere Name like s|a|bales-代表把中条件排除select DepartmentID,Name from HumanResources.Departmentwhere Name like c|a|balesselect AddressID,City,StateProvinceID from Person.Addresswhere StateProvinceID like a-z9a-z9-使用order by关键字对查询记录进行排序-一般总是放入查询语句的末尾,升序或降序-对日期排序时根据日期大小-acs升序,默认可省略select EmployeeID,Title,BirthDate from HumanResources.Employee order by BirthDate asc-对数字排序时根据数字大小-desc降序,不可省略select EmployeeID,Title,BirthDate,SickLeaveHours from HumanResources.Employee order by SickLeaveHours desc-对文本排序时根据首字母排序select EmployeeID,Title,BirthDate,SickLeaveHours from HumanResources.Employee where SickLeaveHours between 50 and 70 and Gender=M order by Title desc-支持多列排序,顺序先排后面的列,再排前方列-以,号分割多列,每列排序方式可不同select EmployeeID,Title,BirthDate,SickLeaveHours from HumanResources.Employee where HireDate between 2000-1-1 and 2002-1-1 order by EmployeeID desc,Title asc-抽取数据行集中顶部指定数量的记录(top)-select top 数量*|列列表 from 表select top 3 * from Person.Contact-先排序,后抽取select top 5 ContactID,Title,FirstName,LastName from Person.Contact order by ContactID desc-先条件排除,再排序,后抽取select top 3 AddressID,AddressLine1,AddressLine2,StateProvinceID from Person.Address where AddressLine2 is not null and StateProvinceID=79 order by AddressID-使用distinct关键字排除数据列中的重复数据-distinct关键字只能作用于单列,不能和其它列混合查询-语法: select distinct 列名from 表select distinct Color from Production.Product where Color is not null select distinct Title from HumanResources.Employee-distinct总是在select关键字后select distinct top 3 Title from HumanResources.Employeewhere Title like %ec% order by Title desc-示例select top 3 SalesPersonID,TerritoryID,SalesQuota,Bonusfrom Sales.SalesPerson where SalesQuota is not null andBonus between 2000 and 6000 order by SalesPersonID desc-字符串函数应用select ascii(.)-获取单个字符的键盘ASCII码select char(100)-获取数字代表的字符-获取字符在文本中的索引编号,从开始计算select charindex(N,WXNIIT)select len(WXNIIT)-获取文本长度select left(WXNIIT,3)-截取文本左方指定长度的字符串select right(WXNIIT,3)-截取文本右方指定长度的字符串select ltrim( WXNIIT)-去处左方空格select rtrim(WXNIIT )-去处右方空格select ltrim(rtrim( WXNIIT )-去处前后空格select reverse(WXNIIT)-反转字符串select str(12345)-数字转字符串-参数(数字常量,保留数字长度,小数位数),自动四舍五入select str(12345.678,7,1)select lower(WXNIIT)-字符串转小写select upper(wxniit)-字符串转大写-参数(字符串,开始截取索引,截取长度)select substring(WXNIIT,3,2)select substring(2008-5-29 10:19:12,1,10)select * from Person.Contact where len(rtrim(FirstName)+rtrim(LastName)15select The EmployeeID is: +ltrim(str(EmployeeID)+, Title is: +Title from HumanResources.Employeeselect EmployeeID,Title,HireDate from HumanResources.Employeewhere substring(ltrim(HireDate),7,4)=2000-日期函数应用-获取系统日期select getdate() -计算日期增加-dateadd(日期代码(yy,mm,dd),要计算的日期,依据日期)select dateadd(yy,10,getdate()-计算年增加select dateadd(mm,33,2005-1-1)-计算月增加select dateadd(dd,365,getdate()-计算日增加-计算二个日期间的差值-datediff(日期代码,比较日期,比较日期)return int-小日期要在参数位置,在前否则产生负数select datediff(yy,2000-1-1,getdate()select datediff(wk,2000-1-1,2008-1-1)select datediff(dd,getdate(),2011-1-1)-获取日期部分-datepart(日期代码,日期)返回日期部分整数select datepart(mm,getdate()select datepart(dd,99/10/01)-datename(日期代码,日期)返回日期部分字符串select datename(yy,2001.05.06)select datename(dd,11/17/2005)-部分日期函数获取日期整数select year(getdate()select month(getdate()select day(getdate()-日期一般只能使用=或!=比较操作符,要精确比较必须使用日期函数select EmployeeID,Title,HireDate from HumanResources.Employeewhere year(HireDate) = 10select EmployeeID,Title,HireDate from HumanResources.Employeewhere datepart(yy,HireDate)=2000 or datename(yy,HireDate)=2001select top 3 EmployeeID,Title,datepart(mm,BirthDate)生日月份 from HumanResources.Employee where month(BirthDate)=7-数学函数select pi()select power(10,3)-求数字次方select rand()-产生-1间随机数-参数(数字,保留小数位(正数舍小数位,负数舍整数位)select round(100.555,2)-四舍五入函数select round(167.555,-2)select SalesPersonID,SalesQuota,Bonus,增加金额=Bonus*round(rand(),2) from Sales.SalesPerson where SalesQuota is not null-次序函数-使用row_number()函数获取自动编号列-语法: row_number() over(order by 列名asc|desc)select row_number() over(order by SalesPersonID)记录编号,SalesPersonID,SalesQuota,Bonus from Sales.SalesPerson where Bonus !=0select row_number() over(order by Bonus desc)记录编号,SalesPersonID,SalesQuota,Bonus from Sales.SalesPerson where Bonus !=0 order by 记录编号desc-使用rank()函数对查询记录进行编号分组-语法:rank() over(order by 列名asc|desc)-有二条以上记录属于同一分组编号时会掩盖下一个数字编号select rank() over(order by Bonus desc)分组编号,SalesPersonID,SalesQuota,Bonus from Sales.SalesPerson select top 10 rank() over(order by ListPrice desc)高定价产品编号,ProductID,Name,ListPrice from Production.Product where ListPrice != 0-使用dense_rank()函数进行标准编号分组-dense_rank()记录有多条时不会掩盖下个分组编号select dense_rank() over(order by datediff(yy,BirthDate,getdate() desc)年龄编号分组,EmployeeID,Title,datediff(yy,BirthDate,getdate()员工年龄 from HumanResources.Employeeselect dense_rank() over(order by SickLeaveHours desc)病假时间分组,EmployeeID,Title,SickLeaveHours from HumanResources.Employee-系统函数select host_name()-获取机器名select host_id()-获取在数据库中的进程编号select suser_name()-获取当前数据库登录名select suser_id()-获取当前数据库登录IDselect user_name()-获取数据库访问对象名称select db_name()-获取数据库名称-获取数据表在数据库中编号select object_id(HumanResources.Employee)-根据表编号获取表名select object_name(869578136)-示例select EmployeeID,upper(Title)大写职位,datediff(yy,BirthDate,getdate()年龄 from HumanResources.Employee-数据类型转换函数-convert(要转换的数据类型,原数据,日期格式符)select convert(varchar(10),1555.666)select convert(money,100)select convert(float,123.55)-日期转文本时要加格式化编号(整数)-1开始,加进位(10,11,100,102等),最大不超过)-十位,百位年份为完整年份select convert(varchar(10),getdate(),108)select The EmployeeID is: +convert(varchar(3),EmployeeID)+, Title is: +Title from HumanResources.Employeeselect EmployeeID,Title,HireDate from HumanResources.Employeewhere convert(varchar(4),HireDate,120)=2001select EmployeeID,Title,convert(varchar(10),BirthDate,102)BirthDate from HumanResources.Employee-聚合函数应用-聚合函数只会返回单列值,不能和其它列同时查询(group分组后除外)-不能用于where条件-avg(数字列)函数求对应列数据平均值select avg(ListPrice)平均定价 from Production.Product-受where条件影响,先条件过滤后求平均值select avg(ListPrice)平均定价 from Production.Product where size is not null and color=red-Error-select ListPrice from Production.Product where avg(ListPrice)1000-count(distinct列名|*)函数求数据表记录行数量-*是通配,无视空值列select count(*) from Production.Productselect count(*) from Production.Product where color!=blue-列名参数会排除空值列(null列)后计数select count(name) from Production.Product-所有列select count(size) from Production.Product-非空值列-加distinct关键字先排除重复列后计数select count(distinct color) from Production.Productselect count(distinct Title) from HumanResources.Employee-max(列名)求数据列中的最大值-max函数中列数据类型不限select max(BirthDate)最小生日 from HumanResources.Employeeselect max(datediff(yy,BirthDate,getdate()最大年龄 from HumanResources.Employeeselect max(VacationHours)最大度假小时 from HumanResources.Employeewhere Gender=M and Title like Tool% -min(列名)求数据列中的最小值-min函数中列数据类型不限-列为字符串类型时求首字母最小select min(Name) from HumanResources.Departmentselect min(LineTotal) from Sales.SalesOrderDetail where OrderQty3 and UnitPrice1000-sum(数字列)求数据列的和select sum(LineTotal) from Sales.SalesOrderDetail select sum(SalesQuota) from Sales.SalesPerson where TerritoryID=4-使用group by语句对查询记录进行分组-默认聚合函数只能单独使用,但使用gourp by语句可结合列-gourp by语句对指定分组的列会自动排除重复记录-必须在where条件后使用,用于指定非聚合函数列select TerritoryID销售区域编号,avg(SalesQuota)平均销售额 from Sales.SalesPerson where TerritoryID is not null group by TerritoryID -可同时对多列分组,顺序是由后向前-多列分组效果不明显select EmployeeID,Title,max(SickLeaveHours)最大病假小时from HumanResources.Employee group by Title,EmployeeID-分组列使用那些有重复数据的数据列select Title职位,max(SickLeaveHours)最大病假小时from HumanResources.Employee group by Title order by 最大病假小时desc-使用having从句对查询条件进行进一步过滤(在where后)-可使用聚合函数进行条件过滤select Title职位,count(BirthDate)在岗人数 from HumanResources.Employee where Title like p% group by Title having avg(datediff(yy,BirthDate,getdate() = 40select SalesOrderID订单编号,sum(OrderQty)定购总数from Sales.SalesOrderDetail where SalesOrderID 45000group by SalesOrderID having min(OrderQty) = 3-使用compute,compute by语句对查询结果进行数据汇总-compute by语句一般在DQL语句末尾-对分组后的每个记录组实现汇总select EmployeeID,Title,Gender,VacationHours,SickLeaveHoursfrom HumanResources.Employee where EmployeeID = 10order by Title compute avg(VacationHours),avg(SickLeaveHours) by Titleselect SalesOrderID,OrderQty,LineTotal from Sales.SalesOrderDetailwhere SalesOrderID 43666 order by SalesOrderID compute sum(OrderQty),avg(LineTotal) by SalesOrderID-compute对所有记录分组进行总汇总-order by 指定对要分组的列进行预排序-compute 聚合函数,. by后对预排序的列进行分组-compute在compute by后对每个分组汇总进行单行的总汇总-语法:compute 聚合函数,.select TerritoryID,SalesPersonID,SalesQuota,Bonus from Sales.SalesPerson where TerritoryID is not null order by TerritoryID compute sum(SalesQuota),max(Bonus) by TerritoryID compute sum(SalesQuota),max(Bonus)select SalesOrderID,OrderQty,UnitPrice from Sales.SalesOrderDetail where SalesOrderID $2000-5select *from Sales.SalesOrderDetailwhere ProductID = 843-6select *from Sales.SalesOrderHeaderwhere ShipDate=2004-6-6-7select SalesOrderID 定单ID, OrderQty 定单数量,UnitPrice单价,LineTotal 总和from Sales.SalesOrderDetail-8select *from Sales.SalesOrderDetailwhere UnitPrice between $2000 and $2100-9select CountryRegionCode,convert(varchar(10),ModifiedDate,120) from Sales.SalesTerritory where TerritoryID=1-10select * fromSales.SalesOrderHeader where TaxAmt$10000-11select *from Sales.SalesTerritory where Name in(Canada,France,Germany)-12select SalesPersonID销售员ID,TerritoryID地区IDfrom Sales.SalesTerritoryHistory where TerritoryID in(2,4)-13select *from Sales.CreditCard where ExpYear=2006 and CardType=Vista-14select *from Sales.SalesOrderHeader where ShipDate2004-7-12-15select SalesOrderID定单号,convert(varchar(10),OrderDate,120)定单日期,Status状态,TotalDue总数from Sales.SalesOrderHeader where TotalDue10000-16select *from Sales.SalesOrderHeader wher