sql

官网略过了 MYSQL Programs 等

• 账户管理

• ALTER USER ‘userName‘@’localhost’ IDENTIFIED BY ‘New-Password-Here’;

• password: 1379, 1234

• 能力

  1. DDL
  2. DML
  3. DQL

• 主键来唯一确定一条记录，不使用任何业务相关的字段作为主键。

• SHOW CREATE TABLE mytable;

  DESC mytable

• show databases

  use test

  show tables

  class_id <> 2 == NOT class_id = 2

  name LIKE 'ab%' like 中 % 匹配任意多个字符

  NOT, AND, OR 的优先级

• SELECt 列1 别名1, .. 可以达到显示重命名的效果

• ORDER BY (DESC) 排序

• LIMIT <N-M> OFFSET <M>

  可以缩写为 LIMIT <M>, <N>;

• SELECT COUNT(*) FROM students;，注意返回的是一个 1*1 的表

  SUM, AVG, MAX, MIN

  只有 COUNT 在没有查询到数据时返回 0

  CEILING, FLOOR;

  GROUP BY;

  SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

• 多表查询以及设置列别名和表别名

  SELECT
      s.id sid,
      s.name,
      s.gender,
      s.score,
      c.id cid,
      c.name cname
  FROM students s, classes c;

  多表查询返回的是一个笛卡尔积

• SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;

• 两个查询语句用 UNION 连接

• insert

  INSERT INTO students (class_id, name, gender, score) VALUES
  (1, '大宝', 'M', 87),
  (2, '二宝', 'M', 81);

• UPDATE

  UPDATE students SET score=score+10 WHERE score<80;

• DELETE

  DELETE FROM students WHERE id>=5 AND id<=7;

• 数据库

  DATABASE 中 information_schema, mysql, performance_schema, sys 是系统库，不要改动。

  SHOW DATABASES;

  CREATE DATABASE test, DROP DATABASE test;

  CREATE TABLE statistics (
      id BIGINT NOT NULL AUTO_INCREMENT,
      class_id BIGINT NOT NULL,
      average DOUBLE NOT NULL,
      PRIMARY KEY (id)
  );

  UES test;

  SHOW TABLES;

  查询信息用 DESC

  DESC students;, SHOW CREATE TABLE students;

  DROP TABLE students;

  ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;, ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;, ALTER TABLE students DROP COLUMN birthday;

• 插入或替换

  REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

• 插入或更新

  INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

  更新不同于替换，更新是在原基础修改（主字段不变），替换是会删除原来的（如果是以自增为主字段则会改变）。

• 插入或忽略

  INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

• 快照

  CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

• example

  INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

• 索引

  ALTER TABLE students ADD INDEX idx_score (score);

• mysql UUID

New

数据库

• 创建

  CREATE DATABASE menagerie;

• 使用

  USE menagerie;

  mysql -u root -p[password] menagerie

• 查询

  SHOW DATABASES

  查询当前使用的数据库 SELECT DATABASE();

表

• 查询

  SHOW TABLES;

• 增加

  CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
     species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

  VARCHAR 取值 $1~65535$

表内容

查询

DESCRIBE <table>

查询所有数据 TABLE <table>

获取创建表的所有信息 SHOW CREATE <table>

SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
OR (species = 'dog' AND sex = 'f');

String comparisons normally are case-insensitive

去重 SELECT DISTINCT owner FROM pet;

排序，默认升序 SELECT name, birth FROM pet ORDER BY birth DESC;

函数：

SELECT name, birth, CURDATE(),
TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
FROM pet;

时间：

SELECT name, birth FROM pet
WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

查看错误信息 SHOW WARNINGS;

Because the result of any arithmetic comparison with NULL is also NULL, you cannot obtain any meaningful results from such comparisons.

In MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is 1.

This special treatment of NULL is why, in the previous section, it was necessary to determine which animals are no longer alive using death IS NOT NULL instead of death <> NULL.

Two NULL values are regarded as equal in a GROUP BY.

When doing an ORDER BY, NULL values are presented first if you do ORDER BY … ASC and last if you do ORDER BY … DESC.

• pattern matching:

  SQL pattern matching enables you to use _ to match any single character and % to match an arbitrary number of characters (including zero characters).

  Do not use = or <> when you use SQL patterns. Use the LIKE or NOT LIKE comparison operators instead.

  SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b' COLLATE utf8mb4_0900_as_cs);
  SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b');
  SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c');

• count

  SELECT species, sex, COUNT(*) FROM pet
  WHERE species = 'dog' OR species = 'cat'
  GROUP BY species, sex;

增加

txt 文件内容（其中 \N 代表 null）：

Whistler        Gwen    bird    \N      1997-12-09      \N

然后使用：

LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
    LINES TERMINATED BY '\r\n';

用 INSERT：

INSERT INTO pet
    VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

删除

DELETE FROM pet;

更新

UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

杂

• batch mode

  mysql -h host -u user -p < batch-file

  >mysql source filename;

• user-defined variables

  mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
  mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

