互联网服务器端开发技术课件 【ch02】数据库基础知识SQL语句

互联网服务器端开发技术数据库基础知识SQL语句第二章iuap数字化技术丛书01SQL概述SQL概述01一、简介数据库(Database)是一种按照数据结构来组织、存储和管理数据的仓库，每个数据库都有一个或多个不同的API用于创建、访问、管理、搜索和复制所保存的数据。关系型数据库管理系统的特点：(1)数据以表格的形式出现；(2)每行为各种记录名称；(3)每列为记录名称所对应的数据域；(4)由众多的行和列组成一张表单；(5)由若干的表单组成Database.SQL概述01二、基本术语数据库：数据库是一些关联表的集合。数据表：表是数据的矩阵。列：一列(数据元素)包含了相同的数据。行：一行(=元组，或记录)是一组相关的数据。冗余：存储两倍同种数据，冗余虽然降低了性能，但是可以提高数据的安全性。主键：主键是唯一的，用来查询数据。外键：外键用于关联两个表。复合键：复合键(组合键)将多个列作为一个索引键，一般用于复合索引。索引：使用索引可快速访问数据库表中的特定信息。参照完整性：参照完整性要求关系中不允许引用不存在的实体。02SQL基本语法SQL基本语法02可以把SQL(结构化查询语言)分为两个部分：数据操作语言(DML)和数据定义语言(DDL)。SQL是用于执行查询的语法，其中也包含更新、插入和删除记录的语法。一、DML和DDL1.DML查询和更新指令构成了SQL的DML部分。(1)SELECT：指从数据库表中获取数据。(2)UPDATE：指更新数据库表中的数据。(3)DELETE：指从数据库表中删除数据。(4)INSERTINTO：指向数据库表中插入数据、SQL基本语法02一、DML和DDL2.DDL语句SQL的数据定义语言(DDL)用以创建或删除表格，也可以用来定义索引(键)、规定表之间的连接，以及施加表间的约束。(1)CREATEDATABASE：

指创建新数据库。(2)ALTERDATABASE：指修改数据库。(3)CREATETABLE：指创建新表。(4)ALTERTABLE：指变更(改变)数据库表。(5)DROPTABLE：指删除表。(6)CREATEINDEX：指创建索引(搜索键)。(7)DROPINDEX：指删除索引。SQL基本语法02一、DML和DDL3.基本语法SQL基本语法02一、DML和DDL4.示例说明(1)创建一个名称为mydb1的数据库。(2)创建一个使用gbk字符集的mydb2数据库。(3)创建一个使用utf8字符集，并带校对规则的mydb3数据库。SQL基本语法02二、操作数据库1.创建数据库基本语法：CREATEDATABASEdatabasename。示例说明：createdatabaselibrarySQL.2.查看数据库基本语法：SHOWDATABASE.示例说明：showdatabase。SQL基本语法02二、操作数据库3.修改数据库基本语法：ALTERDATABASEdatabase.name。示例说明：alterdatabaselibrarySQL。4.删除数据库基本语法：DROPDATABASEdatabasename。示例说明：dropdatabaselibrarySQL.SQL基本语法02三、操作表1.创建表基本语法：CREATETABLEtablename。示例说明：createtablestudent。2.查看表查看表结构：desctabName。查看当前数据库的所有表：showtables。查看当前数据库的建表语句：showcreatetabletabName。SQL基本语法02三、操作表3.修改表基本语法：ALTERTABLEtable_name。示例说明：altertablestudnt。4.删除表基本语法：DROPTABLEtablename。示例说明：droptablestudent。SQL基本语法02四、操作表记录CRUD1.INSERT基本语法：插入的数据应与字段的数据类型相同，数据的大小应在列的规定范围内。在values中列出的数据位置必须与被加入列的排列位置相对应，字符和日期型数据应包含在单引号中。SQL基本语法02四、操作表记录CRUD2.UPDATE基本语法：UPDATE语法可以用新值更新原有表行中的各列。SET子句：指定要修改哪些列和要赋予哪些值。WHERE子句：指定应更新哪些行。如没有WHERE子句，则可更新所有的行。SQL基本语法02四、操作表记录CRUD3.DEL.ETEDELETE语句不能删除某一列的值(可使用UPDATE)，仅能删除记录，不删除表本身。如要删除表，可使用DROPTABLE语句；如果不使用WHERE子句，可删除表中的所有数据。同INSERT和UPDATE一样，从一个表中删除记录将引起其他表的参照完整性问题，所以在修改数据库数据时，这个问题应引起注意。SQL基本语法02四、操作表记录CRUD4.SELECT(1)基本查询它的作用就是从数据库中检索数据，并将查询结果返回给用户。基本语法：(2)使用WHERE子句进行过滤查询(3)使用orderby关键字对查询结果进行排序操作基本语法：asc升序(默认为升序，desc为降序)。SQL基本语法02四、操作表记录CRUD4.SELECT(4)聚合函数①Count：用来统计符合条件行的个数；②AVG：用来计算符合条件的记录中，指定列的平均值；③Sum-SUM：用来将符合条件的记录的指定列进行求和操作；④MAX/MIN：用来获取符合条件的所有记录中，指定列的最大值和最小值；(5)分组查询①通常用于对订单表中商品归类后，显示每一类商品的总价；②查询购买了几类商品，并且每类总价大于100元的商品。SQL基本语法02四、操作表记录CRUD5.数据库的备份与恢复数据库的备份与恢复是不同的，恢复数据只能恢复数据本身，而数据库是无法恢复的。SQL基本语法02五、多表设计与多表查询1.外键约束表是用来保存显示数据的，但是现实生活中数据和数据之间往往具有一定的关联关系，因此在使用表来存储数据时，可以明确声明表和表之间的依赖关系，命令数据库来维护这种关系，这种约束就叫外键约束，实例代码如下：

