mysql详解

一、了解SQL

1.1 数据库基础

1.1.1 什么是数据库

  数据库是保存有组织数据的容器，我们一般使用的mysql等其他软件，其实与我们交互的是DBMS（数据库管理系统），并不是数据库本身。

1.1.2 表

  表是一个某种特定类型数据的结构化清单，在数据库中，表名必须是唯一的，所谓唯一是数据库名+表名的结合是唯一的，对于一个表，会有一些信息来规定该表的数据如何存储，数据如何分解等。描述这些信息的就是模式。

1.1.3 列和数据类型

  列是表的一个字段，可以类比于Excel的表格，每个列都有规定的数据类型，所谓数据类型就是允许存储的数据种类。

1.1.4 行

  行就是类似于Excel的表格一样，每一行可以看做是一条记录，比如：

id	name
1	user
2	admin

  其中，第一行代表着id为1，名为user的记录。

1.1.5 主键

  主键就是能够区分每个行（记录）的唯一值，不可重复，主要为了能快速检索出想要的数据，比如想要id是1的行，那么就能很快的找到。注：主键可以是一列，也可以是多列，如果是多列就必须保证多个值得组合唯一。

1.2 什么是SQL

  SQL是专门设计出用于数据库通信的语言，语法简洁，而且大部分DBMS系统都支持，只不过可能有些差异，有着强制规定的语法。

二、MySQL

2.1 什么是mysql

  因为我们使用DBMS系统与数据库进行通信，所以MySQL就是其中的一款DBMS软件，它简单易用，性能较高且免费开源。

  DBMS分为两类，一种是基于共享文件系统的DBMS，主要用于桌面用途，不适合高端应用。另一种是基于客户机-服务器的DBMS（如：MySQL、Oracle等），用户通过使用安装在计算机上的客户机向服务器软件发送请求，由服务器软件处理请求并将数据返回给我们，对于mysql客户机而言，一般有dos终端命令、其他可视化软件（如：Navicat等）或其他如java等语言程序。

2.2 MySQL工具

  所谓MySQL工具就是我们使用的客户机应用，常用的有如下三个。

2.2.1 命令行

  每个mysql都会安装一个命令行工具，但是有时需要我们进行用户认证，如下命令：
mysql -u username -p password -h localhost -P 3306
  其中，username是用户名，password是密码，localhost是主机名，3306是端口号。
  如上述连接到服务之后就可以使用了，使用时每个语句需要使用；来结束，按enter不会执行，因为他会认为你没有写完命令。

2.2.2 其他

  其他的比如Navicat，SQLyoug等可以到官网去学习如何使用，这里不再介绍，本文主要使用命令行。

三、使用MySQL

3.1 连接

  由于数据库具有一些创建、删除等具有权限要求的操作，因此需要用户进行认证与授权，一般需要主机名（本地服务是localhost）、端口号（默认3306）、用户名和密码。

3.2 选择数据库

  在用户第一次连接到数据库服务时，需要选择使用哪一个数据库，使用如下命令：
use 数据库名；注：use是关键字，我们在建表时不可使用这些关键字命名，可能会造成预期之外的错误。

3.3 了解数据库和表

  当我们选择数据库时，如果不知道有哪些可以使用，可以使用如下语句来显示数据库信息列表：
show databases；
  进入数据库后，选择表也一样，使用如下命令显示表信息列表：
show tables；
  对于show这个关键字还有一些其他的命令：

• show status；用于显示服务器状态信息
• show create 数据库名和show create 表名，分别显示创建数据库或表时的SQL语句。
• show grants；显示用户的权限。
• show errors 和show warnnings；显示服务器错误或警告信息。

四、检索数据

4.1 select语句

  select关键字用于各种检索数据的行为，下面来详细讲解。

4.2 检索单个列

  如下语句：select 列名 from 表名；
该语句表示从一个表中检索一个列的所有行的信息。

