MYSQL知识点补充

很多情况下会为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间的目的。比如我们有一个表，产品名称，单价，库存量，总价值。这个表是不满足第三范式的，因为“总价值”可以由“单价”乘以“数量”得到，说明“金额”是冗余字段。但是，增加“总价值”这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的做法。合理的冗余可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。

3.主键

主键：根据第二范式，需要有一个字段去标识这条记录，主键无疑是最好的标识，需要满足唯一性、非空性，但是很多表也不一定需要主键，但是对于数据量大，查询频繁的数据库表，一定要有主键，主键可以增加效率、防止重复等优点。

4.选择数据类型

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。

更小的通常更好 更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。

简单就好 简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较更复杂。

尽量避免NULL

如果查询中包含可为NULL 的列，对MySQL来说更难优化，因为可为NULL 的列使得索引、索引统计和值比较都更复杂。

数据类型尽量用数字型，数字型的比较比字符型的快很多

例如:数字型占用1字节，字符型char占用2字节，int占4个字节

2.数据库约束

约束的作用：对表中的数据进行限定，保证数据的正确性、有效性和完整性。
分类：

非空约束：not null

唯一约束：unique

主键约束：primary key

外键约束：foreign key

检查：check

默认：DEFAULT
为数据表指定约束有如下两个时机：

建表的同时为相应的数据列指定约束。

建表后创建，以修改表的方式来增加约束。

1.非空约束：not null

SQL中的null不区分大小写，所有数据类型的值都可以是null，包括int、float、boolean等数据类型，空字符串不等于null，0也不等于null，并且null不等于null,所以在唯一约束里允许多个null值。

1. 创建表时添加约束
   CREATE TABLE stu(
    id INT,
    NAME VARCHAR(20) NOT NULL -- name为非空
   );
2. 创建表完后，添加非空约束
   ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
3. 删除name的非空约束
   ALTER TABLE stu MODIFY NAME VARCHAR(20);

2.唯一约束：unique

同一个表内可建多个唯一约束，唯一约束也可由多列组合而成。当为某列创建唯一约束时，MySQL会为该列相应地创建唯一索引。如果不给唯一约束起名，该唯一约束默认与列名相同。

唯一约束：unique，某一列的值不能重复
1. 注意：
* 唯一约束可以有多个NULL值（至少Navicat16是这样）

1. 创建表时，添加唯一约束
   CREATE TABLE stu(
    id INT UNIQUE,             -- 1.列级约束语法
        UNIQUE(id),                -- 2.表级约束语法 
        CONSTRSINT 别名 UNIQUE(id)  -- 3.表级约束语法起别名
   );
   * 注意mysql中，唯一约束限定的列的值可以有多个null，创建组合约束
   * 只能用表级约束法。这里只是为了阐述问题方便放在了同一个表中，
   * 实际只选择其中一种即可，以下的也是如此。
2. 删除唯一约束
   ALTER TABLE stu DROP INDEX phone_number;
3. 在创建表后，添加唯一约束
   ALTER TABLE stu MODIFY id INT UNIQUE;     -- 1.添加单列约束
    ALTER TABLE stu ADD UNIQUE(列名1，列名2);   -- 2.添加组合约束

3.主键约束：primary key

主键约束相当于非空约束和唯一约束，即主键约束的列既不允许出现重复值，也不允许出现null值；如果对多列组合建立主键约束，则多列里包含的每一列都不能为空，但只要求这些列组合不能重复。主键列的值可用于唯一地标识表中的一条记录。

1. 注意：
   1. 含义：非空且唯一
   2. 一张表只能有一个字段为主键
   3. 主键就是表中记录的唯一标识
2. 在创建表时，添加主键约束
   CREATE TABLE stu(
    id INT PRIMARY KEY,                  -- 1.列级主键约束语法
    name VARCHAR(20),
        PRIMARY KEY(id),                     -- 2.表级主键约束语法
        PRIMARY KEY(id,name),                -- 3.表级组合主键约束语法
        CONSTRSINT 别名 PRIMARY KEY(id,name)  -- 4.表级组合主键约束语法 
   );

* 如果需要对多个字段建立组合主键约束，则只能使用表级约束语法

3. 删除主键
   -- 错误 ALTER TABLE stu MODIFY id INT ;
   ALTER TABLE stu DROP PRIMARY KEY;

4. 创建完表后，添加主键
   ALTER TABLE stu MODIFY id INT PRIMARY KEY; -- 1.列级语法添加单列主键约束 
    ALTER TABLE stu ADD PRIMARY KEY(id,name); -- 2.表级语法添加组合主键约束

