05.mysql高级教程

最新推荐文章于 2024-05-12 04:10:54 发布

菠萝橡皮刀

最新推荐文章于 2024-05-12 04:10:54 发布

阅读量300

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/m0_58420188/article/details/127159426

版权

1.子查询

(1)子查询更符合人的思维方

（2）子查询可以对查询效率进行优化

例：查询小刘所在公司：

一般写法：

select c.name 公司名
from company c,member m
where c.id=m.comid and m.name='小刘'

（1）select子查询：从member表中查询

select (select name from company where id = comid)
from member
where name ='小刘'

(2)where子查询:从company表中查询

select name 公司名
from company
where id =(select comid from member where name='小刘')

ps.若要查询一个集合，则使用in

select name 公司名
from company
where id in(select comid from member where name ='小刘' or name='小王')

（3）from子查询：适用三表及以上

ps.select子查询和where子查询是把子查询结果当【数据】

from子查询是把查询结果当【表】

注意：from子查询主要用于3张表以上的连接查询

例：先查A B两张表，再用查询结果和C表连接查询

select c.name 公司名
from (select name n,comid id from member where name='小刘 ') t1
      company c
where t1.id = c.id

2.索引

2.1二叉树结构

如果没有索引，它的查询效率会非常的低。建立起索引后，会生成一个二叉树，如果比根结点小，放到左边，如果比根节点大，放到右边，于是提高了索引效率。

PS.索引虽然提高了查询效率，但却降低了表的更新速度，如对表进行增删改查时，MySql不仅要保存数据，还要保存索引文件每次添加了索引列的字段。

2.2 BTREE树结构：层次比二叉树低，查询效率高

1.树的每个节点最多包含m个孩子

2.除根节点与叶子节点外，每个节点至少有【ceil(m/2)】个孩子

3.若根节点不是叶子节点，那它至少有两个孩子

4.所有的叶子节点都在同一层

5.每个非叶子节点都有n个key与n+1个指针组成，其中【ceil(m/2)-1】<=n<=m-1

插入过程如下：以5叉B树为例：

2.3 索引分类

1）单值索引：一个索引只包含单个列，一个表可以有多个单列索引

2）唯一索引：索引列的值必须唯一，但允许有空

3）复合索引：一个索引包含多个列

2.4索引基本语法

city表： country表：

1）创建索引

为city表中的city_name创建索引：

create index idx_city_name on city(city_name);  //要为索引创建名字

2）查看索引

show index from city

3）删除索引

drop index idx_city_name on city

4）ALTER命令

alter table city add unique idx_city_name(city_name)

3.存储过程

可以理解为存储在数据库中一段SQL语句的集合。

函数：是一个由返回值的过程。

过程：是一个没有返回值的函数。

3.1定义存储过程

无参数：

DELIMITER $$

create  procedure demo_01.demo4()    
	-- 存储过程体
	begin		
		declare name varchar(10) default '';	-- declare声明变量
		set name = "jim";			
		select name;   -- 函数体
	end$$

DELIMITER ;

调用存储过程：

CALL demo4();

含参数：

DELIMITER $$

create procedure demo2(in s_sex char(1),out s_count int)  -- in表示输入，out表示输出
    
	
	begin			
		
       select count(*) into s_count from student where sex =s_sex; 
                                                  -- 通过into把值放入s_count
       select s_count;
		 
	end$$

DELIMITER ;

调用过程：

CALL demo2 ('男',@s_count);

3.2查看存储过程

show procedure status

3.3删除存储过程

drop procedure pro_test1$

3.4常用函数：

（1）if else:

DELIMITER $$
CREATE
    PROCEDURE demo.demo3(IN day INT)
	-- 存储过程体
	BEGIN
		IF day = 0 THEN
		SELECT '星期天';
		ELSEIF day = 1 THEN
		SELECT '星期一';
		ELSEIF day = 2 THEN
		SELECT '星期二';
		ELSE
		SELECT '无效日期';
		END IF;
		
	END$$
DELIMITER ;

(2)case:

DELIMITER $$
CREATE 
    PROCEDURE demo4(IN num INT)
	BEGIN
		CASE -- 条件开始
	
		WHEN num<0 THEN 
			SELECT '负数';
		WHEN num>0 THEN 
			SELECT '正数';
		ELSE 
		SELECT '不是正数也不是负数';
	
		END CASE; -- 条件结束
	END$$
DELIMITER;

(3)while():

DELIMITER $$
CREATE 
    PROCEDURE demo6(IN num INT,OUT SUM INT)
	BEGIN
	     SET SUM = 0;
	     WHILE num<10 DO -- 循环开始
	         SET num = num+1;
	         SET SUM = SUM+num;
	         END WHILE; -- 循环结束
	END$$
DELIMITER;

(4)repeat until:

-- 创建过程
DELIMITER $$
CREATE 
    PROCEDURE demo7(IN num INT,OUT SUM INT)
	BEGIN
	     SET SUM = 0;
	     REPEAT-- 循环开始
		SET num = num+1;
		SET SUM = SUM+num ;
		UNTIL num>=10
		END REPEAT; -- 循环结束
	END$$
DELIMITER;

(5)loop:

DELIMITER $$
CREATE 
    PROCEDURE demo8(IN num INT,OUT SUM INT)
	BEGIN
	     SET SUM = 0;
	     demo_sum:LOOP-- 循环开始
		SET num = num+1;
		IF num > 10 THEN
		    LEAVE demo_sum; -- 结束此次循环
		ELSEIF num <= 9 THEN
		    ITERATE demo_sum; -- 跳过此次循环
		END IF;
		
		SET SUM = SUM+num;
		END LOOP demo_sum; -- 循环结束
	END$$
DELIMITER;

(6)插入信息

DELIMITER $$
CREATE 
    PROCEDURE demo9(IN s_student VARCHAR(10),IN s_sex CHAR(1),OUT s_result VARCHAR(20))
	BEGIN
	   -- 声明一个变量 用来决定这个名字是否已经存在
	   DECLARE s_count INT DEFAULT 0;
	   -- 验证这么名字是否已经存在
	   SELECT COUNT(*) INTO s_count FROM student WHERE `name` = s_student;	
	   IF s_count = 0 THEN
	        INSERT INTO student (`name`, sex) VALUES(s_student, s_sex);
		SET s_result = '数据添加成功';
	   ELSE
                SET s_result = '名字已存在，不能添加';
                SELECT s_result;
	   END IF;
	END$$
DELIMITER;