数据库基础--SQL 语言(DQL、DDL、DML、DCL)

SQL 语言是一种高度非过程化语言。

/****************数据库的创建与删除*****************/
--创建一个名为test_STInfo的数据库--
CREATE DATABASE test_STInfo
DROP DATABASE test_STInfo
USE test_STInfo

/****************数据表的创建与更新*****************/
--创建表名为T_student的学生信息表--
CREATE TABLE T_student (
stuID
VARCHAR (15) PRIMARY KEY,
stuName VARCHAR (10)
NOT NULL,
age INT
sex VARCHAR (2) NOT NULL,
birth
NOT NULL,
DATETIME
NOT NULL
)

--创建院系信息表,并为院校编号所在列定义唯一约束。--
CREATE TABLE T_dept (
deptID
VARCHAR (15) NOT NULL,
deptName VARCHAR (10)
)
--创建成绩信息表--
CREATE TABLE T_result(
stuID VARCHAR (15) ,
curID VARCHAR (15) ,
result
FLOAT ,
PRIMARY KEY (stuID,curID)
)
DROP TABLE T_result
--为成绩信息表定义外键--
CREATE TABLE T_result(
stuID VARCHAR (15) NOT NULL,
curID VARCHAR (15) NOT NULL,
result
FLOAT ,
FOREIGN KEY(stuID) REFERENCES T_student(stuID) ON DELETE CASCADE,
PRIMARY KEY (stuID,curID)
)
--定义课程信息表--
CREATE TABLE T_curriculum(
curID
VARCHAR (15) PRIMARY KEY,
curName VARCHAR (10) ,
credit
INT,
CHECK(credit BETWEEN 3 AND 8)
)
--创建教师信息表--
CREATE TABLE T_teacher (
teaID
VARCHAR (15) PRIMARY KEY,
teaName VARCHAR (10)
NOT NULL,
age INT
NOT NULL,
sex VARCHAR (2) NOT NULL,
deptID VARCHAR (15),
dept VARCHAR (20)
NOT NULL,
profession VARCHAR (10)
)
--直接从结果集中创建表
SELECT stuID,result,SUM(result) AS sum_re
INTO copy_result FROM
T_result
GROUP BY stuID,result
--为教师信息表中表示教师职称单列创建索引--
CREATE INDEX i_profession
ON T_teacher(profession)
--为教师信息表中表示教师所在院系的列和教师职称的列创建复合索引--
CREATE INDEX i_dept_profession
ON T_teacher(dept,profession)
--删除教师信息表中为列教师职称创建的索引--
DROP INDEX T_teacher.i_profession
--向教师信息表中增加一个表示教师工资的列--
ALTER TABLE T_teacher ADD salary INT NOT NULL
ALTER TABLE T_teacher ADD pension INT NOT NULL
--为院系信息表增加主键约束--
ALTER TABLE T_dept ADD PRIMARY KEY(deptID)
ALTER TABLE T_result ADD CONSTRAINT PK_result PRIMARY KEY(stuID,curID)
--为成绩信息表增加外键约束。--
ALTER TABLE T_result ADD FOREIGN KEY(curID)
REFERENCES T_curriculum(curID)
--修改学生信息表中表示学生年龄列的数据类型--
ALTER TABLE T_student ALTER COLUMN sex CHAR(2)
--删除教师信息表中表示院系编号的列--
ALTER TABLE T_teacher DROP COLUMN deptID
--删除院系信息表中的主键--
ALTER TABLE T_result DROP CONSTRAINT PK_result
--删除教师信息表--
DROP TABLE T_teacher

