--------------------------------内置函数-数学函数-----------------------------------------
//十进制转换二进制
BIN(decimal_number)
mysql>select BIN('192'); //11000000
//四舍五入
ROUND()
//向上取整
CEILING(number);
//向下取整
FLOOR(number);
//取最大值
MAX(col); //group by 时用
mysql>select student_name,min(test_score),max(test_score) from student group by student_name;
//取最小值
MIN(col); //group by 时用
//开平方
SQRT(number)
//0-1内的随机值
RAND();
mysql> select * from t1 order by rand();
--------------------------------内置函数-日期函数-----------------------------------------
//返回当前日期 2015-05-11
CURDATE();
//返回当前时间 14:48:52
CURTIME();
//返回当前日期时间 2015-05-11 14:48:52;
NOW()
//返回当前date的UNIX时间戳 1334772617
NUIX_TIMESTAMP(date)
//返回当前的UNIX时间戳的日期值
FROM_NUIXTIME()
//返回当前date的第几周
WEEK(date)
//返回当前date的年份
YEAR(date)
//返回起始时间 和结束时间 之间的天数
DATEDIFF(expr,expr2)
--------------------------------内置函数-正则表达式-----------------------------------------
mysql> select "linux is very good!" REGEXP "^linux";
mysql> select "linux is very good!" REGEXP ".*";
mysql> select name,email form t3 where email REGEXP "@163[.,]com$"; //邮箱
相当于 select name,email form t3 where email like "%@163.com" or email like "%@163,com";
--------------------------------内置函数-统计----------------------------------------------
mysql> select cname,pname,count(pname) from demo group by cname,pname with rollup;
+---------+--------+--------+
|cname |pname |count(pname)|
+---------+--------+--------+
|bj |hd |3 |
|bj |xc |2 |
|bj |NULL |5 | //统计结果
|sh |hd |3 |
|sh |xc |1 |
|sh |NULL |4 | //统计结果
|NULL |NULL |9 | //总计结果
+---------+--------+--------+
--------------------------------内置函数-查询操作次数-----------------------------------------
mysql> show status; //所有事件
mysql> show status like "com%"; //所有com事件
mysql> show status like "com_select%"; //所有select事件
MariaDB [test]> show global status like "com_select%"; //从本次登录以来,所有查询事件的次数
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 5 |
+---------------+-------+
mysql> show status like "innodb_rows%"; //所有select事件
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Innodb_rows_deleted | 0 |
| Innodb_rows_INSERTed | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
+----------------------+-------+
MariaDB [test]> show status like "connections%"; //连接次数
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections | 5 |
+---------------+-------+
MariaDB [test]> show status like "connections%"; //服务器工作的秒器
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections | 5 |
+---------------+-------+
MariaDB [test]> show status like "slow_queries%"; //慢查询次数
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 0 |
+---------------+-------+
MariaDB [(none)]> show variables like "%slow%"; //慢查询设置
+---------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_filter | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_queries | OFF //慢查询没有开启 |
| log_slow_rate_limit | 1 |
| log_slow_verbosity | |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | localhost-slow.log |
+---------------------+--------------------------------------------------------------------------------------------------------------+
MariaDB [(none)]> show variables like "%long%";
+---------------------------------------------------+-----------+
| Variable_name | Value |
+---------------------------------------------------+-----------+
| deadlock_search_depth_long | 15 |
| deadlock_timeout_long | 50000000 |
| long_query_time | 10.000000 | //慢查询的时间
| max_long_data_size | 1048576 |
| performance_schema_events_waits_history_long_size | 10000 |
+---------------------------------------------------+-----------+
--------------------------------内置函数-查询表结构-----------------------------------------
MariaDB [test]> desc t3;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(8) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
MariaDB [test]> desc select * from t3\G;
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys|| key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 1 | |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
table 表名
select_type 查询类型: SIMPLE单表查询, UNION多表查询,PRIMARY主键查询,SUBQUERY子查询
key 普通索引
key_len 索引字段的长度
rows 影响行数
Extra 可能用过什么 Using where Using index
type 查询类型 ALL通过全表扫描得到数据 range范围查询 index索引查询 system表仅一行 const仅一行匹配 eq_ref对于前面的每一行使用主键和唯一 index_merge索引合并优化 unique_subquery主键子查询 index_subquery非主键子查询
possible_keys 可能用到的索引
MariaDB [test]> select * from t3 where id !=0;
+----+------+
| id | name |
+----+------+
| 1 | abc |
| 2 | bbb |
| 3 | ccc |
+----+------+
MariaDB [test]> show index from t3;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t3 | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| t3 | 1 | in_name | 1 | name | A | 3 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
MariaDB [test]> desc select * from t3 where name = 'abc';
+------+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
| 1 | SIMPLE | t3 | ref | in_name | in_name | 10 | const | 1 | Using where; Using index |
+------+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
--------------------------------索引优化-索引使用情况-----------------------------------------
MariaDB [test]> show status like 'handler_read%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 2 |
| Handler_read_last | 0 |
| Handler_read_next | 4 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_deleted | 0 |
| Handler_read_rnd_next | 22 |
+--------------------------+-------+
handler-read_key 这个值代表了一个行被索引值读的次数
Handler_read_rnd_next 这个值越高,说明查询效率越低,应建立索引补救!!!!!
--------------------------------分析优化-检查一个表是否有错误-----------------------------------------
MariaDB [test]> check table t3;
+---------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t3 | check | status | OK |
+---------+-------+----------+----------+
服务器字符集
--------------------------------优化-服务器字符集-----------------------------------------
MariaDB [(test)]> \s
--------------
mysql Ver 15.1 Distrib 5.5.37-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 4
Current database: test
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 5.5.37-MariaDB-log MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 3 hours 57 min 11 sec
Threads: 2 Questions: 60 Slow queries: 0 Opens: 10 Flush tables: 2 Open tables: 33 Queries per second avg: 0.004
--------------
如果服务器不是utf8,
在vi /etc/my.cnf 中[mysqld]中加入:charater-set-server=utf8
MariaDB [test]> show charset;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+----------+-----------------------------+---------------------+--------+
39 rows in set (0.00 sec)
优化-慢查询
--------------------------------优化-慢查询-----------------------------------------
MariaDB [test]> show variables like "%slow%";
+---------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_filter | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_queries | OFF |
| log_slow_rate_limit | 1 |
| log_slow_verbosity | |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | localhost-slow.log |
+---------------------+--------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)
如果服务器没有开启慢查询,
在vi /etc/my.cnf 中[mysqld]中加入:charater-set-server=utf8
log_slow_queries=slow.log
long_query_time=10
MariaDB [test]> show variables like "%long%";
+---------------------------------------------------+-----------+
| Variable_name | Value |
+---------------------------------------------------+-----------+
| deadlock_search_depth_long | 15 |
| deadlock_timeout_long | 50000000 |
| long_query_time | 10.000000 |
| max_long_data_size | 1048576 |
| performance_schema_events_waits_history_long_size | 10000 |
+---------------------------------------------------+-----------+
5 rows in set (0.00 sec)
