SQL语句详解

提纲SQL概述SQL数据定义功能SQL数据查询功能SQL数据修改功能SQL数据控制功能可编程SQLSQL概述历史SQL:StructuredQueryLanguage(结构化查询语言)1974年，由Boyce和Chamber提出1975-1979年，在SystemR上实现，由IBM的SanJose研究室研制，称为Sequel现在SQL已经成了关系数据库的标准语言,并且发展了三个主要标准,即ANSI(美国国家标准机构)SQL;对ANSISQL修改后在1992年采纳的标准,称为SQL-92或SQL2;最近又出了SQL-99也称SQL3标准.SQL-99从SQL2扩充而来,并增加了对象关系特征和许多其它的新功能.SQL概述标准化SQL-86“数据库语言SQL”（首次通过ISO）SQL-89“具有完整性增强的数据库语言SQL”，增加了对完整性约束的支持SQL-92“数据库语言SQL”，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。SQL-99新标准，增加了对面向对象模型的支持SQL特点综合统一集DDL，DML，DCL于一体单一的结构----关系，带来了数据操作符的统一高度非过程化用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径,存取路径的选择以及SQL语句的操作过程由系统自动完成.大大减轻了用户负担,有利于提高数据独立性.用同一种语法结构提供两种使用方式SQL语言既是自含式语言,又是嵌入式语言.作为自含式语言,它能够独立地用于联机交互的使用方式,用户可以在终端键盘上直接键入SQL命令对数据库进行操作.作为嵌入式语言,SQL语句能够嵌入到高级语言(例如C)程序中,提供程序员设计程序时使用.而在两种方式下,SQL语言的语法结构基本上是一致的.这种统一的语法结构提供两种不同的使用方式的方法,为用户提供了极大的灵活性与方便性.语言简洁，易学易用面向集合的操作方式一次一集合SQL概述用户1用户2用户4视图V1视图V1用户3基本表B1基本表B2基本表B4基本表B3存储文件S1存储文件S2存储文件S4存储文件S3SQL数据库体系结构SQL用户VIEWBaseTableStoredfileSQL基本概念数据库的体系结构分为三级,SQL也支持这三级模式结构,如上图所示,其中外模式对应视图,模式对应基本表,内模式对应存储文件.1.基本表(BaseTable)基本表是模式的基本内容.实际存储在数据库中的表对应一个实际存在的关系.2.视图(View)视图是外模式的基本单位,用户可以通过视图使用数据库中基于基本表的数据.视图是从其它表(包括其它视图)中导出的表,它仅是一种逻辑定义保存在数据字典中,本身并不独立存储在数据库中,因此视图是一种虚表.3.存储文件存储模式是内模式的基本单位.一个基本表对应一个或多个存储文件,一个存储文件可以存放在一个或多个基本表,一个基本表可以有若干个索引,索引同样存放在存储文件中.存储文件的存储结构对用户来说是透明的.DDL/DML/DCLDDL(DataDefinitionLanguage)数据定义语言。用来定义和管理数据库中的对象，如Create,Alter和Drop.DDL操作是隐性提交的！不能rollback.[TRUNCATE]DML(DataManipulationLanguage)数据操纵语言。该命令使用户能够查询数据库以及操作已有数据库中的数据。如insert,delete,update,Select等都是DML.DCL(DataControlLanguage)数据控制语言。用来设置或者更改数据库用户或角色权限的语句，这些语句包括GRANT、DENY、REVOKE等语句，在默认状态下，只有sysadmin、dbcreator、db_owner或db_securityadmin等角色的成员才有权利执行数据控制语言。SQL概述SQL功能操作符数据查询SELECT数据定义CREATE，ALTER，DROP数据操纵INSERT，UPDATE，DELETE数据控制GRANT，REVOKE示例关系DEPT(D#,DNAME,DEAN)S(S#,SNAME,SEX,AGE,D#)C(C#,CN,PC#,CREDIT)SC(S#,C#,GRADE)PROF(P#,PNAME,AGE,D#,SAL)PC(P#,C#)SQL数据定义功能域定义基本表的定义索引的定义数据库的建立与撤消SQL数据定义特点基本表的定义基本表的定义（CREATE）格式createtable表名（列名数据类型[default缺省值][notnull][，列名数据类型[default缺省值][notnull]]……[，primarykey（列名[，列名]…）][，foreignkey（列名[，列名]…）references表名（列名[，列名]…）][，check（条件）]）基本表的定义[]中的内容是可选项.1:定义的基本表的名字,在一个数据库中不允许有两个基本表同名.2:规定列(属性)的名称.一个表中不能有两列同名.3:规定列的数据类型.4:是指对某一列设置的约束条件.该列上的数据必须满足.最常见的有:NOTNULL该列值不能为空NULL该列值可以为空UNIQUE该列值不能存在相同DEFAULT该列某值在未定义时的默认值5:规定了关系主键,外键和用户自定义完整性约束.SQL语句只要求语句的语法正确就可以了,对关键字的大小写,语句的书写格式不作要求.语句中不能出现中文状态下的标点符号.基本表的定义createtableS(S#char(8),SNAMEchar(8)notnulldefault'Unknow',AGEtinyint,SEXchar(1),primarykey(S#),check(SEX='M'orSEX='F'))基本表的定义createtableC(C#char(4)primarykey,CNAMEchar(8)notnullunique,PC#char(4)foreignkeyreferencesC(C#))基本表的定义createtableSC(S#char(8),C#char(4),GRADEtinyint,primarykey(S#,C#),foreignkey(S#)referencesS(S#),foreignkey(C#)referencesC(C#),check((GRADEisnull)orGRADEbetween0and100))基本表的定义修改基本表定义（ALTER）更改、添加、除去列和约束格式：altertable表名[add子句]增加新列和约束[drop子句]删除列和约束[modify子句]修改列定义基本表的定义ALTERTABLEtable_name{[ALTERCOLUMNcolumn_name{DROPDEFAULT|SETDEFAULTconstant_expression|IDENTITY[(seed,increment)]}|ADD{column_definition|table_constraint}[,...n]|DROP{[CONSTRAINT]constraint_name|COLUMNcolumn}]}}基本表的定义altertableSaddresumechar(100)notnullaltertableSaltercolumnresumechar(80)altertableSdropcolumnresume如何定义两个相互参照的表？E#ENAMED#D#DNAMEMGRALTERTABLE只允许添加可包含空值或指定了DEFAULT定义的列altertableSaddLOCATIONchar[30]基本表的定义撤消基本表定义（drop）格式droptable表名DANGER删除表定义及该表的所有数据、索引、触发器、约束和权限规范。但由该表导出的视图的定义仍然存在数据字典当中,只是无法使用。任何引用已除去表的视图或存储过程必须通过DROPVIEW或DROPPROCEDURE语句显式除去。DROPTABLE不能用于除去由FOREIGNKEY约束引用的表。必须先除去引用的FOREIGNKEY约束或引用的表域定义域类型（SQL-92）char（n）：固定长度的字符串varchar（n）：可变长字符串int：整数smallint：小整数类型numeric（p，d）：定点数，小数点左边p位，右边q位real：浮点数doubleprecision：双精度浮点数date：日期（年、月、日）time：时间（小时、分、秒）interval：两个date或time类型数据之间的差域定义域定义(oracle支持，sqlserver不支持)域可以便于我们把不同表之间的公共域抽取到一个位置进行维护。比如，一个电子邮件地址字段可能在多个表中使用，所有的都是同样的属性。我们可以定义并使用一个域，而不是分别设置每个表的约束。格式createdomain域名数据类型示例createdomainperson-namechar（20）域定义CREATEDOMAINpostalASTEXTCHECK(VALUE…);CREATETABLEus_snail_addy(address_idSERIALNOTNULLPRIMARYKEY,street1TEXTNOTNULL,street2TEXT,street3TEXT,cityTEXTNOTNULL,postalpostalNOTNULL);sqlserver-用户自定义类型ifexists(select*fromdbo.systypeswherename=N'person_name')execsp_droptypeN'person_name'GOEXECsp_addtypeN'person_name',N'char(20)',N'null'GO定义者的选择变长之利减少存储开销＋元组数/页变长之弊查询计算偏移＋更新挪移数据变长之用长短显著不一＋很少发生变化charorvarchar定义者的选择主码保证其惟一性必须进行字符匹配一个表的主码经常是另外一个表的外码，而外码是对主码的复制，如果主码太长的话，加上外码则会占据很大的表空间表之间的连接一般是基于主外码的，为加快查询会在主外码上建立索引，太长的主码会使得一个页面里容纳很少的索引项，从而增加查找数据时所需要的磁盘I/O数。IDENTITYIDENTITY[(seed,increment)]为一些没有有效主码的表提供计数器有一个起始数（种子），增量值（步长）IDENTITY属性不能为空，也不能带有default示例createtablecustomer1(cust_idsmallintIDENTITYnotnull，cust_namevarchar(50)notnull)createtablecustomer2(cust_idsmallintIDENTITY(100,20)notnullcust_namevarchar(50)notnull)IDENTITYIDENTITY有关函数IDENT_SEED(表名)返回种子值selectIDENT_SEED('customer1')IDENT_INCR(表名)返回增量值selectIDENT_INCR('customer1')IDENT_CURRENT(表名)返回指定的表最后生成的标识值selectIDENT_CURRENT('customer1')select@@identityIDENTITY1.每一次连接会话中的任一时刻，只能对一个表设置IDENTITY_INSERTON，且设置只对当前会话有效；2.在对标识列执行插入操作，一定要列出标识列。SETIDENTITY_INSERTproductsONINSERTINTO