sql经典语句

1.按姓氏笔画排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多

2.数据库加密:
select encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') ； 1--相同；否则不相同 encrypt('原始密码')

3.获取表的列

select name from syscolumns where id=object_id('salechance')

同

select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = 'salechance')

 

4.获取所有用户表

select Name from sysobjects where xtype='u' and status>=0

 

5.查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype='P'

 

 

6.把本地表导入远程表

insert openquery(ITSV,  'SELECT *  FROM 数据库.dbo.表名 ') select * from 本地表

 

7.使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。SET NOCOUNT 设置是在执行或运行时设置，而不是在分析时设置。

SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。
SET NOCOUNT 为 OFF 时，返回计数

8.在SQL查询中：from后最多可以跟多少张表或视图：256

9.在SQL语句中出现 Order by,查询时，先排序，后取在SQL中。

 

10.一个字段的最大容量是8000，而对于nvarchar(4000),由于nvarchar是Unicode码。

 

 

 

 

技巧

首先准备数据

create table tab1(id int not null,tname varchar(20));
create table tab2(id int not null,tname varchar(20));

insert into tab1 values(1,'上海');
insert into tab1 values(2,'北京');
insert into tab1 values(3,'广州');
insert into tab1 values(4,'重庆');
insert into  tab1 values(5,'兰州');

insert into tab2 values(1,'上海');
insert into tab2 values(2,'北京');
insert into tab2 values(3,'广州');
insert into tab2 values(4,'重庆');
insert into  tab2 values(5,'深圳');

 

 

 

11.更新修改过的数据 （即如果t2和t1 tname不同，则将t1的tname修改为t2的tname）

update t1 set t1.tname=t2.tname 
from tab1 t1,
tab2 t2
where t1.id=t2.id and(t1.tname <> t2.tname) ;

 

 update tb1 set name=(select tb2.name from tb2  where tb2.id =tb1.id ) ;

 

例如号码段

create table phone(
city varchar(20) not null,
psection varchar(20) not null
)

create table tel(
telno varchar(20) not null,
city varchar(20)
)

insert into phone values('上海','1340200');
insert into phone values('北京','1369925');

 

insert into tel values('13925727238',null);
insert into tel values('13402000999',null);
insert into tel values('13699254553',null);

 

select * from phone;
select * from tel;

update tel SET tel.city=phone.city FROM tel,phone where tel.telno like phone.psection+'%';

 

 

执行前

 

上海1340200 

北京1369925 

-------------------

 

13925727238    null
13402000999    null
13699254553    null

 

执行后的结果是

13925727238 NULL 

13402000999上海 

13699254553 北京 

 

 

 

12.插入新增的数据

insert into tab1 (id,tname)
select  t2.id,t2.tname from tab2 t2
where not exists(select t1.id ,t1.tname from tab1 t1 where t1.id=t2.id );

 

13.删除已经删除的数据(如果需要的话)

delete  t1 from tab1 t1 where not exists( select * from tab2 where id=t1.id)
delete  from t1 from tab1 t1 where not exists( select * from tab2 where id=t1.id)

 

 

一次删除多个表的记录

delete from tab1 where tname='上海' delete from tab2 where tname='上海'

中间只有空格

 

 

 一条SQL语句查询出成绩名次排名

 

 1 张三 90
2 李四 85
3 王五 100
4 赵六 85
5 汪一 100
6 周九 78
7 何二 56


然后我希望排名次，因为这里有同分，所以必然会出现并列名次。网络上关于并列成绩名次排名有两种方式，其一是这样：

A
名次 姓名 成绩
1 王五 100
1 汪一 100
3 张三 90
4 赵六 85
4 李四 85
6 周九 78
7 何二 56


第二种还是这样：

B
名次 姓名 成绩
1 王五 100
1 汪一 100
2 张三 90
3 赵六 85
3 李四 85
4 周九 78
5 何二 56


SQL语句是一种很实用的技巧，希望和朋友们多交流，现在抛砖引玉把自己的3句SQL语句贴出来共享，抛砖引玉，希望大家多多指教，拍砖的轻点。

对于A方式，比较好办，以Access数据库为例（其他数据库语法大同小异，变化一下对应的即可），可以写成这样：
SELECT e.place AS 名次, d.name AS 姓名, d.mark AS 成绩
FROM [select a.id,count(b.id)+1 as place from sc a left join sc b on a.mark < b.mark group by a.id]. AS e INNER JOIN sc AS d ON e.id = d.id;


同时A方式也可以写成这样，效果等同，不过估计比上面那句效率低点：
SELECT e.place AS 名次, d.name AS 姓名, d.mark AS 成绩
FROM [select a.id,count(iif(b.id is null,null,b.id))+1 as place from sc a left join sc b on a.mark<b.mark group by a.id]. AS e INNER JOIN sc AS d ON e.id = d.id;

而B方式则比较棘手，写了半个钟头才写好.......：
SELECT e.place AS 名次, d.name AS 姓名, d.mark AS 成绩
FROM [select c.id,count(iif(c.mark is null,null,c.mark))+1 as place from (select a.id,b.mark from sc a left join sc b on a.mark < b.mark group by a.id,b.mark)c group by c.id]. AS e INNER JOIN sc AS d ON e.id = d.id;

 

 

