MySQL——基本查询：增删改查CURD

1 MySQL的四大基本操作：

CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）

2 创建Create

2.1 普通插入

// valuelist的顺序要和前面的字段名顺序相同
// 字段名可整体省略，表示全列插入
insert [into] tablename [(columnname1,columnname2..)] values (valuelist),[(valuelist2)...]

2.2 插入否则更新

针对主键或者唯一键冲突
on duplicate key：当发生重复key的时候

// column1是主键，如果已经存在主键=value1的记录，那么就更新该行记录的值
insert into tablename (column1,column2,column3) values (value1,value2,value3) on duplicate key update column2=value2,column3=value3;

mysql> select * from students;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏    | NULL  |
| 101 | 10001 | 孙悟空    | 11111 |
| 102 | 20001 | 曹孟德    | NULL  |
| 103 | 20002 | 孙仲谋    | NULL  |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)
// 要插入的和更新的两个值还是会造成冲突，则插入失败
mysql> insert into students(id,sn,name) values (100,10010,'唐大师') on duplicate key update sn=10001,name='唐大师';
ERROR 1062 (23000): Duplicate entry '10001' for key 'sn'
// 冲突并更新，影响2行
mysql> insert into students(id,sn,name) values (100,10010,'唐大师') on duplicate key update sn=1000,name='唐大师';
Query OK, 2 rows affected (0.00 sec)
// 冲突但是要更新的值和表中的一致，影响0行
mysql> insert into students(id,sn,name) values (100,10010,'唐大师') on duplicate key update sn=1000,name='唐大师';
Query OK, 0 rows affected (0.00 sec)
// 没有冲突，正常插入，影响1行
mysql> insert into students(id,sn,name) values (105,10010,'唐大师') on duplicate key update sn=1000,name='唐大师';
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 |  1000 | 唐大师    | NULL  |
| 101 | 10001 | 孙悟空    | 11111 |
| 102 | 20001 | 曹孟德    | NULL  |
| 103 | 20002 | 孙仲谋    | NULL  |
| 105 | 10010 | 唐大师    | NULL  |
+-----+-------+-----------+-------+
5 rows in set (0.00 sec)

2.3 替换

replace，主键或唯一键不冲突时直接插入，冲突时删除后再插入

replace into tablename (...) values (...);

3 读取retrieve

执行顺序：
（1）from （2）where （3）select （4）order by （5）limit

3.1 select列查询

3.1.1 全列查询

*表示全列
通常情况下不建议使用 * 进行全列查询
– 1. 查询的列越多，意味着需要传输的数据量越大；
– 2. 可能会影响到索引的使用。

select * from tablename;

3.1.2指定列查询

select columnname1,columnname3,columnname2... from tablename;

3.1.3查询字段为表达式

只能设置可以被计算的值，比如10，10+10，或者已存在的字段名相加…；该操作不影响表中存的数据

select columnname1,columnname2,10 from tablename;

3.1.4指定别名

select columnname1,columnname2 总和 from tablename;

3.1.5结果去重

select distinct columnname from tablename;

3.2 where条件筛选:比较运算符和逻辑运算符

比较运算符

比较运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于，可以比较数字、字符串
=	等于，可以比较数字、字符串，NULL 不安全，例如 NULL = NULL 的结果是 NULL， columnname=null查不到记录
<=>	等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!=, <>	不等于，都不能和null进行比较
IS NULL	是 NULL，推荐使用
IS NOT NULL	不是 NULL，推荐使用
BETWEEN a0 AND a1	范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, …)	如果是 option 中的任意一个，返回 TRUE(1)
LIKE	模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

逻辑运算符：
AND，OR，NOT

3.2.1 between a0 and a1

在[a0,a1]之间的值（闭区间）

select * from tablename where columnname between a0 and a1;
// 等价
select * from tablename where columnname >=a0 and columnname <= a1;

3.2.2 or

筛选多个具体值

select * from tablename where columnname = a0 or columnname = a1 or columnname = a3;

3.2.3 in

等价or

select * from tablename where columnname in (a0,a1,a2);

3.2.4 like

%：可以匹配0~n任意长度字符串
_：只能匹配一个字符
not like：相反

select * from tablename where columnname like a%;// 包括a,a0,a12,a345...
select * from tablename where columnname like a_; //只包括a0

3.2.5 多列比较

select * from tablename where columnname1 >(</=) columnname2

3.2.6 总和比较

where后不能用列别名

select columnname1+columnname2 总和 from tablename where columnname1+columnname2 < n;

3.3 order by排序

多个条件排序时从前到后执行

3.3.1 升序

默认升序排列

select * from tablename order by columnname asc;

3.3.2 降序

select * from tablename order by columnname desc;

3.3.3 总和排序

order by可以使用列别名

select columnname1+columnname2 from tablename order by columnname1+columnname2 desc;
select columnname1+columnname2 总和 from tablename order by 总和 desc;

3.4 limit筛选分页结果

// 起始下标为0
select columnname1+columnname2 from tablename order by columnname1+columnname2 desc limit n;// 前n个
select * from tablename limit n,m; // 从n开始截取m个
select * from tablename limit m offset n; // 从n开始截取m个

3.5 group by分组查询

对指定列进行分组之后再查询，多个条件分组可以直接用,分隔写

3.5.1having

和group by搭配使用，用来对分组的数据进行筛选

3.5.1.1 having VS where

（1）where过滤表数据，having过滤分组数据
（2）执行顺序不同，where几乎是查询语句最早执行的部分，having在执行完group by或聚合函数之后才执行

4 更新update

// 不加限制条件就会更新整列 where 1=1
update tablename set columnname1=expr,columnname2=expr where ... order by ... limit ...;

5 删除delete

delete from tablename where... order by... limit...;

5.1 清空表数据

delete from tablename;
trancate [table] tablename;//截断表

区别：
（1）②会将AUTO_INCREMENT置0；
（2）①更新日志，②不更新日志，无法进行回滚
（3）②只能对整表操作

MySQL日志：
承担很大的功能要求
bin log：（由mysqld服务器）记录历史sql操作，该log用来进行多主机同步，增量备份
redo log：mysql数据持久化和crash-safe功能（崩溃时安全）；用户的增删改查操作都是在内存中完成的，通过MySQL的刷新策略再将数据刷新到磁盘中，因此若发生MySQL还未来得及刷新就退出的情况，就需要redo log起作用保证安全
undo log：在事务中承担回滚的日志，数据操作恢复功能