CRUD

Create
Read
Update
Delete

Database CLI

// 顯示database
show databases;

// 新增database
create database <name>;

// 刪除database
drop databases <name>;

// 使用database
use <databases name>;

// 查詢database
select database();

• 要打分號系統才知道要結束
• 習慣上把關鍵字做大寫

Table CLI

// 顯示目前的table
show tables;

// [等效] 顯示欄位
show columns from <table_name>
desc <table_name>

// 刪除table
drop table <table_name>

// 新增Table
CREATE TABLE persion
(
    name VARCHAR(20),
    phone VARCHAR(20),
    age INT
);

// 預覽Table
desc persion;

數據類型

INT 儲存數字
VARCHAR 是長度可變的string，要設定最大長度

// 創建employees表
CREATE TABLE employees(
    eid INT, 
    birth_date DATE,
    first_name VARCHAR(20),
    last_name varchar(20),
    gender ENUM('M','F'),
    hired_date Date
);

// 創建employees表/ 加入必填欄位NOT NULL
CREATE TABLE employees02(
    eid INT, 
    birth_date DATE,
    first_name VARCHAR(20) NOT NULL,
    last_name varchar(20) NOT NULL,
    gender ENUM('M','F'),
    hired_date Date
);

// 創建employees表/ 加入default值
CREATE TABLE employees03(
    eid INT, 
    birth_date DATE,
    first_name VARCHAR(20),
    last_name varchar(20),
    gender ENUM('M','F') DEFAULT 'M',
    hired_date Date
);

// 創建employees表/ 建立uuid的限制/ PRIMARY KEY
CREATE TABLE employees04(
    eid INT, 
    birth_date DATE,
    first_name VARCHAR(20),
    last_name varchar(20),
    gender ENUM('M','F'),
    hired_date Date,
    PRIMARY KEY (eid)
);

// 創建employees表/ 建立uuid的限制/ PRIMARY KEY 和 UNIQUE的差別
create table employees06(a INT PRIMARY KEY, b VARCHAR(0) UNIQUE);
- PRIMARY KEY不允許空值
- UNIQUE 允許空值

// 建立多筆PRIMARY KEY 錯誤寫法
create table employees07(a INT PRIMARY KEY, b INT PRIMARY KEY, c INT);
// 建立多筆PRIMARY KEY 正確寫法/ 且這樣寫法是(a, b)為一組當作uuid
// 實際案例就是用戶id和商品id為一組唯一碼，來綁定使用者只能買一件商品
create table employees07(a INT, b INT, c INT, PRIMARY KEY(a,b));

// 創建employees表/ 自動給予id
CREATE TABLE employees05(
    eid INT AUTO_INCREMENT, 
    birth_date DATE,
    first_name VARCHAR(20),
    last_name varchar(20),
    gender ENUM('M','F'),
    hired_date Date,
    PRIMARY KEY (eid)
);

// AUTO_INCREMENT混用 UNIQE 及 PRIMARY KEY，都可以使用
create table user2(id INT AUTO_INCREMENT UNIQUE, username VARCHAR(32)); 
create table user3(id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(32));

// id會自動給流水號，PRIMARY KEY以id+username聯合為一組
create table user1(id INT AUTO_INCREMENT, username VARCHAR(32), PRIMARY KEY(id, username));

// 如果您想username不重複，username設為UNIQUE，id設為PRIMARY KEY
create table user2(id INT AUTO_INCREMENT, username VARCHAR(32) UNIQUE, PRIMARY KEY(id));

// 完整範例/ 員工資料表/ 寫法ㄧ
CREATE TABLE employees08(
    eid INT AUTO_INCREMENT, 
    birth_date DATE NOT NULL,
    first_name VARCHAR(20) NOT NULL,
    last_name varchar(20) NOT NULL,
    gender ENUM('M','F') NOT NULL,
    hired_date Date NOT NULL DEFAULT '2000-01-01',
    PRIMARY KEY(eid)
);

// 完整範例/ 員工資料表／寫法二
CREATE TABLE employees08(
    eid INT AUTO_INCREMENT PRIMARY KEY, 
    birth_date DATE NOT NULL,
    first_name VARCHAR(20) NOT NULL,
    last_name varchar(20) NOT NULL,
    gender ENUM('M','F') NOT NULL,
    hired_date Date NOT NULL DEFAULT '2000-01-01'
);

// 插入數據
insert into <table_name>(column1_name, column2_name) values (column1_value, column2_value)

// 插入單筆數據-範例
insert into employees(eid, birth_date, first_name, last_name, gender, hired_date) values ("001", "20230101", "Ju", "Dave1", "M", "20240301");

// 插入單筆數據-範例-可不照順序
insert into employees(birth_date, first_name, last_name, gender, hired_date, eid) values ("20230101", "Ju", "Dave1", "M", "20240301", "001");

// 插入多筆數據
insert into employees(eid, birth_date, first_name, last_name, gender, hired_date) values ("001", "20230101", "Ju", "Dave1", "M", "20240301"), ("002","20230102","Ju","Dave2", "M", "20240302");

// 查詢table裡面的數據-SELECT

// 顯示每一條資料
SELECT *from table_name;

// 顯示每一條資料-範例
SELECT *from employees; 

// 顯示特定column
SELECT column_name from table_name;

// 顯示特定column-範例
SELECT eid from employees;

// 顯示多筆特定column-範例
SELECT eid, first_name from employees;

// 將表的coloum，取別名後進行查詢
SELECT first_name as fname, last_name lname from employee;

// 過濾
SELECT *from <table_name> where <table_column>="search key";

// 過濾-範例
SELECT *from employee where title="Database Administrator";

// 過濾 AND
SELECT *from employee where title="Database Administrator" and salary=6000;

// 過濾 OR
SELECT *from employee where title="Database Administrator" or salary=7200;

// 過濾 NOT
SELECT * FROM employee WHERE NOT title = "Database Administrator";

// 過濾 NOT + AND
SELECT * FROM employee WHERE title = "Database Administrator" AND salary != 6000;

// 全部的值更新成一個值(較危險)
UPDATE employee set salary=10000

// 符合條件的數據，更新成一個值
UPDATE employee set salary=10000 where title = "Database Administrator";

// 符合條件的數據，同時更新兩個值
UPDATE employee set salary=15000, notes="updated" where title = "Database Administrator";

// 刪除數據
delete from <table_name> where title="<content>";

// 刪除數據-範例
delete from employee where title="software engineer";

// 刪除整張table裡面的資料
delete from from <table_name>;

// 刪除整張table裡面的資料-範例
delete from from employee;

資料編輯

CONCAT

基本概念

select CONCAT("A","B");
// 結果會是"AB"

應用

// CONCAT
select CONCAT(<table_column>,<table_column>) from <table>;

// CONCAT-範例1
select CONCAT(first_name,last_name) from employee;

// CONCAT-範例2/column name取代號
select CONCAT(first_name," ",last_name) as full_name from employee;

// CONCAT-範例3
select CONCAT(first_name," ",last_name, " is ", title) as description from employee;

// CONCAT_WS 每個變數中間加入符號
select CONCAT_WS("<符號>",<column_name>,<column_name>, <column_name>) as description from employee;

// CONCAT_WS-範例
select CONCAT_WS("-",first_name,last_name, title) as description from employee;
// 結果:Harley-Gilbert-Software Architect

參考資料 https://dev.mysql.com/doc/refman/8.3/en/data-types.html