From http://www.hmes.kh.edu.tw/~jona/redhat/mysqlphp/mysqlsyntax.htm
MySQL的重要語法
[帳號與權限] [資料庫/資料表/欄位的操作] [紀
錄的操作] [資料的
輸出]
一、帳號與權限
設定 root 和其他 user 的密碼
- mysqladmin -u root password '新密碼'
- mysqladmin -u root -p
- Enter password: 此時再輸入密碼(建議採用)
- use mysql;
mysql> UPDATE user SET password=password('新密碼') where user='root';
只改 root 的密碼，如果沒有用 where ，則表示改全部 user 的密碼
- mysql> FLUSH PRIVILEGES; 在 mysql
資料庫內，一定要用 flush 更新記憶體上的資料
刪除空帳號
- mysql> DELETE FROM user WHERE User = '';
- mysql> FLUSH PRIVILEGES;
建立新帳號
- mysql> GRANT 權限 ON 資料庫或資料表 TO 使用者
IDENTIFIED BY '密碼';
權限
資料庫或資料表
*.* 所有資料庫裡的所有資料表
* 預設資料庫裡的所有資料表
資料庫.* 某一資料庫裡的所有資料表
資料庫.資料表 某一資料庫裡的特定資料表
資料表 預設資料庫裡的某一資料表
設定/修改權限
- 用 root 登入 MySQL
mysqladmin -u root -p
Enter password:
- mysql> GRANT all ON db35.* TO
s35@'localhost' IDENTIFIED BY 's35';
把 db35 這個資料庫(含其下的所有資料表)，授權給 s35，從 localhost 上來，密碼為s35
- mysql> GRANT all ON *.*
把所有資料庫及資料表授權給別人，太危險了！
- mysql> GRANT all??? ON www.* TO
'*'@'*' IDENTIFIED BY '';
把 www 這個資料庫(含其下的所有資料表)，授權給 任何機器任何人，無密碼（通常給不特定人士使用）
- mysql> FLUSH PRIVILEGES;
（最後一定要強迫更新權限）
二、資料庫/資料表/欄位的操作
建立資料庫 CREATE DATABASE 資料庫名;
語法：CREATE
DATABASE db_name
使用資料庫 USE
資料庫名;
語法：USE
db_name
刪除資料庫 DROP DATABASE 資料庫名;
語法：DROP
DATABASE [IF EXISTS] db_name
建立資料表
CREATE TABLE 資料表名 (欄位1 資料型態, 欄位2 資料型態,
......);
語法：
CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition,...)
[table_options] [select_statement]
例：
craete database basic;
use basic;
create table basic(
no char(4)
name char(10)
id char(10));
create_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition]
or PRIMARY KEY (index_col_name,...)
or KEY [index_name] KEY(index_col_name,...)
or INDEX [index_name] (index_col_name,...)
or UNIQUE [INDEX] [index_name]
(index_col_name,...)
or [CONSTRAINT symbol] FOREIGN KEY index_name
(index_col_name,...)
[reference_definition]
or CHECK (expr)
資料結構(type):
資料型態 |
說明 |
TINYINT |
有符號的範圍是-128到127，
無符號的範圍是0到255。 |
SMALLINT |
有符號的範圍是-32768到32767，
無符號的範圍是0到65535。 |
MEDIUMINT |
有符號的範圍是-8388608到8388607，
無符號的範圍是0到16777215。 |
INT |
有符號的範圍是-2147483648到2147483647，
無符號的範圍是0到4294967295。 |
INTEGER |
INT的同義詞。 |
BIGINT |
有符號的範圍是-9223372036854775808到
9223372036854775807，無符號的範圍是0到18446744073709551615。 |
FLOAT |
單精密浮點數字。不能無符號。允許的值是-3.402823466E+38到-
1.175494351E-38，0 和1.175494351E-38到3.402823466E+38。 |
DOUBLE |
雙精密)浮點數字。不能無符號。允許的值是-
1.7976931348623157E+308到-2.2250738585072014E-308、
0和2.2250738585072014E-308到1.7976931348623157E+308。 |
DOUBLE PRECISION |
DOUBLE的同義詞。 |
REAL |
DOUBLE的同義詞。 |
DECIMAL |
DECIMAL值的最大範圍與DOUBLE相
同。 |
NUMERIC |
DECIMAL的同義詞。 |
DATE |
日期。支援的範圍是'1000-01-01'到'9999-12-31'。 |
DATETIME |
日期和時間組合。支援的範圍是'1000-01-01
00:00:00'到'9999-12-31 23:59:59' |
TIMESTAMP |
時間戳記。範圍是'1970-01-01
00:00:00'到2037年的某時。 |
TIME |
一個時間。範圍是'-838:59:59'到'838:59:59'。 |
YEAR |
2或4位數字格式的年(內定是4位)。允許的值是1901到2155。 |
CHAR |
固定長度，1 ～ 255個字元。 |
VARCHAR |
可變長度，1 ～ 255個字元。 |
TINYBLOB
|
|
TINYTEXT |
最大長度為255(2^8-1)個字符。 |
MEDIUMBLOB
|
|
MEDIUMTEXT |
最大長度為16777215(2^24-1)個字符。 |
LONGBLOB
|
|
LONGTEXT |
最大長度為4294967295(2^32-1)個字符。 |
ENUM |
一個ENUM最多能有65535不同的值。
|
SET |
一個SET最多能有64個成員。 |
index_col_name:
col_name [(length)]
reference_definition:
REFERENCES tbl_name
[(index_col_name,...)]
[MATCH FULL | MATCH PARTIAL]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET
NULL | NO ACTION | SET DEFAULT
table_options:
type = [ISAM | MYISAM |
HEAP]
or max_rows = #
or min_rows = #
or avg_row_length = #
or comment = "string"
or auto_increment = #
select_statement:
[ | IGNORE | REPLACE] SELECT
... (Some legal select statement)
刪除資料表 DROP TABLE 資料表名;
語法：DROP
TABLE [IF EXISTS] tbl_name [, tbl_name,...]
改變資料表結構(新增/刪除欄位、建立/取消索引、改變欄位資料型態、欄位重新命
名)
語法：
ALTER [IGNORE] TABLE tbl_name alter_spec
[, alter_spec ...]
alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
or ADD INDEX
[index_name] (index_col_name,...)
or ADD PRIMARY
KEY (index_col_name,...)
or ADD UNIQUE
[index_name] (index_col_name,...)
or ALTER
[COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or CHANGE
[COLUMN] old_col_name create_definition
or MODIFY
[COLUMN] create_definition
or DROP
[COLUMN] col_name
or DROP PRIMARY
KEY
or DROP INDEX
key_name
or RENAME [AS]
new_tbl_name
or table_option
範例：
欄位重新命名
mysql> ALTER TABLE t1 CHANGE a b INTEGER;
將資料表 t1 欄位 a 改名為 b (其資料型態是 integer)
改變欄位資料型態
mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT
NULL;
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT
NULL;
將資料表 t1 欄位 b 的資料型態改為 bigint not null
mysql> CREATE TABLE t1 (a
INTEGER,b CHAR(10));
mysql> ALTER TABLE t1 RENAME t2;
將資料表 t1 改名為 t2
mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c
CHAR(20);
將資料表 t2 欄位 a 資料型態由 integer 改為 tinyint not null
，欄位 b 改名為 c 資料型態改為 char(20)
mysql>
ALTER TABLE t2 ADD d TIMESTAMP;
在資料表 t2 增加新欄位 d 資料型態是 timestamp
mysql> ALTER TABLE t2 ADD INDEX (d), ADD
PRIMARY KEY (a);
在資料表 t2 ，對 d 欄位做索引，並以欄位 a 作為主索引鍵
mysql> ALTER TABLE t2 DROP COLUMN c;
刪除欄位 c
mysql> ALTER TABLE t2 ADD c INT
UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c);
新增欄位 c，並做索引(做索引的欄位必須為 not null )
資料表最佳化 OPTIMIZE
TABLE 資料表名
語法：OPTIMIZE TABLE
tbl_name
欄位長度有變動、刪除大量資料，都應進行資料表最佳化
三、紀錄的操作
插入一筆或多筆紀錄 INSERT
INTO 資料表(欄位1,欄位2,......)
VALUES(值1,值2,......), (值1,值2,......), ........
(MySQL 3.22.5
以後可插入多筆記錄)
語法：
INSERT [LOW_PRIORITY |
DELAYED] [IGNORE]
[INTO] tbl_name
[(col_name,...)]
VALUES
(expression,...),(...),...
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name
[(col_name,...)]
SELECT ...
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name
SET col_name=expression,
col_name=expression, ...
範例：
mysql> INSERT INTO tbl_name (col1,col2)
VALUES(15,col1*2);
不可寫成
mysql> INSERT INTO tbl_name (col1,col2)
VALUES(col2*2,15);
因為：欄位 col1 的值先填入後，才可以計算欄位 col2
從檔案讀入資料
語法：
LOAD DATA [LOCAL] INFILE
'file_name.txt' [REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t'] 每一欄位以某字元分開(內定是 tab)
[OPTIONALLY] ENCLOSED BY "] 每一欄位以某字元括住(內定是不使用括號)
[ESCAPED BY '\\' ]]
[LINES
TERMINATED BY '\n'] 設定換行的字元(內
定是 \n)
[IGNORE number LINES]
忽略最前面幾行(最前面幾筆記錄不抄進來)
[(col_name,...)]
範例：
mysql> USE db1;
mysql> LOAD DATA INFILE "./data.txt" INTO TABLE db2.my_table;
從目前 MySQL 目錄讀入 data.txt
mysql> LOAD DATA INFILE "./88.txt" INTO TABLE TEACHER FIELDS
TERMINATED BY ' ' ;
從目前 MySQL 目錄(我的在 /var/lib/mysql )讀入 data.txt ，每一欄位以 空白 分開
mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata
(col1,col2,...);
只將 persondata.txt 裡某些欄位的資料抓過來
刪除紀錄 DELETE [LOW-PRIORITY]
FROM 資料表名 WHERE 條件 [LIMIT rows]
語法：
DELETE [LOW_PRIORITY] FROM tbl_name
[WHERE where_definition] [LIMIT rows]
LOW-PRIORITY 是等到沒有用戶端使用時再刪
LIMIT rows 限制刪除紀錄的筆數
範例：
mysql> DELETE FROM 資料表名;
刪除所有紀錄
mysql> DELETE FROM 資料表名 WHERE 1>0;
刪除所有紀錄，但速度較慢，方便在螢幕上看
更新一筆紀錄 (語法與 INSERT 相同)
REPLACE INTO 資料表(欄位1,欄位2,......)
VALUES(值1,值2,......)
語法：
REPLACE [LOW_PRIORITY | DELAYED]
[INTO]
tbl_name [(col_name,...)]
VALUES
(expression,...)
or REPLACE [LOW_PRIORITY | DELAYED]
[INTO]
tbl_name [(col_name,...)]
SELECT
...
or REPLACE [LOW_PRIORITY | DELAYED]
[INTO]
tbl_name
SET
col_name=expression, col_name=expression,...
更新多筆紀錄
語法：
UPDATE [LOW_PRIORITY] tbl_name SET
col_name1=expr1,col_name2=expr2,... [WHERE
where_definition]
如果沒有設定 WHERE 條件，則整個資料表相關的欄位都更新
範例：
mysql> UPDATE persondata SET age=age+1;
將資料表 persondata 中，所有 age 欄位都加 1
mysql> UPDATE persondata SET age=age*2, age=age+1;
將資料表 persondata 中，所有 age 欄位都*2，再加 1
四、資料的輸出
SELECT
語法：
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [DISTINCT | ALL]
select_expression,...
[INTO OUTFILE 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY col_name,...]
[HAVING where_definition]
[ORDER BY {unsigned_integer
| col_name} [ASC | DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name] ]
範例：
排序輸出
select * from 資料表名 order by
欄位名1,欄位名2,欄位名3......
反向排序輸出
select * from 資料表名 order by 欄位名1,欄位名2,欄位名3...... desc
mysql> select concat(last_name,',
',first_name) AS full_name
from mytable ORDER BY full_name;
mysql> select t1.name, t2.salary from employee AS t1, info AS t2
where
t1.name = t2.name;
顯示資料庫 employee(別名 t1) 裡，資料表 t1 的欄位 name 和 資料表 t2 的欄位 salary 當.....
mysql> select t1.name, t2.salary from employee t1, info
t2 where
t1.name = t2.name;
mysql> select college, region, seed from tournament
ORDER BY
region, seed;
mysql> select college, region AS r, seed AS s from tournament
ORDER BY
r, s;
mysql> select college, region, seed from tournament
ORDER BY
2, 3;
mysql> select col_name from tbl_name HAVING col_name > 0;
mysql> select col_name from tbl_name WHERE col_name > 0;
mysql> select user,max(salary) from users
group by
user HAVING max(salary)>10;
mysql> select user,max(salary) AS sum from users
group by
user HAVING sum>10;
mysql> select * from table LIMIT 5,10; # Retrieve rows 6-15
mysql> select * from table LIMIT 5; #
Retrieve first 5 rows
在命令列下進行批次處理：
shell> mysql -h host -u user -p < batch-file
Enter password: ********
Type |
Bytes |
From |
To |
TINYINT |
1 |
-128 |
127 |
SMALLINT |
2 |
-32768 |
32767 |
MEDIUMINT |
3 |
-8388608 |
8388607 |
INT |
4 |
-2147483648 |
2147483647 |
BIGINT |
8 |
-9223372036854775808 |
9223372036854775807 |
Column type |
``Zero'' value |
DATETIME |
'0000-00-00 00:00:00' |
DATE |
'0000-00-00' |
TIMESTAMP |
00000000000000 (length depends on display size) |
TIME |
'00:00:00' |
YEAR |
0000 |
Column type |
Display format |
TIMESTAMP(14) |
YYYYMMDDHHMMSS |
TIMESTAMP(12) |
YYMMDDHHMMSS |
TIMESTAMP(10) |
YYMMDDHHMM |
TIMESTAMP(8) |
YYYYMMDD |
TIMESTAMP(6) |
YYMMDD |
TIMESTAMP(4) |
YYMM |
TIMESTAMP(2) |
YY |
Type |
Max.size |
Bytes |
TINYTEXT or TINYBLOB |
2^8-1 |
255 |
TEXT or BLOB |
2^16-1 (64K-1) |
65535 |
MEDIUMTEXT or MEDIUMBLOB |
2^24-1 (16M-1) |
16777215 |
LONGBLOB |
2^32-1 (4G-1) |
4294967295 |
Value |
CHAR(4) |
Storage required |
VARCHAR(4) |
Storage required |
'' |
' ' |
4 bytes |
'' |
1 byte |
'ab' |
'ab ' |
4 bytes |
'ab' |
3 bytes |
'abcd' |
'abcd' |
4 bytes |
'abcd' |
5 bytes |
'abcdefgh' |
'abcd' |
4 bytes |
'abcd' |
5 bytes |
Value |
Index |
NULL |
NULL |
"" |
0 |
"one" |
1 |
"two" |
2 |
"three" |
3 |
Other vendor type |
MySQL type |
BINARY(NUM) |
CHAR(NUM) BINARY |
CHAR VARYING(NUM) |
VARCHAR(NUM) |
FLOAT4 |
FLOAT |
FLOAT8 |
DOUBLE |
INT1 |
TINYINT |
INT2 |
SMALLINT |
INT3 |
MEDIUMINT |
INT4 |
INT |
INT8 |
BIGINT |
LONG VARBINARY |
MEDIUMBLOB |
LONG VARCHAR |
MEDIUMTEXT |
MIDDLEINT |
MEDIUMINT |
VARBINARY(NUM) |
VARCHAR(NUM) BINARY |
Column type |
Storage required |
TINYINT |
1 byte |
SMALLINT |
2 bytes |
MEDIUMINT |
3 bytes |
INT |
4 bytes |
INTEGER |
4 bytes |
BIGINT |
8 bytes |
FLOAT(X) |
4 if X <= 24 or 8 if 25 <= X <= 53 |
FLOAT |
4 bytes |
DOUBLE |
8 bytes |
DOUBLE PRECISION |
8 bytes |
REAL |
8 bytes |
DECIMAL(M,D) |
M+2 bytes if D > 0, M+1 bytes
if D = 0 (D+2, if M < D) |
NUMERIC(M,D) |
M+2 bytes if D > 0, M+1 bytes
if D = 0 (D+2, if M < D) |
Column type |
Storage required |
DATE |
3 bytes |
DATETIME |
8 bytes |
TIMESTAMP |
4 bytes |
TIME |
3 bytes |
YEAR |
1 byte |
Column type |
Storage required |
CHAR(M) |
M bytes, 1 <= M <= 255 |
VARCHAR(M) |
L+1 bytes, where L <= M and 1
<= M <= 255 |
TINYBLOB, TINYTEXT |
L+1 bytes, where L < 2^8 |
BLOB, TEXT |
L+2 bytes, where L < 2^16 |
MEDIUMBLOB, MEDIUMTEXT |
L+3 bytes, where L < 2^24 |
LONGBLOB, LONGTEXT |
L+4 bytes, where L < 2^32 |
ENUM('value1','value2',...) |
1 or 2 bytes, depending on the number of enumeration values
(65535 values maximum) |
SET('value1','value2',...) |
1, 2, 3, 4 or 8 bytes, depending on the number of set members
(64 members maximum) |
