文章目录

• 插入操作insert
• 查询操作select
• • select查询
  • where条件判断
  • order by排序
  • limit筛选分页结果
• 更新操作update
• 删除操作delete
• 插入查询结果

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

先创建提供一张表：

mysql> create table students (-> id int unsigned primary key auto_increment,-> sn int unsigned unique key,-> name varchar(20) not null,-> qq varchar(32) unique key-> );Query OK, 0 rows affected (0.26 sec)

插入操作insert

-- 指定列插入mysql> insert into students (sn,name,qq) values (123,'张三','456789');Query OK, 1 row affected (0.03 sec)-- 全列插入mysql> insert into students values (10,124,'关羽','123456');Query OK, 1 row affected (0.04 sec)-- 多行插入mysql> insert into students values (13,127,'王五','14678909'),(14,128,'赵七','567890'),(15,129,'陈九','7890123');Query OK, 3 rows affected (0.01 sec)Records: 3Duplicates: 0Warnings: 0

当然，由于主键 或者 唯一键 对应的值已经存在而导致插入失败

可以选择性的进行同步更新操作语法：

INSERT ... ON DUPLICATE KEY UPDATEcolumn = value [, column = value] ...

mysql> insert into students values (13,132,'许攸','111111') on duplicate key update sn=132,name='xuyyou',qq='1111';Query OK, 2 rows affected (0.04 sec)-- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等-- 1 row affected: 表中没有冲突数据，数据被插入-- 2 row affected: 表中有冲突数据，并且数据已经被更新

发生主键冲突把插入操作改为更新操作

替换

-- 主键 或者 唯一键 没有冲突，则直接插入；-- 主键 或者 唯一键 如果冲突，则删除后再插入mysql> replace into students (sn,name,qq) values (140,'许攸','34567812');Query OK, 1 row affected (0.03 sec)-- 1 row affected: 表中没有冲突数据，数据被插入-- 2 row affected: 表中有冲突数据，删除后重新插入

查询操作select

SELECT[DISTINCT] {* | {column [, column] ...}[FROM table_name][WHERE ...][ORDER BY column [ASC | DESC], ...]LIMIT ...

创建一张表：

-- 创建表结构CREATE TABLE exam_result (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20) NOT NULL COMMENT '同学姓名',chinese float DEFAULT 0.0 COMMENT '语文成绩',math float DEFAULT 0.0 COMMENT '数学成绩',english float DEFAULT 0.0 COMMENT '英语成绩');-- 插入测试数据INSERT INTO exam_result (name, chinese, math, english) VALUES('唐三藏', 67, 98, 56),('孙悟空', 87, 78, 77),('猪悟能', 88, 98, 90),('曹孟德', 82, 84, 67),('刘玄德', 55, 85, 45),('孙权', 70, 73, 78),('宋公明', 75, 65, 30);

select查询

全列查询

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

指定列查询

-- 指定列的顺序不需要按定义表的顺序来SELECT id, name, english FROM exam_result;

查询字段为表达式

-- 表达式不包含字段SELECT id, name, 10 FROM exam_result;

-- 表达式包含多个字段SELECT name,math, chinese + math + english FROM exam_result;

为查询结果指定别名

SELECT column [AS] alias_name [...] FROM table_name;

比如上面我们的查询名称太长，加上as即可：

当然，下面这样也是可以的：

结果去重distinct

select distinct math from exam_result;

where条件判断

比较运算符：

运算符 说明>, >=, <, <= 大于，大于等于，小于，小于等于= 等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL等于，NULL 安全，例如 NULL  NULL 的结果是 TRUE(1)!=,  不等于BETWEEN a0 AND a1 范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)IN (option, ...) 如果是 option 中的任意一个，返回 TRUE(1)IS NULL 是 NULLIS NOT NULL 不是 NULLLIKE 模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

逻辑运算符：

运算符 说明AND 多个条件必须都为 TRUE(1)，结果才是 TRUE(1)OR 任意一个条件为 TRUE(1), 结果为 TRUE(1)NOT条件为 TRUE(1)，结果为 FALSE(0)

下面，举一些案列来进行练习一下把：

• 英语不及格的同学及英语成绩 ( < 60 )

select name,english from exam_result where english<60;

• 语文成绩在 [80, 90] 分的同学及语文成绩

select name,chinese from exam_result where chinese>=80 and chinese<=90;

 select name,chinese from exam_result where chinese between 80 and 90;

• 数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

select name,math from exam_result where math=58 or math=59 or math=98 or math=99;

select name,math from exam_result where math in (58,59,98,99);

• 姓孙的同学 及 孙某同学

select name from exam_result where name like '孙%';select name from exam_result where name like '孙_';

• 语文成绩好于英语成绩的同学

