数据库mysql练习
分类
内连接、自然连接、外链接(左外连接、右外连接)
子查询
# 子查询
select role
from hello2
where id in (select id from hello where name = ${name});
内连接 inner join
# 内连接
select *
from hello inner join hello2 where hello2.id=hello.id;
也就是等值连接
如果其中一个表没有值就是在总的表里面没有显示
自然连接
# 自然连接
select *
from hello natural join hello2;
外连接(左外连接)(右外连接)
左外连接
#左连接
select name, role, age
from hello
left join hello2 on hello2.id = hello.id
where age > 50;
也就是查询出来的数据左边的表会比较多数据,然后呢,右表没有的数据会用null来表示
右外连接
# 右外连接
select *
from hello
right join hello2 on hello.id = hello2.id where age<46;
和左外表查询相反,也就是查询出来的数据右边的表会比较多数据,然后呢,左表没有的数据会用null来表示
补充:练习补充
# 创建数据库
create database testcodel;
# 删除数据库
drop database testcodel;
# 使用数据库
use testcodel;
# 创建表
create table `hello2`
(
`id` int unsigned auto_increment,
`role` varchar(225) not null,
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
# 删除表
drop table hello;
# 插入数据
insert into hello2 (id, role)
values (4, 1),
(3, 0),
(null, 0);
# 删除数据
delete
from hello2
where id = 3;
# 更新表数据
update hello2
set role = ${role}
where id = ${id};
# 更新表数据
update hello
set name=${name},
age=${age}
where id = ${id};
# 查询数据
select *
from hello
where id = ${id};
# 分页查询
select *
from hello
limit 1,2;
select *
from hello
order by id;
# 内连接
select *
from hello h1
inner join hello2 h2 on h2.id = h1.id;
# 子查询
select role
from hello2
where id in (select id from hello where name = ${name});
# 左外连接
select *
from hello
left join hello2 on hello.id = hello2.id;
# 右外连接
select *
from hello
right join hello2 on hello.id = hello2.id
where age < 46;
# 查找年龄比平均年龄大的名字
select name
from hello
where age > (select avg(age) from hello);
# 查询最大年龄
select max(age)
from hello;
#查询最大年龄的名字和年龄
select name, age
from hello
where age = (
select max(age)
from hello);
# 查询最小年龄的信息
select name, age, sex
from hello
where age = (select min(age) from hello);
;
#左连接
select name, role, age
from hello
left join hello2 on hello2.id = hello.id
where age > 50;
# 内连接
select *
from hello
inner join hello2
where hello2.id = hello.id;
# 自然连接
select *
from hello
natural join hello2;