python--12、資料庫進階

来源:http://www.cnblogs.com/jinyudong/archive/2017/12/09/8011033.html
-Advertisement-
Play Games

SQL語句關鍵詞： #再次不做過多介紹 #示例中department為部門表，employee為員工表。# 多表連接查詢 外鏈接語法 交叉連接 不適用任何匹配條件，生成笛卡爾積第一個表的每一列對應後面表的所有列 內連接 inner：只連接匹配的行 找兩張表共有的部分，相當於利用笛卡爾積結果中篩選除了 ...


SQL語句關鍵詞：

#再次不做過多介紹

  1. 使用INSERT實現數據的插入
  2. UPDATE實現數據的更新
  3. 使用DELETE實現數據的刪除
  4. 使用SELECT查詢數據以及。

 

#示例中department為部門表，employee為員工表。#

多表連接查詢

外鏈接語法

select 欄位列 from 表1
inner|left|right join 表2 on 
表1.欄位 = 表2.欄位

交叉連接

不適用任何匹配條件，生成笛卡爾積
第一個表的每一列對應後面表的所有列

內連接 inner：只連接匹配的行

找兩張表共有的部分，相當於利用笛卡爾積結果中篩選除了正確的結果。
若一個表有而另一個表沒有，則不會被匹配到。

mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id; 
或者
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;

外鏈接之左連接 left：優先顯示左表全部記錄

在內連接的基礎上增加左邊有而右邊沒有的數據

mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;

外鏈接之右鏈接 right：優先顯示右表全部記錄

在內連接的基礎上增加右邊有而左邊沒有的數據

mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;

全外鏈接：顯示左右兩個表全部記錄

不管兩個表是左邊沒有還是右邊沒有，都顯示，沒有的數據用null填充。
MySQL不支持全外鏈接。但是可以使用左右連接間接實現

select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
;

#註意！ union與union all的區別：union會去掉相同的紀錄

符合條件連接查詢

以內連接的方式查詢，在對應查詢語句後加 where，order by等操作。

內連接，並過濾
select employee.name,department.name from employee inner join department
    on employee.dep_id = department.id
    where age > 25;

#以上面為基礎，增加排序
select employee.id,employee.name,employee.age,department.name from employee,department
    where employee.dep_id = department.id
    and age > 25
    order by age asc;

子查詢

將一個查詢語句嵌套在另一個查詢語句中。
內層查詢語句的結果，可以為外層查詢語句提供條件。
子查詢中可以包含：IN ,NOT IN ,ANY ,ALL ,EXISTS和NOT EXISTS等關鍵字。
還可以包含比較運算符：= , != , > , < 等。

帶in關鍵字的子查詢

# 查平均年齡25歲以上部門名
select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);

#查技術部員工名
select name from employee where dep_id in (select id from department where name='技術');

#查看哪些部門不超過一人
select name from department where id in (select dep_id from employee group by dep_id having count(id) <=1);

帶有比較運算符的子查詢

比較運算符：=、!=、>、>=、<、<=、<>

# 輸出大於所有人平均年齡的人的人名和年齡
select name,age from emp where age > (select avg(age)) from emp);

# 查詢大於部門內平均年齡的員工名和年齡。
select name,age from emp t1 
inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;

帶EXISTS關鍵字的子查詢

過濾條件使用EXISTS時，內層查詢語句不會反悔查詢記錄，只返回布爾值。true或false
返回true時外層語句才執行。
返回false時外層語句不執行。

#department表中存在dept_id=203，Ture
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=200);
+----+------------+--------+------+--------+


#department表中存在dept_id=205，False
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=204);
Empty set (0.00 sec)

查看MYSQL資料庫中所有用戶

mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

查看資料庫中具體某個用戶的許可權

mysql> show grants for 'cactiuser'@'%';

mysql> select * from mysql.user where user='cactiuser' \G

查看user表結構　需要具體的項可結合表結構來查詢

mysql> desc mysql.user;

grant語句

GRANT USAGE ON . TO 'jiudou'@'10.173.6.182' IDENTIFIED BY PASSWORD '9F4DCFD752EDB2DD095A080EEF9508444FF42894';
GRANT ALL PRIVILEGES ON stock_data. TO 'jiudou'@'10.173.6.182'

MySQL各種許可權

1. usage

連接（登陸）許可權，建立一個用戶，就會自動授予其usage許可權（預設授予）。

mysql> grant usage on . to ‘p1′@’localhost’ identified by ‘123′;

該許可權只能用於資料庫登陸，不能執行任何操作；且usage許可權不能被回收，也即REVOKE用戶並不能刪除用戶。

2. select

必須有select的許可權，才可以使用select table

mysql> grant select on pyt.* to ‘p1′@’localhost’;

mysql> select * from shop;

3. create

必須有create的許可權，才可以使用create table

mysql> grant create on pyt.* to ‘p1′@’localhost’;

4. create routine

必須具有create routine的許可權，才可以使用{create |alter|drop} {procedure|function}

mysql> grant create routine on pyt.* to ‘p1′@’localhost’;

當授予create routine時，自動授予EXECUTE, ALTER ROUTINE許可權給它的創建者：

