mysql必会必知

时间：2016-03-21 20:14:46 阅读：224 评论：0 收藏：0 [点我收藏+]

select distinct CHARACTER_SET_NAME from CHARACTER_SETS limit 12 offset 30;
select distinct CHARACTER_SET_NAME from CHARACTER_SETS limit 30,12;

完全限定(同时使用表明和列名)：
注:CHARACTER_SETS表属于information_schema库

mysql> select CHARACTER_SETS.CHARACTER_SET_NAME from CHARACTER_SETS;
select CHARACTER_SETS.CHARACTER_SET_NAME from information_schema.CHARACTER_SETS;

order by排序：
mysql> select * from CHARACTER_SETS order by CHARACTER_SET_NAME;

多列排序
mysql> select * from CHARACTER_SETS order by MAXLEN,CHARACTER_SET_NAME;

降序：
mysql> select * from CHARACTER_SETS order by CHARACTER_SET_NAME desc;

多列降序
mysql> select * from CHARACTER_SETS order by MAXLEN desc,CHARACTER_SET_NAME desc;

mysql> select * from CHARACTER_SETS where MAXLEN between 2 and 4 order by CHARACTER_SET_NAME;
mysql> select * from CHARACTER_SETS where CHARACTER_SET_NAME = ‘binary‘;
mysql> select * from CHARACTER_SETS where MAXLEN = 1 order by CHARACTER_SET_NAME;
单引号用来限定字符串，如果将值和字符串的列进行比较，则需要限定引号，用来与数值列进行比较的值不用引号

空值检查
mysql> select * from CHARACTER_SETS where DESCRIPTION is null;

NOT操作符在where字句中有且只要一个功能，就是否定他之后跟的任何条件
mysql> select * from CHARACTER_SETS where maxlen not in (2,3) order by CHARACTER_SET_NAME;

通配符:
mysql> select * from CHARACTER_SETS where CHARACTER_SET_NAME like ‘cp%‘;
mysql> select * from CHARACTER_SETS where CHARACTER_SET_NAME like ‘Cp___‘;
（下划线只匹配一个字符，%匹配0个或多个字符）

mysql> select * from CHARACTER_SETS where CHARACTER_SET_NAME like ‘1250‘;
Empty set (0.00 sec)

binary用来区分大小写
mysql> select * from CHARACTER_SETS where CHARACTER_SET_NAME regexp binary ‘cp‘;
+--------------------+----------------------+---------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+---------------------------+--------+
| cp850 | cp850_general_ci | DOS West European | 1 |
| cp1250 | cp1250_general_ci | Windows Central European | 1 |
| cp866 | cp866_general_ci | DOS Russian | 1 |
| cp852 | cp852_general_ci | DOS Central European | 1 |
| cp1251 | cp1251_general_ci | Windows Cyrillic | 1 |
| cp1256 | cp1256_general_ci | Windows Arabic | 1 |
| cp1257 | cp1257_general_ci | Windows Baltic | 1 |
| cp932 | cp932_japanese_ci | SJIS for Windows Japanese | 2 |
+--------------------+----------------------+---------------------------+--------+
8 rows in set (0.00 sec)

mysql> select * from CHARACTER_SETS where CHARACTER_SET_NAME regexp binary ‘Cp‘;
Empty set (0.00 sec

‘|‘是正则表达式的or操作符
mysql> select * from CHARACTER_SETS where CHARACTER_SET_NAME regexp binary ‘cp850|cp852|cp932‘;
+--------------------+----------------------+---------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+---------------------------+--------+
| cp850 | cp850_general_ci | DOS West European | 1 |
| cp852 | cp852_general_ci | DOS Central European | 1 |
| cp932 | cp932_japanese_ci | SJIS for Windows Japanese | 2 |
+--------------------+----------------------+---------------------------+--------+
3 rows in set (0.00 sec)

mysql> select * from CHARACTER_SETS where MAXLEN regexp ‘[23]‘;
mysql> select * from CHARACTER_SETS where MAXLEN regexp ‘2|3‘;
mysql> select * from CHARACTER_SETS where MAXLEN regexp ‘[^23]‘;（^取否）

mysql> select * from CHARACTER_SETS where MAXLEN regexp ‘[1234]‘;
mysql> select * from CHARACTER_SETS where MAXLEN regexp ‘[1-4]‘;

匹配特殊字符(\\)
mysql> select * from CHARACTER_SETS where MAXLEN regexp ‘\\.‘;

拼接字段：
select Concat(CHARACTER_SET_NAME,‘(‘,DESCRIPTION,‘)‘) from CHARACTER_SETS;

可通过rtrim删除右侧多余的空格来整理数据（ltrim去左边的空格）
mysql> select concat(rtrim(CHARACTER_SET_NAME) ,‘(‘, rtrim(DESCRIPTION), ‘)‘) from CHARACTER_SETS;

执行算术计算
mysql> select COLLATION_NAME,ID,SORTLEN,ID*SORTLEN as expanded_num from COLLATIONS limit 3;
+-----------------+----+---------+--------------+
| COLLATION_NAME | ID | SORTLEN | expanded_num |
+-----------------+----+---------+--------------+
| big5_chinese_ci | 1 | 1 | 1 |
| big5_bin | 84 | 1 | 84 |
| dec8_swedish_ci | 3 | 1 | 3 |
+-----------------+----+---------+--------------+
3 rows in set (0.00 sec)

mysql> select COLLATION_NAME,ID,SORTLEN,ID*SORTLEN as expanded_num from COLLATIONS;
+--------------------------+-----+---------+--------------+
| COLLATION_NAME | ID | SORTLEN | expanded_num |
+--------------------------+-----+---------+--------------+
| big5_chinese_ci | 1 | 1 | 1 |
| big5_bin | 84 | 1 | 84 |
| dec8_swedish_ci | 3 | 1 | 3 |
| dec8_bin | 69 | 1 | 69 |
| cp850_general_ci | 4 | 1 | 4 |
| cp850_bin | 80 | 1 | 80 |
| hp8_english_ci | 6 | 1 | 6 |
| hp8_bin | 72 | 1 | 72 |

mysql必会必知

原文：http://www.cnblogs.com/liyongsan/p/5303149.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)