• foreign-key

  CREATE TABLE child (
      id INT,
      parent_id INT,
      INDEX par_ind (parent_id),
      FOREIGN KEY (parent_id)
          REFERENCES parent(id)
          ON UPDATE CASCADE
          ON DELETE CASCADE
  ) ENGINE=INNODB;

• auto_increment

  When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value. For example:

  SELECT LAST_INSERT_ID();

  ALTER TABLE tbl AUTO_INCREMENT = 100;

InnoDB

InnoDB is the default MySQL storage engine.

For example, SELECT … FOR SHARE sets an IS lock, and SELECT … FOR UPDATE sets an IX lock.

intent lock:

Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table.

The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.

LOCK TABLES ... WRITE takes an exclusive lock (an X lock) on the specified table

For example, SELECT ... FOR SHARE sets an IS lock, and SELECT ... FOR UPDATE sets an IX lock.

四个隔离级别

• read uncommited

  事务在读数据的时候并未对数据加锁。

  事务在修改数据的时候只对数据增加行级共享锁？。

  顾名思义，会读到未提交的信息。

  对应脏读问题。

• read commited

  事务对当前被读取的数据加 行级共享锁（当读到时才加锁），一旦读完该行，立即释放该行级共享锁；

  事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。

  只会读到提交的信息。

  对应不可重复读问题（因为在一个事务中，两次读到的同一行可能不一致）。

• repeatable read

  事务在读取某数据的瞬间（就是开始读取的瞬间），必须先对其加 行级共享锁，直到事务结束才释放；

  事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加 行级排他锁，直到事务结束才释放。

  同一个事务中，两次读到同一行的信息一致。

  对应幻读问题（因为这里是同一行的信息一致，对应 update，如果是 insert，即增加，会出现两次读取到的总体内容不一致）。

  MySQL 默认级别。

• serializable

  事务在读取数据时，必须先对其加 表级共享锁 ，直到事务结束才释放；

  事务在更新数据时，必须先对其加 表级排他锁 ，直到事务结束才释放。

  完全串行化。所有结果符合预期，但效率大大降低。

In InnoDB, all user activity occurs inside a transaction. If autocommit mode is enabled, each SQL statement forms a single transaction on its own. By default, MySQL starts the session for each new connection with autocommit enabled, so MySQL does a commit after each SQL statement if that statement did not return an error. If a statement returns an error, the commit or rollback behavior depends on the error.

If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.

To disable autocommit mode implicitly for a single series of statements, use the START TRANSACTION statement. With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state. 即 START TRANSACTION 用于临时禁用 autocommit

• SELECT ... FOR SHARE

  Sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.

  All locks set by FOR SHARE and FOR UPDATE queries are released when the transaction is committed or rolled back.

innodb-locking-reads

NOWAIT and SKIP LOCKED only apply to row-level locks.

Statements that use NOWAIT or SKIP LOCKED are unsafe for statement based replication.

There’s no difference. They are synonyms, though INDEX should be preferred (as INDEX is ISO SQL compliant, while KEY is a MySQL-specific, non-portable, extension).

In the created table, a PRIMARY KEY is placed first, followed by all UNIQUE indexes, and then the nonunique indexes. This helps the MySQL optimizer to prioritize which index to use and also more quickly to detect duplicated UNIQUE keys.

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix length.

Indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

在 MySQL 中，如果在事务中进行了修改但尚未提交，那么修改的记录会被保存在内存中的临时存储区域，称为回滚日志（Undo Log）。

回滚日志是用于事务的回滚和并发控制的关键组件之一。它记录了事务对数据所做的修改操作，在事务回滚或发生异常时，可以使用回滚日志将数据还原到事务开始之前的状态。

当你执行数据更新操作（例如插入、更新或删除）时，MySQL 将在内存中创建相应的回滚日志记录，而不是直接修改数据库的存储。这样做的目的是为了提供事务的原子性和一致性，以便在事务回滚时能够撤销对数据的修改。

只有在事务提交之后，MySQL 才会将回滚日志中的修改应用到实际的数据库存储中。提交操作将会持久化数据的修改，使其成为永久性的更改。

需要注意的是，回滚日志是针对事务的临时存储，一旦事务提交或回滚，相关的回滚日志就不再需要，会被释放或重用。

undo tablespaces

mvcc 的一些理解

In the normal course of accessing an index, some small number of records will need to be reverted to a previous version in order to satisfy the transaction isolation requirements imposed by the system. This has a cost, but as long as the read view of the transaction is fairly new, most records will not require reversion, and there is very little performance cost to doing this.

redo 用于重放已提交事务的修改，而 undo 用于撤销未提交事务的修改和支持并发控制。

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of an SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row.

如果没有 for update，读取是可重复读，没有幻读问题。

但是如果加上for update的话，读取是已提交事务数据，gap锁保证for update情况下，不出现幻读。

gap 锁只有在 repeatable read 下用 for update 才有？

gap lock

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. In this case, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

There are also other effects of using the READ COMMITTED isolation level. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. For UPDATE statements, InnoDB does a “semi-consistent” read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE.

sql isolation 相关行为

注意到 for update 如果作用于一个没有 index 的，会造成整个表 lock

lock 语句