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

SQL基础知识整理:

select 查询结果 如: [学号,平均成绩：组函数avg(成绩)]
from 从哪张表中查找数据 如:[涉及到成绩：成绩表score]
where 查询条件 如:[b.课程号=’0003’ and b.成绩>80]
group by 分组 如:每个学生的平均：按学号分组,MySQL中可以不用having 对分组结果指定条件 如:[大于60分]
order by 对查询结果排序 如:[增序: 成绩 ASC / 降序: 成绩 DESC];
limit 使用limt子句返回topN（对应这个问题返回的成绩前两名）
如:[ limit 2 ==>从0索引开始读取2个]** limit==>从0索引开始 [0,N-1]

① select * from table limit 2,1;                

//含义是跳过2条取出1条数据，limit后面是从第2条开始读，读取1条信息，即读取第3条数据

② select * from table limit 2 offset 1;     

//含义是从第1条（不包括）数据开始取出2条数据，limit后面跟的是2条数据，offset后面是从第1条开始读取，即读取第2,3条

     <br /> <br />**组函数: **<br />**去重 distinct()  **<br />**统计总数sum()  **<br />** 计算个数count()  **<br />**平均数avg() **<br />** 最大值max()**<br />** 最小数min()  **

多表连接:
内连接(省略默认inner) join …on..
左连接left join tableName as b on a.key ==b.key
右连接right join
连接union(无重复(过滤去重))和union all(有重复[不过滤去重])

1.用一条SQL 语句 查询出每门课都大于80 分的学生姓名

1、

--方法一：
select distinct name from table where name not in (select distinct name from table where fenshu<=80)--
方法二：select name from table group by name having min(fenshu)>80

1. 学生表 如下:

删除除了自动编号不同, 其他都相同的学生冗余信息

delete tablenamewhere 自动编号 not in(select min( 自动编号)from tablename group by 学号,姓名,课程编号,课程名称,分数)

3.一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合.
你先按你自己的想法做一下，看结果有我的这个简单吗？
3、

select a.name, b.namefrom team a, team b where a.name < b.name

4.请用SQL 语句实现：从TestDB 数据表中查询出所有月份的发生额都比101 科目相应月份的发生额高的科目。请注意：TestDB 中有很多科目，都有1 －12 月份的发生额。
AccID ：科目代码，Occmonth ：发生额月份，DebitOccur ：发生额。
数据库名：JcyAudit ，数据集：Select * from TestDB
4、

select a.* from TestDB a,(select Occmonth,max(DebitOccur) Debit101ccurfrom TestDBwhere AccID='101' group by Occmonth) bwhere a.Occmonth=b.Occmonth and a.DebitOccur>b.Debit101ccur

5.面试题：怎么把这样一个表儿

查成这样一个结果

5、

select year,(select amount from   aaa m where month=1   and m.year=aaa.year) 
as m1,(select amount from   aaa m where month=2   and m.year=aaa.year) 
as m2,(select amount from   aaa m where month=3   and m.year=aaa.year) 
as m3,(select amount from   aaa m where month=4   and m.year=aaa.year) 
as m4 from aaa group by year

1. 说明：复制表( 只复制结构, 源表名：a新表名：b)

2. 说明：拷贝表( 拷贝数据, 源表名：a目标表名：b)

insert into b(a, b, c)select d,e,f from a;

1. 说明：显示文章、提交人和最后回复时间

select a.title,a.username,b.adddatefrom table a,
(select max(adddate) adddatefrom table where table.title=a.title) b

1. 说明：外连接查询( 表名1 ：a表名2 ：b)

--SQL Server:select a.a, a.b, a.c, b.c, b.d, b.ffrom a LEFT OUTER JOIN b ON a.a = b.c--ORACLE：select a.a, a.b, a.c, b.c, b.d, b.f from a ,bwhere a.a = b.c(+)

1. 说明：日程安排提前五分钟提醒

2. 说明：两张关联表，删除主表中已经在副表中没有的信息
   11、

--SQL Server:Delete from infowhere not exists (select * from infobzwhere info.infid=infobz.infid)

12.有两个表A 和B ，均有key 和value 两个字段，如果B 的key 在A 中也有，就把B 的value 换为A 中对应的value
这道题的SQL 语句怎么写？
12、

update b set b.value=(select a.valuefrom a where a.key=b.key)where b.id in(select b.id from b,awhere b.key=a.key);

参考答案

6、

--SQL:select * into b from a where 1<>1--ORACLE:create table bAsSelect * from a where 1=2

注:<>（不等于）(SQL Server Compact)

比较两个表达式。当使用此运算符比较非空表达式时，如果左操作数不等于右操作数，则结果为 TRUE。否则，结果为 FALSE。