mysql基本操作

增 insert into

// 插入所有列（value的个数要与列的个数对应）
insert into table_name values (value1, value2, value3);

// 指定列名插入
insert into table_name (column_name1, column_name2, column_name3) values (value1, value2, value3);

删 delete

delete from user where some_column=some_value;

为了避免忘记 where 字句，设置安全模式
set sql_safe_updates = 1
1 为开启， 0 是关闭
执行时如果没有指定 where 字句会报错：
[Err] 1175 - You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

改 update

// 必须指定 where 字句，否则会修改所有
update table_name set column_name1=value1, column_name2=value2 where some_column=some_value;

为了避免忘记 where 字句，设置安全模式
set sql_safe_updates = 1
1 为开启， 0 是关闭
执行时如果没有指定 where 字句会报错：
[Err] 1175 - You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

查 select

// 查询列名
select column_name, column from table_name;

// 查询所有列
select * from table_name;

// 查询返回不重复的
select distinct column_name, column_name from table_name;

// 子句 where
select * from table_name where column_name='value'

// 字句运算符
= 等于
<> | != 不等于
> 大于
>= 大于等于
< 小于
<= 小于等于
between 查询范围 eg: select * from user where id between 1 and 10;
like 模糊查询
in 查询多个值 eg: select * from user where id in (1, 2);

// 逻辑运算符
and 并
or 或

// order by 排序
select column_name, column_name from table_name order by column_name, column_name asc | desc
正序 asc eg: select * from user order by id asc;
倒叙desc eg: select * from user order by id desc;

// limit N 查询数量
select * from table_name limit N;

// offset M 偏移量
select * from table_name limit N offset M;

// limit N offset M 简写 limit N, M
select * from table_name limit N, M;