4.3 检索多个列

  检索多个列即在每个列之间使用逗号分隔，如：
select 列1，列2，列3 from 表名；

4.4 检索所有列

  如下：select * from 表名；
注：一般不要使用通配符，除非整个表的所有列都需要。

4.5 检索不同的行

  在检索某一个列时，返回的可能是有重复值得一列，但我们不想要重复值出现该怎么办，select distinct 列 from 表；
关键字distinct表示去除重复的值。

4.6 限制结果

  我们在分页查询的时候可能不想返回所有数据，而是只要有数的几行，因此可以使用limit关键字：
select 列 from 表 limit 开始行，需要的行数；
如果limit后只有一个数，那么就代表从第一行开始检索特定的行数。

4.7 使用完全限定的表名

  如同java的类一样，都有一个完全限定的类名，当我们在检索数据时可能需要这样确定的表或列，如下：
select 表名.列名 from 数据库名.表名

五、排序检索数据

5.1 排序数据

  首先了解一下什么是子句，SQL语句都是由子句组成，例如前面的from …就是一个子句，在我们检索数据时，默认是无顺序的返回，当我们需要排序显示的时候，需要使用order by 子句。
例如：select 列 from 表 order by 列（这个列可以是检索的列，也可以是非检索的列）

5.2 按多个列排序

  在对多个列进行排序的场景也很常见，如：检索用户时可以先按姓检索，再按名检索，如下列：
select 列1， 列2 from 表 order by 列1，列2；
就是将检索的结果先按列1排序，再按列2排序。

5.3 指定排序方向

  排序时默认是升序，但也可以使用关键字指定为降序，如下：
select 列1，列2 from 表 order by 列1 desc，列2 asc；
  上述语句意为，将结果先按列1降序后按列2升序排序。

六、过滤数据

6.1 使用where子句

  有时候在检索数据的时候，我们只想要某个特定的行，比如：id为1或name为james等，这时需要过滤数据，在MySQL中使用where子句来过滤数据，如：
select * from 表 where id = 1，代表检索表中id为1的所有列。
  注：对于where和order by一同使用时，需要将order by放在后面，道理很简单，order by是对最终结果进行排序，因此需要在过滤完成以得到最终数据后再排序。

6.2 where子句操作符

  上述只用了=操作符，余下的还有：

操作符	说明
=	等于
<>	不等于
！=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between	在指定的两个值之间

6.2.1 检查单个值

//检查id为1的值
select id,price from user where id = 1
//检查id大于等于1的值
select id,name,price from user where id >= 1

6.2.2 不匹配检查

//检查id不为1的值
select name,id from user where id <> 1
select name,id from user where id != 1

6.2.3 范围值检查

//检查价格在5到10元之间的值
select price,id,name from user where price between 5 and 10

6.2.4 空值检查

//对于空值null不是0，也不是空串，SQL有检查空值的语句，下面语句是检查出user表中price字段为null的数据
select * from user where pirce is null

七、数据过滤

7.1 组合where子句

  上述的例子中过滤条件都只是一个，SQL也支持多个条件组合。使用and或or关键字来组合。

7.1.1 and和or关键字

//下面语句是从user表中检查出id小于10且id大于5的值
select price,name,id from user where id < 10 and id > 5
//下面是从user表中检查id小于5或id大于10的值
select price,name,id from user where id < 5 or id >10

7.1.2 计算次序

  在MySQL中and操作符的计算次序在or之前，看下例子：

//这个例子中在检索的时候先计算and条件，最后合并or条件
select price,id,name from user where id < 10 or id >5 and id < 8 

  注：由于and和or的顺序不同，我们可以使用( )来强制顺序，如：

//如下就是先执行括号里的条件
select * from user where (id = 1 or id <10) and id > 20

7.2 in操作符

  上述的( ) 还有一个作用，就是配合in操作符来指定值得范围

//下面例子是从表user中检查id在12或13或15中任何一个满足的值
select id,price,name from user where id in (12,13,15)

