MySQL数据库入门学习

 　　数据库，就是数据的集合(a collection of data)，用来存储数据。为什么要用数据库存储数据呢？因为它们有着特定的存储结构，能够快速高效地存取大量的数据，并且在存储结构上面又提供了一层逻辑结构(单个数据库和表)，方便使用。可以把单个数据库想像成一个文件夹，表想像成一个excel表格文件，存取数据就像向表中插入和查询数据。正如excel表格有行和列之分，数据库中的表，也有行和列之分，列规定放什么数据(姓名，年龄)，行就是插入的数据。数据库就是一个简单的物理位置，用来存放数据。怎么操作数据库？用数据库管理软件(Database Management System(DBMS))，比如MySQL和Oracle等。MySQL和Oracle等并不是数据库，而是数据库管理软件，用来管理数据库，比如创建数据库，创建表，从数据库中获取数据等等。数据库管理软件是以服务的形式存在的，使用之前要先启动服务，然后登录连接服务。比如安装了MySQL，还要启动MySQL 服务，然后登录，才能对数据库进行操作。管理员身份打开命令行工具，net start  mysql 启动服务，mysql -u 用户名 -p 密码，

　　显示mysql>，表示登录成功。操作数据使用的是SQL指令或SQL语句。我们写SQL语句，发送给MySQL，MySQL执行SQL语句，从而实现对数据库的操作。SQL也是一门语言，有着特定的语法。比如创建数据库，CREATE {DATABASE | SCHEMA } [IF NOT EXISTS] db_name，{ } 大号表示必写项，里面的 | 表示二者选其一，[] 表示可写可不写，

CREATE DATABASE my_first_db; -- 创建my_first_db 数据库

　　SQL指令不区分大小写，但建议关键字(CREATE DATABASE)全部大写，数据库名等自定义的内容全部小写。每一个指令都要以分号结尾，MySQL见分号执行语句。SQL 关键字要以空格进行分隔，空格之后可以跟任意的空格，也可以跟换行，所以SQL指令可以写多行。还有一种不常见的，语句也可以以\g（go）结尾。SELECT NOW(); 和 SELECT NOW()\g 一样。

 　　[IF NOT EXISTS]，如果不存在数据库db_name, 就创建数据库 db_name，如要存在，就什么也不做，防止创建同名的数据库时报错。再创建一次my_first_db ，

　　报错了。如果加上IF NOT EXISTS

 　　没有报错，只是一个warning。SHOW DATABASES; 命令， 显示创建了哪些数据库。创建数据库成功，就要使用数据库。USE 数据库名

USE my_first_db;

　　Database change，表示已经在my_first_db数据库下面了。SELECT DATABASE(); 也可以确定正在操作哪个数据库。接下来，就要向数据库存储数据，需要创建表。相当于已经创建了一个文件夹，要在其下面创建excel表格。 创建表就要考虑表中存储什么数据(字段)以及用什么类型去存储。CREATE TABLE [IF NOT EXIST] 表名( 字段名 数据类型, .....); 数据类型多种多样，有整数，小数，日期类型，字符串类型等。

　　整数分为INT，BIGINT，SMALLINT， TINYINT，MEDIUMINT 等，它们的主要区别在于所占内存空间不同，所存储的数值范围不同。INT占4个字节，BIGINT占8个字节，SMALLINT 占2个字节， TINYINT占1个字节，MEDIUMINT占3个字节。在它们后面可以跟UNSIGNED UNSIGNED表示存储无符号整数。其实还可以跟width，ZEROFILL，不过现在width和ZEROFILL已经废弃了。

　　小数分为定点数和浮点数，它们的区别在于小数点的精度。定点数，你存什么，取出来就是什么，一模一样，但对于浮点，存进去的数值和取出来的数值可能不一样。定点数类型是DECIMAL和NUMERIC，在MySQL中，它们是一样的。语法DECIMAL[(width[,decimals])] [UNSIGNED] [ZEROFILL]，数字总共多width，有decimals位小数，DECIMAL(6,2) 存储 –9,999.99 ~ 9,999.99。width是可选的，默认是10，decimals也是可选的，默认是0。width 最大能取65，decimals最大能取30。如查只想存正数，可以加UNSIGNED，用0对数字进行填充，加ZEROFILL。