普通SQL语句可以用Exec执行
 
eg: Select * from tableName 
Exec('select * from tableName') 
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N  
 
字段名，表名，数据库名之类作为变量时，必须用动态SQL
 
eg: 
declare @fname varchar(20) 
set @fname = 'FiledName' 
Select @fname from tableName -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。 
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 
 
当然将字符串改成变量的形式也可 
declare @fname varchar(20) 
set @fname = 'FiledName' --设置字段名 
 
declare @s varchar(1000) 
set @s = 'select ' + @fname + ' from tableName' 
Exec(@s) -- 成功 
exec sp_executesql @s -- 此句会报错 
 
 
declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000) 
set @s = 'select ' + @fname + ' from tableName' 
Exec(@s) -- 成功 
exec sp_executesql @s -- 此句正确

 

 行列转换

 

1. 行列转换--普通

假设有张学生成绩表(CJ)如下
[姓名]  [学科]  [成绩]
张三    语文    80
张三    数学    90
张三    物理    85
李四    语文    85
李四    数学    92
李四    物理    82
王五    数学    60

转换成

[姓名] [语文] [数学] [物理]
张三   80     90     85
李四   85     92     82
王五   null   60     null  

 

 declare @sql varchar(4000)
set @sql = 'select 姓名'
select @sql = @sql + ',sum(case 学科 when ''' 学科 ''' then 成绩 end) as ' 学科
  from (select distinct 学科 from CJ) as a
set @sql = @sql ' from cj group by 姓名'
exec(@sql)

 

 

 一般来说：
①.有大量重复值、且经常有范围查询（between, >,< ，>=,< =）和order by、group by发生的列，可考虑建立群集索引；
②.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；
③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。

 

 

 用一条sql语句查询出“每门”课程都大于80分的学生姓名

 

 select distinct name
from stu
where name not in
    (select distinct name from stu where fengshu <=80);

 

 查询各科成绩前2名

 select sid,cid,Achievement
from Achievement a
where (select count(*) from Achievement where cid=a.cid and
                Achievement>=a.Achievement)<=2
order by cid,Achievement desc
go

 

 

 关于分页查询

oracle 获取总页数

SELECT DECODE(mod(COUNT(*),100),0,COUNT(*)/100,TRUNC(COUNT(*)/100,0)+1)   

AS  pages FROM XZQH   

 

分页

SELECT x.* from (SELECT z.*,rownum numbers from XZQH z where rownum<101) x where x.numbers>90  

 

注意oracle会先分配了行号，再根据字段排序

between 1 AND 3 是没问题的

但是 ROWNUM between 4 AND 6  就不可以了，ROWNUM必须从1开始！

row_number() over()函数试试看

 

select t2.* from
    (select t.*,row_number()over(order by t.n_count desc) orderNumber from t_news t order by t.n_count desc)t2 where orderNumber  between 1and 3;

以及

 

SELECT * FROM (
  SELECT  t.*,row_number() over(ORDER BY n_count DESC) r
  FROM t_news t
  ORDER BY t.n_count DESC
) t
WHERE r BETWEEN 4 AND 6

 

通用语法： 解析函数() over(partition by 字段  order by 字段)

当然也可以用老办法也能查出上面的结果，具体如下：

 

select s.*,s.r from(
select rownum r,t.* from
(select * from t_news  ORDER BY n_count DESC) t ) s where s.r between 4 and 6;

 

不过效率要低点。

 

//oracle获取第二名的销售量

 

 insert into tabb values('c1',1500);
insert into tabb values('c2',1000);
insert into tabb values('c3',900);
insert into tabb values('c4',1200);
insert into tabb values('c5',1400);
insert into tabb values('c3',1300);
insert into tabb values('c5',1100);
insert into tabb values('c6',1700);

select *
  from (select name, sales, dense_rank() over(order by sales desc) pm from tabb s)
 where pm = 2

 

//测试 rank()，dense_rank()，row_number()的区别
create table stu(
name varchar(20),
numb varchar(30),
kemu varchar(20),
fenshu number
);

insert into stu values('li','0113101 ','高数','90');
insert into stu values('zhang','0113102 ','高数','80');
insert into stu values('wang','0113103 ','高数','70');
insert into stu values('li','0113101 ','物理','80');
insert into stu values('zhang','0113102 ','物理','90');
insert into stu values('wang','0113103 ','物理','70');

select rank() over(partition by kemu order by fenshu desc) rk,t.* from stu t
select * from (select rank() over(partition by kemu order by fenshu desc) rk,t.* from stu t) y where y.rk<=2;

select rank() over(order by fenshu desc) rk,t.* from stu t;  （1，1，3，3，5，5）
select dense_rank() over(order by fenshu desc) rk,t.* from stu t;（1，1，2，2，3，3）
select row_number() over(order by fenshu desc) rk,t.* from stu t;（1，2，3，4，5，6）