聚合与分组聚合

关于 COUNT 函数，总结如下：

  • COUNT(n) 和 COUNT(*) 用于统计表中的总行数，不关心列值是否为 NULL
  • COUNT(expr) 用于统计列值非 NULL 的行记录数
  • COUNT(DISTINCT expr) 用于统计列值不同且非 NULL 的行记录数
1SELECT user_id, SUM(budget) FROM ad_unit WHERE unit_status = 0 GROUP BY user_id HAVING SUM(budget) > 5000;

很有用的条件判断函数与系统函数

 1mysql> SELECT id, type, name, salary FROM worker;
 2+----+------+--------+--------+
 3| id | type | name   | salary |
 4+----+------+--------+--------+
 5|  1 | A    | tom    |   1800 |
 6|  2 | B    | jack   |   2100 |
 7|  3 | C    | pony   |   NULL |
 8|  4 | B    | tony   |   3600 |
 9|  5 | B    | marry  |   1900 |
10|  6 | C    | tack   |   1200 |
11|  7 | A    | tick   |   NULL |
12|  8 | B    | clock  |   2000 |
13|  9 | C    | noah   |   1500 |
14| 10 | C    | jarvis |   1800 |
15+----+------+--------+--------+

IF 条件判断函数

 1mysql> SELECT name, IF(type='A', '研发', '非研发') AS type FROM worker WHERE id IN (1, 2);
 2+------+-----------+
 3| name | type      |
 4+------+-----------+
 5| tom  | 研发      |
 6| jack | 非研发    |
 7+------+-----------+
 8    
 9    
10mysql> SELECT name, IFNULL(salary, 0) AS salary FROM worker WHERE id IN (1, 2, 3);
11+------+--------+
12| name | salary |
13+------+--------+
14| tom  |   1800 |
15| jack |   2100 |
16| pony |      0 |
17+------+--------+

CASE 条件判断函数

1CASE expr
2 WHEN v1 THEN r1
3 ......
4 WHEN vx THEN rx
5 ELSE rn
6END

 1mysql> SELECT
 2    ->     name,
 3    ->     (CASE type
 4    ->       WHEN 'A' THEN '研发'
 5    ->       WHEN 'B' THEN '测试'
 6    ->       WHEN 'C' THEN '运维'
 7    ->       ELSE '其他'
 8    ->     END) AS type
 9    ->   FROM
10    ->     worker
11    ->   WHERE
12    ->     id IN (
13    ->       1, 2, 3
14    ->     );
15+------+--------+
16| name | type   |
17+------+--------+
18| tom  | 研发   |
19| jack | 测试   |
20| pony | 运维   |
21+------+--------+

系统函数

 1
 2SELECT VERSION(); // MySQL 服务器版本
 3
 4SELECT CONNECTION_ID(); // 查询客户端连接 ID
 5
 6
 7mysql> SELECT * FROM information_schema.PROCESSLIST;
 8+----+------+-----------------+--------------------+---------+------+-----------+----------------------------------------------+
 9| ID | USER | HOST            | DB                 | COMMAND | TIME | STATE     | INFO                                         |
10+----+------+-----------------+--------------------+---------+------+-----------+----------------------------------------------+
11|  4 | root | localhost       | imooc_mysql        | Query   |    0 | executing | SELECT * FROM information_schema.PROCESSLIST |
12|  2 | root | localhost:50675 | imooc_mysql        | Sleep   |   27 |           | NULL                                         |
13|  5 | root | localhost:58433 | information_schema | Sleep   |   67 |           | NULL                                         |
14|  3 | root | localhost:50676 | NULL               | Sleep   |   26 |           | NULL                                         |
15|  6 | root | localhost:58446 | NULL               | Sleep   |   38 |           | NULL                                         |
16+----+------+-----------------+--------------------+---------+------+-----------+----------------------------------------------+
17
18mysql> SELECT THREAD_ID, NAME, TYPE, PROCESSLIST_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 4;
19+-----------+---------------------------+------------+----------------+
20| THREAD_ID | NAME                      | TYPE       | PROCESSLIST_ID |
21+-----------+---------------------------+------------+----------------+
22|        30 | thread/sql/one_connection | FOREGROUND |              4 |
23+-----------+---------------------------+------------+----------------+
24
25
26SELECT CHARSET('慕课网'), CHARSET('MySQL');
27
28select COLLATION('MySQL'), COLLATION(CONVERT('MySQL' USING gbk));
29
30SELECT LAST_INSERT_ID();

通过 SHOW PROCESSLIST 和 SHOW FULL PROCESSLIST 语句也可以查看连接信息

