MySQL基础

了解SQL

数据库基础

什么是数据库

数据库简单来说就是一个以某种形式、有组织的方式 存储的数据集合。可以将其看成一个文件柜。

数据库（database） 保存有组织的数据的容器（可以看成一个文件或者是一个组织）

表

当你将资料放入的文件柜中，并不是将它们随便放入到某个抽屉中就完事了，而是将他们放入到文件夹中再放到文件柜中去。

在数据库领域中我们将其称之为表。表示一种结构化的文件，用来存储某种特定类型的数据。

表 是某种特定类型数据的结构化清单

数据库中每个表都是有名字的并且这个名字具有唯一性。

列和数据类型

表由列组成。列中存储着表中的某部分数据。

列 是表中的一个字段，所有的表都是由一个或者多个列组成的。

简单的理解列就像一个excel表格每一列存储着不同的数据，而这个excel就可以称之为表

数据类型（datatype） 所容许的所有的数据类型。每个表和列都有相对应的数据类型，它限制该列中的存储数据。

数据类型可以限制存储在列中的数据类型，数据类型还可以帮助排序，在优化磁盘使用方面提供了极大的帮助。

行

表中的数据是按行保存的，所保存的每一个记录存储在自己的行内。

主键

表中的每一行都应该有自己的标识ID。就行每一个银行卡持卡人都有用一个该银行卡的ID，顾客可以使用此ID进行存取款操作。

主键（primary key） 一列或一组，其值能够唯一区分表中每一行。

唯一标识表中每行的这个列称为主键。主键用来表示一个特定的行。没有主键，更新和删除表中的行就会很麻烦。

表中的任何一列都可以作为主键来使用，需要满足一下条件

• 任意两行都不具有相同的主键值
• 每行都必须有一个主键值（主键列不允许为NULL）

主键通常定义在表的一列上，但这并不是必需的，一可以一起使用多个列作为主键。

使用主键时应该注意几个地方：

• 不更新主键中的值
• 不重用主键中的值
• 不在主键中使用可能会更改的值（名称什么的）

什么是SQL

SQL是结构化查询语言（Structured Query Language）的缩写，是一种专门用来进行与数据库进行通信的语言。

与其他语言不一样的是SQL由很少的词构成，设计SQL的目的是很好的完成一项任务，提供一种从数据库中读写数据的简单有效的方法。

SQL有一下几种有点：

• SQL不是某个特定的数据库供应商专有语言。几乎所有的DBMS都支持SQL。
• SQL简单易学。他的许多语句都由很多描述性很强的英语组成。
• SQL尽管看上去很简单，但它实际上是一种很强力的语言，灵活使用其语言元素。可以进行很多非常复杂和高级的数据。

MySQL简介

MySQL是什么

数据的所有检索、存储、管理和处理实际上是由数据库软件-DBMS（数据库管理系统）完成的。MySQL就是其中的一种。

MySQL已经存在多年，在全世界范围内得到了广泛的使用和安装，究其有一下原因：

• 成本--MySQL是开源的，可以免费使用
• 性能--MySQL执行速度快
• 可信赖--一些知名的公司都在使用MySQL存储数据
• 简单--安装简单容易上手

MySQL实用命令

mysql -u 用户名 -p 密码 --- 可以进入MySQL用户界面

检索数据

select语句

select为检索（查询) 语句，使用select语句必须满足两个条件：

• 想要查询那个
• 从什么地方查

查询单个列

输入

SELECT username FROM t_sys_user;

输出

分析

查看表结构可以看出，当我们使用这条语句时只会查出t_sys_user表中username列的数据，也就是username的全部行的数据。

查询多个列

输入

select username , password  from t_sys_user;

输出

分析

也可以看出用select语句不仅可以检索单个数据也可也检索多个数据，多个数据之间用','隔开，同时我们发现MySQL语句不区分大小写，但是对于有些标识符（如数据库名、表名、列名）可能有些不同。

检索所有列

除了检索有需求的列之外，还可以将表中所用数据检索出来，可以使用'*'来表示

输入

select * from t_sys_user;

输出

分析

如果使用通配符'*'则列出所有的列。列的顺序一般是列在表定义中出现的顺序。但有时候不是这样的，表的模式的变化可能会导致顺序变化。

通配符最好是不要使用，因为通配符会检索不需要的列，这样会影响检索效率和程序性能。

DISTINCT关键字

distinct关键字，指示MySQL返回不同的值，

输入

select DISTINCT id from t_sys_street;

输出

分析

distinct关键字必须放在列名前面。distinct关键字应用于所有列而不仅是前置它的列。

LIMIT关键字

select返回所有匹配的行，它们可能是指定表中的每一行。为了返回第一行或前几行，可以使用LIMIT语句，例子如下：

输入

select short_name from t_sys_street LIMIT 5;

输出

分析

输入limit 5只是输出结果不多于5行

未得到下一个5行，可以指定要检索的开始行和行数；

输入

select short_name from t_sys_street LIMIT 5,5;

输出

分析

LIMIT 5,5指示MySQL从第5行开始查询5行，第一个数字为开始数字，第二个数字表示要检索的行数。

排序检索数据

排序数据

正常情况下MySQL查询出来的数据是没有顺序的，检索出的数据并不是以存粹的随机顺序显示的，如果不排序的话，数据一般在它底层表中出现的顺序显示。

