SQLServer基本语法总结

                           数据库语法总结

-------------------------------------------------创建的数据库---------------------------------------

-------设置当前数据库为master，以便访问sysdatabases表

USE master 

------批处理（SQLServer规定：如果是建库, 建表，存储过程和视图等必须在语句末尾添加批处理标志”GO”）

GO

----- 判断是否存在要创建的数据库

IF  EXISTS（SELECT * FROM  sysdatabases WHERE  name ='数据库名'）

------存在则删除（再创建）

drop database  数据库名

-------创建数据库

CREATE  DATABASE  数据库名

ON [PRIMARY]

 (

  数据文件参数[，…n]  [文件组参数]

 )

 [LOG ON]

 (

  日志文件参数[，…n]

 )

文件参数包括：NAME=' stuDB_data ',  ------数据文件的逻辑名

              FILENAME='D:/project/stuDB_data.mdf',  -----数据文件的物理名

SIZE=5mb,  ------数据文件初始大小

 MAXSIZE=100mb,  ------数据文件增长的最大值

FILEGROWTH=15%   ------数据文件的增长率

 

---------------------------------------------------创建的表-----------------------------------------------

-------设置当前数据库为创建的数据库，以便访问sysobjects 表

USE 创建的数据库名

------批处理

GO

----- 判断是否存在要创建的表

IF  EXISTS（SELECT * FROM  sysobjects WHERE  name ='表名'）

------存在则删除（再创建）

drop table 表名

----------创建一个数据表

CREATE TABLE 表名

(

       列名1   类型1identity(1,1) 约束,    ----设置为标识列

       列名2   类型2 约束,

       列名n   类型n 约束

)

 

-------创建表时直接创建各种约束

CREATE TABLE 表名

(

       列名1   类型1 primary key(列名1,列名2,……列名n),---主键约束

       列名2   类型2 unique,---唯一约束

       列名3   类型3 identity(初始值,增量),---标识列约束

       列名4   类型4 default 默认值[注意:字符和日期要用单引号]---默认值约束

       列名5   类型5 check(列名及常量及运算符组成的表达式)---检查约束

…………………

       列名n   类型n,foreign key(列名1,列名2,……列名n) references 主键所在表名(列名a1,列名a2,……列名n)

)

 

-----------------------------------------删除数据库-----------------------------------------

drop database 数据库名1，数据库名2，……数据库n

 

-----------------------------------------删除数据表-----------------------------------------

drop table 表名1,表名2,……表名n

 

----------------------------------维护数据表的结构---------------------------------------

-------修改列

alter table 表名 alter column 列名 新类型(长度)

 

-------添加列

alter table 表名 add 列名 类型(长度)

 

-------删除列

alter table 表名 drop column 列名

 

-------添加主键约束

alter table 表名 add constraint 主键约束名 primary key(列名1,列名2,……列名n)

 

-------删除主键约束

alter table 表名 drop constraint 主键约束名

 

-------添加唯一约束

alter table 表名 add constraint 唯一约束名 unique(列名)

 

-------删除唯一约束

alter table 表名 drop constraint 唯一约束名

 

-------添加默认约束

alter table 表名 add constraint 默认约束名 default 默认值 for 列名

 

-------删除默认约束

alter table 表名 drop constraint 默认约束名

 

-------添加检查约束

alter table 表名 add constraint 检查约束名 check(列名及常量及运算符组成的表达式)

 

-------删除检查约束

alter table 表名 drop constraint 检查约束名

 

-------添加外键约束

alter table 表名 add constraint 外键约束名 foreign key(列名1,列名2,……列名n) references

主键所在的表名(列名A1,列名A2,……列名An)

 

-------删除外键约束

alter table 表名 drop constraint 外键约束名

 

---------------------------------------维护表的数据-------------------------------------

------------------------------插入数据-----------------------------

-----------------插入单行数据----------------

--------整行插入（表名后未跟列名要将所有的列值全部列出）

insert [into] 表名 values(列值1,列值2,……列值n)

 

--------插入某几列（不能为空无默认值非表识列的列必须全部列出）

Insert  [into] 表名  (列名1,列名2,……列名n)  values（列值1,列值2,……列值n）

 

------------------插入多行数据-----------------+

----------直接插入多行

