一.数据库的SQL命令
1、创建数据库
CREATE DATABASE + 数据库名称
如果只用 “CREATE DATABASE 数据库名称”，DBMS会默认逻辑名称、文件组大小等属性.
2、修改数据库
ALTER DATABASE +数据库名称
（1）添加数据文件
ADD FILE(具体文件格式)
ADD LOG FILE(具体文件格式)
（2）删除数据库中的文件
REMOVE FILE 文件逻辑名称
（3）指定要修改的文件
MODIFY FILE(具体文件格式)
（4）添加文件组
ADD FILEGROUP 文件组名
（5）删除文件组
REMOVE FILEGROUP 文件组名
（6）修改文件组名称，或设置文件组的只读、读写，指定文件组为默认文件组
MODIFY FILEGROUP 文件组名
{
READ_ONLY|READ_WRITE,
| DEFAULT,
| NAME = 新文件组名 ｝
3、删除数据库
DROP DATABASE 数据库名称
4、查询数据库（分别用系统存储过显示数据库结构、文件信息、文件组信息）
Sp_helpdb [[@dbname=] 'name']
Sp_helpfile [[@filename =] 'name']
Sp_helpfilegroup [[@filegroupname =] 'name']
二.SQL中的数据类型
1、数值类型
2、字符串类型
3、时间日期类型
4、字段属性
（1）UnSigned
无符号的，声明了该列不能为负数
（2）ZEROFILL
位数不足用0来填充，如int（5）3=00003
（3）Auto_Crement
自动在上一条记录的基础上默认+1,通常用来设计唯一的主键，必须是整数类型,可定义起始值和步长
当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)
（4）NULL和NOT NULL
空值和非空值
NULL表示没有插入该列的值
NOT NULL表示该列必须有值
（5）DEFAULT
默认值
（6）其他
三.数据表的SQL命令
１、创建数据表
CREATE TABLE 表名
（列名，数据类型，表约束）
例：
CREATE TABLE S
( SNO VARCHAR(6),SN NVARCHAR(10),SEX NCHAR(1) DEFAULT '男', )
2、表的约束
（1）NULL约束和NOT NULL约束
NULL值不是０也不是空值，是指不确定值
NOT NULL指不允许出现空值
（２）UNIQUE约束
UNIQUE指唯一约束。
使用唯一约束可以确保在非主键列中不输入重复的值。
与PRIMARY KEY约束都具有强制唯一性。
一个表可以定义多个UNIQUE约束，而且允许NULL值，但系统为保证其唯一性,最多只可以出现一个NULL值。
UNIQUE约束用于强制在指定字段上创建一个 UNIQUE索引,缺省为非聚集索引，UNIQUE既可用于列约束,也可用于表约束。
（３）PRIMARY KEY主键约束
CONSTRAINT S_Prim PRIMARY KEY （列约束）
PRIMARY KEY （表约束）
//PRIMARY KEY 约束用于定义基本表的主键，起唯一标识作用，其值不能为空.
在一个基本表中只能定义一个 PRIMARY KEY约束,但可定义多个 UNIQUE约束。
对于指定为 PRIMARY KEY的一个列或多个列的组合,其中任何一个列都不能出现NULL值,而对于 UNIQUE所约束的唯一键,则允许为NULL。
不能为同一个列或一组列,既定义 UNIQUE约束,又定义 PRIMARY KEY约束。
（4）FOREIGN KEY外键约束
NOT NULL FOREIGN KEY REFERENCES 主表名(列名)
例：
CREATE TABLE SC
( SNO VARCHAR(6) NOT NULL FOREIGN KEY REFERENCES S(SNO),
CNO VARCHAR(6) NOT NULL FOREIGN KEY REFERENCES C(CNO),
CONSTRAINT S_C_Prim PRIMARY KEY (SNO,CNO))
外键用于建立两个表数据之间连接的一列或多列，可创建两个表之间的连接，这个列就称为第二个表的外键，包含外键的表称为从表。
包含外部键所引用的主键或唯一键称为主表，系统保证从表在外部键上的取值是主表中某一个主键值或唯一键，或者取空值.
（５）CHECK检查约束
CHECK （条件）
//用来检查字段值所在的范围
建立一个SC表,定义 Score的取值范围为0~100之间：
CREATE TABLE SC
( SNO VARCHAR(6)
CNO VARCHAR(6)
Score NUMERIC(4, 1) CHECK (Score>=0 AND Score<=100))
一个表中可以定义多个 CHECK约束。
每个字段只能定义一个 CHECK约束。
在多个字段上定义的 CHECK约束必须为表约束。
当执行 INSERT、 UPDATE语句时, CHECK约束将验证数据。
3、表的修改
ALTER TABLE 表名
（1）ADD方式：用于增加新列和完整性约束
ALTER TABLE 表名
ADD 列名 数据类型
（２）ALTER方式：用于修改某些列
ALTER TABLE 表名
ALTER COLUMN 列名 数据类型
不能改变列名。
不能将含有空值的列的定义修改为 NOT NULL约束。
若列中已有数据,则不能减少该列的宽度,也不能改变其数据类型。
只能修改 NULL/NOT NULL约束其他类型的约束在修改之前必须先将约束删除,然后再新添加修改过的约束定义。
（３）DROP方式：用于删除完整性定义
ALTER TABLE<表名>
DROP CONSTRAINT<约束名>
例：删除S表中的主键。
ALTER TABLE S
DROP CONSTRAINT S Prim
4、删除数据表
DROP TABLE 表名
四.数据表查询的SQL命令
1、无条件查询
SELECT 列名
FROM 表名
//*表示表的全部列名
SELECT DISTINCT Sno
FROM SC
//DISTINCT表示消除Sno列中的重复项
SELECT Sn AS Name,Sage,Sno
FROM SC
//AS表示别名，输出别名
2、条件查询
运算符含义=、>、<、>=、<= 、!=、<>比较大小AND、OR、NOT多重条件BETWEEN AND确定范围IN确定集合LIKE字符匹配IS NULL空值
SELECT 列名
FROM 表名
WHERE 条件
例：查询Cno为C1的学号和成绩
SELECT Sno,Score
FROM SC
WHERE Cno='C1'
（1）多重条件查询 优先级从高到低:NOT、AND、OR
例：查询工资在1000元~1500元之间的教师的教师号、姓名及职称。
SELECT Tno,Tn,Prof
FROM T
WHERE Sal BETWEEN 1000 AND 1500
//
SELECT Tno,Tn,Prof
FROM T
WHERE Sal>=1000 AND Sal<=1500
（2）确定集合：利用"IN"操作可以查询属性值属于指定集合的元组
例：查询选修C1或C2的学生的学号、课程号和成绩。
SELECT Sno,Cno,Score
FROM SC
WHERE Cno IN('C1','C2')
//OR
SELECT Sno,Cno,Score
FROM SC
WHERE Cno='C1' OR Cno='C2'
利用“NOT IN”可以查询指定集合外的元组。
例：查询没有选修C1和C2的学生学号、课程号和成绩
SELECT Sno,Cno,Score
FROM SC
WHERE Cno NOT IN('C1','C2')
//
SELECT Sno,Cno,Score
FROM SC
WHERE (Cno<>'C1')AND(Cno<>'C2')
（3）部分匹配查询
<属性名> LIKE <字符串常量>
通配符实例%ab%,’ab‘后可接任意字符串_(下划线)‘a_b’,'a’与’b’之间可有一个字符[ ][0-9],0~9之间的字符[^ ][^0-9],不在0~9之间的字符
例：查询所有姓张的教师的教师号和姓名。
SELECT Tno,Tn
FROM T
WHERE Tn LIKE'张%'
例：查询姓名中第二个汉字是“乐”的教师号和姓名。
SELECT Tno,Tn
FROM T
WHERE Tn LIKE'_乐%'
（4）空值查询：某个字段没有值称为具有空值(NUL)通常没有为一个列输入值时,该列的值就是空值。
例：查询没有考试成绩的学生的学号和相应的课程。
SELECT Sno,Cno
FROM SC
WHERE Score IS NULL
3、常用库函数及统计查询
函数功能AVG按列计算平均值SUM按列计算值的总和MAX求一列中的最大值MIN求一列中的最小值COUNT按列值统计个数
例：求学号为S1的学生的总分和平均分。
SELECT SUM(Score) AS TotalScore,AVG(Score) AS AvgScore
FROM SC
WHERE (Sno='S1')
注意：在使用库函数进行查询时，通常要给查询的每一项内容加别名，否则查询结果就不会显示列名.
例：求选修C1号课程的最高分、最低分及之间相差的分数。
SELECT MAX(Score) AS MaxScore,MIN(Score) AS MinSore,MAX(Score)-MIN(Score)AS Diff
FROM SC
WHERE(Cno='C1')
例：求计算机系学生的总数。
SELECT COUNT(Sno)
FROM S
WHERE Dept='计算机'
例：求学校中共有多少个系。
SELECT COUNT(DISTINCT Dept)AS DeptNum
FROM S
4、分组查询
（1）GROUP BY语句：可以将查询结果按属性列或属性列组合在行的方向上进行分组,每组在属性列或属性列组合上具有相同的值。
例：查询每个教师的教师号及其任课的门数。
SELECT Tno,COUNT(*)AS Cnum
FROM TC
GROUP BY Tno
//GROUP BY子句按TNo的值分组,所有具有相同TNo的元组为一组,对每一组使用函数COUNT进行计算,统计出各位教师任课的门数。
（2）HAVING语句：若在GROUP BY子句分组后还要按照一定的条件进行筛选,则需使用 HAVING子句。
例：查询选修两门以上(含两门)课程的学生的学号和选课门数。
SELECT Sno，COUNT(*)AS SCNum
FROM SC
GROUP BY Sno
HAVING (COUNT(*)>=2)
//GROUP BY子句按Sno的值分组,所有具有相同Sno的元组为一组,对每一组使用函数 COUNT进行计算,统计出每个学生选课的门数。
HAVING子句去掉不满足 COUNT(*)>=2的组。
注意：当在一个SQL查询中同时使用 WHERE子句, GROUP BY子句和 HAVING子句时,其顺序是WHERE、 GROUP BY、HAVING。
WHERE与 HAVING子句的根本区别在于作用对象不同:
(1)WHERE句作用于基本表或视图,从中选择满足条件的元组；
(2)HAVING子句而作用于组,选择满足条件的组，必须用在 GROUP BY子句之后,但GROUP BY子句可没有HAVING子句.
5、查询结果的排序
（1）ORDER BY语句：当需要对查询结果排序时,应该使用 ORDER BY子句, ORDER BY子句必须出现在其他子句之后。
排序方式可以指定：
ORDER BY ASC//升序排列
ORDER BY DESC//降序排列
五.数据的操作
1、使用INSERT语句添加数据
INSERT INTO 表名或视图名
（添加数据的列名）VALUES（添加的数据）
注：如果要向表中添加所有的字段，可以省略要插入的数据的列名。
2、使用UPDATE修改指定数据
UPDATE 表名或视图名
SET 修改的内容
WHERE 指定条件
3、使用DELETE语句删除指定数据
DELETE FROM 表名或试图名
WHERE 指定条件
注：在DELETE语句中如果不指定WHERE子句时，则删除表中的所有记录。
六.数据表的操作（行转列）
数据准备：
CREATE TABLE TestTable
(
[Id] [int] IDENTITY(1 , 1) NOT NULL ,
[UserName] [nvarchar](50) NULL ,
[Subject] [nvarchar](50) NULL ,
[Source] [numeric](18, 0) NULL
)
ON [PRIMARY]
GO
INSERT INTO TestTable ([UserName] , [Subject] , [Source])
SELECT N'张三' , N'语文' , 60
UNION ALL
SELECT N'李四' , N'数学' , 70
UNION ALL
SELECT N'王五' , N'英语' , 80
UNION ALL
SELECT N'王五' , N'数学' , 75
UNION ALL
SELECT N'王五' , N'语文' , 57
UNION ALL
SELECT N'李四' , N'语文' , 80
UNION ALL
SELECT N'张三' , N'英语' , 100
GO
1、静态行转列
静态行转列的特点是：已知有几个行要转成几个列。
代码如下：
select UserName 姓名 ,
sum(case Subject when '语文' then Source else 0 end) 语文 ,
sum(case Subject when '数学' then Source else 0 end) 数学 ,
sum(case Subject when '英语' then Source else 0 end) 英语
from TestTable
group by UserName
运行结果如下：
2、动态行转列
动态行转列，就是行数是变化的，转换成的列也是变化。有可能是过滤条件影响。也有可能数据增减导致列数增减。完全是动态，自动判断的。
代码如下：
declare @fusername varchar(30)
declare @fsubject varchar(30)
declare @fsource int
declare @sql varchar(500)
--1.创建临时表
create table #tmp1(姓名 varchar(10))
--2.第一次循环：构建表格动态列
declare mycursor cursor for
select [subject] from testtable group by [subject]
open mycursor
fetch next from mycursor into @fsubject
while (@@fetch_status=0)
begin
exec('alter table #tmp1 add ['+@fsubject+'] int not null default(0)')
fetch next from mycursor into @fsubject
end
close mycursor
DEALLOCATE mycursor
--select * from #tmp1
--3.插入唯一姓名
insert into #tmp1(姓名)
select [username] from testtable group by [username]
--select * from #tmp1
--4.第二次循环更新数据
declare mycursor2 cursor for
select username , [subject] , [Source] from testtable
open mycursor2
fetch next from mycursor2 into @fusername , @fsubject , @fsource
while (@@fetch_status=0)
begin
set @sql= 'update t1 set ['+@fsubject+'] = '+convert(varchar(10) , @fsource)+' from #tmp1 t1 where 姓名='''+@fusername+''' '
print @sql
exec(@sql)
fetch next from mycursor2 into @fusername , @fsubject , @fsource
end
close mycursor2
DEALLOCATE mycursor2
select * from #tmp1
truncate table #tmp1
drop table #tmp1
运行结果如下：
3、使用pivot进行行转列
使用该函数，也是要指定具体列名字段。
代码如下：
select *
from
(
select UserName , [Subject] , [Source] from TestTable
) test
pivot
(
sum(Source) for Subject in (语文 , 数学 , 英语)
) pvt
运行结果如下：
4、使用case when进行行转列
如果是动态列就无法支持。
代码如下：
select 姓名,
sum(isnull(语文分数 , 0)) as 语文分数,
sum(isnull(数学分数 , 0)) as 数学分数,
sum(isnull(英语分数 , 0)) as 英语分数
from
(
select
UserName as 姓名,
case when Subject='语文' then Source end as '语文分数',
case when Subject='数学' then Source end as '数学分数',
case when Subject='英语' then Source end as '英语分数'
from TestTable tt
) t1
group by 姓名
运行结果如下：
七.SQL查询语句的逻辑执行顺序
下面是一个查询语句的逻辑执行顺序（每段语句都标明了执行顺序号）：
执行1：FROM
执行2：ON
执行3：JOIN
执行4：WHERE
执行5：GROUP BY
执行6：HAVING
执行7：SELECT
执行8：DISTINCT
执行9：ORDER BY
执行10：TOP
查询语句的逻辑顺序
逻辑执行过程：
(1) FROM：对FROM子句中的前两个表执行笛卡尔积（Cartesian product)(交叉联接），生成虚拟表VT1
(2)执行ON：对VT1应用ON筛选器。只有那些使JOIN
(3) OUTER(JOIN)：如果指定了OUTERJOIN（相对于CROSSJOIN或(INNERJOIN),保留表（preservedtable：左外部联接把左表标记为保留表，右外部联接把右表标记为保留表，完全外部联接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。
(4) WHERE：对VT3应用WHERE筛选器。只有使
(5) GROUPBY：按GROUPBY子句中的列列表对VT4中的行分组，生成VT5.
(6) HAVING：对VT5应用HAVING筛选器。只有使
(7) SELECT：处理SELECT列表，产生VT7.
(8) DISTINCT：将重复的行从VT7中移除，产生VT8.
(9) ORDERBY：将VT8中的行按ORDERBY子句中的列列表排序，生成游标（VC9).
(10) TOP：从VC9的开始处选择指定数量或比例的行，生成表VT10,并返回调用者。
注意：
步骤10，按ORDER BY子句中的列列表排序上步返回的行，返回游标VC10.这一步是第一步也是唯一一步可以使用SELECT列表中的列别名的步骤。这一步不同于其它步骤的是，它不返回有效的表，而是返回一个游标。SQL是基于集合理论的。集合不会预先对它的行排序，它只是成员的逻辑集合，成员的顺序无关紧要。对表进行排序的查询可以返回一个对象，包含按特定物理顺序组织的行。ANSI把这种对象称为游标。理解这一步是正确理解SQL的基础。
因为这一步不返回表（而是返回游标），使用了ORDER BY子句的查询不能用作表表达式。表表达式包括：视图、内联表值函数、子查询、派生表和共用表达式。它的结果必须返回给期望得到物理记录的客户端应用程序。
八.IF条件与Case When的区别
语法SELECT查询语句多语句复杂逻辑IF条件不支持支持Case When支持支持
（1）IF条件：适用于存储过程、函数、触发器等较复杂逻辑处理场景。不适用于查询语句、视图语句。
数据准备：
create table 学生表2
(
fid int identity(1 , 1),
fname varchar(100),
fsex int,
fscore float
)
insert into 学生表2
(fname , fsex , fscore)
select '大明' , 1 , 90
union all select '小花' , 0 , 80
union all select '小龙' , 1 , 85
union all select '王燕' , 0 , 98
union all select '李学志' , 1 , 92
（2）使用IF条件：
declare @fsex int
set @fsex=1
if @fsex=1
begin
select count(1) as 男生数量 from 学生表2 where fsex=1
end
else if @fsex=0
begin
select count(1) as 女生数量 from 学生表2 where fsex=0
end
else
begin
select 1
end
运行结果如下：
（3）使用case when条件：
select
fname as 姓名,
case when fsex=1 then '男'
when fsex=0 then '女'
else '男/女'
end as 性别,
fscore as 分数
from 学生表2
运行结果如下：
好文链接
您阅读本篇文章共花了：
发表评论