SQL语句2.docx
- 文档编号:24650486
- 上传时间:2023-05-29
- 格式:DOCX
- 页数:42
- 大小:461.65KB
SQL语句2.docx
《SQL语句2.docx》由会员分享,可在线阅读,更多相关《SQL语句2.docx(42页珍藏版)》请在冰豆网上搜索。
SQL语句2
第1章数据库设计
我们学的SQL和Oracle,之所以称之为关系型数据库,是因为数据库中的表是由关系的,呵呵,像是废话。
很多同学在学SQL语句时,觉得非常困难,那是因为你在学一个你根本不了解的数据库,数据库中的表不是你设计的,表与表之间的关系你不明白。
因此在学SQL语句之前,先介绍一下数据库设计。
我已经忘记了数据库设计的几个范式的严格定义,但我会数据库设计。
下面举例说明数据库设计:
学校需要开发一个系统记录有学生、课程和成绩信息。
数据库如何设计?
这里面涉及到两个实体,学生表、课程,这些表为实体表。
这些表之间有什么关系呢?
教师给某个班级的学生上课,成绩记录在成绩表。
一个学生可以参加多门课程,关系是1对多。
1.1数据库设计
设计表
1.2创建数据库和表
createdatabaseSchoolDB
go
useSchoolDB
go
createtabledbo.TStudent
(StudentIDnvarchar(15),
Snamenvarchar(10),
sexnchar
(1),
cardIDnvarchar(20),
Birthdaydatetime,
Emailnvarchar(20),
Classnvarchar(20),
enterTimedatetime)
createtabledbo.TSubject
(
subJectIDnvarchar(10),
subJectNamenvarchar(30),
BookNamenvarchar(30),
Publishernvarchar(20)
)
createtableTScore
(
StudentIDnvarchar(15),
subJectIDnvarchar(10),
markdecimal
)
1.3设置表与表之间的关系
将studentID设置为主键,保存。
设置录入时间列的默认值为系统当前时间
将subJectID设置为主键
添加外键关系
设置为级联更新和级联删除
添加到TSubject表的引用
不设置级联更新和级联删除
将studentID和subJectID作为复合主键。
1.4插入数据
将Excel中的数据插入表TStudent。
insertdbo.TStudent(StudentID,Sname,cardID,sex,Birthday,Class)SELECTconvert(nvarchar(15),convert(int,学号)),姓名,身份证号,性别,出生日期,班级
FROMOpenDataSource('Microsoft.Jet.OLEDB.4.0',
'DataSource="C:
\studentInfo.xls";Extendedproperties=Excel5.0')...[Sheet1$]
第2章元Transact-SQL概述
概述
2.1Ttansact-SQL编程语言
美国国家标准协会(ANSI)和国际标准组织(ISO)为SQL定义了标准,微软通过用Transact-SQL和ANSI—SQL兼容,Transact-SQL还包含了几种能够增强性能的扩展。
2.1.1Ttansact-SQL语句的类型
Ø数据定义语言(DateDefinitionLanguage)语句
DDL语句通过生成数据库、表、和用户自定义的数据库
createobject_name
alterobject_name
dropobject_name
usenorthwind
createtablecustomer
(cust_idint,companyvarchar(40),contactvarchar(30),phonechar(12))
go
Ø数据控制语言(DatecontrolLanguage)语句
用于改变某个用户或角色相关联的权限
grant
deny
revoke
usenorthwind
grantselectonproductstopublic
go
Ø数据操作语言(DateManipulationLanguage)语句
操作数据库中的数据。
你可以更改数据库中的数据或查询数据库中的信息。
Select
Insert
Delecte
Update
usenorthwind
selectcategoryid,productname,productid,unitpricefromproducts
2.1.2Transact-SQL语法要素
Ø批处理
go
一个批处理命令通知SQLServer分析并运行一个批处理的所有指令
实际上不是Transact-SQL语句,只是描述一个批处理。
局部变量作用范围局限在一个批内,必须独占一行。
Exec
用于执行用户自定义的函数,系统存储过程,用户自定义的存储过程或扩展存储过程
Ø注释语句
作为注释或在测试时是一些语句无效
嵌于行内的注释语句
SELECTproductname,(unitsinstock-unitsonorder)--Calculatesinventory
supplierID
FROMproducts
2.2GO
块注释语句
/*
Thiscoderetrievesallrowsoftheproductstable
anddisplaystheunitprice,theunitpriceincreased
by10percent,andthenameoftheproduct.
*/
USEnorthwind
SELECTunitprice,(unitprice*1.1),productname
FROMproducts
GO
2.3标识符
标准标识符
第一个字符是a-z或A-Z
第一个字符后可以是数字,字母,或各种符号@,$,_
第一个字母是符号时表示有特殊的用途
@代表局部变量或参数
#代表临时表和存储过程
##代表一个全局临时表
限定表示符
当对象名包含空格时,当用保留关键字被用作对象的名字时,必须使用括号和引号把限定标识符括起来
usenorthwind
select*from[orderdetails]
或只有setquoted_identifieron/off
usenorthwind
select*from"orderdetails"
标识符的命名原则
短
有意义
使用简单明了的命名习惯
能够区分对象的类型的标识符viewtable
保持对象名和用户名的唯一性sales用户sales表
2.4数据类型
数字型代表数字inttinyintsmallintbigint十进制小数moneysmallmoneydecimal浮点数和real
日期型datetime可以精确到0.333毫秒small
字符型包括char和nchar也包含变长字符类型varchar和nvarchar
二进制型Binary和varbinary,bit代表一位的值0或1,rowversion代表数据库中唯一的8位二进制。
唯一标识代表一个全局特殊标识符(GUID),是一个16位16进制的值。
SQL变量包括SQL所支持的各种数据类型,但不包括text,ntext,rowversion和sql_variant这几种数据类
位图和文本(immage&text)属于大型二进制对象结构
表这种数据类型代表一个表结构,将一个表保存在一个字段中
游标这种数据类型用于存储过程的编程
用户自定义数据类型由数据库管理员生成,它基于系统数据类型,当多个表需要在一个字段存储同一类型数据时,这些字段具有相同的数据类型、长度和可控属性时,选择实用用户自定义的数据类型。
2.5变量
使用Declare定义局部变量,@局部变量作用域仅限于一个批处理中,@@而全局变量在整个会话有效
usenorthwind
declare@EmpIDvarchar(11),@vlNamechar(20)
set@vlName='Dodsworth'
select@EmpID=employeeidfromemployeeswhereLastName=@vlName
select@EmpIDasEmployeeID
2.6系统函数
聚集函数---对于一个集合中的值进行运算,返回一个单一的,汇总的值。
usenorthwind
selectavg(unitprice)asAvgPricefromproducts
go
标量函数---只对单个值进行运算并返回单一的值,只要表达式正确,就可以使用这些函数
usenorthwind
selectDB_name()as'database'
go
行级函数---在T-SQL语句中,记录集函数可以用作表来使用。
Select*fromopenquery(oracleSrv,’selectname,idfromowner.titles’)
Go
2.6.1系统函数范例
1.把时间转化成其他各式
select'ansi:
',convert(varchar(30),getdate(),102)asstyle
union
select'Japanese',convert(varchar(30),getdate(),111)
union
select'European',convert(varchar(30),getdate(),113)
go
运算符
算数运算符+-*/%
比较运算符=<>>=
字符串联运算符+空字符不等于空值
逻辑运算符andornot
表达式
表达式是各种符号和对单个数据进行操作进行
selectorderID,ProductID,(UnitPrice*Quantity)asExtendedAmount
from[orderdetails]
where(UnitPrice*Quantity)>10000
流控制语言
语句集
Begin….end
If….else
while
usenorthwind
ifexists(select*fromorderswherecustomerID='frank')
print'***CustomerCannontbedeleted***'
elsebegin
deleteCustomerswherecustomerid='frank'
print'***Customerdeleted***'
end
go
deleteorderswherecustomerID='frank'
行级
declare@ntinyint
set@n=5
if(@nbetween4and6)
begin
while(@n>0)
begin
select@nas'Number'
case
when(@n%2)=1
then'EVEN'
else'ODD'
endas'Type'
set@n=@n-1
end
end
else
print'NoAnalysis'
go
保留关键字----在T-SQL语句的任何地方都不能使用已经定义的保留关键字
selectProductID,'ProductInventoryStatus'=case
when(UnitsInStock when((UnitsInStock-UnitsOnOrder) when(Discontinued=1)then'***Discontinued***' else'InStock' end fromnorthwind..Products 2.7试验A使用SQLServer联机帮助 附: 字符串函数 selectdateadd(yy,4,(getdate()))表示对当时时间加4年 selectdateadd(qq,2,(getdate()))季度 selectdateadd(mm,2,(getdate()))月 selectdateadd(dd,2,(getdate()))日 selectdateadd(wk,2,(getdate()))周 selectdateadd(hh,2,(getdate()))小时 selectdateadd(mi,2,(getdate()))分钟 selectdateadd(ss,2,(getdate()))秒 selectdateadd(dy,3,getdate()))一年中的第几天 selectdatediff(day,'2005-3-4',getdate())返回时间差 selectdatepart(day,getdate()) 下面是日期型转化成变长字符串,再将变长字符转化成日期型 declare@chvarchar(19) declare@tdatetime set@t=getdate() select@t set@ch=convert(varchar(19),year(@t))+'-'+convert(varchar(19),month(@t))+'-'+convert(varchar(19),day(@t)) select@ch selectconvert(datetime,@ch) 使用WaitfordelayorWaitfortime知道指定的时间 begintran updateauthorssetau_lname=au_lname waitfordelay'00: 00: 10' updatetitlessettitle=title rollbacktran begintran updatetitlessettitle=title updateauthorssetau_lname=au_lname rollbacktran 第3章使用Transact-SQL的查询工具 3.1SQL查询分析器 一个使用方便的文本编辑器 代码颜色转换 带有网格或文本输出的多重查询窗口 执行脚本一部分 查询执行的信息 3.1.1使用SQL查询分析器中的对象浏览工具 对象浏览器有如下功能 为对象生成脚本---生成创建对象的脚本,selectinsertupdate 执行存储过程---执行存储过程,当执行有参数的存储过程时,对象浏览器提示你输入参数的值 打开表----显示查询结果,可以编辑、插入或删除行 改变数据库中的对象 使用Transact-SQL模板 生成数据库,表,视图,索引,存储过程,统计数字和函数 管理扩展属性、连接的服务器、登陆帐号、角色和用户 声明及使用游标 定制脚本 3.1.2使用osql工具 Osql使用SQLServerODBC同服务器进行通信,参数区分大小写。 可以从批文件或命令行运行这个软件。 osql–Sonest00–E使用集成的身份验证连接 osql–Sonest00–Usa–P123区分大小写 go reset清除已经输入的所有语句 ED调用编辑器 ! ! command执行一个操作系统命令 QUIT或EXIT从osql中退出 CTRL+C结束一个osql查询,但不退出osql 1、执行Transact-SQL语句 在运行时动态构造并执行Transact-SQL语句 使用批组织一起要执行的语句 使用脚本文件保存批语句,以供日后使用 declare@dbnamevarchar(30),@tblnamevarchar(30) set@dbname='northwind' set@tblname='products' execute ('use'+@dbname+'select*from'+@tblname) Go 使用批 提交一个语句或同时提交多个语句,只有在SQL查询分析器或osql工具接受这个语句,在ODBC或ODBCAPI的应用程序使用go将会出现错误。 SQLServer优化、编译并执行批中的语句,用户自定义的变量有效范围局限于一个批内部。 对象生成语句必须使用单独的批,批中不能同时出现的语句 Createprocedure Createview Createtrigger Createruleas 3.2Createdefault 使用脚本 使用SQL查询分析器或写字板之类的任何其它文本编辑器,编写并保存脚本,扩展名.sql 1.最佳实践 使用SQL查询分析器可以建立和SQLServer的多个连接,可为对象生成脚本的功能 使用对象浏览工具来查找表和字段名,或为他们生成脚本 使用模板生成对象 运行批文件或执行重复性任务时,使用osql 把经常使用的Transact-SQL语句保存在文件中,可以以后使用。 2.试验A创建并执行Transact—SQL脚本 第4章数据查询 4.1使用select语句查询数据 4.1.1指定列 usenorthwind selectemployeeId,lastname,firstname,titlefromemployees go 使用where子句指定行 usenorthwind selectemployeeId,lastname,firstname,titlefromemployeeswhereemployeeid=5 go 4.1.2过滤数据 使用比较操作符=><>=<=<> usenorthwind selectlastname,cityfromemployeeswherecountry='usa' go usenorthwind selectorderid,customeridfromorderswhereorderdate>'1996-07-04' go 使用字符比较符like %0个或多个字符串 _任何单个的字符 []在指定区域或集合内的任何单个字符 [^]不在指定区域或集合内的任何单个字符 use northwind selectcompanynamefromcustomerswherecompanynamelike'%Restaurant%' 4.1.3使用逻辑操作符 usenorthwind selectproductid,productname,supplierid,unitpricefromproducts where(productnamelike'T%'orproductid=46) and(unitprice>16.00) usenorthwind selectproductid,productname,supplierid,unitpricefromproducts whereproductnamelike'T%'or(productid=46 andunitprice>16.00) 4.1.4查询在一定范围内的值 usenorthwind selectproductname,unitpricefromproducts whereunitpricebetween10and20 go 包括10和20 usenorthwind selectproductname,unitpricefromproducts where(unitprice>10)and(unitprice<20) go 不包括10和20 尽量使用between而不使用and和比较操作符表示的表达式 如果想返回不在指定区域的行时,使用notbetween。 这样会降低数据查询的速度。 4.1.5使用值的列表作为搜索条件 usenorthwind selectcompanyname,countryfromsuppliers wherecountryin('japan','italy') go 使用in或使用由or操作符连接起来的一系列比较操作符,SQLServer在处理他们的方式相同。 不要在搜索条件结尾使用NULL,这将返回意想不到的结果。 可以使用notin,降低数据查询速度。 4.1.6查询未知的值 usenorthwind selectcompanyname,faxfromsuppliers wherefaxisnull 使用isnotnull来查询指定列中非空的行 4.2格式化结果集 4.2.1给数据排序 selectproductid,productname,categoryid,unitpricefromproducts orderbycategoryid,unitpricedesc selectproductid,productname,categoryid,unitpricefromproducts orderby3,4desc asc升序 desc降序 sp_helpsort查看在安装过程中 4.2.2消除重复的行 usenorthwind selectdistinctcountryfromsuppliersorderbycountry go 4.2.3改变字段的名字 usenorthwind selectfirstnameasfirst,lastnameasLast,employeeidas'employeeID' fromemployees go 4.2.4使用符号 符号可能是字母,数字或标识,在结果集中,他们被用作特定的值,以增加结果集的可读性。 selectfirstname,lastname,'Identificationnumber: ',employeeidfromemployees 4.3查询的工作原理 非缓存的查询 所有查询在执行前,都需要经过一下步骤: 解释、解析、优化和编译 解析----检查语句的语法是否正确 解析----校验语句中出现的对象名称是否有效,同时检查对象的所有权的权限 优化----检查是否能够使用索引
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 语句