日記 2013/6/19 10:19

最近這幾天都在忙著學校的期末考科目，在前幾天的考試中已經將比較重要的科目解決了，而在這周的四、五所要考的科目則較為簡單，只要再稍微複習就能獲得高分，在這幾個理應忙碌的日子裡卻顯得十分空閒，幸好早在幾個禮拜前就對所有考試科目有所準備，真正要忙碌的是接下來暑假開始的幾件事項，首先是舉辦最後一次的ITSA競賽，還有幫助學校主任建置一個環境，能讓九組專題生能夠互相交流及分享的網頁，希望由我自己去撰寫並給老師指導，緊接著七月三號要去長庚大學比賽程式，在這之後就是暑假的空閒時期，我可以自己安排自己的行程，來為將來忙碌的三年級做準備，可以的話希望能多考幾張證照，以及事先準備專題的各種專業技術。

日記 2013/6/16 11:28

經過這幾天的英文寫作日記，不得不承認對於文章的結構和組織幫助有限，僅有的好處只在於英文單字的找尋以及使用，對於英文文法的成長也毫無幫助，看來對於英文方面的增進目前只能著手於閱讀和聽力了，在鋪浪上有一位同學很喜歡寫關於甜不辣的腐女小說，重點不再小說內容而是在於文字描述方式以及鋪陳，閱讀下來讓人有身歷其境的感覺，這興許就是文章寫作的神奇之處，希望以後我也能夠培養成這樣的能力，在事件的細節與描述著手、慢慢鋪陳摹寫狀態，發揮出語言最大的力量就是文章的魅力吧，見微知著則是更高的作文境界了，希望在放假的時候能夠更加培養關於中文與英文的語言能力，最好是在說與寫方面能夠更加進步，對於以後工作以及專題企畫案的製作更有幫助。

Diary 2013/6/15 10:55

It's very rare that someone ask me questions in college, but in the last two times that really angry me. The first thing is one girl want to ask me for programming problem, but she had bad attitude in the words. The question is about a function in the C language, she wanted to know what is the function principle. And I said just to use by the format, don't try to know everything about it. Then she answer "Ok! Ok! Ok! genius senior." immediately, the sentence stimulate my temper. the other thing occurrence in today, also a girl want to know the problem of database manage's final exam's content. The girl's logic is very strange and take wrong answer to ask, not anyone questions is really an problem worth to solve. She only want the result of subject, keep waste my time in odd problem.

Diary 2013/6/14 10:49

It's a leisure day that only multimedia production in one day and don't study after class. After relax for a long time, I start to install eclipse JDK to linux ubuntu. The detail of install all follow by the web and only have some bug in it. And the other thins is about my girlfriend, she is in a very exciting mood just want to chat with me. She is crazy in the recent days, I feel a little puzzle by the situation. What can I do to alter the condition? Should I give her some protest about my feeling, or just let it go. Only she happy is all don't matter what I feel. That's the total detail in today.

Diary 2013/6/13 9:55

My Sony's notebook has double system of Windows 7 and Linux Ubuntu in finally. It take me much time to partition the the 500 GB disk, set the three section use by Win 7 and the others for the Ubuntu, swap disk and data store. In yesterday, I made a mistake in change the 80 GB section to swap that make system overhead. Today I reset the hard disk by win 7's application, and start to install Linux Ubuntu in the new partition. When the time after install, I use the terminal to key some instruction but don't have enough privilege to execute. Therefore I found the sudoers.txt and attempt to overwrite the administrate member by add my name and privilege, but the text property is read-only so I am fault to overwrite final. However, shutdown the system and restart it after I take a nap, the sudo instruction become ordinary situation. After all, I update of Linux Ubuntu and download series of software that I use to in win 7,and I press the shut down button to close system. The amazing things happen is nothing happen at all, I think the function of shut down may damage, Hope it can recover by the next time I open it.