7.3 not操作符

  顾名思义，not是否定跟在他后面的条件的，如：

//下面表示从user检查id不在12、13、15中的值
select * from user where id not in (12,13,15)

  注：在mysql中，not可以与in、between以及exists一起使用。

八、用通配符过滤

8.1 like操作符

  先来介绍两个概念。

• 通配符：用来匹配值得一部分
• 搜索模式：由字面值、通配符或两者的结合组成的搜索条件

  注：在搜索子句中使用通配符必须结合like关键字使用

8.1.1 %通配符

  在MySQL中%表示任何字符出现任何次数，如下：

//表示匹配name以luo开头的所有
select * from user where name like 'luo%'
//表示以luo结尾的所有
select * from user where name like '%luo'
//表示name包含luo的所有
select * from user where name like '%luo%'

  注：上述常用在模糊查询的时候，且不能匹配null

8.1.2 _通配符

  与%类似，但_匹配单个的任意字符，而且只能是一个。

//下例只能匹配name是aluo、bluo、我luo、1luo等
select * from user where name like '_luo'

8.2 使用通配符的技巧

  相比于其他的搜索，通配符是效率最慢的，因为他是在所有里面逐一检查匹配，下面给出一些使用通配符的建议：

• 如果其他方式能达到目的，就尽量不适用通配符
• 在使用时除非必要，否则不要用在搜索的开始处
• 注意通配符的位置

九、使用正则进行搜索

9.1 正则表达式介绍

  正则表达式用来匹配文本的特殊的串，目前所有的文本编辑器、操作系统和编程语言都支持。可以匹配如电话号、邮箱等特殊格式的文本内容，是开发中的必备技能。

9.2 使用MySQL正则

  MySQL用where子句对正则进行了初步的支持，只是正则的一个子集，并不是支持所有的正则、

9.2.1 基本的字符匹配

  先看一个简单的例子

/*
下面两个例子是分别用正则和like进行匹配，会发现like可能会不返回值。
因为，like匹配的是整个列，在不使用通配符的情况下是不会返回数据的，而regexp在列值内进行匹配，匹配到之后就会返回相应的行数据
*/
select * from user where name like '1000'
select * from user where name regexp '1000'

9.2.2 进行or匹配

  在正则中，使用|来代替SQL的or操作符，如下：

//下面是匹配user表中id为1或2的值
select * from user where id regexp '1 | 2'

9.2.3 匹配几个字符之一

  在正则中匹配几个特殊字符中的一个，类似于SQL的in操作符，如下：

//下面语句将匹配id是1luo、2luo或3luo
select * from user where id regexp '[123]luo'

9.2.4 匹配范围

  在regexp中也可以匹配代表范围的语句，如下：

//下面语句将匹配id为1luo、2luo、3luo，[1-3]代表1到3的范围
select * from user where id regexp '[1-3]luo'

9.2.5 匹配特殊字符

  在regexp如 . 字符是代表任意字符的意思，但是如果我们只想匹配这 . 的本身时，需要使用转义字符\。如下：

//下面语句将匹配name是.的值
select * from user where name regexp '\\.'

  注：如此还有很多正则的用法，就不一一陈述了。

十、创建计算字段

10.1 计算字段

  何为计算字段，假如我们需要的数据是分别在表中不同列的数据拼接而得到的组合值，我们使用的表的原字段就不合适了，因此计算字段得以发挥。

10.2 拼接字段

  假设有一个user表，有name和age字段，但业务需求是返回形如name( age )的数据，此时就需要拼接了，使用concat函数，如下：

//下面是将user中id为1的数据拼接为name（age）的格式返回
select concat(name,'(',age,')') from user where id = 1

  注：在MySQL中拼接是函数concat( )实现的，但其他DBMS中是用+或||实现的。
  除了concat函数，mysql还有很多函数，如trim、Rtrim和Ltrim，用于去除字符串两边、右边和左边空格的，如下：

