MySQL必知必會V1cn學習小結

MySQL必知必會V1cn學習小結
————
部落格， http://blog.csdn.net/shunqiziranhao007/article/details/8730477
日期，2013年3月28日
————
官網，
http://forta.com/books/0672327120/
————
 
這本書，很適合mysql入門。花不了多久時間就能看完。
 
————
第1章 瞭解sql
————
1.1資料庫基礎
——資料庫，表，列，資料類型，行，主鍵，外鍵
1.2什麼是sql
——sql，結構化查詢語言 (SQL)。sql不區分大小寫。
1.3動手實踐
1.4小結
 
————
第2章mysql簡介
————
2.1什麼是mysql
2.2mysql工具
——mysql命令列公用程式，mysql adminis-trator，mysql query browser。
2.3小結
 
————
第3章使用mysql
————
3.1串連
3.2選擇資料庫
create database crashcourse;
use crashcourse;
執行sql指令檔，注意路徑要一致。
\. create.sql
\. populate.sql
3.3瞭解資料庫和表
show databases;
show tables;
show columns from customers;
show status;
show grants;
show errors;
show warnings;
help
help show
help create
3.4小結
 
————
第4章檢索資料
————
4.1select語句
4.2檢索單個列
select prod_name from products;
4.3檢索多個列
select prod_id, prod_name, prod_price from products;
4.4檢索所有列
select * from products;
4.5檢索不同的行
select vend_id from products;
select distinct vend_id from products;
distinct應用於所有列。
select distinct vend_id, prod_price from products;
4.6限制結果
select prod_name from products;
select prod_name from products limit 5;
select prod_name from products limit 0, 1;
select prod_name from products limit 1, 1;
select prod_name from products limit 0, 2;
4.7使用完整表名
select prod_name from products;
select products.prod_name from products;
select products.prod_name from crashcourse.products;
4.8小結
 
————
第5章排序檢索資料
————
5.1排序資料
select prod_name from products;
排序預設非降序
select prod_name from products order by prod_name;
5.2按多個列排序
select prod_id, prod_price, prod_name from products order by prod_price, prod_name;
5.3指定排序方向
select prod_id, prod_price, prod_name from products order by prod_price;
select prod_id, prod_price, prod_name from products order by prod_price desc;
select prod_id, prod_price, prod_name from products order by prod_price desc, prod_name;
select prod_price from products order by prod_price desc limit 1;
5.4小結
 
————
第6章過濾資料
————
6.1使用where子句
select prod_name,prod_price from products where prod_price = 2.50;
6.2where子句操作符
6.2.1檢查單個值
select prod_name,prod_price from products where prod_name = 'fuses';
select prod_name,prod_price from products where prod_price < 10;
select prod_name,prod_price from products where prod_price <= 10;
6.2.2不匹配檢查
select vend_id, prod_name from products where vend_id <> 1003;
select vend_id, prod_name from products where vend_id != 1003;
6.2.3範圍值檢查
select prod_name,prod_price from products where prod_price between 5 and 10 ;
6.2.4空值檢查
select prod_name,prod_price from products where prod_price is null;
select cust_id from customers where cust_email is null;
6.3小結
 
————
第7章資料過濾
————
7.1組合where子句
7.1.1and操作符
select prod_id, prod_name, prod_price from products where vend_id = 1003 and prod_price <= 10;
7.1.2or操作符
select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003;
7.1.3計算次序
select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003 and prod_price >= 10;
select prod_name, prod_price from products where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;
7.2in操作符
select prod_name, prod_price from products where vend_id in (1002, 1003) order by prod_name;
select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003 order by prod_name;
7.3not操作符
select prod_name, prod_price from products where vend_id not in (1002, 1003) order by prod_name;
7.4小結
 
