Python基礎學習教程—MySQL常見的多表聯查

08-21

5 人贊了文章

一、 MySQL的多表聯查

==================================

表之間的關係有：1對1 1對多多對多

1. 嵌套查詢:一個查詢的結果是另外sql查詢的條件：

如：查詢stu表中年齡最大的是誰？

mysql> select * from stu where age=(select max(age) from stu);

mysql> select * from stu where age in(select max(age) from stu); --（子查詢結果是多條時使用in查詢）

+----+------+------+-----+----------+

| id | name | age | sex | classid |

+----+------+------+-----+----------+

| 14 | abc | 33 | w | python01 |

+----+------+------+-----+----------+

1 row in set (0.01 sec)

2. where關聯查詢

已知：員工personnel表和部門department表，其中員工表中的did欄位為部門表id主鍵關聯。

查詢所有員工信息，並顯示所屬部門名稱

要求：顯示欄位：員工id 部門姓名

mysql> select p.id,d.name,p.name from personnel p,department d where p.did = d.id;

+----+-----------+-----------+

| id | name | name |

+----+-----------+-----------+

| 2 | 人事部 | 李玉剛 |

| 10 | 人事部 | 阿杜 |

| 4 | 市場部 | 劉歡 |

。。。。

3. 連接join查詢

左聯：left join

右聯：right join

內聯：inner join

已知如下表所示，商品類別信息表（具有兩層類別關係，通過pid表示，0表示一級類別）

mysql> select * from type;

+----+-----------+------+

| id | name | pid |

+----+-----------+------+

| 1 | 服裝 | 0 |

| 2 | 數碼 | 0 |

| 3 | 男裝 | 1 |

| 4 | 手機 | 2 |

| 5 | 相機 | 2 |

| 6 | 電腦 | 2 |

| 7 | 女裝 | 1 |

| 8 | 童裝 | 1 |

| 9 | 食品 | 0 |

| 10 | 零食 | 9 |

| 11 | 特產 | 9 |

| 12 | 休閑裝 | 1 |

+----+-----------+------+

12 rows in set (0.00 sec)

mysql> desc type;

+-------+------------------+------+-----+---------+----------------+

+-------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| pid | int(10) unsigned | YES | | NULL | |

+-------+------------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

-- 查詢二級類別信息，並關聯出他們的父類別名稱

mysql> select t1.id,t1.name,t2.name from type t1,type t2 where t1.pid!=0 and t1.pid=t2.id;

+----+-----------+--------+

| id | name | name |

+----+-----------+--------+

| 3 | 男裝 | 服裝 |

| 4 | 手機 | 數碼 |

| 5 | 相機 | 數碼 |

| 6 | 電腦 | 數碼 |

| 7 | 女裝 | 服裝 |

| 8 | 童裝 | 服裝 |

| 10 | 零食 | 食品 |

| 11 | 特產 | 食品 |

| 12 | 休閑裝 | 服裝 |

+----+-----------+--------+

9 rows in set (0.01 sec)

--統計每個一級類別下都有多少個子類別。

mysql> select t1.id,t1.name,count(t2.id) from type t1,type t2 where t1.pid=0 and t1.id=t2.pid group by t1.id;

+----+--------+--------------+

| id | name | count(t2.id) |

+----+--------+--------------+

| 1 | 服裝 | 4 |

| 2 | 數碼 | 3 |

| 9 | 食品 | 2 |

+----+--------+--------------+

3 rows in set (0.00 sec)

二、MySQL的其他操作

==================================

1. MySQL的表複製

複製表結構

mysql> create table 目標表名 like 原表名;

複製表數據

mysql> insert into 目標表名 select * from 原表名;

2. *數據表的索引

創建索引

CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

刪除索引

DROP INDEX index_name ON talbe_name

3. mysql視圖

創建視圖:

mysql> create view v_t1 as select * from t1 where id>4 and id<11;

Query OK, 0 rows affected (0.00 sec)

view視圖的幫助信息:

mysql> ? view

ALTER VIEW

CREATE VIEW

DROP VIEW

查看視圖:

mysql> show tables;

刪除視圖v_t1:

mysql> drop view v_t1;

4. MySQL的內置函數

字元串處理函數

---------------------------------------------

*concat(s1,s2,…Sn) 連接s1,s2..Sn為一個字元串

insert(str,x,y,instr)將字元串str從第xx位置開始，y字元串的子字元串替換為字元串str

lower(str)將所有的字元串變為小寫

upper(str)將所有的字元串變為大寫

left(str,x)返回字元串中最左邊的x個字元

rigth(str,y)返回字元串中最右邊的x個字元

lpad(str,n,pad)用字元串pad對str最左邊進行填充，直到長度為n個字元串長度

rpad(str,n,pad)用字元串pad對str最右邊進行填充，直到長度為n個字元串長度

trim(str) 去掉左右兩邊的空格

ltrim(str) 去掉字元串str左側的空格

rtrim(str) 去掉字元串str右側的空格

