mysql 学习笔记

数据库概念

Database。按照一定格式存储数据文件的仓库。

常见的数据库：

​ mysql、Oracle、SQL server、DB2、MS、Sybase。

这些都是关系型数据库，里面存放的数据以表的形式存在。

非关系型数据库又被称为nosql（not noly sql），里面一般存放文档或者图。

sql语句分类

DQL:

​ 数据查询语言。主要进行数据库表的查询操作。主要使用select语句完成查询。select语句永远不会修改表的内容。

DML：

​ 数据操纵语言。主要对数据库表下的记录进行增（insert）删（delete）改（update）。

DDL

​ 数据定义语言。进行数据库表的创建（create），修改表的结构（alter），删除表（drop）。

DCL

​ 数据控制语言。授权（grant），收回权限（revoke）。

TCL

​ 事务控制语言。事务提交（commit），事务回滚（rollback）。

sql中的条件符号

这些符号放在条件筛选处（where）

between…and… 两边都是闭区间，用来查询处于between之间的值。也可以用 >= and <=代替

is null 是为空的意思。not is null是不为空的意思。在数据库中想要查询一个字段是否为空要用is null判断，不能用=null判断。null不是一个值。

and 符号代表两边的条件都要满足才会被筛选出来。

or符号代表只需要满足一边条件即可。

and优先级要高于or。如果and和or同时出现，想让or先执行可以加小括号。

in（包含）。in（条件1，条件2…），in里面的多个条件，只要满足一个，记录便会被筛选出来。相当于or。

not in（不包含）。not in（条件1，条件2…），只要不满足not in中的记录都会被筛选出来。

like。like代表着模糊查询，字段 like “%_”，其中%代表匹配多个字节， _代表只匹配一个字节。如果是”\“被转义的话则可用 ”\\“代表一个”\“。

order by 字段1 asc，字段2 desc。代表排序，asc代表升序，desc代表降序。排序可以排多个字段，第一个字段为主字段，如果第一个字段相同再根据第二个字段进行排序。

数据处理函数

数据处理函数又被称为单行处理函数，

特点：

​ 一行输入，对应多行输出。

单行处理函数：

​ lower()。将字符串转换成小写。

​ upper()。将字符串转换成大写。

​ substr(被截取字符串，当前开始截取（下标从1开始），截取长度)。字符串截取。

​ concat(字符串1，字符串2)，拼串。

​ length(字符串)。取字符串长度。

​ trim(字符串)。去除字符串前后空格。

​ now()。返回当前时间。年月日时分秒。

​ str_to_date()。将字符串转换成日期。

mysql> select str_to_date('2012-01-22 5:30:32','%Y-%m-%d %h:%i:%s');
+-------------------------------------------------------+
| str_to_date('2012-01-22 5:30:32','%Y-%m-%d %h:%i:%s') |
+-------------------------------------------------------+
| 2012-01-22 05:30:32                                   |
+-------------------------------------------------------+
1 row in set (0.00 sec)

​ date_format()。将日期以指定格式输出。

mysql> select date_format('2012-01-22','%Y/%m/%d');
+--------------------------------------+
| date_format('2012-01-22','%Y/%m/%d') |
+--------------------------------------+
| 2012/01/22                           |
+--------------------------------------+
1 row in set (0.00 sec)

​ format格式化数字：format（数字，“格式”）

mysql> select format(12312415,"$999,999");
+-----------------------------+
| format(12312415,"$999,999") |
+-----------------------------+
| 12,312,415                  |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

​ case…when…then…when…then…else…end。匹配case中的字段，当满足第一个when时then做什么事情，当满足第二个when时，then要做什么事，都不满足又做什么事情。

​ round(数字，小数点后保留几位)。代表四舍五入。

​ rand()。生成随机数。常和round函数搭配使用。

mysql> select round(rand()*100,0);
+---------------------+
| round(rand()*100,0) |
+---------------------+
|                  37 |
+---------------------+
1 row in set (0.00 sec)

​ ifnull(参与运算字段，为null的话可以自定义一个值)。null不能参与混合运算。null与任何数值计算结果都是null,如果我们需要将null加入数学运算可以使用ifnull转化为数值。

mysql> select 100+null;
+----------+
| 100+null |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)

mysql> select 100+ifnull(null,0);
+--------------------+
| 100+ifnull(null,0) |
+--------------------+
|                100 |
+--------------------+
1 row in set (0.00 sec)

分组函数

分组函数又被称为多行处理函数。

特点：

​ 多行输入，一行输出。

注意：

​ 分组函数必须分组后才能使用，如果我们没有分组，则默认以整张表为一组。

​ 分组函数在统计的时候，默认忽略null。

