mysql数据库学习(一)

一、索引

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

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

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

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

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

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

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

   mysql数据库学习(一)

二、索引结构

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

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

 

mysql数据库学习(一)

 mysql数据库学习(一)、、

 mysql数据库学习(一)

 

mysql数据库学习(一)

 

 mysql数据库学习(一)

 

 三、索引设计原则

mysql数据库学习(一)

 

 

 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、创建视图语句

  mysql数据库学习(一)

   4.2、修改视图的语法

  mysql数据库学习(一)

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

  mysql数据库学习(一)

  4.4、删除视图

  mysql数据库学习(一)

 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、存储过程和函数的概述

 mysql数据库学习(一)

  5.1、创建存储过程

  mysql数据库学习(一)

  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、变量声明与赋值

mysql数据库学习(一)

 mysql数据库学习(一)

 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语句的判断语法与传递参数

mysql数据库学习(一)

mysql数据库学习(一)

 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结构

mysql数据库学习(一)

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语句退出循环)

mysql数据库学习(一)mysql数据库学习(一)mysql数据库学习(一)

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、游标/光标

mysql数据库学习(一)

 

mysql数据库学习(一)mysql数据库学习(一)

 

 

mysql数据库学习(一)mysql数据库学习(一)

 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定义返回,所以存储函数能做的存储过程也是能做的)

mysql数据库学习(一)

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、触发器

mysql数据库学习(一)

 

mysql数据库学习(一)

 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);

mysql数据库学习(一)

 

mysql数据库学习(一)

上一篇:JAVA-使用Microsoft SQL Server JDBC读写SqlServer数据库


下一篇:SQL语句练习