MySql面试前必须练习到熟练的--部分语句有错；有些子查询可以用外连接代替

MySQL查询语句练习题(面试时可能会遇到哦！)

case when条件表达式

Mysql 多表查询详解

MySQL数据库——修改约束基本操作

Mysql模糊查询like效率，以及更高效的写法
“d%”会用到索引,其他大部分不会。
可用locate(sub,str,index)代替。locate(sub,str,index)：返回子串位置，不存在返回0。index指定从第index个字符（包括第index个）起查找子串。

mysql中distinct的用法
单独使用时只能放在前面，加不加括号都可以，大于2列时作用于后面所的列，只会过滤指定列都一样的记录。
可在其他函数中用count(distinct(task_id))。

MySQL数据库优化的八种方式(经典必看)
索引，锁表，事务，连接代替子查询，union代替临时表，选择最适用的字段属性尽量设notnull，外键，相同字段比较、比较代替模糊查询，不在索引上用函数（sum、avg等也会影响效率吗？）

MySQL子查询
子查询可以在使用表达式的任何地方使用，并且必须在括号中关闭。
select count() from(SELECT count() FROM 表名 WHERE 条件 GROUP BY id ) a ;

Mysql高级查询-----汇总和分组数据

group by

MySQL索引优化分析

MySql 隔离级别

SQL的主键和外键约束

外键起约束作用，为了保持表之间数据的一致性和完整性。

外键取值规则：

1. 除了null之外不能插入主表对应列中没有的值。
2. 在建外键时，可选定在删除/更新主表记录时，要级联删除/更新子表还是拒绝执行
3. 外键可是主键或唯一约束

Mysql 语句执行顺序
语法顺序：

• select 选择的列
• from 表
• where 查询的条件
• group by 分组属性（所有分组属性上值相同的记录被分为一组）
• having 分组过滤的条件（必须和group by一起使用）
• order by 排序属性（升序asc/降序desc）
• limit 起始记录位置，取记录的条数

执行顺序：

• from
• on
• join
• where
• group by
• having
• select
• distinct
• union
• order by

order by不能放在union之前，会有语法错误。

创建数据库
mysql> create database exp;
Query OK, 1 row affected (0.00 sec)
删除数据库
mysql> drop database exp;
Query OK, 0 rows affected (0.14 sec)
使用数据库
mysql> use techo
Database changed
建表
mysql> create table exp(
    -> id int not null,
    -> name char(20),
    -> score int,
    -> primary key(id),
    -> check(score between 0 and 100)
    -> ) engine = Innodb;
显示数据库所有表
mysql> show tables
    -> ;
+-----------------+
| Tables_in_techo |
+-----------------+
| admin           |
| collect         |
| comment         |
| exp             |
| fan             |
| material        |
| notice          |
| notice_techo    |
| purchase        |
| techo           |
| thumbup         |
| user            |
+-----------------+
12 rows in set (0.03 sec)
显示表结构
mysql> describe exp;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
| score | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
给id设定自增长，应该尽量避免修改表结构，这里只为测试
mysql> ALTER TABLE `techo`.`exp` 
    -> CHANGE COLUMN `id` 
    -> `id` INT(11) NOT NULL AUTO_INCREMENT ;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> describe exp;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | YES  |     | NULL    |                |
| score | int(11)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.06 sec)
插入数据
mysql> insert into exp(name,score) values('i',90);
Query OK, 1 row affected (0.03 sec)

case when条件表达式
case when 判断语句
group by 分组

mysql> select name,sum(case when score>=60 then 1 else 0 end) 及格数 ,
    -> sum(case when score<60 then 1 else 0 end) 不及格数
    -> from exp group by name;
+------+--------+----------+
| name | 及格数 | 不及格数 |
+------+--------+----------+
| i    |      5 |        0 |
| she  |      2 |        0 |
+------+--------+----------+
2 rows in set (0.09 sec)

多表查询

