事务:innodb支持事务，事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。
MySQL内置函数:
十六进制:0-9对应0-9；A-F对应10-15；
CHAR_LENGTH(str)
返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。
mysql> select char_length('mudy');
+---------------------+
| char_length('mudy') |
+---------------------+
| 4 |
+---------------------+
1 row in set (0.22 sec)
CONCAT(str1,str2,...)
字符串拼接
如有任何一个参数为NULL ，则返回值为 NULL。
mysql> select concat('mudy','huyue');
+------------------------+
| concat('mudy','huyue') |
+------------------------+
| mudyhuyue |
+------------------------+
1 row in set (0.04 sec)
CONCAT_WS(separator,str1,str2,...)
字符串拼接（自定义连接符）
CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。
mysql> select concat_ws('-','mudy','huyue');
+-------------------------------+
| concat_ws('-','mudy','huyue') |
+-------------------------------+
| mudy-huyue |
+-------------------------------+
1 row in set (0.00 sec)
CONV(N,from_base,to_base)
进制转换
将16进制的10转换成2进制的
mysql> select conv('10',16,2);
+-----------------+
| conv('10',16,2) |
+-----------------+
| 10000 |
+-----------------+
1 row in set (0.00 sec)
mysql> select conv('9',16,2);
+----------------+
| conv('9',16,2) |
+----------------+
| 1001 |
+----------------+
1 row in set (0.00 sec)
mysql> select conv('a',16,2);
+----------------+
| conv('a',16,2) |
+----------------+
| 1010 |
+----------------+
1 row in set (0.00 sec)
将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位，
并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。
mysql> select format(10000000000,3);
+-----------------------+
| format(10000000000,3) |
+-----------------------+
| 10,000,000,000.000 |
+-----------------------+
1 row in set (0.09 sec)
INSERT(str,pos,len,newstr)
在str的指定位置插入字符串
pos：要替换位置其实位置
len：替换的长度
newstr：新字符串
特别的：
如果pos超过原字符串长度，则返回原字符串
如果len超过原字符串长度，则由新字符串完全替换
mysql> select insert('mudy',5,1,'good');
+---------------------------+
| insert('mudy',5,1,'good') |
+---------------------------+
| mudy |
+---------------------------+
1 row in set (0.00 sec)
mysql> select insert('mudy',1,2,'good');
+---------------------------+
| insert('mudy',1,2,'good') |
+---------------------------+
| gooddy |
+---------------------------+
1 row in set (0.07 sec)
mysql> select insert('mudy',1,5,'good');
+---------------------------+
| insert('mudy',1,5,'good') |
+---------------------------+
| good |
+---------------------------+
1 row in set (0.00 sec)
INSTR(str,substr)
返回字符串 str 中子字符串的第一个出现位置。
mysql> select instr('mudy','y');
+-------------------+
| instr('mudy','y') |
+-------------------+
| 4 |
+-------------------+
1 row in set (0.05 sec)
LEFT(str,len)
返回字符串str 从开始的len位置的子序列字符。
mysql> select left('mudy',2);
+----------------+
| left('mudy',2) |
+----------------+
| mu |
+----------------+
1 row in set (0.07 sec)
LOWER(str)
变小写
UPPER(str)
变大写
LOCATE(substr,str,pos)
获取子序列索引位置
第三个参数表示起始位置
mysql> select locate('dy','mudymudy',1);
+---------------------------+
| locate('dy','mudymudy',1) |
+---------------------------+
| 3 |
+---------------------------+
1 row in set (0.04 sec)
mysql> select locate('dy','mudymudy',4);
+---------------------------+
| locate('dy','mudymudy',4) |
+---------------------------+
| 7 |
+---------------------------+
1 row in set (0.00 sec)
REPEAT(str,count)
返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。
若 count <= 0,则返回一个空字符串。
若str 或 count 为 NULL，则返回 NULL 。
mysql> select repeat('mudy',2);
+------------------+
| repeat('mudy',2) |
+------------------+
| mudymudy |
+------------------+
1 row in set (0.04 sec)
SPACE(N)
返回一个由N空格组成的字符串。
mysql> select space(4)
-> ;
+----------+
| space(4) |
+----------+
| |
+----------+
1 row in set (0.04 sec)
SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
不带有len 参数的格式从字符串str返回一个子字符串，起始于位置
pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串
，起始于位置 pos。 使用 FROM的格式为标准 SQL
语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串
结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos
使用一个负值。
mysql> select substring('imaumudy' from 4 for 3);
+------------------------------------+
| substring('imaumudy' from 4 for 3) |
+------------------------------------+
| umu |
+------------------------------------+
1 row in set (0.06 sec)
mysql> select substring('imaumudy',2,3);
+---------------------------+
| substring('imaumudy',2,3) |
+---------------------------+
| mau |
+---------------------------+
1 row in set (0.00 sec)
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
返回字符串 str ， 其中所有remstr
前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给
定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。
mysql> select trim(' mudy ');
+-----------------------+
| trim(' mudy ') |
+-----------------------+
| mudy |
+-----------------------+
1 row in set (0.00 sec)
mysql> select trim(leading 's' from 'ssssssmudysssssss');
+--------------------------------------------+
| trim(leading 's' from 'ssssssmudysssssss') |
+--------------------------------------------+
| mudysssssss |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select trim(trailing 's' from 'ssssssmudysssssss');
+---------------------------------------------+
| trim(trailing 's' from 'ssssssmudysssssss') |
+---------------------------------------------+
| ssssssmudy |
+---------------------------------------------+
1 row in set (0.00 sec)
自定义函数:
注意在函数中不可以写sql语句，可以有返回值，但是没有in out inout
支持==select nid into a from student where name = 'mudy';==
delimiter \\
CREATE FUNCTION f1(i1 int,i2 int)
RETURNS int
BEGIN
DECLARE num int;
set num = i1 + i2;
RETURN(num);
END\\
delimiter ;
mysql> select f1(3,4);
+---------+
| f1(3,4) |
+---------+
| 7 |
+---------+
1 row in set (0.07 sec)
- 索引的功能:
-
约束
- 主键
- 外键
- 唯一
- 普通
- 组合
加速查找
-
索引的生成：算法是B-tree
- 索引的种类:
- 普通索引－加速查找
- 唯一索引－加速查找，约束列数据不能重复，可以为null
- 主键索引－加速查找，约束列数据不能重复，不能为null
- 组合索引－多列可以创建一个索引文件
1、普通索引的创建
- 创建表的同时创建索引
create table student(
......
index ix_name(name)
)
- 添加索引
create index index_name on table_name(column_name)
mysql> create index nameindex on course(cname);
Query OK, 0 rows affected (0.80 sec)
Records: 0 Duplicates: 0 Warnings: 0
创建索引的代价：增加 删除 更新 都慢了
- 删除索引
drop index_name on tablename
- 查看索引
show index from table_name
mysql> show index from course;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| course | 0 | PRIMARY | 1 | cid | A | 12 | NULL | NULL | | BTREE | | |
| course | 1 | nameindex | 1 | cname | A | 10 | NULL | NULL | YES | BTREE | | |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
2、唯一索引
create unique index index_name on table_name(column_name)
3、主键索引
创建一个主键列就行了
4、组合索引
#普通组合索引:无约束
create table student(
......
index ix_name(name,age)
)
#联合唯一索引：有约束，两列数据同时不相同才能插入，不然报错
create unique index index_name on table_name(column_name,column_name2)
组合索引在查找的时候，遵循==最左匹配==的原则
select * from student where name = 'mudy'#会走索引
select * from student where name = 'mudy' and age = 12#会走索引
select * from student where age = 12 #不会走索引
1、覆盖索引
select * from tb where nid = 1
# 这种是先去索引中找，再去数据中找
select nid from tb where nid < 10
# 先去索引中找
＃zhezho难过情况，只需要在索引表中就能获取到数据时，称为覆盖索引
2、合并索引
name 与 age都是单独的索引
select * from student where name = 'mudy'
select * from student where name = 'mudy' or age = 15
执行计划－想对比较准确的表达出当前SQLyun行状况
是否走索引，走索引的效率高
explain SQL语句
mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.11 sec)
type:all 表示会对整个的数据表进行全表扫描
type:index 表示对全索引扫描
all与index的效率一般不高，都是有优化的余地
2、limit
3、range
对于索引进行范围查找的时候，会执行range
mysql> explain select * from student where sid<2;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)
注意：如果是>或!=的话，就是全表扫描了,不走索引，type:all
mysql> explain select * from course where cname='e' or cid='1';
+----+-------------+--------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| 1 | SIMPLE | course | NULL | index_merge | PRIMARY,nameindex | nameindex,PRIMARY | 33,4 | NULL | 2 | 100.00 | Using union(nameindex,PRIMARY); Using where |
+----+-------------+--------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.12 sec)
mysql> explain select * from course where cid='1';
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | course | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from course where cname='生物';
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | course | NULL | ref | nameindex | nameindex | 33 | const | 2 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.09 sec)
index_merge:表示索引合并了,possible_keys表示有可能的索引
type的可能值:
查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
ALL 全表扫描，对于数据表从头到尾找一遍
select * from tb1;
特别的：如果有limit限制，则找到之后就不在继续向下扫描
select * from tb1 where email = 'seven@live.com'
select * from tb1 where email = 'seven@live.com' limit 1;
虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。
INDEX 全索引扫描，对索引从头到尾找一遍
select nid from tb1;
RANGE 对索引列进行范围查找
select * from tb1 where name < 'alex';
PS:
between and
in
> >= < <= 操作
注意：!= 和 > 符号
INDEX_MERGE 合并索引，使用多个单列索引搜索
select * from tb1 where name = 'alex' or nid in (11,22,33);
REF 根据索引查找一个或多个值
select * from tb1 where name = 'seven';
EQ_REF 连接时使用primary key 或 unique类型
select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
CONST 常量
表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
select nid from tb1 where nid = 2 ;
SYSTEM 系统
表仅有一行(=系统表)。这是const联接类型的一个特例。
select * from (select nid from tb1 where nid = 1) as A;
参考type row,查看执行效率
如何命中索引
- like '%xx'
select * from tb1 where name like '%cn';
- 使用函数
select * from tb1 where reverse(name) = 'wupeiqi';
- or
select * from tb1 where nid = 1 or email = 'seven@live.com';
特别的：当or条件中有未建立索引的列才失效，以下会走索引
select * from tb1 where nid = 1 or name = 'seven';
select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
- 类型不一致
如果列是字符串类型，传入条件是必须用引号引起来，不然...
select * from tb1 where name = 999;
- !=
select * from tb1 where name != 'alex'
特别的：如果是主键，则还是会走索引
select * from tb1 where nid != 123
- >
select * from tb1 where name > 'alex'
特别的：如果是主键或索引是整数类型，则还是会走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123
- order by
select email from tb1 order by name desc;
当根据索引排序时候，选择的映射如果不是索引，则不走索引
特别的：如果对主键排序，则还是走索引：
select * from tb1 order by nid desc;
- 组合索引最左前缀
如果组合索引为：(name,email)
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
int类型的!=
mysql> explain select * from student where class_id != 2;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | classindex | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.07 sec)
mysql> explain select * from student where class_id > 2;
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | range | classindex | classindex | 5 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.03 sec)
mysql> explain select * from student where class_id < 2;
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | range | classindex | classindex | 5 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
面试必备
MySQL翻页
第一页 where nid > 16 limit 10
慢日志
ysql> show variables like '%query%';
+------------------------------+--------------------------------------+
| Variable_name | Value |
+------------------------------+--------------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 10.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/liuna-slow.log |
+------------------------------+--------------------------------------+
13 rows in set (0.30 sec)
