经典完整SQL文文档大全.docx
- 文档编号:23267296
- 上传时间:2023-05-15
- 格式:DOCX
- 页数:33
- 大小:134.70KB
经典完整SQL文文档大全.docx
《经典完整SQL文文档大全.docx》由会员分享,可在线阅读,更多相关《经典完整SQL文文档大全.docx(33页珍藏版)》请在冰豆网上搜索。
经典完整SQL文文档大全
DB2周习总结
摘要:
本文档的知识和操作基本上基于DB2数据库。
目录:
——数据库的基本操作
——数据库表的基本操作
——数据操作和事物处理
——内置函数
——多表查询
——子查询
——其他数据库对象(视图、索引、序列)
——存储过程及触发器
一:
数据库的基本操作
1、创建数据库
db2=>createdatabasestudent
2、删除数据库
db2=>dropdatabasestudent
3、连接数据库
db2=>connecttostudentuserdb2adminusingdb2admin
4、中断数据库
Disconnectstudent
二:
数据库表的基本操作
1、创建表
db2=>createtables(snointnotnull,snamechar(20),sexchar
(2))
db2=>createtablec(cnointnotnull,cnamechar(20),teacherchar(20))
db2=>createtablesc(snointnotnull,cnointnotnull,scorefloat)
2、删除表
db2=>Droptables
3、添加/删除主键和外键
添加主键
方法一:
db2=>altertablescaddprimarykey(sno,cno)//单表创建主键
方法二:
db2=>createtables(snointnotnullprimarykey,snamechar(20),sexchar
(2))
方法三:
db2=>createtables(snointnotnull,snamechar(20),sexchar
(2),constrainttable_nameprimarykey(sno))
添加外键:
方法一:
db2=>altertablescforeignkey(sno)referencess(sno)//要成功的将s(sno)设置成外键必须将sno设置成另一张表sc的主键
方法二:
db2=>createtablesc(snointnotnullprimarykey,cnointnotnull,scorefloat,constraints_c_fkforeignkey(sno)referencess(sno))
删除主键
db2=>altertablescdropprimarykey
删除外键
我们不可以直接删除外键。
解决方法:
我们先删除外键依赖表中的主键,即可删除外键。
有关ONDELETECASCADE和ONDELETESETNULL的用法:
db2=>altertablescaddforeignkey(sno)referencess(sno)ondeletecascade
db2=>select*froms
SNOSNAMESEX
-------------------------------
1001wangwanlim
db2=>select*fromsc
SNOCNOSCORE
-------------------------------------------
100134+9.00000000000000E+001
100144+4.50000000000000E+002
SQL执行文:
db2=>deletefromswheresno=1001
效果查看:
db2=>select*froms
SNOSNAMESEX
-------------------------------
0条记录已选择。
db2=>select*fromsc
CNOSNAMESCORE
-----------------------------------------
0条记录已选择。
4、约束
check约束
学生成绩必须是大于等于0并且小于150的数字(对输入表中的数字进行约束范围)
Altertabletbl_scoreinfoaddconstraintck_tblscoreinfo_scoreCHECK(score>=0andscore<=150)
notnull约束
①Altertabletbl_scoreinfomodify(scorenotnull)
②db2=>createtables(snointnotnullprimarykey,snamechar(20)constraintsna
me_nnotnull,sexchar
(2))
Unique约束
db2=>altertablesaddconstraintsnameunique(sname)sname要不能为空的值才可以定义唯一性的约束
在db2对于唯一性语约束必须该字段不能为空且要有主键。
约束的删除
db2=>altertablescdropconstraintsc_score_check
5、视图
创建一个表student,和一个由student衍生出来的视图stu,stu取的是student表中学号和成绩形成的视图
⑴现在在表中student加入一个新的数据,在没有对视图stu进行主动添加数据的情况下,视图同步跟新了数据
db2=>select*fromstu
VNOVSCORE
----------------------
150
255
360
470
580
690
6条记录已选择。
db2=>select*fromstudent
NONAMESCORE
------------------------------------------
1A50
2B55
3C60
4D70
5E80
6F90
6条记录已选择。
db2=>insertintostudentvalues(7,'G',100)
db2=>select*fromstudent
NONAMESCORE
------------------------------------------
1A50
2B55
3C60
4D70
5E80
6F90
7G100
7条记录已选择。
db2=>select*fromstu
VNOVSCORE
----------------------
150
255
360
470
580
690
7100
7条记录已选择。
⑵在视图stu主动添加了一个数据,在没有主动跟新表的情况下,表自己主动跟新了数据,视图中没有的数据类型中的数据设置为空
db2=>insertintostuvalues(8,110)
db2=>select*fromstu
VNOVSCORE
----------------------
150
255
360
470
580
690
7100
8110
8条记录已选择。
db2=>select*fromstudent
NONAMESCORE
------------------------------------------
1A50
2B55
3C60
4D70
5E80
6F90
7G100
8-110
⑶在表中删除了一个数据学号为8的数据,没有主动对视图删除的情况下,视图自动同步删除了这个数据
db2=>deletefromstudentwhereno=8
db2=>select*fromstudent
NONAMESCORE
------------------------------------------
1A50
2B55
3C60
4D70
5E80
6F90
7G100
7条记录已选择。
db2=>select*fromstu
VNOVSCORE
----------------------
150
255
360
470
580
690
7100
7条记录已选择。
⑷在视图删除了一个数据学号为7的数据,表中同步删除
db2=>deletefromstuwherevno=7
db2=>select*fromstu
VNOVSCORE
----------------------
150
255
360
470
580
690
6条记录已选择。
db2=>select*fromstudent
NONAMESCORE
------------------------------------------
1A50
2B55
3C60
4D70
5E80
6F90
6条记录已选择。
notnull约束
db2=>createtables(snointnotnullprimarykey,snamechar(20)constraintsna
me_nnotnull,sexchar
(2))
Unique约束
db2=>altertablesaddconstraintUnique_nameunique(sname)
在db2对于唯一性语约束必须该字段不能为空且要有主键。
check约束
db2=>altertablescaddconstraintsc_score_checkcheck(score>0andscore<100)
db2=>altertablescdropconstraintsc_score_check
三:
数据操作和事物处理
1、数据的操作
增加
db2=>insertintos(sno,sname,sex)values(1001,'wangwanli','m')
db2=>select*froms
SNOSNAMESEX
-------------------------------
1001wangwanlim
1条记录已选择。
删除
db2=>deletefromswheresno=1001
db2=>select*froms
SNOSNAMESEX
-------------------------------
0条记录已选择。
db2=>select*froms
SNOSNAMESEX
----------------------------------
1001wangwanlim
1条记录已选择。
更新
db2=>updatessetsname='23342'wheresno=1001
db2=>select*froms
SNOSNAMESEX
----------------------------------
100123342m
1条记录已选择。
2、锁
⑴老师曾讲过这样一条命令:
listcommandoptions
⑵大家注意第二行有个“-c”命令,系统默认的是“ON”,即自动提交,现在把它关闭,用到的语句是:
updatecommandoptionsusingcoff
⑶建一张简单的表作为例子
⑷然后加锁,SQL文为:
select*fromemployeewhereemployee_id=’SH0002’forupdate
⑸关键部分到了!
!
!
此时若你打开另一个窗口,进行update操作,是可以更新的
为什么呢?
因为用户1没有更新数据,用户2看的数据与数据库中的数据是一致的
下面用户1作一个update操作
此时用户2再做update操作,我们来看一下效果
然后用户1commit一下
这又是为什么呢?
因为用户1已经更新过了,用户2看到的数据和数据库中实际的数据已经不一致了
还要注意的一点是,此时的数据是用户2更新过后的数据,因为用户2更新数据后会把用户1更新的数据覆盖住
说明:
在DB2中是通过“快照”来实现锁的,当用户1没有更新数据时,只有一个快照,用户2看到的数据是原来的数据;当用户1更新数据后,就产生了两个快照,用户2看到的数据与实际的已不一致
四:
内置函数
函数表
函数名 函数解释 函数举例
AVG() 返回一组数值的平均值. SELECTAVG(SALARY)FROMBSEMPMS;
CORR(),CORRELATION()返回一对数值的关系系数. SELECTCORRELATION(SALARY,BONUS)FROMBSEMPMS;
COUNT() 返回一组行或值的个数. SELECTCOUNT(*)FROMBSEMPMS;
COVAR(),COVARIANCE() 返回一对数值的协方差. SELECTCOVAR(SALARY,BONUS)FROMBSEMPMS;
MAX() 返回一组数值中的最大值. SELECTMAX(SALARY)FROMBSEMPMS;
MIN() 返回一组数值中的最小值. SELECTMIN(SALARY)FROMBSEMPMS;
STDDEV() 返回一组数值的标准偏差. SELECTSTDDEV(SALARY)FROMBSEMPMS;
SUM() 返回一组数据的和. SELECTSUM(SALARY)FROMBSEMPMS;
VAR(),VARIANCE() 返回一组数值的方差. SELECTVARIANCE(SALARY)FROMBSEMPMS;
ABS(),ABSVAL() 返回参数的绝对值. SELECTABS(-3.4)FROMBSEMPMS;
ACOS() 返回参数的反余弦值. SELECTACOS(0.9)FROMBSEMPMS;
ASCII() 返回整数参数最左边的字符的ASCII码. SELECTASCII('R')FROMBSEMPMS;
ASIN() 返回用弧度表示的角度的参数的反正弦函数. SELECTASIN(0.9)FROMBSEMPMS;
ATAN() 返回参数的反正切值,该参数用弧度表示的角度的参数. SELECTATAN(0.9)FROMBSEMPMS;
ATAN2() 返回用弧度表示的角度的X和Y坐标的反正切值. SELECTATAN2(0.5,0.9)FROMBSEMPMS;
BIGINT() 返回整型常量中的数字或字符串的64位整数表示. SELECTBIGINT(EMP_NO)FROMBSEMPMS;
CEILING()ORCEIL() 返回比参数大或等于参数的最小的整数值. SELECTCEILING(3.56)FROMBSEMPMS; SELECTCEIL(4.67)FROMBSEMPMS;
CHAR() 返回日期时间型,字符串,整数,十进制或双精度浮点数的字符串表示. SELECTCHAR(SALARY,',')FROMBSEMPMS;
CHR() 返回具有由参数指定的ASCII码的字符. SELECTCHAR(167)FROMBSEMPMS;
CONCAT() 返回两个字符串的连接. SELECTCONCAT(EMP_NO,EMP_NAM)FROMBSEMPMS;
YEAR() 返回数值的年部分. SELECTYEAR('2003/01/02')FROMBSEMPMS;
VARCHAR() 返回字符串,日期型,图形串的可变长度的字符串表示. SELECTVARCHAR(EMP_NAM,50)FROMBSEMPMS;
UCASE()ORUPPER() 返回字符串的大写. SELECTUCASE(EMP_NAM)FROMBSEMPMS; SELECTUPPER(EMP_NO)FROMBSEMPMS;
TRUNCATE()ORTRUNC() 从表达式小数点右边的位置开始截断并返回该数值. SELECTTRUNCATE(345.6789,2)FROMBSEMPMS;
TIME() 返回一个数值中的时间. SELECTTIME('2001-03-19.12.30.123456')FROMBSEMPMS;
SUBSTR(EXP1,EXP2) 返回EXP1串自EXP2处开始的子串. SELECTSUBSTR('CDNJFDJFJD',5)FROMBSEMPMS; SELECTSUBSTR('CDNJFDJFJD',5,2)FROMBSEMPMS;
SQRT() 返回该参数的平方根. SELECTSQRT(36)FROMBSEMPMS;
SPACE() 返回由参数指定的长度,包含空格在内的字符串. SELECTSPACE(10)FROMBSEMPMS;
SECOND() 返回一个数值的秒部分. SELECTSECOND('18:
34:
32')FROMBSEMPMS;
RTRIM() 删除字符串尾部的空格. SELECTRTRIM('COMMENT')FROMBSEMPMS;
ROUND(EXP1,EXP2) 返回EXP1小数点右边的第EXP2位置处开始的四舍五入值. SELECTROUND(2345.6789,2)FROMBSEMPMS
REPLACE(EXP1,EXP2,EXP3) 用EXP3替代EXP1中所有的EXP2 SELECTCHAR(REPLACE('ROMANDD','NDD','CCB'),10)FROMBSEMPMS;
REPEAT(EXP1,EXP2) 返回EXP1重复EXP2次后的字符串. SELECTCHAR(REPEAT('REPEAT',3),21)FROMBSEMPMS;
REAL() 返回一个数值的单精度浮点数表示. SELECTREAL(10)FROMBSEMPMS;
RAND() 返回0和1之间的随机浮点数. SELECTRAND()FROMBSEMPMS;
POWER(EXP1,EXP2) 返回EXP1的EXP2次幂. SELECTPOWER(2,5)FROMBSEMPMS;
POSSTR(EXP1,EXP2) 返回EXP2在EXP1中的位置. SELECT('ABCDEFGH','D')FROMBSEMPMS;
NULLIF(EXP1,EXP2) 如果EXP1=EXP2,则为NULL,否则为EXP1
NODENUMBER() 返回行的分区号. SELECTNODENUMBER(EMP_NO)FROMBSEMPMS;
MONTH() 返回一个数值的月部分. SELECTMONTH('2003/10/20')FROMBSEMPMS;
MOD(EXP1,EXP2) 返回EXP1除以EXP2的余数. SELECTMOD(20,8)FROMBSEMPMS;
MINUTE() 返回一个数值的分钟部分. SELECTMINUTE('18:
34:
23')FROMBSEMPMS;
LTRIM() 删除字符串前面的空格. SELECTLTRIM('CDDD')FROMBSEMPMS;
HOUR() 返回一个数值的小时部分. SELECTHOUR('18:
34:
23')FROMBSEMPMS;
DOUBLE() 如果参数是一个数字表达式,返回与其相对应的浮点数,如果参数是字符串表达式,则返回该数的字符串表达式. SELECTDOUBLE('5678')FROMBSEMPMS;
EXP() 返回参数的指数函数. SELECTEXP
(2)FROMBSEMPMS;
FLOAT() 返回一个数的浮点表示. SELECTFLOAT(789)FROMBSEMPMS;
FLOOR() 返回小于或等于参数的最大整数. SLECTFLOOR(88.93)FROMBSEMPMS;
HEX() 返回一个表示为字符串的值的16进制表示. SELECTHEX(16)FROMBSEMPMS;
五多表查询
测试数据
db2=>select*froms
SNOSNAMESEX
----------------------------------
23jsjWU
100123drtem
2条记录已选择。
db2=>select*fromc
CNOCNAMETEACHER
---------------------------------------------------
23jsjWUQ
1条记录已选择。
1、leftouterjoin
db2=>select*fromsleftouterjoincons.sno=o
SNOSNAMESEXCNOCNAMETEACHER
---------------------------------------------------------------------------
23jsjWU23jsjWUQ
100123drtem---
2条记录已选择。
2、rightouterjoin
db2=>select*fromsrightouterjoincons.sno=o
SNOSNAMESEXCNOCNAMETEACHER
---------------------------------------------------------------
23jsjWU23jsjWUQ
3、fullouterjoin
db2=>select*fromsfullouterjoinco
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 经典 完整 SQL 文档 大全