（一）查看與修改 字符編碼
-
修改數(shù)據(jù)庫字符集
ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];
如：ALTER DATABASE myDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
-
把表默認(rèn)的字符集和所有字符列（CHAR,VARCHAR,TEXT）改為新的字符集
ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...]
如：ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
-
只是修改表的默認(rèn)字符集
ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];
如：ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
-
修改字段的字符集
ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
如：ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;
-
查看數(shù)據(jù)庫編碼
SHOW CREATE DATABASE db_name;
-
查看表編碼
SHOW CREATE TABLE tbl_name;
-
查看字段編碼
SHOW FULL COLUMNS FROM tbl_name;
（二）添加與刪除 外鍵
-
添加外鍵
alter table <表名> add foreign key (class_id) references <另一個(gè)表> (class_id) on delete set null;///在刪除外鍵時(shí)，將從表的外鍵值設(shè)置為null
- 刪除表的某個(gè)外鍵
SHOW CREATE TABLE user;
ALTER TABLE user DROP FOREIGN KEY location_id;
（三）改變某字段的數(shù)據(jù)類型
- 法一：
alter table <table_name> modify column <column_name> <new_data_type>;
- 法二：
alter table <table_name> change [column] <column_name> <new_column_name> <new_data_type>;【其中的"column"可寫可不寫】
（四）關(guān)于時(shí)間與日期類型
- DATATIME
- 大泄旮狻：8 Byte
- 范圍：1000-01-01 00:00:00/9999-12-31 23:59:59
- 格式：YYYY-MM-DD HH:MM:SS
- 用途：混合日期和時(shí)間值
- TIMESTAMP
- 大卸洹：8 Byte
- 范圍：1970-01-01 00:00:00/2037 年某時(shí)
- 格式：YYYYMMDD HHMMSS
- 用途：混合日期和時(shí)間值，時(shí)間戳
（五）MySQL啟動(dòng)與關(guān)閉相關(guān)
- Ubuntu環(huán)境蜒谤，apt-get安裝為例
- 啟動(dòng)：
sudo /etc/init.d/mysql startsudo start mysqlsudo service mysql start
- 關(guān)閉：
sudo /etc/init.d/mysql stopsudo stop mysqlsudo service mysql stop
- 重啟mysql：
sudo/etc/init.d/mysql restartsudo restart mysqlsudo service mysql restart
- Mac環(huán)境
- 啟動(dòng)：
sudo /usr/local/mysql/support-files/mysql.server start
- 關(guān)閉：
sudo /usr/local/mysql/support-files/mysql.server stop
- 重啟：
sudo /usr/local/mysql/support-files/mysql.server restart
