SQL server 语句大全.doc

发布时间：2022-06-24 发布人：admin 分类：说明书资料大小：0.21M 资料格式：doc 举报版权申诉

c13418456924-4065696-4744302542902539685.doc.pdf-第1页.png

第1页 / 共26页

c13418456924-4065696-4744302542902539685.doc.pdf-第2页.png

第2页 / 共26页

c13418456924-4065696-4744302542902539685.doc.pdf-第3页.png

第3页 / 共26页

c13418456924-4065696-4744302542902539685.doc.pdf-第4页.png

第4页 / 共26页

c13418456924-4065696-4744302542902539685.doc.pdf-第5页.png

第5页 / 共26页

c13418456924-4065696-4744302542902539685.doc.pdf-第6页.png

第6页 / 共26页

c13418456924-4065696-4744302542902539685.doc.pdf-第7页.png

第7页 / 共26页

c13418456924-4065696-4744302542902539685.doc.pdf-第8页.png

第8页 / 共26页

SQL语法

数据库

一、创建数据文件

二、修改数据库

三、删除

数据表

一、判断数据库中的某表是否存在，存在删除该表

二、修改表语法

三、删除数据表

四、约束

高级查询

聚合函数（统计）

SQL变量

一、局部变量：

二、全局变量：

SQL中的代码块

一、数据库中的if和else语句

二、SQL中的while语句

三、Case－End多分支语句

子查询

视图（虚拟表）和索引

一、视图

二、索引

事务管理

数据库的安全性

建立权限

存储过程（procedure）

无参数存储过程

带参数

带参数模糊查找

带参数空值和模糊查找

多参数、输入与输出参数

触发器（trigger）

什么是触发器？

触发器的分类

创建触发器语法

事前触发器（Insert of）

事后触发器（for或after）

使用触发器方法其一 —— 判断

数据库备份和恢复

目录 SQL 语法········································································································· 2 数据库·············································································································5 创建数据文件············································································ 5 修改数据库··············································································· 6 删除························································································ 7 数据表·············································································································8 判断数据库中的某表是否存在，存在删除该表··································8 修改表语法··············································································· 8 删除数据表··············································································· 8 约束························································································ 8 高级查询··········································································································9 聚合函数（统计）···························································································· 10 SQL 变量········································································································ 11 局部变量················································································· 11 全局变量················································································· 11 SQL 中的代码块·······························································································14 数据库中的 if 和 else 语句··························································· 14 SQL 中的 while 语句·································································· 14 Case－End 多分支语句······························································· 14 子查询··········································································································· 15 视图（虚拟表）和索引······················································································ 16 视图·······················································································16 索引·······················································································16 事务管理········································································································ 17 数据库的安全性······························································································· 18 建立权限·················································································19 存储过程（procedure）······················································································ 20 无参数存储过程········································································20 带参数····················································································20 带参数模糊查找········································································20 带参数空值和模糊查找·······························································21 多参数、输入与输出参数····························································21 触发器（trigger）····························································································· 22 什么是触发器？········································································22 触发器的分类···········································································22 创建触发器语法········································································22 事前触发器（Insert of）····························································· 23 事后触发器（for 或 after）·························································· 23 使用触发器方法其一 —— 判断·················································· 24 数据库备份和恢复···························································································· 25 作者：IsonGT 第 1 页 2022-6-24

SQL 语法创建备注段执行自动偏号终止、删除创建语法 GO identity(起始号,加量) drop 删除表（库）名称 create 创建表（库）名称备注大于大于等于等于小于等于小于不等出什么以外备注或与非 is not null in (1,2,3,4) like(8%) like(8%) like(%8%) like(%8) like(?8) insert into 表名（表列 1, ···,） values（内容 1, ···）精确匹配空值精确匹配多个值模糊条件匹配以什么开头中间以什么结尾排位插入到表行值约束备注唯一主键标识列（自动增加）默认外键检查非空语句 GO Identity drop create 符号 > >= = <= < <>or!= [] ^ 特殊单词 Or And Not is in like 8% %8% %8 ?8 insert into values 语句 Unique Primary key Identity Default Forget key Check not null 作者：IsonGT 第 2 页 2022-6-24