Insert  into 表名 (列名1,列名2,……列名n)

Select  列值1,列值2,……列值n   union

Select  列值1,列值2,……列值n   union

……

 

----------新表已存在

insert [into] 表名1(列名1,列名2,……列名n) select (列名a1,列名a2,……列名an) from 源表名

 

----------建表和插入数据一起完成

Select 列名1,列名2,……列名n   into 表名 from 源表名

 

----------SELECT INTO插入多行数据的同时插入新的标识

Select  列名1,列名2,……列名n, Identity(数据类型,标识种子,标识增长量) as 列名into 新表from 原始表

 

--------------------------------查询数据----------------------------

-------显示所有记录的所有列

select * from 表名

 

-------显示所有记录的某些列

select 列名1,列名2,……列名n from 表名

--------显示满足条件的所有记录的所有列

select * from 表名 where 条件表达式 

 

--------显示满足条件的所有记录的某些列

select 列名1,列名2,……列名n from 表名 where 条件表达式

 

----------------内联接查询---------------

--------内联接的等值联接

select 别名1.列名1,……别名1.列名n,别名2.列名1,……别名2.列名n from 表名1 as 别名1 inner join 表名2 as 别名2 on 别名1.列名=别名2.列名 where 条件表达式

 

--------内联接的不等值联接

select 别名1.列名1,……别名1.列名n,别名2.列名1,……别名2.列名n from 表名1 as 别名1 inner join 表名2 as 别名2 on 别名1.列名>别名2.列名 where 条件表达式

 

---------------外联接查询---------------

--------外联接的左外联接

select 别名1.列名1,……别名1.列名n,别名2.列名1,……别名2.列名n from 表名1 as 别名1 left outer join 表名2 as 别名2 on 别名1.列名=别名2.列名 where 条件表达式

 

--------外联接的右外联接

select 别名1.列名1,……别名1.列名n,别名2.列名1,……别名2.列名n from 表名1 as 别名1 right outer join 表名2 as 别名2 on 别名1.列名=别名2.列名 where 条件表达式

 

--------外联接的完全外联接

select 别名1.列名1,……别名1.列名n,别名2.列名1,……别名2.列名n from 表名1 as 别名1 full outer join 表名2 as 别名2 on 别名1.列名=别名2.列名 where 条件表达式

 

--------按某列排序[ASC为升序，DESC为降序。对于联接查询的排序只需将上述语法后加上order by子句即可]

select 列名1,列名2,……列名n from 表名 where 条件表达式 order by 列名

 

-------使用特殊符号显示满足条件的所有记录的某些列

select 列名1+'符号常量'+列名2+'符号常量'+……列名n from 表名 where 条件表达式

 

-------使用as子句显示满足条件的所有记录的某些列

select 列名1 as 新列名1,列名2 as 新列名2,……列名n as 新列名n from 表名 where 条件表达式 

 

-------使用Identity从一个旧表创建一个新表,且只有一个标识列

select identity(类型,初值,增量) as 新列名 into 新创建的表名 from 已有的表名

 

-------使用identity从一个旧表创建一个新表,且只有一个标识列[当新表中只有一个标识列时可不用as]

select identity(类型,初值,增量) as 新列名into 新创建的表名 from 已有的表名

 

-------使用identity从一个旧表创建一个新表,且只有一个标识列

select 新列名=identity(类型,初值,增量)  into 新创建的表名 from 已有的表名

 

-------使用identity从一个旧表创建一个新表,且有一个标识列和其它列[当新表中有很多列时必用as]

select identity(类型,初值,增量) as 新列名1,旧表的列名1 as 新表的列名2,…… 旧表的列名n as 新表的列名n into 新创建的表名 from 已有的表名

 

--------显示前n行记录

select top n * from 表名 where 条件表达式

 

------显示前n%行记录

select top n percent * from 表名 where 条件表达式

 

-------使用聚合函数分组查询[注意sum和avg只用于数字型的列，count用于数字型和字符型的列，max和min用于数字、字符和日期型的列，sum、avg和count支持distinct]

select 列名1,列名2,……列名n from 表名 group by 列名1,列名2,……列名n

select 列名,聚合函数[sum(列名)或avg(列名)或count(列名)或max(列名)或min(列名)] from 表名 group by 列名

 

