sqlserver sql语法大全.pdf

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

iloveyoumw-3639530-4744300845406089262.pdf-第1页.png

第1页 / 共15页

iloveyoumw-3639530-4744300845406089262.pdf-第2页.png

第2页 / 共15页

iloveyoumw-3639530-4744300845406089262.pdf-第3页.png

第3页 / 共15页

iloveyoumw-3639530-4744300845406089262.pdf-第4页.png

第4页 / 共15页

iloveyoumw-3639530-4744300845406089262.pdf-第5页.png

第5页 / 共15页

iloveyoumw-3639530-4744300845406089262.pdf-第6页.png

第6页 / 共15页

iloveyoumw-3639530-4744300845406089262.pdf-第7页.png

第7页 / 共15页

iloveyoumw-3639530-4744300845406089262.pdf-第8页.png

第8页 / 共15页

文本预览

SQL 语句 SQL 语法大全自:合江荔枝网目录创建数据库 ··································································································· - 1 - 删除数据库 ··································································································· - 1 - 备份 sql server ····························································································· - 1 - 创建新表 ······································································································ - 1 - 创建序列 ······································································································ - 1 - 删除新表 ······································································································ - 2 - 增加一个列 ··································································································· - 2 - 添加主键 ······································································································ - 2 - 创建索引 ······································································································ - 2 - 创建视图 ······································································································ - 2 - 几个简单的基本的 sql 语句 ·············································································· - 2 - 几个高级查询运算词 ······················································································ - 3 - 使用外连接 ··································································································· - 4 - 判断数据库是否存在 ······················································································ - 4 - 判断表是否存在 ····························································································· - 4 - 判断存储过程是否存在 ··················································································· - 4 - 判断临时表是否存在 ······················································································ - 4 - 判断视图是否存在·························································································· - 4 - 判断函数是否存在·························································································· - 5 - 获取用户创建的对象信息 ················································································ - 5 - 判断列是否存在 ····························································································· - 5 - 判断列是否自增列·························································································· - 5 - 判断表中是否存在索引 ··················································································· - 5 - 查看数据库中对象·························································································· - 6 - 复制表 ········································································································· - 6 - 拷贝表 ········································································································· - 6 - 跨数据库之间表的拷贝 ··················································································· - 6 - 子查询 ········································································································· - 6 - 显示文章、提交人和最后回复时间 ···································································· - 6 - 外连接查询 ··································································································· - 6 - 在线视图查询 ································································································ - 6 - between 的用法 ···························································································· - 7 - in 的使用方法 ······························································································ - 7 - 删除主表中已经在副表中没有的信息 ································································· - 7 - 四表联查问题 ································································································ - 7 - 日程安排提前五分钟提醒 ················································································ - 7 -

自:合江荔枝网一条 sql 语句搞定数据库分页 ··········································································· - 7 - 前 10 条记录 ································································································· - 7 - 选择排名 ······································································································ - 7 - 派生结果表 ··································································································· - 8 - 随机取出 10 条数据 ························································································ - 8 - 随机选择记录 ································································································ - 8 - 删除重复记录 ································································································ - 8 - 列出数据库里所有的表名 ················································································ - 8 - 列出表里的所有的·························································································· - 8 - 列示排列 ······································································································ - 8 - 初始化表 table1 ····························································································· - 9 - 选择从 10 到 15 的记录 ··················································································· - 9 - 数据类型转换 ································································································ - 9 - 1=1，1=2 的使用 ·························································································· - 9 - 收缩数据库 ································································································· - 10 - 压缩数据库 ································································································· - 10 - 检查备份集 ································································································· - 10 - 修复数据库 ································································································· - 10 - 日志清除 ···································································································· - 10 - 更改某个表 ································································································· - 12 - 存储更改全部表 ··························································································· - 12 - SQL SERVER 中直接循环写入数据 ······························································· - 13 - 注: www.hjlizhi.com

自:合江荔枝网创建数据库创建之前判断该数据库是否存在 if exists (select * from sysdatabases where name='databaseName') drop database databaseName go Create DATABASE database-name 删除数据库 drop database dbname 备份 sql server --- 创建备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：go use 原数据库名 go select * into 目的数据库名.dbo.目的表名 from 原表名(使用旧表创建新表) B：create table tab_new as select col1,col2… from tab_old definition only 创建序列 create sequence SIMON_SEQUENCE minvalue 1 -- 最小值 maxvalue 999999999999999999999999999 最大值 start with 1 开始值 increment by 1 每次加几 cache 20; - 1 -

自:合江荔枝网删除新表 drop table tabname 增加一个列 Alter table tabname add column col type 注：列增加后将不能删除。DB2 中列加上后数据类型也不能改变，唯一能改变的是增加 varchar 类型的长度。添加主键 Alter table tabname add primary key(col) 说明：删除主键：Alter table tabname drop primary key(col) 创建索引 create [unique] index idxname on tabname(col…。) 删除索引：drop index idxname on tabname 注：索引是不可更改的，想更改必须删除重新建。创建视图 create view viewname as select statement 删除视图：drop view viewname 几个简单的基本的 sql 语句 (1) 数据记录筛选： sql="select * from 数据表 where 字段名=字段值 order by 字段名 [desc]" sql="select * from 数据表 where 字段名 like '%字段值%' order by 字段名 [desc]" sql="select top 10 * from 数据表 where 字段名 order by 字段名 [desc]" sql="select * from 数据表 where 字段名 in ('值 1','值 2','值 3')" sql="select * from 数据表 where 字段名 between 值 1 and 值 2" (2) 更新数据记录： sql="update 数据表 set 字段名=字段值 where 条件表达式" sql="update 数据表 set 字段 1=值 1,字段 2=值 2 …… 字段 n=值 n where 条件表达式" (3) 删除数据记录： - 2 -

自:合江荔枝网 sql="delete from 数据表 where 条件表达式" sql="delete from 数据表" (将数据表所有记录删除) (4) 添加数据记录： sql="insert into 数据表 (字段 1,字段 2,字段 3 …) values (值 1,值 2,值 3 …)" sql="insert into 目标数据表 select * from 源数据表" (把源数据表的记录添加到目标数据表) (5) 数据记录统计函数： AVG(字段名) 得出一个表格栏平均值 COUNT(*¦字段名) 对数据行数的统计或对某一栏有值的数据行数统计 MAX(字段名) 取得一个表格栏最大的值 MIN(字段名) 取得一个表格栏最小的值 SUM(字段名) 把数据栏的值相加引用以上函数的方法： sql="select sum(字段名) as 别名 from 数据表 where 条件表达式" set rs=conn.excute(sql) 用 rs("别名") 获取统计的值，其它函数运用同上。查询去除重复值：select distinct * from table1 (5) 数据表的建立和删除： CREATE TABLE 数据表名称(字段 1 类型 1(长度),字段 2 类型 2(长度) …… ) 几个高级查询运算词 A：UNION 运算符 UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 B： EXCEPT 运算符 EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时(EXCEPT ALL)，不消除重复行。 C：INTERSECT 运算符 INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时(INTERSECT ALL)，不消除重复行。注：使用运算词的几个查询结果行必须是一致的。 - 3 -

自:合江荔枝网使用外连接 A、left outer join：左外连接（左连接）：结果集既包括连接表的匹配行，也包括左连接表的所有行。 SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c B：right outer join: 右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。 C：full outer join：全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。判断数据库是否存在 if exists (select * from sys.databases where name = '数据库名') drop database [数据库名] 判断表是否存在 if not exists (select * from sysobjects where [name] = '表名' and xtype='U') begin --这里创建表 end 判断存储过程是否存在 if exists (select * from sysobjects where id = object_id(N'[存储过程名]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [存储过程名] 判断临时表是否存在 if object_id('tempdb..#临时表名') is not null drop table #临时表名判断视图是否存在 --SQL Server 2000 IF EXISTS (SELECT * FROM sysviews WHERE object_id = '[dbo].[视图名]' --SQL Server 2005 IF EXISTS (SELECT * FROM sys.views WHERE object_id = '[dbo].[视图名]' - 4 -

自:合江荔枝网判断函数是否存在 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[函数名]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[函数名] 获取用户创建的对象信息 SELECT [name],[id],crdate FROM sysobjects where xtype='U' /* xtype 的表示参数类型，通常包括如下这些 C = CHECK 约束 D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束 L = 日志 FN = 标量函数 IF = 内嵌表函数 P = 存储过程 PK = PRIMARY KEY 约束（类型是 K） RF = 复制筛选存储过程 S = 系统表 TF = 表函数 TR = 触发器 U = 用户表 UQ = UNIQUE 约束（类型是 K） V = 视图 X = 扩展存储过程 */ 判断列是否存在 if exists(select * from syscolumns where id=object_id('表名') and name='列名') alter table 表名 drop column 列名判断列是否自增列 if columnproperty(object_id('table'),'col','IsIdentity')=1 print '自增列' else print '不是自增列' SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('表名') AND is_identity=1 判断表中是否存在索引 if exists(select * from sysindexes where id=object_id('表名') and name='索引名') print '存在' else print '不存在 - 5 -

自:合江荔枝网查看数据库中对象 SELECT * FROM sys.sysobjects WHERE name='对象名' 复制表 (只复制结构，源表名：a 新表名：b) (Access 可用) 法一：select * into b from a where 1<>1 法二：select top 0 * into b from a 拷贝表 (拷贝数据,源表名：a 目标表名：b) (Access 可用) insert into b(a, b, c) select d,e,f from a; 跨数据库之间表的拷贝 (具体数据使用绝对路径) (Access 可用) insert into b(a, b, c) select d,e,f from a in ‘具体数据库’ where 条件例子：。.from b in '"&Server.MapPath("."&"\data.mdb" &"' where.. 子查询 (表名 1：a 表名 2：b) select a,b,c from a where a IN (select d from b 或者： select a,b,c from a where a IN (1,2,3) 显示文章、提交人和最后回复时间 select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 外连接查询 (表名 1：a 表名 2：b) select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 在线视图查询 (表名 1：a - 6 -

分享到：

赞收藏

资料库

sqlserver sql语法大全.pdf

相关推荐

数据库

热门标签

最新资料