mysql练习

数据库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;