SQL语句大全，学会你就超神了

一、基础

1、创建数据库

create database [databaseName]

2、删除数据库

drop database [databaseName]

3、备份sql server

--- 创建备份数据的device
use master
exec sp_addumpdevice 'disk' ,'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- 开始备份
backup database pubs to testBack

4、创建新表

create table [tableName](
	col1 type1 [not null][primary key],
	col2 type2 [not null],
	...
)
--- 根据已有的表创建新表
1）create table [tableName] like [old_tableName]
2）create table [tableName] as select col1,col2.....from [old_tableName] definition only

5、删除新表

drop table [tableName]

6、增加一个列

alter table [tableName] add column col type
--- 列增加后将不能删除，DB2中列加上后数据类型也不能改变，唯一能改变的时varchar类型的长度

7、添加\删除主键

--- 添加
alter table [tableName] add primary key(col)
--- 删除
alter table [tableName] drop primary key(col)

8、创建\删除索引

--- 创建索引
create [unique] index [idxName] on [tableName] (col,....)
--- 删除索引
drop index [idxName]

9、创建\删除视图

--- 创建视图
create view [viewName] as select statement
--- 删除视图
drop view [viewName]

10、简单sql总结

--- 选择
select * from [tableName] where [范围] 
--- 插入
insert into [tableName](field1,field2) values(value1,value2)
--- 删除
delete from [tableName] where [范围] 
--- 更新
update [tableName] set field1=value1,field2=value2 where [范围]
--- 查找
select * from [tableName] where field1 like '%key%'
--- 排序
select * from [tableName] order by field1.field2 [desc | asc]
--- 总数
select count as totalCount from [tableName]
--- 求和
select sum(field1) as sumCount from [tableName]
--- 平均
select avg(field1) as avgCount from [tableName]
--- 最大
select max(field1) as maxCount from [tableName]
--- 最小
select min(field1) as minCount from [tableName]

11、高级查询运算词

• union 并集运算符
  通过组合其他两个结果表并消去表中任何重复行而派生出一个结果表，当all随union一起使用时即union all，不消除重复行，两种情况下，派生表的每一行来自组合的其中一张表

--- union
select * from table1
union
select * from table2
--- union all
select * from table1
union all
select * from table2

• except 运算符
  通过包括所有在table1中但不在table2中的行并消除所有重复行而派生出一个结果表，当all与except一起使用时即 except all，不消除重复行

select * from table1
except [all]
select * from table2

• intersect 运算符
  通过只包括table1和table2中都有的行并消除所有重复所有重复行而派生出一个结果表，当all一起使用时，不消除重复行

select * from table1
intersect [all]
select * from table2

注：使用运算符的几个查询结果集必须是一致的

12、使用外连接

• 左连接、左外连接 left join
  结果集包括连接表的匹配行，也包括做左连接表的所有行

select * from table1 a
left [out] join table2 b on a.id = b.aid 

• 右连接、右外连接 right join
  结果集包括连接表的匹配行，也包括做右连接表的所有行

select * from table1 a
right [out] join table2 b on a.id = b.aid 

• 全外连接 full/cross ioin
  不仅包括符号连接表的匹配行，还包括两个连接表的所有记录

select * from table1 a
full [out] join table2 b on a.id = b.aid

13、分组 group by

一张表，一旦分组完成后，查询后只能得到组相关的信息

select * from table1
group by col

14、对数据库进行操作

分离数据库：sp_detach_db
附加数据库：sp_attach_db [tableName]
修改数据库名称：sp_renamedb ‘oldName’,‘newName’

二、提升

1、子查询

select * from table1 a
	where a.id in(
		select b.aid from table2 b
	)

2、显示文章、提交人、最后回复时间

select 
	a.title,a.username,b.adddate 
from 
	table1 a,
	(select max(addddate) adddate from table1 a1 where a1.title = a.title ) b

3、外连接查询

select 
	a.id,b.id
from table1 a
left join table2 b on a.id = b.aid

4、between的用法

--- between包含边界值
select * from table1 where time between time1 and time2
--- not between不包含边界值
select * from table1 where time not between time1 and time2 

5、in的使用

select * from table1 where a [not] in ('1','2',....)

6、关联表，删除主表中已经在附表中没有的信息

delete from table1 where not exists(
	select * from table2 where table1.field1 = table2.field1
)

7、多表联查

select * 
from table1 a
left join table2 b on a.id = b.aid
left join table3 c on b.id = c.bid
.... 

8、日程提醒

select * from table1 where datediff('minute','startTime',getdate()) > 5

9、随机取出10条记录

select top 10 * from table order by newid()

10、删除重复记录

delete from table where id not in (
	select max(id) from table group by col1
)

11、初始化表，清空数据

truncate table [tableName]