————
第8章用萬用字元進行過濾
————
8.1like操作符
8.1.1百分比符號（%）萬用字元——任意個
select prod_id, prod_name from products where prod_name like 'jet%';
select prod_id, prod_name from products where prod_name like '%anvil%';
select prod_name from products where prod_name like 's%e';
8.1.2底線（_）萬用字元——單個
select prod_id, prod_name from products where prod_name like '_ ton
select prod_id, prod_name from products where prod_name like '% ton anvil'; anvil';
8.2使用萬用字元的技巧
8.3小結
 
————
第9章用Regex進行搜尋
————
9.1Regex介紹
9.2使用mysqlRegex
help regexp
9.2.1基底字元匹配
select prod_name from products where prod_name regexp '1000' order by prod_name;
select prod_name from products where prod_name regexp '.000' order by prod_name;
select prod_name from products where prod_name regexp binary 'jet' order by prod_name;
9.2.2進行or匹配
select prod_name from products where prod_name regexp '1000|2000' order by prod_name;
9.2.3匹配幾個字元之一
select prod_name from products where prod_name regexp '[123] Ton' order by prod_name;
select prod_name from products where prod_name regexp '1|2|3 Ton' order by prod_name;
9.2.4匹配範圍
select prod_name from products where prod_name regexp '[1-5] Ton' order by prod_name;
9.2.5匹配特殊字元
select vend_name from vendors where vend_name regexp '.' order by vend_name;
select vend_name from vendors where vend_name regexp '\\.' order by vend_name;
9.2.6匹配字元類
9.2.7匹配多個執行個體
select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)' order by prod_name;
select prod_name from products where prod_name regexp '[[:digit:]]{4}' order by prod_name;
select prod_name from products where prod_name regexp '[0-9][0-9][0-9][0-9]' order by prod_name;
9.2.8定位器
select prod_name from products where prod_name regexp '^[0-9\\.]' order by prod_name;
select 'hello' regexp '[0-9]';
9.3小結
 
————
第10章建立計算欄位
————
10.1計算欄位
10.2拼接欄位
select concat(vend_name, '(', vend_country, ')') from vendors order by vend_name;
select concat(rtrim(vend_name), '(', rtrim(vend_country), ')') from vendors order by vend_name;
select concat(rtrim(vend_name), '(', rtrim(vend_country), ')') as vend_title from vendors order by vend_name;
10.3執行算術計算
select prod_id, quantity, item_price from orderitems where order_num = 20005;
select prod_id, quantity, item_price, quantity*item_price as expanded_price from orderitems where order_num = 20005;
select 2*3;
select trim(' a b c ');
select now();
10.4小結
 
————
第11章使用資料處理函數
————
11.1函數
11.2使用函數
11.2.1文本處理函數——left，length，locate，lower，ltrim，right，rtrim，soundex，substring，upper。
help upper;
select vend_name, upper(vend_name) as vend_name_upcase from vendors order by vend_name;
select cust_name, cust_contact from customers where soundex(cust_contact) = soundex('Y lie');
11.2.2日期和時間處理函數
select cust_id, order_num from orders where order_date = '2005-09-01';
select cust_id, order_num from orders where date(order_date) = '2005-09-01';
select cust_id, order_num from orders where date(order_date) between '2005-09-01' and '2005-09-30';
select cust_id, order_num from orders where year(order_date) = 2005 and month(order_date) = 9;
11.2.3數值處理函數
11.3小結
 
————
第12章摘要資料
————
12.1聚集合函式
12.1.1avg（）函數
select avg(prod_price) as avg_price from products;
select avg(prod_price) as avg_price from products where vend_id = 1003;
12.1.2count（）函數
select count(*) as num_cust from customers;
select count(cust_email) as num_cust from customers;
12.1.3max（）函數
select max(prod_price) as max_price from products;
12.1.4min（）函數
select min(prod_price) as min_price from products;
12.1.5sum（）函數
select sum(quantity) as items_ordered from orderitems where order_num = 20005;
select sum(item_price*quantity) as total_price from orderitems where order_num = 20005;
12.2聚集不同值
select avg(distinct prod_price) as avg_price from products where vend_id = 1003;
12.3組合聚集合函式
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;
12.4小結
 
