常用命令应用简例

附录B：常用命令应用简例
=====================================================================================================
1、数据库的创建与删除
CREATE DATABASE db1;  --创建数据库db1
DROP DATABASE db1;    --删除数据库db1

2、数据表的创建与删除
CREATE TABLE t1(      --创建数据表t1，t1包含两个字段
col1  varchar(100),
col2  int
);
DROP TABLE t1;  --删除数据表t1

创建两表之间的外键关联：
CREATE TABLE t2(      --创建主表
t2_c1  char(8)  PRIMARY KEY,  -- 定义主键
t2_c2 int
);
CREATE TABLE t3(      --创建从表
t3_c1 char(8),  
t3_c2 int,
FOREIGN KEY(t3_c1) REFERENCES t2(t2_c1)   -- 定义外键
);
DROP TABLE t3,t2;  -- t3应先于t2被删除

3、索引的创建与删除
CREATE INDEX index1 ON t1(col1);   -- 创建非聚集索引index1
DROP INDEX index1 ON t1;    -- 删除非聚集索引index1

4、视图的创建与删除
CREATE VIEW view1    -- 创建视图view1
AS SELECT * FROM t1;
DROP VIEW view1;    -- 删除视图view1

5、超级用户的创建与删除
CREATE LOGIN log1    -- 创建名为log1的登录
WITH PASSWORD = '123456',   -- 密码为123456
DEFAULT_DATABASE = db1;    -- 默认数据库为db1
GO
EXEC sp_addsrvrolemember ' log1', 'sysadmin';   -- 赋最高权限
GO
    --------------------------------
DROP LOGIN log1;    -- 删除登录log1

6、数据库用户的创建、授权与删除
CREATE LOGIN mylog WITH PASSWORD = '123456',   -- 先创建一个登录
DEFAULT_DATABASE = db1;
GO
USE db1;
GO
CREATE USER user1 FOR LOGIN mylog;   -- user1依赖于mylog
GO
GRANT CREATE TABLE,CREATE VIEW,CREATE PROCEDURE,ALTER ANY SCHEMA TO User1;  -- 授权
GO
    --------------------------------
DROP USER user1;    -- 删除用户user1
DROP LOGIN mylog;    -- 删除登录mylog

7、数据库的备份与还原
USE master;
GO
ALTER DATABASE db1 SET RECOVERY SIMPLE;   -- 切换到简单恢复模式下
GO
BACKUP DATABASE db1        -- 将数据库db1完整备份到文件db1_disk.bak
TO DISK = 'D:/Backup/db1_disk.bak'
WITH FORMAT;
-----------------------------------------
USE master;
GO
EXEC sp_addumpdevice 'disk', 'db1_simple', 'D:/Backup/db1_disk.bak'; 
GO
RESTORE DATABASE db1     -- 利用备份文件db1_disk.bak完整备份db1
FROM db1_simple;

