不会吧？不会吧？不会真有人不知道这份数据库“真经”吧？

数据库“真经”

---

基本概念

1. mysql是一个处理文件的软件，可以将数据库看作是一个文件夹，一个表看作是excel表或者是一个文件

2. 结构化查询语言 (Structured Query Language)，具有定义、查询、更新和控制等多种功能，是关系数据库的标准语言

3. sql分类：

   1. 数据操纵语言DML Data Manipulation Language，操作的对象主要是数据 :

      SELECT INSERT UPDATE DELETE

   2. 数据定义语言DDL Data Definition language，操作的对象主要是表 :

   CREATE ALTER DROP RENAME TRUNCATE

   3. 数据控制语言DCL Data Control Language ，操作的对象主要是角色:
      GRANT REVOKE

   4. Transaction，操作的对象主要是一次事务:

      COMMIT ROLLBACK SAVEPOINT

4. 关系型数据库：sql、mysql、sql server、sqlite、orcle、db2等，非关系型数据库：mongodb、redis等

基础操作

数据库的操作：

1. 创建用户

   create user name identified by password;

2. 更改密码

   update user set password=password("1123") where user="user";

3. 授予权力
   grant powers on tablename/databasename to username;

   grant all privileges on userInfo to user0;授予用户对于表的所有权限

4. 撤销权力
   revoke powers on tablename/databasename from username;

5. 创建

   create database test0 character set utf8;`

6. 删除

   drop database name;

表的操作

1. 增

   create table name{
   ......
   }engine=innodb charactor set utf8mb4;
   

2. 删

   1. 删表

      drop table name;

   2. 删数据

      1. 全部

         delete from table;

         truncate table name;

      2. 单条

         delete from table where ...删除后再次插入的话，若有自增的字段则会接着删除字段的值

3. 查

   select * from tablename;`

4. 改

   update name set columns = values;

5. 查看表得相关属性：

   desc tbname; 查看表得属性

6. 表重命名

   rename old_name to new _name要求是表的所有者

数据的操作

1. 增

   insert into tablename(columns...) values(..),(...),...;

2. 删

   delete from tablename where xxx;

3. 改

   update tablename set column = xxx where xxx;

4. 查

   select * from tablename;

字段的操作

1. 增

   alter table name add column type;

2. 删

   alter table name drop column;

3. 改

   alter table name modify(column type);

数据约束

1. 整数

   1. int
   2. tinyint
   3. largeint

2. 浮点数

   1. float
   2. double
   3. decimal

3. 字符串

   1. char
   2. varchar
   3. text
   4. 关于char和varchar：在储存效率方面varchar更高，在查找的情况下char效率更高

4. 日期

   1. time
   2. date
   3. datatime

5. 集合（表示字段可以取其中到的元素的组合）

   create table name(
   columns set(...)
   )
   

6. 枚举（表示只能用其中的一个值）

   create table name(
   columns enum(...)
   )
   