内连接：join，inner join。两者相同，得到的是两表on条件下的共同数据。
外连接：left [outer] join 左表数据都在，右表没有符合条件的数据的话填充为null
right [outer] join 和左连接相反
full join（mysql不支持，用left join ..union..right join 代替） 左右表符合条件的共同数据加上左右表的不符合条件的数据
cross join 笛卡尔积

注意，mysql的容错能力：

1. 一般cross join 后是加where，加on也会被解释为where
2. 内连接要加on限定条件，如果不加会被解释为交叉连接（左/右连接不加on会有语法问题）
3. 如果用逗号连接表格，会被解释为交叉连接（所以为了效率，一般都不用逗号连接表？）

设置两个表ab用来测试

mysql> select * from a;
+----+------+
| id | age  |
+----+------+
|  1 |   12 |
|  2 |   32 |
|  3 |   44 |
|  4 |   31 |
+----+------+
4 rows in set (0.00 sec)

mysql> select * from b;
+----+------+
| id | name |
+----+------+
|  2 | ^^   |
|  3 | :)   |
|  4 | w    |
|  5 | `3`  |
+----+------+
4 rows in set (0.00 sec)

内连接，得到满足on条件的a，b内部的数据

mysql> select * from a
    -> join b on a.id=b.id;
+----+------+----+------+
| id | age  | id | name |
+----+------+----+------+
|  2 |   32 |  2 | ^^   |
|  3 |   44 |  3 | :)   |
|  4 |   31 |  4 | w    |
+----+------+----+------+
3 rows in set (0.06 sec)

若on条件不是相等属性列，则结果会是查询后a与查询后b的笛卡尔积。

mysql> select * from a
    -> join b on a.id=2 and b.id>3;
+----+------+----+------+
| id | age  | id | name |
+----+------+----+------+
|  2 |   32 |  4 | w    |
|  2 |   32 |  5 | `3`  |
+----+------+----+------+
2 rows in set (0.00 sec)

外连接，left join 左连接，得到共同满足条件的左右表的数据，再加上左表剩下的数据。

mysql> select * from a
    -> left join b on a.id=b.id;
+----+------+------+------+
| id | age  | id   | name |
+----+------+------+------+
|  2 |   32 |    2 | ^^   |
|  3 |   44 |    3 | :)   |
|  4 |   31 |    4 | w    |
|  1 |   12 | NULL | NULL |
+----+------+------+------+
4 rows in set (0.00 sec)

mysql> select * from a
    -> left join b on a.id=2 and b.id>3;
+----+------+------+------+
| id | age  | id   | name |
+----+------+------+------+
|  2 |   32 |    4 | w    |
|  2 |   32 |    5 | `3`  |
|  1 |   12 | NULL | NULL |
|  3 |   44 | NULL | NULL |
|  4 |   31 | NULL | NULL |
+----+------+------+------+
5 rows in set (0.00 sec)

外连接，right join 右连接

mysql> select * from a
    -> right join b on a.id=b.id;
+------+------+----+------+
| id   | age  | id | name |
+------+------+----+------+
|    2 |   32 |  2 | ^^   |
|    3 |   44 |  3 | :)   |
|    4 |   31 |  4 | w    |
| NULL | NULL |  5 | `3`  |
+------+------+----+------+
4 rows in set (0.00 sec)

mysql> select * from a
    -> right join b on a.id=2 and b.id>3;
+------+------+----+------+
| id   | age  | id | name |
+------+------+----+------+
| NULL | NULL |  2 | ^^   |
| NULL | NULL |  3 | :)   |
|    2 |   32 |  4 | w    |
|    2 |   32 |  5 | `3`  |
+------+------+----+------+
4 rows in set (0.00 sec)

找出有name没有age的id 【where a.column is null】

mysql> select b.id id,name from a
    -> right join b on a.id = b.id
    -> where a.id is null;
+----+------+
| id | name |
+----+------+
|  5 | `3`  |
+----+------+
1 row in set (0.00 sec)

full join --> left+right+union（union后重复记录会合并）

mysql> select * from a
    -> left join b on a.id=b.id
    -> union
    -> select * from a
    -> right join b b2 on a.id=b2.id
    -> ;
