sqlserver常用SQL语句整理

15年10月份，大概一年半以前的某个工作日凌晨2点钟开始整理的sql语句。

创建数据库

CREATE DATABASE 数据库名
ON PRIMARY     --默认就属于PRIMARY主文件组，可省略
(
 NAME='  ',  --主数据文件的逻辑名
 FILENAME='D:\project\stuDB_data.mdf',  --主数据文件的物理名
 SIZE=5mb,  --主数据文件初始大小
 MAXSIZE=100mb,  --主数据文件增长的最大值
 FILEGROWTH=15%   --主数据文件的增长率
)
LOG ON
(
  NAME='  ',
  FILENAME='D:\project\stuDB_log.ldf',
  SIZE=2mb,
  FILEGROWTH=1MB
)
GO

删除数据库

DROP DATABASE 数据库名 
USE master  --设置当前数据库为master，以便访问sysdatabases表
GO
IF  EXISTS（SELECT * FROM  sysdatabases WHERE  name ='stuDB'）
   DROP DATABASE stuDB

SqlServer的主要数据类型

Image:可用来存储图像
Char:固定长度的非Unicode字符数据
Varchar:可变长度非 Unicode 数据
Nchar: 固定长度的Unicode 数据
Nvarchar:可变长度的Unicode 数据
DataTime：日期和时间
int,smallint:整数
Money:货币数据类型
Bit:布尔

创建表

CREATE TABLE  表名
 (
    字段1 数据类型 列的特征，
    字段2 数据类型 列的特征，
     ...
)

USE stuDB   -数据库名 
GO
CREATE  TABLE  stuInfo    /*-创建学员信息表-*/
(
 stuName  VARCHAR(20)  NOT  NULL ,  --姓名，非空（必填）
 stuNo   CHAR(6)  NOT  NULL,   --学号，非空（必填）
 stuAge  INT  NOT  NULL,  --年龄，INT类型默认为4个字节
 stuID  NUMERIC(18,0),     --身份证号
 stuSeat   SMALLINT  IDENTITY (1,1),   --座位号，自动编号
 stuAddress   TEXT   --住址，允许为空，即可选输入
) 
GO

创建表：保证数据的完整性 = 实施完整性约束

删除表

DROP TABLE 表名
USE stuDB   --将当前数据库设置为stuDB ，以便在stuDB数据库中建表
GO
IF EXISTS（SELECT * FROM  sysobjects  WHERE  name=’stuInfo’ ）
    DROP  TABLE  stuInfo
CREATE  TABLE  stuInfo   /*-创建学员信息表-*/
(
…..
) 
GO

添加约束

ALTER TABLE 表名  
     ADD CONSTRAINT 约束名  约束类型  具体的约束说明
--	主键（Primary Key）约束：如 PK_stuNo
--	唯一（Unique Key）约束：如 UQ_stuID(允许为空,不能重复)
--	默认（Default Key）约束：如 DF_stuAddress(当插入记录时该字段没有赋值，会使用默认值)
--	检查（Check Key）约束：如 CK_stuAge(列字段的内容必须符合要求,通常是一个取值范围)
--	外键(Foreign Key)约束：如 FK_stuNo 
ALTER TABLE stuInfo 
     ADD CONSTRAINT PK_stuNo PRIMARY KEY (stuNo)
ALTER TABLE stuInfo 
    ADD CONSTRAINT UQ_stuID UNIQUE (stuID)
ALTER TABLE stuInfo 
    ADD CONSTRAINT DF_stuAddress 
          DEFAULT ('地址不详') FOR stuAddress
ALTER TABLE stuInfo 
    ADD CONSTRAINT CK_stuAge 
        CHECK(stuAge BETWEEN 15 AND 40)
ALTER TABLE stuMarks
   ADD CONSTRAINT FK_stuNo          
       FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo)
GO

主外键关系

1. 当主表中没有对应的记录时，不能将记录添加到子表
   成绩表中不能出现在学员信息表中不存在的学号；
2. 不能更改主表中的值而导致子表中的记录孤立
   把学员信息表中的学号改变了，学员成绩表中的学号也应当随之改变；
