SQLserver基本脚本语句 （整理）

SQLserver基本脚本语句  

数据库中表的操作：

检查表stuInfo是否存在：

USE stuDB--设置当前数据库为stuDB

GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'stuInfo')

删除表stuInfo：

DROP TABLE stuInfo

GO

创建表stuInfo：

CREATE TABLE stuInfo/*--创建学员表--*/

(

stuName VARCHAR(20) NOT NULL,--非空

stuNo CHAR(6) NOT NULL,--定长

stuAge INT NOT NULL,

stuID NUMERIC(18,0),--18位数字 小数位为0

stuSeat SMALLINT IDENTITY(1,1),--自增列

stuAddress TEXT

) 

GO

 

约束的类型：

      主键约束：Primary Key constraint

      唯一约束：Unique Constraint

      检查约束：Check Constraint      范围格式限制

      默认约束：Default Constraint

      外键约束：Foreign Key Constraint

添加约束格式：

ALTER TABLE 表名

ADD CONSTRAINT 约束名 约束类型 具体约束说明

--添加主键约束

ALTER TABLE stuInfo
     ADD CONSTRAINT PK_stuNo PRIMARY KEY (stuNo)

--添加唯一约束

ALTER TABLE stuInfo
    ADD CONSTRAINT UQ_stuID UNIQUE (stuID)

--添加默认约束

ALTER TABLE stuInfo
    ADD CONSTRAINT DF_stuAddress
          DEFAULT ('地址不详') FOR stuAddress

--添加检查约束

ALTER TABLE stuInfo
    ADD CONSTRAINT CK_stuAge
        CHECK(stuAge BETWEEN 15 AND 40)

--添加外键约束

ALTER TABLE stuMarks
   ADD CONSTRAINT FK_stuNo         
       FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo)
GO

删除约束：

ALTER TABLE 表名

   DROP CONSTRAINT 约束名

例如：

ALTER TABLE stu_DB

    DROP CONSTRAINT DF_stuAddress

表中数据的操作：

1.添加数据

                      INSERT INTO user (id,name,pwd,age) VALUES (22,'张三',‘123’,22)

2.修改数据

                     UPDATE user SET pwd = '456' WHERE name = '张三'

3.查询数据(排序ORDER BY "栏位一" [ASC, DESC], "栏位二" [ASC, DESC] )

                     SELECT name,pwd FROM user WHERE age<25 ORDER BY age

4.删除数据

                     DELETE FROM user WHERE age = 20

 

数据库的操作：

检查数据库是否存在：

USE master--设置当前数据库为master

GO

IF EXISTS (SELECT * FROM sysdatabases WHERE name="stuDB")

删除数据库：

DROP DATABASE stuDB

GO

创建数据库：

CREATE DATABASE stuDB