为了明确的排序用select语句检索出的数据，可以使用ORDER BY子句。ORDER BY子句取一个或多个列的名字，据此对输出进行排序。

输入

select short_name from t_sys_street ORDER BY short_name;

输出

分析

order by语句可以对要查询的列以首字母顺序进行排序。

多个数据排序

输入

select short_name,street_name from t_sys_street ORDER BY short_name,street_name;

输出

效果同上，注意用逗号隔开

指定排序方向

排序防线分为正序和倒序（DESC），ORDER BY默认以正序方式排列就是A-Z，

下面演示倒序排列方式：

select short_name from t_sys_street ORDER BY short_name DESC;

多个排序也是同样的，这里就不作演示了。

过滤数据

WHERE子句

在select语句中，数据根据WHERE子句中指定的搜索条件进行检索，WHERE子句在FROM子句之后给出，如下所示：

输入

select short_name from t_sys_street where short_name = '五沟营镇';

输出

where子句将数据进行过滤，数据可以在应用层进行过滤。

在同时使用where和order by时应该把order by放在where之后。

WHERE操作符

使用几个例子：

不匹配检查

输入

select short_name from t_sys_street where short_name <> '五沟营镇';
select short_name from t_sys_street where short_name != '五沟营镇';

输出

范围检查

输入

select id from t_sys_street where id BETWEEN '1' and '5';

输出

在使用between时必须指定两个值-所需范围的低点和高点，并且用and分隔。

通配符

LIKE操作符

通配符就是用来匹配值的一部分的特殊字符。

百分号(%)通配符

最常用的通配符是百分号（%）。在搜索中百分号代表任意字符出现任意次数。

示例如下：

输入

select * from t_sys_street where short_name like '五%';

输出

分析

可以从我们的语句可以看出，我们只是搜索出了五开头的street_name信息，数据库就把所有五开头的信息列了出来，而且每个字符的长度还不一样

通配符在搜索模式中任意位置使用，并且可以使用多个通配符，示例如下：

select * from t_sys_street where short_name like '%一%';

下划线(_)通配符

下划线的用法和百分号基本一致，但是下划线只是匹配一个字符而百分号匹配多个字符。示例如下：

输入

select * from t_sys_street where short_name like '_沟营镇';

输出

通配符使用技巧

通配符的使用确实很方便，但是它是牺牲了一定的效率，它比其他的检索方式更加的浪费时间，建议使用的注意一下技巧：

• 不高过度使用通配符。如果其他的方式可以达到，就不要使用通配符。
• 在确实需要使用时，不要把通配符放在搜索模式前面，避免索引失效而浪费大量时间。
• 注意通配符的位置。如果放错位置，可能不会返回想要的值。

正则表达式

介绍

所有种类的语言、文本编辑器、操作系统都支持正则表达式。正则表达式用正则表达式语言来建立，正则表达式语言是用来完成刚讨论的所有工作以及更多工作的一种语言。

基本字符匹配

示例：

select * from t_sys_street where short_name REGEXP '五沟营'；

输出

除了like被REGEXP替代外，这条语句看起来很像like语句。

or匹配

为搜索两个字符串之一，使用|，示例

select * from t_sys_street where short_name REGEXP '五沟营|东华门'；

结果

数据处理函数

大多的函数支持一下几种函数：

• 用于处理文本串的文本函数。如添加和删除值，大小写转换
• 用于在数值数据上进行算术操作的数值函数。如返回绝对值，进行代数运算
• 用于处理时间和时间值并从这些值中提取特定成分的日期和时间函数。如返回两个日期时间之差。
• 返回DBMS正使用的特殊信息的系统函数。 如返回用户登录信息

文本处理函数

示例

select dict_type,UPPER(dict_type) from t_sys_dict_type ORDER BY dict_type DESC;

输出

UPPER将数据以大写方式呈现。

一下为文本处理常见的函数：

日期和时间处理函数

日期和时间采用相对应的数据类型和特殊的格式存储，以便能快速的处理和有效的排序或过滤，并且节省物理存储空间。

一下为常用的日期和时间处理函数：

测试：

select * from t_sys_dict_type where DATE(create_time) BETWEEN '2018-06-17' and '2020-06-17'；

效果：

其中，使用between来定义时间的范围日期，也可以用年份来查询想要的时间数值，示例如下：

select * from t_sys_dict_type where YEAR(create_time) = 2019 and MONTH(create_time) = 10;

效果：

Year()是从一个日期中返回年份的函数。类似Month()从日期中返回月份。因此where YEAR(create_time) = 2019 and MONTH(create_time) = 10;查询出create_time年份为2019年10月的所有值。

数值处理函数

数值处理函数仅处理数值数据。这些函数一般主要 用于代数、三角或者几何运算，因此没有串或者日期-时间处理函数使用那么频繁。

常用的数值处理函数：

聚合函数

我们需要汇总数居而不需要把他们实际检索出来，为此MySQL提供了专门的函数。MySQL查询可用于检索数据，以便分析和报表生成。这种类型的检索例子有一下几种：

• 确定表中行数
• 获取表中行组的和
• 找出表列的最大值、最小值和平均值。

为了方便这种类型的检索，MySQL给出了五个聚合函数，聚合函数运行在组上，计算和返回单个值的函数。

AVG()函数