/**************** 基本查询操作*****************/
--查询T_student中所有学生的全部信息
SELECT * FROM T_student
--ALL关键字、默认关键字
SELECT ALL teaName FROM T_teacher
--DISTINCT关键字/重复元组去除
SELECT DISTINCT stuName,stuID FROM T_student
--查询T_student中学生编号以及学生编号对应的学生姓名信息
SELECT stuID,stuName FROM T_student
--日期的输入格式
SELECT * FROM T_student WHERE birth>='1988-10-14'
----------------------------
SELECT stuID ,stuName,age,sex,birth
FROM T_student
WHERE birth >'19860101'
--查询教师信息表中年龄在到岁之间的教师信息
SELECT teaID,teaName,age,sex,dept,profession
FROM T_teacher
WHERE age BETWEEN 30 AND 50
----------------------------
SELECT teaID,teaName,age,sex,dept,profession
-8-
FROM T_teacher
WHERE age>=30 AND age<=50
----------------------------
SELECT teaID,teaName,age,sex,dept,profession
FROM T_teacher
WHERE NOT age BETWEEN 30 AND 50
--查询学号首字母在a到z的所有学生
SELECT * FROM T_teacher
WHERE teaID BETWEEN 'a' AND 'z'
--查询教师信息表中教师所在部门为计算机系和数学系的所有教师信息
SELECT teaID,teaName,age,sex,dept,profession
FROM T_teacher
WHERE dept IN('计算机系','数学系')
----------------------------
SELECT teaID,teaName,age,sex,dept,profession
FROM T_teacher
WHERE dept='计算机系' OR dept='数学系'
--查询教师信息表中不在计算机系或者数学系的所有教师信息
SELECT teaID,teaName,age,sex,dept,profession
FROM T_teacher
WHERE dept NOT IN('计算机系','数学系')
--查询学生信息表中位学生编号中以s开头并以结尾的学生信息
SELECT stuID,stuName,age,sex,birth
FROM T_student
WHERE stuID LIKE's_____3'
----------------------------
SELECT stuID,stuName,age,sex,birth
FROM T_student
WHERE stuID NOT LIKE 's_____3'
--例.15 查询学生信息表中姓王的所有学生的信息
SELECT stuID,stuName,age,sex,birth
FROM T_student
WHERE stuName LIKE'王%'
----------------------------
SELECT deptID,deptName
FROM T_dept
WHERE
deptID like '%$_%' ESCAPE '$'
--查询以讲或教开头的职称的所有教师信息
SELECT teaID,teaName,age,sex,dept,profession
FROM T_teacher
WHERE profession LIKE '[讲教]%'
----------------------------
SELECT teaID,teaName,age,sex,dept,profession
FROM T_teacher
WHERE profession LIKE '[^讲教]%'
----查询课程号不为空的教师信息
SELECT * FROM T_teacher
WHERE deptID IS
NOT NULL
--查询教师信息表中教师津贴为空的教师信息
SELECT teaID ,teaName , profession,salary,pension
FROM T_teacher
WHERE pension IS NULL

--***空值检查注意:NULL与、空字符串是完全不同的***
--查询课程表中课程学分>4的课程信息
SELECT curName,credit
FROM T_curriculum
WHERE credit>=4
--查询教师信息表中教师职称不是教授的教师信息
SELECT teaID,teaName,dept,profession
FROM T_teacher
WHERE profession!='教授'
--查询教师信息表中年龄在岁以上,职称为教授的教师信息
SELECT teaID,teaName,age,sex,dept,profession
FROM T_teacher
WHERE age>'45' AND profession='教授'
--查询教师信息表中年龄在岁以上或者职称为教授的教师信息
SELECT teaID,teaName,age,sex,dept,profession
FROM T_teacher
WHERE age>'45' OR profession='教授'
--查询计算机系或者是数学系中教师的工资大于的教师信息
SELECT teaID,teaName,age,sex,dept,profession ,salary
FROM T_teacher
WHERE (dept ='计算机系'OR dept ='数学系') AND salary >3000
--查询T_student中所有学生的全部信息(使用列别名)
SELECT stuID AS 学生编号, stuName AS 学生姓名, age AS 年龄, sex AS 性别, birth
AS 出生日期
FROM T_student
--查询教师的年收入
SELECT teaID AS 教师编号,teaName AS 教师姓名, salary*12 AS 年收入
FROM T_teacher
--将T_student中学生姓名和性别连接起来查询学生信息
SELECT stuID ,stuName + sex AS '姓名+性别' FROM T_student

