day23-必備SQL和表關係及授權

死不悔改奇男子發表於2024-04-25

1. 必備SQL語句

上一節講解了最基礎SQL語句：增刪改查，其實在日常的開發中還有很多必備的SQL語句。

這一部分的SQL語句都是圍繞著對表中的資料進行操作的。

提示：今天的所有操作我都只會在 MySQL自帶的客戶端工具上進行操作。

例如：現在建立如下兩張表。

create database day26db default charset utf8 collate utf8_general_ci;

create table depart(
	id int not null auto_increment primary key,
    title varchar(16) not null
)default charset=utf8;


create table info(
	id int not null auto_increment primary key,
    name varchar(16) not null,
    email varchar(32) not null,
    age int,
    depart_id int
)default charset=utf8;

insert into depart(title) values("開發"),("運營"),("銷售");

insert into info(name,email,age,depart_id) values("武沛齊","wupeiqi@live.com",19,1);
insert into info(name,email,age,depart_id) values("於超","pyyu@live.com",49,1);
insert into info(name,email,age,depart_id) values("alex","alex@live.com",9,2);
insert into info(name,email,age,depart_id) values("tony","tony@live.com",29,1);
insert into info(name,email,age,depart_id) values("kelly","kelly@live.com",99,3);
insert into info(name,email,age,depart_id) values("james","james@live.com",49,1);
insert into info(name,email,age,depart_id) values("李傑","lijie@live.com",49,1);

1.1 條件

根據條件搜尋結果。

select * from info where age > 30;
select * from info where id > 1;
select * from info where id = 1;
select * from info where id >= 1;
select * from info where id != 1;
select * from info where id between 2 and 4;   -- id大於等於2、且小於等於4

select * from info where name = '武沛齊' and age = 19;
select * from info where name = 'alex' or age = 49;
select * from info where (name = '李傑' or email="pyyu@live.com")  and age=49;

select * from info where id in (1,4,6);
select * from info where id not in (1,4,6);
select * from info where id in (select id from depart);
# select * from info where id in (1,2,3);

# exists select * from depart where id=5，去查資料是否存在，如果存在，如果不存在。
select * from info where exists (select * from depart where id=5);
select * from info where not exists (select * from depart where id=5);

select * from (select * from info where id>2) as T where age > 10;

select * from info where info.id > 10;
select * from info where id > 10;

1.2 萬用字元

一般用於模糊搜尋。

# % 可以匹配任意長度字元
select * from info where name like "%沛%";
select * from info where name like "%沛";
select * from info where email like "%@live.com";
select * from info where name like "武%齊";
select * from info where name like "k%y";
select * from info where email like "wupeiqi%";

# _ 匹配單個字元，不能多也不能少
select * from info where email like "_@live.com";
select * from info where email like "_upeiqi@live.com";
select * from info where email like "__peiqi@live.com";
select * from info where email like "__peiqi_live.co_";

注意：數量少，資料量大的搜尋。

1.3 對映

想要獲取的列。

select * from info;

select id, name from info;
select id, name as NM from info;
select id, name as NM, 123  from info;
注意：少些select * ,自己需求。

select
   id,
   name,
   666 as num,
   ( select max(id) from depart ) as mid, -- max/min/sum
   ( select min(id) from depart) as nid, -- max/min/sum
   age
from info;

select
   id,
   name,
   ( select title from depart where depart.id=info.depart_id) as x1
from info;

# 注意：效率很低

select
   id,
   name,
   ( select title from depart where depart.id=info.depart_id) as x1,
   ( select title from depart where depart.id=info.id) as x2
from info;

select
   id,
   name,
   case depart_id when 1 then "第1部門" end v1
from info;

select
   id,
   name,
   case depart_id when 1 then "第1部門" else "其他" end v2
from info;

select 
   id,
   name,
   case depart_id when 1 then "第1部門" end v1,
   case depart_id when 1 then "第1部門" else "其他" end v2,
   case depart_id when 1 then "第1部門" when 2 then "第2部門" else "其他" end v3,
   case when age<18 then "少年" end v4,
   case when age<18 then "少年" else "油膩男" end v5,
   case when age<18 then "少年" when age<30 then "青年" else "油膩男" end v6
