# h1 MySql 資料庫 計算每堂課修課人數 ``` = SQL SELECT count(c.id),c.Name from enroll as e ,course as c where e.id=c.id group by c.id ``` 找到每個人修甚麼課 ```=+ SELECT s.name,c.name from enroll e ,course c,student s where e.id=c.id and s.id=e.sid order by s.id DESC ``` 找到每個仁修課的平均分數 ```=+ SELECT s.name,AVG(Score) from enroll e ,course c,student s where e.id=c.id and s.id=e.sid GROUP by s.id order by s.id DESC ``` 找到學長姐的姓名 ```=+ SELECT s.name,m.name from student s,student m where s.mentor =m.id ``` 1.CREATE 建立資料庫：CREATE DATABASE db_name 建立表：CREATE TABLE table_name (col_name col_type [NOT NULL] [PRIMARY KEY], ...) 2.DROP 刪除表：DROP TABLE table_name 3.ALTER 新增列：ALTER TABLE table_name ADD COLUMN col_type 新增主鍵：ALTER TABLE table_name ADD PRIMARY KEY(col_name) 4.INSERT 插入記錄：INSERT INTO table_name (col_name, ...) VALUES (col_value, ...) 5.DELETE 刪除記錄：DELETE FROM table_name WHERE col_name=col_value *safe update模式只能通過主鍵欄位更新記錄，更新同理 6.UPDATE 更新記錄：UPDATE table_name SET col_name=col_value, ... WHERE ... 7.SELECT 查詢部分欄位：SELECT col_name, ... FROM table_name WHERE ... 查詢全部欄位：SELECT * FROM table_name WHERE ... 8.OUTER JOIN (表) table_name1 LEFT/RIGHT OUTER JOIN table_name2 *左聯查詢把table1的內容全部查出，table2中只查出滿足條件的內容，其他為null 9.INNER JOIN （表） table_name1 INNER JOIN table_name2 ON table_name1.col_name1=table_name2.col_name2 *只查出滿足條件的記錄 10.ORDER BY 升序：SELECT ... ORDER BY col_name 降序：SELECT ... ORDER BY col_name DESC 11.聚合函式（欄位） 和：sum(col_name) 平均：avg(col_name) 最大：max(col_name) 最小：min(col_name) 計數：count(col_name) 12.GROUP BY 某欄位同值記錄合併：GROUP BY col_name eg. SELECT sum(col_name2) as new_col_name FROM table_name GROUP BY col_name