//下面是将user表id为1的name和age字段去除两边空格并拼接为name(age)格式的语句
select concat(trim(name),'(',trim(age),')') from user where id = 1

  使用别名，上述可以看到检查出来的结果只是一个值，但是我们如果需要使用列名去引用时，需要使用别名，关键字是as，如下：

//下面语句将查询的结果起一个title的别名，他可以被其他客户机引用，就像是实际的列一样
select concat(name,'(',age,')') as title from user where id = 1

10.3 执行算数计算

  同其他语言一样，SQL也支持简单的算数运算，如：

操作符	说明
+	加
-	减
*	乘
/	除

//下面是返回user中id等于1的age和id相乘的结果
select age*id from user where id = 1

十一、使用数据处理函数

11.1 函数

  与其他大多数计算机语言一样，SQL支持函数，类似trim( )等，但是需要注意的是，函数的可移植性不强，所以当决定使用函数后最好进行注释，以便维护。

11.2 使用函数

  大多数DBMS系统都包含以下函数：

• 用于处理文本的文本函数
• 用于数值计算操作的数值函数
• 用于处理时间日期并从中提取特定成分的时间日期函数
• 返回当前DBMS正使用的特殊信息的系统函数

11.2.1 文本处理函数

  看下例：

//下面利用upper函数将查询的name信息转换为大写
select upper(name) as newName,id from user where id = 1

  如上所述的其他文本函数如下：

函数	说明
left( )	返回串左边的字符
length( )	返回串的长度
locate( )	找出串的一个子串
lower( )	将串转为小写
ltrim( )	去掉串左边的空格
right( )	返回串右边的字符
rtrim( )	去掉串右边的空格
soundex( )	返回串的soundex值
substring( )	返回子串的字符
upper( )	将串转为大写

11.2.2 日期和时间处理函数

  对于日期格式来说也是常用的数据，因此有时需要我们做些处理以得到想要的部分，如下常见的函数：

函数	说明
addDate( )	增加一个日期( 天、周等 )
addtime( )	增加一个时间（时、分等）
curdate( )	返回当前日期
curtime( )	返回当前时间
date( )	返回日期时间的日期部分
datediff( )	计算两个日期之差
date_add( )	高度灵活的日期运算函数
date_format( )	返回一个格式化的日期或时间串
day( )	返回一个日期的天数部分
dayofweek( )	对于一个日期返回一个对应的星期几
hour( )	返回一个时间的小时部分
minute( )	返回分钟部分
month( )	返回月份部分
now( )	返回当前日期和时间
second( )	返回时间的秒部分
time( )	返回日期的时间部分
year( )	返回日期的年部分

  对于上述函数的应用，假设下面场景，需要返回2021-2-1的记录：

//下面一般情况下能匹配，返回2021年2月1日的记录，但如果p_time存放的是带有时间格式的该如何
select * from user where p_time = '2021-2-1'
//下面例子就可以将查询的p_time取日期部分与2021-2-1比较，就能达到预期的结果
select * from user where Date(p_time) = '2021-2-1'

  注：在msyql中一般日期格式为yyyy-mm-dd。

11.2.3 数值处理函数

  下面的函数主要用于代数、几何等运算，因此不常用，但在一些DBMS系统中都保持一致：

函数	说明
abs( )	绝对值
cos( )	余弦值
exp( )	指数值
mod( )	操作的余数
pi( )	返回圆周率
rand( )	返回一个随机数
sin( )	正弦值
sqrt( )	返回一个数的平方根
tan( )	返回正切值

十二、汇总数据

12.1 聚集函数

  有时，我们并不是想要表中的实际数据，而是需要根据实际数据来汇总一些其他信息，如最值、总个数等。因此MySQL提供了5个聚集函数：

函数	说明
avg( )	返回某列的平均值
count( )	返回某列的行数
max( )	返回某列的最大值
min( )	返回某列的最小值
sum( )	返回某列之和