3. 子表存在与主表对应的记录，不能从主表中删除该行
   不能把有成绩的学员删除了
4. 删除主表前，先删子表
   先删学员成绩表、后删除学员信息表

删除约束

ALTER TABLE 表名  
      DROP CONSTRAINT 约束名

运算符

= > < >= <= <>不等于 !

通配符

‘_’	一个字符	A Like ‘C_’
%	任意长度的字符串	B Like ‘CO_%’
[ ]	括号中所指定范围内的一个字符	C Like ‘9W0[1-2]’
[^]	不在括号中所指定范围内的一个字符	D Like ‘%[A-D][^1-2]’

逻辑表达式

AND	逻辑与	1 AND 1 = 1; 1 AND 0 = 0; 0 AND 0 = 0;
OR	逻辑或	1 OR 1 = 1; 1OR 0 = 1; 0 OR 0 = 0;
NOT	逻辑非	NOT 1 = 0; NOT 0 = 1;

插入数据行

InSert  [INTO] <表名>  [列名] Values <值列表>
INSERT INTO 	Table_1 (id,loginId,LoginPwd) 
values (3,4,5)

插入多行数据(一个表中数据引入另一个表/建临时表)

应用：复制学员表Student为Table_1，包含表结构和全部的数据
语法：
Insert Into <表名>(列名)
Select <列名>
From <源表名>

INSERT INTO Table_1(id, loginId, LoginPwd) 
SELECT Id,Name,Number
FROM Student

INSERT INTO tb_TB(id, number, 数学成绩) 
SELECT A.id,A.学号,A.数学成绩 
FROM (SELECT  id,number As 学号,Math As 数学成绩
FROM tb_Score
WHERE Math between 60 and 100
ORDER BY tb_Score.number           ------
) A

除非另外还指定了TOP 或FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

INSERT INTO tb_TB(id, number, 数学成绩) 
SELECT A.id,A.学号,A.数学成绩 
FROM (SELECT  id,number As 学号,Math As 数学成绩
FROM tb_Score
WHERE Math between 60 and 100
--ORDER BY tb_Score.number
) A

//(9 行受影响)

一般常用的创建临时表的方法是:
Select <列名>
Into <表名>(列名)
From <源表名> –若不存在自己创建，若存在提示已存在

select  A.id,A.学号,A.数学成绩 
into tb_TB
from(SELECT  id,number As 学号,Math As 数学成绩
FROM tb_Score
WHERE Math between 60 and 100
--ORDER BY tb_Score.number
) A

//消息 2714，级别 16，状态 6，第 1 行 数据库中已存在名为 ‘tb_TB’ 的对象。

select  A.id,A.学号,A.数学成绩 
into tb_TC
from(SELECT  id,number As 学号,Math As 数学成绩
FROM tb_Score
WHERE Math between 60 and 100
--ORDER BY tb_Score.number
) A

select * from tb_TC
--新表中自动创建的id，number,Math与源表中数量，类型一致

Q：在数据库表中，数据行位置并不重要，但是一个单位中要根据奇数行和偶数行的数据来汇总，在这个汇总的基础上再得到一个数值，因此，要查询数据库表的奇数行和偶数行的总数。
A：新建一个临时表，从原表中任选一列数据存入临时表，根据新的标识列，进行奇偶判断。

SELECT 	
IDENTITY(int,1,1) AS ID 
INTO TEMPTABL
FROM tb_TC

select * from TEMPTABL

SELECT count(ID) AS 奇数列汇总 
FROM TEMPTABL
WHERE ID%2 <> 0

SELECT count(ID) AS 偶数列汇总 
FROM TEMPTABL 
WHERE ID%2 = 0

更新数据行

语法：
Update <表名> Set <列名 = 更新值>
[Where <更新条件>]

UPDATE Students SET SSEX = 0

UPDATE Scores
SET Scores = Scores + 5
WHERE Scores <= 95

删除数据行

语法：Delete From <表名> [Where <删除条件>]

DELETE FROM Table_3
where s = '6'

删除表

语法：Truncate Table <表名>