select name,chinese,english from exam_result where chinese > english;

• 总分在 200 分以下的同学

select name,chinese+english+math from exam_result where chinese+english+math<200;

注意：下面这样写是错误的：

mysql> select name,chinese+english+math total from exam_result where total 

这是由于sql的执行顺序影响：这里的执行顺序非常重要，这里无法使用别名，很简单：这是因为先执行from,先从哪个表里筛选数据，筛选的时候，得先设定筛选条件

当然，下面这样也是错误的：不能在筛选条件做重命名

• 语文成绩 > 80 并且不姓孙的同学

select name,chinese from exam_result where chinese >80 and name not like '孙%';

• 孙某同学，否则要求总成绩 > 200 并且 语文成绩 80

select name,chinese,math,english,chinese+math+english 总分 from exam_result where name like'孙_' or(chinese+math+english>200 and chinese 80);

• NULL 的查询

为了演示，先创建一张表test：

select * from test where name is null;

order by排序

-- ASC 为升序（从小到大）-- DESC 为降序（从大到小）-- 默认为 ASCSELECT ... FROM table_name [WHERE ...]ORDER BY column [ASC|DESC], [...];

对于没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序

下面，通过一些案例来理解order by排序：

• 同学及数学成绩，按数学成绩升序显示

 select name,math from exam_result order by math asc;

• 同学排序显示

-- NULL 视为比任何值都小，升序出现在最上面

• 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示

select name,math,english,chinese from exam_result order by math desc,english asc,chinese asc

注意：order by默认是升序asc 排序

• 查询同学及总分，由低到高

select name,math+chinese+english as total from exam_result order by total;

为什么里又能使用别名了呢？where后面不能使用别名，为什么order by排序这里可以了呢？

要对表结构的数据做排序，一定是得先有数据的，然后再进行排序。

• 查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

select name,math from exam_result where name like '孙%' or name like '曹%' order by math desc;

limit筛选分页结果

-- 起始下标为 0-- 从 s 开始(下标从0开始)，筛选 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;

需要由数据才能排序，只有数据准备好了，你才要显示，limit的本质功能是“显示”

更新操作update

UPDATE table_name SET column = expr [, column = expr ...][WHERE ...] [ORDER BY ...] [LIMIT ...]

对查询到的结果进行列值更新

• 将孙悟空同学的数学成绩变更为 80 分

update exam_result set math=80 where name='孙悟空';

• 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

update exam_result set math=60,chinese=70 where name='曹孟德';

• 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

 select name,math+chinese+english total from exam_result order by total asc limit 3;

• 将所有同学的语文成绩更新为原来的 2 倍

update exam_result set chinese=chinese*2;

删除操作delete

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

• 删除孙悟空同学的考试成绩

delete from exam_result wherename='孙悟空';

• 删除整张表数据

-- 准备测试表CREATE TABLE for_delete (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20));Query OK, 0 rows affected (0.16 sec)-- 插入测试数据INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');-- 删除整表数据DELETE FROM for_delete;-- 查看删除结果SELECT * FROM for_delete;Empty set (0.00 sec)-- 再插入一条数据，自增 id 在原值上增长INSERT INTO for_delete (name) VALUES ('D');

• 截断表

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；
2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚
3. 会重置 AUTO_INCREMENT 项

TRUNCATE [TABLE] table_name

举例子：

-- 准备测试表CREATE TABLE for_truncate (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20));-- 插入测试数据INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');-- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作TRUNCATE for_truncate;

-- 再插入一条数据，自增 id 在重新增长INSERT INTO for_truncate (name) VALUES ('E');

-- 查看表结构，会有 AUTO_INCREMENT=2 项SHOW CREATE TABLE for_truncate\G

插入查询结果

INSERT INTO table_name [(column [, column ...])] SELECT ...

• 删除表中的的重复记录，重复的数据只能有一份

-- 创建原数据表CREATE TABLE duplicate_table (id int, name varchar(20));-- 插入测试数据INSERT INTO duplicate_table VALUES(100, 'aaa'),(100, 'aaa'),(200, 'bbb'),(200, 'bbb'),(200, 'bbb'),(300, 'ccc');

-- 创建一张空表 no_duplicate_table，结构和 duplicate_table 一样CREATE TABLE no_duplicate_table LIKE duplicate_table;-- 将 duplicate_table 的去重数据插入到 no_duplicate_tableINSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;-- 通过重命名表，实现原子的去重操作RENAME TABLE duplicate_table TO old_duplicate_table,no_duplicate_table TO duplicate_table;-- 查看最终结果SELECT * FROM duplicate_table;

为什么以后是通过rename方式机型的：就是单纯地想等一切都就绪了，然后统一放入、更新、生效等