INSERT [INTO] table_name
	[(column [, column] ...)]
	VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...

create table students (
  id int unsigned primary key auto_increment,
  sn int not null unique comment '學號',
  name varchar(20) not null,
  qq varchar(20)
);
-- 插入測試數據
insert into students values 
(100, 10000, '唐三藏', null),
(101, 10001, '孫悟空', '11111'),
(102, 20001, '曹孟德', null),
(103, 20002, '孫仲謀', null);

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);

-- 全列插入（指定id）
insert into students values (104, 20003, '魯智深', '22222');
-- 省略自增主鍵（自動生成id）
insert into students (sn, name, qq) values (20004, '林沖', '33333');

insert into students (sn, name) values 
(20005, '武松'),
(20006, '楊志');

-- 主鍵沖突（id=100已存在），執(zhí)行更新
insert into students (id, sn, name) values (100, 10010, '唐大師')
on duplicate key update sn = 10010, name = '唐大師'; // 同步更新語法
-- 唯一鍵沖突（sn=20001已存在），執(zhí)行更新
insert into students (sn, name) values (20001, '曹阿瞞')
on duplicate key update name = '曹阿瞞';

-- sn=20002已存在，刪除原記錄后插入新數據
replace into students (sn, name) values (20002, '孫伯符');

-- 創(chuàng)建空表（結構與students一致）
create table students_copy like students;
-- 將students的去重數據插入新表
insert into students_copy select distinct * from students;

select 
  [distinct] {* | column [, column] ...}
from table_name
[where ...]
[order by column [asc | desc], ...]
limit ...

-- 全列查詢（數據量大時性能差，不建議在生產環(huán)境使用）
-- 1. 查詢的列越多，意味著需要傳輸的數據量越大
-- 2. 可能會影響到索引的使用。(索引待后面我們再進行理解)
select * from exam_result;

-- 查詢姓名、語文、數學成績
select name, chinese, math from exam_result;

-- 常量表達式
select id, name, 10 from exam_result;
-- 單字段運算（英語成績+10）
select id, name, english + 10 from exam_result;
-- 多字段運算（總分）
select id, name, chinese + math + english from exam_result;

select 
  id, 
  name, 
  chinese + math + english as 總分 
from exam_result;

-- 去重查詢數學成績 distinct
select distinct math from exam_result;

-- 1. 英語不及格（<60）
select name, english from exam_result where english < 60;
-- 2. 語文成績在[80, 90]之間（between...and）
select name, chinese from exam_result where chinese between 80 and 90;
-- 3. 數學成績是58、59、98、99中的一個（in）
select name, math from exam_result where math in (58, 59, 98, 99);
-- 4. 姓孫的同學（like %）
select name from exam_result where name like '孫%';
-- 5. 姓名是兩個字且姓孫（like _）
select name from exam_result where name like '孫_';
-- 6. 語文成績好于英語成績
select name, chinese, english from exam_result where chinese > english;
-- 7. 總分低于200分（表達式作為條件）
select name, chinese + math + english as 總分 
from exam_result 
where chinese + math + english < 200; // 不能直接用總分 < 20,因為執(zhí)行這里的時候還沒執(zhí)行前面的部分
-- 8. 語文>80且不姓孫（and + not）
select name, chinese from exam_result where chinese > 80 and name not like '孫%';
-- 9. qq號不為空（is not null）
select name, qq from students where qq is not null;
-- 10. null安全比較（<=>）
select name, qq from students where qq <=> null;

-- 1. 按數學成績升序
select name, math from exam_result order by math;
-- 2. 按數學降序，英語升序，語文升序
select name, math, english, chinese 
from exam_result 
order by math desc, english, chinese;
-- 3. 按總分降序（表達式排序）
select name, chinese + math + english as 總分 
from exam_result 
order by 總分 desc;
-- 4. null排序（null視為最小值，升序在最前）
select name, qq from students order by qq;
select name, qq from students order by qq desc;