AVG()通过对表中的行数计数并计算特定列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可以用来返回特定列或特定行的平均值。

输入：

select AVG(lat) FROM t_sys_area; 

输出：

分析AVG()函数输出列或行的平均值

COUNT()函数

count()函数进行计数，可以用COUNT()确定表中行的数目或符合特定条件的行的数目。

COUNT()函数有两种使用方式：

• 使用count(*)来查询表中行的总数，不管表中的值为空还是非null
• 使用count(column)对特定列中具有值的行进行计算，忽略null值

输入：

select COUNT(*)FROM t_sys_area; 

输出：

结论：count(*)可以查出表中所有的行数

输入：

select COUNT(lat)FROM t_sys_area; 

输出：

结论:count(column)可以查询出指定行的行数

MAX()函数

MAX()指定返回某一列的最大值

输入：

select MAX(lat) FROM t_sys_area; 

输入：

结论：MAX()可以输出指定列的最大数值。

MAX()函数忽略列值为null的行

MIN()函数

MIN()函数的功能为返回列的最小值

输入：

select MIN(lat) FROM t_sys_area; 

输出：

结论：MIN()函数可以输出指定列的最小值，并且忽略列值为null的行

SUM()函数

SUM()函数用来计算指定列值的和

输入：

select SUM(lat) FROM t_sys_area; 

输出：

结论：SUM()函数可以计算指定列值之和，同时也会忽略列值为null的行

聚集不同值

MySQL5版本以后添加了一个新的聚合函数DISTINCT，DISTINCT可以对其他五个聚合函数进行使用：

• 对所有的行执行计算，指定ALL参数或不给参数（因为null是默认行为）；
• 只包含不同的值，指定DISTINCT参数

输入：

select AVG(DISTINCT lat) FROM t_sys_area; 

输出：

分组数据

GROUP BY

分组是在select语句中的group by子句中建立。例子如下：

输入：

select id, COUNT(*)FROM t_sys_area GROUP BY id; 

输出：

结论：使用了group by 子句，就不必指定要计算和估值的每个组了，GROUP BY 子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。

在具体使用GROUP BY 子句前，需要知道一些重要的规定。

• GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
• 如果在GROUP BY 子句中嵌套了分组，数据将在最后规定的分组上进行汇总。
• GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚合函数）如果在SELECT中使用表达式，就必须在GROUP BY中指定相同的表达式。
• 除聚合函数外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
• 如果分组中有一个null值，则null作为一个分组返回。如果列中有多个null值，它们将分为一组。
• GROUP BY必须出现在where之后ORDER BY之前

HAVING

除了能使用GROUP BY 分组数据外，MySQL还支持过滤分组，规定包括那些分组、排除那些分组。为此，MySQL提供了 HAVING子句。HAVING非常类似与WHERE。

如何过滤分组，例子如下

select id, COUNT(*)FROM t_sys_area GROUP BY id HAVING id>=3; 

输出：

分析：这条语句跟上一条几乎一样，只是过滤了id<2的数据

以上所得，WHERE子句在这里并不起作用，因为过滤是基于分组聚集值而不是特定行值。

分组和排序

虽然GROUP BY 和 ORDER BY经常完成相同的工作，但他们是非常不同的，如下显示它俩的区别：

为了说明GROUP BY 和 ORDER BY的使用方法请看以下的例子：

select id, COUNT(*)FROM t_sys_area GROUP BY id HAVING id>=50; select lat, COUNT(*)FROM t_sys_area GROUP BY lat HAVING lat>=50; 

输出：

为了排序我们需要添加ORDER BY子句：

select lat, COUNT(*)FROM t_sys_area GROUP BY lat HAVING lat>=50 ORDER BY lat DESC; 

输出：

分析，使用order by子句使用倒序后就按顺序排列了

SELECT子句顺序

子查询

SELECT查询是SQL的查询。迄今为止，我们前面所看到的都是简单的查询，即从单个数据库表中检索数据的简单查询。

利用子查询进行过滤

我们现在使用一个具有关联关系的数据库表进行一个简单的关联子查询，我们的订单存储在两张表内。对于包含订单号、客户ID、订单时间的每个订单我们存储在orders表中，各个订单的关联信息存储在ordersitems表中。

现在我们假设需要列出物品TNT2的所有客户，应该怎样检索？我们分析一下：

1. 首先检索物品TNT2的所有订单编号；
2. 根据订单编号查询出所有的客户ID；
3. 根据ID返回用户信息

首先，我们先查出TNT2的所有订单编号：

select order_num from orderitems where prod_id = 'TNT2';

输出：

之后，查出订单编号为20005和20007的所有客户ID。

SELECT cust_id from orders where order_num in (20005,20007);

输出

最后，把把两个句子组合成一个子查询语句：

SELECT cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'TNT2');

输出：

总结：在SELECT语句中，子查询语句总是限制性括号内的语句（从内到外），在这个查询中子查询就做了两个操作：

1. 先执行select order_num from orderitems where prod_id = 'TNT2';语句得到订单号；
2. 跟据返回的数值执行SELECT cust_id from orders where order_num in (20005,20007);语句；

现在得到了客户ID，之后检索这些客户信息：

select cust_name,cust_contact from customers where cust_id in (10001,10004);

同样也可以使用子查询：

select cust_name,cust_contact from customers where cust_id in (SELECT cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'TNT2'));

输出：