5. 自动增长：
   1.  概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长

   2.  在创建表时，添加主键约束，并且完成主键自增长
       CREATE TABLE stu(
       id INT PRIMARY KEY AUTO_INCREMNET,
       name VARCHAR(20)
       );

   3.  删除自动增长
       ALTER TABLE stu MODIFY id INT;

   4.  添加自动增长
       ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

4.外键约束：foreign key

外键约束通常用于定义两个实体之间的一对多、一对一的关联关系。对于一对多的关联关系，通常在多的一端增加外键列。

当主表的记录被从表记录参照时，主表记录不允许被删除，必须先把从表里参照该记录的所有记录全部删除后，才可以删除主表的该记录。还有一种方式，删除主表记录时级联删除从表中所有参照该记录的从表记录。

从表外键参照的只能是主表主键列或者唯一键列，这样才可保证从表记录可以准确定位到被参照的主表记录。同一个表内可以拥有多个外键。

建立外键约束同样可以采用列级约束语法和表级约束语法。如果仅对单独的数据列建立外键约束，则使用列级约束语法即可；如果需要对多列组合创建外键约束，或者需要为外键约束指定名字，则必须使用表级约束语法，值得指出的是，虽然MySQL支持使用列级约束语法来建立外键约束，但这种列级约束语法建立的外键约束不会生效，MySQL提供这种列级约束语法仅仅是为了和标准SQL保持良好的兼容性。

1. 在创建表时，可以添加外键
   * 语法：
     CREATE TABLE 表名(
        ....
        外键列
        CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称)
     );

2. 删除外键
   ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

3. 创建表之后，添加外键
   ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);


4. 级联操作

   * 如果想定义当删除主表记录时，从表记录也会随之删除，则需要在建立外键约束后添加on delete cascade或添加on delete set null，
   * 第一种是删除主表记录时，把参照该主表记录的从表记录全部级联删除；
   * 第二种是指定当删除主表记录时，把参照该主表记录的从表记录的外键设为null；
   * 如果想定义当更新主表记录时，从表记录也会随之更新，则需要定义级联更新on update cascade。

   1. 添加级联操作
      语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
            FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;
   2. 分类：
      1. 级联更新：ON UPDATE CASCADE 或者 ON UPDATE SET NULL
      2. 级联删除：ON DELETE CASCADE

5.DEFAULT：默认，该字段的内容不用手动插入有默认值

添加默认
alter table 表名 modify 字段名 字段类型 default 值;

删除默认
alter table 表名 modify 字段名 字段类型 ;
或
alter table 表名 modify 字段名 字段类型 DEFAULT NULL;

3. CRUD

一、数据库的CRUD

增(create)、删(delete)、改(update)、查(Retrieve)

库的增(create)、删(drop)、改(update)、查(show)

1.创建数据库

创建一个名称为mydb1的数据库。

create database mydb1;

创建一个使用utf-8字符集的mydb2数据库。

create database mydb2 character set utf8;

2.查看数据库

显示所有数据库

show databases;

显示创建数据库的语句信息

