免费一级做网站,wordpress 菜单插件,冠县网站开发,开发一个简单的系统
2-MySQL 约束,视图,索引及常见函
1 SQL约束
SQL 约束用于规定表中的数据规则。实际上就是表中数据的限制条件。是为了保证数据的完整性而实现的一套机制。
MySQL的约束种类如下#xff1a;
非空约束#xff1a;NOT NULL
NOT NULL约束强制…
2-MySQL 约束,视图,索引及常见函
1 SQL约束
SQL 约束用于规定表中的数据规则。实际上就是表中数据的限制条件。是为了保证数据的完整性而实现的一套机制。
MySQL的约束种类如下
非空约束NOT NULL
NOT NULL约束强制该字段不接受 NULL 值。
唯一约束UNIQUE
UNIQUE 约束唯一标识数据库表中的每条记录。 即该字段的值不能重复但null除外。
主键约束PRIMARY KEY
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键、自动增长约束PRIMARY KEY AUTO_INCREMENT
在每次插入新记录时自动地创建主键字段的值。开始值是 1每条新记录递增 1。
外键约束FOREIGN KEY
FOREIGN KEY约束保证一个表中的数据匹配另一个表中的值的参照完整性。
默认约束DEFAULT
DEFAULT约束用于保证该字段有默认值。
1.1 主键约束#
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须 唯一、非空。
每个表都应该有一个主键并且每个表只能有一个主键。
作用:
1保证实体的完整性;
2加快数据库的操作速度
3 在表中添加新记录时DBMS会自动检查新记录的主键值不允许该值与其他记录的主键值重复。
4) DBMS自动按主键值的顺序显示表中的记录。如果没有定义主键则按输入记录的顺序显示表中的记录。
案例演示
-- 创建学生表将id设置为主键
CREATE TABLE student (s_id INT PRIMARY KEY,s_name VARCHAR(30),s_age INT
);-- 显示student表的结构
DESC student;-- 插入数据
INSERT INTO student(s_id, s_name, s_age)
VALUES
(1, Tom, 23),
(2, Jerry, 24);-- 查询所有数据
SELECT * FROM student;-- 尝试在主键列插入重复值报错
INSERT INTO student(s_id, s_name, s_age)
VALUES
(2, Jhon, 25);-- 如需撤销 PRIMARY KEY 约束
-- ALTER TABLE 表名 DROP PRIMARY KEY;
ALTER TABLE student DROP PRIMARY KEY;-- 如果在建表后需要添加主键
-- ALTER TABLE 表名 ADD PRIMARY KEY(主键列名);
ALTER TABLE student ADD PRIMARY KEY (s_id);
1.2 主键自增约束#
在每次插入新记录时自动地创建主键字段的值。
默认地AUTO_INCREMENT 的开始值是 1每条新记录递增 1。
案例演示
-- 创建教师表
CREATE TABLE teacher (t_id INT PRIMARY KEY AUTO_INCREMENT, -- 设置主键自增t_name VARCHAR(30)
);-- 显示teacher表的结构
DESC teacher;-- 插入数据: 主键列为空插入数据时自动生成一个自增的数字
INSERT INTO teacher
VALUES
(NULL, 吴亦凡),
(NULL, 王力宏);SELECT * FROM teacher;-- 如果需要改变自增的起始值
ALTER TABLE teacher AUTO_INCREMENT 100;
-- 再次插入数据查看
INSERT INTO teacher
VALUES
(NULL, 玉田),
(NULL, 王麻子);-- 如需删除自增约束
-- ALTER TABLE 表名 MODIFY 列名 数据类型;
ALTER TABLE teacher MODIFY t_id INT;-- 如果在建表后添加自增约束
-- ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;
ALTER TABLE teacher MODIFY t_id INT AUTO_INCREMENT;-- 再次插入数据测试
INSERT INTO teacher
VALUES
(NULL, 小甜甜);
1.3 非空约束#
NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着如果不向字段添加值就无法插入新记录或者更新记录。
案例演示
-- 创建员工表employee
CREATE TABLE employee (e_id INT PRIMARY KEY AUTO_INCREMENT, -- 设置主键自增e_name VARCHAR(100) NOT NULL -- 姓名列不能为空
);DESC employee;-- 插入数据
INSERT INTO employee
VALUES
(NULL, 八戒),
(NULL, 悟空);SELECT * FROM employee;-- 尝试插入一条空数据报错此时添加数据标记 非空的字段必须给值否则约束不通过。
INSERT INTO employee
VALUES
(NULL, NULL);-- 如需删除非空约束
ALTER TABLE employee MODIFY e_name VARCHAR(100) NULL;-- 如需在创建表后添加非空约束
ALTER TABLE employee MODIFY e_name VARCHAR(100) NOT NULL;
1.4 默认约束#
default当设置约束后插入数据时如果不想给值可以设置默认值。
当插入时发现你没给值可以使用默认值填充。
案例演示
-- 创建学生信息表
CREATE TABLE student_info (s_id INT PRIMARY KEY AUTO_INCREMENT, -- 设置主键自增s_name VARCHAR(100) NOT NULL, -- 姓名列不能为空s_modify_time datetime default now() -- 当添加数据时如果没有给该列赋值默认去当前时间
);insert into student_info(s_id, s_name) values (null, tom);select * from student_info;
1.5 唯一约束#
unique字段数据必须唯一不重复、字段可以为NULL。
案例演示
-- 创建用户表规定用户名唯一
CREATE TABLE tb_user (u_id INT PRIMARY KEY AUTO_INCREMENT, -- 设置主键自增u_username VARCHAR(100) UNIQUE -- 用户名唯一
);DESC tb_user;-- 插入数据
INSERT INTO tb_user
VALUES
(NULL, zhangsan),
(NULL, lisi);SELECT * FROM tb_user;-- 尝试插入重复值报错
INSERT INTO tb_user
VALUES
(NULL, zhangsan);-- 如需删除唯一约束
-- ALTER TABLE 表名 DROP INDEX 列名
ALTER TABLE tb_user DROP INDEX u_username;-- 如果在创建表后添加唯一约束
-- ALTER TABLE 表名 ADD UNIQUE (列名)
ALTER TABLE tb_user ADD UNIQUE (u_username);
-- 注意表中该列如果已经存在重复值不能添加唯一约束。先删除重复值
1.6 外键约束#
一个表中的 FOREIGN KEY 外键指向另一个表中的 UNIQUE KEY(唯一约束的键)。
我们通过一个实例来解释外键
订单表orderlist
idnumberuid1hn00112hn00213hn00324hn00425hn00536hn0063
用户表user
idname1张三2李四3王五
订单表中的uid列指向user表中的id列也就是说添加订单时订单所属的用户必须是真实存在的同理如果要删除用户表中的数据也必须保证订单表中已经没有该用户的订单。
外键约束能防止非法数据插入外键列用于预防破坏表之间连接的行为。
示例
-- 外键约束语法CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)-- 创建用户表主表
CREATE TABLE USER (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL
);-- 添加用户数据
INSERT INTO USER VALUES (NULL,张三),(NULL,李四);SELECT * FROM USER;-- 创建订单表
CREATE TABLE orderlist (id INT PRIMARY KEY AUTO_INCREMENT, -- idnumber VARCHAR(20) NOT NULL, -- 订单编号uid INT, -- 外键列CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) -- 外键列uid指向user表中的主键id列
);-- 订单表添加数据外键列的值必须是主表user中存在的主键值
INSERT INTO orderlist
VALUES
(NULL, hn001, 1),
(NULL, hn002, 1),
(NULL, hn003, 2);
-- 如果不存在添加失败
INSERT INTO orderlist VALUES (NULL,hn005,3);-- 删除李四用户删除失败
DELETE FROM USER WHERE NAME李四;DESC orderlist;-- 如需删除外键
-- 标准语法ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;-- 在创建表后添加外键约束
-- 标准语法ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);-- 当添加数据时如果想取消外键约束的检查
SET FOREIGN_KEY_CHECKS 0;-- 恢复外键约束检查
SET FOREIGN_KEY_CHECKS 1;1.7 外键级联操作#
级联更新
当我们想把user用户表中的某个用户id修改我们希望订单表中该用户所属的订单用户编号也随之修改。
级联删除
当我们想把user用户表中的某个用户删掉我们希望该用户所有的订单也随之被删除。
-- 添加外键约束同时添加级联更新 标准语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名)
ON UPDATE CASCADE;-- 添加外键约束同时添加级联删除 标准语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名)
ON DELETE CASCADE;-- 添加外键约束同时添加级联更新和级联删除 标准语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名)
ON UPDATE CASCADE ON DELETE CASCADE;
示例
-- 继续使用上一节中的数据-- 删除外键约束
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;-- 添加外键约束同时添加级联更新和级联删除
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) ON UPDATE CASCADE ON DELETE CASCADE;-- 将王五用户的id修改为5 订单表中的uid也随之被修改
UPDATE USER SET id5 WHERE id3;-- 将王五用户删除 订单表中该用户所有订单也随之删除
DELETE FROM USER WHERE id5;
2 多表查询
2.1 表与表的关系#
通过之前ER图的讲解我们知道实体与实体的关系有三种实体对应表所以表与表的关系也有三种。
2.1.1 一对一# 一对一关系是建立在两张表之间的关系。一个表中的一条数据可以对应另一个表中的一条数据。
例如一个人对应一张身份证一张身份证对应一个人。
实现方式
在任意一个表建立外键去关联另外一个表的主键。
示例
-- 创建person表
CREATE TABLE person(id INT PRIMARY KEY AUTO_INCREMENT, -- 主键idNAME VARCHAR(20) -- 姓名
);
-- 添加数据
INSERT INTO person VALUES (NULL,张三),(NULL,李四);-- 创建card表
CREATE TABLE card(id INT PRIMARY KEY AUTO_INCREMENT, -- 主键idnumber VARCHAR(20) UNIQUE NOT NULL, -- 身份证号pid INT UNIQUE, -- 外键列CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id)
);
-- 添加数据
INSERT INTO card VALUES (NULL,12345,1),(NULL,56789,2);
注一对一关系使用场景较少因为在该案例的场景中我们可以把身份证号码作为person表的一个字段。
2.1.2 一对多#
一对多关系是建立在两张表之间的关系。一个表中的一条数据可以对应另一个表中的多条数据但另一个表中的一条数据只能对应第一张表的一条数据。 例如
一个班级拥有多个学生一个学生只能够属于某个班级。
一个用户可以有多个订单一个订单只能属于某个用户。
实现方式
在多的一方建立外键约束来关联一的一方主键。注意外键永远在多方。外键允许重复允许含有空值。
示例
-- 上一节讲解外键约束时采用的用户和订单表即为一对多的关系-- 班级和学生示例
-- 创建班级表
CREATE TABLE class (c_id INT PRIMARY KEY AUTO_INCREMENT, -- 主键idc_name VARCHAR(100) NOT NULL, -- 班级名称c_capacity INT -- 班级容量
);-- 班级表插入数据
INSERT INTO class
VALUES
(NULL, 大数据01, 80),
(NULL, 大数据02, 80),
(NULL, 大数据03, 60);SELECT * FROM class;-- 创建学生表
CREATE TABLE student (s_id INT PRIMARY KEY AUTO_INCREMENT, -- 主键ids_name VARCHAR(100) NOT NULL, -- 学生姓名cid INT, -- 外键列 学生所属班级CONSTRAINT sc_fk1 FOREIGN KEY (cid) REFERENCES class (c_id) -- 外键约束
);DESC student;-- 学生表插入数据如果外键列所表示的班级id在班级表不存在则数据插入失败
INSERT INTO student
VALUES
(NULL, 张三, 1),
(NULL, 李四, 1),
(NULL, 王五, 2);SELECT * FROM student;
2.1.3 多对多#
多对多关系是关系数据库中两个表之间的一种关系 该关系中第一个表中的一个行可以与第二个表中的一个或多个行相关。第二个表中的一个行也可以与第一个表中的一个或多个行相关。 例如
学生表和课程表一个学生可以选修多门课程一个课程可以被多个学生选修。
产品表和订单表一个订单中可以包含多个产品一个产品可能出现在多个订单中。
实现方式
创建第三个表该表通常称为联接表它将多对多关系划分为两个一对多关系。在该表中建立两个列每个列作为外键参照各自的表的主键。
示例
-- 创建student表
CREATE TABLE student(id INT PRIMARY KEY AUTO_INCREMENT, -- 主键idNAME VARCHAR(20) -- 学生姓名
);
-- 添加数据
INSERT INTO student VALUES (NULL,张三),(NULL,李四);-- 创建course表
CREATE TABLE course(id INT PRIMARY KEY AUTO_INCREMENT, -- 主键idNAME VARCHAR(10) -- 课程名称
);
-- 添加数据
INSERT INTO course VALUES (NULL,语文),(NULL,数学);-- 创建中间表
CREATE TABLE stu_course(id INT PRIMARY KEY AUTO_INCREMENT, -- 主键idsid INT, -- 用于和student表中的id进行外键关联cid INT, -- 用于和course表中的id进行外键关联CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id), -- 添加外键约束CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id) -- 添加外键约束
);
-- 添加数据
INSERT INTO stu_course VALUES (NULL,1,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);
-- 上面的关系表示id为1的学生选择了id为1和2的课程而id为1的课程也被id为1和2的两个学生选择多对多的关系-- 练习 订单表和商品表
-- 创建订单表
CREATE TABLE t_order (id INT PRIMARY KEY AUTO_INCREMENT, -- 主键idorder_number VARCHAR(20) NOT NULL, -- 订单编号order_time TIMESTAMP -- 下单时间
);-- 创建商品表
CREATE TABLE t_product (id INT PRIMARY KEY AUTO_INCREMENT, -- 主键idproduct_name VARCHAR(100), -- 商品名称product_price DOUBLE -- 商品价格
);-- 创建中间表连接表表名一般情况下取两张表的名字用下划线连接方便管理
CREATE TABLE t_order_product (id INT PRIMARY KEY AUTO_INCREMENT, -- 主键idorder_id INT, -- 引用订单表idproduct_id INT, -- 引用商品表idCONSTRAINT order_product_fk1 FOREIGN KEY (order_id) REFERENCES t_order (id),CONSTRAINT order_product_fk2 FOREIGN KEY (product_id) REFERENCES t_product (id)
);-- 插入数据
INSERT INTO t_product
VALUES (NULL, 西游记, 88), (NULL, 三国演义, 77), (NULL, 水浒传, 99);SELECT * FROM t_product;INSERT INTO t_order
VALUES (NULL, hn001, NULL), (NULL, hn002, NULL), (NULL, hn003, NULL);SELECT * FROM t_order;-- 插入中间表数据
INSERT INTO t_order_product
VALUES
(NULL, 1, 1),(NULL, 1, 3),(NULL, 2, 2),(NULL, 2, 3);
-- 表示id为1的订单买了西游记和水浒传两本书id为2的订单买了三国演义和水浒传两本书SELECT * FROM t_order_product;注意在多对多关系中插入数据时先两侧再中间表而删除数据时要先中间后两侧。
2.2 多表查询#
2.2.1 准备数据#
重新给商品表和商品类别表初始化数据。
-- 创建category分类表
CREATE TABLE category (cid int comment 主键id,cname varchar(50) DEFAULT NULL comment 分类名称,PRIMARY KEY (cid)
);-- 创建product 表并声明 category 表的cid字段作为外键
CREATE TABLE product (pid int(11) NOT NULL AUTO_INCREMENT comment 主键id,pname varchar(500) DEFAULT NULL comment 产品名称,price DECIMAL(10,2) DEFAULT NULL comment 产品价格,cid int(11) DEFAULT NULL comment 产品所属类别,PRIMARY KEY (pid),CONSTRAINT produce_cid_fk FOREIGN KEY (cid) REFERENCES category (cid)
);
-- 向商品类别表中添加数据
insert into category (cid,cname) values(1,家用电器/电脑), (2,男装/女装/童装/内衣),(3,女鞋/箱包/珠宝/钟表),(4,食品/酒类/生鲜/特产),(5,美妆/个护清洁/宠物);-- 向商品表中添加数据
INSERT INTO product VALUES(null, 联想Lenovo威6 14英寸商务轻薄笔记本电脑(i7-8550U 8G 256G PCIe SSD FHD MX150 Win10 两年上门)鲨鱼灰,5999,1);
INSERT INTO product VALUES(null,联想(Lenovo)拯救者Y7000 15.6英寸游戏笔记本电脑(英特尔八代酷睿i5-8300H 8G 512G GTX1050 黑),5999,1);
INSERT INTO product VALUES(null,三洋SANYO9公斤智能变频滚筒洗衣机 臭氧除菌 空气洗 WiFi智能 中途添衣 Magic9魔力净,2499,1);
INSERT INTO product VALUES(null,海尔Haier) 滚筒洗衣机全自动 10公斤变频 99%防霉抗菌窗垫EG10014B39GU1,2499,1);
INSERT INTO product VALUES(null,雷神(ThundeRobot)911SE炫彩版 15.6英寸游戏笔记本电脑I7-8750H 8G 128SSD1T GTX1050Ti Win10 RGB IPS,6599,1);
INSERT INTO product VALUES(null,七匹狼休闲裤男2018秋装新款纯棉男士直筒商务休闲长裤子男装 2775 黑色 32/80A,299,2);
INSERT INTO product VALUES(null,真维斯JEANSWEST t恤男 纯棉圆领男士净色修身青年打底衫长袖体恤上衣 浅花灰 M,35,2);
INSERT INTO product VALUES(null,PLAYBOY/花花公子休闲裤男弹力修身 秋季适中款商务男士直筒休闲长裤 黑色适中款 31(2.4尺),128,2);
INSERT INTO product VALUES(null,劲霸男装K-Boxing 短版茄克男士2018新款休闲舒适棒球领拼接青年夹克|FKDY3114 黑色 185,362,2);
INSERT INTO product VALUES(null,Chanel 香奈儿 女包 2018全球购 新款蓝色鳄鱼皮小牛皮单肩斜挎包A 蓝色,306830,3);
INSERT INTO product VALUES(null,皮尔卡丹(pierre cardin)钱包真皮新款横竖款男士短款头层牛皮钱夹欧美商务潮礼盒 黑色横款款式一,269,3);
INSERT INTO product VALUES(null,PRADA 普拉达 女士黑色皮质单肩斜挎包 1BD094 PEO V SCH F0OK0,28512,3);
INSERT INTO product VALUES(null,好想你 干果零食 新疆特产 阿克苏灰枣 免洗红枣子 玛瑙红500g/袋,21.9,4);
INSERT INTO product VALUES(null,三只松鼠坚果大礼包1588g每日坚果礼盒干果组合送礼火红A网红零食坚果礼盒8袋装,128,4);
INSERT INTO product VALUES(null,三只松鼠坚果炒货零食特产每日坚果开心果100g/袋,32.8,4);
INSERT INTO product VALUES(null,洽洽坚果炒货孕妇坚果零食恰恰送礼每日坚果礼盒26g*30包 780g/盒新老包装随机发货),149,4);
INSERT INTO product VALUES(null,今之逸品【拍3免1】今之逸品双眼皮贴双面胶美目舒适隐形立显大眼男女通用 中号160贴,9.9,5);
INSERT INTO product VALUES(null,自然共和国 原自然乐园 芦荟舒缓保湿凝胶300ml*2约600g进口补水保湿舒缓晒后修复面膜,72,5);
2.2.2 连接查询#
2.2.2.1 笛卡尔积查询#
笛卡尔积在SQL中的实现方式是交叉连接(Cross Join)。笛卡尔积是关系代数里的一个概念表示两个表中的每一行数据任意组合得到的结果集记录数是两个表记录数的乘积这样的结果集也称笛卡尔积。
假设集合A{a, b}集合B{0, 1, 2}则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
笛卡尔积是很可怕的比如 AB两表 个10000条数据得到的笛卡尔积就有 10000 0000条。
示例
-- 商品类别 5条数据
select count(*) from category;
-- 商品 18条数据
select count(*) from product;
-- 交叉查询 90条数据
select count(*) from product,category;
-- 详细数据
select * from product,category;
2.2.2.2 内连接查询#
内链接是查询两个表的交集的部分。 显示内连接#
标准语法
SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件;
示例
-- 查看所有的商品信息并且展示其所属分类信息
SELECT * FROM product p INNER JOIN category c ON p.cid c.cid;-- 加条件查询名称中包含 坚果 信息的商品及所属分类信息
SELECT * FROM product p INNER JOIN category c ON p.cid c.cid WHERE p.pname like %坚果%;
隐式内连接#
标准语法
SELECT 列名 FROM 表名1,表名2 where 关联条件;
示例
-- 查看所有的商品信息并且展示其所属分类信息
select * from product p, category c where p.cid c.cid;-- 加条件
select * from product p, category c where p.cid c.cid and p.pname like %坚果%;
推荐使用显示内连接。
内链接的问题
向商品类别表插入一条数据
INSERT into category values (6,手机/运营商/数码);
但是当我关联查询的时候
select DISTINCT c.* from category c inner join product p on c.cid p.cid;
只能查询出5个类别
商品表只有这5个类别的数据。如何解决 用外连接。
2.2.2.3 外连接查询#
外连接可以把关联查询的两张表的一张表作为主表另外一张作为从表而外链接始终保证主表的数据完整。
外连接分三类左外连接left outer join、右外连接right outer join和全外连接full outer join可以省略outer。
左外连接left join# 左外连接查询从左表table1返回所有的行即使右表table2中没有匹配。如果右表中没有匹配则结果为 NULL。
语法
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name table2.column_name;
-- 注其中的OUTER关键字可以省略
案例演示
-- 查询所有的商品分类信息并将该分类下所有的商品展示
SELECT * FROM category c LEFT OUTER JOIN product p on c.cid p.cid;
-- 可以看到即使商品表中没有该分类的商品也会展示分类表中的信息
数据库在通过连接两张或多张表来返回记录时都会生成一张中间的临时表然后再将这张临时表返回给用户。
右外连接right join# RIGHT JOIN 关键字从右表table2返回所有的行即使左表table1中没有匹配。如果左表中没有匹配则结果为 NULL。
语法
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_nametable2.column_name;
案例演示
-- 先给商品表插入一条数据
INSERT INTO product VALUES(null,樱桃键盘,72,7);
-- 用右连接实现上面的查询结果
select * from product p right join category c on c.cid p.cid;
2.2.2.4 多表查询综合案例#
1 查询价格在一万以内名字中包含 想 的商品分类是什么
隐式外连接
-- 查询价格在一万以内名字中包含 想 的商品分类是什么
select distinct c.cname from product p,category c where p.cidc.cid and p.price 10000 and p.pname like %想%
显式外连接
-- 查询价格在一万以内名字中包含 想 的商品分类是什么
select distinct c.cname from product p left join category c on p.cidc.cid where p.price10000 and p.pname like %想%;
2查询所有分类商品的个数
左外连接 :
-- 先通过连接条件生成临时结果然后再通过group by 汇总出最终结果
select c.cname,count(p.cid) num from category c
left join product p on c.cid p.cid
group by c.cname;
2.2.3 子查询#
有时一次查询查不出结果需要将一次查询完的结果作为条件再进行查询。
子查询是指在查询语句中嵌套另一个查询子查询可以支持多层嵌套。
对于一个普通的查询语句子查询可以出现在两个位置。
1出现在 from 语句后当成数据表#
语法
SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];
示例
-- 通过商品表统计商品共有多少分类
select count(*) from (select DISTINCT cid from product) t1;
select count(*) from (select cid from product group by cid) t1;
2出现在where 条件后作为过滤条件的值#
子查询的结果是单行单列的
语法
SELECT 列名 FROM 表名 WHERE 列名(SELECT 列名 FROM 表名 [WHERE 条件]);
示例
-- 类别为 家用电器/电脑的商品名称和价格
select pname, price from product
where cid(select cid from category where cname家用电器/电脑);
子查询的结果是多行单列的
语法 SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]);
示例
-- 查询商品价格大于5000的类别
select * from category where cid in (select cid from product where price5000);-- 获取所有商品中,平均价格大于1000的分类的全部商品
select * from product where cid in (select p.cid FROM product p group by p.cid HAVING avg(price) 1000);
使用子查询要注意如下要点
1子查询要用括号括起来。
2把子查询当成数据表时出现在from 之后可以为该子查询起别名尤其是作为前缀来限定数据列时必须给子查询起别名。
3把子查询当成过滤条件时将子查询放在比较运算符的右边这样可以增强查询的可读性。
2.2.4 综合练习#
创建表
--创建部门表
create table dept (deptid int primary key ,deptname varchar(20),address varchar(20));
insert into dept values(1,研发部,北京),(2,人事部,上海),(3,销售部,深圳),(4,公关部,东莞);
--创建员工表
create table emp(empid int primary key ,empname varchar(20),salary DECIMAL ,rizhidate date,mgr int,deptid int);
insert into emp values(1001,tom,18000,2013-10-11,1005,1) ;
insert into emp values(1002,jerry,13000,2021-11-11,1005,1);
insert into emp values(1003,jack,10000,2020-09-11,1001,1) ;
insert into emp values(1004,rose,5000,2020-10-11,1001,2) ;
insert into emp values(1005,bob,20000,2018-08-11,null,2);需求--查出部门的员工数和部门的名称
select count(d.deptname),d.deptname from dept d join emp e on d.deptide.deptid group by deptname
-- 公司最高的工资是多少以及这个人是谁
select empname,salary from emp where salary (select max(salary) from emp)
-- 每个组最高的工资以及是谁
select emp.empname,emp.salary from (select max(salary) max,deptid from emp group by deptid) t join emp on t.deptidemp.deptid and t.maxemp.salary
--平均工资大于公司总平均工资的部门是哪个部门
select t1.deptid,t1.avg from (select avg(salary) avg,deptid from emp group by deptid) t1 join (select avg(salary) avg from emp) t2 on t1.avgt2.avg
-- 求出哪个员工的工资大于本部门的平均工资
select t2.empname,t2.salary,t2.deptid,t1.avg from (select avg(salary) avg,deptid from emp group by deptid) t1 join emp t2 on t2.salary t1.avg and t1.deptidt2.deptid3 视图
什么是视图
视图是一张虚拟表
表示一张表的部分数据或多张表的综合数据
其结构和数据是建立在对表的查询基础上
视图中不存放数据
数据存放在视图所引用的原始表中
一个原始表根据不同用户的不同需求可以创建不同的视图
为什么使用视图
重用SQL语句简化复杂的SQL操作。在编写查询后可以方便地重用它而不必知道其基本查询细节。保护数据。可以授予用户访问表的特定部分的权限而不是整个表的访问权限。
规定及限制
因为视图不包含数据所以每次使用视图时都必须处理查询执行时需要的所有检索与表一样视图必须唯一命名不能给视图取与别的视图或表相同的名字 名称_view对于可以创建的视图数目没有限制
视图语法
-- 创建视图
create view view_name as
select 语句;-- 查询视图数据
select 字段1,字段2, ... from view_name-- 删除视图
drop view view_name;
示例
-- 查询 类别为 家用电器/电脑 且 商品价格5000的商品信息
select pname, price from product
where cid(select cid from category where cname家用电器/电脑) and price 5000;-- 查询 类别为 家用电器/电脑 且 商品价格5000的商品信息
select pname, price from product
where cid(select cid from category where cname家用电器/电脑) and price 5000;
发现上面的两个查询有公共的部分那我们就可以把公共部分创建视图基于这个视图在来查询相应价格的数据
-- 创建视图 查询 类别为 家用电器/电脑 的 商品信息
create view cp1_view as
select pname, price from product
where cid(select cid from category where cname家用电器/电脑);-- 在此基础上用视图直接筛选价格5000的商品
select * from cp1_view where price 5000;-- 在此基础上用视图直接筛选价格5000的商品
select * from cp1_view where price 5000;
删除视图
drop view cp1_view;
4 索引
4.1 索引概述#
为什么要学索引
如果新华字典没有汉语拼音、偏旁部首目录你如何查找某个汉字
一页一页翻找效率低
如果带着汉语拼音、偏旁部首目录你如何查找
先看汉语拼音目录找到汉字对应的页数直接找对应页码即可。利用索引检索效率高
索引是什么
Mysql官方对索引的定义是索引Index是帮助Mysql高效获取数据的数据结构。
提取句子主干就是索引是数据结构。
索引的目的
索引的目的在于提高查询或检索效率。拿空间换时间
索引的优势
提高数据检索效率降低数据库IO成本。
降低数据排序的成本降低CPU的消耗。
索引的劣势
索引也是一张表保存了主键和索引字段并指向实体表的记录所以也需要占用磁盘和内存空间
虽然索引大大提高了查询速度同时却会降低更新表的速度如对表进行INSERT、UPDATE和DELETE。因为更新表时MySQL不仅要保存数据还要保存一下索引文件每次更新添加了索引列的字段 都会调整因为更新所带来的键值变化后的索引信息 。
4.2 MySQL的索引存储结构#
MySQL默认使用的是B树存储结构如下图所示 其中
非叶子节点只存储键值。
只有叶子节点才会存储数据数据存储的是非主键的数据。
叶子节点之间使用双向指针连接最底层的叶子节点形成了一个双向有序链表。
优势
范围查询特别方便只需要在叶子节点利用有序链表查找范围即可。
4.3 MySQL索引分类#
普通索引 最基本的索引它没有任何限制。唯一索引索引列的值必须唯一但允许有空值。如果是组合索引则列值组合必须唯一。主键索引一种特殊的唯一索引不允许有空值。一般在建表时同时创建主键索引。组合索引顾名思义就是将单列索引进行组合。外键索引只有InnoDB引擎支持外键索引用来保证数据的一致性、完整性和实现级联操作。全文索引快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。
4.4 MySQL 索引使用#
索引常见语法
-- 创建索引
create table 表名(字段名 字段类型... index 索引名 (类名))
或者
CREATE [UNIQUE|FULLTEXT] INDEX 索引名称
[USING 索引类型] -- 默认是BTREE
ON 表名(列名...);
或者
ALTER TABLE 表名 ADD INDEX 索引名称(列名);-- 查看索引
SHOW INDEX FROM 表名;-- 删除索引
DROP INDEX 索引名称 ON 表名;
案例演示
--创建普通索引
create table index1(id int,name varchar(20) , gender varchar(20),index (id)) ;
insert into index1 values(1,zs,female)
--执行计划
explain select * from index1 where namezs;
explain select id from index1 where id 10;
--创建唯一索引
create table index2(id int unique,name varchar(20) ,gender varchar(20), unique index un_index (id)) ;
insert into index2 values(1,zs,female)
--执行计划
explain select * from index2 where namezs;
explain select id from index2 where id 1;
--创建联合索引
create table index3(id int unique,name varchar(20) , gender varchar(20), index n_g_index (name,gender)) ;
insert into index3 values(1,zs,female)
--执行计划
explain select * from index3 where namezs;
explain select id from index3 where gender female;
explain select id from index3 where namezsand gender female;2查看某个表的索引
show index from product; 部分结果说明
Table创建索引的表
Non_unique索引是否非唯一
Key_name索引的名称
Column_name定义索引的列字段
Seq_in_index该列在索引中的位置
Null该列是否能为空值
Index_type索引类型
3删除索引
DROP INDEX index_product_price on product;
show create table
4.5 MySql创建索引的使用技巧#
创建索引的指导原则
(一) 按照下列标准选择建立索引的列
频繁搜索的列经常用作查询选择的列经常排序、分组的列经常用作连接的列主键/外键
(二) 使用索引时注意事项
查询时减少使用*返回全部列不要返回不需要的列索引应该尽量小在字节数小的列上建立索引WHERE子句中有多个条件表达式时包含索引列的表达式应置于其他条件表达式之前避免在ORDER BY子句中使用表达式
4.6 索引失效的场景#
1当在查询条件中出现 、NOT、in、not exists 时查询时更倾向于全表扫描。
2在查询条件中有or 也不走索引尽量不使用。
3查询条件使用LIKE通配符
SQL语句中使用后置通配符会走索引例如查询姓张的学生select from student where name LIKE hai%而前置通配符(select from student where name LIKE %niu)会导致索引失效而进行全表扫描。
4在索引列上使用函数 或 计算。
5索引列数据类型不匹配。比如字段类型是string 但条件值不是string。
案例演示
--索引失效场景
explain select * from index1 where id 1
explain select * from index1 where id in (1,2,3,4,5);
explain select * from index1 where id 1 or id 2 or id 3
explain select * from index1 where id like %1234%
explain select * from index1 where id1 2
explain select * from index1 where id 123
5 关键字
5.1UNION 操作符#
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
语法
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
注意
UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时每个 SELECT 语句中的列的顺序必须相同。
UNION 会自动去除多个结果中的重复结果如果允许重复的值使用 UNION ALL。
示例
-- 查询分类为 家用电器/电脑 的 价格大于3000的 商品信息
SELECT p.pid, p.pname, p.price, c.cid, c.cname
FROM product p join category c
on p.cid c.cid
WHERE c.cname 家用电器/电脑 and p.price 200-- 查询分类为 男装/女装/童装/内衣 的 价格大于200的 商品信息
SELECT p.pid, p.pname, p.price, c.cid, c.cnameFROM product p join category con p.cid c.cidWHERE c.cname 男装/女装/童装/内衣 and p.price 200-- 将上面两条语句的查询结果合并
SELECT p.pid, p.pname, p.price, c.cid, c.cname
FROM product p join category c
ON p.cid c.cid
WHERE c.cname 家用电器/电脑 and p.price 200
UNION
SELECT p.pid, p.pname, p.price, c.cid, c.cname
FROM product p join category c
on p.cid c.cid
WHERE c.cname 男装/女装/童装/内衣 and p.price 200-- 如果有重复数据UNION ALL会保留重复
5.2 distinct去重#
--查询商品表汇总有多少种不同的价格
select distinct price from product
5.3 not与exists#
exists判断在数据集中是否存在
--在商品表中查找在category表中存在分类的product数据
select * from product where cid not in (select cid from category)
select * from product p where not exists (select * from category c where p.cid c.cid)
6 常用函数
6.1 字符串相关函数# CONCAT(s1,s2...sn) 链接字符串 select concat(hello,world); CONCAT_WS(x, s1,s2...sn)间隔符链接 select concat_ws(*,a,b,c) LOWER(s)转换小写 select LOWER(HELLO) UPPER转换为大写 select UPPER(hello) trim去除字符串的左右部分的空格 select trim( hello ) SUBSTRING(s, start, length) /SUBSTR(s,start,length) 截取字符串 select substring(1234567,2,4) REPLACE(s,s1,s2)替换字符串字段 select replace(12121abc123122123,abc,xxxxx) REVERSE翻转 select reverse(123)
6.2 数字类型函数# max min avg sum rand()随机数 select rand() mod()余数 select mod(12,9) floor()向下取整 select floor(3.14) ceil向上取整 select ceil(3.14) 数字格式化保留指定位数的小数 select format(2.2252222,2)
6.3 日期函数# CURRENT_DATE() 当前日期 CURRENT_TIME() 当前时间 CURRENT_TIMESTAMP()当前时间 相当于上面两个的总体 datediff(dat1,dat2)时间间隔天数 select datediff(2022-10-11,2022-10-01) DATE_ADD 计算起始日期 d 加上一个时间段后的日期 SELECT DATE_ADD(2017-06-15, INTERVAL 10 DAY); - 2017-06-25
SELECT DATE_ADD(2017-06-15 09:34:21, INTERVAL 15 MINUTE); - 2017-06-15 09:49:21
SELECT DATE_ADD(2017-06-15 09:34:21, INTERVAL -3 HOUR); -2017-06-15 06:34:21 SELECT DATE_ADD(2017-06-15 09:34:21, INTERVAL -3 HOUR); -2017-04-15 DATE_FORMAT(d,f) 格式化日期 select date_format(CURRENT_TIMESTAMP(),%Y-%m-%d )
6.4 高级函数# case when 语法 CASE
WHEN 条件值1 THEN result1
WHEN 条件值2 THEN result2
...
WHEN 条件值n THEN resultN
ELSE result END as 字段名-- 给入职3年的员工薪资10000
select ename, workyears,
case when workyears 3 then salary10000
else salary end as salary
from (select ename,salary,floor(datediff(current_date(),hiredate)/365) as workyears from t_emp) t if 语法IF(expr,v1,v2) select ename, workyears, if(workyears3,salary10000,salary) as salary
from (select ename,salary,floor(datediff(current_date(),hiredate)/365) as workyears from t_emp)t
7 行列转换
7.1 行转列#
即将原本同一列下多行的不同内容作为多个字段输出对应内容。
准备数据
-- 创建成绩表
CREATE TABLE row_to_column_score (id INT(11) NOT NULL auto_increment,userid VARCHAR(20) NOT NULL COMMENT 用户id,subject VARCHAR(20) COMMENT 科目,score DOUBLE COMMENT 成绩,PRIMARY KEY(id)
)ENGINE INNODB DEFAULT CHARSET utf8;-- 添加数据
INSERT INTO row_to_column_score(userid,subject,score) VALUES (001,语文,90);
INSERT INTO row_to_column_score(userid,subject,score) VALUES (001,数学,92);
INSERT INTO row_to_column_score(userid,subject,score) VALUES (001,英语,80);
INSERT INTO row_to_column_score(userid,subject,score) VALUES (002,语文,88);
INSERT INTO row_to_column_score(userid,subject,score) VALUES (002,数学,90);
INSERT INTO row_to_column_score(userid,subject,score) VALUES (002,英语,75.5);
INSERT INTO row_to_column_score(userid,subject,score) VALUES (003,语文,70);
INSERT INTO row_to_column_score(userid,subject,score) VALUES (003,数学,85);
INSERT INTO row_to_column_score(userid,subject,score) VALUES (003,英语,90);
INSERT INTO row_to_column_score(userid,subject,score) VALUES (003,政治,82);-- 查看全部
select * from row_to_column_score;
----------------------------
| id | userid | subject | score |
----------------------------
| 1 | 001 | 语文 | 90 |
| 2 | 001 | 数学 | 92 |
| 3 | 001 | 英语 | 80 |
| 4 | 002 | 语文 | 88 |
| 5 | 002 | 数学 | 90 |
| 6 | 002 | 英语 | 75.5 |
| 7 | 003 | 语文 | 70 |
| 8 | 003 | 数学 | 85 |
| 9 | 003 | 英语 | 90 |
| 10 | 003 | 政治 | 82 |
----------------------------
10 rows in set (0.00 sec)
先来查看转换后的结果
----------------------------------------
| userid | 语文 | 数学 | 英语 | 政治 |
----------------------------------------
| 001 | 90 | 92 | 80 | 0 |
| 002 | 88 | 90 | 75.5 | 0 |
| 003 | 70 | 85 | 90 | 82 |
----------------------------------------
可以看出这里行转列是将原来的subject字段的多行内容选出来作为结果集中的不同列并根据userid进行分组显示对应的score。
1、使用case...when....then 进行行转列
select userid,
sum(case when subject语文 then score else 0 end) as 语文,
sum(case when subject数学 then score else 0 end) as 数学,
sum(case when subject英语 then score else 0 end) as 英语,
sum(case when subject政治 then score else 0 end) as 政治
from row_to_column_score
group by userid;
2、使用IF() 进行行转列
select userid,
sum(if(subject 语文,score,0)) as 语文,
sum(if(subject 数学,score,0)) as 数学,
sum(if(subject 英语,score,0)) as 英语,
sum(if(subject 政治,score,0)) as 政治
from row_to_column_score
group by userid;
注意
SUM() 是为了能够使用GROUP BY根据userid进行分组因为每一个userid对应的subject语文的记录只有一条所以SUM() 的值就等于对应那一条记录的score的值。
7.2 列转行#
准备数据
-- 创建成绩表
CREATE TABLE column_to_row_score (id INT(11) NOT NULL auto_increment,userid VARCHAR(20) NOT NULL COMMENT 用户id,cn_score DOUBLE COMMENT 语文成绩,math_score DOUBLE COMMENT 数学成绩,en_score DOUBLE COMMENT 英语成绩,po_score DOUBLE COMMENT 政治成绩,PRIMARY KEY(id)
)ENGINE INNODB DEFAULT CHARSET utf8;-- 插入数据
INSERT INTO column_to_row_score(userid,cn_score,math_score,en_score,po_score) VALUES (001,90,92,80,0);
INSERT INTO column_to_row_score(userid,cn_score,math_score,en_score,po_score) VALUES (002,88,90,75.5,0);
INSERT INTO column_to_row_score(userid,cn_score,math_score,en_score,po_score) VALUES (003,70,85,90,82);-- 查看全部
select * from column_to_row_score;
------------------------------------------------------
| id | userid | cn_score | math_score | en_score | po_score |
------------------------------------------------------
| 1 | 001 | 90 | 92 | 80 | 0 |
| 2 | 002 | 88 | 90 | 75.5 | 0 |
| 3 | 003 | 70 | 85 | 90 | 82 |
------------------------------------------------------
3 rows in set (0.00 sec)
先查看转换后的结果
-----------------------
| userid | course | score |
-----------------------
| 001 | 数学 | 92 |
| 001 | 语文 | 90 |
| 001 | 政治 | 0 |
| 001 | 英语 | 80 |
| 002 | 政治 | 0 |
| 002 | 英语 | 75.5 |
| 002 | 数学 | 90 |
| 002 | 语文 | 88 |
| 003 | 政治 | 82 |
| 003 | 英语 | 90 |
| 003 | 数学 | 85 |
| 003 | 语文 | 70 |
-----------------------
看以看出转换是将userid的每个科目分数分散成一条记录显示出来。
实现
select userid, 语文 as course, cn_score as score from column_to_row_score
union all
select userid, 数学 as course, math_score as score from column_to_row_score
union all
select userid, 英语 as course, en_score as score from column_to_row_score
union all
select userid, 政治 as course, po_score as score from column_to_row_score
order by userid;
这里其实是将每个userid对应的多个科目的成绩查出来通过UNION将结果合并起来达到上面的效果。
8 分组后的topN
准备数据
-- 创建学生成绩表
CREATE TABLE stu_score (id int(11) NOT NULL AUTO_INCREMENT, -- idname varchar(20) DEFAULT NULL, -- 姓名course varchar(20) DEFAULT NULL, -- 课程名称score int(11) DEFAULT NULL, -- 成绩PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT10 DEFAULT CHARSETutf8;-- 插入数据
insert into stu_score(name,course,score) values
(张三,语文,80),
(李四,语文,90),
(王五,语文,93),
(张三,数学,77),
(李四,数学,68),
(王五,数学,99),
(张三,英语,90),
(李四,英语,50),
(王五,英语,89);
需求1查询每门课程分数最高的学生以及成绩
-- 使用自关联查询
select a.*
from stu_score a
join (select course, max(score) max_score from stu_score group by course) b
on a.course b.course and a.score max_score;
/*
分析上面的查询是首先按照课程分组将每门课程的最高分先查询出来虚拟化成一张表然后使用原成绩表与该表关联条件是两张表的课程和成绩相同即查询出每门课程最高成绩的学生信息
*/-- 使用子查询
select a.* from stu_score a where score (select max(score) from stu_score b where a.course b.course);
/*
分析课程相同的条件下查询最高的成绩再查询成绩为最高成绩的学生信息
*/---------------------------
| id | name | course | score |
---------------------------
| 12 | 王五 | 语文 | 93 |
| 15 | 王五 | 数学 | 99 |
| 16 | 张三 | 英语 | 90 |
---------------------------
需求2查询每门课程前两名的学生以及成绩
-- 使用自关联查询
select a.*
from stu_score a
left join stu_score b
on a.course b.course and a.score b.score
group by a.id
having count(b.id) 2
order by a.course, a.score desc;/*
解释左表a 右表b首先查询所有课程相同但是a的成绩小于b的成绩的数据然后按照a表中的数据分组分组后统计每条数据即每条学生成绩信息在b表中对应的数据 2 (如果有一个比当前学生成绩高的就出现1条所以要求前2名比当前学生成绩高的应该小于2这样就取到了top2)最后按照课程和成绩排序使结果好看
*/-- 使用子查询
select a.*
from stu_score a
where (select count(*) from stu_score b where a.course b.course and a.score b.score) 2
order by a.course, a.score desc;
/*
分析课程相同的条件下查询a表成绩比b表小的判断条件 这样的数据少于2条那么a表中该记录就是top2
*/---------------------------
| id | name | course | score |
---------------------------
| 15 | 王五 | 数学 | 99 |
| 13 | 张三 | 数学 | 77 |
| 16 | 张三 | 英语 | 90 |
| 18 | 王五 | 英语 | 89 |
| 12 | 王五 | 语文 | 93 |
| 11 | 李四 | 语文 | 90 |
---------------------------
