数据库进阶

 

视图

• 对于复杂的查询，在多个地方被使用，如果需求发生了改变，需要更改sql语句，则需要在多个地方进行修改，维护起来非常麻烦
• 解决：定义视图
• 视图本质就是对查询的封装
• 定义视图，建议以v_开头

create view 视图名称 as select语句;

• 例：创建视图，查询学生对应的成绩信息

create view v_stu_score_course as 
select
    stu.*,cs.courseNo,cs.name courseName,sc.score
from
    students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses cs on cs.courseNo = sc.courseNo

• 查看视图：查看表会将所有的视图也列出来

show tables;

• 删除视图

drop view 视图名称;
例：
drop view v_stu_score_course;

• 使用：视图的用途就是查询

select * from v_stu_score_course;

---

 

内置函数

字符串函数

• 拼接字符串concat(str1,str2...)

select concat(12,34,'ab');

• 包含字符个数length(str)

select length('abc');

• 截取字符串
  • left(str,len)返回字符串str的左端len个字符
  • right(str,len)返回字符串str的右端len个字符
  • substring(str,pos,len)返回字符串str的位置pos起len个字符

select substring('abc123',2,3);

• 去除空格
  • ltrim(str)返回删除了左空格的字符串str
  • rtrim(str)返回删除了右空格的字符串str

select ltrim('  bar   ');

• 大小写转换，函数如下
  • lower(str)
  • upper(str)

select lower('aBcD');

数学函数

• 求四舍五入值round(n,d)，n表示原数，d表示小数位置，默认为0

select round(1.6);

• 求x的y次幂pow(x,y)

select pow(2,3);

• 获取圆周率PI()

select PI();

• 随机数rand()，值为0——1.0的浮点数

select rand();
例1：随机取一条数据
select * from students order by rand() limit 1;

日期时间函数

• 当前日期current_date()

select current_date();

• 当前时间current_time()

select current_time();

• 当前日期时间now()

select now();

• 日期格式化date_format(date,format)

• 参数format可选值如下

%Y 获取年，返回完整年份
%y 获取年，返回简写年份
%m 获取月，返回月份
%d 获取日，返回天值
%H 获取时，返回24进制的小时数
%h 获取时，返回12进制的小时数
%i 获取分，返回分钟数
%s 获取秒，返回秒数

• 例：将使用-拼接的日期转换为使用空格拼接

select date_format('2016-12-21','%Y %m %d');

流程控制

• case语法：等值判断
• 说明：当值等于某个比较值的时候，对应的结果会被返回；如果所有的比较值都不相等则返回else的结果；如果没有else并且所有比较值都不相等则返回null

case 值 when 比较值1 then 结果1 when 比较值2 then 结果2 ... else 结果 end
例：
select case 1 when 1 then 'one' when 2 then 'two' else 'zero' end as result;

自定义函数

创建

• 语法如下

delimiter $$
create function 函数名称(参数列表) returns 返回类型
begin
sql语句
end
$$
delimiter ;

• 说明：delimiter用于设置分割符，默认为分号
• 在“sql语句”部分编写的语句需要以分号结尾，此时回车会直接执行，所以要创建存储过程前需要指定其它符号作为分割符，此处使用//，也可以使用其它字符

示例

• 要求：创建函数my_trim，用于删除字符串左右两侧的空格

• step1：设置分割符

delimiter $$

• step2：创建函数

create function my_trim(str varchar(100)) returns varchar(100)
begin
return ltrim(rtrim(str));
end
$$

• step3：还原分割符

delimiter ;

使用自定义函数

select '  abc  ',my_trim('   abc   ')

---

存储过程

存储过程，也翻译为存储程序，是一条或者多条SQL语句的集合

创建

• 语法如下

delimiter //
create procedure 存储过程名称(参数列表)
begin
sql语句
end
//
delimiter ;

• 说明：delimiter用于设置分割符，默认为分号
• 在“sql语句”部分编写的语句需要以分号结尾，此时回车会直接执行，所以要创建存储过程前需要指定其它符号作为分割符，此处使用//，也可以使用其它字符

示例

• 要求：创建查询过程，查询学生信息
• step1：设置分割符

delimiter //

• step2：创建存储过程

create procedure proc_stu()
begin
select * from students;
end
//

• step3：还原分割符

delimiter ;

调用

• 语法如下

call 存储过程(参数列表);

调用存储过程proc_stu
call proc_stu();

• 存储过程和函数都是为了可重复的执行操作数据库的 sql 语句的集合.
• 存储过程和函数都是一次编译,就会被缓存起来,下次使用就直接命中缓存中已经编译好的 sql, 不需要重复编译
• 减少网络交互,减少网络访问流量

---

事务

为什么要有事务

• 事务广泛的运用于订单系统、银行系统等多种场景
• 例如：A用户和B用户是银行的储户，现在A要给B转账500元，那么需要做以下几件事：
  • 1. 检查A的账户余额>500元；
    2. A 账户中扣除500元;
    3. B 账户中增加500元;
• 正常的流程走下来，A账户扣了500，B账户加了500，皆大欢喜。那如果A账户扣了钱之后，系统出故障了呢？A白白损失了500，而B也没有收到本该属于他的500。以上的案例中，隐藏着一个前提条件：A扣钱和B加钱，要么同时成功，要么同时失败。事务的需求就在于此
• 所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要么都执行，要么都不执行。所以，应该把他们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性

事务命令

• 要求：表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎
• 查看表的创建语句，可以看到engine=innodb

show create table students;

• 修改数据的命令会触发事务，包括insert、update、delete

• 开启事务，命令如下：

  • 开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中

begin;

• 提交事务，命令如下
  • 将缓存中的数据变更维护到物理表中

commit;

• 回滚事务，命令如下：
  • 放弃缓存中变更的数据

rollback;

---

 

索引

• 思考：在图书馆中是如何找到一本书的？
• 一般的应用系统对比数据库的读写比例在10:1左右，而且插入操作和更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重
• 当数据库中数据量很大时，查找数据会变得很慢
• 优化方案：索引

语法

• 查看索引

show index from 表名;

• 创建索引

方式一：建表时创建索引

create table create_index(
id int primary key,
name varchar(10) unique,
age int,
key (age)
);

方式二：对于已经存在的表，添加索引

如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
字段类型如果不是字符串，可以不填写长度部分

create index 索引名称 on 表名(字段名称(长度))
例：
create index age_index on create_index(age);
create index name_index on create_index(name(10));

• 删除索引：

drop index 索引名称 on 表名;

示例

创建测试表testindex

create table test_index(title varchar(10));

向表中加入十万条数据

• 创建存储过程proc_test，在存储过程中实现插入数据的操作
• step1：定义分割符

delimiter //

• step2：定义存储过程

create procedure proc_test()
begin
declare i int default 0;
while i<100000 do
insert into test_index(title) values(concat('test',i));
set i=i+1;
end while;
end 
//

• step3：还原分割符

delimiter ;

• 执行存储过程proc_test

call proc_test();

查询

• 开启运行时间监测：

set profiling=1;

• 查找第1万条数据test10000

select * from test_index where title='test10000';

• 查看执行的时间：

show profiles;

• 为表title_index的title列创建索引：

create index title_index on test_index(title(10));

• 执行查询语句：

select * from test_index where title='test10000';

• 再次查看执行的时间

show profiles;

缺点

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
• 但是,在互联网应用中,查询的语句远远大于增删改的语句,甚至可以占到80%~90%,所以也不要太在意,只是在大数据导入时,可以先删除索引,再批量插入数据,最后再添加索引

分析查询

explain
select * from test_index where title='test10000'

---