/****************表中数据的排序与分组*****************/
--查询教师信息表中教师的工资,并按照教师工资从低到高排序
SELECT teaID,teaName,dept,profession,salary
FROM T_teacher
ORDER BY salary ASC
--查询教师信息表中教师的工资,并按照教师工资从低到高排序
SELECT teaID,teaName,dept,profession,salary
FROM T_teacher
ORDER BY salary DESC
--查询工资,并按照工资从低到高排序(使用列序号方式)
SELECT teaID,teaName,dept,profession,salary
FROM T_teacher
ORDER BY 5 ASC
--查询所有教师的工资,并按照从低到高排序(表示工资的列不出现)
SELECT teaID,teaName,dept,profession
FROM T_teacher
WHERE dept ='计算机系'
ORDER BY salary
---------------------------------
SELECT teaID,teaName,dept,profession,salary
FROM T_teacher
ORDER BY salary DESC,dept ASC
--查询教师信息表中教师的数量
SELECT COUNT(*) FROM T_teacher
--查询教师信息表中计算机系教师的数量
SELECT COUNT(dept) FROM T_teacher
--查询教师信息表中不重复的院校的数量
SELECT COUNT(DISTINCT dept)
FROM T_teacher
--查询教师信息表中的最高工资和最低工资
SELECT MAX(salary),MIN(salary)
FROM T_teacher
--查询教师信息表中的教师的工资总和以及教师的平均工资
SELECT SUM(salary),COUNT(salary),AVG(salary)
FROM T_teacher
--对教师信息表中的院校进行分组,并统计出每个院校中拥有教师职称的数量
SELECT dept,COUNT(profession)
FROM T_teacher
GROUP BY dept
--对教师信息表中的院校和教师职称进行分组,并统计出该院校教师的最高工资
SELECT dept,profession,MAX(salary)
FROM T_teacher
GROUP BY dept,profession
--对院校和教师职称进行分组,并统计出该院校教师最高工资>3000的记录
SELECT dept,profession,MAX(salary)
FROM T_teacher
GROUP BY dept,profession
HAVING MAX(salary)>3000
--对教师信息表中的职称进行分组,并统计出年龄>30教师最高工资>3000的记录
SELECT profession,MAX(salary)
FROM T_teacher
WHERE age>30
GROUP BY profession
HAVING MAX(salary)>3000
--对教师信息表中的院校和教师职称进行分组,并查询出教师最高工资>3000的教师姓名
SELECT dept,profession
FROM T_teacher
GROUP BY dept,profession
HAVING MAX(salary)>3000
--对教师信息表中的院校进行分组,并根据每个院校中教师的最高工资降序排序
SELECT dept,profession,MAX(salary)
FROM T_teacher
GROUP BY dept,profession
ORDER BY MAX(salary) DESC
--对教师信息表中的教师津贴字段进行分组
SELECT COUNT(*),pension
FROM T_teacher
GROUP BY pension
ORDER BY pension ASC
--对教师信息表中的院校和教师职称进行分组,并对分组后的教师最高工资进行统计
SELECT dept,profession,SUM(salary)
FROM T_teacher
GROUP BY dept,profession WITH ROLLUP
--查询教师信息表中的教师信息,只显示按照教师编号升序排序后的前条记录
SELECT TOP 3 teaID,teaName,dept,profession
FROM T_teacher
ORDER BY teaID