8、数据库的分离与附加
USE master;
GO
EXEC sp_detach_db 'db1', NULL, 'true';   -- 分离数据库
-----------------------------------------
--利用分离得到的db1.mdf进行附加
DECLARE @data_path nvarchar(256);
SET @data_path
= 'C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/';
EXEC ('CREATE DATABASE db1
      ON (FILENAME = '''+ @data_path + 'db1.mdf'') 
      FOR ATTACH');
GO

9、存储过程的创建、调用和删除
CREATE PROCEDURE MyPro      -- 定义带两个参数的存储过程
    @min_avgrade numeric(3,1), 
    @max_avgrade numeric(3,1)  
AS
    SELECT s_no,s_name,s_sex,s_avgrade,s_dept
    FROM student
    WHERE s_avgrade>=@min_avgrade AND s_avgrade<=@max_avgrade ;
    -----------------------------------------
    EXEC MyPro 50,88.8;    -- 调用存储过程
    -----------------------------------------
    DROP PROCEDURE MyPro    -- 删除存储过程

10、触发器的创建、应用和删除
CREATE TRIGGER MyTrigger ON t1     -- 创建触发器
AFTER DELETE
AS
BEGIN
PRINT '有人删除表t1中的数据!'
END
------------------------------------------------------
-- 当执行DELETE语句时，该触发器被触发，如：
DELETE FROM t1;
------------------------------------------------------
DROP TRIGGER MyTrigger;    -- 删除触发器

11、游标的创建、应用和删除
DECLARE MyCursor CURSOR       --创建游标
STATIC
FOR SELECT col1 FROM t1;
OPEN MyCursor;       
DECLARE @RowCount Integer, @i Integer, @col varchar(100);
SET @i=0
SET @RowCount = @@CURSOR_ROWS  
WHILE @i<@RowCount
BEGIN
FETCH NEXT FROM MyCursor   -- 将游标中的数据逐一输出
INTO @col;
PRINT @col;
SET @i=@i+1
END
CLOSE MyCursor; 
DEALLOCATE MyCursor;    -- 删除游标

12、查看服务器上所有的数据库
SELECT *
FROM sys.databases
ORDER BY name

13、查看数据库中所有的数据表
USE db1;     -- 查询数据库db1中包含的所有数据表
SELECT * FROM sys.tables;

14、查看数据表的结构
SELECT a.name 字段名, b.name 字段类型, a.max_length 字段长度
FROM sys.all_columns as a
JOIN sys.types as b
ON a.system_type_id = b.system_type_id
WHERE object_id = object_id('t1');

15、向数据表插入数据
INSERT INTO t1 VALUES('庆祝建国六十周年！', 60);  -- 向表t1插入数据
INSERT INTO t1 VALUES('北京奥运！', 29);
INSERT INTO t1 VALUES('上海世博会！', 51);

16、更新表中的数据
UPDATE t1
SET col1 = '庆祝中华人民共和国建国六十周年！'
WHERE col2=60;

17、删除表中的数据
DELETE FROM t1     
WHERE col2=29;

18、降序、有条件查询
SELECT col1, col2
FROM t1
WHERE col2 > 50
ORDER BY col2 DESC

19、等值连接查询
SELECT t2.t2_c1, t2.t2_c2, t3.t3_c2
FROM t2
INNER JOIN t3
ON (t2.t2_c1 = t3.t3_c1);

20、左外连接查询
SELECT t2.t2_c1, t2.t2_c2, t3.t3_c2
FROM t2
LEFT JOIN t3
ON(t2.t2_c1 = t3.t3_c1);

21、右外连接查询
SELECT t2.t2_c1, t2.t2_c2, t3.t3_c2
FROM t2
RIGHT JOIN t3
ON(t2.t2_c1 = t3.t3_c1);

22、全外连接查询
SELECT t2.t2_c1, t2.t2_c2, t3.t3_c2
FROM t2
FULL JOIN t3
ON(t2.t2_c1 = t3.t3_c1);

23、IF语句
DECLARE @n1 int, @n2 int
SET @n1 = 2
SET @n2 = 20
IF @n1<@n2
BEGIN
  PRINT '@n1 小于@n2';
END
ELSE IF @n1=@n2
BEGIN
  PRINT '@n1 等于@n2';
END
ELSE
BEGIN
  PRINT '@n1 大于@n2';
END
GO

24、CASE语句
SELECT 学号 = s_no, 姓名及爱好 = 
  CASE s_no
WHEN '20060201' THEN '李好，游泳'
WHEN '20060202' THEN '王丫，登山'
ELSE '没有这个人'
  END
FROM student

25、WHILE语句
DECLARE @n int, @i int, @resulet int;  -- 计算n的阶乘n!
SET @n = 5;
SET @i = 1;
SET @resulet = 1;
WHILE @i <= @n
BEGIN
SET @resulet = @resulet * @i;
SET @i = @i + 1;
END
PRINT @resulet

26、字符串数据和数值型数据的转换 
DECLARE @f float, @s varchar(20);       -- 数值型数据 → 字符串数据
SET @f=3.14159;
SET @s=CONVERT(varchar(20),@f);
--------------------------------------------------
DECLARE @f float, @s varchar(20);   -- 字符串数据 → 数值型数据
SET @s='1.73205';
SET @f=CONVERT(float,@s);

27、时间类型数据和字符串类型数据的转换
DECLARE @dt datetime, @str varchar(30);   -- 时间数据 → 字符串数据
SET @dt=GETDATE();
--SET @str=CONVERT(varchar(30), @dt, 114);
SET @str=CONVERT(varchar(30), @dt, 111);
PRINT @str
--------------------------------------------------
DECLARE @dt datetime, @str varchar(30);   -- 字符串数据 → 时间数据
SET @str = '2009/09/12';
SET @dt=CONVERT(datetime, @str);
PRINT @dt;

28、获取服务器、数据库、应用程序、用户、登录等名称
PRINT HOST_NAME();  -- 获取服务器名
PRINT DB_NAME();  -- 获取数据库名
PRINT APP_NAME();  -- 获取应用程序名
PRINT USER_NAME();  -- 获取数据库用户名
PRINT SUSER_SNAME();  -- 获取登录名