mysql数据库teach_读书笔记 - 《MYSQL必知必会》

术语： 数据库 ， 表 ， 模式 ， 列 ， 数据类型 ， 行 ， 主键

命令行： mysql -u ben -p -h myserver -P 9999

检索

SELECT 列名 FROM 表名;

关键字：DISTINT 指示MYSQL只返回不同的值

SELECT DISTINT vend_id FROM products;

tips：DISTINT关键字应用于所有列而不仅仅是前置它的列

关键字：LIMIT 指示MYSQL只返回前N行

SELECT prod_name FROM products LIMIT 5;

关键字：ORDER BY 取一个或多个列的名字，据此对输出进行排序

SELECT prod_name FROM products ORDER BY prod_name;

tips：按多个列排序时，先对第一列排序，依次往后

指定排序方向：DESC(降序)，ASC(默认升序)

SELECT prod_id,prod_name FROM products ORDER BY prod_price DESC;

检索数据

关键字：WHERE , AND , OR , IN , NOT , BETWEEN

关键字：LIKE

%通配符：任意字符出现任意次数

SELECT prod_id,prod_name FROM products WHERE prod_name LIKE 'Jet%';

_通配符：匹配单个字符

SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '_ton';

tips：除非绝对有必要，不要把通配符用在搜索模式的开始处，这样搜索起来是最慢的

关键字：REGEXP(表达式)

SELECT prod_name FROM products WHERE prod_name REGEXP '10000';

若需要区分大小写，可使用BINARY关键字

WHERE prod_name REGEXP BINARY 'JetPack .000';

匹配类型

例子

OR匹配

'1000|2000'

匹配其中一个字符

'[123]'

匹配范围

'[1-5]'

匹配特殊字符(转义)

'\\.'

元字符

说明

*

0个或多个匹配

+

1个或多个匹配

?

0个或1个匹配

^

文本的开始

$

文本的结尾

例子：SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticts?\\)'；

解说：sticts?匹配stict和sticts(s后的?使s可选，因为?匹配它之前的任何字符的0次或1次出现)

例子：SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name；

解说：^匹配串的开始，因此，^[0-9\\.]只在.或任意数字为串中第一个字符时才匹配它们

tips：LIKE匹配整个串，而REGEXP匹配子串

创建计算字段

函数：Concat() 拼接串，即把多个串连接起来形成一个较长的串，用逗号分隔

关键字：AS 别名

SELECT Concat(vendor_name,'(',vendor_country,')') AS vendor_title FROM vendors ORDER BY vend_name;

SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;

数据处理函数

文本处理函数

Left()

返回串左边的字符

Right()

返回串右边的字符

Length()

返回串的长度

RTrim()

去掉串右边的空格

Locate()

找出串的一个子串

Soundex()

返回串的SOUNDEX值

Lower()

将串转换成小写

SubString

返回子串的字符

LTrim()

去掉串左边的空格

Upper()

将串转换为大写

日期和时间处理函数

AddDate()

增加一个日期(天/周等)

AddTime()

增加一个时间(时/分等)

CurDate()

返回当前日期

CurTime()

返回当前时间

Date()

返回日期时间的日期部分

DateDiff()

计算两个日期之差

Date_Add()

高度灵活的日期运算函数

Date_Format()

返回一个格式化的日期或时间串

Day()

返回一个日期的天数部分

DayOfWeek()

对于一个日期，返回对应的星期几

Hour()

返回一个时间的小时部分

Minute()

返回一个时间的分钟部分

Month()

返回一个日期的月份部分

Now()

返回当前日期和时间

Second()

返回一个时间的秒部分

Time()

返回一个日期时间的时间部分

Year()

返回一个日期的年份部分

数值处理函数

Abs()

返回一个数的绝对值

Cos()

返回一个角度的余弦

Exp()

返回一个数的指数值

Mod()

返回除操作的余数

Pi()

返回圆周率

Rand()

返回一个随机数

Sin()

返回一个角度的正弦

Sqrt()

返回一个数的平方根

Tan()

返回一个角度的正切

汇总函数

AVG()

返回某列的平均值

SELTCT AVG(prod_price) AS avg_price FROM products WHERE vend_id=1003;

COUNT()

返回某列的行数

SELECT COUNT(cust_email) AS num_cust FROM customers;

MAX()

返回某列的最大值

SELECT MAX(prod_price) AS max_price FROM products;

MIN()