/****************连接查询与集合查询*****************/
--查询学生所选课程的成绩信息
SELECT T_result.stuID,T_curriculum.curID,
T_curriculum.curName,T_result.result
FROM T_result,T_curriculum
WHERE T_result.curID=T_curriculum.curID
ORDER BY T_result.stuID
-- 查询学生所选课程的成绩信息(使用别名)
SELECT R.stuID,C.curID, C.curName,R.result
FROM T_result R,T_curriculum C
WHERE R.curID=C.curID
ORDER BY R.stuID
--查询学生编号为s102203学生的课程成绩信息
SELECT R.stuID,C.curID, C.curName,R.result
FROM
T_result R,T_curriculum C
WHERE R.curID=C.curID
AND R.stuID = 's102203'
--查询学生姓名为郑茹的学生选课成绩信息
SELECT R.stuID,S.stuName,C.curID, C.curName,R.result
FROM T_result R,T_curriculum C,T_student S
WHERE R.curID=C.curID
AND R.stuID=S.stuID
AND S.stuName = '赵亮'
--查询课程成绩在分以上的学生选课信息
SELECT R.stuID,C.curID, C.curName,R.result
FROM T_result R,T_curriculum C
WHERE R. result>80
--查询学生编号为s102203学生的选课成绩信息(使用ON子句)
SELECT R.stuID,C.curID, C.curName,R.result
FROM T_result R JOIN T_curriculum C
ON
R.curID=C.curID
AND
R.stuID = 's102203'
--查询学生所选课程的成绩信息(使用USING子句)
SELECT R.stuID,C.curID, C.curName,R.result
FROM T_result R JOIN T_curriculum C
ON R.curID=C.curID
--对成绩信息表(T_result)和课程信息表(T_curriculum)进行交叉连接
SELECT R.stuID,C.curID
FROM T_result R,T_curriculum C
--例.9 在课程信息表中选择课时数比操作系统的课时数多的课程信息
SELECT C2.curID,C2.curName,C2.credit
FROM T_curriculum C1,T_curriculum C2
WHERE C1.curName = '操作系统'
AND C1.credit<C2.credit
--查询学生对应的所有选课信息(左外连接)
SELECT R.stuID,R.curID,C.curName
FROM T_result R LEFT JOIN T_curriculum C
on R.curID=C.curID
--查询所有课程对应的学生信息(右外连接)
SELECT R.stuID,R.curID,C.curName
FROM T_result R RIGHT JOIN T_curriculum C
on R.curID=C.curID
--查询所有学生的所有选课信息
SELECT R.stuID,R.curID,C.curName
FROM T_result R FULL JOIN T_curriculum C
on R.curID=C.curID
--查询所有学生的所有选课信息(使用并操作)
SELECT R.stuID,R.curID,C.curName
FROM T_result R LEFT JOIN T_curriculum C
on R.curID=C.curID
UNION
SELECT R.stuID,R.curID,C.curName
FROM T_result R RIGHT JOIN T_curriculum C
on R.curID=C.curID

