Python项目-Day22-多表连接-索引-函数定义-存储过程

最新推荐文章于 2023-05-10 14:03:27 发布

原创最新推荐文章于 2023-05-10 14:03:27 发布 · 265 阅读

0 ·

CC 4.0 BY-SA版权

python项目专栏收录该内容

36 篇文章

订阅专栏

本文深入探讨了Python项目中SQL的使用技巧，包括多表连接、索引优化、视图管理以及存储过程的设计与应用等内容，是提升数据库操作效率的重要指南。

Python项目-Day22-多表连接-index索引

多表查询

等值连接

SELECT `user`.id,`user`.`name` FROM user ,menu WHERE `user`.id=menu.id

内连接

SELECT * FROM course as c ,course as cc WHERE c.cno=cc.cpno

多级评论是内连接,一张表有评论人和被评论人

外连接

SELECT * FROM user LEFT JOIN menu on `user`.id = menu.id

统计参加考试的学生

SELECT DISTINCT(sno) from sc WHERE grade is not NUL

左连接

SELECT * FROM `user` LEFT JOIN menu ON `user`.id=menu.id

右连接

SELECT * FROM `user` RIGHT JOIN menu on `user`.id=menu.id

内连接

SELECT * FROM `user` INNER JOIN menu ON `user`.id=menu.id

连接格式:– 左连接表1 left join 表2 on 表1.xx=表2.xx

索引(提高查询速度)
- 创建索引的原则：经常作为条件的列上适合创建索引
- 不适合创建索引:经常改动的列；数据比较少
- 使用索引：dbms决定是否使用索引
- 系统会为primary key和unique自动创建索引 primary key
PRIMARY, INDEX, UNIQUE 这3种是一类
PRIMARY 主键。就是唯一且不能为空。
INDEX 索引，普通的
UNIQUE 唯一索引。不允许有重复。
FULLTEXT 是全文索引，用于在一篇文章中，检索文本信息的。

添加索引
```
ALTER TABLE student ADD INDEX index_name(sname);
ALTER TABLE student ADD UNIQUE INDEX index_name(sname);
ALTER TABLE student ADD FULLTEXT INDEX index_name(sname);
```
删除索引
```
DROP INDEX index_name ON student；
```
添加全文索引
```
ALTER TABLE student add FULLTEXT INDEX index_name(sname)；
```
创建视图
```
CREATE VIEW student_info AS
SELECT * FROM student
```
1、对视图内容的更新直接影响基本表。

2、当视图来自多个基本表、不允许删除、增加数据。

查看所有表和视图
```
SHOW TABLES;
```
删除视图
```
DROP VIEW view_stugrade;
```
关系型数据库的三大模式:

mysql函数

创建函数

CREATE FUNCTION fun_getage(str VARCHAR(12))
RETURNS CHAR(12)
BEGIN
    RETURN(SELECT sage from student WHERE sname=str);
END;

调用函数
```
SELECT fun_getage('李彤')
```
方法：
1. 必须有返回值
2. 返回值指定类型 RETURNS
3. 返回值通过 return

存储过程

mysql’存储过程的参数类型:IN,OUT,INOUT
- IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
- OUT 输出参数:该值可在存储过程内部被改变，并可返回
- INOUT 输入输出参数:既能输入一个值又能传出来一个值

定义变量

声明变量(使用DECLARE)
```
DECLARE score INT  DEFAULT(0);
```
DEFAULT可选

赋值变量

1.使用set实现赋值
set cname_res="不及格";
2.使用select XX into xx
SELECT grade into score FROM sc WHERE cno=cid and sno=sid;
将grade赋值给score

条件判断

格式:

if 条件  then...
elseif... then
else...
End if;

if score<60 THEN
    set cname_res="不及格";
ELSEIF score>=60 and score<=80 THEN
    SET cname_res="及格";
ELSE 
    SET cname_res='优秀';
end IF;

循环

mysql有三种循环while 循环、 loop 循环和repeat循环

while do

while i < 11 do 　　　　　　　　　　//   循环体
insert into user_profile (uid) values (i);
set i = i +1;
end while;

loop

lp1 : LOOP　　　　　　　　　　　　　　//  lp1 为循环体名称   LOOP 为关键字
    insert into user_profile (uid) values (i);
    set i = i+1;
    if i > 30 then
leave lp1;　　　　　　　　　　　　　　//  离开循环体
    end if;
end LOOP;

repeat

repeat 
    insert into user_profile_company (uid) values (i+1);
    set i = i + 1;
until i >= 20

end repeat;

注意默认结束符 “;”，在mysql 操作中语句结束要使用 “;”, 不然会出现语法错误。

创建带输入和输出参数的存储过程

DELIMITER $$
					CREATE FUNCTION func_employee_sal (empno INT(11))
						RETURNS DOUBLE(10,2)
					COMMENT'查询某个雇员的工资'
					BEGIN
						RETURN (SELECT sal
							FROM t_employee
							WHERE t_employee.empno=empno);
					END$$
DELIMITER ;

删除存储过程和函数

DROP PROCEDURE proce_name;
DROP FUNCTION func_name;

存储过程与存储函数的区别

1:存储函数和存储过程统称为存储例程(store routine),存储函数的限制比较多,例如不能用临时表,只能用表变量,而存储过程的限制较少,存储过程的实现功能要复杂些,而函数的实现功能针对性比较强
2:返回值不同
存储函数必须有返回值,且仅返回一个结果值
存储过程可以没有返回值,但是能返回结果集(out,inout)
3:调用时的不同
存储函数嵌入在SQL中使用,可以在select 存储函数名(变量值);
存储过程通过call语句调用 call 存储过程名
4:参数的不同
存储函数的参数类型类似于IN参数
存储过程的参数类型有三种
1: in 数据只是从外部传入内部使用(值传递),可以是数值也可以是变量
2: out 只允许过程内部使用(不用外部数据),给外部使用的(引用传递:外部的数据会被先清空才会进入到内部),只能是变量
3: inout 外部可以在内部使用,内部修改的也可以给外部使用,典型的引用传递,只能传递变量