总结：实际上为了获得客户信息我们执行了三条语句，首先返回订单号，之后根据订单号获得用户ID，再根据ID查出客户信息，SQL最层才是我们需要的信息；由此可见WHERE子句使用子查询可以多层嵌套，没有数目限制。

虽然子查询一般与IN操作符结合使用，但是也可以用等于和不等于进行测试。

作为计算字段使用子查询

使用子查询的另一种方式是计算字段。假设需要customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中，为了获得参数，应进行一下步骤：

1. 从customers表中检索客户列表；
2. 对于检索出的每一个客户，统计其在orders表中的订单总数；

我们可以使用count(*)来进行计算，

select count(*) as  orders from orders where cust_id = 10001;

为了对每一个客户进行计数计算，应该将每一个count(*)看成一个子查询：

select cust_name,cust_state,(select count(*) as  orders from orders where orders.cust_id = customers.cust_id) from customers order by cust_name; 

输出：

总结：这条查询语句对customers表中每个客户返回三列：cust_name,cust_state和orders。orders是一个计算字段，它是由圆括号中的子查询建立的，总共执行了5次，因为检索出了5个客户；

子查询中的WHERE子句与前面使用的有所不同，因为它使用了完全限定列名，

orders.cust_id = customers.cust_id;

这种类型的子查询被称为相关子查询。任何时候只要列名具有多义性，就必须使用相关子查询，如过不是用就会出现一下情况：

select cust_name,cust_state,(select count(*) as  orders from orders where cust_id = cust_id) from customers order by cust_name; 

输出：

总结：这明显是不对的，为什么会出现这样的结果呢，因为两个cust_id一个是属于orders表另一个属于customers表，如果不使用限定列名，cust_id只是指定了orders表中的列名，返回的是orders表中的总数，所以数据之然是不匹配的。

虽然子查询这种构造在SELECT语句中非常的有用，但是必须注意限制有歧义的列名。

组合查询

大多数的SQL查询都是从多个或单个表中返回数据的单条SELECT语句。MySQL允许执行多个查询，并将结果作为单个查询结果集返回。这些组合通常称为并（union）或复合查询（compound query）。

有两种情况才需要符合查询：

• 在单个查询中从不同的表返回类式结构的数据；
• 对单个数据执行多个查询，按单个查询返回数据；

使用UNION

用UNION操作符来进行组合查询，只需要在各条SELECT语句之间放上关键字UNION。

举一个例子，加入需要一个商品价格小于等于5的所有物品的一个列表，而且还想包括供应商1001和1002生产的所有物品。使用UNION来进行查询：

首先先来看单条语句：

select vend_id,prod_id,prod_price from products where prod_price <=5;

输出：

输入：

select vend_id,prod_id,prod_price from products where vend_id in (1001,1002);

输出：

总结：第一条语句检索价格不高于5的，第二条语句检索找出供应商1001和1002生产的所有物品。

组合这两条语句：

select vend_id,prod_id,prod_price from products where prod_price <=5 
union 
select vend_id,prod_id,prod_price from products where vend_id in (1001,1002);

输出：

总结：就是把前两个语句组合在一起使用UNION来进行分隔，并把输出整合成一个结果集。

UNION规则

并查询时非常容易使用的，但是使用时要注意几个规则：

• UNION必须由两条或两条以上的SELECT语句组成，语句之间使用UNION分隔。
• UNION查询中的每一个列、表达式、聚合函数必须相同；
• 列数据类型必须兼容，类型不必完全相同，但是必须是DBMS可以隐式转换的类型。

包含或取消重复行

根据上面的语句我们发现第一条SELECT语句返回了4行，第二条返回了5行，然而使用UNION查询时，只返回了8行。

UNION从查询结果集中自动删除了重复行，这是UNION的默认行为，如果想要改变只需要使用UNION ALL， 例子：

select vend_id,prod_id,prod_price from products where prod_price <=5 union all select vend_id,prod_id,prod_price from products where vend_id in (1001,1002);

输出：

总结：使用UNION ALLMySQL不取消重复的行。

对组合结果进行排序

说到排序那就需要用到ORDER BY，只需要在UNION语句末尾加上ORDER BY，在使用UNION组合查询时，只能使用一条ORDER BY语句，对于结果集，不允许用一种方式排序一部分，又用另一种方式排序另一部分，因此不允许使用多条ORDER BY进行排序。

输出：

select vend_id,prod_id,prod_price from products where prod_price <=5 union select vend_id,prod_id,prod_price from products where vend_id in (1001,1002) order by vend_id,prod_price;

输出：

分析：这条UNION在最后一条SELECT语句使用了ORDER BY子句。虽然ORDER BY子句似乎只是后一条SELECT语句的组成部分，但是实际上MySQL将用它来排序所有的结果集。

数据插入

除了常用的SELECT语句外我们还需要学习其他的语句，下面介绍一下INSERT语句：

顾名思义INSERT就是插入的意思用来插入或添加行到数据表中。插入可以用几种方式来使用：

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

插入完整的行

指定表名和要插入到行的值

输入：

INSERT INTO student VALUES(1,'张三','中国上海市浦东新区','1828321@126.com');

一般insert语句不会产生输出

结论：

虽然这种语法很简单，但是并不安全，应该尽量避免使用。上面的MySQL语句高度依赖表中列定义的次序，而且还依赖其他次序容易获得的信息。即使可得到这些次序，也不能保证下一次表结构变动后个个列保持相同的次序。因此，这样编写MySQL是很不安全的。