　　浮点数是DOUBLE(也称Real)和FLOAT，存储近似值。FLOAT有两种格式，FLOAT[(width, decimals)]  [UNSIGNED] [ZEROFILL] 或 FLOAT[(precision)]  [UNSIGNED] [ZEROFILL], 第二种就是设置精度，精度如果取0-24，那它还是FLOAT, 如果取25-53，它就变成了DOUBLE。但通常，只使用FLOAT关键字，不设置任何参数，用来存储4个字节的单精度浮点数。DOUBLE 只有一种格式，DOUBLE [(width, decimals)]  [UNSIGNED] [ZEROFILL]， 和FLOAT一样，通常只使用DOUBLE关键字，不设置任何参数，用来存储8个字节的双精度浮点数。

　　字符串类型，经常用的是CHAR、VARCHAR、TEXT。CHAR类型语法是 CHAR(width)，存储固定长度(width)的字符，如果width没有提供，width默认是1，就是char(1), width 的最大值是255. 若存入字符数小于width，则以空格补于其后。但当读取数据的时候，空格又会被移除掉，也就是说，如果原来字符串中末尾有空格，存储到char类型，这些空格就会消失掉。VARCHAR 后面也是跟一个长度， VARCHAR(width)， 存储字符到最大长度(width)，也就是说，存入多少就是多少，不会补空格，直到最大长度width，如果超出最大长度，就会截取到最大长度，空格也是有效字符，比如varchar(2),  如果存储 'a       ' 会被截取成 'a '。width最大能取到65,536，VARCHAR最大能存储65536个字符。在实际存储中，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。 TEXT最大能存储65536个字符。Mysql中，一个中文也是一个字符，65536个字符，就是65536个汉字。

　　时间和日期类型：Date 存储年月日，MySQL 在存储和显示日期的时候是按照 YYYY-MM-DD 的格式，比如2024-01-31。Time 存储 时分秒，格式为HH:MM:SS，比如12:25:36。TIMESTAMP和DATETIME： 存储年月日，时分秒，格式为YYYY-MM-DD HH:MM:SS[.fraction][time zone offset]。TIMESTAMP 在内部是用UTC 时区 进行存储的，而DATETIME 则不会转化成UTC。

　　ENUM类型：指定列的取值范围，列的值只能取定义的列表中的一个，比如 列的类型是ENUM('Apple', 'Orange', 'Pear')。SET类型和ENUM一样，也是定义取值范围，不过，列的值可以是一个或多个。ENUM和SET类型，虽然操作的都是字符串，但真正存储到数据库中的是数字，所以千万不要用ENUM或SET 来定义数字。

　　创建一个student表，姓名，年龄，成绩，最喜欢的水果，

CREATE TABLE fruits_enum
-> (fruit_name ENUM('Apple', 'Orange', 'Pear'));

INSERT INTO fruits_enum VALUES ('Apple');

-- A list of several allowed values isn’t accepted either:
-- INSERT INTO fruits_enum VALUES ('Apple,Orange'); 

　　，只不过，它能插入list

CREATE TABLE fruits_set
-> ( fruit_name SET('Apple', 'Orange', 'Pear') );
 INSERT INTO fruits_set VALUES ('Apple,Orange');

 　　时间和日期类型：，但我们向表中插入数据的时候，格式可以有多种形式，比如 YYYY-MM-DD，YYYY/MM/DD

CREATE TABLE testdate (mydate DATE);
INSERT INTO testdate VALUES ('2020/02/31');

　　插入成功后再查询，SELECT * FROM testdate; 

 

1. If you want the timestamp to be set only when a new row is inserted into the
table, add DEFAULT CURRENT_TIMESTAMP to the end of the column declara‐
tion.
2. If you don’t want a default timestamp but would like the current time to be
used whenever the data in a row is updated, add ON UPDATE CURRENT_TIME
STAMP to the end of the column declaration.
3. If you want both of the above—that is, you want the timestamp set to the
current time in each new row and whenever an existing row is modified—
add DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP to the
end of the column declaration.

If you do not specify DEFAULT NULL or NULL for a TIMESTAMP column, it will have
0 as the default value.

 

 

需要注意的是，MySQL在内部进行数字计算时，使用的是signed BIGINT 或 signed DOUBLE，取值范围为–9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807。如是两个数的计算值，比如相乘，越出了范围，就会报错，

