07-数据库高级

作者: EndEvent | 来源:发表于2017-10-11 08:09 被阅读1938次

一、复习

数据来源: student_info.xls
1、查询显示前5行学生信息
2、查询语文成绩是80~90之间的学生信息
3、查询1711和1713班级所有学生信息
4、求出学生的总分大于250以上的学生信息
5、求出每个班级的数学平均分
6、求出每个班级语文平均分大于80分以上的班级
7、查询1703班级所有学生数学平均成绩
8、统计1703班级对应科目平均成绩
9、查询每个科目的最高分
10、查询单科第一的学生信息
11、查询总分第一的学生信息

student_info

1、SELECT * FROM students_info LIMIT 5;
2、SELECT * FROM students_info WHERE chinese BETWEEN 80 AND 90;
3、SELECT * FROM students_info WHERE class IN ("1711","1713");
4、SELECT *,(math+chinese+english) AS "总分" FROM students_info WHERE (math+chinese+english)>250;
5、SELECT class AS "班级",AVG(math) AS "数学平均分" FROM students_info GROUP BY class;
6、SELECT class AS "班级",AVG(chinese) AS "语文平均分" FROM students_info GROUP BY class HAVING AVG(chinese)>80;
7、SELECT class AS "班级",AVG(math) AS "数学平均分" FROM students_info WHERE class=1703;
8、SELECT class AS "班级",AVG(math) AS "数学平均分",AVG(chinese) AS "语文平均分",AVG(english) AS "英语平均分" FROM students_info WHERE class=1703;
9、SELECT MAX(math) AS "数学",MAX(chinese) AS "语文",MAX(english) AS "英语" FROM students_info;
10、SELECT * FROM students_info WHERE math=(SELECT MAX(math) FROM students_info) OR english=(SELECT MAX(english) FROM students_info) OR chinese=(SELECT MAX(chinese) FROM students_info);
11、SELECT *,(math+chinese+english) AS "总分" FROM students_info WHERE (math+chinese+english)=(SELECT MAX(math+chinese+english) FROM students_info);

虚拟字段是不可以放在where后面的，例如where avg(math)其中math是实际字段，但avg(math)是求出来的平均分是虚拟字段；
需要进行条件处理的虚拟字段，可以放在having后面；

二、多表查询

1.连接查询

表1-test_name

表2-test_detail

1.1 内连接 inner join 或者 join
内连接：查询左右表都有的数据，不要左右中空的那一部分；
内连接：左右连接的交集；

1.1内连接

语法:
  select 列1,列2,列N from
  tableA 
  inner join 
  tableB
  on tableA.列 = tableB.列  (此处表连接成一张大表，完全当成一张普通表看)
  where,having,group by.... (条件照常写)

例如:
查询每个学生的具体信息
select tableA.name,tableB.* from 
test_name tableA 
inner join 
test_detail tableB
on tableA.id=tableB.id;

查询年龄小于20的学生信息
select tableA.name,tableB.* from 
test_name tableA 
inner join 
test_detail tableB
on tableA.id=tableB.id
where tableB.age<20;

test_name tableA即是test_name AS tableA，两种写法。

1.2 左连接 left join
左连接1: 得到的是A的所有数据，和满足某一条件的B的数据;

1.2.1左连接

左连接2: 得到的是A中的所有数据减去"与B满足同一条件的数据"，然后得到的A剩余数据；

1.2.2左连接

语法:
  select 列1,列2,列N from
  tableA 
  left join 
  tableB
  on tableA.列 = tableB.列  (此处表连接成一张大表，完全当成一张普通表看)
  where,having,group by.... (条件照常写)

例如,左连接1:
select tableA.name,tableB.* from 
test_name tableA 
left join 
test_detail tableB
on tableA.id=tableB.id;

例如,左连接2:
select tableA.name,tableB.* from 
test_name tableA 
left join 
test_detail tableB
on tableA.id=tableB.id
where
tableB.id is not null;

1.3 右连接
右连接1: 得到的是B的所有数据，和满足某一条件的A的数据；

