[Database] Mysql語法整理

不知道有沒有漏掉的，這是我目前整理出來，常用到的語法。


====================================================
====================================================
@@@@@@------>資料庫管理

/-------------------------------------------------------------------------------
    建立資料庫
-------------------------------------------------------------------------------/
shell> mysql -u openacs -popenacs
mysql> CREATE DATABASE +(NAME);

/-------------------------------------------------------------------------------
 檢視現有的資料庫
-------------------------------------------------------------------------------/
shell> mysql -u openacs -popenacs
mysql> SHOW DATABASES;

/-------------------------------------------------------------------------------
 刪除現有的資料庫
-------------------------------------------------------------------------------/
shell> mysql -u openacs -popenacs
mysql> DROP DATABASE +(NAME);



====================================================
====================================================

@@@@@@------>資料表單管理

/-------------------------------------------------------------------------------
    建立資料表
-------------------------------------------------------------------------------/
shell> mysql -u openacs -popenacs
mysql> use +(DATABASE-NAME)
mysql> create table +(Table-Name){
sn integer auto_increment primary key,
name char(20),
mail char(50),
home char(50),
messages char(50),
...
       };

/-------------------------------------------------------------------------------
    檢視資料表清單
-------------------------------------------------------------------------------/
(1)
shell> mysql -u openacs -popenacs
mysql> use +(DATABASE-NAME)
mysql> show tables;
--------------------------------------------------------------------------------
(2)
shell> mysql -u openacs -popenacs
mysql> show tables from +(DATABASE-NAME);

/-------------------------------------------------------------------------------
    刪除資料表
-------------------------------------------------------------------------------/
shell> mysql -u openacs -popenacs
mysql> use +(DATABASE-NAME)
mysql> drop +(Table-Name);

/-------------------------------------------------------------------------------
列出資料表"欄位"資訊
-------------------------------------------------------------------------------/
shell> mysql -u openacs -popenacs
mysql> use +(DATABASE-NAME)
mysql> describe +(Table-Name);


====================================================
====================================================

@@@@@@------>資料表單內容管理

/-------------------------------------------------------------------------------
列出資料表欄位、內容資訊
-------------------------------------------------------------------------------/
(1)
shell> mysql -u openacs -popenacs
mysql> use +(DATABASE-NAME)
mysql> show columns from +(Table-Name);
--------------------------------------------------------------------------------
(2)
shell> mysql -u openacs -popenacs
mysql> show columns from +(Table-Name) from +(DATABASE-NAME);

/-------------------------------------------------------------------------------
修改資料表"欄位"名稱
-------------------------------------------------------------------------------/
shell> mysql -u openacs -popenacs
mysql> use +(DATABASE-NAME)
mysql> alter table +(Table-Name) charange column +(舊欄位名字) +(新欄位名字) +(資料型態);

/-------------------------------------------------------------------------------
新增資料表"欄位"名稱
-------------------------------------------------------------------------------/
shell> mysql -u openacs -popenacs
mysql> use +(DATABASE-NAME)
mysql> alter table +(Table-Name) add column +(欄位名字) +(資料型態);

/-------------------------------------------------------------------------------
插入資料表"欄位"的內容
-------------------------------------------------------------------------------/
shell> mysql -u openacs -popenacs
mysql> use +(DATABASE-NAME)
mysql> insert into +(Table-Name)(欄位1,欄位2,欄位3,欄位4, ...... 欄位N)
       values('值1','值2','值3','值4', ...... '值N');

/-------------------------------------------------------------------------------
更新、修改資料表"欄位"的內容
-------------------------------------------------------------------------------/
shell> mysql -u openacs -popenacs
mysql> use +(DATABASE-NAME)
mysql> update +(Table-Name)
       set +(欄位1,欄位2,欄位3,欄位4, ...... 欄位N) (ex: password=password('xxx') )
       where +(條件式) (ex: user='root');

