sql经典语句

最新推荐文章于 2023-10-10 19:20:15 发布

wxg6203

最新推荐文章于 2023-10-10 19:20:15 发布

阅读量141

点赞数

分类专栏： sql基础文章标签： SQL Oracle Access Go

sql基础专栏收录该内容

23 篇文章 0 订阅

订阅专栏

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）