mysql> show grants for ‘p1′@’localhost’;

+—————————————————————————+

Grants for p1@localhost

+————————————————————————–+

| GRANT USAGE ON . TO ‘p1′@’localhost’ IDENTIFIED BY PASSWORD ‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257′ |

| GRANT SELECT, CREATE, CREATE ROUTINE ON pyt.* TO ‘p1′@’localhost’|

| GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE pyt.pro_shop1 TO ‘p1′@’localhost’ |

+————————————————————————————-+

5. create temporary tables(註意這裡是tables，不是table)

必須有create temporary tables的許可權，才可以使用create temporary tables.

mysql> grant create temporary tables on pyt.* to ‘p1′@’localhost’;

[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt

mysql> create temporary table tt1(id int);

6. create view

必須有create view的許可權，才可以使用create view

mysql> grant create view on pyt.* to ‘p1′@’localhost’;

mysql> create view v_shop as select price from shop;

7. create user

要使用CREATE USER，必須擁有mysql資料庫的全局CREATE USER許可權，或擁有INSERT許可權。

mysql> grant create user on . to ‘p1′@’localhost’;

或：mysql> grant insert on . to p1@localhost;

8. insert

必須有insert的許可權，才可以使用insert into ….. values….

9. alter

必須有alter的許可權，才可以使用alter table

alter table shop modify dealer char(15);

10. alter routine

必須具有alter routine的許可權，才可以使用{alter |drop} {procedure|function}

mysql>grant alter routine on pyt.* to ‘p1′@’ localhost ‘;

mysql> drop procedure pro_shop;

Query OK, 0 rows affected (0.00 sec)

mysql> revoke alter routine on pyt.* from ‘p1′@’localhost’;

[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt

mysql> drop procedure pro_shop;

ERROR 1370 (42000): alter routine command denied to user ‘p1′@’localhost’ for routine ‘pyt.pro_shop’

11. update

必須有update的許可權，才可以使用update table

mysql> update shop set price=3.5 where article=0001 and dealer=’A';

12. delete

必須有delete的許可權，才可以使用delete from ….where….(刪除表中的記錄)

13. drop

必須有drop的許可權，才可以使用drop database db_name; drop table tab_name;

drop view vi_name; drop index in_name;

14. show database

通過show database只能看到你擁有的某些許可權的資料庫，除非你擁有全局SHOW DATABASES許可權。

對於p1@localhost用戶來說，沒有對mysql資料庫的許可權，所以以此身份登陸查詢時，無法看到mysql資料庫：

mysql> show databases;

+——————–+

| Database |

+——————–+

| information_schema|

| pyt |

| test |

+——————–+

15. show view

必須擁有show view許可權，才能執行show create view。

mysql> grant show view on pyt.* to p1@localhost;

mysql> show create view v_shop;

16. index

必須擁有index許可權，才能執行[create |drop] index

mysql> grant index on pyt.* to p1@localhost;

mysql> create index ix_shop on shop(article);

mysql> drop index ix_shop on shop;

17. excute

執行存在的Functions,Procedures

mysql> call pro_shop1(0001,@a)；

+———+

| article |

+———+

| 0001 |

| 0001 |

+———+

mysql> select @a;

+——+

| @a |

+——+

| 2 |

+——+

18. lock tables

必須擁有lock tables許可權，才可以使用lock tables

mysql> grant lock tables on pyt.* to p1@localhost;

mysql> lock tables a1 read;

mysql> unlock tables;

19. references

有了REFERENCES許可權，用戶就可以將其它表的一個欄位作為某一個表的外鍵約束。

20. reload

必須擁有reload許可權，才可以執行flush [tables | logs | privileges]

mysql> grant reload on pyt.* to p1@localhost;

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

mysql> grant reload on . to ‘p1′@’localhost’;

Query OK, 0 rows affected (0.00 sec)

mysql> flush tables;

21. replication client

擁有此許可權可以查詢master server、slave server狀態。

mysql> show master status;

ERROR 1227 (42000): Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation

mysql> grant Replication client on . to p1@localhost;

或：mysql> grant super on . to p1@localhost;

mysql> show master status;

+——————+———-+————–+——————+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.000006 | 2111 | | |

+——————+———-+————–+——————+

mysql> show slave status;

22. replication slave

擁有此許可權可以查看從伺服器，從主伺服器讀取二進位日誌。

mysql> show slave hosts;

ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation

mysql> show binlog events;

ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation

mysql> grant replication slave on . to p1@localhost;

mysql> show slave hosts;

Empty set (0.00 sec)

mysql>show binlog events;

+—————+——-+—————-+———–+————-+————–+

| Log_name | Pos | Event_type | Server_id| End_log_pos|Info | +—————+——-+————–+———–+————-+—————+

| mysql-bin.000005 | 4 | Format_desc | 1 | 98 | Server ver: 5.0.77-log, Binlog ver: 4 | |mysql-bin.000005|98|Query|1|197|use mysql; create table a1(i int)engine=myisam|

……………………………………

23. Shutdown

關閉MySQL：

[mysql@mydev ~]$ mysqladmin shutdown

重新連接：

[mysql@mydev ~]$ mysql

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

[mysql@mydev ~]$ cd /u01/mysql/bin

[mysql@mydev bin]$ ./mysqld_safe &

[mysql@mydev bin]$ mysql

24. grant option

擁有grant option，就可以將自己擁有的許可權授予其他用戶（僅限於自己已經擁有的許可權）

mysql> grant Grant option on pyt.* to p1@localhost;

mysql> grant select on pyt.* to p2@localhost;

25. file

擁有file許可權才可以執行 select ..into outfile和load data infile…操作，但是不要把file, process, super許可權授予管理員以外的賬號，這樣存在嚴重的安全隱患。

mysql> grant file on . to p1@localhost;

mysql> load data infile ‘/home/mysql/pet.txt’ into table pet;

26. super

這個許可權允許用戶終止任何查詢；修改全局變數的SET語句；使用CHANGE MASTER，PURGE MASTER LOGS。

mysql> grant super on . to p1@localhost;

mysql> purge master logs before ‘mysql-bin.000006′;

27. process

通過這個許可權，用戶可以執行SHOW PROCESSLIST和KILL命令。預設情況下，每個用戶都可以執行SHOW PROCESSLIST命令，但是只能查詢本用戶的進程。

mysql> show processlist;

+—-+——+———–+——+———+——+——-+——————+

| Id | User | Host | db | Command | Time | State | Info |

+—-+——+———–+——+———+——+——-+——————+

| 12 | p1 | localhost | pyt | Query | 0 | NULL | show processlist |

+—-+——+———–+——+———+——+——-+——————+

另外，管理許可權（如 super， process， file等）不能夠指定某個資料庫，on後面必須跟*.*

mysql> grant super on pyt.* to p1@localhost;

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

mysql> grant super on . to p1@localhost;

Query OK, 0 rows affected (0.01 sec)

 

  
 

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

-Advertisement-
Play Games
更多相關文章
  • 項目實戰6—Mysql實現企業級日誌管理、備份與恢復實戰
    Mysql實現企業級日誌管理、備份與恢復實戰 環境背景：隨著業務的發展，公司業務和規模不斷擴大，網站積累了大量的用戶信息和數據，對於一家互聯網公司來說，用戶和業務數據是根基。一旦公司的數據錯亂或者丟失，對於互聯網公司而言就等於說是滅頂之災，為防止系統出現操作失誤或系統故障導致數據丟失，公司要求加強用 ...
  • kali 系統安裝中遇到的那些坑
    我用的vmware 安裝kali，期間遇到不少小坑： NO1： 安裝一半提示安裝失敗，停在了這裡，經各種百度，解決，分區大小最好大於25G才能過（不知道什麼原理） NO2： 安裝完之後用ssh鏈接一直伺服器拒絕密碼，賬號密碼都是正確的，趕緊確定下ssh伺服器動了沒，然後找到 /etc/ssh/ssh ...
  • Ubuntu 16.04 主題美化及常用軟體安裝
    一、主題美化 系統清理 系統更新： 安裝完系統之後，需要更新一些補丁。Ctrl+Alt+T調出終端，執行一下代碼： 卸載libreOffice： libreOffice是ubuntu自帶的開源office軟體，我們一般習慣用WPS，在第二部分會說明WPS在Ubuntu中的安裝 卸載不常用軟體： 刪除 ...
  • SQL Server CLR 使用 C# 自定義存儲過程和觸發器
    這一篇博客接著上一篇博客繼續介紹 SQL CLR Stored Procedure 和 CLR Trigger， 上一篇博客介紹了 SQL CLR Function 的使用，以及 CLR 程式集的註冊和 CLR Function 的註冊。 我的上一篇博客：SQL Server CLR 使用 C# 自 ...
  • 學習之Oracle_01
    ☞ | (Oracle入門操作) | ☜ ☞ | (Oracle表空間) | ☜ ☞ | (Oracle賬號密碼) | ☜ ...
  • mysql 雙機熱備註意事項
    上一篇文章已經介紹過 主從複製， 本文對主從複製只是簡單描述，如對主從複製不清楚的，可以先看上一篇文章 主從複製 一：介紹 mysql版本：5.7.20 第一個主服伺服器ip:192.168.71.139 第二個主服伺服器ip:192.168.71.141 二：配置 第一臺主伺服器192.168.7 ...
  • SQL Server 2016 快照代理過程分析
    概述 快照代理準備已發佈表的架構和初始數據文件以及其他對象、存儲快照文件並記錄分發資料庫中的同步信息。 快照代理在分發伺服器上運行；SQLServer2016版本對快照代理做了一些比較好的優化，接下來詳細瞭解一下快照的執行過程。 一、快照代理文件 在執行快照作業是會在指定的快照目錄生成4種類型的文件 ...
  • 本地搭建wamp出現You don’t have permission to access/on this server提示的解決方法
    本地搭建wamp，當輸入http://localhost/，apache出現You don't have permission to access/on this server.的提示，解決辦法： 找到httpd.conf，用記事本打開httpd.conf，然後將 <Directory /> Opt ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類