mysql增强

作者: 骇客与画家 | 来源:发表于2018-01-29 21:07 被阅读0次

Maven增强

ts-manage 聚合工程，小组的高级、中级程序员他来搭建聚合工程，包括聚合工程下的子项目。

ts-manage-pojo User.pojo，照猫画虎写其它模块

ts-manage-mapper

ts-manage-service

ts-manage-controller/web

Maven增强

项目间关系：依赖、继承、聚合

依赖，调用jar时,maven会自动去找这个jar依赖的相关的其它的jar包。
继承，jar管理，所有子项目中共有jar包，在此声明，版本升级就非常的方便。
聚合，在pom.xml中有一组module，管理多个子项目，一键编译。

总览.png

测试（工作量非常大）和运维（部署）
2017年美国博士DevOpts开发运维一体化

Maven+GitHub版本控制+Jenkins
自动测试 JUnit @Test，会自动产生一个测试报告
自动运维自动从GitHub下载新的代码，自动的编译，自动打包，自动部署到tomcat下，自动启动

ecshop网上开源商城代码，小型电商。

mysql增强

在传统企业中由DBA数据库管理员，写SQL，查询性能调优。

但是让大家只是了解？随着硬件和互联网，大数据的发展。例如：nosql 不仅仅是sql

关系型数据库：Oracle，SqlServer，MySQL

软肋，表的数据的关联关系非常的密切，多表联查，随着数据的增长，查询越来越慢。

数据量有一定限制！

Oracle单表可以支持到多少条数据？上亿

MySQL 1000万
nosql，最低亿级数据以上，mongodb,hbase

MySQL旧的方式在新的形势下被淘汰：
1）视图
2）存储过程，函数
3）触发器
旧的传统的企业，旧的系统还在使用这些旧的技术
我们面试时，会被问。

练习下面两个视图：============================================

CREATE VIEW v_goods238 AS
SELECT * FROM t_goods WHERE category_id=238

CREATE VIEW v_cat_goods AS 
SELECT c.name,g.item_type,g.title FROM t_goods_category c
LEFT JOIN t_goods g
ON c.id=g.category_id

mysql增强，大型系统

1、【废除】视图，本质就是一个查询语句，作用把复杂SQL封装起来，调用中直接访问视图名称即可。这样调用者就非常方便。很难SQL就是DBA，系统分析师。

2、【废除】存储过程，本质脚本语言（弱）。性能高（在数据库上直接执行，它的服务器性能是所有服务器中最强）数据库迁移oracle,mysql

3、【废除】函数，本质自定义函数，丰富业务算法（个性）。

数据库迁移oracle,mysql

4、【废除】触发器，本质类似拦截器。也很死板。
数据库迁移oracle,mysql

5、索引，复合索引，最左前缀特性本质，就是提高查询效率

什么时候创建索引？用户抱怨时。
什么字段上创建索引？where,order
主键有索引吗？主键天生就有索引！！！！

1 数据库编程(了解)

数据库中可以编写并保存一段程序，做数据运算

视图 View
存储过程 Procudure
函数 Function
触发器 Trigger

mysql 数据库编程能力较弱，很多功能无法实现

2 变量

2.1 用户会话变量

一个客户端与服务器连接期间的变量

set @v1 = 123;
select @v1;

2.2 局部变量

begin ... end; 是一对大括号，局部变量只能在 begin 到 end 之间使用，end结束后，变量消失

使用 declare 关键字来定义局部变量

delimiter ; 结束符改成;

delimiter // 结束符改成//

创建复杂的存储过程中间会用到;结束符，如果几个;放在一起，MySQL只会识别第一个，以后的不会识别，这样会及其不完整

begin
   declare v2 int default 123;
   select v2;
   select @v1;
end//
 （上面无法执行，只是参考）

3. 视图

3.1 简单视图

视图的本质就是一个查询语句

select * from t_goods where category_id=238

