mysql数据库学习（一）

2022-02-03 15:19:57

一、索引

　　　　索引（index）是帮助在mysql高效获取查询数据的一种数据结构（有序）

　　　　MySQL索引数据结构对经典的B+Tree进行了优化，在原B+Tree的基础上，增加了一个指向相邻叶子节点的链表指针，

　　　　就形成了带有链表指针的B+Tree,提高了区间访问的性能

　　　　优势：可以快速查找对应的数据，类似根据目录查找信息，降低数据库的IO成本

　　　　　　　通过索引列对数据库进行排序，降低数据排序的成本，降低CPU的消耗

　　　　劣势：对于数据库的新增insert、删除delete、修改update等操作索引有相应的处理，增加了系统的开销，处理时长变长

　　　　　　　索引也是一张表，该表保存了主键与索引字段，所以也是要占有空间的

二、索引结构

　　索引是在mysql存储引擎中实现的，而不是在服务器层实现

　　mysql提供了4中索引（BTREE）HASH R-tree(空间索引) Full-text(全文索引)

、、

三、索引设计原则

 1 CREATE TABLE `city` (
 2   `city_id` INT(11) NOT NULL AUTO_INCREMENT,
 3   `city_name` VARCHAR(50) NOT NULL,
 4   `country_id` INT(11) NOT NULL,
 5   PRIMARY KEY (`city_id`)
 6 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
 7 
 8 CREATE TABLE `country` (
 9   `country_id` INT(11) NOT NULL AUTO_INCREMENT,
10   `countyr_name` VARCHAR(100) NOT NULL,
11   PRIMARY KEY (`country_id`)
12 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
13 
14 SHOW INDEX FROM city;
15 #CREATE INDEX <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])
16 
17 CREATE  INDEX idx_city_name ON city(city_name);
18 
19 DROP INDEX idx_city_name ON city;
20 
21 #alter
22 #ALTER TABLE 表名 ADD INDEX 索引名 (column_list);
23 #ALTER TABLE 表名 ADD UNIQUE 索引名(column_list); 唯一索引 不允许有值是重复的，但是多个NULL可以#Duplicate entry 'XX市' for key 'idx_city_name_unique'
24 #ALTER TABLE 表名 ADD PRIMARY KEY 索引名(column_list);主键索引
25 #ALTER TABLE 表名 ADD fulltext 索引名(column_list);   全文索引(字符串数据类型)
26 ALTER TABLE city ADD UNIQUE idx_city_name_unique(city_name);

四、视图

　　视图是一种虚拟存在的表，视图就是一条SELECT语句执行后返回的结果集

　　视图相对于普通表的优势：简单、安全、数据独立（修改源表中的字段，可以通过修改视图来解决，增加源表的字段完全没有影响）

　　4.1、创建视图语句

　　4.2、修改视图的语法

　　4.3、关于视图创建和修改的选项说明

　　4.4、删除视图

 1 #创建视图
 2 
 3 SHOW TABLES;
 4 
 5 SHOW CREATE VIEW view_city_country ;
 6 
 7 CREATE VIEW view_city_country AS 
 8 SELECT c.city_id,c.city_name,ct.country_id,ct.country_name  FROM  city c,country ct WHERE c.country_id=ct.country_id;
 9 
10 SELECT *  FROM  view_city_country;
11 
12 DROP VIEW IF EXISTS view_city_country;

五、存储过程和函数

　　5.1、存储过程和函数的概述

　　5.1、创建存储过程

　　5.2、存储过程简单操作

 1 #创建存储过程和函数
 2 #DELIMITER $ 定义分隔符为（$）；mysql默认DELIMITER是分号（;）为分隔符，只要有分号，默认语句完成，需要执行 
 3 
 4 DELIMITER $$
 5 USE `sqllevel`$$
 6 DROP PROCEDURE IF EXISTS `pro_test`$$
 7 
 8 CREATE PROCEDURE pro_test()
 9 BEGIN
10     SELECT 'HELLO' ;
11 END$$
12 
13 DELIMITER ;
14 
15 #调用存储过程call procedure_name();
16 CALL pro_test();
17 
18 #查看存储过程
19 #查询db_name数据库中所有的存储过程
20 #SELECT NAME FROM mysql.proc WHERE db='db_name';
21 SELECT NAME FROM mysql.proc WHERE db='sqllevel';
22 
23 #查询存储过程的状态信息
24 SHOW PROCEDURE STATUS;
25 
26 #查询某个存储过程的定义
27 SHOW CREATE PROCEDURE pro_test;
28 
29 #删除存储过程
30 DROP PROCEDURE pro_test;

　　5.3、变量声明与赋值

 1 #DELIMITER $ 定义分隔符为（$）；mysql默认DELIMITER是分号（;）为分隔符，只要有分号，默认语句完成，需要执行 
 2 #变量声明
 3 
 4 DELIMITER $$
 5 USE `sqllevel`$$
 6 DROP PROCEDURE IF EXISTS `pro_test_1`$$
 7 
 8 CREATE PROCEDURE pro_test_1()
 9 BEGIN
10 DECLARE num INT DEFAULT 5;
11     SELECT num+10;
12 END$$
13 DELIMITER ;
14 
15 CALL pro_test_1();
16 
17 DELIMITER $$
18 CREATE PROCEDURE pro_test_2()
19 BEGIN
20 DECLARE num INT DEFAULT 0;
21 SET num=num+11;
22     SELECT num;
23 END$$
24 DELIMITER ;
25 #通过set方式进行赋值
26 CALL pro_test_2();
27 
28 DELIMITER $$
29 CREATE PROCEDURE pro_test_3()
30 BEGIN
31 DECLARE num INT;
33    SELECT COUNT(*) INTO num FROM city;
34    SELECT CONCAT('总共有记录数:',num);
36 END$$
37 DELIMITER ;
38 #通过select ...into方式进行赋值
39 CALL pro_test_3();
40 
41 SELECT NAME FROM mysql.proc WHERE db='sqllevel';

　　5.4、if语句的判断语法与传递参数

 1 #DELIMITER $ 定义分隔符为（$）；mysql默认DELIMITER是分号（;）为分隔符，只要有分号，默认语句完成，需要执行 
 2 #变量声明
 3 
 4 DELIMITER $$
 5 USE `sqllevel`$$
 6 DROP PROCEDURE IF EXISTS `pro_test4`$$
 7 CREATE PROCEDURE pro_test4(IN height INT)
 8 BEGIN
 9   #declare height int default 175;
10   DECLARE description VARCHAR(50) DEFAULT '';
11   IF height>=180 THEN
12       SET description='身材高挑';
13   ELSEIF height>=170 AND height<180 THEN
14       SET description='身材中等';
15   ELSE  
16       SET description='身材偏矮';
17   END IF;
18 SELECT CONCAT('此人身材:',description);
19 END$$    
20 DELIMITER ;
21 
22 CALL pro_test4(170);
23 #====================================================================================    
24 #====================================================================================    
25 DELIMITER $$
26 USE `sqllevel`$$
27 DROP PROCEDURE IF EXISTS `pro_test5`$$
28 CREATE PROCEDURE pro_test5(IN height INT,OUT description VARCHAR(10))
29 BEGIN
30   IF height>=180 THEN
31       SET description='身材高挑';
32   ELSEIF height>=170 AND height<180 THEN
33       SET description='身材中等';
34   ELSE  
35       SET description='身材偏矮';
36   END IF;
37 END$$    
38 DELIMITER ;
39 
40 CALL pro_test5(170,@description);
41 SELECT @description;

　　5.5、case结构

DELIMITER $$
CREATE PROCEDURE pro_test_case(IN months INT)
BEGIN
  DECLARE quarters VARCHAR(10);
  CASE
     WHEN months>=1 AND months<=3  THEN
    SET quarters='第一季度';
     WHEN months>=4 AND months<=6  THEN
    SET quarters='第二季度';
     WHEN months>=7 AND months<=9  THEN
    SET quarters='第三季度';
     ELSE 
    SET quarters='第四季度';
  END CASE;
  SELECT CONCAT('输入的月份：',months,'是第',quarters) quarters;
END$$    
DELIMITER ;

CALL pro_test_case(2);

　　5.6、while、repeat（推出循环的时候until 后的条件不需要分号）、loop循环语法（需要使用leave语句退出循环）

DELIMITER $$
CREATE PROCEDURE pro_test_loop(num INT)
BEGIN
  DECLARE total INT DEFAULT 0;
  c:LOOP
    SET total=total+num;
    SET num=num-1;
    IF num<=0 THEN
    LEAVE c;
    END IF;
  END LOOP c;
  SELECT total;
END$$    
DELIMITER ;

CALL pro_test_loop(100);

　　5.7、游标/光标

 1 DELIMITER $$
 2 CREATE PROCEDURE pro_test_cusor()
 3 BEGIN
 4   DECLARE s_id INT(11);
 5   DECLARE s_name VARCHAR(50);
 6   DECLARE s_coun INT(11);
 7   DECLARE has_data INT DEFAULT 1;
 8   #下面两项必须紧挨，因为退出是与游标相关联系的
 9   DECLARE mycursor CURSOR FOR SELECT * FROM city;
10   DECLARE EXIT HANDLER FOR NOT FOUND SET has_data=0;  
11   
12   OPEN mycursor;
13   
14   REPEAT
15   FETCH mycursor INTO s_id,s_name,s_coun;
16   SELECT CONCAT(s_id,s_name,s_coun);
17   UNTIL has_data=0
18   END REPEAT;
19   
20   CLOSE mycursor;
21   
22 END$$    
23 DELIMITER ;
24 
25 DROP PROCEDURE pro_test_cusor;
26 CALL pro_test_cusor();

　　5.8、存储函数（有返回值，存储过程没有返回值，但是存储过程有out定义返回,所以存储函数能做的存储过程也是能做的）

DELIMITER $$
CREATE FUNCTION fun_city(countryId INT)
RETURNS INT
BEGIN
   DECLARE cnum INT;
   SELECT COUNT(*) INTO cnum FROM city WHERE country_id=countryId;
   RETURN cnum;
END$$    
DELIMITER ;
DROP FUNCTION fun_city;
SELECT fun_city(1);

　　5.9、触发器

 1 #创建触发器
 2 DELIMITER $$
 3 CREATE TRIGGER city_trigger_insert AFTER INSERT ON city FOR EACH ROW
 4 BEGIN
 5     INSERT INTO city_logs(id,operation,operate_time,operate_id,operate_params) VALUES 
 6     (NULL,'insert',NOW(),new.city_id,CONCAT('插入后(','city_id=',new.city_id,',city_name=',new.city_name,',country_id=',new.country_id,')'));
 7 END$$
 8 DELIMITER ;
 9 DROP TRIGGER city_trigger_insert;
　　#查询触发器
   SHOW TRIGGERS;
10 INSERT INTO `city`  VALUES (NULL,'城都',1);

码农公寓

相关文章