SQL 语言是一种高度非过程化语言。
/****************数据库的创建与删除*****************/--创建一个名为test_STInfo的数据库--CREATE DATABASE test_STInfoDROP DATABASE test_STInfoUSE test_STInfo
/****************数据表的创建与更新*****************/--创建表名为T_student的学生信息表--CREATE TABLE T_student (stuIDVARCHAR (15) PRIMARY KEY,stuName VARCHAR (10)NOT NULL,age INTsex VARCHAR (2) NOT NULL,birthNOT NULL,DATETIMENOT NULL)
--创建院系信息表,并为院校编号所在列定义唯一约束。--CREATE TABLE T_dept (deptIDVARCHAR (15) NOT NULL,deptName VARCHAR (10))--创建成绩信息表--CREATE TABLE T_result(stuID VARCHAR (15) ,curID VARCHAR (15) ,resultFLOAT ,PRIMARY KEY (stuID,curID))DROP TABLE T_result--为成绩信息表定义外键--CREATE TABLE T_result(stuID VARCHAR (15) NOT NULL,curID VARCHAR (15) NOT NULL,resultFLOAT ,FOREIGN KEY(stuID) REFERENCES T_student(stuID) ON DELETE CASCADE,PRIMARY KEY (stuID,curID))--定义课程信息表--CREATE TABLE T_curriculum(curIDVARCHAR (15) PRIMARY KEY,curName VARCHAR (10) ,creditINT,CHECK(credit BETWEEN 3 AND 8))--创建教师信息表--CREATE TABLE T_teacher (teaIDVARCHAR (15) PRIMARY KEY,teaName VARCHAR (10)NOT NULL,age INTNOT NULL,sex VARCHAR (2) NOT NULL,deptID VARCHAR (15),dept VARCHAR (20)NOT NULL,profession VARCHAR (10))--直接从结果集中创建表SELECT stuID,result,SUM(result) AS sum_reINTO copy_result FROMT_resultGROUP BY stuID,result--为教师信息表中表示教师职称单列创建索引--CREATE INDEX i_professionON T_teacher(profession)--为教师信息表中表示教师所在院系的列和教师职称的列创建复合索引--CREATE INDEX i_dept_professionON T_teacher(dept,profession)--删除教师信息表中为列教师职称创建的索引--DROP INDEX T_teacher.i_profession--向教师信息表中增加一个表示教师工资的列--ALTER TABLE T_teacher ADD salary INT NOT NULLALTER 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,birthFROM T_studentWHERE birth >'19860101'--查询教师信息表中年龄在到岁之间的教师信息SELECT teaID,teaName,age,sex,dept,professionFROM T_teacherWHERE age BETWEEN 30 AND 50----------------------------SELECT teaID,teaName,age,sex,dept,profession-8-FROM T_teacherWHERE age>=30 AND age<=50----------------------------SELECT teaID,teaName,age,sex,dept,professionFROM T_teacherWHERE NOT age BETWEEN 30 AND 50--查询学号首字母在a到z的所有学生SELECT * FROM T_teacherWHERE teaID BETWEEN 'a' AND 'z'--查询教师信息表中教师所在部门为计算机系和数学系的所有教师信息SELECT teaID,teaName,age,sex,dept,professionFROM T_teacherWHERE dept IN('计算机系','数学系')----------------------------SELECT teaID,teaName,age,sex,dept,professionFROM T_teacherWHERE dept='计算机系' OR dept='数学系'--查询教师信息表中不在计算机系或者数学系的所有教师信息SELECT teaID,teaName,age,sex,dept,professionFROM T_teacherWHERE dept NOT IN('计算机系','数学系')--查询学生信息表中位学生编号中以s开头并以结尾的学生信息SELECT stuID,stuName,age,sex,birthFROM T_studentWHERE stuID LIKE's_____3'----------------------------SELECT stuID,stuName,age,sex,birthFROM T_studentWHERE stuID NOT LIKE 's_____3'--例.15 查询学生信息表中姓王的所有学生的信息SELECT stuID,stuName,age,sex,birthFROM T_studentWHERE stuName LIKE'王%'----------------------------SELECT deptID,deptNameFROM T_deptWHEREdeptID like '%$_%' ESCAPE '$'--查询以讲或教开头的职称的所有教师信息SELECT teaID,teaName,age,sex,dept,professionFROM T_teacherWHERE profession LIKE '[讲教]%'----------------------------SELECT teaID,teaName,age,sex,dept,professionFROM T_teacherWHERE profession LIKE '[^讲教]%'----查询课程号不为空的教师信息SELECT * FROM T_teacherWHERE deptID ISNOT NULL--查询教师信息表中教师津贴为空的教师信息SELECT teaID ,teaName , profession,salary,pensionFROM T_teacherWHERE pension IS NULL--***空值检查注意:NULL与、空字符串是完全不同的***--查询课程表中课程学分>4的课程信息SELECT curName,creditFROM T_curriculumWHERE credit>=4--查询教师信息表中教师职称不是教授的教师信息SELECT teaID,teaName,dept,professionFROM T_teacherWHERE profession!='教授'--查询教师信息表中年龄在岁以上,职称为教授的教师信息SELECT teaID,teaName,age,sex,dept,professionFROM T_teacherWHERE age>'45' AND profession='教授'--查询教师信息表中年龄在岁以上或者职称为教授的教师信息SELECT teaID,teaName,age,sex,dept,professionFROM T_teacherWHERE age>'45' OR profession='教授'--查询计算机系或者是数学系中教师的工资大于的教师信息SELECT teaID,teaName,age,sex,dept,profession ,salaryFROM T_teacherWHERE (dept ='计算机系'OR dept ='数学系') AND salary >3000--查询T_student中所有学生的全部信息(使用列别名)SELECT stuID AS 学生编号, stuName AS 学生姓名, age AS 年龄, sex AS 性别, birthAS 出生日期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,salaryFROM T_teacherORDER BY salary ASC--查询教师信息表中教师的工资,并按照教师工资从低到高排序SELECT teaID,teaName,dept,profession,salaryFROM T_teacherORDER BY salary DESC--查询工资,并按照工资从低到高排序(使用列序号方式)SELECT teaID,teaName,dept,profession,salaryFROM T_teacherORDER BY 5 ASC--查询所有教师的工资,并按照从低到高排序(表示工资的列不出现)SELECT teaID,teaName,dept,professionFROM T_teacherWHERE dept ='计算机系'ORDER BY salary---------------------------------SELECT teaID,teaName,dept,profession,salaryFROM T_teacherORDER 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_teacherGROUP BY dept--对教师信息表中的院校和教师职称进行分组,并统计出该院校教师的最高工资SELECT dept,profession,MAX(salary)FROM T_teacherGROUP BY dept,profession--对院校和教师职称进行分组,并统计出该院校教师最高工资>3000的记录SELECT dept,profession,MAX(salary)FROM T_teacherGROUP BY dept,professionHAVING MAX(salary)>3000--对教师信息表中的职称进行分组,并统计出年龄>30教师最高工资>3000的记录SELECT profession,MAX(salary)FROM T_teacherWHERE age>30GROUP BY professionHAVING MAX(salary)>3000--对教师信息表中的院校和教师职称进行分组,并查询出教师最高工资>3000的教师姓名SELECT dept,professionFROM T_teacherGROUP BY dept,professionHAVING MAX(salary)>3000--对教师信息表中的院校进行分组,并根据每个院校中教师的最高工资降序排序SELECT dept,profession,MAX(salary)FROM T_teacherGROUP BY dept,professionORDER BY MAX(salary) DESC--对教师信息表中的教师津贴字段进行分组SELECT COUNT(*),pensionFROM T_teacherGROUP BY pensionORDER BY pension ASC--对教师信息表中的院校和教师职称进行分组,并对分组后的教师最高工资进行统计SELECT dept,profession,SUM(salary)FROM T_teacherGROUP BY dept,profession WITH ROLLUP--查询教师信息表中的教师信息,只显示按照教师编号升序排序后的前条记录SELECT TOP 3 teaID,teaName,dept,professionFROM T_teacherORDER BY teaID
/****************连接查询与集合查询*****************/--查询学生所选课程的成绩信息SELECT T_result.stuID,T_curriculum.curID,T_curriculum.curName,T_result.resultFROM T_result,T_curriculumWHERE T_result.curID=T_curriculum.curIDORDER BY T_result.stuID-- 查询学生所选课程的成绩信息(使用别名)SELECT R.stuID,C.curID, C.curName,R.resultFROM T_result R,T_curriculum CWHERE R.curID=C.curIDORDER BY R.stuID--查询学生编号为s102203学生的课程成绩信息SELECT R.stuID,C.curID, C.curName,R.resultFROMT_result R,T_curriculum CWHERE R.curID=C.curIDAND R.stuID = 's102203'--查询学生姓名为郑茹的学生选课成绩信息SELECT R.stuID,S.stuName,C.curID, C.curName,R.resultFROM T_result R,T_curriculum C,T_student SWHERE R.curID=C.curIDAND R.stuID=S.stuIDAND S.stuName = '赵亮'--查询课程成绩在分以上的学生选课信息SELECT R.stuID,C.curID, C.curName,R.resultFROM T_result R,T_curriculum CWHERE R. result>80--查询学生编号为s102203学生的选课成绩信息(使用ON子句)SELECT R.stuID,C.curID, C.curName,R.resultFROM T_result R JOIN T_curriculum CONR.curID=C.curIDANDR.stuID = 's102203'--查询学生所选课程的成绩信息(使用USING子句)SELECT R.stuID,C.curID, C.curName,R.resultFROM T_result R JOIN T_curriculum CON R.curID=C.curID--对成绩信息表(T_result)和课程信息表(T_curriculum)进行交叉连接SELECT R.stuID,C.curIDFROM T_result R,T_curriculum C--例.9 在课程信息表中选择课时数比操作系统的课时数多的课程信息SELECT C2.curID,C2.curName,C2.creditFROM T_curriculum C1,T_curriculum C2WHERE C1.curName = '操作系统'AND C1.credit<C2.credit--查询学生对应的所有选课信息(左外连接)SELECT R.stuID,R.curID,C.curNameFROM T_result R LEFT JOIN T_curriculum Con R.curID=C.curID--查询所有课程对应的学生信息(右外连接)SELECT R.stuID,R.curID,C.curNameFROM T_result R RIGHT JOIN T_curriculum Con R.curID=C.curID--查询所有学生的所有选课信息SELECT R.stuID,R.curID,C.curNameFROM T_result R FULL JOIN T_curriculum Con R.curID=C.curID--查询所有学生的所有选课信息(使用并操作)SELECT R.stuID,R.curID,C.curNameFROM T_result R LEFT JOIN T_curriculum Con R.curID=C.curIDUNIONSELECT R.stuID,R.curID,C.curNameFROM T_result R RIGHT JOIN T_curriculum Con R.curID=C.curID
/****************子查询*****************/--查询教师信息表中比赵伟老师工资高的教师信息SELECT teaID,teaName,age,sex,dept,profession,salaryFROM T_teacherWHERE salary >(SELECT salaryFROM T_teacherWHERE teaName = '赵伟')ORDER BY salary--查询和教师姓名为毛翠的老师在同一个系的教师信息SELECT teaID,teaName,age,sex,dept,profession,salaryFROM T_teacherWHERE dept IN(SELECTdeptFROM T_teacherWHERE teaName = '毛翠')ORDER BY salary ASC--查询和教师姓名为毛翠的老师不在同一个系的教师信息SELECT teaID,teaName,age,sex,dept,profession,salaryFROM T_teacherWHERE dept NOT IN(SELECTdeptFROM T_teacherWHERE teaName = '毛翠')ORDER BY salary ASC--查询教师工资比任意一个数学系教师的工资都高的教师信息SELECT teaID,teaName,age,sex,dept,profession,salaryFROM T_teacherWHERE salary >ANY(SELECT salaryFROM T_teacherWHERE dept = '数学系')AND dept != '数学系'ORDER BY salary ASC--查询其他院系的教师中工资比物理系工资最少的教师还低的教师信息SELECT teaID,teaName,age,sex,dept,profession,salaryFROM T_teacherWHERE salary <ALL(SELECT salaryFROM T_teacherWHERE dept = '物理系')AND dept != '物理系'ORDER BY salary ASC--查询院系和职称都与教师编号为t103265的教师相同的教师信息SELECT T.teaID,T.teaName,T.age,T.sex,T.dept,T.profession,salaryFROM T_teacher T ,(SELECT dept,professionFROM T_teacherWHERE teaID = 't103265') KWHERE T.dept=K.dept AND T.profession=K.profession--查查询选修课程编号为这门课的学生信息SELECT S.stuID ,S.stuName,S.age,S.sexFROM T_student SWHERE EXISTS(SELECT *FROM T_result TWHERES.stuID=T.stuIDAND T.curID = 't105')--查询没有选修课程编号为这门课的学生信息(使用NOT EXISTS)SELECT S.stuID ,S.stuName,S.age,S.sexFROM T_student SWHERE NOT EXISTS(SELECT *FROM T_result TWHERES.stuID=T.stuIDAND T.curID = 't105')--查询学生编号为s102203的成绩信息(在SELECT子句中使用子查询)SELECT R.stuID,(SELECT stuNameFROM T_studentWHERE stuID= R.stuID) AS stuName, R.result,R.curIDFROM T_result RWHERE R.stuID = 's102203'ORDER BY R.result ASC--查询学生编号为s102203学生的选课成绩信息SELECT R.stuID,C.curID, C.curName,R.resultFROMT_curriculum C,(SELECT curID,stuID,resultFROM T_result)RWHERE R.curID=C.curIDAND R.stuID = 's102203'ORDER BY R.result--查询以学生编号s2开头的学生的平均成绩SELECT R.stuID, AVG(R.result)FROMT_result R,T_curriculum CWHERE R.curID=C.curIDGROUP BY R.stuIDHAVING R.stuID IN(SELECT stuIDFROM T_studentWHERE stuID LIKE 's2%')ORDER BY R.stuID--查询教师信息表中职称与教师编号为t103265教师相同但工资比该教师高的教师信息SELECT teaID,teaName,age,sex,dept,profession,salaryFROM T_teacherWHERE profession =(SELECT professionFROM T_teacherWHERE teaID = 't181585')ANDsalary>(SELECT salaryFROM T_teacherWHERE teaID = 't181585')--创建一张新的学生信息表,要求新表中包括原来学生信息表(T_student)的所有数据CREATE TABLE T2_student (stuIDVARCHAR (15) PRIMARY KEY,stuName VARCHAR (10)NOT NULL,age INTsex VARCHAR (2) NOT NULL,birthNOT NULL,DATETIMENOT NULL)INSERT INTO T2_studentSELECT * 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,CASEWHEN age<=30 THEN salary+300WHEN age<=40 THEN salary+200WHENage<=50THEN salary+100ELSE salaryEND newSalaryFROM T_teacher/****************视图的创建与维护*****************/--为教师信息表创建一个视图CREATE VIEW V_teacherASSELECT teaID,teaName,age,sex,dept,professionFROM T_teacher--为教师信息表创建一个视图,并为视图创建别名CREATE VIEWV_teacher2(v_teaID,v_teaName,v_age,v_sex,v_dept,v_profession)ASSELECT teaID,teaName,age,sex,dept,professionFROM T_teacher--创建学生成绩视图,要求视图中的学生的成绩都要在及格以上CREATE VIEW V_resultASSELECT R.stuID,C.curID, C.curName,R.resultFROM T_result R,T_curriculum CWHERE R.curID=C.curIDAND R.result>=60--为教师信息表创建视图,要求视图中所有教师的工资要大于CREATE VIEW V_teacher_salaryASSELECT dept,profession,MAX(salary) AS M_salaryFROM T_teacherGROUP BY dept,professionHAVING MAX(salary)>3000--为教师信息表中院系编号为t_10的教师创建视图,并为该视图添加CHECK约束条件CREATE VIEW V_teacher_deptASSELECT T.teaID,T.teaName,T.age,T.sex, D.deptID,T.dept,T.professionFROM T_teacher T,dbo.T_dept DWHERE T.dept=D.deptName AND D.deptID='t_10'WITH CHECK OPTION--基于一个已有视图创建新的视图,要求该视图中学生成绩最高分都在分以上。CREATE VIEW V_result2ASSELECT stuID,MAX(result) AS ResultFROM T_resultGROUP BY stuIDHAVING MAX(result)>=80-------------------------CREATE VIEW V_studentASSELECT * FROM T_student-------------------------CREATE VIEW V_teacher_salaryASSELECT MAX(salary)AS maxsalaryFROM T_teacherWHERE dept ='计算机系'--加密视图CREATE VIEW V_teacher_salaryWITH ENCRYPTIONASSELECT MAX(salary)AS maxsalaryFROM T_teacherWHERE dept ='计算机系'--删除V_teacher视图。dbo.T_teacherDROP VIEW V_teacherDROP VIEW V_teacher_salary
--**更新视图中的数据:不是所有视图中的数据都是可更新的**/**基本判断原则视图中的每一列都必须和一个基本表中的一个源列对应视图中的每一行都必须和一个基本表中的一个源行对应**//**通过视图更新数据的注意事项所有的数据修改都必须遵守在待更新基本表(甚至包括相关表)上设置的完整性约束条件**//**为什么要通过视图更新数据可以限制用户可更新的底层表中的列和行;使列名具有更好的描述性。**/
/****************插入数据记录*****************/--向学生信息表中插入一条学生记录INSERT INTO T_studentVALUES('s281234','王龙',20,'男', '19890218')--向学生信息表中插入一条学生记录(在INSERT INTO语句中使用列名)INSERT INTO T_student(stuID,stuName,age,sex,birth)VALUES('s284321','李茜',20,'女', '19890820')--向成绩信息表中插入一条在学生信息表中不存在的数据记录INSERT INTO T_resultVALUES('s284321','t321',75)--向成绩信息表中插入一条学生的成绩记录,该学生信息存在与学生信息表中INSERT INTO T_resultVALUES('s281234','t321',75)--向学生视图V_student中插入一条学生记录。INSERT INTO V_studentVALUES('s286666','李山山',20,'女', '19890505')/****************修改数据记录*****************/--修改学生信息表中一条学生记录UPDATE T_studentSET age =age+1WHERE stuID='s281234'--修改课程信息表中操作系统的学分数UPDATE T_curriculumSET credit=4WHERE curName='操作系统'--在成绩信息表中修改学生编号,该学生编号并不在学生信息表中UPDATE T_resultSET curID ='s111111'WHERE stuID='s281234'--UPDATE 语句与FOREIGN KEY 约束"FK__T_result__curID__07020F21"冲突。----该冲突发生于数据库"test_STInfo",表"dbo.T_curriculum", column 'curID'。----将成绩信息表中学生编号为s102203学生选修的t333这门课的成绩加分UPDATE T_resultSET result=result+10WHERE stuID='s102203'AND curID = 't333'--修改成绩信息表中学生编号为s281234的成绩信息UPDATE T_result SET result=result+10WHERE stuID='s281234'--修改课程信息表中操作系统这门课的课时和学分,让它与C语言的课时和学分相同UPDATE T_curriculum SET credit=(SELECT creditFROM T_curriculumWHERE curName='C语言')WHERE curName='操作系统'--修改教师工资(使用CASE条件表达式)。UPDATE T_teacher SET salary=CASEWHEN salary<=3000THEN salary+salary*0.1WHEN salary>3000 AND salary<=4000WHEN salary>4000THEN salary+salary*0.08THEN salary+salary*0.05ELSE salaryEND--在教师视图V_teacher中修改教师编号为t105320教师的职称UPDATE V_teacherSET profession= '副教授'WHERE teaID ='t105320'
/****************删除数据记录*****************/--删除学生信息表中学生编号为s286666的学生记录DELETE FROM T_studentWHERE stuID='s286666'--在学生信息表中删除学生编号DELETE FROM T_studentWHERE stuID='s206363'--删除成绩信息表中学生编号为s102203选修的数据库基础课的成绩DELETE FROM T_resultWHERE curID=(SELECT C.curIDFROM T_curriculum CWHERE C.curName = '数据库基础')AND stuID='s102203'--删除课程信息表中全部记录DELETE FROM T_curriculum--删除教师工资视图V_teacher_salary中一条教师信息DELETE FROM V_teacher_salaryWHERE dept ='计算机系'--在教师视图V_teacher中删除教师编号为t105320教师信息DELETE FROM V_teacherWHERE teaID='t105320'
/****************权限的授予与回收*****************/--将查询数据库test_STInfo中教师信息表的权限授予saUSE test_STInfoGRANT SELECT ON T_teacherTO sa--将更新数据库test_STInfo中教师信息表的权限授予user2和user3GRANT INSERT,UPDATE,DELETE ON T_teacherTO user2, user3--将修改数据库test_STInfo中教师信息表教师工资的权限授予user3GRANT UPDATE(salary) ON T_teacherTO user3--将查询数据库test_STInfo中教师信息表的权限授予user2,并允许user2将该授权授予其他用户GRANT SELECT ON T_teacherTO user2WITH GRANT OPTION--将更新数据库test_STInfo中教师信息表的权限授予所有用户GRANT INSERT,UPDATE,DELETE ON T_teacherTO PUBLIC--将查询数据库test_STInfo中教师信息表的权限收回REVOKE SELECT ON T_teacherFROM user2
/****************事务的控制与管理*****************/--开始并提交一个事务。BEGIN TRANSACTIONINSERT INTO accountTable VALUES ('A',5000);UPDATE accountTable SET cashValue=cashValue+1000 WHERE accoutUser = 'A';COMMITSELECT * FROM accountTable--设置事务的隔离级别为可重复读。SET TRANSACTION ISOLATION LEVEL REPEATABLE READ--查看数据库详细信息EXEC SP_HELPDB test_STInfo--重命名数据库ALTER DATABASE test_STInfoMODIFY 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_numCHECK (num3 IN ('A','B'))--------------------------------------ALTER TABLE num ADD num4 VARCHAR(20)ALTER TABLE num ADD CONSTRAINT CK_num1CHECK (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--***动态约束使用触发器实现***--
