4 SQL语言复习

1数据库语言SQL结构化查询语言SQL(StructuredQueryLanguage)SQL语言复习2教学要求掌握SQL语句的书写方法熟练使用SQL语句实现建表，修改表，删除表，向表中插入，删除，修改，查询等操作3重点难点使用SQL语句查询表格中的数据4概述SQL的发展1974年，由Boyce和Chamberlin提出1975~1979，IBMSanJoseResearchLab的关系数据库管理系统原型SystemR实施了这种语言SQL-86是第一个SQL标准SQL-89、SQL-92(SQL2)、SQL-99(SQL3)5概述现状大部分DBMS产品都支持SQL，成为操作数据库的标准语言有方言，支持程度不同6交互式SQL一般DBMS都提供联机交互工具用户可直接键入SQL命令对数据库进行操作由DBMS来进行解释SQL的形式7嵌入式SQL能将SQL语句嵌入到高级语言（宿主语言）使应用程序充分利用SQL访问数据库的能力、宿主语言的过程处理能力一般需要预编译，将嵌入的SQL语句转化为宿主语言编译器能处理的语句SQL的形式8SQL语言主要组成部分数据定义语言（DDL，DataDefinitionLanguage）数据操纵语言（DML，DataManipulationLanguage）数据控制语言（DCL，DataControlLanguage）9创建用户使用Createuser用户名identifiedby密码[defaulttablespace缺省表空间][temporarytablespace临时表空间];给用户赋连接权Grantconnectto用户名;DDL——创建用户CreateUser10具有CONNECT角色的用户可以登录数据库，执行数据查询和操纵。即可以执行ALTERTABLE，CREATEVIEW，CREATEINDEX，DROPTABLE，DROPVIEW，DROPINDEX，GRANT，REVOKE，INSERT，SELECT，UPDATE，DELETE，AUDIT，NOAUDIT等操作用户权限11如果只是给用户赋予建表权，如：grantcreatetabletouser1这样用户还是不能成功建表，提示错误是对表空间没有操作权。grantcreatetabletou1是指u1拥有了建表的权限,oracle也知道了他有默认的表空间,但默认的表空间没有给他分配使用权利（使用空间）,所以出错.用户权限——建表权12因此要先使用quota（配额）给用户赋予表空间使用权alteruseru1quota200Mon表空间1;--给他200M空间alteruseru1quotaunlimitedon表空间1;--让他随意使用表空间1;收回权限：Revokecreatetablefromu1;用户权限——建表权13RESOURCE角色可以创建表，即执行CREATETABLE操作。创建表的用户将拥有对该表的所有权限。Createtable权限和createanytable权限是有区别的，后者可以建立其他用户下的表格。如用U1登录，可以这样建表Createtablesystem.t(t1int);用户权限——用角色赋建表权14建立其他用户（如U1）的表格，表格的所有权应归属于表格的拥有者（U1），而不是创建者。即他不能对该表格进行权限外操作。若想将权限赋予所有用户，可以使用Public角色。如：Grantselectonsctopublic;执行后，所有用户将拥有SC表的查询权。用户权限——用角色赋建表权151.定义基本表(1)定义基本表的语句格式：CREATETABLE表名(列定义[{,列定义,表约束}])表名：列定义：列名、列数据类型、长度、是否允许空值等。定义完整性约束：列约束和表约束[CONSTRAINT约束名]约束定义DDL——建立表结构Create16CreateTableStudent(snochar(10)primarykey,snamevarchar2(20)notnull,sagesmallint,ssexchar(2),sdeptvarchar2(20))定义基本表17CreateTableCourse(cnochar(10)primarykey,cnamevarchar(20),creditsmallint,snumbersmallint)--最大选课人数CreateTableSC(snochar(10)notnull,cnochar(10)notnull,gradesmallint,primarykey(sno,cno))定义基本表18Oracle中复制表Createtabletasselect*fromstudent;只复制表结构：Createtablet1asselect*fromstudentwhere1=2;SQLServer中复制表Select*intotfromstudent;复制基本表19Oracle中查看表达式的值Oracle中可以通过Dual表查看常量或表达式的值：SelectsysdatefromDual;Selectdbms_random.value(1,10)fromdual;--产生1到10的随机数SQLServer中查看表达式的值Selectgetdate();Oracle中查看表达式的值20用SQL删除关系（表）将整个关系模式（表结构）彻底删除表中的数据也将被删除DropTablerDropTablestudent;一旦一个表被删除，数据将不能被恢复！DDL——删除表结构Drop21用SQL删除关系（表）将整个关系模式（表结构）彻底删除表中的数据也将被删除与表格相关的外码约束也一起删除DropTablerCascadeConstraints;DropTablestudentCascadeConstraints;一旦一个表被删除，数据将不能被恢复！并且相关外码也被删除DDL——删除表结构Drop22增加表中的属性向已经存在的表中添加属性allownull（新添加的属性要允许为空）已有的元组中该属性的值被置为NullAlterTabletAddADAlterTablestudentAddphonechar(16);DDL——修改表结构Alter23修改表中的某属性(某列)修改属性及相应的数据AlterTabletmodify(AD)AlterTableStudentmodifysagechar(3)DDL——修改表结构Alter24删除表中的某属性(某列)去除属性及相应的数据AlterTabletDropcolumnAAlterTableStudentDropcolumnsageDDL——修改表结构Alter25练习——DDL：1、创建Student，Course，SC三张表（暂时不建立外码和唯一码Unique）。2、为Student表添加身份证号（整数）列，查看表结构。3、将Student表中的“身份证号”列的类型改为20个长度的变长字符串，查看表结构。4、删除Student表的“身份证号”列。5、删除这三张表。26DML——数据库的修改数据库修改包括插入数据修改数据删除数据27数据添加用SQL的插入语句，向数据库表中添加数据按关系模式的属性顺序InsertIntoStudentValues(‘01001’,‘张三’,27,‘M’,‘CS’)按指定的属性顺序，也可以只添加部分属性（非Null属性为必需）InsertIntoStudent(sno,sname,sage)Values(‘01002’,‘李四’,26)DML——向表中添加数据(Insert)28–只能对整个元组操作，不能只删除某些属性上的值–只能对一个关系起作用，若要从多个关系中删除元组，则必须对每个关系分别执行删除命令DeleteFromrWhereP–从关系r中删除满足P的元组，只是删除数据，而不是删除定义（表结构）DML——数据删除（Delete）29删除单个元组例：删除学号为01001的学生的基本信息DeleteFromStudentWheresno=‘01001’删除多个元组例：删除选课而未参加考试的学生的选课信息DeleteFromSCWheregradeisnull删除整个关系中的所有数据例：删除所有学生的选课信息DeleteFromSC30改变符合条件的某个（某些）元组的属性值例：将01001学生转入MA系UpdateStudentSetsdept=‘MA’,sage=sage+1Wheresno=‘01001’例：所有学生年龄加1UpdateStudentSetsage=sage+1DML——数据更新（Update）31练习——DML：1、为Student（10行以上），Course（8行以上），SC（30行以上）表添加记录。2、为Student表添加列“班级号”（10个长度定长字符串）。3、为学生填写班级号（随机产生1-3之间的数）4、将每个同学的班级号前面/后面加上“T”。5、删除班级号前面/后面的“T”。6、删除班级号为空的学生。7、删除成绩不及格的学生的选课信息。32数据查询是数据库应用的核心功能基本结构Selectcol1,col2,...,colnFromr1,r2,...,rmWherePSelectSnamefromStudentDML——数据查询（Select）33Select子句——*与属性列表星号*表示所有属性星号*：按关系模式中属性的顺序排列显式列出属性名：按用户顺序排列Select*FromStudent34Select子句——更名为结果集中的某个属性改名使结果集更具可读性Selectsnoas‘学号’，cnoas课程号，grade成绩FromSCSelectsno，sname，to_char(sysdate,’yyyy’)-sageasbirthday_YearFromStudent35Where子句查询满足指定条件的元组可以通过Where子句来实现使where子句中的逻辑表达式返回True值的元组，是符合要求的元组，将被选择出来36Where子句——运算符比较：、=、、=、=、等确定范围：BetweenAandB、NotBetweenAandB确定集合：IN、NOTIN字符匹配：LIKE，NOTLIKE空值：ISNULL、ISNOTNULL多重条件：AND、OR、NOT37Where子句——Like字符匹配：Like、NotLike通配符%——匹配任意字符串_——匹配任意一个字符大小写敏感38Where子句——Like例：列出姓张的学生的学号、姓名。Selectsno，snameFromStudentWheresnameLIKE‘张%’例：列出张姓且单名的学生的学号、姓名。Selectsno，snameFromStudentWheresnameLIKE‘张_’39From子句列出将被查询的关系（表）例：列出所有学生的学号、姓名、课号、成绩。SelectSudent.sno，sname，SC.cno，gradeFromstudent，SCWhereStudent.sno=SC.sno40From子句——元组变量为From子句中的关系定义元组变量方便关系名的引用例：列出所有学生的学号、姓名、课号、成绩。SelectT.sno，sname，cno，gradeFromStudentT，SCS--不能使用as关键字WhereT.sno=S.sno41OrderBy子句指定结果集中元组的排列次序耗时ASC升序（缺省）、DESC降序例：列出CS系中的男生的学号、姓名、年龄，并按年龄进行排列（升序）Selectsno,sname,sageFromStudentWheresdept=‘CS’andssex=‘m’Orde