MYSQL必知必会-薛冰冰

• MYSQL必知必会-薛冰冰
• 第一章 数据库基础（相关概念）
• 1.1.1 数据库（database）
• 1.1.2 表
• 1.1.3 列和数据类型
• 1.1.4 行
• 1.1.5 主键
• 1.2 使用MYSQL
• 1.2.1 连接数据库
• 1.2.2 选择数据库
• 第二章 检索数据
• 2.1.1 select 语句
• 2.1.2 特殊检索
• 第三章 排序检索语句
• 3.1.1 排序数据
• 第四章 过滤数据
• 4.1.1 where 子句
• 4.1.2 空值检查
• 4.2.1 where 子句---AND
• 4.2.2 where子句 ---OR
• 4.2.3 计算次序
• 4.2.4 多个条件下的or语句
• 第五章 通配符进行过滤
• 5.1.1 百分号%通配符
• 5.1.2 下划线_通配符
• 第六章 创建计算字段
• 6.1 计算字段
• 6.2 拼接字段
• 6.3 使用别名
• 6.4 执行算数计算
• 第七章 使用数据处理函数
• 7.1 使用函数
• 7.2.1 文本处理函数
• 7.2.2 日期和时间处理函数
• 7.2.3 数值处理函数
• 第八章 汇总函数
• 8.1 聚集函数
• 8.2.1 AVG函数
• 8.2.2 count 函数
• 8.2.3 MAX函数
• 8.2.4 sum函数
• 8.2.5 聚集不同值
• 8.2.6 组合聚集函数
• 第九章 分组数据
• 9.1 创建分组
• 9.2 过滤分组
• 9.3 分组与排序
• 9.4 子句顺序
• 第十章 使用子查询
• 10.1 利用子查询进行过滤
• 10.2 作为计算字段使用子查询
• 第十一章 联结表
• 11.1.1 关系表
• 11.2.1 创建联结
• 11.2.2 内部联结
• 11.3 联结多个表
• 第十二章 创建高级联结
• 12.1 使用表别名
• 12.2 使用不同类型的联结
• 12.2.1 自联结
• 12.2.2 自然联结
• 12.2.3 外部联结
• 12.2.4 使用带聚集函数的联结
• 第十三章 组合查询
• 13.1 创建组合查询
• 13.2 包含或删除重复的行
• 13.3 排序组合查询的结果
• 第十四章 全文本搜索
• 14.1 启用全文本搜索支持
• 14.2 进行全文本搜索
• 14.3 使用查询扩展
• 14.4 布尔查询
• 第十五章 插入数据
• 15.1 数据插入
• 15.1.1 插入整行数据
• 15.1.2 插入多个行
• 15.1.3 插入检索出的值
• 第十六章 更新和删除数据

第一章 数据库基础（相关概念）

1.1.1 数据库（database）

定义：保存有组织的数据的容器

区别于数据库软件，数据库是是通过数据库软件（DBMS）创建和操纵的容器。数据库软件有Mysql、PostgreSQL、Microsoft SQL Server等等。

1.1.2 表

定义：某种特定类型数据的结构化清单，用于方便索引，拥有唯一的名字。

在同一数据库中不能有两个一样的表名

1.1.3 列和数据类型

表由列组成，列中储存则表中的信息，即表中的字段（不同的维度）

分解数据很重要，分解方法就是把列拿出来
每一列都有相应的数据类型

1.1.4 行

表中的数据是按行储存的，是一个记录。

1.1.5 主键

标识表中一列的，例如用户ID，订单编号等等，可以唯一区分表中每个行。例如学生姓名不能作为主键，可能有重名的情况，而学号可以作为主键。

应该自行创建主键，便于后期管理。

任意两行都不能有相同的主键

每行必须有主键值

1.2 使用MYSQL

1.2.1 连接数据库

主机名：localhost

端口：3306

1.2.2 选择数据库

先用use打开数据库才可以读取数据

show databases:整个用户的所有表格名字

show tables：大表里小表的名字

show column from customers

第二章 检索数据

2.1.1 select 语句

select 查询的列 form 需要的子文件

多条语句用；号间隔

检索多个列用 ，号间隔

检索所有用 *号

2.1.2 特殊检索

检索不同的值（确定种类）

select distinct 查询内容 from 文件

• select distinct name ,price from hjmallind_goods 实际效果是只有name不重复而价格还是重复的，即distinct不能部分使用

限制检索数量（前几行检索，或指定几行检索）

select 内容 from 文件
limit 数字

• limit a，b 即a为开始检索的行数，b为检索的个数

完全限定名字的用法

select 文件.列名 from 文件

• 效果和不完全限定一样

第三章 排序检索语句

3.1.1 排序数据

order by 子句 按字母顺序排列

• order by 使用时既有字母又有文字时，字母在前，遵循ASCII码的顺序原则

select from 
order by 列名

文字按照拼音排序可使用

select name from hjmallind_goods

order by CONVERT(name USING GBK) asc;

多个列排序

select l1，l2，l3 form 文件

order by l1，l2

• 先按照l1再按照l2

指定排序方向

order by price desc(降序)

找出最大的值的方法

select form

order by price desc

limit 1

• 注意顺序 form>order>limit

第四章 过滤数据

4.1.1 where 子句

select from
where 条件

• order by 在where 子句之后
• 单引号：当字符串出现时

select from

where price between a and b

• between包括a ，b 的值

4.1.2 空值检查

select from
where price is null

例子：找出客户中邮箱地址为空值的ID号码

select cust_id from customers

where cust_email is null

4.2.1 where 子句---AND

where 条件1 and 条件2 and……
同时满足条件

4.2.2 where子句 ---OR

where 条件1 or 条件2

满足一个即可

4.2.3 计算次序

例子：需要列出价格为10美元即以上的由1002或者1003制造的产品

where price>=10 and (id=1003 or id=1002)

• 注意:优先处理and语句，不加括号会认定为价格大于10的ID为1003 和 任意的ID为1002 的产品

4.2.4 多个条件下的or语句

挑选出ID为1002，1003，1004，1005 的产品

 where ID in（1002，1003，1004，1005）

挑选出除了1002，1003的产品

where ID is not in（1002，1003）

• not + in，between，exists

第五章 通配符进行过滤

通配符：用来匹配值的一部分特殊字符

5.1.1 百分号%通配符

例子：找出以jet开头的所有产品

where name like 'jet%'

• %表示任何字符出现任意次数，即任意站位的字符，但是不能表示null值

表示name中有anvil 的产品

where name like '%anvil%'

5.1.2 下划线_通配符

where name like '_ton anvil'

• 下划线只能代表一个字符，不能多不能少

通配符的使用需要注意，因为查询的时间较长

第六章 创建计算字段

6.1 计算字段

• 创建整理出满足需求的数据表格

字段：基本上与列的意思相同，经常互换使用。使用场景不同。

6.2 拼接字段

拼接：将值联结到一起构成单个值，位于一列，用concat函数来实现

• 多数其他数据库用丨 丨来实现

select concat (name,'(',price,')')
注意形式，联结部分中间用逗号

• 分析：name ＋ ( +price + )

删除数据空格，用RTRIM函数实现

select RTRIM(price)

应用例子是什么？？？

• 除了RTRIM,还有LTRIM,TRIM代表删除左边和两边的空格

6.3 使用别名

新拼接好的列没有新的名字，所以用AS关键字对列赋予新的列名

select concat (price,'(',original_price,')') as '价格变化'
from hjmallind_goods

• as 也可以重新定义已有的列名

6.4 执行算数计算

算数操作符

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

已知单品价格和数量，新建总价格的一列

select quantity,item price，quantity*item price as totall

from

第七章 使用数据处理函数

7.1 使用函数

文本函数：处理文本串，删除、填充、大小写转换

数值函数：数值数据的算数操作,返回绝对值、代数运算

日期时间函数：返回两个日期的差值，检查日期有效性

系统函数：返回正使用的特殊信息，返回用户的登录信息、检查版本细节

7.2.1 文本处理函数

left返回字符串左边的字符length返回字符串长度locate找出串的子串lower小写转换LTRIM左空格删除right返回字符串右边的字符RTRIM右空格删除soundex***substring返回子串的字符upper大写转换

soundex()函数

可以匹配发音相似的数据，例如：'Y.Lee 和 Y.Lie',是一种算法

where soundex(name)=soundex('Y lie')

7.2.2 日期和时间处理函数

adddate增加日期addtime增加时间（时、分）curdate返回当前日期curtime返回当前时间date日期的时间和日期datediff日期之差date_add日期运算函数date_format返回格式化日期或时间day返回日期天数部分dayofweek对于一个日期返回对应的星期几hour/minute/month/second返回一个时间的小时/分钟/月份/秒部分now当前时间time返回时间部分year年份部分

• 搭配where语句进行时间的数据过滤

mysql支持的日期格式：××××-××-××

where order_date='2009-03-06'

• 如果原始数据中既有日期又有时间，则直接检索失败，需要将时间和日期分隔开。例如：date（order_date)='2009-03-06'

查询九月下旬的数据

where date（order_date) between '2009-09-15' and '2009-09-30'

或者

where year(order_date)=2009 and month(order_date)=9

7.2.3 数值处理函数

abs绝对值cos余弦值exp指数mod除后余数pi圆周率rand随机数sin正弦sqrt平方tan正弦

第八章 汇总函数

8.1 聚集函数

• 确定表中行数（满足某条件的值的个数）
• 获得表中行组的和
• 找出列的最大值、最小值、平均值

AVG某列平均值count某列行数max某列的最大值min某列的最小值sum某列的值的和

• count(*)对于null空值也计数

8.2.1 AVG函数

select avg(price) as avg_price

form products

8.2.2 count 函数

• 用法1：count（*）对表中行的数目进行计数，且无论列中包含的是不是空值。
• 用法2：count（column）对特定的列中具有值的行进行计算。

select count(*) as num_cust

from customers

统计有邮箱的用户数量

select count(cust_email) as num

from custmers

8.2.3 MAX函数

select max() as
from products

• 当对非数值列使用max函数时，返回最后一行

8.2.4 sum函数

用来求指定列的和

select sum(quantity)

from orderitem

where order_num=20005

综合求和

select sum(price*quantity)
from
where order_name=20005

8.2.5 聚集不同值

只对不同的值进行计算操作用Distinct函数

select avg（distinct price）
from

8.2.6 组合聚集函数

select count(*)

min(price),

max(price),

avg(price)

from

第九章 分组数据

9.1 创建分组

• 按照不同的序号返回特定值,用group by 实现

select id ，count(*) as num
from
group by id

group by 语句出现在where子句之后，order by 之前。

使用 with rollup 可以查出null值

group by name with rollup

9.2 过滤分组

• 想要买了两件以上商品的顾客名单，使用过滤分组的方法

select ID，count(*) as num
from orders
group by id
having count(*)>=2

• where 与 having的组合使用

select id，count(*) as num
from product
where price>=10
group by id
having count(*)>=2

9.3 分组与排序

• group by 与 order by 的区别

order bygroup by排序产生的输出分组行，但输出的可能不是分组的顺序任意列都可以使用只可能使用选择列或者表达式列不一定需要若和聚集函数一起使用列则必须使用

• 在使用group by之前应该用确定的order by 语句规定排序方式

例子：检索总计订单价格大于等于50的订单号和订单总价格

select id，sum(quanlity*price) as totall

from

group by id

having sum(quantity*price)>=50

如果想要按订单总价格排序输出

select id，sum(quanlity*price) as total
from
group by id
having sum(quantity*price)>=50
order by total

• order by排序在最后

9.4 子句顺序

select→from→where→group by→having→order by →limit

第十章 使用子查询

10.1 利用子查询进行过滤

现在需要列出订购物品TNT2 的所有客户（需要的信息在不同的表中）

1.检索包括物品TNT2 的所有订单编号

2.检索具有前一步列出的订单编号的所有客户的id

3.检索id主人的客户信息

• 把上一步的select的结果作为下一个select语句的where子句

select order_num
from orderitem
where prod_id ='TNT2'
select cust_id
from orders
where order_name in(20005,20007)

结合一下：

select cust_id
from orders
where order_name in (select order_num
from orderitem
where prod_id='TNT2')

系统首先处理括号里面的查询，从内到外

select cust_name

from customers

where cust_id in(select cust_id

from orders

where order_name in(select order_name

from orderitem

where prod_id='TNT2'))

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

需要显示customers表中的每个客户的订单总数，订刊与相应的客户id在orders表中。

1.从customers表中检索出客户列表

2.对于检索出的每个客户，统计在orders表中的订单数目

若单独对某一客户进行查询

select count(*) 
from orders
where cust_id=10001

对所有customers表中客户进行查询

select cust_name,(select count(*) from orders

where orders.cust_id=customers.cust_id)

from custmers

order by cust_name

• 统一的列将两个表联系起来

相关子查询：orders.cust_id=customers.cust_id（完全限定列名）

第十一章 联结表

11.1.1 关系表

关系表的设计是要保证把信息分解成多个表，一类数据一个表，避免相同的数据重复出现。

例子：商品和商品供应商两个信息→分别设计两个表格→一个储存商品信息和供应商ID信息→一个储存商品供应商的信息，并具有特殊的供应商ID→两个表通过供应商ID互相联系

此时的供应商ID为外键：某个表的中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

11.2.1 创建联结

select vend_name,prod_name,prod_price

from vendors,products

where vendors.vend_id=products.vend_id

order by vend_name,prod_name

• 把不在一个表里的数据通过一个公共的列将两个表重组成一个组

11.2.2 内部联结

from inner join -------on 语句和where子句效果一致

select vend_name,prod_name,prod_price
from vendors inner join products
on vendors.vend_id=products.vend_id

11.3 联结多个表

select Prod.name,vend_name,prod.price,quantity
from orderitems,products,vendors
where products.vend_id=vendors.vend_id
and orderitems.prod_id=product.prod_id
and order_num=20003

第十二章 创建高级联结

12.1 使用表别名

from customers as c，products as p

where p.vend_id=c.vend_id

• 注意：表的别名不可返回客机中，只能在查询中使用

12.2 使用不同类型的联结

12.2.1 自联结

例子：现发现商品DINCT质量出现问题，怀疑此商品供应商的其他商品也存在问题，找出其他商品的ID和name

方法1(子查询)
select prod_id,prod_name
from products
where vend_id=(select vend_id
from products
where prod_name='DINCT')
方法2（自联结）
select p1.prod_id,p1.prod_name
from products as p1,products as p2
where p1.vend_id=p2.vend_id
and p2.prod_name='DINCT'

12.2.2 自然联结

select customers.*

12.2.3 外部联结

例子：找出下单的客户和订单于没下单的客户和订单，前者用内部联结实现，后者用外部联结

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

• 有没有下单的客户都可显示

left / right outer join:left 代表左边的 customers表中的行全部检索；right代表右边的orders表中的行全部显示，可以通过改变位置而改变效果

12.2.4 使用带聚集函数的联结

例子：将客户表与订单表联结，检索出每个客户的订单数量

select customers.cust_name,customers.cust_id,count(orders.order_num) as num

form customers inner join orders

on customers.cust_id=orders.cust_id

• 想要包括没有下订单的客户，可以用left outer join函数

select customers.cust_name,customers.cust_id,count(orders.order_num) as num
form customers left outer join orders
on customers.cust_id=orders.cust_id

第十三章 组合查询

使用单个查询从不同的表中返回信息

在单个表中执行多个查询，按单个查询返回信息

13.1 创建组合查询

例子：找出价格小于5的商品和供应商为10003，10002的商品

select vend_id,vend_price,prod_id

from produces

where prod_price<=5

union

select vend_id,vend_price,prod_id

from produces

where vend_id in (10002,10003)

• UNION 使用规则

必须由两句以上的select语句，在select语句之间使用

union连接的select查询的内容必须完全相同

13.2 包含或删除重复的行

union会自动删除同时符合多个条件的行，只返回一行

可以使用 union all 函数将重复的行显示出来，而where 子句无法实现这个功能

13.3 排序组合查询的结果

在最后一个select语句加上group by 语句，则对所有查询结果进行排序

第十四章 全文本搜索

14.1 启用全文本搜索支持

先制定搜索范围，规范索引

create table productnote
(
note_id int not null auto_increment,
prod_id char(10) not null,
note_text text null,
fulltext(note_text) 
) engine=mysam

14.2 进行全文本搜索

索引之后使用match函数指定被搜索的列，against函数制定要使用搜索的表达式

select note_text

from productnotes

where match(note_text) against('rabbit')

注意：fulltext( )和match（）内容和顺序必相同

select note_text,
match (note_text) against('rabbit') as rank
from productnote

• 不在where子句中使用则返回的是所有的行，返回的是等级值，若无'rabbit'则返回值为0；rabbit出现的越靠前则值越大。可用order by进行筛选

14.3 使用查询扩展

select note_text
from productnote
where match(note_text) against('anvils'with query expansion)

不仅会检索出含有‘anvils’的行，还会返回可能有关的行

14.4 布尔查询

查询包含'heavy'而不包含'rope' 的行

select note_text

from productnotes

where match(note_text) against('heavy-rope*' in boolean mode)

布尔操作符

+包含且必须存在-排除且必须不出现>包含且增加等级值（优先排序）<包含且减少等级值()把词组成子表达式~取消一个词的排序值*词尾的统配符“ ”定义一个短语

against（'rabbit bait' in boolean mode） →分开检索'rabbit' 'bait'

'"rabbit bait"' in boolean mode →把rabbit bait 当成一个整体进行检索

'+safe+(<combination)' →搜索匹配safe 和combination，且降低后者的等级。

第十五章 插入数据

15.1 数据插入

15.1.1 插入整行数据

insert into customer
values('susan',
'100 main street',
'los angles',
null,
null)

• 括号里必须包括所有的列里面的值

以上插入方法不安全，更为安全的方法：

insert into customer

15.1.2 插入多个行

使用多条insert，中间用分号连接（列不同）

若列的次序相同可以在括号之间用逗号连接

15.1.3 插入检索出的值

例子：把custnew中的客户表插入到customer中

insert into custnew

（cust_id,cust_address,cust_email)

select cust_id,cust_address,cust_email

from custnew

• 注意：两个表中cust_id不能相同，要不会报错
• 第二个select语句也可以用where子句

第十六章 更新和删除数据

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

update customer

set email='xuebingbing0306.126.com'

where cust_id=1006

可以用null值替换，把某一值删除

delect from customer
where ID=1006

注意：所有where尽量用主键

在update和delect之间要用select测试是否正确