【数据库】表操作 习题总结
目录
10.有一张员工表emp，字段：姓名name，性别sex，部门depart，工资salary。查询以下数据：
关系建表
一对多（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;