SQL基本语法02五、多表设计与多表查询2.多表设计多表之间的关系如下。(1)一对多：第一个表的单个行可以与第二个表的一个或多个行相关，但第二个表的一个行只能与第一个表的一个行相关。(2)一对一：第一个表的单个行只可以与第二个表的一个行相关，且第二个表的一个行也只能与第一个表的一个行相关。(3)多对多：第一个表中的一个行可以与第二个表的一个或多个行相关。第二个表的一个行也可以与第一个表的一个或多个行相关。SQL基本语法02五、多表设计与多表查询3.多表查询(1)笛卡儿积查询将两张表的记录进行一个相乘的操作，结果就是笛卡儿积查询，如果左表有n条记录，右表有m条记录，笛卡儿积查询出有n*m条记录。因为这种操作包含了很多错误的数据，所以使用时需要慎重，实例代码如下：(2)内连接查询查询的是左边表和右边表都能找到对应记录的记录，实例代码如下：SQL基本语法02五、多表设计与多表查询(3)外连接查询①左外连接查询：在内连接基础上，增加左边表有而右边表没有的记录，实例代码如下：②右外连接查询：在内连接基础上，增加右边表有而左边表没有的记录，实例代码如下：③全外连接查询：在内连接基础上增加左边表有而右边表没有的记录，以及右边表有而左边表没有的记录，实例代码如下：03JDBCJDBC03一、JDBC快速入门Sun公司提供了一套数据库驱动的接口规范，即JDBC，开发人员通过导入jar包就可以调用其中的方法操作数据库，这样的jar包就叫数据库驱动。1.注册数据库驱动JDBC03一、JDBC快速入门2.获取连接JDBC03一、JDBC快速入门3.获取传输器createStatement0：创建向数据库发送SQL的Statement对象。prepareStatement(sq1)：创建向数据库发送预编译SQL的PrepareSatement对象。4.利用传输器执行SQL语句获取结果集executeQuery(Stringsql)：用于向数据库发送查询语句。executeUpdate(Stringsql)：用于向数据库发送insert语句、update语句或delete语句。execute(Stringsql)：用于向数据库发送任意SQL语句。JDBC03一、JDBC快速入门5.遍历结果集取出结构ResultSet以表的样式在内存中保存了查询结果，并且还维护了一个游标，最初游标在第一行之前，每调用一次next()方法就试图下移一行，如果移动成功则返回true；ResultSet还提供了多个get方法，用来获取查询结果中的不同类型的数据。6.释放资源conn是一个有限的资源，用完后需要及时释放表。stat占用内存，使用完后释放。rs占用内存，使用完后释放。释放时，后创建的先释放，并且保存大的文本和二进制数据。JDBC03二、PreparedStatement1.SQL语句注入由于JDBC程序在执行过程中，SQL语句拼装时使用了由页面传入的参数。如果用户恶意传入一些SQL语句中的特殊关键字，就会导致SQL语句的意义发生变化，这种攻击方式就叫SQL语句注入。JDBC03二、PreparedStatement2.PreparedStatementPreparedStatement继承于Statement，不同的是PreparedStatement使用预编译机制，在创建PreparedStatement对象时就需要将SQL语句传入，传入的过程中参数要用“?”替代，这个过程会导致传入的SQL语句被进行预编译，然后再调用PreparedStatement的setXXX将参数设置上去，但是这时语句已经被预编译。PreparedStatement可利用预编译的机制将SQL语句的主干和参数分别传输给数据库服务器，从而使数据库分辨出哪些是SQL语句的主干，哪些是参数。JDBC03二、PreparedStatement2.PreparedStatementPreparedStatement主要的优点如下：(1)可以防止SQL语句注入；(2)由于使用了预编译机制，其执行效率要高于Statement；(3)SQL语句先使用“?”形式替代参数，然后再用方法设置“?”的值，比使用拼接字符串的效率更高。JDBC03三、处理大数据1.保存大的文本和二进制数据大文本和二进制数据是用来存储大数据的，因此其操作方式有所不同。JDBC03三、处理大数据2.用JDBC操作大文本插入大文本：查询大文本：JDBC03三、处理大数据3.用JDBC操作二进制插入：查询：JDBC03三、处理大数据4.批处理(1)Statement方式执行批处理优点：可以执行多条不同结构的SQL语句。缺点：没有使用预编译机制，运行效率低。如果执行多条结构相同且参数不同的SQL语句时，仍需要多次使用SQL语句的主干。(2)PrparedStatement方式实现的批处理优点：有预编译机制，运行效率比较高。如果执行多条结构相同且参数不同的SQL语句时，不需要重复使用SQL语句的主干。缺点：只能执行主干相同且参数不同的SQL语句，不能在一个批中加入结构不同的SQL语句。04事务事务04一、概述事务是指逻辑上的一组操作，这组操作要么同时完成，要么同时不完成。如果不主动控制事务，数据库则默认处在单独的事务中，当然也可以使用命令去开启一个事务。(1)Starttransactio：开启事务。该语句后的SQL语句处在一个事务中，并且不会立即执行。(2)Commit：提交事务。只有提交事务，其中的所有SQL语句才会执行。(3)Rollback：回滚事务。将之前所有的SQL语句都取消。(4)JDBC中的管理事务。事务04二、事务的四大特性(1)原子性：事务的一组操作是原子的、不可再分割的。因此，这组操作要么同时完成，要么同时不完成。(2)一致性：事务在执行前后数据的完整性保持不变。数据库在某个状态下符合所有的完整性约束的状态称为数据库具有完整性。(3)隔离性：当多个事务同时操作一个数据库时，可能存在并发问题。此时，应该保证各个事务的隔离，使事务之间不会互相干扰。(4)持久性：事务一旦被提交，它对数据库中数据的改变就是永久性的，不可回滚。事务04三、隔离性存在的问题不可重复读在一个事务内读取表中的某一行数据，多次读取的结果不同。2脏读一个事务读取到了另一个事务未提交的数据。1幻读(虚读)一个事务读取了另一个事务插入的数据(已提交)，导致前后读取的数据不一致。3事务04四、数据库的四个隔离级别1.Readuncommitted此隔离级别的数据库会有脏读、不可重复读、幻读的问题。2.Readcommitted此隔离级别的数据库可以防止脏读，但有不可重复读、幻读的问题。3.Repeatableread此隔离级别的数据库可以防止脏读、不可重复读，但是不能防止幻读。事务04四、数据库的四个隔离级别4.Serializable此隔离级别将数据库串行化，可以避免脏读、不可重复读、幻读。安全性：Serializable>Repeatableread>Readco