数据库sql基础知识大全

数据库原理主讲：赵海霞河南科技大学电信学院第三章关系数据库语言SQL概述SQL的数据定义SQL的数据操纵SQL的视图定义SQL的数据控制嵌入式SQL第三章关系数据库语言SQL概述SQLStructuredQueryLanguage是1974年由Boyde和Chamberlin提出的1974年IBM的SystemR1979年Oracle1982年IBM的DB21984年Sybase1986年10月ANSI公布SQL-86标准1989年ISO公布SQL-89标准120页1992年公布SQL-92标准622页1999年公布SQL-99标准1700页2003年公布SQL-2003标准3600页第三章关系数据库语言SQL3.1概述SQL语言的特点综合统一集DDL、DML、DCL为一体实体和联系都是关系,因此每种操作只需一种操作符高度非过程化面向集合的操作方式以同一种语法结构提供两种使用方式交互式和嵌入式语言简捷,易学易用数据定义CREATE、DROP、ALTER数据查询SELECT数据更新INSERT、UPDATE、DELETE数据控制GRANT、REVOKE第三章关系数据库语言SQL3.1概述SQL语言的特点支持三级模式结构视图外模式基本 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 的集合模式存储文件和索引内模式第三章关系数据库语言SQL3.1概述SQL数据库体系结构用户2用户3用户4视图V1视图V2基本表B1基本表B2基本表B3基本表B4存储文件S1存储文件S2存储文件S3存储文件S4用户1SQL用户视图基本表存储文件第三章关系数据库语言SQL3.1概述SQL的组成数据定义语言TheDataDefinitionLanguage,DDL数据操作语言TheDataManipulationLanguage,DML嵌入式和动态SQL事务管理安全性管理触发器和高级完整性约束客户服务器执行和远程数据库存取高级特性第三章关系数据库语言SQL3.1概述SQL的数据定义定义和修改基本表定义模式中的关系：CREATETABLEDROPTABLEALTERTABLE定义视图定义外模式：CREATEVIEWDROPVIEW定义索引定义内模式：CREATEINDEXDROPINDEX第三章关系数据库语言SQL3.2SQL的数据定义基本表的定义基本格式CREATETABLE<表名><列名><数据类型><列级完整性约束条件>,<列名><数据类型><列级完整性约束条件>…,<表级完整性约束条件>；<表名>：所要定义的基本表的名字<列名>：组成该表的各个属性列<列级完整性约束条件>：涉及相应属性列的完整性约束条件<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件第三章关系数据库语言SQL3.2SQL的数据定义例1建立一个“学生”表Student,它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成,其中学号不能为空,值是唯一的,并且姓名取值也唯一,CREATETABLEStudentSnoCHAR5NOTNULLUNIQUE,SnameCHAR20UNIQUE,SsexCHAR1,SageINT,SdeptCHAR15；基本表的定义第三章关系数据库语言SQL3.2SQL的数据定义基本表的定义说明：SQL支持空值的概念,允许空值的列未输入数据时系统自动置为空值,SQL支持的数据类型随系统不同而有所差异,但一般都有：全字长整型、半字长整型、定点实型、浮点实型、CHARn、VARCHARn、TEXT、DATE第三章关系数据库语言SQL3.2SQL的数据定义基本表的定义常用完整性约束主码约束：PRIMARYKEY唯一性约束：UNIQUE非空值约束：NOTNULL参照完整性约束FOREIGNKEYcheck约束思考：PRIMARYKEY与UNIQUE的区别Primarykey在建立的时候会默认地建立此field的索引,且此primarykey可以作为作为另外的表的foreignkey；再者primarykey跟unique的区别是Primarykey一定是notnull,而unique则没有此限制第三章关系数据库语言SQL3.2SQL的数据定义SQLServer2005中的数据类型精确数字bigintdecimalintnumericsmallintmoneytinyintsmallmoneybit 近似数字floatreal日期和时间datetimesmalldatetime字符串chartextvarchar Unicode 字符串ncharntextnvarchar 二进制字符串binaryimagevarbinary 其他数据类型cursortimestampsql_variantuniqueidentifiertablexml例2建立一个“课程”表C,它由课程号Cno,课程名称Cname,教师Teacher组成,其中Cno为主码,CREATETABLECCnoCHAR5,CnameCHAR10,TeacherCHAR10,PrimarykeyCno;或像 书 关于书的成语关于读书的排比句社区图书漂流公约怎么写关于读书的小报汉书pdf 上写的将Primarykey直接定义在属性列后,基本表的定义例3建立一个“学生选课”表SC,它由学号Sno、课程号Cno,修课成绩Grade组成,其中Sno,Cno为主码,CREATETABLESCSnoCHAR5,CnoCHAR3,Gradeint,PrimarykeySno,Cno,FOREIGNKEYSnoREFERENCESSSno,FOREIGNKEYCnoREFERENCESCCno;第三章关系数据库语言SQL3.2SQL的数据定义基本数据类型1数值型DB2SMALLINT半字长二进制整数INTEGER全字长二进制整数DECIMALP,q或者DECp,q压缩十进制数,共p位,其中小数点后q位FLOAT双字长浮点数2字符串型CHARTERn或CHARnVARCHARn3时间型DATETIME4位串型BITn基本表的删除基本格式DROPTABLE表名；示例DROPTABLES；第三章关系数据库语言SQL3.2SQL的数据定义基本表的修改基本格式ALTERTABLE<表名>ADD<新列名><数据类型>完整性约束DROP<完整性约束名>ALTERCOLUMN<列名><数据类型>；<表名>：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的完整性约束条件ALTERCOLUMN子句：用于修改列名和数据类型第三章关系数据库语言SQL3.2SQL的数据定义基本表的修改增加列基本格式ALTERTABLE表名ADD列名类型；示例ALTERTABLESADDADDRESSVARCHAR30修改列基本格式ALTERTABLE表名ALTERCOLUMN列名类型；ALTERTABLESALTERCOLUMNSASMALLINT；删除列基本格式ALTERTABLE表名DROP列名；ALTERTABLESDROPUNIQUESn；第三章关系数据库语言SQL3.2SQL的数据定义例子修改表ALTERTABLEstudentADDsnoCHAR5NULL--向student表中添加一列snoALTERTABLEstudent--修改sno的定义保障此列不为空MODIFYsnoCHAR5NOTNULLALTERTABLEstudent--向student表添加主键约束ADDPRIMARYKEYsnoALTERTABLEstudent--从student表删除列sexDROPCOLUMNsex例子修改表--向study表中添加外键约束ALTERTABLEstudyADDCONSTRAINTfkcnoFOREIGNKEYcnoREFERENCEScoursecnoONDELETECASCADEONUPDATECASCADE--向study表中添加外键约束ALTERTABLEstudyADDCONSTRAINTfksnoFOREIGNKEYsnoREFERENCESstudentsnoONDELETECASCADEONUPDATECASCADE视图的定义和修改基本操作CREATEVIEWDROPVIEW后面详细讨论第三章关系数据库语言SQL3.2SQL的数据定义索引的建立和删除建立索引是加快查询速度的有效手段建立索引DBA或表的属主即建立表的人根据需要建立有些DBMS自动建立以下列上的索引PRIMARYKEYUNIQUE维护索引DBMS自动完成 使用索引DBMS自动选择是否使用索引以及使用哪些索引第三章关系数据库语言SQL3.2SQL的数据定义索引的建立和删除索引的结构RDBMS中,索引一般采用B+树或HASH索引实现,具体由RDBMS决定,B+树具有动态平衡的优点HASH索引查找速度快,索引是关系数据库的内部实现技术,属内模式范畴,用户创建索引时,可创建唯一索引、非唯一索引或聚簇索引,在SQL86和SQL89标准中,基本表没有关键码概念,可以用索引机制来弥补,索引属于物理存储的路径概念,而不是逻辑的概念,在定义基本表时,还要定义索引,就把数据库的物理结构和逻辑结构混在一块了,因此在SQL2中引入了主码主键的概念,用户在创建基本表时用主码子句Primarykey直接定义主码,但至今大多数DBMS仍使用索引机制,有索引创建和撤销语句,其功能仅限于查询时起作用,索引的建立索引建立的基本格式ASCDESC[，列名ASCDESC]…）；一个索引项对应一个记录升序或降序CREATE[UNIQUE][CLUSTER]INDEX索引名ON表名（列名第三章关系数据库语言SQL3.2SQL的数据定义聚簇索引Unique索引表明此索引的每个索引值只对应唯一的数据记录Cluster索引又称聚簇索引,必须维护表中行的物理存储顺序和索引顺序一致,经常进行更新操作的表不宜建立聚簇索引,索引的建立例、为学生-课程数据库中的S,C,SC三个表建立索引,其中S表按学号升序建唯一索引,C表按课程号升序建唯一索引,SC表按学号升序和课程号降序建唯一索引,CREATEUNIQUEINDEXSsnoONSSno；CREATEUNIQUEINDEXCcnoONCCno；CREATEUNIQUEINDEXSCnoONSCSnoASC,CnoDESC；第三章关系数据库语言SQL3.2SQL的数据定义索引的建立唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后,插入新记录时DBMS会自动检查新记录在该列上是否取了重复值,这相当于增加了一个UNIQUE约束第三章关系数据库语言SQL3.2SQL的数据定义索引的建立聚簇索引建立聚簇索引后,基表中数据也需要按指定的聚簇属性值的升序或降序存放,也即聚簇索引的索引项顺序与表中记录的物理顺序一致例：CREATECLUSTERINDEXSsnameONSSN；在S表的SN姓名列上建立一个聚簇索引,而且S表中的记录将按照SN值的升序存放第三章关系数据库语言SQL3.2SQL的数据定义索引的建立在一个基本表上最多只能建立一个聚簇索引聚簇索引的用途：对于某些类型的查询,可以提高查询效率聚簇索引的适用范围很少对基表进行增删操作很少对其中的变长列进行修改操作第三章关系数据库语言SQL3.2SQL的数据定义索引的删除索引删除的基本格式DROPINDEX索引名删除索引时,系统会从数据字典中删去有关该索引的描述,第三章关系数据库语言SQL3.2SQL的数据定义SQL的数据操纵SQL的数据查询检索SELECTSQL的数据更新增、删、改INSERT,DELETE,UPDATE第三章关系数据库语言SQL3.3SQL的数据操纵SQL的数据查询一般格式SELECT目标列FROM基本表或视图WHERE条件表达式GROUPBY列名1HAVING内部函数表达式ORDERBY列名2ASCDESC相当于投影相当于选择或连接被查询的关系对查询结果排序分组统计第三章关系数据库语言SQL3.3SQL的数据操纵SQL的数据查询常用格式SELECT-FROM-WHERE句型SELECTA1,A2,,AnFROMR1,R2,,RmWHEREF其中,条件表达式F可使用下列操作符：算术比较运算符,=,,=,,<>逻辑运算符AND,OR,NOT集合运算符UNION,INTERSECT,EXCEPT集合成员资格运算符IN,NOTIN谓词EXISTS,ALL,SOME,UNIQUE聚合函数AVG,MIN,MAX,SUM,COUNT嵌套的SELECT语句第三章关系数据库语言SQL3.3SQL的数据操纵示例数据库学生-课程数据库学生表：StudentSno,Sname,Ssex,Sage,Sdept课程表：CourseCno,Cname,Cpno,Ccredit学生选课表：SCSno,Cno,Grade第三章关系数据库语言SQL3.3SQL的数据操纵一、单表查询查询仅涉及一个表,是一种最简单的查询操作选择表中的若干列选择表中的若干元组对查询结果排序使用集函数对查询结果分组第三章关系数据库语言SQL3.3SQL的数据操纵查询指定列例1查询全体学生的学号与姓名,SELECTSno,SnameFROMStudent；  例2查询全体学生的姓名、学号、所在系,SELECTSname,Sno,SdeptFROMStudent；第三章关系数据库语言SQL3.3SQL的数据操纵查询全部列例3查询全体学生的详细记录,SELECTSno,Sname,Ssex,Sage,SdeptFROMStudent；或SELECT*FROMStudent；第三章关系数据库语言SQL3.3SQL的数据操纵查询经过计算的值例4查全体学生的姓名及其出生年份,SELECTSname,2009-SageFROMStudent； 输出结果：Sname2009-Sage----------------------李勇1976刘晨1977王名1978张立1978第三章关系数据库语言SQL3.3SQL的数据操纵查询经过计算的值例5查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名,SELECTSname,'YearofBirth:',2009-Sage,ISLOWERSdeptFROMStudent；第三章关系数据库语言SQL3.3SQL的数据操纵查询经过计算的值输出结果：Sname'YearofBirth:'2009-SageISLOWERSdept----------------------------------------------李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is第三章关系数据库语言SQL3.3SQL的数据操纵使用列别名改变查询结果的列标题SELECTSnameNAME,'YearofBirth:’BIRTH,2009-SageBIRTHDAY,ISLOWERSdeptDEPARTMENTFROMStudent；输出结果：NAMEBIRTHBIRTHDAYDEPARTMENT------------------------------------------------------李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is第三章关系数据库语言SQL3.3SQL的数据操纵选择表中的若干元组消除取值重复的行查询满足条件的元组第三章关系数据库语言SQL3.3SQL的数据操纵1.消除取值重复的行在SELECT子句中使用DISTINCT短语假设SC表中有下列数据SnoCnoGrade---------------------9500119295001285950013889500229095002380第三章关系数据库语言SQL3.3SQL的数据操纵ALL与DISTINCT例6查询选修了课程的学生学号,1SELECTSnoFROMSC;或默认ALLSELECTALLSnoFROMSC; 结果：Sno-------95001950019500195002950022SELECTDISTINCTSnoFROMSC; 结果：Sno-------9500195002第三章关系数据库语言SQL3.3SQL的数据操纵ALL与DISTINCT注意DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误写法SELECTDISTINCTCno,DISTINCTGradeFROMSC;正确写法SELECTDISTINCTCno,GradeFROMSC; 第三章关系数据库语言SQL3.3SQL的数据操纵2.查询满足条件的元组比较大小确定范围确定集合字符串匹配涉及空值的查询多重条件查询第三章关系数据库语言SQL3.3SQL的数据操纵比较大小在WHERE子句的<比较条件>中使用比较运算符=,>,<,>=,<=,=或<>,>,<,逻辑运算符NOT+比较运算符例8查询所有年龄在20岁以下的学生姓名及其年龄,SELECTSname,SageFROMStudentWHERESage<20；或SELECTSname,SageFROMStudentWHERENOTSage>=20；第三章关系数据库语言SQL3.3SQL的数据操纵确定范围使用谓词BETWEEN…AND…NOTBETWEEN…AND…例10查询年龄在20~23岁包括20岁和23岁之间的学生的姓名、系别和年龄,SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23；相当于若干´AND´的缩写第三章关系数据库语言SQL3.3SQL的数据操纵确定范围例11查询年龄不在20~23岁之间的学生姓名、系别和年龄,SELECTSname,Sdept,SageFROMStudentWHERESageNOTBETWEEN20AND23；第三章关系数据库语言SQL3.3SQL的数据操纵确定集合使用谓词IN<值表>,NOTIN<值表><值表>：用逗号分隔的一组取值例12查询信息系IS、数学系MA和计算机科学系CS学生的姓名和性别,SELECTSname,SsexFROMStudentWHERESdeptIN'IS','MA','CS';相当与若干´OR´的缩写第三章关系数据库语言SQL3.3SQL的数据操纵字符串匹配NOTLIKE‘<匹配串>’ESCAPE‘<换码字符>’DB2中,下划线´_´表示匹配任何单个字符百分号´%´表示匹配任何字符串当用户要查询的字符串本身含有%或_时,要使用ESCAPE'<换码字符>'短语对通配符进行转义第三章关系数据库语言SQL3.3SQL的数据操纵字符串匹配匹配模板为固定字符串例14查询学号为95001的学生的详细情况,SELECT*FROMStudentWHERESnoLIKE'95001'；等价于：SELECT*FROMStudentWHERESno='95001'；第三章关系数据库语言SQL3.3SQL的数据操纵字符串匹配匹配模板为含通配符的字符串例15查询所有不姓刘学生的姓名、学号和性别,SELECTSname,Sno,SsexFROMStudentWHERESnameNOTLIKE‘刘%’；第三章关系数据库语言SQL3.3SQL的数据操纵匹配模板为含通配符的字符串续例16查询姓"欧阳"且全名为三个汉字的学生的姓名,SELECTSnameFROMStudentWHERESnameLIKE'欧阳__'；第三章关系数据库语言SQL3.3SQL的数据操纵字符串匹配匹配模板为含通配符的字符串续例17查询名字中第2个字为"阳"字的学生的姓名和学号,SELECTSname,SnoFROMStudentWHERESnameLIKE'__阳%'；第三章关系数据库语言SQL3.3SQL的数据操纵字符串匹配使用换码字符将通配符转义为普通字符例19查询DB_Design课程的课程号和学分,SELECTCno,CcreditFROMCourseWHERECnameLIKE'DB\_Design'ESCAPE'\'第三章关系数据库语言SQL3.3SQL的数据操纵涉及空值的查询使用谓词ISNULL或ISNOTNULL“ISNULL”不能用“=NULL”代替例21某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩,查询缺少成绩的学生的学号和相应的课程号,SELECTSno,CnoFROMSCWHEREGradeISNULL；第三章关系数据库语言SQL3.3SQL的数据操纵涉及空值的查询例22查所有有成绩的学生学号和课程号,SELECTSno,CnoFROMSCWHEREGradeISNOTNULL；第三章关系数据库语言SQL3.3SQL的数据操纵多重条件查询用逻辑运算符AND和OR来联结多个查询条件AND的优先级高于OR可以用括号改变优先级可用来实现多种其他谓词NOTINNOTBETWEEN…AND…第三章关系数据库语言SQL3.3SQL的数据操纵多重条件查询例23查询计算机系年龄在20岁以下的学生姓名,SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；第三章关系数据库语言SQL3.3SQL的数据操纵对查询结果排序使用ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示第三章关系数据库语言SQL3.3SQL的数据操纵对查询结果排序例24查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列,SELECTSno,GradeFROMSCWHERECno='3'ORDERBYGradeDESC；第三章关系数据库语言SQL3.3SQL的数据操纵查询结果SnoGrade--------------9501095024950079295003829501082950097595014619500255第三章关系数据库语言SQL3.3SQL的数据操纵使用集函数COUNT对一列中的值的个数记数COUNT*计算记录个数SUM对一列求和AVG对一列求平均值MAX对一列求最大值MIN对一列求最小值第三章关系数据库语言SQL3.3SQL的数据操纵使用集函数SELECTCOUNTDISTINCTSnoFROMSC；例25求选修了课程的学生人数,重复的只记一个第三章关系数据库语言SQL3.3SQL的数据操纵对查询结果分组使用GROUPBY子句分组细化集函数的作用对象未对查询结果分组,集函数将作用于整个查询结果对查询结果分组后,集函数将分别作用于每个组第三章关系数据库语言SQL3.3SQL的数据操纵对查询结果分组例30求各个课程号及相应的选课人数,SELECTCno,COUNTSnoFROMSCGROUPBYCno； 结果CnoCOUNTSno122234344433548将表按列的值分组,列的值相同的分在一组,GROUPBY常和库函数一起使用,用于分组统计,第三章关系数据库语言SQL3.3SQL的数据操纵对查询结果分组GROUPBY子句的作用对象是查询的中间结果表分组方法：按指定的一列或多列值分组,值相等的为一组使用GROUPBY子句后,SELECT子句的列名列表中只能出现分组属性和集函数第三章关系数据库语言SQL3.3SQL的数据操纵使用HAVING短语例31查询选修了3门以上课程的学生学号,SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT*>3；第三章关系数据库语言SQL3.3SQL的数据操纵使用HAVING短语例32查询有3门以上课程是90分以上的学生的学号及90分以上的课程数SELECTSno,COUNT*FROMSCWHEREGrade>=90GROUPBYSnoHAVINGCOUNT*>=3;HAVING是选择分组的条件,且必须和GROUPBY一起使用WHERE是选择记录的条件；第三章关系数据库语言SQL3.3SQL的数据操纵使用HAVING短语只有满足HAVING短语指定条件的组才输出HAVING短语与WHERE子句的区别：作用对象不同WHERE子句作用于基表或视图,从中选择满足条件的元组HAVING短语作用于组,从中选择满足条件的组第三章关系数据库语言SQL3.3SQL的数据操纵使用聚合函数对数据进行汇总和统计聚合函数常见的几种用法：SUMALL|DISTINCTexpression返回表达式中所有值的和,或只返回DISTINCT值,SUM只能用于数字列,空值将被忽略,COUNTALL|DISTINCTexpression|*返回组中项目的数量,COUNT*返回组中项目的数量,这些项目包括NULL值和重复值,COUNTALLexpression对组中的每一行都计算相应的expression的值,并返回非空值的expression的数量,COUNTDISTINCTexpression对组中的每一行都计算expression并返回唯一的非空值的数量,使用聚合函数对数据进行汇总和统计AVGALL|DISTINCTexpression返回组中值的平均值,空值将被忽略,MAXALL|DISTINCTexpression返回表达式的最大值MINALL|DISTINCTexpression返回表达式的最小值,聚合函数一般仅能出现在SELECT和HAVING子句中,不同的DBMS系统对聚合函数有不同的扩充,使用聚合函数之后,SELECT子句中所出现的列名或列名表达式必须出现在GROUPBY子句中或者为聚合函数表达式,