《MySQL必知必會(huì)》讀書筆記
第1-3章MySql簡(jiǎn)單命令行操作
mysql --help 幫助信息
mysql -u userName -p 用戶登錄
show databases; 列出當(dāng)前所有數(shù)據(jù)庫
use databaseName 使用當(dāng)前數(shù)據(jù)庫
show tables; 列出當(dāng)前數(shù)據(jù)庫所有的表
show columns from tableName; 查看表結(jié)構(gòu)
第2章customers表
第4章數(shù)據(jù)檢索
select 語句的簡(jiǎn)單使用，先創(chuàng)建模板數(shù)據(jù)庫 crashcourse
-- 快捷鍵：運(yùn)行選中的sql, ctrl + r
show COLUMNS FROM products;
-- 檢索單列數(shù)據(jù)
select prod_name from products;
-- 檢索多列數(shù)據(jù)，列名與列名之間使用逗號(hào)進(jìn)行分隔
select prod_id, prod_name,prod_price from products;
-- 檢索所有的列使用通配符*號(hào)
select * from products;
-- 對(duì)于檢索出來的列的值進(jìn)行去重（注意這里只檢索了1列），在select后面使用select 后面添加上 distinct 關(guān)鍵字;
-- 注意distinct作用域該關(guān)鍵字之后的所有列，理解為所有列的組合后去重
select distinct vend_id from products;
-- 使用limit start,size 返回指定開始行和行數(shù)，注意start下標(biāo)是從0開始
select * from products;
select * from products limit 0,5;
-- 使用完全限定名：表名.字段名， 數(shù)據(jù)庫名.表名 來指定操作的列/表 ，使用限定名在navicat中會(huì)有列名提示
select products.prod_id from crashcourse.products;
第5章排序檢索數(shù)據(jù)
-- 使用order by 關(guān)鍵字對(duì)檢索結(jié)果按照指定列的值進(jìn)行排序 默認(rèn)升序：英文字符串按照a-z的順序，數(shù)組0-n;
-- 優(yōu)先級(jí) . > 數(shù)字 > 英文
-- 默認(rèn)升序-asc 降序關(guān)鍵字-desc
select prod_name from products order by prod_name;
-- 按多個(gè)列排序，order by 關(guān)鍵字后面按照排序的優(yōu)先級(jí)從高到低，使用逗號(hào)分隔添加排序的參照列
select prod_id, prod_price,prod_name from products order by prod_price, prod_name;
-- 使用order by + limit 的組合可以找到指定列中的最大值或最小值; limit 1 表示僅返回一行
select prod_price from products order by prod_price desc limit 1;
第6章過濾數(shù)據(jù)
-- 可以使用 where 關(guān)鍵字對(duì)查詢結(jié)果進(jìn)行過濾， where 條件表達(dá)式 order by 排序參照字段
select prod_name, prod_price
from products
where prod_price =2.50;
-- where 操作符 <> 和 != 的區(qū)別是什么？ between 指定的兩個(gè)值之間
-- mysql 在執(zhí)行匹配的時(shí)候不區(qū)分大小寫
select prod_name, prod_price
from products
where prod_name = 'fuses'
-- 使用 <> 或者 != 進(jìn)行不匹配檢查
select vend_id, prod_name
from products
where vend_id <> 1003;
-- 使用between進(jìn)行范圍值檢查, 注意 between是包含邊界值的
select prod_id, prod_name, prod_price
from products
where prod_price between 5 and 10;
-- 使用is null 進(jìn)行控制檢測(cè)，注意例如 0 這樣的值不是空值，空值指的是 null
select cust_id, cust_name
from customers
where cust_email is null;
-- 那mysql的非空檢測(cè)呢？判斷NULL用is null 或者 is not null ；判斷空字符串，要用 =”或者 <>”
select cust_id, cust_name
from customers
where cust_email is not null;
第7章數(shù)據(jù)過濾（where查詢的高級(jí)使用）
-- 使用and(都滿足) or(只要滿足一個(gè)) 連接多個(gè)條件表達(dá)式
select vend_id, prod_id, prod_name
from products
where vend_id = 1003 and prod_price <=10;
-- 銷售商id = 1002 或者 1003
select vend_id, prod_id, prod_name
from products
where vend_id = 1003 or vend_id = 1002;
-- 邏輯運(yùn)算符的優(yōu)先級(jí)，一般使用()去提升優(yōu)先級(jí)
-- 列出價(jià)格10美元（含）以上且由1002或1003制造的產(chǎn)品
select vend_id, prod_name, prod_price
from products
where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10
-- in 操作符表示能夠和提供的多個(gè)值中匹配上一個(gè)，就將整條記錄查詢出來，可以和or關(guān)鍵字進(jìn)行替換，in比較清晰直觀
select vend_id, prod_id, prod_name
from products
where vend_id in(1002,1003)
order by prod_name;
-- not 運(yùn)算符用來否定之后的條件表達(dá)式
select vend_id, prod_id, prod_name
from products
where vend_id not in(1002,1003)
order by prod_name;
第8章用通配符進(jìn)行過濾
-- 使用like運(yùn)算符進(jìn)行模糊搜索 %-表示0-n個(gè)任意字符 _表示1個(gè)任意字符 %keyWord% 表示包含搜索內(nèi)容
-- 產(chǎn)品名稱"jet"打頭
select prod_id, prod_name
from products
where prod_name like 'jet%';
-- like 匹配是可以區(qū)分大小寫？那怎么區(qū)分大小寫呢？
-- 方式一在創(chuàng)建表的時(shí)候指定字段區(qū)分大小寫；方式二在查詢的時(shí)候指定字符集 collation
select prod_id, prod_name
from products
where prod_name collate latin1_bin like 'Jet%';
-- 查詢產(chǎn)品名包含 anvil
select prod_id, prod_name
from products
where prod_name like '%anvil%';
-- 查詢產(chǎn)品名指定開頭和結(jié)尾
select prod_id, prod_name
from products
where prod_name like 's%e';
-- _ 通配符
select prod_id, prod_name
from products
where prod_name like '_ ton anvil';
第9章使用正則表達(dá)式進(jìn)行搜索（未完成）
第10章創(chuàng)建計(jì)算字段（未完成）
第11章使用數(shù)據(jù)處理函數(shù)
-- 文本處理函數(shù) RTrim 去除右側(cè)的空格 Upper轉(zhuǎn)大寫
select vend_name, RTrim(vend_name) as name_rtrim, Upper(vend_name) as name_upper
from vendors
order by vend_name;
-- 常用的文本處理函數(shù)：left-返回串左邊，length-返回長(zhǎng)度，locate-找出子串，lower-轉(zhuǎn)小寫，ltrim-去除左邊空格
-- right-返回右邊字符串，rtrim-去掉右邊空格，substring-截取，upper轉(zhuǎn)大寫，soundex-返回soundex值
-- 日期時(shí)間處理函數(shù)
-- addDate-增加日期（天、周），addTime-增加時(shí)間（時(shí)、分），curDate-當(dāng)前日期，curTime當(dāng)前時(shí)間，date-返回日期時(shí)間的日期部分
-- dateDiff-返回日期差，data_format-返回格式化的日期或時(shí)間串，day-返回天數(shù)（月），dayOfWeek-返回天數(shù)（周），hour-小時(shí)
-- minute-分鐘，month-月份，now-日期時(shí)間，second-秒，time-返回時(shí)間，year-返回年份
-- 日期格式必須為 yyyy-mm-dd
-- 查詢指定日期的訂單信息
select order_num, order_date
from orders
where order_date = '2005-09-01'
-- 注意當(dāng)字段的數(shù)據(jù)類型為 datetime 時(shí)，order_date = '2005-09-01' 能夠和數(shù)據(jù) 2005-09-01 00:00:00 匹配上，但是和2005-09-01 05:30:34就匹配不上了
-- 可以只比較日期部分，使用date函數(shù)
select order_num, order_date
from orders
where Date(order_date) = '2005-09-01'
-- 檢索指定月份的訂單
-- 方式一：between 月份第一天和最后一天的日期，但是需要自己去判斷這個(gè)月的天數(shù)，不推薦
select order_num, order_date
from orders
where Date(order_date) between '2005-09-01' and '2005-09-30'
-- 方式二：使用Year，Month函數(shù)（月份從1開始）
select order_num, order_date
from orders
where YEAR(order_date) = 2005 and MONTH(order_date) = 9
-- 數(shù)值處理函數(shù)
-- ads-絕對(duì)值，mod-取余，round-隨機(jī)數(shù)
第12章匯總數(shù)據(jù)
-- 聚合函數(shù)：count-統(tǒng)計(jì)行數(shù)，sum-求和，avg-平均數(shù)，max-最大值，min-最小值
-- avg 產(chǎn)品平均價(jià)格，注意avg會(huì)忽略null值
select avg(prod_price) as avg_price
from products
-- 指定銷售商下所有產(chǎn)品的平均價(jià)格，先進(jìn)行where刪選，在對(duì)篩選結(jié)果進(jìn)行avg操作
select avg(prod_price) as avg_price
from products where vend_id = 1003
-- count(*) 統(tǒng)計(jì)行數(shù)無論列中是否包含null值，count(column) 忽略指定列的null值，統(tǒng)計(jì)行數(shù)
select count(*) as num_cust
from customers;
select count(cust_email) as num_cust
from customers;
-- max-最大值,忽略null
select MAX(prod_price) max_price
from products;
-- min-最小值,忽略null
select MIN(prod_price) min_price
from products;
-- sum 指定訂單下物品的數(shù)量
select SUM(quantity) as items_ordered
from orderitems
where order_num = 20005;
-- sum 兩個(gè)字段進(jìn)行數(shù)學(xué)運(yùn)算之后的值（計(jì)算訂單總金額）
select SUM(quantity*item_price) as totol_price
from orderitems
where order_num = 20005;
-- 聚集不同值，用到distinct關(guān)鍵字 （特定銷售商家下不同價(jià)格的平均價(jià)格）
select avg(distinct prod_price)
from products
where vend_id = 1003
-- 組合聚合函數(shù)，結(jié)果集中可以包含多個(gè)聚集函數(shù)
select count(*) as item_num, min(prod_price), max(prod_price), avg(prod_price)
from products
第13章分組數(shù)據(jù)
分組數(shù)據(jù)：將結(jié)果集按照指定字段，根據(jù)字段相同，換分為更小的組，經(jīng)常使用聚合函數(shù)對(duì)劃分的組進(jìn)行數(shù)據(jù)統(tǒng)計(jì)
-- group by 后面只能夠是分組字段或者表達(dá)式（什么樣的表達(dá)式呢）
-- select 后面只能夠接分組字段或者聚合函數(shù)結(jié)果；
-- 如果分組列中存在null值，那么null值所在行會(huì)被劃分到一組
-- 使用 group by 關(guān)鍵字查詢不同銷售商有多少產(chǎn)品
select vend_id, count(*) as vend_num
from products
group by vend_id
-- 對(duì)分組之后的數(shù)據(jù)再次進(jìn)行過濾，使用having關(guān)鍵字，where是對(duì)分組前的數(shù)據(jù)進(jìn)行分組
select vend_id, count(*) as vend_num
from products
group by vend_id
having count(*) >= 2
-- 按銷售商分組，數(shù)量2個(gè)（含）以上，價(jià)格10（含）以上
select vend_id, count(*) as vend_num
from products
where prod_price >=10
group by vend_id
having count(*) >= 2
-- 在group by 子句后面添加上order by 表示對(duì)分組之后的數(shù)據(jù)進(jìn)行排序，having子句中不能夠使用別名，要使用聚合函數(shù)或者表達(dá)式
-- 檢索所有訂單總金額，升序排序，總金額大于50
select order_num, sum(item_price * quantity) as total_price
from orderitems
group by order_num
having sum(item_price * quantity) > 50
order by total_price
-- select 查詢語句順序
-- select distinct from where group by having order by limit
第14章使用子查詢
子查詢：將查詢出來的結(jié)果作為條件表達(dá)式的一部分，或者將結(jié)果集當(dāng)做一張新的表；也可以理解為將多條查詢語句轉(zhuǎn)換為一條查詢語句。
-- 利用子查詢進(jìn)行過濾
-- 查詢購買過TNT2的所有客戶
select order_num from orderitems where prod_id = 'TNT2'
select cust_id from orders where order_num = 20005
select cust_name from customers where cust_id = 10001
show columns from customers
show columns from orders
-- 子查詢
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'
)
)
-- 作為計(jì)算字段使用子查詢
-- 查詢每個(gè)用戶的訂單數(shù)量以及用戶基本信息
select cust_id, cust_name, cust_state from customers
select count(*) as order_sum from orders where cust_id = 10001
-- 子查詢
select cust_name, cust_state, (select count(*)
from orders
where orders.cust_id = customers.cust_id) as order_sum
from customers
-- 上方案例也叫做相關(guān)子查詢 customer.cust_id 是來自于外部表
第15章聯(lián)結(jié)表
聯(lián)結(jié)表：將2張表或多張表聯(lián)結(jié)成一張表進(jìn)行數(shù)據(jù)查詢
聯(lián)結(jié)方式：
- 自然連接 from table1, table2
- 內(nèi)連接 table1 inner join table2 on 聯(lián)結(jié)條件
- 外連接（左外，右外） table1 left join table2 on 聯(lián)結(jié)條件
為什么使用多表？
舉例，一個(gè)廠家生產(chǎn)多個(gè)產(chǎn)品，如果每一條產(chǎn)品信息都需要插入廠家相關(guān)信息，廠家信息會(huì)冗余，也容易插入錯(cuò)誤廠家信息，當(dāng)修改廠家的信息的時(shí)候會(huì)去修改每一條產(chǎn)品信息，浪費(fèi)空間，時(shí)間。所以會(huì)將產(chǎn)品和廠家放到兩張表中，通過廠家id進(jìn)行聯(lián)結(jié)。
-- 創(chuàng)建簡(jiǎn)單連接
select vend_name, prod_name, prod_price
from products, vendors
where products.vend_id = vendors.vend_id
order by vend_name, prod_name
-- 沒有添加聯(lián)結(jié)關(guān)系的表，進(jìn)行聯(lián)結(jié)查詢返回的是笛卡爾積，表1中的1行和表2中的每一行進(jìn)行拼接
-- 使用 inner join 進(jìn)行內(nèi)連接
select vend_name, prod_name, prod_price
from products inner join vendors
on products.vend_id = vendors.vend_id
order by vend_name, prod_name
-- 連接多張表（顯示編號(hào)20005訂單中的物品）
select prod_name, vend_name, prod_price, quantity
from products, vendors, orderitems
where products.vend_id = vendors.vend_id
and orderitems.prod_id = products.prod_id
and orderitems.order_num = 20005
-- 查詢購買過TNT2的所有客戶（多表連接版本）
-- customers, orderitems,
select distinct cust_name
from customers, orders, orderitems
where orderitems.order_num = orders.order_num
and orders.cust_id = customers.cust_id
and orderitems.prod_id = 'TNT2'
第16章創(chuàng)建高級(jí)聯(lián)結(jié)
-- 使用表別名 table as alais
select distinct cust_name
from customers as c, orders as o, orderitems as oi
where c.cust_id = o.cust_id
and o.order_num = oi.order_num
and oi.prod_id = 'TNT2'
-- 使用不同類型的聯(lián)結(jié)
-- 自聯(lián)結(jié) 一張表自己聯(lián)結(jié)自己（找到prod_id為DTNTR的物品的供應(yīng)商，以及這個(gè)供應(yīng)商下的其他商品）
select p1.prod_id
from products p1 inner join products p2
on p1.vend_id = p2.vend_id
where p2.prod_id = 'DTNTR'
-- 自然聯(lián)結(jié) 自然聯(lián)結(jié)排除相同列的多次出現(xiàn)，對(duì)于某一張表使用 select tableName.*, 并且補(bǔ)充其沒有的列
-- 查詢產(chǎn)品id為 'FB'的產(chǎn)品，在那些被哪些顧客購買，購買的訂單，訂單詳情
select * from orderitems
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 o.order_num = oi.order_num
and oi.prod_id = 'FB'
-- 外部聯(lián)結(jié) 聯(lián)結(jié)包含了那些在相關(guān)表中沒有關(guān)聯(lián)行的行
-- 表A中的行和表B中的行進(jìn)行關(guān)聯(lián)，當(dāng)表B中的沒有和表A中的行進(jìn)行關(guān)聯(lián)的行，
-- 但是又需要展示表A中的無法創(chuàng)建關(guān)聯(lián)的行的數(shù)據(jù)時(shí)，可以使用外聯(lián)結(jié)
-- 查詢所有顧客的訂單號(hào)
select c.cust_name, o.order_num
from customers as c
left outer join orders o
on c.cust_id = o.cust_id
-- 使用在聚集函數(shù)的聯(lián)結(jié)
-- 檢索所有顧客的訂單數(shù)量（為什么cust_name能夠直接查出來）
select c.cust_name, c.cust_id, count(o.order_num)
from customers as c
inner join orders o
on c.cust_id = o.cust_id
group by c.cust_id;
第17章組合查詢（未完成）
第18章全文本搜索（未完成）
第19章插入數(shù)據(jù)
-- 插入完整行數(shù)據(jù),自增字段不需要插入數(shù)據(jù)，所以傳遞null值；數(shù)據(jù)需要和字段匹配
insert into customers values(null,'Pep E. LaPew','100 Main Street','Los Angeles','CA','90046','USA',null,null);
select * from customers;
-- 插入數(shù)據(jù)的時(shí)候指定字段，字段名的順序不重要，字段名只需要和值對(duì)應(yīng)就行
insert into customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
value ('Pep E. LaPew','100 Main Street','Los Angeles','CA','90046','USA',null,null);
-- 可以一次插入多行數(shù)據(jù),只要值和字段對(duì)應(yīng)就行
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. Martin','42 Galaxy Way','New York','NY','11213','USA');
-- 將檢索出來的數(shù)據(jù)插入到表中,不需要values字段，直接將查詢語句接在字段后面就可以
insert into customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
select cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email from customer_new;
第20章更新和刪除數(shù)據(jù)
-- 關(guān)鍵字update 更新語句一定要帶上 where 條件，不然會(huì)更新整張表
select * from customers;
update customers
set cust_email = 'elmer@fudd.com'
where cust_id = 10005;
-- 在update語句中可以使用子查詢的結(jié)果更新指定的字段
-- ignore 關(guān)鍵字，當(dāng)更新多行時(shí)，即使某一行更新失敗，剩余行也會(huì)繼續(xù)進(jìn)行更新
-- 刪除某列的值
update customers
set cust_email = null
where cust_id = 10005;
-- 刪除數(shù)據(jù) 關(guān)鍵字 delete，同時(shí)也需要記住帶上where條件
delete from customers
where cust_id = 10009;
-- truncate table table_name 表示刪除整張表的內(nèi)容，原理是刪除整張表后，重新創(chuàng)建一張新的表
-- update delete 使用注意事項(xiàng)
-- 帶上where條件，進(jìn)行update和where操作之前需要使用select語句進(jìn)行查找
第21章創(chuàng)建和操縱表
-- 21.1 使用SQL創(chuàng)建數(shù)據(jù)表
-- create table table_name(column_name datatype,) ENGINE=engine_name 是這樣？
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(50) NULL ,
PRIMARY KEY (cust_id)
) ENGINE=INNODB;
-- 數(shù)據(jù)庫引擎：INNODB 是事務(wù)處理引擎不支持全文本搜索 MEMORY==MyISAM 支持全文本搜索，不支持事務(wù)
-- null, not null 表明字段可為空/不可為空，當(dāng)字段為null時(shí)表示插入數(shù)據(jù)的時(shí)候該字段可以不賦值；not null 表示必須帶值
-- aotu_increment 表示字段自增長(zhǎng)，默認(rèn)種子值為：1，增長(zhǎng)間隔為：1
-- PRIMARY KEY 聲明主鍵字段
-- 聯(lián)合主鍵,由兩個(gè)字段的組合組成主鍵，組合不能夠重復(fù)
create table orderitems
(
order_num INT not NULL ,
order_item INT NOT NULL ,
prod_id CHAR NOT NULL ,
quantity INT NOT NULL ,
item_price DECIMAL(8,2) NOT NULL ,
PRIMARY KEY (order_num,order_item)
) ENGINE= INNODB
-- create table if not EXISTS table_name(); 創(chuàng)建表之前先判斷表是否存在
-- 查詢最后一條插入語句返回的自增長(zhǎng)id
select LAST_INSERT_ID();
-- 21.2 更新表（修改表結(jié)構(gòu)）關(guān)鍵字 alter table
-- 當(dāng)表已經(jīng)存放數(shù)據(jù)了就不建議對(duì)表結(jié)構(gòu)進(jìn)行修改，可能會(huì)觸發(fā)約束限制，表與表之間也會(huì)創(chuàng)建聯(lián)系，字段之間也會(huì)創(chuàng)建關(guān)聯(lián)
-- 21.2.1 刪除表的某一列
ALTER TABLE vendors
DROP COLUMN vend_phone;
-- 21.2.2 ALTER創(chuàng)建外鍵關(guān)系，在兩張可以建立外鍵關(guān)系的表創(chuàng)建完成之后，可以通過alter 語句添加外鍵關(guān)系
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_order
FOREIGN KEY(order_item) REFERENCES orders(order_num);
-- 語法總結(jié)
ALTER TABLE 外鍵表名
ADD CONSTRAINT 外鍵約束名稱(fk_外鍵表名_主鍵表名)
FOREIGN KEY(外鍵表外鍵字段) REFERENCES 主鍵表(主鍵字段)
-- 21.3 刪除表 drop table
-- drop 不僅僅刪除表數(shù)據(jù)還刪除表結(jié)構(gòu)
drop table customers2;
-- 21.4 重命名表
RENAME TABLE customers2 TO customers
第22章使用視圖
視圖：視圖是一張?zhí)摂M的表，定義了要查詢的數(shù)據(jù)，自身并不包含數(shù)據(jù)；可以理解為就是某條查詢語句的結(jié)果集
視圖使用場(chǎng)景：
- 常用的重復(fù)的SQL
- 方便進(jìn)行下一步查詢的重用
- 只使用表中的部分?jǐn)?shù)據(jù)
- 保護(hù)數(shù)據(jù)，可以為視圖添加訪問權(quán)限
視圖使用：把視圖當(dāng)做一張表，直接使用 select 關(guān)鍵字就行
常用命令：
create view -- 創(chuàng)建視圖
show create view view_name; -- 查看視圖創(chuàng)建語句
drop view view_name; -- 刪除視圖
create or replace view -- 直接創(chuàng)建或者替換視圖
-- 22.1 創(chuàng)建視圖 create view view_name AS SELECT子句
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;
-- 使用視圖
SELECT * FROM productcustomers;
-- 22.2 使用視圖格式化檢索出來的數(shù)據(jù)（需要經(jīng)銷商+地區(qū)的拼接字段）
CREATE OR REPLACE VIEW vendorlocations AS
SELECT CONCAT(TRIM(vend_name), ' (', TRIM(vend_country), ') ')
AS vend_title
FROM vendors
ORDER BY vend_name;
-- 22.3 使用視圖過濾不想要的數(shù)據(jù)（過濾沒有電子郵件的用戶）
CREATE view customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;
SELECT * FROM customeremaillist;
-- 22.4 使用視圖與計(jì)算字段（查詢指定訂單中每種物品的總價(jià)格）
CREATE VIEW orderitemsexpended AS
SELECT prod_id,quantity, item_price, item_price*quantity AS expanded_price
FROM orderitems
WHERE order_num = 20005;
SELECT * FROM orderitemsexpended;
-- 22.5 更新視圖
-- 通常視圖是可以更新的，視圖更新實(shí)際上更新的是基表的數(shù)據(jù)，但不是所有的視圖都能夠被更新
-- 在視圖是使用了分組，聯(lián)結(jié)，聚合函數(shù)，DISTINCT 等操作，視圖是不能夠被更新的
-- 2.6 刪除視圖
DROP VIEW IF EXISTS vendorlocations;
第23章使用存儲(chǔ)過程
存儲(chǔ)過程：將多條SQL語句進(jìn)行封裝，加上業(yè)務(wù)邏輯
為什么使用存儲(chǔ)過程：
- 簡(jiǎn)化復(fù)雜操作
- 防止出錯(cuò)，保證數(shù)據(jù)的完整性
- 簡(jiǎn)化變動(dòng)的管理，外部的調(diào)用不用發(fā)生變化，只需要修改內(nèi)部的邏輯
- 提高性能，一次創(chuàng)建多次調(diào)用
-- 23.1 創(chuàng)建存儲(chǔ)過程 CREATE PROCEDURE PROCEDURE_NAME BEGIN SELECT子句 END;
-- 創(chuàng)建一個(gè)放回產(chǎn)品平均價(jià)格的存儲(chǔ)過程
CREATE PROCEDURE productpricing()
BEGIN
SELECT AVG(prod_price) AS priceaverage
FROM products;
END;
-- 23.2 執(zhí)行存儲(chǔ)過程 CALL PROCEDURE_NAME;
CALL productpricing();
-- 23.3 刪除存儲(chǔ)過程 DROP PROCEDURE PROCEDURE_NAME
DROP PROCEDURE productpricing;
-- 23.4 創(chuàng)建帶參數(shù)的存儲(chǔ)過程，存儲(chǔ)過程一般不直接顯示查詢結(jié)果，返回查詢的值
-- 在存儲(chǔ)過程中使用變量臨時(shí)存儲(chǔ)數(shù)據(jù)
-- 創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過程，獲取產(chǎn)品表中最高，最低，平均價(jià)格
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;
-- 23.5 調(diào)用帶參數(shù)的存儲(chǔ)過程，使用@變量名方式
CALL productpricing(
@price_low,
@price_high,
@price_avg
);
SELECT @price_low, @price_high, @price_avg;
-- 23.6 同時(shí)使用in, out 參數(shù)（接受訂單號(hào)，返回該訂單合計(jì)）
CREATE PROCEDURE ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT SUM(item_price * quantity)
FROM orderitems
WHERE order_num = onumber
INTO ototal;
END;
CALL ordertotal(20009, @total);
SELECT @total;
-- 23.7 創(chuàng)建帶邏輯判斷的存儲(chǔ)過程 （有的洲的顧客訂單總金額需要添加營(yíng)業(yè)稅）
-- Name:ordertotal
-- Parameters: onumber = order number
-- taxable = 0 if not taxable, 1 if taxable
-- ototal = order tatal varible
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
)
BEGIN
DECLARE total DECIMAL(8,2);
DECLARE taxrate INT DEFAULT 6;
SELECT SUM(item_price * quantity)
FROM orderitems
WHERE order_num = onumber
INTO total;
IF taxable THEN
SELECT total + (total/100*taxrate) into total;
END IF;
SELECT total into ototal;
END;
CALL ordertotal(20005,1,@ototal);
SELECT @ototal;
-- 23.8 刪除存儲(chǔ)過程
DROP PROCEDURE IF EXISTS ordertotal;
第24章使用游標(biāo)（未完成）
第25章使用觸發(fā)器（未完成）
第26章管理事務(wù)處理
SHOW COLUMNS FROM orders;
SHOW COLUMNS FROM orderitems;
-- START TRANSACTION、ROLLBACK、COMMIT 關(guān)鍵字
-- 26.1 事務(wù)回滾：START TRANSACTION -- ROLLBACK 之間的SQL語句修改都不生效
-- 能夠回滾 INSERT, UPDATE, DELETE語句，但是不能夠回滾 SELECT, DROP,CREATE語句
SELECT * FROM customeremaillist;
START TRANSACTION;
DROP VIEW IF EXISTS customeremaillist;
SELECT * FROM customeremaillist;
ROLLBACK;
SELECT * FROM customeremaillist;
SELECT * FROM orderitems;
START TRANSACTION;
DELETE FROM orderitems;
SELECT * FROM orderitems;
ROLLBACK;
SELECT * FROM orderitems;
-- 26.2 事務(wù)提交
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;
-- 26.3 打開關(guān)閉自動(dòng)提交 0-假，1-真（默認(rèn)為真）
SET autocommit=1; -- 關(guān)閉自動(dòng)提交
-- 26.4 完成新增訂單整個(gè)流程
第27章全球化和本地化（未完成）
第28章安全管理（未完成）
訪問控制的場(chǎng)景:
- 多數(shù)用戶只需要讀寫，少量用戶需要?jiǎng)?chuàng)建刪除
- 部分用戶只需要擁有讀權(quán)限
- 某些用戶需要擁有處理其他賬戶的權(quán)限
- 用戶只需要訪問存儲(chǔ)過程，不需要訪問數(shù)據(jù)
root 用戶為超級(jí)管理員，日常操作不應(yīng)該使用root用戶
user表用來管理當(dāng)前用戶