+------+------+------+------+
| id   | age  | id   | name |
+------+------+------+------+
|    2 |   32 |    2 | ^^   |
|    3 |   44 |    3 | :)   |
|    4 |   31 |    4 | w    |
|    1 |   12 | NULL | NULL |
| NULL | NULL |    5 | `3`  |
+------+------+------+------+
5 rows in set (0.00 sec)

mysql> select * from a
    -> left join b on a.id=b.id
    -> where b.id is null
    -> union
    -> select * from a
    -> right join b b2 on a.id=b2.id
    -> where a.id is null;
+------+------+------+------+
| id   | age  | id   | name |
+------+------+------+------+
|    1 |   12 | NULL | NULL |
| NULL | NULL |    5 | `3`  |
+------+------+------+------+
2 rows in set (0.00 sec)

cross join 笛卡尔积

mysql> select * from a
    -> cross join b
    -> ;
+----+------+----+------+
| id | age  | id | name |
+----+------+----+------+
|  1 |   12 |  2 | ^^   |
|  2 |   32 |  2 | ^^   |
|  3 |   44 |  2 | ^^   |
|  4 |   31 |  2 | ^^   |
|  1 |   12 |  3 | :)   |
|  2 |   32 |  3 | :)   |
|  3 |   44 |  3 | :)   |
|  4 |   31 |  3 | :)   |
|  1 |   12 |  4 | w    |
|  2 |   32 |  4 | w    |
|  3 |   44 |  4 | w    |
|  4 |   31 |  4 | w    |
|  1 |   12 |  5 | `3`  |
|  2 |   32 |  5 | `3`  |
|  3 |   44 |  5 | `3`  |
|  4 |   31 |  5 | `3`  |
+----+------+----+------+
16 rows in set (0.00 sec)

指定条件，实现了内连接（join）的效果

mysql> select * from a
    -> cross join b
    -> where a.id=b.id;
+----+------+----+------+
| id | age  | id | name |
+----+------+----+------+
|  2 |   32 |  2 | ^^   |
|  3 |   44 |  3 | :)   |
|  4 |   31 |  4 | w    |
+----+------+----+------+
3 rows in set (0.02 sec)

on a.col=b.col 可用using(col)代替，但后面不可以有其他的条件

mysql> select distinct b.name,a.id
    -> from a join b using(id);
+------+----+
| name | id |
+------+----+
| ^^   |  2 |
| :)   |  3 |
| w    |  4 |
+------+----+
3 rows in set (0.00 sec)

mysql> select distinct b.name,a.id
    -> from a right join b using(id);
+------+------+
| name | id   |
+------+------+
| ^^   |    2 |
| :)   |    3 |
| w    |    4 |
| `3`  | NULL |
+------+------+
4 rows in set (0.00 sec)

distinct 只能放在前面

mysql> select distinct(a.id) from a join b on a.id=2;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

mysql> select b.name, distinct(a.id) from a,b
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct(a.id) from a,b' at line 1

有其他列时，distinct作用于后面所有的列，过滤掉id和name都一样的记录。

mysql> select distinct a.id,b.name
    -> from a,b;
+----+------+
| id | name |
+----+------+
|  1 | ^^   |
|  2 | ^^   |
|  3 | ^^   |
|  4 | ^^   |
|  1 | :)   |
|  2 | :)   |
|  3 | :)   |
|  4 | :)   |
|  1 | w    |
|  2 | w    |
|  3 | w    |
|  4 | w    |
|  1 | `3`  |
|  2 | `3`  |
|  3 | `3`  |
|  4 | `3`  |
+----+------+
16 rows in set (0.00 sec)

mysql> select distinct(a.id),name from a join b on a.id=2;
+----+------+
| id | name |
+----+------+
|  2 | ^^   |
|  2 | :)   |
|  2 | w    |
|  2 | `3`  |
+----+------+
4 rows in set (0.00 sec)

explain详解

explain 可以清楚mysql是如何处理sql语句的

explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E"; 

type 为all的是全表扫描

创建唯一索引

