千锋教育-做有情怀、有良心、有品质的IT职业教育机构

400-811-9990
当前位置:千锋视频教程 >  python视频教程  >  Python技术学习之数据库多表联合查询

Python技术学习之数据库多表联合查询

时间:2018-05-31 15:32     来源:千锋视频教程 作者:千锋老师

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

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

1row 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 andt1.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. *数据表的索引

创建索引

CREATEINDEX index_name ON table_name (column_list)

CREATEUNIQUE INDEX index_name ON table_name (column_list)

删除索引

DROPINDEX index_name ON talbe_name

3. mysql视图

创建视图:

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

QueryOK, 0 rows affected (0.00 sec)

view视图的帮助信息:

mysql>? view

ALTERVIEW

CREATEVIEW

DROPVIEW

查看视图:

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,exprtype) 返回一个日期或者时间值加上一个时间间隔的时间值

*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_timetrigger_event

ONtbl_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_bakvalues(old.id,old.name,old.sex,old.age,old.addtime);

->end;

->$

QueryOK, 0 rows affected (0.05 sec)

mysql>delimiter ;

7. mysql日志

开启日志: 在mysql配置文件中开启:log-bin=mysql-bin

查看bin-log日志:

mysql>showbinary logs;

查看最后一个bin-log日志:

mysql>showmaster status;

此时就会多一个最新的bin-log日志

mysql>flushlogs;

查看最后一个bin日志.

mysql>showmaster status;

mysql>resetmaster;

清空所有的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@localhostmnt]# mysql -u root -p mydemo<mydemo_2017-7-26.sql

Enterpassword:

2.查看bin-log日志

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

查找到恢复的节点

3.执行bin-log日志文件,恢复最后一块的增量数据。

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

