當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

《MySQL必知必会》读书笔记

發布時間：2023/12/20 数据库 27 豆豆

生活随笔收集整理的這篇文章主要介紹了《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必知必会》读书笔记的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。