from info;

1.4 排序

select * from info order by age desc; -- 倒序
select * from info order by age asc;  -- 順序

select * from info order by id desc;
select * from info order by id asc;
select * from info order by age asc,id desc; -- 優先按照age從小到大；如果age相同則按照id從大到小。


select * from info where id>10 order by age asc,id desc;
select * from info where id>6 or name like "%y" order by age asc,id desc;

1.5 取部分

一般要用於獲取部分資料。

select * from info limit 5;   										-- 獲取前5條資料
select * from info order by id desc limit 3;						-- 先排序，再獲取前3條資料
select * from info where id > 4 order by id desc limit 3;			-- 先排序，再獲取前3條資料


select * from info limit 3 offset 2;	-- 從位置2開始，向後獲取前3資料

資料庫表中：1000條資料。

第一頁：select * from info limit 10 offset 0;
第二頁：select * from info limit 10 offset 10;
第三頁：select * from info limit 10 offset 20;
第四頁：select * from info limit 10 offset 30;
...

1.6 分組

select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;

select age,count(1) from info group by age;

select depart_id,count(id) from info group by depart_id;

select depart_id,count(id) from info group by depart_id having count(id) > 2;

select count(id) from info;
select max(id) from info;

select age,max(id),min(id),sum(id),count(id) from info group by age;

select age,name from info group by age;  -- 不建議
select * from info where id in (select max(id) from info group by age);

select age,count(id) from info group by age having count(id) > 2;
select age,count(id) from info where id > 4 group by age having count(id) > 2;  -- 聚合條件放在having後面

到目前為止SQL執行順序：
    where 
    group by
    having 
    order by
    limit

select age,count(id) from info where id > 2 group by age having count(id) > 1 order by age desc limit 1;
- 要查詢的表info
- 條件 id>2
- 根據age分組
- 對分組後的資料再根據聚合條件過濾 count(id)>1
- 根據age從大到小排序
- 獲取第1條

1.7 左右連表

多個表可以連線起來進行查詢。

展示使用者資訊&部門名稱：

主表 left outer join 從表 on 主表.x = 從表.id

select * from info left outer join depart on info.depart_id = depart.id;

select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;

從表 right outer join 主表 on 主表.x = 從表.id

select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;

為了更加直接的檢視效果，我們分別在 depart 表和 info 中額外插入一條資料。

insert into depart(title) values("運維");

這樣一來主從表就有區別：

info主表，就以info資料為主，depart為輔。

select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;

depart主表，就以depart資料為主，info為輔。

select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;

select * from info left outer join depart on ....
select * from depart left outer join info on ....

簡寫：select * from depart left join info on ....

-- 內連線:    表  inner join 表  on 條件
select * from info inner join depart on info.depart_id=depart.id;

+----+-----------+------------------+------+-----------+----+--------+
| id | name      | email            | age  | depart_id | id | title  |
+----+-----------+------------------+------+-----------+----+--------+
|  1 | 武沛齊    | wupeiqi@live.com |   19 |         1 |  1 | 開發   |
|  2 | 於超      | pyyu@live.com    |   49 |         1 |  1 | 開發   |
|  3 | alex      | alex@live.com    |    9 |         2 |  2 | 運營   |
|  4 | tony      | tony@live.com    |   29 |         1 |  1 | 開發   |
|  5 | kelly     | kelly@live.com   |   99 |         3 |  3 | 銷售   |
|  6 | james     | james@live.com   |   49 |         1 |  1 | 開發   |
|  7 | 李傑      | lijie@live.com   |   49 |         1 |  1 | 開發   |
+----+-----------+------------------+------+-----------+----+--------+

到目前為止SQL執行順序：
    join 
    on 
    where 
    group by
    having 
    order by
    limit

寫在最後：多張表也可以連線。

1.8 聯合

select id,title from depart 
union
select id,name from info;


