数据库的日志、备份、视图、索引、SQL编程及案例分析

---

数据库日志

在数据库保存数据时，有时候不可避免会出现数据丢失或者被破坏，这样情况下，我们必须保证数据的安全性和完整性，就需要使用日志来查看或者恢复数据了。

数据库中数据丢失或被破坏可能原因：

• 误删除数据库
• 数据库工作时，意外断电或程序意外终止
• 由于病毒造成的数据库损坏或丢失
• 文件系统损坏后，系统进行自检操作
• 升级数据库时，命令语句不严格
• 设备故障等等
• 自然灾害
• 盗窃

如何避免：数据库日志，日志会记录我们需要的各种数据的变化，一旦出现问题，可以通过日志，进行数据的回溯和溯源，从而将数据恢复过来。

MySQL日志

MySQL有几个不同的日志文件，可以帮助你找出mysqld内部发生的事情：

日志类型	记入文件中的信息类型
错误日志	记录启动、运行或停止时出现的问题
查询日志	记录建立的客户端连接和执行的语句
二进制日志	记录所有更改数据的语句。主要用于复制和即时点恢复
慢日志	记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询
事务日志	记录InnoDB等支持事务的存储引擎执行事务时产生的日志

1.错误日志

错误日志，顾名思义，就是用来记录数据库错误的日志。

启动、运行、关闭等等操作中，如果DBMS出现了错误信息，则会记录到错误日志中。

在window系统中，错误日志会被写入data\主机名称.err，在这个日志文件中记录错误信息

在Linux中，随着安装的方式不同，该日志文件可能存储的位置也会发生变化

mysql> show variables like "%log_error%";
+----------------------------+------------------------------------------------------+
| Variable_name              | Value                                                |
+----------------------------+------------------------------------------------------+
| binlog_error_action        | ABORT_SERVER                                         |
| log_error                  | F:\mysql\mysql-8.1.0-winx64\data\LAPTOP-G7DGNGN7.err |
| log_error_services         | log_filter_internal; log_sink_internal               |
| log_error_suppression_list |                                                      |
| log_error_verbosity        | 2                                                    |
+----------------------------+------------------------------------------------------+

2.通用查询日志

会记录各种查询及相关日志信息，需要手动开启

mysql> show variables like "%general_log%";
+------------------+------------------------------------------------------+
| Variable_name    | Value                                                |
+------------------+------------------------------------------------------+
| general_log      | OFF                                                  |
| general_log_file | F:\mysql\mysql-8.1.0-winx64\data\LAPTOP-G7DGNGN7.log |
+------------------+------------------------------------------------------+

在配置文件可以开启查询日志

3.慢查询日志

慢日志，主要是用来做软件优化的，将查询速度较慢的SQL记录下来，以方便于开发者进行SQL优化。

默认没有被开启，如果要开启，则需要在配置文件中添加对应项，开启服务。

--默认没有启用慢查询，为了服务器调优，建议开启
mysql> show global variables like '%slow_query_log%';
+---------------------+-----------------------------------------------------------+
| Variable_name       | Value                                                     |
+---------------------+-----------------------------------------------------------+
| slow_query_log      | OFF                                                       |
| slow_query_log_file | F:\mysql\mysql-8.1.0-winx64\data\LAPTOP-G7DGNGN7-slow.log |
+---------------------+-----------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

--开启方法,当前生效，永久有效配置文件中设置
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.01 sec)

--刷新日志，将缓存中的数据写入磁盘
mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)

mysql> show global variables like '%slow_query_log%';
+---------------------+-----------------------------------------------------------+
| Variable_name       | Value                                                     |
+---------------------+-----------------------------------------------------------+
| slow_query_log      | ON                                                        |
| slow_query_log_file | F:\mysql\mysql-8.1.0-winx64\data\LAPTOP-G7DGNGN7-slow.log |
+---------------------+-----------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
--使用 mysqldumpslow 命令获得日志中显示的查询摘要来处理慢查询日志
--mysqldumpslow slow.log
--那么多久算是慢呢？
--如果查询时长超过long_query_time的定义值（默认10秒），即为慢查询：
mysql> show global variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)