/****************子查询*****************/
--查询教师信息表中比赵伟老师工资高的教师信息
SELECT teaID,teaName,age,sex,dept,profession,salary
FROM T_teacher
WHERE salary >
(SELECT salary
FROM T_teacher
WHERE teaName = '赵伟')
ORDER BY salary
--查询和教师姓名为毛翠的老师在同一个系的教师信息
SELECT teaID,teaName,age,sex,dept,profession,salary
FROM T_teacher
WHERE dept IN
(SELECT
dept
FROM T_teacher
WHERE teaName = '毛翠')
ORDER BY salary ASC
--查询和教师姓名为毛翠的老师不在同一个系的教师信息
SELECT teaID,teaName,age,sex,dept,profession,salary
FROM T_teacher
WHERE dept NOT IN
(SELECT
dept
FROM T_teacher
WHERE teaName = '毛翠')
ORDER BY salary ASC
--查询教师工资比任意一个数学系教师的工资都高的教师信息
SELECT teaID,teaName,age,sex,dept,profession,salary
FROM T_teacher
WHERE salary >ANY
(SELECT salary
FROM T_teacher
WHERE dept = '数学系')
AND dept != '数学系'
ORDER BY salary ASC
--查询其他院系的教师中工资比物理系工资最少的教师还低的教师信息
SELECT teaID,teaName,age,sex,dept,profession,salary
FROM T_teacher
WHERE salary <ALL
(SELECT salary
FROM T_teacher
WHERE dept = '物理系')
AND dept != '物理系'
ORDER BY salary ASC
--查询院系和职称都与教师编号为t103265的教师相同的教师信息
SELECT T.teaID,T.teaName,T.age,T.sex,T.dept,T.profession,salary
FROM T_teacher T ,(SELECT dept,profession
FROM T_teacher
WHERE teaID = 't103265') K
WHERE T.dept=K.dept AND T.profession=K.profession
--查查询选修课程编号为这门课的学生信息
SELECT S.stuID ,S.stuName,S.age,S.sex
FROM T_student S
WHERE EXISTS
(SELECT *
FROM T_result T
WHERE
S.stuID=T.stuID
AND T.curID = 't105')
--查询没有选修课程编号为这门课的学生信息(使用NOT EXISTS)
SELECT S.stuID ,S.stuName,S.age,S.sex
FROM T_student S
WHERE NOT EXISTS
(SELECT *
FROM T_result T
WHERE
S.stuID=T.stuID
AND T.curID = 't105')
--查询学生编号为s102203的成绩信息(在SELECT子句中使用子查询)
SELECT R.stuID,
(SELECT stuName
FROM T_student
WHERE stuID
= R.stuID) AS stuName, R.result,R.curID
FROM T_result R
WHERE R.stuID = 's102203'
ORDER BY R.result ASC
--查询学生编号为s102203学生的选课成绩信息
SELECT R.stuID,C.curID, C.curName,R.result
FROM
T_curriculum C,
(SELECT curID,stuID,result
FROM T_result
)R
WHERE R.curID=C.curID
AND R.stuID = 's102203'
ORDER BY R.result
--查询以学生编号s2开头的学生的平均成绩
SELECT R.stuID, AVG(R.result)
FROM
T_result R,T_curriculum C
WHERE R.curID=C.curID
GROUP BY R.stuID
HAVING R.stuID IN
(SELECT stuID
FROM T_student
WHERE stuID LIKE 's2%')
ORDER BY R.stuID
--查询教师信息表中职称与教师编号为t103265教师相同但工资比该教师高的教师信息
SELECT teaID,teaName,age,sex,dept,profession,salary
FROM T_teacher
WHERE profession =
(SELECT profession
FROM T_teacher
WHERE teaID = 't181585')
AND
salary>
(SELECT salary
FROM T_teacher
WHERE teaID = 't181585')
--创建一张新的学生信息表,要求新表中包括原来学生信息表(T_student)的所有数据
CREATE TABLE T2_student (
stuID
VARCHAR (15) PRIMARY KEY,
stuName VARCHAR (10)
NOT NULL,
age INT
sex VARCHAR (2) NOT NULL,
birth
NOT NULL,
DATETIME
NOT NULL
)
INSERT INTO T2_student
SELECT * FROM T_student

