【数据库】表操作 习题总结

目录

关系建表

数据库sql的执行顺序

内外连接的写法

1.设计一张商品表

2.设计一张老师表

3.设计一张图书表

4.查询练习

5.查询练习

6.设计一个考勤系统

7.设计一个学校宿舍管理系统

8.设计一个车辆违章系统

9.设计一个学校食堂管理系统

10.有一张员工表emp,字段:姓名name,性别sex,部门depart,工资salary。查询以下数据:

11.面试真题

12.面试真题


关系建表

一对多(1:n)在多的表中创建外键 (外键对应即是一的表中的主键)。

多对多 (m:n)  额外创建一个中间表,中间表至少有两个字段,分别作为外键指向多对多双方的主键。

数据库sql的执行顺序

1、一条sql语句通常包括:

 select
 from
 join
 where
 group by 
 having
 order by
 聚合函数
 limit 
 top

2、浅谈执行顺序:

1)、首先确定一点,并不是按照我们写的语句顺序,从左—>右执行的

2)、获取结果集 ----> 指定查询的某些字段 --> 按照某些内容进行排序

  • 一.首先 执行from ,join 确定表之间的关系,得到初步的----->结果集1
  • 二.where 对结果集1 进行筛选 得到–>结果集2
  • 三.group by 进行分组 -->结果集3
  • 四.对结果集3进行having筛选,得到 ---->结果集 4
  • ​ 指定查询的字段:
  • 五.select 指定需要查询的字段,也可以是聚合函数 —>结果去重
  • 六.合并分组结果集,并按照order by 的 条件进行排序
  • 七. 如果存在limit 或者top之类的话,这是在最后才会执行的

 

内外连接的写法

内连接

1.A join B on A.x = B.x;

2.A inner join B on A.x = B.x;   inner可以省略

3.where A.x = B.x;

select * A join B on A.x = B.x where...

select * from A,B where A.x = B.x...

外连接

1.A left join B on A.x = B.x;

2.A right join B on A.x = B.x;

select * A left join B on A.x = B.x where...

1.设计一张商品表

设计一张商品表,包含以下字段:商品名称、商品价格、商品库存、商品描述。

drop table if exists product;
create table product(  
	name varchar(20),  --商品名称  varchar() 使用字符集UTF8 一个汉字占三个字节
	price decimal(11,2), --商品价格  使用decimal() 比double精确
	storage int,       --商品库存
	description varchar(100) --商品描述。
);

2.设计一张老师表

设计一张老师表,包含以下字段:姓名、年龄、身高、体重、性别、学历、生日、身份证号。

drop table if exists teacher;
create table teacher(
	name varchar(20),
	age int,
	height double,
	weight double,
	sex bit,
	birthday TIMESTAMP,  timestamp 时间日期型 精确到秒 格式 2000-1-1 10:05:00
	id_number varchar(18)
);

3.设计一张图书表

设计一张图书表,包含以下字段:图书名称,图书作者、图书价格、图书分类

drop table if exists book;
create table book(
	name varchar(20),
	author varchar(20),
	price decimal(11,2),
	category varchar(20)
);

4.查询练习

查询article文章表中,发表日期create_date在2019年1月1日上午10点30分至2019年11月10日下午4点2分的文章。

select * from article where create_date between '2019-01-01 10:30:00' and '2019-11-10 16:02:00';

5.查询练习

查询article文章表中,文章标题title为空,或者满足发表日期create_date在2019年1月1日之后

 select * from article where title is null or create_date > '2019-01-01 00:00:00';

6.设计一个考勤系统

考勤系统,包含员工表,考勤记录表  员工与记录 一对多

-- 主要考虑记录表中的记录信息,是如何关联到员工表,员工与记录关系为1:m。

create table emp(
  id int primary key,
  name varchar(20)
);

create table info(
  id int primary key,
  emp_id int,
  info_date timestamp,
  foreign key (emp_id) references emp(id)
);

7.设计一个学校宿舍管理系统

学校宿舍管理系统,要求包含宿舍信息,学生信息,每日的宿舍查房记录。宿舍与学生一对多,宿舍与查房一对多

-- 主要考虑学生与宿舍的关系:m:1,宿舍的查房记录是根据宿舍来查的,与宿舍有关系,一个宿舍可以多次查房,宿舍与查房记录是1:m的关系


create table dormitory(
  id int primary key,
  number varchar(20)
);