show create database mydb2; 
“ ` ”（ESC键 下面的按键），表示反引号，默认情况下，反引号括起来的字符串，区分大小写。
show create database mydb1;

注意：mysql默认语言集是不是utf8，每次在创建数据库的时候应指定字符集。Oracle是在安装时，即指定了字符集。

(1)编译MySQL时，指定了一个MySQL默认的字符集，这个字符集是 latinl ;
(2)安装MySQL时，可以在配置文件(my.ini)中指定一个默认的的字符集，如果没指定，这个值继承自编译时指定的;

(3)启动mysql时，可以在命令行参数中指定一个默认的的字符集，如果没指定，这个值继承自配置文件中的配置,此时character_set_server被设定为这个默认的字符集;
(4)当创建一个新的数据库时，除非明确指定，这个数据库的字符集被缺省设定为character_set_server;

(5)当选定了一个数据库时, character_set_database 被设定为这个数据库默认的字符集;
(6)在这个数据库里创建一张表时，表默认的字符集被设定为character_set database，也就是这个数据库默认的字符集;

(7)当在表内设置一栏时，除非明确指定，否则此栏缺省的字符集就是表默认的字符集;
相当于一个优先级,7最高,1最低

mysql中的默认编码是latin1，可以使用“SHOW VARIABLES LIKE 'character_set_server';”命令来进行查看。latin1编码是单字节编码，向下兼容ASCII，但不支持中文

3.修改数据库

修改mydb1的字符集为utf8(不能修改数据库名)
	alter database mydb1 character set utf8;	
4.删除数据库

删除数据库mydb3
	drop database mydb3;

二、表的CRUD

增(create)、删(delete)、改(update)、查(Retrieve)

表的增(create)、删(drop/delete/truncate)、改(alter)、查(show)

1.创建表(create)

create table t1 (id int, name varchar(20)) ;

但此时会报错误：ERROR 1046 (3D000): No database selected。注意，在mysql中对表操作前，必须先选择所使用的数据库。 use mydb2;

查看当前选择的数据库中的表：
		show tables;
	查看表结构：
		desc t1;

查看创建表的语法： show create table t1; ENGINE=InnoDB 默认指定的存储引擎 innoDB。

例如：创建一个员工表：

create table employee(empno int, ename varchar(20), sal int);

后面表的代码以此例为照应

2.查看表

查看所有的表：
		show tables;
查看指定表的创建语句
		show create table employee;
	注意，mysql表名称区分大小写
显示指定表的结构：
		desc employee;

3.修改表

更改表名：    rename table employee to worker;
增加一个字段：alter table employee add column height double; 
  （column关键字在Oracle中，添加则语法错误）
修改一个字段：alter table employee modify column height float;
修改字段名: alter table employee change column height height1 float;
删除一个字段：alter table employee drop column height1;
修改表的字符集:alter table employee character set gbk;

4.删除表（drop）

删除employee表
	drop table employee;		
	(MySQL中不能使用purge，添加会出现语法错误)

3.表数据的CURD

1.create数据

创建一个员工表，新建employee表并向表中添加一些记录：

create table employee(
id int,
name varchar(20),
sex int,
birthday date,
salary double,
entry_date date,
resume text
);

insert into employee values(1,'张三',1,'1983-04-27',15000,'2012-06-24','一个大牛');
insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(2,'李四',1,'1984-02-22',10000,'2015-07-24','一个中流');
insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(3,'王五',0,'1985-08-28',7000,'2018-08-24','一个小菜');

2.update数据

将所有员工薪水都增加500元。

update employee set salary=salary+500;

将王五的员工薪水修改为10000元，resume改为也是一个中流

update employee set salary=10000, resume='也是一个中流' where name='王五';

3.delete数据

删除表中姓名为王五的记录。
		delete from employee where name='王五';		
		【注意from不能省略】
删除表中所有记录。
		delete from employee; 
使用truncate删除表中记录。
		truncate employee;
		（无条件 效率高）

4.Retrieve数据

检索谁的id>2

select id, name as "名字", salary "月薪", salary*12 年薪  from employee where id>2;

4.索引

1.什么是索引

在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用指向数据，这样就可以在这些数据结构上实现高效查找，这些数据结构就是索引。

简单来说索引的出现就是为了提高数据的查询效率，就像书的目录一样。在书籍中，用户不必翻阅完整个书就能根据目录迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

2.索引的分类

单值索引：一个索引只包含单个列，一个表中可以有多个单值索引

唯一索引：索引列的值必须唯一，可为空

复合索引：一个索引包括多个列

3.索引的优势

1）提高数据检索效率，降低磁盘IO成本

2）通过对数据的排序，降低排序成本

在数据库系统中建立索引主要有以下作用：

（1）快速取数据；

（2）保证数据记录的唯一性；

（3）实现表与表之间的参照完整性；

（4）在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。

4. 索引的劣势

1）索引虽提高了查询效率，但同时降低了更新、修改、删除的效率，因为MySQL不仅要保存数据，还要维护数据和索引的关系。

2）需要成本去维护索引。一个性能良好的索引需要不断的去尝试，以找到最优解。

5. 什么情况下适合建立索引

1）主键自动建立唯一索引

2）频繁作为查询条件的字段（where后面的字段）

3）查询中与其他表关联的字段（各种join on后面的字段）

4）单值/复合索引选择？（高并发下倾向选择复合索引）

5）查询中排序的字段

6）查询中统计或分组的字段

6. 什么情况下不适合建立索引

1）表数据太少

2）频繁更新的字段

3）where后面用不到的字段

※7. 什么时候会出现索引失效

1）like以通配符开头('%abc')会导致索引失效，违反最左前缀法则

最左前缀法则：

顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上。

①如果第一个字段是范围查询需要单独建一个索引；

②在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边；

当创建(a,b,c)复合索引时，想要索引生效的话，只能使用 a和ab、ac和abc三种组合！

实例：以下是常见的几个查询：

mysql>SELECT `a`,`b`,`c` FROM A WHERE `a`='a1' ; //索引生效
mysql>SELECT `a`,`b`,`c` FROM A WHERE `b`='b2' AND `c`='c2'; //索引失效
mysql>SELECT `a`,`b`,`c` FROM A WHERE `a`='a3' AND `c`='c3'; //索引生效，实际上值使用了索引a

2）在索引列上做任何操作(计算、函数、类型转换)，会导致索引失效而转向全表扫描

3）存储引擎不能使用索引中范围条件右边的列，举例：select id，name from student where id > 50 and name = '张三'，会导致name索引失效

4）尽量使用覆盖索引，不要select *

5）MySQL在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描，理由也很简单，B+Tree叶子节点用指针相连且是排好序的，这种数据结构只能解决有序的定值查询，像不等于这种无法利用索引查询。（5.7 版本以后的 is not null 只会降低效率，不会导致索引失效）

6）IS NULL、IS NOT NULL无法使用索引，理由同上

7）字符串不加单引号索引失效

隐式转换-->函数操作

8）用or连接时会导致索引失效

8. 为什么建议InnoDB必须建主键

对于InnoDB来说，如果不手动建主键索引，MySQL底层依然会帮我们创建一个聚集

索引来维护整张表的所有数据，因为B+Tree必须依靠索引才能建立。为什么建议InnoDB

必须建主键呢？因为本身数据库的资源就非常宝贵，我们尽量能手动做的就不要麻烦MySQL

去帮我们维护，说白了就是降低数据库开销。

9. 为什么推荐使用整型主键

我们就拿UUID举个例子，一大串十分长但无具体意义的字符串，回顾上面InnoDB的索引图，是比较两个int型数据快捷呢还是比较两个字符串快捷呢？想都不用想肯定是比较两个int型更具有优势，字符串需要逐位的去比较，如果碰巧两个字符串只有最后一位不一致那不是亏得要死。

10. 为什么推荐使用自增主键

上文B+Tree第三条特性：叶子节点用指针连接，提高区间访问性能。

这样带来了一个好处那就是范围查找，比如一行SQL：select * from table name where id between 1 and 20，MySQL只需要查到索引等于1的位置，然后通过链表往后依次找到20的位置，首尾位置之间就是我们需要查找的结果集。但这样也带来了一个问题，加入我们主键已经插入了1、2、3、4、6、7，这时候我们插入了5，MySQL在维护索引的时候就会打破原有链表顺序，导致链表节点分裂重排，从而消耗性能。

11. 为什么InnoDB非主键索引存储的是主键值

保持一致性，当数据库表进行DML操作时，同一行记录的页地址会发生改变，

因非主键索引保存的是主键的值，无需进行更改。同时还可以节省存储空间，

因为Innodb数据本身就已经汇聚到主键索引所在的B+树上了，如果普通索引还继续再保存一份数据，就会导致有多少索引就要存多少份数据。

5.查询优化

时间优化

之前提到的字段类型小

空间优化

索引（如何防止索引失效）

1、在全值匹配中遵循最左前缀原则
2、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换）
3、不要在作为范围条件的索引列的右边列使用索引
4、尽量使用覆盖索引（只访问索引的查询即索引列和查询列一致），减少使用 select *（5.7 版本以后的不会导致索引失效）
5、不使用 != 或者 <> ，会导致全表扫描（ 5.7 版本以后的只会降低效率，不会导致索引失效）
6、在不允许为 null 的索引字段上，不能使用 is null 或者 is not null 的索引列作为查询条件。（5.7 版本以后的 is not null 只会降低效率，不会导致索引失效）
7、不使用 like以通配符开头（如：'%abc..'）
如何解决 like 以通配符开头导致索引失效？
8、字符串不要忘记加单引号，不然会导致索引失效
9、少用 or ，用它来连接时会索引失效

详情可见：

http://t.csdn.cn/C6EvN

事务(Transaction)

（TCL）

1. 概念

* 如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

2. 数据库引擎

* 概念：在mysql中的数据用各种不同的技术存储在文件（或内存）中

3. 事务的ACID特征

* 原子性（Atomicity）：是不可分割的最小操作单位，要么同时成功，要么同时失败
   * 一致性（Consistency）：保证数据的状态操作前和操作后保持一致
   * 隔离性（Isolation）：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰
   * 持久性（Durability）：当事务提交或回滚后，数据库会持久化的保存数据
-------------------------------------------------------------------------

4. 事务操作步骤

1. 开启事务
2. 编写事务逻辑操作单元
3. 提交事务或回滚事务
-------------------------------------------------------------------------

5. 事务分类

* 隐式事务：没有明显的开启和结束事务的标志
       * insert、update、delete语句本身就是一个事务
   * 显式事务：具有明显的开启和结束事务的标志
       1. 取消自动提交事务：set autocommit=0;
       2. 开启事务：start transaction;
       3. 提交或回滚事务：
           - commit;
           - rollback;
-------------------------------------------------------------------------

6. 事务的隔离级别（面试常考）

* 事务并发问题是如何产生的？
- 当多个事务同时操作同一个数据库的相同数据时

※ * 事务并发问题

- 脏读：一个事务读取到了另外一个事务未提交的数据
       - 不可重复读：同一个事务中，多次读取到的数据不一致
       - 幻读：一个事务读取数据时，另外一个事务进行更新，导致第一个事务读取到了没有更新的数据
   * 处理事务并发问题，设置事务隔离级别

uncommitted 不受约束的

committed 受约束的

repeatable 可重复的

serializable 可串行化的
       - READ UNCOMMITTED（读未提交数据）：不避免
       - READ COMMITTED（读已提交数据）：可以避免脏读
       - REPEATABLE READ(可重复读)：可以避免脏读、不可重复读和一部分幻读
       - SERIALIZABLE（串行化）：可以避免脏读、不可重复读和幻读

事务的隔离级别：
                               脏读不可重复读   幻读
read uncommitted：√               √                   √
read committed： ×               √                   √
repeatable read： ×               ×                   √
serializable × ×                 ×

* 注意：隔离级别从小到大安全性越来越高，但是效率越来越低
mysql中默认第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed
查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;

   * 设置隔离级别
       - set session|global transaction isolation level 隔离级别名;
   * 查看隔离级别
       - select @@tx_isolation;

6.存储过程

1.什么是存储过程？

由MySQL5.0 版本开始支持存储过程。

如果在实现用户的某些需求时，需要编写一组复杂的SQL语句才能实现的时候，那么我们就可以将这组复杂的SQL语句集提前编写在数据库中，由JDBC调用来执行这组SQL语句。把编写在数据库中的SQL语句集称为存储过程。

存储过程：（PROCEDURE）是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是很有好处的。

就是数据库 SQL 语言层面的代码封装与重用。

2.存储过程的基本使用

格式

-- 创建存储过程
delimiter 自定义结束符号  ||  $$
create procedure `数据库名`.`存储过程名`([in , out , inout] 参数名 数据类型。。。)
begin
	sql语句
	//select name from emp where id = ?;
end 自定义结束符号  || $$ 
delimiter ;

-- 调用存储过程
call 存储过程名;

参数

存储过程就类似于Java中的方法，需要先定义，使用时需要调用。存储过程可以定义参数，参数分为IN、OUT、INOUT类型三种类型。

IN类型的参数表示接受调用者传入的数据；
OUT类型的参数表示向调用者返回数据；
INOUT类型的参数即可以接受调用者传入的参数，也可以向调用者返回数据。

定义变量

1.局部变量
delimiter $$
create procedure `mytest`.`test`()
begin
	declare var_name varchar(20) default 'aaa';  -- 声明/定义变量
	set var_name = 'zhangsan'; -- 给变量赋值
	select var_name; -- 输出变量的值
end $$
dilimiter ;
call test();

2.用户变量
delimiter $$
create procedure `mytest`.`test`()
begin
	set @var_name = 'beijing';
	select @var_name;
end $$
dilimiter ;
call test();
select @var_name; -- 外部也是可以使用用户变量

3.系统变量
//系统变量分为全局变量与会话变量
语法：@@global.var_name
查看全局变量：show global variables;
修改全局变量：set @@global.xxx = xxx;

传参

1. in 
delimiter $$
create procedure `mytest`.`test`(in param_testid int)
begin
	select * from user where id = param_testid;
end $$
delimiter ;
call test(1);

2. out
delimiter $$
create procedure `mytest`.`test`(in in_testid int,out out_testname varchar(20))
begin
	select name into out_testname from user where id = in_testid;
end $$
delimiter ;
call test(1,@o_testname);
select @o_testname;

3. inout
delimiter $$
create procedure `mytest`.`test`(inout param_test int)
begin
	set param_test = param_test*10;
end $$
delimiter ;
call test(2);

流程控制语句 +++++++

。。。。。。
*游标
*异常处理

3.后端如何调用触发器

xml方式：

在mybatis当中，调用存储过程

<parameterMap type="savemap" id=“usermap"> 
	<parameter property="name" jdbcType="VARCHAR" mode="IN"/>
	<parameter property="sex" jdbcType="CHAR" mode="IN"/>
	<parameter property="result" jdbcType="VARCHAR" mode="OUT"/>
</parameterMap>

<insert id="saveUserDemo" parameterMap="savemap" statementType="CALLABLE"> 
{call saveuser(?, ?, ?)} 
</insert >

调用数据库管理

HashMap<String, Object> map = new HashMap<String, Object>(); 
	map.put("name", "Jim"); 
	map.put("sex","男");
	userDao.saveUserDemo(map); 
	map.get(“result”);//获得输出参数

注解方式：

// key1和key2都是参数map中的key
// key1是需要传入存储过程的值
// key2是用于接收存储过程返回的值
@Select("call demo('${key1}', #{key2, mode=OUT, jdbcType=VARCHAR})")
@Options(statementType = StatementType.CALLABLE)
String getDemoValue(Map<String, Object> map);

4.为什么要用存储过程

简化对变动的管理。如果表名、列名、或业务逻辑有了变化。只需要更改存储过程的代码。使用它的人不用更改自己的代码。
通常存储过程都是有助于提高应用程序的性能。当创建的存储过程被编译之后，就存储在数据库中，可重复使用。
存储过程有助于减少应用程序和数据库服务器之间的流量。因为应运程序不必发送多个冗长的SQL语句，只用发送存储过程中的名称和参数即可。

5.存储过程的缺点

不易维护，阅读性差
如果使用大量的存储过程，那么使用这些存储过程的每个连接的内存使用量将大大增加。此外，如果在存储过程中过度使用大量的逻辑操作，那么CPU的使用率也在增加，因为MySQL数据库最初的设计就侧重于高效的查询，而不是逻辑运算。
很难调试存储过程。只有少数数据库管理系统允许调试存储过程。不幸的是，MySQL不提供调试存储过程的功能。
开发和维护存储过程都不容易。

开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。这可能导致应用程序开发和维护阶段的问题。

6.为什么存储过程比sql语句效率高？

1.存储过程经过预编译处理而SQL查询没有，SQL语句需要先被数据库引擎处理成低级的指令然后才执行。

2.减少应用程序和数据库服务器之间的流量。

7.触发器

概念：

触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

作用：

触发器经常用于加强数据的完整性约束和业务规则等;

可在写入数据前，强制检验或者转换数据(保证安全性);

触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚。

语法

CREATE TRIGGER trigger_name trigger_time before trigger_event insert
    ON tbl_name FOR EACH ROW trigger_stmt
    
trigger_time是触发程序的动作时间。它可以是BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发。
trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一：
·         INSERT：将新行插入表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。
·         UPDATE：更改某一行时激活触发程序，例如，通过UPDATE语句。
·         DELETE：从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。
tbl_name：表示建立触发器的表名，即在哪张表上建立触发器。
trigger_stmt：表示触发器程序体，可以是一条SQL语句，也可以是BEGIN和END包含的多条语句。即BEFORT INSERT、BEFORT UPDATE、BEFORT DELETE、AFTER INSERT 、AFTER UPDATE、AFTER DELETE。

注意，不能同时在一个表上建立两个形同类型的触发器，因此在一个表上最多可以建立6个触发器。

案例：。。。

new old

在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据。
在UPDATE型触发器中，OLD用来表示原数据，NEW用来表示将要或已经修改为的新数据。
在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据。
值得注意的是，OLD是只读的，而NEW可以在触发器中使用SET赋值，这样不会再次触发触发器，造成循环调用。

触发器优缺点

安全性

触发器有回滚性，保证数据完整

保存用户的操作，存入日志

触发器可以对数据库中的相关表进行连环更新

...

缺点：。。。
面试题*：

什么是存储过程？有哪些优缺点？

什么是触发器？触发器的使用场景有哪些？

MySQL中都有哪些触发器？
存储过程：（PROCEDURE）是事先经过编译并存储在数据库中的一段SQL语句的集合。

优点：1、存储过程可以重复使用，从而可以减少数据库开发人员的工作量。2、存储过程位于服务器上，降低了网络传输的数据量。3、安全性高。

缺点：1、开发调试差。2、可移植性差。3、如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译。4、维护困难。

触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。

使用场景

可以通过数据库中的相关表实现级联更改。

实时监控某张表中的某个字段的更改而需要做出相应的处理。

例如可以生成某些业务的编号。

注意不要滥用，否则会造成数据库及应用程序的维护困难。

mysql触发器有六种，分别是：1、Before Insert；2、After Insert；3、Before Update；4、After Update；5、Before Delete；6、After Delete

8.锁

见http://t.csdn.cn/cgIJm

http://t.csdn.cn/rc4ma

MySQL的InnoDB锁机制分为表级锁和行级锁

① 行级锁行级锁中有共享锁和排它锁。

共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

select * from t_user where id =10 lock in share mode;

排他锁又称为写锁(独占锁)，简称X锁，顾名思义，排他锁就是不能与其他锁并存。

如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，

包括共享锁和排他锁。

select * from t_user where id =10 for update;

MySQL InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型。

实例如下：

select * from t_user where id=10 for update; # 错误
select * from t_user where id=10 lock in share mode; # 错误
select * from t_user where id=1 # 正常获取数据

②InnoDB 中的两个表锁：

意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁；

意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是 InnoDB 自动加的，不需要用户干预。

再强调一下，对于INSERT、UPDATE和DELETE，InnoDB 会自动给涉及的数据加排他锁；对于一般的SELECT语句，InnoDB 不会加任何锁，事务可以通过以下语句显式加共享锁或排他锁。

共享锁：SELECT … LOCK IN SHARE MODE;
排他锁：SELECT … FOR UPDATE;

③优劣

表锁的优势：开销小；加锁快；无死锁表锁的劣势：锁粒度大，并发处理能力低

行锁的劣势：开销大；加锁慢；会出现死锁行锁的优势：锁的粒度小，处理并发的能力强

9. 事务

1. 什么是事务？

事务是一系列的动作，它们综合在一起才是一个完整的工作单元，这些动作必须全部完成，如果有一个失败的话，那么事务就会回滚到最开始的状态，仿佛什么都没发生过一样。

数据库事务是保证在并发情况下能够正确执行的重要支撑，MySQL常见的数据库引擎中支持事务的是InnoDB。

事务就是一系列操作，正确执行并提交，如果中途出现错误就回滚。事务要保证能够正常的执行，就必须要保持ACID特性。

事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态。如果单元中的所有SQL语句均执行成功，则事物被顺利执行。

2.事务的开始和结束

COMMIT 或ROLLBACK 语句
DDL 或DCL 语句（自动提交）
用户会话正常结束
系统异常终了

3. 执行DML需要手动开启和提交事务吗

 SHOW VARIABLES LIKE '%autocommit%';

对于DML数据操作，我们必须要记住提交事务，如果autocommit为1的话，当然就不用我们自己操心了！！数据库会帮我们提交的！！但是在我们的MyBatis持久层框架中，进行DML操作时我们必须要手动开启事务，并且手动提交事务！！因为在MyBatis持久层框架中，它们处理DML语句的时候会自动设置autocommit=0;如果DML中不进行手动提交事务，那么最后事务就会进行回滚。

为什么我们在写SSM项目的时候，不用手动提交呢？

org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(Object, TransactionDefinition)

4. 执行DQL需要手动开启和提交事务吗

。。。

5. 事务的四个特性

① 原子性(atomicity) 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

事务是一个原子操作, 由一系列动作组成。 组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有的操作执行成功，整个事务才提交。

事务中的任何一个数据库操作失败，已经执行的任何操作都必须被撤销，让数据库返回初始状态。

② 一致性(consistency) 事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

一旦所有事务动作完成, 事务就被提交。数据和资源就处于一种满足业务规则的一致性状态，即数据不会被破坏。

比如a+b=100,一个事务改变了a比如增加了a的值，那么必须同时改变b，保证在事务结束以后a+b=100依然成立，这就是一致性。

③ 隔离性(isolation) 事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

在并发数据操作时，不同的事务拥有各自的数据空间，它们的操作不会对对方产生干扰。准确地说，并非要求做到完全无干扰。

数据库规定了多种事务隔离级别，不同的隔离级别对应不用的干扰程度。隔离级别越高，数据一致性越好，但并发行越弱。

比如对于A对B进行转账，A没把这个交易完成的时候，B就不知道A要给他转多少钱。

④ 持久性(durability) 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

数据库管理系统一般采用重执行日志来保证原子性、一致性和持久性。

重执行日志记录了数据库变化的每一个动作，数据库在一个事务中执行一部分操作后发生错误退出，数据库即可根据重执行日志撤销已经执行的操作。对于已经提交的事务即使数据库崩溃，在重启数据库时也能根据日志对尚未持久化的数据进行相应的重执行操作。

6. 事务的隔离机制

一个事务与其他事务隔离的程度称为隔离级别。

数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制,

就会导致各种并发问题。

隔离级别	存在问题
READ UNCOMMITTED	脏读、不可重复读、幻读
READ COMMITTED	不可重复读、幻读
REPEATABLE READ	幻读
SERIALIZABLE	无

① DEFAULT(读提交) 这是一个PlatfromTransactionManager默认的隔离级别，使用数据库默认的事务隔离级别。

另外四个与JDBC的隔离级别相对应。大部分数据库的默认级别都是READ_COMMITTED(读取已提交)。

② READ_UNCOMMITTED（读取未提交）这是事务最低的隔离级别，允许当前事务读取未被其他事务提交的变更。

这种隔离级别会产生脏读，不可重复读和幻读。

产生脏读场景：A事务读取一个字段，但是这个字段被另外一个事务更新却未提交，

再次读取该字段时如果另外一个事务回滚则出现了脏读现象(读到的数据与第一次，

数据库中的数据都不同)。

产生不可重复读场景：A事务读取一个字段，但是这个字段被另外一个事务更新并提交，

再次读取该字段值不一样则出现了不可重复读现象(同一个事务中，不能保证读取的字段值相同)。

产生幻读场景：A事务读取一个字段集合，但是这个表被另外一个事务更新并提交(如插入了几行)，

再次读取该表可能会多几行则出现了幻读现象。

③ READ_COMMITTED（读取已提交) 保证一个事务修改的数据提交后才能被另外一个事务读取，

另外一个事务不能读取该事务未提交的数据。可以避免脏读，但不可重复读和幻读的现象仍然可能出现。

不可重复读

A事务读取一个字段，但是这个字段被另外一个事务更新并提交，再次读取该字段值不一样则出现了不可重复读现象(同一个事务中，不能保证读取的字段值相同)。

举例就是对于一个数A原来是50，然后提交修改成100，这个时候另一个事务在A提交修改之前，
读取到了A是50，刚读取完，A就被修改成100了，这个时候另一个事务再进行读取发现A就突然变成100了

幻读

读取一个字段，但是这个表被另外一个事务更新并提交(如插入了几行)，再次读取该表可能会多几行则出现了幻读现象。

④ REPEATABLE_READ（可重复读) 确保事务可以多次从某行记录的一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新。这种事务隔离级别可以防止脏读，不可重复读，但是可能出现幻读。

它除了保证一个事务不能读取另一个事务未提交的数据外，还保证了在一个事务过程，

读取的数据不会发生变化(即使数据库中的数据在该事务过程中发生了变化)。

//如下代码所示，可重复读意味着两次读取字段A值相同！

public void test(){
	//开启事务
	//读取字段A；
	//此时数据库中A发生了变化；
	//读取字段A；
	//提交事务；
	//关闭事务；
}

幻读

读取一个字段集合，但是这个表被另外一个事务更新并提交(如插入了几行)，

再次读取该表可能会多几行则出现了幻读现象。

⑤ SERIALIZABLE :（可串行化）在并发情况下和串行化的读取的结果是一致的，没有什么不同。这是花费最高代价但是最可靠的事务隔离级别，事务被处理为顺序执行。除了防止脏读，不可重复读外，还避免了幻读。但性能十分低下！

⑥ 什么是脏读、不可重复读和幻读？脏读: 对于两个事务 T1, T2。T1 读取了已经被 T2 更新但还没有被提交的字段。之后, 若 T2 回滚, T1读取的内容就是临时且无效的，也就是脏数据。

不可重复读:对于两个事务 T1, T2。 T1 读取了一个字段, 然后 T2 更新了该字段。之后, T1再次读取同一个字段, 值就不同了。

幻读:事务T1读取一条指定where条件的语句，返回结果集。此时事务T2插入一些新记录，恰好满足T1的where条件。然后T1使用相同的条件再次查询，结果集中可以看到T2插入的记录，这些多出来的新纪录就是幻读。

不可重复读和幻读的区别：

不可重复读重点是在update，即事务前后对比特定数据内容的修改。而幻读是insert和delete，即事务前后数据结果集的对比。

Oracle数据库支持READ COMMITTED(默认) 和 SERIALIZABLE这两种事务隔离级别。所以Oracle不会出现脏读。

MySQL 支持 4 种事务隔离级别：READ_UNCOMMITTED（读取未提交），READ_COMMITTED（读取已提交），REPEATABLE_READ（可重复读-默认）和SERIALIZABLE （可串行化）。

Oracle 默认使用的是READ_COMMITTED。MySQL默认事务隔离级别为 REPEATABLE_READ。

DQL数据查询语言的执行顺序

SELECT        ⑤
       字段
FROM ①
       表名
WHERE ②
       条件列表
GROUP BY ③
       分组字段列表
HAVING ④
       分组后条件列表
ORDER BY ⑥
       排序字段列表
LIMIT ⑦
       分页参数

SELECT                                 ⑤
        字段
FROM           ①
        表名
WHERE             ②
        条件列表
GROUP BY             ③
        分组字段列表
HAVING                         ④
        分组后条件列表
ORDER BY                                 ⑥                   
        排序字段列表
LIMIT                                                 ⑦
        分页参数

王小小鸭

关注

3
点赞
踩
16

收藏

觉得还不错? 一键收藏
0
评论
MYSQL知识点补充

在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用指向数据，这样就可以在这些数据结构上实现高效查找，这些数据结构就是索引。简单来说索引的出现就是为了提高数据的查询效率，就像书的目录一样。在书籍中，用户不必翻阅完整个书就能根据目录迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。
复制链接

扫一扫

专栏目录