编写更加安全的insert语句如下

INSERT INTO student(id,name,address,email) VALUES(2,'李四','上海市杨浦区','123128321@126.com');

输出：

结论：此例子，跟前一个例子完全相同，不同的是在表中指名了每一列的名称，相当于表名中的第一个值制定了values的第一个值，以此类推。

插入多个行

insert可以插入单行，那么也可以插入多行，只需要在每个语句执行时使用分号隔开每个语句就可以了，示例如下：

INSERT INTO student(id,name,address,email) VALUES(3,'老六','上海市杨浦区','123128321@126.com');
INSERT INTO student(id,name,address,email) VALUES(4,'王五','河南省信阳市','109897321@126.com');

输出：

以上是一种方式，也可以使用组合语句只需要使INSERT语句中的列名和次序相同就可以，展示如下：

INSERT INTO student(name,address,email) VALUES('张飞','三国','128321@126.com'),('关羽','蜀国','1231231@qq.com'),('刘备','蜀国','11231231@qq.com');

其中单条INSERT语句有多组值，每一组织用括号括起来，使用逗号分离。

更新和删除数据

更新数据

为了更新或者修改表中的数据，我们可以使用UPDATE语句，可以使用两种方式来更新表中的数据：

• 更新表中的特定行
• 更新表中所有行

注意：

1. 不要省略WHERE，在使用UPDATE时一定要细心。因为稍不注意就会更新表中所有行的数据。
2. UPDATE与安全。限制和控制UPDATE语句的使用。

UPDATE的语句很简单，主要由三部分组成：

• 要更新的表；
• 列名和他们的值
• 确定更新行的过滤条件

举一个简单的例子，客户为10005有了性的Email地址需要更新，展示如下：

update customers set cust_email = 'giles@126.com'where cust_id = 10005;

总结：UPDATE语句总是要以更新的表的名字开始。在这个例子中，要更新的表的名字为customers。SET命令用来将新赋值给被更新的列。SET来设置cust_email列为指定的值：

update customers set cust_email = 'giles@126.com'where cust_id = 10005;

UPDATE语句以WHERE子句结束，他来告诉MySQL指定更新哪一行。

更新多个列的语法稍有不同：

update customers set cust_name='giles',cust_email = 'giles@126.com'where cust_id = 10005;

总结：更新多个值是需要使用一个set命令，每个‘列-值’之间用逗号进行分割。

删除数据

为了从表中删除一个数据，可以使用DELETE语句。可以用两个方式使用：

• 从表中删除特定行
• 从表中删除所有行

DELETE语句同样也是非常的简单，

delete from customers where cust_id = '10006'

总结：很明显这一条语句就是删除customer表中idwei10006的数据，where子句用来过滤。

DELETE语句不需要通配符或列名。DELATE语句是删除整行而不是删除整列，

注意：

1. 删除表的内容而不是删除表。delete语句可以删除表中的行，也可以删除表中所有行，但不能删除整张表；
2. 更快的删除。如果想要删除整张表中的数据，请使用TRUNCATE TABLE语句，它可以完成相同的工作，但是速度会更快；

删除和更新的原则

当我们使用DELETE和UPDATE语句时，使用WHERE子句是非常有必要的，如果我们省略了WHERE，则所有的更新和删除操作会被用到整行，从而造成数据误删和数据整表更新，所以当我们使用更新和删除操作时，应该准许以下几种习惯：

• 除非确山要删除或更新每一行，否则请添加过滤语句；
• 保证每一张表都有主键，否则不同要使用没有过滤条件的更新和删除语句；
• 在使用更新和删除语句前，先使用查询语句进行测试确保过滤条件是正确的；
• 使用强制实施引用完整的数据库，这样MySQL则不允许删除具有与其他表相关联的数据的行；

注意

MySQL没有撤销这一按钮，使用更新和删除务必要小心。

表

创建表

MySQL不仅用于表数据操纵，还可以执行数据库和表的所有操作，包括表的创建和处理。

表的创建方法：

• 使用具有交互式创建和管理表的工具；
• 表也可以直接使用SQL语句操作；

可以使用CREATE TABLE语句。值得注意的是，在使用交互式工具时，实际上使用的是MySQL语句。

为利用CREATE TABLE创建表，必须给出下列信息：

• 新表的名字。在CREATE TABLE 之后给出；
• 表列的名字和定义。用逗号分隔；

CREATE TABLE语句也可能会包括其他关键字或选项，单至少要包括表的名字和列的细节。

输出：

CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

分析：CREATE TABLE之后为表名，括号内第一个为列名，第二个指定列名类型，第三个指定是否为空，PRIMARY KEY (cust_id)指定主键ID，可以看出表定义的所有列都在括号呢，各列之间用逗号隔开。

使用NULL

每个表列或者是NULL列，或者是NOT NULL列，这种状态在创建时由表的定义规定。

例子：

CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;

分析：这条语句创建了orders表，orders包含了三个列，分别是订单号，订单日期和客户ID。所有三个列都需要，因此每个列都含有NOT NULL。这就会阻止插入没有值的列。

AUTO_INCREMENT：

简单来说就是告诉MySQL每增加一行数据这一行就自动增量一般用于自增ID。每一张表只允许一个AUTO_INCREMENT列。