1.3.1右连接

右连接2: 得到的是B中的所有数据减去 "与A满足同一条件的数据“，然后得到的B剩余数据;

1.3.2右连接

语法:
  select 列1,列2,列N from
  tableA 
  right join 
  tableB
  on tableA.列 = tableB.列  (此处表连接成一张大表，完全当成一张普通表看)
  where,having,group by.... (条件照常写)

例如,右连接1:
select tableA.name,tableB.* from 
test_name tableA 
right join 
test_detail tableB
on tableA.id=tableB.id;

例如,右连接2:
select tableA.name,tableB.* from 
test_name tableA 
right join 
test_detail tableB
on tableA.id=tableB.id
where
tableA.name is not null;

左连接：即以左表为基准，到右表找匹配的数据,找不到匹配的用NULL补齐；
推荐左连接来代替右连接，兼容性会好一些；

2.多表查询

表1-studet

表2-score

1、男同学的考试科目及对应成绩
2、姓张同学的考试科目及对应成绩
3、既有英语又有计算机成绩的学生信息
4、姓王的同学并且有一科以上成绩大于90分的学生信息

5、查询李四的考试科目（c_name）和考试成绩（grade）
6、查询计算机成绩低于95的学生信息
7、查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

1、
SELECT name,sex,c_name,grade 
FROM 
student,score
WHERE
student.id=score.stu_id AND sex='男';
2、
SELECT name,c_name,grade
FROM
student,score
WHERE
student.id=score.stu_id AND name LIKE "张%"; 
3、
SELECT student.*
FROM 
student,score s1,score s2
WHERE
student.id=s1.stu_id AND student.id=s2.stu_id 
AND
s1.c_name="计算机" AND s2.c_name="英语"; 
4、
SELECT student.*
FROM
student,score
WHERE
student.id=score.stu_id AND grade>90 AND name LIKE "王%";
5、
SELECT name,c_name,grade 
FROM
student,score
WHERE
student.id=score.stu_id AND name="李四";
6、
SELECT student.*,c_name,grade 
FROM
student,score
WHERE
student.id=score.stu_id AND c_name="计算机" AND grade<95;
7、
SELECT name,2017-birth AS age,department,c_name,grade 
FROM
student,score
WHERE
student.id=score.stu_id AND address like "%湖南%";

找到表与表的对应关系;
如果多张表中有同一个属性名时必须标注是哪个表中的属性;

三、数据库索引

索引概念
索引就是加快检索表中数据的方法。数据库的索引类似于书籍的目录。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。
索引特点
2.1 加快数据库的检索速度;
2.2 降低了数据库插入、修改、删除等维护任务的速度;
2.3 索引创建在表上，不能创建在视图上;
2.4 索引既可以直接创建，也可以间接创建;
2.5 可以在优化隐藏中，使用索引;
2.6 查询处理器执行SQL语句，在一个表上，一次只能使用一个索引;
索引优点
3.1 创建唯一性索引，保证数据库表中每一行数据的唯一性;
3.2 大大加快数据的检索速度，这也是创建索引的最主要的原因;
3.3 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义;
3.4 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间;
3.5 通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能;
索引缺点
4.1 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加;
4.2 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大;
4.3 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度;
添加索引(一般数据库默认都会为主键生成索引)
普通索引: 这是最基本的索引，它没有任何限制，也是我们大多数情况下用到的索引；
唯一索引: 与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）；

语法1(index普通索引):
  alter table 表名 add index  索引名称(列名)；
例如:
  alter table students_info add index cxy_class(class);
语法2(index普通索引):
   create index 索引名称 on 表名(列名);
例如:
  create index idx_math on studetns_info(math);

语法:(unique唯一索引)
  alter table 表名 add unique 索引名称(列名)；
例如:
  alter table students_info add unique cxy_name(name);

6.删除索引

语法: 
  drop index 索引名 on 表名;
例如:
  drop index cxy_class on students_info;

7.显示索引信息

语法:
  show index from 表单名;
