数据库学习(2.SQL基础篇)

北京久其软件股份有限公司2020年1月28日久其通用数据管理平台数据库学习——SQL基础篇李双义北京久其软件股份有限公司2020年1月28日概述□SQL概述□数据定义□数据查询□数据更新□数据处理北京久其软件股份有限公司2020年1月28日SQL概述SQL（StructuredQueryLanguage）结构化查询语言SQL集数据查询、数据操纵、数据定义和数据控制功能于一体。SQL集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能与一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括：▲定义关系模式，插入数据，建立数据库▲对数据库中的数据进行查询和更新▲数据库重构和维护▲数据库安全性、完整性控制北京久其软件股份有限公司2020年1月28日流行的SQL开发工具MicrosoftAccess基于PC机的数据库管理系统，既可以手工输入SQL语句也可以使用图形用户界面工具来生成SQL语句PersonalOracleX使用命令行的方式来操作数据库MicrosoftQueryMicrosoftQuery是Microsoft公司的VisualC++和VisualBasic开发工具包中所附带的一个非常有用的查询工具，它可应用在基于ODBC标准下的数据库，该查询工具在将查询语句提交给数据库之前会将基保留在驱动器上。开放型数据库联接（ODBC）ODBC是为应用程序接口API提供的访问下层数据库所用的函数库，它通过数据库引擎与数据库交流，就像在Windows通过打印驱动程序与打印机通信那样，为了访问数据库，可能还会需要网络驱动程序与异地数据库通信北京久其软件股份有限公司2020年1月28日SQL创建数据库在任何数据库项目中管路数据的第一步工作就是建立数据库经典的数据库创建语句如下：CREATEDATABASEdatabase_name例：createdatabasemanage1DROPDATABASE语句DROPDATABASEdatabase_name例：dropdatabasemanage1北京久其软件股份有限公司2020年1月28日数据定义□关系数据库系统支持三级模式结构，其模式、外模式和内模式中的基本对象有表、视图和索引。因此SQL的数据定义功能包括模式定义、表定义、视图和索引的定义。SQL通常不提供修改模式定义、修改视图定义和修改索引定义的操作。用户如果想修改这些对象，只能先将它们删除掉，然后再重建。北京久其软件股份有限公司2020年1月28日模式（SCHEMA）定义模式：CREATESCHEMA模式名AUTHORIZATION用户名要创建模式，调用该命令的用户必须拥有DBA权限，或者获得了DBA授予的Createschema的权限。定义模式实际上定义了一个命名空间，在这个空间中可以进一步定义该模式包含的数据库类型，例如基本表、视图、索引等。目前，在createschema中可以接受createtable，createview和grant子句。也就是说用户可以在创建模式的同时在这个模式定义中进一步创建表、视图，定义授权。删除模式：DROPSCHEMA模式名CASCADE|RESTRICT北京久其软件股份有限公司2020年1月28日基本表基本表的定义：CREATETABLE表名(列明数据类型[列级完整性约束条件][,列明数据类型[列级完整性约束条件]]…[，表级完整性约束条件]);表的删除：DROPTABLE表名[RESTRICT|CASECADE]北京久其软件股份有限公司2020年1月28日建表时的约束条件约束条件：NOTNULL非空UNIQUE唯一PRIMARYKEY主键FOREIGNKEY外键CHECK检查条件例:CREATETABLEemployee(EnoCHAR(9)PRIMAEYKEY,EnameCHAR(20),DeptCHAR(20),EageSMALLINT,foreignkey(Dept)referenceDept(Dept)check(Eage0andEage130));北京久其软件股份有限公司2020年1月28日基本表基本表的修改ALTERTABLE表名[ADD新列名数据类型[完整性约束]][DROP完整性约束名][ALTERCOLUMN列名数据类型];ADD子句用于增加新列和新的完整性约束条件，DORP子句用于删除指定的完整性约束条件，ALTERCOLUMN子句用于修改原有的列定义，包括修改列名和数据类型。例：向employee表中增加“入职时间”列，其数据类型为日期型ALTERTABLEemployeeADDE_entranceDATE;北京久其软件股份有限公司2020年1月28日视图一、建立视图SQL语言用CREATEVIEW命令建立视图，其一般格式为：CREATEVIEW视图名[(列名[,列名]…)]AS子查询[WITHCHECKOPTION];二、删除视图DROPVIEW视图名[CASCADE];例：CREATEVIEWTest_employeeASSELECTEno,Ename,EageFROMemployeeWHEREDept=‘Test’;北京久其软件股份有限公司2020年1月28日视图作用当对一组数据建立视图以后，你可以像处理另外一个表一样去处理视图。但是在视图中修改数据时要受到一些限制，当表中的数据改变以后你将会在查询视图时，发现相应的改变，视图并不占用数据库或表的物理空间。视图的作用：1.视图能够简化用户的操作2.视图使用户能以多个角度看待同一数据3.视图对重构数据库提供了一定程度的路基独立性。4.视图能够对机密数据提供安全保护5.适当的利用视图可以更清晰的表达查询北京久其软件股份有限公司2020年1月28日索引索引的建立：CREATE[UNIQUE][CLUSTER]INDEX索引名ON表名(列名[次序][,列名次序]]…);删除索引：DROPINDEX索引名例：CREATEUNIQUEINDEXEmpnoOnemployee(Eno);DROPINDEXEmpno;北京久其软件股份有限公司2020年1月28日索引在SQL中使用索引是其于以下几个原因1.在使用UNIQUE关键字时强制性地保证数据的完整性2.可以容易地用索引字段或其它字段进行排序3.提高查询的执行速度在数据库中使用索引可以让SQL使用直接访问方式。SQL采用树形结构来存储和返回索引数据，用以指示的数据存储在树的最末端（也就是叶子），它们被称为结点（也可以叫叶子）。每一个结点中有一个指向其它结点的指针。结点左边的值只是它的双亲结点，结点右边的值则是孩子结点或叶子。北京久其软件股份有限公司2020年1月28日序列序列是一个独立的编号发生器，提供流水线的主键。是一个自增的数字。CREATESEQUENCEname[INCREMENTBYn]增幅，默认自增1[STARTWITHn]从n开始计数[{MAXVALUEn|ONMAXVALUS}]限定最大值[{MUNVALUEn|NOMINVALUE}]限定最小值[{CYYCLE|NOCYCLE}]环绕计数[{CACHEn|NOCACHE}]缓冲例：CREATESEQUENCESeq_empINCREMENTBY1STARTWITH2000;INSERTINTOINV_MASTER(INV_NO,…)VALUES(SEQ_INV.NEXTVAL,…)DROPSEQUENCESeq_emp；北京久其软件股份有限公司2020年1月28日序列1.序列是一个独立的编号发生器2.在一个事务中，通过访问序列的NEXTVAL和CURRVAL可以获得序列的下一个值和当前值3.保证多个事务之间不会出现并发冲突4.可以在多个表之间复用北京久其软件股份有限公司2020年1月28日数据查询⑤SELECT[ALL|DISTINCT]目标列表表达式[,目标列表表达式]…①FROM表名或视图名[,表名或视图名]②[WHERE条件表达式]③[GROUPBY列名1④[HAVING条件表达式]]⑥[ORDERBY列名2[ASC|DESC]];北京久其软件股份有限公司2020年1月28日数据查询一、单表查询二、连接查询三、嵌套查询四、集合查询北京久其软件股份有限公司2020年1月28日数据查询---单表查询1.查询指定列2.查询全部列3.查询经过计算的值(1)Select子句(别名、去重、算术运算、)Select后和from后的表都可以使用别名(2)Where子句(比较大小、确定范围(betweenand)、确定集合(IN)、字符匹配(LIKE\ESCAPE)、涉及空值的查询、多重条件查询)having北京久其软件股份有限公司2020年1月28日数据查询---连接查询□连接查询是关系数据库中最主要的查询，包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和符合条件连接查询等。一、等值与非等值连接查询二、自身连接查询三、外连接查询四、符合条件查询北京久其软件股份有限公司2020年1月28日数据查询---嵌套查询□在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询快嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询一、带有IN为此的子查询二、带有比较运算符的子查询三、带有ANY(SOME)或ALL为此的子查询四、带有EXISTS谓词的子查询北京久其软件股份有限公司2020年1月28日数据查询---集合查询□SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。集合操作主要包括UNION、交操作INTERSECT和差操作EXCEPT一、UNIONUNION将多个查询结果合并起来时，系统会自动去掉重复元组二、INTERSECTINTERSECT语句等价于where语句中and运算三、EXCEPTEXCEPT语句等价于与where语句中and和非运算北京久其软件股份有限公司2020年1月28日数据更新1.插入数据INSERT2.修改数据UPDATE3.删除数据DELETE4.事务处理COMMIT、SAVEPOINT、ROLLBACK北京久其软件股份有限公司2020年1月28日数据更新---插入数据一、插入元组INSERTINTO表名[(属性列1[,属性列2])…]VALUES(常量1[,常量2]…);例：INSERTINTOEmployee(Eno,Ename,Esex,Edept,Eage)VALUES(‘200512123’,’小明’,’男’,’IS’,18)INTO指出新增的元组在哪些属性上要赋值时，属性的顺序可以与CREATETABLE中的顺序不一样。INTO子句中没有出现的属性列，新元组在这些列上将取空值。二、插入子查询Insertinto表名[(属性列1)[,(属性列2)]]子查询语句北京久其软件股份有限公司2020年1月28日数据更新---修改数据修改操作有称为更新操作，其语句的一般格式为UPDATE表名SET列名=表达式[,列名=表达式]…[WHERE条件];一、修改某一个元组的值UPDATE表名SET列名=表达式WHERE条件;二、修改多个元组的值UPDATE表名SET列名=表达式三、带有子查询的修改语句UPDATE表名SET列名=表达式WHERE子查询语句北京久其软件股份有限公司2020年1月28日数据更新---删除数据删除语句的一般格式为：DELETEFROM表名[WHERE条件];一、删除一个元组的值DELETEFROM表名WHERE条件;二、删除多个元组的值DELETEFROM表名三、带有子查询的删除语句DELETEFROM表名WHERE子查询语句北京久其软件股份有限公司2020年1月2