目录
一、MySQL表的增删查改的基础使用
注释：在SQL中可以使用“--空格+描述”来表示注释说明
CURD:即新增（Create）、查询（Retrieve）、更新（Update）、删除（Delete）
CREATE TABLE `books` (
`bid` int(11) NOT NULL AUTO_INCREMENT COMMENT '书籍的主键',
`name` varchar(45) NOT NULL COMMENT '书籍的名称，我们假设书籍不会重复',
`total` int(11) NOT NULL DEFAULT '0' COMMENT '图书馆应该有多少本这书',
`current` int(11) NOT NULL DEFAULT '0' COMMENT '现在图书馆有多少本这书',
PRIMARY KEY (`bid`),
UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='图书馆系统的书籍管理表';
1.1 新增（Create）
如果设置过默认库，则直接插入默认库的books表内
insert into books (name, total, current) values
('三国演义', 10, 10),
('红楼梦', 20, 20),
('西游记', 30, 30),
('水浒传', 40, 40);由于name位置是UQ(unique),所以如果再次插入相同name的书籍，则会报错
-- 由于现在的默认库是 ccb_1211，所以 books 的隐含台词是 ccb_1211.books
insert into books (name) values ('红楼梦');
-- 在第一条语句执行成功的前提下，再次执行，会失败，因为破坏了 name 上的唯一约束
insert into books (name) values ('红楼梦');
-- 一次性插入多条记录
insert into books (name) values ('JavaSE'), ('JavaDS'), ('JavaDB');AI只能保证是自增的，但是不保证是连续的，例如我们这里的主键bid勾上了自增AI，先插入的书红楼梦 bid = 1,第二次插入红楼梦由于name是唯一的，所以插入失败，但是bid = 2还是被消耗掉了，所以后面的书就从bid = 3开始插入。
bid name
1 红楼梦
3 JavaSE
4 JavaDS
5 JavaDB字段的顺序和建表时的顺序无关，只要和后边的 values 的顺序对应上即可
insert into books (total, name, current) values
(10, '春', 10),
(20, '夏', 20),
(30, '秋', 30),
(40, '冬', 40);没有默认值的字段，没有设置值，会报错， name 没有默认值，且没有指定值
insert into books (current) values (100);如何才能成功的进行插入
1.要求所有的字段必须都有值，字段有默认值的除外（设置默认值/自增/运行为Null）
2.要求插入的所有值满足类型要求
3.要求插入的所有字段的值必须满足字段约束：NN，UQ等
-- 全字段插入时
insert into books (bid, name, total, current) values (100, '一百', 100, 100);
-- 全字段插入时，可以使用自增
insert into books (bid, name, total, current) values (null, '一百1', 100, 100);
-- 全字段插入时，可以省略字段
insert into books values (null, '一百2', 100, 100);1.2 查询（Retrieve）
1. 21数据的查询分为以下几个部分
select 。。。
from 数据源（表）
where 条件
order by 排序的依据
limit + offset 分片
1. 从哪里进行数据的检索 from...
2. 应该检索哪些记录出来 where...
3. 具体要展示记录的是什么投影 select...
4. 将记录按照指定顺序排序 order by...
5. 排序的结果中获取切片 limit + offset
1.22 从一个数据表中进行查询
from 的后面跟上表名（books），在没有 where 条件的情况下，所有的记录都视为满足条件，select * 代表表中有哪些字段，展示哪些字段
select * from books;
//这行代码就是把books表所有内容全部输出,满足条件的记录有多少，就有多少行（暂时先不考虑 limit）from 后边的表可以起一个别名（alias）,以后用b这个别名就可以代表books
select * from books as b;
-- as 可以省略
select * from books b;1.23 where 条件
给定一个逻辑表达式
将表中所有记录代入到这个逻辑表达式中
只检索逻辑表达式的结果是 true 的，mysql 中的 0 视为 false，非 0 视为 true
= 代表相等判断，在 SQL 中，== 这个运算符不存在
select bid, name, total, current from books where bid = 2;
//找到bid = 2的字段，输出他的name,total,currentwhere 条件的运算符
| >, >=, <, = | 大于，大于等于，小于，小于等于 |
| = | 等于，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,结果才是TRUE |
| OR | 任意一个条件时TRUE，结果就是TRUE |
| NOT | 条件为TRUE，结果为FALSE |
And 的优先级高于OR，先对and进行运算，再对OR进行运算
select * from books where 1 = 1; -- 所有记录
select * from books where 1 = 2; -- 一条记录都没有
select * from books where bid != 2 and total > 10;
select * from books where (bid != 2 and bid != 3) or total > 10;
select bid, name, total, current, bid != 2, bid != 3, bid != 2 and bid != 3, total > 10 from books;表达式 c between start to end; c是否存在于【start，end】这个左闭右闭的区间内
所以，隐藏的含义就是C得是一个可以视为区间的类型，比如int,日期，时间
select 1 between 0 and 5;
select '2022-12-12' between '2019-12-12' and '2025-12-12';
select '09:48:32' between '08:00:00' and '12:00:00';把 between ... and ... 代入到 where 条件中
select * from books where total between 20 and 30;IN(option,...)
因为表内只有三国演义和红楼梦，所以只会返回这两本书的所有字段
select * from books where name in ('三国演义', '哈利波特', '红楼梦');关于NULL的理解
1. NULL（空）代表未知
2. 凡是有null参加的表达式，得到的结果仍然是null
select null;
select 1 + null;
select 1 * null;
select 1 - null;
select 1 / null;
select 1 = null;
select 1 != null;
select null = null;
select null != null;3. 在布尔运算的时候，null视为是false
select id, name from test1 where name = null; -- 这个查出来多少条记录 0
select id, name from test1 where name != null; -- 这个查出来多少条记录 0
-- 针对 null 的比较，不能简单的使用 = 和 != 比较
-- 1) 标准做法，使用 col is null 或者 col is not null <=> not (col is null)
-- 2) 不是很常见的做法，使用 <=> 进行比较 not (a <=> null)
-- <=> 也可以进行非 null 的正常相等比较
select id, name, name is null, name is not null from test1;
select id, name, name <=> null, not name <=> null from test1;
select 1 <=> 1, null <=> null;
select 1 = 1, null = null;关于字符串的模糊匹配
xxx like '...', %表示任意多个（包括0个）任意字符；_ 表示任意一个字符,且必须为1个字符
select '张' like '张%'; -- 返回true
select '张三' like '张%'; -- 返回true
select '张一二三四五六七八九十' like '张%'; -- 返回true
select '小张' like '张%'; -- 返回false
select '张' like '张_'; -- 返回false
select '张三' like '张_'; -- 返回true
select '张一二三四五六七八九十' like '张_'; -- 返回false
select '小张' like '张_'; -- 返回falseMySQL中like 查询效率不一定很高，所以不要滥用
如果非要用，尽量使用'张%' 或者'张_'
尽量不要使用'%张' 或者'_张'
原因和内部的存储结构有关，一般称这个原则为最左匹配原则
where条件小结：
逻辑上，将所有记录，带入到where后面的逻辑表达式中，得到表达式的boolean值结果，只留下结果为true的记录
1. 相等判断使用 =； <=>也代表相等判断，且多个null的判断
2. not, and, or 必须这么写，注意优先级
3. x betweens s and e; 在左闭右闭区间内检索
4. e in (e1,e2,... ), 元素在不在集合中
5. 关于null的理解以及比较，is null / <=>
6. 字符串的模糊匹配 %和_的使用
1.24 select
1. 直接写个 select * , 代表所有字段都展示，并且展示的顺序按照建表的顺序来
2. 实际应用中不建议使用*，直接给出字段名称，要几个就输入几个字段，字段之间用逗号分割
3. 可以起别名：select name n from books
4. 可以有函数运算
upper(...) 把字符串变成大写
concat(...) 连接几个字符串
substr(...) 切割子串
select 'hello', upper('hello');
select 'HELLO', lower('HELLO');
select concat('hello', 'world');
select concat('hello', ' ', 'world');
select concat('hello', ' ', 'world', '!');
-- 第一个字符是从 1 开始，不是从 0
select substr('hello', 1, 3); --返回hel5. select distinct
由于distinct会合并相同的记录，所以会影响检索出来的行数
例如现在表内有4个total值: 10, 20,10,20
如果select total 则输出10, 20,10,20
但如果是select distinct total则只会输出独特的项 10,20
//这两种写法都可以
select distinct total from books;
select distinct(total) from books;select 小结
1. 除了几个特殊情况外，投影的规则，不影响最终检索出来的行数
2. 投影中出现了*,就不能出现其他东西了
3. 除了*之外，都可以起别名
4. distinct 做去重，不能是*；有了distinct之后，就不能出现其他字段了
select name, total from books;
//下面的就会报错，name是正常输出所有字段，但是distinct会做去重来影响字段
select name, distinct(total) from books;1.25 对检索出来的记录进行排序 order by
1. 如果没有指定排序规则，则应该认为检索出来的顺序是未定义行为（无序的）
2. order by 其中order是排序的意思，by是以什么依据来进行排序
-- 使用 order by
select * from books; -- 无序
select * from books order by bid; -- 以 bid 作为顺序排序，按照默认规则（升序）
select * from books order by bid asc; -- 升序
select * from books order by bid desc; -- 降序如果有相同的，则相同的记录之间的顺序仍是未定义顺序，如果有多个total值相等，则total向等的值的顺序是未定义的
select * from books order by total asc;如果想要让total相等的字段变得有序，需要引入第二次排序规则
先按照 total 的升序进行排序
针对 total 相等的记录之间，按照 current 的倒序排序
多次排序之间的升降序可以不同
select * from books order by total asc, current desc;order by 也可以用于表达式
select bid, name, total, current, total - current from books order by total - current asc;
select bid, name, total, current, total - current from books order by total - current desc;1.26 分片 limit + offset
1. 分片必须是在已经有序的结果上才有确定的结果
2. 传入两个东西：从哪开始取 offset (从0开始），最多取多少条limit
//从第一个（0相当于数组的下标，指向的是第一个数）开始取offset 0，最多取两个limit 2
select * from books order by bid limit 2 offset 0;
//从第3个开始取offset 2，最多取两个limit 2
select * from books order by bid limit 2 offset 2;很容易实现的一个分页功能, 展示一个列表，每页一共10条记录
取第 x 页，limit 2 offset (x - 1) * 2
select * from 表 where条件 order by 排序limit 10 offset 0; 第一页
select * from 表 where条件 order by 排序limit 10 offset 10; 第二页
select * from 表 where条件 order by 排序limit 10 offset 20; 第三页
1.3 更新（Update）
1. 通过where子句，找出符合条件的所有记录（下一步的更新只会更新这些记录）
2. 通过set语句对这些记录中的一个或者多个字段更新
-- 更推荐使用，因为上面两个属于bid主键PK和name是UQ
update books set total = 100 where bid = 7;
update books set total = 101 where name = '左传';
-- 不太推荐使用，因为total = 10的记录有很多，可能会把其他的也修改了
update books set total = 102 where total = 10;
update books set total = 103 where current = 5;
--
update books set current = 0; --没有设置where语句,所有记录的current都会被修改为0；1.4 删除（Delete）
先通过where条件筛选出记录，删除这些满足条件的记录
delete from books where bid > 3; -- 删除所有bid大于3的记录
delete from books where bid = 2；-- 删除所有bid=2的记录
delete from books; 没有设置where条件，则所有记录都会被删除
扫一扫
854
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
