SQL数据库的基本操作

一.数据库的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<join_condition>为真的行才被插入VT2。

(3) OUTER(JOIN)：如果指定了OUTERJOIN（相对于CROSSJOIN或(INNERJOIN),保留表（preservedtable：左外部联接把左表标记为保留表，右外部联接把右表标记为保留表，完全外部联接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。

(4) WHERE：对VT3应用WHERE筛选器。只有使<where_condition>为true的行才被插入VT4.

(5) GROUPBY：按GROUPBY子句中的列列表对VT4中的行分组，生成VT5.

(6) HAVING：对VT5应用HAVING筛选器。只有使<having_condition>为true的组才会被插入VT6.

(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 

运行结果如下：