12.1.1 avg（）函数

  avg（）函数只能给出对应的某一个列的平均值，如果需要计算多个列需要使用多个avg函数，如下例：

//下面两个例子不同点在于，前者对所有行进行计算，而后者对特定的行进行计算
select avg(id) as a_id from user 
select avg(id) as a_id from user where id >= 2 

12.1.2 count（）函数

  对于count函数有两种用法：

• count( * )：对表中的行进行计数，不管对应的列是否为null。
• count( 列 )：对特定的列计数，如果该列有null，则不会计数。

select count(*) as num from user 
select count(id) as num from user

12.1.3 max( )和min( )函数

  max和min要求返回指定列的最大或最小值。

select max(id) as max from user 
select min(id) as min from user

12.1.4 sum( )函数

  sum函数返回特定列的和，也支持多个列进行算数运算。

//下面两例，前者返回id和，后者返回id和price乘积后的和
select sum(id) as sum from user 
select sum(id*price) as sum from user

12.2 聚集不同值

  对于上述的函数，默认是对列的所有值进行计算，但可以使用distinct关键字指定仅计算不同值。

//下面是只对不同的id值得列进行计算，依次还有max、min等函数也可使用该关键字
select count(distinct id) from user 

  注：对于聚集函数，可以进行组合使用，但注意语法，比如不可将别的值作为函数的参数，一般函数只接受列为参数。

十三、分组数据

13.1 数据分组

  顾名思义，分组就是把原始的数据按照特定规则进行分组之后在进行计算。

13.2 创建分组

  分组是在select语句中使用group by子句进行的，方便理解，看下例：

select id,count(*) as num from user group by id

  上述语句执行结果如下：

id	num
1	3
2	4
3	5

  可以看出，上述语句是将id排序后进行分组，因此id为1、2、3的三组中，id为1的有3个记录，id为2的有4个记录等等。而不是对整个结果集进行聚集。
  对于分组有以下几个规则要遵守：

• group by可以有任意个列，因此可以对分组进行嵌套
• 如果进行了嵌套，数据将在最后规定的分组上进行汇总
• group by中的列必须是检索列或有效的表达式
• 对于null值被看做是一个分组，即所有null的列为一组
• group by出现在where之后，order by之前

  下面举例子来进一步说明其用法：
准备表test，有如下数据：

下面看几个语句：

select id,score from test group by id

结果如下：

  可见，如果分组后查询的依然是id和score这些实际的列，那么会返回每一组的第一条记录。再看下面：

select id,count(*) as num from test group by id

  结果如下：

可以看到，返回的是各个分组的记录数。

13.3 过滤分组

  对于过滤，我们之前用的where只是对行进行过滤，而MySQL提供一个对分组进行过滤的关键字having，如下：

//下面语句是在user表中对id进行分组，并去除掉记录小于2的分组
select id,count(*) as num from test group by id
select id,count(*) as num from test group by id having count(*) >= 1

  结果分别如下：


可以看出，第一个是返回分组的数据，第二个是返回记录数大于1的组的数据。

13.4 select子句的顺序

  下面列出了select子句的顺序以及其信息：

子句	说明	是否必须使用
select	要返回的列或表达式	是
from	从中检索数据的表	仅在从表中选择数据时使用
where	行级过滤	否
group by	分组说明	仅在按组计算聚集时使用
having	组级过滤	否
order by	输出排序顺序	否
limit	要检索的行数	否

十四、使用子查询

14.1 子查询

  上述的所有查询都是针对单个表的单条查询语句，SQL还允许创建子查询，下面从几个例子中来了解其用处及用法。

14.2 利用子查询过滤

  假设这样一个场景，有三个表，分别是订单orders表、物品items表和用户users表。现在有这样的需求，列出订了物品nike的用户信息，下面需要几个步骤：

• 在订单表中查询所有订了nike物品的订单号
• 根据查询的订单号查询这些订单的用户id
• 最后根据用户id查询用户信息

