《MySQL必知必会》读书笔记
生活随笔
收集整理的這篇文章主要介紹了
《MySQL必知必会》读书笔记
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
數據庫（database）的概念：數據庫是一個以某種有組織的形式存儲的數據集合，也可以稱為 “保存有組織數據的容器”。
表（table）：某種特定類型數據的結構化清單，同一個數據庫中不能有兩張相同名字的表。
模式（schema）：關于數據庫和表的布局及特性的信息（比如，定義數據在表中如何存儲；存儲什么樣的數據；如何分解；各部分信息如何命名等。）
列（column）：表中的一個字段，所有的表都是由一個或多個列組成。
數據類型（datatype）：所容許的數據的類型，每個表列都有相應的數據類型，它限制（容許）該列種存儲的數據。數據類型可幫助正確的排序，優化磁盤的使用，在創建表時，需特別關注數據類型的選擇。
行（row）：表中的記錄是按行存儲的，行就是表中的一個記錄。
主鍵（primary key）：每一行的唯一標識用以區分每一個獨立的行，而這個列稱為主鍵，沒有主鍵，更新或刪除特定的行很困難，因為沒有安全的方法保證只涉及相關的行。
主鍵規則：應該總是定義主鍵；任意兩行都不具有相同的主鍵；每一行都必須有有一個主鍵值（主鍵值不允許為null值）
主鍵的幾個好習慣：1.不更新主鍵列中的值 2.不重用主鍵列種的值 3.不再主鍵列種使用會更改的值（比如 name 就不適合）。
自動增量：某些表列需要唯一值，例如：訂單編號、雇員ID等；每個行添加到表中時，MYSQL可以自動的為每個行分配一個可用編號、不用再添加時手動分配唯一值，這個功能就是所謂的自動增量。
create.sql:
######################################## # MySQL Crash Course # http://www.forta.com/books/0672327120/ # Example table creation scripts ################################################################ # Create customers table ######################## CREATE TABLE customers (cust_id int NOT NULL AUTO_INCREMENT,cust_name char(50) NOT NULL ,cust_address char(50) NULL ,cust_city char(50) NULL ,cust_state char(5) NULL ,cust_zip char(10) NULL ,cust_country char(50) NULL ,cust_contact char(50) NULL ,cust_email char(255) NULL ,PRIMARY KEY (cust_id) ) ENGINE=InnoDB;######################### # Create orderitems table ######################### 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;##################### # Create orders table ##################### CREATE TABLE orders (order_num int NOT NULL AUTO_INCREMENT,order_date datetime NOT NULL ,cust_id int NOT NULL ,PRIMARY KEY (order_num) ) ENGINE=InnoDB;####################### # Create products table ####################### CREATE TABLE products (prod_id char(10) NOT NULL,vend_id int NOT NULL ,prod_name char(255) NOT NULL ,prod_price decimal(8,2) NOT NULL ,prod_desc text NULL ,PRIMARY KEY(prod_id) ) ENGINE=InnoDB;###################### # Create vendors table ###################### CREATE TABLE vendors (vend_id int NOT NULL AUTO_INCREMENT,vend_name char(50) NOT NULL ,vend_address char(50) NULL ,vend_city char(50) NULL ,vend_state char(5) NULL ,vend_zip char(10) NULL ,vend_country char(50) NULL ,PRIMARY KEY (vend_id) ) ENGINE=InnoDB;########################### # Create productnotes table ########################### 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;##################### # Define foreign keys ##################### ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num); ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id); ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id); ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);數據sql:
######################################## # MySQL Crash Course # http://www.forta.com/books/0672327120/ # Example table population scripts ################################################################## # Populate customers table ########################## INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com'); INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact) VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse'); INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com'); INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com'); INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact) VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');######################## # Populate vendors table ######################## INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA'); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA'); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA'); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA'); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England'); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');######################### # Populate products table ######################### INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');####################### # Populate orders table ####################### INSERT INTO orders(order_num, order_date, cust_id) VALUES(20005, '2005-09-01', 10001); INSERT INTO orders(order_num, order_date, cust_id) VALUES(20006, '2005-09-12', 10003); INSERT INTO orders(order_num, order_date, cust_id) VALUES(20007, '2005-09-30', 10004); INSERT INTO orders(order_num, order_date, cust_id) VALUES(20008, '2005-10-03', 10005); INSERT INTO orders(order_num, order_date, cust_id) VALUES(20009, '2005-10-08', 10001);########################### # Populate orderitems table ########################### INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20005, 1, 'ANV01', 10, 5.99); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20005, 2, 'ANV02', 3, 9.99); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20005, 3, 'TNT2', 5, 10); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20005, 4, 'FB', 1, 10); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20006, 1, 'JP2000', 1, 55); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20007, 1, 'TNT2', 100, 10); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20008, 1, 'FC', 50, 2.50); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20009, 1, 'FB', 1, 10); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20009, 2, 'OL1', 1, 8.99); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20009, 3, 'SLING', 1, 4.49); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20009, 4, 'ANV03', 1, 14.99);############################# # Populate productnotes table ############################# INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(101, 'TNT2', '2005-08-17', 'Customer complaint: Sticks not individually wrapped, too easy to mistakenly detonate all at once. Recommend individual wrapping.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(102, 'OL1', '2005-08-18', 'Can shipped full, refills not available. Need to order new can if refill needed.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(103, 'SAFE', '2005-08-18', 'Safe is combination locked, combination not provided with safe. This is rarely a problem as safes are typically blown up or dropped by customers.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(104, 'FC', '2005-08-19', 'Quantity varies, sold by the sack load. All guaranteed to be bright and orange, and suitable for use as rabbit bait.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(105, 'TNT2', '2005-08-20', 'Included fuses are short and have been known to detonate too quickly for some customers. Longer fuses are available (item FU1) and should be recommended.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(106, 'TNT2', '2005-08-22', 'Matches not included, recommend purchase of matches or detonator (item DTNTR).' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(107, 'SAFE', '2005-08-23', 'Please note that no returns will be accepted if safe opened using explosives.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(108, 'ANV01', '2005-08-25', 'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(109, 'ANV03', '2005-09-01', 'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(110, 'FC', '2005-09-01', 'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(111, 'SLING', '2005-09-02', 'Shipped unassembled, requires common tools (including oversized hammer).' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(112, 'SAFE', '2005-09-02', 'Customer complaint: Circular hole in safe floor can apparently be easily cut with handsaw.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(113, 'ANV01', '2005-09-05', 'Customer complaint: Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(114, 'SAFE', '2005-09-07', 'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added. Comment forwarded to vendor.' );?
?
#################檢索數據################### / / 檢索單個列 SELECT prod_name FROMproducts ;/ / 檢索多個列 SELECT prod_id,prod_name,prod_price FROMproducts ;/ / 檢索所有列 SELECT * FROMproducts ;/ / 檢索不同行（查詢products表中產品的所有供應商ID） SELECT vend_id FROMproducts ;SELECT DISTINCT vend_id FROMproducts ;-- 使用 DISTINCT 去重 / / 使用 DISTINCT 去重多列時，查出的是唯一的一行 只有AAA BBB CCC 都不同 才會被過濾 否則所有行都將被檢索出來 SELECT DISTINCT AAA,BBB,CCC FROMtable_name ;/ / 檢索前5行 帶一個值得LIMIT總是從第一行開始檢索 SELECT prod_name FROMproducts LIMIT 5 ;/ / 檢索第二個5行 (從第5行開始的后5行數據 ) 第一個數為開始位置 第二個數為檢索行數 SELECT prod_name FROMproducts LIMIT 5, 5 ;/ / 行0 檢索出來的第一行是0而不是行1 因此LIMIT (1, 1) 檢索出來的是第1行 / / 行數不夠時：LIMIT指定檢索的行數為檢索的最大行數，如果沒有足夠的行 (例如：給出LIMIT 10,5 但只有13行 ) ，MYSQL將只返回它能返回的那么多行 / / LIMIT 4 OFFSET 3 從行3開始取4行數據 / / 完全限定列名 (表名.列名) 表名（庫名.表名） SELECT products.`prod_name` FROMtest.`products` ;#################排序檢索數據################### / / 關系型數據庫設計理論認為，如果不規定排序順序，則不應該假定檢索出來的順序有意義 SELECT products.`prod_name` FROMproducts ;/ / 按照單個列排序 SELECT prod_name FROMproducts ORDER BY prod_name ;/ / 按照多個列排序 先按價格，再按名稱 SELECT prod_id,prod_price,prod_name FROMproducts ORDER BY prod_price,prod_name ;/ / 指定排序方向 默認時升序（A - Z） 降序（Z - A） 使用desc關鍵字 按價格降序檢索 SELECT prod_id,prod_price,prod_name FROMproducts ORDER BY prod_price DESC ;/ / 多個列降序排序 以降序排序產品（最貴的在前面）, 然后再對產品名排序 / / 下面只有prod_price 按照降序，prod_name仍然默認排序，如果要按照多個列排序 每個列必須都要指定 DESC 關鍵字 SELECT prod_id,prod_price,prod_name FROMproducts ORDER BY prod_price DESC,prod_name / / 使用 ORDER BY 和 LIMIT 組合找出一個列中最高或最低的值 SELECT prod_price FROMproducts ORDER BY prod_price DESC LIMIT 1 ;#################過濾數據################### / / 檢索prod_price = 2.50 的行的 prod_name prod_price列數據 SELECT prod_name,prod_price FROMproducts WHERE prod_price = 2.50 ;/ / 檢索prod_name = 'fuses' 的一行 mysql在執行匹配時不區分大小寫 SELECT prod_name,prod_price FROMproducts WHERE prod_name = 'fuses' ;/ / 檢索價格在5美元和10美元之間的所有產品 (BETWEEN 包括開始值 和 結束值 ) SELECT prod_name,prod_price FROMproducts WHERE prod_price BETWEEN 5 AND 10 ;/ / 空值檢查 SELECT prod_name FROMproducts WHERE prod_price IS NULL ;SELECT * FROMcustomers WHERE cust_email IS NULL ;/ / AND 操作符 SELECT prod_id,prod_price,prod_name FROMproducts WHERE vend_id = 1003 AND prod_price <= 10 ;/ / OR 操作符 SELECT prod_id,prod_price,prod_name FROMproducts WHERE vend_id = 1002 OR vend_id = 1003 ;/ / OR 和 AND 一起使用時 SELECT prod_id,prod_price,prod_name FROMproducts WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10 ;SELECT prod_id,prod_price,prod_name FROMproducts WHERE (vend_id = 1002 AND prod_price >= 10) OR (vend_id = 1003 AND prod_price >= 10) / / IN操作符 檢索1002 和 1003 產商生產的產品 SELECT prod_id,prod_price,prod_name FROMproducts WHERE vend_id IN (1002, 1003) ;SELECT prod_id,prod_price,prod_name FROMproducts WHERE vend_id NOT IN (1002, 1003) ;/ / LIKE % 任意字符出現任意次數 SELECT prod_id,prod_price,prod_name FROMproducts WHERE prod_name LIKE 'jet%' ;/ / _ 匹配單個字符 SELECT prod_id,prod_price,prod_name FROMproducts WHERE prod_name LIKE '_ ton anvil' ;SELECT prod_id,prod_price,prod_name FROMproducts WHERE prod_name LIKE '% ton anvil' ;/ / 正則表達式匹配 REGEXP SELECT prod_id,prod_price,prod_name FROMproducts WHERE prod_name REGEXP '1000' ;/ / .匹配任意一個字符 SELECT prod_id,prod_price,prod_name FROMproducts WHERE prod_name REGEXP '.000' ;/ / | 匹配兩個中的任意一個 SELECT prod_id,prod_price,prod_name FROMproducts WHERE prod_name REGEXP '1000|2000' ;/ / 匹配幾個字符之一 [ 123 ] 定義一組字串 匹配1或2或3 SELECT prod_name FROMproducts WHERE prod_name REGEXP '[123] ton' ORDER BY prod_name ;SELECT prod_name FROMproducts WHERE prod_name REGEXP '[1-5] ton' ORDER BY prod_name ;/ / 正則匹配特殊字符.使用 \\ 進行轉義（一般時一個反斜杠 mysql兩個 一個mysql自己解釋 一個正則表達式要求） SELECT prod_name FROMproducts WHERE prod_name REGEXP '\\.' ORDER BY prod_name ;#################計算字段################### / / 拼接字段 把兩個單列值拼接 RTRIM() 刪除括號內字符串右側多余空格 LTRIM() 刪除括號內字符串左側多余空格 TRIM() 去掉兩邊的空格 SELECT CONCAT(vend_name,'(',RTRIM(vend_country),')') AS 供應商名稱及地址 FROMvendors ORDER BY vend_name ;SELECT CONCAT('(',vend_id,',',vend_name,',location:',vend_country,')') FROMvendors ORDER BY vend_name ;/ / 算數操作 / / 檢索出訂單表產品id, 物品數量，物品單價 SELECT prod_id,quantity,item_price FROMorderitems WHERE order_num = 20005 ;/ / 檢索出訂單表產品id, 物品數量，物品單價 并匯總價格 SELECT prod_id,quantity,item_price,quantity * item_price AS '每種物品價格總計' FROMorderitems WHERE order_num = 20005 ;#################使用數據處理函數################### / / UPPER() 將文本轉換為大寫 SELECT vend_name,UPPER(vend_name) AS 大寫的供應商名 FROMvendors ;/ / 日期和時間處理函數 SELECT cust_id,order_num FROMorders WHERE order_date = '2005-09-01' ;/ / 這樣比較更精確 只比較日期部分 SELECT * FROMorders WHERE DATE(order_date) = '2005-09-01' ;/ / 監測2005年9月下的所有訂單 SELECT * FROMorders WHERE DATE(order_date) BETWEEN '2005-09-01' AND '2005-09-30' ;或 SELECT * FROMorders WHERE YEAR(order_date) = 2005 AND MONTH(order_date) = 9 ;/ / 聚合函數 / / AVG 平均值 忽略為NULL的行 SELECT prod_id,vend_id,prod_name,prod_price,AVG(prod_price) AS 所有產品的平均價格,prod_desc FROMproducts ;/ / COUNT(*) 對表中的行數目進行計數，不管表列中包含的是空值 (NULL) 還是非空值 SELECT COUNT(*) AS num_cust FROMcustomers ;/ / 使用 COUNT(COLUMN) 對特定列中具有值的行進行計數 不管行中各列有什么值 SELECT COUNT(cust_email) AS num_email_cust FROMcustomers ;/ / MAX(COLUMN) 返回指定列中的最大值 MIN(COLUMN) 返回指定列最小值 都忽略null值 SELECT MAX(prod_price) AS max_price FROMproducts ;/ / SUM() / / 用于檢索物品總數 或者計算值的總和 SELECT SUM(item_price * quantity) AS total_2005_mt FROMorderitems WHERE order_num = 20005 ;/ / 聚集函數使用 SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min,MAX(prod_price) AS price_max,AVG(prod_price) AS price_avg FROMproducts ;/ / 數據分組 GROUP BY / / 返回供應商1003提供的產品數目 SELECT * FROMproducts ;/ / 返回各個供應商提供的產品數目 SELECT vend_id,COUNT(*) AS num_prods FROMproducts GROUP BY vend_id ;/ / 使用 WITH ROLLUP 關鍵字 可以得到每個分組以及每個分組匯總級別 SELECT VEND_ID,COUNT(*) AS num_prods FROMproducts GROUP BY vend_id WITH ROLLUP ;/ / 過濾分組 WHERE 過濾行 HAVING 過濾分組 where是在數據分組前進行過濾 having是在數據分組后進行過濾 / / 檢索至少有兩個訂單的顧客 SELECT cust_id,COUNT(*) AS orders FROMorders GROUP BY cust_id HAVING COUNT(*) >= 2 ;/ / 檢索在過去163月內具有兩個以上訂單的顧客 SELECT cust_id,COUNT(*) AS num_order FROMorders WHERE PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'),DATE_FORMAT(order_date, '%Y%m')) <= 163 GROUP BY cust_id HAVING COUNT(*) >= 2 ;/ / 檢索價格10（含）以上，具有2個（含）以上的供應商 SELECT vend_id,COUNT(*) AS num_prods FROMproducts WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2 ;//分組排序 //檢索總計訂單大于等于50的訂單號和總計訂單價格 并按照總計訂單價格asc排序 SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >=50 ORDER BY ordertotal;//SELECT子句順序 SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT//子查詢 1.利用子查詢做數據過濾 //檢索需要訂購物品TNT2的所有客戶//1.先查出訂單明細表中 訂購TNT2的訂單編號 SELECT order_num FROM orderitems WHERE prod_id='TNT2'; //2.根據order_num 可以在訂單表中 查詢出cust_id SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROMorderitems WHERE prod_id = 'TNT2'); //3.根據cust_id查詢出customers中顧客信息 SELECT * FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2')); //作為計算字段使用子查詢 //檢索customers表中每個客戶的訂單總數//1.先檢索訂單表中每個客戶有多少個訂單 SELECT COUNT(*) order_total FROM orders GROUP BY cust_id; //2.在customers表中，根據cust_id檢索出每個客戶的訂單數目 SELECT cust_id,cust_name,(SELECT COUNT(*) order_total FROM orders WHERE orders.`cust_id` = customers.`cust_id`) AS '訂單總數' FROM customers ORDER BY cust_name;//聯結查詢 //等值聯結 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` ORDER BY vend_name,prod_name;//多表聯結 SELECT prod_name,vend_name,prod_price,quantity FROM orderitems,products,vendors WHERE orderitems.`prod_id` = products.`prod_id` AND products.`vend_id` = vendors.`vend_id` AND order_num = 20005;//使用多表查詢檢索需要訂購物品TNT2的所有客戶（子查詢,多表聯結查詢） SELECT cust_name,cust_contact FROM customers WHERE customers.`cust_id` IN (SELECT cust_id FROM orders INNER JOIN orderitems ON orders.`order_num` = orderitems.`order_num` AND orderitems.`prod_id` = 'TNT2');SELECT cust_name,cust_contact FROM customers,orders,orderitems WHERE customers.`cust_id` = orders.`cust_id` AND orders.`order_num` = orderitems.`order_num` AND orderitems.`prod_id` = 'TNT2';//創建高級聯結 //1.使用列別名 使用別名引用被檢索的表列，列別名可以返回到程序客戶機 好處：1.縮短sql,可讀性增強 2.允許單挑select語句多次使用相同表 SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;//使用表別名進行限定，表別名不返回客戶機 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 oi.`prod_id` = 'TNT2';//自聯結 //檢索物品為DTNTR的供應商生產的所有物品 //1.使用子查詢檢索 SELECT prod_id,prod_name FROM products WHERE products.`vend_id` IN (SELECT vend_id FROM products AS p WHERE p.prod_id = 'DTNTR');//從相同表檢索數據，大多數情況下自聯結查詢效率會比子查詢快得多 返回的列必須指定那張表的，即使兩張表一樣，否則mysql找不到 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` FROM customers INNER JOIN orders ON customers.`cust_id` = orders.`cust_id`;//檢索所有客戶包括那些沒有訂單的客戶 //左外部聯結和右外部聯結（左外就是用join左邊所有行，右外就是join右邊所有行） 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 total_order 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 total_order FROM customers LEFT OUTER JOIN orders ON customers.`cust_id` = orders.`cust_id` GROUP BY customers.`cust_id`;//組合查詢（復合查詢） UNION 會自動去除重復的 //檢索價格小于等于5的所有物品 而且還想包括供應商1001和1002生產的所有物品(不考慮價格) SELECT vend_id,prod_name,prod_price FROM products WHERE prod_price <=5 UNION SELECT vend_id,prod_name,prod_price FROM products WHERE vend_id IN (1001,1002);//多個where子句 SELECT vend_id,prod_name,prod_price FROM products WHERE prod_price<=5 OR vend_id IN (1001,1002);//UNION ALL 返回所有匹配行 SELECT vend_id,prod_name,prod_price FROM products WHERE prod_price <=5 UNION ALL SELECT vend_id,prod_name,prod_price FROM products WHERE vend_id IN (1001,1002);//對組合查詢結果進行排序 SELECT vend_id,prod_name,prod_price FROM products WHERE prod_price <=5 UNION SELECT vend_id,prod_name,prod_price FROM products WHERE vend_id IN (1001,1002) ORDER BY vend_id,prod_price;//最常用的引擎為 MYISAM(支持全文本搜索) 和 INNODB //全文索引 FULLTEXT 不要再導入數據的時候開啟全文索引 因為更新索引花費時間 //使用 MATCH() 指定搜索列 和 AGAINST()指定搜索內容 全文本索引排序會按照含詞等級來，等級值高的排在前面SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('rabbit');//查看全文索引排序等級 SELECT note_text,MATCH(note_text) AGAINST('rabbit') AS rank FROM productnotes;//未使用查詢拓展 SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('anvils');//查詢拓展 查詢匹配的相關內容 SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('anvils' WITH QUERY EXPANSION);//布爾文本搜索 不依賴全文索引 但數據量大性能低 IN BOOLEAN MODE 沒有指定布爾操作符 SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('heavy' IN BOOLEAN MODE); //檢索包含heavy但不包含任意rope開始的詞的行 SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('heavy -rope*' IN BOOLEAN MODE);//全文索引 短詞被忽略(3個或3個以下) 詞列表里的詞被忽略 50%規則 一個詞超過50%以上行忽略//INSERT操作可能很耗時，而且它可能降低等待處理的select語句性能，如果數據檢索最重要，可以在 INSERT 和 INTO 中間添加關鍵字 LOW_PRIORITY update和delete 也適應此規則 // INSERT 將一條select語句的結果插入到表中 select后面的列不需要和insert的列名一致，mysql僅僅把查詢結果當作值來處理 INSERT INTO tableA(a,b,c)SELECT a,b,c FROM tableB;//UPDATE 如果更新多條發生錯誤會進行回滾 如果不想回滾可使用 IGNORE 例如：update IGNORE tableA//如果刪除表中指定行用delete 所過是想刪除表中所用行 用 TRUNCATE TABLE 完成相同的工作 但更快 原理：刪除原來的表 建立新表//創建表： CREATE TABLE IF NOT EXISTS tableName //主鍵：唯一標識每一行數據的列，唯一且不為nullSELECT LAST_INSERT_ID(cust_id) FROM customers;//引擎:INNODB :一個可靠的事務引擎，但不支持全文本搜索MEMORY :功能上等同于 MYISAM ,但數據存儲在內存，不是磁盤 速度很快MYISAM :性能極高的引擎，支持全文本搜索，但不支持事務處理//視圖 虛擬的表 本身沒有數據 而是一種需要檢索的數據的查詢 視圖不能被索引 //創建視圖 cerate VIEW viewname; //查看創建的視圖 SHOW cerate VIEW viewname; //刪除視圖 DROP VIEW viewname; //更新視圖 可以先drop 再create 也可以使用create OR REPLACE VIEW //視圖的用途：1.簡化復雜聯結 2.格式化檢索的結果數據（比如拼接字段） 3.過濾不想要的數據 4.使用視圖獲取計算字段結果集SELECT cust_name,cust_contact FROM customers,orders,orderitems WHERE customers.`cust_id` = orders.`cust_id` AND orderitems.`order_num` = orders.`order_num` AND prod_id = 'TNT2';//創建視圖 CREATE VIEW productcustomers AS SELECT cust_name,cust_contact,prod_id FROM customers,orders,orderitems WHERE customers.`cust_id` = orders.`cust_id` AND orderitems.`order_num` = orders.`order_num`;//刪除 DROP VIEW productcustomersSELECT cust_name,cust_contact FROM productcustomers WHERE prod_id = 'TNT2';?
?
總結
以上是生活随笔為你收集整理的《MySQL必知必会》读书笔记的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： webstorm 使用技巧
- 下一篇： 000-EMC 深入解读-目录