MariaDB & SQL

認識資料庫系統

資料庫 (DataBase, DB) 是一組相關資料的集合，其資料之間可能具有某些關聯，允許使用者從不同的權限層級來加以存取，例如：進銷存系統、會計資訊系統、企業資源規劃系統等。操作與管理資料庫的軟體稱為資料庫管理系統 (DataBase Management System, DBMS)，例如：MariaDB、MySQL、SQL Server、Oracle、Sybase、Informix、PostgreSQL、MS Access等資料庫。 透過 DBMS 可以對資料進行定義、建立、處理與共享，其中定義是指定資料類型、結構及相關限制，建立是輸入並儲存資料，處理是包括新增、更新、查詢、刪除等動作，共享是讓多個使用者同時存取資料庫。

MariaDB 資料庫應用與管理：

• MariaDB 資料庫與帳號管理

開源的 MariaDB 資料庫是關連式資料庫，其資料庫管理系統內有許多個資料庫，每一個資料庫有一個或多個資料表所組成，每一個資料表是由一筆或多筆記錄所組成，紀錄必須是由多個欄位所組成，欄位必須設定資料型態以後方能儲存資料。 帳號管理：MariaDB 帳密設定。

資料庫：SQL 指令

• 建立資料庫

create database 資料庫名稱;

create database hrebc DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

• 列出資料庫編碼

show create database hrebc;

• 列出所有資料庫

show databases;

• 修改資料庫編碼

ALTER DATABASE hrebc CHARACTER SET utf8 COLLATE utf8_general_ci;

• 使用資料庫

use 資料庫名稱;

use hrebc;

• 刪除資料庫

drop database 資料庫名稱;

drop database hrebc;

資料表：SQL 指令

常用資料庫資料型態

1. INT (整數) 2. CHAR (1~255字元) 3. VARCHAR (不超過255字元) 4. TEXT (字串最多65535字元)

• 建立資料表

CREATE TABLE '資料表名稱' (名稱1 類型1 AI PK, 名稱2 類型2, 名稱3 類型3, ...);

create table hrebc15 (sid integer auto_increment primary key, eid char(30), name char(30), department char(30), position char(30), Tel char(20), ext char(10), mobile char(20), mail_addr char(60), line char(20), web_addr char(60), bkg_addr char(60)); // 對照目前的資料庫編碼：例如 utf8_unicode_ci

create table hrebc15 (sid integer auto_increment primary key, eid char(30), name char(30), department char(30), position char(30), Tel char(20), ext char(10), mobile char(20), mail_addr char(60), line char(20), web_addr char(60), bkg_addr char(60)) CHARACTER SET = utf8; // 指定資料表編碼：例如 utf8_general_ci

• 修改表格名

ALTER TABLE `原表格名` RENAME TO `新表格名`;

ALTER TABLE `hrebc15` RENAME TO `hrebc9`;

• 列出資料表欄位資訊

describe 資料表名稱; describe hrebc15;

• 新增資料表欄位

alter table '資料表名稱' add column '欄位名稱' '資料型態';

ALTER TABLE `hrebc15` ADD `test` INT(30) NULL DEFAULT NULL AFTER `bkg_addr`;

• 修改資料表欄位

alter table 資料表名稱 change column 原來欄位名稱 新欄位名稱資料型態;

ALTER TABLE `hrebc15` CHANGE COLUMN `line` `line_at` INT(20) NOT NULL;

ALTER TABLE `hrebc15` CHANGE COLUMN `line_at` `line` CHAR(20) DEFAULT NULL;

• 刪除資料表

drop table 資料表名稱; drop table hrebc15;

• 清空資料表

只清除資料並保留結構、欄位、索引 and so on.

truncate table 資料表名稱; truncate table hrebc15;

• 插入欄位資料(增加資料)

insert into 資料表名稱(欄位1,欄位2,欄位3,欄位4, ...... 欄位N) values('值1','值2','值3','值4', ...... '值N');

insert into hrebc15 (name) values ('Blessir');

insert into hrebc15 (eid,name,department,position,Tel,ext,mobile,mail_addr,line,web_addr,bkg_addr) values('104481001','Bless01','資訊部','經理','03-123456','168','0937130xxx','chuang???@gmail.com','Line168','www.google.com.tw','no');

• 更新欄位資料(修改資料)

update 資料表名稱 set 欄位1='值1',欄位2='值2',欄位3='值3',... 欄位N='值N' where 條件式;

update hrebc15 set name='Chuan-Fu' where name='Blessir';

• 查詢單一欄位資料

select 欄位名 from 資料表名稱;

select name from hrebc15;

• 查詢多個欄位資料

select 欄位名, 欄位名, 欄位名 from 資料表名稱;

select sid, name from hrebc15;

• 查詢欄位資料的唯一值(重複值只列一次)

select distinct name from hrebc15;

• 查詢所有資料

select * from 資料表名稱;

select * from hrebc15;

• 條件式查詢 and

select * from 資料表名稱 where 條件式1 and 條件式2;

select * from hrebc15 where sid=2 and name='Bless02';

• 條件式查詢 or

select * from 資料表名稱 where 條件式1 or 條件式2;

select * from hrebc15 where sid=2 or name='Chuan-Fu';

• 查詢某一範圍 between

select * from 資料表名稱 where 欄位名 between 值1 and 值2;

select * from hrebc15 where sid between 2 and 3;

• 查詢空值欄位的資料, 查詢不是空值欄位的資料

select * from 資料表名稱 where 欄位名 is null;

select * from hrebc15 where eid is null;

select * from 資料表名稱 where !isnull('欄位名');

select * from hrebc15 where !isnull('eid');

• 查詢特定筆數資料

select * from hrebc15 limit 2, 3; (第2筆資料開始選取3筆資料：第3、4、5筆)

• 查詢結果遞增排序與遞減排序

select * from 資料表名稱 order by 欄位名;

select * from hrebc15 order by name;

select * from 資料表名稱 order by 欄位名 desc ;

select * from hrebc15 order by name desc;

• 條件式查詢（=, <, >, !=）

select * from 資料表名稱 where 條件式;

select * from hrebc15 where name='Bless03';

select distinct position from hrebc15 where substr(name,1,5)='Bless'; (重複值只列一次)

• 查詢比對字串列出單一欄位

select 欄位名 from 資料表名稱 where 欄位名 like '字串';

select * from hrebc15 where name like 'Bless';

• 查詢比對字串列出所有欄位

select 欄位名 from 資料表名稱 where 欄位名 like '%字串%';

select * from hrebc15 where name like '%less';

select * from hrebc15 where name like 'Ble%';

select * from hrebc15 where name like '%le%';

• 刪除條件值資料

delete from 資料表名稱 where 條件式;

delete from hrebc15 where sid=4;

delete from hrebc15 where sid=2 and name='Bless02';

• 比對刪除條件值資料

delete from 資料表名稱 where 欄位名 like '%字串%';

delete from hrebc15 where name like '%le%';