SHOW PROCESSLIST 只会打印前 100 条连接信息，而 SHOW FULL PROCESSLIST 正如它的名字一样，可以打印全部的连接信息

  • Id：用户客户端连接 MySQL 时，系统自动分配的连接 ID
  • User：当前连接的用户名
  • Host：当前用户的 id 和 端口号
  • db：当前连接选择的数据库，如果没有选择，则是 NULL
  • Command：当前连接执行的命令，取值为 Sleep（睡眠）、Query（查询）、Connect（连接）
  • Time：状态持续的时间，单位是秒
  • State：当前连接执行 SQL 语句的状态
  • Info：显示当前执行的 SQL 语句

用户与权限

 1CREATE USER 'imooc-1'@'localhost' IDENTIFIED BY 'imooc';
 2    
 3GRANT ALL PRIVILEGES ON *.* TO 'imooc-2'@'localhost' IDENTIFIED BY 'imooc';
 4FLUSH PRIVILEGES;
 5    
 6
 7    -- 授予所有的权限
 8GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'HOST';
 9
10-- 授予某个库所有表的所有权限
11GRANT ALL PRIVILEGES ON DB_NAME.* TO 'USERNAME'@'HOST';
12
13-- 授予某个库所有表的 SELECT、UPDATE 权限
14GRANT SELECT, UPDATE ON DB_NAME.* TO 'USERNAME'@'HOST';
15
16-- 授予某个库下某个表的 INSERT 权限
17GRANT INSERT ON DB_NAME.TABLE_NAME TO 'USERNAME'@'HOST';
18
19-- 授予某个库下某个表的某个列的 UPDATE 权限
20GRANT UPDATE(COLUMN_NAME) ON DB_NAME.TABLE_NAME TO 'USERNAME'@'HOST';
21
22-- 授予某个库下创建、修改、删除表结构的权限
23GRANT CREATE ON DB_NAME.* TO 'USERNAME'@'HOST';
24GRANT ALTER ON DB_NAME.* TO 'USERNAME'@'HOST';
25GRANT DROP ON DB_NAME.* TO 'USERNAME'@'HOST';
26
27-- 撤销某个库下某个表的 DROP 权限
28REVOKE DROP ON DB_NAME.TABLE_NAME FROM 'USERNAME'@'HOST';
29
30-- 撤销某个库下某个表的所有权限
31REVOKE ALL ON DB_NAME.TABLE_NAME FROM 'USERNAME'@'HOST';
32
33SHOW GRANTS FOR 'imooc-1'@'localhost';

数据备份与恢复

这里只介绍mysqldump工具

 1# 导出 imooc_mysql 库的所有数据，如果有更多的库，空格隔开即可
 2mysqldump -uroot -proot --databases imooc_mysql > ~/QinyiZhang/mysql_backup_data/imooc_mysql.sql
 3
 4# 导出 imooc_mysql 库中的 worker 表数据，如果有更多的表，空格隔开即可
 5# 需要注意，导出指定表只能针对一个库，且导出指定表的 SQL 中没有创建库的语句，只有删除表、创建表、插入数据
 6mysqldump -uroot -proot --databases imooc_mysql --tables worker > ~/QinyiZhang/mysql_backup_data/imooc_mysql_worker.sql
 7
 8# 导出 imooc_mysql 库中 worker 表 id > 5 的数据（字段是数字类型）
 9# 这也被称为条件导出，如果有多个表的条件相同，则可以同时导出多个表
10mysqldump -uroot -proot --databases imooc_mysql --tables worker --where='id > 5' > ~/QinyiZhang/mysql_backup_data/imooc_mysql_worker_id_greater_5.sql
11
12# 导出 imooc_mysql 库中 worker 表 type = B 的数据（字段是字符类型）
13mysqldump -uroot -proot --databases imooc_mysql --tables worker --where="type = 'B'" > ~/QinyiZhang/mysql_backup_data/imooc_mysql_worker_type_equal_B.sql
14
15# 只导出 imooc_mysql 库中 worker 表结构，不导出数据
16mysqldump -uroot -proot --no-data --databases imooc_mysql --tables worker > ~/QinyiZhang/mysql_backup_data/imooc_mysql_worker_only_table.sql

mysqldump 提供了 –single-transaction 选项。使用这个选项会在导出数据之前提交一个 BEGIN SQL 语句，即把备份操作放在一个事务中去执行，不会阻塞任何应用程序且能保证导出时数据库的一致性状态。

1mysqldump --single-transaction -uroot -proot --databases imooc_mysql > ~/QinyiZhang/mysql_backup_data/imooc_mysql_single_transaction.sql
2# mysqldump 还有一个非常重要的选项：-F，它将在导出数据之后生成一个新的 binlog 文件
3mysqldump --single-transaction -uroot -proot --databases imooc_mysql -F > ~/QinyiZhang/mysql_backup_data/imooc_mysql_new_binlog.sql

恢复数据

mysql -uroot -proot imooc_mysql < ~/QinyiZhang/mysql_backup_data/imooc_mysql_worker.sql

