附录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(); -- 获取登录名