MySQL的重要語法

[帳號與權限] [資料庫/資料表/欄位的操作] [紀 錄的操作] [資料的 輸出]

一、帳號與權限

設定 root 和其他 user 的密碼

刪除空帳號

建立新帳號

設定/修改權限  

 

二、資料庫/資料表/欄位的操作

建立資料庫 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 有符號的範圍是-128127, 無符號的範圍是0255
SMALLINT 有符號的範圍是-3276832767, 無符號的範圍是065535
MEDIUMINT 有符號的範圍是-83886088388607, 無符號的範圍是016777215
INT 有符號的範圍是-21474836482147483647, 無符號的範圍是04294967295
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)