-------使用having子句查询[注意where、group和having的顺序不能颠倒]

select 列名1,列名2,……列名n from 表名 group by 列名1,列名2,……列名n having 条件表达式

 

--------使用通配符进行模糊查询

select * from 表名 where 通配符条件表达式

                   --- 列名 like '_A%'

                 --- 列名 not like '_A%'

                   --- 列名 in(值1，值2，……值n)

                   --- 列名 not in(值1，值2，……值n)

                   --- 列名 between 值1 and 值2

                   --- 列名 not between 值1 and 值2

                   --- 列名 is null

                   --- 列名 is not null

 

-----------------------------更新数据----------------------------

--------更新满足条件的记录的某些列值

update 表名 set 列名1=值1,列名2=值2,……列名n=值n where 条件表达式

 

-------内联接的等值联接更新

update 表名 set 列名1=值1,列名2=值2,……列名n=值n from 表名1 as 别名1 inner join 表名2 as 别名2 on 别名1.列名=别名2.列名 where 条件表达式

 

--------内联接的不等值联接更新

update 表名 set 列名1=值1,列名2=值2,……列名n=值n from 表名1 as 别名1 inner join 表名2 as 别名2 on 别名1.列名>别名2.列名 where 条件表达式

 

--------外联接的左外联接更新

update 表名 set 列名1=值1,列名2=值2,……列名n=值n from 表名1 as 别名1 left outer join 表名2 as 别名2 on 别名1.列名=别名2.列名 where 条件表达式

 

-------外联接的右外联接更新

update 表名 set 列名1=值1,列名2=值2,……列名n=值n from 表名1 as 别名1 right outer join 表名2 as 别名2 on 别名1.列名=别名2.列名 where 条件表达式

 

--------外联接的完全外联接更新

update 表名 set 列名1=值1,列名2=值2,……列名n=值n from 表名1 as 别名1 full outer join 表名2 as 别名2 on 别名1.列名=别名2.列名 where 条件表达式

 

------------------------------删除数据-----------------------------

-------删除满足条件的记录

delete from 表名 where 条件表达式

 

-----删除所有的记录,只保留表的结构.

truncate table 表名

 

----------------------------------------------添加SQL登录帐户------------------------------------------

------------------添加登录帐户-----------------

--------添加 Windows登录帐户

EXEC sp_grantlogin   ‘jbtraining/S26301‘       

--------添加 SQL登录帐户[帐户：zhangsan 密码：1234]

EXEC  sp_addlogin  'zhangsan', '1234' 

-----------------添加数据库用户------------------     

--------在master数据库中添加两个用户

USE master

GO

EXEC sp_grantdbaccess  'jbtraining/S26301', 'S26301DBUser' 

EXEC sp_grantdbaccess  'zhangsan', 'zhangsanDBUser'

-----------------分配对表的权限----------------

--------为S26301DBUser分配建表的权限

GRANT  create  table  TO  S26301DBUser

--------为zhangsanDBUser分配对表stuInfo的select, insert, update权限

GRANT select, insert, update  ON  stuInfo  TO  zhangsanDBUser

 

--------------------------------------------数据库中的变量-----------------------------------------------

------------------------------------局部变量-------------------------------------

-----------局部变量的声明（局部变量必须以标记@作为前缀 ，如@age）

DECLARE   @变量名  数据类型

-----------局部变量的赋值（局部变量的使用必须先声明，再赋值 ）

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

 

----------------------------------全局变量-----------------------------------------

-----------全局变量的使用（全局变量不需定义，由系统维护；且不能赋值，只能读取它的值，以@@作前缀）

变量	含义
@@ERROR	最后一个T-SQL错误的错误号
@@ IDENTITY	最后一次插入的标识值
@@LANGUAGE	当前使用的语言的名称
@@MAX_CONNECTIONS	可以创建的同时连接的最大数目
@@ROWCOUNT	受上一个SQL语句影响的行数
@@SERVERNAME	本地服务器的名称
@@TRANSCOUNT	当前连接打开的事务数
@@VERSION	SQL Server的版本信息

 

----------------------------------------------------逻辑控制语句--------------------------------------------------

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

IF （条件）

BEGIN

语句1

语句2

……

END