TRUNCATE TABLE Table_3

DELETE FROM Table_3    --与上面的语句等价

查询

语法:
SELECT <列名>
FROM <表名>
[WHERE <查询条件表达式>]
[ORDER BY <排序的列名>[ASC或DESC]]

SELECT id,Name,Number 
FROM Student 
WHERE SEX = 0 
ORDER BY ClassNo

查询全部的行和列:

SELECT * FROM Students

查询部分行:

SELECT SCode,SName,SAddress FROM Students
WHERE SAddress = '河南新乡'

SELECT SCode,SName,SAddress FROM Students
WHERE SAddress <> '河南新乡'

使用AS来命名列:

SELECT SCode AS 学员编号,SName AS学员姓名,SAddress AS 学员地址  
FROM Students
WHERE SAddress <> '河南新乡'

SELECT Name + '.' + Number AS '姓名' 
FROM Student

SELECT Name  AS '男' 
FROM Student
where Sex=1

使用=来命名列:

SELECT 'A' = Name+'.'+Number 
FROM Student

查询空行:

SELECT Name FROM Student WHERE Sex IS NULL

使用常量列(‘’AS列名,列名下元素赋值’’):

SELECT 姓名=Name,学号= Number,'.Net班' AS 班级
FROM Student

--查询Name命名为ok
SELECT Name AS ok,学号= Number,'.Net班' AS 班级
FROM Student

限制固定行数:

SELECT TOP 5 Name, Number
FROM Student WHERE Sex = 0

返回百分之多少行:

SELECT TOP 40 PERCENT Name, Address
FROM Student WHERE Sex = 0

SELECT TOP 1 PERCENT Name, Address
FROM Student WHERE Sex = 0
--若存在至少返回一行

升序排列:

SELECT id As 学员编号,(Math*0.9+5) As 综合成绩
FROM Tb_Score
WHERE (Math*0.9+5)>60
ORDER BY number

//消息8115，级别16，状态8，第1 行 将varchar 转换为数据类型numeric 时出现算术溢出错误。

降序排列:

…
ORDER BY number DESC

按多列排序:

SELECT  number As 学号,Math As 数学成绩
FROM tb_Score
WHERE Math>60
ORDER BY tb_Score.number
--tb_Score可省略

模糊查询

LIKE:
Q:以下的SQL语句：
SELECT * FROM 数据表
WHERE 编号 LIKE ‘00[^8]%[A,C]%’
可能会查询出的编号值为(B)。
A、9890ACD
B、007_AFF
C、008&DCG
D、KK8C

IS NULL(把某一字段中内容为空的记录查询出来):

SELECT ID, Name FROM Student WHERE Phone Is null

BETWEEN:

SELECT  number As 学号,Math As 数学成绩
FROM tb_Score
WHERE Math between 60 and 100
ORDER BY tb_Score.number

IN(把某一字段中内容与所列出的查询内容列表匹配的记录查询出来):

SELECT ID, Number FROM tb_address WHERE address 
IN ('北京','沈阳','南京')

聚合函数

SUM:

SELECT SUM(Math) FROM Tb_Score WHERE English = '95'

AVG:

SELECT AVG(Math) AS 平均成绩 
From Tb_Score WHERE Math >=60

MAX、MIN:

SELECT AVG(Math) AS 平均成绩, MAX (Math) AS 最高分, 
MIN (Math) AS 最低分 From tb_Score WHERE Math >=60

COUNT/COUNT(*):

SELECT COUNT (*)  AS 及格人数 From tb_Score 
WHERE Math>=60

分组汇总

GROUP BY:
指定 GROUP BY 时，如果 SELECT 子句中包含聚合函数, 则计算每组的汇总值。

SELECT math,AVG(Math) as 平均成绩
FROM tb_Score
GROUP BY math

选择列表中任一非聚合表达式内的所有列都应包含在 GROUP BY 列表中，或者 GROUP。
BY 表达式必须与选择列表表达式完全匹配。否则列无法对应。

SELECT number,math
FROM tb_Score
GROUP BY number,math

多列分组:
用于两个或多个元素在两个或多个列值相同的情况。