例如:
  show index from studens_info;

四、数据库视图

什么是数据库视图
1.1 视图是虚拟表，其内容由查询定义，是从一个或几个基本表（或视图）中导出的表；
1.2 视图是原始数据库数据的一种变换，是查看表中数据的另外一种方式(将视图可以只看看到感兴趣的数据)；
1.3 视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。
1.4 视图看上去非常像数据库的物理表，对它的操作同任何其它的表一样。当通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因，有些视图可以修改对应的基表，而有些则不能（仅仅能查询）。
创建数据库视图

语法:
  create view 视图名称 as select 语句;
例如(将student和score表生成数据库视图)：
create view student_score 
as 
select student.*,score.c_name,score.grade 
from 
student,score 
where 
student.id=score.stu_id;

3.删除视图

语法:
  drop view 视图名
例如:
  drop view student_score;

4.视图的使用

数据库视图和实际数据库表一样方式：
例如:
  select * from student_score;
例如: 
  select * from student_score where name like "王%";

5.视图的使用场景

  - 使用视图可以简化复杂查询(例如多表查询时，经常需要进行多表关联);
  - 视图用利于提高安全性(不同用户查看不同视图);

五、数据存储过程

可以使用notepad++编写，然后复制黏贴方式；也可以用Navicat，在对应数据库中添加函数；

数据存储过程
存储过程其实就是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。存储过程可由应用程序通过一个调用来执行，而且允许用户声明变量，同时存储过程可以接收和输出参数、返回执行存储过程的状态值，也可以嵌套调用。
存储过程的优点
2.1 减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量不会有很大的差别。但差别在于存储过程包含了成百上千行甚至更多的SQL语句，性能绝对比一条条的调用SQL语句要高的多；
2.2 执行速度更快。存储过程创建时，数据库已经对数据进行了一次解析和优化。其存储过程一旦执行，在内存中就会保留一份这个存储过程，在下次执行同样的存储过程时，可以从内存中直接读取；
2.3 更强的安全性。存储过程时通过用户授权权限(而不是基于表)，它们可以提供对特定数据的访问，提高代码安全，比如防止SQL注入；
2.4 业务逻辑可以封装在存储过程中，这样不仅容易维护，而且执行效率也高；
存储过程的缺点
3.1 可移植性: 当从一种数据库迁移到另外一种数据库时，不少的存储过程的编写要进行调整和修改；
3.2 存储过程需要花费一定的学习时间去学习，比如学习语法等；
定义存储过程的结束符
SQL语句中是以分号来表结束。而存储过程的结束，需要先定义分隔符，例如定义//为分隔符，即为DELIMITER //定义结束符，另外可以根据自己的喜好定义其他的符号。
如何创建存储过程

  DELIMITER //
  CREATE PROCEDURE p2()
  LANGUAGE SQL
  DETERMINISTIC
  SQL SECURITY DEFINER
  COMMENT "存储过程程序测试"
  BEGIN
  SELECT "hello world!";
  END //
  DELIMITER ;

5.1 `DELIMITER //`定义好结束符；
5.2 `CREATE PROCEDURE p2()`使用`CREATE PROCEDURE+`方法创建存储过程；
5.3 ` LANGUAGE SQL`指定了使用的语句；
5.4 `DETERMINISTIC`当确定每次存储过程输入和输出都是相同内容时，可以使用该关键字，否则默认为`NOT DETERMINISTIC`；
5.5 `SQL SECURITY DEFINER`表示调用时检查用户的权限，`INVOKER`值表示用户调用该存储过程时检查，`DEFINER`值创建存储过程时检查；
5.6 `COMMENT`其表示存储过程的注释说明部分；
5.7 `BEGIN`和`END`之间的，是存储过程的主题部分;
5.8 最后` DELIMITER ;`恢复mysql分号定界符;
5.9 select其实可以当做输出语句使用(类似于print);

更为简单写法，mysql存储存储过程，封装sql(默认是分号为结束符):
delimiter //  # 定义好结束符（为了区分默认的分号结束符）
create procedure p1()
begin
  select * from actor;