Insert into my_first_db.person(age) values(18447440737095516 * 100) -- 报错

The SERIAL data type can be used as an alias for BIGINT UNSIGNED
NOT NULL AUTO_INCREMENT UNIQUE . Unless you must optimize for
data size and performance, consider using SERIAL for your id -like
columns. Even the UNSIGNED INT can run out of range much
quicker than you’d expect, and often at the worst possible time.

　

　 创建一个表保存用户信息， 姓名，年龄。建议数据库名称，表名，字段名，使用小写字母并用下划线分割，因为在Linux下，它们是区分大小写的，而在Windows下，它们不区分大小写， 

 

 　　But under Windows, this will normally work.

CREATE TABLE person (
    name VARCHAR(255),
    age INT
);

　　查看一下数据库有没有创建表成功，可以使用SHOW TABLES; 命令，显示数据库中所有的表。

 　　插入数据用insert，也可以从外部加载数据，单条插入或批量插入，自动更新或有default值的不用插入。使用sql 脚本插入，一个.sql文件，在 .sql文件所在的文件夹，打开命令行，输入mysql 数据库名 < sql 脚本。

　　删除，由于数据库删除是删除一行，所以它不需要选择字段，只要提供条件，满足条件才会删除，

　　update，如果没有条件，对整张表的数据进行更新，

 

　　查询

　　https://dev.mysql.com/doc/index-other.html  有 Example Databases，sakila库。下载zip到本地，解压，mysql命令行中，

source C:\Users\Sam\Downloads\sakila-db\sakila-schema.sql
source C:\Users\Sam\Downloads\sakila-db\sakila-data.sql

　　查询： 查询出符合条件的记录， 如果没有列出条件，就把整张表的数据全列出来。select 字段名 from 表名 where 条件，

SELECT city FROM sakila.city;

　　查询条件，等值查询=， !=, 大于，小于，区间查询 between and , 

SELECT name FROM sakila.language WHERE name = 'English';
SELECT city FROM sakila.city WHERE city_id < 5;

　　模糊查询 like： 表达式中的%表示任意多个字符，_ 表示任意一个字符。查询电影名中包含family的电影

SELECT title FROM sakila.film WHERE title LIKE "%family%";
SELECT first_name FROM sakila.actor WHERE first_name LIKE "NAT_%";

　　一般情况下，要避免使用在模糊查询的开头使用%，因为，它不会使用索引，会进行全表扫描

　　多条件进行查询，AND，OR, NOT，XOR 

SELECT city, city_id FROM sakila.city WHERE city_id = 3 OR city_id = 4

　　当AND和OR组合使用时，要注意执行顺序，优先级，AND的执行顺序高于OR，不过可以使用括号，改变优先级

SELECT city, city_id FROM sakila.city WHERE (city_id = 3 OR city_id = 4) AND country_id = 60;
SELECT fid, title FROM sakila.film_list WHERE FID < 7 AND NOT (FID = 4 OR FID = 6);

 　　Order by 字段名，以哪个字段对查询结果进行排序，可以多个字段，

SELECT address, district FROM sakila.address ORDER BY district ASC, address DESC;
SELECT address, district FROM sakila.address ORDER BY district, address;

　　LIMIT： 一个参数限制返回的条数，二个参数，第一个参数是offset(从哪一条数据开始返回，从0开始计算)，第二个参数是返回最多几条

SELECT name FROM customer_list LIMIT 10;
SELECT name FROM sakila.customer_list LIMIT 5, 5; 

　　多表关联join，the ON keyword that specifies the required columns to compose the condition. tell MySQL the columns that hold the relationship between the tables;

SELECT city, country 
FROM sakila.city INNER JOIN sakila.country
ON city.country_id = country.country_id
WHERE country.country_id < 5
ORDER BY country, city;

If in the join condition the column names in both tables used for matching are the
same, you can use the USING clause instead:

SELECT city, country 
FROM sakila.city INNER JOIN sakila.country
USING (country_id)
WHERE country.country_id < 5
ORDER BY country, city;

 　　mysql语句，以\G 结尾，结果会以行显示

　　Key：

