数据库基本知识

1 sql简介 

2 数据库三范式

3 视图

4 索引

1 什么是索引

2 索引的原理

3 索引的类型？（3种）

4创建索引的两种方式

5 语句中创建增加索引

6 为什么要创立索引呢（优点）？

7 建立方向索引的不利因素（缺点）

8 创建方向索引的准则?

9 不适合建立索引的情形

10 数据库索引的实现原理

5 存储过程

6 触发器

7 游标

8 数据库约束

9 封锁协议

1 封锁类型

2 封锁粒度

3 封锁协议

4 两段锁协议

5 乐观锁和悲观锁(需要再查 )

10 sql 常用

1 创建表

2 插入

3 更新

4 删除

5 添加列

6 删除列

7 删除表

8 DISTINCT

9 LIMIT

10 排序

11 过滤

12 while

13 AND OR

14 in

11 事务

12 权限管理

1 sql简介 

DML（data manipulation language）是数据操纵语言：它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言，增删查改。

DDL（data definition language）是数据定义语言：DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

DCL（Data Control Language）是数据库控制语言：是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。

2 数据库三范式

第一范式：数据库表中每个字段不可再分

第二范式：满足第一范式并且表中的非主键字段都依赖主键字段。

第三范式：满足第二范式并且表中的非主键字段必须不传递依赖于主键字段。

四、BCNF： 在满足第三范式的基础上，且不允许主键的一部分被另一部分或其它部分决定。

详解

第一范式 1NF

1）有主键，且主键不能为空。

2）字段不能再分。

示例：（以下例子 不满足 第一范式）

/*学号      年龄        信息*/
  Sno       Sage        Sinfo
  1001      19          陕西省西安市，电话：10086
  1001      20          陕西，宝鸡

主键：Sno

不满足原因：1）主键重复。2）Sinfo字段可以再分。

第二范式 2NF

1）满足第一范式。

2）表中的每一个非主属性，必须完全依赖于本表码。

3）只有当一个表中，主码由两个或以上的属性组成的时候，才会出现不符合第二范式的情况。

示例：（以下例子 不符合 第二范式）

/*学号    课程号     得分    课程名*/
  Sno      Cno      Score    Sname
  1001     001       99      数据库

主键： Sno,Cno

不满足原因：Sname不完全依赖于码，课程名部分依赖于码Cno。

三、第三范式 3NF

1）满足第二范式。

2）非主属性不能传递依赖于码。

示例：（以下例子 不符合 第三范式）

/*学号     系别     系主任*/
  Sno     Sdept     Shead
  1001   计算机系    张三

主键：Sno

不满足原因：Shead传递依赖于码，Sno->Sdept->Shead，可得Sno->Shead。

四、BCNF

1）满足第三范式。

2）所有非主属性对每一个码都是完全函数依赖。

3）所有的主属性对每一个不包含它的码，也是完全函数依赖。

4）没有任何属性完全函数依赖于飞码的任何一组属性。

3 视图

视图实际上是在数据库中通过Select查询语句从多张表中提取的多个表字段所组成的虚拟表。

1）视图并不占据物理空间，所以通过视图查询出的记录并非保存在视图中，而是保存在原表中。

2）通过视图可以对指定用户隐藏相应的表字段，起到保护数据的作用。

3）在满足一定条件时，可以通过视图对原表中的记录进行增删改操作。

4）创建视图时，只能使用单条select查询语句

CREATE VIEW myview ASSELECT Concat(col1, col2) AS concat_col, col3*col4 AS count_colFROM mytableWHERE col5 = val;

4 索引

1 什么是索引

MySQL官方定义：索引（Index）是帮助MySQL高效获取数据的数据结构。

数据库查询是数据库的主要功能之一，最基本的查询算法是顺序查找（linear search）时间复杂度为O(n)，显然在数据量很大时效率很低。优化的查找算法如二分查找（binary search）、二叉树查找（binary tree search）等，虽然查找效率提高了。但是各自对检索的数据都有要求：二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织）。所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构。这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。MySQL数据库几个基本的索引类型：普通索引、唯一索引、主键索引、全文索引。

聚集索引（主键索引）：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

非聚集索引（普通索引、唯一索引、全文索引）：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

1）索引分为：聚集索引、非聚集索引、唯一索引等。

