【测试开发学习历程】MySQL增删改操作 + 备份与还原 + 索引、视图、存储过程

前言：

SQL内容的连载，到这里就是最后一期啦！

如果有小伙伴要其他内容的话，我会追加内容的。（前提是我有学过，或者能学会）

接下来，我们就要开始python内容的学习了 ~ ~ 

目录

1 MySQL增删改操作

1.1 数据添加操作

1.1.1 插入完整的行

1.1.2 插入多行

1.2 数据更新操作

1.3 数据删除操作

1.4 清空表操作

1.5 SQL语言的分类

1.5.1 DML

1.5.2 DDL

1.5.3 DCL

2 备份与还原

2.1 备份数据库与表

2.2 还原数据库与表

3 索引、视图、存储过程

3.1 索引

3.1.1 普通索引

3.1.2 唯一索引

3.2 视图

3.2.1 创建视图

3.3.2 删除视图

3.3 存储过程

---

1 MySQL增删改操作

1.1 数据添加操作

使用insert into语句来完成插入操作，插入操作可以采用下列方式：

• 插入完整的行；
• 插入行的一部分；
• 插入多行；
• 插入某些查询的结果。

1.1.1 插入完整的行

格式为：insert into 表名 (列名) values (各个列的值);

在插入行时，MySQL将用values列表中的相应值填入列表中的对应项。values的第一个值对应于第一个指定的列名。因为提供了列名，values必须以其指定的次序匹配指定的列名，不一定按照各个列出现在实际表中的次序，优点是：即使表的结构改变，此insert语句仍然能正确使用；

插入语句中可以省略列名，直接写成insert into 表名 values (各个列的值);但这种情况下必须确保所有列都插入数据。

1.1.2 插入多行

例中单条insert语句中有多组值，每组值用一对圆括号括起来，用逗号分隔。

insert可以插入由select查询出来的值，该方法由一条insert语句和一条select语句组成。

格式：insert into 表A （表A列1，表A列2） select 表B列1，表B列2 from 表B

1.2 数据更新操作

为了更新表中的数据，可使用update语句；

update语句可以更新表中特定的行，也可以更新表中所有的行；

格式：update 表名 set 列名=XXX where YYY；

注意：update语句如果后面不跟where语句的话，将修改表中所有的行，使用的时候需要小心，以免产生错误的修改。

1.3 数据删除操作

1.4 清空表操作

从一个表中删除数据，使用delete语句，delete的使用方法有以下两种：

• 从表中删除特定的行（通过where子句指定条件）；
• 从表中删除所有的行（不带where子句）

格式：delete from 表名 where XXX;

删除表中特定的行，通过使用where子句来指定删除哪一行，比如删除customers表中ID为1006的用户：delete from customers where cust_id=1006;

注意：数据库的删除操作属于高危操作，删除前最好备份相应的表，备份命令 create table products_bak select * from products;

1.5 SQL语言的分类

SQL语言可分为两类：DML（数据库操作语言）和DDL（数据库描述语言）

1.5.1 DML

  SELECT   - 从数据库表中获取数据

  UPDATE   - 更新数据库表中的数据

  DELETE FROM  - 从数据库表中删除数据

  INSERT INTO   - 向数据库表中插入数据

1.5.2 DDL

  CREATE DATABASE   - 创建新数据库

  ALTER DATABASE   - 修改数据库

  DROP DATABASE  - 删除数据库

  CREATE TABLE   - 创建新表

  ALTER TABLE   - 变更（改变）数据库表

  DROP TABLE   - 删除表

  CREATE INDEX   - 创建索引（搜索键）

  DROP INDEX   - 删除索引

1.5.3 DCL

  GRANT   - 授权

  REVOKE   - 取消授权

2 备份与还原

2.1 备份数据库与表

备份数据库

mysqldump -u root -p 数据库名 >database.sql

 备份表

mysqldump -u root -p 数据库名  表名 >table.sql

2.2 还原数据库与表

source进入mysql命令窗口

mysql -u用户名 -p密码；
create database newdb；#创建一个要导入的数据库
use newdb;
source data.sql;

3 索引、视图、存储过程

3.1 索引

索引：创建索引可以提高数据查询的速度，但会降低增删改数据的速度。

3.1.1 普通索引

这是最基本的索引，它没有任何限制。

#创建索引cust_index

CREATE INDEX cust_index on customers(cust_name);

3.1.2 唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

CREATE UNIQUE INDEX cust_name_index ON customers(cust_name);

查看customers表的索引

SHOW INDEX FROM customers;

删除索引cust_index

DROP INDEX cust_index ON customers;

数据库建立索引常用的规则如下：

• 表的主键、外键必须有索引；
• 数据量大的表应该有索引；
• 经常与其他表进行联结的表，在联结字段上应该建立索引；
• 经常出现在Where子句中的字段，应该建立索引；

3.2 视图

视图：视图是虚拟的表。使用视图可以重用SQL语句，简化复杂的SQL操作。

#查询购买了TNT2的客户的姓名、联系人

SELECT cust_name,cust_contact 
FROM orderitems 
INNER JOIN orders ON orderitems.order_num=orders.order_num 
INNER JOIN customers ON orders.cust_id=customers.cust_id AND prod_id='TNT2';

3.2.1 创建视图

CREATE VIEW productcustomers AS SELECT 
cust_name,cust_contact,prod_id 
FROM orderitems 
INNER JOIN orders ON orderitems.order_num=orders.order_num 
INNER JOIN customers ON orders.cust_id=customers.cust_id;

查询购买了TNT2的客户的姓名、联系人

SELECT cust_name,cust_contact FROM productcustomers WHERE prod_id='TNT2';

3.3.2 删除视图

DROP VIEW productcustomers;

3.3 存储过程

存储过程：一组为了完成特定功能的SQL 语句集，用户可以通过调用存储过程来实现相应的操作。好处是简化复杂的操作。

#创建存储过程productpricing，这个存储过程不带参数
CREATE PROCEDURE productpricing()
BEGINSELECT AVG(prod_price) as priceaverageFROM products;
END;#调用存储过程productpricing
CALL productpricing();

#创建一个带参数的存储过程,OUT表示参数是从存储过程中传出来的
CREATE PROCEDURE productprice(OUT pricemin DECIMAL(8,2),OUT pricemax DECIMAL(8,2),OUT priceavg DECIMAL(8,2)
)
BEGINSELECT MIN(prod_price) INTO pricemin FROM products;SELECT MAX(prod_price) INTO pricemax FROM products;SELECT AVG(prod_price) INTO priceavg FROM products;
END;#调用存储过程
CALL productprice(@pricemin,@pricemax,@priceavg);
SELECT @pricemin,@pricemax,@priceavg;

#调用存储过程
CALL productprice(@pricemin,@pricemax,@priceavg);
SELECT @pricemin,@pricemax,@priceavg;#查看所有的存储过程
SHOW PROCEDURE STATUS;