SQLServer语句

转载：https://www.cnblogs.com/genesis/p/4673149.html

 

1、创建数据库
create database 数据库名字 
2、删除数据库
drop database 数据库名字
3、创建表(identity(1,1)自动增长，倍数为1,primary key设置主键)
create table UserInfo
(
Id bigint identity(1,1) primary key,
Name nvarchar(50) not null default(''),
Password nvarchar(50) not null
)

4、修改数据类型(alter:改变，column:列)
alter table UserInfo
alter column Name nvarchar(50) not null

5、增加一列
alter table UserInfo
add Height nvarchar(50) 

6、删除一列
alter table UserInfo
drop column Height

7、添加主键
alter table UserInfo add primary key(Id)

8、插入(增)
insert into UserInfo(Name,Password,Height) values('admin','123','18')
9、删除(删)
delete from UserInfo where id=4
DELETE FROM table_name 删除所有行

truncate table aaa;
10、更新(改)
update UserInfo set Name='ccc' where id=3

11、返回不同的值,相同值合并，distinct
select distinct Name from UserInfo
12、模糊查询(%替代一个或多个字符 _仅替代一个字符 []中括号内可填多个值匹配任意值，加!取反)
select * from UserInfo
where Name like '%覆盖%'

带条件的查询-模糊查询(都是针对字符串操作的)

查询所有姓张的同学

Select * from student where left(sName,1)=‘张‘ 看上去很美,如果改成查询名字中带亮的学生怎么做？

换一种做法 like

Select * from student where sName like ‘张%’ 会把所有姓张的都查询到，现在我想查询姓张并且名字是一个字的学生？

Select * from student where sName like ‘%亮%’ _ 、 % 、 [] 、 ^

^只有MSSQL Server支持，其他DBMS用not like。

%通配符多字符匹配的通配符，它匹配任意次数（零或多个）出现的任意字符

_通配符单字符匹配，它匹配单个出现的字符

[] 只匹配一个字符 并且这个字符必须是[]范围内的 [0-9] [a-z]

not与like一起使用：not like ….

