深入MySQL的使用

深入MySQL的使用

一、数据类型

1.数值类型

1.整型

UNSIGNED（无符号） 主要用于整型和浮点类型，使用无符号。即，没有前面面的-（负号）。
存储位数更长。tinyint整型的取值区间为，-128~127。而使用无符号后可存储0-255个长度。

创建时在整型或浮点字段语句后接上：

Unsigned

MySQL数据类型	所占字节	值范围
tinyint	1字节	-128~127
smallint	2字节	-32768~32767
mediumint	3字节	-8388608~8388607
int	4字节	范围-2147483648~2147483647
bigint	8字节	±9.22*10的18次方

2.浮点类型

MySQL数据类型	所占字节	值范围
float(m, d)	4字节	单精度浮点型，m总个数，d小数位
double(m, d)	8字节	双精度浮点型，m总个数，d小数位
decimal(m, d)		decimal是存储为字符串的浮点数

3.字符类型

MySQL数据类型	所占字节	值范围
CHAR	0-255字节	定长字符串
VARCHAR	0-255字节	变长字符串
TINYBLOB	0-255字节	不超过255个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65535字节	二进制形式的长文本数据
TEXT	0-65535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据
VARBINARY(M)	允许长度0-M个字节的定长字节符串	值的长度+1个字节
BINARY(M)	M	允许长度0-M个字节的定长字节符串

2.时间类型

注意：

1.时间类型在web系统中用的比较少，很多时候很多人喜欢使用int来存储时间。插入时插入的是unix时间戳，因为这种方式更方便计算。在前端业务中用date类型的函数，再将unix时间戳转成人们可识别的时间。

2.上面的类型你可以根据实际情况实际进行选择有些人为了在数据库管理中方便查看，也有人使用datetime类型来存储时间。

MySQL数据类型	所占字节	值范围
date	3字节	日期，格式：2014-09-18
time	3字节	时间，格式：08:42:30
datetime	8字节	日期时间，格式：2014-09-18 08:42:30
timestamp	4字节	自动存储记录修改的时间
year	1字节	年份

二、约束

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。

1.主键约束 primary key

主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。
每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。
当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

# 添加主键约束
CREATE TABLE student(
	id INT(32) PRIMARY KEY auto_increment,
	name VARCHAR(32),
	sex VARCHAR(32),
	age INT(3),
	salary FLOAT(8,2),
	course VARCHAR(32)
)

# 查询所有字段
SELECT * FROM student;

# 删除主键约束
ALTER TABLE student DROP PRIMARY KEY;

2.唯一约束

含义：该字段上的数据不能重复，但可以为null

注意：一张表中可以有多个唯一约束

# 唯一约束
# 添加唯一约束
ALTER TABLE student ADD UNIQUE(phone);

# 修改唯一约束
ALTER TABLE student MODIFY name VARCHAR(32) UNIQUE;

# 删除唯一约束
ALTER TABLE student DROP INDEX name;

3.非空约束

含义：该字段上的数据不能为null

注意：一张表中可以有多个非空约束

# 非空约束
# 添加非空约束
ALTER TABLE student MODIFY name VARCHAR(32) NOT NULL;

# 删除非空约束
ALTER TABLE student MODIFY name VARCHAR(32) NULL;

# 默认值
ALTER TABLE student MODIFY `name` VARCHAR(32) DEFAULT "defaultName" NULL;

4.外键约束

含义：外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系

外键的缺点：添加数据还会去外键所在的表中查询，效率很低

# 外键约束
# 添加外键约束
ALTER TABLE student ADD FOREIGN KEY(c_id) REFERENCES class(id);

# 删除外键约束
# 注意：获取外键名--SHOW CREATE TABLE student;
ALTER TABLE student DROP FOREIGN KEY student_ibfk_1;

三、索引

索引用于快速找出在某个列中有一特定值的行。

当然索引也不易过多，索引越多写入，修改的速度越慢。因为，写入修改数据时，也要修改索引。

1.索引的数据结构 — B+Tree

2.索引的优缺点：

优点：查询快

优点：查询快

缺点：更新数据时，索引文件也会发生改变，所以一张表中的索引不宜过多

解决缺点：批量更新时，先删除索引，更新完毕后再添加索引

3.索引的分类

聚簇索引 - 主键索引：叶子节点上就是数据行

非聚簇索引 - 唯一/普通/全文索引：叶子节点上是主键地址

4.索引失效的情况

1.使用模糊查询时

2.索引如果是联合索引，第一个字段失效时，不会找第二个字段

3.查询时，MySQL会有查询算法，判断是走顺序查找更快，还是索引索引更快，如果是顺序查找更快就不走索引这条路