2） 一张表可以有多个唯一索引和非聚集索引，但最多只能有一个聚集索引。

3）索引可以包含多列。

4）合理的创建索引能够提升查询语句的执行效率，但降低了新增、删除操作的速度，同时也会消耗一定的数据库物理空间。

2 索引的原理

对要查询的字段建立索引其实就是把该字段按照一定的方式排序；建立的索引只对该字段有用，如果查询的字段改变，那么这个索引也就无效了，比如图书馆的书是按照书名的第一个字母排序的，那么你想要找作者叫张三的就不能用改索引了；还有就是如果索引太多会降低查询的速度。

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

首先明白为什么索引会增加速度，DB在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。那么在任何时候都应该加索引么？

这里有几个反例：

1、如果每次都需要取到所有表记录，无论如何都必须进行全表扫描了，那么是否加索引也没有意义了。

2、对非唯一的字段，例如“性别”这种大量重复值的字段，增加索引也没有什么意义。

3、对于记录比较少的表，增加索引不会带来速度的优化反而浪费了存储空间，因为索引是需要存储空间的，而且有个致命缺点是对于update/insert/delete的每次执行，字段的索引都必须重新计算更新。所以并不是任何情况下都改建立索引的

3 索引的类型？（3种）

1.唯一索引 (不允许其中任何两行具有相同索引值的索引)

当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。

2.主键索引（特殊的唯一性索引）

数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。

在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问

3.聚集索引

表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。

如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

4创建索引的两种方式

 自动创建- 在定义主键或唯一键约束时系统会自动在相应的字段上创建唯一性索引。

 手动创建- 用户可以在其它列上创建非唯一的索引，以加速查询。

1 查看/创建/删除索引语句

查看表中已经存在 index：

show index from table_name;

2 创建和删除索引

CREATE TABLE(创建表的时候创建)

CREATE TABLE index1 (id INT , name VARCHAR(20) , sex BOOLEAN , INDEX ( id) );

运行结果显示创建成功，使用SHOW CREATE TABLE语句查看表的结构。显示如下：

mysql> SHOW CREATE TABLE index1 \G

 *************************** 1. row ***************************

Table: index1