//先从items表中查询出物品nike的所有订单号
select order_num from items where prod_id = 'nike'

结果如下：

//再根据查询的订单号找到对应的客户id
select uid from orders where order_num in (20005,20007)

结果如下：

//最后根据查询的用户id查询users表中的详细用户信息
select * from users where uid in (10001,10004)

  上述例子可以将三条语句合并为一条复杂的语句来执行：

select * 
from users 
where id in(select uid 
			from orders 
			where order_num in(select order_num 
								from items 
								where prod_id = 'nike'));

  对于上述的嵌套语句，先执行最里面的select语句并将结果用于where子句进行过滤，注：不要用太多的嵌套，以防造成性能问题。

14.3 作为计算字段使用子查询

  看下例：

//下面语句将（）里的子句作为要查询的列执行，注意：子查询里的where条件用的是全限定列名防止多个表的重名字段造成错误
select 
name,statu,(select count(*) from orders where orders.uid = users.uid) as order_num from users order by name;

十五、联结表

15.1 联结

  SQL强大的一点就是在执行检索的时候能联结表，为了深入理解联结，我们需要明确一些关系数据库的基础知识。

15.1.1 关系表

  假设这样一个场景，在一个订单表中有一系列订单信息，其中包括对应的用户，如果我们将用户的id、密码、生日、联系方式等信息都存储在这个记录中，会造成很大的问题，因为这些订单中难免会有相同的用户对应不同的订单，因此就需要解决这个问题。
  下面回忆一下主键，就是代表这个记录在其表的唯一标识，如果将用户信息单独放在一个表中，而订单表只存储用户的主键号，这样既能确保对应关系，又能避免重复信息出现多次。
  而在订单表中存储的用户主键就是订单表的外键，当前表存储其他表的主键信息，即这个信息就是当前表的外键。

15.1.2为什么使用联结

  联结就是把不同的表通过对应关系联结为一个不是真实存在的表，这种机制方便在不同的表中按照确定的关联关系检索数据。

15.2 创建联结

  如之前的子查询，使用全限定列名来查询不同表的列，如：

//下面是在users和orders表中查询两个表的uid相同的数据
select users.name,orders.id from orders,users where orders.uid = users.uid

15.2.1 where子句的重要性

  对于上述的例子，如果不指定where条件，返回的结果就会将每个行进行匹配，检索的行数将是第一个表的行数乘第二个表的行数的积，这肯定是错误的，因此需要使用where子句进行正确的过滤。

15.2.2 内部联结

  上述的联结是基于两个表的相等测试，也叫等值联结或内部联结，其实上述的联结也可以用另一种方式表示：

//下面不同于使用where子句的是，from后是表inner join另一个表 on 条件的形式
select users.name,orders.id from orders inner join users on orders.id = users.id

  注：在联结表的时候可以联结多个，值得注意的是，不要联结不必要的表，且where条件要正确，且联结的越多，性能越低。

十六、创建高级联结

16.1 使用表别名

  之前有过给列起别名的例子，SQL也允许给表起别名，方便在一条SQL语句中多次使用一个表，如下：

//下面将表user和orders分别起别名u和o，注：可以用于select等其他语句部分
select * from user as u,orders as o where u.uid = o.uid

16.2 使用不同类型的联结

  相比于内部联结（或等值联结），还有其他三种联结，自联结、自然联结和外部联结。

16.2.1 自联结

  假设，在一个物品表中有一个物品id为dntr，显然是一个错误，此时想要根据这个物品查询其生产的厂家生产的其他物品是否有同样的问题。解决如下：

//下面使用了子查询
select prod_id,prod_name 
from products 
where vend_id = (select vend_id from products where prod_id = 'dntr')
//第二个解决方式如下
select p1.prod_id,p1.prod_name
from products as p1,products as p2
where p1.vend_id = p2.vend_id and p2.prod_id = 'dntr'

  注：上述的第二种方式就是自联结，将同一个表使用两次不同的别名引用。

