MySQL的CRUD操作

系列文章目录

一、添加操作

• 添加单条语句

insert into `借阅表` values (100010,'223410','2001','2022-10-22',null,'借阅');

• 也可以一次性添加多条数据,括号后面用逗号隔开

INSERT INTO `借阅表` VALUES (100002, '223411', '0002', '2020-09-28', '2020-10-13', '已还'), (100003, '321123', '1001', '2020-07-01', NULL, '过期'),(100004, '321124', '2001', '2020-10-09', '2020-10-14', '已还'),(100005, '321124', '0001', '2020-10-15', NULL, '借阅');

二、修改操作

• update是修改属性值

update `库存表` set `书号`='借出' where `条码`=223410;

• alter 是修改字段
• 这里是把单位的字段修改成college

ALTER TABLE `读者表` change 单位 college varchar(10) not null; -- 需要更换的字段名加不加引号都可以

三、约束操作

• 共有6类
  1、NOT NULL：非空约束，用于约束该字段的值不能为空。比如姓名、学号等。
  2、DEFAULT：默认值约束，用于约束该字段有默认值，约束当数据表中某个字段不输入值时，自动为其添加一个已经设置好的值。比如性别。
  3、PRIMARY KEY：主键约束，用于约束该字段的值具有唯一性，至多有一个，可以没有，并且非空。比如学号、员工编号等。
  4、UNIQUE：唯一约束，用于约束该字段的值具有唯一性，可以有多个，可以没有，可以为空。比如座位号。
  5、CHECK：检查约束，用来检查数据表中，字段值是否有效。比如年龄、性别。
  6、FOREIGN KEY：外键约束，外键约束经常和主键约束一起使用，用来确保数据的一致性，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值。比如学生表的专业编号，员工表的部门编号，员工表的工种编号。

• 检查约束

-- 设置check
-- 修改读者类别表，可借数量必须在0——30之间
alter TABLE `读者类型表` add check (`可借数量`>0 and  `可借数量`<30);

• 外键约束

-- 在多方创建外键 （读者表)

alter table `读者表` add CONSTRAINT reads_id foreign key (`类别号`) references `读者类型表` (`类别号`)
on delete cascade 
on update cascade;

四、查询操作

-- 直接查询
select * from `借阅表`；

-- 去重
select distinct  `书号`,`库存状态` from `库存表`;

-- where条件查询
-- select 字段 from 表名 where 条件；
select * from `图书表` where `数量`=10;

-- 模糊查询
-- select 字段 from 表名 where 字段 like '%数据%';
SELECT * from `图书表` where `书号` like '%A%';
-- 从 图书表 表中查询 name 中含有 'A' 的所有记录

-- 算术运算符
-- 语法：>（大于）, <（小于）, =（等于）, !=（不等于）, <>（不等于）, >=（大于等于）, <=（小于等于）
select * from `图书表` where `数量`>10;

-- 逻辑运算符
-- and（且）, or（或）, not（非）

SELECT * from `图书表` where (`类别`='财经' or `类别`='文学') and `数量`>5; 

-- in与not in运算符
-- select 字段 from 表名 where 字段 in(列表)
select * from `图书表` where `数量` in(5, 10,20);
从 图书表中查询 数量 为 (5, 10,20) 之间的所有记录


-- 排序查询
-- select 字段 from 表名 order by 字段 排序方式(升序 asc, 降序 desc);
select * from `图书表` order by `数量` asc;
-- 从 图书表 表中查询所有记录并按照 数量 升序排序


-- 范围运算
-- select 字段 from 表名 where 字段 between 范围1 and 范围2;

select * from `图书表` where `数量` between 5 and 20;
-- 从 `图书表` 表中查询 数量 >= 13 and 数量 <= 15 的所有记录
-- 	它等价于 select * from `图书表` where `数量` >= 5 and `数量` <= 20;


-- 限制查询

-- select 字段 from 表名 limit n, m;

select * from `图书表` limit 3, 5;
-- 从 `图书表` 表中查询第三行到第五行的记录，但要注意的是 0 表示第一行记录，


-- 嵌套查询

select `书号`, `书名` from `图书表` where `书号` = (select `书号` from `库存表` where `条码`='123412');

-- 查询 student 表中 (engScore 表中 score = 100 的 name）的 name，age 记录
-- 也就是说通过查询库存表中的条码是123412的书号，然后用这个条码当作条件查询 `图书表` 表中的书号与书名

-- ## 多表连查

# 内连接
#语法：select 字段 from 表1 inner join 表2 on 表1.字段 = 表2.字段;

select * from `图书表` inner join `库存表` on `图书表`.`书号` = `库存表`.`书号`;

-- 将 `图书表` 表与 `库存表` 通过相同的 书号 拼接起来，简单的来说就是两个 表 合并


-- 左连接
select * from `图书表` left join `库存表` on `图书表`.`书号` = `库存表`.`书号`;
-- 与内连接形式相同，但左表为主表，指定字段都会显示，右表为从表，无内容会显示 null

-- 右连接
select * from `图书表` right join `库存表` on `图书表`.`书号` = `库存表`.`书号`;

-- 与内连接形式相同，但右表为主表，指定字段都会显示，左表为从表，无内容会显示 null



-- 聚合函数
select min(`单价`) from `图书表`;
-- 从 `图书表` 中查询最小的 `单价` 

select max(`单价`) from `图书表`;
-- 从 `图书表` 中查询最大的 `单价` 

-- select sum(字段) from 表名;
select sum(`单价`) from `图书表`;
-- 图书表 中统计所有 单价 的和

-- select avg(字段) from 表名;
select avg(`单价`) from `图书表`;
-- 从 `图书表` 中对所有的 age 求平均值


-- select count(字段) from 表名;
 select count(`书名`) as 书类 from `图书表`;
-- 从图书表 中查询 书名 的记录个数
-- 给从 图书表 中查询的 `书名` 的记录个数 起了一个别名叫 '名字记录个数'

相关子查询

1、IN 子查询

• IN 子查询用于进行一个给定值是否存在在子查询结果集中的判断

select * FROM `读者表` where `类别号` in  (SELECT `类别号` FROM `读者类型表` );

2、比较子查询

• 使表达式的值与子查询的结果进行比较运算

select * FROM `读者表` where `类别号` =any(SELECT `类别号` FROM `读者类型表` );

3、EXISTS 子查询

• 用于测试子查询的结果是否为空表，若子查询的结果不为空表，若子查询的结果集不为空，EXISTS返回TRUE，否则返回FALSE。EXISTS还可与NOT结合使用，即NOT EXISTS，其返回值与EXISTS刚好相反。

SELECT * from `图书表` where EXISTS 
(SELECT * FROM `库存表`  where `书号`=`图书表`.`书号`);

4、联合查询

• 两个表的数据按照一定的查询条件查询出来后，需要将结果合并到一起显示出来。

SELECT * from `图书表` where `书号`='A0120'
union all
SELECT * from `图书表` where `类别`='文学';