MySQL表的增删查改（基础）

前言

MySQL下的数据库语句

---

一、插入语句

DROP TABLE IF EXISTS student;
CREATE TABLE student (
    id INT,
    sn INT comment '学号',
    name VARCHAR(20) comment '姓名',
    -- 注意最后一个不写,号
    qq_mail VARCHAR(20) comment 'QQ邮箱'
);

-- values 中的值的顺序，必须严格按照建表时的顺序
INSERT INTO student VALUES (100, 10000, '唐三藏', NULL);
INSERT INTO student VALUES (101, 10001, '孙悟空', '11111');
-- 大小写都可以
-- 一次插入多组数据
insert into student values (102, 10002, '陈沛鑫', null), (103, 10003, '刘老师', '111');

-- 没有出现的字段，填充默认值
-- 如果缺失的字段没有默认值怎么办？
INSERT INTO student (id, sn, name) VALUES 
 (102, 20001, '曹孟德');

-- 带一个日期格式的字段    
DROP TABLE IF EXISTS student;
CREATE TABLE student (
    id INT,
    sn INT comment '学号',
    name VARCHAR(20) comment '姓名',
    qq_mail VARCHAR(20) comment 'QQ邮箱',
    joined_at DATETIME,
    brithday DATE
);

insert into student 
	values (103, 3001, 'xxx', null, '2022-03-18 19:36:27', '2000-12-12');
    
    
-- 创建考试成绩表
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);

二、查询语句

1.查询

-- 慎用，查询全部信息
SELECT * FROM exam_result;
-- 查询id信息
select id from exam_result;
select id, name from exam_result;
select name, id from exam_result;

2.select后看作表达式

-- 将select后面看作为一个表达式
-- 输出全为103
select 103 from exam_result;
select id, name, 103 from exam_result;
select 100 / 2 from exam_result;
-- 查询id*10的
select id * 10 from exam_result;
select id, name, english + math + chinese from exam_result;
-- 查询id，name,englishi+math+chinese并给此式子称为total ，可以起中文名称
select id, name, english + math + chinese as total from exam_result;
select id, name, english + math + chinese as 总分 from exam_result;
select id, name, english + math + chinese 总分 from exam_result;

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 (100, 10000, '唐三藏', NULL);
INSERT INTO student VALUES (101, 10001, '孙悟空', '11111');
INSERT INTO student (id, sn, name) VALUES 
	(102, 20001, '曹孟德'),
	(103, 20002, '孙仲谋');
 
-- false/true/false/false
select id = 101 from student;
select id, id > 101 or id = 102 from student;
select id != 101 from student;
select not id = 101 from student;

3.有null参与的 和 = 与<=>

-- 有 null 参与的运算结果还是 null
-- 即使是 null，做bool值的时候，也看作 false
select qq_mail = '111' from student;
select qq_mail + 10 from student;
select qq_mail = null from student;


select null = null; -- null
select 1 = null; -- null
select null <=> null; -- 1
select 1 <=> 1; -- 1
select 1 <=> null; -- 0
select null is not null; -- 0

4.排序

-- 排序
-- order（排序）by（按照哪个字段、表达式）
-- asc : ascend（升序），不写默认是升序
-- desc : descend（降序）
-- 可以指定指定多个排序字段，是前面相等的情况下，产生意义
-- 不写 order by 的结果集，一律认为无序
select * from exam_result order by math, id desc, chinese;

5.分页

-- 分页
-- limit : 限制  每次多少个结果
-- offset : 起始位置，从 0 开始计算
-- limit l offset o;
-- limit o, l;
-- limit l;   <-> limit l offset 0;
-- 必须先要求结果集有序
select * from exam_result order by math, id;
select * from exam_result order by math, id limit 2;
select * from exam_result order by math, id limit 2 offset 3;
select * from exam_result order by math, id limit 3, 2;

6.条件

-- where 条件
select id, name, math, math > 90 from exam_result;
select id, name, math from exam_result where math > 90;
-- null 在 bool 中视为 false
select * from student where qq_mail = null;
SELECT name, english FROM exam_result WHERE english < 60;
SELECT name, chinese, english FROM exam_result WHERE chinese > english;
SELECT name, chinese + math + english 总分 FROM exam_result 
	WHERE chinese + math + english < 200;
