痞客興的部落格

select * from customers where customerid = '1'

select * from orderdetails group by productid

select productid,
sum(quantity) as s_qty,
avg(quantity) as a_qty,
max(quantity) as max_qty,
min(quantity) as min_qty,
count(quantity) as c_qty
from orderdetails group by productid

insert into shippers values('100','ups','(606) 666-1234')

insert into shippers select * from newshippers

update products set price=price*0.5

update products set productname="Chang Sam", price=10.5 where productid=2

delete from shippers where shipperid='5'

delete from shippers(全刪注意）

SELECT * FROM Orders, Shippers where orders.shipperid=shippers.shipperid

select orders.orderid, shippers.shipperid from orders,shippers where orders.shipperid=shippers.shipperid

select o.orderid, s.shipperid from orders o,shippers s where o.shipperid=s.shipperid

create view myview as select orders.orderid, shippers.shipperid from orders,shippers where orders.shipperid=shippers.shipperid

CREATE TABLE stud_info
( stud_id CHAR(10) NOT NULL ,
stud_name NVARCHAR(10) NOT NULL ,
stud_sex BIT NOT NULL ,
stud_mail VARCHAR(100) NULL ,
stud_dob DATETIME NOT NULL ,
CONSTRAINT PK_stud_info PRIMARY KEY (stud_id)
) ;

CREATE TABLE stud_atte
( atte_date DATETIME NOT NULL ,
stud_id CHAR(10) NOT NULL ,
atte_stat BIT NOT NULL ,
CONSTRAINT PK_stud_atte PRIMARY KEY (atte_date,stud_id) ,
CONSTRAINT FK_stud_atteTOstud_info
FOREIGN KEY (stud_id) REFERENCES stud_info(stud_id)
) ;

CREATE TABLE stud_work
( work_date DATETIME NOT NULL ,
stud_id CHAR(10) NOT NULL ,
work_stat BIT NOT NULL ,
work_scor DECIMAL(5,2) NOT NULL ,
CONSTRAINT PK_stud_work PRIMARY KEY (work_date,stud_id) ,
CONSTRAINT FK_stud_workTOstud_info
FOREIGN KEY (stud_id) REFERENCES stud_info(stud_id)
) ;

CREATE TABLE stud_exam
( exam_date DATETIME NOT NULL ,
stud_id CHAR(10) NOT NULL ,
exam_scor DECIMAL(5,2) NOT NULL ,
scor_rate DECIMAL(5,2) NOT NULL ,
CONSTRAINT PK_stud_exam PRIMARY KEY (exam_date,stud_id) ,
CONSTRAINT FK_stud_examTOstud_info
FOREIGN KEY (stud_id) REFERENCES stud_info(stud_id)
) ;

CREATE TABLE stud_leav
( leav_date DATETIME NOT NULL ,
stud_id CHAR(10) NOT NULL ,
leav_stat CHAR(01) NOT NULL ,
leav_scor DECIMAL(2,1) NOT NULL ,
CONSTRAINT PK_stud_leav PRIMARY KEY (leav_date,stud_id) ,
CONSTRAINT FK_stud_leavTOstud_info
FOREIGN KEY (stud_id) REFERENCES stud_info(stud_id)
) ;

CREATE TABLE stud_scor
( stud_id CHAR(10) NOT NULL ,
work_scor DECIMAL(5,2) NOT NULL ,
leav_scor DECIMAL(5,2) NOT NULL ,
exam_scor DECIMAL(5,2) NOT NULL ,
CONSTRAINT PK_stud_scor PRIMARY KEY (stud_id) ,
CONSTRAINT FK_stud_scorTOstud_info
FOREIGN KEY (stud_id) REFERENCES stud_info(stud_id)
)

CREATE index index_name on table_name(column1,column2......) 建立資料索引,加快資料讀取速度

CREATE UNIQUE index index_name on table_name(column1,column2......) 建立資料索引並檢查唯一,加快讀取速度

alter table stud_info add stud_age int 舊表新增欄位

alter table stud_info
change stud_age stud_age decimal(3,0) 更改欄位屬性

alter table stud_info
drop column stud_age 刪除某一個欄位

insert into stud_info(stud_id,stud_name,stud_sex,stud_mail,stud_dob)
values("NK10100002","學生-002",1,"Mail-002@server","1994/03/01") 新增一筆資料

insert into stud_info values("NK10100003","學生-003",2,"Mail-003@server","1994/03/03") 新增一筆資料(簡易)

insert into stud_info values
("NK10100002","學生-002",1,"Mail-002@server","1994/03/01"),
("NK10100003","學生-003",2,"Mail-003@server","1994/03/03"); 新增多筆資料

INSERT INTO stud_info (stud_id,stud_name,stud_sex,stud_mail,stud_dob)
SELECT 'NK10100006','學生-006',0,'Mail-006@server','1994/04/01'
UNION SELECT 'NK10100007','學生-007',1,'Mail-007@server','1994/03/01'
UNION SELECT 'NK10100008','學生-008',0,'Mail-008@server','1994/02/01' 新增多筆資料

update stud_info set stud_name = "VIP" where stud_id="NK10100002" 更新資料

SELECT stud_name 姓名, stud_sex 性別 FROM stud_info 修改顯示的欄位名稱,運算元產生結果命名,多db相同欄位區別

SELECT DISTINCT atte_date FROM stud_atte 過濾掉相同的資料

SELECT * FROM stud_info WHERE stud_dob < "1994/07/01" 1994年上半年出生的

between少用,因為不參考index

SELECT * FROM stud_info ORDER BY stud_id 升幂排序

SELECT * FROM stud_info ORDER BY stud_id DESC 降幂排序

SELECT stud_id, stud_name FROM stud_info ORDER BY stud_dob desc 依生日由小排到大(要用desc)

SELECT DISTINCT work_scor FROM stud_work ORDER BY work_scor desc 依成績由小到大不重複

SELECT DISTINCT work_date, work_scor FROM stud_work ORDER BY work_date, work_scor DESC 依work_date先排再對work_scor後針對work_scor做降幂

distinct 和 groupby的結果是相同,但groupby的速度會慢很多,因為它會真的去做群組,distinct只取第一筆.

SELECT work_date, COUNT( * ) FROM stud_work GROUP BY work_date 每次日期有多少人交作業

SELECT work_date, MAX( work_scor ) FROM stud_work GROUP BY work_date 每次日期交作業的最高分

SELECT work_date, MIN( work_scor ) FROM stud_work GROUP BY work_date 每次日期交作業的最低分

SELECT work_date, AVG( work_scor ) FROM stud_work GROUP BY work_date 每次日期交作業的平均分

SELECT work_date, SUM( work_scor ) FROM stud_work GROUP BY work_date 每次日期交作業的加總總分

SELECT work_date, MAX( work_scor ) , MIN( work_scor ) , AVG( work_scor ) FROM stud_work GROUP BY work_date 每次日期交作業的最高分,最低分,平均分

SELECT work_date, COUNT( work_stat ) FROM stud_work WHERE work_stat =0 GROUP BY work_date 每次日期未交作業的人數

SELECT stud_id, COUNT( work_stat ) cnt FROM stud_work WHERE work_stat =0 GROUP BY stud_id ORDER BY cnt DESC 沒有交作業的排行

SELECT stud_work.stud_id, stud_info.stud_name, stud_work.work_date, stud_work.work_scor
FROM stud_work JOIN stud_info
ON stud_work.stud_id = stud_info.stud_id
以stud_id join二個表

SELECT stud_work.stud_id, stud_info.stud_name, stud_work.work_scor
FROM stud_work
JOIN stud_info ON stud_work.stud_id = stud_info.stud_id
以stud_work為主,若stud_work的stud_id沒有,雖stud_info有也不會列出來

SELECT stud_work.stud_id, stud_info.stud_name, stud_work.work_scor
FROM stud_work
RIGHT JOIN stud_info ON stud_work.stud_id = stud_info.stud_id
以stud_info為主,雖stud_work的stud_id沒有,只要stud_info有就會列出來

SELECT a.stud_id, b.stud_name, a.work_scor
FROM stud_work a
RIGHT JOIN stud_info b ON a.stud_id = b.stud_id
以資料庫別名使用

SELECT stud_info.stud_id, stud_info.stud_name, COUNT( stud_work.work_stat )
FROM stud_info
LEFT JOIN stud_work ON stud_work.stud_id = stud_info.stud_id
GROUP BY stud_work.stud_id
所有學生交作業的記錄

SELECT stud_info.stud_id, stud_info.stud_name, COUNT( stud_work.work_stat )
FROM stud_info
JOIN stud_work ON stud_work.stud_id = stud_info.stud_id
GROUP BY stud_work.stud_id
有交作業的學生的作業記錄

SELECT *
FROM stud_info
WHERE stud_id
IN (
SELECT DISTINCT stud_work.stud_id
FROM stud_work
WHERE stud_work.work_scor =100
)
列出成績100的學生資料

select * from stud_info where stud_id in(select stud_work.stud_id from stud_work where work_scor <(select avg(stud_work.work_scor) from stud_work))
低於平均分學生資料

select a.stud_id,a.stud_name,b.stud_id,b.stud_name from stud_info a, stud_info b where a.stud_dob = b.stud_dob and a.stud_id <> b.stud_id
查詢同一表內生日相同者(self join)

select max(work_scor) from stud_work where stud_id in(select stud_id from stud_info where stud_sex =1)
找出男生最高分

select stud_info.stud_id,stud_info.stud_name
from stud_info left join stud_work
on stud_info.stud_id = stud_work.stud_id
where stud_work.work_scor =(select max(stud_work.work_scor) from stud_work where stud_work.stud_id in(select stud_info.stud_id from stud_info where stud_sex = 1)) and stud_info.stud_sex = 1
找出男生最高分的id和name