【MySQL】表的增查改删

一、 增：

insert into：

例：

CREATE TABLE students (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INT NOT NULL UNIQUE COMMENT '学号',
name VARCHAR(20) NOT NULL,
qq VARCHAR(20)
);

1. 全字段插入（可一次插入一条，也可以插入多条）：
   insert into students values(1,101,'A',null),(2,102,'B',123789654);
2. 指定字段插入（可一次插入一条，也可以插入多条）：
   insert into students (sn,name) values (103,'C'),(104,'C');
3. 更新数据：
   将所有字段遍历一遍当遇到有冲突的数据时将其更新，若没有任何数据有冲突则插入一组新的数据。

  insert into students values(7,101,'H',null) on dupilcate key update sn = 109;

  4. 替换数据
  将字段遍历一遍，有冲突的将其替换掉，没有就新创建一个
  replace into students (sn,name,qq) values(120,'H',null);

二、查：

测试用例：

-- 创建表结构
CREATE TABLE exam 
(id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
yuwen float DEFAULT 0.0 COMMENT '语文成绩',
shuxue float DEFAULT 0.0 COMMENT '数学成绩',
yingyu float DEFAULT 0.0 COMMENT '英语成绩'
);

-- 插入测试数据
INSERT INTO exam (name, yuwen, shuxue, yingyu) VALUES
('A', 67, 98, 56),
('B', 87, 78, 77),
('C', 88, 98, 90),
('D', 82, 84, 67),
('E', 55, 85, 45),
('F', 70, 73, 78),
('G', 75, 65, 30);
('王二', 75, 65, 30);
('王二狗', 30);
('王老三',65, 30);

select：（挑选）

1. 全字段查询：
   通常情况下不建议使用 * 进行全列查询:
   查询的列越多，意味着需要传输的数据量越大；可能会影响到索引的使用
   select * from exam;
2. 指定子字段查询
   select name,id,shuxue from exam;(指定字段的顺序不需要按照定义的来)
   
3. 可以向里面加一些表达式，不会影响原表：
   select name,id,shuxue 10 from exam;
select name,id,shuxue+10 from exam;

select name,shuxue+yuwen+yingyu total from exam;(as 可以省略)
   
4. 去重
   select distinct shuxue from exam;
   本来有2个98，现在只显示一次
   

where：

where不能用别名（as）

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，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	是 NULL
IS NOT NULL	不是 NULL
LIKE	模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

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

例：

1. 英语小于60：
   
2. 语文在80和90之间：
   
   
3. 语文是是100或60的：

4. 几个姓王的：
先插几个
insert into exam values ('王二',52,35,82),('王二狗',52,35,82);
后查找：

5. 语文好于英语：

6. 总分在200以下
此处不能使用别名

7. 语文成绩大于50且姓王

8. 查找王某同学或者要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80的同学。

select name,yuwen+yingyu+shuxue,yuwen,yingyu,shuxue from exam 
where name like '王%' 
or yuwen+shuxue+yingyu>200 
and yuwen < shuxue 
and yingyu > 80;

9. null的查询：
不能用 = 必须用is
<=> 与 = 在null比较时的区别：

asdasdasdasdas8. asdas

order by （排序）

1.按照成绩排序
升序：

降序：


2. 多个字段排序：
以第一个字段为主，其他字段为次。

limit（分页）

下标从0开始
分页操作：

-- 起始下标为 0-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

三、改：

update（更新，修正）：

1. 将王二数学成绩改为100（改多项字段同理）
   
2. 将所有人成绩改为100：
   
3. 找到倒数前三的同学：
   select name shuxue+yuwen+yingyu total from exam order by total limit 3;
4. 将成绩倒数前三的同学数学成绩加30
   update exam set shuxue = shuxue + 30 oredr by yuwen+shuxue+yingyu limit 3;
5. 给所有同学语文成绩乘2
   update exam set yuwen = yuewen * 2;

四、删

delete（删除表的数据而非结构）

不会重置自增的值。

1. 删除A同学：
   delete from exam where name = 'A';
2. 删除表中全部数据（先删从表，后删主表）：
   delete from exam;

truncate(截断表)

TRUNCATE [TABLE] table_name

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；
2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快
3. 会重置 AUTO_INCREMENT 项

五、表去重

1. 复制表：(创建一个和之前表结构相同的表)
   create table exam2 like exam;
2. 将以前表的数据去重复制到新表
   insert into exam2 select distinct * from exam;
3. 修改表名:
   rename table exam2 to exam3;