7. 外键

   create table tb1(
   constraint foreign_key_name foreign key(column_name) references other_table_name(column_name
   )
   

8. 联合主键，主键只有一个，但是可以由多个字段共同组成。

   create table name(
   id int...,
   name ...;
   primery key(id, name)
   )
   

9. 联合外键

   create table name(
   id ...,
   name  ...,
   primery key (id, name),
   condtraint fk_name foreign key(id,name) references other(id, name)
   )
   

10. 唯一索引

    1. 单字段索引

       unique unique_name (column);

    2. 联合索引， 表示索引的组合是唯一的

       unique unique_name (column1, column2);

11. 索引与主键

    1. 都可以加快查找速度，且都是唯一的，都可以是联合的
    2. 索引可以为空， 主键不能

添加注释

1. 为表添加注释
   comment on table emp is "雇员表";
2. 为列添加注释
   comment on column emp.Empno is "雇员工号";

查询结果去重distinct

1. distinct必须放在开头

2. 多字段
   每个字段不一样才去重

3. select distinct name from userinfo;
   

条件比较

1. ＝，!＝ ,<>， <,>,<=,>=,any,some,all

   -- any大于只查询的最小值
   -- all大于子查询的最大值
   -- some大于子查询中介于最大值最小值之间的值
   select * from class0 where class0.age > some (select age from class1);
   select * from class0 where class0.age > any (select age from class1);
   select * from class0 where class0.age > all (select age from class1);
   

2. is null,is not null：是否为空

   select * from class1 where name is null;
   select * from class1 where name is not null;
   

3. between x and y：筛选查询结果的某个字段值是否存在范围内

   select * from class1 where age between 18 and 20;
   

4. in（list）， not in（list）：筛选查询结果的某个字段值是否存在范围内

   select * from class1 where class1.age in (select age from class0);
   select * from class1 where class1.age not in (select age from class0);
   

5. exists（sub－ query）：查询的结果是否存在在子查询中

   select distinct age from class0 where exists(select age from class1);
   

6. like _ ,%,escape ‘\‘ _% escape ‘\’

逻辑复合条件

1. not，（and， or） and优先级最高

   select * from class1 where name like "老%" and age > 21;
   select * from class1 where name like "老%" or age > 21;
   

2. sql优化问题

   1. AND: 把检索结果较少的条件放到后面
   2. OR: 把检索结果较多的条件放到后面

like

1. 条件表达式中字符串匹配操作符是‚like
   1. %通配符表示任意字符出现任意次数
   2. _通配符表示任意字符出现一次
2. 技巧和注意事项：
3. 不能过度使用通配符。如果其他操作符能达到目的，就不要使用通配符。
4. 确实需要使用通配符时，除非绝对必要，否则不要把通配符用到搜索模式最开始处，因为这样搜索起来是最慢的

order by

1. 按照单个列排序：order by col

   select * from class1 order by age;
   

2. 降序和升序：order by col desc (asc)

   select * from class1 order by age desc;
   

3. 按多个列排序（优先级）：order by col1 desc(asc), col2 desc(asc)

select * from class1 order by age desc, id asc;

并集，全集，交集，差集

1. 并集（union）：去重

   select age from class1 union select age from class0;
   

2. 全集（union all）

   select age from class1 union all select age from class0;
   

3. 交集

   select age from class1 where age in (select age from class0);
   

4. 差集

   select age from class1 where age not in (select age from class0);
   

SQL 函数

函数一般是在数据上执行的，它给数据的转换和处理提供了方便。只是将取出的数据进行处理，不会改变数据库中的值

Sql函数可以分为组函数和单行函数。

1. 组函数又被称作聚合函数，用于对多行数据进行操作，并返回一个单一的结果，组函数仅可用亍选择列表戒查询的having子句
2. 单行函数对单个数值进行操作，并返回一个值

单行函数的分类

1. 字符函数

   1. concat（string1， string2）连接两个字符串
   2. initcap（string） string中每个单词首字母大写
   3. Lower(string) 以小写形式返回string
   4. lpad,rpad 填充字符型数据
   5. ltrim/rtrim （string1,string2）
   6. trim(A from B)
   7. Substr（） 提取字符串的一部分substr（string， 1， 2）
   8. upper(string）以大写形式返回string
   9. Instr（）字符串出现的位置, instr（ string ，’A‘）
   10. Length（）字符串长度 ，一个汉字3个长度

2. 数字函数

   数字函数以NUMBER类型为参数返回NUMBER值

   1. round（number， n） 返回四舍五入后的值
   2. trunc（number， n）
   3. mod（x， y）求余数
   4. ceil()上取整
   5. floor()下取整

3. 日期和时间函数

   1. Add_months(d,x) 返回加上x月后的日期d的值
   2. LAST_DAY(d) 返回的所在月份的最后一天
   3. Months_between(date1,date2) 返回date1和date2之间月的数目

4. 转换函数

   1. to_char
   2. to_number
   3. to_date

单行函数嵌套

1. 单行函数可被嵌入到任何层
2. 嵌套函数从最深层到最低层求值

组函数

组函数基亍多行数据返回单个值

1. avg()：返回某列的平均值
2. min()：返回某列的最小值
3. max()：返回某列的最大值
4. sum()：返回某列值的和
5. count()：返回某列的行数
6. 组函数仅在选择列表和Having子句中有效

数据分组

1. 创建分组
   1. group by 子句
      Group by 子句可以包含任意数目的列
   2. 除组函数语句外， select语句中的每个列都必须在group by 子句中给出
   3. 如果分组列中具有null值，则null将作为一个分组返回。如果列中有多行null值，他们将
      分为一组
   4. Group by 子句必须出现在where子句之后， order by 子句之前
2. 过滤分组（having子句)
   1. Where过滤行， having过滤分组
   2. Having支持所有where操作符
3. 分组排序
   1. 一般在使用group by 子句时，应该也给出order by子句
4. 出现在SELECT列表中的字段，如果出现的位置不是在组函数中，那么必须出现在GROUP BY子句中
5. 不能在 WHERE 子句中使用组函数.不能在 WHERE 子句中限制组. 使用Having对分组进行限制

Select子句顺序

1. Sql语句执行过程：
   1. 读取from子句中的基本表、视图的数据， [执行笛卡尔积操作]
   2. 选取满足where子句中给出的条件表达式的元组
   3. 按group子句中指定列的值分组，同时提取满足Having子句中组条件表达式的那些组
   4. 按select子句中给出的列名戒列表达式求值输出
   5. Order by子句对输出的目标表进行排序

多表查询

交叉连接

1. CROSS JOIN产生了一个笛卡尔积，就象是在连接两个表格时忘记加入一个WHERE子句一样

   select emp.empno, emp.ename, emp.deptno, dept.loc
   from emp ， dept;
   

2. 可以使用CROSS JOIN 来达到相同的结果

select emp.empno, emp.ename, emp.deptno, dept.loc
from emp cross join dept;

自然连接

1. NATURAL JOIN子句基亍两个表中列名完全相同的列产生连接

   1. 两个表有相同名字的列
   2. 数据类型相同
   3. 从两个表中选出连接列的值相等的所有行

   select *
   from emp natural join dept
   Where deptno = 10;
   

2. 自然连接的结果不保留重复的属性

using创建连接

select e.ename,e.ename,e.sal,deptno,d.loc
from emp e join dept d using(deptno)
where deptno=20;

using子句引用的列在sql任何地方丌能使用表名戒者别名做前缀,同样适合natural子句

使用on创建连接

1. 自然连接的条件是基亍表中所有同名列的等值连接

2. 为了设置任意的连接条件戒者指定连接的列，需要使用ON子句

3. 连接条件不其它的查询条件分开书写

4. 使用ON 子句使查询语句更容易理解

   select ename,dname
   from emp join dept on emp.deptno=dept.deptno
   where emp.deptno=30;
   

左外连接

在LEFT OUTER JOIN中，会返回所有左边表中的行，即使在右边的表中没有可对应的列值

select e.ename,d.deptno,d.dname
from dept d
left outer join emp e
on e.deptno=d.deptno;

右外连接

RIGHT OUTER JOIN中会返回所有右边表中的行，即使在左边的表中没有可对应的列值

select e.ename,d.deptno,d.dname
from emp e
right outer join dept d
on e.deptno=d.deptno;

分页

一般情况

select *
from (select rownum rn, t2.ename, t2.sal
from (select e.ename, e.sal from emp e order by e.sal desc) t2
) t1
where t1.rn >= 6
and t1.rn <= 10;
--select * from t_user limit 0,10; limitstartRow,pageSize

性能优化

在使用select * from tablenem limit start, step; 当查询时，会遍历数据库，找到start，当数据太大时就会很慢，解决方案：

1. 索引表中扫：

   select * from tablename where id > ? in ( select id from tablename limit start, step) ;--也会很慢
   

2. 最优方案：

   select * from tablename where id > ? limit step;--通过在主键中扫描，达到最快
   

3. 页面只有上一页， 下一页max_id min_id

   1. 下一页：

      select * from tablename where id > max_id limit 10;
      

   2. 上一页：

      select * from tablename where id < min_id order by id desc limit 10;
      

4. 当有多页时，上一页 192 193 196 197 198 199 下一页

   select * from tablename where id in (select id from tablename where id > max_id limit 30) as N order by N.id desc limit 10;
   

5. between…and…只适用于连续的id索引

表设计

视图

基础概念

1. 视图(view)，也称虚表, 不占用物理空间，这个也是相对概念，因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。 每次使用的时候, 只是重新执行SQL
2. 视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生
3. 视图的定义存在数据库中，与此定义相关的数据并没有再存一份于数据库中。通过视图看到的数据存放在基表中
4. 视图看上去非常象数据库的物理表，对它的操作同任何其它的表一样。当通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表数据的改变也会自动反映在由基表产生的视图中

创建视图

在CREATE VIEW语句后加入子查询

CREATE [OR REPLACE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH READ ONLY];

create or replace view v$_emp_dept
as
select emp.deptno,ename,dname from emp
join dept on emp.deptno=dept.deptno
with read only

使用视图

在查询时，不需要再写完全的Select查询语句，只需要简单的写上从视图中查询的语句就可以了

当视图不再需要的时候，用“drop view” 撤销。删掉视图不会导致数据的丢失，因为视图是基于数据库的表之上的一个查询定义

事务处理

基本概念

事务处理

1. 事务（Transaction）是一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位，是数据库环境中的逻辑工作单位。 事务是为了保证数据库的完整性

2. 事务不能嵌套

3. 一个Transaction起始于一条DML(Insert、 Update和Delete )语句，结束于以下的几种情况：

   1. 用户显式执行Commit语句提交操作或Rollback语句回退
   2. 当执行DDL(Create、 Alter、 Drop)语句事务自动提交
   3. 用户正常断开连接时， Transaction自动提交
   4. 系统崩溃或断电时事务自动回退

Commit & Rollback

1. Commit表示事务成功地结束，此时告诉系统，数据库要进入一个新的正确状态，该事务对数据库的所有更新都以交付实施。每个Commit语句都可以看成是一个事务成功的结束，同时也是另一个事务的开始
2. Rollback表示事务不成功的结束，此时告诉系统，已发生错误，数据库可能处在不正确的状态，该事务对数据库的更新必须被撤销，数据库应恢复该事务到初始状态。每个Rollback语句同时也是另一个事务的开始
3. 一旦执行了commit语句，将目前对数据库的操作提交给数据库（实际写入DB），以后就不能用rollback进行撤销
4. 执行一个 DDL ， dcl语句或从 SQL*Plus正常退出，都会自动执行commit命令
5. savepoint：保存点，即相当于一个执行截断点

delete from userInfo where name="xxx";
savepoint sp;
delete from userInfo where name="xxxx";
rollback to sp;
commit;
-- 表示sp前面的成功执行。后面的不执行

事务的四个特性：ACID

事务四大特征：原子性，一致性，隔离性和持久性

1. 原子性（Atomicity）
   一个原子事务要么完整执行，要么干脆不执行。这意味着，工作单元中的每项任务都必须正确执行。如果有任一任务执行失败，则整个工作单元或事务就会被终止。即此前对数据所作的任何修改都将被撤销。如果所有任务都被成功执行，事务就会被提交，即对数据所作的修改将会是永久性的
2. 一致性（Consistency）
   一致性代表了底层数据存储的完整性。它必须由事务系统和应用开发人员共同来保证。事务系统通过保证事务的原子性，隔离性和持久性来满足这一要求; 应用开发人员则需要保证数据库有适当的约束(主键，引用完整性等)，并且工作单元中所实现的业务逻辑不会导致数据的不一致(即，数据预期所表达的现实业务情况不相一致)。例如， 在一次转账过程中，从某一账户中扣除的金额必须与另一账户中存入的金额相等。支付宝账号100 你读到余额要取，有人向你转100 但是事物没提交（这时候你读到的余额应该是100，而不是200） 这种就是一致性
3. 隔离性（Isolation）
   隔离性意味着事务必须在不干扰其他进程或事务的前提下独立执行。换言之，在事务或工作单元执行完毕之前，其所访问的数据不能受系统其他部分的影响。
4. 持久性（Durability）
   持久性表示在某个事务的执行过程中，对数据所作的所有改动都必须在事务成功结束前保存至某种物理存储设备。这样可以保证，所作的修改在任何系统瘫痪时不至于丢失
5. 所有的这些都是为了保证数据的一致性

提交或回滚前数据的状态

1. 以前的数据可恢复
2. 当前的用户可以看到DML操作的结果
3. 其他用户不能看到DML操作的结果
4. 被操作的数据被锁住,其他用户不能修改这些数据

提交后数据的状态

1. 数据的修改被永久写在数据库中.
2. 数据以前的状态永久性丢失.
3. 所有的用户都能看到操作后的结果
4. 记录锁被释放,其他用户可操作这些记录

回滚后数据的状态

语句将放弃所有的数据修改

1. 修改的数据被回退
2. 恢复数据以前的状态
3. 行级锁被释放

事务测试

1. 打开mysql的命令行，将自动提交事务给关闭

--查看是否是自动提交 1表示开启，0表示关闭
select @@autocommit;
--设置关闭
set autocommit = 0;

2. 数据准备

--创建数据库
create database tran;
--切换数据库 两个窗口都执行
use tran;
--准备数据
 create table psn(id int primary key,name varchar(10)) engine=innodb;
--插入数据
insert into psn values(1,'zhangsan');
insert into psn values(2,'lisi');
insert into psn values(3,'wangwu');
commit;

3. 测试事务

--事务包含四个隔离级别：从上往下，隔离级别越来越高，意味着数据越来越安全
read uncommitted; 	--读未提交
read commited;		--读已提交
repeatable read;	--可重复读
serialize		--序列化执行，串行执行
--产生数据不一致的情况：
脏读
不可重复读
幻读

隔离级别	异常情况		异常情况
读未提交	脏读	不可重复读	幻读
读已提交		不可重复读	幻读
可重复读			幻读
序列化

4. 测试1：脏读 read uncommitted，当一个事务在更新数据时，另一个事务读取到了该事务还未commit的事务

set session transaction isolation level read uncommitted;
A:start transaction;
A:select * from psn;
B:start transaction;
B:select * from psn;
A:update psn set name='msb';
A:selecet * from psn
B:select * from psn;  --读取的结果msb。产生脏读，因为A事务并没有commit，读取到了不存在的数据
A:commit;
B:select * from psn; --读取的数据是msb,因为A事务已经commit，数据永久的被修改

更新数据：

插入：

5. 测试2：当使用read committed的时候，就不会出现脏读的情况了，当时会出现不可重复读的问题，即在同一次事务中读到不同的值，就好比取钱的时候，银行卡插进去是1000元，等了几分钟就只有几百元了

set session transaction isolation level read committed;
A:start transaction;
A:select * from psn;
B:start transaction;
B:select * from psn;
--执行到此处的时候发现，两个窗口读取的数据是一致的
A:update psn set name ='zhangsan' where id = 1;
A:select * from psn;
B:select * from psn;
--执行到此处发现两个窗口读取的数据不一致，B窗口中读取不到更新的数据
A:commit;
A:select * from psn;--读取到更新的数据
B:select * from psn;--也读取到更新的数据
--发现同一个事务中多次读取数据出现不一致的情况

6. 测试3：当使用repeatable read的时候(按照上面的步骤操作)，就不会出现不可重复读的问题，但是会出现幻读的问题，也就是一个事务完成添加数据的操作时，另一个事务感知到了，但是并不会展示，仅仅感知

set session transaction isolation level repeatable read;
A:start transaction;
A:select * from psn;
B:start transaction;
B:select * from psn;
--此时两个窗口读取的数据是一致的
A:insert into psn values(4,'sisi');
A:commit;
A:select * from psn;--读取到添加的数据
B:select * from psn;--读取不到添加的数据
B:insert into psn values(4,'sisi');--报错，无法插入数据
--此时发现读取不到数据，但是在插入的时候不允许插入，出现了幻读，设置更高级别的隔离级别即可解决

7. 总结：以上问题都没有保证数据的一致性

约束 constraint

基本概念

1. 当我们创建表的时候，同时可以指定所插入数据的一些规则，比如说某个字段不能为空值，某个字段的值（比如年龄）不能小于零等等，这些规则称为约束。约束是在表上强制执行的数据校验规则
2. 支持下面五类完整性约束
   1. NOT NULL非空
   2. UNIQUE Key 唯一键
   3. PRIMARY KEY 主键
   4. FOREIGN KEY 外键
   5. CHECK 自定义检查约束

创建约束的时机

1. 在建表的同时创建
2. 建表后创建

分类

约束从作用上分类，可以分成两大类：

1. 表级约束：可以约束表中的任意一列或多列。可以定义除了Not Null以外的任何约束
2. 列级约束：只能约束其所在的某一列。可以定义任何约束

列级约束

列级约束: 从形式上看，在每列定义完后马上定义的约束，在逗号之前就定义好了

carete table parent(c1 number primary key );
create table child (c number primary key , c2 number references parent(c1));  

表级约束

create table child( c number , c2 number , primary key (c2), foreign key(c2) references parent(c1));  

具体分类

主键约束（ PRIMARY KEY）

1. 主键约束是数据库中最重要的一种约束。在关系中，主键值不可为空，也不允许出现重复，即关系要满足实体完整性规则

2. 注意：

   1. 主键从功能上看相当于非空且唯一
   2. 一个表中只允许一个主键
   3. 主键是表中能够唯一确定一个行数据的字段
   4. 主键字段可以是单字段或者是多字段的组合
   5. Oracle为主键创建对应的唯一性索引

3. 建议命名：

   1. 约束_表名_字段

4. 主键可用下列两种形式之一定义

   1. 主键子句：在表的定义中加上如下子句 primary key(列)

   2. 主键短语：在主属性的定义之后加上primary key字样

      create table t3(
      id number(4),
      constraint t3_pk primary key(id)
      )
      

非空约束（NOT NULL）

1. 确保字段值不允许为空

2. 只能在字段级定义

   CREATE TABLE employees(
   employee_id NUMBER(6),
   name VARCHAR2(25) NOT NULL,
   salary NUMBER(8,2),
   hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL
   )
   

唯一性约束（UNIQUE）

1. 唯一性约束条件确保所在的字段或者字段组合不出现重复值

2. 唯一性约束条件的字段允许出现空值

   CREATE TABLE employees(
   id NUMBER(6),
   name VARCHAR2(25) NOT NULL UNIQUE,
   email VARCHAR2(25),
   salary NUMBER(8,2),
   hire_date DATE NOT NULL,
   CONSTRAINT emp_email_uk UNIQUE(email)
   );
   

CHECK 约束

1. Check约束用于对一个属性的值加以限制

2. 在check中定义检查的条件表达式，数据需要符合设置的条件

   create table emp3
   ( id number(4) primary key,
   age number(2) check(age > 0 and age < 100),
   salary number(7,2),
   sex char(1),
   constraint salary_check check(salary > 0)
   )
   

关系模型的三类完整性规则

为了维护数据库中的数据与现实世界的一致性，关系数据库的数据与更新操作必须遵循下列三类完整性规则：

1. 实体完整性规则：这条规则要求关系中在组成主键的属性上不能有空值
2. 参照完整性规则：这条规则要求“不引用不存在的实体”。例如： deptno是dept表的主键，而相应的属性也在表emp中出现，此时deptno是表emp的外键。在emp表中， deptno的取值要么为空，要么等于dept中的某个主键值
3. 用户定义的完整性规则 ：用户定义的完整性规则反应了某一具体的应用涉及的数据必须满足的语义要求

外键约束（ FOREIGN KEY）

1. 外键是表中的一个列，其值必须在另一表的主键或者唯一键中列出
   作为主键的表称为“主表”，作为外键的关系称为“依赖表”

2. 外键参照的是主表的主键或者唯一键

3. 对于主表的删除和修改主键值的操作，会对依赖关系产生影响，以删除为例：当要删除主表的某个记录（即删除一个主键值，那么对依赖的影响可采取下列3种做法：

   1. RESTRICT方式：只有当依赖表中没有一个外键值与要删除的主表中主键值相对应时，才可执行删除操作
   2. CASCADE方式：将依赖表中所有外键值与主表中要删除的主键值相对应的记录一起删除
   3. SET NULL方式：将依赖表中所有与主表中被删除的主键值相对应的外键值设为空值

   FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO);
   

   约束的添加和撤销

   约束的添加和撤销

   1. 添加:

      alter table tablename add constraint con_name unique(col) ;

   2. 删除

      alter table tablename drop constraint com_name [cascade] ;

   索引

   基本概念

   1. 索引是为了加快对数据的搜索速度而设立的。索引是方案（schema）中的一个数据库对象,与表独立存放
   2. 索引的作用：在数据库中用来加速对表的查询,通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O
   3. Sql中的索引是非显示索引，也就是在索引创建以后，在用户撤销它之前不会在用到该索引的名字，但是索引在用户查询时会自动起作用
   4. 索引的创建有两种情况：
      1. 自动: 当在表上定义一个PRIMARY KEY 或者UNIQUE 约束条件时
      2. 手动: 用户可以创建索引以加速查询
   5. 开发中使用索引的要点：
      1. 索引改善检索操作的性能， 但降低数据插入、修改和删除的性能。在执行这些操作时， DBMS必须动态地更新索引
      2. 索引数据可能要占用大量的存储空间
      3. 并非所有的数据都适合于索引。唯一性不好的数据（如省）从索引的到的好处不比具有更多可能值的数据（如姓名）从索引得到的好处多
      4. 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能是索引的备选
      5. 可以在索引中定义多个列（如省加城市），这样的索引只在以省加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处

   使用

   创建

   CREATE INDEX index ON table (column[, column]...);

   删除

   DROP INDEX upper_last_name_idx;

   进阶

   不能命中索引的几种方式

   1. like

   2. 调用函数

   3. or

      select * from tablename where index_column = ? or not_column_name=?--不会走索引
      
      select * from tablename where index_column1 = ? or not_column_name=? and index_column2=?--会直接屏蔽or...and之间的走
      
      

   4. 索引!=(主键除外)

   5. > (主键除外)

   6. 满足的条件的值的类型与声明索引时的类型不一致（主键除外）

   7. order by(主键除外)

