书写规范

• 尽量一个 保留字 跟一个 变量 占一行
• 保留字 尽量大写

链接登录数据库 和 使用 show

# 命令行登录(-u/-p后面无空格)
mysql -u username -p password

# 退出
exit;
quit;

# 展示数据库列表
show databases;

# 切换到 test 数据库
use test;

# 展示表 user 的所有属性详情
show columns from user;
describe user;

# 显示创建该数据库或表的 myqsl 脚本
show create database database_name;
show create table table_name;

select 语句

# 从指定的表中检索出指定的列
select column_name1, column_name2 ... 
from table_name;
# 检索表中所有列
select * 
from table_name;

# 使用 distinct 去重复的行
select distinct column_name 
from table_name;

若使用 distinct 对两个或多个列进行去重，除非所有这些列都相同，否则检索出所有的行

select 和 limit

# 检索指定内容的前五条（未满则能显示多少显示多少）
select column_name 
from table_name 
limit 5;
# 检索指定内容从下标 3 开始的后 4 条数据（下标开始是 0 ）
select column_name 
from table_name 
limit 3, 4;

# 上一条语句也可以这么写（MySQL5 特性，为了不弄混）
select column_name 
from table_name 
limit 4 offset 3;

使用完全限定的表名

可以将 select 中使用的 列名 和 表名 都加上上一级的名字

# 列名完全限定
select user.username 
from user;
# 列名表名都完全限定
select user.username 
from cerw.user;

排序检索数据

select * from user;检索出来的数据顺序一般是他们添加到表中的顺序, 但如果数据遭到修改或删除, 表内的顺序可能会受到 MySQL 重用回收存储空间的影响, 所以不应该依赖默认没有设置排序规则的数据顺序, 它并不可靠.

# 可以使用 非选择列进行排序（就像这里的 password）
select username 
from user 
order by password;

按照多个列排序

# username 为第一排序优先级，passowrd 为第二排序优先级
select username
from user
order by username, password;

指定排序方向

# 为 username 指定降序desc，就只会对 username 生效
select username, password
from user
order by username desc, password;

desc 关键字只应用到直接位于前面的列名

如果想要对每个列都进行降序排序， 需要每一列后都加上 desc，不然默认就是升序 asc

找出最大值: order by 和 limit 组合

注意 limit 要在 order by 的后面

select price
from products
order by price desc
limit 1;

过滤数据

# order by 必须在 where 的后面
select username, password, price
from user
where price = 5;
order by username;

# 常用的 where 子句操作符
# 不等于 <> !=
# < <= = > >=
# between

MySQL 在执行匹配时默认不区分大小写, 即 name = 'powerstot'也能匹配到 结果为 'Powerstot' 的值

字符串用单引号, 数值不用

# between 查询结果包括两端, 即闭区间
select username, password, price 
from user
where price between 5, 10;

空值检查

# 查找指定值为空的行
select username
from user
where username is null

NULL 与 不匹配

在通过过滤选择行时, 数据库并不会返回对应数据为 NULL 值的行, 因为未知具有特殊的含义, 数据库不知道是否匹配, 所以在 匹配过滤时 和 不匹配过滤时 都不返回他们

因此, 过滤数据时, 一定不要忘记加上返回那些被过滤列是 NULL 值的行.

使用通配符(模糊查询) & 技巧

‘%’通配符

代表任意长度任意字符

select *
from user
where username like '%Powers%';

注意尾空格, %Powers 不能匹配Powers后面带有空格的行

最好解决这个这个问题的办法就是去掉尾空格, 使用函数(?)

注意 NULL , 虽然通配符能匹配任何内容, 但是where xxx like '%' 也不回匹配内容为 NULL 的行

‘_’通配符

代表一个字符长度的任意字符

select *
from user
where username like 'Powers_';

使用通配符的技巧

使用通配符花的时间一般要比其他搜索时间长

