[SQL] SQL基本語法

撰寫順序

/*****************
* SELECT
******************/


-- 選取所有員工資料
SELECT * FROM `emp` ;

-- 選取所有員工的 員工編號, 姓名, 性別
SELECT EmpID, Name, Gender FROM emp
SELECT EmpID, Name, IF(Gender=0, "男", "女") FROM emp
SELECT EmpID, Name, IF(Gender=0, "男", "女") AS Gender2 FROM emp


-- 選取所有員工的 員工編號, 姓名+職稱, 性別
SELECT EmpID, concat(Name, " ", Title, " ", IF(gender=0, "先生", "小姐")), Gender FROM emp




/*****************
* WhERE
******************/
-- 選取員工編號 5 的員工
SELECT * FROM emp WHERE EmpID=5

-- 選取 女性的員工
SELECT * FROM emp WHERE gender=1

-- 選取 員工編號介於 6 和 10 之間的員工
SELECT * FROM Emp WHERE EmpID BETWEEN 6 AND 10;

-- 選取 員工編號不是介於 6 和 10 之間的員工
SELECT * FROM Emp WHERE EmpID NOT BETWEEN 6 AND 10;

-- 選取 員工編號為1,3,5,7,9 的員工
SELECT * FROM Emp WHERE EmpID IN (1,3,5,7,9);

-- 選取 有參與計劃的員工
SELECT * FROM Emp WHERE EmpID IN (SELECT EmpID FROM EmpProject)


-- 選取 住在中正區的員工
SELECT * FROM Emp WHERE Address Like'%中正區%'

-- 選取 不住在中正區的員工
SELECT * FROM Emp WHERE Address NOT Like'%中正區%'

-- 選取 員工編號 >= 10 的員工
SELECT * FROM Emp WHERE EmpID >= 10

-- 選取 員工編號 不等於 10 的員工
SELECT * FROM Emp WHERE EmpID != 10


/*****************
* ORDER BY
******************/

-- 選取 員工資料, 依照薪水排序, 由低至高
SELECT * FROM Emp ORDER BY Salary ASC
SELECT * FROM Emp ORDER BY Salary

-- 選取 員工資料, 依照薪水排序, 由高至低
SELECT * FROM Emp ORDER BY Salary DESC

-- 選取 員工資料, 依男女及薪水高低排序, 由高至低
SELECT * FROM Emp ORDER BY Gender, Salary DESC


/*****************
* GROUP BY
******************/

-- 選取 所有的員工數
SELECT COUNT(*) FROM Emp

-- 選取 男女生的員工數量
SELECT Gender, COUNT(*) AS CNT
FROM Emp
GROUP BY Gender


-- 各部門薪水總合
SELECT DepID, SUM(Salary)
FROM Emp
GROUP BY DepID


-- 各部門中男女的平均薪水
SELECT DepID, gender, AVG(Salary)
FROM Emp
GROUP BY DepID, gender

SELECT DepID, IF(Gender=0, '男', '女'), AVG(Salary)
FROM Emp
GROUP BY DepID, IF(Gender=0, '男', '女')

SELECT DepID, IF(Gender=0, '男', '女') AS Gender2, AVG(Salary)
FROM Emp
GROUP BY DepID, Gender2

/*****************
* HAVING
******************/

-- 平均薪資高於 $50,000 的部門
SELECT DepID, AVG(Salary)
FROM Emp
GROUP BY DepID
HAVING AVG(Salary) > 50000


-- 各部門薪資低於 $50,000 的員工數
SELECT DepID, COUNT(*)
FROM Emp
WHERE Salary < 50000
GROUP BY DepID

-- 超過1位員工薪資低於$50,000的部門, 依數量排序
SELECT DepID, count(*)
FROM Emp
WHERE Salary < 50000
GROUP BY DepID
HAVING COUNT(*) > 1
ORDER BY COUNT(*)



/*****************
* JOIN
******************/

-- 員工及其部門名稱
SELECT a.EmpID, a.Name, b.DepName
FROM Emp a JOIN Dep b ON a.DepID=b.DepID


-- 選出員工扶養親屬的狀況
SELECT a.*, b.*
FROM Emp a JOIN Family b ON a.EmpID = b.EmpID
#選出完全匹配的

SELECT a.*, b.*
FROM Emp a LEFT JOIN Family b ON a.EmpID = b.EmpID
#選出所有員工的狀況

-- 選出所有員工參與計劃的狀況
SELECT a.*, b.*, c.*
FROM Emp a LEFT JOIN EmpProject b ON a.EmpID = b.EmpID
LEFT JOIN Project c ON b.ProjectID = c.ProjectID
#JOIN多個表格

/*****************
* UNION
******************/
#垂直合併
-- 列出所有員工及其親屬
SELECT EmpID, name FROM Emp
UNION
SELECT EmpID, FamilyName FROM Family
ORDER BY EmpID


/*****************
* INSERT
******************/

-- 新增一筆員工資料
INSERT INTO Emp(Name, DepID) VALUES('小丸子', 2);

-- 複製 5 ~10 號的員工
INSERT INTO Emp(Name, Gender, DepID)
SELECT Name, Gender, DepID FROM Emp
WHERE EmpID BETWEEN 5 AND 10;



/*****************
* UPDATE
******************/

-- 小丸子長大變成大丸子
UPDATE Emp SET Name= '大丸子' WHERE EmpID = 17

-- 員工編號超過17, 姓名後面都加上 “-2”, 且都調到部門編號2
UPDATE Emp
SET Name = concat(Name, "-2"), DepID = 2
WHERE EmpID > 17



/*****************
* DELETE
******************/

-- 刪除員工編號超過16的資料
DELETE *  FROM Emp WHERE EmpID > 16