mysql 自用 数据管理_MySQL基础之数据管理【3】

MySQL中的多表联查

--查询emp的id username age depName

create table emp(

id int unsigned auto_increment key,

username varchar(20) not null unique comment '编号',

age tinyint unsigned not null default 18 comment '年龄',

sex enum('男','女','保密') not null default '保密' comment '性别',

addr varchar(20) not null default '北京',

depId tinyint unsigned not null comment '部门对应的编号'

)engine=innodb charset=utf8;

insert emp(username,age,depId) values('king',24,1),

('queen',25,2),

('imooc',26,1),

('lily',27,1),

('rose',28,3),

('john',29,3);

create table dep(

id tinyint unsigned auto_increment key,

depName varchar(50) not null unique,

depDesc varchar(100) not null

)engine=innodb charset=utf8;

insert dep(depName,depDesc) values('PHP教学部','研发PHP课件'),

('JAVA教学部','研发JAVA课件'),

('WEB教学部','研发WEB课件'),

('IOS教学部','研发IOS课件');

笛卡尔积的形式(不常用)

select emp.id,emp.username,emp.age,dep.depName from emp,dep;

--显示

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

| id | username | age | depName |

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

| 1 | king | 24 | IOS教学部 |

| 1 | king | 24 | JAVA教学部 |

| 1 | king | 24 | PHP教学部 |

| 1 | king | 24 | WEB教学部 |

| 2 | queen | 25 | IOS教学部 |

| 2 | queen | 25 | JAVA教学部 |

| 2 | queen | 25 | PHP教学部 |

| 2 | queen | 25 | WEB教学部 |

| 3 | imooc | 26 | IOS教学部 |

| 3 | imooc | 26 | JAVA教学部 |

| 3 | imooc | 26 | PHP教学部 |

| 3 | imooc | 26 | WEB教学部 |

| 4 | lily | 27 | IOS教学部 |

| 4 | lily | 27 | JAVA教学部 |

| 4 | lily | 27 | PHP教学部 |

| 4 | lily | 27 | WEB教学部 |

| 5 | rose | 28 | IOS教学部 |

| 5 | rose | 28 | JAVA教学部 |

| 5 | rose | 28 | PHP教学部 |

| 5 | rose | 28 | WEB教学部 |

| 6 | john | 29 | IOS教学部 |

| 6 | john | 29 | JAVA教学部 |

| 6 | john | 29 | PHP教学部 |

| 6 | john | 29 | WEB教学部 |

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

内连接的形式(查询两个表中符合连接条件的记录)

--select 字段名称,... from tbl_name1 [inner] join tbl_name2 on 连接条件;

select e.id,e.username,e.age,d.depName

from emp as e

inner join dep as d

on e.depId=d.id;

--显示

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

| id | username | age | depName |

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

| 1 | king | 24 | PHP教学部 |

| 2 | queen | 25 | JAVA教学部 |

| 3 | imooc | 26 | PHP教学部 |

| 4 | lily | 27 | PHP教学部 |

| 5 | rose | 28 | WEB教学部 |

| 6 | john | 29 | WEB教学部 |

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

外连接的形式

--左外连接(select 字段名称,... from tbl_name1 left [outer] join tbl_name2 on 条件;)

--先显示左表中的全部记录,再去右表中查询符合条件的记录,不符合的以null代替

select e.id,e.username,e.age,d.depName,d.depDesc

from emp as e

left outer join dep as d

on e.depId=d.id;

--显示

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

| id | username | age | depName | depDesc |

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

| 1 | king | 24 | PHP教学部 | 研发PHP课件 |

| 2 | queen | 25 | JAVA教学部 | 研发JAVA课件 |

| 3 | imooc | 26 | PHP教学部 | 研发PHP课件 |

| 4 | lily | 27 | PHP教学部 | 研发PHP课件 |

| 5 | rose | 28 | WEB教学部 | 研发WEB课件 |

| 6 | john | 29 | WEB教学部 | 研发WEB课件 |

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

--右外连接(select 字段名称,... from tbl_name1 right [outer] join tbl_name2 on 条件;)

--先显示右表中的全部记录,再去左表中查询符合条件的记录,不符合的以null代替

select e.id,e.username,e.age,d.depName

from emp as e

right outer join dep as d

on e.depId=d.id;

--显示

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

| id | username | age | depName |

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

| 1 | king | 24 | PHP教学部 |

| 2 | queen | 25 | JAVA教学部 |

| 3 | imooc | 26 | PHP教学部 |

| 4 | lily | 27 | PHP教学部 |

| 5 | rose | 28 | WEB教学部 |

| 6 | john | 29 | WEB教学部 |

| NULL | NULL | NULL | IOS教学部 |

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

多表联查的操作

create table user(

id tinyint unsigned auto_increment key comment '编号',

username varchar(20) not null unique comment '用户名',

email varchar(50) not null default '2214@qq.com' comment '邮箱',

proId tinyint unsigned not null comment '用户所在省份的编号'

)engine=innodb charset=utf8;

insert user(username,proId) values('a','1'),

('b','1'),

('c','1'),

('d','2'),

('e','3'),

('f','1'),

('g','1');

create table pro(

id tinyint unsigned auto_increment key comment '编号',

proName varchar(10) not null unique comment '省份名称'

)engine=innodb charset=utf8;

insert pro(proName) values('北京'),('上海'),('深圳');

--查询user: id,username pro: proName

select u.id,u.username,p.proName

from user as u

join pro as p

on u.proId=p.id;

--显示

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

| id | username | proName |

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

| 1 | a | 北京 |

| 2 | b | 北京 |

| 3 | c | 北京 |

| 4 | d | 上海 |

| 5 | e | 深圳 |

| 6 | f | 北京 |

| 7 | g | 北京 |

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

--修改北京为首都

update pro set proName='首都' where id=1;

--显示

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

| id | username | proName |

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

| 1 | a | 首都 |

| 2 | b | 首都 |

| 3 | c | 首都 |

| 4 | d | 上海 |

| 5 | e | 深圳 |

| 6 | f | 首都 |

| 7 | g | 首都 |

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

四个表关联的查询

create table pro2(

id tinyint unsigned not null auto_increment key comment '编号',

proName varchar(10) not null unique comment '省份名称'

)engine=innodb charset=utf8;

insert pro2(proName) values('北京'),('上海'),('深圳');

create table admin(

id tinyint unsigned auto_increment key comment '编号',

username varchar(20) not null unique comment '用户名',

email varchar(50) not null default '24235@qq.com' comment '邮箱',

proId tinyint unsigned not null comment '用户所在省份的编号'

)engine=innodb charset=utf8;

insert admin(username,proId) values('king',1),('queen',2);

create table cate(

id tinyint unsigned auto_increment key comment '编号',

cateName varchar(50) unique comment '商品分类名称',

cateDesc varchar(100) not null default '好东西' comment '商品分类描述'

)engine=innodb charset=utf8;

insert cate(cateName) values('母婴'),('服装'),('电子');

create table product(

id int unsigned auto_increment key comment '编号',

productName varchar(50) not null unique comment '商品名称',

price float(8,2) not null default '12' comment '价格',

cateId tinyint unsigned not null comment '商品所在分类的编号',

adminId tinyint unsigned not null comment '管理员编号'

)engine=innodb charset=utf8;

insert product(productName,price,cateId,adminId) values('iphone9',9888,3,1),

('adidas',388,2,2),

('nike',888,2,2),

('奶瓶',288,1,1);

--查询product:id productName price cate:catename(两个表)

select p.id,p.productName,p.price,c.cateName

from product as p

join cate as c

on p.cateId=c.id;

--查询管理员 id username email pro:proName(两个表)

select a.id,a.username,a.email,p.proName

from admin as a

join pro2 as p

on a.proId=p.id;

--查询product:id productName price

--cate:cateName

--admin:username email

--pro2:proName(四表查询)

select p.id,p.productName,p.price,c.cateName,a.username,a.email,pr.proName

from product as p

join admin as a

on p.adminId=a.id

join cate as c

on p.cateId=c.id

join pro2 as pr

on a.proId=pr.Id

where p.price<1000

order by p.price desc

limit 0,2;

--显示

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

| id | productName | price | cateName | username | email | proName |

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

| 3 | nike | 888.00 | 服装 | queen | 24235@qq.com | 上海 |

| 2 | adidas | 388.00 | 服装 | queen | 24235@qq.com | 上海 |

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值