end
//
delimiter ; # 恢复mysql分号定界符

注意:
1、过程体的开始与结束使用BEGIN与END进行标识!!!
2、# 即是注释符

数据存储过程

调用存储过程的方法

  语法: 
    call 存储过程名字();
  例如:
    call p2()即是调用存储过程

注意: 如果没有设置结束符，默认是分号为结束符，即调用时是: call p2();

显示存储过程相关信息

  show procedure status;

删除存储过程

  drop procedure 存储过程名字;

带参数存储过程

# 定义
delimiter //
create procedure demo2_in(n int)
begin
  select * from actor where actor_id>n;
end
//
delimiter ;
# 调用
mysql> call demo2_in(190);

# 定义
delimiter //
create procedure show_data(i_name varchar(20))
begin 
    select * from student where name=i_name;
end
//
delimiter ;

案例: 写一个数据库存储过程，名字为add_data，用于插入数据表student(表结构参考上面student表);

上述可以写成create procedure demo2_in(in n int)表示存储过程有一个输入参数，而in参数是默认的，即可以省略不写
数据类型和数据库中的是一样的！

存储过程 --- 定义变量
在存储过程的一开始声明变量，并指出它们的数据类型，一旦声明变量后，就可以在存储过程中使用。

语法:
  declare 变量名 数据类型 default 默认值;
例如:
  declare a,b int default 5;
  declare str varchar(20);

# 定义
  delimiter //
  create procedure add_data()
  begin 
    declare a,b int default 10;
    insert into students values(a,"变量测试a",100);
    set b = 3030;
    insert into students values(b,"变量测试b",99);
  end
  //
  delimiter ;

存储过程 --- if语句
if语句，就是条件选择语句。

 语法：
  if 条件 then
    执行语句;
  else 
    执行语句;
  endif;


例如：
# 定义
delimiter //
create procedure show_data_all(isAll int)
begin
    if isAll=1 then
     select * from students;
   else
     select * from students limit 3;
   end if;
end
//
delimiter ;

# 调用
mysql> call show_data_all(0);

布尔类型: true和false，不区分大小写.

存储过程 --- case语句
当有很多if语句时，就可以考虑使用case语句，它是多分支选择语句。

语法:
  case 变量
  when 具体数值1 then
    执行语句1;
  when 具体数值2 then
    执行语句2;
  when 具体数值3 then
    执行语句3;
  else
    上述值都没有时，执行语句4;
  end case;

# 定义
delimiter //
create procedure add_data_case(n int)
begin
  case n
  when 0 then
     insert into students values(1000,"测试0",90);
   when 1 then
     insert into students values(1001,"测试1",91);
   else
     insert into students values(1002,"测试2",92);
   end case;
   end
   //
delimiter ;

# 调用
 mysql> call add_data_case(1);
 mysql> call add_data_case(10);

students数据结构

案例: 例如表student，表score，表students_info，写一个存储过程show_table，传入不同的表名即显示对应表的所有数据；

13.存储过程 --- while语句
while语句和其他编程语言中的while类似，都是循环操作。

语法:
  while 条件 do
    执行语句
  end while;

例如(往test_name 表中插入数据):
delimiter //
create procedure add_data_while(n int)
begin
    declare i int default 0;
    while i<n do
        insert into test_name values(2000+i,"数据测试");
        set i=i+1;
    end while;
    
    select * from test_name;
end
//
delimiter ;

mysql> call add_data_while(10) ;

备注:
  上述操作在第二次调用时会报错，原因在于id是主键，主键是唯一的不能重复。
  解决方式，即添加自增长属性，在插入时，不设置id即可。

MySQL中concat函数可以连接一个或者多个字符串。
例如: concat("hello","world!");
如何看效果: select concat("hello"," world!");

loop以及leave
loop循环不需要循环条件，同时也不需要结束条件，而leave语句就是可以离开循环的，这个方式方便于在游标中遍历获取到的数据。