Create Table: CREATE TABLE `index1` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `sex` tinyint(1) DEFAULT NULL, KEY `index1_id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

结果可以看到，id字段上已经建立了一个名为index1_id的索引.使用EXPLAIN语句可以查看索引是否被使用，SQL代码如下：

mysql> EXPLAIN SELECT * FROM index1 where id=1 \G

 *************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: index1

type: ref

possible_keys: index1_id

key: index1_id

key_len: 5

ref: const

rows: 1

Extra: 1

row in set (0.00 sec)

 

5 语句中创建增加索引

语句中进行，也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。删除索引可以利用ALTER TABLE或DROP INDEX语句来实现。

（1）使用ALTER TABLE语句创建索引。

语法如下：

alter table table_name add index index_name (column_list) ;

alter table table_name add unique (column_list) ;

alter table table_name add primary key (column_list) ;

其中包括普通索引、UNIQUE索引和PRIMARY KEY索引3种创建索引的格式，table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以同时创建多个索引。

创建索引的示例如下：

mysql> use tpsc

Database changed

mysql> alter table tpsc add index shili (tpmc ) ;

Query OK, 2 rows affected (0.08 sec)

Records: 2 Duplicates: 0 Warnings: 0

（2）使用CREATE INDEX语句对表增加索引。

能够增加普通索引和UNIQUE索引两种。其格式如下：

create index index_name on table_name (column_list) ;

create unique index index_name on table_name (column_list) ;

说明：table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

（3）删除索引。

删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：

drop index index_name on table_name ;

alter table table_name drop index index_name ;

alter table table_name drop primary key ;

其中，在前面的两条语句中，都删除了table_name中的索引index_name。而在最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

删除索引的操作，如下面的代码：

mysql> drop index shili on tpsc ;

Query OK, 2 rows affected (0.08 sec)

Records: 2 Duplicates: 0 Warnings: 0

该语句删除了前面创建的名称为“shili”的索引。

6 为什么要创立索引呢（优点）？

因为，创建索引可以大大提高系统的性能。

1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

7 建立方向索引的不利因素（缺点）

1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

8 创建方向索引的准则?

索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

一般来说，应该在这些列上创建索引（适合）。

1.在经常需要搜索的列上，可以加快搜索的速度；

2.在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

3.在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

4.在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

5.在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

6.在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。 

9 不适合建立索引的情形

1.查询很少使用的列，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

2.数值很少的列，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

3.定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

4.修改大于检索，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少 索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

10 数据库索引的实现原理

数据库数据文件是存储在磁盘里面的，根据局部性原理与磁盘的预读，操作系统将贮存和磁盘分割为大小相等的块，每个存储块称为一页，主存和磁盘是一页为单位交换数据的。如果程序要读的数据不在主存中，会出发一个缺页异常，系统发起IO请求，磁盘会找到数据的起始位置并向后梁旭读取一页或者几页载入内存，然后异常返回，程序继续运行。

一般用磁盘I/O评价索引的性能。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。

而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

5 存储过程

①重复使用。存储过程可以重复使用,从而可以减少数据库开发人员的工作量。

②减少网络流量。存储过程位于服务器上,调用的时候只需要传递存储过程的名称以及参数就可以了,因此降低了网络传输的数据量。

③安全性。参数化的存储过程可以防止SQL注入式攻击,而且可以将Grant、Deny以及Revoke权限应用于存储过程。 简单讲: 1.存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译...

 

存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

存储过程的优点如下：

1）存储过程是一个预编译的代码块，执行效率比较高

2）一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率。

3）可以一定程度上确保数据安全

命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。

包含 in、out 和 inout 三种参数。

给变量赋值都需要用 select into 语句。

每次只能给一个变量赋值，不支持集合的操作。

create procedure myprocedure( out ret int )
    begin
        declare y int;
        select sum(col1)
        from mytable
        into y;
        select y*y into ret;
    end //
delimiter ;
call myprocedure(@ret);select @ret;

6 触发器

触发器与编程语言中的函数非常类似，都需要声明、执行等。但是触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行。有点类似DOM中的事件。

那么为什么要使用数据库对象触发器呢？在具体开发项目时，经常会遇到如下实例：

<1> 在学生表中拥有字段学生姓名，字段学生总数，每当添加一条学生信息时，学生的总数就必须同时更改。

<2> 在学生表中还会有学生姓名的缩写，学生住址等字段，添加学生信息时，往往需要检查电话、邮箱等格式是否正确。

上面的例子使用触发器完成时具有这样的特点，需要在表发生改变时，自动进行一些处理。MySQL在触发DELETE/UPDATE/INSERT语句时就会自动执行所设置的操作，其他SQL语句则不会激活触发器。

1 触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE

2 触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化。

3 INSERT 触发器包含一个名为 NEW 的虚拟表。

4DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。

5 UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改地，而 OLD 是只读的。

6 可以使用触发器来进行审计跟踪，把修改记录到另外一张表中。

7 MySQL 不允许在触发器中使用 CALL 语句 ，也就是不能调用存储过程。

7 游标

游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL 选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。如果曾经用 C 语言写过对文件进行处理的程序，那么游标就像您打开文件所得到的文件句柄一样，只要文件打开成功，该文件句柄就可代表该文件。对于游标而言，其道理是相同的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。

在存储过程中使用游标可以对一个结果集进行移动遍历。

游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。

使用游标的四个步骤：

  1. 声明游标，这个过程没有实际检索出数据；
  2. 打开游标；
  3. 取出数据；
  4. 关闭游标； 
delimiter //
create procedure myprocedure(out ret int)
    begin
        declare done boolean default 0;

        declare mycursor cursor for
        select col1 from mytable;
        # 定义了一个continue handler，当 sqlstate '02000' 这个条件出现时，会执行 set done = 1
        declare continue handler for sqlstate '02000' set done = 1;

        open mycursor;

        repeat
            fetch mycursor into ret;
            select ret;
        until done end repeat;

        close mycursor;
    end //
 delimiter ;

8 数据库约束

数据库约束用于保证数据库表数据的完整性（正确性和一致性）。可以通过定义约束\索引\触发器来保证数据的完整性。

总体来讲,约束可以分为:

主键约束：primary key；

外键约束：foreign key；

唯一约束：unique；

检查约束：check；

空值约束：not null；

默认值约束：default；

9 封锁协议

1 封锁类型

在运用S锁和X锁对数据M加锁的时候，需要约定一些规则，例如何时申请S锁或者X锁，持锁时间，这些规则就是封锁协议。其中不同的封锁协议对应不同的隔离级别。

排它锁 (X 锁），共享锁 (S 锁）

一个事务 T 对数据对象 A 加了 X 锁，T 就可以对 A 进行读取和更新。加锁期间其它事务不能对数据对象 A 加任何其它锁；

一个事务 T 对数据对象加了 S 锁，T 可以对 A 进行读取操作，但是不能进行更新操作。加锁期间其它事务能对数据对象 A 加 S 锁，但是不能加 X 锁。

2 封锁粒度

粒度可以是整个数据库，也可以是表，行，或者分量。

粒度越小，开销越大。

3 封锁协议

1 级封锁协议

一级封锁协议对应READ-UNCOMMITTED 隔离级别，本质是在事务A中修改完数据M后，立刻对这个数据M加上共享锁(S锁)[当事务A继续修改数据M的时候，先释放掉S锁，再修改数据，再加上S锁]，根据S锁的特性，事务B可以读到事务A修改后的数据(无论事务A是否提交，因为是共享锁，随时随地都能查到数据A修改后的结果)，事务B不能去修改数据M，直到事务A提交，释放掉S锁。

缺点：
可能会造成如下后果

  1. 丢失更新。
  2. 脏读。
  3. 不可重复读。
  4. 幻读

2 级封锁协议

二级封锁协议对应READ-COMMITTED隔离级别，本质是事务A在修改数据M后立刻加X锁，事务B不能修改数据M，同时不能查询到最新的数据M(避免脏读)，查询到的数据M是上一个版本(Innodb MVCC快照)的。

优点：
1.避免脏读。

缺点：
可能会造成如下后果

  1. 丢失更新。
  2. 不可重复读。
  3. 幻读。

3 级封锁协议

三级封锁协议对应REPEATABLE-READ隔离级别,本质是二级封锁协议基础上，对读到的数据M瞬间加上共享锁，直到事务结束才释放（保证了其他事务没办法修改该数据），这个级别是MySql 5.5 默认的隔离级别。

优点：1.避免脏读。2.避免不可重复读。

缺点：1.幻读。2.丢失更新。

4 最强封锁协议

最强封锁协议对应Serialization隔离级别，本质是从MVCC并发控制退化到基于锁的并发控制，对事务中所有读取操作加S锁，写操作加X锁，这样可以避免脏读，不可重复读，幻读，更新丢失，开销也最大，会造成读写冲突，并发程度也最低。

4 两段锁协议

加锁和解锁分为两个阶段进行。两段锁是并行事务可串行化的充分条件，但不是必要条件。

lock-x(A)...lock-s(B)...lock-s(c)...unlock(A)...unlock(C)...unlock(B)

5 乐观锁和悲观锁(需要再查 )

悲观锁(Pessimistic Lock), 顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

乐观锁(Optimistic Lock), 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。
原理

一、乐观锁
 总是认为不会产生并发问题，每次去取数据的时候总认为不会有其他线程对数据进行修改，因此不会上锁，但是在更新时会判断其他线程在这之前有没有对数据进行修改，一般会使用版本号机制或CAS操作实现。

 version方式：一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。

核心SQL代码：

update table set x=x+1, version=version+1 where id=#{id} and version=#{version};  

 CAS操作方式：即compare and swap 或者 compare and set，涉及到三个操作数，数据所在的内存值，预期值，新值。当需要更新时，判断当前内存值与之前取到的值是否相等，若相等，则用新值更新，若失败则重试，一般情况下是一个自旋操作，即不断的重试。

一、悲观锁
 总是假设最坏的情况，每次取数据时都认为其他线程会修改，所以都会加锁（读锁、写锁、行锁等），当其他线程想要访问数据时，都需要阻塞挂起。可以依靠数据库实现，如行锁、读锁和写锁等，都是在操作之前加锁，在Java中，synchronized的思想也是悲观锁。


10 sql 常用

SQL 支持以下三种注释：
# 注释
SELECT *
FROM mytable; -- 注释
/* 注释1
   注释2 */

1 创建表

CREATE TABLE mytable (
  id INT NOT NULL AUTO_INCREMENT,
  col1 INT NOT NULL DEFAULT 1,
  col2 VARCHAR(45) NULL,
  col3 DATE NULL,
  PRIMARY KEY (`id`));

2 插入

普通插入
INSERT INTO mytable(col1, col2)VALUES(val1, val2);
插入检索出来的数据
INSERT INTO mytable1(col1, col2)SELECT col1, col2FROM mytable2;
将一个表的内容复制到一个新表
CREATE TABLE newtable ASSELECT * FROM mytable;

3 更新

UPDATE mytableSET col = valWHERE id = 1;

4 删除

DELETE FROM mytableWHERE id = 1;
TRUNCATE TABLE 可以清空表，也就是删除所有行。
使用更新和删除操作时一定要用 WHERE 子句，不然会把整张表的数据都破坏。可以先用 SELECT 语句进行测试，防止错误删除。

5 添加列

ALTER TABLE mytable ADD col CHAR(20);

6 删除列

ALTER TABLE mytable DROP COLUMN col;

7 删除表

DROP TABLE mytable;

8 DISTINCT

相同值只会出现一次。它作用于所有列，也就是说所有列的值都相同才算相同。
SELECT DISTINCT col1, col2FROM mytable;

9 LIMIT

限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。
返回前 5 行：
SELECT *FROM mytableLIMIT 5;
SELECT *FROM mytableLIMIT 0, 5;
返回第 3 ~ 5 行：
SELECT *FROM mytableLIMIT 2, 3;

10 排序

•	ASC ：升序（默认）
•	DESC ：降序
可以按多个列进行排序，并且为每个列指定不同的排序方式：
SELECT *FROM mytableORDER BY col1 DESC, col2 ASC;

11 过滤

不进行过此尽量使用 SQL 语句来过滤不必要的数据，而不是传输所有的数据到客户端中然后由客户滤的数据非常大，导致通过网络传输了很多多余的数据，从而浪费了网络带宽。因端进行过滤。
SELECT *FROM mytableWHERE col IS NULL;

12 while

下表显示了 WHERE 子句可用的操作符

操作符

说明

= < >

等于 小于 大于

<> !=

不等于

<= !>

小于等于

>= !<

大于等于

BETWEEN

在两个值之间

IS NULL

为NULL值

应该注意到，NULL 与 0 、空字符串都不同。

13 AND OR

用于连接多个过滤条件。优先处理 AND，因此当一个过滤表达式涉及到多个 AND 和 OR 时，应当使用 () 来决定优先级。

14 in

IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。

15 NOT 

操作符用于否定一个条件。

....更多详情请看

https://blog.csdn.net/weixin_41563161/article/details/101228148

11 事务

基本术语

  1. 事务（transaction）指一组 SQL 语句；
  2. 回退（rollback）指撤销指定 SQL 语句的过程；
  3. 提交（commit）指将未存储的 SQL 语句结果写入数据库表；
  4. 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。

1 不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CRETE 和 DROP 语句。

2 MySQL 的事务提交默认是隐式提交，也就是每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

3 通过设置 autocommit 为 0 可以取消自动提交，直到 autocommit 被设置为 1 才会提交；autocommit 标记是针对每个连接而不是针对服务器的。

4 如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。

START TRANSACTION
// ...SAVEPOINT delete1
// ...ROLLBACK TO delete1
// ...COMMIT

12 权限管理

MySQL 的账户信息保存在 mysql 这个数据库中。

USE mysql;SELECT user FROM user;

创建账户

CREATE USER myuser IDENTIFIED BY 'mypassword';

新创建的账户没有任何权限。

修改账户名

RENAME myuser TO newuser;

删除账户

DROP USER myuser;

查看权限

SHOW GRANTS FOR myuser;

授予权限

GRANT SELECT, INSERT ON mydatabase.* TO myuser;

账户用 username@host 的形式定义，username@% 使用的是默认主机名。

删除权限

REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;

GRANT 和 REVOKE 可在几个层次上控制访问权限：

  • 整个服务器，使用 GRANT ALL 和 REVOKE ALL；
  • 整个数据库，使用 ON database.*；
  • 特定的表，使用 ON database.table；
  • 特定的列；

特定的存储过程。

更改密码

必须使用 Password() 函数

SET PASSWROD FOR myuser = Password('newpassword');