ENGINE=InnoDB：

可以发现每一条创建表的语句都是以ENGINE=InnoDB结束的，与其他的DBMS一样，MySQL有一个具体管理和处理数据的内部引擎，在使用创建表语句时，该引擎具体创建表，而在你使用查询语句或其他数据库处理时，该引擎在内部处理你的请求。

更新表

更新表可以使用ALTER TABLE语句。但是，在理想状态下，当表中存储数据以后，该表就不应该被更新。在表的设计过程中，需要花费大量精力来考虑。

为了使用ALTER TABLE更改表结构，必须给出一下信息，

• 在ALTER TABLE之后指定要更新的表名；
• 对表列进行更新；

假如给一张表添加一个列：

ALTER TABLE customers ADD cust_test CHAR(20);

删除刚刚添加的列：

ALTER TABLE customers DROP COLUMN cust_test;

ALTER TABLE的一种常见的用途是定义外键。一下有几个例子：

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);

ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);

ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

这里，由于要更改4张表，使用了4条语句。为了单个表进行多个修改，可以使用单条ALTER TABLE语句，每个更改用逗号分隔。

复杂的表结构更改一般需要删除过程，它涉及几个步骤：

1. 用新的列布局创建一个新表；
2. 使用INSERT SELECT语句从旧表复制数据到新表；
3. 检验包含所需数据的新表；
4. 重命名旧表
5. 根据需要，重新创建触发器，存储过程，索引和外键；

删除表

删除表可以使用DROP TABLE 语句，

DROP TABLE customers；

注意：

DROP TABLE 是删除表而不是其内容。

重命名

使用RENAME TABLE 重命名一张表

RENAME TABLE customers TO cust2;

重命名多个表

RENAME TABLE customers TO cust2,
		cust4 TO cust3;

只需要使用逗号隔开就行。

视图

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

我们为什么使用视图？

1. 重用SQL语句；
2. 简化复杂的SQL操作。在重写时，可以重用它而不必知道它的查询细节；
3. 使用表的组成部分而不是整张表；
4. 保护数据。可以给用户授予表的特定部分的访问权限而不是整张表；
5. 更改数据格式和表示。视图可以返回与底层表的表示和格式不同的数据；

视图的规则和限制：

1. 与表一样，试图必须唯一命名；
2. 对于可以创建的试图数目没有限制；
3. 为了创建视图必须拥有足够的创建权限；
4. 视图可以嵌套，就是可以从其他视图检索出的数据来构造一个视图；
5. ORDER BY可以用在视图中；
6. 视图不能索引，也不能有关联的触发器和默认值；
7. 视图可以和表一起使用；

使徒的创建：

• 视图用CREATE TABLE语句来创建；
• 使用SHOW CREATE VIEW viewname；来查看创建的视图；
• 使用DROP删除视图；
• 更新视图时，可以先用DROP 再用 CREATE，也可以直接用CREATE OR REPLACE VIEW。

简化联结

视图的最常见的应用之一时隐藏复杂的SQL，这通常都会涉及到联结。例子：

CREATE VIEW productcustomers AS SELECT cust_name,cust_contact,prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;

总结:创建了一个productcustomers的视图，它连接了三张表，以返回订购了任意产品的所有客户的列表。

为了检索订购了TNT2产品的客户，如下展示：

select cust_name,cust_contact from productcustomers where prod_id = 'TNT2';

输出：

总结：这条语句就是直接查询productcustomers视图。

使用视图重新格式化检索出的数据

输入：

select Concat(RTrim(vend_name),'(',RTrim(vend_country),')')
	AS vend_title
	from vendors
	order by vend_name;

输出：

现在，假如经常需要这个格式的结果。不必在每次需要时执行联结，创建一个视图没次需要时创建它。

create view vendorlocations AS 
	select Concat(RTrim(vend_name),'(',RTrim(vend_country),')')
		AS vend_title
		from vendors
	order by vend_name;

	select * from vendorlocations;

输出：

过滤不需要的数据

例如定义一个视图，过滤没有Email的数据：

create view custmaillist AS 
SELECT cust_id,cust_name,cust_email
from customers
where cust_email is not null;

select * from custmaillist;

输出：

更新视图

通常，视图是可更新的。更新一个视图将更新其基表。对视图的更改实际上就是对基表的更改。

但是，并非所有的视图都是可更新的。如果MySQL不能正确的确定被更新的基数据，则不允许更。这实际上就是如果视图定义由以下操作，则不能更新：

• 分组；
• 联结；
• 子查询；
• 并；
• 聚合函数；
• DISTINCT;
• 到出列；

2020-06-22 17:18:00 先到这里

存储过程

目前使用的大多数SQL语句都是针对一个或者多个表的单条语句。并非所有的操作都那么简单，经常会有一个完整的操作需要多条语句才能完成。例如，考虑以下情况：

• 为了处理订单，需要核对以保证库存中有相应的物品；
• 如果库存有物品，这些物品需要预定以便不讲他们再卖给别人，并且要减少可用物品你的数量以获得正确的库存；
• 库存中没有的物品需要订购，这需要与供应商进行交互；
• 关于哪些物品入库和那些物品退订，需要通知相应的客户；

这显然不是一个完整的例子，但足够我们来理解。

