《mysql必知必会》读书笔记

信息

《MySQL必知必会》 福塔 人民邮电出版社

摘录

基本概念

数据库

保存有组织的数据的容器（通常是一个文件或一组文件）。

表

某种特定类型数据的结构化清单。

模式

关于数据库和表的布局及特性的信息。有时，模式用作数据库的同义词。

主键

一列或一组列，其值可以唯一区分表中的每一行。应该总是定义主键。

使用

连接

使用mysql命令行连接或者图形化客户端连接，本人使用MySqlWorkbench图形化客户端。

选择库

在mysql命令行中通过use xx选择数据库，在MySqlWorkbench中通过双击库来选择数据库

检索

SELECT prod_id, prod_name, prod_price FROM products;
SELECT * FROM products;
-- 检索不同行
SELECT DISTINCT vend_id FROM products;
SELECT prod_name FROM products LIMIT 5;
-- 从行5开始最多展示5行，MySql的行从0开始计数
SELECT prod_name FROM products LIMIT 5, 5;
-- 全限定表明
SELECT products.prod_name FROM crashcourse.products;

排序

-- 只对prod_price倒序
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;
-- 最贵商品
FROM products ORDER BY prod_price DESC LIMIT 1;

过滤

SELECT prod_name, prod_price FROM products WHERE prod_price = 2. 50;

WHERE 子句操作符 BETWEEN AND 在指定两个值之间（包含）

SELECT prod_name FROM products WHERE prod_price IS NULL;

组合过滤，不要依赖默认计算次序，多用括号

SELECT prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

IN一般比OR执行更快

SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003) ORDER BY prod_name;

MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反

SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003) ORDER BY prod_name;

匹配

通配符LIKE

正则表达式REGEXP

计算字段

SELECT Concat( RTrim( vend_name), ' (', RTrim( vend_country), ')') AS vend_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;

函数

聚集函数

AVG()\COUNT()\MAX()\MIN()\SUM()

SELECT AVG( prod_price) AS avg_price FROM products;

使用COUNT(*)对表中行的数目进行计数, 使用COUNT(column) 对特定列中具有值的行进行计数，忽略NULL值。

SELECT SUM( item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;

分组

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

GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

WHERE在数据分组前进行过滤， HAVING在数据分组后进行过滤。

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

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

查询子句顺序

SELECT | FROM | WHERE | GROUP BY | HAVING | ORDER BY | LIMIT

子查询

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

联结

外键

外键为某个表中的一列，它包含另一个表的主键，定义了两个表之间的关系

存在一个商品表和一个供应商信息表，供应商ID是供应商信息表的主键，这个供应商ID是商品表的==外键==，它将商品表和供应商信息表关联。

联结样例

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

SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON orders.cust_id = 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;

组合

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

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

插入

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

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 York', 'NY', '11213', 'USA' );

更新和删除

更新

UPDATE customers SET cust_email = 'elmer@ fudd. com' WHERE cust_id = 10005;

UPDATE customers SET cust_email = NULL WHERE cust_id = 10005;

删除

DELETE FROM customers WHERE cust_id >= 10010;

全表删除使用TRUNCATE TABLE更快

注意

使用UPDATE和DELETE使用WHERE之前，先用SELECT进行测试，MySQL没有撤销功能。

表

建表

CREATE TABLE customers ( cust_id int NOT NULL AUTO_INCREMENT, cust_name char( 50) NOT NULL , cust_address char( 50) , cust_city char( 50) , cust_state char( 5) , cust_zip char( 10) , cust_country char( 50) , cust_contact char( 50) , cust_email char( 255) , PRIMARY KEY (cust_id) ) ENGINE= InnoDB;

MySQL数据类型

MySQL数据类型：字符串、数值、日期、二进制，具体再查询

联合主键

CREATE TABLE orderitems ( order_num int NOT NULL , order_item int NOT NULL , prod_id char( 10) NOT NULL , quantity int NOT NULL , item_price decimal( 8, 2) NOT NULL , PRIMARY KEY (order_num, order_item) ) ENGINE= InnoDB;

自增长

每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）。

常用引擎

InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；
MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

更新表

ALTER TABLE vendors ADD vend_phone CHAR(20);

删除表

DROP TABLE customers2;

重命名表

RENAME TABLE customers2 TO customers;

视图

视图可以看成虚拟表，仅仅是用来查看存储在别处的数据的一种设施，视图本身不包含数据，它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。

CREATE VIEW orderitemsexpanded AS SELECT order_num, prod_id, quantity, item_price, quantity* item_price AS expanded_price FROM orderitems;

事务

事务处理（transactionprocessing）可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。

事务（transaction）指一组SQL语句；
回退（rollback）指撤销指定SQL语句的过程；
提交（commit）指将未存储的SQL语句结果写入数据库表；
保留点（savepoint）指事务处理中设置的临时占位符（place-holder），你可以对它发布回退（与回退整个事务处理不同）

回退

SELECT * FROM ordertotals; 
START TRANSACTION; 
DELETE FROM ordertotals; 
SELECT * FROM ordertotals; 
ROLLBACK; 
SELECT * FROM ordertotals;

INSERT、UPDATE和DELETE可以回退，CREATE或DROP不能回退

提交

START TRANSACTION; 
DELETE FROM orderitems WHERE order_ num = 20010; 
DELETE FROM orders WHERE order_ num = 20010; 
COMMIT;

当COMMIT或ROLLBACK语句执行后，事务会自动关闭（将来的更改会隐含提交）。

保留点

SAVEPOINT delete1;
ROLLBACK TO delete1;

保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放。