學習筆記

DDL、DML請參考這邊

DDL  :架構，就是Table Schema

DML :資料，ex :insert、update、delete..這些

--查出「陳一美」資料
SELECT *
FROM sqltest.dbo.Table_1
WHERE name='陳一美'

--查出名字有「大」的資料[like]
SELECT *
FROM sqltest.dbo.Table_1
WHERE name LIKE '%大%'

--查出所有女生，並依照年齡排序(大→小)[ORDER BY]
SELECT *
FROM sqltest.dbo.Table_1 a
JOIN sqltest.dbo.Table_2 b
ON a.name=b.name
WHERE b.sex='1'
ORDER BY a.age ASC--預設也是ASC

--查出所有男生，並依照年齡排序(小→大)[ORDER BY]
SELECT *
FROM sqltest.dbo.Table_1 a
JOIN sqltest.dbo.Table_2 b
ON a.name=b.name
WHERE b.sex='0'
ORDER BY a.age DESC

--查出是男生，年齡在20歲之前的
SELECT *
FROM sqltest.dbo.Table_1 a
JOIN sqltest.dbo.Table_2 b
ON a.name=b.name
WHERE b.sex='0'
AND a.age<='20'

--查出歲數在20~30之間的[BETWEEN AND]
SELECT *
FROM sqltest.dbo.Table_1
WHERE age BETWEEN '20' AND '30'

--查出歲數等於32的[IN]
SELECT *
FROM sqltest.dbo.Table_1
WHERE age IN ('32')

--將多拉V夢的歲數改為200[UPDATE]
UPDATE sqltest.dbo.Table_1
SET age='200'
WHERE name='多拉V夢'

--查出男生與女生的歲數平均[GROUP BY]
SELECT b.sex,AVG(age)
FROM sqltest.dbo.Table_1 a
JOIN sqltest.dbo.Table_2 b
ON a.name=b.name
GROUP BY b.sex;

--查出男生與女生的歲數平均，並顯示平均大於20的資料[GROUP BY][HAVING]
SELECT b.sex,AVG(age)
FROM sqltest.dbo.Table_1 a
JOIN sqltest.dbo.Table_2 b
ON a.name=b.name
GROUP BY b.sex
HAVING AVG(age)>'20'

下一篇:基本語法練習題2_JOIN篇