​ 分组函数不能出现在where子句中。

​ 在一条select语句中，如果有group by存在，则select后只能跟参加分组的字段和分组函数。

​ having必须跟在group by的后面使用，不能单独使用，having可以对select的结果进一步筛选。

多行处理函数：

​ count()。统计函数。按分组统计数量。count(*)会将有null的一条记录也统计上。

​ sum()。可直接按字段求和，也可具体分组后求和。

​ avg()。可直接按字段求平均值，也可具体分组后求平均值。

​ max()。可直接按字段求最大值，也可具体分组后求最大值。

​ min()。可直接按字段求最小值，也可具体分组后求最小值。

​ distinct 去除重复的记录，不会更改原表数据。只能出现在查询的字段之前。

查询语句执行顺序

我们平常写select的语句顺序一般是：

select… from…where…group by… having…order by…limit…

其执行查询顺序为：

from---->where---->group by---->having---->select---->order by---->limit

where里面不能出现分组函数，因为where比分组优先级高，当执行到where时还未分组。

连接查询

连接分为：内连接和外连接还有全连接。

​ 内连接：内连接可细分为等值连接，非等值连接，和自身连接。查询的条件用等号表示则为等值连接，非等式则是非等值连接，自身连接自身为自身连接。

​ 外连接：外连接可细分为左外连接和右外连接。如果连接为左外连接，则左表为主表，查询结果可以显示所有主表内容，即使某条记录没有与之对应的次表连接上，这条记录也会在查询结果显示。只是对应的次表记录为null。

没有任何限制的两个表进行连接，会产生表1记录条数×表2记录条数，这么多条记录，这种现象叫做笛卡尔积。

通过笛卡尔积我们可以得出：表的连接次数越多，查询效率越低。

​ 多表连接：

​ select…from a join b on a,b连接条件 join c on a,c连接条件 join d left on a,d连接条件

​ 在一条select语句中可以有内连接也可以有外连接

​ union合并查询结果集：

​ 在select语句中where部分我们可以使用or来连接两个字段表示满足二者之一即可出现在查询结果集，虽然这样方便但查询效率低，如果把or连接的两个字段分成两个select语句，中间用union连接，则查询效率会提高。但是union有限制，要求返回结果的列数和字段数据类型相同，因此不常用。

limit

limit常用于分页。可以将我们查询的结果只显示我们设定的条数。

​ 用法：

​ limit 起始记录位置，显示记录个数。（下标从0开始）

​ 公式：

​ 第pageNo页：limit （pageNo-1）×pageSize，pageSize

数据类型

varchar:

​ 最大长度为255，会根据实际存储的数据大小来动态的分配空间，缺点：速度慢

char：

​ 最大长度为255，初始化时分配长度，固定后，无论是否存放足够长的数据都会占用初始分配的长度大小空间，速度快，缺点：浪费空间。

int：

​ 最大长度为11。

bigint：

​ 相当于java中的long

float：

​ 单精度浮点数

double：

​ 双精度浮点数

date：

​ 短日期类型。年月日

datetime：

​ 长日期类型。年月日时分秒

clob：

​ 字符大对象（character large object），最多可以存储4G的字符串。

blob：

​ 二进制大对象，配合io流插入图片、视频等。

快速创表

create table 新表 as select *from 原表

将原表的查询结果给新表，相当于复制了原表。

删表数据

将表中数据删除有两种删法，一种是truncate，另一种是delete

truncate删表数据的速度非常快，实质上是将表中的数据从硬盘上直接删除。无法找回。

delete删表数据的速度较truncate则非常慢，看似将表删除了，但是在硬盘上的表数据依然存在，因此我们可以回滚来恢复数据。

语句：

​ truncate table 表名

​ delete from 表名

约束

非空约束： not null（不能为空，可以重复）

唯一约束：unique（不能重复，可以为空）

主键约束：primary key（非空约束和唯一约束合在一起的作用就是主键约束，不能重复也不能为空）

外键约束：foreign key

在mysql如果一个字段被 not null和unique同时修饰，则该字段会编程主键，该约束被称为联合约束。

primary key和auto_increment一起使用，可以自动增长我们新纪录的主键值。

表级约束和列级约束：

​ 直接加在字段后的是列级约束。我们用的复合主键 primary key（字段1，字段2）来定义约束，这就是表级约束。

外键：

​ 当A表的主键被B表的外键引用，这时候外键约束就产生了，A表被称为父表，B表被称为子表。

创建表时，我们先创建父表，再创建子表。删除表时，我们先删除子表，再删除父表。

存储引擎

存储引擎来为我们的数据表指定存储格式。

