数据库DATABASE
数据库文件组有主文件组和次文件组，数据文件和日志文件
ON PRIMARY主文件组
FILENAME ‘文件名’
LOG ON
文件分逻辑名称（NAME）和物理名称(FILENAME即存储地址)。最大尺寸默认不需要定义
--表示我会出现问题的内容
CREATE DATABASE school
ON
PRIMARY --主文件
( NAME='schmgt1',
FILENAME='D:\SQLprogram',
SIZE=10MB,
MAXSIZE=40MB,
FILEGROWTH=2MB
), --忘记两个之间的符号是，
( NAME='schmgt2',
FILENAME='D:\SQLprogram',
SIZE=10MB,
MAXSIZE=40MB,
FILEGROWTH=2MB
),
FILEGROUP sch1 --不使用=
( NAME='schmgt3',
FILENAME='D:\SQLprogram',
SIZE=10MB,
--MAXSIZE=默认, --默认最大尺寸
FILEGROWTH=2MB
),
( NAME='schmgt4',
FILENAME='D:\SQLprogram',
SIZE=10MB,
FILEGROWTH=1MB
),
FILEGROUP sch2
( NAME='schmgt5',
FILENAME='D:\SQLprogram',
SIZE=10MB,
MAXSIZE=20MB,
FILEGROWTH=10%
)
LOG ON --LOG ON
( NAME='schmgtlog',
FILENAME='D:\SQLprogram',
SIZE=20MB,
FILEGROWTH=10%
)表TABLE
表的列需要数据类型，列的约束
列的约束有六种，下面都列了（可能不合理，主要看格式）
主键可以是多个候选码，要写的最后，括号写列名
CREATE TABLE student
( 学号 char(6) not null PRIMARY KEY, --主键；非空也是一个约束
姓名 char(8) UNIQUE, --唯一约束
性别 char(2),
出生时间 datetime,
专业 char(12) FOREIGN KEY REFERENCES course(专业), --外键，加参考列
总学分 int CHECK(0<=总学分 and 总学分<=100), --CHECK约束
备注 varchar(500) DEFAULT 0 --默认值 0
)视图VIEW
CREATE VIEW CE_SUM
AS （SELECT） --VIEW是AS
-------------------------------------------------------------
--不重要,记格式
SELECT score.学号 num,SUM(成绩) score_sum
FROM score,student
WHERE score.学号=student.学号
AND 专业='计算机'
GROUP BY score.学号游标CURSOR
游标分声明-打开-读取-关闭和删除四步
只有CREATE有CURSOR
DECLARE xs_cur2 CURSOR --声明
DYNAMIC
FOR
SELECT 学号,姓名,总学分
FROM xsb
WHERE 专业= '计算机'
FOR UPDATE OF 总学分
OPEN xs_cur2 --打开
FETCH NEXT FROM xs_cur2 --读取数据
FETCH NEXT FROM xs_cur2 --下一条数据
FETCH RELATIVE 2 FROM xs_cur2 --两条后
FETCH PRIOR FROM xs_cur2 --前一条
FETCH RELATIVE -3 FROM xs_cur2
SELECT '执行情况' = @@FETCH_STATUS
FETCH FIRST FROM xs_cur2 --数据的第一条
CLOSE xs_cur2 --关闭
DEALLOCATE xs_cur2 --删除索引INDEX
--创建唯一，聚集索引
CREATE UNIQUE CLUSTERED INDEX kc_id
ON kcb (课程号)/*在视图上创建索引*/
CREATE VIEW dbo.vxs1 WITH SCHEMABINDING
AS
SELECT 学号, 姓名
FROM dbo.xsb
GO
CREATE UNIQUE CLUSTERED INDEX inx1
ON dbo.vxs1(学号)
GOT-SQL语句 用户定义系列——自定义函数FUNCTION
标量函数
CREATE FUNCTION average(@num char(20)) RETURNS int
AS
BEGIN
DECLARE @aver int
SELECT @aver=
(
SELECT avg(成绩)
FROM cjb
WHERE 课程号=@num
GROUP BY 课程号
)
RETURN @aver
END
--使用在创建表的列中也可以直接写函数average('101')至少输入变量可以直接放在括号里面，不用声明变量
DECLARE @course1 char(20) /*定义局部变量*/
DECLARE @aver1 int
SELECT @course1 = '101' /*给局部变量赋值*/
SELECT @aver1=dbo.average(@course1) /*调用用户函数，并将返回值赋
给局部变量*/
SELECT @aver1 AS '101课程的平均成绩' /*显示局部变量的值*/嵌入表值函数
--（1）创建视图。
USE pxscj
GO
CREATE VIEW xsv
AS
SELECT dbo.xsb.学号, dbo.xsb.姓名, dbo.kcb.课程名, dbo.cjb.成绩
FROM dbo.kcb
INNER JOIN dbo.cjb ON dbo.kcb.课程号 = dbo.cjb.课程号
INNER JOIN dbo.xsb ON dbo.cjb.学号 = dbo.xsb.学号
--（2）定义内嵌函数。
CREATE FUNCTION student_score(@id char(6)) RETURNS table
AS RETURN
(
SELECT *
FROM pxscj.dbo.xsv
WHERE dbo. xsv.学号= @id
)
--（3）调用内嵌函数，查询学号为191301的学生的各科成绩及学分。
SELECT *
FROM pxscj.[dbo].student_score('191301')多语句表值函数
CREATE FUNCTION MAM_FUN (@课程号 char(3))
RETURNS @score TABLE(最高分 int,平均分 int,最低分 int)
AS --多语句表值函数RETURNS部分
BEGIN
INSERT @score --INSERT的设定
SELECT MAX(成绩),AVG(成绩),MIN(成绩)
FROM score
WHERE 课程号=@课程号
RETURN
END
SELECT * FROM MAM_FUN(101) --调用存储过程PROCEDURE
简单存储过程
USE pxscj
GO
CREATE PROCEDURE cjb_info
AS
SELECT *
FROM cjb
WHERE 学号= '191301'
EXECUTE cjb_info
使用带参数的存储结构
USE pxscj
GO
CREATE PROCEDURE xkc_info1 @name char (8), @cname char(16)
AS
SELECT a.学号, 姓名, 课程名, 成绩, t.学分
FROM xsb a INNER JOIN cjb b
ON a.学号 = b.学号 INNER JOIN kcb t
ON b.课程号= t.课程号
WHERE a.姓名=@name and t.课程名=@cname
GO
EXECUTE xkc_info1 '王林', '计算机基础'
使用带OUTPUT参数的存储过程
CREATE PROCEDURE dbo.xsb_insert
AS
INSERT INTO xsb
VALUES('091201', '陶伟', 1, '1990-03-05', '软件工程',50, NULL);
--存在存储过程的嵌套
CREATE PROCEDURE xsb_iud @X bit, @STR CHAR(8) OUTPUT
AS
BEGIN
EXEC xsb_insert
IF @X=0
BEGIN
UPDATE xsb SET 姓名='刘英', 性别=0 WHERE 学号='091201'
SET @STR='修改成功'
END
ELSE
IF @X=1
BEGIN
DELETE FROM xsb WHERE 学号='091201'
SET @STR='删除成功'
END
END
接下来执行存储过程xsb_iud来查看结果：
DECLARE @str char(8)
EXEC dbo. xsb_iud 0, @str OUTPUT
SELECT @str;
使用带有通配符的存储过程
CREATE PROCEDURE xkc_info2 @name varchar(30) = '李%'
AS
SELECT a.学号,a.姓名,c.课程名,b.成绩
FROM xsb a INNER JOIN cjb b
ON a.学号 =b.学号 INNER JOIN kcb c
ON c.课程号= b.课程号
WHERE 姓名 LIKE @name
GO
--执行存储过程：
EXECUTE xkc_info2 /*参数使用默认值，显示所有李姓学生信息*/
--或者：
EXECUTE xkc_info2 '王%' /*传递给@name 的实参为'王%'，显示所有王姓学生信息*/
使用OUTPUT游标参数的存储过程
CREATE PROCEDURE xsb_cursor @xsb_cursor cursor VARYING OUTPUT
AS
SET @xsb_cursor = CURSOR FORWARD_ONLY STATIC FOR
SELECT *
FROM xsb
OPEN @xsb_cursor
DECLARE @MyCursor cursor
EXEC xsb_cursor @xsb_cursor = @MyCursor OUTPUT /*执行存储过程*/
FETCH NEXT FROM @MyCursor
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
练习B的例题
使用了输出，变量
CREATE PROCEDURE CPA @sn1 char(6),@sn2 char(6),@x bit OUTPUT --存储过程的格式完全不记得
AS
BEGIN--DECLARE @x bit --begin-end是要出现的
SELECT @x=
CASE --case格式，变量不在case后，最后要end
WHEN (SELECT AVG(成绩)FROM score WHERE 学号=@sn1)
>(SELECT AVG(成绩)FROM score WHERE 学号=@sn2) THEN 1
ELSE 0
END
FROM score
END
DECLARE @x bit
EXEC CPA 081109,081110,@x OUTPUT
SELECT @x触发器TRIGGER
分DML和DDL
分FOR/AFTER和INSTEAD OF
CREATE TRIGGER score_insert
ON score FOR INSERT
AS
DECLARE @学号 char(6)
SELECT @学号=student.学号 FROM inserted ,student WHERE inserted.学号=student.学号
IF @学号 IS NOT NULL
print('插入数据成功')
ELSE begin
print('该学号不存在于student表中，不能插入记录，插入将终止！')
ROLLBACK TRANSACTION
end创建登录名
create login 登录名
from windows|with password='123' --登录名分Windows和SQL-Server
with default_database=pxscj创建用户名
create user tao
for 登录名
with default_schema=dbo固定用户名角色，删除用户名角色
EXEC sp_addsrvrolemember ' SQL_tao', 'sysadmin'
EXEC sp_dropsrvrolemember 'SQL_tao', 'sysadmin'添加固定用户名角色成员
EXEC sp_addrolemember 'db_owner', 'User_SQL_tao'
EXEC sp_droprolemember 'db_owner', 'User_SQL_tao'创建角色
create role role1
authorization dbo
drop role role1授予用户或角色权限
grant create table --权限
to tao --用户或角色
grant select
on pxscj
to tao
deny 权限
to 用户、角色
revoke 权限
from 用户、角色
456
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
