# MySQL 常用語法

###### tags: `MySQL`

## Basic

### 1. 建立table
CREATE TABLE **table_name** (**column_name** **column type**)
Ex: CREATE TABLE hw2_db.info (username VARCHAR(255), email VARCHAR(255), DateCreated DATETIME NOT NULL DEFAULT now());

### 2. 檢查table在不在
SELECT table_name FROM information_schema.TABLES WHERE table_name = **table_name**
Ex: SELECT table_name FROM information_schema.TABLES WHERE table_name ='info'

### 3. 刪除整個table
DROP TABLE **table_name**;
Ex: DROP TABLE hw2_db.info;

### 4. 插入資料
INSERT INTO **table_name** (**column_name1**, **column_name2**) VALUES (**value1**,**value2**);
ex: INSERT INTO hw2_db.info (username, email) VALUES ('b','b');

### 5. 刪除table資料(column還在)
- 清除所有資料
DELETE from **table_name**
Ex: delete from hw2_db.info

- 清除指定資料
DELERE from **table_name** where **column** = **value**
Ex: delete from team where sn = 4

### 6. 取出資料
- 取整個表
SELECT * from info
Ex: SELECT * from info

- 取整個表並照欄位資料進行排序
SELECT * from **table_name** ORDER BY **Column** ASC/DESC
Ex: SELECT * from info ORDER BY DateCreated ASC

### 7. 列出所有資料庫
show databases

### 8. 列出database所有table
show tables

### 9. 修改一筆資料
update **table_name** set **column** = **value** where **row** = **value**
p.s where可加任何條件

## Advanced configuration

### 1. 設定table的時區
//假設為台灣
SET time_zone = "+8:00"

### 2. 設定database可被修改
SET SQL_SAFE_UPDATES=0