返回某列的最小值

SELECT MIN(prod_price) AS min_price FROM products;

SUM()

返回某列值之和

SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num=20005;

DISTINCT参数：只包含不同的值

SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id=1003;

SELECT COUNT(DISTINCT cust_email) AS sum_cust FROM customers;

tip：DISTINCT不能用于COUNT(*)，必须使用列名，不能用于计算或表达式。

分组数据

关键字：GROUP BY指示MySQL分组数据，然后对每个组而不是整个数据集进行聚集

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;

tips：GROUP BY 字句可以包含任意数目的列，这使得能对分组进行嵌套，为数据分组提供更细致的控制。

tips：如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算(所以不能从个别的列取回  数据)

tips：GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。

tips：除聚集计算函数外，SELECT语句中的每个列都必须在GROUP BY子句中给出。

关键字：HAVING过滤分组

SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >=2;

tips：HAVING和WHERE的差别，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤

SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >=2;

子查询

利用子查询进行过滤

SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

作为计算字段使用子查询

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

联结

内部联结：

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

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

自联结

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';

tips：自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句，虽然最终结果是相同的，但有时候处理联结远比处理子查询快得多。

自然联结

外部联结

SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

使用带聚集函数的联结

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 GROUP BY customers.cust_id;

组合查询

关键字：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);

tips：UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔。

tips：UNION中的每个查询必须包含相同的列/表达式或聚集函数(不过各个列不需要以相同的次序列出)。

tips：UNION从查询结果中自动去除了重复的行。

关键字：UNION ALL，返回所有匹配行(包含重复行)

SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5

UNION ALL

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)

GROUP BY vend_id, prod_price;

tips：在用UNION组合查询时，只能使用一条GROUP BY子句，它必须出现在最后一条SELECT语句之后。

全文本搜索

创建表时启用全文本搜索，CREATE TABLE语句接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。

CREATE TABLE productnotes

(

note_id      int             NOT NULL AUTO_INCREMENT,

prod_id      char(10)   NOT NULL,

note_date  datetime   NOT NULL,

PRIMARY KEY(note_id),

FULLTEXT(note_text)

)ENGINE=MyISAM;

在定义之后，MySQL自动维护该索引。在增加/更新或删除行时，索引随之自动更新。

进行全文搜索，使用两个函数Math()指定被搜索的列和Against()指定要使用的搜索表达式

SELECT note_text FROM productnotes WHERE Math(note_text) Against('rabbit');

tips：传递给Math()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们(而且次序正确)。

tips：LIKE以不特别有用的顺序返回数据，全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回。等级由MySQL根据行中词的数目，唯一词的数目，整个索引中词的总数以及包含该词的行的数目计算出来。以下例子可展示排序如何工作：

SELECT note_text, Math(note_text) Against('rabbit') AS rank FROM productnotes;

查询扩展

查询扩展用来设法放宽所返回的全文本搜索结果的范围，先进行一个基本的全文本搜索，找出与搜索条件匹配的所有行，然后MySQL检查这些匹配行并选择所有有用的词，再次进行全文本搜索，这次不仅使用原本的条件，而且还使用所有有用的词。

SELECT note_text FROM productnotes WHERE Math(note_text) Against('anvils' WITH QUERY EXPANSION);

布尔文本搜索

与全文本搜索不同的地方在于，即使没有定义FULLTEXT索引，也可以使用布尔文本搜索。

以布尔方式，可以提供如要匹配的词，要排斥的词，排列提示，表达式分组等等的内容。

SELECT note_text FROM productnotes WHERE Math(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

全文本布尔操作符有：+  -  > 

插入数据

插入一行

INSERT INTO customers(cust_name, cust_address, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('Pep E . LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);

tips：INSERT操作可能很耗时(特别是有很多索引需要更新时)，而且它可能降低等待处理的SELECT语句的性能。如果数据检索是最重要的(通常是这样)，则你可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级。】

插入多行

INSERT INTO customers(cust_name, cust_address, cust_state, cust_zip, cust_country) VALUES('Pep E . LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA'), VALUES('M. Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');

插入检索处的数据

INSERT INTO customers(cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) SELECT cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country FROM custnew;

tips：事实上，MySQL不关心SELECT返回的列名，它使用的是列的位置，因为SELECT中的第一列将用来填充表列中指定的第一列，以此类推。这对于从使用不同列名的表中导入数据是非常有用的。

SELECT字句顺序

SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT