mysql多个表增删改查6_多表内容的增删改查

笛卡尔积

将两表所有的数据一一对应,生成一张大表 不建议

select * from dep,emp; #两个表拼一起

select * from dep,emp where dep.id = emp.dep_id; #找到两表之间对应的关系记录

select * from dep,emp where dep.id = emp.dep_id and dep.name='技术'; #筛选部门名称为技术的大表中的记录

select emp.name from dep,emp where dep.id = emp.dep_id and dep.name='技术'; #拿到筛选后的记录的员工姓名字段数据

连表查询

inner join 内连接

select * from 表名 inner join 表2 on dep.id=emp.dep_id;

第一步:连表

select * from dep inner join emp on dep.id=emp.dep_id;

第二步: 过滤

select * from dep inner join emp on dep.id=emp.dep_id where dep.name='技术';

第三步:找对应字段数据

select emp.name from dep inner join emp on dep.id=emp.dep_id where dep.name='技术';

left join 左连接

(left join左边的表为主表,主表记录必须全部显示,辅表没办法对应上的,就通过null来补全)

select * from dep left join emp on dep.id=emp.dep_id;

right join 右连接

select * from dep right join emp on dep.id=emp.dep_id;

union 全连接

mysql> select * from dep left join emp on dep.id=emp.dep_id

-> union

-> select * from dep right join emp on dep.id=emp.dep_id;

子查询

(一个查询结果集作为另一个查询的条件)

select name from emp where dep_id = (select id from dep where name = '技术');

左连接 ，右连接，内连接和全外连接的4者区别

基本定义：

left join (左连接)：返回包括左表中的所有记录和右表中连接字段相等的记录。

right join (右连接)：返回包括右表中的所有记录和左表中连接字段相等的记录。

inner join (等值连接或者叫内连接)：只返回两个表中连接字段相等的行。

full join (全外连接)：返回左右表中所有的记录和左右表中连接字段相等的记录。

小练习1

书名 作者 出版社 价格 出版日期

倚天屠龙记 egon 北京工业地雷出版社 70 2019-7-1

九阳神功 alex 人民音乐不好听出版社 5 2018-7-4

九阴真经 yuan 北京工业地雷出版社 62 2017-7-12

九阴白骨爪 jinxin 人民音乐不好听出版社 40 2019–8-7

独孤九剑 alex 北京工业地雷出版社 12 2017-9-1

降龙十巴掌 egon 知识产权没有用出版社 20 2019-7-5

葵花宝典 yuan 知识产权没有用出版社 33 2019–8-2

0.建表book，并向表中插入数据

create table book(

书名 varchar(12),

作者 varchar(12),

出版社 varchar(12),

价格 int unsigned not null,

出版日期 date

);

insert into book values

('倚天屠龙记','egon','北京工业地雷出版社',70,'2019-7-1'),

('九阳神功','alex','人民音乐不好听出版社',5,'2018-7-4'),

('九阴真经','yuan','北京工业地雷出版社',62,'2017-7-12'),

('九阴白骨爪','jinxin','人民音乐不好听出版社',40,'2019–8-7'),

('独孤九剑','alex','北京工业地雷出版社',12,'2017-9-1'),

('降龙十巴掌','egon','知识产权没有用出版社',20,'2019-7-5'),

('葵花宝典','yuan','知识产权没有用出版社',33,'2019–8-2');

1.查询 egon 写的所有书和价格

select 书名,价格 from book where 作者='egon';

2.找出最贵的图书的价格

select 书名,价格 from book ;

3.求所有图书的均价

4.将所有图书按照出版日期排序

5.查询alex写的所有书的平均价格

6.查询人民音乐不好听出版社出版的所有图书

7.查询人民音乐出版社出版的alex写的所有图书和价格

8.找出出版图书均价最高的作者

9.找出最新出版的图书的作者和出版社

10.显示各出版社出版的所有图书

11.查找价格最高的图书，并将它的价格修改为50元

12.删除价格最低的那本书对应的数据

13.将所有alex写的书作业修改成alexsb

14.select year(publish_date) from book

自己研究上面sql语句中的year函数的功能，完成需求：

将所有2017年出版的图书从数据库中删除

15.有文件如下，使用python写代码将文件中的数据写入数据库

学python从开始到放弃|alex|人民大学出版社|50|2018-7-1

学mysql从开始到放弃|egon|机械工业出版社|60|2018-6-3

学html从开始到放弃|alex|机械工业出版社|20|2018-4-1

学css从开始到放弃|wusir|机械工业出版社|120|2018-5-2

学js从开始到放弃|wusir|机械工业出版社|100|2018-7-30

答案

#第一步手动创建表

# create table book(

# id int primary key auto_increment,

# book_name char(20) not null,

# author char(12) not null,

# press char(20) not null,

# price float(6,2),

# pub_date date

# );

# 写入数据

import pymysql

conn = pymysql.Connection(host='127.0.0.1', user='root', password="",

database='daycs')

cur = conn.cursor()#右边

with open('book.txt',encoding='utf-8') as f:

try:

for line in f:

line = line.strip()

if '\t' in line:

lst = line.split('\t')

elif '|' in line:

lst = line.split('|')

sql = 'insert into book(book_name,author,press,price,pub_date) values (%s,%s,%s,%s,%s);'

cur.execute(sql,lst)

except Exception:

conn.rollback()#操作不成功 回滚

conn.commit()

cur.close()

conn.close()

# select book_name,price from book where author = 'egon'

# 2.找出最贵的图书的价格

# select max(price) from book;

# select price,book_name from book order by price desc limit 1;

# 3.求所有图书的均价

# select avg(price) from book;

# 4.将所有图书按照出版日期排序

# select * from book order by pub_date;

# 5.查询alex写的所有书的平均价格

# select avg(price) from book where author = 'alex'

# 扩展: 求所有人自己出版的图书的平均价格

# select author,avg(price) from book group by author

# 扩展: 求所有人自己出版的图书的平均价格>30的所有人

# select author from book group by author having avg(price)>30

# 6.查询人民音乐不好听出版社出版的所有图书

# select * from book where press = '人民音乐不好听出版社';

# 7.查询人民音乐出版社出版的alex写的所有图书和价格

# select * from book where press = '人民音乐不好听出版社' and author = 'alex';

# 8.找出出版图书均价最高的作者

# select author,avg(price) as avg_p from book group by author order by avg_p desc limit 1;

# 9.找出最新出版的图书的作者和出版社

# select author,press from book order by pub_date desc limit 1;

# 10.显示各出版社出版的所有图书

# select press,group_concat(book_name) from book group by press;

# 11.查找价格最高的图书，并将它的价格修改为50元

# select max(price) from book;

# update book set price = 50 where price = 70

# 12.删除价格最低的那本书对应的数据

# select min(price) from book;

# delete from book where price = 5;

# 13.将所有alex写的书作者修改成alexsb

# update book set author = 'alexsb' where author = 'alex';

# 14.select year(publish_date) from book

# 自己研究上面sql语句中的year函数的功能，完成需求：

# 将所有2017年出版的图书从数据库中删除

# delete from book where year(publish_date) = 2017;

小练习2

键表准备

数据导入

init.sql文件内容

Navicat Premium Data Transfer

Source Server : localhost

Source Server Type : MySQL

Source Server Version : 50624

Source Host : localhost

Source Database : sqlexam

Target Server Type : MySQL

Target Server Version : 50624

File Encoding : utf-8

Date: 10/21/2016 06:46:46 AM

*/

SET NAMES utf8;

SET FOREIGN_KEY_CHE