内容字号：
段落设置：
字体设置：
oracle中生成guid唯一id及随机数
&oracle如何得到32位的世界唯一随机数
&我们在创建表的时候一般都用序列生成的数字来保证数据的唯一，但这只能保证在单个实例中，无法适合并行或远程的环境的主关键字
&因为在各自环境理里可能生成的数字重复，从而会导致冲突的发生；而sys_guid()会保证创建的标识符在每个环境里都是唯一的。
&sys_guid()得到的是一个32位的随机数。
&例如：select sys_guid()
&或 在程序块里
&&& v_guid& varchar2(64);
&&& v_guid:=sys_guid();
&&& dbms_output.put_line(v_guid);
----------------------------------------------------------------------------------
项目中若采用oracle数据库，则主键生成可以有两种方式，一是用sys_guid(),二是定义一个sequence，然后用nextval得到ID。
Oracle的定义sequence例子：
create sequence seq_test
minvalue 10000
maxvalue 99999
start with 20000
increment by 1
从网络搜索得到的结论看，一般的意见总结为：
1.SYS_GUID()比sequence复杂；
2.SYS_GUID做主键，则表、索引存储开销多；
3.SYS_GUID索引查询比sequence慢；
--------------------------------------随机数---------------------------------------------------------
1、从表中随机取记录
&&&& select * from (select * from staff order by dbms_random.random)
&&&& where rownum & 4
&&&& 表示从STAFF表中随机取3条记录
2、产生随机数
&&&&&&&& SELECT DBMS_RANDOM.RANDOM FROM DUAL;
&&&&&&&& 产生一个任意大小的随机数
&&&&&&& SELECT ABS(MOD(DBMS_RANDOM.RANDOM,100)) FROM DUAL;
&&&&&&&& 产生一个100以内的随机数
&&&&&&&& SELECT TRUNC(100+900*dbms_random.value) FROM
&&&&&&&& 产生一个100～1000之间的随机数
&&&&&&&& SELECT dbms_random.value FROM
&&&&&&&& 产生一个0～1之间的随机数
&&&&&&&& SELECT dbms_random.value(10,20) FROM
&&&&&&&& 产生一个10～20之间的随机数
&&&&&&&& SELECT dbms_random.normal FROM
&&&&&&&& NORMAL函数返回服从正态分布的一组数。此正态分布标准偏差为1，期望值为0。这个函数返回的数值中有68%是介于-1与+1之间，95%介于-2与+2之间，99%介于-3与+3之间。
3、产生随机字符串
&&&&&&&& select dbms_random.string('P',20)
&&&&&&&& 第一个参数 P 表示 printable，即字符串由任意可打印字符构成
&&&&&&&& 第二个参数表示返回字符串长度
4、ceil( n )函数是返回大于或等于n的最小整数。
DBMS_RANDOM.VALUE()是随机产生( 0,1 )之间的数。
要产生两位的随机数，可以DBMS_RANDOM.VALUE()*100，这样产生( 0,100 )
的随机数，当产生( 0,10)之间的数时，只要加上10就可以保证产生的数都是两位了。
ORACLE的PL/SQL提供了生成随机数和随机字符串的多种方式，罗列如下:
1、小数( 0 ~ 1)
&&&& select dbms_random.value from dual
2、指定范围内的小数 ( 0 ~ 100 )
&&&& select dbms_random.value(0,100) from dual
3、指定范围内的整数 ( 0 ~ 100 )
&&&& select trunc(dbms_random.value(0,100)) from dual
4、长度为20的随机数字串
&&&& select substr(cast(dbms_random.value as varchar2(38)),3,20) from dual
5、正态分布的随机数
&&&& select dbms_random.normal from dual
6、随机字符串
&&&& select dbms_random.string(opt, length) from dual
&&&&& opt可取值如下：
&&&&& 'u','U'&&& :&&& 大写字母
&&&&& 'l','L'&&& :&&& 小写字母
&&&&& 'a','A'&&& :&&& 大、小写字母
&&&&& 'x','X'&&& :&&& 数字、大写字母
&&&&& 'p','P'&&& :&&& 可打印字符
7、随机日期
&&&& select to_date(2454084+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J') from dual
&&&& 通过下面的语句获得指定日期的基数
&&&& select to_char(sysdate,'J') from dual
8、生成GUID
&&&& select sys_guid() from dual
--生成带分隔符(-)的GUID的自定义函数
create or replace function my_guid
return varchar2
&&& guid varchar(36);
&&& temp varchar(32);
&&& temp:=sys_guid();
&&& guid:= substr(temp,1,8) || '-'
&&&&&&&& ||substr(temp,9,4) || '-'
&&&&&&&& ||substr(temp,13,4)|| '-'
&&&&&&&& ||substr(temp,17,4)|| '-'
&&&&&&&& ||substr(temp,21,12);
分享给小伙伴们：
有您参与更精彩！
本类最热新闻
48小时最热
01020304050607089plsql常用函数汇总 - jack_Meng - 博客园
随笔 - 614, 文章 - 1, 评论 - 61, 引用 - 0
oracle教程:PLSQL常用方法汇总
在SQLPLUS下，实现中-英字符集转换alter session set nls_language='AMERICAN';alter session set nls_language='SIMPLIFIED CHINESE'; 主要知识点：
一、有关表的操作
create table test as select * --从已知表复制数据和结构create table test as select * from dept where 1=2; --从已知表复制结构但不包括数据2)插入数据：insert into test select *
二、运算符
算术运算符：+ - * / 可以在select 语句中使用连接运算符：|| select deptno|| 比较运算符：& &= = != & &= like between is null in逻辑运算符：not and or 集合运算符： intersect ，union， union all， minus 要求：对应集合的列数和数据类型相同查询中不能包含long 列列的标签是第一个集合的标签使用order by时，必须使用位置序号，不能使用列名例：集合运算符的使用：intersect ，union， union all， minus select * from emp intersect select * from emp where deptno=10 ;select * from emp minus select * from emp where deptno=10;select * from emp where deptno=10 union select * from emp where deptno in (10,20); --不包括重复行 select * from emp where deptno=10 union all select * from emp where deptno in (10,20); --包括重复行
三，常用 ORACLE 函数
sysdate为系统日期 dual为虚表
一）日期函数[重点掌握前四个日期函数]1,add_months[返回日期加(减)指定月份后(前)的日期]select sysdate S1,add_months(sysdate,10) S2,add_months(sysdate,5) S32,last_day [返回该月最后一天的日期] select last_day(sysdate) 3,months_between[返回日期之间的月份数]select sysdate S1, months_between('1-4月-04',sysdate) S2,months_between('1-4月-04','1-2月-04') S3 from dual4,next_day(d,day): 返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日 select sysdate S1,next_day(sysdate,1) S2,next_day(sysdate,'星期日') S3 FROM DUAL 5,round[舍入到最接近的日期](day:舍入到最接近的星期日)select sysdate S1,round(sysdate) S2 ,round(sysdate,'year') YEAR,round(sysdate,'month') MONTH ,round(sysdate,'day') DAY from dual6,trunc[截断到最接近的日期] select sysdate S1,trunc(sysdate) S2,trunc(sysdate,'year') YEAR,trunc(sysdate,'month') MONTH ,trunc(sysdate,'day') DAY from dual7,返回日期列表中最晚日期select greatest('01-1月-04','04-1月-04','10-2月-04') from dual
二）字符函数(可用于字面字符或数据库列)1，字符串截取select substr('abcdef',1,3) from dual2,查找子串位置select instr('abcfdgfdhd','fd') from dual3,字符串连接select 'HELLO'||'hello world'4, 1)去掉字符串中的空格select ltrim(' abc') s1,rtrim('zhang ') s2,trim(' zhang ') s3 from dual2)去掉前导和后缀select trim(leading 9 from ) s1,trim(trailing 9 from ) s2,trim(9 from ) s35,返回字符串首字母的Ascii值select ascii('a') from dual6,返回ascii值对应的字母select chr(97) from dual7,计算字符串长度 select length('abcdef') from dual8,initcap（首字母变大写） ,lower(变小写),upper(变大写)select lower('ABC') s1,upper('def') s2, initcap('efg') s3 9,Replaceselect replace('abc','b','xy')10,translateselect translate('abc','b','xx') -- x是1位11,lpad [左添充] rpad [右填充](用于控制输出格式)select lpad('func',15,'=') s1, rpad('func',15,'-') s2select lpad(dname,14,'=')12, decode[实现if ..then 逻辑]select deptno,decode(deptno,10,'1',20,'2',30,'3','其他')
三）数字函数1，取整函数（ceil 向上取整,floor 向下取整）select ceil(66.6) N1,floor(66.6) N22, 取幂(power) 和 求平方根(sqrt)select power(3,2) N1,sqrt(9) N23,求余select mod(9,5)4,返回固定小数位数 (round:四舍五入，trunc:直接截断)select round(66.667,2) N1,trunc(66.667,2) N2 5,返回值的符号(正数返回为1,负数为-1)select sign(-32),sign(293)
四）转换函数1，to_char()[将日期和数字类型转换成字符类型]1) select to_char(sysdate) s1,to_char(sysdate,'yyyy-mm-dd') s2,to_char(sysdate,'yyyy') s3,to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') s4,
to_char(sysdate, 'hh24:mi:ss') s5,to_char(sysdate,'DAY') s62) select sal,to_char(sal,'＄99999') n1,to_char(sal,'＄99,999') n2 from emp2, to_date()[将字符类型转换为日期类型] insert into emp(empno,hiredate) values(8000,to_date('','yyyy-mm-dd'));3, to_number() 转换为数字类型 select to_number(to_char(sysdate,'hh12')) //以数字显示的小时数
五）其他函数user: 返回登录的用户名称 s vsize: 返回表达式所需的字节数select vsize('HELLO')nvl(ex1,ex2): 　ex1值为空则返回ex2,否则返回该值本身ex1（常用） 例：如果雇员没有佣金，将显示0，否则显示佣金 select comm,nvl(comm,0)nullif(ex1,ex2): 值相等返空，否则返回第一个值例：如果工资和佣金相等，则显示空，否则显示工资select nullif(sal,comm),sal,coalesce: 　返回列表中第一个非空表达式select comm,sal,coalesce(comm,sal,sal*10)　　　nvl2(ex1,ex2,ex3) :如果ex1不为空，显示ex2,否则显示ex3如：查看有佣金的雇员姓名以及他们的佣金 　　 　 select nvl2(comm,ename,') as HaveCommName,
六）分组函数max min avg count sum1，整个结果集是一个组1) 求部门30 的最高工资，最低工资,平均工资，总人数，有工作的人数，工种数量及工资总和select max(ename),max(sal), min(ename),min(sal),avg(sal),count(*) ,count(job),count(distinct(job)) ,sum(sal) from emp where deptno=30;2, 带group by 和 having 的分组1)按部门分组求最高工资，最低工资，总人数，有工作的人数，工种数量及工资总和select deptno, max(ename),max(sal),min(ename),min(sal),avg(sal),count(*) ,count(job),count(distinct(job)) ,sum(sal) from2)部门30的最高工资，最低工资，总人数，有工作的人数，工种数量及工资总和 select deptno, max(ename),max(sal),min(ename),min(sal),avg(sal),count(*) ,count(job),count(distinct(job)) ,sum(sal) from emp group by deptno having deptno=30;3, stddev 返回一组值的标准偏差select deptno,stddev(sal) fromvariance 返回一组值的方差差select deptno,variance(sal) from4, 带有rollup和cube操作符的Group Byrollup 按分组的第一个列进行统计和最后的小计cube 按分组的所有列的进行统计和最后的小计select deptno,job ,sum(sal) from emp group by deptno,select deptno,job ,sum(sal) from emp group by rollup(deptno,job); cube 产生组内所有列的统计和最后的小计 &select deptno,job ,sum(sal) from emp group by cube(deptno,job);
七、临时表
只在会话期间或在事务处理期间存在的表.临时表在插入数据时，动态分配空间 create global temporary table temp_dept(dno number,dname varchar2(10))oinsert into temp_dept values(10,'ABC');select * from temp_ --无数据显示,数据自动清除on commit preserve rows:在会话期间表一直可以存在（保留数据）on commit delete rows:事务结束清除数据（在事务结束时自动删除表的数据）
========================== 连接操作 ================================ 例如有A,B两张表： A表&&&&&&&&&&& B表 a b&&&&&&&& c d 1 0&&&&&&&& 4 7 2 9&&&&&&&& 1 5 select * from A,B where A.a = B.c 等同于 select * from A join B on A.a = B.c 结果： a b c d 1 0 1 5 左连接： select * from A,B where A.a = B.c(+) 等同于 select * from A left join B on A.a = B.c 即以A表查询为主，附带查询出满足A.a = B.c条件的B表中的结果 结果： a b c d 1 0 1 5 2 9 右连接： select * from A,B where A.a(+) = B.c 等同于 select * from A right join B on A.a = B.c 此查询为左连接，即以A表查询为主，附带查询出满足A.a = B.c条件的B表中的结果 结果： c d a b 1 5 1 0 4 7 =============================================================== ===================== oracle 函数 =============================== DECODE: decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值) A表 a b c 1 3 0 3 2 9 1 0 5 3 7 7 5 5 9 select decode(A.a,1,4,3,8),A.b,A.c form A 意思:如果A.a的值为1，则将A.a的值查询为4，如果A.a的值为3，则将A.a的值查询为8 结果： a b c 4 3 0 8 2 9 4 0 5 8 7 7 5 5 9 select decode(A.a,1,4,8),A.b,A.c form A 意思:如果A.a的值为1，则将A.a的值查询为4，否则A.a的值查询为8 结果： a b c 4 3 0 8 2 9 4 0 5 8 7 7 8 5 9 select decode(A.a,1,4,3,8,7),A.b,A.c form A 意思:如果A.a的值为1，则将A.a的值查询为4，如果A.a的值为3，则将A.a的值查询为8，否则查询为7 结果： a b c 4 3 0 8 2 9 4 0 5 8 7 7 7 5 9 ===============================================================
==========================SEQUENCE===================================== SEQUENCE： CREATE&& SEQUENCE&& LOCATIONS_SEQ &&&&&&&&& INCREMENT&& BY&& 1 &&&&&&&&& START&& WITH&& 1 &&&&&&&&& MAXVALUE&& 9900 &&&&&&&&& MINVALUE&& 1 SELECT LOCATIONS_SEQ.currval FROM DUAL ; SELECT LOCATIONS_SEQ.nextval FROM DUAL ; ============================SEQUENCE=================================== ============================函数===================================
日期函数： add_months(d,n) 日期d加n个月 last_day(d) 包含d的月份的最后一天的日期 month_between(d,e) 日期d与e之间的月份数，e先于d new_time(d,a,b) a时区的日期和时间d在b时区的日期和时间 next_day(d,day) 比日期d晚，由day指定的周几的日期 sysdate 当前的系统日期和时间 greatest(d1,d2,...dn) 给出的日期列表中最后的日期 least(d1,k2,...dn) 给出的日期列表中最早的日期 to_char(d [,fmt]) 日期d按fmt指定的格式转变成字符串 to_date(st [,fmt]) 字符串st按fmt指定的格式转成日期值，若fmt忽略，st要用缺省格式 round(d [,fmt]) 日期d按fmt指定格式舍入到最近的日期 trunc(d [,fmt]) 日期d按fmt指定格式截断到最近的日期
分组函数： avg([distinct/all] n) 列n的平均值 count([all] *) 返回查询范围内的行数包括重复值和空值 count([distinct/all] n) 非空值的行数 max([distinct/all] n) 该列或表达式的最大值 min([distinct/all] n) 该列或表达式的最小值 stdev([distinct/all] n) 该列或表达式的标准偏差，忽略空值 sum([distinct/all] n) 该列或表达式的总和 variance([distinct/all] n) 该列或表达式的方差，忽略空值
数值函数： abs(m) m的绝对值 mod(m,n) m被n除后的余数 power(m,n) m的n次方 round(m[,n]) m四舍五入至小数点后n位的值（n缺省为0） trunc(m[,n]) m截断n位小数位的值（n缺省为0）
字符函数： initcap(st) 返回st将每个单词的首字母大写，所有其他字母小写 lower(st) 返回st将每个单词的字母全部小写 upper(st) 返回st将每个单词的字母全部大写 concat(st1,st2) 返回st为st2接st1的末尾（可用操作符"||"） lpad(st1,n[,st2]) 返回右对齐的st,st为在st1的左边用st2填充直至长度为n,st2的缺省为空格 rpad(st1,n[,st2]) 返回左对齐的st,st为在st1的右边用st2填充直至长度为n,st2的缺省为空格 ltrim(st[,set]) 返回st,st为从左边删除set中字符直到第一个不是set中的字符。缺省时，指的是空格 rtrim(st[,set]) 返回st,st为从右边删除set中字符直到第一个不是set中的字符。缺省时，指的是空格 replace(st,search_st[,replace_st]) 将每次在st中出现的search_st用replace_st替换，返回一个st。缺省时，
删除search_st substr(st,m[,n]) n=返回st串的子串，从m位置开始，取n个字符长。缺省时，一直返回到st末端 length(st) 数值，返回st中的字符数 instr(st1,st2[,m[,n]]) 数值，返回st1从第m字符开始，st2第n次出现的位置，m及n的缺省值为1
关于树形结构表的递归查询：
Start with ~~ connect by prior ~~~~
CREATE TABLE TBL_TEST
ID&&& NUMBER,
NAME VARCHAR2(100 BYTE), &&
PID&& NUMBER&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
1,向下查询
select * from TBL_TEST t
start with id=5
connect by prior id = pid
2，向上查询
select * from TBL_TEST
start with id=5
connect by prior pid = id
oracle 所特有的 函数
DECODE函数是ORACLE PL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其他数据库厂商的SQL实现还没有此功能。DECODE有什么用途呢？ 先构造一个例子，假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％，通常的做法是，先选出记录中的工资字段值? select salary into var-salary from employee，然后对变量var-salary用if-then-else或choose case之类的流控制语句进行判断。 如果用DECODE函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成。如下：select decode(sign(salary - 8000),&0,salary*1.15,&0,salary*1.2,salary) from employee 是不是很简洁？ DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。初看一下，DECODE 只能做等于测试，但刚才也看到了，我们通过一些函数或计算替代value，是可以使DECODE函数具备大于、小于或等于功能。
n& Select decode(值1,值2,返回值1,返回值2)
n& Select (case when condition1 then 返回值1 when condition2 then 返回值2 end)
n& Decode可以与caise ..when .. End相互转换，相对case .. When .. End更好用.
1、说明：创建数据库
CREATE DATABASE database-name
2、说明：删除数据库
drop database dbname &&&&&&&&&
3、说明：备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2& from tab_old definition only
5、说明：删除新表
drop table tabname
6、说明：增加一个列
Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
7、说明：添加主键： Alter table tabname add primary key(col)
说明：删除主键： Alter table tabname drop primary key(col)
8、说明：创建索引：create [unique] index idxname on tabname(col&.)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
9、说明：创建视图：create view viewname as select statement
删除视图：drop view viewname
10、说明：几个简单的基本的sql语句
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like &%value1%& ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
11、说明：几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
12、说明：使用外连接
A、left outer join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B：right outer join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full outer join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1&&1
法二：select top 0 * into b from a
2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in &具体数据库& where 条件
例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..
4、说明：子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
5、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、说明：外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、说明：在线视图查询(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a & 1;
8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
9、说明：in 的使用方法
select * from table1 where a [not] in (&值1&,&值2&,&值4&,&值6&)
10、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())&5
13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
14、说明：前10条记录
select top 10 * form table1 where 范围
15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、说明：随机取出10条数据
select top 10 * from tablename order by newid()
18、说明：随机选择记录
select newid()
19、说明：删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
20、说明：列出数据库里所有的表名
select name from sysobjects where type='U'
21、说明：列出表里的所有的
select name from syscolumns where id=object_id('TableName')
22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
显示结果：
type vender pcs
23、说明：初始化表table1
TRUNCATE TABLE table1
24、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
1、1=1，1=2的使用，在SQL语句组合时用的较多
&where 1=1& 是表示选择全部&& &where 1=2&全部不选，
if @strWhere !=''
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
我们可以直接写成
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere
2、收缩数据库
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收缩数据和日志
DBCC SHRINKDB
DBCC SHRINKFILE
3、压缩数据库
dbcc shrinkdatabase(dbname)
4、转移数据库给新用户以已存在用户权限
exec sp_change_users_login 'update_one','newname','oldname'
5、检查备份集
RESTORE VERIFYONLY from disk='E:\dvbbs.bak'
6、修复数据库
ALTER DATABASE [dvbbs] SET SINGLE_USER
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
ALTER DATABASE [dvbbs] SET MULTI_USER
7、日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
&&&&&&& @MaxMinutes INT,
&&&&&&& @NewSize INT
USE&&&& tablename&&&&&&&&&&&& -- 要操作的数据库名
SELECT& @LogicalFileName = 'tablename_log',& -- 日志文件名
@MaxMinutes = 10,&&&&&&&&&&&&&& -- Limit on time allowed to wrap log.
&&&&&&& @NewSize = 1&&&&&&&&&&&&&&&&& -- 你想设定的日志文件的大小(M)
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
& FROM sysfiles
& WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
&&&&&&& CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
&&&&&&& CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
& FROM sysfiles
& WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
& (DummyColumn char (8000) not null)
DECLARE @Counter&& INT,
&&&&&&& @StartTime DATETIME,
&&&&&&& @TruncLog& VARCHAR(255)
SELECT& @StartTime = GETDATE(),
&&&&&&& @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE&&&& @MaxMinutes & DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
&&&&& AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)&
&&&&& AND (@OriginalSize * 8 /1024) & @NewSize&
& BEGIN -- Outer loop.
&&& SELECT @Counter = 0
&&& WHILE& ((@Counter & @OriginalSize / 16) AND (@Counter & 50000))
&&&&& BEGIN -- update
&&&&&&& INSERT DummyTrans VALUES ('Fill Log')&
&&&&&&& DELETE DummyTrans
&&&&&&& SELECT @Counter = @Counter + 1
&&&&& END&&
&&& EXEC (@TruncLog)&
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
&&&&&&& CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
&&&&&&& CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
& FROM sysfiles
& WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF
8、说明：更改某个表
exec sp_changeobjectowner 'tablename','dbo'
9、存储更改全部表
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
&@OldOwner as NVARCHAR(128),
&@NewOwner as NVARCHAR(128)
DECLARE @Name&& as NVARCHAR(128)
DECLARE @Owner& as NVARCHAR(128)
DECLARE @OwnerName& as NVARCHAR(128)
DECLARE curObject CURSOR FOR
&select 'Name'&& = name,
& 'Owner'&& = user_name(uid)
&from sysobjects
&where user_name(uid)=@OldOwner
&order by name
OPEN& curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
&if @Owner=@OldOwner
& set @OwnerName = @OldOwner + '.' + rtrim(@Name)
& exec sp_changeobjectowner @OwnerName, @NewOwner
-- select @name,@NewOwner,@OldOwner
&FETCH NEXT FROM curObject INTO @Name, @Owner
close curObject
deallocate curObject
10、SQL SERVER中直接循环写入数据
declare @i int
while @i&30
&& insert into test (userid) values(@i)
&& set @i=@i+1
11、一些排名函数
&&&&&&&&&&& （可参见：）
&&&&&&&&&&& row_number()& 最常用&&出现重复的只取第一个
&&&&&&&&&&& rank() 两个名次一样的，下一条数据自动跳过
&&&&&&&&&&& dense_rank()& 两个名次不一样的，下一条数据不跳过
一些混合用法：
聚合函数/排序函数+ Over(partition by column order by column)
（可参见：）
select decode(sign(numb-6),-1,numb,6) numb,decode(sign(numb-6),-1,xingshi,'其他') xingshi, sum(amount) from
(select xingshi,amount, row_number() over(order by amount desc) numb from
&&&&&& (select substr(t.name,1,1) xingshi, count(1) amount from pub_user t
& where substr(t.name,1,1) not between '0' and '9' and substr(t.name,1,1) not between 'a' and 'z'
&&&&&&&&&&&&& group by substr(t.name,1,1) order by amount desc )
) group by decode(sign(numb-6),-1,numb,6),decode(sign(numb-6),-1,xingshi,'其他')
select t.key taskType,sum(rn) countNum,t.key_rn from
&&&&&& (select (case when key &=5 then xingshi
&&&&&&&&&&&&&&&&&&&&&&& &&&&&&&&&&&&&&&&& else '其他' end) key,
&&&&&&&&&&&&&&&&&&&&&&& &&&&&&&&&& (case when key &=5 then key
&&&&&&&&&&&&&&&&&&&&&&& &&&&&&&&&&&&&&&& else 6 end) key_rn,rn
&&&&&&&&&&&&&&&&&&&&&&& &&&&& from (select row_number() over(
&&&&&&&&&&&&&&&&&&&&&&& &&&&&&&&&&&&&&&&&& order by rn desc) key,rn,xingshi
&&&&&&&&&&&&&&&&&&&&&&& &&&&&&&&&&&&& from (
&&&&&&&&&&&&&&&&&&&&&&&&& select t.xingshi,count(1) rn
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& from (select substr(t.name,1,1) xingshi,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& t.user_id from pub_user t)& t
&&&&&&&&&&&&&&&&&&&&&&&& &&&&&&&where xingshi not between
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 'a' and 'z' and xingshi
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& not between '0' and '9'
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& group by t.xingshi))) t
&&&&&&&&&&&&&&&&&&&&&&& group by t.key,t.key_rn order by t.key_
select * from ( select rank() over(partition by classroom order by grade desc)mm ,name,grade,classroom from grade_class) t where t.grade&90
select avg(grade) AS 平均分,t.classroom from grade_class& t group by& t.classroom having avg(grade)&94