ELSE

BEGIN

语句1；

语句2；

……

END

 

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

WHILE （条件）

BEGIN

语句1

语句2

……

BREAK

END

----------SQL中的CASE－END多分支语句

CASE

WHEN 条件1 THEN  结果1

WHEN 条件2 THEN  结果2

……

ELSE 其他结果

END

 

----------------------------------------------------创建事务--------------------------------------------------------

开始事务：BEGIN TRANSACTION

提交事务：COMMIT TRANSACTION

回滚（撤销）事务：ROLLBACK TRANSACTION

--------示例说明创建事务

BEGIN TRANSACTION

DECLARE @errorSum INT     ------定义变量，用于累计事务执行过程中的错误

SET @errorSum=0            -------初始化为0，即无错误

UPDATE bank SET currentMoney=currentMoney-1000   ------转账：张三的账户少1000元，李四的账户多1000元

   WHERE customerName='张三'

SET @errorSum=@errorSum+@@error

UPDATE bank SET currentMoney=currentMoney+1000

   WHERE customerName='李四'

SET @errorSum=@errorSum+@@error  ------累计是否有错误

IF @errorSum<>0  ------如果有错误

  BEGIN

    print '交易失败，回滚事务'

    ROLLBACK TRANSACTION

  END 

ELSE

  BEGIN

    print '交易成功，提交事务，写入硬盘，永久的保存'

    COMMIT TRANSACTION  

  END

GO

 

-----------------------------------------------------创建索引-------------------------------------------------------

--------判断要创建的索引是否存在

USE stuDB

GO

IF EXISTS (SELECT name FROM sysindexes WHERE name = '索引名（如：IX_writtenExam）')

DROP INDEX 表名.索引名 

--------创建索引

CREATE [UNIQUE（唯一索引）] [CLUSTERED||NONCLUSTERED（聚集索引或非聚集索引）]   

    INDEX   索引名

     ON 表名 (列名)

    WITH  FILLFACTOR=x（x填充因子（系数）:指定一个0～100之间的值，表示索引页填充的百分比）

-------指定按索引查询

SELECT * FROM  表名 with  (INDEX=索引名（IX_writtenExam）)

WHERE 列名（writtenExam） BETWEEN 60 AND 90

 

-----------------------------------------------------创建视图------------------------------------------------------

--------判断要创建的视图是否存在

IF EXISTS (SELECT * FROM sysobjects WHERE  name = '视图名（如：view_stuInfo_stuMarks）')

     DROP VIEW视图名

GO

--------创建视图

CREATE VIEW 视图名

  AS

   查询语句（如： SELECT 姓名=stuName,学号=stuInfo.stuNo,

                   笔试成绩 =writtenExam,  机试成绩=labExam,

                    平均分=(writtenExam+labExam)/2

                       FROM stuInfo LEFT JOIN stuMarks

                       ON stuInfo.stuNo=stuMarks.stuNo）

GO

---------查询试图

SELECT * FROM 视图名

 

---------------------------------------------------------系统存储过程----------------------------------------------------

---------常用的系统存储过程

系统存储过程	说明
sp_databases	列出服务器上的所有数据库。
sp_helpdb	报告有关指定数据库或所有数据库的信息
sp_renamedb	更改数据库的名称
sp_tables	返回当前环境下可查询的对象的列表
sp_columns	回某个表列的信息
sp_help	查看某个表的所有信息
sp_helpconstraint	查看某个表的约束
sp_helpindex	查看某个表的索引
sp_stored_procedures	列出当前环境中的所有存储过程。
sp_password	添加或修改登录帐户的密码。
sp_helptext	显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。

----------常用的扩展存储过程:  xp_cmdshell

1,可以执行DOS命令下的一些的操作

2,以文本行方式返回任何输出

xp_cmdshell调用语法：

EXEC xp_cmdshell DOS命令 [NO_OUTPUT]

例如:EXEC xp_cmdshell 'mkdir d:/bank', NO_OUTPUT (在d盘创建了一个名为bank的文件夹)

 

----------创建存储过程

CREATE  PROC[EDURE]  存储过程名

@参数1  数据类型 = 默认值 OUTPUT,

              …… ,

@参数n  数据类型 = 默认值 OUTPUT

             AS

            SQL语句