create unique index idx_a_id on a(id);

唯一索引 type 为const。通过索引找到值就结束扫描返回查询结果。

普通索引是ref。找到值还要继续扫描，直到将索引文件扫描完为止。

使用索引

select id from a where a =1;
explain select id from a where a =1;

extra 显示using index 表示使用了索引。若是 using filesort则是使用了内部排序（？）Using temporary则是使用了临时表。

order by 使用了文件排序（filesort）
优化：创建复合索引

create index idx_order_levelDate on itdragon_order_list (order_level,input_date);
未使用索引
explain select * from itdragon_order_list order by order_level,input_date;
使用了索引，但返回的只有索引列的数据
explain select order_level,input_date from itdragon_order_list order by order_level,input_date;
force index(索引名)：强制使用索引
explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;

再次优化：
对于重复且分布平均的字段，排序加索引的作用不大。
可以先固定这种字段的值再排序。

mysql> create table c (
    -> id int auto_increment,
    -> other varchar(10),
    -> primary key(id))
    -> engine=innodb;
mysql> explain select id from c;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | c     | index | NULL          | idx_c_id | 4       | NULL |    1 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select other from c;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | c     | index | NULL          | idx_c_o | 33      | NULL |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select other from c where other="1";
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.09 sec)
mysql> insert  c values(1,'1');
Query OK, 1 row affected (0.09 sec)
mysql> insert  c(other) values('2');
Query OK, 1 row affected (0.22 sec)

mysql> explain select other from c where other="1";
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | c     | const | idx_c_o       | idx_c_o | 33      | const |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select id from c where other="1";
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | c     | const | idx_c_o       | idx_c_o | 33      | const |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select id from c where id="1";
+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys    | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | c     | const | PRIMARY,idx_c_id | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------------+
1 row in set (0.05 sec)

mysql> explain select id from c where id="1";
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | c     | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select id from c ;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | c     | index | NULL          | idx_c_o | 33      | NULL |    2 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

select database();查看当前使用哪个数据库
show databases; 显示所有数据库

mysql> select database();
+------------+
| database() |
+------------+
| techo      |
+------------+
1 row in set (0.00 sec)

mysql> show status like 'table%'
    -> ;
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 100   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 0     |
| Table_open_cache_misses    | 0     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.00 sec)

表锁

session1锁表
mysql> lock table a write;
Query OK, 0 rows affected (0.10 sec)

session2读表

session1添加数据
mysql> insert a values(7,00);
Query OK, 1 row affected (0.46 sec)

session1释放所持有的锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

session2拿到读锁读到数据

session2
mysql> select * from a;
+----+------+
| id | age  |
+----+------+
|  1 |   12 |
|  2 |   32 |
|  3 |   44 |
|  4 |   31 |
|  7 |    0 |
+----+------+
5 rows in set (1 min 2.45 sec)

查看事务隔离级别

mysql> select @@tx_isolation
    -> ;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.06 sec)
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)

设置事务隔离级别
set [session|global] transaction isolation level {read uncommited | read committed | repeatable read | serialization}
默认的行为（不带session和global）是为下一个（未开始）事务设置隔离级别。如果你使用GLOBAL关键字，语句在全局对从那点开始创建的所有新连接（除了不存在的连接）设置默认事务级别。你需要SUPER权限来做这个。使用SESSION 关键字为将来在当前连接上执行的事务设置默认事务级别。 任何客户端都能自由改变会话隔离级别（甚至在事务的中间），或者为下一个事务设置隔离级别。

给当前会话设置隔离级别
mysql> set session transaction isolation level read uncommitted
    -> ;
Query OK, 0 rows affected (0.15 sec)

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set (0.00 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

获得行锁、表锁争用情况

表锁情况
mysql> show status like 'table%'
    -> ;
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 104   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 3     |
| Table_open_cache_misses    | 0     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.11 sec)
行锁情况
mysql> show status like 'innodb_row_lock%'
    -> ;
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

删除表数据

delete from a where id=1;
Query OK, 1 row affected (0.05 sec)