目录
Create
INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...案例：
mysql> create table t1(
-> id int primary key auto_increment,
-> sn int not null unique comment "学号",
-> name varchar(20) not null,
-> vx varchar(20)
-> );
单行插入数据 + 全列插入
- 插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致
多行数据+指定列插入
- 注意，这里在插入的时候，也可以不用指定id(当然，那时候就需要明确插入数据到那些列了)，那么mysql会使用默认的值进行自增。
- 插入两条记录，value_list 数量必须和指定列数量及顺序一致
使用insert语句也可以一次向表中插入多条记录，插入的多条记录之间使用逗号隔开，并且插入记录时可以只指定某些列进行插入。
说明一下： 在插入记录时，只有允许为空的列或自增长字段可以不指定值插入，不允许为空的列必须指定值插入，否则会报错。
插入否则更新
向表中插入记录时，如果待插入记录中的主键或唯一键已经存在，那么就会因为主键冲突或唯一键冲突导致插入失败。
主键冲突
唯一键冲突 （☆）
可以选择性的进行同步更新操作语法：
INSERT ... ON DUPLICATE KEY UPDATE
column = value [, column = value] ...
-- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等-- 1 row affected: 表中没有冲突数据，数据被插入-- 2 row affected: 表中有冲突数据，并且数据已经被更新
-- 通过 MySQL 函数获取受到影响的数据行数SELECT ROW_COUNT();+-------------+| ROW_COUNT() |+-------------+| 2 |+-------------+
替换数据
- 主键或者唯一键没有冲突，则直接插入
- 主键或者唯一键如果冲突，则删除后插入
1 row affected: 表中没有冲突数据，数据被插入2 row affected: 表中有冲突数据，删除后重新插入
Retrieve
语法：
SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...
为了方便演示我们先创建一个表，包括自增长主键id,姓名以及该同学的语文、数学、英语成绩：
创建完表之后我们可以查看表结构：
查看完表结构之后，我们插入一些数据：
Select列
全列查询
通常情况下不建议使用 * 进行全列查询
- 查询的列越多，意味着需要传输的数据量越大；
- 可能会影响到索引的使用。
指定列查询
指定name
指定id,name
查询字段为表达式
表达式包含一个字段
表达式包含多个字段
为查询结果指定别名
SELECT column [AS] alias_name [...] FROM table_name;
结果去重
发现数学98重复了
where条件
添加where字句的区别：
-
如果在查询数据时没有指定where子句，那么会直接将表中所有的记录作为数据源来依次执行select语句。
-
如果在查询数据时指定了where子句，那么在查询数据时会先根据where子句筛选出符合条件的记录，然后将符合条件的记录作为数据源来依次执行select语句。
where子句中可以指明一个或多个筛选条件，各个筛选条件之间用逻辑运算符AND或OR进行关联，下面给出了where子句中常用的比较运算符和逻辑运算符。
-
比较运算符：
| >,>=,<,<= | 大于，大于等于，小于，小于等于 |
| = | 等于，NULL不安全，例如NULL=NULL的结果是NULL，而不是TRUE(1) |
| <=> | 等于，NULL安全，例如NULL<=>NULL的结果是TURE（1） |
| !=,<> | 不等于 |
| BETWEEN a0 AND a1 | 范围匹配，[a0,a1]，如果a0<=value<=a1,返回TRUE（1） |
| IN(option....) | 如果是option中的任意一个，返回TRUE（1） |
| IS NULL | 是NULL |
| IS NOT NULL | 不是NULL |
| LIKE | 模糊匹配，%表示任意多个（包括0个）任意字符；_表示任意一个字符。 |
逻辑运算符：
| 运算符 | 说明 |
| AND | 多个条件必须都为TRUE（1） |
| OR | 任意一个条件为TRUE（1），结果为TRUE（1） |
| NOT | 条件为TRUE（1），结果为false（0） |
等价于电路中的与或非
英语不及格的同学及英语成绩
( < 60 )
语文成绩在
[80, 90]
分的同学及语文成绩
常规做法
between ..and..
查询数学成绩是58或59或98或99分的同学及其数学成绩
常规做法
in
分别查询姓孙的同学和孙某同学
查询姓孙的
查询孙某同学
查询语文成绩好于英语成绩的同学
总分在
200
分以下的同学
方式一：
方式二：
语文成绩
> 80
并且不姓孙的同学
孙某同学，否则要求总成绩
> 200
并且 语文成绩
<
数学成绩 并且 英语成绩
> 80
NULL 的查询
NULL 和 NULL 的比较，= 和 <=> 的区别
结果排序 结果排序
ASC 为升序（从小到大）
DESC 为降序（从大到小）
默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];
同学及数学成绩，按数学成绩升序显示
id、同学名字、sn及
qq
号，按
qq
号排序显示
语法：Select .......... from table_name [ where .......] order by [asc/desc];
降序排序NULL 视为比任何值都小，降序出现在最下面
查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
查询同学及总分，由高到低
查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示
筛选分页结果
-- 起始下标为 0
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;筛选前三行
筛选 从3开始筛选5行
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
从第0行开始，筛选3行
从第1行开始，筛选3行
小贴士：建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死
按
id
进行分页，每页
3
条记录，分别显示 第
1
、
2
、
3
页
SELECT id, name, math, english, chinese FROM exam_result
ORDER BY id LIMIT 3 OFFSET 0;
--
第
1
页
-- 第 2 页
-- 第 3 页
Update
语法：
UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]
将孙悟空同学的数学成绩变更为
80
分
将曹孟德同学的数学成绩变更为
60
分，语文成绩变更为
70
分
原始数据
跟新后的数据
将总成绩倒数前三的
3
位同学的数学成绩加上
30
分
先找到总成绩倒数前三的人
select name,math,chinese+math+english 总分 from exam_result order by 总分 limit 3;
update exam_result set math=math+30 order by math+chinese+english asc limit 3;
Delete
删除表
语法：
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
删除孙悟空同学的考试成绩
原始数据
删除操作后
删除整张表数据
注意：删除整表操作要慎用！
先准备一张表
插入1点数据
删除整表数据
查看删除结果
再插入一条数据，自增 id 在原值上增长
查看表结构，会有 AUTO_INCREMENT=n 项
截断表
TRUNCATE [TABLE] table_name
注意：这个操作慎用
1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是 TRUNCATE 在删除数据的时候，并不经过真正的事物，所以无法回滚3. 会重置 AUTO_INCREMENT 项
CREATE TABLE for_truncate (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');
再插入一条数据，自增 id 在重新增长
和delete对比一下
总结：truncate截断表会是自增长的重新开始，而delete不会重新开始
插入查询结果
INSERT INTO table_name [(column [, column ...])] SELECT ...
删除表中的的重复复记录，重复的数据只能有一份
创建原数据表
mysql> CREATE TABLE duplicate_table (id int, name varchar(20));
Query OK, 0 rows affected (0.02 sec)
插入测试数据
mysql> INSERT INTO duplicate_table VALUES
-> (100, 'aaa'),
-> (100, 'aaa'),
-> (200, 'bbb'),
-> (200, 'bbb'),
-> (200, 'bbb'),
-> (300, 'ccc');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
创建一张空表 no_duplicate_table，结构和 duplicate_table 一样
mysql> CREATE TABLE no_duplicate_table LIKE duplicate_table;
Query OK, 0 rows affected (0.02 sec)
将 duplicate_table 的去重数据插入到 no_duplicate_table
mysql> INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
通过重命名表，实现原子的去重操作
mysql> RENAME TABLE duplicate_table TO old_duplicate_table,
-> no_duplicate_table TO duplicate_table;
Query OK, 0 rows affected (0.03 sec)
查看最终结果
mysql> SELECT * FROM duplicate_table;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
3 rows in set (0.00 sec)
mysql>
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