事务隔离级别

  • READ-UNCOMMITTED：它是最低的隔离级别，正如它的名称一样，它允许一个事务读取其他事务未提交的数据。这个隔离级别很少在工业环境中应用，因为它的性能并不会比其他高级别的性能好很多
  • READ-COMMITTED：它可以保证一个事务修改的数据提交之后才能被其他的事务读取。这个隔离级别是大多数数据库系统的默认隔离级别，但并不是 MySQL 默认的
  • REPEATABLE-READ：它的核心在于 “可重复”，即在一个事务内，对同一字段的多次读取结果都是相同的，也是 MySQL 的默认事务隔离级别
  • SERIALIZABLE：它是最高的隔离级别，花费的代价也是最高的，事务的处理是顺序执行的。在这个级别上，可能会导致大量的锁超时现象和锁竞争。同样，在工业级环境中，很少被使用
 1-- global.tx_isolation 代表系统级的隔离级别，即对所有的会话都生效
 2-- tx_isolation 代表会话级隔离级别，只对当前会话生效
 3mysql> SELECT @@global.tx_isolation, @@tx_isolation;
 4+-----------------------+-----------------+
 5| @@global.tx_isolation | @@tx_isolation  |
 6+-----------------------+-----------------+
 7| REPEATABLE-READ       | REPEATABLE-READ |
 8+-----------------------+-----------------+
 9    
10-- 查看默认的 SQL 语句自动提交
11mysql> SHOW VARIABLES LIKE 'autocommit';
12+---------------+-------+
13| Variable_name | Value |
14+---------------+-------+
15| autocommit    | ON    |
16+---------------+-------+
17
18-- 关闭自动提交
19mysql> SET autocommit = off;
20
21-- 检验自动提交是否已关闭
22mysql> SHOW VARIABLES LIKE 'autocommit';
23+---------------+-------+
24| Variable_name | Value |
25+---------------+-------+
26| autocommit    | OFF   |
27+---------------+-------+
  • start transaction：显式地开启一个事务
  • commit：提交事务，使得对数据库做的所有修改成为永久性
  • rollback：回滚结束用户的事务，并撤销正在进行的所有未提交的修改

通过锁解决并发数据

不通过索引条件查询时，InnoDB 一定会使用表锁，而不是行锁（因为没有索引只能全表扫描）

查询时，不论是使用主键索引、唯一索引或者普通的索引，InnoDB 都会使用行锁来对数据加锁

索引定义及其优化

索引的优点：

  • 减少扫描的数据量，加速查询
  • 减少或完全消除数据库的排序操作（ORDER BY），因为索引是有序的
  • 将服务器的随机 IO 变为顺序 IO，例如，想要查询 salary 处于 1500 ~ 2100 的员工，就可以按照索引顺序查询

索引的缺点：

  • 索引会占据额外的存储空间（毕竟它是数据结构），包括磁盘和内存
  • 由于对数据需要排序，自然会影响到数据更新（插入、更新、删除）的速度
  • 几乎任何技术或优化都具有两面性，有优点，自然也就会有缺点。所以，索引虽好，不要滥用。一定要在足够理解索引和业务的基础之上再去选择创建索引、使用索引。

叶子结点才存储数据，页与页之间是双链表

查看索引占据的空间大小

MySQL 服务器所有索引占据的空间

1mysql> SELECT CONCAT(ROUND(SUM(index_length) / (1024*1024), 2), ' MB') FROM information_schema.TABLES;
2+---------------------------------------------------------+
3| CONCAT(ROUND(SUM(index_length) / (1024*1024), 2), ' MB') |
4+---------------------------------------------------------+
5| 0.26 MB                                                  |
6+---------------------------------------------------------+

单独查询一个库或一个表占据的索引空间

 1mysql> SELECT
 2    ->     CONCAT(ROUND(SUM(index_length) / (1024*1024), 2), ' MB')
 3    ->   FROM
 4    ->     information_schema.TABLES
 5    ->   WHERE
 6    ->     TABLE_SCHEMA = 'imooc_mysql';
 7+----------------------------------------------------------+
 8| CONCAT(ROUND(SUM(index_length) / (1024*1024), 2), ' MB') |
 9+----------------------------------------------------------+
10| 0.03 MB                                                  |
11+----------------------------------------------------------+
12
13mysql> SELECT
14    ->     CONCAT(ROUND(SUM(index_length) / (1024*1024), 2), ' MB')
15    ->   FROM
16    ->     information_schema.TABLES
17    ->   WHERE
18    ->     TABLE_SCHEMA = 'imooc_mysql'
19    ->     AND TABLE_NAME = 'worker';
20+----------------------------------------------------------+
21| CONCAT(ROUND(SUM(index_length) / (1024*1024), 2), ' MB') |
22+----------------------------------------------------------+
23| 0.03 MB                                                  |
24+----------------------------------------------------------+