类型备注语句 Int Nvarchar Varchar Bit Datetime Ntext text 使用单引号:字符常量，日期，DB_ID（）之类的。不使用单引号:数值,元 OR 对象(列名,表名,数据库名)[青],关键字 OR 蓝色保留字,Int 类型。数字整型类型双字符类型单字符类型位类型时间类型文本类型文本类型增 insert 备注插入值删 delete 语法 Insert 表名[(列名，···)] Values (值，···) 备注语法 Delet [from] Where 条件表名不能加条件，不能带条件，清空数据！删除删除改 update 备注修改修改修改查 check 查询备注运行查询语法 updata 表名 Set 列=新值[，···] where 条件查询 where 条件语法 select * from 表 540D [group by 分组列名] [having 过滤条件] [order by 排序列] 语句 Insert Values 语句 Delete Truncate table 语句 Updata Set Where Select Where 语句 select * from Select *列名[或] [From]表名 [where 筛选条件] 注：顺序不能改变目录作者：IsonGT 第 3 页 2022-6-24

常用语句 use master if DB_ID('studentMS') is not null drop database studentMS create database studentMS 使用 master 数据库判断 studentMS 库是否存在如果存在就删除 studentMS 创建 studentMS 库 if OBJECT_ID('studentInfo') is not null drop table studentInfo 判断 studentInfo 表是否存在如果存在就删除 studentInfo create table studentInfo select * from score 多联查：创建 studentInfo 表选 score 查询 Select 表 1 的某列，表 2 的某列，表 3 的某列 From 表 1，表 2，表 3 Where 表 1.列名=表 2.列名=表 3.列名目录作者：IsonGT 第 4 页 2022-6-24

一、创建数据文件判断数据库是否存在，存在删除数据库数据库 if exists (select * from master.dbo.sysdatabases where dbid=DB_ID('数据库名')) drop database 数据库名或 if DB_ID('数据库名') is not null drop database 数据库名 GO create database Workers On(--建立主数据文件-- Name=‘数据库逻辑主数据文件名’， Filename=‘数据库物理主数据文件名（需指明具体路径及文件名称）后缀名为“.mdf”’， Size=数据文件初始大小，单位默认为 M， MaxSize=数据文件可增长到最大值，单位默认为 M，不指定即无限大， FileGrowth=数据文件每次增长率，可以是百分比，默认单位为 M，0 则不增长 )，（建立次数据时记得在主数据文件最好加上逗号） (--建立次数据文件-- Name=‘数据库逻辑次数据文件名’， Filename=‘数据库物理次数据文件名（需指明具体路径及文件名称）后缀名为“.ndf”’， Size=数据文件初始大小，单位默认为 M， MaxSize=数据文件可增长到最大值，单位默认为 M，不指定即无限大， FileGrowth=数据文件每次增长率，可以是百分比，默认单位为 M，0 则不增长 ) Log on(--建立日志文件-- Name=‘数据库逻辑日志文件名’， Filename=‘数据库物理主日志文件名（需指明具体路径及文件名称）后缀名为“.ldf”’， Size=数据文件初始大小，单位默认为 M， MaxSize=数据文件可增长到最大值，单位默认为 M，不指定即无限大， FileGrowth=数据文件每次增长率，可以是百分比，默认单位为 M，0 则不增长 ) 作者：IsonGT 第 5 页 2022-6-24

