（一）几个数据库相关的概念
1.数据库
数据库: 保存有组织数据的容器。
数据的所有存储、检索、管理和处理实际上是有数据库软件DBMS完成的。
我们通过数据库软件DBMS来创建和操纵容器。
2.表
某种特定类型数据的结构化清单。表名是唯一的，用来标识自己。
表具有一些特性，定义了数据在表中如何的存储，存储什么样的数据，数据如何分解，各部分信息如何命名等。描述这组信息叫做模式(schema)，它是关于数据库和表的布局及特性信息。
3.列和数据类型
列：表中的一个字段
数据类型：每个列都有相应的数据类型
4.行
表中的一条记录
5.主键
一列或者一组列，能够唯一区分表中的每个行。
习惯上：不更新主键列中的值，不重用主键列的值，不在主键列中使用可能更改的值。
6.外键
外键为某个表的一列，它包含另一个表的主键值。
（二）mysql使用方法
1.mysql是什么？
mysql是一种DBMS，即是一种数据库软件。它是一种基于客户机-服务器的数据库。
2.mysql的优点：
（1）因为开源，成本低
（2）执行速度快，性能好
（3）可信赖
（4）易于安装和使用
3.连接好数据库后，就可以访问数据库并做操作，其中use是用来选择数据库的，show是用来查看mysql数据库、表、每部信息的。
（1）use + database name;
表示切换使用哪个数据库。
用use打开数据库，才能读取其中的数据。
（2）show database;
返回数据库的列表
（3）show tables;
获取一个数据库内的表的列表
（4）show columns from customers;
显示每列的信息，后面用的比较多的是 desc customers；
（三）用select检索数据
1.select：
用途：从表中检索一个或者多个数据列。
select语句中需要体现两种信息：选什么，从什么地方选。
2.检索单列
例如：select prod_name from products;
解释：从products表中检索出来prod_name列
注意：返回结果是未排序的。
3.检索多列
例如：select prod_id,prod_name,prod_price from products;
4.检索所有列
select * from product；
优点：不明确列名的时候使用。
5.distinct关键字
作用：指示mysql只返回不同的值的行
例如：select distinct vend_id from products;
使用：它必须放在列名前面
6.limit
作用：返回结果的前几行
例如：select prod_name from products limit 5;
如果是 select prod_name from products limit 5，5;则表示从行5开始，检索5行.
注意：检索出来第一行是行0。如果行数不够，能返回多少就返回多少。
7.完全限定的表名
select products.prod_name from product;
（四）用where进行数据过滤
1、where子句
作用：指定搜索条件，因为一般数据库表都包含大量的数据，很少我们需要所有的行，通常会根据特定需要来提取数据的子集。where语句就是来指定搜索条件（过滤条件）
位置：放在from之后，order by之前
例子：select prod_name,prod_price from products where prod_price = 2.50
解释：这里采用了相等测试，只返回prod_price为2.5的行，还可以有等于、不等于、小于、小于等、大于、大于等、between操作符。
扩展：根据这些操作符，可以做单个值匹配（=）、不匹配检查（！=或者<>）、范围值检查（between）、空值检查（is null）
例子1：between用法，它需要两个值。
select prod_name,prod_price from products where prod_price between 5 and 10；
例子2：空值检查
select cust_id from customers where cust_email is null;
2.组合where子句
目的：为了进行更强的过滤控制，mysql允许给出多个where自居，以逻辑操作符and或者or的方式使用。
and例子：
select prod_id,prod_price,prod_name
from products
where vend_id =1003 and prod_price <=10;
解释：必须同时满足两个条件
or例子：
select prod_id,prod_price,prod_name
from products
where vend_id =1003 or vend_id =1002;
解释：满足任意一个条件即可
注意：计算次序用圆括号界定，要不容易混淆。
3、in操作
作用：指定条件范围
例子：select prod_name,prod_price
from products
where vend_id in (1002,1003);
解释：检索供应商1002和1003制造的所有产品。in操作符后面跟着的是合法值得清单。
另一种写法：
select prod_name,prod_price
from products
where vend_id = 1002 or vend_id = 1003;
那么为什么使用in操作符呢，优点是什么呢？
（1）清楚只管
（2）计算次序容易理解
（3）in执行比or执行的快
（4）在in中可以包含其他的select语句
4、not操作符：
作用：where子句中用来否定后跟条件的关键字。
例如：select prod_name,prod_price
from products
where vend_id not in (1002,1003);
解释：检索除了1002和1003之外的所有。
（五）通配符过滤
1、应用场景
之前说的数据过滤都是对已知值进行过滤的，比如说匹配一个值或者多个值，大于某个值或者是小于某个值，或者是检查某个范围的值。
但是如果我要搜索产品名中包含anvil的所有产品呢，这时候通配符就可以大显身手了，我们可以利用通配符搜索模式，找出产品名中任何位置出现anvil的产品。
2、什么是通配符
概念：用来匹配值得一部分的特殊字符
如何使用：为了在搜索子句中使用通配符，必须使用like操作符。
3、有哪些通配符以及如何使用呢？
（一）百分号通配符%
表示：任何字符出现任意次数,也可以是0次
例子：
（1）找到以jet开头的产品，接受jet后面为任意多个字符
select prod_id,prod_name
from products
where prod_name like 'jet%';
（2）匹配任何位置包含anvil，不论在之前还是之后出现什么字符。
select prod_id,prod_name
from products
where prod_name like '%anvil%';
（3）找到以s起头、以e结尾的所有产品：
select prod_name
from products
where prod_name like 's%e';
注意：
（1）可以用‘**%’的形式进行尾空格处理，也可以用trim函数进行处理
（2）%通配符不能匹配null
（二）下划线通配符_
表示：下划线只匹配单个字符而不是多个字符
这也是与%的区别，这里就不举例赘述了。
4、小结：
通配符是一种非常有用的搜索工具，但是不能过度使用，否则搜索时间会很长。
（六）正则表达式匹配
1、啥是正则表达式
正则表达式的作用是匹配文本，将一个正则表达式与一个文本串进行比较，mysql用where子句对正则表达式提供了初步的支持，允许指定正则表达式，过滤select检索出的数据。
2、like与regexp的区别
举个例子来看两者的差别：
（一）like统配符
select prod_name
from products
where prod_name like '1000'
order by prod_name;
结果：不返回数据
（二）正则表达式
select prod_name
from products
where prod_name regexp '1000'
order by prod_name;
结果：返回一行
原因：like匹配的是整个列，只有使用通配符的时候才会返回。而regexp是在列值中匹配，如果被匹配的文本在列值中出现regexp将会找到他，相应的行将被返回。
3、有哪几种匹配呢？
（一）基本字符匹配
例1：检索列prod_name包含文本1000的所有行
select prod_name
from products
where prod_name regexp '1000'
order by prod_name;
注意：regexp后所跟的东西作为正则表达式处理。
例2：检索列prod_name包含000的所有行
select prod_name
from products
where prod_name regexp '.000'
order by prod_name;
注意：.是正则表达式语言中的一个特殊的字符，它表示的匹配任意一个字符，所以1000和2000都符合条件。
（二）or匹配
使用：当我想搜索两个串之一时，使用|
例子：匹配prod_name为1000或者2000的情况
select prod_name
from products
where prod_name regexp '1000|2000'
order by prod_name;
（三）匹配几个字符之一
表示：匹配任何一个单一字符，当想匹配特定字符的时候，可通过制定一组用[]括起来的字符来完成。
例1：
select prod_name
from products
where prod_name regexp '[123] Ton'
order by prod_name;
解释：正则表达式是[123] Ton，[123]定义了一组字符，即匹配1或者2或者3，这么看，其实[ ]是另一种形式or语句，也可以看做是[1|2|3]的缩写。
例2：
select prod_name
from products
where prod_name regexp '[^123] Ton'
order by prod_name;
解释：匹配的是除这些字符意外的任何东西。
（三）匹配范围
集合可以用来定义要匹配的一个或者多个字符，如果想匹配0到9，可以用[0123456789],也可以用[0-9],并且范围不一定是数值，也可以匹配字符，[a-z]匹配任意的字母字符。
例1：
select prod_name
from products
where prod_name regexp '[1-5] Ton'
order by prod_name;
解释：这个表达式的意思是匹配1到5，例如.5 Ton也会返回。
（四）匹配特殊字符
为了匹配特殊字符，必须使用\\为前导
例如：
（1） \\-匹配-，\\.匹配.
（2） 匹配\用\\\
（五）匹配字符类
（1）[:alnum:] -- 任意字符和数字
（2）[:alpha:] -- 任意字符
（3）[:blank:] -- 空格和制表
（4）[:cntrl:] -- ascii控制字符
（5）[:digit:] -- 任意数字
（6）[:graph:] -- 与[:print:]相同，但是不包含空格
（7）[:print:] -- 任意可打印字符
（8）[:lower:] -- 任意小写字母
（9）[:punct:] -- 既不在[:alpha:]也不在[:cntrl:]中的任意字符
（10）[:space:] -- 包括空格在内的任意空白字符
（11）[:upper:] -- 任意大写字母
（12）[:xdigit:] -- 任意十六进制数字
（六）匹配多个实例
意义：之前的正则表达式师徒匹配单词出现。但是有的时候需要对匹配书目进行更强的控制
例1：
select prod_name
from products
where prod_name regexp '\\([0-9] sticks?\\)'
order by prod_name;
解释：其中\\是用来匹配括号的，[0-9]用来匹配任意数字，sticks？匹配stick和sticks，因为?匹配他前面任何字符的0次或者1次出现。
例2：
select prod_name
from products
where prod_name regexp '[[:digit:]]{4}'
order by prod_name;
解释：[:digit:]匹配任意数字，{4}确切的要求它前面的数字出现4次，所有正则表达式匹配连在一起的任意4位数字。
同理，可以写成：
select prod_name
from products
where prod_name regexp '[0-9][0-9][0-9][0-9]’
order by prod_name;
扩展：
*表示0个或者过个匹配
+表示1个或者多个匹配
？表示0个或者1个匹配
{n}表示指定书目的匹配
{n,}表示不少于指定数目的匹配
{n,m}表示匹配数目的范围
（七）定位符
目的：为了匹配特定位置的文本。
例子：找出以一个数或者小数点开始的所有产品，这里需要定位符^，表示文本的开始。
select prod_name
from products
where prod_name regexp '^[0-9\\.]'
order by prod_name;
注意：在集合^[123]中表示否定该集合，在此处表示的是文本的开始。
扩展：
（1）^ 文本的开始
（2）$ 文本的结尾
（七）计算字段
--为什么需要计算字段？
因为存储在数据库表中的数据一般不是应用程序所需要的格式，有的时候我们需要对原始数据做一些变换等需求。这就是计算字段发挥作用的时候了。
注意：
（1）我们需要直接从数据库中检索出转换、计算或者格式化过的数据，而不是检索出数据，然后再在客户机应用程序或者报告程序中重新格式化。
（2）计算字段并不实际存在于数据库表中，计算字段是运行时在select语句内创建的。
本文主要介绍两个知识点，一个是拼接字段，一个是执行算数计算。
1、拼接字段
拼接：将值联结到一起构成一个单个值。在select语句中，使用concat()函数来拼接两个列,待拼接的各个串用逗号分隔。
例子：
select concat(vend_name,' (',vend_country,') ')
from vendors
order by vend_name;
2、执行算数计算
例子：
select prod_id,
quantity,
item_price,
quantity*item_price as expanded_price
from orderitems
where order_name = 20005;
解释：其中expanded_price是一个计算字段，计算为quantity*item_price。在执行算数计算的时候，加减乘除都可以甲酸，并且用圆括号来确定计算顺序。
扩展：删除右侧的所有空格，可以用rtrim()函数，如果是删除左侧的所有空格，可用ltrim()，去掉两边的空格是trim(),在拼接字段有时候会用到。例如：
select concat(rtrim(vend_name),' (',rtrim(vend_country),')') as
vend_title
from vendors
order by vend_name;
（八）数据处理函数
本文主要介绍mysql支持什么样的函数，以及如何使用这些函数。
1、使用函数，那么主要有哪些种类的函数呢？
（1）用于处理文本串的文本函数。比如说：删除或者填充值，转化大小写
（2）用于在数值数据上进行算数计算，比如说：返回绝对值以及代数运算
（3）用于处理日期和时间值，并且从这些值中提取特定的成分的日期和时间函数，比如说返回这两个值的日期之差，检查日期的有效性。
（4）返回DBMS正使用的特殊信息的系统函数。比如返回用户登录信息、检查版本细节。
2、文本处理函数
（1）rtrim()：去除列值右边的空格
（2）upper()：将文本转换大写
（3）left()：返回串左边的字符
（4）length()：返回串的长度
（5）locate()：找出串的一个子串
（6）lower()：转换为小写
（7）soundex()：返回串的soundex值，发音相似
（8）substring()：返回子串的字符
例子：
select cust_name,cust_contact
from customers
where soundex(cust_contact) = soundex('Y Lie');
3、日期和时间处理函数
（1）adddate() ：增加一个日期（天、周）
（2）addtime() ：增加一个时间（时、分）
（3）curdate()：返回当前日期
（4）date()：返回日期时间的日期部分
（5）datediff()：计算两个日期之差
（6）day()：返回一个日期的天数部分
（7）dayofweek()：对应一个日期返回对应星期几
（8）hour()：返回一个日期的小时部分
（9）minute()：返回一个时间的分钟部分
（10）month()：返回一个时间的月份部分
（11）now()：返回当前日期的时间
（12）second()：返回一个时间的秒部分
（13）time()：返回一个日期时间的时间部分
（14）year()：返回一个日期的年份部分
注意：不管是插入表还是更新表还是用where子句进行过滤，日期必须为yyyy-mm-dd的格式，虽然其他的日期格式可能可行，但是这是首选的日期格式，它排除了多意义性。
例如：
select cust_id,order_num
from orders
where order_date = '2015-09-01'；
如果日期的形式是‘2015-09-01 00:00:00’，那么就检索不出来，所以更加可靠的形式为：
select cust_id,order_num
from orders
where date(order_date) = '2015-09-01'；
如果我想检索出2005年9月下的所有订单：
方法一：
select cust_id,order_num
from orders
where date(order_date) between '2005-09-01' and '2015-09-30'；
方法二：
select cust_id,order_num
from orders
where year(order_date) =2005 and month(order_date)=9;
4、数值处理函数：
它一般用于代数、三角或几何运算。
abs、cos、exp、mod、pi、rand、sin、sqrt、tan
（九）汇总数据
这部分主要介绍聚集函数以及如何利用它们来汇总表的数据。
1、聚集函数的概念：运行在行组上，计算和返回单个值的函数。
2、mysql主要的聚集函数有哪些？
（1）avg：返回某个列的平均值
（2）count：返回某列的行数
（3）max：返回某列的最大值
（4）min：返回某列的最小值
（5）sum：返回某列值之和
3、avg
概念：
（1）通过对表中的行数计数病计算特定列值之和。求得该列的平均值。
（2）avg可以用来返回所有列的平均值，也可以用来范数特定列或行的平均值。
例子：
selelct avg(prod_price) as avg_price
from product;
解释：返回products表中所有产品的平均价格
select avg(prod_price) as avg_price
from products
where vend_id = 1003;
解释：指定特定行或者特定列求平均值，它返回的是特定供应商1003所提供产品的平均价格。
注意：
（1）avg只能确定特定数值列的平均值，而且列名必须作为函数的参数给出。如果说我们想获得更多列的平均值，要使用多个avg()函数。
（2）avg()函数忽略列值为NULL的行
4、count
概念：
（1）count()主要是用来计数
（2）利用count()确定表中行的数目，以及符合特定条件的行的数目
注意：
（1）使用count(*)对表中行的数目进行统计，不管是表列中包含的是null和非null
（2）使用count(column)对特定的具有值的行进行计数，忽略null
例子：
select count(*) as num_cust
from customers;
解释：返回customers表中客户的总数，这里利用count(*)对所有的行进行计数，不管各行中有什么值，计数值将在num_cust中返回。
select count(cust_emial) as num_cust
from customers;
解释：只对有点子邮件的客户进行计数。
5、max()
概念：max()返回指定列的最大值，但是max()需要制定列名
例子：
select max(prod_price) as max_price
from products;
解释：返回products表中最贵的物品。
注意：
（1）在用于文本数据的时候，如果数据是按相应的列排序，则max()返回的是最后一行。
（2）max()函数忽略列值为null的行
6、min()
概念：max()返回指定列的最小值，但是max()需要制定列名，使用方法与max同理
7、sum
概念：用来返回指定列值的和（总计）
例子：
select sum(quantity) as items_ordered
from orderitems
where order_num = 20005;
解释：sum(quantity)用来返回订单中所有物品的数量之和，where子句值统计某个物品订单中的物品。
同理，sum也可以用来合计计算值。例如下面的例子，可以得到总的订单金额,where子句同样保证只统计某个物品订单中的物品。
select sum(item_price*quantity) as total_price
from orderitems
where order_num = 20005;
注意：sum()会忽略列值为null的行。
8、在聚合函数中用distinct
例子：加了distinct参数之后，平均值只考虑各个不同的价格
select avg(distinct prod_price) as avg_price
from products
where vend_id = 1003;
注意：
（1）distinct只能用于count()
（2）不能用count(distinct)
（3）不能用distinct count(*)
9、组合聚集函数，看到这个例子就会秒懂~
例如：
select count(*) as num_items
min(prod_price) as price_min,
max(prod_price) as price_max,
avg(prod_price) as price_avg
from products;
（十）排序检索数据
目的：为了明确的排序用select语句检索出来的数据，可以用order by子句，order by往往取一个或者多个列的名字，根据此对输出进行排序。
1、如何用order by
按照单个列排序的例子：
select prod_name
from products
order by prod_name;
如果按照多个列进行排序的话，只要列出列名，列名之间用逗号分隔。例如下面的例子，首先按照价格排序，再按照商品名字排序。
select prod_id,prod_price,prod_name
from products
order by prod_price,prod_name;
注意：对于上述例子，仅仅在多个行具有相同的prod_price值时才对prod_name进行排序。如果prod_price列中所有的值都是唯一的，那么就不会按照prod_name排序。
2、指定排序方向
升序：asc，默认的
降序：desc
例子：按照价格降序，最贵的在前面。
select prod_id,prod_price,prod_name
from products
order by prod_price desc;
如果按照多个列进行排序呢？例如：
select prod_id,prod_price,prod_name
from products
order by prod_price desc,prod_name;
解释：以降序排序产品，然后再对产品名排序。
注意：这里只对prod_price降序了，如果想在多个列上进行姜旭排序，必须对每个列指定desc关键字。
扩展：使用order by和limit的组合，能够找出一个列的最高和最低的值。
例如：如何找出最昂贵物品的值
select prod_price
from products
order by prod_price desc
limit 1;
（十一）分组数据
1、什么是分组？
分组就是汇总表内容的子集，分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。
2、涉及的子句
group by子句和having子句
3、下面从两个部分介绍分组：
（1）创建分组 --group by
（2）过滤分组 --having
（一）创建分组
举例：
select vend_id, count(※) as num_prods
from products
group by vend_id;
解释：
对vend_id分组，并且统计个数。
group by指定要被分组的目标（vend_id），并做统计。
注意：
（1）group by子句可以包含任意数目的列
（2）如果在group by子句中嵌套了分组，数据将会在最后规定的分组上进行汇总。
（3）在group by中列出的列不能是聚集函数
（4）如果在select中使用表达式，则必须在group by子句中指定相同的表达式，不能使用别名。
（5）除了聚集计算语句之外，select语句中的没个列都必须在group by子句中给出。
（6）如果分组列中具有null，则它们将会作为一个分组返回。
（7）group by必须在where语句之后，order语句之前。
（二）过滤分组
意义：规定了包括哪些分组，排除哪些分组
比如：我想列出至少有两个订单的所有顾客，此时，必须基于完整的分组进行过滤，而不是根据个别的行进行过滤。
注意：where过滤指定的是行而不是分组。where没有分组的概念。
引入：having进行过滤分组，事实上，所有类型的where子句都可以用having来代替。
where和having的区别：where过滤行，having过滤分组
举例1：
select cust_id, count(※) as orders
from orders
group by cust_id
having count(※)>2;
解释：订单数大于2的用户
举例2：
select vend_id, count(※) as num_prods
from products
where prod_price >10
group by vend_id
having count(※)>2;
解释：同时用了where和having，表示具有2个以上，价格10以上的产品的供应商。
最后注意：
一般在使用group by子句的时候，应该给出order by子句，这是保证数据正确排序的唯一方法。千万不要依赖group by的排序数据。
总结：
select子句的顺序:
select
from
where
group by
having
order by
limit
（十二）子查询
子查询经常出现的场景：
（1）where子句的in操作符中
（2）用来填充计算列
1、在这里举一个例子，就知道子查询是什么，以及子查询如何使用了。
举例：列出订购物品TNT2的所有客户。
参考：mysql必知必会样例表
涉及的表：orderitems、orders、customers
检索的步骤：
（1）检索包含物品TNT2的所有订单号
select order_num
from orderitems
where prod_id = 'TNT2';
得到的结果是：order_num:20005和20007
（2）查询具有订单20005和20007的客户
select cust_id
from orders
where order_num in (20005,20007);
得到的结果是：cust_id:10001和10004
（3）检索客户id为10001和10004的客户信息
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'));
2、再举一个例子：现在需要显示customers表中每个客户的订单总数。
涉及的表：customers，orders（存储订单与相应的客户id）
（1）先过滤某个特定用户的订单数量，再推广到每个用户。
select count(※) as orders
from orders
where cust_id = 10001;
(2)对每个用户进行count计算
select cust_name,
cust_state,
(select count(※)
from orders
where orders.cust_id = customers.cust_id) as orders
from customers
order by cust_name;
备注：该子查询对检索出的每个客户执行一次
子查询的优缺点：
优点：
在where子句中使用子查询能够编写出功能很强并且很灵活的SQL语句
缺点：
（1）包含子查询的select语句难以阅读和调试。
（2）虽然对嵌套的子查询的数目没有限制，不过在实际使用中由于性能的限制，不能嵌套太多的子查询。
网友评论