SQL常用语句和函数
从一个表中选取数据插入到另一个表中：
select column_name(s) into new_table_name from old_table_name --new_table_name表不必事先存在（复制旧表的架构和数据）
select * into table_new from table_old where 1=2 --只复制旧表架构
insert into Table2(field1,field2,…) select value1,value2,… from Table1 --Table2必须事先存在，并且字段field,field2…也必须存在；
insert into Table2 select * from Table1
identity与select into合用，插入行号：
set identity_insert students on
insert students(id, name, age, city) values(10, 'jim', 18, 'newyank')
set identity_insert students off
--注意：一张表中只能有一列为identity，如果指定了自增列，又需要插入指定id的值，需要停止indentiy，执行后再开启
select identity(int,1,1) as rownumber, name, age, sex, city
into students_backup
from students
--注意：如果查询的列中有自增列，需要将其删除，或者屏蔽，因为一张表中只有一个identity字段。
在有identity列的插入时，需要返回当前行的identit的列值：scope_identity()函数
insert into dbo.tb_test2( NAME ) values ( '测试' )
select scope_identity()
返回架构范围内对象的数据库对象标识号：object_id()
--查询表是否存在
select object_id(N'students', N'u')
--与以下语句等价
select id from sysobjects where name=N'Students' and type=N'U'
遇到以零做除数错误
select (Quantity*Price)/nullif((Quantity*UnitPrice), 0) as[平均值] from ##表
nullif(Expression1, Expression2)
--给定两个参数Expression1和Expression2，如果两个参数相等，则返回NULL；否则就返回第一个参数。
对已存在表进行操作：
--增加列：
alter table tablename add colum_name datatype
--修改列：
alter table dbo.tb_test alter column NAME text
--注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
--添加主键：
alter table tabname add primary key(col)
--删除主键：
alter table tabname drop primary key(col)
--添加唯一约束：
alter table dbo.Students add constraint UK_Stu_Name unique(Name)
--删除唯一约束：
alter table dbo.Students drop constraint UK_Stu_Name
索引、视图
--创建索引：
create [unique] index idxname on tabname(col….) --unique唯一索引，col1, col2, col3...多个列联合索引
--删除索引：
drop index idxname
--注：索引是不可更改的，想更改必须删除重新建。
--创建视图：
create view viewname as select statement
--删除视图：
drop view viewname
--注：更新视图暂时没有直接方法，建议删除重建
运算符
--union 运算符
union 运算符通过组合其他两个结果表（例如 table1 和 table2）并消去表中任何重复行而派生出一个结果表。当 all 随 union一起使用时（即 union all），不消除重复行。两种情况下，派生表的每一行不是来自 table1 就是来自table2。
--except 运算符
except 运算符通过包括所有在 table1 中但不在 table2 中的行并消除所有重复行而派生出一个结果表。当 all 随 except 一起使用时 (except all)，不消除重复行。
--intersect 运算符
intersect 运算符通过只包括 table1 和 table2 中都有的行并消除所有重复行而派生出一个结果表。当 all 随 intersect 一起使用时 (intersect all)，不消除重复行。
数据库分页查询：
declare @pageSize int --每页数量
declare @pageIndex int --页码
set @pageSize=10
set @pageIndex=5
--适应于SQL Server 2012以下的版本
select top @pageSize id from tb_test2
where id not in (select top (@pageIndex - 1) * @pageSize id from tb_test2)
--row_number() over( order by id desc )，该关键字只有在SQL Server 2005版本以上才有
select * from (select row_number() over( order by id desc ) as rowNumber, * from tb_test2) temp
where rowNumber > (@pageIndex - 1) * @pageSize and rowNumber <= @pageIndex * @pageSize
--SQL Server 2012以上版本才支持
select * from tb_test2
order by id
offset ((@pageIndex - 1) * @pageSize) rows
fetch next @pageSize rows only
--选择从10到15的记录：
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
--随机取出10条数据：
select top 10 * from tablename order by newid()
按姓氏笔画排序：
select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as --从少到多
查询各科成绩最好的前三名，按课程号升序排列，同课程的成绩倒叙排列，加上排名
select CID, SID, Score, Place=(select count(Score) from dbo.SC b where b.CID=a.cid and b.Score>a.score)+1
from dbo.SC a
where Score in(select top 3 Score from dbo.SC where CID=a.CID order by Score desc)
order by a.CID asc, a.Score desc
结果：
查询各科的及格率
select CID, convert(varchar(50),100*Pass/(Pass+Fail),2)+'%' as 及格率 from (
select CID, sum(case when Score>=60 then 1 else 0 end) Pass,
sum(case when Score<60 then 1 else 0 end) Fail
from dbo.SC
group by dbo.SC.CID) T1
结果：
查询“英语”课程第三名的学生的成绩单
--查询所有英语成绩（会有并列可能）
select * from dbo.SC
where CID=(select CID from dbo.Course where CName='英语')
--把成绩加入序号，直接获取序号为3的数据
select * from (
select SID, CID, Score, Place=(select count(distinct(Score)) from dbo.SC b
where CID=(select CID from dbo.Course where CName='英语') and b.Score>a.score)+1
from (
select SID, CID, Score from dbo.SC where CID=(select CID from dbo.Course where CName='英语') ) a
)c where c.Place=3
结果：
取出表A中第31到第40记录(SQLServer,以自动增长的ID作为主键，注意:数据不是连续的)
select top 10 * from tableA where ID not in (select top 30 ID from tableA)
team表里只有一个字段name，共有4条纪录，分别是a，b，c，d，对应四个球队，现在四个球队进行比赛，用sql语句显示所有可能的比赛组合
select * from timp a, timp b where a.name > b.name
有多条重复数据时，只保留一条
delete from Btype_CustomColumns
where typeid in (select typeid from Btype_CustomColumns group by typeid having count(typeid) > 1)
and dbo.Btype_CustomColumns.ID not in (select min(dbo.Btype_CustomColumns.ID) from Btype_CustomColumns group by typeid having count(typeid)>1)
转换函数：convert()
说明：一般在时间类型(datetime, smalldatetime)与字符串类型(nchar, nvarchar, char, varchar)相互转换的时候才用到
tyle数字在转换时间时的含义如下：
------------------------------------------------------------------------------------------------------------Style(2位表示年份) | Style(4位表示年份) | 输入输出格式 ------------------------------------------------------------------------------------------------------------0 | 100 | mon dd yyyy hh:miAM(或PM) ------------------------------------------------------------------------------------------------------------1 | 101 美国 | mm/dd/yy ------------------------------------------------------------------------------------------------------------2 | 102 ANSI | yy-mm-dd ------------------------------------------------------------------------------------------------------------3 | 103 英法 | dd/mm/yy ------------------------------------------------------------------------------------------------------------4 | 104 德国 | dd.mm.yy ------------------------------------------------------------------------------------------------------------5 | 105 意大利 | dd-mm-yy ------------------------------------------------------------------------------------------------------------6 | 106 | dd mon yy ------------------------------------------------------------------------------------------------------------7 | 107 | mon dd,yy ------------------------------------------------------------------------------------------------------------8 | 108 | hh:mm:ss ------------------------------------------------------------------------------------------------------------9 | 109 | mon dd yyyy hh:mi:ss:mmmmAM(或PM)------------------------------------------------------------------------------------------------------------10 | 110 美国 | mm-dd-yy ------------------------------------------------------------------------------------------------------------11 | 111 日本 | yy/mm/dd ------------------------------------------------------------------------------------------------------------12 | 112 ISO | yymmdd ------------------------------------------------------------------------------------------------------------13 | 113 欧洲默认值 | dd mon yyyy hh:mi:ss:mmm(24小时制) ------------------------------------------------------------------------------------------------------------14 | 114 | hh:mi:ss:mmm(24小时制) ------------------------------------------------------------------------------------------------------------20 | 120 ODBC 规范 | yyyy-mm-dd hh:mi:ss(24小时制) ------------------------------------------------------------------------------------------------------------21 | 121 | yyyy-mm-dd hh:mi:ss:mmm(24小时制) ------------------------------------------------------------------------------------------------------------
1、时间转换为指定形式
select convert(nvarchar(20), getdate(), 120)
select convert(nvarchar(100), getdate(), 109)
select convert(nvarchar(20), getdate(), 102)
select convert(nvarchar(100), getdate(), 113)
结果：
2、转为XML格式
IF OBJECT_ID('Orders','U') IS NOT NULL
DROP TABLE Orders
CREATE TABLE Orders
(
ID bigint primary key not null,
ProductID int,
ProductName nvarchar(50),
Price float,
Scheme text,
Created datetime default(getdate())
)
INSERT INTO Orders(ID,ProductID,ProductName,Price,Scheme)
VALUES(201405130001,101,'Card',10.899,'<xml><ProductID>101</ProductID><ProductName>Card</ProductName></xml>')
结果：
存储过程：
--每次执行都进行重新编译
--1、存储过程，重复编译
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_WITH_RECOMPILE', N'P') IS NOT NULL
DROP procedure PROC_SELECT_STUDENTS_WITH_RECOMPILE;
GO
CREATE procedure PROC_SELECT_STUDENTS_WITH_RECOMPILE
with recompile --重复编译
AS
SELECT * FROM Students
GO
--加密存储过程
--2、查询存储过程，进行加密
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_WITH_ENCRYPTION', N'P') IS NOT NULL
DROP procedure PROC_SELECT_STUDENTS_WITH_ENCRYPTION;
GO
CREATE procedure PROC_SELECT_STUDENTS_WITH_ENCRYPTION
with encryption --加密
AS
SELECT * FROM Students
GO
set ansi_nulls on
表示对空值(null)对等于(=)或不等于(<>)进行判断时，遵从 SQL-92 规则。为off时不遵从SQL-92规则
SQL-92规则中，在对空值(null)进行等于(=)或不等于(<>)比较时，取值为false。也就是说:
- 即使是表中字段column_name中包含空值(null),在进行条件判断 where column_name = NULL 时，该select查询语句返回的数据是空的/返回零行。
- 即使是表中字段column_name中包含非空值，在进行条件判断 where column_name <> NULL时，该select查询语句返回的数据是空的/返回零行。
set quoted_identifier on
on：表示使用 引用标识符，标识符可以用双引号分隔，但是，文字必须用单引号分隔；off：表示标识符不能用双引号分隔，否则标识符会被当做字符串值来返回，不再是字符来返回。而且，文字部分必须用单引号或双引号分隔。
数据库操作：
--修改数据库的名称:
sp_renamedb 'old_name', 'new_name'
--备份数据库：
backup database ttt to disk = 'F:\BaiduNetdiskDownload\t.bak'
--还原数据库：
use master
restore database test_aa from disk = 'F:\BaiduNetdiskDownload\t.bak'
--分离数据库：
use master
exec sp_detach_db @dbname=N'数据库名'
--附加数据库：
exec sp_attach_db @dbname=N'数据库名',@filename1=N'.mdf的文件路径',@filename2=N'.ldf的文件路径'
--列出数据库里所有的表名：
select name from sysobjects where type='U' --U代表用户
--列出表里的所有的列名：
select name from syscolumns where id=object_id('TableName')