注意事项：

1. 避免使用select
2. count(1)或count(列)代替count()
3. 创建表时尽量使用char代替varchar
4. 表得字段顺序固定长度的字段优先
5. 组合索引代替多个单列索引（经常使用多个条件查询时）
6. 尽量使用短索引
7. 使用连接(join)代替子查询
8. 连表注意类型需要一致
9. 索引散列值（重复少）不适合建立索引。例如：性别。

触发器

1. 首先：delimiter 设置sql语句的结束标志，sql语句默认是“；”结束也就是：delimiter ;，可以修改为：delimiter //表示当遇到“//”，语句才算结束，可以恢复设置：delimiter ;

2. trigger用于在增、删、改的前后触发一系列动作

   create trigger t1 on  tablename before  insert for each row;
   create trigger t1 on tablename after insert for each row;
   

3. new/old用于表示插入、删除、更新操作的行的数据

   1. new表示新插入的数据行

      delimiter //
              create trigger t1 befor insert on tablename for each row
              begin
                  insert into t2(name) values(new.name);
              end //
              delimiter ;
      

   2. old表示即将被删除的数据行

      delimiter //
              create trigger t1  befor delete on tablename for each row
              begin
                  insert into t2(name) values(old.name);
              end //
              delimiter ;
      

存储过程