repeat(str,x) 返回字元串str重複x次

replace(str,a,b)將字元串的的a替換成b

strcmp(s1,s2) 比較字元串s1和s2

substring(s,x,y)返回字元串指定的長度

*length(str) 返回值為字元串str 的長度

數值函數

-----------------------------------------------------

*abs(x) 返回x的絕對值

ceil(x) 返回大於x的最小整數值

floor(x) 返回小於x的最大整數值

mod(x,y) 返回x/y的取余結果

*rand() 返回0~1之間的隨機數

*round(x,y)返回參數x的四捨五入的有y位小數的值

truncate(x,y) 返回x截斷為y位小數的結果

日期和時間函數

---------------------------------------------------

curdate() 返回當前日期,按照』YYYY-MM-DD』格式

curtime() 返回當前時間,當前時間以HH:MM:SS

*now() 返回當前日期和時間,

*unix_timestamp(date) 返回date時間的unix時間戳

from_unixtime(unix_timestamp[,format]) 返回unix時間的時間

week(date) 返回日期是一年中的第幾周

year(date) 返回日期的年份

hour(time) 返回time的小時值

minute(time) 返回日time的分鐘值

monthname(date) 返回date的月份

*date_fomat(date,fmt) 返回按字元串fmt格式化日期date值

date_add(date,INTERVAL,expr type) 返回一個日期或者時間值加上一個時間間隔的時間值

*datediff(expr,expr2) 返回起始時間和結束時間的間隔天數

//統計時間戳647583423距離當前時間相差天數（生日天數（不考慮年份））

mysql> select datediff(date_format(from_unixtime(647583423),"2017-%m-%d %h:%i:%s"),now());

其他常用函數

------------------------------------------------------

*database() 返回當前資料庫名

version() 返回當前伺服器版本

user() 返回當前登陸用戶名

inet_aton 返回當前IP地址的數字表示 inet_aton("192.168.80.250");

inet_ntoa(num) 返回當前數字表示的ip inet_ntoa(3232256250);

*password(str) 返回當前str的加密版本

*md5(str) 返回字元串str的md5值

5. MySQL的事務處理

關閉自動提交功能（開啟手動事務）

mysql> set autocommit=0;

從表t1中刪除了一條記錄

mysql> delete from t1 where id=11;

此時做一個p1還原點:

mysql> savepoint p1;

再次從表t1中刪除一條記錄:

mysql> delete from t1 where id=10;

再次做一個p2還原點:

mysql> savepoint p2;

此時恢復到p1還原點，當然後面的p2這些還原點自動會失效:

mysql> rollback to p1;

退回到最原始的還原點:

mysql> rollback;

回滾

mysql> commit;

事務提交

開啟自動事務提交（關閉手動事務）

mysql> set autocommit=1;

6. MySQL的觸發器

格式：1、觸發器的定義：

CREATE TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt

說明：

# trigger_name：觸發器名稱

# trigger_time:觸發時間，可取值：BEFORE或AFTER

# trigger_event：觸發事件，可取值：INSERT、UPDATE或DELETE。

# tb1_name：指定在哪個表上

# trigger_stmt：觸發處理SQL語句。

示例：

mysql> delimiter $$

mysql> create trigger del_stu before delete on stu for each row

-> begin

-> insert into stu_bak values(old.id,old.name,old.sex,old.age,old.addtime);

-> end;

-> $$

Query OK, 0 rows affected (0.05 sec)

mysql> delimiter ;

7. mysql日誌

開啟日誌：在mysql配置文件中開啟：log-bin=mysql-bin

查看bin-log日誌:

mysql>show binary logs;

查看最後一個bin-log日誌:

mysql>show master status;

此時就會多一個最新的bin-log日誌

mysql>flush logs;

查看最後一個bin日誌.

mysql>show master status;

mysql>reset master;

清空所有的bin-log日誌

執行查看bin-log日誌

備份數據:

mysqldump -uroot -pwei test -l -F >/tmp/test.sql

其中：-F即flush logs，可以重新生成新的日誌文件，當然包括log-bin日誌

// Linux關閉MySQL的命令

$mysql_dir/bin/mysqladmin -uroot -p shutdown

// linux啟動MySQL的命令

$mysql_dir/bin/mysqld_safe &

8、有關慢查詢操作：

開戶和設置慢查詢時間:

vi /etc/my.cnf

log_slow_queries=slow.log

long_query_time=5

查看設置後是否生效

mysql> show variables like "%quer%";

慢查詢次數:

mysql> show global status like "%quer%";

9 資料庫的恢復

1. 首先恢復最後一次的備份完整數據

[root@localhost mnt]# mysql -u root -p mydemo<mydemo_2017-7-26.sql

Enter password:

2. 查看bin-log日誌

[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000009;

查找到恢復的節點

3. 執行bin-log日誌文件，恢復最後一塊的增量數據。

[root@localhost data]# mysqlbinlog --no-defaults --stop-position="802" mysql-bin.000009|mysql -u root -p123456 mydemo;