create view v_goods238 as 
select * from t_goods 
where category_id=238;

select * from v_goods238;

DELIMITER $$  /*设定一个标识符，标识结束$$*/

USE `tedu_store`$$  /*打开指定数据库tedu_store*/

DROP VIEW IF EXISTS `v_goods238`$$  /*如果此视图已经存在，就删除*/

create algorithm = undefined definer = `root`@`localhost` sql security
definer VIEW `v_goods238` as
select
  `t_goods`.`id`            AS `id`,
  `t_goods`.`category_id`   AS `category_id`,
  `t_goods`.`item_type`     AS `item_type`,
  `t_goods`.`title`         AS `title`,
  `t_goods`.`sell_point`    AS `sell_point`,
  `t_goods`.`price`         AS `price`,
  `t_goods`.`num`           AS `num`,
  `t_goods`.`barcode`       AS `barcode`,
  `t_goods`.`image`         AS `image`,
  `t_goods`.`status`        AS `status`,
  `t_goods`.`priority`      AS `priority`,
  `t_goods`.`created_time`  AS `created_time`,
  `t_goods`.`modified_time` AS `modified_time`,
  `t_goods`.`created_user`  AS `created_user`,
  `t_goods`.`modified_user` AS `modified_user`
FROM `t_goods`
WHERE (`t_goods`.`category_id` = 238)$$

DELIMITER ;

select * from v_goods238;

3.2 多级关联视图

create view v_cat_goods as 
select 
    c.name,
    g.item_type,
    g.title
from 
    t_goods_category c
left join 
    t_goods g
on 
    c.id = g.category_id;
    
select * from v_cat_goods;

视图在大型项目中被废除！！！！
视图过程是把查询所有的记录都查询回来，然后再过滤数据，过滤掉非238.
如果这张表中有一千万数据。数据量大时无法优化。
Java程序可以吗？mybatis它直接处理，返回结果数据。结果就比视图快。

4. 存储过程

存储过程是存储在数据库服务器中的一段过程代码

优点：

Jsp-java-database(proc)

存储过程离数据库最近，所以它执行代码是效率最高的。

Client(brower)-WebServer（tomcat）-DatabaseServer（mysql）

WebServer和DatabaseServer，在企业中，WebServer一般服务器即可，但是DatabaseServer是所有服务器中最好的。甚至大型项目，小型机。Unix+Oracle。

废除：

1）写数据库代码（存储过程-单独学习语法，无法断点，system.out没有输出，几乎无法调试。DBA），写java代码（人员众多，好调试）。后期维护。不好维护。Java好维护。

2）存储过程不好写，质量不好控制。

4.1 定义存储过程

--  修改结束符
delimiter //

--  创建存储过程p1
create procedure p1()
begin  --  大括号开始
   -- 定义局部变量v2
   declare v2 int default 123;
   select v2; --  显示v2变量的值
end//  --  大括号结束

4.2 调用存储过程

call p1()//

4.3 查看存储过程

show procedure status where db='db1'
    查看指定库中的存过程
show create procedure p1

4.4 删除存储过程

drop procedure if exists p1//

4.5 存储过程的参数

三种参数：

in 输入参数
out 输出参数
inout 既能输入又能输出

存储过程参数测试

--  向学生表和联系方式表同时插入数据
--  1) 插入学生数据
--  2) 得到新插入的自增主键值
--  3) 插入联系方式表
传统方式：
1．  页面填写两张表的数据，学生名称，学生的电话，存在两个表中
2．  学生表自增主键
3．  联系表中和学生表的关联（一对一）

