SQL语言-表联结&索引&视图&存储&触发器&事务

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;