Diary 2013/6/12 9:29

Today my final exam's three major subject is already in entirely preparing. the database manage, operation system and English are all in ready, and object-oriented, worldwide civilization and cultural relic could be open book in the examine reduce the most time in study those subject. The lease two subject information manage and multimedia production are in the recent two days, and I will wait the order of teacher and do my best at it. Today's top point is the SQL highlight that I fount in the blogger's javascript can use the code of sql language in beauty layout. The tip let my database manage's key points could show in the best view. That's the total special thing beside study in this day, and thanks god for the early ending in reading.

資料庫管理上機(3)

檔名store_select01_999.sql
相關教學文件: http://db.tt/rOsoYFqv

使用上一次建立的資料庫..

use bookstore_062
SELECT * FROM dbo.Author_062
SELECT * FROM dbo.Browse_062
SELECT * FROM dbo.Cart_062
SELECT * FROM dbo.Member_062
SELECT * FROM dbo.Order_062
SELECT * FROM dbo.Product_062
SELECT * FROM dbo.Record_062
SELECT * FROM dbo.Transaction_062

--滿足條件
/* 1. 單價等於 500的商品*/
Select * from Product_062  where   unitPrice = 500                                              
/* 2. 會員 su 的會員編號*/
Select mid  from   Member_062  where  name='su'                                               
/* 3. 交易方式(method)透過傳真fax購買的資料*/
Select  *  from  Transaction_062  where   method = 'fax'                                              
/* 4. cardType以 visa 付款的的交易資料*/
Select  *  from   Transaction_062    where  cardType = 'visa'                                               
--比較大小條件
/* 5. 單價大於 500的商品*/
Select  *  from   Product_062   where unitPrice > 500                                                     
--多個條件 AND
/* 6. 價格介於 500 與 650 元之間的商品  */
Select  *  from   Product_062  where  unitPrice >= 500 and unitPrice <= 650                                                   
/* 7. 價格低於600元的書*/
Select  *  from  Product_062  where catelog = 'book' and unitPrice < 600                                                     
--多個條件 OR
/*8. 交易方式透過fax 或 email 的交易資料*/
Select  *  from  Transaction_062    where   method = 'fax' or method = 'email'                                                  
--否定 Not
/* 9. 不是透過購物車的交易資料*/
Select  *  from  Transaction_062   where not method = 'cart'                                                     
 
--空值 IS NULL /非空值 IS NOT NULL 
/* 10. 沒有透過介紹人(introducer)的會員id, name */
Select  pId, name from  Member_062 where introducer is null                                                  
/* 11. 經由介紹進來的會員id, name  (即introducer 不是空值 )*/
Select  pId,name  from Member_062  where introducer is not null                                          
/* 12. 沒有聯絡方式的會員(即沒有email, 也沒有聯絡電話)*/
Select  *  from  Member_062  where email is null and phone is null                                                        
--模糊相似條件 LIKE , 
/* 13. 所有的女會員…pid like '_2%'*/
Select *   from  Member_062 where pId like '_2%'                                                 
/* 14. 住台北市的會員姓名及地址*/
Select  name, address    from  Member_062 where   address like '台北市%'                                         
--IN 集合條件 / Not IN 集合條件
/* 15. 透過fax ,及 email 的交易資料  請使用(in (‘fax’,’email’)*/
Select  *  from   Transaction_062   where method in('fax','email')                                                     
/* 16. 不是透過fax ,及 email 的交易資料  (請使用not  in (‘fax’,’email’)*/
Select  *  from  Transaction_062    where method not in ('fax','email')                                                    
-- 範圍條件 BETWEEN .. AND 
/* 17. 價格介於 500 與 650 元之間的商品)*/
Select  *  from  Product_062  where unitPrice between 500 and 650                                                     
--排序(由高到低ORDER  BY DESC)及排名次(TOP N, TOP N PERCENT)
/* 18. 價格由高至低列出商品資料*/
Select  *   from   Product_062   order by unitPrice desc                       
/* 19. 列出價格最高的3樣商品之商品名稱及商品價格*/
Select   top 3 pName,unitPrice   from  Product_062   order by  unitPrice desc                         
/* 20. 列出價格在前百分之10的商品資料*/
select top 10 percent * from Product_062 order by unitPrice desc
- 使用distinct 查詢不重覆的資料
/* 21. 有購買記錄的會員 id  (一個人如果有多次購買,只出現一次名字, distinct)*/
select distinct transMid from Transaction_062
/* 22. 列出有被購買過的商品編號 (同一商品只要出現一次) */
select distinct pNo from Product_062
--日期函數
/* 23. 本月份生日的會員id, name,生日   -- month(birthday)=month(getdate())*/
select mid,name,birthday from Member_062 where month(birthday)=month(getdate())
/* 24. 2003年的交易……  year函數*/
select * from dbo.Transaction_062 where year(transTime) = 2003

