MySQL管理用 SQL [轉貼]

原文作者: 燕俠 AaA

----------------------------------------
※ MySQL 使用者管理 SQL 語法：
----------------------------------------

$ mysql -u root
//AaA:第一次還沒設密碼時登入用

$ mysql -u root -p
//AaA:用密碼登入,也可以p後面直接加密碼,不過很容易被別人發現

$ mysql --i-am-a-dummy -u root -p
//告知我是初學者,刪資料會先被提示

mysql> SELECT VERSION(); //AaA:顯示目前MySQL版本

mysql> SELECT USER(); //AaA:顯示目前使用者

mysql> CREATE USER username;
//AaA:建立使用者,會產生沒有任何權限的使用者 username@%

mysql> set password for username = password('123456');
//AaA:設定使用者 username@% 的密碼

mysql> set password for username = password();
//AaA:將使用者 username@% 的密碼清空

mysql> set password for root@localhost = password('123456');
//AaA:設定使用者 root@localhost 的密碼

mysql> GRANT ALL ON *.* TO username;
//AaA:給予使用者所有權限(但不包含GRANT權限)

mysql> REVOKE all ON *.* FROM username;
//AaA:取消使用者所有權限 (*.* 前面*是資料庫,後面*是表格)

mysql> GRANT ALL ON *.* TO username IDENTIFIED BY 'password';
//AaA:給予使用者所有權限,並設定使用者密碼

mysql> FLUSH PRIVILEGES; //AaA:即時更新權限的更改

mysql> DROP USER username; //AaA:刪除使用者,等於執行以下兩個指令
mysql> DELETE FROM user WHERE User='username';
mysql> DELETE FROM db WHERE User='username';


---------------------------------------
※ MySQL 資料庫管理 SQL 語法：
---------------------------------------
mysql> SHOW DATABASES; //AaA:顯示所有databases
mysql> USE db_name; //AaA:使用某個database

mysql> CREATE DATABASES db_name; //AaA:創建一個database

mysql> DROP DATABASE db_name; //AaA:砍掉一個database


------------------------------------
※ MySQL 表格管理 SQL 語法：
------------------------------------
mysql> SHOW tables;
//AaA:顯示該database的所有table,必須先 USE databasename;

mysql> DESC table_name;
//AaA:取得tablename的schema,同 describe tablename;

mysql> SHOW COLUMNS FROM table_name;
//AaA:顯示表格的定義

mysql> CREATE TABLE table1 (
id CHAR(200) NOT NULL,
KEY index_name (id(10)));
//AaA:建立一個以id為索引的table

mysql> DROP TABLE table1;
//AaA:刪除一個TABLE table1

mysql> CREATE TABLE table2 (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name, first_name));
//AaA:建立一個以id為主鍵,而last_name,first_name為雙索引的table


//-- INSERT SQL 語法：
mysql> INSERT INTO table2(id, last_name, first_name) VALUES(22,'amzshar','liu');
mysql> INSERT INTO table2(id, last_name, first_name) VALUES(33,'cathy','chung');
mysql> INSERT INTO table2(id, last_name, first_name) VALUES(44,'bruce','lin');
mysql> INSERT INTO table2(id, last_name, first_name) VALUES(55,'qing','wang');
mysql> INSERT INTO table2(id, last_name, first_name) VALUES(66,'gino','fu');
mysql> INSERT INTO table2(id, last_name, first_name) VALUES(77,'annisa','chen');
//AaA插入數筆資料

//-- SELECT SQL 語法：
mysql> SELECT COUNT(*) from table2;
//AaA:查出有幾筆資料在 TABLE table2

mysql> SELECT * FROM table2
WHERE last_name="amzshar" OR first_name="liu";
//AaA:搜尋方式1

mysql> SELECT * FROM table2
WHERE last_name LIKE 'amz%';
//AaA:搜尋方式2

mysql> select (1+2)*3; //AaA:回應 9
mysql> select 1 IS NULL, 0 IS NULL, NULL IS NULL; //AaA:回應 0 0 1
mysql> select 2 BETWEEN 2 AND '3'; //AaA:回應 1
mysql> select ISNULL(1+1); //AaA:回應 0
mysql> select ISNULL(1/0); //AaA:回應 1
mysql> select ABS(-23); //AaA:回應 絕對值 32
mysql> select SQRT(4); //AaA:回應 平方根 2.000000
mysql> select GREATEST(2,0); //AaA:回應 最大值 2

mysql> select SUBSTRING('International',5);
//AaA:回應 子字串 'national'

mysql> select SUBSTRING('International',5,6);
//AaA:回應 子字串 'nation'

mysql> select REVERSE('abc');
//AaA:回應 'cba'

mysql> select LCASE('International');
//AaA:回應Low Case 'international'

mysql> select UCASE('International');
//AaA:回應Upper case 'INTERNATIONAL'

mysql> SELECT xx FROM tableD
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 7;
//AaA:查出最後7天內的資料

//-- UPDATE SQL 語法：
mysql> update table2 SET last_name='AaA' where id = 22;


//-- ALTER SQL 語法：
mysql> ALTER TABLE table2 ADD ts TIMESTAMP;
//AaA:在 TABLE table2 增加一個新TIMESTAMP列 ts

mysql> ALTER TABLE table2 DROP COLUMN ts;
//AaA:在 TABLE table2 刪除列 ts


//-- SHOW 的一些 SQL 語法：
mysql> SHOW DATABASES;
mysql> SHOW TABLES [FROM db_name];
mysql> SHOW COLUMNS FROM tbl_name [FROM db_name];
mysql> SHOW INDEX FROM table2 [FROM db_name];
mysql> SHOW GRANTS FOR user xxx;

//-- LOCK 的一些 SQL 語法：
mysql> LOCK TABLES table2 READ, test3 WRITE;
mysql> SELECT SUM(value) FROM table2 WHERE id= some_id;
mysql> UPDATE id
SET total_value=sum_from_previous_statement
where id=some_id;
mysql> UNLOCK TABLES;

//-- INDEX 的一些 SQL 語法：
mysql> CREATE INDEX index_name ON tbl_name (column(10));
mysql> DROP INDEX index_name ON tbl_name