CREATE TABLE `t_student` (
  `stu_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `t_tel` (
  `stu_id` int(11) NOT NULL,
  `tel` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

DELIMITER //
create procedure p_student_tel
(in name varchar(20),
in tel varchar(20), out id int)
begin
   declare v int;-- 定义临时变量用来存自增主键值
   --  插入学生数据
   insert into t_student(name) values(name);
   --  获得新生成的自增主键值存到v
   set v = last_insert_id();
   --  插入联系方式数据
   insert into t_tel(stu_id,tel) values(v, tel);
   --  自增主键值存到输出参数id
   set id=v;
end//

-- 调用
CALL p_student_tel('刘强东',13010100808,@id);

--  调用
CALL p_student_tel('刘强东',13010100808,@id);
 
select * from t_student;
select * from t_tel;
select @stu_id;

5 流程控制

5.1 if

if 条件 then

   代码

end if

if 条件 then
   代码1
else
   代码2
end if

5.2 case

case
   when 条件1 then ...
   when 条件2 then ...
   else ...
end case

case 变量或表达式
   when 值1 then ...
   when 值2 then ...
   else
end case

分支判断测试

delimiter //
 
drop procedure if exists p1//
 
create procedure p1(in v int)
begin
   if v=1 then
      select 'v的值是1';
   end if;
   case v
      when 2 then select 'v的值是2';
      when 3 then select 'v的值是3';
      else select 'v的值不是2,3';
   end case;
end//
 
call p1(1) //
call p1(2) //
call p1(3) //
call p1(4) //

5.3 while

while 条件 do
   代码
end while

5.4 loop

--  lp: 循环命名，可以起任意的名字

lp: loop
   ...
   if 条件 then
      leave lp; --  指定离开哪个循环
   end if;
   ...
end loop;

5.5 repeat

repeat
   代码
until 条件 end repeat;

循环测试

--  新建 tb1 表
use db1 //

drop table if exists tb1 //

create table tb1(
   id int primary key auto_increment,
   num int
)engine=innodb charset=utf8 //

delimiter //
drop procedure if exists p2//
create procedure p2(in n int)
begin
   declare i int default 0;
   --  while
   while i<n do -- i的值从0递增到n-1
      insert into tb1(num) values(i+1);
      set i=i+1;
   end while; 
   --  loop
   set i=0;
   lp: loop
        insert into tb1(num) values(i+1);
        set i=i+1;
        
        if i=n then -- i等于n时退出loop循环
            leave lp;
        end if;
    end loop;
    -- repeat
    set i=0;
    repeat
      insert into tb1(num) values(i+1);
      set i=i+1;
   until i=n end repeat; -- i等于n时退出repeat循环
end//

call p2(10) //

select * from tb1 //

6. 函数

函数有返回值
调用存储过程用call，调用函数直接调

函数为什么不让用？

分布式？又放在Java端

Zookeeper集群管理者，当一台服务的配置更新，zk会自动把配置改变信息更新到其它所有的机器上。

5.1 创建函数

create function fn()
returns varchar(100)
begin
   执行代码运算产生计算结果
   return 计算结果;
end//

5.1.1求平方的函数 fn_pow

DELIMITER //
drop function if exists fn_pow //

create function fn_pow(n int)
returns int -- 函数返回什么类型的数据
begin
   
   set r = n*n;
   return r; -- 向调用位置返回计算结果
end //

select fn_pow(5) //
select *, fn_pow(num) from tb1 //

6.2 查看函数

show function status;

show function status where db='tedu_store';

show create function fn_pow;

6.2 删除函数

drop function if exists fn_pow //

7 触发器

对表中的数据操作时，可以触发一段代码执行

三个数据操作：

insert
update
delete

两个触发时间：

before
after

一张表中最多可以有6个触发器

before insert
after insert
before update
after update
before delete
after delete

两个隐含对象

new

新的数据行
insert的new：要插入的新行
update的new：修改后的新行
delete的new：没有
old

旧的数据行

insert的old：没有

update的old：修改前的旧数据

delete的old：被删除的旧数据
访问新行或旧行数据：

new.id
new.name
old.age
old.tel

7.1 创建触发器

操作商品表insert,before,new

create trigger 名 触发时间 on 表
for each row
begin
   代码
end;

用触发器自动更新updated字段

--  学生表添加 updated字段
alter table t_student
add created datetime //
--  添加触发器，插入数据时自动填入时间
--  before insert
DELIMITER //
CREATE TRIGGER tr_b_i
BEFORE INSERT ON t_student FOR EACH ROW
BEGIN
 SET new.created=NOW();
END //

insert into t_student(name) values('tony') //
 
select * from t_student //

--  添加触发器，修改数据时自动修改时间

alter table t_student
add updated datetime //

DELIMITER //
CREATE TRIGGER tr_b_u
BEFORE UPDATE ON t_student FOR EACH ROW
BEGIN
  SET new.updated = NOW();
END //

UPDATE t_student SET NAME='tina' WHERE stu_id=2
 
select * from t_student //


-- 级联删除。删除主表信息时利用触发器删除子表信息

-- before delete

delimiter //
create trigger tr_b_d
before delete on t_student for each row
begin
    delete from t_tel where stu_id=old.stu_id;
END //

select * from t_student;

select * from t_tel;

delete from t_student where name='a';

7.2 查看触发器

进入系统库 information_schema

use information_schema //

查询 triggers 表中触发器信息

select * from triggers

7.3 删除触发器

drop trigger 触发器名

级联删除

删除主表的数据时。要把子表的数据删除。

8 数据库的备份、恢复

8.1 备份方式：

1）冷备份（按月）

2）热备份

a. 定时任务 Job （按周，日（凌晨2点），小时）、

b. 实时备份（主从复制，实时同步）

查看数据安装路径

select @@basedir AS basePath FROM DUAL;

C:\Program Files\MySQL Server 5.5\bin>

使用 mysqldump 命令备份数据库

使用 mysql 命令恢复数据库

8.2 备份

退出 mysql，在系统命令行执行：

下面命令是一行代码，不能折行

mysqldump -uroot -p
--default-character-set=utf8    //表中存的是什么编码
hr>d:\hr.sql    //库>文件

数据库名

8.3 恢复

再次恢复：

1）在数据库中新建一个库

create database jtdb2 charset utf8;

在系统命令行执行恢复命令

下面命令是一行代码，不能折行

mysql -uroot -p
--default-character-set=utf8
jtdb2:\jtds.sql

9. 索引

9.1 存储过程产生大量数据

1) 创建一个内存表 tm, engine=memory
2) 创建一个磁盘表 td
3) 在存储过程中，循环 20000 次向内存表插入随机字符串
4) 把 20000 条数据，一批存磁盘表
5）清空内存表

-- 内存表

drop table if exists tm;
create table tm(
    c varchar(20)
) engine=memory chaset=utf8;

-- 磁盘表

drop table if exists td;
create table td(
    id int primary key auto_increment,
    c varchar(20)
) engine=innodb charset=utf8;

-- 存储过程

drop procedure if exists gen_data;
delimiter //
create procedure gen_data(in n int)
begin
    declare i int default 0;
    while i<n do
        insert into tm values (uuid());
        set i=i+1;
    end while;
    insert into td(c) select c from tm;
    delete from tm;
end //

call gen_data(1000)

DROP PROCEDURE IF EXISTS test_insert;
DELIMITER //
CREATE PROCEDURE test_insert(cnt INT)
  BEGIN 
    DECLARE i INT DEFAULT 0;
    START TRANSACTION;
    WHILE i<cnt
      DO 
          INSERT INTO td(c) VALUES(UUID());
      SET i=i+1;
      END WHILE;
    COMMIT;
END //


call test_insert(1000);

9.2 索引优化

索引其本质是创建了一张索引表，现有表的拷贝

（索引需要代价，插入数据要重新排序）

只拷贝现有表的指定索引字段。重要是做了一件事情：

排序

为什么它要排序呢？为什么排序后就快了呢？

不创建索引，查询时，全表遍历。

折半算法

用索引提高数据查找效率

-- 没有索引，花3.5秒查询
select * from td where c='ab' //

-- 对c字段创建索引，花1分40秒左右
create index td_c_index on td(c) //

-- 有索引查询花费  0.00 秒
select * from td where c='ab' //

9.3 MySQL 验证索引的使用-最左前缀特性

复合索引

通过 EXPLAIN 关键字可以判断查询 SQL 语句是否使用索引

EXPLAIN select * from td where c='ab'

Possible_keys如果有值就代表使用了哪个索引，如果null就代表查询没有使用索引，全表遍历。

SELECT * FROM tb1 
WHERE user_id=100;

SELECT * FROM tb1 
WHERE order_id=300;

SELECT * FROM tb1 
WHERE user_id=100 AND order_id=300;

SELECT * FROM tb1 
WHERE order_id=300 AND user_id=100 ;

没使用索引
EXPLAIN 
SELECT * FROM t_goods 
WHERE title LIKE '%皮面%'

使用索引
EXPLAIN
SELECT * FROM t_goods
WHERE title LIKE '皮面%'

没有使用索引
EXPLAIN
SELECT * FROM t_goods
WHERE title LIKE '%皮面'

EXPLAIN select * from tb_cart where user_id=1 and item_id=1

 EXPLAIN select * from tb_cart where user_id=1

EXPLAIN SELECT * FROM tb_cart WHERE item_id=1

查看SQL的执行计划，可以看出第一句、第二句SQL使用了索引，第三句 SQL未使用索引。很好的证明了索引左侧前缀特性。

注意：

1）下面的语句违反了左侧前缀的特性，为何仍然可以使用索引呢？因为MYSQL对SQL语句有优化，它会重新组合where条件。

EXPLAIN SELECT * FROM tb_cart WHERE item_id=1 AND user_id=1

2）没有where条件的查询是不会使用索引的。

10. 作业

商品表 tb_item

select * from tb_item;

有什么品牌

select distinct brand from tb_item;

最贵商品的品牌、名称、价格

select brand,title,price
from tb_item
order by price desc
limit 1;

分页查看商品

select id,brand,title,price
from tb_item
order by price desc
-- limit 0,10;
-- limit 10,10;
-- limit 20,10;
limit 30,10;

商品名和商品描述

select
    title,item_desc
from 
    tb_item i
join
    tb_item_desc 
on 
    i.id = d.item_id
limit 1;

商品分类表 tb_item_cat

select * from tb_item_cat;

select * 
from 
    tb_item_cat
where 
    name like '%电脑%' 
or
    name like '%笔记本%'
or 
    name like '%手机%';

查询所有的顶层分类

select * from tb_item_cat
where 
    is_parent=1 
and 
    parent_id is null;

查询 161 下的二层分类

select * from 
    tb_item_cat
where 
    parent_id=161
order by sort_order;

查询 162 下的三层分类

select * from tb_item_cat
where 
    parent_id=162
order by 
    sort_order;

用户表

select * from tb_user;

订单表

select * from tb_order;

用户 id 是14的订单

select * from tb_order where user_id=14;

订单编号是 20161001490698615071

查询这个订单的所有商品

select * from tb_order_item
where order_id=20161001490698615071;

订单编号是 20161001490698615071

查询每种类别的商品数量

select cid,count(*) from tb_item group by cid

查询类别 163 的商品

select id,title,price from tb_item where cid=163;

查询商品价格不大于100的商品名称列表

select id,title,price from tb_item where price<100;

查询品牌是联想,且价格在40000以上的商品名称和价格

 select id,title,price from tb_item where brand='联想' and price>40000;

查询品牌是三木,或价格在10以上的商品名称和价格

 select id,brand,title,price from tb_item where brand='三木' or price<50;

查询品牌是三木、广博、齐心的商品名称和价格

select id,brand,title,price from tb_item where brand in('三木','广博','齐心');

查询品牌不是联想、戴尔的商品名称和价格

select id,brand,title,price from tb_item where brand not in('联想','戴尔');

查找品牌是联想且价格大于10000的电脑名称

select id,brand,title,price from tb_item where brand='联想' and price>10000;

查询联想或戴尔的电脑名称列表

select id,brand,title,price from tb_item where brand='联想' or brand='戴尔';

查询联想、戴尔、三木的商品名称列表

select id.brand,title,price from tb_item where brand in('联想','戴尔','三木');

查询不是戴尔的电脑名称列表

select id,brand,title,price from tb_item where brand not in('戴尔');

查询所有是记事本的商品品牌、名称和价格

select id,brand,title,price from tb_item where title like '%记事本%';

查询品牌是末尾字符是'力'的商品的品牌、名称和价格

select id,brand,title,price from tb_item where brand like '%力';

名称中有联想字样的商品名称

select id,brand,title,price from tb_item where title like '%联想%';

查询卖点含有'爆款'电脑名称

select id,brand,title,price from tb_item where cell_point like '%爆款%';

查询开头字母是A的电脑名称

select id,brand,price from tb_item where title like 'A%';

将地址表中的城市、地区、详细地址组合到一起,

select concat(receiver_city, receiver_district,receiver_address) addr from tb_address;

获取订单编号为 20161001490698615071 中的每种商品的标题、单价、数量、总价

select title,price,num,total_fee from tb_order_item where order_id='20161001490698615071';

统计商品表中各个品牌的商品数量

select brand,count(*) from tb_item group by brand

统计商品表中各个品牌的商品数量,并且按照数量从少到多排序

select brand,count(*) c from tb_item group by brand order by c;

统计商品表中各个品牌的商品数量,去除品牌为NULL的行,并且按照数量从少到多排序

select brand,count(*) c from tb_item where brand is not null group by brand order by c;

查询不同品牌中最贵的商品价格

select id,title,brand,price from tb_item where(brand,price) in(select brand,max(price) m from tb_item where brand is not null group by brand);

将不同品牌中最贵的商品按照价格降序排序

select id,title,brand,price from tb_item where (brand,price)in(select brand,max(price) m from tb_item where brand is not null group by brand) order by price desc;

找出不同品牌中最贵的商品的前三名

select id,title,brand,price from tb_item where (brand,price)in(select brand,max(price) m from tb_item where brand is not null group by brand) order by price desc 
limit 3;

查询订购了10000028商品的客户姓名和联系方式(三层子查询)

首先查询订单表,根据10000028这个商品编号找到有该商品的订单编号

然后查询订单表,根据上面得到的订单编号找到订购此订单的用户编号

最后查询用户表,根据第二部中查到的用户编号,找到对应的用户名和联系方式

select id,username,phone,email from tb_user where id in (select user_id from tb_order where (select order_id from td_order_item where item_id=10000028))

select 
    distinct 
from
    td_user u 
join 
    tb_order o 
on 
    u.id=o.user_id
join 
    tb_order_item i
on
    o.order_id=i.order_id
where 
    item_id=10000028

mysql增强

Maven增强

Maven增强

mysql增强

1 数据库编程(了解)

2 变量

2.1 用户会话变量

2.2 局部变量

3. 视图

3.1 简单视图

3.2 多级关联视图

4. 存储过程

4.1 定义存储过程

4.2 调用存储过程

4.3 查看存储过程

4.4 删除存储过程

4.5 存储过程的参数

5 流程控制

5.1 if

5.2 case

5.3 while

5.4 loop

5.5 repeat

6. 函数

5.1 创建函数

5.1.1求平方的函数 fn_pow

6.2 查看函数

6.2 删除函数

7 触发器

7.1 创建触发器

7.2 查看触发器

7.3 删除触发器

8 数据库的备份、恢复

8.1 备份方式：

8.2 备份

8.3 恢复

9. 索引

9.1 存储过程产生大量数据

9.2 索引优化

9.3 MySQL 验证索引的使用-最左前缀特性

10. 作业

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

我爱编程