/****************常用函数*****************/
--计算给定字符串的长度
SELECT LEN('CREATE TABLE ')
--计算给定字符串中含有汉字的长度。
SELECT LEN(' CREATE 教师表')
--将指定的字符串全部转型为小写格式
SELECT LOWER('CREATE TABLE')
--将指定的字符串全部转型为大写格式
SELECT UPPER('create table')
--截取指定字符串从第个位置开始,长度为的字串
SELECT SUBSTRING('structured query language',12,5)
--取得指定字符串最左面的个子串
SELECT LEFT('structured query language',10)
--取得指定字符串最右面的个子串
SELECT RIGHT ('structured query language',8)
--去除指定字符串左侧的空格
SELECT LTRIM ('
structured query language')
--去除指定字符串右侧的空格
SELECT RTRIM (' structured query language
')
--去除指定字符串左侧和右侧的空格
SELECT LTRIM(RTRIM('
structured query language
'))
--填充空格。
SELECT SPACE(10)
--查找子串SQL在指定字符串中第一次出现的位置(使用CHARINDEX函数)
SELECT CHARINDEX ('SQL','Microsoft SQL Server')
--查找子串SQL在指定字符串中第一次出现的位置(使用PATINDEX函数)
SELECT PATINDEX ('%SQL%','Microsoft SQL Server')
--将指定字符串的顺序颠倒
SELECT REVERSE('query')
--将指定子串替换掉
SELECT STUFF('structured query language',1,10,'sql' )
--取得字符Z的ASCII码
SELECT ASCII('Z')
--取得字符串ABC的ASCII码
SELECT ASCII('ABC')
--查询对应的字符
SELECT CHAR (65)
--给出两个单词hart和heart,查询这两个单词的发音是否相同
SELECT SOUNDEX('hart')
SELECT SOUNDEX('heart')
--将指定字符串重复次后显示(REPLICATE函数)
SELECT REPLICATE('MySQL',2)
--计算指定数字的绝对值
SELECT ABS(-3)
--计算指定数字的平方值
SELECT SQUARE(-3)
--计算指定数字的平方根
SELECT SQRT(9)
--计算指定数字的自然对数
SELECT LOG(2.7182818284590451) AS value1, LOG(9) AS value2
--计算指定数字以为底的对数值
SELECT LOG10(10) AS value1, LOG10(9) AS value2
--计算-2的次幂和-2的-3次幂
SELECT POWER(-2,3), POWER(-2,-3)
--对指定数字进行四舍五入操作
SELECT ROUND(3.5,0),ROUND(2345.3654879,3)
--对指定数字进行四舍五入操作
SELECT ROUND(2345.3654879,-3), ROUND(2345.3654879,-4)
--取得大于等于指定数的最小整数
SELECT CEILING(10.3)
--取得小于等于指定数的最小整数
SELECT FLOOR(10.3)
--计算指定数字的正弦值
SELECT SIN(PI()/6)
--计算指定数字的余弦值
SELECT COS(PI()/3)
--计算指定数字的正切值
SELECT TAN(PI()/4)
--计算指定数字的余切值
SELECT COT(PI()/4)
--计算指定数字的反正弦值。
SELECT ASIN(1)
--计算指定数字的反余弦值
SELECT ACOS(1)
--计算指定数字的反正切值
SELECT ATAN(1),ATAN(10)
--将指定的弧度数转换为角度
SELECT DEGREES(PI()/3)
--将指定的角度值转换为弧度
SELECT RADIANS(60)
--取得指定值的符号标志
SELECT sign(-6),sign(0),sign(6)
--返回系统当前的日期和时间。
SELECT GETDATE()
--将当前系统日期向后推迟两天。
SELECT DATEADD(DAY,2,GETDATE() )
--将教师信息表中教师的工资信息转换为字符类型。
SELECT teaID,teaName, CAST(salary AS CHAR (5))
FROM T_teacher
--将指定的数字转换为字符类型。
SELECT STR(12345.67,3), STR(12345.67,5), STR(12345.67,7,2)
--将指定日期时间按照指定格式转换
SELECT CONVERT (VARCHAR (12), GETDATE(), 11),CONVERT (VARCHAR (12),
GETDATE(), 111)
--以查询教师的工资和津贴为例,验证COALESCE函数。
SELECT pension,salary,COALESCE(pension,salary)
FROM T_teacher
--根据教师的年龄增加相应的工资。
SELECT teaID,teaName,age,salary,CASE
WHEN age<=30 THEN salary+300
WHEN age<=40 THEN salary+200
WHEN
age<=50
THEN salary+100
ELSE salary
END newSalary
FROM T_teacher

/****************视图的创建与维护*****************/
--为教师信息表创建一个视图
CREATE VIEW V_teacher
AS
SELECT teaID,teaName,age,sex,dept,profession
FROM T_teacher
--为教师信息表创建一个视图,并为视图创建别名
CREATE VIEW
V_teacher2(v_teaID,v_teaName,v_age,v_sex,v_dept,v_profession)
AS
SELECT teaID,teaName,age,sex,dept,profession
FROM T_teacher
--创建学生成绩视图,要求视图中的学生的成绩都要在及格以上
CREATE VIEW V_result
AS
SELECT R.stuID,C.curID, C.curName,R.result
FROM T_result R,T_curriculum C
WHERE R.curID=C.curID
AND R.result>=60
--为教师信息表创建视图,要求视图中所有教师的工资要大于
CREATE VIEW V_teacher_salary
AS
SELECT dept,profession,MAX(salary) AS M_salary
FROM T_teacher
GROUP BY dept,profession
HAVING MAX(salary)>3000
--为教师信息表中院系编号为t_10的教师创建视图,并为该视图添加CHECK约束条件
CREATE VIEW V_teacher_dept
AS
SELECT T.teaID,T.teaName,T.age,T.sex, D.deptID,T.dept,T.profession
FROM T_teacher T,dbo.T_dept D
WHERE T.dept=D.deptName AND D.deptID='t_10'
WITH CHECK OPTION
--基于一个已有视图创建新的视图,要求该视图中学生成绩最高分都在分以上。
CREATE VIEW V_result2
AS
SELECT stuID,MAX(result) AS Result
FROM T_result
GROUP BY stuID
HAVING MAX(result)>=80
-------------------------
CREATE VIEW V_student
AS
SELECT * FROM T_student
-------------------------
CREATE VIEW V_teacher_salary
AS
SELECT MAX(salary)
AS maxsalary
FROM T_teacher
WHERE dept ='计算机系'
--加密视图
CREATE VIEW V_teacher_salary
WITH ENCRYPTION
AS
SELECT MAX(salary)
AS maxsalary
FROM T_teacher
WHERE dept ='计算机系'
--删除V_teacher视图。dbo.T_teacher
DROP VIEW V_teacher
DROP VIEW V_teacher_salary

--**更新视图中的数据:不是所有视图中的数据都是可更新的**
/**基本判断原则
视图中的每一列都必须和一个基本表中的一个源列对应
视图中的每一行都必须和一个基本表中的一个源行对应
**/
/**通过视图更新数据的注意事项
所有的数据修改都必须遵守在待更新基本表(甚至包括相关表)上设置的完整性约束条件
**/
/**为什么要通过视图更新数据
可以限制用户可更新的底层表中的列和行;
使列名具有更好的描述性。
**/

/****************插入数据记录*****************/
--向学生信息表中插入一条学生记录
INSERT INTO T_student
VALUES
('s281234','王龙',20,'男', '19890218')
--向学生信息表中插入一条学生记录(在INSERT INTO语句中使用列名)
INSERT INTO T_student(stuID,stuName,age,sex,birth)
VALUES
('s284321','李茜',20,'女', '19890820')
--向成绩信息表中插入一条在学生信息表中不存在的数据记录
INSERT INTO T_result
VALUES
('s284321','t321',75)
--向成绩信息表中插入一条学生的成绩记录,该学生信息存在与学生信息表中
INSERT INTO T_result
VALUES
('s281234','t321',75)
--向学生视图V_student中插入一条学生记录。
INSERT INTO V_student
VALUES
('s286666','李山山',20,'女', '19890505')

/****************修改数据记录*****************/
--修改学生信息表中一条学生记录
UPDATE T_student
SET age =age+1
WHERE stuID='s281234'
--修改课程信息表中操作系统的学分数
UPDATE T_curriculum
SET credit=4
WHERE curName
='操作系统'
--在成绩信息表中修改学生编号,该学生编号并不在学生信息表中
UPDATE T_result
SET curID ='s111111'
WHERE stuID='s281234'
--UPDATE 语句与FOREIGN KEY 约束"FK__T_result__curID__07020F21"冲突。--
--该冲突发生于数据库"test_STInfo",表"dbo.T_curriculum", column 'curID'。
--
--将成绩信息表中学生编号为s102203学生选修的t333这门课的成绩加分
UPDATE T_result
SET result=result+10
WHERE stuID='s102203'
AND curID = 't333'
--修改成绩信息表中学生编号为s281234的成绩信息
UPDATE T_result SET result=result+10
WHERE stuID='s281234'
--修改课程信息表中操作系统这门课的课时和学分,让它与C语言的课时和学分相同
UPDATE T_curriculum SET credit=
(SELECT credit
FROM T_curriculum
WHERE curName='C语言')
WHERE curName
='操作系统'
--修改教师工资(使用CASE条件表达式)。
UPDATE T_teacher SET salary=
CASE
WHEN salary<=3000
THEN salary+salary*0.1
WHEN salary>3000 AND salary<=4000
WHEN salary>4000
THEN salary+salary*0.08
THEN salary+salary*0.05
ELSE salary
END
--在教师视图V_teacher中修改教师编号为t105320教师的职称
UPDATE V_teacher
SET profession= '副教授'
WHERE teaID ='t105320'

/****************删除数据记录*****************/
--删除学生信息表中学生编号为s286666的学生记录
DELETE FROM T_student
WHERE stuID='s286666'
--在学生信息表中删除学生编号
DELETE FROM T_student
WHERE stuID='s206363'
--删除成绩信息表中学生编号为s102203选修的数据库基础课的成绩
DELETE FROM T_result
WHERE curID=
(SELECT C.curID
FROM T_curriculum C
WHERE C.curName = '数据库基础')
AND stuID='s102203'
--删除课程信息表中全部记录
DELETE FROM T_curriculum
--删除教师工资视图V_teacher_salary中一条教师信息
DELETE FROM V_teacher_salary
WHERE dept ='计算机系'
--在教师视图V_teacher中删除教师编号为t105320教师信息
DELETE FROM V_teacher
WHERE teaID
='t105320'

/****************权限的授予与回收*****************/
--将查询数据库test_STInfo中教师信息表的权限授予sa
USE test_STInfo
GRANT SELECT ON T_teacher
TO sa
--将更新数据库test_STInfo中教师信息表的权限授予user2和user3
GRANT INSERT,UPDATE,DELETE ON T_teacher
TO user2, user3
--将修改数据库test_STInfo中教师信息表教师工资的权限授予user3
GRANT UPDATE(salary) ON T_teacher
TO user3
--将查询数据库test_STInfo中教师信息表的权限授予user2,
并允许user2将该授权授予其
他用户
GRANT SELECT ON T_teacher
TO user2
WITH GRANT OPTION
--将更新数据库test_STInfo中教师信息表的权限授予所有用户
GRANT INSERT,UPDATE,DELETE ON T_teacher
TO PUBLIC
--将查询数据库test_STInfo中教师信息表的权限收回
REVOKE SELECT ON T_teacher
FROM user2

/****************事务的控制与管理*****************/
--开始并提交一个事务。
BEGIN TRANSACTION
INSERT INTO accountTable VALUES ('A',5000);
UPDATE accountTable SET cashValue=cashValue+1000 WHERE accoutUser = 'A';
COMMIT
SELECT * FROM accountTable
--设置事务的隔离级别为可重复读。
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
--查看数据库详细信息
EXEC SP_HELPDB test_STInfo
--重命名数据库
ALTER DATABASE test_STInfo
MODIFY NAME=test_new
--使用系统存储过程
EXEC SP_RENAMEDB 'test_new','test_STInfo'
--自增字段
CREATE TABLE num
(num1 int IDENTITY(1,1) NOT NULL)
--增加一列,设置默认值
ALTER TABLE num ADD num2 VARCHAR(15)
DEFAULT '中国' NOT NULL
--修改列
ALTER TABLE num ALTER COLUMN num2 VARCHAR(10) NOT NULL
--删除列
ALTER TABLE num DROP COLUMN num2
--删除表
DROP TABLE num

--****删除表时先删除引用表***--
--CHECK约束
ALTER TABLE num ADD num3 VARCHAR(20)
ALTER TABLE num ADD CONSTRAINT CK_num
CHECK (num3 IN ('A','B'))
--------------------------------------
ALTER TABLE num ADD num4 VARCHAR(20)
ALTER TABLE num ADD CONSTRAINT CK_num1
CHECK (num4 LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')
--禁用一个约束条件
ALTER TABLE num NOCHECK CONSTRAINT CK_num
--启用一个约束
ALTER TABLE num CHECK CONSTRAINT CK_num
--禁用所有约束
ALTER TABLE num NOCHECK CONSTRAINT ALL
--启用所有约束
ALTER TABLE num CHECK CONSTRAINT ALL
--删除约束
ALTER TABLE num DROP CONSTRAINT CK_num
--***动态约束使用触发器实现***--