SQL入门宝典(无锡NIIT名师授课)

SQL入门宝典

SQL示例

--注释标记,(use 数据库名称)指定当前要操作的数据库对象

use AdventureWorks

--SQL语言不区分大小写

--DQL语句的应用

--从数据表中获取所有数据行记录

--语法select * from 表名,select,from是关键字,*是通配符

--from关键字只能和select关键字一起应用,select可单独应用

select * from HumanResources.Employee

select * from HumanResources.Department

--获取数据表中指定列的数据

--语法: select 列名,列名... from 表名以,号分割列名

select EmployeeID,Title,BirthDate from HumanResources.Employee

select DepartmentID,Name from HumanResources.Department

--抽取列时定制自定义列名,掩盖原表列名

--格式在SQL中'文本'格式代表字符串

select '员工编号'=EmployeeID,'职位'=Title,'生日'=BirthDate from 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.Contact

select 'Shift Name is ',Name,'Begin Time is',StartTime from HumanResources.Shift

--查询时组合多列的数据形成新列

SQL入门宝典

--使用+号组合多列数据,类似字符串组合

--被组合的列只能是文本列,数字或其它列必须转换

select 'The Department Name is '+Name+' Group in '+

GroupName'部门信息描述' from HumanResources.Department

select FirstName+' '+LastName'Name',Phone from

Person.Contact

--运算符操作

--算术运算符

select 100+1000

select 1000*10/10

select '500%5=',500%5'Result'

select (100-50)*50%(2+50)

--可运用于数字列之间的运算操作

select SalesOrderID'订单编号',ProductID'产品编号',

OrderQty'数量',UnitPrice'单价',OrderQty*UnitPrice'小计'

from Sales.SalesOrderDetail

select EmployeeID'员工编号',Rate'时薪',Rate*8+10'日薪'

from HumanResources.EmployeePayHistory

--比较运算符

--一般使用在where条件语句中

--where是查询过滤条件声明关键字,必须跟在from语句后

--语法: where 列名比较运算符比较值(对应列的数据类型)

select EmployeeID,Title from HumanResources.Employee

where Title='Stocker'

select DepartmentID,Name from HumanResources.Department

where DepartmentID <= 3

select * from HumanResources.Employee where

SickLeaveHours >= 100

--逻辑运算符应用(and,not,or)

--也必须和where条件结合,实现多条件过滤

--逻辑与(and)

select EmployeeID,Title,HireDate,Gender from

HumanResources.Employee where HireDate='2001-7-1'

and Gender <> 'M'

select ProductID,Name,ListPrice,Color from

Production.Product where ListPrice >=1000 and

SQL入门宝典

Color='red' and ProductID>=300

--逻辑或(or)

select SalesOrderID,OrderQty,UnitPrice,LineTotal from

Sales.SalesOrderDetail where OrderQty > 5 or

LineTotal !< 10000

select 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='Buyer'

select ProductID,Name,ListPrice from Production.Product

where not ListPrice !< 1000

--逻辑运算符优先级not > and > or

select EmployeeID,HireDate,Gender,SickLeaveHours from

HumanResources.Employee where HireDate='1999-4-4' or

Gender='F' and not SickLeaveHours <= 50

--范围操作符(between)

--语法between 值范围(文本,日期,数字) and 值范围

--包含范围值本身

select EmployeeID,Title,BirthDate from

HumanResources.Employee where BirthDate between

'1960-5-5' and '1970-1-1'

select SalesPersonID,SalesQuota,Bonus from

Sales.SalesPerson where Bonus between 2000 and 5000

select ContactID,FirstName,LastName from Person.Contact

where FirstName between 'Amy' and 'James'

--not between 不在某一范围内

--范围值不包括在条件中

select SalesOrderID,OrderDate,ShipDate from

Sales.SalesOrderHeader where OrderDate not between

'2002-1-1' and '2004-1-1'

select ProductID,Name,ListPrice from Production.Product

where ListPrice not between 500 and 1000

SQL入门宝典

--空或非空判断运算符(is null | is not null)

--数据表中的空值列必须以null关键字标识

--is null抽取列值为null的数据列

select AddressID,AddressLine1,AddressLine2,City from

Person.Address where AddressLine2 is null

select ProductID,Name,ListPrice,Size from Production.Product

where Size is null and ListPrice > 1000

--is not null抽取列值不为null的数据列

select SalesPersonID,SalesQuota,Bonus from Sales.SalesPerson

where SalesQuota is not null and Bonus>3000

select EmployeeID,DepartmentID,StartDate,EndDate from

HumanResources.EmployeeDepartmentHistory where EndDate

is not null and DepartmentID=10

--列举运算符(in|not in)

--语法: 列名in (值(对应列数据类型),值....)

--相当于or条件的组合

--列举值可以存在或不存在,存在则显示,不存在则忽略

select ContactID,Title,FirstName+' '+LastName'Name' from

Person.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 ('Product …… 此处隐藏：17423字，全部文档内容请下载后查看。喜欢就下载吧 ……