---
# System prepended metadata

title: h1 MySql 資料庫

---

# 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