语法:
  循环名称:loop
    循环语句
    if(条件) then
      leave 循环名称;
    end if;
  end loop;

例如(往students表中插入数据):
delimiter //
create procedure add_data_loop()
begin
  declare i,_id,_math int default 0;
  set _id = 3000;
  set _math = 50;
  loop_insert:loop
    if i >= 100 then
        leave loop_insert;
    end if;
    
    set i = i +1;
    set _id = _id + 1;
    set _math = _math + 1;
    insert into students values(_id,"loop测试",_math);
  end loop;
end 
//
delimiter ;

存储过程 --- 游标
数据库中游标是十分重要的概念，游标提供了一种对从表中检索出数据进行操作的灵活手段。游标实际上是一种能从包含多条数据记录的结果集中每次提取一条记录的机制。
游标即是对查询数据库返回记录进行遍历，以方便进行相应操作；

语法:
  declare 游标名称 cursor for select .....
  declare continue handler for not found
  open 游标名称;
    fetch 游标名称 into variable[,variable];
  close 游标名称;
说明:
  声明一个游标，并用cursor for select
  指定当前遍历完结果集后，游标如何继续处理
  打开游标
    游标向下走一步（取出数据）
  使用后关闭游标
例如(通过有游标方式，修改students_info表中math成绩):
delimiter //
create procedure change_table()
begin
    # 定义变量math
    declare _math int;
    # 定义查询条件name
    declare _name char(20);
    # 遍历完成条件，默认为0即未完成
    declare done int default 0;
    
    # 定义游标
    declare student_cursor cursor for select name,math from students_info;
    # 遍历完成后的操作，改变done为1
    declare continue handler for not found set done=1;
    
    # 打开游标
    open student_cursor;
    # 循环，取出数据
    cursor_loop:loop
        # 结束条件
        if done=1 then
            leave cursor_loop;
        end if;
        
        # 取出数据
        fetch student_cursor into _name,_math;
        
        # 更新数据
        update students_info set math=_math+10 where name=_name;
    end loop;
    # 关闭游标
    close cursor_loop; 
    
    # 显示所有数据
    select * from students_info;
end
//
delimiter ;

案例: 将表test_detail中的age字段改为显示年份，将成绩score减少5分；

delimiter //
create procedure change_detail()
begin
    # 定义变量
    declare _age char(20);
    declare _score int;
    # 查询条件
    declare _id int;
    # 数据遍历完成条件
    declare done int default 0;
    
    # 定义游标
    declare detail_cursor cursor for select id,age,score from test_detail;
    # 遍历完成后的操作
    declare continue handler for not found set done=1;
    
    # 打开游标
    open detail_cursor;
    # loop循环
    cursor_loop:loop
        # 结束循环
        if done then
            leave cursor_loop;
        end if;
        
        # 取出数据
        fetch detail_cursor into _id,_age,_score;
        
        # 更新数据
        update test_detail set age=2017-_age,score=_score-5 where id=_id;
    end loop;
    # 关闭游标
    close detail_cursor; 
    
    # 查询显示数据
    select * from test_detail;
end
//
delimiter ;

六、数据备份和数据恢复

1.备份恢复概念
有多种可能会导致数据表的丢失或者服务器的崩溃，一个简单的DROP TABLE或者DROP DATABASE的语句，就会让你的数据表化为乌有。更危险的是DELETE * FROM table_name，可以轻易的清空你的数据表，而这样的错误是很容易的发生的。因此，拥有能够恢复的数据对于一个数据库系统来说是非常重要的。

2.用Navicat for Mysql自带工具备份恢复

3.数据库备份通过导出数据或者文件的拷贝来保护数据

数据导入

数据导入
数据导出

数据导出

七、sql优化

如何发现有问题的SQL
慢查询日志，哪些查询需要优化是有效率问题，在MySQL中就有这个慢查日志就是记录有效率问题的SQL进行监控。

  // 查询是否开启慢查日志
  show variables like "slow_query_log";
  // 慢查日志的开启
  set global log_queries_not_using_indexes=on;
  set global slow_query_log=on;
  // 设置查询时间大于1s，但在实际生产环境中一般都是100ms
  set global long_query_time=1;
  // 查看慢查日志存储目录
  show variables like "%query_log%";