4.二进制日志

bin_log日志，记录数据变量的日志，所以主要记录数据增删改。主要用来做数据备份、恢复等等一系列功能。

注意，不同的版本中，是否开启。

--查看有哪些二进制日志文件：
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       447 | No        |
| binlog.000002 |      3352 | No        |
| binlog.000003 |      1915 | No        |
| binlog.000004 |       180 | No        |
| binlog.000005 |       157 | No        |
| binlog.000006 |      1426 | No        |
| binlog.000007 |       157 | No        |
| binlog.000008 |       180 | No        |
| binlog.000009 |      2670 | No        |
| binlog.000010 |       157 | No        |
| binlog.000011 |      5066 | No        |
| binlog.000012 |      4708 | No        |
| binlog.000013 |       180 | No        |
| binlog.000014 |       201 | No        |
| binlog.000015 |       157 | No        |
| binlog.000016 |      6622 | No        |
| binlog.000017 |       497 | No        |
| binlog.000018 |       157 | No        |
+---------------+-----------+-----------+
--查看当前正在使用的是哪一个二进制日志文件：
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000018 |      157 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
--查看二进制日志内容：
mysql> show binlog events in 'binlog.000018';
+---------------+-----+----------------+-----------+-------------+----------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                             |
+---------------+-----+----------------+-----------+-------------+----------------------------------+
| binlog.000018 |   4 | Format_desc    |         1 |         126 | Server ver: 8.1.0, Binlog ver: 4 |
| binlog.000018 | 126 | Previous_gtids |         1 |         157 |                                  |
+---------------+-----+----------------+-----------+-------------+----------------------------------+

--该语句还可以加上Position（位置），指定显示从哪个Position（位置）开始：
mysql> show binlog events in 'binlog.000018' from 126;
+---------------+-----+----------------+-----------+-------------+------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+------+
| binlog.000018 | 126 | Previous_gtids |         1 |         157 |      |
+---------------+-----+----------------+-----------+-------------+------+

--使用命令mysqlbinlog查看二进制日志内容：mysqlbinlog [options] log-files

---

数据库备份

根据日志，做主从复制，就可以实现数据的备份。
根据服务器状态，可以分为热备份、温备份、冷备份

• 热备份：读、写不受影响；
• 温备份：仅可以执行读操作；
• 冷备份：离线备份；读、写操作均中止；

从对象来分，可以分为物理备份与逻辑备份

• 物理备份：复制数据文件；
• 逻辑备份：将数据导出至文本文件中；

从数据收集来分，可以完全备份、增量备份、差异备份

• 完全备份：备份全部数据；
• 增量备份：仅备份上次完全备份或增量备份以后变化的数据；
• 差异备份：仅备份上次完全备份以来变化的数据；

mysqldump工具

mysqldump是mysql官方提供的一个逻辑备份工具，可以通过这个工具，实现数据库的备份

C:\Windows\System32>mysqldump -uroot -p**** -B db_first > F:\a.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

结果如下所示：

存在SQL文件，如何还原数据。

第一种方式：用记事本打开×××.sql，使用SQL粘贴执行

第二种，使用MySQL命令

