[資料庫]MariaDB

2019-05-23 本文已影响0人 RJ阿杰

複雜的資料處理可以讀出來使用pandas處理後存回SQL。

安裝及基本操作

安裝

載點:windows下載安裝程式安裝

簡介

伺服端程式:
伺服器:指一個持續執行的背景程序
mysql服務程式daemon，會在通訊埠(port)3306傾聽用戶端發出的查詢。
用戶端程式:
標準mysql用戶端程式就叫mysql，可以透過程式的文字介面執行sql查詢，也可以透過匯入文字檔查詢，不過大部分時候是透過各種語言撰寫程式來執行。
指令稿(script)
預選的mysql_safe指令稿是最常啟動mysql的方式。
工具
mysqlaccess負責產生帳號、設定權限。
mysqladmin可以透過指令管理資料庫伺服器本身、檢查狀態、關閉伺服器。
mysqlshow能檢查伺服器狀態以及資料庫與資料表的相關資訊。
mysqldump是最受歡迎的匯出工具之一，可以把資料和資料表匯出成純文字的dump(傾卸)檔，這種檔案可以備份資料或移往其他主機。
設定檔
設定檔名稱為my.ini或my.cnf
帳號
mysql帳號權限是依照"使用者名稱"跟"主機名稱"配組組合後所設定的權限所分配，所以就算都用一個root帳號登入依照使用主機不同(例如本地端跟遠端)也會有不同權限。

指令相關

SQL指令以 ; 或\g作為結束，若無則視為同一指令直到 ; 或\g出現，如果輸入了一些指令再按下ENTER之前想反悔可以輸入\c不需要再加;則會直接終止這條SQL指令輸入，SQL指令本身不區分大小寫。

開啟、關閉、登入服務

#新增資料庫伺服器:
mysqld --install MariaDB
#移除資料庫伺服器:
mysqld --remove MariaDB
#開啟服務:
net start MariaDB
#關閉服務:
net stop MariaDB
#登入管理系統:
mysql -u root -p
補充說明:這邊root可以換成其他使用者，root通常為最高權限帳號不建議直接使用root來做一般操作

帳號相關

#查詢使用者與主機:
mysql -u root -p -e "SELECT User,Host FROM mysql.user;"
#查詢權限
mysql -u root -p -e "SHOW GRANTS FOR '使用者名稱'@'主機名稱';"

#創建新使用者
mysql -u root -p -e "CREATE USER '使用者名稱'@'主機名稱' IDENTIFIED BY '密碼';"

#設定權限
mysql -u root -p -e "GRANT SELECT ON *.* TO '使用者名稱'@'主機名稱';"
補充說明:授權給某個帳號再所有資料庫及資料表使用SELECT 這個SQL指令，ALL為所有指令。
#授權用戶能給其它用戶授權
mysql -u root -p -e "GRANT privileges ON 資料庫名稱.資料表名稱 TO '使用者名稱'@'主機名稱' WITH GRANT OPTION;"

#修改密碼
mysql -u root -p -e "SET PASSWORD FOR '使用者名稱'@'主機名稱'=PASSWORD('新密碼');"
#刪除帳號
mysql -u root -p -e "DROP USER '使用者名稱'@'主機名稱';"
#更新帳號異動
mysqladmin -u root -p flush-privileges

基本觀念與mysql用戶端

登入帳號

mysql -u jhin -p
參數-u後面接使用者名稱，這邊jhin為我的使用者名稱，參數-p會提示你輸入密碼。

id is 55表示你在這個伺服器目前的id是55。

help指令

help
MariaDB用戶端的命令說明
help <item>
1.help contents
列出能用help呼叫的說明內容類別，例如 help Account Management
2.help CREATE USER
列出能用help呼叫的說明內容項目，例如 help Account Management
3.help 內容項目
列出能用help呼叫的說明內容

prompt(命令提示)

MariaDB [(none)]>
none代表指定的資料庫

資料庫

資料庫中所有的資料都存在資料表(table)中，SQL指令本身不區分大小寫，但在資料庫、資料表、欄位名稱是區分大小寫的。
提醒:SQL指令結束記得加 ;

MariaDB初始資料庫

information_schema (儲存伺服器本身相關資料)
mysql (儲存帳號相關資訊)
performance_schema (主要用於收集資料庫伺服器效能引數相關資料)
test (空的資料庫，用來測試)

基本的SQL指令

顯示目前支援的儲存格式()

SHOW ENGINES;

show-engines

查找資料庫列表

SHOW DATABASES;

show-databases

指定預設資料庫

USE 資料庫名稱;

use

新增資料庫

CREATE DATABASE 資料庫名稱;

create-database

刪除資料庫

DROP DATABASE 資料庫名稱;

drop-database

新增資料表

#新增資料表
CREATE TABLE 資料庫名稱.資料表名稱 (欄位名稱1 資料類型 , 欄位名稱2 資料類型,...);
補充說明:範例(CREATE TABLE Student.info (name varchar(10), join_date date))
#新增資料表(指定預設資料庫)
CREATE TABLE 資料表名稱 (欄位名稱1 資料類型 , 欄位名稱2 資料類型,...);
補充說明:

