检索数据——select 语句
select [column_name, column_name] from [table_name]; #1
select * from [table_name]; #2
select distinct [column_name] from [table_name]; #3
select [column_name] from [table_name] limits 2,3; #4
#1 从数据表中选择特定列
#2 从数据表中选择所有列
#3 从数据表中选择特定列, 结果不重复。 key word : distinct
#4 从数据表中选择特定列,从第二行开始展示,展示3列
排序检索数据——order by 子句
select [column_name] from [table_name] order by [column_name] (desc|asc); # 1
select [column_name] from [table_name] order by [column_name1] (desc|asc), [column_name2]; # 2
#1 按照某列排序输出, desc降序输出 asc为默认升序输出可省略
#2 首先按照 column_name1 排序,当column_name1相等时按照column_name2 排序
过滤数据——where子句
select [column_name] from [table_name] where [condition]; # 1
select [column_name] from [table_name] where [column_name] between a and b; # 2
select [column_name] from [table_name] where ([condition] or [condition]) and [condition]; # 3
select [column_name] from [table_name] where (not) in ([condition], [condition]); # 4
# 1 2 当输出满足条件的行 当检测NULL时 condition 为 is null
# 3 当 and 与 or 同时出现时, and优先级更高,所以先计算and ,所以条件可能会被错误的组合 因此需要圆括号正确组合
# 4 in 相当与 or 写法更简便, not in 不在条件中
通用符进行过滤——like
select [column_name] from [table_name] where like '%abc'; # 1
select [column_name] from [table_name] where like '_abc'; # 2
# 1 % 表示任意字符0次,1次或任意多次, 不能匹配 null
# 2 _ 表示匹配单个字符
正则表达式
select [column_name] from [table_name] where regexp '.000'; # 1
select [column_name] from [table_name] where regexp '1000|2000'; # 2
select [column_name] from [table_name] where regexp '[123] ton'; # 3
select [column_name] from [table_name] where regexp '[1-5] ton'; # 4
select [column_name] from [table_name] where regexp '\\.'; # 5
# 1 匹配任意字符 + 000
# 2 匹配1000 或 2000
# 3 匹配 1ton .2 ton .3 ton
# 4 匹配 1-5ton
# 5 匹配 . 由于.*?()等元字符被转义 所以匹配他们本身的时候需要加上 \\
元字符 | 说明 |
* | 匹配0个或多个 |
+ | 匹配1个或多个 |
? | 匹配0个或1个 |
{n} | 匹配n个 |
{n,} | 匹配n个以上 |
{n,m} | 匹配n至m个 |
例如:
select [column_name] from [table_name] where [column_name] regexp '\\([0-9] sticks?\\)' ;
匹配结果可以为: (1 stick) 或 (3 sticks)
元字符 | 说明 |
^ | 文本的开始 |
$ | 文本的结束 |
例如:
select [column_name] from [table_name] where [column_name] regexp '^[0-9//.]' order by [column_name];
匹配的是 以数字或”.“ 开头的字符串