知識點-數(shù)據(jù)庫

1、show databases;/顯示所有數(shù)據(jù)庫use db-name;/使用數(shù)據(jù)庫show tables;/顯示該數(shù)據(jù)庫中的所有表show columns from ta_name; 或者describe ta_name;/顯示表的詳細信息help show;/顯示所有show語句檢索數(shù)據(jù)select 從表中檢索出一個或多個數(shù)據(jù)列select prod_name from products;/從表中檢索出prod_name列select*from products;/從表中檢索出所有列select distinct vend_id from products;/從表中檢索vend_id列,只返

2、回不同的值select prod_name from products limit 5,5;/從表中檢索prod_name列,從行5開始檢索4行;(行號從0開始select d_name from duces;/使用完全限定的表名排序檢索數(shù)據(jù)select+order by 對檢索的結果進行排序,默認為升序select prod_name from products order by prod_name;/從表中檢索prod_name列,按升序排列select prod_name,prod_price from products order

3、by prod_price,prod_name;/從表中檢索出prod_name,prod_price列,按prod_price升序排列,prod_price相同時按prod_name升序排列select prod_name from products order by prod_name desc;/從表中檢索出prod_name列,按降序排列select prod_name,prod_price from products order by prod_price desc,prod_name;/ 從表中檢索出prod_name,prod_price列,按prod_price降序排列,pro

4、d_price相同時按prod_name升序排列select prod_name from products order by prod_name desc limit 5;/從表中檢索prod_name列,按降序排列,且只返回5行select+where 指定搜索條件 /在表中查找prod_name=2.5的prod_name,prod_price列order by 應該放在where之后.select prod_name,prod_price from products where prod_price between 5 and 10;/select prod_name from pro

5、ducts where prod_price is null;/查找prod_price為空的prod_name列數(shù)據(jù)過濾select+where+or/and/in/not in 指定搜索條件select prod_id,prod_name from products where vend_id=1002 or vend_id=1003 and prod_price<=10; /and的優(yōu)先級高于or,在本語句中執(zhí)行順序為vend_id=1002 and prod_price<=10 or vend_id=1003。若要按自己需要的順序執(zhí)行,需要加括號。select prod_n

6、ame,prod_price from products where vend_id in (1002,1003;/檢索供應商1002和1003提供的產品的prod_name列和prod_price列select prod_name,prod_price from products where vend_id not in (1002,1003;/檢索除供應商1002,1003之外的所有供應商提供的鏟平的prod_name列和prod_price列mysql支持使用not對in、between和exists字句取反。用通配符進行過濾%:可以匹配任何字符出現(xiàn)的任何次數(shù)(0次,1次,多次,但是不能

7、匹配null。_:可以匹配任何字符僅一次。盡量不要將通配符放在搜索模式最開始處,這樣搜索起來是最慢的。通配符搜索的處理一般要比其它搜索花的時間更久,所以不能過度使用通配符。select prod_id,prod_name from products where prod_name likejet%;匯總數(shù)據(jù) select avg(prod_priceas avg_price from products;/返回avg_price,它包含表中所有商品的平均價格select avg(distinct prod_price as avg_price from products;/計算平均值只考慮不同價

8、格avg忽略列值為null的行,avg(括號中只能有一個參數(shù)。select count(* as num_cust from customers;/對表中的行進行計數(shù),包含值為null的行select count(cust_email as num_cust from customers;/對表中cust_email有值得行進行計數(shù),忽略值為null的行max(,min(忽略值為null的行select sum(quantity as items from orderitems;/計算表中quantity列的總和select sum(item_price*quantity as items f

9、rom orderitems./sum(中(中的值可以是表達式select count(* as num_items,min(prod_price as min_price,max(prod_price as max_price from products;/組合聚合函數(shù)分組數(shù)據(jù)group by:創(chuàng)建分組,亂序輸出,如果要按順序輸出,需要用order byhaving:過濾分組group by 出現(xiàn)在where之后,order by 之前where的過濾是基于行的,在分組之前過濾;having的過濾基于分組,在分組之后過濾。select vend_id,count(* as num_prods

10、 from products group by vend_id;/對按vend_id分組的每個組進行匯總select vend_id,count(* as num_prods from products group by vend_id having count(*>2; /過濾出count(*>2的分組select vend_id,count(* as num_prods from products where prod_price>10 group by vend_id having count(*>2;/ where過濾出prod_price>10的行,然后

11、按vend_id分組數(shù)據(jù),再過濾出count(*>2的分組select vend_id,count(* as num_prods from products group by vend_id order by num_prods;/用order by對結果按num_prods排序輸出select子句 子查詢子查詢是嵌套在其它查詢中的查詢.內層查詢的結果用于外面子查詢的where子句。先建立和測試最內層的查詢,再用硬編碼數(shù)據(jù)建立和測試外層查詢,在確認它正確以后再嵌入子查詢,然后再進行測試。select cust_id from orders where order_num in(selec

12、t 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;/將計算字段作為子查詢聯(lián)結表-低級聯(lián)結關系表:一個表中的外鍵是另一個表中的主鍵笛卡爾積:在沒有聯(lián)結條件的表關系返回的結果為笛卡爾積。檢索出的結果是第一個表的行數(shù)乘以第二個表的行數(shù)。聯(lián)結的表越多,性能下降的越厲害。以下聯(lián)結為等值聯(lián)結或內聯(lián)結:使

13、用where子句聯(lián)結:select vend_name,prod_name,prod_price from vendors,products wherevendors.vend_id=products.vend_id;/從表vendors和products中查詢vend_name,prod_name, prod_price,匹配條件為兩個表的vend_id相等不使用where字句,會反悔兩個表關系的笛卡爾積select vend_name,prod_name,prod_price from vendors,products;/用on子句聯(lián)結select vend_name,prod_name,

14、prod_price from vendors inner join products onvendors.vend_id=products.vend_id;聯(lián)結表-高級聯(lián)結給表起別名,表別名只在執(zhí)行查詢中使用,不返回到客戶機。有時候處理聯(lián)結比處理子查詢快很多自然聯(lián)結:只能選擇那些唯一的列,這一般是通過對表使用通配符(select *,對所有其它表的列使用明確的子集來完成的select prod_id,prod_name from products where vend_id=(select vend_id from products whereprod_id=DTNTR;/先找到生產ID為D

15、TNTR的供應商,再找出這個供應商生產的其他物品select d_id,d_name from products as p1,products as p2 where p1.vend_id=p2.vend_id and p2.vend_id=DTNTR;/給表取別名使用聯(lián)表查詢外部聯(lián)結 left/rigthe outer join on.left從from子句的左邊表中選擇所有行,rigth從右邊表中選擇所有行。select customers.cust_id,orders.orders_num from customers left outer join orders

16、 oncustomers.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;組合查詢從多個表中檢索數(shù)據(jù)時用union過濾,從單個表中檢索數(shù)據(jù)時用where過濾。union: union中的每個查詢必須包含相同的列、表達式或聚集函數(shù),從查詢結果中自動去除重

17、復的行。如果需要將查詢結果全部返回(包括重復行,用union all。order by必須用在最后一條select語句之后,只能出現(xiàn)一次。select vend_id,prod_id,prod_price from products where prod_price<=5 union selectvend_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 al

18、l selectvend_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 all selectvend_id,prod_id,prod_price from products where vend_id in (1001,1002 order by vend_id,prod_price;插入數(shù)據(jù) 可以在 insert 中省略某些列，省略的列必須滿足以下條件：允許

19、NULL 值或給出默認值，否則將產生錯誤信 息。 如果數(shù)據(jù)檢索是最重要的，可以在 insert 和 into 之間添加關鍵字 low_priority 開降低 insert 的優(yōu)先級。 插入完整的行：insert into customers values(, , ,; 對每列必須插入一個值，如果沒有值，可以使用 NULL。這種方法過度依賴表中列的定義次序。為確保安全應 該使用以下方法： insert into customers(列名， ， values(，; 插入多條數(shù)據(jù)：insert into customers(列名 values(， ，,（， ，）; 插入檢索出的數(shù)據(jù)：insert into c