16.2.2 自然联结

  所谓自然联结就是，联结的列没有重复出现的，建议对其中一个表使用通配符，目前用到得内部联结都是自然联结。

16.2.3 外部联结

  假设如下场景，需要查询每个客户下了多少订单，包括那些未下单的用户：

select custormes.cust_id,orders.order_num 
from custormes 
left outer join orders 
on custormes.cust_id = orders.cust_id

  如果使用内部查询，那些没有下单的用户将被忽略，按需求所以使用外部联结，结果如下：

  注：外部联结包括left outer join 和right outer join ，代表按照左侧或右侧的表检索出所有行。另外，在使用联结的时候总是要给出联结条件，否则会出现笛卡尔积。

十七、组合查询

  使用场景，在不同的表查询的数据结构类似或对单个表执行多个查询时，可以将这些查询合并，使用关键字union。

17.1 创建组合查询

  如下：

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)

结果：

上述的两个查询，可以使用组合来显示，

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 all来改变这一默认机制。

十八、插入数据

18.1 数据插入

  对于select语句而言，另外还有三个语句，第一个就是insert，有如下使用方式：

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

18.2 插入完整的行

  例如：

insert into users values(null,'luo','123456',24);
insert into users(id,name,age,password) values(null,'luo',24,'123456')

  上述的两个sql语句都是插入语句，但是有很大的区别，第一条必须遵守表中的列的顺序插入值，当表结构改变时会很容易出错，不建议使用，第二条是指定了表中的列（允许只指定一部分），此时不论表结构是否改变，也不会出错。

18.3 插入多个行

  插入多个行有两个方式：

• 分别写多条insert语句
• 如果插入的列相同，可以如下：insert into uses(id,name,age) values( … ),( … )…

18.4 插入检索出的数据

  就是用insert select 语句：

insert into users(id,name,age) select id,name,age from users where id = 1;

  上述可见，插入的数据是select查询的结果，列名不一定匹配，他是按照位置插入的。

十九、更新和删除数据

19.1 更新

  有如下用法：

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

update users set id = 1,name = 'hong' where id = 2

  注意：更新时不要忽略where，否则就是对整个表的所有行进行更新

19.2 删除

  使用方式：

• 删除特定的行
• 删除所有行

delete from users where id = 1

  注：删除只是删除表的行，即使删除所有行，表本身还在，如果删除所有行，可以使用truncate table，这是更快的删除表所有行的方式。

二十、创建和操纵表

20.1 创建表

  使用create table关键字

• 在关键字之后写新表的名字
• 之后是列名，数据类型和一些可选的关键字选项
  如：

create table users if not exists
(
id int not null auto_increment,
name varchar(20) default 'luo',
age date,
primary key (id)
)engine=innodb

  注：在表名后应该跟if not exists代表表不存在时才创建，否则会出错，auto_increment代表自增，primary key( id ) 表示id为主键列，default可以指定该列的默认值，engine表示使用哪个引擎，值得注意的是外键不可跨引擎。

20.2 更新表

  可以使用alter table 来更新表，一般在设计表时使用，一旦确定后（即向里面插入数据）就尽量不要更改。他的一般作用是创建外键：

alter table users 
add constraint fk_users_orders foreign key (order_id)
references orders(order_id)

20.3 其他操作

  删除表：drop table users
  表重命名：rename table users to user2

二十一、使用视图

21.1 视图

  视图就是讲查询出来的结果封装为一个虚拟的表，供后续使用。视图可以看做是表来用，可以查询、联结等。主要是方便重用，对其他用户隐藏实际表的数据。

21.2 使用视图

  如下：

• 创建：create view viewname as select …
• 查看创建视图的语句：show create view viewname
• 删除视图：drop view viewname

二十二、使用存储过程