————
第13章分組資料
————
13.1資料分組
select count(*) as num_prods from products where vend_id = 1003;
13.2建立分組
select vend_id, count(*) as num_prods from products group by vend_id;
select vend_id, count(*) as num_prods from products group by vend_id with rollup;
13.3過濾分組
select cust_id, count(*) as orders from orders group by cust_id having count(*) >= 2;
select vend_id, count(*) as num_prods from products where prod_price >= 10 group by vend_id having count(*) >= 2;
select vend_id, count(*) as num_prods from products group by vend_id having count(*) >= 2;
13.4分組和排序
select order_num, sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price) >= 50;
select order_num, sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price) >= 50 order by ordertotal;
13.5select子句順序
13.6小結
 
————
第14章使用子查詢
————
14.1子查詢
14.2利用子查詢進行過濾
select order_num from orderitems where prod_id = 'TNT2';
select cust_id from orders where order_num in (20005, 20007);
select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'TNT2');
select cust_name, cust_contact from customers where cust_id in (select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'TNT2'));
14.3作為計算欄位使用子查詢
select count(*) as orders from orders where cust_id = 10001;
select cust_name, cust_state, (select count(*) from orders where orders.cust_id = customers.cust_id) as orders from customers order by cust_name;
select cust_name, cust_state, (select count(*) from orders where cust_id = cust_id) as orders from customers order by cust_name;
14.4小結
 
————
第15章連接表
————
15.1連接
15.1.1關係表
15.1.2為什麼要使用連接
15.2建立連接
select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id = products.vend_id order by vend_name, prod_name;
15.2.1where子句的重要性
select vend_name, prod_name, prod_price from vendors, products order by vend_name, prod_name;
15.2.2內部連接
select vend_name, prod_name, prod_price from vendors inner join products on vendors.vend_id = products.vend_id;
15.2.3連接多個表
select prod_name, vend_name, prod_price, quantity from orderitems, products, vendors where products.vend_id = vendors.vend_id and orderitems.prod_id = products.prod_id and order_num = 20005;
15.3小結
 
————
第16章建立進階連接
————
16.1使用表別名
16.2使用不同類型的連接
16.2.1自連接
16.2.2自然連接
16.2.3外部連接
16.3使用帶聚集合函式的連接
16.4使用連接和連接條件
16.5小結
 
————
第17章組合查詢
————
17.1組合查詢
17.2建立組合查詢
17.2.1使用union
17.2.2union規則
17.2.3包含或取消重複的行
17.2.4對組合查詢結果排序
17.3小結
 
————
第18章全文本搜尋
————
18.1理解全文本搜尋
18.2使用全文本搜尋
18.2.1啟用全文本搜尋支援
18.2.2進行全文本搜尋
18.2.3使用查詢擴充
18.2.4布爾文本搜尋
18.2.5全文本搜尋的使用說明
18.3小結
 
————
第19章插入資料
————
19.1資料插入
19.2插入完整的行
insert into customers values(NULL, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);
insert into customers(cust_name, cust_contact, cust_email, cust_address, cust_city, cust_state, cust_zip, cust_country) values('Pep E. LaPew', NULL, NULL, '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA');
19.3插入多個行
19.4插入檢索出的資料
19.5小結
 
————
第20章更新和刪除資料
————
20.1更新資料
update customers set cust_email = 'elmer@fudd.com' where cust_id = 10005;
update customers set cust_name = 'The Fudds', cust_email = 'elmer@fudd.com' where cust_id = 10005;
update customers set cust_email = NULL where cust_id = 10005;
20.2刪除資料
delete from customers where cust_id = 10006;
20.3更新和刪除的指導原則
20.4小結
 
————
第21章建立和操縱表
————
21.1建立表
21.1.1表建立基礎
21.1.2使用null值
21.1.3主鍵再介紹
21.1.4使用auto_increment
21.1.5指定預設值
21.1.6引擎類型
21.2更新表
alter table vendors add vend_phone char(20);
alter table vendors drop column vend_phone;
21.3刪除表
drop table customers2;
21.4重新命名表
rename table customer2 to customers;
21.5小結
————
看create.sql
 
