MySQL

LAST_INSERT_ID()：函数返回最后一条记录的id

SQL语句

SELECT

-- SELECT * FROM stu WHERE class_id IS NOT null;

-- SELECT sname, IFNULL(class_id,'未分配') as cid FROM stu;

-- 排序
-- SELECT * FROM stu ORDER BY class_id ASC, age ASC;

SELECT * FROM stu ORDER BY id DESC LIMIT 2, 2;

INSERT

insert 三种方式：

INSERT INTO tb_name (col1, col2, ...) VALUES (val1, val2, ...) [ (val1, val2, ...) ...]

INSERT INTO tutors SET Tname='Tom', Gender="F", Age=30;

INSERT INTO tutors(Name,Gender,Age) SELECT Name,Gender,Age FROM students WHERE Age>20;

DELETE

1	DELETE FROM tb_name WHERE condition;

TRUNCATE

清空表，并重置AUTOINCREMENT计数器。

1	mysql > TRUNCATE student;

UPDATE

1	UPDATE tb_name set col1=...,col2=... WHERE

数据表结构修改

-- 修改表名
ALTER TABLE stu_bak RENAME stus;
RENAME TABLE stus TO stu_bak;

-- 修改字段
ALTER TABLE stu_bak MODIFY sname VARCHAR(50) NOT NULL;
-- 修改字段名
ALTER TABLE stu_bak CHANGE sname name CHAR(30) NOT NULL;

-- 添加字段
ALTER TABLE stu_bak CHANGE sname name CHAR(30) NOT NULL [AFTER id];

-- 删除字段
ALTER TABLE stu_bak DROP sex;

-- 添加主键
ALTER TABLE stu_bak add PRIMARY key(id);

mysql 内置函数

字符串

left(str, len) right(str, len) mid(str, len)

CHAR_LENGTH(str) CONCAT(str, str2)

1	SELECT IF(CHAR_LENGTH(cname) > 8, CONCAT(LEFT(cname,8),'...'), cname) FROM stu_bak;

正则表达式

1	SELECT * from stu_bak WHERE cname REGEXP '^小';

整型

MySQL数据类型	范围（有符号）	范围（无符号）
tinyint(m)	1个字节范围(-128~127)	(0，255)
smallint(m)	2个字节范围(-32768~32767)	(0，65 535)
mediumint(m)	3个字节范围(-8388608~8388607)	(0，16 777 215)
int(m)	4个字节范围(-2147483648~2147483647)	(0，4 294 967 295)
bigint(m)	8个字节范围(+-9.22*10的18次方)	(0，18 446 744 073 709 551 615)

取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。
m的含义不是允许字段的长度，而是显示长度，在为字段设置 zerofill 时有效。

1	ALTER TABLE stu_bak ADD stu_count smallint(6) ZEROFILL default null;

浮点型

类型	大小	范围（有符号）	范围（无符号）
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
DECIMAL	DECIMAL(M,D) ，m<65 是总个数，d<30	依赖于M和D的值	依赖于M和D的值

下面是检测浮点数精度的示例

1 2	alter table class add e FLOAT(10,2); update class set e = 12345678.66 where id=11;

查看结果时会发布浮点数结果不精确。

float：2^23 = 8388608，一共七位，这意味着最多能有7位有效数字，但绝对能保证的为6位，即float的精度为6~7位有效数字
double：2^52 = 4503599627370496，一共16位，double的精度为15~16位
浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值
decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位
对货币等对精度敏感的数据，应该用定点数decimal存储

浮点型

类型	大小	范围（有符号）	范围（无符号）
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
DECIMAL	DECIMAL(M,D) ，m<65 是总个数，d<30	依赖于M和D的值	依赖于M和D的值

下面是检测浮点数精度的示例

1 2	alter table class add e FLOAT(10,2); update class set e = 12345678.66 where id=11;

查看结果时会发布浮点数结果不精确。