select id,title from depart 
union
select email,name from info;
-- 列數需相同

select id from depart 
union
select id from info;

-- 自動去重

select id from depart 
union all
select id from info;

-- 保留所有

小結

到目前為止，你已經掌握瞭如下相關指令（SQL語句）：

資料庫
資料表
資料行
- 增加
- 刪除
- 修改
- 查詢（各種變著花樣的查詢）

2.表關係

在開發專案時，需要根據業務需求去建立很多的表結構，以此來實現業務邏輯，一般表結構有三類：

單表，單獨一張表就可以將資訊儲存。
一對多，需要兩張表來儲存資訊，且兩張表存在 一對多 或 多對一關係。
多對多，需要三張表來儲存資訊，兩張單表 + 關係表，創造出兩個單表之間多對多關係。

在上述的表：一對多的 info.depart_id欄位、多對多的 boy_girl.boy_id 、girl_id 直接用整型儲存就可以，因為他們只要儲存關聯表的主鍵ID即可。

在開發中往往還會為他們新增一個 外來鍵約束，保證某一個列的值必須是其他表中的特定列已存在的值，例如：info.depart_id的值必須是 depart.id中已存在的值。

一對多示例：

create table depart(
    id int not null auto_increment primary key,
    title varchar(16) not null
)default charset=utf8;


create table info(
    id int not null auto_increment primary key,
    name varchar(16) not null,
    email varchar(32) not null,
    age int,
    depart_id int not null,
    constraint fk_info_depart foreign key (depart_id) references depart(id)
)default charset=utf8;

如果表結構已建立好了，額外想要增加外來鍵：

alter table info add constraint fk_info_depart foreign key info(depart_id) references depart(id);

刪除外來鍵：

alter table info drop foreign key fk_info_depart;

多對多示例：

create table boy(
    id int not null auto_increment primary key,
    name varchar(16) not null
)default charset=utf8;

create table girl(
    id int not null auto_increment primary key,
    name varchar(16) not null
)default charset=utf8;


create table boy_girl(
    id int not null auto_increment primary key,
    boy_id int not null,
    girl_id int not null,
    constraint fk_boy_girl_boy foreign key boy_girl(boy_id) references boy(id),
    constraint fk_boy_girl_girl foreign key boy_girl(girl_id) references girl(id)
)default charset=utf8;

如果表結構已建立好了，額外想要增加外來鍵：

alter table boy_girl add constraint fk_boy_girl_boy foreign key boy_girl(boy_id) references boy(id);
alter table boy_girl add constraint fk_boy_girl_girl foreign key boy_girl(girl_id) references girl(id);

刪除外來鍵：

alter table info drop foreign key fk_boy_girl_boy;
alter table info drop foreign key fk_boy_girl_girl;

在以後專案開發時，設計表結構及其關係的是一個非常重要的技能。一般專案開始開發的步驟：

需求調研
設計資料庫表結構（根據需求）
專案開發（寫程式碼）

大量的工作應該放在前2個步驟，前期的設計完成之後，後續的功能程式碼開發就比較簡單了。

案例：簡易版路飛學城

create database luffy default charset utf8 collate utf8_general_ci;

use luffy;

create table info(
    id int not null auto_increment primary key,
    username varchar(16) not null,
    mobile char(11) not null,
    password varchar(64) not null
)default charset=utf8;

create table course(
    id int not null auto_increment primary key,
    title varchar(16) not null
)default charset=utf8;


create table module(
    id int not null auto_increment primary key,
    title varchar(16) not null,
    course_id int not null,
    constraint fk_module_course foreign key (course_id) references course(id)
)default charset=utf8;


create table day(
    id int not null auto_increment primary key,
    title varchar(16) not null,
    module_id int not null,
    constraint fk_day_module foreign key (module_id) references module(id)
)default charset=utf8;


create table video(
    id int not null auto_increment primary key,
    title varchar(16) not null,
    day_id int not null,
    constraint fk_video_day foreign key (day_id) references day(id)
)default charset=utf8;