那么如何编写代码？可以单独编写每条语句，并根据结果有根据的执行下一条语句。在每次需要处理这个结果时，都必须做这些工作。

可以创建存储过程，存储过程简单来说就是为以后的使用而保存的一条或多条MySQL语句的合集。可以将其视为批处理文件，但它的作用不仅限于批处理。

为什么使用存储过程？

为什么要使用存储过程，下面列出一些主要的理由：

• 通过把处理封装在容易使用的单元中，简化复杂的操作；
• 由于不要求建立一系列处理步骤，这保证了数据的完整性，如果所有开发人员和应用程序都使用统一存储过程，则所使用的所有代码都是相同的。
• 简化对变动的管理。如果表名、列名或业务逻辑有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化；

这一点的延伸性就是防止错误。需要执行的步骤越多，出错的可能性就会越大。防止错误保证了数据的一致性。

• 提高性能。因为使用存储过程比单独使用的SQL语句要快；
• 存在一些只能在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写灵活性更强的代码；

换句话说，使用存储过程的三个好处，即简单、安全、高性能。显然他们都很重要。不过，再将SQL代码转换为存储过程前，也必须知道它的一些缺陷。

• 一般来说，存储过程的编写比一般的SQL语句复杂，编写存储过程需要更高的技能，丰富的经验；
• 你可能没有创建存储过程的权限。许多数据库管理员限制创建存储过程的权限，允许用户使用存储过程，但不允许他们创建存储过程；

执行存储过程

MySQL称存储过程的执行为调用，因为MySQL执行存储过程的语句为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。请看以下的例子：

CALL productpricing(@pricelow,
		@pricehigh,
	       	@priceaverage);

分析：其中，执行名为productpricing的存储过程，他计算并返回产品的最低、最高和平均价格。

存储过程可以显示结果也可以不显示结果

创建存储过程

编写一个存储过程是一个微不足道的事情。来一个demo-一个返回产品平均价格的存储过程。

create procedure productpricing()
begin
	select avg(prod_price) as priceaverage
	from products;
end;

分析:我们创建了一个名为productpricing()的存储过程，使用create procedure productpricing()来定义语句。如果存储过程接受参数，他们将在（）内展示出来。此存储过程没有参数，但是()仍然需要。begin和end来限定存储体过程，过程本是仅是一个简单的select语句。

那么如何使用这个存储过程？如下：

call productpricing();

输出：

分析：call productpricing();执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数，所以存储过程名后需要有()符号。

删除存储过程

存储过程在被创建以后，被保存在服务器上以供使用，直至被删除。删除命令从服务器中删除存储过程。

删除刚才创建的存储过程

drop procedure productpricing;

分析：这条语句用来删除存储过程，注意没有使用后面的括号。如果指定的删除存储过程不存在时将会报出一个错误。

使用参数

productpricing只是一个简单的存储过程，他简单的显示了select语句的结果。一般存储过程并不显示结果，而是把结果返回给你指定的变量。

这里就不得不提一下变量了：变量就是内存中的一个特定的位置，用来存储临时数据。

以下是productpricing的修改版本，以前的需要先删除，否则不能创建：

create procedure productpricing(
out pl DECIMAL(8,2),
out ph DECIMAL(8,2),
out pa DECIMAL(8,2)
)
begin
	select min(prod_price)
	into pl
	from products;
	select max(prod_price)
	into ph
	from products;
	select avg(prod_price)
	into pa
	from products;
end;

分析：此存储过程接受三个参数：pl存储产品最低价格，ph存储最高价格，pa存储平均价格。每个参数必须具有指定的类型，这里使用十进制。关键之out指出相应的参数用来从存储过程传出一个值。MySQL支持in（传递给存储过程）、out(从存储过程中传出)、inout（对存储过程传入和传出）类型的参数。

为此调用修改过的存储过程：

CALL productpricing(@pricelow,
			@pricehigh,
			@priceaverage); 

分析：由于此存储过程要求三个参数，因此必须传递三个参数，所以这条语句给出了三个参数。他们是存储过程将保存结果的三个变量的名字。

再调用时发现并不显示数据。为了显示结果可以使用select：

select @pricelow,@pricehigh,@priceaverage;

输出：

下面这个例子，这次使用IN和OUT参数。ordertotal接受订单号并返回该订单的合计：

CREATE PROCEDURE ordertotal(
	IN onumber INT,
	OUT ototal DECIMAL(8,2)
)
BEGIN
	SELECT SUM(item_price*quantity)
	from orderitems
	where order_num = onumber
	INTO ototal;
END;

onumber定义为IN，因为订单号被传入存储过程。ototal定义为OUT，因为要从存储过程返回合计。SELECT语句使用这两个参数，WHERE子句使用onumber选择正确的行，INTO使用ototal存储合计。

调用新的存储过程：

call ordertotal(2005,@total);

给ordertotal传递两个参数；第一个参数为订单号，第二个参数为包含计算出来的合计的变量名。

显示：

select @total

检查存储过程

可以使用show create procedure 语句：

show create procedure productpricing;

为了获得包括何时、谁创建的等详细信息可以使用show procedure status productpricing;

游标

MySQL检索返回一组成为结果集的行。这组返回的行都是与SQL语句相匹配的行。

有时需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标是一个存储在MySQL服务器上的数据库查询，它不是一条select语句，而是被改语句检索出来的结果集。

