《MySQL必知必会》阅读笔记（一）——查询「建议收藏」

MySQL简介

SQL：Structured Query Language，结构化查询语言，是一种专门用来与数据库通信的语言，几乎所有的DBMS（数据库管理系统）都支持SQL，不是某种数据库的专用语言

MySQL：基于客户机-服务器的DBMS。MySQL是服务器软件，可以在本地安装的副本上运行，也可以连接到具有访问权限的远程服务器上的副本。客户机为程序开发语言

第4,5章  检索数据

select distinct type from osc_users; /*返回唯一值的type行*/
select * from osc_opt_logs limit 6,5; /*从第6行开始返回5行*/

排序检索数据

select * from osc_users order by type;/*根据type值返回检索数据*/
select id, type, activate_code from osc_users order by type,activate_code;/*根据type排序，当type相同时，根据activate_code排序木，默认为升序排序*/
select id, type, activate_code from osc_users order by type desc ,activate_code desc ;/*desc表示降序排序，大小写排序取决于数据库如何设置*/
select * from osc_users order by type limit 1;/*limit必须在order by之后*/

select字句顺序 where, order by,  limit

第6章  过滤数据

本章讲授如何使用select语句的where子句指定搜索条件

where字句操作符说明

=       等于
<>      不等于
！=     不等于
<       小于
<=      小于等于
>       大于
>=      大于等于
BETWEEN 在指定的两个值之间

比较字符串时要使用单引号，比较值时不需要

select * from osc_users where id between 12 and 28;/*检索id从12到28*/

空值检查

select * from osc_users where ident is null;/*检索ident为null的user*/

第7章  数据过滤

AND   与，计算优先次序较OR高，可通过使用圆括号改变计算次序

OR     或

select * from osc_last_msgs where (user=12 or user=28) and sender=1;

IN   指定条件范围

select * from osc_last_msgs where user in (12,28) and sender=1;

NOT 否定它之后所跟的任何条件

第8章 使用通配符进行过滤

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

搜索模式（search pattern）：由字面值、通配符或两者组合构成的搜索条件

% 通配符，表示任意长度的字符，可插入在字符串的任意位置

select * from osc_users where email like '%cn';

_ 通配符，表示任意单个字符

select * from osc_users where email like '_@os%';

注意：

1. 通配符搜索消耗时间长，能用其它方式搜索就不用通配符搜索

2. 通配符用在搜索模式的开始处搜索起来是最慢的，除非有必要，否则不要这样使用

第9章 用正则表达式进行搜索

mysql只支持部分正则表达式

REGEXP  表示后面跟的正则表达式

select * from osc_users where email regexp 'oschina\\.cn';/*匹配包含oschina.cn的值*/
select * from osc_users where email like 'oschina.cn';/*这句无数据返回，like匹配的是整个列值，这是like关键字与regexp的重要区别*/

BINARY  在字符串前面使用，表示区分大小写

|      或

select * from osc_users where email regexp 'oschina\\.cn|com' order by email desc;

匹配几个字符之一 [abc] [a-z]  [0-9]

匹配特殊字符，在字符前加上\\      例如要匹配.    则使用\\.

匹配字符类

[:alnum:]      任意字母和数字
[:alpha:]      任意字符
[:blank:]      空格和制表
[:cntrl:]      ASCII控制字符
[:lower:]      任意小写字符
[:upper:]      任意大写字符
[:digit:]      任意数字
[:xdigit:]     任意十六进制数字
[:space:]      包括空格在内的任意空白字符
[:punct:]      既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:print:]      任意可打印字符
[:graph:]      与[:print:]相同，但不包括空格

重复元字符

*      0个或多个匹配
+      1个或多个匹配
？      0个或1个匹配
{n}    指定数目的匹配
{n,}   不少于指定数目的匹配
{n,m}  匹配数目的范围，m不大于255

select * from osc_users where name regexp '^[[:digit:]]{4}' order by id;/*name字段开始处包含4个数字*/

 定位符，匹配特定位置的文本

^          文本的开始
$          文本的结尾
[[:<:]]    词的开始
[[:>:]]    词的结尾

^ 还有另一个含义，在集合的开始处放置一个^表示否定，例如[^123]表示匹配除了字符1,2,3以外的任何东西

第10章  创建计算字段

通过计算字段输出由表内字段值计算出的内容

拼接（concatenate）字段：将字段值联接到一起构成单个值，可使用Concat()函数拼接两个列

select name, CONCAT(province,'  ',city) from osc_users;/*拼接字段*/

使用别名，AS关键字

select id,name,concat(province,': ',city) as address from osc_users;/*将字段拼接为一个address别名字段*/

执行算数计算，对字段进行 +，-，*,/ 运算

第11章  使用数据处理函数

常用的文本处理函数

Length()         返回字符串的长度
Left()           返回串左边的字符
Right()          返回串右边的字符
Lower()          将串转换为小写
Upper()          将串转换为大写
Trim()           去掉字符串两端空格
LTrim()          去掉串左边的空格
RTrim()          去掉串右边的空格
Locate()         找出串的一个字串
SubString()      返回子串的字符
Soundex()        返回发音相似的值