5.经验

1.表中唯一标识的字段（不允许重复并且不允许为null），就可以设置为主键索引

2.在业务经常查询的条件需要加上索引(根据数据选择唯一索引或普通索引)

3.查询条件竟可能使用主键字段 SELECT * FROM student WHERE id=xxx;

6.主键索引

alter table 表 add PRIMARY KEY(字段)

注意：主键索引就是主键约束

# 主键索引
# 添加主键索引
CREATE TABLE student(
	id INT(32) PRIMARY KEY auto_increment,
	name VARCHAR(32),
	sex VARCHAR(32),
	age INT(3),
	salary FLOAT(8,2),
	course VARCHAR(32)
)

# 删除主键索引
ALTER TABLE student DROP PRIMARY KEY;

7.唯一索引

alter table 表 add UNIQUE(字段)

注意：唯一索引就是唯一约束

# 唯一索引
# 添加唯一索引
ALTER TABLE student ADD UNIQUE(phone);

# 修改唯一索引
ALTER TABLE student MODIFY name VARCHAR(32) UNIQUE;

# 删除唯一索引
ALTER TABLE student DROP INDEX name;

8.普通索引

最基本的索引，它没有任何限制

注意：普通索引不是约束，单单让当前字段查询快而已（没有做任何的限制 – 字段上的数据可以重复也可以为null）

alter table 表 add index(字段)

# 普通索引
# 添加普通索引
ALTER TABLE student ADD INDEX(course);

# 删除普通索引
DROP INDEX course on student;

# 全文索引
#alter table 表 add FULLTEXT(字段)
# 注意：MySQL5.5以上版本才有的索引，在工作中也不使用该索引

四、视图

含义：虚拟表，和普通表一样使用，mysql5.1版本出现的新特性，是通过表动态生成的数据

创建视图：create [or replace] view 视图名 as select 字段名 from 表名 where 条件 [with check option]

​ with check option – 当修改值超过条件时，不允许修改

​ or replace – 如果已有视图，则替换

查询视图：select * from 视图名

修改视图：update 视图名 set 字段=”值”

删除视图：drop view 视图名

# 创建视图
# or replace - 如果有当前视图名，就替换
# with check option - 修改时，超出了条件就不允许修改
CREATE OR REPLACE VIEW view01 AS SELECT name,phone,age FROM student WHERE age>40 WITH CHECK OPTION;

# 查询视图中的数据
SELECT * FROM view01;

# 修改视图中的数据
UPDATE view01 set name="ccc" where name="defaultName";
UPDATE view01 set age=20 where name="土行孙";

# 删除视图中的数据
DELETE FROM view01 WHERE name="欧克鎏孙";

# 注意：更新视图中的数据，会影响到原表

# 删除视图
DROP view view01;

五、触发器

由某个事件去触发某个操作，这些事件有：insert、update、delete语句；

创建触发器：

[ delimiter XX ]

create trigger 触发器名 [before | after] 触发事件 on 表名 for each row 执行语句

before ： 前置触发器

after ：后置触发器

Ps：

new.字段：当前写法可以获得添加语句字段对应的值

old.字段：当前写法可以获得删除语句字段对应的值

begin

多条执行语句

end 加上 “结束符XX”

drop trigger t1

# 触发器

# 场景：班级表、学科表、学生表

CREATE TABLE class(
	id INT(3) PRIMARY KEY auto_increment,
	name VARCHAR(32),
	num INT(3) DEFAULT 0
)
INSERT INTO class(name) VALUES("Java2211");
INSERT INTO class(name) VALUES("Java2212");
INSERT INTO class(name) VALUES("HTML2201");
INSERT INTO class(name) VALUES("HTML2202");
INSERT INTO class(name) VALUES("HTML2203");

CREATE TABLE course(
	id INT(3) PRIMARY KEY auto_increment,
	name VARCHAR(32),
	num INT(3) DEFAULT 0
)
INSERT INTO course(name) VALUES("Java");
INSERT INTO course(name) VALUES("HTML");

CREATE TABLE student(
	id INT(3) PRIMARY KEY auto_increment,
	name VARCHAR(32),
	class_id INT(3),
	course_id INT(3)
)

# 添加学生
# 后置触发器：student表添加数据，就更新class和course表
delimiter xx
create trigger trigger01 after insert on student for each row 
	BEGIN
		UPDATE class set num=num+1 WHERE id = new.class_id;
		UPDATE course set num=num+1 WHERE id=new.course_id;
	END xx
delimiter ;

INSERT INTO student(name,class_id,course_id) VALUES("金蝉子",1,1);

INSERT INTO student(name,class_id,course_id) VALUES("孙悟空",2,1);

INSERT INTO student(name,class_id,course_id) VALUES("猪八戒",3,2);

INSERT INTO student(name,class_id,course_id) VALUES("哪吒",4,2);

INSERT INTO student(name,class_id,course_id) VALUES("杨戬",4,2);

# 删除学生
# 前置触发器：删除学生时，就先更新class和course表
delimiter xx
create trigger trigger02 before delete on student for each row 
	BEGIN
		UPDATE class set num=num-1 WHERE id = old.class_id;
		UPDATE course set num=num-1 WHERE id= old.course_id;
	END xx
delimiter ;

DELETE FROM student where id=1;

# 删除触发器
DROP TRIGGER trigger01;
DROP TRIGGER trigger02;

六、存储过程

把SQL语句封装在一个存储过程中，调用即可，类似于Java中的方法

# 存储过程

# 最简单的存储过程
delimiter xx
CREATE PROCEDURE pro01()
	BEGIN
		SELECT * FROM student;
	END xx
delimiter ;

# 调用存储过程
CALL pro01();

# 存储过程的参数
# IN -- 传入参数
# OUT - 返回参数
# INOUT - 传入/返回参数
# 注意：存储过程没有返回值，但是有返回参数

# 带有传入参数的存储过程
# 需求：传入id，查询出对应的学生信息
delimiter xx
CREATE PROCEDURE pro02(IN s_id INT(3))
	BEGIN
		SELECT * FROM student WHERE id = s_id;
	END xx
delimiter ;

# 调用存储过程
CALL pro02(2);

# 带有返回参数的存储过程
# 需求：传入id，返回对应的学生姓名
delimiter xx
CREATE PROCEDURE pro03(IN s_id INT(3),OUT s_name VARCHAR(32))
	BEGIN
		SELECT name INTO s_name FROM student WHERE id = s_id;
	END xx
delimiter ;

# 调用存储过程
CALL pro03(2,@s_name);
SELECT @s_name;

# 带有传入返回参数的存储过程
# 需求：传入id，返回对应的学生年龄
delimiter xx
CREATE PROCEDURE pro04(IN s_id INT(3),OUT s_age INT(3))
	BEGIN
		SELECT age INTO s_age FROM student WHERE id = s_id;
	END xx
delimiter ;
# 调用存储过程
CALL pro04(2,@s_age);
SELECT @s_age;

delimiter xx
CREATE PROCEDURE pro04(INOUT param INT(3))
	BEGIN
		SELECT age INTO param FROM student WHERE id = param;
	END xx
delimiter ;
# 调用存储过程
SET @param = 2;
CALL pro04(@param);
SELECT @param;

# 删除存储过程
DROP PROCEDURE pro04;

八、函数

1.预定义函数

MySQL给我们提供的函数

注意：MySQL的函数必须有返回值，而且使用select查询

# 数学函数
SELECT ABS(-100);# 计算绝对值
SELECT SQRT(9);# 计算平方根
SELECT POW(3,3);# 计算次方
SELECT MOD(10,3);# 计算余数
SELECT RAND();# 计算随机数

# 字符串函数
SELECT LENGTH("用良心做教育，做真实的自己");# 获取字符串所占的字节数
SELECT CONCAT("用良心做教育",",","做真实的自己");#字符拼接
SELECT SUBSTRING("用良心做教育，做真实的自己",2,5);# 字符从1开始

# 日期时间函数
SELECT NOW();
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAYOFMONTH(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
SELECT DATE_FORMAT(NOW(),"%Y年%m月%d日 %H时%i分%s秒");#Y--2022四位  y--22 两位

2.自定义函数

注意：函数必须带返回值

# 无参数带返回值的函数
# 需求：查询出id为3对应的学生姓名
delimiter xx
CREATE FUNCTION fun01() RETURNS VARCHAR(32)
	BEGIN
		DECLARE s_name VARCHAR(32);
		SELECT name INTO s_name FROM student WHERE id=3;
		RETURN s_name;
	END xx
delimiter ;

# 调用函数
SELECT fun01();

# 带参数带返回值的函数
# 需求：查询出指定id对应的学生姓名
delimiter xx
CREATE FUNCTION fun02(s_id INT(3)) RETURNS VARCHAR(32)
	BEGIN
		DECLARE s_name VARCHAR(32);
		SELECT name INTO s_name FROM student WHERE id= s_id;
		RETURN s_name;
	END xx
delimiter ;
# 调用函数
SELECT fun02(2);

# 删除函数
DROP FUNCTION fun01;