mysql数据库学习（一）
结构表
1 CREATE TABLE `dm_user` ( 2 `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', 3 `phone` varchar(255) DEFAULT NULL COMMENT '手机号', 4 `password` varchar(255) DEFAULT NULL COMMENT '密码', 5 `wxUserId` varchar(100) DEFAULT NULL, 6 `realName` varchar(255) DEFAULT NULL COMMENT '真实姓名', 7 `nickName` varchar(255) DEFAULT NULL COMMENT '昵称', 8 `sex` int(1) DEFAULT NULL COMMENT '性别(0:男,1:女)', 9 `hobby` varchar(255) DEFAULT NULL COMMENT '兴趣爱好', 10 `idCard` varchar(255) DEFAULT NULL COMMENT '身份证号', 11 `birthday` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '生日', 12 `createdTime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', 13 `updatedTime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', 14 PRIMARY KEY (`id`), 15 KEY `idx_phone_realName_sex` (`phone`,`realName`,`sex`) 16 ) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=utf8 COMMENT='用户表' 17 18 #=============================================== 19 CREATE TABLE `city` ( 20 `city_id` int(11) NOT NULL AUTO_INCREMENT, 21 `city_name` varchar(50) NOT NULL, 22 `country_id` int(11) NOT NULL, 23 PRIMARY KEY (`city_id`), 24 UNIQUE KEY `idx_city_name_unique` (`city_name`), 25 KEY `idx_city_name` (`city_name`) 26 ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 27 #=============================================== 28 29 CREATE TABLE `city_innodb` ( 30 `city_id` int(11) NOT NULL AUTO_INCREMENT, 31 `city_name` varchar(50) NOT NULL, 32 `country_id` int(11) NOT NULL, 33 PRIMARY KEY (`city_id`), 34 KEY `fk_city_country` (`country_id`), 35 CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country_innodb` (`country_id`) ON UPDATE CASCADE 36 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 37 #=============================================== 38 39 CREATE TABLE `city_logs` ( 40 `id` int(11) NOT NULL AUTO_INCREMENT, 41 `operation` varchar(20) NOT NULL COMMENT '操作类型：insert/update/delete', 42 `operate_time` datetime NOT NULL COMMENT '操作时间', 43 `operate_id` int(11) NOT NULL COMMENT '操作表ID', 44 `operate_params` varchar(500) DEFAULT NULL COMMENT '操作参数', 45 PRIMARY KEY (`id`) 46 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 47 #=============================================== 48 CREATE TABLE `country` ( 49 `country_id` int(11) NOT NULL AUTO_INCREMENT, 50 `country_name` varchar(100) NOT NULL, 51 PRIMARY KEY (`country_id`) 52 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 53 54 #=============================================== 55 56 CREATE TABLE `country_innodb` ( 57 `country_id` int(11) NOT NULL AUTO_INCREMENT, 58 `country_name` varchar(100) NOT NULL, 59 PRIMARY KEY (`country_id`) 60 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 61 #=============================================== 62 63 CREATE TABLE `goods_innodb` ( 64 `id` int(11) NOT NULL AUTO_INCREMENT, 65 `name` varchar(20) NOT NULL, 66 PRIMARY KEY (`id`) 67 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
一、索引
索引（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);