千锋Python培训课程还有严格、科学、负责的教学就业管理制度,班主任、职业规划师全程跟班,把握每位学员的学习状态,并有专业的职业素养课和就业指导课,确保教学及就业质量。还有大量的免费Python视频教程帮助学员快速学习,更可以免费加入千锋开发者联盟,为会员提供免费技术支持及终身就业服务,免费参加千锋举办的各类技术沙龙和活动!

  • 北京天丰利校区(总部)地址:北京市海淀区宝盛北里西区28号天丰利商城4层
    北京沙河校区:北京市昌平区沙阳路18号北京科技职业技术学院广场服务楼2、3层
    咨询电话:400-811-9990
    面授课程:HTML5大前端培训、JavaEE+分布式开发培训、Python全栈+人工智能培训、全链路UI/UE设计培训、物联网+嵌入式培训、大数据+人工智能培训、全栈软件测试培训、PHP全栈+服务器集群培训、云计算+信息安全培训、Unity游戏开发培训、红帽RHCE认证、Android培训、iOS培训、好程序员
  • 深圳西部硅谷校区地址:深圳市宝安区宝安大道5010号深圳西部硅谷B座A区605-619
    深圳大学城校区地址:深圳市南山区留仙大道1201号大学城创客小镇16栋3楼
    咨询电话: 0755-23015275/23015546-801(硅谷) 0755-86660670-801(大学城)
    面授课程:HTML5大前端培训、JavaEE+分布式开发培训、Python全栈+人工智能培训、全链路UI/UE设计培训、物联网+嵌入式培训、大数据+人工智能培训、全栈软件测试培训、PHP全栈+服务器集群培训、云计算+信息安全培训、Unity游戏开发培训、红帽RHCE认证、Android培训、iOS培训、好程序员
  • 上海校区地址:上海市宝山区同济支路199号智慧七立方3号楼2-4层
    咨询电话:400-811-9990 021-65233829-609
    面授课程:HTML5大前端培训、JavaEE+分布式开发培训、Python全栈+人工智能培训、全链路UI/UE设计培训、物联网+嵌入式培训、大数据+人工智能培训、全栈软件测试培训、PHP全栈+服务器集群培训、云计算+信息安全培训、Unity游戏开发培训、红帽RHCE认证、Android培训、iOS培训、好程序员
  • 郑州校区地址:郑州市二七区航海中路60号海为科技园C区10层、12层
    咨询电话:0371-55191750
    面授课程:HTML5大前端培训、JavaEE+分布式开发培训、Python全栈+人工智能培训、全链路UI/UE设计培训、物联网+嵌入式培训、大数据+人工智能培训、全栈软件测试培训、PHP全栈+服务器集群培训、云计算+信息安全培训、Unity游戏开发培训、红帽RHCE认证、Android培训、iOS培训、好程序员
  • 广州校区地址:广州市天河区元岗路310号智汇park创意园E座5层
    咨询电话:020-22119207
    面授课程:HTML5大前端培训、JavaEE+分布式开发培训、Python全栈+人工智能培训、全链路UI/UE设计培训、物联网+嵌入式培训、大数据+人工智能培训、全栈软件测试培训、PHP全栈+服务器集群培训、云计算+信息安全培训、Unity游戏开发培训、红帽RHCE认证、Android培训、iOS培训、好程序员
  • 大连校区地址:辽宁省大连市高新园区爱贤街10号大连设计城A座901
    咨询电话:400-811-9990
    面授课程:HTML5大前端培训、JavaEE+分布式开发培训、Python全栈+人工智能培训、全链路UI/UE设计培训、物联网+嵌入式培训、大数据+人工智能培训、全栈软件测试培训、PHP全栈+服务器集群培训、云计算+信息安全培训、Unity游戏开发培训、红帽RHCE认证、Android培训、iOS培训、好程序员
  • 武汉金融港校区地址:武汉市东湖高新技术开发区光谷大道77号金融港B18栋3楼
    武汉智慧园校区地址:武汉市东湖高新技术开发区光谷大道61号智慧园21栋2楼
    咨询电话:027-59313371
    面授课程:HTML5大前端培训、全链路UI/UE设计培训、PHP全栈+服务器集群培训、JavaEE+分布式开发培训、大数据+人工智能培训、 Unity游戏开发培训、Python培训、云计算+信息安全培训、全栈软件测试培训、智能物联网+嵌入式培训、Android培训、iOS培训
  • 成都校区地址:成都市武侯区科华北路62号力宝大厦N(北楼)18楼
    咨询电话:400-811-9990 028-83178771
    面授课程:HTML5大前端培训、JavaEE+分布式开发培训、Python全栈+人工智能培训、全链路UI/UE设计培训、物联网+嵌入式培训、大数据+人工智能培训、全栈软件测试培训、PHP全栈+服务器集群培训、云计算+信息安全培训、Unity游戏开发培训、红帽RHCE认证、Android培训、iOS培训、好程序员
  • 西安校区地址:西安市雁塔区高新六路52号立人科技C座西区4楼
    咨询电话:029-85363390
    面授课程:HTML5大前端培训、JavaEE+分布式开发培训、Python全栈+人工智能培训、全链路UI/UE设计培训、物联网+嵌入式培训、大数据+人工智能培训、全栈软件测试培训、PHP全栈+服务器集群培训、云计算+信息安全培训、Unity游戏开发培训、红帽RHCE认证、Android培训、iOS培训、好程序员
  • 杭州校区地址:浙江省杭州市江干区九堡旺田书画城A座4层
    咨询电话:400-811-9990 0571-86893632
    面授课程:HTML5大前端培训、JavaEE+分布式开发培训、Python全栈+人工智能培训、全链路UI/UE设计培训、物联网+嵌入式培训、大数据+人工智能培训、全栈软件测试培训、PHP全栈+服务器集群培训、云计算+信息安全培训、Unity游戏开发培训、红帽RHCE认证、Android培训、iOS培训、好程序员
  • 青岛校区地址:青岛市市北区龙城路卓越世纪中心3号楼8层801
    咨询电话:0532-80911190
    面授课程:HTML5大前端培训、JavaEE+分布式开发培训、Python全栈+人工智能培训、全链路UI/UE设计培训、物联网+嵌入式培训、大数据+人工智能培训、全栈软件测试培训、PHP全栈+服务器集群培训、云计算+信息安全培训、Unity游戏开发培训、红帽RHCE认证、Android培训、iOS培训、好程序员
  • 重庆校区地址:重庆市高新区科园一路2号大西洋国际12-1
    咨询电话:400-811-9990 023-68883009
    面授课程:HTML5大前端培训、JavaEE+分布式开发培训、Python全栈+人工智能培训、全链路UI/UE设计培训、物联网+嵌入式培训、大数据+人工智能培训、全栈软件测试培训、PHP全栈+服务器集群培训、云计算+信息安全培训、Unity游戏开发培训、红帽RHCE认证、Android培训、iOS培训、好程序员
  • 长沙校区地址:湖南省长沙市岳麓区麓谷企业广场A2栋三单元306号
    咨询电话:0731-85513210
    面授课程:HTML5大前端培训、JavaEE+分布式开发培训、Python全栈+人工智能培训、全链路UI/UE设计培训、物联网+嵌入式培训、大数据+人工智能培训、全栈软件测试培训、PHP全栈+服务器集群培训、云计算+信息安全培训、Unity游戏开发培训、红帽RHCE认证、Android培训、iOS培训、好程序员
  • 哈尔滨校区地址:哈尔滨市松北区创新一路699号 科技创新城19号楼B座五楼
    咨询电话:400-811-9990/0451-87173191
    面授课程:HTML5大前端培训、JavaEE+分布式开发培训、Python全栈+人工智能培训、全链路UI/UE设计培训、物联网+嵌入式培训、大数据+人工智能培训、全栈软件测试培训、PHP全栈+服务器集群培训、云计算+信息安全培训、Unity游戏开发培训、红帽RHCE认证、Android培训、iOS培训、好程序员
  • 南京校区地址:南京市建邺区应天大街780号应天智汇产业园弘辉园1幢2楼
    咨询电话:400-811-9990
    面授课程:HTML5大前端培训、JavaEE+分布式开发培训、Python全栈+人工智能培训、全链路UI/UE设计培训、物联网+嵌入式培训、大数据+人工智能培训、全栈软件测试培训、PHP全栈+服务器集群培训、云计算+信息安全培训、Unity游戏开发培训、红帽RHCE认证、Android培训、iOS培训、好程序员
  • 千锋教育服务号

    了解千锋动态
    关注千锋教育服务号

  • 千锋教育移动站

    扫一扫快速进入
    千锋移动端页面

  • 千锋互联服务号

    扫码匿名提建议
    直达CEO信箱