SQL的表联结
内部联结:
#内部联结
#目前为止我们使用的联结叫做等值联结,必须联结的表都有匹配的数据才进行展示
SELECT * FROM student s, teacher t WHERE s.`class_id`=t.`cls_id`;
#使用inner join关键字也能实现内部联结
SELECT * FROM student s INNER JOIN teacher t ON s.`class_id`=t.`cls_id`;
外部联结:
#外部联结
#因为我们有时候需要那些没有被关联到的行的信息,所以需要使用外部联结
#左外联结
#以左表为基准,按照过滤条件查找右边表的记录,如果匹配到就组成一行
#如果匹配不到,就只显示左表的字段,右表中不存在的字段用NULL来表示
SELECT * FROM student s LEFT JOIN teacher t ON s.`class_id`=t.`cls_id`;
SELECT * FROM teacher t LEFT JOIN student s ON s.`class_id`=t.`cls_id`;
#右外联结
#以右表为基准,按照过滤条件查找左边表的记录,如果匹配到就组成一行
#如果匹配不到,就只显示右表的字段,左表中不存在的字段用NULL来表示
SELECT * FROM student s RIGHT JOIN teacher t ON s.`class_id`=t.`cls_id`;
#左联结可以替代右联结,只要将基准表放在左边位置即可。
#使用左联结来查询所有的老师及相应的课程信息,不论是否有课。
SELECT * FROM teacher t LEFT JOIN project p ON t.`project_id`=p.`pro_id`;
#使用右联结来查询所有的有课程信息的老师,没有课的老师不需要显示。
SELECT * FROM teacher t RIGHT JOIN project p ON t.`project_id`=p.`pro_id`;
使用联结和联结条件的注意事项:
#使用联结和联结条件的注意事项
#1、注意所使用的联结类型,一般使用内联结,如果有需要没有匹配上的数据,可以使用左联结
#2、保证表和表之间的联结字段的正确性,否则会返回不正确的数据
#3、注意一定要指定联结条件,否则会返回笛卡尔积
#4、在一个联结中可以使用多个表,但是建议先去检查两两之间的联结结果,如果出现问题比较好排查。
MYSQL的索引
#MYSQL的索引
#索引:如果给一个表建立的索引之后,可以提升查询效率,但是会降低增删改的效率
#索引的分类:
#普通索引INDEX:这个是最基本的索引,没有任何限制
#唯一索引UNIQUE INDEX:索引例必须唯一,但允许有空值
#主键索引:主键本身就是索引,一张表只能有一个主键索引,不允许重复,不允许为NULL
#全文索引:它是查找文本中的关键词,主要用于全文检索,一般只针对比较大的数据才使用。
#组合索引:多了个列来组成的索引。
#哪些场景需要使用索引:
#在经常需要搜索的列上,可以加快查询速度
#在作为主键的列上,强制该列的唯一性和组织表中排列结构
#在经常需要根据范围来进行查询的列上来创建索引,比如大于、大于等于等等。
#需要进行排序的列上,使用order by的列上。
#在经常使用where子句中的列创建索引也能提高判断效率
主键:
#主键
#唯一标识的一条记录,不能有重复不能有空值
#保证数据的完整性
#只能有一个
CREATE TABLE Users (
UserID INT AUTO_INCREMENT,
Username VARCHAR(255) NOT NULL,
Email VARCHAR(255) NOT NULL,
PRIMARY KEY (UserID)
);
#在这个例子中,UserID 字段被设置为主键,并且自动增长(AUTO_INCREMENT)。
#如果你已经创建了一个表,并且想要给某个字段或字段组合添加主键约束,你可以使用ALTER TABLE语句。
ALTER TABLE Users ADD PRIMARY KEY (UserID);
外键:
#外键
#表的外键一定是另外一个表的主键,可以有重复值,也可以有空值
#用来和其他表来建立联系用的
#一个表可以有多个外键
#给表添加外键
#语法:alter table 当前表名 add constraint 外键名称 foreign key (需要添加外键的列) references 所依赖的表名 (依赖的主键)
ALTER TABLE student ADD CONSTRAINT stu_tch_clsId FOREIGN KEY (class_id) REFERENCES teacher (cls_id);
索引:
#索引
#该字段没有重复值,但是可以有一个空值
#提高查询排序的速度
#一个表中可以有多个唯一索引
创建-查看-删除 索引:
#创建唯一索引
#create unique index 索引名 on 表名 (表中的列名)
#查看索引
#show index from 表名
#删除索引
#drop index 索引名 on 表名
#创建普通索引
CREATE INDEX idx_stu_name ON student(stu_name);
#创建唯一索引
CREATE UNIQUE INDEX idx_id ON student(id);
#展示索引
SHOW INDEX FROM student;
#删除索引
DROP INDEX idx_stu_name ON student;
DROP INDEX idx_id ON student;
视图
#视图
#视图是一个虚拟的表,使用视图可以实现对SQL语句的重用,能够简化SQL语句。
#视图是一个将写好的查询语句作为一个表来进行使用,使用视图的时候,可以获取到相应的结果
#创建视图
#create view 视图名称 as select 语句
CREATE VIEW v_stuIfo AS SELECT id, stu_name FROM student;
CREATE VIEW v_tch_stu AS
SELECT stu_name, teacher_name FROM student s
INNER JOIN teacher t ON s.`class_id`=t.`cls_id`;
查看视图内容:
#查看视图内容
SELECT teacher_name FROM v_tch_stu;
删除视图:
#删除视图
DROP VIEW v_stuIfo, v_tch_stu;
查询有哪些视图:
#查询有哪些视图
SHOW TABLES;
查看创建视图的语句:
#查看创建视图的语句
SHOW CREATE TABLE v_tch_stu;
SHOW CREATE VIEW v_tch_stu;
存储过程
#存储过程
#存储过程:一组为了特定功能的sql语句的集合,包括了增删改查等各种操作,
#用户可以调用写好的存储过程来完成相应的操作,类似于执行了一个函数
#delimiter是一个命令,可以改变mysql的结束符
#默认的mysql的结束符是分号,但在编写存储过程、触发器等情况下,可能要在语句中使用到分号
#但是并不想直接执行语句,就可以使用delimiter来临时改变结束符
#语句执行完毕之后要使用delimiter将结束符再重新定义为分号即可。
创建存储过程:
#定义结束符
DELIMITER $$
#创建存储过程
CREATE PROCEDURE avgAge()
BEGIN
SELECT AVG(age) AS avgAge FROM student;
END$$
#这里将分号重新指定为结束符,注意这里分号之前要加空格
DELIMITER ;
调用存储过程:
#调用存储过程
CALL avgAge();
查看存储过程的状态:
#查看存储过程的状态
SHOW PROCEDURE STATUS;
删除存储过程:
#删除存储过程
DROP PROCEDURE avgAge;
触发器
#触发器
#触发器是一种特殊类型的存储过程,它不同于存储过程,是主要通过事件触发而被执行的。
#不是主动调用的,而存储过程是需要主动调用来生效的。
#tigger,是指事先为某一张表绑定一段代码,当表中的某些内容发生了改变的时候(增删改)
#系统要自动触发代码执行
创建触发器:
#创建触发器
#delimiter 自定义结束符号
#create trigger 触发器名字 触发时间 触发事件 on 表 for each row
#begin
# --触发器内容主体,每行用分号来结束
#end 自定义结束符
#delimiter ; 将分号重新指定为结束符
触发时间:
#触发时间
#before:表中的数据发生改变之前
#after:表中数据发生改变之后的状态
触发事件:
#触发事件:
#触发器是针对数据改变才会生效的,所以对应的操作一般只有INSERT、DELETE、UPDATE三种
定义触发器:
#有两张表,第一张存的是学生信息,第二张存放的学生的人数。
#实现在学生表中删除一个学生信息,学生人数-1
#定义触发器
DELIMITER $$
CREATE TRIGGER stuCou_by_stuInfo AFTER DELETE ON student FOR EACH ROW
BEGIN
UPDATE stuCount SET countStu=countStu-1;
END$$
DELIMITER ;
#删除学生的时候可以直接改变学生人数字段的值
DELETE FROM student WHERE id=13;
事务
#事务
#事务就是由单独的一个单元或者多个SQL语句组成的,在一个单元中,每一个sql语句都相互依赖,整个单元作为不可分割的整体。
#简单来说就是事务中的内容要么都执行成功,要么都执行失败
#autocommit,实际上数据库是自动开启事务自动提交(每一句sql语句都会执行成功),
查询事务是否开启自动提交:
#查询事务是否开启自动提交
SELECT @@autocommit;
设置事务是否自动提交:
#设置是否自动提交
SET autocommit=0; #禁止自动提交
SET autocommit=1; #开启自动提交
事务提交与回滚:
#事务会使用begin rollback, commit来实现
#begin 代表开始一个事务
#rollback 事务回滚
#commit 事务提交
#开启事务
BEGIN;
INSERT INTO student VALUES(14,"迪丽热巴","女",30,1,"2024-08-08");
COMMIT; #提交事务
BEGIN;
INSERT INTO student VALUES(15,"范迪塞尔","男",35,1,"2024-08-08");
ROLLBACK; #回滚事务
END;