float：2^23 = 8388608，一共七位，这意味着最多能有7位有效数字，但绝对能保证的为6位，即float的精度为6~7位有效数字
double：2^52 = 4503599627370496，一共16位，double的精度为15~16位
浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值
decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位
对货币等对精度敏感的数据，应该用定点数decimal存储

ENUM

ENUM 类型因为只允许在集合中取得一个值，有点类似于单选项。在处理相互排拆的数据时容易让人理解，比如人类的性别。换个枚举最大可以有 65535 个成员值

1	ALTER TABLE stu ADD sex ENUM('男','女') DEFAULT NULL;

可以使用索引或值添加enum数据

1 2	INSERT INTO stu (sname,class_id,sex) VALUES('李岗',1,'男'); INSERT INTO stu (sname,class_id,sex) VALUES('李玉',1,2);

可以使用值与索引检索ENUM

1 2	SELECT * from stu WHERE sex='女'; SELECT * from stu WHERE sex=2;

SET

SET 类型与 ENUM 类型相似但不相同。SET 类型可以从预定义的集合中取得任意数量的值。一个 SET 类型最多可以包含 64 项元素。

使用SET类型添加文章属性字段

1	ALTER TABLE article ADD flag SET('推荐','置顶','图文','热门');

添加数据

1	INSERT INTO article (title,status,flag)VALUES('后盾人',1,'图文,推荐,置顶');

使用 find_in_set 查找数据

1	SELECT * FROM article WHERE find_in_set('图文',flag);

使用like 查找数据

1	SELECT * FROM article WHERE flag like '%置顶%'

二进制比较

可以使用二进制方式对SET类型进行模糊筛选。

SET成员	十进制值	二进制值
推荐	1	0001
置顶	2	0010
图文	4	0100
热门	8	1000

获取包含图文与推荐的文章

1	SELECT * FROM article WHERE flag & 5;

时间日期

日期时间类型	占用空间	日期格式	最小值	最大值	零值表示
DATETIME	8 bytes	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59	0000-00-00 00:00:00
TIMESTAMP	4 bytes	YYYY-MM-DD HH:MM:SS	1970-01-01 08:00:01	2038-01-19 03:14:07	00000000000000
DATE	4 bytes	YYYY-MM-DD	1000-01-01	9999-12-31	0000-00-00
TIME	3 bytes	HH:MM:SS	-838:59:59	838:59:59	00:00:00
YEAR	1 bytes	YYYY	1901	2155	0000

Mysql保存日期格式使用 YYYY-MM-DD HH:MM:SS的ISO 8601标准
向数据表储存日期与时间必须使用ISO格式

参考日期时间函数文档

事务

RDBMS：ACID(原子性，一致性，隔离性，持久性)

事务日志

重做日志 redo log
撤销日志 undo log

隔离级别

事务隔离级别	脏读	不可重复读	幻读	说明
读未提交（read-uncommitted）	是	是	是	最低的事务隔离级别，一个事务还没提交时，它做的变更就能被别的事务看到
不可重复读（read-committed）	否	是	是	保证一个事物提交后才能被另外一个事务读取。另外一个事务不能读取该事物未提交的数据。
可重复读（repeatable-read）	否	否	是	多次读取同一范围的数据会返回第一次查询的快照，即使其他事务对该数据做了更新修改。事务在执行期间看到的数据前后必须是一致的。
串行化（serializable）	否	否	否	事务 100% 隔离，可避免脏读、不可重复读、幻读的发生。花费最高代价但最可靠的事务隔离级别。

当高并发访问会遇到多个事务的隔离问题，可能会出现以下：

脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

MySQL 默认隔离级别可重读(REPEATABLE-READ)。

mysql> SHOW GLOBAL VARIABLES LIKE '%iso%';
# 修改
mysql> SET transaction_isolation='READ-UNCOMMITTED';

