事務(wù):innodb支持事務(wù)，事務(wù)用于將某些操作的多個(gè)SQL作為原子性操作，一旦有某一個(gè)出現(xiàn)錯誤，即可回滾到原來的狀態(tài)，從而保證數(shù)據(jù)庫數(shù)據(jù)完整性。
MySQL內(nèi)置函數(shù):
十六進(jìn)制:0-9對應(yīng)0-9；A-F對應(yīng)10-15；
CHAR_LENGTH(str)
返回值為字符串str 的長度，長度的單位為字符。一個(gè)多字節(jié)字符算作一個(gè)單字符。
對于一個(gè)包含五個(gè)二字節(jié)字符集, LENGTH()返回值為 10, 而CHAR_LENGTH()的返回值為5。
mysql> select char_length('mudy');
+---------------------+
| char_length('mudy') |
+---------------------+
| 4 |
+---------------------+
1 row in set (0.22 sec)
CONCAT(str1,str2,...)
字符串拼接
如有任何一個(gè)參數(shù)為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)
進(jìn)制轉(zhuǎn)換
將16進(jìn)制的10轉(zhuǎn)換成2進(jìn)制的
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)
將數(shù)字X 的格式寫為'#,###,###.##',以四舍五入的方式保留小數(shù)點(diǎn)后 D 位，
并將結(jié)果以字符串的形式返回。若 D 為 0, 則返回結(jié)果不帶有小數(shù)點(diǎn)，或不含小數(shù)部分。
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：要替換位置其實(shí)位置
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 中子字符串的第一個(gè)出現(xiàn)位置。
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)
獲取子序列索引位置
第三個(gè)參數(shù)表示起始位置
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)
返回一個(gè)由重復(fù)的字符串str 組成的字符串，字符串str的數(shù)目等于count 。
若 count <= 0,則返回一個(gè)空字符串。
若str 或 count 為 NULL，則返回 NULL 。
mysql> select repeat('mudy',2);
+------------------+
| repeat('mudy',2) |
+------------------+
| mudymudy |
+------------------+
1 row in set (0.04 sec)
SPACE(N)
返回一個(gè)由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)
不帶有l(wèi)en 參數(shù)的格式從字符串str返回一個(gè)子字符串，起始于位置
pos。帶有l(wèi)en參數(shù)的格式從字符串str返回一個(gè)長度同len字符相同的子字符串
，起始于位置 pos。 使用 FROM的格式為標(biāo)準(zhǔn) SQL
語法。也可能對pos使用一個(gè)負(fù)值。假若這樣，則子字符串的位置起始于字符串
結(jié)尾的pos 字符，而不是字符串的開頭位置。在以下格式的函數(shù)中可以對pos
使用一個(gè)負(fù)值。
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中沒有一個(gè)是給
定的,則假設(shè)為BOTH 。 remstr 為可選項(xiàng)，在未指定情況下，可刪除空格。
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)
自定義函數(shù):
注意在函數(shù)中不可以寫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
- 索引的種類:
- 普通索引－加速查找
- 唯一索引－加速查找，約束列數(shù)據(jù)不能重復(fù)，可以為null
- 主鍵索引－加速查找，約束列數(shù)據(jù)不能重復(fù)，不能為null
- 組合索引－多列可以創(chuàng)建一個(gè)索引文件
1、普通索引的創(chuàng)建
- 創(chuàng)建表的同時(shí)創(chuàng)建索引
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
創(chuàng)建索引的代價(jià)：增加 刪除 更新 都慢了
- 刪除索引
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、主鍵索引
創(chuàng)建一個(gè)主鍵列就行了
4、組合索引
#普通組合索引:無約束
create table student(
......
index ix_name(name,age)
)
#聯(lián)合唯一索引：有約束，兩列數(shù)據(jù)同時(shí)不相同才能插入，不然報(bào)錯
create unique index index_name on table_name(column_name,column_name2)
組合索引在查找的時(shí)候，遵循==最左匹配==的原則
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
# 這種是先去索引中找，再去數(shù)據(jù)中找
select nid from tb where nid < 10
# 先去索引中找
＃zhezho難過情況，只需要在索引表中就能獲取到數(shù)據(jù)時(shí)，稱為覆蓋索引
2、合并索引
name 與 age都是單獨(dú)的索引
select * from student where name = 'mudy'
select * from student where name = 'mudy' or age = 15
執(zhí)行計(jì)劃－想對比較準(zhǔn)確的表達(dá)出當(dāng)前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 表示會對整個(gè)的數(shù)據(jù)表進(jìn)行全表掃描
type:index 表示對全索引掃描
all與index的效率一般不高，都是有優(yōu)化的余地
2、limit
3、range
對于索引進(jìn)行范圍查找的時(shí)候，會執(zhí)行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的可能值:
查詢時(shí)的訪問方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
ALL 全表掃描，對于數(shù)據(jù)表從頭到尾找一遍
select * from tb1;
特別的：如果有l(wèi)imit限制，則找到之后就不在繼續(xù)向下掃描
select * from tb1 where email = 'seven@live.com'
select * from tb1 where email = 'seven@live.com' limit 1;
雖然上述兩個(gè)語句都會進(jìn)行全表掃描，第二句使用了limit，則找到一個(gè)后就不再繼續(xù)掃描。
INDEX 全索引掃描，對索引從頭到尾找一遍
select nid from tb1;
RANGE 對索引列進(jìn)行范圍查找
select * from tb1 where name < 'alex';
PS:
between and
in
> >= < <= 操作
注意：!= 和 > 符號
INDEX_MERGE 合并索引，使用多個(gè)單列索引搜索
select * from tb1 where name = 'alex' or nid in (11,22,33);
REF 根據(jù)索引查找一個(gè)或多個(gè)值
select * from tb1 where name = 'seven';
EQ_REF 連接時(shí)使用primary key 或 unique類型
select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
CONST 常量
表最多有一個(gè)匹配行,因?yàn)閮H有一行,在這行的列值可被優(yōu)化器剩余部分認(rèn)為是常數(shù),const表很快,因?yàn)樗鼈冎蛔x取一次。
select nid from tb1 where nid = 2 ;
SYSTEM 系統(tǒng)
表僅有一行(=系統(tǒng)表)。這是const聯(lián)接類型的一個(gè)特例。
select * from (select nid from tb1 where nid = 1) as A;
參考type row,查看執(zhí)行效率
如何命中索引
- like '%xx'
select * from tb1 where name like '%cn';
- 使用函數(shù)
select * from tb1 where reverse(name) = 'wupeiqi';
- or
select * from tb1 where nid = 1 or email = 'seven@live.com';
特別的：當(dāng)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'
特別的：如果是主鍵或索引是整數(shù)類型，則還是會走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123
- order by
select email from tb1 order by name desc;
當(dāng)根據(jù)索引排序時(shí)候，選擇的映射如果不是索引，則不走索引
特別的：如果對主鍵排序，則還是走索引：
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)
