数据库作业案例

数据库作业案例

复习

"""
1、单表查询
	增删改查的完整语法
select distinct 字段 from 表 where   group by   having   order by   limit  

比较：> < =
区间：between and  |  in  |  not in
逻辑: and  or  not
相似：like  _%
正则：regexp

聚合函数：group_concat()、max()

having：可以对 聚合函数 结果进行筛选，不能使用 聚合函数 别名
order by：分组后对 聚合函数 进行排序，能使用 聚合函数 别名
limit：条数 | 偏移,条数


2、多表查询
内连接：from emp inner join dep on emp.dep_id = dep.id  只保存两表有对应关系的记录
左连接：from emp left join dep on emp.dep_id = dep.id   左表记录全部保存，右边没有对应记录空填充
右连接：from emp right join dep on emp.dep_id = dep.id  右表记录全部保存，左边没有对应记录空填充
全连接：
from emp left join dep on emp.dep_id = dep.id
union
from emp right join dep on emp.dep_id = dep.id
"""

案例

# 按指定编码创建数据库
create database 数据库名 charset="编码格式";
# 修改数据库编码
alter database 数据库名 charset="编码格式";

# 修改字段
alter table 库.表 modify 字段 类型(长度) 约束;
alter table 库.表 change 旧字段 新字段 类型(长度) 约束;

# 创建一个学生student表，有主键id字段，name唯一字段、age字段、height字段、mobile字段
create table student(
	id int not null auto_increment,
    name varchar(64) unique not null,
    age int unsigned default 0,
    height decimal(5,2) unsigned not null,
    mobile char(11),
    primary key(id),
    unique(name, mobile)
);
truncate student;

# 字段的增加
alter table 表名 add 字段 类型(长度) 约束 
alter table 表名 add 字段 类型(长度) 约束 first
alter table 表名 add 字段 类型(长度) 约束 after 已有字段

# 移动字段
alter table 表名 modify 字段 类型(长度) 约束 first

# 将 学生表、国家表、课程表、学生简介表 四个表放在一起考虑表关系，并完成数据的增删测试
create table country(
	id int,
    name varchar(16)
);
create table student(
	id int,
    name varchar(16),
    country_id int,
    foreign key(country_id) references country(id)
    on update cascade
    on delete cascade
);
create table student_info(
	id int,
    info varchar(256),
    student_id int unique,
    foreign key(student_id) references student(id)
    on update cascade
    on delete cascade
);

create table course(
    id int,
    name varchar(16)
);
create table student_course(
	id int,
    student_id int,
    course_id int,
    foreign key(student_id) references student(id)
    on update cascade
    on delete cascade,
    foreign key(course_id) references course(id)
    on update cascade
    on delete cascade
)

CREATE TABLE `emp`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `gender` enum('男','女','未知') NULL DEFAULT '未知',
  `age` int(0) NULL DEFAULT 0,
  `salary` decimal(5,2) NULL DEFAULT 0,
  `area` varchar(20) NULL DEFAULT '中国',
  `port` varchar(20) DEFAULT '未知',
  `dep` varchar(20),
  PRIMARY KEY (`id`)
);

INSERT INTO `emp` VALUES 
	(1, 'yangsir', '男', 42, 10.50, '上海', '浦东', '教职部'),
	(2, 'engo', '男', 38, 9.4, '山东', '济南', '教学部'),
	(3, 'jerry', '女', 30, 3.0, '江苏', '张家港', '教学部'),
	(4, 'tank', '女', 28, 2.4, '广州', '广东', '教学部'),
	(5, 'jiboy', '男', 28, 2.4, '江苏', '苏州', '教学部'),
	(6, 'zero', '男', 18, 8.8, '中国', '黄浦', '咨询部'),
	(7, 'owen', '男', 18, 8.8, '安徽', '宣城', '教学部'),
	(8, 'jason', '男', 28, 9.8, '安徽', '巢湖', '教学部'),
	(9, 'ying', '女', 36, 1.2, '安徽', '芜湖', '咨询部'),
	(10, 'kevin', '男', 36, 5.8, '山东', '济南', '教学部'),
	(11, 'monkey', '女', 28, 1.2, '山东', '青岛', '教职部'),
	(12, 'san', '男', 30, 9.0, '上海', '浦东', '咨询部'),
	(13, 'san1', '男', 30, 6.0, '上海', '浦东', '咨询部'),
	(14, 'san2', '男', 30, 6.0, '上海', '浦西', '教学部');

# 查询教学部山东人的平均薪资
# 1、查谁 2、从哪查 3、条件是啥
select avg(salary) from emp where dep='教学部' and area='山东';
select avg(salary) from emp where dep='教学部' group by area having area='山东';
select avg(salary) from emp where area='山东' group by dep having dep='教学部';

# 查询姓名中包含英文字母n并且居住在上海的人的所有信息
select * from emp where name like '%n%' and area='上海';

# 查询姓名中包含英文字母n但不包含数字的人的所有信息
select * from emp where name like '%n%' and name not regexp '.*[0-9].*';

# 查看各部门的平均年龄并降序排序
select dep, avg(age) from emp group by dep order by avg(age) desc;

# 查询各部门中年纪最大的人的姓名与居住地（户籍+区域）
select max(age), dep from emp group by dep;

select name, concat_ws('-', area, port) from emp 
where (age, dep) in (('36', '咨询部'),('38', '教学部'),('42', '教职部'));

select name, concat_ws('-', area, port) from emp 
where (age, dep) in (select max(age), dep from emp group by dep);

# 查询不同年龄层次平均薪资大于5w组中工资最高者的姓名与薪资
select age, max(salary) from emp group by age having avg(salary) > 5;

select name, salary from emp where (age, salary) in (select age, max(salary) from emp group by age having avg(salary) > 5);

create table dep(
	id int primary key auto_increment,
	name varchar(16),
	work varchar(16)
);
create table emp(
	id int primary key auto_increment,
	name varchar(16),
	salary float,
	dep_id int
);
insert into dep values(1, '市场部', '销售'), (2, '教学部', '授课'), (3, '管理部', '开车');
insert into emp(name, salary, dep_id) values('egon', 3.0, 2),('yanghuhu', 2.0, 2),('sanjiang', 10.0, 1),('owen', 88888.0, 2),('liujie', 8.0, 1),('yingjie', 1.2, 0);


# 查询每一个部门下的员工们及员工职责
# 1、查谁 2、从哪查，信息量不够连表 3、条件是啥，如果存在分组（不能直接查询的字段聚合处理 | 将要查询的字段添加进分组）
select group_concat(emp.name), work, dep.name from emp right join dep on emp.dep_id=dep.id group by emp.dep_id, work, dep.name;

select group_concat(emp.name) 员工们, max(work) 工作职责, max(dep.name) 部门 from emp right join dep on emp.dep_id=dep.id group by emp.dep_id;

select group_concat(emp.name) 员工们, max(work) 工作职责, dep.name 部门 from emp right join dep on emp.dep_id=dep.id group by dep.name;