日期与时间处理函数，格式xxxx-xx-xx

Date()
Year()
Month()
Day()
Hour()
Minute()
Second()
Now()
CurDate()
CurTime()

select name,reg_time from osc_users where DATE(reg_time)='2008-09-04';/*Date()返回datetime类型字段的时间部分*/

选择日期区间， 使用 between  and 

select name,reg_time from osc_users where DATE(reg_time) between '2008-09-01' and '2008-09-30';

另一种方法

select name,reg_time from osc_users where YEAR(reg_time)=2008 and MONTH(reg_time)=9;

数值处理函数

仅处理数值数据，一般用于代数，三角或几何运算

第12章  汇总数据

聚集函数

AVG()         返回某列的平均值
COUNT()       返回某列的行数
MAX()         返回某列的最大值
MIN()         返回某列的最小值
SUM()         返回某列值之和

select COUNT(*) as openidNum from osc_users where type=3;/*计算第三方用户数量*/

使用distinct 包含不同值

select AVG(distinct score) from osc_users;

第13章  数据分组

分组允许把数据分为多个逻辑组，以便对于每个组进行聚集计算

数据分组：GROUNP BY

select type,COUNT(*) as typeNum from osc_users GROUP BY type;/*根据type进行分组计算，计算出每个type对应的行数*/

关于使用GROUNP BY的重要规定：

1. GROUNP BY子句可以包含任意数目的列，这使得能够对分组进行嵌套，为数据分组提供更细致的控制

2. 如果在GROUNP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总

3. GROUNP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数），如果在SELECT中使用表达式，则必须在GROUNP BY子句中指定相同的表达式，不能使用别名

4. 除了聚集计算语句外，SELECT语句中的每个列都必须在GROUNP BY子句中给出

5. 如果分组列中具有null值，则NULL将作为一个分组返回。如果列中有多个NULL，它们将分为一组

6. 子句顺序，WHERE        GROUNP BY         ORDER BY

select type,gender,COUNT(*) as typeNum from osc_users GROUP BY type,gender;/*先根据type进行分组，每个type再根据gender分组计算*/

过滤分组：HAVING

对GROUP BY分出的组进行过滤，HAVING支持所有WHERE操作符

select gender,COUNT(*) as s from osc_users group by gender HAVING COUNT(*)>=5;/*根据gender进行分组，并过滤出总数大于5的分组*/

select子句顺序总结

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

第14章  使用子查询

子查询：嵌套在其它查询中的查询。即作为另一个的查询的查询条件

SELECT * FROM osc_users WHERE id IN (SELECT ……)

作为计算字段查询

SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
        FROM orders
        WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

第15章  联结表

关系表

大量一类相同数据应抽离出来另外分表，提供一个主键id给主表作为外键，从而建立关系表

等值联接

SELECT vend_name, prod_name, prod_price FROM vendors, products 
       WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;

内部联接

SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products 
       ON vendors.vend_id = products.vend_id; 

right join, left join, inner join三者区别

inner join：返回两表联接字段相等处的数据

right join：返回左表联接字段相等处的数据和右表所有数据，左表无对应数据处用null代替

left join：返回左表所有数据和右表联接字段相等处的数据，右表无对应数据处用null代替

SELECT b.user,COUNT(*) AS num FROM osc_tweet_topics a right join osc_opt_logs b ON a.log = b.id 
       WHERE a.ref_id = 0 AND ref_type =100 AND a.title = "世界杯" AND obj_type = 100  group by b.user;

联接多个表。先列出所有表，再定义表之间的关系

SELECT prod_name, vend_name, prod_price, quantity FROM orderitems, products, vendors 
       WHERE products.vend_id = vendors.vend_id 
             AND orderitems.products = products.prod_id 
             AND order num =20005;

第16章  创建高级联接

表别名：使用表别名可缩短sql语句

自联接：

假如发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商的其它物品是否也存在问题。此查询要求先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其它物品

方法一，使用子查询

SELECT prod_id,prod_name FROM products WHERE vend_id = (SELECT vend_id WHERE prod_id = 'DTNTR');

方法二，使用自联接。如下例，先根据vend_id联接两个表，再根据p2.prod_id过滤数据

SELECT p1.prod_id, p1.prod_name FROM products p1, products p2 
       WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';

虽然这两种方法结果是相同的，但是有时候处理联接远比处理子查询快的多，应该试一下两种方法，以确定哪一种性能更好

自然联接：

无论何时对表进行联接，应该至少有一个列出现在不止一个表中（被联接的列）。

内部联接返回所有数据，甚至相同的列多次出现。自然联接排除多次出现，使每个列只返回一次。这个操作是手动完成的，主表使用通配符，其它表使用一个明确的子集

外部联接：

联接包含那些在相关表中没有关联行的行，这类联接称为外部联接

LEFT OUTER JOIN

RIGHT OUTER JOIN

使用带聚集函数的联接

SELECT customers.cust_name,customers.cust_id,COUNT(orders.order_num) AS num_ord 
       FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id
            GROUNP BY customers.cust_id;

第17章  组合查询

本章讲述如何利用UNION操作符将多条SELECT语句组合成一个结果集

第18章  全文本搜索