發表新文章 回覆主題  [ 1 篇文章 ] 

討論區首頁 : 資訊專區 : PHP程式設計

發表人 內容
 文章主題 : MySQL的重要語法
文章發表於 : 2012年 6月 4日, 20:40 
離線
系統管理員

註冊時間: 2009年 1月 14日, 06:05
文章: 1419
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


Back to top
 個人資料  
 
顯示文章 :  排序  
發表新文章 回覆主題  [ 1 篇文章 ] 

討論區首頁 : 資訊專區 : PHP程式設計


誰在線上

正在瀏覽這個版面的使用者:沒有註冊會員 和 1 位訪客


不能 在這個版面發表主題
不能 在這個版面回覆主題
不能 在這個版面編輯文章
不能 在這個版面刪除文章
不能 在這個版面上傳附加檔案

搜尋:
前往 :  
cron
Style by Midnight Phoenix & N.Design Studio
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group.
正體中文語系由 竹貓星球 維護製作