create-table
MariaDB的資料類型可以參考:
https://www.yiibai.com/mariadb/mariadb-data-types.html
http://n.sfs.tw/content/index/10266
https://ithelp.ithome.com.tw/articles/10203456?sc=iThelpR
http://www.codedata.com.tw/database/mysql-tutorial-8-storage-engine-datatype/

刪除資料表

#刪除資料表
DROP TABLE 資料庫名稱.資料表名稱;
#刪除資料表(指定預設資料庫)
DROP TABLE 資料表名稱;
#刪除資料表(指定預設資料庫不用加資料庫名稱)
DELETE FROM 資料庫名稱.資料表名稱;

drop-table
delete

查詢資料表

#查詢資料庫中的所有資料表(指定預設資料庫不用加 FROM 資料庫名稱)
SHOW TABLES FROM 資料庫名稱;
#顯示某個資料表的欄位設定(指定預設資料庫不用加 FROM 資料庫名稱)
SHOW COLUMNS FROM 資料表名稱 FROM 資料庫名稱;
補充說明:可以搭配LIKE查詢特定欄位
#顯示某個資料表的索引欄位設定(指定預設資料庫不用加 FROM 資料庫名稱)
SHOW INDEX FROM 資料表名稱 FROM 資料庫名稱;
#查詢資料表(指定預設資料庫不用加資料庫名稱)
DESC 資料庫名稱.資料表名稱;
#顯示資料表欄位設定
DESCRIBE 資料庫名稱.資料表名稱;

show-tables
show-columns
show-index
describe

插入資料

#插入資料(指定預設資料庫不用加資料庫名稱)
INSERT INTO 資料庫名稱.資料表名稱 VALUES (值1,值2,...) , (值1,值2,...);
補充說明:多個以,隔開
INSERT INTO 資料庫名稱.資料表名稱 (欄位名稱1,欄位名稱2..) SELECT 欄位名稱1,欄位名稱2 FROM 資料庫名稱.資料表名稱;

insert

查詢資料

#查詢資料(指定預設資料庫不用加資料庫名稱)
SELECT * FROM 資料庫名稱.資料表名稱;
補充說明: * 代表所有資料，也可以換成欄位名稱
#條件查詢資料(指定預設資料庫不用加資料庫名稱)
SELECT * FROM 資料庫名稱.資料表名稱 WHERE 欄位名稱 = 值;
補充說明: = 也可以換成 (>=、<....)，例如(SELECT * FROM dataset.test WHERE feature2 >= 45;)
補充說明2:WHERE也可以搭配AND、OR一起使用
補充說明3: = 值也可以為IS NULL或IS NOT NULL

select
MariaDB操作符可以參考:
https://www.yiibai.com/mariadb/mariadb-comparison-operator.html
NULL可以參考:
https://www.kaifaxueyuan.com/database/mariadb/mariadb-null-values.html
MariaDB子句可以參考:
https://www.yiibai.com/mariadb/mariadb-where.html
https://www.kaifaxueyuan.com/database/mariadb/mariadb-where-clause.html

修改資料表

#修改資料表存儲格式
ALTER TABLE 資料庫名稱.資料表名稱 ENGINE = 儲存格式;
#修改資料表名稱
ALTER TABLE 資料庫名稱.資料表名稱 RENAME 資料庫名稱.資料表名稱;

#新增欄位(指定預設資料庫不用加資料庫名稱)
ALTER TABLE 資料庫名稱.資料表名稱 ADD 欄位名稱 資料類型;
#新增欄位指定位置(指定預設資料庫不用加資料庫名稱)
ALTER TABLE 資料庫名稱.資料表名稱 ADD COLUMN 欄位名稱1 資料類型 AFTER 原有欄位名稱1 , ADD COLUMN 欄位名稱2 資料類型 AFTER 原有欄位名稱2;
補充說明:將欄位名稱1新增在原有欄位名稱1之後，若要放在前面則使用FIRST取代AFTER，新增多個一樣可以用,隔開

#刪除欄位
ALTER TABLE 資料庫名稱.資料表名稱 DROP COLUMN 欄位名稱;
#刪除資料
DELETE FROM 資料庫名稱.資料表名稱 WHERE 欄位名稱 = 值;
補充說明:刪除欄位名稱中 = 值的資料

#修改欄位資料類型(指定預設資料庫不用加資料庫名稱)
ALTER TABLE 資料庫名稱.資料表名稱 MODIFY 欄位名稱 資料類型;
補充說明:這邊也可以使用AFTER或FIRST
#修改欄位資料名稱(指定預設資料庫不用加資料庫名稱)
ALTER TABLE 資料庫名稱.資料表名稱 CHANGE 欄位名稱 新欄位名稱 資料類型;
補充說明:這邊也可以使用AFTER或FIRST

#一個條件下修改欄位值
UPDATE 資料庫名稱.資料表名稱 SET 欄位名稱1 = 值1 WHERE 欄位名稱2 = 值2;
補充說明:將欄位名稱1的值改成值1，範圍是欄位名稱2 = 值2的資料
#多個條件下修改多個欄位值
UPDATE 資料庫名稱.資料表名稱 SET 欄位名稱1 = 值1 , 欄位名稱2 = 值2 WHERE 欄位名稱3 = 值3 AND 欄位名稱4 = 值4;
補充說明:將欄位名稱1的值改成值1，欄位名稱2改成值2，範圍是欄位名稱3 = 值3且欄位名稱4 = 值4的資料

alter-table
update