经典完整SQL文文档大全

DB2 周习总结周习总结 摘要 本文档的知识和操作基本上基于摘要 本文档的知识和操作基本上基于 DB2 数据库 数据库 目录 目录 数据库的基本操作数据库的基本操作 数据库表的基本操作数据库表的基本操作 数据操作和事物处理数据操作和事物处理 内置函数内置函数 多表查询多表查询 子查询子查询 其他数据库对象 视图 索引 序列 其他数据库对象 视图 索引 序列 存储过程及触发器存储过程及触发器 一 数据库的基本操作一 数据库的基本操作 1 创建数据库 创建数据库 db2 create database student 2 删除数据库删除数据库 db2 drop database student 3 连接数据库连接数据库 db2 connect to student user db2admin using db2admin 4 中断数据库中断数据库 Disconnect student 二 数据库表的基本操作二 数据库表的基本操作 1 创建表创建表 db2 create table s sno int not null sname char 20 sex char 2 db2 create table c cno int not null cname char 20 teacher char 20 db2 create table sc sno int not null cno int not null score float 2 删除表删除表 db2 Drop table s 3 添加添加 删除主键和外键删除主键和外键 添加主键添加主键 方法一 db2 alter table sc add primary key sno cno 单表创建主键 方法二 db2 create table s sno int not null primary key sname char 20 sex char 2 方法三 db2 create table s sno int not null sname char 20 sex char 2 constraint table name primary key sno 添加外键 添加外键 方法一 db2 alter table sc foreign key sno references s sno 要成功的将 s sno 设置成 外键必须将 sno 设置成另一张表 sc 的主键 方法二 db2 create table sc sno int not null primary key cno int not null score float constraint s c fk foreign key sno references s sno 删除主键删除主键 db2 alter table sc drop primary key 删除外键删除外键 我们不可以直接删除外键 解决方法 我们先删除外键依赖表中的主键 即可删 除外键 有关 ON DELETE CASCADE 和和 ON DELETE SET NULL 的用法 的用法 db2 alter table sc add foreign key sno references s sno on delete cascade db2 select from s SNOSNAME SEX 1001 wangwanli m db2 select from sc SNO CNO SCORE 1001 34 9 00000000000000E 001 1001 44 4 50000000000000E 002 SQL 执行文 db2 delete from s where sno 1001 效果查看 db2 select from s SNO SNAME SEX 0 条记录已选择 db2 select from sc CNO SNAME SCORE 0 条记录已选择 4 约束约束 check 约束约束 学生成绩必须是大于等于0并且小于150的数字 对输入表中的数字进行约束范围 Alter table tbl scoreinfo add constraint ck tblscoreinfo score CHECK score 0 and score create table s sno int not null primary key sname char 20 constraint sna me n not null sex char 2 Unique 约束约束 db2 alter table s add constraint sname unique sname sname 要不能为空的值 才可以定义唯一性的约束 在 db2 对于唯一性语约束必须该字段不能为空且要有主键 约束的删除约束的删除 db2 alter table sc drop constraint sc score check 5 5 视图视图 创建一个表student 和一个由student衍生出来的视图stu stu取的是student表中 学号和成绩形成的视图 现在在表中student加入一个新的数据 在没有对视图stu进行主动添加数据的 情况下 视图同步跟新了数据 db2 select from stu VNO VSCORE 1 50 2 55 3 60 4 70 5 80 6 90 6 条记录已选择 db2 select from student NO NAME SCORE 1 A 50 2 B 55 3 C 60 4 D 70 5 E 80 6 F 90 6 条记录已选择 db2 insert into student values 7 G 100 db2 select from student NO NAME SCORE 1 A 50 2 B 55 3 C 60 4 D 70 5 E 80 6 F 90 7 G 100 7 条记录已选择 db2 select from stu VNO VSCORE 1 50 2 55 3 60 4 70 5 80 6 90 7 100 7 条记录已选择 在视图stu主动添加了一个数据 在没有主动跟新表的情况下 表自己主动跟新了 数据 视图中没有的数据类型中的数据设置为空 db2 insert into stu values 8 110 db2 select from stu VNO VSCORE 1 50 2 55 3 60 4 70 5 80 6 90 7 100 8 110 8 条记录已选择 db2 select from student NO NAME SCORE 1 A 50 2 B 55 3 C 60 4 D 70 5 E 80 6 F 90 7 G 100 8 110 在表中删除了一个数据学号为8的数据 没有主动对视图删除的情况下 视图自动同 步删除了这个数据 db2 delete from student where no 8 db2 select from student NO NAME SCORE 1 A 50 2 B 55 3 C 60 4 D 70 5 E 80 6 F 90 7 G 100 7 条记录已选择 db2 select from stu VNO VSCORE 1 50 2 55 3 60 4 70 5 80 6 90 7 100 7 条记录已选择 在视图删除了一个数据学号为7的数据 表中同步删除 db2 delete from stu where vno 7 db2 select from stu VNO VSCORE 1 50 2 55 3 60 4 70 5 80 6 90 6 条记录已选择 db2 select from student NO NAME SCORE 1 A 50 2 B 55 3 C 60 4 D 70 5 E 80 6 F 90 6 条记录已选择 not null约束约束 db2 create table s sno int not null primary key sname char 20 constraint sna me n not null sex char 2 Unique 约束约束 db2 alter table s add constraint Unique name unique sname 在 db2 对于唯一性语约束必须该字段不能为空且要有主键 check 约束约束 db2 alter table sc add constraint sc score check check score 0 and score alter table sc drop constraint sc score check 三三 数据操作和事物处理数据操作和事物处理 1 数据的操作数据的操作 增加 db2 insert into s sno sname sex values 1001 wangwanli m db2 select from s SNO SNAME SEX 1001 wangwanli m 1 条记录已选择 删除 db2 delete from s where sno 1001 db2 select from s SNO SNAME SEX 0 条记录已选择 db2 select from s SNO SNAME SEX 1001 wangwanli m 1 条记录已选择 更新 db2 update s set sname 23342 where sno 1001 db2 select from s SNO SNAME SEX 1001 23342 m 1 条记录已选择 2 锁锁 老师曾讲过这样一条命令 list command options 大家注意第二行有个 c 命令 系统默认的是 ON 即自动提交 现在把它关 闭 用到的语句是 update command options using c off 建一张简单的表作为例子 然后加锁 SQL 文为 select from employee where employee id SH0002 for update 关键部分到了 此时若你打开另一个窗口 进行 update 操作 是可以更新的 为什么呢 因为用户 1 没有更新数据 用户 2 看的数据与数据库中的数据是一致的 下面用户 1 作一个 update 操作 此时用户 2 再做 update 操作 我们来看一下效果 然后用户 1 commit 一下 这又是为什么呢 因为用户 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 OR CEIL 返回比参数大或等于参数的最小的整数值 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 OR UPPER 返回字符串的大写 SELECT UCASE EMP NAM FROMBSEMPMS SELECTUPPER EMP NO FROMBSEMPMS TRUNCATE OR TRUNC 从表达式小数点右边的位置开始截断并返回该数值 SELECTTRUNCATE 345 6789 2 FROMBSEMPMS TIME 返回一个数值中的时间 SELECTTIME 2001 03 19 12 30 123456 FROMBSEMPMS SUBSTR EXP1 EXP2 返回 EXP1 串自 EXP2 处开始的子串 SELECT SUBSTR CDNJFDJFJD 5 FROM BSEMPMS SELECT SUBSTR CDNJFDJFJD 5 2 FROM BSEMPMS 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 from s SNO SNAME SEX 23 jsj WU 1001 23drte m 2 条记录已选择 db2 select from c CNO CNAME TEACHER 23 jsj WUQ 1 条记录已选择 1 leftleft outerouter joinjoin db2 select from s left outer join c on s sno o SNO SNAME SEX CNO CNAME TEACHER 23 jsj WU 23 jsj WUQ 1001 23drte m 2 条记录已选择 2 rightright outerouter joinjoin db2 select from s right outer join c on s sno o SNO SNAME SEX CNO CNAME TEACHER 23 jsj WU 23 jsj WUQ 3 fullfull outerouter joinjoin db2 select from s full outer join c on s sno o SNO SNAME SEX CNO CNAME TEACHER 23 jsj WU 23 jsj WUQ 1001 23 drte m 2 条记录已选择 4 4 innerinner joinjoin db2 select from s inner join c on s sno o SNO SNAME SEX CNO CNAME TEACHER 23 jsj WU 23 jsj WUQ 1 条记录已选择 数据来自其他表 主要说明一下Union all 和 Union 的用法 db2 select from s student c class where student sno between student sno and o union all select from s c where s sno o SNO SNAME SEX CNO 1 ww n 1 2 ww n 2 1 ww n 1 1 ww n 2 1 ww n 4 2 ww n 2 2 ww n 4 3 ww n 4 8 条记录已选择 db2 select from s student c class where student sno between student sno and o union select from s c where s sno o SNO SNAME SEX CNO 1 ww n 1 1 ww n 2 2 ww n 2 1 ww n 4 2 ww n 4 3 ww n 4 6 条记录已选择 六 子查询六 子查询 1 查找查找 23drte 学生的学生的 jsj 老师老师 db2 select c teacher from c sc where sc sno in select sno from s where sname 23drte and o o TEACHER WUQ 1 条记录已选择 PS In 和 Exists 使用时候数据量的区别 用 in 的话 是 n 前面的字段和后面的嵌套数据集进行比较 而我们用 exists 时候 比较的 不仅仅是 in 前面的字段 故一般情况下 Exits 操作出来的结果大于等于 in 出来的结果 2 Any db2 select c teacher from c sc where sc sno any select sno from s where sname 23drte and o o TEACHER WUQ 1 条记录已选择 3 All db2 select c teacher from c sc where sc sno all select sno from s where sname 23drte and o o TEACHER WUQ 1 条记录已选择 注 查询是一门高深的学问与技术 具体的问题需要自己用心去思考 七 其他数据库对象七 其他数据库对象 1 视图视图 视图的创建 db2 create view s view s min s max s avg as select min score max score avg score from sc db2 select from s view S MIN S MAX S AVG 9 00000000000000E 001 4 50000000000000E 002 2 70000000000000E 002 1 条记录已选择 2 索引索引 索引的作用 优化数据库 数据库索引好比是一本书前面的目录 能加快数据库的查询速度 例如这样一个查询 select from table1 where id 44 如果没有索引 必须遍历 整个表 直到ID等于44的这一行被找到为止 有了索引之后 必须是在ID这一列上建立的索 引 直接在索引里面找 44 也就是在ID这一列找 就可以得知这一行的位置 也就是 找到了这一行 可见 索引是用来定位的 索引分为聚簇索引和非聚簇索引两种 聚簇索引 是按照数据存放的物理位置为顺序的 而非聚簇索引就不一样了 聚簇索引能提高多行检索的速度 而非聚簇索引对于单行的检 索很快 创建创建 db2 create index s index on s sno sname sex 八八 存储过程及触发器存储过程及触发器 1 存储过程存储过程 常用的控制语句语法常用的控制语句语法 1 if 语句语句 If 判断条件判断条件 then 语句块 语句块 Elseif 判断条件判断条件 then 语句块 语句块 Else 语句块 语句块 End if 2 case 语句语句 Case when 判断条件判断条件 then 语句块 语句块 When 判断条件判断条件 then 语句块 语句块 Else 语句块 语句块 End case 3 while 语句语句 While 判断条件判断条件 do 语句块语句块 end while 示例 示例 1 根据工资 设置税收求得实际工资和应纳税 根据工资 设置税收求得实际工资和应纳税 db2 create procedure pw s varchar 20 begin declare a decimal 8 2 set a select salary from employee where name s if a 10000 then update employee set salary salary 0 8 tax salary 0 2 where name s elseif a 5000 then update employee set salary salary 0 9 tax salary 0 1 where name s else update employee set salary salary 500 tax 0 where name s end if end elseif 必须连在一起 db2 select from employee NO NAME SALARY TAX 1 wjw 12000 00 2 fzx 6000 00 3 chx 3000 00 3 条记录已选择 db2 call pw wjw 返回状态 0 db2 call pw fzx 返回状态 0 db2 call pw chx 返回状态 0 db2 select from employee NO NAME SALARY TAX 1 wjw 9600 00 2400 00 2 fzx 5400 00 600 00 3 chx 3500 00 0 00 3 条记录已选择 2 插入一条数据 插入一条数据 db2 create procedure t sno char 2 sname varchar 20 ssalary decimal 8 2 begin insert into employee no name salary values sno sname ssalary end DB20000I SQL 命令成功完成 db2 call t 4 wjg 20000 返回状态 0 db2 select from employee NO NAME SALARY TAX 1 wjw 9600 00 2400 00 2 fzx 5400 00 600 00 3 chx 3500 00 0 00 4 wjg 20000 00 4 条记录已选择 3 case 语句语句 根据分数判断等级根据分数判断等级 db2 create procedure p9 s char 2 begin declare b int set b select score from student where stuno s case when b 80 then update student set dj yx wherestuno s when b 60 then update student set dj jg where stuno s else update student set dj bjg where stuno s end case end db2 select from student STUNO SCORE DJ 1 90 2 70 3 50 3 条记录已选择 db2 call p9 1 返回状态 0 db2 call p9 2 返回状态 0 db2 call p9 3 返回状态 0 db2 select from student STUNO SCORE DJ 1 90 yx 2 70 jg 3 50 bjg 3 条记录已选择 db2 create procedure pw a int begin while a 4 do set a 1 end while return a end db2 call pw 5 返回状态 1 切记语法结构 开始必须有 BEGIN 2 创建触发器 创建触发器 before 触发器触发器 db2 create trigger t1 before update of stuno on epo referencing new n for eac h row select from epogz db2 DROP TRIGGER T1 DB20000I SQL 命令成功完成 AFTER 触发器 UPDATE 触发 db2 create trigger t1 AFTER update of stuno on epo referencing new n OLD O fo r each row UPDATE EPOGZ SET STUNO N STUNO WHERE STUNO O STUNO 当 修改 epo 表中 stuno 列 同时修改 epogz 表中的 stuno db2 update epo set stuno A002 WHERE STUNO A001 db2 SELECT FROM EPOGZ STUNO SALARY A002 5000 00 A002 5500 00 B002 5800 00 B001 8000 00 4 条记录已选择 db2 sql0696 SQL0696N 触发器 的定义包括相关名或转换表名 的 无效使用 原因码 说明 触发器定义中无效使用了 的值指示如下特定问题 1 DELETE 触发器中不允许 NEW 相关名和 NEW TABLE 名 2 INSERT 触发器中不允许 OLD 相关名和 OLD TABLE 名 3 BEFORE 触发器中不允许 OLD TABLE 名和 NEW TABLE 名 DELETE 触发触发 只用到 old 表 触发器的一个练习 删除的数据直接到另一个表中 db2 create trigger tr4 after delete on employee referencing old as o for each row insert into employee his values o no o name o salary o tax sysdate db2 create trigger t1 AFTER delete on epo referencing OLD O for each row del ete from EPOGZ WHERE STUNO O STUNO 清除 epo 中一行是同时清除 epogz 的相 同 stuno 的行 db2 SELECT FROM EPO STUNO STUNAME AGE A002 WJG 24 A002 XWH 24 B002 WJY 22 B001 SY 24 4 条记录已选择 db2 SELECT FROM EPOGZ STUNO SALARY A002 5000 00 A002 5500 00 B002 5800 00 B001 8000 00 4 条记录已选择 db2 DELETE FROM EPO WHERE STUNO A001 SQL0100W 找不到进行 FETCH UPDATE 或 DELETE 操作的行 或者查询的结果是一个空表 SQLSTATE 02000 db2 DELETE FROM EPO WHERE STUNO B001 db2 SELECT FROM EPO STUNO STUNAME AGE A002 WJG 24 A002 XWH 24 B002 WJY 22 3 条记录已选择 db2 SELECT FROM EPOGZ STUNO SALARY A002 5000 00 A002 5500 00 B002 5800 00 3 条记录已选择 db2 create trigger t1 AFTER delete on epo referencing new n OLD O for each r ow delete from EPOGZ WHERE STUNO O STUNO DB21034E 该命令被当作 SQL 语句来处理 因为它是无效的 命令行处理器 命令 在 SQL 处理期间 它返回 SQL0696N 触发器 DB2ADMIN T1 的定义包括相关名或转换表名 N 的无效使用 原因码 1 LINE NUMBER 1 SQLSTATE 42898 INSERT 触发器触发器 只用到 new 表 db2 create trigger t1 AFTER insert on epo referencing new n for each row insert into epogz values n stuno 2500 db2 insert into epo values C001 WZ 20 db2 SELECT FROM EPO STUNO STUNAME AGE A002 WJG 24 A002 XWH 24 B0