create语句,创建库、表
create database [数据库名]; 创建数据库
create table [表名](
列名1 数据类型 约束,
列名2 数据类型 约束,
列名3 数据类型 约束,
。
。
。
列名n 数据类型 约束
); 创建表
+---------------------------------------------------------------------------------------------------+
drop语句,删除库、表
drop database [数据库名]; 删除数据库
drop table [表名]; 删除表
+---------------------------------------------------------------------------------------------------+
show语句,查看数据库与表信息
show databases; 查看当前主机中所有的数据库
show tables; 查看当前库中所有的表
show create table [表名]; 显示创建数据表的语句
show create database [数据库名]; 显示创建数据库的语句
showcolumns from [表名]; 显示表中所有的列
+---------------------------------------------------------------------------------------------------+
alter语句,修改表结构
alter table [表名] add [列名] [数据类型]; 添加列
alter table [表名] change [旧列名] [新列名] [数据类型]; 修改列
alter table [表名] drop column [列名]; 删除列
+---------------------------------------------------------------------------------------------------+
delete语句,删除表数据
delete from [表名]; 删除表中所有数据
delete from [表名] where [列名] = [值]; 删除指定行
+---------------------------------------------------------------------------------------------------+
update语句,修改表数据
update [表名] set [列名] = [值]; 将表中某列所有修改为指定值
update [表名] set [列名] = [值] where [列名] = [值]; 指定某行某列修改为指定值
+---------------------------------------------------------------------------------------------------+
检索数据
select查询主句
select [列名] from [表名]; 检索单列
select [列名],[列名],[列名]... from [表名]; 检索多列
select * from [表名]; 检索所有列
select distinct [列名] from [表名]; 去除重复值
+---------------------------------------------------------------------------------------------------+
限定查询结果
limit子句
select [列名] from [表名]
limit 5; 只返回小于等于五条数据
select [列名] from [表名]
limit 5,5; 返回从第五行开始的第五条数据
+---------------------------------------------------------------------------------------------------+
结果排序
order by子句
select [列名] from [表名]
order by [列名]; 指定以某列进行排序,默认升序[a-z][A-Z][0-9]
select [列名] from [表名]
order by [列名1],[列名2],[列名3]..; 多列排序,先按列1排,再按列2排,后面以此类推
select [列名] from [表名]
order by [列名] desc; 指定降序排列 关键字desc
select [列名] from [表名]
order by [列名1] desc [列名2]; 混合使用升降序,先按列1降序排,再按列2降序排
+---------------------------------------------------------------------------------------------------+
过滤数据
where子句
select [列名] from [表名]
where 列名 = [值]; 只返回符合条件【列名 = [值]】的值
where字句操作符
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
BETWEEN 在指定的两个值之间
select [列名] from [表名]
where 列名 between 5 and 10; 限定结果在5到10之间
is null空值检查
select [列名] from [表名]
where [列名] is null; 检索出某列为null的所有行
组合where子句:or 两个条件任意满足一个
select [列名] from [表名]
where [列名] = [值] or [列名] = [值]; 检索满足任意一个条件的行
组合where子句:and 两个条件必须同时满足
select [列名] from [表名]
where [列名] = [值] and [列名] = [值]; 检索满足所有条件的行
混合使用and和or
select [列名] from [表名]
where ([列名] = [值] or [列名] = [值]) and [列名] = [值]; 使用括号定义条件优先级,先判断or,再判断and
in操作符:指定取值范围
select [列名] from [表名]
where [列名] in (值1,值2,值3...); 检索列的值为括号中的行(in功能和or类似,但是in执行更快)
not操作符
select [列名] from [表名]
where [列名] not in (值1,值2,值3...); 检索所有列中值不为括号中的值的行
+---------------------------------------------------------------------------------------------------+
数据检索
like操作符,通配符过滤
% 表示任意字符,出现0次或任意次数
select [列名] from [表名]
where [列名] like “a%”; 检索以a开头的结果
select [列名] from [表名]
where [列名] like “%a%”; 检索中间包含a的结果
select [列名] from [表名]
where [列名] like “%a”; 检索末尾为a的结果
_ 表示任意字符出现一次
select [列名] from [表名]
where [列名] like “a_”; 检索以a开头且后面只有一个字符结果
select [列名] from [表名]
where [列名] like “_a_”; 检索a前后为一个字符的结果
select [列名] from [表名]
where [列名] like “_a”; 检索a前为一个字符的结果
正则表达式搜索结果
基本字符匹配 regexp
select [列名] from [表名]
where [列名] regexp “abc”; 检索包含abc文本的结果(不区分大小写)
select [列名] from [表名]
where [列名] regexp binary “abc”; binary 关键字区分大小写
select [列名] from [表名]
where [列名] regexp “a|b”; 匹配结果中含a和b的结果
select [列名] from [表名]
where [列名] regexp “[abc] df”; 匹配多个值,结果中包含a、b、c三个值的结果
select [列名] from [表名]
where [列名] regexp “[0-9]”; 匹配范围0到9,若匹配字母,可写为[a-z]或[A-Z]
select [列名] from [表名]
where [列名] regexp “\\|”; 匹配包含 | 的结果,\\表示转义
匹配字符类
预定义的字符集
元字符 说明
[:alnum:] 任意字母和数字(同[a-zA-Z0-9])
[:alpha:] 任意字符(同[a-zA-Z])
[:blank:] 空格和制表(同[\\t])
[:cntrl:] ASCII控制字符(ASCII 0到31和127)
[:digit:] 任意数字(同[0-9])
[:graph:] 与[:print:]相同,但不包括空格
[:lower:] 任意小写字母(同[a-z])
[:print:] 任意可打印字符
[:punct:] 既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:] 包括空格在内的任意空白字符(同[\\f\\n\\r\\t\\v])
[:upper:] 任意大写字母(同[A-Z])
[:xdigit:] 任意十六进制数字(同[a-fA-F0-9])
重复元字符
元 字 符 说 明
* 0个或多个匹配
+ 1个或多个匹配(等于{1,})
? 0个或1个匹配(等于{0,1})
{n} 指定数目的匹配
{n,} 不少于指定数目的匹配
{n,m} 匹配数目的范围(m不超过255)
定位符
元 字 符 说 明
^ 文本的开始
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