GO

 

-----------处理存储过程中的错误

RAISERROR (msg_id | msg_str , severity state WITH option[,...n]])

注意:  msg_id(在sysmessages系统表中指定用户定义错误信息)

msg_str(用户定义的特定信息，最长255个字符)

severity(定义严重性级别。用户可使用的级别为0–18级)

state(表示错误的状态，1至127之间的值)

option(指示是否将错误记录到服务器错误日志中

 

----------调用存储过程

EXEC[UTE]  过程名  [参数]

---------------------------------------------------SQL Server中的函数---------------------------------------------------

-----------字符串函数

函数名	描述	举例
CHARINDEX	用来寻找一个指定的字符串在另一个字符串中的起始位置	SELECT CHARINDEX('ACCP','My Accp Course',1 ) 返回：4
LEN	返回传递给它的字符串长度	SELECT LEN('SQL Server课程') 返回：12
LOWER	把传递给它的字符串转换为小写	SELECT LOWER('SQL Server课程') 返回：sql server课程
UPPER	把传递给它的字符串转换为大写	SELECT UPPER('sql server课程') 返回：SQL SERVER课程
LTRIM	清除字符左边的空格	SELECT LTRIM ('  周智宇  ') 返回：周智宇  （后面的空格保留）
RTRIM	清除字符右边的空格	SELECT RTRIM ('  周智宇  ') 返回：  周智宇（前面的空格保留）
RIGHT	从字符串右边返回指定数目的字符(最后几个字符)	SELECT RIGHT('买卖提.吐尔松',3) 返回：吐尔松
REPLACE	替换一个字符串中的字符	SELECT REPLACE('莫乐可切.杨可','可','兰') 返回：莫乐兰切.杨兰
STUFF	在一个字符串中，删除指定长度的字符，并在该位置插入一个新的字符串(填充)	SELECT STUFF('ABCDEFG', 2, 3, '我的音乐我的世界') 返回：A我的音乐我的世界EFG

 

----------日期函数

函数名	描述	举例
GETDATE	取得当前的系统日期	SELECT GETDATE() 返回：今天的日期
DATEADD	将指定的数值添加到指定的日期部分后的日期	SELECT DATEADD(mm,4,’01/01/99’) 返回：以当前的日期格式返回05/01/99
DATEDIFF	两个日期之间的指定日期部分的区别	SELECT DATEDIFF(mm,’01/01/99’,’05/01/99’) 返回：4
DATENAME	日期中指定日期部分的字符串形式	SELECT DATENAME(dw,’01/01/2000’) 返回：Saturday
DATEPART	日期中指定日期部分的整数形式	SELECT DATEPART(day, ’01/15/2000’) 返回：15

 

------------数学函数

函数名	描述	举例
ABS	取数值表达式的绝对值	SELECT ABS(-43) 返回：43
CEILING	返回大于或等于所给数字表达式的最小整数	SELECT CEILING(43.5) 返回：44
FLOOR	取小于或等于指定表达式的最大整数	SELECT FLOOR(43.5) 返回：43
POWER	取数值表达式的幂值	SELECT POWER(5,2) 返回：25
ROUND	将数值表达式四舍五入为指定精度	SELECT ROUND(43.543,1) 返回：43.5
Sign	对于正数返回+1，对于负数返回-1，对于0 则返回0	SELECT SIGN(-43) 返回：-1
Sqrt	取浮点表达式的平方根	SELECT SQRT(9) 返回：3

 

-----------系统函数

函数名	描述	举例
CONVERT	用来转变数据类型	SELECT CONVERT (VARCHAR (5)，12345) 返回：字符串12345
CURRENT_USER	返回当前用户的名字	SELECT CURRENT_USER 返回：你登录的用户名
DATALENGTH	返回用于指定表达式的字节数	SELECT DATALENGTH ('中国A盟') 返回：7
HOST_NAME	返回当前用户所登录的计算机名字	SELECT HOST_NAME() 返回：你所登录的计算机的名字
SYSTEM_USER	返回当前所登录的用户名称	SELECT SYSTEM_USER 返回：你当前所登录的用户名
USER_NAME	从给定的用户I D返回用户名	SELECT USER_NAME(1) 返回：从任意数据库中返回“dbo”