檔名store_select02_999.sql
相關教學文件: http://db.tt/pUIdNIVs

使用上一次建立的資料庫..

/*  1. 列出商品編號,名稱與創作人姓名(product, author) */
/*  2. 列出你所出版的商品編號,商品名稱 (product, author) */
/*  3. 列出交易編號，會員id 及會員姓名 (transaction, member) */
/*  4. 列出會員id, 姓名及介紹人id,姓名 (沒有介紹人的不必列出) (member, member) P204, * /
/*  5. 列出會員id, 姓名及介紹人 id,姓名 (所有人都要列出,不管有沒有介紹人) P204, LEFT OUTER JOIN  */
 /* 6. 列出有被購買過的商品編號, 商品名稱 (同一商品只要出現一次) (DISTINCT) */
/*  7. 列出沒有被購買過的商品編號與名稱 (record, product, not in ) P199 In 運算子 */

use bookstore_062
SELECT * FROM dbo.Author_062;
SELECT * FROM dbo.Browse_062
SELECT * FROM dbo.Cart_062
SELECT * FROM dbo.Member_062
SELECT * FROM dbo.Order_062
SELECT * FROM dbo.Product_062
SELECT * FROM dbo.Record_062
SELECT * FROM dbo.Transaction_062



/* 1. 列出商品編號,名稱與創作人姓名(product, author)*/
SELECT dbo.Product_062.pNo  ,  pName , name        FROM      dbo.Product_062, dbo.Author_062
WHERE     dbo.Product_062.pNo     =        dbo.Author_062.pNo                        
/* 2. 列出你所出版的商品編號,商品名稱  (product, author)*/
SELECT  Product_062.pNo  ,  pName      FROM        dbo.Product_062        ,       dbo.Author_062             
WHERE       Product_062.pNo = Author_062.pNo    and        name = '張崴凱'
/* 3. 列出交易編號，會員id 及會員姓名  (transaction, member)*/
SELECT tNo   ,     Member_062.mId    ,   name   FROM    Transaction_062    ,   Member_062              
WHERE     Member_062.mId = Transaction_062.transMid               
/* 4. 列出會員id, 姓名及介紹人id,姓名 (沒有介紹人的不必列出) (member, member) P204, */
SELECT  m1.mid , m1.name , m1.introducer , m2.name                       
FROM  Member_062 as m1  , Member_062 as m2   WHERE   m1.introducer = m2.mId                                    
/* 5. 列出會員id, 姓名及介紹人 id,姓名 (所有人都要列出,不管有沒有介紹人) P204, LEFT OUTER JOIN */
SELECT  m1.mid , m1.name , m1.introducer , m2.name                       
FROM  Member_062 as m1  left outer join Member_062 as m2  on  m1.introducer = m2.mId     
/* 6. 列出有被購買過的商品編號, 商品名稱  (同一商品只要出現一次) (DISTINCT)*/
select  distinct Product_062.pNo ,  pName   from   Product_062 , Record_062                
WHERE   Product_062.pNo = Record_062.pNo
/* 7. 列出沒有被購買過的商品編號與名稱  (record, product, not in  ) P199 In 運算子*/
Select  pNo, pName  from  Product_062  where  pNo  not  in  (select  pNo  from  Record_062)

