sql数据库实例.docx
- 文档编号:25152463
- 上传时间:2023-06-05
- 格式:DOCX
- 页数:16
- 大小:21.49KB
sql数据库实例.docx
《sql数据库实例.docx》由会员分享,可在线阅读,更多相关《sql数据库实例.docx(16页珍藏版)》请在冰豆网上搜索。
sql数据库实例
数据库
use学生系统
--向student表中增加“入学时间”列,其数据类型是datetime
altertablestudentaddScomedatetime
--删除刚添加的入学时间字段scome
altertablestudentdropcolumnscome
--将sage列的数据类型改为int类型
altertablestudentaltercolumnsageint
--删除student表
droptablestudent
--修改student表将sno定义为非空
altertablestudentaltercolumnsnochar(20)notnull
------问题如何设置将sno列的约束条件设置为唯一?
?
?
altertablestudentaddconstraints_nounique(sno)
--将student表中的sno设置为主键
altertablestudentaddconstraintpk_snoprimarykey(sno)
--修改student表使sage默认为,ssex只能输入男或女
altertablestudentaddconstraintdf_agedefault20forsage,
constraintcsexcheck(ssexin('男','女'))
--删除students表中对学生性别的约束定义
altertablestudentdropconstraintcsex
--修改student表使ssex默认为男
altertablestudentaddconstraintsssexdefault'男'forssex
--创建ssc表,sno,cno联合做主键,sno、cno单独做外键,
--注意sno,cno在对应表中已经成为主键
createtablesc
(
snochar(20)foreignkeyreferencesstudent(sno),
cnochar(20)foreignkeyreferencescourse(cno),
gradeintconstraintpk_scprimarykey(sno,cno)
)
--修改sc表,设置sno的值要么从student表的sno中选取,要么为空。
另外还应该注意sc中的sno要与student中的sno的字符类型及长度保持一致
altertablescaddconstraintsc_snoforeignkey(sno)referencesstudent(sno)
--新建一个表时添加约束条件例
createtableff
(
snochar(20)notnullunique,
snamechar(20),
sagesmallintdefault20,
ssexchar
(2),
constraintc_sexcheck(ssexin('男','女'))
)
--建立索引,在三个表中建立索引,
--sc表中学号按升序排列,课程号按降序排列(clustered聚簇索引,nonclustered非聚簇索引)
--添加clustered(聚集索引),表中的数据行才能按数据存储
createclusteredindexff_indexAgeonff(useragedesc)
createuniqueindexstudentsnoonstudent(sno);
createuniqueindexcoucononcourse(cno);
createuniqueindexscnoonsc(snoasc,cnodesc);
--根据年龄进行逆序排列
select*fromtable_indexorderbyuseragedesc
--索引的删除注意在sql中需要“on表名”(验证过的)
dropindex索引名on表名
或者是
dropindex表名.索引名
--select语句查询经过计算的列,对列起别名
select2008-sageasbirthdatefromstudent
--引用字面值
selectsno,'born',2008-sagefromstudent
--查询所有系,并使系名唯一
selectdistinctsdeptfromstudent
--查询条件谓词
selectsno,cno,gradefromsc
wheregrade<60
wherenotgrade<60
--或者是wheregrade!
<60
wheregradebetween60and80
wheregradenotbetween60and80
wheregrade>=60andgrade<=80
wheresdeptin('is','ma','cs')
wheresdeptnotin('is','ma','cs')
wheregradeisnull
wheregradeisnotnull
wheresnamelike'李%'
wheresdeptlike'i?
'
wheresnamenotlike'李*'
--分组,使用集合函数
selectcno,count(sno),avg(grade)fromsc
groupbycno
--查询选修了三门以上的学生的学号
selectsno,count(cno)as人数fromsc
groupbysno
havingcount(*)>3
--连接查询
--等值连接
selectstudent.*,sc.*fromstudent,sc
wherestudent.sno=sc.sno
--非等值连接(就是把=换成<,>等)
selectstudent.*,sc.*fromstudent,sc
wherestudent.sno=sc.snoandsc.grade>60
--交叉连接(不带连接谓词的连接)
selectsno,cnofromstudent,course
--自身连接
selecta.*,b.*fromstudenta,studentb
wherea.sno=b.sno
--(查询和刘晨同在一个系学习的学生)
--注意这里一定要写成b.*,如果写成a.*结果将出错
selectb.*fromstudenta,studentb
wherea.sname='刘晨'anda.sdept=b.sdept
--但写成这样的结果就不会出错了,请注意观察其区别
selecta.*fromstudenta,studentb
whereb.sname='刘晨'anda.sdept=b.sdept
--外连接(left(outer)join,right(outer)join,full(outer)join)
--查询所有学生的选课情况,没有选课学生的记录也要显示出来
--(即把students表作为主表)
selectstudent.*,sc.*
fromstudentleftjoinsconstudent.sno=sc.sno
--复合条件连接(在where子句中使用多个条件的连接查询)
selectstudent.*fromstudent,sc
wherestudent.sno=sc.snoando='002'andgrade>90
--字符串连接查询
--查询每个学生的学号、姓名并把结果显示到一个列中
selectsno+snameasalllfromstudent
--一道综合题(查询所有成绩为优秀的学生的姓名),关键点是要注意空值,且勿忽略
selectsnamefromstudent,sc
wherestudent.sno=sc.snoand
student.snonotin
(selectsnofromscwheregradeisnull)
groupbysname
havingmin(grade)>=90
--子查询
--查年龄比平均年龄大的学生信息(Average缩写为avg)
select*fromstudentwheresage>(selectavg(sage)fromstudent)
--查询和刘晨同一年龄的学生信息(两种方法)
1.select*fromstudentwheresage=
(selectsagefromstudentwheresname='刘晨')
2.selecta.*fromstudenta,studentb
whereb.sname='刘晨'anda.sage=b.sage
--带有in的子查询(带有in是因为筛选出来的结果可能不止一条,当筛选出的结果是一条时可以用等号代替)
select*fromcourse
wherecnoin(selectdistinctcnofromsc)
--查询选修了课程名为’数据库‘的课程的学生信息(两种方法)
1.select*fromstudentwheresnoin
(selectsnofromscwherecnoin
(selectcnofromcoursewherecname='数据库'))
2.selectstudent.*
fromstudent,sc,course
wherestudent.sno=sc.snoando=o
andame='数据库'
-->any大于子查询结果中的某个值,即大于最小值(>min())
-->all大于子查询结果中的所有值,即大于最大值(>max())
-- -- --查询其他系中比is系某一学生年龄小的学生名单(两种方法) 1.select*fromstudent wheresage wheresdept='is')andsdept<>'is' 2.select*fromstudentwheresdeptnotin(selectsdeptfromstudentwheresdept='is')and sage<(selectmax(sage)fromstudentwheresdept='is') --查询跟学号为08001学生同姓的学生信息 --这里的left函数的作用是从名字最左端取第一个参数,即寻找同姓的学生 select*fromstudent wheresnamelike(selectleft(sname,1)fromstudentwheresno='08001')+'%' --查询所有选修了001课程的学生姓名(运用exists) selectsnamefromstudentwhereexists (select*fromscwheresno=student.snoandcno='001') --查询选修了所有课程的学生姓名和所在系 1.selectsname,sdeptfromstudent wherenotexists(select*fromcoursewherenotexists(select*fromscwheresno=student.snoandcno=o)) 2.selectsname,sdeptfromstudent wheresnoin(selectsnofromscgroupbysnohavingcount(*)=(selectcount(*)fromcourse)) --查询至少选修了08002学生选修的全部课程的学生学号,用distinct除去一样的(也就是说有一样的只选取一样中的一个) 1.selectdistinctsnofromsca wherenotexists(select*fromscbwhereb.sno='08002'andnotexists (select*fromsccwherec.sno=a.snoando=o)) 2.selectsnofromscwherecnoin(selectcnofromscwheresno='08002') groupbysnohavingcount(*)=(selectcount(*)fromscwheresno='08002') --用avg集函数统计所有学生的平均成绩 select(selectsum(sage)fromstudent)/count(*)fromstudent selectavg(sage)fromstudent --集合查询 --查询计算机系学生及学号为001学生的全部信息(并操作) select*fromstudentwheresdept='cs'unionselect*fromstudentwheresno='001' --并操作union交操作intersect差操作minus --查询选修了课程但没有选修课程的学生的集合的交差(注意第二个句子是错误的 --cno的值是而不是,这样的话,只要cno=001就符合条件了,cno<>'002'根本起不到作用) 1.selectsnofromscwherecno='001'andsnonotin(selectsnofromscwherecno='002') 2.selectsnofromscwherecno='001'andcno<>'002' --数据更新 --插入数据-插入一条选课记录(‘’,‘’) insertintosc(sno,cno)values('08020','001') --通过子查询向表中插入多条数据 insertintosc(sno,cno)selectsno,cnofromstudent,course --通过selectinto语句创建新表并插入多条数据,语法结构如下: --select<列名>,<列名>,。 。 。 <表达式>as<别名>,。 。 。 into<表名>from<表名>,<表名>,。 。 。 --where条件表达式groupby子句orderby子句 --下面的例子中的0指的是将sssc表中的grade值置为0 selectsno,cno,0asgradeintossscfromstudent,course --修改数据 --修改某一元组的值 updatestudentsetsage=22wheresno='08002' --修改多个元组的值 updatestudentsetsage=sage+1 --带子查询的修改语句————将计算机系的全部学生成绩置 updatescsetgrade=0wheresnoin(selectsnofromscwheresnoin(selectsnofromstudentwheresdept='IS')) updatescsetgrade=0wheresnoin(selectsnofromstudentwheresdept='is') --删除学号为08020的学生记录 deletefromstudentwheresno='08020' --删除计算机系所有学生的选课记录 deletefromscwheresnoin(selectsnofromstudentwheresdept='cs') --数据操作权限的设置 --授权语句的格式 --grant<权限>,<权限>...on<对象名>to<用户>,<用户>。 。 。 [withgrantoption] --把创建数据库和视图的权限授予用户noruser grantcreatedatabase,createviewtonoruser --把修改sno,查询student表的权限授予用户noruser grantupdate(sno),selectonstudenttonoruser --收回权限————其语法结构为 --revoke<权限>,<权限>...on<对象名>from<用户>,<用户>。 。 --把用户修改sno的权限收回 revokeupdate(sno)onstudentfromnorsuer --拒绝权限——格式为 --deny<权限>,<权限>...on<对象名>to<用户>,<用户>。 。 --拒绝用户创建数据库和视图的权限 denycreatedatabase,createviewtonoruser denyselectonstudentfromnoruser --定义视图 createviewvw_student as selectsno,sname,sagefromstudentwheresdept='cs' --视图的修改(最后一句的作用是限制对视图的更新操作不能超过视图条件限制) alterviewvw_student as select*fromstudent withcheckoption --查询视图和修改视图的方法与查询、修改基本表的方法一致 --删除视图 dropview<视图名> --存储过程 --创建存储过程([procedure) --不带参数的存储过程(书本例题)@@@@@@@ use学生系统 go ifexists(select*fromstudentwheresno='08002'andsage=20) dropprocproc_student go createprocproc_student as select*fromstudentwheresdept='is' go --带参数的存储过程(declare声明参数)(如果有错,参数加括号createprocproc_sstudent (@ssnamechar(10),@sssexchar (2)) ) use学生系统 go createprocproc_sstudent @ssnamechar(10),@sssexchar (2) as ifnotexists(select*fromstudentwheresname=@ssnameandssex=@sssex) begin print'不存在此人' return end select*fromstudentwheresname=@ssnameandssex=@sssex go --执行存储过程(1,2) use学生系统 executeproc_student go use学生系统 executeproc_sstudent'王江','男' go --查看存储过程 use学生系统 go sp_helptextproc_student go --修改存储过程————语法结构为 --recompile(再编译),encryption(编密码) alterproc存储过程名 [withrecompile|encryption|recompile,encryption] as sql语句 --删除存储过程 use学生系统 go dropprocproc_student go --触发器(trigger) --创建触发器的语法格式为: --createtrigger触发器名 --ontable|view --for|after|insteadofinsert,update,delete --as --.... --在数据库学生系统的student表上创建三个after触发器 use学生系统 go --第一个触发器 createtriggerinsert_student onstudentafterinsert as print'有新的同学加入哦' go --第二个触发器 createtriggerupdate_student onstudentafterupdate as print'有同学的信息被修改了哦' go --第三个触发器 createtriggerdelete_student onstudentafterdelete as print'有同学的信息被删除了哦' go --查看触发器————查看student表上的delete类型的触发器的信息 ()sp_help'触发器名称' ()sp_helptext: 用于查看触发器的正文信息 ()sp_depends: 用于查看触发器所引用或者指定的表涉及的所有触发器 sp_depends'触发器名称' sp_depends'表名' use学生系统 go sp_dependsdelete_student go --修改触发器 --altertrigger触发器名 --ontable|view --for|after|insteadofinsert,update,delete --as --sql语句 --删除触发器 use学生系统 go droptriggerdelete_student 新添内容; 1.--为年龄添加default约束,默认值为 altertableuserinfoaddconstraintdefault_agedefault(22)foruserage --删除对年龄的Default约束条件 altertableuserinfodropdefault_age --修改约束条件(思路,可以先删除这个列的约束条件,然后再给他添加约束条件) altertableuserinfoadduserage2intdefault(23) (注意,一般情况下不要一句话就这么搞定,因为删除时就出现问题,不让你删除这一列) 对于删除有约束条件的列时应该先删除该列的约束条件,
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sql 数据库 实例
![提示](https://static.bdocx.com/images/bang_tan.gif)