SQL 相关语法
一、SQL约束
主键约束 Primary Key
外键约束 Foreign Key
检查约束 Check 约束
默认约束 Default 约束
唯一约束 Unique 约束
二、SQL通配符
通配符 解释 示例
‘_’ 一个字符 A Like ‘C_’
% 任意长度的字符串 B Like ‘CO_%’
[] 括号中所指定范围内的一个字符,包括括号中的值 C Like ‘9WO[1-2]’
[^] 不在括号中所指定范围内的任意一个字符,包括括号中的值 D Like ‘9WO[^1-2]’
三、SQL T-SQL语法
1.And Or Not
当一个语句中使用了多个逻辑运算符时,首先求 Not 的值,然后求And的值,最后求Or的值。
示例:(IN 关键字用来限定范围,也可以用 Not In)
Not (付款方式=’信用卡’) OR (信用卡 IN (‘牡丹卡’,’龙卡’,’阳光卡’))
2. SQL表达式中 不等于用’<>’表式
3.一次插入多行数据
A.Insert [into] <表名>[列名] Select [列名]/* From <表名>
此语句将现有表中数据添加到已存在表中,两表结构必须相同。
B.Select [列名] into <新表名> From <表>
此语句为将查询出来的相应列的值插入到新表中,新表为执行此语句时创建。如果查询表的列中包含标识列,可以使用以下语句:
Select Identity(int,1,1) As <列名>,<列名>...Into <新表名> From <表名>
4.删除表中所有数据
A.Truncate Table <表名> 删除表中所有数据,但是表的结构,列,约束,索引等不会被改动,不能用于有外键约束引用的表,这种情况下，
需使用Delete语句。此语句类似于Delete From <表名> 但是比Delete执行速度快,占用资源少
四、SQL 查询
1.SQL查询语句:
Select <列名>/* From <表名> [Where <查询条件表达式>] [Order by <排序列名>[ASC或DESC]] 默认不写为 ASC
ASC 升序 DESC 降序
2.在查询中改变列名
示例1:Select Scode As 学员编号,SName As 学员姓名,SAddress As 地址 From Students
Select 姓名=FirstName+’.’+LastName From Employees
示例2: 使用计算，合并得到新列的命名
Select FirstName+’.’+LastName As 姓名 From Employees
3.查询是否空行用 Is Null 或 Is Not Null
4.在查询中使用常量列:Select SName 姓名,’河北新龙’ As 学校名称 ..
5.查询指定行数(查询返回限制的行数) 使用 Top 关键字
示例1:查询前5条记录
Select Top 5 [列名]/* From <表名> [Where <条件表达式>]
示例2:按百分比(20%)查询记录
Select Top 20 Percent [列名]/* From <表名> [Where <条件表达式>]
五、SQL 相关函数
1.字符串函数
CharIndex() 用来找一个指定的字符串在另一个字符串中的起始位置。
示例:Select CharIndex(‘ACCP’,’My AccpCourse’) 返回:4
Len() 返回传递给它的字符串的长度。
示例:Select Len(‘SQL’) 返回:3
Replace() 替换一个字符串的字符。
示例:Select Replace(‘莫乐可切.杨可’,’可’,’兰’) 返回:莫乐兰切.杨兰
2.日期函数
GetDate() 取得当前的系统时间。
DateAdd() 将指定的数值添加到指定的日期部分后的日期。
示例:Select DateAdd(mm,4,’01/01/99’) 返回:05/01/99
DateName() 日期中指定日期部分的字符串形式。
示例:Select DateName(dw,’01/01/2000’) 返回:星期六
DatePart 日期中指定部分的整数形式。
示例:Select DatePart(day,’01/15/2000’) 返回:15
3.系统函数
Convert() 用来转换数据类型。
示例:Select Convert(varchar(5),12345) 返回:’12345’
六、高级查询(一)
1.Between 用来查找那些介于两个已知值之间的未知值。
示例:Select * From Score Where Score Between 60 and 80
在此请注意Between 后面最大值和最小值用 And 分开且最小值写在前面
2.聚合函数
Sum() 返回表达式中所有数值的总和，只能用于数字类型的列。
示例:Select Sum(ytd_sales) From titles Where type=’Business’
注意这种查询只返回一个数,不能够直接与可能返回多行数据的列一起使用来进行查询,但是在一个查询中可以使用多个聚合函数。
Avg() 返回表达式中所有数值的平均值，只能用于数字类型的列。
示例:Select Avg(Score) As 平均成绩 From Score
Max() 返回表达式中最大值, Min() 返回表达式中最小值,可以用于数字型、字符型以及日期/时间类型的列。
示例:Select Max(Score) 最高分,Min(Score) 最低分 From Score
Count() 返回表达式中非空值的计数，可用于数字和字符类型的列，可以使用星号(*)作为Count的表达式，而不必指定特定的列计算所有的行数。
示例:Select Count(*) As 及格人数 From Score where Score>=60
3.分组查询
分组查询采用 Group By 子句来实现
示例:Select CourseID,Avg(Score) As 课程平均成绩 Form Score Group By CourseID
注意在使用Group By关键字时，在Select 列表中可以指定的项目是有限制的:
(1)被分组的列。
(2)为每个分组返回一个值的表达式,例如用一个列名作为参数的聚合函数
分组查询筛选采用Having关键字来实现, Having和Where子句可以在同一个Select语句中一起使用,顺序为:
示例:要查询“有多个员工的工资不低于2000的部门编号”
Select 部门编号,Count(*) From 员工信息表 Where 工资>=2000 Group By 部门编号 Having Count(*)>1
4.多表联接查询
内联接 Inner Join
示例1:在Where子句中指定联接条件
Select Students.Sname,Score.CourseID,Score.Score From
Students,Score Where Students.Scode=Score.StudentID
示例2:在From子句中使用Join…On
Select S.SName,C.CourseID,C.Score From Students As S Inner Join Score As C On (S.Score=C.StudentID)
外联接
左外联接 Left Outer Join
示例:要统计所有学员的考试情况，要求显示所有参加考试学员的每次考试分数，
没有参加考试的学员也要显示出来，这时候以学员信息表为主表，学员成绩表为从表的左外联接查询:
Select S.SName,C.CourseID,C.Score From Students As S Left Outer Join Score As C On S.Scode=C.StudentID
查询的结果可能有部分学员没有出现在成绩上，对应的科目和成绩以Null填充
右外联接 Right Outer Join 与左外联接类似。
七、数据库设计
无论数据库的大小和复杂程度如何，在进行数据库的系统分析时，都可以参考下列基本步骤：
A、需求分析阶段
(1)收集信息
(2)标识对象(实体)
(3)标识每个对象需要存储的详细信息(属性)
(4)标识对象(实体)之间的关系
B、概要设计阶段
绘制E-R(实体-关系)图
C、详细设计阶段
将E-R图转换为多张表
八、SQL语句创建数据库的实现
数据库文件的组成:
主数据文件：*.mdf
次要数据文件：*.ndf
日志文件：*.ldf
use master
go
if exists(select * from sysdatabases where name='stuDB')
drop database stuDB
go
Create database stuDB
on primary --默认就属于 Primary 主文件组，可省略
(
/*---数据文件的具体描述---*/
name='stuDB_data', --主数据文件的逻辑名
filename='D:\project\stuDB_data.mdf', --主数据文件的物理名
size=5mb, --主数据文件的初始大小
maxsize=100mb, --主数据文件增长的最大值
filegrowth=15% --主数据文件的增长率
)
log on
(
/*---日志文件的具体描述，各参数含义同上---*/
name='stuDB_log',
filename='D:\project\stuDB_log.ldf',
size=2mb,
filegrowth=1mb
)
GO
SQL语句创建表的实现
if exists(select * from sysobjects where name='stuDB')
drop table stuDB
go
create table 表名
(
字段1 数据类型 列的特征， /*列的特征包括该列是否为空(NULL)、是否是标识列、是否有默认值、是否为主键*/
字段2 数据类型 列的特征，
....
)
示例1:
create table stuinfo
(
stuName varchar(20) not null, --学员姓名，非空
stuNo char(6) not null, --学号，非空
stuAge int not null, --年龄，Int类型不用指定大小
stuID numeric(18,0), --身份证号，Numeric(18,0)代表18位数字，小数位数为0
stuSeat smallint identity(1,1), --座位号，自动编号(标识列)，从1开始递增，标识种子为1
stuAddress text --住址，允许为空
)
SQL语句添加约束的实现
Alter table 表名
Add constraint 约束名 约束类型 具体的约束说明
示例2:
alter table stuInfo
add constraint PK_stuNo Primary Key(stuNo), --添加主键约束(stuNo作为主键)
add constraint UQ_stuID Unique(stuID), --添加唯一约束(身份证号全国唯一)
add constraint DF_stuAddress Default('地址不详') for stuAddress, --添加默认约束(如果地址不填，默认为"地址不详")
add constraint CK_stuAge Check(stuAge between 15 and 40) --添加检查约束，要求年龄只能在15-40岁之间
--添加外键约束(主表stuInfo和从表stuMarks建立关系，关联字段为stuNo)
alter table stuMarks
add constraint FK_stuNo Foreign Key(stuNo) references stuInfo(stuNo)
SQL语句删除约束的实现
Alter table 表名
Drop constraint 约束名
九、T-SQL编程
局部变量
声明局部变量的语法:
Declare @变量名 数据类型(Size)
局部变量的赋值: 使用SET语句或Select语句
(1) @变量名=value
(2)Select @变量名=value
示例:(查询李文才的左右同桌)
Declare @name varchar(8),@seat int
Set @name='李文才'
Select @seat=stuSeat from stuInfo where stuName=@name
Select * from stuInfo where stuSeat=@seat+1 or stuSeat=@seat-1
Go
声明全局变量使用@@标志
常用全局变量:
@@Error 最后一个T-SQL错误的错误号
@@Identity 最后一次插入的标识值
@@Rowcount 受上一个SQL语句影响的行数
@@ServerName 本地服务器的名称
@@ServiceName 该计算机上的SQL服务的名称
@@Version SQL Server的版本信息
输出语句有两种，语法分别是:
print 变量或字符串
Select 变量 As 自定义列名
示例: (用print方法输出的结果将在消息窗口以文本方式显示，用Select方法结果将在网格以表格方式显示)
print '服务器的名称: '+@@Servername
Select @@Servername As '服务器名称'
SQL逻辑控制语句语法:
if-else条件语句
if(条件)
Begin
语句1
语句2
...
End
else
Begin
...
End
示例:
统计并显示本班笔试平均分，如果在70以上，显示“成绩优秀”，并显示前3名学员的考试信息。
如果在70以下，显示“本班成绩较差”，并显示后3名学员的考试信息。
Declare @myavg float
Select @myavg=AVG(writtenExam) From stuMarks
print '本班平均分'+convert(varchar(5),@myavg)
if(@myavg>70)
begin
print '本班笔试成绩优秀，前三名的成绩为'
Select Top 3 * From stuMarks Order By writtenExam Desc
end
else
begin
print '本班笔试成绩较差，后三名的成绩为'
Select Top 3 * From stuMarks Order By writtenExam
end
注意:如果要把输出的表格数据和文本消息显示在同一个窗口，需要设置：在查询分析器中单击右键选择→文本显示结果
While循环语语法:
While(条件)
begin
语句或语句块
[break]
end
Case多分支语句语法:
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
十、高级查询(二)
简单子查询语法:
Select ... From 表1 where 字段1=(子查询)
注意:一般来说，表连接都可以用子查询替换，但反过来说却不一定
如果子查询返回多条记录必须用 In 或 Not In
Exists和Not Exists子查询
If [Not] Exists(子查询)
语句
十一、事务、索引、视图
事务的4个属性(ACID特性):
(1)原子性
(2)一致性
(3)隔离性
(4)持久性
如何创建事务:
开始事务: Begin Transaction
提交事务: Commit Transaction
回滚事务: Rollback Transaction
事务的分类:
显式事务: 用Begin Transaction明确指定事务的开始
隐性事务: 通过设置 Set Implicit_Transactions on语句，将隐性事务模式设置为打开。
当以隐性事务操作时，SQL Server将在提交或回滚事务后自动启动新事务。无法
描述事务的开始，只需提交或回滚每个事务。
自动提交事务: 这是SQL Server的默认模式，它将每条单独的T-SQL语句视为一个事务。如果
成功执行，则自动提交。如果错误，则自动回滚。
索引分类:
唯一索引: 唯一索引不允许两行具有相同的索引值。(Unique)
注意:创建了唯一约束，将自动创建唯一索引
主键索引: 创建一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型
聚集索引: 在聚集索引中，表中各行的物理顺序与键值的逻辑(索引)顺序相同。 (Clustered) (NoNClustered)
注意: 表只能包含一个聚集索引。但可以有多个非聚集索引(最多249个)，设置某列为主键，该列就默认为聚集索引。
创建索引语法:
Create [Unique] [Clustered] [NoNClustered] Index IX_索引名
ON table_name (column_name[,column_name]...)
[With Fillfactor=x]
创建视图语法:
Create View View_name
As <Select语句>
使用视图:
Select * from View_name
十二、存储过程
存储过程的定义: 存储过程可包含逻辑控制语句和数据操作语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值
常用的系统存储过程:
sp_databases 列出服务器上的所有数据库
sp_renamedb 更改数据库的名称
sp_tables 返回当前环境下可查询的对象的列表
sp_columns 返回某个表列的信息
sp_helpconstraint 查看某个表的约束
示例:
ExEC sp_databases
ExEC sp_renamedb '原数据库名','新数据库名'
ExEC sp_tables
ExEC sp_columns 表名
ExEC sp_helpconstraint 表名
扩展存储过程:
EXEC xp_cmdshell 'mkdir d:\bank'，[NO_OUTPUT] NO_OUTPUT为可选参数，执行Dos命令后是否输出返回信息
自定义存储过程语法:
Create Proc[edure] 存储过程名
[{@参数1 数据类型}[=默认值][output],
......,
{@参数n 数据类型}[=默认值][output]
]
As
SQL语句
注意:输入参数可以设置默认值
存储过程的调用方法:
EXEC 存储过程名 参数,...
处理错误信息:
RaisError('相关错误信息',16,1)
示例：创建一个用户注册的存储过程
CREATE procedure [InsertUserInfo]
@NickName varchar(20),
@UserName varchar(20),
@Password varchar(20),
@Birthday datetime,
@Gender int,
@LastLoginTime datetime,
@LastLoginIp varchar(20),
@status int,
@Type int,
@FaceUrl varchar(100),
@Remark varchar(256)
AS
declare @Count as int
select @count = count(*) from UserInfo where NickName = @NickName
if(@Count <> 0)
raiserror('昵称已经被注册，请选用其他昵称。', 16,1)
INSERT UserInfo(NickName,UserName,Password,Birthday,Gender,LastLoginTime,LastLoginIp,status,Type,FaceUrl,Remark)
VALUES(@NickName,@UserName,@Password,@Birthday,@Gender,@LastLoginTime,@LastLoginIp,@status,@Type,@FaceUrl,@Remark)
return @@identity
GO
十三、触发器
触发器的类型:
Insert触发器: 当向表中插入数据时触发，自动执行触发器所定义的SQL语句
Update触发器: 当更新表中某列、多列时触发，自动执行触发器所定义的SQL语句
Delete触发器: 当删除表中记录时触发，自动执行触发器所定义的SQL语句
触发器两个特殊表的相关信息:
修改操作 Inserted表 Deleted表
增加(Insert)记录时 存放新增的记录 ...............
删除(Delete)记录时 ............. 存放被删除的记录
修改(Update)记录时 存放用来更新的新记录 存放更新前的记录
触发器的功能:
(1)强化约束
(2)跟踪变化
(3)级联运行
SQL里面的触发器语法:
Create Trigger trig_Trigname
on table_name
[with encryption] ----加密 syscomments表中包含Create Trigger语句文本的条目
for {[Delete,Insert,Update]}
As
SQL语句
注意: Create Trigger必须是批处理中的第一条语句，并且只能应用到一个表中
示例:
CREATE TRIGGER my_trig ON CarsOrders
FOR INSERT
AS
DECLARE @Saleld int,@Carld int
select @Saleld=Saleld from inserted
select @Carld=Carld from inserted
update CarSales set SaleNum=SaleNum+1 where Saleld=@Saleld
update CarsType set SaleNum=SaleNum+1 where Carld=@Carld
使用 DISTINCT 消除重复项语法:
select distinct column_name from table_name
参考资料：自己学SQL总结的一点基础知识，希望对有用处