seclect03 (group分群)
相關教學文件: http://db.tt/QB4NMr7O
相關教學文件: http://db.tt/rOsoYFqv

use bookstore_062
SELECT * FROM dbo.Author_062;
SELECT * FROM dbo.Browse_062
SELECT * FROM dbo.Cart_062
SELECT * FROM dbo.Member_062
SELECT * FROM dbo.Order_062
SELECT * FROM dbo.Product_062
SELECT * FROM dbo.Record_062
SELECT * FROM dbo.Transaction_062

/* (01)列出所有商品數、平均價、最高價，和最低價 (P.208  Q14)*/
SELECT COUNT(*)  AS 商品數 , AVG(unitPrice)  AS平均價,  MAX(unitPrice)  AS最高價,
MIN(unitPrice)  AS最低價 FROM   Product_062     
/* (02A)透過介紹人進來的會員人數*/
SELECT    COUNT(introducer) as 透過介紹人進來的會員人數   FROM   dbo.Member_062      
/* (02A)住台北市的會員數*/
SELECT    COUNT(*)  as 住台北市的會員數 FROM  Member_062  WHERE address = '台北市%'                             
/* (02B) 2003年完成的交易數 */
select COUNT(*) as 二零零三年完成的交易數 from dbo.Transaction_062 where year(transTime)=2003
/* (02C)單價 >500元的商品數 */
select COUNT(*) as 單價高於五百元 from dbo.Product_062 where unitPrice > 500
/*(02D)12月份生日的會員數*/
select COUNT(*) as  十二月份生日 from dbo.Member_062 where MONTH(birthday) = 12
/* (03)列出類別，商品樣數*/
SELECT   catalog , COUNT(*) as 列出類別商品樣數  FROM   Product_062    GROUP  BY  catalog  
/* (03A) 商品樣數> 2的,請列出商品類別及商品樣數*/
SELECT  catalog , COUNT(*) as 商品樣數大於二  FROM   Product_062    GROUP BY  catalog  HAVING  COUNT(*)  > 2 

 
/*(04)列出每一筆交易的交易編號和交易總金額  P208-Q15*/
SELECT  tNo  , SUM(salePrice) as 交易總金額  FROM  Record_062   GROUP  BY  tNo  
/*(04A)列出每筆交易總金額超過???錢的交易編號及其交易總金額  (Having)*/
SELECT  tNo , sum(salePrice) as 交易總金額  FROM   dbo.Record_062   GROUP BY   tNo         
Having  SUM(salePrice) >2000                  
/* (04B)：列出每一筆上網 (method=’cart’)達成的交易之交易編號和購買商品總樣數 P208-Q16 */
SELECT   Record_062.tNo  , COUNT(*) as 交易編號和購買商品總樣數  FROM   Record_062, dbo.Transaction_062
WHERE  Record_062.tNo=dbo.Transaction_062.tNo  AND  method='cart'     GROUP BY    Record_062.tNo  