SELECT Number AS 学员编号,courseId AS 内部测试, 
AVG(Score) AS 内部测试平均成绩 ge
FROM studentScores
GROUP BY Number,courseId
--转化为平均成绩

HAVING:
输出两个或多个元素在两个或多个列值相同,例如成绩出现多次的分组的平均成绩：

SELECT Number AS 学员编号, courseId AS 内部测试, 
AVG(Score) AS 内部测试平均成绩
FROM studentScores
GROUP BY Number,courseId
HAVING COUNT(Score)>1

select * from	
(SELECT Number AS 学员编号, courseId AS 内部测试,
AVG(Score) AS 内部测试平均成绩 
FROM studentScores
GROUP BY Number,courseId					
HAVING COUNT(Score)>1) A
where 学员编号=12

if((select count (*) a from 
(SELECT distinct Number from studentScores) A )
<>(select count (Number) b 
from studentScores ))
begin
print '有重复'
end
else
begin
print '无重复'
end

对比:
WHERE子句从数据源中去掉不符合其搜索条件的数据。
GROUP BY子句搜集数据行到各个组中，统计函数为各个组计算统计值。
HAVING子句去掉不符合其组搜索条件的各组数据行。

多表联结查询

分类：
1.内联结(INNER JOIN)
2.外联结
①.左外联结(LEFT JOIN)
②.右外联结(RIGHT JOIN)
③.完整外联结(FULL JOIN)
3.交叉联结(CROSS JOIN)

SELECT S.SName,C.CourseID,C.Score 
From Score AS C
INNER JOIN Students AS S
ON C.StudentID = S.SCode

SELECT S.SName,C.CourseID,C.Score 
From Students AS S
INNER JOIN Score AS C
ON C.StudentID = S.SCode

SELECT Students.SName, Score.CourseID, Score.Score
FROM Students,Score --FROM 表A表B
WHERE Students.SCode = Score.StudentID

//上面三个结果相同

SELECT S.SName,C.CourseID,C.Score 
From Students AS S
INNER JOIN Score AS C
ON C.StudentID <> S.SCode

三表联结:

SELECT 
--表A,B,C内要显示的列
S.SName AS 姓名,C.Score AS 成绩   --,CS.CourseName AS 课程
FROM Students AS S 
INNER JOIN Score AS C ON (S.SCode = C.StudentID)
INNER JOIN Course AS CS ON (CS.CourseID = C.CourseID)

左外联结(LEFT JOIN /LEFT OUTER JOIN):

SELECT S.SName --,C.CourseID,C.Score 
From Students AS S
LEFT JOIN Score AS C
ON C.StudentID = S.SCode

SELECT C.CourseID,C.Score --,S.SName
From Score AS C
LEFT JOIN Students AS S
ON C.StudentID = S.SCode

//上面两个结果相同

T-SQL

局部变量：
局部变量必须以标记@作为前缀，如@age。
局部变量的使用也是先声明，再赋值。
全局变量：
全局变量必须以标记@@作为前缀，如@@version。
全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值。
声明局部变量：DECLARE @变量名 数据类型

DECLARE @name varchar(8)

DECLARE @seat int

赋值:
SET @变量名 = 值
或 SELECT @变量名 = 值

SET @name='张三'     --必须确保筛选出的记录只有1条

SELECT @name=stuName FROM stuInfo 
WHERE stuNo='s25302'

Eg：编写T-SQL查找李文才的左右同桌。
/–查找李文才的信息–/

DECLARE @name varchar(8)  --学员姓名
SET @name='李文才'        --使用SET赋值
SELECT * FROM stuInfo WHERE stuName = @name
/*--查找李文才的左右同桌--*/
DECLARE @seat int  --座位号
SELECT @seat=stuSeat FROM stuInfo  --使用SELECT赋值
   WHERE stuName=@name
SELECT * FROM stuInfo 
   WHERE (stuSeat = @seat+1) OR (stuSeat = @seat-1)
GO

全局变量

INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge)   
VALUES('武松','s25328','男','23')
--如果大于0表示上一条语句执行有错误
print '当前错误号'+convert(varchar(5),@@ERROR)
print '刚才报名的学员，座位号为:'+convert(varchar(5),@@IDENTITY ) 
  Go

IF-ELSE语句

ELSE是可选部分,如果有多条语句,才需要BEGIN-END语句块。
SQL中的IF-ELSE语句：

IF (条件)
 BEGIN
    语句1
    语句2
    …
 END
ELSE
 BEGIN
    语句;
    语句;
    …
 END

WHILE循环语句

BREAK表示退出循环,如果有多条语句才需要BEGIN-END语句块。
SQL中的WHILE语句：

WHILE 条件）
 BEGIN
    语句1
    语句2
    ……
    BREAK
 END

DECLARE @n int
WHILE(1=1) --条件永远成立
  BEGIN
    SELECT @n=COUNT(*) FROM stuMarks 
            WHERE writtenExam<60    --统计不及格人数
    IF (@n>0)
       UPDATE stuMarks     --每人加分
            SET writtenExam=writtenExam+2 
    ELSE
       BREAK    --退出循环
  END
print '加分后的成绩如下：'
SELECT * FROM stuMarks

CASE-END多分支语句

CASE 
  WHEN 条件1 THEN  结果1
  WHEN 条件2 THEN  结果2
  ……
  ELSE 其他结果
END

print  'ABCDE五级显示成绩如下：'
SELECT stuNo,
成绩=CASE  
      WHEN writtenExam<60 THEN 'E'
      WHEN writtenExam BETWEEN 60 AND 69 THEN 'D'
      WHEN writtenExam BETWEEN 70 AND 79 THEN 'C'
      WHEN writtenExam BETWEEN 80 AND 89 THEN 'B'
      ElSE 'A'
     END
FROM stuMarks

USE stuDB
GO
SELECT 考号=ExamNo,学号=stuNo,笔试=writtenExam,机试=labExam,
  平均分=(writtenExam+labExam)/2,
  等级=CASE  
       WHEN (writtenExam+labExam)/2<60 THEN '不及格'
       WHEN (writtenExam+labExam)/2 BETWEEN 60 AND 69 THEN '差'
       WHEN (writtenExam+labExam)/2 BETWEEN 70 AND 79 THEN '中'
       WHEN (writtenExam+labExam)/2 BETWEEN 80 AND 89 THEN '良'
       ElSE '优'
      END
FROM stuMarks

批处理语句：SQLServer规定：如果是建库、建表语句、以及我们后面学习的存储过程和视图等，则必须在语句末尾添加 GO 批处理标志。

Eg:对机试成绩进行反复加分，直到平均分超过分为止。请编写T-SQL语句实现。
90分以上： 不加分
80－89分： 加分
70－79分： 加分
60－69分： 加分
60分以下： 加分

SELECT * FROM stuMarks  --原始成绩
DECLARE @labAvg INT
WHILE(1=1)
 BEGIN
    UPDATE stuMarks 
      SET labExam=
        CASE
            WHEN labExam<60 THEN labExam+5
            WHEN labExam between 60 AND 69 THEN labExam+3
            WHEN labExam between 70 AND 79 THEN labExam+2 
            WHEN labExam between 80 AND 89 THEN labExam+1
            ELSE labExam
        END
     SELECT @labAvg=AVG(labExam) FROM stuMarks
     IF @labAvg>=85
        BREAK
 END
SELECT * FROM stuMarks  --加分后的成绩

事务

假定张三的账户直接转账1000元到李四的账户。

CREATE TABLE bank
(
    customerName CHAR(10),  --顾客姓名
    currentMoney MONEY     --当前余额
)
GO

ALTER TABLE bank
   ADD CONSTRAINT CK_currentMoney
--添加约束：根据银行规定，账户余额不能少于1元，否则视为销户 
       CHECK(currentMoney>=1)
GO

INSERT INTO bank(customerName,currentMoney)
        VALUES('张三',1000)
INSERT INTO bank(customerName,currentMoney)
        VALUES('李四',1)

haha，后面整理的资料丢失了，只到这了。