mysql -uroot -p××× -B db_first <  a.sql
## 2.读入数据
代码如下（示例）：
```c
data = pd.read_csv(
    'https://labfile.oss.aliyuncs.com/courses/1283/adult.data.csv')
print(data.head())

该处使用的url网络请求的数据。

---

MySQL索引视图（view）

视图（view）

数据库对象——view，可以看成一个特殊的表（但是注意，视图不是表）。

也就是在开发者，将查询结果封装成一个视图，我们就可以将这个结果当做一张表来使用。

视图通过以定制的方式显示来自一个或多个表的特定数据

视图是一种数据库对象，用户可以像查询普通表一样查询视图

视图内其实没有存储任何数据，它只是对表的一个查询

视图的定义保存在数据字典内，创建视图所基于对表称为“基表”

视图的优点

1. 简化查询，不用做复杂的查询，而是将从视图中查询需要的结果【视图不是表，不能加快查询速度】
2. 在安全性而言，视图有一定的保护作用，通过视图可以隐藏或者显示需要显示和隐藏的字段

优点：

• 提供了灵活一致级别安全性。
• 隐藏了数据的复杂性
• 简化了用户的SQL指令
• 通过重命名列，从另一个角度提供数据

需要注意，视图主要是用来展示数据，查询数据的，不是用来更新数据【增删改】。除非有必要，否则不要修改视图中数据。

定义视图

create view [可选项] 视图名称 as (查询语句)

--定义一个视图
 create view v_emp_dept2 as select emp.*, dept.name as deptName, dept.intro as intro from emp, dept where emp.dept
_id = dept.id;

--视图操作起来和表没有区别
select * from v_emp_dept2;

视图的注意事项

1. 视图必须有唯一命名
2. 在mysql中视图的数量没有限制
3. 创建视图必须从管理员那里获得必要的权限
4. 视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图
5. 在视图中可以使用OREDR BY，但是如果视图内已经使用该排序子句，则视图的ORDER BY将覆盖前面的
   ORDER BY。
6. 视图不能索引，也不能关联触发器或默认值
7. 视图可以和表同时使用

修改视图

--使用CREATE OR REPLACE VIEW 语句修改EMP_V_10 视图. 为每个列指定列名.

CREATE OR REPLACE VIEW emp_v_10
(id, name, sal, dept_id)
AS SELECT id,name,
salary, dept_id
FROM employees
WHERE dept_id = 10;

--在CREATE VIEW 语句中字段与子查询中的字段必须一一对应，否则就别指定别名，或在子查询中指定别名
--使用ALTER VIEW 语句修改EMP_V_10 视图. 为每个列指定列名.

ALTER VIEW emp_v_10
(id, name, sal, dept_id)
AS SELECT id,name,
salary, dept_id
FROM employees
WHERE dept_id = 10;

--在CREATE VIEW 语句中字段与子查询中的字段必须一一对应，否则就别指定别名，或在子查询中指定别名
--创建复杂视图，创建一个从两个表中查询数据，并进行分组计算的复杂视图.

CREATE VIEW dept_sum_vu_10
(name, minsal, maxsal, avgsal)
AS SELECT d.name, MIN(e.salary),
MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.dept_id = d. id
AND e.dept_id = 10;

修改视图的结构，没有修改是数据！！！

视图的数据是表中的数据，视图本身是没有数据的！

视图是定义在数据字典中的。

不要对视图的数据修改增删改，它就是用来查询的！！！！

删除视图

drop view  视图名称;

视图案例练习（作业）

--在数据库db_classes下创建college表。College表内容如下所示
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| number | int         | NO   | PRI | NULL    |       |
| name   | varchar(20) | NO   |     | NULL    |       |
| major  | varchar(20) | NO   |     | NULL    |       |
| age    | int         | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
    
mysql> CREATE TABLE college(
    -> number INT(10) NOT NULL UNIQUE PRIMARY KEY COMMENT '学号',
    -> name VARCHAR(20) NOT NULL COMMENT '姓名',
    -> major VARCHAR(20) NOT NULL COMMENT '专业',
    -> age INT(5) COMMENT '年龄'
    -> );
--在student表上创建视图college_view。视图的字段包括student_num、student_name、student_age和department。ALGORITHM设置为MERGE类型，并且为视图加上WITH LOCAL CHECK OPTION条件

mysql> create view college_view (student_num,student_name,student_age,department)
    -> as
    -> select number,name,age,major from college
    -> with local check option;

--查看视图college_view的详细结构
mysql> show create view college_view\G
*************************** 1. row ***************************
                View: college_view
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `college_view` (`student_num`,`student_name`,`student_age`,`department`) AS select `college`.`number` AS `number`,`college`.`name` AS `name`,`college`.`age` AS `age`,`college`.`major` AS `major` from `college` WITH LOCAL CHECK OPTION
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
--更新视图。向视图中插入3条记录。记录内容如下表所示
mysql> INSERT INTO college_view VALUES(0901,'张三',20,'外语');
Query OK, 1 row affected (0.00 sec)

mysql>     INSERT INTO college_view VALUES(0902,'李四',22,'计算机');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO college_view VALUES(0903,'王五',19,'计算机');
Query OK, 1 row affected (0.00 sec)

mysql> select * from college_view;
+-------------+--------------+-------------+------------+
| student_num | student_name | student_age | department |
+-------------+--------------+-------------+------------+
|         901 | 张三         |          20 | 外语       |
|         902 | 李四         |          22 | 计算机     |
|         903 | 王五         |          19 | 计算机     |
+-------------+--------------+-------------+------------+
--修改视图，使其显示专业为计算机的信息，其他条件不变

--方法一：
mysql> CREATE OR REPLACE ALGORITHM=UNDEFINED VIEW
    ->     college_view(student_num,student_name,student_age,department)
    ->     AS SELECT number,name,age,major
    ->     FROM college WHERE major='计算机'
    ->     WITH LOCAL CHECK OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from college_view;
+-------------+--------------+-------------+------------+
| student_num | student_name | student_age | department |
+-------------+--------------+-------------+------------+
|         902 | 李四         |          22 | 计算机     |
|         903 | 王五         |          19 | 计算机     |
+-------------+--------------+-------------+------------+
2 rows in set (0.00 sec)
    
--方法二：
mysql> ALTER ALGORITHM=UNDEFINED VIEW
    -> college_view(student_num,student_name,student_age,department)
    -> AS SELECT number,name,age,major
    -> FROM college WHERE major='计算机'
    -> WITH LOCAL CHECK OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from college_view;
+-------------+--------------+-------------+------------+
| student_num | student_name | student_age | department |
+-------------+--------------+-------------+------------+
|         902 | 李四         |          22 | 计算机     |
|         903 | 王五         |          19 | 计算机     |
+-------------+--------------+-------------+------------+
2 rows in set (0.00 sec)

--删除视图college_view
mysql> DROP VIEW college_view;
Query OK, 0 rows affected (0.00 sec)

索引（index）

索引优缺点

优点：索引的优点是可以提高检索数据的速度，这是创建索引的最主要的原因；对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度；使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。

缺点：

1. 占据对应的存储空间
2. 数据发生了更改，索引也要进行更新和维护

索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空
间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了。

索引分类

根据索引在底层的存储方式，分为聚簇索引和非聚簇索引。

MySQL中，innodb引擎中，主键索引就是一种聚簇索引。而其他的普通索引都是非聚簇索引。

根据索引的功能：普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引等

索引的设计原则

为了使索引的使用效率更高，在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的索引。本小
节将向读者介绍一些索引的设计原则。

1. 选择惟一性索引
2. 为经常需要排序、分组和联合操作的字段建立索引
3. 为常作为查询条件的字段建立索引
4. 限制索引的数目
5. 尽量使用数据量少的索引
6. 尽量使用前缀来索引
7. 删除不再使用或者很少使用的索引

创建索引

创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。创建索引有三种方式，这三种方式分别是创建表的时候创建索引、在已经存在的表上创建索引和使用ALTER TABLE语句来创建索引。

-- 普通索引的创建方式
create index 索引名称 on 表名称(字段名称);

-- 可以通过前缀表示对应的索引
create unique|Fulltext|其他类型 index 索引名称 on 表名称(字段名称);


-- 创建表的同时创建索引
create table t_name (
	字段 类型 约束,
    ……
    
    -- 主键索引
    primary key(主键字段),
    
    -- 普通所以
    index 索引名称(字段 [,其他字段]),
    
    -- 其他索引
    unique index 索引名称(字段)
  
)



在已经存在的表上，可以直接为表上的一个或几个字段创建索引。基本形式如下：help create index
CREATE [ UNIQUE | FULLTEXT | SPATIAL ]  INDEX 索引名
ON 表名 (属性名 [ (长度) ] [  ASC | DESC] );

1．创建普通索引
	CREATE INDEX index_name ON table(column(length))

2．创建惟一性索引
	CREATE UNIQUE INDEX indexName ON table(column(length))

3．创建全文索引
	CREATE FULLTEXT INDEX index_content ON article(content)

4．创建单列索引
	CREATE INDEX index3_name on index3 (name(10));
	
5．创建多列索引

6．创建空间索引



# 修改表结构的方式添加索引
ALTER TABLE table_name ADD [索引类型] INDEX index_name ON (column)


在已经存在的表上，可以通过ALTER TABLE语句直接为表上的一个或几个字段创建索引。基本形式如下：
ALTER  TABLE 表名  ADD  [ UNIQUE | FULLTEXT | SPATIAL ]  INDEX 
索引名（属性名 [ (长度) ] [ ASC | DESC]）;

1．创建普通索引
	ALTER TABLE table_name ADD INDEX index_name (column(length))

2．创建惟一性索引
	ALTER TABLE table_name ADD UNIQUE indexName (column(length))

3．创建全文索引
	ALTER TABLE index3 add fulltext index index3_name(name);

4．创建单列索引
	ALTER TABLE index3 add index index3_name(name(10));

5．创建多列索引
6．创建空间索引

注意：MySQL5.5及以后版本，默认采用了innodb引擎，如果没有特殊修改过，默认采用的索引类型底层都是基于B+树（B+ Tree）。

如何查看某张表是否存在索引和索引情况

show index from 表名称;

-- 也可以通过查看创建的语句来查看索引情况
show create table 表名称

索引案例练习（作业）

--创建测试表
mysql> DROP VIEW college_view;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test1(id int,num int,pass varchar(50));
Query OK, 0 rows affected (0.02 sec)

mysql> create table test2(id int,num int,pass varchar(50),    index idIdx (id));
Query OK, 0 rows affected (0.03 sec)

mysql> create table test3(id int,num int,pass varchar(50));
Query OK, 0 rows affected (0.01 sec)

mysql>

2、向表test1里插入100条数据
--python：
import os
import random
import hashlib

for i in range(1, 101):
    value1 = i
    value2 = i + int(random.random() * i)
    value3 = hashlib.md5(str(i).encode()).hexdigest()
    os.system(f"mysql -p61553 -uroot -e \"insert into db_classes.test1 values({value1}, {value2}, '{value3}');\" >> F:/mysql.txt 2>&1")

mysql> select * from test1;
+------+------+----------------------------------+
| id   | num  | pass                             |
+------+------+----------------------------------+
|    1 |    1 | c4ca4238a0b923820dcc509a6f75849b |
|    2 |    2 | c81e728d9d4c2f636f067f89cc14862c |
|    3 |    5 | eccbc87e4b5ce2fe28308fd9f2a7baf3 |
|    4 |    4 | a87ff679a2f3e71d9181a67b7542122c |
|    5 |    9 | e4da3b7fbbce2345d7772b0674a318d5 |
|    6 |    7 | 1679091c5a880faf6fb5e6087eb1b2dc |
|    7 |   11 | 8f14e45fceea167a5a36dedd4bea2543 |
|    8 |    8 | c9f0f895fb98ab9159f51fd0297e236d |
|    9 |   17 | 45c48cce2e2d7fbdea1afc51c7c6ad26 |
|   10 |   16 | d3d9446802a44259755d38e6d163e820 |
|   11 |   14 | 6512bd43d9caa6e02c990b0a82652dca |
|   12 |   12 | c20ad4d76fe97759aa27a0c99bff6710 |
|   13 |   17 | c51ce410c124a10e0db5e4b97fc2af39 |
|   14 |   19 | aab3238922bcc25a6f606eb525ffdc56 |
|   15 |   21 | 9bf31c7ff062936a96d3c8bd1f8f2ff3 |
|   16 |   31 | c74d97b01eae257e44aa9d5bade97baf |
|   17 |   18 | 70efdf2ec9b086079795c442636b55fb |
|   18 |   21 | 6f4922f45568161a8cdf4ad2299f6d23 |
|   19 |   25 | 1f0e3dad99908345f7439f8ffabdffc4 |
|   20 |   28 | 98f13708210194c475687be6106a3b84 |
|   21 |   29 | 3c59dc048e8850243be8079a5c74d079 |
|   22 |   42 | b6d767d2f8ed5d21a44b0e5886680cb9 |
|   23 |   37 | 37693cfc748049e45d87b8c7d8b9aacd |
|   24 |   24 | 1ff1de774005f8da13f42943881c655f |
|   25 |   39 | 8e296a067a37563370ded05f5a3bf3ec |
|   26 |   39 | 4e732ced3463d06de0ca9a15b6153677 |
|   27 |   28 | 02e74f10e0327ad868d138f2b4fdd6f0 |
|   28 |   30 | 33e75ff09dd601bbe69f351039152189 |
|   29 |   32 | 6ea9ab1baa0efb9e19094440c317e21b |
|   30 |   57 | 34173cb38f07f89ddbebc2ac9128303f |
|   31 |   52 | c16a5320fa475530d9583c34fd356ef5 |
|   32 |   49 | 6364d3f0f495b6ab9dcf8d3b5c6e0b01 |
|   33 |   52 | 182be0c5cdcd5072bb1864cdee4d3d6e |
|   34 |   39 | e369853df766fa44e1ed0ff613f563bd |
|   35 |   56 | 1c383cd30b7c298ab50293adfecb7b18 |
|   36 |   62 | 19ca14e7ea6328a42e0eb13d585e4c22 |
|   37 |   49 | a5bfc9e07964f8dddeb95fc584cd965d |
|   38 |   62 | a5771bce93e200c36f7cd9dfd0e5deaa |
|   39 |   43 | d67d8ab4f4c10bf22aa353e27879133c |
|   40 |   77 | d645920e395fedad7bbbed0eca3fe2e0 |
|   41 |   68 | 3416a75f4cea9109507cacd8e2f2aefc |
|   42 |   76 | a1d0c6e83f027327d8461063f4ac58a6 |
|   43 |   49 | 17e62166fc8586dfa4d1bc0e1742c08b |
|   44 |   57 | f7177163c833dff4b38fc8d2872f1ec6 |
|   45 |   89 | 6c8349cc7260ae62e3b1396831a8398f |
|   46 |   84 | d9d4f495e875a2e075a1a4a6e1b9770f |
|   47 |   82 | 67c6a1e7ce56d3d6fa748ab6d9af3fd7 |
|   48 |   76 | 642e92efb79421734881b53e1e1b18b6 |
|   49 |   52 | f457c545a9ded88f18ecee47145a72c0 |
|   50 |   98 | c0c7c76d30bd3dcaefc96f40275bdc0a |
|   51 |   70 | 2838023a778dfaecdc212708f721b788 |
|   52 |   72 | 9a1158154dfa42caddbd0694a4e9bdc8 |
|   53 |   68 | d82c8d1619ad8176d665453cfb2e55f0 |
|   54 |   60 | a684eceee76fc522773286a895bc8436 |
|   55 |   78 | b53b3a3d6ab90ce0268229151c9bde11 |
|   56 |   66 | 9f61408e3afb633e50cdf1b20de6f466 |
|   57 |   71 | 72b32a1f754ba1c09b3695e0cb6cde7f |
|   58 |   80 | 66f041e16a60928b05a7e228a89c3799 |
|   59 |  115 | 093f65e080a295f8076b1c5722a46aa2 |
|   60 |   77 | 072b030ba126b2f4b2374f342be9ed44 |
|   61 |   95 | 7f39f8317fbdb1988ef4c628eba02591 |
|   62 |   89 | 44f683a84163b3523afe57c2e008bc8c |
|   63 |   85 | 03afdbd66e7929b125f8597834fa83a4 |
|   64 |  114 | ea5d2f1c4608232e07d3aa3d998e5135 |
|   65 |   96 | fc490ca45c00b1249bbe3554a4fdf6fb |
|   66 |   77 | 3295c76acbf4caaed33c36b1b5fc2cb1 |
|   67 |   71 | 735b90b4568125ed6c3f678819b6e058 |
|   68 |   92 | a3f390d88e4c41f2747bfa2f1b5f87db |
|   69 |  108 | 14bfa6bb14875e45bba028a21ed38046 |
|   70 |   78 | 7cbbc409ec990f19c78c75bd1e06f215 |
|   71 |  106 | e2c420d928d4bf8ce0ff2ec19b371514 |
|   72 |   96 | 32bb90e8976aab5298d5da10fe66f21d |
|   73 |  137 | d2ddea18f00665ce8623e36bd4e3c7c5 |
|   74 |  144 | ad61ab143223efbc24c7d2583be69251 |
|   75 |  131 | d09bf41544a3365a46c9077ebb5e35c3 |
|   76 |   79 | fbd7939d674997cdb4692d34de8633c4 |
|   77 |  113 | 28dd2c7955ce926456240b2ff0100bde |
|   78 |   99 | 35f4a8d465e6e1edc05f3d8ab658c551 |
|   79 |  148 | d1fe173d08e959397adf34b1d77e88d7 |
|   80 |  103 | f033ab37c30201f73f142449d037028d |
|   81 |   84 | 43ec517d68b6edd3015b3edc9a11367b |
|   82 |   89 | 9778d5d219c5080b9a6a17bef029331c |
|   83 |  136 | fe9fc289c3ff0af142b6d3bead98a923 |
|   84 |  133 | 68d30a9594728bc39aa24be94b319d21 |
|   85 |   95 | 3ef815416f775098fe977004015c6193 |
|   86 |  108 | 93db85ed909c13838ff95ccfa94cebd9 |
|   87 |  144 | c7e1249ffc03eb9ded908c236bd1996d |
|   88 |  166 | 2a38a4a9316c49e5a833517c45d31070 |
|   89 |  130 | 7647966b7343c29048673252e490f736 |
|   90 |  176 | 8613985ec49eb8f757ae6439e879bb2a |
|   91 |  164 | 54229abfcfa5649e7003b83dd4755294 |
|   92 |  180 | 92cc227532d17e56e07902b254dfad10 |
|   93 |  136 | 98dce83da57b0395e163467c9dae521b |
|   94 |  175 | f4b9ec30ad9f68f89b29639786cb62ef |
|   95 |   98 | 812b4ba287f5ee0bc9d43bbf5bbe87fb |
|   96 |  151 | 26657d5ff9020d2abefe558796b99584 |
|   97 |  168 | e2ef524fbf3d9fe611d5a8e90fefdc9c |
|   98 |  112 | ed3d2c21991e3bef5e069713af9fa6ca |
|   99 |  130 | ac627ab1ccbdb62ec96e702f07f6425b |
|  100 |  124 | f899139df5e1059396431415e770c6dd |
+------+------+----------------------------------+
100 rows in set (0.00 sec)
    
# 注意：测试时可以插入300000条记录
mysql> select count(*) from test1;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.01 sec)


3、在有索引和没有索引的情况下执行查询
1）没有创建索引时查询
mysql> reset query cache;（在mysql8.0中已将重置查询缓存移除）
mysql> explain select num,pass from test1 where id>=50 and id<55;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

2）创建索引后再次查询
mysql> reset query cache;
mysql> explain select num,pass  from test2 where id>=50 and id<55;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test2 | NULL       | range | idIdx         | idIdx | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

4、在有索引和没有索引的情况下新增数据
1）没有创建索引时插入数据
mysql> insert into test3 select * from test1;
Query OK, 100 rows affected (0.01 sec)
Records: 100  Duplicates: 0  Warnings: 0

2）创建索引后再次插入数据
mysql> insert into test2 select * from test1;
Query OK, 100 rows affected (0.00 sec)
Records: 100  Duplicates: 0  Warnings: 0

SQL编程

SQL语法，结构化查询语言，主要用在关系型数据库操作上，但是要注意，SQL语句，是一种编程语言。

• 是否存在变量
• 存在程序控制流程（三大流程）

SQL编程，也是做数据库相关的编程，如定义数据库函数、数据库存储过程、触发器、游标。

触发器（trigger）

触发器（trigger）是一个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，
比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

触发器经常用于加强数据的完整性约束和业务规则等。例如，当学生表中增加了一个学生的信息时，学生的总数就应该同时改变。因此可以针对学生表创建一个触发器，每次增加一个学生记录时，就执行一次学生总数的计算操作，从而保证学生总数与记录数的一致性。

触发器的定义语法

create trigger 触发器名称 before|after insert|delete|update 
ON 表名 FOR EACH ROW
 BEGIN
	-- 触发器对应的代码
	-- 可能有多行代码;
	-- 每行代码需要使用分号;
 END

触发器案例练习（作业）

mysql> create table student2(
    -> id int unsigned auto_increment primary key not null,    
    -> name varchar(50));
Query OK, 0 rows affected (0.01 sec)
	
mysql> insert into student2(name) values('jack');
Query OK, 1 row affected (0.01 sec)

mysql> create table student2_total(total int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student2_total values(1);
Query OK, 1 row affected (0.00 sec)

--将结束符改为$$
mysql> delimiter $$

mysql> create trigger student2_insert_trigger
    -> after insert
    -> on student2 for each row
    -> begin
    -> update student2_total set total=total+1;
    -> end
    -> $$
Query OK, 0 rows affected (0.01 sec)
	$$

作业

索引和视图

学生表：Student (Sno, Sname, Ssex , Sage, Sdept)
学号，姓名，性别，年龄，所在系 Sno为主键
课程表：Course (Cno, Cname,)
课程号，课程名 Cno为主键
学生选课表：SC (Sno, Cno, Score)
学号，课程号，成绩 Sno,Cno为主键

1.用SQL语句创建学生表student，定义主键，姓名不能重名，性别只能输入男或女，所在系的默认值是 “计算机”。
mysql> create table 学生表(
    ->     Sno INT PRIMARY KEY,
    ->     Sname VARCHAR(255) UNIQUE,
    ->     Ssex ENUM('男', '女'),
    ->     Sage INT,
    ->     Sdept VARCHAR(255) DEFAULT '计算机'
    -> );
Query OK, 0 rows affected (0.01 sec)

2.修改student 表中年龄（age）字段属性，数据类型由int 改变为smallint。
mysql> alter table 学生表
    -> modify column Sage smallint;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc 学生表;
+-------+-------------------+------+-----+-----------+-------+
| Field | Type              | Null | Key | Default   | Extra |
+-------+-------------------+------+-----+-----------+-------+
| Sno   | int               | NO   | PRI | NULL      |       |
| Sname | varchar(255)      | YES  | UNI | NULL      |       |
| Ssex  | enum('男','女')   | YES  |     | NULL      |       |
| Sage  | smallint          | YES  |     | NULL      |       |
| Sdept | varchar(255)      | YES  |     | 计算机    |       |
+-------+-------------------+------+-----+-----------+-------+
5 rows in set (0.00 sec)
3.为SC表建立按学号（sno）和课程号（cno）组合的升序的主键索引，索引名为SC_INDEX 。
mysql> CREATE INDEX SC_INDEX ON 学生选课表 (Sno, Cno);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
4.创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩。
mysql> create view stu_info as
    -> select S.Sname,S.Ssex,C.Cname,SC.SCore
    -> from 学生表 as S
    -> join 学生选课表 as SC on S.Sno=SC.Sno
    -> join 课程表 as C on SC.Cno = C.Cno;
Query OK, 0 rows affected (0.00 sec)
mysql> desc stu_info;
+-------+-------------------+------+-----+---------+-------+
| Field | Type              | Null | Key | Default | Extra |
+-------+-------------------+------+-----+---------+-------+
| Sname | varchar(255)      | YES  |     | NULL    |       |
| Ssex  | enum('男','女')   | YES  |     | NULL    |       |
| Cname | varchar(255)      | YES  |     | NULL    |       |
| SCore | smallint          | YES  |     | NULL    |       |
+-------+-------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)