python 多表查询_Python基础学习教程—MySQL常见的多表联查

一、 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;

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

| Field | Type | Null | Key | Default | Extra |

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

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

| name | varchar(16) | NO | | NULL | |

| 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

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;