从头到尾学习sql.docx
- 文档编号:30039833
- 上传时间:2023-08-04
- 格式:DOCX
- 页数:37
- 大小:33.97KB
从头到尾学习sql.docx
《从头到尾学习sql.docx》由会员分享,可在线阅读,更多相关《从头到尾学习sql.docx(37页珍藏版)》请在冰豆网上搜索。
从头到尾学习sql
第一节库操作及基本查询
/*库操作*//*SQL数据库管理*/
createtableemp
(
eidint,
enamevarchar(10),
salmoney/*不可给自动增加列赋值*/
/*列名列表要和值列表匹配*/
/*字符类型与日期类型加单引号*/
insertintoemp(sal,ename,eid)values(1234,'rose',1001)
/*删除表中数据*/
1。
droptableemp--最为彻底
2。
truncatetableemp--不带日志,效率较高
/*此两种方法不能被外键引用,不可带条件删除*/
3。
deletefromempwhere...deletefrom 表名where条件
/*不能删除被引用的数据*/
/*表重命名*/
sp_rename'emp','newemp'
/*列重命名*/
sp_rename'newemp.eid','neweid','COLUMN'
/*复制表结构(带数据)*/
select*intoempfromnewemp--自增与NOTNULL可复制,其他约束不
/*复制表结构(不带数据)*/
select*intoempfromnewempwhere1>2
/*复制部分列*/
selectclo1,col2,col3intoempfromnewemp
/*上三种中emp为select自动创建新表*/
/*查询指定表约束*/
sp_helpconstraintemp
/*执行指定的SQL指令*/
sp_executesqlN'select*fromemp'
/*N表示后面的字符串采用unicode编码体系(无论中英文均一字符双字节)*/
/*模糊查询like*/
select*fromempwhereeidnotlike'[0-8][0-8][0-12]'
/*中括号内代表‘一’位字符的范围,[0-12]即[0-1]||[0-2]*/
select*fromempwhereenamelike'w_' 下划线
/*"_"通配符,代表所有字符,就是麻将里的“混”*/
select*fromempwhereenamelike'w%'
/*"%"代表任意位的任意字符*/
select*fromempwherenotename='we'
/*not不等于*/
select*fromempwhereenameisnull
/*isnull不是=null!
*/
select*fromempwhereenameisnotnull
/*isnotnull不是notisnull*/
select*fromempwhereeid notin(2002)
/*in包含于*/
select*fromempwhereeidbetween1001and1002
/*between在两值之间的范围内取值*/
第二节表操作及约束
/*表操作*/
/*创建表*/
createtableemp
(
eidintprimarykey,--主键
enamesql_variant, --通用数据类型
flagtimestamp --时间戳
)
/*插入列*/
altertableempaddesexvarchar(10)default'nan'
/*插入数据*/
insertintoemp(eid,ename)values(1004,'accp')
insertintoemp(eid,ename)values(1005,getdate())
insertintoemp(eid,ename)values(1006,3.1415926)
/*更新数据*/
updateempsetename='app'whereeid=1004
/*约束*/
createtableemp
(
eidintconstraintpk_eidprimarykey,--主键约束
enamevarchar(10),
salnumeric(7,2)constraintck_salcheck(sal>0),--检查约束
selfidvarchar(18)constraintuq_idunique, --唯一约束
idatedatetimeconstraintdef_datadefaultgetdate()--默认约束
)
sp_pkeysemp--查看主键
sp_helpemp--查看约束
altertableempdropCK__emp__sal__78B3EFCA--删除约束
altertableempaddconstraintpk_idprimarykey(eid)--增加约束
/*外键操作*/
createtabledept--主表
(
didintprimarykey,
dnamevarchar(10),
)
createtableemp
(
eidintprimarykey,
enamevarchar(10),
dnointconstraintfk_didforeignkeyreferencesdept(did)onupdatecascade
/*ondeletenoaction无级连更新,删除时外键冲突报错并回滚delete*/
/*onupdatenoaction无级连更新,更新时外键冲突报错并回滚update*/
/*ondeletecascade删除时允许级连更新*/
/*onupdatecascade更新时允许级连更新*/
)
altertableempaddconstraintfk_didforeignkey(dno)referencesdept(did)--增加外键
第三节多表查询及聚合函数
/*查询实例*/
createtablecom
(
cidintprimarykey,
cnamevarchar(10),
ctelvarchar(20)
)--公司表
createtabledept
(
didintprimarykey,
dnamevarchar(10),
dtelvarchar(20),
cnointreferencescom(cid)
)--部门表
createtableemp
(
eidintprimarykey,
enamevarchar(10),
etelvarchar(10),
dnointreferencesdept(did)
)--员工表
/*插入数据*/
insertintocomselect1001,'sun','120'
unionselect1002,'IBM','130'
unionselect1003,'top','140'
unionselect1004,'MS','150'--union集合运算符,批量操作
insertintodeptselect2001,'财务部','110',1001
unionselect2002,'行政部','120',1001
unionselect2003,'组织部','130',1001
unionselect2004,'人事部','140',1001
insertintoempselect3001,'rose','110',2001
unionselect3002,'jack','120',2002
unionselect3003,'tom','130',2003
unionselect3004,'mike','140',2004
unionselect3005,'wilin','150',2002
/*多表连接查询*/
select*fromdeptleftouterjoinemponemp.dno=dept.did
--左外连接,左表的所有数据以及右表的匹配数据,不匹配显示NULL
select*fromempleftouterjoindeptonemp.dno=dept.didleftouterjoincomono=com.cid
--多表左连接
/*右连接*/
select*fromcomrightouterjoindeptoncom.cid=o
--右表中的所有数据,左表中的匹配数据,左表中不能和右表匹配的数据‘不显示’,
--右表中的数据如果在左表中找不到匹配数据,会在对应左表位置显示为NULL
/*内连接*/
createtableemployee
(
eidintprimarykey,
enamevarchar(10),
reporttointreferencesemployee(eid)
)
insertintoemployeeselect1001,'rose',null
unionselect1002,'will',1001
unionselect1003,'yao',1001
unionselect1004,'gigi',1002
select*fromemployeeeinnerjoinemployeemonm.eid=e.reportto
--显示员工与其直接上级的对应关系
/*聚合函数*/
createtablegoods
(
gidint,
gnamevarchar(10),
pricemoney
)
insertintogoodsselect1002,'accp',3456
unionselect1002,'bccp',56
unionselect1003,'cccp',456
/*错误事例*/
selectgid,avg(price)asavgpfromgoods
/*正确事例*/
selectgid,avg(price)asavgpfromgoodsgroupbygid
--单行与聚合不能一起使用,除非一句单行进行分组
/*错误事例*/
select*fromgoodswhereprice=max(price)
/*正确事例*/
select*fromgoodswhere price=(selectmax(price)fromgoods)
--聚合函数不能出现在where指令中
第四节多维数据集及计算列
createtableinventory
(
itemvarchar(10),
colorvarchar(10),
quantityint--库存表
)
insertintoinventoryselect'table','blue',124
unionselect'table','red',223
unionselect'table','red',211
unionselect'chair','blue',101
unionselect'chair','red',210
/*groupby,orderby*/
selectitem,color,sum(quantity)asqufrominventorygroupbyitem,colororderbyitemdesc
--只有item和color相同的行才能分在一个组中
/*withCUBE*/
selectitem,color,sum(quantity)asqufrominventorygroupbyitem,colorwithCUBEorderbyitemdesc
--CUBE运算符生成的结果集是多维数据集。
多维数据集是事实数据的扩展,事实数据即记录个别事件的数据。
--扩展建立在用户打算分析的列上。
这些列被称为维。
多维数据集是一个结果集,其中包含了各维度的所有可能组合的交叉表格。
--CUBE运算符在SELECT语句的GROUPBY子句中指定。
该语句的选择列表应包含维度列和聚合函数表达式。
--GROUPBY应指定维度列和关键字WITHCUBE。
结果集将包含维度列中各值的所有可能组合,以及与这些维度值组合相匹配的基础行中的聚合值。
/*withROLLUP*/
selectitem,color,sum(quantity)asqufrominventorygroupbyitem,colorwithROLLUPorderbyitemdesc
--CUBE和ROLLUP的区别在于:
--CUBE生成的结果集显示了所选列中值的所有组合的聚合
--ROLLUP生成的结果集显示了所选列中值的某一层次的聚合
--ROLLUP层次结构列语句中列的顺序由select中的列名列表顺序决定
/*计算列*/
createtableemp
(
eidintprimarykey,
joinyearint,--工龄
salmoney,--年薪
allsalassal*joinyear--总收入=工龄×年薪
)
--不需要给计算列提供值
insertintoemp(eid,joinyear,sal)values(1,5,10000)
select*fromemp
/*COMPUTE*/
selectitem,color,quantityfrominventoryorderbyquantityCOMPUTEsum(quantity),count(item)
--此语句有两个结果集是包含选择列表信息的所有明细行
--第二个结果集只有一行,其中包含COMPUTE子句中指定的聚合函数的合计
--多个聚合函数由逗号分割
--COMPUTE语句中orderby子句为可选
/*COMPUTEby*/
selectitem,color,quantityfrominventoryorderbyitemCOMPUTEsum(quantity)byitem
--当COMPUTE带有可选的BY子句时,符合select条件的每个组都有两个结果集
--每个组的第一个结果集是明细行集,其中包含该组的选择列表信息
--每个组的第二个结果集有一行,包含该组的COMPUTE子句中所指定的聚合函数小计
--必须先排序,后计算
--依据列表与排序依据列表匹配
--orderby子句必选
/*范式*/
/*1NF*/
--去除非依赖性
--保证所有的属性都与实体相关
/*2NF*/
--去除部分依赖性
--如果非关键属性只依赖于关键属性的一部分,非关键属性应去除掉
--例如有gid,wno,pid为组合键,而pname仅和pid相关,pname为非关键属性
--解决方法为将pid与pname分离为另一张表
/*3NF*/
--去除传递依赖性
--传递依赖指的是非关键属性的相互依赖
--如满足3NF则一定满足2NF,1NF
--如满足2NF则一定满足1NF
第五节批处理及case表达式
/*批处理定义*/
/*以一次执行一批命令中的一条命令的方式处理一组命令的过程被称为批处理*/
UsePubs--相当于快捷键alt+u
Select*fromauthors
Updateauthors
setphone='890451-7366'
whereau_lname='White'
Go
/*go标识批处理结束*/
/*waitfor*/
/*WAITFOR{DELAY'time'|TIME'time'}
DELAY是在完成WAITFOR语句之前等待的时间。
完成WAITFOR语句之前等待的时间最多为24小时。
TIME关键字后为time_to_execute,它指定WAITFOR语句要等待的时间。
*/
WAITFOR DELAY'00:
00:
02'SELECTEmployeeIDFROMNorthwind.dbo.Employees
--指定在执行SELECT语句之前等待两秒:
USEpubs
BEGIN
WAITFORTIME'22:
00'DBCCCHECKALLOC
END
--TIME关键字指定在10P.M以后对指定数据库pubs进行检查,以确保所有页的分配和使用正确
--一组要执行的T-SQL语句可以包含在BEGIN END中,相当于{}
/*while循环*/
WHILE(SELECTAVG(price)FROMgood)<4000
--市场平均价不得超过4000元
BEGIN
UPDATEgood
SETprice=price*2
--价格增加一倍
IF(SELECTMAX(price)FROMgood)>3000
--商品最高价格不得超过3000
BREAK
ELSE
CONTINUE
END
--市场价分析实例
/*CASE(有列名)*/
createtablegoods
(
gidintprimarykey,
gnamevarchar(10),
pricemoneydefault3000
)
insertintogoodsvalues(1001,'mp3',default)
insertintogoodsvalues(1002,'mp4',default)
insertintogoodsvalues(1001,'cpu',default)
selectgid,
casegname
when'mp3'then'显示器'
else'hd'
end
asgnamefromgoods
--上面六行仅为一条select语句
--CASE与end成对出现
--将or显示为oregon
--case仅修改显示内容,不修改表中实际内容
/*case表达式(无列名)*/
selectgid,
case
whengname='mp3'andgid=1001then'显示器'
elsegname
--上行的列名gname代表无操作
end
asgnamefromgoods
--case表达式与case的主要区别为case表达式可以加条件判断
/*update语句中使用case表达式*/
UPDATEpublishersSETstate=
CASE
WHENcountry<>'USA' THEN'--'
ELSEstate
END
city=
CASE
WHENpub_id='9999'THEN'LYON'
ELSEcity
END
WHEREcountry<>'USA'ORpub_id='9999'
--上面只有一条语句,不知道使用这样的缩进是把句子结构看清楚了?
还是更晕了?
/*定义变量*/
declare@resint,@avarchar(10)
--局部变量用一个@,全局变量用两个@
/*变量赋值*/
set@res=89
set@a='accp'
/*打印变量值*/
print@res
第六节交叉报表及事务
/*交叉报表*/
createtablepivot
(
year int,
quarter int,
amount real
)
insertintopivotvalues(1990,1,1.1)
insertintopivotvalues(1990,2,1.2)
insertintopivotvalues(1990,3,1.3)
insertintopivotvalues(1990,4,1.4)
insertintopivotvalues(1991,1,2.1)
insertintopivotvalues(1991,2,2.2)
insertintopivotvalues(1991,3,2.3)
insertintopivotvalues(1991,4,2.4)
select*frompivot
--原始结果集
selectyear,
sum(casequarterwhen1thenamountelse0end)asQ1,
sum(casequarterwhen2thenamountelse0end)asQ2,
sum(casequarterwhen3thenamountelse0end)asQ3,
sum(casequarterwhen4thenamountelse0end)asQ4
frompivot
groupbyyear
--行列互换后的结果集
/*
case:
如果季度的值为1,则转换为amount列,否则计0,并将次列起别名为Q1
sum:
求和,若符合条件则对该季度求和,不符合聚合0,
为0处不可为null,null将被sum和avg等聚合函数忽略,造成错误结果
*/
/*事务*/
/*
原子性:
事务的所有操作在数据库中要么全部正确反映出来,要么全部不反映
一致性:
事务隔离执行时(即在没有其他事务并发执行的情况下)
保持数据库的一致性
隔离性:
尽管多个事务可以并发执行,但系统保证,对于任何一对事务t1和t2,
在t1看来,t2或者在t1开始之前已经停止执行,或者在t1完成之后开始执行
这样,每个事务都感觉不到系统中有其他事务在并发的执行
持久性:
一个事务成功完成后,它对数据库的改变必须是永久的,即使是系统出现
故障时也是如此
*/
begintrant1
--开始事务,t1为事务名
createtableemp
(
empidintprimarykey,
enamevarchar(20),
etelint,
reporttointreferencesemp(empid)
)
rollbacktrant1
--回退,撤销事务的更改
insertintoempselect1001,'rose',110,null
uni
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 从头到尾 学习 sql
![提示](https://static.bdocx.com/images/bang_tan.gif)