MySQL语句必知必会（一）

选择数据库

use [database];

返回所有数据库列表

show databases;

返回数据库中所有表的列表

show tables;

显示表列

show columns from [table];
describe [table];

显示创建特定数据库或者表的语句

show create database [database];
show create table [table];

检索数据

select [column_name1], [column_name2], [column_name3] from [table];
select * from [table];

检索不同的行

select distinct [column_name] form [table];

限制检索结果

select [column_name] from [table] limit [row_num];
select [column_name] from [table] limit [start_row_num] [row_num];
select [column_name] from [table] limit [row_num] offset [start_row_num];

排序检索数据

select * from [table] order by [column_name1], [column_name2];
select * from [table] order by [column_name1] desc, [column_name2] desc;

过滤数据

select [column_name] from [table] where [column_name] = [value];

支持的条件操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between	在指定的两个值之间

select [column_name] from [table] where [column_name] between [value1] and [value2];

检查空值

select [column_name] form [table] where [column_name] is null;

组合条件过滤数据

select [column_name] from [table] where [column_name1] = [value1] and [column_name2] >= [value2];
select [column_name] from [table] where [column_name] = [value1] or [column_name] >= [value2];
select [column_name] from [table] where [column_name] = [value1] or ([column_name] >= [value] and [column_name] <= [value]);

in 操作符

select [column_name] from [table] where [column_name] in ([value1], [value2]);

not 操作符

select [column_name] from [table] where [column_name] not in ([value1], [value2]);

用通配符过滤数据

// %(百分号)表示任意字符出现任意次数
select [column_name] from [table] where [column_name] like '<value>%';
// _(下划线)表示匹配单个字符
select [column_name] from [table] where [column_name] like '_[value]';
// 通配符搜索处理时间较长因此不应被过度使用

使用正则表达式过滤数据

select [column_name] form [table] where [column_name] regexp [regexp];
// 测试语句：select 'hello' regexp '[0-9]';

计算字段

// 拼接字段将值联结到一起构成单个值
select concat([column_name1], '(', [column_name2], ')') form [table];
// 使用别名
select concat([column_name], '(', [column_name], ')') as [alias] form [table];
// 执行算术计算
select [column_name1]*[column_name2] as [alias] from [table];

---

参考书籍或文章：
1.《MySQL必知必会》