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 更新記憶體上的資料
• update user set password=OLD_PASSWORD('1234')

刪除空帳號

• 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 *.*　 把所有資料庫及資料表授權給別人，太危險了！

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

建立資料庫　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):

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: ********

字元語言設定

my.ini

default-character-set=big5  內定為 latin1   可設為 gb2312