Mysql笔记

1. 选择数据库 use database;
2. 展示数据库/表 show databases;/show tables;
3. 检索不同行(去重)distinct 例:select distinct id from table;
4. 限制结果(限制行数)limit  例:select name from table limit 5;
5. 检索单个列               例:select name from table;
6. 检索多个列               例:select id,name,age from table;
7. 排序数据order by(升序:asc/降序:desc)例:select id from table order by name;
8. 空值检查 null            例:select name from table where price is null;
9. 范围值检查between and    例:select name from table where price between 5 and 10;
10. 百分号通配符%('%彭%'/'彭%'/'%彭')例:select id,name from table where name like '%王%';
11. 下划线(_)通配符          例:select id,name from table where name like '_芙蓉';
12. 基本字符匹配regexp       例:select name from table where name regexp '.000' order by name;
13. 匹配字符之一             例:select name from table where name regexp '[123] ton' order by name;
14. 匹配特殊字符\\           例:select name from table where name regexp '\\.' order by name;
15. 匹配多个实例             例:select name from table where name regexp '\\([0-9] sticks?\\)' order by name;
16. 匹配任意数字[[:digit:]]  例:select name from table name regexp '[[:digit:]]{4}' order by name;
17. 拼接concat               例:select concat(name, '(',country,')') from table order by name;
18. 去掉右边的所有空格rtrim  例:select concat(rtrim(name),'(',rtrim(county),')') from table order by name;
19. 匹配发音类似 soundex()   例:select name,age from table where soundex(name)=soundex('Y Lie');
20. union编写多条select语句  例:select a_id,b_id,b_price from table where price<=5 union select a_id,b_id,b_price from table where a_id in (1001,1002);
21. 包含或取消重复的行union all 例:select select a_id,b_id,b_price from table where price<=5 union all selecta_id,b_id,b_price from table where a_id in (1001,1002);
22. 按指定要求进行顺序排序 field()  例: