MySQL必知必会2
使用数据处理函数
函数
与其他大多数计算机语言一样,sql支持利用函数来处理数据。函数一般是在数据上执行的,他给数据的转换和处理提供了方便,在前一章中用来去掉尾空格的rtrim()就是一个函数的例子
文本处理函数
输入: select vend_name,upper(vend_name) as vend_name_upcase from vendors order by vend_name;
分析: 将列 vend_name_upcase转换为大写
示例:
mysql> select name,upper(name) as name_upcase from account order by name; +------+-------------+ | name | name_upcase | +------+-------------+ | aaa | aaa | | aest | aest | | bbb | bbb | | ccc | ccc | | ccc | ccc | | ddd | ddd | | ddd | ddd | | t57l | t57l | | test | test | | tsdf | tsdf | +------+-------------+
常用的文本处理函数
函数 | 说明 |
---|---|
left() | 返回串左边的字符 |
length() | 返回串的长度 |
locate() | 找出串的一个子串 |
lower() | 将串转为小写 |
ltrim() | 去掉串左边的空格 |
right() | 返回串右边的字符 |
rtrim() | 去掉串右边的空格 |
soundex() | 返回串的soundex的值 |
substring() | 返回子串的字符 |
upper() | 将串转换为大写 |
日期和时间处理函数
日期和时间采用相应的数据类型和特殊的格式存储,以便于能快速和有效的排序或过滤,并且节省物理存储空间
常用日期和时间处理函数
函数 | 说明 |
---|---|
adddate() | 增加一个日期(天、周等) |
addtime() | 增加一个时间(时、分等) |
curdate() | 返回当前日期 |
curtime() | 返回当前时间 |
date() | 返回日期时间的日期部分 |
datediff() | 计算两个日期之差 |
date_add() | 高度灵活的日期运算函数 |
date_format() | 返回一个格式化的日期或时间串 |
day() | 返回一个日期的天数部分 |
dayofweek() | 对于一个日期、返回对应的星期几 |
hour() | 返回一个时间的小时部分 |
minute() | 返回一个时间的分钟部分 |
month() | 返回一个日期的月份部分 |
now() | 返回当前日期和时间 |
second() | 返回一个时间的秒部分 |
time() | 返回一个日期时间的时间部分 |
year() | 返回一个日期的年份部分 |
汇总数据
聚集函数 运行在行组上,计算和返回单个值的函数
avg函数
输入: select avg(prod_price) as avg_price from products;
分析: 此select语句返回值avg_price,它包含products表中所有产品的平均价格
只用于单个列 avg()只能用来确定特定数值的平均值,而且列名必须作为函数参数给出
null值 avg()函数忽略列值的null的行
count函数
输入 select count(*) as num_cust from customers;
分析 利用count对所有行计数,不管行中各列有什么值。包含null值
输入 select count(cust_email) as num_cust from customers;
分析 使用count对cust_email列中有值的行进行计数,不包含null值
聚集不同的值
输入 select avg(distinct prod_price) as avg_price from products where vend_id = 1003;
分析 排除掉相同的prod_price的值
分组数据
创建分组
输入 select vend_id,count(*) as num_prods from proucts group by vend_id;
分析 上面的select语句指定了两个列,vend_id包含产品供应商的id,num_prods为计算字段。group by 子句指示mysql按vendid排序并分组数据。
过滤分组
输入:
select cust_id,count(*) as orders from orders group by cust_id having count(*) >= 2;
分析 having子句,他过滤 count(*)>=2的那些分组
having和where的差别:having用于分组后过滤,where用于分组前过滤
分组和排序
输入
select order_num,sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price) >=50 order by ordertotal;
输出
mysql> select order_num,sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price) >=50 order by ordertotal; +-----------+------------+ | order_num | ordertotal | +-----------+------------+ | 20006 | 55.00 | | 20008 | 125.00 | | 20005 | 149.87 | | 20007 | 1000.00 | +-----------+------------+
使用子查询
利用子查询进行过滤
输入
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' ) );
输出
+----------------+--------------+ | cust_name | cust_contact | +----------------+--------------+ | coyote inc. | y lee | | yosemite place | y sam | +----------------+--------------+
分析
为了执行上述select语句;mysql实际上必须执行3条select语句,最里边的子查询返回订单号列表,此列表用于其外面的子查询的where子句。外面的子查询返回客户id列表,此客户id列表用于最外层的where子句,最外层的查询确实返回所需的数据
连接表
外键
外键为某个表中的一列,他包含另一个表的主键值
创建联结
输入
select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id=products.vend_id order by vend_name,prod_name;
分析
最大的差别是在于所指定的两个列(prod_name和prod_price)在一个表中,二另一个列(vend_name)在另一个表中
现在来看from子句。与以前的select语句不一样,这条语句的from子句列出了两个表,分别是vendors表和products表。他们就是这条select语句联结的两个表的名字。这两个表用where子句正确联结,wher子句指示mysql匹配vendors表中的vend_id和products表中的vend_id
**内部联结*
输入
select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id=products.vend_id;
分析 此语句中的select域前面的select语句相同,但from子句不同,这里,两个表之间的关系是from子句的组成部分,以inner join指定,在使用这种语法时,联结条件用特定的on子句而不是where子句给出
创建高级联结
使用表别名
输入
select cust_name,cust_contact from customers as c,orders as o,orderitems as oi where c.cust_id=o.cust_id and oi.order_num=o.order_num and prod_id='tnt2';
自联结
输入
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='dtntr'
自然联结
无论何时对表进行联结,应该至少有一个列出现在不止一个表中(被联结的列)。标准的联结返回所有数据,甚至相同的列多次出现。自然联结排除多次出现,使每个列只返回一次
输入
select c.*,o.order_num,o.order_date, oi.prod_id,oi.quantity,oi.item_price from customers as c,orders as o,orderitems as oi where c.cust_id=o.cust_id and oi.order_num=o.order_num and prod_id='fb';
外部联结
输入
select customers.cust_id,orders.order_num form customers left outer join orders on customers.cust_id = orders.cust_id;
组合查询
多数sql查询都只包含从一个或多个表中返回数据的单条select语句。mysql也允许执行多个查询(多条select语句),并将结果作为单个查询结果集返回
创建组合查询
使用union
union的使用很简单。所需做的只是给出每条select语句,在各条语句之间加上关键字union
输入
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指示mysql执行两条语句,并把输出组合成单个查询结果集
全文本搜索
两个最常用的引擎为myisam和innodb,前者支持全文本搜索,后者不支持
使用全文本搜索,必须索引被搜索的列,而且要随着数据的改变不断的重新索引
在对表列进行适当的设计后,mysql会自动进行所有索引和重新索引
在索引之后,select可与match()和against()一起使用以实际执行搜索
启用全文本搜索支持
一般在创建时启用全文本搜索,create table语句接收fulltext子句,他给出被索引的一个逗号分隔的列表。
创建表
create table productnotes( note_id int not null auto_increment, prod_id char(10) not null, note_date datetime not null, note_text text null, primary key(note_id), fulltext(note_text) )engine=myisam;
分析 这些列中有一个名为note_text的列,为了进行全文本搜索,mysql根据子句fulltext(note_text)的指示对他进行索引。这里的fulltext索引单个列,如果需要也可以指定多个列
在定义之后mysql自动维护该索引,在增加、更新、或删除行时,索引随之自动更新
进行全文本搜索
在索引之后,使用两个函数match()和against()执行全文本搜索,其中match()指定被搜索的列,against()指定要使用的搜索表达式
输入
select note_text from productnotes where match(note_text) against("rabbit");
分析 此select语句检索单个列note_text。由于where子句,一个全文本搜索被执行。match(note_text)指示mysql针对指定的列进行搜索,against('rabbit')指定词rabbit作文搜索文本。
使用查询扩展
查询扩展用来设法放宽所返回的全文本搜索结果的范围,考虑下面的情况。你想找出所有提到anvils的注释。只有一个注释包含词anvils,但你还想找出可能与你的搜索有关的其他行,即使他们不包含词anvils
这也是扩展的一项任务,在使用查询扩展时,mysql对数据和索引进行两遍扫描来完成搜索:
首先,进行一个基于全文本的搜索,找出与搜索条件匹配的所有行
其次,mysql检查这些匹配并选择所有有用的词
在其次,mysql再次进行全文搜索,这次不仅使用原来的条件,而且还使用所有有用的词
利用查询扩展,能找出可能相关的结果,即使他们并不精确包含所查找的词
使用查询扩展
select note_text from productnotes where match(note_text) against('anvils' with query expansion);
布尔文本搜索
mysql支持全文本搜索的另一种形式,称为布尔方式,即使没有全文本搜索也可以使用,但这是一种非常缓慢的操作
输入
select note_text from productnotes where match(note_text) against('heavy' in boolean mode);
分析 此全文本搜索检索包含词heavy的所有行,其中使用了关键字in boolean mode,但实际上没有指定布尔操作符,因此,其结果与没有指定布尔方式的结果相同
为了匹配包含heavy但不包含任意以rope开始的词的行可以使用以下查询
输入
select note_text from productnotes where match(note_text) against('heavy -rope*' in boolean mode);
全文本布尔操作符
布尔操作符 | 说明 |
---|---|
+ | 包含,词必须存在 |
- | 排除,词必须不出现 |
> | 包含,而且增加等级值 |
< | 包含,且减少等级值 |
() | 把词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等) |
~ | 取消一个词的排序值 |
* | 词尾的通配符 |
"" | 定义一个短语(与单个词的列表不一样,他匹配整个短语以便包含或排除这个短语) |
插入数据
插入完整的行
输入
insert into customers(cust_name, cust_contact, cust_email, cust_address, cust_city, cust_state )values('pep e.lapew', null, null, '100 main street', 'los angeles', 'ca' )
插入多个行
insert into customers( cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country )values( 'pep e. lapew', '100 main street', 'los angeles', 'ca', '90046', 'usa' ), ( 'm.martian', '42 galaxy way', 'new your', 'ny', '11213', 'usa' );
更新和删除数据
输入
update customers set cust_email='elmer@fudd.com' where cust_id=10005;
更新多个列
update customers set cust_name='the fudds', cust_email='elmer@fudd.com' where cust_id=10005;
删除数据
delete from customers where cust_id = 10006;
上一篇: MySQL 关闭安全更新模式
下一篇: 旅游出行必备六种药