• 不要过度使用通配符
• 尽量不要用在 搜索模式(要搜索的模式串) 的开始处, 这样会很慢

使用正则表达式搜索

MySQL 中的正则表达式仅为所有正则表达式的一个子集

语法 where xxx regexp '模式'

基本匹配

# '.' 代表一个字符长度的任意字符, 类似 like 中的 '_'
select *
from user
where username regexp 'Powerstot.';

# '|' 代表或的意思, 类似于 'or'
select *
from user
where username regexp 'Powerstot1|Powerstot2|song'

匹配默认不区分大小写, 如果要区分, 需要在 regexp 后加上 binary 关键字

select *
from user
where username regexp binary 'powerstot';

匹配几个字符之一

使用 [] 来匹配括号里的单个字符

select *
from user
where username regexp 'Powerstot[1234]';
# 也可以写成 'Powerstot[1|2|3|4]'
# 如果写成 'Powerstot1|2|3', 就变成匹配 'Powerstot1','2','3' 了

如果要表示除这些之外的字符 ↓

select *
from user
where username regexp 'Powerstot[^234]';

匹配范围

定义要匹配的范围里的一个或多个字符

# 使用集合
select *
from user
where username regexp 'Powerstot[0-9]';

匹配特殊字符(转义)

例如要匹配 .

select *
from user
where username regexp '\\.';

可以用来转义之前遇到的所有特殊字符, 如|,[]等

匹配范围中的多个实例

*     0或多个
+     1或多个 等于 {1,}
?     0或1个 等于 {0,1}
{n}   n个
{n,}  >=n个
{n,m} n<=数目<=m(m不超过255)

# 匹配 1~9 个数字
select *
from user
where username regexp '[[:digit:]]{1,9}';

# \\( 转义括号, [0-9]的一个数字, 空格, stick, s? 表示匹配 0 或 1 个 s, \\) 转义括号使其作为匹配使用
select *
from user
where username regexp '\\([0-9] sticks?\\)';

常用的匹配字符类

使用时要在外面加一对中括号[]

[:alnum:]    大小写字母 + 数字 (同[a-zA-Z0-9])
[:alpha:]    大小写字母 (同[a-zA-Z])
[:digit:]    数字 (同[0-9])
[:lower:]    小写字母 (同[a-z])
[:upper:]    大写字母 (同[A-Z])
[:space:]    空格以及其他空白字符 (同[\\n\\t\\r\\f\\v])

定位符

用来匹配特定位置的文本, 而不是一个串中任意位置的文本

^    指定的文本开始
$    指定的文本结束

如果想匹配数字开头的, 而不是含有数字的就可以使用^[0-9\\.]

因为^代表了匹配要从串的开头开始

^ 可以代表否定, 如 [^0-9], 也可以代表串的开头

regexp 与 like 的异同

like 用于匹配整个串

regexp 用于匹配子串

利用^开始表达式, $结束表达式, 可以使 regexp 与 like 作用一样

简单测试正则表达式

select 后接要测试的串, regexp 接模式

如果匹配则显示 1

不匹配显示 0

select 'hello' regexp '[0-9]';
# 返回 0 

在 Select 处计算字段

拼接字段

concat(reg1,reg2,...)拼接所有参数

trim(reg) 去除左右两边空格, 类似的还有rtrim() ltrim() 去除右边空格和左边空格

select concat(trim(username), '(', trim(password), ')')
from user
order by username;

mysql 中使用 concat() 函数来链接参数, 而其他 DBMS 可能不一致, 语言转换时要注意

使用别名 alias

as后加上新名字即可

别名 有时也叫 导出列

select concat(trim(username), '(', trim(password), ')') as usernamepassword
from user
order by username;

执行算数计算

select price * 10 as nowPrice
from user
order by username;

使用 select 简单测试计算

select 3*2;
select trim('abc');
select now(); # 返回当前日期时间

参考

• 《MySQL 必知必会》