ON(

/*--数据文件的具体描述--*/

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

--系统存储过程?
--1.给表中字段添加描述信息
  Create table T2 (id int , name char (20))
  GO
  EXEC sp_addextendedproperty 'MS_Description', 'Employee ID', 'user', dbo, 'table', T2, 'column', id
  EXEC sp_updateextendedproperty 'MS_Description', 'this is a test', 'user', dbo, 'table', T2, 'column', id
  --2.修改数据库名称
  EXEC sp_renamedb 'old_db_name', 'new_db_name'
  --3.修改数据表名称和字段名称
  EXEC sp_rename 'old_table_name', 'new_table_name'--修改数据表名称
  EXEC sp_rename 'table_name.[old_column_name]', 'new_column_name', 'COLUMN'--修改字段名称
  --4.给定存储过程名，获取存储过程内容
  exec sp_helptext sp_name
 
  --创建新的 SQL Server 登录，使用户得以连接使用 SQL Server 身份验证的 SQL Server。
  EXEC sp_addlogin @loginame = '', @passwd = '', @defdb = '', @deflanguage = NULL, @sid = NULL, @encryptopt = NULL
  --使 Windows NT 用户或组帐户得以使用 Windows 身份验证连接到 SQL Server。
  EXEC sp_grantlogin @loginame = ''
  --删除 SQL Server 登录，以阻止使用该登录名访问 SQL Server。
  EXEC sp_droplogin @loginame = ''
  --阻止 Windows NT 用户或组连接到 SQL Server。
  EXEC sp_denylogin @loginame = ''
  --从 SQL Server 中删除用 sp_grantlogin 或 sp_denylogin 创建的 Windows NT 用户或组的登录项。
  EXEC sp_revokelogin @loginame = ''
  --更改登录的默认数据库。
  EXEC sp_defaultdb @loginame = '', @defdb = ''
  --更改登录的默认语言。
  EXEC sp_defaultlanguage @loginame = '', @language = ''
  --添加或更改 SQL Server 登录密码。
  EXEC sp_password @old = '', @new = '', @loginame = ''
  --添加服务器角色新成员。
  EXEC sp_addsrvrolemember @loginame = '', @rolename = ''
  --添加服务器角色某成员。
  EXEC sp_dropsrvrolemember @loginame = '' , @rolename = ''
  --为 SQL Server 登录或 Windows NT 用户或组在当前数据库中添加一个安全帐户，并使其能够被授予在数据库中执行活动的权限(授予默认的“public”数据库角色)。
  EXEC sp_grantdbaccess @loginame = '', @name_in_db = NULL
  --或
  EXEC sp_adduser @loginame = '', @name_in_db = NULL, @grpname = ''
  --从当前数据库中删除安全帐户。
  EXEC sp_revokedbaccess @name_in_db = ''
  --或
  EXEC sp_dropuser @name_in_db = ''
  --在当前数据库创建新数据库角色。
  EXEC sp_addrole @rolename = '', @ownername = ''
  --在当前数据库删除某数据库角色。
  EXEC sp_droprole @rolename = ''
  --在当前数据库中添加数据库角色新成员。
  EXEC sp_addrolemember @rolename = '', @membername = ''
  --在当前数据库中删除数据库角色某成员。
  EXEC sp_droprolemember @rolename = '', @membername = ''
  --权限分配给数据库角色、表、存储过程等对象
  --1、授权访问
  GRANT
  --2、拒绝访问
  DENY
  --3、取消授权或拒绝
  REVOKE
  --4、Sample(pubs):
  GRANT SELECT ON authors TO Limperator
  DENY SELECT ON authors TO Limperator
  REVOKE SELECT ON authors TO Limperator
 
--http://blog.csdn.net/fredrickhu/archive/2009/10/28/4740546.aspx

1、查询SQL中的所有表：

Select TABLE_NAME FROM 数据库名称.INFORMATION_SCHEMA.TABLES Where TABLE_TYPE='BASE TABLE' 执行之后，就可以看到数据库中所有属于自己建的表的名称

2、查询SQL中所有表及列：

Select dbo.sysobjects.name as Table_name, dbo.syscolumns.name AS Column_name FROM dbo.syscolumns INNER JOIN dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id Where (dbo.sysobjects.xtype = 'u') AND (NOT (dbo.sysobjects.name LIKE 'dtproperties'))

3、在Sql查询分析器，还有一个简单的查询方法：

EXEC sp_MSforeachtable @command1="sp_spaceused '?'" 执行完之后，就可以看到数据库中所有用户表的信息

4、查询总存储过程数:

select count(*) 总存储过程数 from sysobjects where xtype='p'

XTYPE 有以下几种类型。

D = 默认值或 DEFAULT 约束
F = FOREIGN KEY 约束
L = 日志
FN = 标量函数
IF = 内嵌表函数
P = 存储过程
PK = PRIMARY KEY 约束（类型是 K）
RF = 复制筛选存储过程
S = 系统表
TF = 表函数
TR = 触发器
U = 用户表
UQ = UNIQUE 约束（类型是 K）
V = 视图
X = 扩展存储过程

1.获取表的基本字段属性

--获取SqlServer中表结构
SELECT syscolumns.name,systypes.name,syscolumns.isnullable,
syscolumns.length
FROM syscolumns, systypes
WHERE syscolumns.xusertype = systypes.xusertype
AND syscolumns.id = object_id('你的表名')

2.如果还想要获取字段的描述信息则

--获取SqlServer中表结构 主键，及描述
declare @table_name as varchar(max)
set @table_name = '你的表名'
select sys.columns.name, sys.types.name, sys.columns.max_length, sys.columns.is_nullable,
(select count(*) from sys.identity_columns where sys.identity_columns.object_id = sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id) as is_identity ,
(select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id) as description
from sys.columns, sys.tables, sys.types where sys.columns.object_id = sys.tables.object_id and sys.columns.system_type_id=sys.types.system_type_id and sys.tables.name=@table_name order by sys.columns.column_id

3.单独查询表的递增字段

--单独查询表递增字段
select [name] from syscolumns where
id=object_id(N'你的表名') and COLUMNPROPERTY(id,name,'IsIdentity')=1

4.获取表的主外键

--获取表主外键约束
exec sp_helpconstraint '你的表名' ;