用一个别名来描述多个sql语句的执行过程

1. 最简单的使用

   delimiter //
       create PROCEDURE p1()
       begin
       select * from userinfo;
       end //
       delimiter ;
   
       call p1();
   

2. 传参（in， out， inout）

   1. in 表示传入的参数， 可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内

      delimiter //
              create procedure p2(
                  in v1 int
                  )
                  begin
                  set v1 = 2 * v1;
                  select v1;
                  end //
      
              delimiter ;
      
              call p2(19);
      
              ----------------
              delimiter //
              create procedure p6(
                  in v int
                  )
                  begin 
                  set v = 10;
                  select v;
                  end //
              delimiter ;
      
              call p6(10);
      
              set @v4 = 0;
              call p6(@v4);
              select @v4;  -- 此时@v4依然为0
      

   2. out 表示存储执行存储过程的返回结果，且参数只能是一个变量，且只能对其赋值（函数执行完以后也生效），不能在函数内获取其值

      delimiter //
              create procedure p3(
                  out v int
                  )
                  begin
                  set v = 10;
                  end //
              delimiter ;
      
              set @v1 = 0; # @varible_name 类似于定义一个局部变量，跟session一样
              call p3(@v1); 
              select @v1;
      
              -----------------------------
              delimiter //
              create procedure p4(
                  out v int
                  )
                  begin 
                  set v = v + 5;  -- 这儿有问题，不能这样，只能直接赋值
                  select v;
                  end //
              delimiter ;
              -- 这段代码是有问题的。
              delimiter //
              create procedure p5(
                  out v int
                  )
                  begin 
                  set v = 5; -- 对的
                  select v;
                  end //
              delimiter ;
      
              set @v3 = 0;
              call p5(@v3);
              
      create procedure name(
                  in v1 int,
                  out v2 int
              )
              begin
              set v2 = v1;
              select v2;
              end
      
              call(num, num)
      

   3. inout 表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完）

      delimiter //
              create procedure p7(
                  inout v int
                  )
                  begin
                  set v = 9999;
                  select v;
                  end //
              delimiter ;
      
              call p7(10);
      
              set @v5 = 0;
              call p7(@v5);
              select @v5;
      
              ------------------------------------
      
              delimiter //
              create procedure p8(
                  inout v int
                  )
                  begin
                  set v = v + 9999;
                  select v;
                  end //
              delimiter ;
      
              set @v6 = 1;
              call p8(@v6);
              select @v6;
      

3. 总结：

   1. in只可以读取值/变量，不能更改
   2. out不能读，可以更改
   3. inout既可以读又可以更改

往期精彩文章

1. 爆肝！！！！JavaSE知识点1.3w字总结
2. 关于Java高并发编程你需要知道的“升段攻略”