Create
Read
Update
Delete
// 顯示database
show databases;
// 新增database
create database <name>;
// 刪除database
drop databases <name>;
// 使用database
use <databases name>;
// 查詢database
select database();
// 顯示目前的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,要設定最大長度
eid | birth_date | first_name | last_name | gender | hired_date |
---|---|---|---|---|---|
INT | DATE | VARCHAR(20) | VARCHAR(20) | ENUM('M','F') | DATE |
123 | 20180101 | Ju | Dave | M | 20240324 |
// 創建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;
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