　　主键：唯一标识每一行。在InnoDB存储引擎，主键会被建立索引。也可以在其它列，创建索引，在创建表的时候，index或key 索引名 （要添加索引的字段名）比如 KEY idx_names_email (first_name, last_name, email));

For MySQL to be able to use an index, the query needs to meet both the following conditions:

　　The leftmost column listed in the KEY (or PRIMARY KEY ) clause must be in the query.

　　2. The query must contain no OR clauses for columns that aren’t indexed.

mysql> SELECT * FROM customer_mod WHERE
-> last_name = 'Williams' AND
-> email = 'rose.w@nonexistent.edu';

　　以上就不能用索引

　　别名：使用AS， 字段别名，

SELECT first_name AS 'First Name' FROM sakila.actor;

SELECT CONCAT(first_name, ' ', last_name, ' played in ', title) AS movie
FROM sakila.actor JOIN sakila.film_actor USING (actor_id)
JOIN sakila.film USING (film_id)
ORDER BY movie LIMIT 20;

　　表名起别名：

use sakila;
SELECT ac.actor_id, ac.first_name, ac.last_name, fl.title FROM
actor AS ac 
INNER JOIN film_actor AS fla USING (actor_id)
INNER JOIN film AS fl USING (film_id)
WHERE fl.title = 'AFFAIR PREJUDICE';

 　　remove the duplicate rows,  and produce a set of unique values, add DISTINCT to the query:

SELECT DISTINCT srcuser FROM mail;

　　To count the number of unique values in a column, use COUNT(DISTINCT):

SELECT COUNT(DISTINCT srcuser) FROM mail;

 

　　distinct: The DISTINCT clause applies to the query output and removes rows that have identical values in the columns selected for output in the query.  To remove duplicates, MySQL needs to sort the output. If indexes are available that are in the same order as required for the sort, or the data itself is in an order that’s useful, this process has very little overhead. However, for large tables and without an easy way of accessing the data in the right order, sorting can be very slow. You should use DISTINCT (and other aggregate functions) with caution on large datasets. If you do use it, you can check its behavior using the EXPLAIN statement 

　　分组：主要是用于统计数据，通常和聚合函数一起使用，聚合函数：count计算行数，count(*)把所有行都加起来，不管行的值 是不是null，count(字段名)则仅把值为非null的行加起来，可以使用count(distinct 字段句)统计去重的行数。

 SELECT COUNT(email) FROM customer;

　　还有min(), max(), sum(), avg()聚合函数。分组之后要用having对分组后的数据进行过滤。The HAVING clause must contain an expression or column that’s listed in the SELECT clause

SELECT title, COUNT(rental_id) AS num_rented FROM film 
INNER JOIN inventory USING (film_id)
INNER JOIN rental USING (inventory_id)
GROUP BY title
HAVING num_rented > 30
ORDER BY num_rented DESC LIMIT 5;

　　In a left join, each row in the left table—the one that’s doing the driving—is processed and output, with the matching data from the second table if it exists and NULL values if there is no matching data in the second table. 

　　nested query,  当nest query返回一行一列，可以把它赋值给一个值，可以进行比较大少，

 　　然后

 　　当返回一列多行时，就相当于一个数组，可以使用 in( = Any) 子查询，> any(或some) 子查询，<= All 字查询， NOT IN。 当子查询返回多行多列时，要使用特殊的语法，

mysql> SELECT emp_no, YEAR(from_date) AS fd
-> FROM titles WHERE title = 'Manager' AND
-> (emp_no, YEAR(from_date)) IN
-> (SELECT emp_no, YEAR(from_date)
-> FROM titles WHERE title <> 'Manager');

 　nest Query

mysql> SELECT first_name, last_name FROM staff
-> WHERE EXISTS (SELECT * FROM customer
-> WHERE customer.first_name = staff.first_name
-> AND customer.last_name = staff.last_name);

　　在子查询中，子查询语句可以访问外查询中列出的表。在这个例子，staff.first_name和staff.last_name 是作为常量提供给子语句，然后和 customer 的first_name和last_name相比较，比如匹配成功，子查询返回true，外查询那一行记录就出现在结果集中。举例：比如外查询中，staff表中的一行记录，first_name和 last_name 是 Jon and Stephens ,  子查询返回false，因为SELECT * FROM customer WHERE first_name = 'Jon' and last_name = 'Stephens' 没有查到任何记录，所以 staff表中的这一行记录就不会出现结果集中。再比如staff 表中的记录，first_name和 last_name 是 Mike and Himily,  子查询返回true，因为SELECT * FROM customer WHERE first_name = 'Jon' and last_name = 'Stephens' 返回一条记录，staff表中这一行，就出现在结果集中。