慢查日志的存储格式

// 查询时间
# Time: 170906 15:04:33
// 执行sql的主机信息
# User@Host: root[root] @ localhost [127.0.0.1]
// sql的执行信息(执行时间、锁定时间、所发送的行数、所扫描的行数)
# Query_time: 0.001000  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
// sql执行时间(时间戳的形式记录执行时间) 
SET timestamp=1504681473;
// sql的语句
show variables like "slow%";

如果不小心删除慢查日志，通过flush logs;会自行创建新的文件；

如何分析sql查询

使用explain查询sql的执行计划
  explain select customer_id,first_name,last_name from customer;
查询结果含义
  table: 显示这行数据是关于哪张表的；
  type: 重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL；
  possible_keys: 显示可能应用在这张表中的索引，如果为空，即没有可能的索引；
  key: 实际使用的索引，如果为null则没有使用索引；
  key_len：使用索引的长度，在不损失精准性的情况下，长度越短越好；
  ref：显示索引的那一列被使用了，如果可能的话，是一个常数；
  rows：mysql认为必须检查的从来返回请求数据的行数；
  extra：列需要注意的返回值；
  （Using filesort: 使用外部文件，看到这个时，表示查询需要优化的，mysql需要进行额外的步骤来发现如何对返回的行排序。
     Using temporary: 使用临时表，看到这个时，表示查询需要优化的，mysql需要创建临时表来存储结果，这个通常发生在对不同的列集进行order by上，而不是grop by上）

4、count()和max()的优化方法

  // 查询address表中，手机号码最大的 --- 优化max函数
  select max(phone) from address;
  explain select max(phone) from address;

  // 建立一个索引，即最直接的应对之道，是为phone建立一个简单的索引：
  create index idx_phone on address(phone);

5、随机数的产生：
rand()是产生0~1的数据数(包含0，不包含1)。
如果需要n~m范围内的随机数: rand() * (m-n) + n
上述可能会出现小数，所以可以使用round四舍五入: round( rand() * (m-n) +n );
例如需要产生50~100的随机数: round( rand() * 50 + 50 )；

6、字符串拼接
concat(str1,str2,…)返回结果为连接参数产生的字符串。

  concat("1、","hello ","world");

7、大量数据的产生
为了方便测试，以后更多需要自己模拟大数量，即往数据库中插入很多数据。
例如，需要插入100W条数据:

delimiter //
create procedure add_test_data()
begin
declare n,i int default 0;
declare _time char(32) default '0000-00-00 00:00:00';
declare _data char(32);
set n = 1000000;
create table test(id int(4) primary key auto_increment,c_name char(20),c_data char(32),c_time char(20));
loop_insert:loop
    if i>=n then 
        leave loop_insert;
    end if;
    
    set i = i + 1;
    set _data = round(rand()*10000+1000);
    insert into test(c_name,c_data,c_time) values("数据测试",_data,_time);
end loop;
update test set c_time=concat('2017-09-10 ',round(rand()*10+10),":",round(rand()*49+10),":",round(rand()*49+10)) where c_time='0000-00-00 00:00:00';
end 
//
delimiter ;

test表单，没有建立索引:
select * from test where c_time between '2017-09-10 12:00:00' and '2017-09-10 12:30:33';
select max(c_data) from test where c_time between '2017-09-10 12:00:00' and '2017-09-10 12:30:33';
select * from test where c_data=3333;
select max(c_time) from test where c_data=3333;

test_index表单，建立索引:
建立索引:
  create index c_time on test_index(c_time) ;
  create index c_data on test_index(c_data);

07-数据库高级

一、复习

二、多表查询

三、数据库索引

四、数据库视图

五、数据存储过程

六、数据备份和数据恢复

七、sql优化

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

软件测试