MySQL擴展

来源:https://www.cnblogs.com/xiaoqigui/archive/2022/07/27/16524128.html
-Advertisement-
Play Games

1、行轉列 源數據： 目標數據： 數據準備 -- 建表插入數據 drop table if exists time_temp; create table if not exists time_temp( `year_col` int not null comment '年份', `month_col ...


1、行轉列

源數據：

目標數據：

數據準備

-- 建表插入數據
drop table if exists time_temp;
create table if not exists time_temp(
 `year_col` int  not null comment '年份',
 `month_col` int  not null comment '月份',
 `day_col` int  not null comment '天數'
)engine  = innodb default charset = utf8;


insert into time_temp values 
(2020,1,31),
(2020,2,29),
(2020,3,31),
(2020,4,30),
(2020,5,31),
(2020,6,30);

insert into time_temp values 
(2022,1,31),
(2022,2,29),
(2022,3,31),
(2022,4,30),
(2022,5,31),
(2022,6,30);

1.1 方式1 分組+子查詢

-- 將數據根據年份分組，然後在進行子查詢通過月份查出對應的天數；
select t.year_col,
(select t1.day_col from time_temp t1 where t1.month_col = 1 and t1.year_col = t.year_col) 'm1',
(select t1.day_col from time_temp t1 where t1.month_col = 2 and t1.year_col = t.year_col) 'm2',
(select t1.day_col from time_temp t1 where t1.month_col = 3 and t1.year_col = t.year_col) 'm3',
(select t1.day_col from time_temp t1 where t1.month_col = 4 and t1.year_col = t.year_col) 'm4',
(select t1.day_col from time_temp t1 where t1.month_col = 5 and t1.year_col = t.year_col) 'm5',
(select t1.day_col from time_temp t1 where t1.month_col = 6 and t1.year_col = t.year_col) 'm6'
from time_temp t
group by t.year_col;

1.2 方式2：分組+case when

-- 先根據年份分組，在根據case when .. then ... 條件判斷 輸入出指定列的信息
select t.year_col,
	min(case when t.month_col = 1 then t.day_col end) 'm1',
	min(case when t.month_col = 2 then t.day_col end) 'm2',
	min(case when t.month_col = 3 then t.day_col end) 'm3',
	min(case when t.month_col = 4 then t.day_col end) 'm4',
	min(case when t.month_col = 5 then t.day_col end) 'm5',
	min(case when t.month_col = 6 then t.day_col end) 'm6'
from time_temp t
group by t.year_col;

2、刪除重覆數據

思路：先查詢出需要保留的數據，然後刪除其他的數據；

-- ====================刪除重覆數據=========================
DROP TABLE IF EXISTS `results_temp`;
CREATE TABLE `results_temp`(
  `id` int primary key auto_increment comment '主鍵',
  `stu_no` int NOT NULL COMMENT '學號',
  `subj_no` int NOT NULL COMMENT '課程編號',
  `exam_date` datetime NOT NULL COMMENT '考試時間',
  `stu_result` int NOT NULL COMMENT '考試成績'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '成績表臨時' ROW_FORMAT = Dynamic;

-- 將另外一張表的數據插入到此表中(也可以用其他方式插數據，這裡時為了方便) 插入兩次，讓數據重覆
insert into results_temp (stu_no,subj_no,exam_date,stu_result)
select stu_no,subj_no,exam_date,stu_result
from results 
where subj_no = 1;

-- 查詢數據，每個學生的同一門課程的成績有兩個,或者多個
select * from results_temp 
order by stu_no desc;

解決方法：篩選出我們需要的數據，其他數據刪除；

-- 剔除重覆的學生成績，只保留一份
-- 我們要保留的數據
select min(id) from results_temp
group by stu_no;

delete from results_temp 
where id not in( -- 除了我們要保留的數據其他數據都刪除
	select * from(
		select min(id) from results_temp -- 我們要保留的數據
		group by stu_no 
	) rt
);

再次執行SQL重覆數據刪除成功

select * from results_temp 
order by stu_no desc;

3、如果一張表，沒有id自增主鍵，實現自定義一個序號

實現思路：通過定義一個變數，查詢到一行數據就對變數 +1；

使用@關鍵字創建“用戶變數”;

mysql中變數不用事前申明，在用的時候直接用“@變數名”。
第一種用法：set @num=1; 或set @num:=1;
第二種用法：select @num:=1; 也可以把欄位的值賦值給變數 select @num:=欄位名 from 表名 where ……
註意上面兩種賦值符號，使用set時可以用 = 或 := ，但是使用select時必須用 :=

SQL實現

select @rownum:=@rownum + 1 'id',stu_no,stu_result   -- @rownum:=@rownum + 1 每查詢出一條數據就對變數 @rownum 加一
from results,
(select @rownum:= 0) rowss  -- 聲明：前面要使用 @rownum 要在這裡（form後面）先聲明並賦值為0  @rownum:= 0 ,前面才可以使用
where subj_no = 2
order by stu_no desc;

4、約束

4.1 主鍵約束 primary key

4.1.1 創建表和約束

-- 主鍵約束
create table employees_temp1(
	emp_id int primary key,
	emp_name varchar(50)
)engine = innodb charset = utf8;

4.1.2 主鍵約束特點1：非空

insert into employees_temp1 values (null,'張三'); -- 添加一條數據，主鍵為空

4.1.3 主鍵約束特點2： 唯一

insert into employees_temp1 values (101,'張三');
insert into employees_temp1 values (101,'張三'); -- 插入兩個相同的數據

4.2 唯一約束 unique

4.2.1 創建表和唯一約束

-- 唯一約束，
create table employees_temp2(
	emp_id int primary key,
	emp_name varchar(50),
	emp_tel char(11) unique -- 使用列級別聲明
)engine = innodb charset = utf8;

4.2.2 唯一約束特點1：沒有非空約束非空

-- 唯一約束特點1：沒有非空約束非空
insert into employees_temp2 values (101,'張三',null); -- 可以插入null值
insert into employees_temp2 values (102,'李四',null);

4.2.3 唯一約束特點2：可以保證值的唯一性

-- 唯一約束特點2：可以保證值的唯一性
insert into employees_temp2 values (103,'王五','13501020304');
insert into employees_temp2 values (104,'劉六','13501020304'); -- 手機號不能相同

4.2.4 組合唯一約束

-- 補充：組合唯一約束，可以指定多列作為唯一條件
create table employees_temp3(
	emp_id int primary key,
	emp_name varchar(50),
	emp_nick char(11),
	-- 使用表級別聲明,真實姓名和昵稱的組合唯一
	constraint uk_emp_name_nick unique(emp_name,emp_nick)
)engine = innodb charset = utf8;

-- 多列唯一約束，可以保證多列值組合起來，保證值的唯一性，但是單列值，不保證唯一
insert into employees_temp3 values (101,'王五','小五');
insert into employees_temp3 values (102,'王五','大五');

insert into employees_temp3 values (104,'王五','大五'); -- 不可以
insert into employees_temp3 values (103,'王六','大五');

4.2.5 刪除唯一約束

-- 修改表語法
-- alter table 表名 drop 約束名
alter table employees_temp3 drop index uk_emp_name_nick;

-- drop 語法
-- drop index 約束名 on 表名
drop index uk_emp_name_nick on employees_tem

4.3 外鍵約束 delete時的級聯刪除和級聯置空

4.3.1 級聯刪除 on delete cascade

-- 級聯刪除
-- 創建部門表
drop table if exists departments_temp1;
create table departments_temp1(
	dept_id int primary key,
	dept_name varchar(50)
)engine = innodb charset = utf8;

-- 插入部門數據
insert into departments_temp1 values(100,'研發部'),(200,'市場部')

-- 創建員工表 和外鍵約束
drop table if exists employees_temp4;
create table employees_temp4(
  emp_id int primary key,
	emp_name varchar(50),
	emp_nick char(11),
	dept_id int,
	
	-- 使用表級聲明，增加部門編號的外鍵約束，並指定級聯刪除
	constraint fk_emp_dept_id foreign key (dept_id) 
	references departments_temp1(dept_id)
	on delete cascade
)engine = innodb charset = utf8;

-- 插入員工數據
insert into employees_temp4 values (101,'王五','小五',100);
insert into employees_temp4 values (102,'李四','小四',200);

查詢數據：

select * from employees_temp4;
select * from departments_temp1;


-- 當設置外鍵屬性為級聯刪除時，刪除部門表中的數據，自動將所有關聯表中的外鍵數據，一併刪除
delete from departments_temp1 where dept_id = 100;

-- 再次查詢數據：
select * from employees_temp4;
select * from departments_temp1;
-- 部門刪除後，該部門的數據也被刪除了

4.3.2 級聯刪除置空 on delete set null

-- 級聯置空
-- 創建部門表
drop table if exists departments_temp2;
create table departments_temp2(
	dept_id int primary key,
	dept_name varchar(50)
)engine = innodb charset = utf8;

-- 插入部門數據
insert into departments_temp2 values(100,'研發部'),(200,'市場部')

-- 創建員工表和外鍵約束
drop table if exists employees_temp5;
create table employees_temp5(
  emp_id int primary key,
	emp_name varchar(50),
	emp_nick char(11),
	dept_id int,
	
	-- 使用表級聲明，增加部門編號的外鍵約束，並指定級聯刪除
	constraint fk_null_emp_dept_id foreign key (dept_id) 
	references departments_temp2(dept_id)
	on delete set null
)engine = innodb charset = utf8;

-- 插入員工數據
insert into employees_temp5 values (101,'王五','小五',100);
insert into employees_temp5 values (102,'李四','小四',200)

查詢數據：

select * from employees_temp5;
select * from departments_temp2;


-- 當設置外鍵屬性為級聯置空時，刪除部門表中的數據，自動將所有關聯表中的外鍵數據，一併置空
delete from departments_temp2 where dept_id = 200;

select * from employees_temp5;
select * from departments_temp2;
-- 部門被刪除後，該部門的數據被置空

4.4 外鍵約束 update時的級聯更新和級聯置空

4.4.1 級聯更新 on update cascade

-- -- ================ update 的級聯刪除和級聯置空==========
drop table if exists departments_temp1_2;
create table departments_temp1_2(
	dept_id int primary key,
	dept_name varchar(50)
)engine = innodb charset = utf8;

insert into departments_temp1_2 values(100,'研發部'),(200,'市場部')

drop table if exists employees_temp4_2;
create table employees_temp4_2(
  emp_id int primary key,
	emp_name varchar(50),
	emp_nick char(11),
	dept_id int,
	
	# 使用表級聲明，真實姓名和昵稱是組合唯一
	constraint uk_emp_name_nick unique(emp_name,emp_nick),
	-- 使用表級聲明，增加部門編號的外鍵約束，並指定級聯更行修改
	constraint fk_emp_dept_id_update foreign key (dept_id) 
	references departments_temp1_2(dept_id)
	on update cascade  -- 更新部門表中的數據，自動將所有關聯表中的外鍵數據，一併更新
)engine = innodb charset = utf8;

insert into employees_temp4_2 values (101,'王五','小五',100);
insert into employees_temp4_2 values (102,'李四','小四',200);

查詢數據:

select * from employees_temp4_2;
select * from departments_temp1_2;

部門表數據更新:

-- 當設置外鍵屬性為級聯更新時刪除時，更新部門表中的數據，自動將所有關聯表中的外鍵數據，一併更新
update departments_temp1_2 set dept_id = 111 where dept_id = 100;

-- 再次查詢數據
select * from employees_temp4_2;
select * from departments_temp1_2;

4.4.2 級聯更新置空

-- ==================update 級聯更新置空========================
drop table if exists departments_temp2_2;
create table departments_temp2_2(
	dept_id int primary key,
	dept_name varchar(50)
)engine = innodb charset = utf8;

insert into departments_temp2_2 values(100,'研發部'),(200,'市場部')

drop table if exists employees_temp5_2;
create table employees_temp5_2(
  emp_id int primary key,
	emp_name varchar(50),
	emp_nick char(11),
	dept_id int,
	
	-- 使用表級聲明，增加部門編號的外鍵約束，並指定級聯更新置空
	constraint fk_emp_dept_id_update2 foreign key (dept_id) 
	references departments_temp2_2(dept_id)
	on update set null
)engine = innodb charset = utf8;

select * from employees_temp5_2;
select * from departments_temp2_2;

查詢數據：

insert into employees_temp5_2 values (101,'王五','小五',100);
insert into employees_temp5_2 values (102,'李四','小四',200);

部門表數據更新

-- 當設置外鍵屬性為級聯置空時，更新部門表中的數據，自動將所有關聯表中的外鍵數據，一併置空
update departments_temp2_2 set dept_id = 111 where dept_id = 100；

-- 再次查詢數據
select * from employees_temp5_2;
select * from departments_temp2_2;

4.5 非空約束

-- 非空約束
drop table if exists employees_temp6;
create table employees_temp6(
  emp_id int primary key,
	emp_name varchar(50),
	emp_nick char(11),
	dept_id int not null,
	
	-- 使用表級聲明，真實姓名和昵稱是組合唯一
	constraint uk_emp_name_nick unique(emp_name,emp_nick)
)engine = innodb charset = utf8;

-- 增加非空約束列，插入數據時，必須保證該列有效值，或者預設值，但不能為null
insert into employees_temp6 values (101,'王五','小五',100);
insert into employees_temp6 values (102,'李四','小四',null); -- 不能插入，因為 dept_id 設置了不能為空

5、索引

5.1分類

  1. 主鍵索引(主鍵約束) primary key
  2. 唯一索引(唯一約束) unique
  3. 普通索引 index/key
  4. 全文索引fulltext (存儲引擎必須時MyISAM)

5.2 作用

為了提高資料庫的查詢效率(SQL執行性能) ，底層索引演算法是B+樹（BTree）；

5.3 建議

索引的創建和管理是資料庫負責，開發人員無權干涉，原因：查詢數據是否走索引，是資料庫決定，底層演算法覺得走索引查詢效率高就走索引，如果覺得不走索引查詢效率高，就不走索引，在寫SQL語句時，儘量要避免索引失效(SQL調優)；

5.4 註意

1.不是索引越多越好，資料庫底層要管理索引，也需要耗費資源和性能(資料庫性能會下降)；
2.如果當前列數據重覆率較高，比如性別，不建議使用索引；
3.如果當前列內容，經常改變，不建議使用索引，因為數據頻繁修改要頻繁的維護索引，性能會下降；
4.小數據量的表也不推薦索引，因為小表的查詢效率本身就很快；

5.5 強調

一般索引都是加在where,order by 等子句經常設計的列欄位，提高查詢性能；

主鍵索引和唯一索引，對應列查詢數據效率高；

5.6 建表時添加索引

-- 普通索引的創建1，建表時添加
drop table if exists employees_temp7;
create table employees_temp7(
  emp_id int primary key,
	emp_name varchar(50),
	index index_emp_name (emp_name)
)engine = innodb charset = utf8;

5.7 建表後添加索引

-- 普通索引的創建2，建表後添加
drop table if exists employees_temp8;
create table employees_temp8(
  emp_id int primary key,
	emp_name varchar(50)
)engine = innodb charset = utf8;

-- 使用修改表語法，添加索引
alter table employees_temp8 add index index_emp_name_new(emp_name);

5.8 查看表的索引

-- 查看表的索引語法
show index from employees_temp7;
show index from employees_temp8;

5.9 刪除索引

-- 刪除索引1
alter table employees_temp7 drop index  index_emp_name;
show index from employees_temp7;


-- 刪除索引2
drop index index_emp_name_new on employees_temp8;
show index from employees_temp8;

5.10 分析執行語句的執行性能

-- 分析執行語句的執行性能
-- 查看SQL語句的執行計劃，通過分析執行計劃結果，優化SQL語句，提示查詢性能
-- 使用 explain select 語句，可以看SQL是全表查詢還是走了索引等
-- 先把索引添加回來
alter table employees_temp8 add index index_emp_name_new(emp_name);
explain  select * from employees_temp8;

5.10 全文索引

-- 全文索引
-- 快速進行全表數據的定位，是使用與MyISAM存儲引擎表，而且只適用於char,varchar,text等數據類型
drop table if exists employees_temp9;
create table employees_temp9(
  emp_id int primary key,
	emp_name varchar(50),
	fulltext findex_emp_name(emp_name)
)engine = myisam charset = utf8;

6、存儲過程

6.1 帶入參存儲過程

-- 作用：可以進行程式編寫，實現整個業務邏輯單元的多條SQL語句的批量執行；比如：插入表10W數據
-- 帶入參的存儲過程
-- delimiter //  將MySQL結束符號更改為 // ,其他符號也可以
delimiter //
create procedure query_employee_by_id(in empId int)
begin
	select * from employees_temp6 where emp_id = empId;
end//


-- 調用存儲過程
call query_employee_by_id(101);


-- 刪除存儲過程
drop procedure query_employee_by_id;

6.2 帶出參存儲過程

-- 帶出參的存儲過程
delimiter //
create procedure query_employee_by_count(out empNum int)
begin
	select count(1) into empNum from employees_temp6;
end//

-- 調用
-- 定義變數，接收存儲過程的結果
set @empNum = 0;
-- 調用出參村塾過程
call query_employee_by_count(@empNum);

-- 獲取存儲過程結果
select @empNum from dual;

6.3 自定義存儲過程

-- 自定義存儲過程，實現出入一個數值,並計算該值內的所有奇數之和，並輸出結果
delimiter //
create procedure sum_odd(in num int)
begin
	declare i int; -- 先定義，後賦值
	declare sums int;
	set i = 0;
	set sums = 0;
	-- declare i int default 0; -- 定義後直接賦預設值
	-- declare sums int default 0;
	while i <= num do
		if i % 2 = 1 then
			set sums = sums + i;
		end if;
	set i = i + 1;
	end while;
	
	-- 輸出結果
	select sums from dual;
end
//

-- 調用存儲過程，查看結果
call sum_odd(100);


-- 恢復MySQL預設的分隔符 註意在最後一定要執行一遍整個語句
delimiter ;

7、觸發器

7.1 觸發器語法

  • 創建類似於存儲過程

  • 語法：create    trigger    trigger_name     trigger_time    trigger_event    on   tab_name   for   each row   trigger_stmt

    • trigger_name:觸發器名
    • trigger_time 觸發時機 befor,after
    • trigger_event 觸發事件， 取值：insert,update,delete
    • tab_name: 觸發器作用的表名，即在那張表上建立觸發器，如果對該表操作，觸發器會自動生效
    • trigger_stmt: 觸發事件的執行程式主體，可以是一條SQL,也可以是使用begin...end 包含的duoSQL語句

7.2 觸發器分類 （6種）

before 和 after 與 insert,update,delete的組合：

  • before insert, before update, before delete
  • after insert, after update, after delete

7.3 簡單案例

-- 簡單案例，當對指定表刪除數據時，自動將該條刪除的數據備份
drop table if exists employees_temp10;
create table employees_temp10(
  emp_id int primary key,
	emp_name varchar(50),
	emp_time datetime
)engine = innodb charset = utf8;

insert into employees_temp10 values (101,'王五',now());

drop table if exists employees_temp10_his;
create table employees_temp10_his(
  emp_id int primary key,
	emp_name varchar(50),
	emp_time datetime
)engine = innodb charset = utf8;

-- 自定義觸發器
-- NEW 和 OLD 含義：代表觸發器所在表中，當對數據操作時，觸發觸發器的那條數據
-- 對於insert觸發事件：NEW 表示插入後的新數據
-- 對於update觸發事件：NEW 表示修改後的數據，OLD表示被修改前的原數據
-- 對於delete出發時間：OLD 表示被刪除前的數據

-- 語法：NEW/OLD.表中的列名

delimiter //
create trigger backup_employees_temp10_delete 
after delete
on employees_temp10
for each row
begin
	insert into employees_temp10_his(emp_id,emp_name,emp_time) 
	value (OLD.emp_id,OLD.emp_name,OLD.emp_time);
end
//
delimiter ;

-- 刪除employees_temp10 中的數據
delete from  employees_temp10 where emp_id = 101;

-- 查詢employees_temp10 和歷史表 employees_temp10_his
select * from employees_temp10;
select * from employees_temp10_his;

employees_temp10表：

employees_temp10_his表：

8、數據集合連接(union 和 union all)

-- 快速建表,直接將查詢的數據建成一張表
-- crate table table_name (select_SQL)
create table employees_temp11(select * from employees where department_id in(50,60));
create table employees_temp12(select * from employees where department_id in(60,70));
-- 兩張表有重覆的數據 department_id = 60

8.1 union

-- union 連接：union前的那個SQL語句，不能是分號結尾
-- 查詢結果連接，會自動去重，相同的數據只保留一份
-- 結果51條，50號部門45條，50號部門5條，70號部門1條，執行自動去重
select department_id,employee_id
from employees_temp11
union    -- 查詢的數據會去重
select department_id,employee_id
from employees_temp12;

8.2 union all

-- 結果56條，50號部門45條，50號部門5條，70號部門1條，執行連接，不會自動去重，相同的數據任然會保留
select department_id,employee_id
from employees_temp11
union all --查詢到的數據不會去重
select department_id,employee_id
from employees_temp12;

8.3 union all

-- 連接兩條SQL語句，查詢結果列，上下列個數要統一,否則會報錯，也可以寫*（表結構統一）
select department_id,department_id,Last_name
from employees_temp11
union all
select department_id,department_id,hire_date
from employees_temp12;

-- 別名處理
-- 如果第一個SQL語句的結果集使用了別名處理，自動作用到連接的後面結果集，但要單獨寫在後面就沒有效果
select department_id,department_id '部門編號',Last_name -- 有效果
from employees_temp11
union all
select department_id,department_id,hire_date
from employees_temp12;

select department_id,department_id,Last_name
from employees_temp11
union all
select department_id,department_id  '部門編號',hire_date -- 無效果
from employees_temp12;

-- 連接查詢，預設是按照查詢結構第一列升序排序，也可以自定義
select employee_id,department_id '部門編號'
from employees_temp11
union all
select employee_id,department_id
from employees_temp12 order by employee_id desc;

9、視圖-view

視圖：view,是從表中抽離出（查詢出），在邏輯上有相關性的數據集合，它是一個虛表。

數據：視圖中的數據可以從一張表或者多張表查詢，視圖的結構和數據都依賴於基本表(原始表)；
通過視圖可以直接查看到基本表中的數據，且可以直接操作，增刪改查；
理解：可以將視圖理解為被存儲起來的SQL語句，就是select語句；

特點：1.可以簡化SQL語句，經常需要執行的複雜sql語句我們可以通過視圖緩存，簡化查詢數據及操作；
特點：2.提高安全性，通過視圖只能查詢和修改你看到的數據，其他數據你看不到也改不了，比如工資，密碼；

9.1創建視圖

-- 創建視圖
-- 普通視圖和複雜視圖
-- 創建視圖語法：
-- create or replace [{undefined | merge | temptable}]
-- view view_name [coll_list]
-- as 
-- select_SQL 

-- 創建視圖1：查詢50號部門的數據
create or replace view employee_view1
as
select employee_id,last_name,salary,department_id
from employees
where department_id = 50;

9.2 查詢視圖

-- 查詢視圖
select * from employee_view1;

9.3 查看視圖結構

-- 查看視圖的內容結構
desc employee_view2;

9.4 視圖特點

-- 視圖中的數據，不是固定的，實際上還是查詢的基礎表的數據，所以基礎表的數據發生改變，視圖的數據也會改變
select * from employee_view1 where last_name = 'Fripp';
-- 修改基礎表：employees，將Fripp的salary，從8200更改為9000
update employees set salary = 9000 where last_name = 'Fripp';

-- 視圖中的數據，由於是源於基礎表，跟基本表是有關係的，所以修改視圖，就是修改源表
select * from employee_view1 where last_name = 'Fripp';

-- 修改視圖 employee_view1 ,將Fripp的工資從9000更改為12000
update employees set salary = 12000 where last_name = 'Fripp';

-- 刪除也是同理，刪除視圖中的數，源表中的數據也會刪除
-- 刪除最低工資的Olson刪除
delete from employee_view1 where last_name = 'Olson';

select * from employees where last_name = 'Olson';

9.5 修改視圖

-- 修改視圖
-- crate or replace view view_name as select_sql
-- 如果不加or replace ，第一次創建視圖是成功的，第二次會檢查視圖名是否存在，如果存在創建失敗
-- 如果加上or replace，發現已經存在會替換
create or replace view employee_view1
as
select employee_id,last_name,salary,department_id,manager_id
from employees
where department_id = 50;

-- 查看視圖
select * from employee_view1;

9.6 複雜視圖

-- 查詢員工表的所有部門的平均工資
create or replace view employee_view3
as
    select d.department_id,d.department_name,avg(e.salary) 'salary_avg'
    from employees e,departments d
where e.department_id = d.department_id
group by d.department_id;

-- 查詢視圖
select * from employee_view3;

# 複雜視圖說明：如果視圖是複雜視圖，對此視圖的增刪改操作
-- 一般是無效的，因為複雜視圖一般是有多表經過計算來的，所以資料庫不知道該怎麼操作
-- 比如：分組，group by,聚合函數，去重等
-- 舉例：修改50號部門的平均工資
update employee_view3 set salary_avg = 6000 where department_id = 50; -- 不能修改

-- 刪除視圖，語法類似與刪除表，刪除視圖定義，不會影響基本表
drop view employee_view3;

10、枚舉類型

語法：enum(允許的值列表)，比如：性別定義：gender enum('男'，'女')；
好處1：可以實現對該列值的限制，非指定值列表的其他值，是部允許插入的，增加數據的安全性；
好處2:相對於字元串類型純屬男或女，枚舉可以節約存儲空間，原因：使用整數進行管理，取值範圍是2個位元組，有65535個選項可以使用；
場景：列中的值存在大量的重覆數據，且是預先設置好的固定，並不容易發生改變；

10.1 創建枚舉

-- 實例用法
drop table if exists employees_temp13;
create table if not exists employees_temp13(
	emp_id int primary key auto_increment comment '編號',
	emp_name varchar(32) not null comment '姓名',
	emp_sex enum('男','女') comment '性別'
)engine innodb charset = utf8 comment '員工臨時表13';

10.2 插入枚舉數據

10.2.1 使用列表值

-- 插入數據，使用列表值
insert into employees_temp13 values
(1,'張三','男');

10.2.2 使用索引

-- 插入數據，使用索引,從1開始編號
insert into employees_temp13 values
(2,'李四',2);

10.2.3 註意點

-- 不正常插入數據
insert into employees_temp13 values
(3,'王五',3); -- 不能插入數據

insert into employees_temp13 values
(4,'王五','未知'); -- 未知

insert into employees_temp13 values
(4,'王五',null); -- 允許插入null

10.3 枚舉查詢

-- 帶條件查詢
-- 使用索引查詢
select * from employees_temp13 where emp_sex = 1;
-- 使用列表值查詢
select * from employees_temp13 where emp_sex = '男';
-- 查詢為null的
select * from employees_temp13 where emp_sex is null;

11、據備份和恢復

11.1 數據備份

# 作用1：備份就是為了防止原數據丟失，保證數據的安全。當資料庫因為某些原因造成部分或者全部數據丟失後，備份文件可以找回丟失的數據。
# 作用2：方便數據遷移，當需要進行新的資料庫環境搭建，複製數據時，備份文件可以快速實現數據遷移。
# 數據丟失場景：人為操作失誤造成某些數據被誤刪，硬體故障造成資料庫部分數據或全部數據丟失，安全漏洞被入侵數據惡意破壞等
# 非數據丟失場景：資料庫或者數據遷移，開發測試環境資料庫搭建，相同資料庫的新環境搭建等
# 方式1：前面介紹的Navicat或者SQLyog，導出腳本
# 方式2：MySQL提供了mysqldump命令，可以實現數據的備份。可以備份單個資料庫、多個資料庫和所有資料庫。

# 語法：mysqldump -h主機ip –u用戶名 –p密碼 [option選項] 資料庫名 [表名1 [表名2...]] > filename.sql

# 最後的文件名：可以直接是單個文件，也可以文件名前加上可以訪問的絕對路徑，如：d:/filename.sql 或則 /usr/tmp/filename.sql
# 選項指令說明：
# --add-drop-table ：導出sql腳本會加上 DROP TABLE IF EXISTS 語句，預設是打開的，可以用 --skip-add-drop-table 來取消
# --add-locks ：該選項會在INSERT 語句中捆綁一個LOCK TABLE 和UNLOCK TABLE 語句，好處：防止記錄被再次導入時，其他用戶對錶進行的操作，預設是打開的
# -t 或 --no-create-info ： 忽略不寫創建每個轉儲表的CREATE TABLE語句
# -c 或 --complete-insert ： 在每個INERT語句的列上加上欄位名，在資料庫導入另一個資料庫已有表時非常有用
# -d 或 --no-data ：忽略，不創建每個表的插入數據語句
# --where ： 只轉儲給定的WHERE條件選擇的記錄
# --opt 該選項是速記；等同於指定（--add-drop-table，--add-locks，--create-options，--disable-keys，--extended-insert，--lock-tables，--quick，--set-charset）
# 該選項預設開啟，但可以用 --skip-opt 禁用。如果沒有使用 --opt，mysqldump 就會把整個結果集裝載到記憶體中，然後導出。如果數據非常大就會導致導出失敗
# -q 或 --quick ： 不緩衝查詢，直接導出到標準輸出。預設為打開狀態，使用--skip-quick取消該選項。
-- 備份資料庫的語法不能在navicat中執行，跟mysql名是同級的，命令行執行

11.1.1 備份整個資料庫

mysqldump -u root -p bbsdb > D:/sqlDumpTest/bbsdbTemp.sql

11.1.2 備份整個資料庫，插入數據語句前 增加列名指定 -c

mysqldump -u root -p -c bbsdb > D:/sqlDumpTest/bbsdbTemp.sql

11.1.3 備份單張表

mysqldump -u root -p -c bbsdb bbs_detail > D:/sqlDumpTest/bbsdbTemp.sql

11.1.4 備份多張表

mysqldump -u root -p -c bbsdb bbs_detail bbs_sort > D:/sqlDumpTest/bbsdbTemp.sql

11.2.5 備份多個資料庫

mysqldump -u root -p --databases [option] bbsdb [xxdb1 xxdb2] > D:/sqlDumpTest/bbsdbTemp.sql

11.2.6 備份所有資料庫

mysqldump -u root -p --all-databases bbsdb [xxdb1 xxdb2] > D:/sqlDumpTest/bbsdbTemp.sql

11.2 數據恢復

數據恢復：前提，先備份數據文件;

11.2.1 source命令

-- 方式1：使用source命令，是在MySQL的命令行中執行的，所以必須登錄到MySQL資料庫中，且要先創建好資料庫，並切換到當前資料庫中
-- source D:/sqlDumpTest/bbsdbTemp.sql

11.2.2 mysql指令

-- 方式 2：使用mysql指令，不需要登錄
-- 語法：mysql -uroot -p db_name < D:/sqlDumpTest/bbsdbTemp.sql

11.2.3 多數據備份

--方式3：如果備份的是多資料庫，備份的資料庫文件中，包含創建和切換資料庫語句，不需要先創建資料庫，直接使用source命令
-- 語法：登錄到mysql中，在命令行中執行
-- source D:/sqlDumpTest/bbsdbTemp.sql

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • VScode搭建docker環境
    鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 前言 本環境通過使用VMware在Ubuntu20.04下通過VScode搭建docker環境，可通過遠程連接容器進行開發。（僅作參考） 一、環境準備 VMware 15 pro，Ubuntu20.04，VScode（Linux版本） 虛擬機搭建 ...
  • 【操作系統】文件
    寫在前面 本系列的文章是博主邊學邊記錄的，可能不是特別的正確，因為會加上博主自己的理解，僅供參考。 正文: 1.文件目錄 文件目錄是一種數據結構，用於標識系統中文件及其物理地址，供檢索的時候使用。對於目錄的管理有如下要求： 1.實現按名存取 2.提高對目錄的檢索速度 3.文件共用 在多用戶系統中，應 ...
  • 項目部署到Linux
    鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 項目部署 之前我們講解Linux操作系統時，就提到，我們服務端開發工程師學習Linux系統的目的就是將來我們開發的項目絕大部分情況下都需要部署在Linux系統中。那麼在本章節，我們將通過兩種方式，來演示項目部署，分別是：手動部署項目 和 基於sh ...
  • 基於STM32的外部電源電壓檢測
    STM32測量外部電源的電壓 本人在項目中遇到一個需求：使用電池給STM32開發板供電，並需要實時顯示當前電源的電量情況。這個需求可以說是很常見了，但是卻困擾了我整整一個多月。 在收到這個需求的時候我首先想到的就是上網查找相關的技術貼，其中一條名為《基於STM32F103內部AD測量電池電壓》的帖子 ...
  • 說透緩存一致性與記憶體屏障
    電腦的演進就是一部在挖坑和填坑之間反覆橫跳的發展史。對這一點的理解會隨著本文的後續講述逐漸加深。比如高速緩存Cache很好地解決了CPU與記憶體的速度矛盾，但是也為電腦系統帶來了更高的複雜度 ...
  • Redis 哈希Hash底層數據結構
    1. Redis 底層數據結構 Redis資料庫就像是一個哈希表，首先對key進行哈希運算得到哈希值再取模得到一個下標，每個元素是一個節點，節點之間形成鏈表。這感覺有點像Java中的HashMap。 不同的數據類型的實現方式是不一樣的，可以通過object encoding命令查看底層真正的數據存儲 ...
  • Mysql 數據恢復邏輯 基於binlog redolog undolog
    註：文中有個易混淆的地方"事務" sql事務，即每次資料庫操作生成的事務，這個事務trx_id只在undolog里存儲，同時undolog維護了此事務是否完成的狀態。 日誌持久化事務，為了保證redolog和binlog的一致性而用的Mysql內部獨立維護的2PC提交事務。這個xid只有在redol ...
  • Redis SCAN命令
    1. 獲取指定首碼的key 需求描述： Redis中有大量以xxx開頭的key，在不使用keys命令的情況下，如何快速獲取這些首碼的key 解決方案： redis自帶的scan命令可以解決這個問題 2. SCAN命令 SCAN是一個基於游標的迭代器。這意味著在每次調用該命令時，伺服器都會返回一個更新 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類