　子查询不止在where中，还可以在from中。

mysql> SELECT emp_no, monthly_salary FROM
-> (SELECT emp_no, salary/12 AS monthly_salary FROM salaries) AS ms
-> LIMIT 5;

　　sql语句的执行顺序：执行from-> join -> on -> where -> group by -> having -> select -> distinct -> order by　　

　　事务：事务是作为一个逻辑工作单元在数据库上执行的操作（使用一个或多个 SQL 语句）。事务中的所有 SQL 语句的修改要么作为一个单元提交（应用于数据库）要么回滚（从数据库撤消），而不能是部分修改。事务就是要保证一组数据库操作，要么全部成功，要么全部失败。数据库事务必须是原子性、一致性、隔离性、持久性（ACID （ Atomicity 、 Consistency 、 Isolation 、 Durability ）。MySQL 每秒可以处理数千个请求，并且并行处理它们，而不是串行处理，有多个事务同时执行时，有可能出现脏读，不可重复读，和幻读。脏读就是一个事务A读取到另一个事务B还没有提交的数据，事务B只是执行了数据的更改，还没有commit，事务A就读到了更改后的数据。如果事务B回滚了，事务A的数据就不对了。不可重复读：在事务A 中做了两次查询，一个在T1时间，一个在T2时间，结果不一致，因为在T1 和T2 时间间隔内，另一个事务做了提交（更新）。初始的查询不可重复，因为再一次查询时，返回了不同的结果。幻读：事务A正在执行，在事务A读取的记录范围内，事务B添加或删除了一行记录并提交了，导致在事务A中，如要再次执行查询，会得到不同的行数，幻读是因为没有range 锁。

 

 　　mysql 默认的隔离级别是可重复读，在同一个事务中，所有查询结果都是一致的，也就是说，第一个查询语句返回的结果和以后所有查询语句返回的结果是一致的。 In this mode, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into any gaps within that range.

　　比如在一个事务中，SELECT * FROM person WHERE i BETWEEN 1 AND 4; 返回

+---+----------+
| i | name |
+---+----------+
| 1 | Vinicius |
| 2 | Sergey |
| 3 | Iwo |
| 4 | Peter |
+---+----------+

　　然后在第二个事务中，UPDATE person SET name = 'Kuzmichev' WHERE i=2; 然后commit ; 然后SELECT * FROM person WHERE i BETWEEN 1 AND 4;

i | name |
+---+-----------+
| 1 | Vinicius |
| 2 | Kuzmichev |
| 3 | Iwo |
| 4 | Peter

 　　但在在第一个事务中，SELECT * FROM person WHERE i BETWEEN 1 AND 4;

+---+----------+
| i | name |
+---+----------+
| 1 | Vinicius |
| 2 | Sergey |
| 3 | Iwo |
| 4 | Peter |
+---+----------+

　　用锁实现隔离级别：锁有表级锁，

 

　　使用select 实现插入数据。

mysql> INSERT INTO recommend (film_id, language_id, release_year, title, length)
-> SELECT film_id, language_id, release_year, title, length
-> FROM film ORDER BY RAND() LIMIT 10;

　　The overall effect is that the rows output from the SELECT statement is  inserted into the destination table by the INSERT INTO statement.

Note also that the column names don’t need to be the same for the SELECT and the INSERT

mysql> INSERT INTO art.people (person_id, first_name, last_name)
-> SELECT actor_id, first_name, last_name FROM sakila.actor;

Sometimes, you’ll encounter duplication issues when inserting with a SELECT state‐
ment.  If you want MySQL to ignore this and keep going, add the IGNORE keyword after INSERT

mysql> INSERT IGNORE INTO recommend (film_id, language_id, release_year,
-> title, length, sequence_id )
-> SELECT film_id, language_id, release_year, title, length, 1
-> FROM film LIMIT 1;

