常見注釋

• -- 很少支持

• 行內注釋

• /**/段落注釋

基礎語法

SELECT

檢索資料

排序檢索資料

過濾資料

通配符搜索

創建計算欄位

select prod_id ,quantity,item_price,quantity*item_price as expanded_price
from orderitems
where order_num = 20008;
#如上創建了一個expanded_price欄位（quantity*item_price的結果的別名），其僅在此時有效而不會存放到表中，

使用函式

select vend_name, upper(vend_name) as vend_name_upcase
from vendors
#將vend_name列下的所有行以大寫形式回傳
select avg(prod_price) as avg_peice from products where vend_id ='1001'
#回傳平均值
select count(*) as num_cust from customers
#回傳長度（數目），也可對串列中特定值進行計數

分組

select  vend_id,count(*) as num_prods from  products #對vend_id每行進行計數
group by vend_id;#按照vend_id排序并分組

select cust_id,count(*) as orders
from orders 
group by cust_id 
having count(*)>=2#過濾分組中>=2的，having支持where的所有操作

select order_num,count(*) as items
from orderitems group by order_num
having count(*) >=3
order by items,order_num desc#對分組依照選定的列進行排序

子句查詢

select cust_name,cust_contact from customers where cust_id =(select cust_id 
from orders
where order_num = (select order_num from orderitems where prod_id = 'jp2000'));
#由內而外，哈哈
等效于：
select order_num 
from orderitems
where prod_id = 'jp2000'；
select cust_id 
from orders
where order_num =20006
select cust_name,cust_contact from customers where cust_id =10003

聯結（回傳不在同一個表中的行）

/*等值語法*/
select vend_name, prod_name,prod_price
from vendors,products
where vendors.vend_id=products.vend_id;#此處過濾聯結條件，
#如沒有聯結條件過濾，將檢索出“笛卡爾積”：表1行數*表2行數
/*規范語法*/
select vend_name, prod_name,prod_price
from vendors inner join products
on vendors.vend_id=products.vend_id

自聯結（比子查詢更快）

/*子查詢*/
select cust_id, cust_name, cust_contact
from customers
where cust_name = (select cust_name from customers where cust_contact ='jim jones');
/*自聯結*/
select c1.cust_id, c1.cust_name,c1.cust_contact
from customers as c1,customers as c2#不以別名進行會引發錯誤
where c1.cust_name=c2.cust_name and c2.cust_contact='jim jones'#聯結cust_name與c2.cust_name ，并過濾cust_contact='jim jones'的行
/*（c1的cust_name同c2相同，找到了c2的cust_contact='jim jones'也就相當于找到了c1cust_contact='jim jones'，知道了cust_contact='jim jones'就可知道cust_id）*/

組合查詢

select cust_name,cust_contact,cust_email,cust_state
from customers
where cust_state in('il','in','mi')
union  #組合上下select多個select之間需要多個union分隔，union默認排除重復，union all則不排除
select cust_name,cust_contact,cust_email,cust_state#union中每個查詢必須包含相同的列、運算式、或聚集函式
from customers
where cust_name ='wascals'
order by cust_name;#不能分別對每條union指定不同的排序

INSERT

依賴于次序的插入

/*在得知列的次序后才可使用此方式添加，若發生了列的次序變動此添加方式將不安全*/
insert into customers
values('1000000006','toy land','123 any street','new york','ny', '11111','usa',null, null);
#依賴于次序的插入，必須為每一列提供一個值，如某列無添加則應寫上null

提供列名的插入

insert into customers(cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip)
#必須為提供了列名的列給出一個值
values(null, null,'1000000006','toy land','123 any street','new york','ny', '11111');

從另一個表插入

insert into 表名（列名）
select 列名
from 表名
where 過濾

復制一個表

/*sql*/
select * 
into custcopy 
from customers;
/* mysql*/
create table custcopy as
select * 
from customers;

UPDATE

更新單個列

update customers
set cust_email = 'kim@@thetoystore.com'
where cust_id = '100000000005'#如不指定，將更新customers表cust_email列下的所有行

更新多個列

update customers
set cust_email = 'kim@@thetoystore.com',cust_contact='sam roberts'
where cust_id = '100000000006'

DELLETE

delete from customers
where cust_id = '1000000006'#洗掉此行，不過濾則洗掉所有行
#update洗掉列
#truncate洗掉表

添加洗掉列&&表

添加表

/*添加表時為防止覆寫，應洗掉表后再進行添加*/
create table orderitems
(order_num integer not null,
order_item integer not null,
prod_id char(10) not null,
quantity integer not null default 1,#設定quantity列下的行默認值為1
item_price decimal(8,2) not null);#not null即不允許填入null，默認可填入null，只有為 not null的列方可為主鍵及唯一標識

/*add列*/
alter table vendors
add vend_phone char (20);
/*del列*/
alter table vendors
drop column vend_phone;#此操作不可逆
/*洗掉表*/
drop table custcopy;#此操作不可逆

視圖

create view#創建視圖
drop view 視圖名#洗掉視圖
/*以視圖簡化聯結，創建視圖*/
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;
/*一個視圖過濾查詢例子*/
create view customeremaillist as
select cust_id, cust_name,cust_email
from customers
where cust_email is not null;#回傳查詢中所有cust_email不為空的，并將其添加到視圖中
/*視圖計算欄位例子*/
create view orderitemsexpanded as 
select order_num,prod_id,quantity,item_price,quantity*item_price,quantity*item_price as
expanded_price
from orderitems

事務管理

/*撤銷整體*/
start transaction ;
-- 標識事務處理塊,塊中內容未執行完則整體撤銷
/*撤銷部分操作*/
savepoint delete1;#標識
rollback to delete1;回傳標識delete1

標籤：MySQL

上一篇：MySQL學習筆記（5）：運算子

下一篇：MySQL按指定字符合并及拆分