————
第22章使用視圖
————
22.1視圖
22.1.1為什麼使用視圖
22.1.2視圖的規則和限制
22.2使用視圖
create view xx, show create view xx, drop view xx;
22.2.1利用視圖簡化複雜的連接
22.2.2用視圖重新格式化檢索出的資料
22.2.3用視圖過濾不想要的資料
22.2.4使用視圖與計算欄位
22.2.5更新視圖
22.3小結
 
————
第23章使用預存程序
————
23.1預存程序
23.2為什麼要使用預存程序
23.3使用預存程序
23.3.1執行預存程序
call productpricing(@pricelow, @pricehigh, @priceaverage);
23.3.2建立預存程序
create procedure productpricing()
begin
    select avg(prod_price) as priceaverage from products;
end
23.3.3刪除預存程序
drop procedure productpricing;
23.3.4使用參數
create procedure productpricing(out pl decimal(8, 2), out ph decimal(8, 2), out pa decimal(8, 2) )
begin
    select min(prod_price) into pl from products;
    select max(prod_price) into ph from products;
    select avg(prod_price) into pa from products;
end;
call productpricing(@pricelow, @pricehigh, @priceaverage);
select @pricehigh, @pricelow, @priceaverage;
create procedure ordertotal(in onumer int, out ototal decimal(8,2))
begin
    select sum(item_price*quantity) from orderitems where order_num = onumber into ototal;
end
call ordertotal(20005, @total)
23.3.5建立智能預存程序
23.3.6檢查預存程序
show create procedure ordertotal;
23.4小結
 
————
第24章使用遊標
————
24.1遊標
24.2使用遊標
24.2.1建立遊標
create procedure processorders()
begin
    declare ordernumbers cursor for select order_num from orders;
end
24.2.2開啟和關閉遊標
open ordernumbers;
close ordernumbers;
24.2.3使用遊標資料
24.3小結
 
————
第25章使用觸發器
————
25.1觸發器
25.2建立觸發器
create trigger newproduct after insert on products fro each row select 'Product added';
25.3刪除觸發器
drop trigger newproduct;
25.4使用觸發器
create trigger neworder after insert on orders for each row select new.order_num;
25.4.1insert觸發器
25.4.2delete觸發器
25.4.3update觸發器
25.4.4關於觸發器的進一步介紹
25.5小結
 
————
第26章管理交易處理
————
26.1交易處理
26.2控制交易處理
start transaction;
26.2.1使用rollback
select * from ordertotals;
start transaction;
delete from ordertotals;
select * from ordertotals;
rollback;
select * from ordertotals;
26.2.2使用commit
start transaction;
delete from orderitems where order_num = 20010;
delete from orders where order_num = 20010;
commit;
26.2.3使用保留點
savepoint delete1;
rollback to delete1;
26.2.4更改預設的提交行為
set autocommit=0;
26.3小結
 
————
第27章全球化和本地化
————
27.1字元集和校對順序
27.2使用字元集和校對順序
show character set;
27.3小結
 
————
第28章安全管理
————
28.1存取控制
28.2系統管理使用者
use mysql;
select user from user;
28.2.1建立使用者帳號
create user ben identified by 'p@$$wOrd';
rename user ben to bforta;
28.2.2刪除使用者帳號
drop user bforta;
28.2.3設定存取權限
show grants for bforta;
grant select on crashcourse.* to bforta;
show grants for bforta;
28.2.4更改口令
set password for bforta = password('n3w p@$$wOrd');
28.3小結
 
————
第29章資料庫維護
————
29.1備份資料
29.2進行資料庫維護
29.3診斷啟動問題
29.4查看記錄檔
29.5小結
 
————
第30章改善效能
————
30.1改善效能
30.2小結
 
附錄a mysql入門
附錄b 範例表
附錄c mysql語句的文法
附錄d mysql資料類型
附錄e mysql保留字
索引