select * from exam_result where 1 = 1;
-- between   and
select * from exam_result where math between 60 and 65;
-- 为或的关系
select * from exam_result where name in ('孙悟空', '唐三藏', '猪悟能');

7.%

-- 孙开头
select name, name like '孙%' from exam_result;
-- 孙结尾
select name, name like '%孙' from exam_result;
-- 只要有孙
select name, name like '%孙%' from exam_result;
-- 两个字，孙开头
select name, name like '孙_' from exam_result;

二、更新和删除

1.更新

-- 创建考试成绩表
DROP TABLE IF EXISTS exam_result;
CREATE TABLE exam_result (
	id INT,
	name VARCHAR(20),
	chinese DECIMAL(3,1), -- 三位数组，2位整数，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 chinese = 5 where name = '唐三藏';
update exam_result set math = math + 1 where name = '唐三藏';
update exam_result set chinese = 4, math = 4, english = 4 where name = '唐三藏';
update exam_result set chinese = 4, math = 4, english = 4;

-- update 支持 limit，但不支持 limit, offset
update exam_result set chinese = 40, math = 40, english = 40 order by id limit 3;

2.删除和截断

-- 数据的删除
-- truncate（截断）   list.clear()   O(1)
truncate exam_result;
-- delete
delete from exam_result;   -- foreach (item in table) delete
delete from exam_result where id < 7;
delete from exam_result order by id limit 3; 

三、应用

1.基本功能

CREATE TABLE `lib_0403`.`books` (
  `bid` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL COMMENT '书籍名称',
  `count` INT NOT NULL COMMENT '存量',
  `total` INT NOT NULL COMMENT '总量',
  PRIMARY KEY (`bid`))
COMMENT = '书籍信息';

CREATE TABLE `lib_0403`.`readers` (
  `rid` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`rid`))
COMMENT = '借阅者信息';

CREATE TABLE `lib_0403`.`records` (
  `reid` INT NOT NULL AUTO_INCREMENT,
  `rid` INT NOT NULL COMMENT '谁借的',
  `bid` INT NOT NULL COMMENT '借的哪本书',
  `borrowed_at` DATETIME NOT NULL DEFAULT current_timestamp COMMENT '借阅时间',
  PRIMARY KEY (`reid`))
COMMENT = '借阅记录';

insert into records(rid,bid) values (1,1);
insert into readers(rid,name) values (1,'金南俊');

-- 借阅人：浏览书籍记录
select * from books order by bid;

-- 借阅人：借书操作 提供：当前用户（rid） 借了哪本书
insert records(rid,bid) values (3,3);
update books set count = count - 1 where bid = 3;

-- 借阅人：还书操作
delete from records where rid = 3;
update books set count = count + 1 where bid = 3; 

-- 借阅人：查询借阅记录
select * from records where rid = 1 order by  borrowed_at;

-- 管理员：上架书籍
insert into books (name,count,total) values ('解忧杂货店',10,15);

-- 管理员：下架
-- 数量减少
update books set count = count - 1  where bid = 2;
-- 不能有人还借着 delete from books where  ...;

2.外键

为了使records和books、readers表中数据具有约束性（建立联系），我们给records 建立外键（首先应该清空records表中的数据，然后修改表;或者新建表）

Foreign Key Name:通常用fk_name命名，方便区分

-- 此时不能直接插入records表会报错，应该先插入books和readers表
insert into records (rid,bid) values (2,4);
-- 并且不能直接删除，比如说books表中的某行信息

对于右侧的On Updata /OnDelete
No Action 等价于 Restrict(限制)：想要删除读者表中的信息时，受借阅记录影响不能直接删除，收到限制
Cascade(级联) ：就是像瀑布一样，当删除读者表某条信息时，对应的借阅记录中
记录的也会被删除
Set Null (设立为空):即字面意义设置为空，当读者表中某条信息删除时，对应的借阅信息中的rid（对应字段）会被自动改为null