---
title: Mysql
---
# 1、資料控制語言
> 新增刪除使用者權限
> GRANT 授予、REVOKE 收回
```
GRANT ALL
ON .TO 'admin'@'localhost'
IDENTIFIED BY '123456'
WITH GRANT OPTION;
```
# 2、資料定義語言
> 用來定義資料庫的物件
> 資料庫、資料表、使用者、視圖等。
> CREATE、DROP、SHOW
# 3、資料操作語言
> 對資料庫和資料表進行讀取、寫入、修改和刪除操作。
> SELECT、INSERT、UPDATE、DELETE
# 4、在 SQL 中英文大小寫並無區分
`select * from first;`
`SELECT * FROM first;`
# 5、空白字元的個數並不會對敘述句造成影響
```
select * from first;
select * from
first;
```
# 6、字串型態的常數必須要用「'」單引號包住
```
insert into first
values('maggie');
select 'maggie';
select 'maggie;
-> ';
```
# 7、註解
```
SELECT #註解
select ; #註解
select / 註解
aaa*/ from first;
```
# 8、找出 int 型態的最大最小值:
> https://mariadb.com/kb/en/data-types-numeric-data-types/
```
create table int_test (
i1 tinyint,
i2 smallint,
i3 mediumint,
i4 int,
i5 bigint);
insert into int_test
value(-128,-32768,-8388608,-2147483648,-9223372036854775808),
(127,32767,8388607,2147483647,9223372036854775807);
```
# 9、無符號數 unsigned:
> 可以表示特定類型規定範圍內的整數（包括負數），而無符號數只能表示 非負數（0 及 正數）。
```
create table int_test_2(
i1 tinyint unsigned
);
insert into int_test_2
value(-1),
(155);
```
# 10、浮點數: float 7 個有效位數、double 15 個有效位數
> 有給參數的話，第一個參數是總長數，第二個參數是小數位數長度(5 位數)
```
create table float_test(
f1 float,
f2 float(10,5),
f3 double,
f4 double(10,5)
);
insert into float_testvalue
(1.324,1.32400,345.111111,345.111111),
(1.324,100000.00000,3456.7891213,99999.99999);
```
# 11、短字串: char、varchar
```
create table string_test(
c1 char(10),
c2 varchar(10)
);
insert into string_test
value('maggie','gebi'),
('abcdefghijk','abcde222222222222');
```
# 12、長字串: text(65,535byte)
> https://mariadb.com/kb/en/data-types/
```
create table text_test(t1 text);
insert into text_test
value('A TEXT column with a maximum length of 65,535
(216 - 1) characters. ');
```
# 13、日期時間: date(3byte)、time(3byte)、datetime(8byte)、timestamp(4byte)
```
create table date_time_test(
d1 date,
d2 time,
d3 datetime,
d4 timestamp
);
```
> 未設定 timestamp，預設為 CURRENT_TIMESTAMP(當下時間)
```
insert into date_time_test(d1,d2,d3,d4)
value('2025-01-08','12:00:00','2025-01-08
11:04:05','2025-01-08 11:04:05');
```
---
# 14、資料控制語言
> 新增刪除使用者權限
> GRANT 授予、REVOKE 收回
`GRANT ALL ON . TO 'admin'@'localhost' IDENTIFIED BY '123456' WITH GRANT OPTION;`
# 15、建立權限
```
GRANT privileges
ON object
TO user;
identifier by 'password';
```
```
GRANT select ON mydb.atable TO 'peter'@'localhost' IDENTIFIED BY '1234567';
```
## 開新的資料庫 mydb 建立 A、B 資料表
```
create database mydb;
create table atable(
id int,
game_skill_name varchar(20),
level int
);
create table btable(
id int,
english int,
math int,
chinese int
);
insert into atable
values(1, 'STR', 7),
(2, 'DEX', 5),
(3, 'WIS', 10),
(4, 'INT', 10),
(5, 'LUK', 4),
(6, 'AGI', 8);
insert into btable
values(1, 80, 90, 100),
(2, 70, 80, 90),
(3, 60, 70, 80),
(4, 50, 60, 70),
(5, 40, 50, 60);
```
## 授予 peter A 資料表權限
`GRANT select ON mydb.atable TO 'peter'@'localhost' IDENTIFIED BY '1234567';`
### 其上述的指令是以下兩個指令的組合而成。
`use mydb;`
`1-create user'peter1'@'localhost' IDENTIFIED by '1234567';`
`2-GRANT select on mydb.btable to 'peter1'@'localhost';`
## 開 peter 的帳號，連進去 mydb 然後進行操作
`mysql -upeter -p1234567`
`show databases;`
`use mydb`
## 確認無法使用 B 資料表
`show tables;`
`select * from atable;`
## 授予 peter A 資料表權限
`1-create user'peter'@'localhost' IDENTIFIED by '1234567';`
`2-GRANT select on mydb.atable to 'peter'@'localhost';`
## 開 peter 的帳號，連進去 mydb 然後進行操作
`mysql -upeter -p1234567`
## 確認無法使用 B 資料表
`show databases;`
`use mydb;`
`show tables;`
`select * from atable;`
---
> grant 建立使用者+權限
> root 最高權限
> select 是特定
> all 是全部
> create 建立使用者
# 16、授予某個使用者對某個資料庫的全部權限
```
GRANT ALL PRIVILEGES
ON mydb.*
TO 'myuser'@'localhost';
```
# 17、開啟多個權限 select,insert
```
GRANT select,insert on 特定資料庫.資料表
from '某user'@'localhost';
```
# 18、開啟單個資料表
`on 特定資料庫.特定資料表`
# 19、開啟全部資料庫
`on *.*`
# 20、開啟多個資料表,要獨立開
```
on 特定資料庫.特定資料表1
on 特定資料庫.特定資料表2
```
## 1、如果要開啟 mysql 資料庫的所有資料表給 jack2
```
GRANT SELECT
ON mysql.*
TO 'jack2'@'localhost'
IDENTIFIED by '1234567';
```
## 2、開啟所有資料庫的搜尋加寫入權限給 jack3
```
GRANT SELECT,insert
ON *.*
TO 'jack3'@'localhost'
IDENTIFIED by '1234567';
```
## 3、如果要開啟 2 個資料表搜尋權限給 jack1
```
GRANT SELECT
ON mydb.atable
TO 'jack1'@'localhost'
```
```
IDENTIFIED by '1234567';
GRANT SELECT
ON my
TO 'jack1'@'localhost'
```
# 21、建立使用者
```
create user 'john1'@'localhost'
IDENTIFIED by'1234567'
```
> 重新命名使用者
> `RENAME USER 'jack1'@'localhost' TO 'jack2'@'localhost';`
# 22、授予權限
```
GRANT SELECT
ON mydb.btable
TO 'john1'@'localhost'
```
# 23、撤銷某 user 的權限
```
REVOKE SELECT
OF *.*
from 'jack2'@'localhost';
```
> 刪除權限會立即生效
> 但使用者還是存在
# 24、刪除使用者
`DROP USER 'jack3'@'localhost';`
> 刪除使用者 連線並不會受到影響直到下次登入
> 可以針對不能範圍的權限進行刪除
> 不可以針對不同的物件，進行權限刪除
> 刪除使用者所有資料表
```
DROP USER 'jack1'@'localhost' CASCADE
```
# 25、建立遠端使用者
`CREATE USER 'john2'@'%' IDENTIFIED by'1234567';`
# 26、授予權限
`GRANT SELECT on mydb.atable to 'jack2'@'%';`
```
mysql -ujohn2 -p1234567 #本地端連線ok
mysql -ujohn2 -p1234567 -hlocalhost #本地端連線ok
mysql -ujohn2 -p1234567 -h172.18.2.251 #遠端ok
```
## 1、來源-我建立同事授權我 sql 資料庫
`CREATE USER 'wendy'@'XXX.XXX.XX.XX'
IDENTIFIED by'1234567';`
`GRANT SELECT on *.* to 'wendy'@'XXX.XXX.XX.XX';`
`mysql -umaggie -p1111 -hXXX.XXX.XX.XX -P6603 我連同學的`
## 2、目的地-同事連我填同事 ip 有設定 port 就要改 port
`mysql -uwendy -p1234567 -hXXX.XXX.XX.XXX -P6603 我連同學的`
# 27、查看自己的權限
`show grants;`
# 28、查看別人的權限(僅限 root 或是帳號所有權限者)
`show grants for 'wendy'@'192.168.10.47';`
# 29、重設使用者密碼
`SET PASSWORD FOR 'root'@'localhost' = PASSWORD('1234');`
# 30、修改 root 密碼
```
mysqladmin -uroot password(未設定密碼時)
mysqladmin -uroot password -p原先密碼(有設定密碼時)
New PASSWORD:*****
confirm new password:****
```
# 31、修改一般帳號密碼
`alter user 'peter'@'localhost' IDENTIFIED by '1234'`
# 32、查詢連線
`show processlist;`
# 33、刪除帳號
`drop user 'peter'@'localhost';`
`drop user 'john2'@'%'`
`drop user 'wendy'@'192.168.10.47'`
> (如果正在連線要刪除連線才會斷開只刪除帳號就沒用)
# 34、刪除連線
`kill 號碼; (由proccesslist中查看到)`
---
# 35、建立資料庫
`CREATE Database newdb;`
# 36、刪除資料庫
`Drop Database newdb;`
# 37、使用資料庫
`use newdb;`
# 38、建立資料表
```
create table student(
id int unsigned,
name varchar(40),
heigh tinyint unsigned,
weight tinyint unsigned
);
```
# 39、查看資料表欄位資訊
`describe student;`
# 40、刪除資料表
`drop table student;`
`drop table if exists student; (如果有student才刪)`
# 41、NOT NULL 須有值,不能為空
> 不能為空值意思是「必填」，在插入資料時，該欄位不能是空的，像平常在註冊網站會員時，就可以看到表單會規定帳號、密碼、聯絡信箱和地址必填等等，就是在說這些資料必須 NOT NULL。
## 1、實作
```
drop table if exists students;
create table students(
id int unsigned not null,
name varchar(40),
heigh tinyint unsigned,
weight tinyint unsigned
);
describe student;
insert into students(name)values('jack');
insert into students(id,name)values(3,'jack');
SELECT * from students;
```
# 42、UNIQUE 唯一值
> 這個是說這個欄位的值必須是唯一的，在生活中我們常常會規定某些資料「不能重複」，如果重複了會造成一些問題，例如說身分證字號、抽獎流水號、電話號碼、車牌等等，這些都是用想的就知道絕對不能重複的資料，那這些資料就具有 UNIQUE 唯一值的特性。
## 1、實作
```
drop table if exists students;
create table students(
id int unsigned not null unique,
name varchar(40),
heigh tinyint unsigned,
weight tinyint unsigned
);
describe students;
insert into students(id)values(3); (Duplicate重複 entry 3)
```
# 43、DEFAULT 預設值
> DEFAULT 限制用來設定欄位的預設值。
> 當你在 INSERT 資料時若該欄位沒指定值則會採用預設值。
```
drop table if exists students;
create table students(
id int unsigned not null unique,
name varchar(40),
heigh tinyint unsigned not null default 160,
weight tinyint unsigned not null default 60
);
describe students;
insert into students(heigh,weight)values(111,80);
insert into students(id,heigh)values(1,160);
```
# 44、增減欄位或變更欄位屬性
## 1、ADD COLUMN 新增欄位
`ALTER TABLE students ADD COLUMN age tinyint unsigned;`
> 在 name 後面新增 nickname;AFTER 須指定在哪個欄位
> `ALTER TABLE students ADD COLUMN nickname varchar(20) AFTER name;`
> FIRST 就新增在最前面;無須指定在哪個欄位前面
> `ALTER TABLE students ADD COLUMN id0 int FIRST;`
## 2、DROP COLUMN 刪除欄位
`ALTER TABLE students DROP COLUMN id0;`
## 3、MODIFY COLUMN 變更欄位
`ALTER TABLE students MODIFY COLUMN age INT(3);`
# 45、PRIMARY KEY 主要索引
> ; 唯一性、不可為空、可用來建立關聯
## 1、單一索引
```
drop table if exists students;
create table students(
id int unsigned PRIMARY KEY,
name varchar(40),
heigh tinyint unsigned,
weight tinyint unsigned
);
describe students;
insert into students(id)
values(1); ( Duplicate entry '0' for key 'PRIMARY')
```
## 2、雙主索引
```
drop table if exists students;
create table students(
id int unsigned,
name varchar(40),
heigh tinyint unsigned,
weight tinyint unsigned,
PRIMARY KEY(id,name)
);
describe students;
insert into students(name,heigh)
values('s',180);(Duplicate entry '0-s' for key 'PRIMARY')
```
# 46、INDEX 次要索引
> 用於提高查詢速度
> `CREATE INDEX index_name(自己取) ON 某_table (column的值);`
## 1、單一索引
`CREATE INDEX index_name(自己取)ON 某_table (column的值);`
`CREATE INDEX idx_students_height ON students (heigh);`
`CREATE INDEX index_heigh ON students (heigh);`
## 2、在多列上建立索引
`CREATE INDEX index_name(自己取)ON 某_table (column的值);`
`CREATE INDEX idx_students_t ON students (heigh,weight);`
## 3、建立唯一值索引
`CREATE UNIQUE INDEX index_name(自己取)ON 某_table (column的值);`
`CREATE UNIQUE INDEX idx_students_t1 ON students (heigh,weight);`
## 4、刪除索引
`ALTER TABLE 某_table DROP INDEX index_name(自己取);`
`ALTER TABLE students DROP INDEX idx_students_t1;`
`ALTER TABLE students DROP INDEX index_heigh;`
## 5、顯示索引
`show indexes from 某_table `
`show indexes from students;`
## 6、AUTO_INCREMENT 自動索引
```
CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT,
gender ENUM('M', 'F'),
department VARCHAR(50),
salary DECIMAL(10, 2),
hire_date DATE
);
insert into employee (name)
values ('jack'),
('jack'),
('jack');
insert into employee (id,name)
values (99,'jack');
insert into employee (name)
values ('jack');
```
---
# 47、資料匯入 (mysql 執行)
> 欄位分隔符號：FIELDS TERMINATED BY
> 欄位刮符號：ENCLOSED BY
> 換行符號：LINES TERMINATED BY
```
LOAD DATA LOCAL INFILE 匯入檔案
INTO TABLE 表格名稱
FIELDS TERMINATED BY','
ENCLOSED BY '"'
LINES TERMINATED BY'\r\n';
```
## 1、實作
```
create table test_import(
c1 int,
c2 int,
c3 int
);
describe test_import;
LOAD DATA LOCAL INFILE "D:/Mysql/text.txt"
INTO TABLE test_import
FIELDS TERMINATED BY','
LINES TERMINATED BY'\r\n';
select * from test_import;
; \t 縮排
LOAD DATA LOCAL INFILE "D:/Mysql/123.csv"
INTO TABLE test_import
FIELDS TERMINATED BY'\t'
LINES TERMINATED BY'\r\n';
drop table test_import;
```
## 2、實作
```
create table test_bathroom(
itemname varchar(255),
category varchar(255),
year int,
itemunit varchar(255),
itemvalue int
);
describe test_bathroom;
LOAD DATA LOCAL INFILE "D:/Mysql/stat_p_90.csv" INTO TABLE test_bathroom character set utf8
FIELDS TERMINATED BY','
LINES TERMINATED BY'\r\n';
select * from test_bathroom;
drop table test_bathroom;
```
# 48、大型資料匯入 (windows 執行)
> mysqlimport -u root -p
> --local # 本地端
> --fields-terminated-by="," #欄位分割符號
> --lines-terminated-by="\r\n" # 換行
> 資料庫名稱 匯入檔案
## 1、實作
```
use mydb;
create table test2_import(
c1 int,
c2 int,
c3 int
);
; mysqlimport 查import指令
; 需要將檔名改成資料表的名稱(須相同)
mysqlimport -u root -p1234 --local --fields-terminated-by="," --lines-terminated-by="\r\n" mydb "D:/Mysql/test2_import.txt"
mysqlimport -u root -p1234 --local --fields-terminated-by="\t" --lines-terminated-by="\r\n" mydb "D:/Mysql/test2_import.csv"
; 匯入 有 "的資料
mysqlimport -u root -p1234 --local --fields-terminated-by="," --fields-enclosed-by="\"" --lines-terminated-by="\r\n" mydb "D:/Mysql/test2_import.txt"
```
# 49、大型資料匯出 (mysql 執行)
> SELECT \* FROM 資料表
> INTO OUTFILE 檔案路徑
> FIELDS TERMINATED BY ','
> ENCLOSED BY '"' # 單 雙 單
> LINES TERMINATED BY '\r\n';
## 1、試著匯出"的 csv 檔案
```
SELECT * FROM test2_import INTO OUTFILE "D:/Mysql/測試匯出.csv"
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';
```
## 2、試著匯出縮排分隔的 txt 檔案
```
SELECT * FROM test2_import INTO OUTFILE "D:/Mysql/測試匯出.txt"
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n';
```
## 3、自己測試匯出中文檔案
```
SELECT * FROM test_bathroom INTO OUTFILE "D:/Mysql/測試匯出1.csv"
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';
```
# 50、透過 Mysql 將搜尋的結果導出至特定檔案 (windows 執行)
`mysql -uroot -p1234567 -D 資料庫 -e "查詢SQL" > 要放到哪個位置`
> show 出資料
> mysql -uroot -p1234567 -D 資料庫 -e "查詢 SQL"
## 1、匯出特定檔案
`mysql -uroot -p1234 -Dmydb -e "select * from test2_import" > D:/Mysql/測試匯出3.text`
## 2、show 出資料
`mysql -uroot -p1234 -Dmydb -e "select * from test2_import"`
# 51、建立 88mb 的金融資料表匯入： (使用 mysql 語法匯入)
## 1、實作
```
use mydb;
create table future_k1(
date date,
time time,
open_price float,
high_price float,
low_price float,
close_price float,
volume int
);
describe future_k1;
LOAD DATA LOCAL INFILE "D:/Mysql/future_k.csv"
INTO TABLE future_k1
FIELDS TERMINATED BY','
LINES TERMINATED BY'\r\n';
select * from future_k1;
```
> limit 限制輸出
> `select * from future_k1 limit 10; `
> 匯入檢驗
> 正確性檢驗 OK
> 容量占用率 88357kb -> 77824kb (省 12%)
> 搜尋效率檢查
> `select * from future_k1 where date = '2013-01-10';`
> 0.291 sec 耗時
## 2、加快效率
```
create table future_k2(
date date,
time time,
open_price smallint unsigned,
high_price smallint unsigned,
low_price smallint unsigned,
close_price smallint unsigned,
volume tinyint
);
LOAD DATA LOCAL INFILE "D:/Mysql/future_k.csv"
INTO TABLE future_k2
FIELDS TERMINATED BY','
LINES TERMINATED BY'\r\n';
```
> 匯入檢驗
> 正確性檢驗 OK
> 容量占用率 88357kb -> 77824kb(省 12%) -> 65536 (省 16%)
> 搜尋效率檢查
> `select * from future_k2 where date = '2013-01-10';`
> 0.262 sec 耗時
## 3、加上單一索引
```
CREATE INDEX date_index
ON future_k2 (date);
show indexes from future_k2;
```
> 匯入檢驗
> 正確性檢驗 OK
> 容量占用率 88357kb -> 77824kb(省 12%) -> 86016 (加上 index 索引檔案才變大)
> 搜尋效率檢查
> `select * from future_k2 where date = '2013-01-10';`
> 0.002 sec 耗時
## 4、加 primary key
```
create table future_k3(
date date,
time time,
open_price smallint unsigned,
high_price smallint unsigned,
low_price smallint unsigned,
close_price smallint unsigned,
volume tinyint,
primary key (date,time)
);
LOAD DATA LOCAL INFILE "D:/Mysql/future_k.csv"
INTO TABLE future_k3
FIELDS TERMINATED BY','
LINES TERMINATED BY'\r\n';
```
> 匯入檢驗
> 正確性檢驗 OK
> 容量占用率 88357kb -> 57344kb(省 35%)
> 搜尋效率檢查
> `select * from future_k3 where date = '2013-01-10';`
> 0.001 sec 耗時
---
# 52、查詢資料
`SELECT (column1...,column2) FROM (table_name);`
## 1、實作
`SELECT name,gender FROM employee WHERE name = 'David';`
> 合併欄位中重複資料
> `SELECT distinct name,age FROM employee;`
> 欄位使用別名
```
SELECT name,salary,age,salary/age KPI FROM employee;
SELECT name,salary,age,salary/age as KPI FROM employee;
```
> 查詢特定的值
> `SELECT name,gender FROM employee WHERE gender = 'F' or age > 40;`
> Where
> `SELECT * FROM employee Where gender = 'M';`
> ORDER BY 排序
> 未指定時為 ASC 正序
```
SELECT * FROM employee ORDER BY age; #asc
SELECT * FROM employee ORDER BY age DESC;
SELECT * FROM employee ORDER BY age DESC,salary ASC;
```
> 指定哪個欄位的值的那一行的 age 反序和 salary 正序
> `SELECT * FROM employee Where department='sales' ORDER BY age DESC,salary ASC;`
### 2、牛刀小試
> 搜尋出 future_k3 的 2016-01-10 的資料，並按照盤價由大到小排序
> `SELECT * From future_k3 WHERE date = '2013-01-10' ORDER BY close_price DESC;`
> 限制資料筆數
> 顯示 3 筆
> `SELECT * From future_k3 WHERE date = '2013-01-10' limit 3;` > `SELECT * From future_k3 WHERE date = '2013-01-10' limit 3,6;`
# 53、新增
## 1、插入資料-實作
> insert into table_name(column1...,column2) VALUES(value...)
```
use mydb;
CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT,
gender ENUM('M', 'F'),
department VARCHAR(50),
salary DECIMAL(10, 2),
hire_date DATE
);
insert into employee (name, age, gender, department, salary, hire_date)
VALUES('John', 28, 'M', 'Sales', 50000.00, '2022-03-15'),
('Mary', 35, 'F', 'Marketing', 65000.00, '2022-04-01'),
('David', 42, 'M', 'Engineering', 80000.00, '2022-02-15');
select * FROM employee;
```
## 2、插入搜尋後資料-實作 INSERT INTO+SELECT
> insert into 新建的資料表 (column) SELECT (value) FROM 要複製的資料表
```
CREATE TABLE employee_age (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT
);
insert into employee_age(name,age)
select name,age FROM employee;
select * FROM employee_age;
```
# 54、DELETE 刪除資料
> DELETE FROM （table_name） WHERE （搜尋條件）;
> 刪除特定資料
> `DELETE FROM employee where name= 'Mary';`
> 刪除所有資料
> `DELETE FROM employee;`
> 寫入資料 查看 id 是否繼續 increment
> `insert into employee (name, age, gender, department, salary, hire_date) VALUES('John', 28, 'M', 'Sales', 50000.00, '2022-03-15');`
> 顯示資料表的設立語法
> `show create table employee;`
> 清空格式化資料表(僅刪除資料表內容，但保留結構)
> `TRUNCATE employee;`
# 55、UPDATE 更新資料
> UPDATE table_name SET column1.. = value Where 特定條件;
## 1、實作
```
UPDATE employee SET gender = 'M'; 全性別變成M
UPDATE employee SET salary = 90000 Where name = 'David';
UPDATE employee SET age = age+3,salary =10000 Where name = 'David';
```
# 56、運算單元
> 運算符號
> `SELECT 1+1,1-1,1*1,10/3,10%3;`
> Null 空值的判斷
```INSERT INTO employee(name)value('jack');
SELECT * from employee WHERE age = null;
SELECT * from employee WHERE age <=> null; 太空運算符號<=>
SELECT * from employee WHERE age is null; 顯示null空值的欄位
SELECT * from employee WHERE age is not null; 顯示非null的欄位
```
---
# 57、IN 的判斷
> 將搜尋的值與指定集合內的值做確認
```
SELECT * FROM employee WHERE name = 'David' or name ='Mary';
SELECT * FROM employee WHERE name in ('David','Mary');
```
# 58、BETWEEN 的判斷
> 將兩個值內的範圍顯示出來
```
SELECT distinct time From future_k3;
SELECT * From future_k3
WHERE date = '2013-01-10' and time BETWEEN '09:00:00' and '10:00:00';
```
## 1、牛刀小試
> future_k3 請撈出 2015 以及 2013 年的數據,只要早上 8:45~13:45 的資料即可
```
SELECT * FROM future_k3;
WHERE year(date) in ('2013','2015') and time BETWEEN '08:45:00' and '13:45:00';
```
---
# 59、模糊搜尋
> LIKE 運算子：主要透過萬用字元『%』、『_』來進行字串的搜查
> 『%』：匹配多個字元的萬用字元
> 『_』：匹配一個字元的萬用字元
> 搜尋名字中以 m 開頭的員工
> `SELECT * From employee Where name like 'M%'; %代表0~N個字元`
`SELECT * From employee Where name like 'M__'; %代表1個字元`
## 1、牛刀小試
> 請搜尋出 d 結尾的名稱
> `SELECT * From employee Where name like '%d'; `
> 請搜尋出至少 5 個字母的名字 5~N 個字元
> `SELECT * From employee Where name like '_____%'; `
> 請搜尋出名稱裡面包含 a 的名字
> `SELECT * From employee Where name like '%a%';` > `select * from employee;`
# 60、正則表達式
> 一種表示文字模式的方法，通常用來進行模式匹配和搜尋
## 1、實作
```
CREATE TABLE member (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
join_date DATE,
id_number VARCHAR(10) NOT NULL,
email VARCHAR(50) NOT NULL,
url VARCHAR(100) NOT NULL
);
INSERT INTO member (username, password, join_date, id_number, email, url) VALUES
('john123', 'password123', '2022-01-01', 'A123456789', 'john123@gmail.com', 'https://www.example.com/john123'),
('mary456', 'password456', '2022-01-15', 'B234567890', 'mary456@gmail.com', 'https://www.example.com/mary456'),
('david789', 'password789', '2022-02-01', 'C345678901', 'david789@gmail.com', 'https://www.example.com/david789'),
('lisa111', 'password111', '2022-02-15', 'D456789012', 'lisa111@gmail.com', 'https://www.example.com/lisa111'),
('kevin222', 'password222', '2022-03-01', 'E567890123', 'kevin222@gmail.com', 'https://www.example.com/kevin222');
```
> ^ 匹配字符串的開始;使用 $ 匹配字符串的結尾
> 檢查身分證字號格式式否正確
> `SELECT * FROM member WHERE id_number REGEXP '^[A-Z][1-2][0-9]{8}$';`
> 使用 + 匹配前面的字符一次或多次
> `SELECT * FROM member WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@gmail.com$';`
# 61、函數應用(數值函數+字串)
## 1、實作
> TRUNCATE employee; 格式化清除資料,保留屬性
```
INSERT INTO employee (name, age, gender, department, salary, hire_date)
VALUES('John', 28, 'M', 'Sales', 50000.00, '2022-03-15'),
('Mary', 35, 'F', 'Marketing', 65000.00, '2022-04-01'),
('David', 42, 'M', 'Engineering', 80000.00, '2022-02-15'),
('Lisa', 29, 'F', 'Sales', 55000.00, '2022-05-01'),
('Kevin', 31, 'M', 'Marketing', 70000.00, '2022-03-15'),
('Emma', 27, 'F', 'Engineering', 75000.00, '2022-04-01'),
('Peter', 38, 'M', 'Sales', 60000.00, '2022-02-15'),
('Olivia', 33, 'F', 'Marketing', 85000.00, '2022-05-01');
```
## 2、AVG
> 求群組中數值的平均值
> 針對一個欄位去做統計(統計函數的使用)
> `SELECT AVG(salary) FROM employee;`
> 針對多個欄位去做算
> `SELECT (salary+age)/2 FROM employee;`
> 一般函數的使用
> `SELECT YEAR(hire_date) from employee;`
### 統計函數有哪些
> sum、avg、count、max、min
## 2、實作
`Select * from employee;`
`Select AVG(salary),SUM(salary)from employee;`
```
Select CONCAT('name:',name),
LOWER(name),
UPPER(name),
LENGTH(name) from employee;
```
# 62、函數應用(時間函數)
## 1、實作
```
Select NOW(),
Year(hire_date),
MONTH(hire_date),
DAY(hire_date),
hire_date,
ADDDATE(hire_date,INTERVAL 1 DAY),
SUBDATE(hire_date,INTERVAL 1 DAY) from employee;
```
---
# 63、群組化
> 群組化分兩個動作
> 資料分群
> 統計特定結果
```
SELECT
FROM
WHERE
ORDER BY
GROUP BY
LIMIT
```
> 預設回傳三個群組的第一筆資料
> `SELECT * FROM employee GROUP BY department;`
> 搜尋每個部門的平均薪資
```
SELECT department,
AVG(salary),
COUNT(salary)
FROM employee
GROUP BY department;
```
## 1、實作(GROUP BY)
> 將資料依照指定欄位的值進行分組，並對每個分組進行統計分析
> 算出不同的到職年份的職員 個年度的平均薪資
> `SELECT YEAR(hire_date),AVG(salary) From employee GROUP BY YEAR(hire_date);`
> 算出男性與女性的平均年齡
> `SELECT gender,AVG(age) FROM employee GROUP BY gender;`
> 算出不同部分性別的平均薪資
> `SELECT department,gender,AVG(age) FROM employee GROUP BY department,gender;`
## 2、實作(HAVING)
> HAVING 關鍵字可以在 GROUP BY 語句中使用，用於篩選分組後的結
> 果。與 WHERE 不同的是，HAVING 用於篩選聚合函數的結果，而
> WHERE 用於篩選原始資料列。
> 篩選群組化的數據
> `SELECT department,gender,AVG(age) FROM employee GROUP BY department,gender having AVG(salary)>70000;`
> 篩選群組化以前的數據
> `SELECT department,gender,AVG(age) FROM employee WHERE salary > 70000 GROUP BY department,gender;`
> 算出不同部分性別的平均薪資 (GROUP_CONCAT)
> `SELECT department,gender,GROUP_CONCAT(name),AVG(salary) FROM employee GROUP BY department,gender;`
---
# 64、關聯式資料庫
## 1、自我練習實作(創建三個資料表(客戶資料、商品資料))
> 客戶資料
```
CREATE TABLE customer (
id INT unsigned PRIMARY KEY ,
name VARCHAR(50) NOT NULL,
address VARCHAR(100) NOT NULL,
join_date DATE,
email VARCHAR(50) NOT NULL,
);
INSERT INTO customer (username, password, join_date, id_number, email, url)
VALUES
('john123', '東京都港区白金台5-20-1', '2024-01-01','john123@gmail.com'),
('mary456', '東京都港区白金台5-20-2', '2024-01-15', 'mary456@gmail.com'),
('david789', '東京都港区白金台5-20-3', '2024-02-01', 'david789@gmail.com'),
('lisa111', '東京都港区白金台5-20-4', '2024-02-15', 'lisa111@gmail.com'),
('kevin222', '東京都港区白金台5-20-5', '2024-03-01', 'kevin222@gmail.com');
```
> 庫存
```
CREATE TABLE purchase (
id INT unsigned PRIMARY KEY,
production_name varchar(40),,
price int,
purchase_date DATE
);
```
> 銷售紀錄
```
CREATE TABLE sale_record (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT Unsigned,
qty int,
price float
);
```
## 2、實作
> 顧客資料
```
CREATE TABLE member (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
join_date DATE,
id_number VARCHAR(10) NOT NULL,
email VARCHAR(50) NOT NULL,
url VARCHAR(100) NOT NULL
);
INSERT INTO member (username, password, join_date, id_number, email, url)
VALUES
('john123', 'password123', '2022-01-01', 'A123456789', 'john123@gmail.com', 'https://www.example.com/john123'),
('mary456', 'password456', '2022-01-15', 'B234567890', 'mary456@gmail.com', 'https://www.example.com/mary456'),
('david789', 'password789', '2022-02-01', 'C345678901', 'david789@gmail.com', 'https://www.example.com/david789'),
('lisa111', 'password111', '2022-02-15', 'D456789012', 'lisa111@gmail.com', 'https://www.example.com/lisa111'),
('kevin222', 'password222', '2022-03-01', 'E567890123', 'kevin222@gmail.com', 'https://www.example.com/kevin222');
```
> 商品資訊
```
CREATE TABLE purchase (
id INT PRIMARY KEY AUTO_INCREMENT,
member_id INT NOT NULL,
product_name VARCHAR(50) NOT NULL,
price DECIMAL(8, 2) NOT NULL,
purchase_date DATE
);
INSERT INTO purchase (member_id, product_name, price, purchase_date)
VALUES
(1, 'T-shirt', 20.99, '2022-01-02'),
(2, 'Jeans', 39.99, '2022-01-17'),
(3, 'Sneakers', 79.99, '2022-02-03'),
(4, 'Dress', 59.99, '2022-02-18'),
(5, 'Jacket', 99.99, '2022-03-03'),
(1, 'Jeans', 29.99, '2022-03-05'),
(3, 'Dress', 89.99, '2022-03-20'),
(2, 'Handbag', 49.99, '2022-04-01');
```
# 65、關聯式資料庫(外部鍵約束)
> 一種約束條件，用於保護資料庫中的數據完整性
> 要求一個資料表中的某個欄位的值必須與另一個資料表中的欄位的值相
> 符，否則就無法進行相應的操作
> CONSTRAINT 限制、約束
```
ALTER TABLE purchase
ADD CONSTRAINT fk_member_id
FOREIGN KEY (member_id)
REFERENCES member(id);
```
> 檢查表結構，確認是否存在重複的主鍵或唯一索引
```SHOW CREATE TABLE `purchase`;```
## 1、實作(失敗寫入範例)
```
INSERT INTO purchase (member_id, product_name, price, purchase_date)
VALUES
(8, 'T-shirt', 20.99, '2022-01-02');
```
## 2、實作(成功寫入範例)
```
INSERT INTO purchase (member_id, product_name, price, purchase_date)
VALUES
(5, 'T-shirt', 20.99, '2022-01-02');
```
> 多下的指令要刪除值，前提是有設定id屬性有PRIMARY KEY
`DELETE FROM purchase WHERE id = 11;`
## 3、實作(子查詢)
> 有購買過牛仔物的紀錄
> `SELECT * FROM purchase WHERE product_name='jeans';`
> 會員基本資料
> `SELECT * FROM member;`
> 有購買過牛仔褲的會員基本資料
> `SELECT * FROM member WHERE id in(SELECT member_id FROM purchase WHERE product_name = 'jeans');`
# 66、資料表合併
> 交叉結合
> `SELECT * FROM member,purchase;`
> 交叉結合 可以別名資料表
> `SELECT * FROM member m, purchase p;`
> 內部結合 可以別名資料表
> `SELECT * FROM member m , purchase p WHERE m.id = p.member_id;`
```
select
m.username,
p.product_name,
p.purchase_date
from member m,
purchase p
where m.id = p.member_id;
```
> inner join 內部另一種寫法
```
SELECT m.username,
p.product_name,
p.price,
p.purchase_date
FROM member m
INNER JOIN purchase p
ON m.id = p.member_id;
```
> 右結合
```
SELECT m.username,
p.product_name,
p.price,
p.purchase_date
FROM member m
RIGHT JOIN purchase p
ON m.id = p.member_id;
```
> 左結合
```
select
m.username,
p.product_name,
p.price,
p.purchase_date
from member m
left join purchase p
on m.id=p.member_id;
```
```
INSERT INTO member (username, password, join date, id number, email, url)
VALUES
(jack123', 'password123', '2022-01-01', 'A123456789\*, Jack123@gmail.com', 'https://www.example.com/jack123');
```
---
# 67、資料庫備份管理
> 確認是否可以登入
> `mysql -uroot -p(password) -D mydb`
> 備份資料：(windows 執行,全部備份不需要再刪除 data)
> `mysqldump -u root -p(password) --all-databases >D:/Mysql/backup/檔名.bak`
> 備份資料：(單一個資料庫)
> `mysqldump -u root -p(password) Mynewdb >D:/Mysql/backup/檔名.bak`
> 備份資料：(單一個資料庫+databases 幫你建立)
> `mysqldump -u root -p(password) --databases Mynewdb >D:/Mysql/backup/檔名.bak`
> 還原全部：(windows 執行)
> `mysql -u root -p(password) < D:/Mysql/backup/檔名.sql`
> 先將 mydb 資料庫刪除
> `Drop database mydb;`
> 故意建立一個不同名稱的資料庫(也可以相同)
> `CREATE DATABASE mynewdb;`
> 還原資料庫
> `mysql -uroot -p(password) -D mynewdb < D:/Mysql/backup/檔名.sql`
> =======table
> 備份資料庫(備份多個資料庫)
> `mysqldump -uroot -p123456 mynewdb member purchase employee> G:\我的雲端硬碟\課堂-新尖兵全端-202412\mysql\課堂筆記\some_tables.bak`
---
# 68、VIEW 的應用(單純給對方存取觀看)
> 建立 VIEW 資料表
```
CREATE VIEW employee_simaple AS
SELECT name ,gender , department
FROM employee;
```
> 顯示資料表
> `show tables;`
> 搜尋 VIEW
> `select * from employee_simaple;`
> 顯示所有資料表類型
> `show full tables; `
> 建立資料表合併的 VIEW
```
CREATE VIEW purchase_record AS
select
m.username ,
p.product_name,
p.purchase_date
from member m , purchase p
where m.id = p.member_id;
```
> 刪除 VIEW
> `DROP VIEW purchase_record;`
> 修改 VIEW
```
ALTER VIEW purchase_record AS
select
m.username ,
p.product_name
from member m , purchase p
where m.id = p.member_id;
```
> 開權限給別人
```
GRANT SELECT
ON mynewdb.purchase_record
TO peter@'localhost'
IDENTIFIED BY'123456';
```
Sign in with Wallet
Connect another wallet