/-------------------------------------------------------------------------------
刪除資料表"欄位"
-------------------------------------------------------------------------------/
shell> mysql -u openacs -popenacs
mysql> use +(DATABASE-NAME)
mysql> alter table +(Table-Name) drop column +(欄位名字);

/-------------------------------------------------------------------------------
資料表內容檢視及管理(select、delete)
-------------------------------------------------------------------------------/
Selete用法：
(1)查詢所有欄位的資料-
select * from +(Table-Name);
(2)查詢單一個欄位的資料-
select +(欄位名字) from +(Table-Name);
(3)查詢多個欄位的資料-
select +(欄位名字0),+(欄位名字1),+(欄位名字2),.. from +(Table-Name);
(4)有條件的，查詢所有欄位的資料-
(條件式ex: user='root', name='Brian')
select * from +(Table-Name) where +(條件式);
(5)有條件的，查詢所有欄位的資料，搭配 and-
(條件式ex: user='root', name='Brian')
select * from +(Table-Name) where +(條件式0) and +(條件式1);
(6)有條件的，查詢所有欄位的資料，搭配 or-
(條件式ex: user='root', name='Brian')
select * from +(Table-Name) where +(條件式0) or +(條件式1);
(7)查詢特定範圍的欄位資料，between-
select * from +(Table-Name) where +(欄位名字) between +(value1 and value2);
(8)查詢欄位資料為空值的資料-
select * from +(Table-Name) where +(欄位名字) is null;
(9)查詢結果排序-
遞增->
select * from +(Table-Name) order by +(欄位名字);
遞減->
select * from +(Table-Name) order by +(欄位名字) desc;
(10)查詢比對字串、並列出單一欄位-
select +(欄位名字) from +(Table-Name) where +(欄位名字) like '%字串%';
(11)查詢比對字串、並列出單一欄位-
select * from +(Table-Name) where +(欄位名字) like '%字串%';
--------------------------------------------------------------------------------
Delete用法：
(1)刪除條件值的資料-
(條件式ex: user='root', name='Brian')
delete from +(Table-Name) where +(條件式);
(2)刪除條件值的資料，搭配and-
(條件式ex: user='root', name='Brian')
delete from +(Table-Name) where +(條件式1) and +(條件式2);
(3)刪除條件值的資料，搭配or-
(條件式ex: user='root', name='Brian')
delete from +(Table-Name) where +(條件式1) or +(條件式2);
(4)刪除條件值的資料，比對條件值(字串)的資料
(條件式ex: user='root', name='Brian')
delete from +(Table-Name) where +(欄位名字) like '%字串%';



====================================================
====================================================

@@@@@@------>使用者權限跟Mysql安全管理

/-------------------------------------------------------------------------------
使用者帳號、密碼建立
-------------------------------------------------------------------------------/
/* 建立openacs使用者，並設定為localhost方可Access的權限，預設password為openacs */
shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.*
TO openacs@localhost IDENTIFIED
BY 'openacs' WITH GRANT OPTION;
/* 說明.... */
   GRANT +(option : RELOAD,PROCESS,SELECT,INSERT,UPDATE,DELETE,CREATE,DROP )
   ON *.* TO username@+(localhost,%)
   INDENTIFIED BY '+(password)' WITH GRANT OPTION;

/-------------------------------------------------------------------------------
  更新使用者密碼
-------------------------------------------------------------------------------/
/* 更新使用者密碼 */
shell> mysql -u root mysql
mysql> UPDATE user SET Password=openacs('new_password') WHERE user='openacs';
mysql> FLUSH PRIVILEGES;
/* openacs 是我測試用的帳號，我密碼假設與帳號一樣 */

/-------------------------------------------------------------------------------
   Mysqld 安全選項
-------------------------------------------------------------------------------/
/* mysqld 選項影響安全 */
--skip-grant-tables 這個選項導致伺服器根本不使用權限系統。
--secure
--skip-name-resolve
--skip-networking