dongwaguan 2008-4-18 22:13
DB2笔记
ls -lrt
149 ps -af
150 df -k
151 bootinfo -y
152 ll *info*
153 db2 list db directory
154 db2 connect to zmccdev
155 lsdev -Cc memory
156 db2 list tables|pg
157 db2 "select * from CDR_CALL_HU_20070210"|pg
158 ps -af
159 lsattr -El mem0
160 db2 list tablespaces show detail
161 db2 load query table ST_ACCT_SHOULDITEM_MM
128 db2 connect zmcczt
129 db2 connect to zmcczt
130 ps -af
131 db2 list tablespaces show detal|grep -i name
132 db2 list tablespaces show detail
133 db2 list tablespaces show detail|grep -i name
134 db2 "select tabname from syscat.tables where tbspace='TBS20' order by tabname"
135 db2 "select tabname from syscat.tables where tbspace='TBS20' order by tabname"
136 db2 "select tabname from syscat.tables where tabname like 'ODS%'"
137 db2 "select tabname from syscat.tables where tabname like 'DWD%'"
138 db2 "select tabname from syscat.tables where tabname like 'DW%'"
139 db2 "select tabname from syscat.tables where tabname like 'DW%DM'"
140 db2 "select distinct op_time from DW_CUSTSVC_COMPLAIN_200602_DM order by op_time"
141 db2 "select distinct op_time from DW_CUSTSVC_COMPLAIN_200609_DM order by op_time"
142 db2 "select distinct op_time from DWD_CUSTSVC_AUTOTELFEE_200702_DM order by op_time"
1，联接到DB2数据库：1，db2 connect to zmccdev默认的是当前用户和密码方式登陆到数据库amccdev;
2, db2 connect to zmccdev user devy using devy'spasswd是devy用户和密码方式登陆到数据库amccdev;
2, 创建数据库表：db2 "create table devy_table2(id integer not null,name char(10),TEL integer)"
db2 "create table devy_table3(id smallint not null,name varchar(9),dept smallint with defult 10,job char(5),years smallint,salary decimal(7,2),comm decimal(7,2),birth_date date)"
3, 向表中添加记录：db2 "insert into devy_table1 values(1,'devy',11)"
db2 "insert into devy_table3(name,job,id) values('devy','qa',122),('jill','qa',110),('kiit','dev',109)"
db2 "insert into devy_table3(id,name) select id,name from devy_table1 where id=1"
4, 查询表中的记录：db2 "select * from devy_table1"
5, 更新表中一条记录：db2 "update devy_table1 set id=1,name='devy',TEL=13"
6, 在unix下执行一个sh 文件的命令是：sh devy_test.sh
7，导出一个表的内容：db2 "export to devy_test2 of del select * from devy_table1 where name='devy'"
db2 "export to /data01/work/devy/devy_test0315.del of del select * from devy_table3"
8，db2 "select count(name) as name,length('devyhefeng') length from devy_table3"
9，把一个文件内容导入到表中：db2 "import from /data01/work/devy/devy_test0315.del of del insert into devy_table1"
db2 "import from /data01/work/devy/devy_test0315.del of del replace into devy_table2"
db2 "load from /data01/work/devy/devy_test0315.del of del replace into devy_table2"
1,启动实例：db2start
2,停止实例：db2stop
3,列出所有实例：db2ilist
4,例出当前实例：db2 get instance
5,察看示例配置文件：db2 get dbm cfg|more
db2 "create table devyb as (select case student when 'student1' then 'studnet1' end as name,case subject when 'chinese' then '80'end as chinese,case subject
when 'math' then '70' end as math,case subject when 'english' then '60' end as english from devya) definition only"
db2 "create table devyb as (select case subject when 'chinese' then 'studnet1' end as name,case subject when 'chinese' then '80'end as chinese,case subject when 'chinese' then '70' end as math,case subject when 'chinese' then '60'end as english from devya) definition only"
一，数据控制语言DCL：
1,使用Grant语句授予权限和特权给用户或组：
grant privilege on object-type object-name to [user|whih rization-name [with grant option]
eg: grant insert,delete on table testtable to user devy with grant option
2,使用revoke撤销用户的权限和特权：
revoke privilege on object-type object-name from [user|group|public] authorization-name
revoke all privileges on table testtable from devy
二，数据定义语言DDL：
1,使用create语句创建新的数据库对象，包括：
缓冲池（buffer pool)
事件监视器（event monitor)
函数（function)
索引（Index)
模式（schema)
存储过程（stored procedure)
表（table)
表空间（Table space)
触发器（trigger)
视图（view)
eg:create table testtable(
deptnum smallint not null,
deptname varchar(14),
manager smallint,
division varchar(10),
location varchar(13))
2,使用declare语句生成新的数据库对象
declare global temporary table session temp1 like employee1 on commit preserve rows not logged in mytempspace
3,使用alter 语句来改变数据库对象包括：
缓冲池（buffer pool)
模式（schema)
表（table)
表空间（Table space)
触发器（trigger)
视图（view)
eg:alter table testtbale1 add newcoloum1 char(2)
4,使用Drop来去掉数据库对象，包括：
缓冲池（buffer pool)
事件监视器（event monitor)
函数（function)
索引（Index)
模式（schema)
存储过程（stored procedure)
表（table)
表空间（Table space)
触发器（trigger)
视图（view)
eg:drop table testtable
三，数据操作语言DML：
1,使用select 语句从数据库表中检索数据
eg:1> 为了要限制结果集中的行数，请使用fetch first子句
select * from staff fetch first 10 rows only
2> 通过指定一个选择列表并用逗号分隔列名，就可以检索特定的列了。
select name,salary from staff
3> 使用distinct 子句来排除结果集中重复的行。
select distinct dept,job from staff
4> 使用AS 子句为选择列表上的表达式或项指定一个有意义的句字。
select name,salary+comm as pay form staff
2,使用insert 语句向表或视图中添加新行
eg: 1> insert into staff values(1213,'cemy',20,'sales',3,90000.00,30000.00)
2> insert into staff (id,name,dept,job,years,salary,comm)
values(1212,'cemy',20,'salse',3,90000.00,30000,00), (1213,'wolfrum',20,'sales',2,90000.00,10000,00)
3,使用update语句来改变表或视图中的数据
eg:1> update staff set dept=51,salary=70000 where id =750
2> update staff set(dept,salary)=(51,70000) where id=750
4, 使用delete 语句来删除数据
delete from staff where id in(1212,1213)
四， SQL工具
1,使用where 子句和谓词来限制查询返回的数据个数
eg：1> select name,salary from staff where salary>20000
2> select name,job,salary from staff where job<>'mgr' and salary>20000
3> select name from staff where name like 'S%'
4> select lastname from employee where lastname in (select sales_person from sales where sales_date<'01/01/1996')
5> select e.salary from employee e where e.salay<(select avg(s.salary)from staff s)
2, 使用order by 子句对结果进行排序
eg: select name,salary from staff where salary>20000 order by salary
3, 使用连接从一个以上的表中检索数据
1> 连接
select deptnum ,deptname,manager,id,name,dept,job from org,staff
select deptnum,deptname,id as manager_id,name as manager from org,staff where manager=id order by deptnum
2> 内连接
select deptnum,deptname,id as manager_id,name as manager from org inner join staff on manager=id order by deptnum
3> 外连接
左外连接
select deptnum,deptname,id as manager_id,name as manager from org left outer join staff on manager=id order by deptnum
右外连接
select deptnum,deptname,id as manager_id,name as manager from org right outer join staff on manager=id order by deptnum
全外连接
select deptnum,deptname,id as manager_id,name as manager from org full outer join staff on manager=id order by deptnum
select empno,deptname,projname from (employee left outer join project on respemp=empno)left outer join department on mgrno=empno
4, 使用集合运算符把两个以上的查询合并成一个查询
select sales_person from sales where region='ontario-south' union select sales_person from sales where sales>3
5, 使用group by子句来汇总结果
select sales_date,max(sales) as max_sales from sales group by sales_date
select op_time,count(*) call_num from Seg_roam_city_hu_200703_dm group by op_time
select year(sales_date) as year,region,sum(sales) as tot_sales from sales group by grouping sets(year(sales_date),region,())
select sales_person,sum(sales) as total_sales from sales group by sales_person having sum(sales)>25
6,连接与子查询比较
select empno,lastname from emp where workdept in (select deptno from dept where deptname='planning')
select empno,lastname from emp,dept where workdept=deptno and deptname='planning'
7,having 和where的区别
select workdept,avg(bonus),max(bonus),min(bonus) from emp where workdept not in ('D11','D21') group by workdept having count(*)>10
8, 关系除法
select stuname from students where not exists (
select * from classes where not exists(
select * from scores where scores.stuno=student.stuno and scores.classno=classes.classno))
9, Case表达式
select creator,name,'TABLE'from sysibm.systables where type='T'
union all select creator,name,'VIEW'from sysibm.systables where type='V'
union all select creator,name,'ALIAS' from sysibm.systables where type='A'
等价于：
select creator,name,case type when 'T' then then 'TABLE'when 'V' then 'VIEW' when 'A' Then 'Alias' end from sysibm.systables
将表旋转：
select case subject when 'chinese' then 'studnet1' end as name,case subject when 'chinese' then '80'end as chinese,case subject when 'chinese' then '70' end as math,case subject when 'chinese' then '60'end as english from devya fetch first 1 rows only
五， SQL函数
一>,列函数
使用列函数的一些规则：
1> 列函数只能用在select语句中。
2> 对列函数不许显示的指定列名或表达式。
3> 每个列函数对所做的select操作的数据行集合只返回一个值。
4> 如果对select语句的某个列使用列函数，除非也使用Group by子句，否则必须对同一个select语句中的其它所有列也使用这个列函数。
5> 使用group by子句来对一组命令列使用列函数，任何在这条select语句中的命令列也必将被这个列函数处理。
6> 除去count 和count_big 函数外，列函数的结果值与它处理的列具有相同的数据类型。count列函数返回整形值，而count_big列函数返回小数值。
7> 除去count and count_big函数外，列函数的结果值可以是空值。count and count_big总返回数字型的结果值。
8> 如果预先在where子句中定义的条件没有返回数据，而是返回空值null,则列函数将不返回sqlcode 100.
9> 当在可以取空值的列上使用avg,max,min,stddev,sum和variance函数时，在应用这些函数前将所有空值剔除。
10> 在使用某个列函数之前，可以使用distinct关键字来去除重复的值。Distinct对max和min函数无效。
11> 可以使用ALL关键字指出重复的值不被去除，All在列函数使用中是默认的。
12> 只有当一个where子句是having子句的子查询的一部分时，列函数才能在where子句中被定义。
13> 在列函数的表达式中指定的每个列名都必须被相同的组所引用。
1,AVG函数
select workdept,avg(salary) from emp group by workdept
2,count函数
select count(distinct workdept) from emp
3,count_big函数
select count_big(distinct workdept) from emp
4,Max 函数
5,Min 函数
6,Stddev 函数
函数返回一组数的标准均方差
7,Sum 函数
select sum (salary+comm+bonus) from emp
8,Variance或var 函数
返回一组数据的方差
Variance=sum(X**2)/count(X)-(sum(X)/count(X))**2
二>, 标量函数
abs:返回数的绝对值
hex：返回值的十六进制表示。
length:返回自变量中的字节数
select deptname,length(deptname) from org --每个名称的长度
year:抽取日期时间值的年份部分。
