《MySQL必知必会》读书笔记_3

PS:这次的信息量有点大。

聚集不同值

SELECT AVG(DISTINCT prod_price) AS avg_price

FROM products

WHERE vend_id = 1003

#相同的值不会被计算

 

组合聚集函数

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 vend_id FROM products

#创建分组的样子

SELECT vend_id,COUNT(*) AS num_prods

FROM products

GROUP BY vend_id

 

 

P84~P96

 

主键：唯一标识

外键：某个表的主键值，包含在另一个表中的一列，定义了两个表之间的关系。

PS：外键约束，会导致如果删除表A中的一行数据，如果表A的主键为表B的外键的话，并且表B中有数据引用外键为删除数据，那么会删除失败。需要先接触约束，删除表B中的数据，才能删除表A中的数据。

 

PS：需要学习数据库范式的概念，才能更加了解书中所说的设计方式。

 

联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。

联结不是物理实体，它在实际的数据库表中不存在。

SELECT vend_name, prod_name, prod_price

FROM vendors, products

WHERE vendors.vend_id = products.vend_id

ORDER BY vend_name, prod_name

 

 

笛卡尔积：由于没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。（PS：（R*S））

SELECT vend_name, prod_name, prod_price

FROM vendors, products

#WHERE vendors.vend_id = products.vend_id

ORDER BY vend_name, prod_name

 

 

数据量很大时，应该保证所有的联结都有WHERE子句，这样可以减少返回数据量，同理也要保证子句的正确性。

 

内部联结，与上面的联结语句结果相同，不过按照规范首选下面的语句。

SELECT vend_name, prod_name, prod_price

FROM vendors INNER JOIN products

ON vendors.vend_id = products.vend_id

 

多做实验：为执行任一给定的SQL操作，一般存在不止一种方法。很少有绝对正确或绝对错误的方法。性能会受很多因素影响，因此有必要对不同的选择机制进行实验，以找出最适合具体情况的方法。

 

外连接

SELECT customers.cust_id, orders.order_num

FROM customers LEFT OUTER JOIN orders

ON customers.cust_id = orders.cust_id

 

MySQL下没有*=操作符，其它DBMS中很流行。

外部链接类型有两种形式：左外连接和右外连接。唯一的差别是所关联的表的顺序不同。具体使用哪一种纯粹根据方便而定。

 

带聚集函数的联结

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

 

SELECT customers.cust_name,

 customers.cust_id,

 COUNT(orders.order_num) AS num_ord

FROM customers LEFT OUTER JOIN orders

ON customers.cust_id = orders.cust_id

GROUP BY customers.cust_id

 

UNION组合查询：将两个SELECT语句的结果和到一起

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从查询结果中自动去除重复行，是默认行为。要想返回所有行需要使用UNION ALL。

 

对组合查询结果排序：在语句的最后ORDER BY 用来排序所有SELECT语句返回的结果。

 

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

不要在导入数据时使用FULLTEXT，更新索引需要耗费时间。

在索引后使用两个函数：MATCH()指定被搜索的列；AGAINST()指定要使用的搜索表达式。

 

SELECT note_text

FROM productnotes

WHERE Match(note_text) Against('rabbit')

 

传递给Match()的值必须与FULLTEXT()定义中的相同。

除非使用BINARY，否则全文本搜索不区分大小写。

 

使用查询扩展

SELECT note_text

FROM productnotes

WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION)

 

 

布尔文本搜索

SELECT note_text

FROM productnotes

WHERE Match(note_text) Against('anvils' IN BOOLEAN MODE)

 

 

插入检索出的数据

INSERT INTO tablename1(filed1,filed2) SELECT filed1,filed2 FROM tablename2

 

更新多个字段中的数据

UPDATE  tablename1

SET filed1 = value1,

   filed2 = value2

WHERE filed3 = value3

 

更快的删除表中所有行，使用TRUNCATE TABLE语句。

 

 

#创建表

CREATE TABLE vendors

{

vend_id INT NOT NULL AUTO_INCREMENT, #自增属性

vend_name CHAR(50) NOT NULL, #不为空属性

vend_city CHAR(50) NULL, #允许为空属性

vend_age int NOT NULL DEFAULT 1, #默认值为1，不允许使用函数，只能用常亮

PRIMARY KEY (vend_id, vend_name) #联合主键，可以单个字段做主键

} ENGINE = INNODB #选择引擎，如果没有默认为MyISAM

 

外键不能跨引擎。

 

常用数据库引擎

InnoDB是一个可靠地事务处理引擎，它不支持全文本搜索。

MEMORY在功能上等同于MyISAM,但由于数据存储在内存中，速度很快

MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

 

更新表

ALTER TABLE tablename1

ADD filed1 CHAR(20)

 

ALTER TABLE tbalename2

DROP COLUMN filed1

 

删除表

DROP TABLE tablename1

 

重命名表

RENAME TBALE oldtablename TO newtablename

 

删除存储过程

DROP PROCEDURE procedurename

 

使用参数的存储过程

CREATE PROCEDURE procedurename( #创建存储过程

IN prfiled1 int, #接收来自外部参数

OUT prfiled2 DECIMAL(8,2) #输出结果到外部

)

BEGIN

SELECT Min(filed1) #内部查询语句

INTO prfiled2 #内部查询结果输出到变量

FROM table1;

WHERE filed2 = prfiled1 #使用接收外部参数作为查询条件

END;

 

执行存储过程

CALL procedurename(@prfiled1,@prfiled2)

SELECT @prfiled2