视图,索引,事务,存储过程,触发器,char和varchar
视图
视图就是将这个查询返回的结果集起了一个名字保存起来,视图就是临时存储数据构成的一张表。不是一个真正的表,但是我们使用时候可以当成一张表来使用。
作用
- 简化查询
- 进行权限控制,视图可以把表的增删改查的权限封闭,只开放相应的视图权限,视图里面只是有我们想开放的数据。
比如公司的货物信息需要与合作的公司共享,不想让他知道全部的信息并能进行更改。
示例
-- 查询每个栏目下面商品的平均价格,并按照平均价格排序,
-- 查询平均价前三高的栏目。
SELECT cat_id,AVG(market_price) price
FROM goods
GROUP BY cat_id ORDER BY price DESC LIMIT 0,3;
一条复杂的语句,创建一个视图并命名,再次调用可以直接执行视图。
CREATE VIEW v_top3_avg_by_cat AS
SELECT cat_id,AVG(market_price) price
FROM goods
GROUP BY cat_id ORDER BY price DESC LIMIT 0,3;
SELECT * FROM v_top3_avg_by_cat;
运行的结果是一样的
注意
1、视图的数据来源于数据表,修改视图的时候也会修改数据表。
2、但是我们建议使用视图主要用于查询数据,不建议用视图去修改数据。
事务
事物是指逻辑上的一组操作,组成这组操作的各个单元,要么全部执行成功,要么全部实行失败。比如转账。
数据库—事务
索引(书的目录)
比如电商平台,我们常常会根据名字搜索想要的东西,这时给名字建立索引会提高效率。
建立索引的好处是会自动排序,不建立索引的话会进行全表搜索,当数据量大的时候效率低。
索引是一个“内置表”,该表的数据是对某个真实表的某个(些)字段的数据做了“排序”之后的存储形式。
其作用是:极大提高表查找数据的速度!——其效率(速度)可以匹敌二分查找(非常著名的算法)。
注意:索引在提高查找速度的同时,降低增删改的速度。
形式
对创建(设计)表来说,建立索引是非常简单的事,形式如下:
索引类型 (字段名1,字段名2, … ) //可以使用多个字段建立索引,但通常是一个。
分类
MySQL目前主要有以下几种索引类型:
1.普通索引
2.唯一索引
3.主键索引
4.组合索引
5.全文索引
1、普通索引:key(字段名1,字段名2, … ):它只具有索引的基本功能——提速(其他索引不但有提速还有额外功能)
2、唯一索引:unique key (字段名1,字段名2, … ) duplicate
3、主键索引:primary key (字段名1,字段名2, … ) unique key + not null
CREATE TABLE product(
id INT PRIMARY KEY auto_increment,
`name` VARCHAR(10),
address varchar(50),
KEY(`name`)
);
INSERT INTO product(name,address) VALUES('小米手机','北京');
INSERT INTO product(name,address) VALUES('华为手机','深圳');
INSERT INTO product(name,address) VALUES('三星手机','韩国');
INSERT INTO product(name,address) VALUES('苹果手机','美国');
INSERT INTO product(name,address) VALUES('锤子手机','北京');
INSERT INTO product(name,address) VALUES('OPP手机','北京');
-- 反复执行这条语句进行数据的增加
INSERT INTO product(name,address) SELECT name,address FROM product;
这时我们随意更改一条语句在进行查找,如果没有索引的话会效率很低。
SELECT * FROM product WHERE name='小米手机1';
使用索引
存储过程
包含一系列sql语句的集合,完成一个复杂的功能,存储过程可以被反复使用,执行效率高。
存储过程功能上类似于Java里面封装的函数。
DELIMITER:定界符
MYSQL的默认结束符为";",其实就是告诉mysql解释器,该段命令是否已经结束了,mysql可以执行了。
在命令行客户端中,如果有一行命令以分号结束,如输入下面的语句:
select * from test_table;
回车后,mysql将会执行该命令。
但有时候,不希望MySQL这么做,可能输入较多的sql语句,且语句中包含有分号,那么我们就可以重新定义定界符。
-- 存储过程 类似于java中函数
delimiter $$ -- 将原来";"这个定界符替换为"$$"
CREATE PROCEDURE show_goods()
BEGIN
UPDATE goods SET goods_number=goods_number+1;
SELECT * FROM goods;
END $$
delimiter ; -- 存储过程定义完了之后恢复原来的定界符";"
CALL show_goods; -- 调用存储过程
触发器(Trigger):
是一个特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件触发。
就像电梯上的超载报警,达到条件自动触发。
触发器经常用于加强数据的完整性约束和业务规则等。
在MySQL中,触发器可以在你执行INSERT、UPDATE或DELETE的时候,执行一些特定的操作。在创建触发器时,可以指定是在执行SQL语句之前或是之后执行这些操作。通过触发器,你可以实现一些业务逻辑或一些数据限制,在简化应用程序逻辑,优化系统性能时非常有用。
实现一个触发器:在student表里面插入一条记录的时候在student_total表里面的total字段加1。
CREATE TABLE student_total(
total INT
);
SELECT * FROM student_total;
DELIMITER $$
CREATE TRIGGER student_insert_trigger
AFTER INSERT ON student FOR EACH ROW
BEGIN
UPDATE student_total SET total=total+1;
END $$
DELIMITER ;
INSERT INTO student(`name`, age, gender) VALUES('xxxx11',20,'男');
这个表只有一个字段一条记录,这条记录的作用就是当student表每插入一条数据的时候不停地加1。
删除触发器:
DROP TRIGGER IF EXISTS student_insert_trigger;
char和varcahr
char:定长,char(M),M代表宽度,即:可容纳的字符数 0<=M<=255。char(10):可存10个字符
varchar:变长,varchar(M), 0<=M<=65535
这么看起来使用varchar似乎更好?并不会
区别
char是定长:M个字符,如果存的小于M个字符,实占M个字符。
varchar:M个字符,存的小于M个字符,设定为N,N<M,实占N个字符。
选择原则:
1、空间利用率。
2、速度上:定长的速度快。
char(10)
varchar(10)
char利用率<=100%。而varchar永远<100%,因为需要1-2个字节用于标志实存字符长度。
现在磁盘的空间比较多,如果存储的字节不是特别多10个以内都用char,速度会提升,
例如:char(5),只存1个,利用率20%,存三四个利用率在60%-70%,定长速度比较快。
varchar(5),利用了4个,还要加额外的1-2个说明字符,所以存的短的话没有必要用varchar。
最极端例子:一个字段存性别:男、女
使用char(1)要比varchar(1)不管是速度还是空间利用率都要高
那么什么时候使用varchar更好呢?
存储一段介绍并且介绍的字数差异较大时。