22.1 存储过程

  就是将一个或多个SQL语句存储起来，方便以后使用，可以理解为批处理。对于存储过程就可以想象为java的封装，将一些特定功能的SQL语句封装为一个函数，这样可以去除冗余且性能更高。

22.2 使用存储过程

22.2.1 执行

  使用call关键字执行存储过程，后面跟存储过程的名字，并向其中传入需要的参数，如：

call productpricing(@pricelow,@pricehigh,@priceavg)

  上述的语句是使用productpricing的存储过程，将其最低价、最高价和平均价返回。注：存储过程可以返回数据，也可以不返回数据。

22.2.2 创建存储过程

  使用语句create procedure创建：

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

  其中，productpricing是存储过程的名字，（）里面可以定义需要的参数，也可以不定义，但（）必须有，begin和end中间是函数体，函数体的每个语句用；分割。
  注：mysql解析SQL时默认的分割符是；，但是在存储过程中函数体的；不会被解析为函数体的成分，会导致报错，因此可以使用关键字delimiter将mysql默认的分割符换做其他，之后在end最后再换回来，保证以后使用存储过程时，其函数体的；有效。如：

delimiter //
create procedure productpricing()
begin
	select avg(prod_price) as price_avg from products;
end //
delimiter ;

  使用存储过程如：call productpricing（）；即可。

22.2.3 使用参数

  看下例：

delimiter //
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 //
delimiter ;

  上述上述例子是使用了out参数用来从存储过程输出值，而函数体的三个语句是分别将查询的结果赋值给三个变量，并返回给调用者。使用如下：

call productpricing(@pricelow,@pricehigh,@priceavg);
//上述语句只是调用并将结果返回，要想显示，可以进行如下操作
select @pricelow,@pricehigh,@priceavg;

  除了out之外，还有in、inout两个，其中in是想存储过程传入参数，如：

delimiter //
create procedure ordering(
	in num int,
	out total decimal(8,2)
)
begin
	select sum(price) from orders where order_num = num into total;
end //
delimiter ;

  可以如下使用上述存储过程：

call ordering(2005，@total);
select @total;

22.2.4 建立智能存储过程

  我们可以像使用java函数一样，可定义局部变量，可使用控制流等，考虑下面场景，我需要合计订单，但是需要对订单增加营业税，不过只针对某些客户。看如下存储过程：

--Name:ordertotal
--parameters:onumber = order number
--			 taxable = 0 if not taxable ,1 if taxable
--		 	 ototal = order total variable
delimiter //
create procedure ordertotal(
	in onumber int,
	in taxable boolean,
	out ototal decimal(8,2)
)comment 'obtain order total ,optionally adding tax'
begin
	-- 定义局部变量total
	declare total decimal(8,2);
	-- 定义税率tax
	declare taxrate default 6;
	-- 获取订单数赋值给变量total
	select sum(price) 
	from orders
	where order_num = onumber
	into total;
	--是否需要增加营业税，通过布尔值判断
	if taxable then
		-- 是，就增加
		select total + (total/100*taxrate) into total;
	end if;
		-- 最后不论加没加，都把变量total保存到ototal并返回给调用者
		select total into ototal;
end;
delimiter ;

  通过上述的较为复杂的存储过程，可以看出，可以类比于java的函数封装和return的返回值等操作。

二十三、管理事务处理

23.1 事务处理

  当根据业务需要，我们要进行一系列的操作，而这些操作往往都是要么一起成功，要么一起失败，因此就需要使用事务管理了。

• 事务：指一组SQL
• 回退：指撤销指定SQL语句的过程
• 提交：将未存储的SQL处理结果写入数据库
• 保留点：事务处理中设置的临时占位符，你可以对他发布回退

23.2 控制事务

  mysql中使用start transaction开启事务，之后跟一系列的SQL语句，使用rollback回退开启事务之后一直到rollback之间的语句。注：不可以回退create、drop和select语句。
  同理，除了rollback之外，还可以使用commit关键字进行提交。