-- 語法1：limit 條數（取前n條）
select * from exam_result order by id limit 3;
-- 語法2：limit 起始下標, 條數（從s(下標從0開始)開始取n條）
select * from exam_result order by id limit 3, 3;
-- 語法3：limit 條數 offset 起始下標（推薦，更清晰）
select * from exam_result order by id limit 3 offset 6;
-- 分頁示例：每頁3條，第1-3頁
select * from exam_result order by id limit 3 offset 0; -- 第1頁
select * from exam_result order by id limit 3 offset 3; -- 第2頁
select * from exam_result order by id limit 3 offset 6; -- 第3頁

-- 1. 統(tǒng)計學生總數（count(*)不受null影響）
select count(*) as 學生總數 from students;
-- 2. 統(tǒng)計qq號非空的學生數（null不計入）
select count(qq) as qq已收集人數 from students;
-- 3. 統(tǒng)計數學成績總分和去重后總分
select sum(math) as 數學總分, sum(distinct math) as 去重數學總分 from exam_result;
-- 4. 統(tǒng)計語文成績平均分
select avg(chinese) as 語文平均分 from exam_result;
-- 5. 英語最高分和最低分
select max(english) as 英語最高分, min(english) as 英語最低分 from exam_result;
-- 6. 統(tǒng)計70分以上的數學最低分
select min(math) as 70+數學最低分 from exam_result where math > 70;

-- 準備雇員表（經典測試表）
create table emp (
  empno int primary key,
  ename varchar(20),
  job varchar(20),
  deptno int,
  sal float
);
insert into emp values
(7369, 'smith', 'clerk', 20, 800),
(7499, 'allen', 'salesman', 30, 1600),
(7521, 'ward', 'salesman', 30, 1250),
(7566, 'jones', 'manager', 20, 2975),
(7654, 'martin', 'salesman', 30, 1250),
(7698, 'blake', 'manager', 30, 2850),
(7782, 'clark', 'manager', 10, 2450),
(7788, 'scott', 'analyst', 20, 3000);
-- 1. 按部門分組，統(tǒng)計每個部門的平均工資和最高工資
select deptno, avg(sal) as 平均工資, max(sal) as 最高工資 from emp group by deptno;
-- 2. 按部門和崗位分組，統(tǒng)計平均工資和最低工資
select deptno, job, avg(sal) as 平均工資, min(sal) as 最低工資 from emp group by deptno, job;
-- 3. 篩選平均工資低于2000的部門（having篩選分組結果）
select deptno, avg(sal) as 平均工資 from emp group by deptno having avg(sal) < 2000;

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

-- 1. 更新單字段（孫悟空數學成績改為80）
update exam_result set math = 80 where name = '孫悟空';
-- 2. 更新多字段（曹孟德數學60、語文70）
update exam_result set math = 60, chinese = 70 where name = '曹孟德';
-- 3. 按表達式更新（所有同學語文成績翻倍）
update exam_result set chinese = chinese * 2;
-- 4. 結合排序和limit（總分倒數前三的同學數學+30）
update exam_result 
set math = math + 30 
order by chinese + math + english 
limit 3;
-- 5. 條件更新（英語<60的同學英語+10）
update exam_result set english = english + 10 where english < 60;

-- 1. 刪除孫悟空的考試成績
delete from exam_result where name = '孫悟空';
-- 2. 刪除英語<60的同學成績
delete from exam_result where english < 60;

-- 刪除for_delete表所有數據（自增id不重置）
create table for_delete (
  id int primary key auto_increment,
  name varchar(20)
);
insert into for_delete (name) values ('a'), ('b'), ('c');
delete from for_delete;
-- 插入新數據，自增id從4開始
insert into for_delete (name) values ('d');
select * from for_delete; -- id=4

-- 截斷表（自增id重置為1）
create table for_truncate (
  id int primary key auto_increment,
  name varchar(20)
);
insert into for_truncate (name) values ('a'), ('b'), ('c');
truncate for_truncate;
-- 插入新數據，自增id從1開始
insert into for_truncate (name) values ('d');
select * from for_truncate; -- id=1

from → on → join → where → group by → with → having → select → distinct → order by → limit