《[高等教育]SQL serve基础知识笔记经典_必看.doc》由会员分享，可在线阅读，更多相关《[高等教育]SQL serve基础知识笔记经典_必看.doc（46页珍藏版）》请在三一文库上搜索。
1、一、 运行及连接启动“配置工具”中的“SQL server配置管理器”，然后启动“SQL server(MSSQlSERVER)”，再关闭“SQL server配置管理器”,启动“Microsoft SQL Server Management Studio”.此时即可正常运行了.二、 编辑环境1、 SQL Server是对大小写不敏感的语言，即不区分大小写。2、 在新建查询时注意：新建的查询应该是选择对象为当前的数据库，并且新建表后需要在数据库名称上右击，选择“刷新”，此时才会显示新建的表.三、 数据定义-创建基本表（1）基本格式如下：CREATE TABLE ( 列级完整性约束条件 , 列级
2、完整性约束条件 , ) ;例如：建立一个“课程”表coursecreate table Course(Cno char(4) primary key ,/*列级完整性约束*/Cname char(40) ,Cpno char(4) ,Ccredit smallint ,foreign key (Cpno) references Course(Cno) /*表级完整性约束*/);注：表级完整性约束条件与列级完整性约束条件均可以有多个.（2）完整性约束如下：1) 主码约束： PRIMARY KEY2) 唯一性约束：UNIQUE3) 非主值约束：NOT NULL4) 参照完整性约束注：PRIMARY
3、 KEY = UNIQUE + NOT NULL四、 数据定义-修改基本表（1）一般格式如下：ALTER TABLE ADD 完整性约束 DROP ALTER COLUMN ;其中：ADD 子句用于增加新列和新的完整性约束条件；DROP子句用于删除指定的完整性约束条件 ；ALTER COLUMN 子句用于修改原有的列定义，包括修改列名和数据类型。注：无论表中是否有数据，新增加的列一律为空.（2）新增列向Student表增加“入学时间”列，其数据类型为日期型。ALTER TABLE Student ADD Sentreac DATE ;（3）修改数据类型将年龄的数据类型改为char(假设原来为i
4、nt).ALTER TABLEStudentALTERcoulumn Sage char.（4）增加约束条件增加课程名称必须惟一值的约束条件 ALTER TABLE Course ADD UNIQUE (Cname) ;（5）删除某一列格式如下：Alter table drop column ;例如：删除COMPANY数据库PROJ表中的Begindate和Enddate两列。alter table COMPANY.dbo.PROJ drop coLumn Begindate ;alter table PROJ drop column Enddate ;五、 基本数据类型数据类型含义Char(
5、n)长度为n的定长字符串Varchar(n)最大长度为n的变长字符串Int长整数Smallint短整数Numeric(p,d)定点数，由p位数字(不包括符号、小点数)组成，小数后面有d位数字Real取决于机器精度的浮点数Double precision取决于机器的双精度浮点数Float(n)浮点数，精度至少为n位数字Date日期，包含年、月、日，格式为YYYY-MM-DDTime时间，包含一日的时、分、秒，格式为：HH-MM-SS六、 数据定义-删除基本表（1） 删除基本表的一般格式Drop table restrict|cascade ;说明：a) 若选择restrict则该表的删除是有限条
6、件的欲删除的基本表不能被其它表的约束所引用(如check , foreign key等约束)，不能有视图，不能有触发器，不能有存储过程和函数。如果存在这些依赖对象则该表不能被删除。b) 若选择cascade，则该表的删除没有限制条件。在删除基本表的同时，相关的依赖项，例如视图，都被一起删除。（2） 举例删除DEPT表：drop table DEPT ;七、 数据更新-插入数据(1)插入元祖A）格式如下：Insert Into ( ,) ;Values ( , ) ;B）说明：Into子句中没有出现的属性列，新元祖在这些列上将去空值；如过某个常量为空或者不确定的话可以用 取代.C）举例：向dep
7、t表添加14号部门客服中心，地址为开发区紫光路2号。insert into dept(DNO ,DNSME ,ADDR)values (14 ,部门客服中心 ,开发区紫光路号) ;向dept表添加15号部门技术支持部，地址不详。insert into deptvalues (15 ,部门技术支持部 ,NULL) ;向empl表添加1401号员工张山，入职时间为2007年9月1日，工资2000元，14号部门。insert into EMPLvalues (1401 ,张山 ,2007-9-1 ,2000 ,14) ;注意：字符串要用单引号括起来，标点符号一律是英文状态下输入的(2)插入子查询结果
8、A）格式：InsertInto ( ,) 子查询 ;B）举例：对每一个系，求学生的平均年龄，并把结果存入数据库InsertInto DEPT_age (Sdept ,Ava_age) Select Sdept , AVG(Sage)From StudentGroup by Sdept ;八、 数据更新-修改数据（1） 修改数据的一般格式Update Set = , = where ;（2） 修改某一个元组的值把dept表中客服中心的地址改为“大连甘井子区红岭路”。update deptset ADDR = 大连甘井子区红岭路where DNAME = 客服中心 ;（3） 修改多个元组的值把jo
9、b表中参与104号项目的每人天数增加5天。update JOBset DAYS = DAYS + 5 where PNO = 104 ;把job表中的每人天数乘以系数0.8。update JOBset DAYS = DAYS * 0.8 （4） 带子查询的的修改语句将计算机科学系全体学生的成绩置零Update SCSet Grade = 0Where CS =(select SdeptFrom StudentWhere Student.Sno = SC.Sno) ;九、 数据更新-删除数据（1）一般格式Delete From where ;（1） 删除某一个元组的值删除dept表中15号部门的
10、信息。deletefrom deptwhere DNO = 15 ;（2） 删除多个元组的值删除dept表中地址为空的部门。deletefrom deptwhere ADDR is NULL ;删除empl表中2008年入职且工资低于1500元的员工。delete from EMPL where HIREDATE like 2008% and SALARY 1500 ;（3） 带子查询的删除语句Delete From SCWhere CS = ( select Sdept From StudentWhere Student.Sno = SC.Sno ) ;十、 数据传输-运行脚本添加数据步骤如
11、下：(以添加到COMPANY数据库为例)(1)使用记事本编辑dept.sql文件(可以新建一个文本文档然后将其扩展名改为.sql)，添加数据如下：Insert into dept values(10,市场销售部，大连市软体园路8号);Insert into dept values(11,财务部，大连市软体园路6号);Insert into dept values(12,研发部，大连市软体园路9号);Insert into dept values(13,人力资源部，大连市软体园路4号);关闭并保存文件。(2)选择菜单“文件” “打开”“文件(F)”选择COMPANY为当前数据库，如图：depa.
12、sql的语句被显示在编辑窗口，按F5执行查询，如图：执行成功后显示的结果如图：十一、 数据传输-从Excel表导入数据到数据库步骤如下：(以导入到COMPANY数据库中的DEPT表为例)(1)开始菜单-导入和导出数据，如下图：(2) 选择数据源为Microsoft Excel 97-2000,如图：(数据源为已经建好数据的表格)选择目的为COMPANY数据库，如图：指定从源数据库复制表和视图，如图：选择源表为DEPT$,目的表为COMPANY.dbo.DEPT,如图：立即运行，数据即可导入到指定的表。十二、 数据传输-从数据库导出数据到Excel表步骤如下：(以从COMPANY数据库的表dep
13、t导出数据到excel表格为例)(1)启动“导入和导出数据(32)”，按照上面的导入数据的步骤操作,选择数据源为COMPANY数据库，如图：选择目的为Excel文件，并为文件命名，如图：指定从源数据库复制表和视图，如图：选择源表为DEPT，如图：立即运行，单击“完成”按钮，执行成功，如图：打开DEPT.XLS查看结果，如图：十三、 数据查询-单表查询(1)查询语句格式Select all|distinct ,From ,where group by having order by asc|desc ;注：all|distinct中all为缺省值，取消结果中的重复列则用distinct；asc|
14、desc中asc为缺省值，表示按照升序排列。对于空值，若按照升序排，则含空值的元组显示在最后面；若按降序排，则空值的元组最先显示。(2)查询指定列a)查询部门表dept中所有部门的详细信息，并且列名用汉字表示。 select DNO,DNAME,ADDRfrom dept ;b)查询部门表dept中人力资源部的部门编号。select DNOfrom deptwhere DNAME = 人力资源部 ;中各个列的先后顺序可以与表中的顺序不一致.(3)查询全部列查询全体学生的详细记录Select *From Student ;(4)将查询结果的列名用别名显示查询部门表dept中所有部门的详细信息，并
15、且列名用汉字表示。 select DNO 部门编号 ,DNAME 部门名称 ,ADDR 部门地址from dept ;(5)在查询的结果中插入新的一列用来显示指定的内容Select Sname NAME ,Year of Birth: BIRTH ,Sbirth BIRTHDAY ,Sdept DEPARTMENTFrom Stuent ;则显示的结果中，每个元组的第二列均为”Year of Birth:”，此列在原数据库中是不存在的.(6)查询经过计算的值Select 子句的 不仅可以是表中的属性列，也可以是表达式。例：查询全体学生的姓名及其出生年月Select Sname ,2004 Sa
16、ge /*当时年份减去年龄为出生年月From Stufent ;(7)设置查询显示的字母全为大写(或小写)Select Sname ,Year of Birth: ,2004 Sage ,LOWER(Sdept)From Student ;此时Sdept显示的结果全为小写Select Sname ,Year of Birth: ,2004 Sage ,UPPER(Sdept)From Student ;注：要设置查询表中的属性列名的大小写可以用LOWER,UPPER。(8)消除取值重复的行a)查询雇员表empl中出现的所有部门编号，要求无重复。select distinct DNOfrom E
17、MPL ;b)查询项目表proj中所有项目名称。select PNAMEfrom PROJ等价于select all PNAMEfrom PROJ(9)查询满足条件的元组常用的查询条件查询条件谓词比较=, , = , = ,!= , ,! , ! ,NOT+上述比较运算符确定范围Between and ,not between and确定集合In , not in字符匹配Like ,not like空值NULL ,NOT NULL多重条件(逻辑运算)And ,or ,not注：Between后是范围的下限，and后是范围的上限.查询结果中包含上下限的结果.a) 查询成绩不及格的学生的学号Sel
18、ect distinct SnoFrom SCWhere Grade 60 ;b) 查询年龄在20到30(包含20及30)之间的学生的姓名、系别和年龄Select Sname ,Sdept ,SageFrom StudentWhere Sage between 20 and 30 ;c) 查询计算科学系(CS) ,数学系(MA) ,信息系(IS)学生的姓名和性别Select Sname ,Ssex From StudentWhere Sdept in (CS ,MA ,IS) ;(10)含通配符”%”的查询%(百分号)：代表任意长度(长度可以为0)的字符串，例如a%b代表以a开头且以b结尾的任
19、意长度的字符串，acb ,afdsagasdsab ,ab都满足。例：查询雇员表empl中姓名以“伟“字结尾的员工信息。select *from EMPLwhere ENAME like %伟 ;(11)含通配符”_”的查询_(下划线)：代表任意单个字符，例a_b代表以a开头且以b结尾的长度为3的字符串。例：查询姓“欧阳”且全名3个汉字的学生的姓名Select SnameFrom StudentWhere Sname like 欧阳_ ;注：一个汉字占两个字符的位置(12)字符串本身含通配符的查询使用使用escape 。查询以”DB_”开头，且倒数第三个字符为i的课程的详细情况。Select
20、*From StudentWhere Cname like DB_%i_ escape ;注：escape 表示”为换码字符，则第一个”_”是普通的字符，后两个”_”表示通配符。(13)涉及空值的查询例：查询所有有成绩的学生的学号Select Sno From SCWhere Grade IS NULL ;注：此处“IS”不能用”=”替代(14)带排序的查询查询工作表job中的工作信息，结果按工作天数升序排列。select *from JOBorder by DAYS asc ;等价于select *from JOBorder by DAYS asc ;查询雇员表empl中所有员工的详细信息，
21、结果按员工姓名降序排列。select *from EMPLorder by ENAME desc ;(15)含聚集函数的查询聚集函数主要有：Count (distinct|all *)/统计元组个数Count (distinct|all )/统计一列中值的个数Sum (distinct|all )/计算一列值的总和(此列必须是数值型)Avg (distinct|all )/计算一列值的平均值(此列必须是数值型)Max (distinct|all )/求一列值中的最大值Min (distinct|all )/求一列值中的最小值注：distinct|all缺省时为all.在聚集函数遇到空值时，除c
22、ount(*)外，都跳过空值而只处理非空值.例：查询1号课程的学生的最高分数Select max(Grade)From SCWhere Cno = 1 ;(16)到GROUP BY子句的查询a)求各个课程号及相应的选课人数select Cno ,count(Sno)from SCgroup by Cno ;b)查询选修了3们以以上的课程的学生的学号select Snofrom SCcroup by Snohaving count(*) 3 ;注：where子句与having子句短语的区别在于作用对象不同。Where子句作用于基本表或视图，从中选择满足条件的元组；having子句作用与组，从中选
23、择满足条件的组。十四、 数据查询-连接查询若一个查询同时设计两个以上的表，则称之为连接查询。(1) 等值与非等值连接查询格式如下：. . 其中主要的比较运算符有：= , ,= ,= ,!=(或) 等.此外连接谓词还可以有如下的形式：. between . and . 当连接运算符为=时，称为等值连接，否则称为非等值连接。注：当属性列在查询的所有表中是唯一的时候则可以去掉前面的表名，否则必须加上表名。例：查询每个学生及其选修课程的情况.Select Student.* ,SC.*From Student ,SCWhere Student.Sno = SC.Sno ; (2) 自身连接例：查询每一
24、门课程的间接先修课(即先修课的先修课)分析：此时为Course表的自身连接，故要为Course表去两个别名，一个是first,一个是second。Select first.Cno ,second.CnoFrom Course first ,Course secondWhere first.Cpno = second.Cno ;(3)外连接在上例中，没有显示200215123和200215125两个学生的信息，原因在于他们没有选课。有时想以Student表为主体列出每个学生的基本情况及其选课情况，则需要使用外连接。用外连接做上面的例题：Select Student.Sno ,Sname ,Sse
25、x ,Sage ,Sdept ,Cno ,CgradeFrom Student LEFT JOIN SC ON (Student.Sno = Sc.Sno) ;/*也可以用USING来去掉上面结果中的重复值：From Student LEFT JOIN SC USING(Sno) ; */注：做链接列出左边关系(如本例)中的所有元组，右外连接列出右边关系中的所有元组。(4)复合条件连接在上面的例子中，where子句中只有一个条件，即连接谓词。Where也可以有多个连接条件，称为符合条件连接。例：查询选修2号课程且成绩在90分以上的所有学生Select Student.Sno ,SnameFro
26、m Student ,SCWhere Student.Sno = SC.Sno AND/*连接谓词*/SC.Cno = 2 AND SC.Grade 90 ; /*其它限制条件*/例：查询每个学生的学号、姓名、选修课程名及成绩Select Student.Sno ,Sname ,Cname ,GradeFrom Student ,SC ,CourseWhere Student.Sno = SC.Sno and SC.Cno = Course.Cno ;十五、 数据查询-集合查询集合查询操作主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT。注：多个集合操作的个查询结果的列数
27、必须相同，对应项的数据类型也必须相同。(1) 并操作UNION使用UNION将多个查询结果并起来时，系统会自动的去掉重复元组；如果要保留重复元组，则可以使用UNION ALL操作符。例：查询计算机科学系的学生 及年龄不大于19岁的学生.Select *From StudentWhere Sdept = CS UnionSelect *From StudentWhere Sage = 19 ;例：查询选修了1号课程或者2号课程的学生Select SnoFrom SCWhere Cno = 1UnionSelect SnoFrom SCWhere Cno = 2 ;(2)交操作(INTERSECT
28、)例：查询计算机科学系的学生与年龄不大于19岁的学生的交集Select *From StudentWhere Sdept = CSIntersectSelect *From StudentWhere Sage = 19 ;等价于Select *From StudentWhere Sdept = CS and Sage = 19 ;(3)差操作(EXCEPT)例：查询计算机科学系的学生与年龄不大于19岁的学生的差集Select *From StudentWhere Sdept = CSExceptSelect *From StudentWhere Sage = (select AVG(Grade
29、) /*某个学生的平均成绩*/From SC yWhere y.Sno = x.Sno) ;(3) 带有ANY(SOME)或ALL谓词的子查询子查询返回单值可以用比较运算符，但是但会多值要用NAY或ALL谓词修饰。而使用ANY或ALL谓词时则必须同时使用比较运算符。例：查询其他系中 比计算机科学系某一学生年龄小的学生的姓名及年龄Select Sname ,SageFrom StudentWhere Sage any (select SageFrom StudentWhere Sdept = CS) And Sdept CS ;例：查询其他系中 比计算机科学系所有学生年龄都小的学生的姓名及年龄S
30、elect Sname ,SageFrom StudentWhere Sage all (select SageFrom StudentWhere Sdept = CS) And Sdept CS ;(4) 带有EXITS或NOT EXITS谓词的子查询例：查询所有选修了1号课程的学生的姓名Select SnameFrom StudentWhere not exits(select *From StudentWhere Sno = Student.Sno and Cno = 1) ;例：查询选修了全部课程的学生的姓名Select SnameFrom Student Where not exit
31、s(select *From CourseWhere not exits(select *From SCWhere Sno = Student.Sno andCno = Course.Cno) ;例：查询了至少选修了学生200215122选修的全部课程的学生的号码Select distinct SnoFrom SC SCXWhere not exits(select *From SC SCYWhere SCY.Sno = 200215122 andnot exits(select *from SC SCZwhere SCZ.Sno = SCX.Sno andSCZ.Cno = SCY.Cno)
32、 ;十七、 视图1、基本介绍1) 视图是从一个或者几个基本表导出的表，它是一个虚表。数据库中只是存放视图的定义，而不存放视图对应的数据;2) RDBMS在执行CREATE VIEW语句的结果只是把视图的定义存入数据字典，并不执行其中的SELECT语句。只在对视图进行查询时，才按视图的定义从表中将数据查出;3) 视图不仅可以建立在一个或者多个表上，还可以建立在一个或者已定义好的视图上，或者建立在基本表与视图上;4) 对视图的更新，最终要转换为对基本表的更新;5) 视图属于数据库外模式范畴.2、建立视图格式如下：Create view ( ,)As with check option ;注：子查询
33、通常不允许含有order by 子句和distinct子句。例如：建立信息系学生的视图Create view IS_Studentasselect Sno ,Sname ,Sagefrom Student where Sdept = IS ;例如：建立信息系学生的视图，并要求进行修改和插入操作时任需要保证视图只有信息系的学生。Create view IS_Studentasselect Sno ,Sname ,Sagefrom Student where Sdept = IS ;with check option.例：建立信息系选修了1号课程的学生的视图。Create view IS_S1(S
34、no ,Sname ,Grade)AsSelect from Student.Sno ,Sname ,GradeFrom Student ,SCWhere Sdept = IS andStudetn.Sno = SC.Sno andSC.Cno = 1 ;3、删除视图格式：Drop view cascade ;例：删除视图BT_S Drop view BT_S ;4、查询视图例：查询选修了1号课程的信息系的学生Select IS_Student.Sno ,SnameFrom IS_Student ,SCWhere IS_Student.Sno = SC.Sno andSC.Cno = 1 ;例
35、：在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩Select *From S_GWhere Gave = 90 ;5、更新视图例：将信息系学生视图IS_Student 中学号为200215122的学生的姓名改为“刘辰”。Update IS_StudentSet Sname = 刘辰Where Sno = 200215122 ;例：删除信息系学生视图IS_Student中学号为200215129的记录Delete From IS_StudentWhere Sno = 200215129 ;6、视图的作用1) 视图能够简化用户的操作2) 视图使用户能以多种角度看待同一数据3) 视图对重
36、构数据库提供了一定程度的逻辑独立性4) 视图能够对机密数据提供安全保护5) 适当的利用视图可以更清晰的表达查询十八、 索引的建立与删除1、建立索引格式：Create unique cluster index On ( , ) ;2、说明1) 索引可以建立在表的以列或者多列上，各列名之间用逗号隔开，每个列名后面还可以用指定索引值的排列次序，可选asc(升序)或desc(降序)，缺省为asc.2) Unique表名此索引的每一个索引值只对应唯一的数据记录;例：Create unique index Stusno on Student(Sno) ;Create unique index Scno o
37、n SC(Sno ASC ,Cno DESC) ;Create cluster index Stusname on Student(Sname) ;3) 建立索引是为了减少查询操作的时间;4) 索引是关系数据库的内部实现技术，属于内模式的范畴.3、删除索引格式如下：Drop index ;例：删除Student表的Stusname索引Drop index Stusname ;十九、 授权与回收1、 GRANT(授权)一般格式：Grant ,On , To ,with grant option ;说明：1) 发出给grant语句的可以是DBA，也可以是该数据库对象创建者，也可以是已经拥有该权限的
38、用户。2) 接受权限的用户可以是一个或多个用户，也可以是PUBLIC，即全体用户。3) 如果指定了with grant option子句，则获得某种权限的用户还可以把这种权限再授予其他的用户；如果没有指定grant with option子句，则获得某种权限的用户只能使用该权限，不能传播该权限。4) SQL允许具有with grant option的用户把相应权限或其子集传递授予其他的用户，但不允许循环授权，即被授权者不能再将权限授回给授权者或其祖先。【例1】将查询Student表的权限授给用户U1Grant selectOn table StudentTo U1 注：在SQL2008中，应将
39、对象名去掉，即去掉此处的table.【例2】把对Student表和Course表的全部操作权限授给用户U2和U3Grant all privilecesOn table Student ,CourseTo U2 ,U3 ;注：在SQL2008中一次只能授权一张表【例3】把对表SC的查询权限授给所有用户Grant selectOn table SCTo public ;【例4】把查询student表和修改学生学号的权限授给用户U4Grant update(Sno) ,selectOn table StudentTo U4 ;注：1) 在SQL2008中，本例中的Sno不能是主码.2) 对属性列的
40、授权时必须明确指出属性列名【例5】把对表SC的INSERT权限授给U5用户，并允许将此权限再授予其他用户.Grant insertOn table SCTo U5With grant option ;注：此时用户U5不仅拥有了对SC表的INSERT权限，还可以传播此权限.【例6】U5将此权限传给用户U6Grant insertOn table SCTo U6With grant option ;【例7】U6还可以将此权限传给U7Grant insertOn table SCTo U7 ;注：u6未给U7传播的权限，因此U7不能再传播此权限.2、 REVOKE(收回权限)一般格式如下：Revoke ,On , 对象名