# 设置会话隔离级别，影响当前连接
set session transaction isolation level read uncommitted;
# 设置全局隔离级别，影响全局连接
set global transaction isolation level read uncommitted;

事务 SQL 语句

mysql > START TANSACTION;
DELETE FROM tutors WHERE Tname like 'stu%';

ROLLBACK; // 回滚
COMMIT; // 提交

建议：明确使用事务，并且关闭自动提交；

mysql> set autocommit=0;

保存点： SAVEPOINT

1 2	mysql> SAVEPOINT point; mysql> ROLLBACK TO point;

日志：

锁

锁粒度：从大到小，MySQL服务器仅支持表级锁，行锁需要由存储引擎完成。

表锁
页锁
行锁

InnoDB 是主流储存引擎并支持行级锁的，有更高的并发处理性能。

行锁开销大，锁表慢
行锁高并发下可并行处理，性能更高
行锁是针对索引加的锁，在通过索引检索时才会应用行锁，否则使用表锁。
在事务执行过程中，随时都可以执行锁定，锁在执行 COMMIT或者ROLLBACK的时候释放

使用非索引字段筛选时，将造成全表锁定即表级锁，应该避免这种情况发生，提升数据库的并发性能。

查询没有指定明确范围时也会造成大量记录的锁定。

悲观锁

非观锁指对数据被外界修改持保守态度，在整个数据处理过程中，将数据处于锁定状态，可以很好地解决并发事务的更新丢失问题。

BEGIN;
SELECT * FROM goods WHERE id=1 FOR UPDATE;
UPDATE goods SET num=num-2 WHERE id=1; 
...

乐观锁

在每次去拿数据的时候认为别人不会修改，不对数据上锁，但是在提交更新的时候会判断在此期间数据是否被更改，如果被更改则提交失败。

下面使用版本字段来实现乐观锁操作，并实现更改商品库存的案例。

事务A查询商品库存，获取了商品记录，记录中有VERSION字段用于记录版本号（目前为0）

1 2	BEGIN; SELECT * FROM goods WHERE id = 1;

事务B同时查询，也获取了版本号为0的记录

1 2	BEGIN; SELECT * FROM goods WHERE id = 1;

事务A更改库存，并增加版本号

1	UPDATE goods SET num=num-10,VERSION =VERSION+1 WHERE VERSION=0;

事务B更改数据，但使用的是事务B查询到的0号版本，因为事务A已经提交版本号为1，造成事务B修改失败，保证了数据的完整性。

1	UPDATE goods SET num=num-10,VERSION =VERSION+1 WHERE VERSION=0;

表锁机制

针对一些不支持事务的处理引擎可以使用锁表的方式控制业务。

读锁：共享锁
写锁：独占锁

1 2	LOCK TABLES tb_name {READ\|WRITE} UNLOCK TABLES

源码编译

https://www.jianshu.com/p/117dab1b658d

cmake . -DBUILD_CONFIG=mysql_release -DCPACK_MONOLITHIC_INSTALL=ON -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DMYSQLX_TCP_PORT=33060 -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock -DMYSQL_TCP_PORT=3306 -DMYSQLX_UNIX_ADDR=/usr/local/mysql/mysqlx.sock -DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/usr/local/mysql/etc -DENABLE_DOWNLOADS=ON -DWITH_BOOST=system

1 2	启动命令：/etc/init.d/mysqld start 关闭命令：/etc/init.d/mysqld stop

修改 root 密码

1	UPDATE user SET PASSWORD=PASSWORD('rootroot') WHERE User='root';

Tips

mysql 命令行 \c表示取消本条SQL，后面不需要写 ;。

1	show databases \c

自连接效率好于子查询。

1
2
3

SELECT s2.sname FROM stu as s1 
INNER JOIN stu as s2 on s1.class_id= s2.class_id
WHERE s1.sname = '张三' AND s2.sname != '张三';