create table module_record(
	id int not null auto_increment primary key,
    user_id int not null,
    module_id int not null,
    constraint fk_user_id foreign key module_record(user_id) references info(id),
    constraint fk_module_id foreign key module_record(module_id) references module(id)
)default charset=utf8;

3. 授權

之前我們無論是基於Python程式碼 or 自帶客戶端去連線MySQL時，均使用的是 root 賬戶，擁有對MySQL資料庫操作的所有許可權。

如果有多個程式的資料庫都放在同一個MySQL中，如果程式都用root賬戶就存在風險了。

這種情況怎麼辦呢？

在MySQL中支援建立賬戶，並給賬戶分配許可權，例如：只擁有資料庫A操作的許可權、只擁有資料庫B中某些表的許可權、只擁有資料庫B中某些表的讀許可權等。

3.1 使用者管理

在MySQL的預設資料庫 mysql 中的 user 表中儲存著所有的賬戶資訊（含賬戶、許可權等）。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| day26              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
10 rows in set (0.00 sec)

mysql> select user,authentication_string,host from  mysql.user;
+----------------------------------+-------------------------------------------+-------------------------------+
| user                             | authentication_string                     | host                          |
+----------------------------------+-------------------------------------------+-------------------------------+
| root                             | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | localhost                     |
| mysql.session                    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost                     |
| mysql.sys                        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost                     |
+----------------------------------+-------------------------------------------+-------------------------------+
3 rows in set (0.00 sec)

建立和刪除使用者

create user '使用者名稱'@'連線者的IP地址' identified by '密碼';

create user wupeiqi1@127.0.0.1 identified by 'root123';
drop user wupeiqi1@127.0.0.1;

create user wupeiqi2@'127.0.0.%' identified by 'root123';
drop user wupeiqi2@'127.0.0.%';

create user wupeiqi3@'%' identified by 'root123';
drop user wupeiqi3@'%';

create user 'wupeiqi4'@'%' identified by 'root123';
drop user 'wupeiqi4'@'%';

修改使用者

rename user '使用者名稱'@'IP地址' to '新使用者名稱'@'IP地址';

rename user wupeiqi1@127.0.0.1 to wupeiqi1@localhost;

rename user 'wupeiqi1'@'127.0.0.1' to 'wupeiqi1'@'localhost';

修改密碼

set password for '使用者名稱'@'IP地址' = Password('新密碼')

set password for 'wupeiqi4'@'%' = Password('123123');

3.2 授權管理

建立好使用者之後，就可以為使用者進行授權了。

授權

grant 許可權 on 資料庫.表 to   '使用者'@'IP地址'

grant all privileges on *.* TO 'wupeiqi'@'localhost';         -- 使用者wupeiqi擁有所有資料庫的所有許可權
grant all privileges on day26.* TO 'wupeiqi'@'localhost';     -- 使用者wupeiqi擁有資料庫day26的所有許可權
grant all privileges on day26.info TO 'wupeiqi'@'localhost';  -- 使用者wupeiqi擁有資料庫day26中info表的所有許可權

grant select on day26.info TO 'wupeiqi'@'localhost';          -- 使用者wupeiqi擁有資料庫day26中info表的查詢許可權
grant select,insert on day26.* TO 'wupeiqi'@'localhost';      -- 使用者wupeiqi擁有資料庫day26所有表的查詢和插入許可權

grant all privileges on day26db.* to 'wupeiqi4'@'%';


注意：flush privileges;   -- 將資料讀取到記憶體中，從而立即生效。

對於許可權

all privileges  除grant外的所有許可權
select          僅查許可權
select,insert   查和插入許可權
...
usage                   無訪問許可權
alter                   使用alter table
alter routine           使用alter procedure和drop procedure
create                  使用create table
create routine          使用create procedure
create temporary tables 使用create temporary tables
create user             使用create user、drop user、rename user和revoke  all privileges
create view             使用create view
delete                  使用delete
drop                    使用drop table
execute                 使用call和儲存過程
file                    使用select into outfile 和 load data infile
grant option            使用grant 和 revoke
index                   使用index
insert                  使用insert
lock tables             使用lock table
process                 使用show full processlist
select                  使用select
show databases          使用show databases
show view               使用show view
update                  使用update
reload                  使用flush
shutdown                使用mysqladmin shutdown(關閉MySQL)
super                   􏱂􏰈使用change master、kill、logs、purge、master和set global。還允許mysqladmin􏵗􏵘􏲊􏲋除錯登陸
replication client      伺服器位置的訪問
replication slave       由複製從屬使用