mysql支持9个存储引擎（根据mysql版本不同，支持的存储引擎个数可能会有所不同）

常用的存储引擎有3种：InnoDB、memory、MyISAM

mysql默认的存储引擎为InnoDB。

InnoDB:

​ 支持事务，可以rollback和commit，在数据库崩溃后可以恢复数据，非常安全。但是不能压缩表数据，效率不高。

表结构存储在frm文件中。

​ 表中数据和索引存储在tablespace表空间中。（表空间是一个逻辑名称）

Memory:

​ 查询效率非常快，数据和表索引都在内存中，但是不安全，不支持事务，表中数据和索引在断电后全部消失。

表结构存储在frm文件中。

表数据和索引都放到内存中。

MyISAM:

​ 不支持事务，但是可以压缩表数据，可以将表转换为只读来节省空间。

​ 表结构存储在frm文件中。

​ 表数据存放到MYD文件中。

​ 表索引存放到MYI文件中。

存储引擎使用：

create table（字段1，字段2…）values（值1，值2…）engine=InnoDB DEFAULT CHARSET=UTF8;

事务

一个事务就是一个完整的业务逻辑。(事务是最基本的工作单元，不可再分)

事务的特性：

A:

​ 原子性，事务是一个最基本的工作单元，不可再分。

C：

​ 一致性，事务开启后执行，要么全部成功才算成功，一条语句失败则全部失败。

I：

​ 隔离性，事务于事务之间相互独立，互不影响。

D：

​ 持久性，commit后的数据会被持久化到硬盘。

如果我们不开启事务的，则数据库会默认开启，并且默认提交数据。

开启事务：start transaction

​ 输入DML语句

​ commit/rollback

隔离性：

隔离性分为4个等级：

读未提交

​ 当A事务在B事务没提交之前读取了B正在修改的数据，而之后B事务提交了事务并更改了A已读到的数据，则此时A读到的数据会变成脏数据，被称为脏读。

读已提交

​ 该级别解决了脏读，但是又产生了新的问题：当A事务（未提交）读到了B事务已经更改且提交过的数据，但B事务在提交之后又对数据进行了修改并提交，A事务为了确认数据又读了一次数据，则发现在A的一个事务中，读两次同样的数据而值却不一样。这种问题被称为不可重复读。

可重复读

​ 该级别解决了不可重复读，但是又产生了新的问题：A事务提前准备了一个快照来保存事务开启前的数据状态。在A事务开启后B事务对A要读的数据进行多次修改并提交，但是A事务不去理会修改的数据，专心从快照里面寻找数据。则A就算多次读B修改过后的数据也没问题。但是，A读到的B修改过后的数据都是假象，不是最新的数值。这种读取方式被称为幻读。

串行化

​ 串行化解决了幻读，在A事务没提交之前B事务不允许开启。但是这样会降低效率。

查看隔离级别：

​ select @@tx_isolation

更改隔离级别：

​ set global transaction isolation level read commited

索引

索引是建立在数据库表字段上，为了提升查询效率而存在。

一个字段可以有一个索引，一个索引可以有多个字段，被称为联合索引（先从左边字段索引，如果值相同，再从右边字段索引）

索引底层是一个B-tree（自平衡二叉树）。

使用索引的条件：

​ 数据量庞大

​ 该字段频繁被当做条件用来查询

​ 很少的DML操作（操作一次，B-tree就要修改一次）

创建索引：

create index 索引名称 on 表名（索引字段）

查看sql语句是否进行了索引进行数据检索：

​ explain 语句

索引失效的条件：

​ 模糊查询并以%开始。

​ 联合索引只使用右边的字段（只使用左边字段则无问题）

​ or连接的两个字段，一个有索引，一个没有索引，则索引失效。

​ 索引参加数值运算。

视图

我们可以面向视图进行curd操作，但是在操作视图进行curd操作后，原表的数据也会被修改。

创建视图：

​ create view 视图名 as select语句

视图的作用：

​ 视图其实就是将一些繁琐的DQL语句封装成一个视图，在我们执行这些繁琐的DQL语句时可以直接通过视图来进行操作。

范式

数据库中的设计范式有三种。

第一范式：表中必须有一个主键，且每个字段不能再分。

第二范式：在满足第一范式的基础上，所有非主键字段要完全依赖主键字段，不能产生部分依赖。

第三范式：在满足第二范式的基础上，所有非主键字段要直接以来主键字段，不能产生传递依赖。

三范式只是一种理想化的设计形式，现实中，为了我们的查询效率可能不会特意去满足范式，因为表与表的每一次连接操作，都会浪费大量时间。在我们的内存支持下，我们可能会追求效率，而忽略范式的规范，以冗余换效率。