mysql - 基础语句

增 - 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];