SQL的增删查改

CRUD  Create（增加） Retrieve（查询） Update（更新） Delete（删除）

1、增

-- 创建一张学生表
DROP TABLE IF EXISTS student;
CREATE TABLE student (
    id INT,
    sn INT comment '学号',
    name VARCHAR(20) comment '姓名',
    qq_mail VARCHAR(20) comment 'QQ邮箱'
);

-- 全字段插入
INSERT INTO student VALUES (111,11111,'唐僧','123456');

-- 批量插入(缺少的字段会填入默认值，比如下面猪八戒的qq_mail就是Null)
-- 注：字段和值的匹配顺序和匹配数量应一致
INSERT INTO student VALUES
    (222,22222,'孙悟空','654321');
    
INSERT INTO student (id,sn,name) VALUES
    (333,33333,'猪八戒');
-- 如果缺少的字段没有默认值，将会失败
-- 如果值的类型配不上也会失败
    

2、查

-- 创建考试成绩表
DROP TABLE IF EXISTS exam_result;
CREATE TABLE exam_result (
	id INT,
	name VARCHAR(20),
	chinese DECIMAL(3,1),
	math DECIMAL(3,1),
	english DECIMAL(3,1)
);

-- 插入测试数据
INSERT INTO exam_result (id,name, chinese, math, english) VALUES
	(1,'唐三藏', 67, 98, 56),
	(2,'孙悟空', 87.5, 78, 77),
	(3,'猪悟能', 88, 98, 90),
	(4,'曹孟德', 82, 84, 67),
	(5,'刘玄德', 55.5, 85, 45),
	(6,'孙权', 70, 73, 78.5),
	(7,'宋公明', 75, 65, 30);
    
-- 选择
SELECT * FROM exam_result;
SELECT name,id FROM exam_result;

-- 后面跟常数
SELECT 1 FROM exam_result;
SELECT id,chinese,2 FROM exam_result;

-- 后面跟带运算的表达式
SELECT 1 + 1 FROM exam_result;

-- 后面跟有字段参与的表达式
SELECT id,name,chinese + math + english From exam_result;
-- 别名（alias）（第二句话少了AS,但是结果一样）
SELECT id,name,chinese + math + english AS total from exam_result;
SELECT id,name,chinese + math + english total from exam_result;

-- 后面跟bool表达式（true就是1，false就是0）
SELECT 1 = 1 FROM exam_result;
SELECT id,id > 3 FROM exam_result;

-- 与或非 and or not 
SELECT id,id > 2 and id < 6 FROM exam_result;
SELECT id,id > 2 or id < 5 FROM exam_result;

-- 关于null（只要有null参与的表达式，结果仍然是null）
SELECT 1 = NULL FROM exam_result;
SELECT 1 + NULL FROM exam_result;

-- DISTINCT （会去掉重复的）
SELECT DISTINCT math FROM exam_result;
SELECT * FROM exam_result;

-- count 计数（注意 NULL 不算数）
SELECT count(*) FROM exam_result;

SELECT * FROM exam_result;
-- 此时是无序的
-- 排序 descend：下降 ascend：上升（不写默认为升序）
SELECT * FROM exam_result order by id asc;
SELECT * FROM exam_result order by math desc;
-- 如果math相等，则互相的顺序还是无序，此时需要指定一个次要的排序依据（下一句的次要排序依据就是id）
SELECT * FROM exam_result order by math desc,id;

-- 分页 offset：偏移量（从哪一条开始，从0开始） limit：限制（展示多少条）
SELECT * FROM exam_result order by id limit 3 offset 0;

-- 选择行：where
SELECT id,name,math > 90 FROM exam_result;
-- 只会显示0或者1
SELECT id,name,math FROM exam_result where math > 90;
-- between and 两边均闭
SELECT * FROM exam_result where math between 65 and 70;

-- in
SELECT name FROM exam_result where name in ('孙悟空');

-- 模糊匹配：LIKE 只能用于字符串类型
-- %:数量是0个或多个
-- % ：代表任意字符
SELECT * FROM exam_result where name like '孙%';

-- NULL 作为boolean值，视为false
-- NULL不能直接用 = 判断
SELECT * FROM student where qq_mail is null;
SELECT * FROM student where qq_mail is not null;
-- <=> 可以进行null比较的
SELECT null = null;
SELECT null <=> null;

3、改、删

-- 更新：1）首先 where 某些符合条件的行
--       2）这些行的一些字段全部更新
-- 创建考试成绩表
DROP TABLE IF EXISTS exam_result;
CREATE TABLE exam_result (
	id INT,
	name VARCHAR(20),
	chinese DECIMAL(3,1),
	math DECIMAL(3,1),
	english DECIMAL(3,1)
);

-- 插入测试数据
INSERT INTO exam_result (id,name, chinese, math, english) VALUES
	(1,'唐三藏', 67, 98, 56),
	(2,'孙悟空', 87.5, 78, 77),
	(3,'猪悟能', 88, 98, 90),
	(4,'曹孟德', 82, 84, 67),
	(5,'刘玄德', 55.5, 85, 45),
	(6,'孙权', 70, 73, 78.5),
	(7,'宋公明', 75, 65, 30);
    
update exam_result set name = 'hello' ,math = 99 where id in(1,3,5);
update exam_result set chinese = chinese + 1 where id in (1,2,3);
update exam_result set name = 'hi' order by id limit 2; -- 没法用offset
-- 删除表中的数据
truncate exam_result; -- 重置了， 清空表中的数据；还原自增id
delete from exam_result where id in(1,2,3); 
delete from exam_result; -- 没法用offset