程式開發學習之路

mySql的常用指令


一、登入mySql的方式
mysql -hhostname -uroot -ppassword

Example： mysql -uroot -proot -h10.10.10.10

二、離線指令
quit
\q

三、顥示連結的狀態
status

四、建立資料庫
create database 資料庫名稱;

五、刪除資料庫
drop database 資料庫名稱;

六、show 出資料庫
show databases;

七、檢查使用中的資料庫
select database();

八、使用資料庫
use 資料庫名稱;

九、顯示資料庫內的資料表名稱
show tables;

十、顯示資料庫欄位名稱及內容
describe 資料表名稱;

十一、查看mysql的目前有那些在執行
show processlist;

十二、顯示索引表
show index 資料表名稱;

十三、匯入資料
mysql -uroot -p 資料庫<資料檔名.sql

SELECT 
SELECT "欄位" FROM "表格名稱";

WHERE 
SELECT "欄位"
FROM "表格名稱"
WHERE "條件式"
條件式為：欄位=值

DISTINCT
SELECT DISTINCT "欄位"
FROM "表格名稱"

COUNT
SELECT COUNT("欄位")
FROM "表格名稱"

ORDER BY
SELECT "欄位"
FROM "表格名稱"
WHERE "條件" #[不一定要有]
ORDER BY "欄位" [ASC|DESC] #[要放在最後]

GROUP BY
SELECT "欄位1", SUM("欄位2")
FROM "表格名稱"
GROUP BY "欄位1"

HAVING 
SELECT "欄位1", SUM("欄位2")
FROM "表格名稱"
GROUP BY "欄位1"
HAVING (函數條件)

CREATE TABLE 
CREATE TABLE "表格名稱"
("欄位 1" "資料種類",
"欄位 2" "資料種類"",
... )"基本設定";

DROP TABLE 
DROP TABLE "表格名稱"

TRUNCATE TABLE 
TRUNCATE TABLE "表格名稱"

INSERT INTO 
INSERT INTO "表格名稱" ("欄位1", "欄位2", ...)
VALUES ("值1", "值2", ...)

UPDATE 
UPDATE "表格名稱"
SET "欄位1" = 要更新的值
WHERE "條件式"

DELETE FROM 
DELETE FROM "表格名稱"
WHERE "條件式"

AND及OR 
SELECT "欄位"
FROM "表格名稱"
WHERE "條件式"
AND "條件式"
OR "條件式"

IN 
SELECT "欄位"
FROM "表格名稱"
WHERE "欄位" IN ('值1','值2','值3',...)

BETWEEN
SELECT "欄位"
FROM "表格名稱"
WHERE "欄位" BETWEEN '值1' AND '值2'

LIKE
SELECT "欄位"
FROM "表格名稱"
WHERE "欄位" LIKE 值

IF("條件式",真,假)
SELECT "欄位"
FROM "表格名稱"
WHERE 欄位=IF("條件式",真,假)

CASE 
WHEN "條件式" 
THEN "條件為成立" 
ELSE "條件為不成立" 
END
SELECT
CASE
WHEN "欄位"="值"
THEN 1
ELSE 0
END
FROM "表格名稱"