select tNo , COUNT(*) as  交易編號和購買商品總樣數 from Record_062 
where tNo in (select tNo from Transaction_062 where method='cart') group by tNo
/*(05)列出每一位會員的會員編號和交易總金額  (沒有交易的會員不必列出 P209-Q18*/
SELECT     transMid    , SUM(salePrice) as 會員編號和交易總金額  FROM   dbo.Transaction_062 ,  dbo.Record_062     
WHERE    dbo.Transaction_062.tNo = dbo.Record_062.tNo       GROUP BY  transMid 
/*(05A)列出每一位會員的會員編號和交易總金額  (沒有交易的會員也要列出*/
SELECT    mId   , SUM(salPrice) as 交易總金額                    
FROM   dbo.Member_062 LEFT OUTER JOIN dbo.Record_062  ON 
WHERE  dbo.Transaction_062.tNo = dbo.Record_062.tNo   GROUP BY  mId                     
/*(06)對每一個商品，列出其商品編號，總銷售數量及總銷售金額P208-Q16*/
SELECT   pNo , SUM(amount)as 總銷售數量 ,  SUM(salePrice) as 總銷售金額    FROM   Record_062    GROUP BY pNo           
/* (07)列出商品編號及作者人數  )*/
SELECT    pNo  , COUNT(*)as 作者人數   FROM  Author_062   GROUP  BY   pNo  
/* (07A) 列出每商品的商品編號及作者人數(沒有作者的商品也要列出*/
SELECT   Product_062.pNo  ,COUNT(Author_062.name) as  作者人數                 
FROM    Product_062   LEFT OUTER  JOIN  Author_062   ON   Product_062.pNo=Author_062.pNo                                
GROUP BY  Product_062.pNo                   
/* (08)每個月份的生日人數*/
SELECT  MONTH(birthday) as 月份, COUNT(*)  as 人數 FROM  dbo.Member_062  GROUP BY  MONTH(birthday) having month(birthday) is not null
--Subquery子查詢
/* 沒有交易的會員id,姓名 */
SELECT mId, name FROM  Member_062  WHERE  mId  NOT IN (SELECT  Transmid  FROM  Transaction_062)
/* 沒有被購買的商品 */
select * from Product_062 where pNo not in (select pNo from Record_062)

store_update999.sql

6.5.1 SQL的修改語法

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

1.刪除會員 c0927777 所有的瀏覽記錄

2.把所有書類商品定價提高1成

3 設定你的介紹人是 jenny (請查一下 jenny 的員工代號

6.3.3 更改資料表(Alter Table)



**改變欄位
4. 在「會員」資料表新增一個sex 欄位, 固定長度1個字元的文字
**新增欄位
ALTER TABLE table_name
ADD column_name datatype

5. 將「商品」資料表的pName欄位 的寬度改為變動長度40

ALTER TABLE table_name
ALTER COLUMN column_name datatype

-- 6.將「商品」v00111 改為"A123456789B123456789C123456789D123456789"

**刪除欄位
ALTER TABLE table_name
DROP COLUMN column_name

6.5.1 SQL的修改語法
select * from dbo.Browse_062
select * from dbo.Product_062
select * from dbo.Member_062


--1.刪除會員 c0927777 所有的瀏覽記錄
select * from dbo.Browse_062 where mId='c0927777'
delete dbo.Browse_062 where mId='c0927777'
--2.把所有書類商品定價提高1成
select * from dbo.Product_062 where catalog='book'
update dbo.Product_062 set unitPrice = unitPrice*1.1  where catalog='book'
--3 設定你的介紹人是 jenny (請查一下 jenny 的員工代號
select mId from dbo.Member_062 where name='jenny'
update dbo.Member_062 set introducer = (select mId from dbo.Member_062 where name='jenny') where name='陳奕安' 
--6.3.3 更改資料表(Alter Table)



--**改變欄位
--4. 在「會員」資料表新增一個sex 欄位, 固定長度1個字元的文字
--**新增欄位
ALTER TABLE dbo.Member_062
ADD sex char(1)

--5. 將「商品」資料表的pName欄位 的寬度改為變動長度40

ALTER TABLE dbo.Product_062
ALTER COLUMN pName varchar(40)

-- 6.將「商品」v00111 改為"A123456789B123456789C123456789D123456789"
--**刪除欄位
update dbo.Product_062
set pName = 'A123456789B123456789C123456789D123456789'
where pNo = 'v00111'