修改数据库二、 Alter Database 数据库名 ( Add filr ( Name=‘数据库逻辑日志文件名’， NewName=‘数据库逻辑日志文件名’， Filename=‘数据库物理数据文件名(需指明具体路径及文件名称).后缀名’， Size=数据文件初始大小，单位默认为 M， MaxSize=数据文件可增长到最大值，单位默认为 M，不指定即无限大， FileGrowth=数据文件每次增长率，可以是百分比，默认单位为 M，0 则不增长 ) Add log filr ( Name=‘数据库逻辑日志文件名’， NewName=‘数据库逻辑日志文件名’， Filename=‘数据库物理数据文件名(需指明具体路径及文件名称).后缀名’， Size=数据文件初始大小，单位默认为 M， MaxSize=数据文件可增长到最大值，单位默认为 M，不指定即无限大， FileGrowth=数据文件每次增长率，可以是百分比，默认单位为 M，0 则不增长 ) Remove filr 逻辑文件名 Modify file ( Name=‘数据库逻辑日志文件名’， NewName=‘数据库逻辑日志文件名’， Filename=‘数据库物理数据文件名(需指明具体路径及文件名称).后缀名’， Size=数据文件初始大小，单位默认为 M， MaxSize=数据文件可增长到最大值，单位默认为 M，不指定即无限大， FileGrowth=数据文件每次增长率，可以是百分比，默认单位为 M，0 则不增长 ) Modify name=新数据库名 ) 添加次数据文件 Alter Database Student Add File ( Name=‘数据库逻辑次数据文件名’， FileName=‘数据库物理主日志文件名（需指明具体路径及文件名称）后缀名为“.ldf”’， Size=数据文件初始大小，单位默认为 M ) 添加日志文件 Alter Database Student Add Log File ( Name=‘数据库逻辑日志文件名’，作者：IsonGT 第 6 页 2022-6-24

FileName=‘数据库物理主日志文件名（需指明具体路径及文件名称）后缀名为“.ldf”’， Size=数据文件初始大小，单位默认为 M， MaxSize=数据文件可增长到最大值，单位默认为 M，不指定即无限大 ) 更改文件：（更改逻辑名、数据文件大小、最大增长值） Alter Database Student Modify File ( Name=‘逻辑名’， Size=数文件大小， MaxSize=最大增长值 ) 三、删除 a) 删除数据文件：Alter Database Student Remove File 数据库名 b) 删除日志文件：Alter Database Student Remove Log File 数据库名 c) 删除不需要的数据库：Drop Database 数据库名[，···] d) ( 注:可删除单个或多个数据库，除 model、master、tempdb 等无法删除) 目录作者：IsonGT 第 7 页 2022-6-24

数据表一、判断数据库中的某表是否存在，存在删除该表 if exists(select name from sysobjects where name='表名') drop table 表名或 if OBJECT_ID('表名') is not null drop table 表名二、修改表语法 1. 修改表结构 --修改列结构 Alter Table 表名 Add 列名数据类型列的特性| Drop Column 列名| 2. 在表中添加新列 Alter Table 表名 Add 列名数据类型列的特性 3. 修改列的数据类型（当前类型转为某类型） Alter Table 表名 Alter Column 表名数据类型列的特性 4. 删除列三、 Alter Table 表名 Drop Column 列名删除数据表 Drop Table 表名四、约束 --修改列约束 Drop Constraint 约束名(注：先删除现有的约束) Alter Column 列名 Add Constraint 约束名约束表达式(注：在创建新的约束) --删除约束 Alter Table 表名 Drop Constraint 约束名 --添加约束 Alter Table 表名 Add Constraint 约束名约束类型具体的约束说明（表名，···）如：主键约束（PK） Alter Table 表名 Add Constraint PK_约束名 primary key（列名，···）唯一约束（UQ） Alter Table 表名 Add Constraint UQ_约束名 unique（列名，···）默认约束（DF） Alter Table 表名 Add Constraint DF_约束名 default（‘默认值’）for 列名检查约束（CK） Alter Table 表名 Add Constraint CK_约束名 check（列名>=值 and 列名<=值）外键约束（FK） Alter Table 表名 Add Constraint FK_约束名 Foreign key （列名）References 表名（列名）目录作者：IsonGT 第 8 页 2022-6-24

分享到：

赞收藏

资料库

SQL server 语句大全.doc

相关推荐

数据库

热门标签

最新资料