對於資料庫和表

資料庫名.*            資料庫中的所有
資料庫名.表名          指定資料庫中的某張表
資料庫名.儲存過程名     指定資料庫中的儲存過程
*.*                  所有資料庫

檢視授權

show grants for '使用者'@'IP地址'

show grants for 'wupeiqi'@'localhost';
show grants for 'wupeiqi4'@'%';

取消授權

revoke 許可權 on 資料庫.表 from '使用者'@'IP地址'

revoke ALL PRIVILEGES on day26.* from 'wupeiqi'@'localhost';

revoke ALL PRIVILEGES on day26db.* from 'wupeiqi4'@'%';
注意：flush privileges;   -- 將資料讀取到記憶體中，從而立即生效。

一般情況下，在很多的正規公司，資料庫都是由 DBA 來統一進行管理，DBA為每個專案的資料庫建立使用者，並賦予相關的許可權。

總結

本節主要講解的三大部分的知識點：

常見SQL語句，專案開發中使用最頻繁的知識點。
表關係，專案開發前，專案表結構設計時必備知識點。
- 單表
- 一對多
- 多對多
授權，在MySQL中建立使用者並賦予相關許可權。

學術研究必備!EndNote 21大客戶授權版
2023-10-30
Oracle新建表空間、使用者及授權
2018-04-09
Oracle
關於表空間和表的關係
2011-05-02
事實表和維表關係
2017-06-19
oracle顯式授權和隱式授權
2012-02-10
Oracle
ASP.NET Core策略授權和 ABP 授權
2020-07-12
ASP.NET
關於微信公眾號靜默授權和非靜默授權的區別
2021-12-16
深入學習MySQL授權表
2010-07-16
MySql
有關係統整合中的統一認證和儘量統一的授權問題
2007-08-09
oracle-tom-表和段關係
2012-04-25
Oracle
刪除表空間及所有指向關係
2014-09-10
備份集和備份片之間的關係
2022-03-18
關於QQ授權登入
2018-03-27
讀到的"關於授權"
2013-10-28
關於SAP授權培訓
2007-07-04
gRPC四種模式、認證和授權實戰演示，必贊~~~
2021-07-13
RPC模式
專案經理必備的11種人際關係技能
2015-04-28
MySql建立使用者及授權
2007-05-22
MySql
Django（15）外來鍵和表關係
2021-05-17
Django
首選DNS和備用DNS的關係
2014-07-17
DNS
WebGL和OpenGL的區別及關係
2023-02-22
Web
Sql Server必備外掛--SQL Prompt
2016-01-25
SQLServer
表的關聯關係
2018-10-08
Oracle的表空間、使用者和使用者授權
2016-05-11
Oracle
抖音分享和授權(iOS)
2019-04-04
iOS
Django（59）驗證和授權
2021-06-11
Django
Paypal授權登入流程及實現
2021-09-06
使用者建立授權及刪除
2021-01-14
常見的授權錯誤及原因
2022-12-21
ORM建立表關係
2022-02-28
ORM
淺談MySQL中授權(grant)和撤銷授權(revoke)用法詳解
2017-03-21
MySql
授權機制與授權模型研究
2009-08-19
模型
sql profile和baseline的協作關係
2013-06-11
SQL
AppStore 關於賬號授權
2014-04-04
APP
1.7.6. 授權和撤銷管理許可權
2020-02-21
DB2授權和特權安全機制
2012-11-27
DB2
學術研究必備!EndNote 21大客戶授權版 endnote 21大客戶版
2024-02-20
Oracle建立使用者、角色、授權、建表
2018-04-27
Oracle