MySQL doesn’t complain, but it does report that it encountered a duplicate. Note that
the data is not changed; all we did was ignore the error.

mysql> SHOW WARNINGS;

+---------+------+-------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'recommend.PRIMARY' |
+---------+------+-------------------------------------------------+

　　

delete or update rows from more than one table in one statement, and
you can use those or other tables to decide what rows to change.

　　删除：

DELETE FROM inventory USING inventory
LEFT JOIN rental USING (inventory_id)
WHERE rental.inventory_id IS NULL;

　　 USING indicates that a filter query (a join or otherwise) is going to follow.  当删除多张表时，由于外键约束，写一个query语句可能会报错。

DELETE FROM film_actor, film USING
film JOIN film_actor USING (film_id)
LEFT JOIN inventory USING (film_id)
WHERE inventory.film_id IS NULL;

ERROR 1451 (23000): Cannot delete or update a parent row:
a foreign key constraint fails (
`sakila`.`film_actor`, CONSTRAINT `fk_film_actor_film`
FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`)
ON DELETE RESTRICT ON UPDATE CASCADE)

　　这时解决办法有两种，一种是开启事务，依次删除，

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM film_actor USING
-> film JOIN film_actor USING (film_id)
-> LEFT JOIN inventory USING (film_id)
-> WHERE inventory.film_id IS NULL;
Query OK, 216 rows affected (0.01 sec)

mysql> DELETE FROM film_category USING
-> film JOIN film_category USING (film_id)
-> LEFT JOIN inventory USING (film_id)
-> WHERE inventory.film_id IS NULL;
Query OK, 42 rows affected (0.00 sec)

mysql> DELETE FROM film USING
-> film LEFT JOIN inventory USING (film_id)
-> WHERE inventory.film_id IS NULL;
Query OK, 42 rows affected (0.00 sec)

mysql> COMMIT;

　　一种是SET foreign_key_checks=0; 删除前去掉外键检查，删除成功后，再加上外键检查

mysql> SET foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM film, film_actor, film_category
-> USING film JOIN film_actor USING (film_id)
-> JOIN film_category USING (film_id)
-> LEFT JOIN inventory USING (film_id)
-> WHERE inventory.film_id IS NULL;
Query OK, 300 rows affected (0.03 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> SET foreign_key_checks=1;

　　但不建议这么做。

　　利用多表信息更新，就是在update 表的后面，加join，提供条件。

mysql> UPDATE film JOIN film_category USING (film_id)
-> JOIN category USING (category_id)
-> SET rating = 'R' WHERE category.name = 'Horror';

　　无论是多表删除还是多表更新，都不能使用order by 和limit，更新的时候，还不能更新子查询中要读取的表。

　　Replace：它会先删除，再插入，所以，如果有外键约束，它还是会报错，

REPLACE INTO actor VALUES (1, 'Penelope', 'Guiness', NOW()); 

REPLACE INTO actor_2 (actor_id, first_name, last_name)

VALUES (1, 'Penelope', 'Guiness');

REPLACE actor_2 SET actor_id = 1,
-> first_name = 'Penelope', last_name = 'Guiness';

Note that if you don’t specify a value for a col‐
umn, it’s set to its default value, just like for INSERT

In contrast, if there isn’t a matching row in a REPLACE statement, it acts just like an
INSERT :
mysql> REPLACE actor_2 (actor_id, first_name, last_name)
-> VALUES (1000, 'William', 'Dyer');
Query OK, 1 row affected (0.00 sec)
You can tell that only the insert occurred, since only one row was affected.

If a table doesn’t have a primary key or another unique key, replacing doesn’t make
sense. This is because there’s no way of uniquely identifying a matching row in order
to delete it. When you use REPLACE on such a table, its behavior is identical to INSERT

mysql 提供了一个非标准的 INSERT ... ON DUPLICATE KEY UPDATE，和replace 功能相似，

mysql> INSERT INTO actor_3 (actor_id, first_name, last_name) VALUES
-> (1, 'Penelope', 'Guiness'), (2, 'Nick', 'Wahlberg'),
-> (3, 'Ed', 'Chase'), (1001, 'William', 'Dyer')
-> ON DUPLICATE KEY UPDATE first_name = VALUES(first_name),
-> last_name = VALUES(last_name);

 

The  EXPLAIN statement   tells you how MySQL is going to do the job in terms of the indexes, keys, and steps it’ll take if you ask it to resolve a query. 比如：EXPLAIN SELECT * FROM actor。它会返回许多字段。

　　id:  first and only select 语句，在这个query中，如果使用子查询，每一个select可能会产生不同的id。

　　select_type: SIMPLE, 表示，它没有使用子查询，也没有使用UNION 

　　type： all:  meaning all rows in the table are processed by this SELECT statement.

　　possible_keys: 可能使用到的key。key：真正使用的key。

　　rows：MySQL 需要处理的行数。filtered： query的语句返回的行数占整个table行数的比例，100表示整个表的所有行都返回了。

 

 　　

<subquery2> ? That’s a virtual
table name used here because the optimizer materialized the results of the subquery,
or in other words stored them in a temporary table in memory. You can see that the
query with an id of 2 has a select_type of MATERIALIZED . The outside query ( id 1)
will look up the results of the inner query ( id 2) from this temporary table. This is
just one of many optimizations that MySQL can perform while executing complex
queries.

　　Mysql8 提供了  EXPLAIN ANALYZE

mysql> EXPLAIN ANALYZE SELECT first_name, last_name
-> FROM actor JOIN film_actor USING (actor_id)
-> JOIN film USING (film_id)
-> WHERE title = 'ZHIVAGO CORE'\G   \G 表示不用表格

 

　　MySQL语句的执行过程，比如 select * from T where id = 10; 是怎么执行的？下图是MySQL的基本架构

 　　连接数据库，等待你的是连接器，它负责建立连接，验证身份，获取权限，维护和管理连接。客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。数据库长连接是指客户端持续有请求，则一直使用同一个连接。短连接是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。建立连接的过程比较复杂，建议尽量减少建立连接的动作，也就是尽量使用长连接。

 　　分析器 先做词法分析，做什么查询，表名，条件，

MySQL 从你输入的 "select" 这个关键字识别出来，这是一个查询语句。它也要把字符串 “T” 识别
成 “ 表名 T” ，把字符串 “ID” 识别成 “ 列 ID” 。 再做语法分析，

根据词法分析的结果，语法分析器会根据语法规则，
判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果不满足，就报错语法错误。

　　优化器：

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（ join ）
的时候，决定各个表的连接顺序，决定语句的执行方案

　　执行器：执行语句，不过要先判断你对表有没有查询权限，如果没有，返回权限错误，如果有，调用引擎提供在接口。

 　　更新语句和查询语句执行过程一样，只不过，更新流程还涉及两个重要的日志模块， redo log （重做日志）和 binlog （归档日志）。更新的时候先写日志，再写磁盘，称为 Write-Ahead Logging。具体来说，当有一条记录更新时，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。同时， InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，但如果更新较多，redo log 写满了，只好将操作记录先写到磁盘中，再把redo log 清空（也可以写一部分，清空一部分redo log）。

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是
1GB ，就是可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环

 

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。
checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文
件。
write pos 和 checkpoint 之间的是 “ 粉板 ” 上还空着的部分，可以用来记录新的操作。如果 write pos
追上 checkpoint ，表示 “ 粉板 ” 满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把
checkpoint 推进一下。
有了 redo log ， InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个
能力称为 crash-safe

　　 redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog （归档日志）。

redo log 是物理日志，记录的是 “ 在某个数据页上做了什么修改 ” ； binlog 是逻辑日志，记录的
是这个语句的原始逻辑，比如 “ 给 ID=2 这一行的 c 字段加 1 ” 。

redo log 是循环写的，空间固定会用完； binlog 是可以追加写入的。 “ 追加写 ” 是指 binlog 文件
写到一定大小后会切换到下一个，并不会覆盖以前的日志。

update T set c=c+1 where ID=2; 的执行过程

1. 执行器先找引擎取 ID=2 这一行。 ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一
行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然
后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上 1 ，比如原来是 N ，现在就是 N+1 ，得到新的一行
数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处
于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的 binlog ，并把 binlog 写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（ commit ）状态，更
新完成。

redo log 的写入拆成了两个步骤： prepare 和
commit ，这就是 " 两阶段提交 " 。为什么必须有 “ 两阶段提交 ” 呢？这是为了让两份日志之间的逻辑一致