要通配_、%、[、^这些字符怎么办？[_]、[%]、[ [ ]、^(不需要放到中括号里，因为^只有放到中括号中才认为是通配符)

 

13、按指定列升序或降序,asc(desc)
select * from UserInfo 
order by Height asc(desc)
14、显示前十条数据，top操作符
select top 10 * from UserInfo
显示50%数据
select top 50 percent * from UserInfo
15、显示Name值为aaa和ccc，in操作符
select * from UserInfo
where Name in ('aaa','ccc')
16、别名as
select Name as 用户,Password as 密码 from UserInfo
17、连表查询，两个表连在一起查询
select p.LastName,p.FirstName,o.OrderNo
from Persons as p,Orders as o
where P.Id_P=o.Id_P
18、join连表查询并排序
JOIN: 如果表中有至少一个匹配，则返回行 与inner join功能一样
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行 
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行 
FULL JOIN: 只要其中一个表中存在匹配，就返回行

select p.LastName,p.FirstName,o.OrderNo
from Persons as p
inner join Orders as o
on p.Id_P=o.Id_P
order by o.OrderNo
INNER JOIN 关键字在表中存在至少一个匹配时返回行，如果 "Persons" 中的行在 "Orders" 中没有匹配，就不会列出这些行。

---

select top 5 * from A order by classesId  获得最小的5个值
--获取最大的5个值并排序

select * from
(
    select TOP 5 * from A order by classesId desc
) as t order by classesId

select top 5 * from A order by classesId desc,numId asc 获得最小的5个值，如果numId相同则排序
select top 10 percent * from A order by classesId desc  获得最大的10%的值 
select distinct classesId, name from A  去除重复数据
select * from A where classesId between 20 and 30  查询classesId在20-30分之间的所有值一
select * from A where classesId >20 and classesId<30  查询classesId在20-30分之间的所有值二
select * from A where classesId=4 or classesId=8 or classesId=18 查询classesId为4，8，18的所有值
select * from A where classesId in(4,8,18) 查询classesId为4，8，18的所有值
select * from A where LEFT(name,1)='J'  查询所有第一个字母为J的所有值
select * from A where name like 'J%'  查询所有第一个字母为J的所有值
select * from A where name like 'J_'  查询所有第一个字母为J并且后面只有一个任意字符的所有值
select * from A where name like 'J[0-9a-z]' 查询所有第一个字母为J并且后面为0-9或a-z的所有值
select * from A where name not like 'J[0-9a-z]' 与上取反
select * from A where classesId is null 查询classesId为null的所有值
select * from A where classesId is not null 与上取反
select COUNT(name) as '名字总和' from A group by gender 分组
select * from C union select * from D 联合查询，会去除重复值
select * from C union all select * from D 联合查询，保留重复值
select LEN(name) from C 计算字符串长度（字符的个数。）
select datalength(name) from C  计算字符串所占用的字节数，不属于字符串函数。
select left(name,2) from c 从左截取字符串
select right(name,2) from c  从右截取字符串
select substring(name,1,2) from c 索引从1开始截取2个字符串

---

一、基础
1、说明：创建数据库
CREATE DATABASE 数据库名字
create database OneDatabase
2、说明：删除数据库
drop database 数据库名字
drop database OneDatabase
4、说明：创建新表
create table 新表名字(col1 type1 [not null] [primary key],col2 type2 [not null],..)
create table Student
(
    Id int identity(1,1) primary key,
    Name nvarchar(50) not null,
    Pwd nvarchar(50) not null
)
根据已有的表创建新表： 
select * into newStudent from Student
5、说明：删除新表
drop table tabname 
6、说明：增加一个列
Alter table tabname add column col type
alter table Student add Age int not null(not null必须是表没有值的情况下)
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
7、说明：添加主键： Alter table 表名 add primary key(列名) 
alter table newStudent add primary key(Id)
8、说明：创建索引：create [unique] index idxname on tabname(col….) 
create unique index 索引名字 on 表名(列名) 
删除索引：drop index 索引名字 on 表名
注：索引是不可更改的，想更改必须删除重新建。
9、说明：创建视图：create view 视图名字 as select Name from newStudent 
删除视图：drop view 视图名字
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)，不消除重复行。 
注：使用运算词的几个查询结果行必须是一致的。 

二、提升
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1 不拷贝行数据
法二：select top 0 * into b from a  不拷贝行数据

3、说明：跨数据库之间表的拷贝
select * into newTable from Text.dbo.PlanTable 拷贝所有行
select top 5 * into newTable from Text.dbo.PlanTable  只拷贝前5行

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
方法一：
select t.name,t.classesId,y.className,y.id
from A AS t,B as y
where t.classesId=y.id
方法二：
select t.name,t.classesId,y.className,y.id
from A as t
join B as y
on t.classesId=y.id

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
select * from A where classesId between 5 and 20

9、说明：in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
具体实现：
关于数据库分页：
  declare @start int,@end int
  @sql  nvarchar(600)
  set @sql=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’
  exec sp_executesql @sql

注意：在top后不能直接跟一个变量，所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列，如果top后还有具体的字段，这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的，查询的结果后实际表中的不一致（逻辑索引中的数据有可能和数据表中的不一致，而查询时如果处在索引则首先查询索引）

17、说明：随机取出10条数据
select top 10 * from tablename order by newid()

18、说明：随机选择记录
select newid()

20、说明：列出数据库里所有的表名
select name from sysobjects where type='U' // U代表用户

21、说明：列出表里的所有的列名
select name from syscolumns where id=object_id('TableName')

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、收缩数据库

14：查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = '表名'

15、存储过程分页

create proc usp_page
@pageIndex int,
@pageSize int,
@pageCount int output
as
begin
    --查询表的记录
    select * from
    (
    select *,Rn=ROW_NUMBER() over(order by SerialNumber asc) from Member
    ) as TblMember
    where TblMember.Rn between(@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize

    --计算总条数
    declare @rdCount int
    select @rdCount=COUNT(*) from Member
    --计算页数
    set @pageCount=ceiling(@rdCount/(@pageSize*1.0))
end

declare @pc int
exec usp_page @pageSize=10,@pageIndex=1,@pageCount=@pc output
print @pc