文章目录

  • 增 - insert
  • 删 - delete & truncate
  • 改 - update
  • 查询 - select
  • 去重 - distinct
  • 别名的使用 - as
  • 筛选条件 - where
  • 模糊查找 - like & regexp
  • 包含 - in
  • 结果条目数 - limit
  • 排序 - order by


增 - insert

# 用于插入新数据

# 按照位置添加值
insert into TABLENAME values(v1, v2...);

# 按照关键字添加值, 不填字段允许为空或有默认值
insert into TABLENAME(COLUMNNAME1, COLUMNNAME1, ...) values (v1, v2, ...);

删 - delete & truncate

# 删除某条数据
delete from TABLENAME where COLUMNNAME = v;

# 删除表内所有数据
delete from TABLENAME;
truncate table TABLENAME;

改 - update

# 更新单个值
update TABLENAME set COLUMNNAME = newValue where COLUMNNAME = oldValue;

# 更新多个字段
update TABLENAME set C1 = newV1, C2 = newV2 where COLUMNNAME = oldValue;

查询 - select

# 用于从表中选取数据, 并将结果存储在一个结果集中(一个表)
select COLUMNNAME from TABLENAME;

select C1,C2 from TALBENAME;

select * from TABLENAME;

# 限制展示结果的数量
select * from TABLENAME limit NUM;

去重 - distinct

# 如果结果完全重复则去重
select destinct C1, C2, ... from TABLENAME;

别名的使用 - as

# 为 表 指定别名
select stu.name, stu.stunum, s.math, s.english from student as stu, score as s where stu.name = "KAI";

# 为 列 指定别名, 用于展示
select name as n from student;

筛选条件 - where

# 语法
# select C1, C2, ... from TABLENAME where COLUMNNAME 运算符 VALUE

# select 接的是展示列
select C1, C2, ... from TABLENAME where KEY = "VALUE";
select * from TABLENAME where KEY > VALUE;

# 多个条件连接 and 和 or
select C1, C2, ... from TABLENAME where condition1 and condition2;
select * from TABLENAME where condition1 or condition2;

# 用圆括号限制先后顺序
select * from TABLENAME where condition1 or (condition2 and condition3);

运算符

描述

=

等于

<> / !=

不等于

>

大于

<

小于

>=

大于等于

<=

小于等于

between

在某个范围内

like

模糊模式

in

包含

模糊查找 - like & regexp

# like
select * from TABLENAME where COLUMNNAME like VALUE

# % - 代替一个或多个字符
select * from person where name like "y%"; # 查找 name 字段以 y 开头的
select * from person where name like "%e"; # 查找 name 字段以 e 结尾的
select * from person where name like "%ang%"; # 查找 name 字段包含 ang 的
select * from person where name not like "%h%" # 不包含 h 的

# _ - 代替一个字符
select * from company where name like "_oo_le"; 

# regexp
select * from TABLENAME where COLUMNNAME regexp re_condition

# [] - 代替字符组中的任意一个
select * from tb1 where name regexp "[az]";

# [^ ] - 代替不在字符组中的任意一个
select * from tb1 where name regexp "[^az]";

# "^ " - 以...开头
select * from tb1 where name regexp "^y";

# " $" - 以...结尾 
select * from tb1 where name regexp "ue$";

包含 - in

# 列中的值在选项中
select * from TABLENAME where COLUMNNAME in (V1, V2, ...);

结果条目数 - limit

# 配合 select 使用, 限制结果显示的数量
select * from TABLENAME limit NUM;

排序 - order by

# 对指定列进行排序, 默认升序
# 如果希望结果降序, 使用 desc 关键字, 升序关键字为 asc

# 以 cloumn 进行排序, desc 为降序
select * from TABLENAME order by COLUMNNAME [desc];

# 先以 c1 进行排序, 如果有并列, 则以 c2 进行排序, 升降序策略单独指定
select * from TABLENAME order by C1 [desc], C2 [desc];