游标主要应用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据做出浏览或更改。

使用游标的几个步骤：

• 在使用游标前必须先声明它。这个过程实际上并没有检索数据，他只是定义要使用的select语句；
• 一旦声明后，必须打开游标以供使用。这个过程用前面定义的select语句检索出来实际数据。
• 对于填有数据的游标，根据需要取出各行；
• 在结束游标使用时，必须关闭游标；

在声明游标后，可根据需要频繁的打开和关闭游标。再打开游标后可根据需要频繁的执行取出操作。

创建游标

游标使用declare语句创建。declare命名游标，并定义相对应的select语句，根据需要带where或其他子句。例子如下：

create procedure processorders()
begin
	declare ordersnumbers cursor
	for
	select order_num from orders;
end;

分析：这个存储过程并没有做很多的事情，declare语句用来定义和命名游标，因为这里有ordersnumbers。存储过程处理完成后，游标就会消失。

在定义游标后，可以打开它。

打开和关闭游标

游标有open cursor语句来打开。

open ordersnumbers;

在处理open语句时执行查询，存储检索出的数据以共浏览和滚动。

右边处理完成后，使用close cursor来关闭游标：

close ordersnumbers;

close释放所有游标使用的资源和内存，因此在每个游标都不再需要时都应该关闭。

下面是前面例子的修改版本：

create procedure processorders()
begin
	declare ordersnumbers cursor
	for
	select order_num from orders;
	open odersnumbers;

	close ordersnumbers;
end;

这个检索存储过程声明、打开和关闭一个游标。但对检索出的数据什么也没做。

使用游标数据

在一个游标被打开后，可以使用fetch语句分别访问它的每一行。fetch指定检索什么数据，检索出来的数据存储在什么地方。它还向前移动游标中内部行指针，使下一条fetch语句检索下一行。

第一个例子从游标中检索单个行

create procedure processorders()
begin

	declare o int;

	declare  ordersnumbers cursor
	for
	select order_num from orders;

	open ordersnumbers;

	fetch ordersnumbers into o;

	close ordersnumbers;
end;

其中fetch用来检索当前行的order_num列到第一个名为o的局部声明变量中。对检索出的数据不作任何处理。

下一个例子，循环检索数据，第一行到最后一行：

create procedure processorders()
begin

	declare done boolean default 0;
	declare o int;

	declare  ordersnumbers cursor
	for
	select order_num from orders;
	declare continue handler for sqlstate '02000' set done=1;

	open ordersnumbers;

	fetch ordersnumbers into o;

	close ordersnumbers;
end;

与前一个例子不一样的是，这个例子的fetch是在repeat中，因此他反复执行知道done为真。为使它起作用，用一个declare 0定义变量done。只有使用

declare continue handler for sqlstate '02000' set done=1;

句子时，done才能在结束时被设置为真。

为了把这些内容组织起来，下面给出游标在存储过程中更详细的例子

create procedure processorders()
begin

	declare done boolean default 0;
	declare o int;
	declare t decimal(8,2);

	declare  ordersnumbers cursor
	for
	select order_num from orders;
	declare continue handler for sqlstate '02000' set done=1;

	create table if not exists ordertotals
	(order_num int,total decimal(8,2));

	open ordersnumbers;

	repeat

	fetch ordersnumbers into o;

	call ordertotal(o,1,t);

	insert into ordertotals(order_num,total)
	values(o,t);

	until done end repeat;

	close ordersnumbers;
end;

此存储过程还在运行中创建了一个新表ordertotals。这个表将保存存储过程中生成的结果。fetch像以前一样取每个order_num，然后call执行另一个存储过程。最后insert保存每个订单的订单号和合计。

更新时间2020-06-23 01:35:00明天继续更新！！！

触发器

MySQL语句在需要的时候被执行，存储过程也是如此。但是，如果你要某条语句在发生某件事情的时候自动执行该怎么办？假如：

• 添加一个客户到某个数据库表中时；
• 每当销售出去一件商品，需要在库存表中减去时；

所有的例子都是一个特点就是需要改动表时自动处理。这就是触发器。触发器是MySQL响应以下任意语句而自动执行的一条SQL语句：

• DELETE
• INSERT
• UPDATE

其它语句不支持触发器。

创建触发器

创建触发器时，需要4条信息：

• 唯一的触发器名；
• 触发器关联的表；
• 触发器应该相应的活动；删除、修改、添加；
• 触发器何时执行；

触发器用CREATE TRIGGER语句创建。

create trigger newproduct after insert on products
for each row SELECT 'Product added';

创建一个叫newproduct的触发器，触发器可以在一个操作发生之前或之后执行，这个触发器降至insert语句执行之后执行。for each row 对每个插入行执行。

删除触发器

删除触发器的语法就是DROP TRIGGER

DROP TRIGGER newproduct;

触发器是不能更改和覆盖的。为了修改一个触发器，必须先删除它，然后再创建它。

触发器这里请详情看这里*MySQL5.7参考手册触发器*

结语

这个博客呢到这里就结束了，其中还有一些东西没有写，比如，事务处理啊什么的，这上面还有很多不足的，就请大家多多指出来啦，同样的我这里还有好多资料什么的，大家需要的话可以来找我哈QQ:1778058380;

最后，感谢您抽出宝贵的时间来看我的博客，如果对您有帮助那真是我天大的荣幸！！