create table student(
  id int primary key,
  name varchar(20),
  dormitory_id int,
  foreign key (dormitory_id) references dormitory(id)
);

create table info(
  id int primary key,
  dormitory_id int,
  status bit,
  info_date timestamp,
  foreign key (dormitory_id) references dormitory(id)
);

8.设计一个车辆违章系统

车辆违章系统,包含用户表,车辆表,违章信息表。违章信息表中包含用户和车辆的违章信息。

-- 用户可以拥有多辆车,关系为1:m,
题目已经说明违章信息包括用户和车辆,说明违章信息表中要记录用户和车辆
,一个用户可以有多次违章记录,用户与违章记录关系为1:m,
一辆车也可以有多次违章记录,车辆与违章记录关系也为1:m

create table user(
  id int primary key,
  name varchar(20)
);

create table cars(
  id int primary key,
  name varchar(20),
  user_id int,
  foreign key (user_id) references user(id)
);

create table info(
  id int primary key,
  user_id int,
  cars_id int,
  foreign key (user_id) references user(id),  --两个外键
  foreign key (cars_id) references cars(id)
);

9.设计一个学校食堂管理系统

学校食堂管理系统,包含食堂表,食堂仓口表,仓口收费记录表。

-- 一个食堂有多个仓口卖饭,关系为1:m,
--每个仓口卖饭可以有很多次,仓口与收费记录也是1:m

create table hall(
  id int primary key,
  name varchar(20)
);

create table hall_opening(
  id int primary key,
  name varchar(20),
  hall_id int,
  foreign key (hall_id) references hall(id)
);

create table info(
  id int primary key,
  price int,
  info_date timestamp,
  hall_opening_id int,
  foreign key (hall_opening_id) references hall_opening(id)
);

10.有一张员工表emp,字段:姓名name,性别sex,部门depart,工资salary。查询以下数据:

1、查询男女员工的平均工资

      select sex,avg(salsry) as '平均薪资' from emp group by sex;

2、查询各部门的总薪水

     select depart,sum(salsry) from emp group by depart;

3、查询总薪水排名第二的部门

     select depart ,sum(salary) from emp group by depart order by sum(salary) desc limit 1,1;

4、查询姓名重复的员工信息

      select name from emp group by name having count(name)>1;

5、查询各部门薪水大于10000的男性员工的平均薪水

     select depart,avg(salary) from emp where sex = '男' and salary>10000 group by depart;

11.面试真题

有员工表、部门表和薪资表,根据查询条件写出对应的sql【同程艺龙2020届校招笔试题】

现在有员工表、部门表和薪资表。部门表depart的字段有depart_id, name;员工表 staff 的字段有 staff_id, name, age, depart_id;薪资表salary 的字段有 salary_id,staff_id,salary,month。

(问题a):求每个部门'2016-09'月份的部门薪水总额

   select depart.name,sum(salary) from depart,staff,salary where depart.depart_id = staff.depart_id and staff.staff_id = salary.staff_id and month between '2016-09-01 00:00:00' and '2016-09-30 23:59:59' group by depart.depart_id;

select

        depart.name,sum(salary)

from

        depart join staff on depart.depart_id = staff.depart_id 

        join salary on staff.staff_id = salary.staff_id

where 

        month between '2016-09-01 00:00:00' and '2016-09-30 23:59:59'

group by

         depart.depart_id;

(问题b):求每个部门的部门人数,要求输出部门名称和人数

   select depart.depart_id,count(staff_id) from depart,staff where depart.depart_id = staff.depart_id group by depart.depart_id;

(问题c):求公司每个部门的月支出薪资数,要求输出月份和本月薪资总数

   select depart.depart_id ,mouth,sum(salary) from depart,staff,salary where depart.depart_id = staff.depart_id and staff.staff_id = salary.staff_id group by depart.depart_id ;

12.面试真题

写出以下数据库的查询条件【交大思诺2020届校招笔试题】

有两个表分别如下:

表A(varchar(32) name, int grade)

数据:zhangshan 80, lisi 60, wangwu 84

表B(varchar(32) name, int age)

数据:zhangshan 26, lisi 24, wangwu 26, wutian 26

写SQL语句得到如下查询结果:

| NAME   | GRADE | AGE |

| --------- | ----- | ---- |

| zhangshan | 80  | 26  |

| lisi   | 60  | 24  |

| wangwu  | 84  | 26  |

| wutian  | null | 26  |

 select B.name , grade,age from B left join A on B.name = A.name;