按照知识点概括梳理 看的时候可以搜索你要复习的关键字查找 知识点 MySQL 数据库 1.MySQL 事务 MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员， 你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操 作语句就构成一个事务！ 1. 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务 2. 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行 3. 事务用来管理 insert,update,delete 语句 一般来说，事务是必须满足 4 个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、 Isolation（隔离性）、Durability（可靠性） 1. 事务的原子性：一组事务，要么成功；要么撤回。 2、稳定性 ： 有非法数据（外键约束之类），事务撤回。 3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的 100%隔离，需要牺牲速度。 4、可靠性：软、硬件崩溃后，InnoDB 数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼 得， innodb_flush_log_at_trx_commit 选项 决定什么时候吧事务保存到日志里。 在 MySQL 控制台使用事务来操作 1，开始一个事务 start transaction 2, 做保存点 savepoint 保存点名称 3, 操作 4，可以回滚，可以提交，没有问题，就提交，有问题就回滚。 2. MySQL 索引 MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速 度。 打个比方，如果合理的设计且使用索引的 MySQL 是一辆兰博基尼的话，那么没有设计和使用索引 的 MySQL 就是一个人力三轮车。 索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索 引，但这不是组合索引。组合索引，即一个索包含多个列。 创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条 件)。 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。 上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索 引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件。 建立索引会占用磁盘空间的索引文件。 1. 普通索引 1.创建索引 这是最基本的索引，它没有任何限制。它有以下几种创建方式： CREATE INDEX indexName ON mytable(username(length)); 如果是 CHAR，VARCHAR 类型，length 可以小于字段实际长度；如果是 BLOB 和 TEXT 类型， 必须指定 length。 2.修改表结构 ALTER mytable ADD INDEX [indexName] ON (username(length)) 3.创建表的时候直接指定 CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) ); 4.删除索引的语法 DROP INDEX [indexName] ON mytable; 2. 唯一索引 它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值 的组合必须唯一。它有以下几种创建方式： 1. 创建索引 REATE UNIQUE INDEX indexName ON mytable(username(length)) 2. 修改表结构 ALTER mytable ADD UNIQUE [indexName] ON (username(length)) 3. 创建表的时候直接指定 CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) ); 3. 使用 ALTER 命令添加和删除索引 有四种方式来添加数据表的索引：  1. ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):  该语句添加一个主键，这意味着索引值必须是唯一的，且不能为 NULL 。  2. ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):  这条语句创建索引的值必须是唯一的（除了 NULL 外， NULL 可能会出现多次）。  3. ALTER TABLE tbl_name ADD INDEX index_name (column_list):  添加普通索引，索引值可出现多次。  4. ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):  该语句指定了索引为 FULLTEXT ，用于全文索引。 4. 显示索引信息 你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出 信息。 尝试以下实例 : mysql> SHOW INDEX FROM table_name\G ........ 3. MySQL 临时表 MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭 连接时， MySQL 会自动删除表并释放所有空间。 临时表在 MySQL 3.23 版本中添加，如果你的 MySQL 版本低于 3.23 版本就无法使用 MySQL 的 临时表。不过现在一般很少有再使用这么低版本的 MySQL 数据库服务了。 如果你使用了其他 MySQL 客户端程序连接 MySQL 数据库服务器来创建临时表，那么只有在关闭客 户端程序时才会销毁临时表，当然你也可以手动销毁。 4. MySQL 处理重复数据 1). MySQL 处理重复数据 有些 MySQL 数据表中可能存在重复的记录，有些情况我们允许重复数据的存在，但有时 候 我们也需要删除这些重复的数据。 2). 防止表中出现重复数据 你可以在 MySQL 数据表中设置指定的字段为 PRIMARY KEY （主键） 或者 UNIQUE （唯一） 索 引来保证数据的唯一性。 让我们尝试一个实例：下表中无索引及主键，所以该表允许出现多条重复记录。 CREATE TABLE person_tbl ( first_name CHAR(20), last_name CHAR(20), sex CHAR(10) ); 如果你想设置表中字段 first_name ， last_name 数据不能重复，你可以设置双主键模式来设置数据的唯 一性， 如果你设置了双主键，那么那个键的默认值不能为 NULL ，可设置为 NOT NULL 。如下所示： CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), PRIMARY KEY (last_name, first_name) ); 如果我们设置了唯一索引，那么在插入重复数据时， SQL 语句将无法执行成功 , 并抛出错。 INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE 会忽略数据库中已经存在的 数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库 中已经存在数据，达到在间隙中插入数据的目的。 以下实例使用了 INSERT IGNORE INTO ，执行后不会出错，也不会向数据表中插入重复数据： mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) -> VALUES( 'Jay', 'Thomas'); Query OK, 1 row affected (0.00 sec) mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) -> VALUES( 'Jay', 'Thomas'); Query OK, 0 rows affected (0.00 sec) INSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错 误，只以警告形式返回。 而 REPLACE INTO into 如果存在 primary 或 unique 相同的记录，则先删 除掉。再插入新记录。 另一种设置数据的唯一性方法是添加一个 UNIQUE 索引，如下所示： CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10) UNIQUE (last_name, first_name) ); 3). 查询重复记录 select user_name,count(*) as count from user_table group by user_name having count>1; select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 4) . 统计重复数据 以下我们将统计表中 first_name 和 last_name 的重复记录数： mysql> SELECT COUNT(*) as repetitions, last_name, first_name -> FROM person_tbl -> GROUP BY last_name, first_name -> HAVING repetitions > 1; 以上查询语句将返回 person_tbl 表中重复的记录数。 一般情况下，查询重复的值，请执行以下操作：  确定哪一列包含的值可能会重复。  在列选择列表使用 COUNT(*) 列出的那些列。  在 GROUP BY 子句中列出的列。  HAVING 子句设置重复数大于 1 。 5). 过滤重复数据 如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。 mysql> SELECT DISTINCT last_name, first_name -> FROM person_tbl -> ORDER BY last_name; 你也可以使用 GROUP BY 来读取数据表中不重复的数据： mysql> SELECT last_name, first_name -> FROM person_tbl -> GROUP BY (last_name, first_name); 6). 删除重复数据 如果你想删除数据表中的重复数据，你可以使用以下的 SQL 语句： mysql> CREATE TABLE tmp SELECT last_name, first_name, sex -> FROM person_tbl; -> GROUP BY (last_name, first_name); mysql> DROP TABLE person_tbl; mysql> ALTER TABLE tmp RENAME TO person_tbl; 当然你也可以在数据表中添加 INDEX （索引） 和 PRIMAY KEY （主键）这种简单的方法来删除 表中的重复记录。方法如下： mysql> ALTER IGNORE TABLE person_tbl -> ADD PRIMARY KEY (last_name, first_name); 5. MySQL 导出数据 MySQL 中你可以使用 SELECT...INTO OUTFILE 语句来简单的导出数据到文本文件上。 1). 使用 SELECT ... INTO OUTFILE 语句导出数据 以下实例中我们将数据表 w3cschool_tbl 数据导出到 /tmp/tutorials.txt 文件中 : mysql> SELECT * FROM tutorials_tbl -> INTO OUTFILE '/tmp/tutorials.txt'; 你可以通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式： mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.txt' -> FIELDS TERMINATED BY ',' ENCLOSED BY '"' -> LINES TERMINATED BY '\r\n'; 在下面的例子中，生成一个文件，各值用逗号隔开。这种格式可以被许多程序使用。 SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table; 2).SELECT ... INTO OUTFILE 语句有以下属性:  LOAD DATA INFILE 是 SELECT ... INTO OUTFILE 的逆操作， SELECT 句法。为了将一个数据 库的数据写入一个文件，使用 SELECT ... INTO OUTFILE ，为了将文件读回数据库，使用 LOAD DATA INFILE 。  SELECT...INTO OUTFILE 'file_name' 形式的 SELECT 可以把被选择的行写入一个文件中。该文 件被创建到服务器主机上，因此您必须拥有 FILE 权限，才能使用此语法。  输出不能是一个已存在的文件。防止文件数据被篡改。  你需要有一个登陆服务器的账号来检索文件。否则 SELECT ... INTO OUTFILE 不会起任何作 用。  在 UNIX 中，该文件被创建后是可读的，权限由 MySQL 服务器所拥有。这意味着，虽然你就可以 读取该文件，但可能无法将其删除。 3). 导出表作为原始数据 mysqldump 是 MySQL 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本，其中包含从头重新 创建数据库所必需的命令 CREATE TABLE INSERT 等。 使用 mysqldump 导出数据需要使用 --tab 选项来指定导出文件指定的目录，该目标必须是可写的。 以下实例将数据表 tutorials_tbl 导出到 /tmp 目录中： $ mysqldump -u root -p --no-create-info \ --tab=/tmp W3CSCHOOL w3cschool_tbl password ****** 4). 导出 SQL 格式的数据 导出 SQL 格式的数据到指定文件，如下所示： $ mysqldump -u root -p W3CSCHOOL w3cschool_tbl > dump.txt password ****** 以上命令创建的文件内容如下： -- MySQL dump 8.23 -- -- Host: localhost Database: W3CSCHOOL --------------------------------------------------------- -- Server version 3.23.58 -- -- Table structure for table `w3cschool_tbl` -- CREATE TABLE w3cschool_tbl ( w3cschool_id int(11) NOT NULL auto_increment, w3cschool_title varchar(100) NOT NULL default '', w3cschool_author varchar(40) NOT NULL default '', submission_date date default NULL, PRIMARY KEY (w3cschool_id), UNIQUE KEY AUTHOR_INDEX (w3cschool_author) ) TYPE=MyISAM; -- -- Dumping data for table `w3cschool_tbl` -- INSERT INTO w3cschool_tbl VALUES (1,'Learn PHP','John Poul','2007-05-24'); INSERT INTO w3cschool_tbl VALUES (2,'Learn MySQL','Abdul S','2007-05-24'); INSERT INTO w3cschool_tbl VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06'); 如果你需要导出整个数据库的数据，可以使用以下命令： $ mysqldump -u root -p W3CSCHOOL > database_dump.txt password ****** 如果需要备份所有数据库，可以使用以下命令： $ mysqldump -u root -p --all-databases > database_dump.txt password ****** --all-databases 选项在 MySQL 3.23.12 及以后版本加入。 该方法可用于实现数据库的备份策略。 5). 将数据表及数据库拷贝至其他主机 如果你需要将数据拷贝至其他的 MySQL 服务器上 , 你可以在 mysqldump 命令中指定数据库名 及数据表。 在源主机上执行以下命令，将数据备份至 dump.txt 文件中 : $ mysqldump -u root -p database_name table_name > dump.txt password ***** 如果完整备份数据库，则无需使用特定的表名称。 如果你需要将备份的数据库导入到 MySQL 服务器中，可以使用以下命令，使用以下命令你需要确 认数据库已经创建： $ mysql -u root -p database_name < dump.txt password ***** 你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是 相通的，是可以相互访问的： </p> $ mysqldump -u root -p database_name \ | mysql -h other-host.com database_name 以上命令中使用了管道来将导出的数据导入到指定的远程主机上。 Oracle 数据库 1. 对索引的理解： 1、索引相当于字典的目录，作用在于提升查询效率，降低磁盘读写，提升数据库性能 2、索引是一种独立于表的数据库对象，可以存储在于表不同的磁盘中间或表空间 3、索引一旦创建，由 oracle 数据库自己来维护，并且由 oracle 管理系统来指定何时使用索引，我们不需 要在查询语句中自己指定索引 4、索引的删除或损毁不会对数据库表带来影响，只会影响查询效率 5、创建索引的时候，如果没有指定表空间，会存储到默认的表空间 6、在删除表的时候，基于表的索引会被删除 2. 索引工作原理 创建索引的时候，oracle 会先在表空间中给索引开辟一个空间，并按索引字段对应的值进行分组，并把分 好组的地址 rowid 存入索引空间中，当再次查询的时候，数据库会自动判断查询的条件中是否建有索引， 如果有，则根据索引去查询符合条件的数据 3. 索引的创建 两种：1.自动创建--------即在创建主键 primarykey 或唯一性约束 unique 的时候， 数据库会自动在相应的列上 创建唯一性索引 一般业务过程中通过主键查询比较频繁，提升查询效率 2.手动创建--------即在不唯一的列上创建非唯一的索引，加速查询效率 create index index_xxx_xxx on 表（创建索引的列） tablespace 表空间（表空间 可以不指定）工具创建 4. 索引的弊端： 一般在数据量比较大的表中并且经常查询的字段上才去建立索引，数据量小或不经常查询的字段建立 索引的话，不仅会占用内存空间，而且会降低查询效率，索引不是越多越好， 下列情形不要创建索引 1、表很小 2、列不经常作为连接条件的 3、表经常更新的 系统日志历史表必须增加索引，效率超高 5. 执行计划 explain plan 作用：查询索引是否生效以及预估索引性能效果等 方式：1、explain plan window 窗口 2、对查询语句按 f5 6. 数据字典 主要存储的是数据库系统信息，由数据库系统自己去维护管理，一般我们只进行查询，不做修改 7. Pl/sql 是 oracle 对 sql 语言的过程化扩张，是存储过程的基础 组成：声明部分、可执行部分、异常处理部分 普通变量、引用变量(%type)、记录型变量（%rowtype） 1， 使用引用类型，当列中的数据类型发生改变，不需要修改变列的类型。而使用普通方式，当列的类型 改变时，需要修改变列的类型 使用 %TYPE 是非常好的编程风格，因为它使得 PL/SQL 更加灵活，更加适应于对数据库定义的更新。 8. 游标 cursor 可以理解为数据库表返回的结果集，它带有向前移动的指针，并且每次只向前移动一行数据 作用：可以临时存储返回的多行数据，通过变量游标，可以得到每一行的数据 9. 存储过程 program window--procedure 定义： 是一组预编译的 sql 语句，也就是给 plsql 语句包装起来，完成一次创建任意调用的功能，相当于 java 中的方法 作用：在开发中，有时候为了某种特定的业务功能，需要对数据库进行多次的连接关闭，这种连接关闭是 很耗费资源的，并且会对数据库进行多次的 io 读写，性能比较低，如果把业务功能放在 plsql 中，只需要 连接关闭一次数据库就可以实现我们的业务功能，大大提高了效率 2、更好的安全机制，对于没有权限执行存储过程的用户，也可以授权他们执行存储过程 3、对应大量的 sql 语句和重复执行的 sql 语句，存储过程执行要快 = 参数： 不带参数的、带输入参数的。带输入输出参数的 关于写存储的 3 个窗口的选择： 预发布的时候使用 command 测试使用 test 10. 存储过程和存储函数的区别： 1、存储过程可以有返回值也可以没有返回值，存储函数必须有返回值 2、存储过程和存储函数都可以通过输出参数 out 实现多个返回值 怎么选择： 原则上只有一个返回值的用存储函数，否则用存储过程 但是我们一般都使用存储过程，因为 1、存储过程可以有返回值也可以没有返回值，存储的灵活性 2、存储过程既然有返回值了，可以替代存储函数 3、Oracle 新版中已经不推荐适用存储函数了 Java 中通过 CallableStatement 调用存储过程 11. 触发器： 解释： 1、是一段 plsql 程序 2、它用来触发 dml(insert、update、delete)操作的 3、在进行 dml 操作时会自动触发执行的一段程序 换句话说：触发器就是在执行某个操作（增删改）的时候触发一个动作(一段程序)。 有点像 struts2 的拦截器，可以对 cud 增强 触发器类型 1、语句级触发器（表级触发器） 在指定的操作语句执行之前或之后执行一次，不管它影响了多少行 2、行级触发器（for each row） 触发语句作用都的每一条语句都会被触发 数据库三范式是什么? 第一范式（1NF）： 字段具有原子性 , 不可再分。所有关系型数据库系统都满足第一范式） 数据库表中的字段都是单一属性的，不可再分。例如，姓名字段，其中的姓和名必须作为一个整体，无法 区分哪部分是姓，哪部分是名，如果要区分出姓和名，必须设计成两个独立的字段。 第二范式（2NF）： 第二范式（ 2NF ）是在第一范式（ 1NF ）的基础上建立起来的，即满足第二范式（ 2NF ）必须先满 足第一范式（ 1NF ）。 要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的惟 一标识。这个惟一属性列被称为主关键字或主键。 第二范式（ 2NF ）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部 分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原 实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言 之，第二范式就是非主属性非部分依赖于主关键字。 第三范式的要求如下： 满足第三范式（ 3NF ）必须先满足第二范式（ 2NF ）。简而言之，第三范式（ 3NF ）要求一个数据库 表中不包含已在其它表中已包含的非主关键字信息。 所以第三范式具有如下特征： 1 ，每一列只有一个值 2 ，每一行都能区分。 3 ，每一个表都不包含其他 表已经包含的非主关键字信息。 例如，帖子表中只能出现发帖人的 id ，而不能出现发帖人的 id ，还同时出现发帖人姓名，否则，只要出现 同一发帖人 id 的所有记录，它们中的姓名部分都必须严格保持一致，这就是数据冗余。