首页 > 数据库 >数据库的基本操作2

数据库的基本操作2

时间：2023-10-25 21:34:21浏览次数：38  
标签：dep -- 数据库 course int student 基本操作 id

今日内容详细

外键

"""
缺陷
1.表的重点不清晰				可以忽略
	到底是员工表还是部门表
2.表中相关字段一直在重复存储		可以忽略
	浪费存储空间
3.表的扩展性极差,牵一发而动全身   不能忽略
"""

	解决方式
    	将上述一张表拆分成两张表
        	emp与dep
  		# 上述三个缺陷全部解决
"""
带来了一个小问题 表与表之间的数据没有对应关系了
"""

外键字段>>>:部门编号
    其实就是用来标识表与表之间的数据关系
    # 简单的理解为该字段可以让你去到其他表中查找数据

表与表之间的关系

一对多
多对多
一对一
没有关系

# 一对多的表关系
"""如何判断表关系：换位思考法"""
以员工表和部门表为例
	先站在员工表
    	问：一个员工能否有多个部门?
    	答：不能
    在站在部门表
    	问：一个部门能否有多个员工?
        答：可以
    # 结论：一个可以，一个不可以，表关系就是：一对多， 表关系中没有多对一
    """针对于一对多，外键字段要建在多的一方"""

    
如何在SQL层面建立一对多的关系: 先把基础表的中基础字段建立出来，然后在考虑外键字段
create table emp(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    dep_id int,
    foreign key(dep_id) references dep(id) # 让两张表建立了外键关系
    on update cascade # 级联更新
    on delete cascade # 级联删除
);


create table dep(
	id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(32)
);

## 录入数据
insert into emp(name, age, dep_id) values('kevin', 20, 1);
insert into dep(dep_name,dep_desc) values('人事部', '管理人才');



### 多对多
以图书表和作者表为例
我们站在图书表的角度
	问：一本图书能不能有多个作者?
    答：可以
我们再站在作者表的角度
	问：一个作者能不能写多本书
    答：可以
得出结论：如果两个都可以，那么表关系就是'多对多'
"""针对于多对多的表关系，外键字段建在第三张表中"""
# 针对多对多表关系，外键字段如何创建？

在SQL层面建立多对多的表关系
create table book(
	id int primary key auto_increment,
    title varchar(32),
    price decimal(8,2)
);

create table author(
	id int primary key auto_increment,
    name varchar(32),
    addr varchar(32)
);

create table book2author(
	id int primary key auto_increment,
    book_id int,
    author_id int,
    foreign key(book_id) references author(id) # 让两张表建立了外键关系
    on update cascade # 级联更新
    on delete cascade, # 级联删除
    foreign key(author_id) references book(id) # 让两张表建立了外键关系
    on update cascade # 级联更新
    on delete cascade
);

insert into book(title, price) values('史记', 1000);
insert into book(title, price) values('西游记', 2000);
insert into author(name, addr) values('zhangsan', 'beijing');
insert into author(name, addr) values('lisi', 'shanghai');

insert into book2author(book_id, author_id) values(1, 1);
insert into book2author(book_id, author_id) values(1, 2);
insert into book2author(book_id, author_id) values(2, 1);
insert into book2author(book_id, author_id) values(2, 2);


### 一对一
以作者表和作者详情表为例
# 以作者表和作者详情表为例
外键关系建在哪里？
	# 两张表都可以，但是，推荐建在查询频率较高的一张表
    
在SQL层建立一对一的关系

create table author1(
	id int primary key auto_increment,
    name varchar(32),
    gender varchar(32),
    author_detail_id int unique,
    foreign key(author_detail_id) references author_detail(id)
    on update cascade
    on delete cascade
);


create table author_detail(
	id int primary key auto_increment,
    qq varchar(32),
    email varchar(32)
);

"""你们把数据录入进去，然后测试一下!!!"""

注意事项

1.在创建表的时候 需要先创建被关联表(没有外键字段的表)
2.在插入新数据的时候 应该先确保被关联表中有数据
3.在插入新数据的时候 外键字段只能填写被关联表中已经存在的数据
4.在修改和删除被关联表中的数据的时候 无法直接操作
	如果想要数据之间自动修改和删除需要添加额外的配置

多表查询

"""在此之前，都是单表下的查询"""
多表查询的思路是：
	1. 子查询
    	# 查询kevin的部门名称
        1. 应该先查询kevin 的部门编号(部门表的id)
        select dep_id from emp where name='kevin';
        2. 然后拿着查询出来的部门id去dep表中查询部门名称
        select *from dep where id = (select dep_id from emp where name='kevin';);
        
        """子查询就是：一条SQL的执行结果就是另外一条SQL的执行条件!"""
        其实就是分步操作
        
    2. 连表查询(重点)
    """把多张有关系的表链接成一张大的虚拟表，连接出来的虚拟表不是实际存在的，它是在内存中存储，然后按照单表查询."""
       专业的连表语法：
        inner join # 内连接，查询的是两张表中都有的数据
        left join  # 左连接，以左表为基准，查询左表中所有的数据，右表没有的数据，使用NULL填充
        right join # 右连接，以右表为基准，查询右表中所有的数据，右表没有的数据，使用NULL填充
        union  # 连接两个SQL语句的结果
    	select * from emp left join dep on emp.dep_id=dep.id
        union
        select * from emp right join dep on emp.dep_id=dep.id;
        
        """连表可以连很多张表，不只是两张，大多数都是两张"""
        select * from emp left join dep on emp.dep_id=dep.id inner join A on A.id=dep.A_id where ...;

数据准备

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

create table emp(
    id int primary key auto_increment,
    name varchar(20),
    sex enum('male','female') not null default 'male',
    age int,
    dep_id int
);
"""如果两张表没有建立强制的约束关系，就使用逻辑意义上的关联"""
#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'保洁')
;

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

多表查询练习题

1、查询所有的课程的名称以及对应的任课老师姓名
2、查询平均成绩大于八十分的同学的姓名和平均成绩
3、查询没有报李平老师课的学生姓名
4、查询挂科超过两门(包括两门)的学生姓名和班级
'''可能有点难，自己做，能做几个做几个.'''
###########################编写SQL不要想着一次性写完 可以边写边看######################################

-- 1、查询所有的课程的名称以及对应的任课老师姓名
-- SELECT
-- 	teacher.tname,
-- 	course.cname 
-- FROM
-- 	teacher
-- 	INNER JOIN course ON teacher.tid = course.teacher_id;
-- 2、查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先确定需要使用到的表 
# 2.在思考多表查询的方式
# 第一步先查询成绩表中 平均成绩大于80的学生编号
# 1.1 按照学生id分组并获取平均成绩
-- select student_id,avg(num) from score group by student_id;
# 1.2 筛选出平均成绩大于80的数据  (针对聚合函数的字段结果 最好起别名防止冲突)
-- select student_id,avg(num) as avg_num from score group by student_id having avg(num) > 80;
# 1.3 将上述SQL的结果与student表拼接
-- SELECT
-- 	student.sname,
-- 	t1.avg_num 
-- FROM
-- 	student
-- 	INNER JOIN ( SELECT student_id, avg( num ) AS avg_num FROM score GROUP BY student_id HAVING avg( num ) > 80 ) AS t1 ON student.sid = t1.student_id;
-- 3、查询没有报李平老师课的学生姓名
# 1.先查询李平老师教授的课程编号
-- select course.cid from course where teacher_id = 
-- (select tid from teacher where tname ='李平老师');
# 2.根据课程id号筛选出所有报了的学生id号
-- select distinct score.student_id from score where course_id in (select course.cid from course where teacher_id = 
-- (select tid from teacher where tname ='李平老师'));
# 3.去学生表中根据id号取反筛选学生姓名
-- SELECT
-- 	student.sname 
-- FROM
-- 	student 
-- WHERE
-- 	sid NOT IN (
-- SELECT DISTINCT
-- 	score.student_id 
-- FROM
-- 	score 
-- WHERE
-- 	course_id IN ( SELECT course.cid FROM course WHERE teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老师' ) ) 
-- 	);
-- 4、查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先筛选出小于60分的数据
-- select * from score where num < 60;
# 2.按照学生id分组 然后统计挂科数量
-- select student_id,count(course_id) from score where num < 60 group by student_id;
# 3.筛选出挂科超过两门的学生id
-- select student_id from score where num < 60 group by student_id
-- having count(course_id) >=2;
# 4.先将上述结果放在一边 去连接student和class表
SELECT
	student.sname,
	class.caption 
FROM
	class
	INNER JOIN student ON class.cid = student.class_id 
WHERE
	student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id ) >= 2 );

# 基本上是不用写SQL语句
Navicat的使用需要下载
'''它不是免费的，收费的，所以：1. 花钱去买，2. 白嫖，3. 免费试用14天'''
#1. Windows版本
#2. Mac版本

去官网下载：https://www.navicat.com.cn/products/

连接mysql命令：mysql -h127.0.0.1 -P3306 -uroot -p123456
# 自己电脑
mysql -u root -p

# 127.0.0.1---------一样的-------->localhost

SQL文件

/*
 数据导入：
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50624
 Source Host           : localhost
 Source Database       : sqlexam

 Target Server Type    : MySQL
 Target Server Version : 50624
 File Encoding         : utf-8

 Date: 10/21/2016 06:46:46 AM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;

-- ----------------------------
--  Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`teacher_id`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;

-- ----------------------------
--  Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),
  KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;

-- ----------------------------
--  Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;

-- ----------------------------
--  Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

练习

1、查询所有的课程的名称以及对应的任课老师姓名
select course.cname,teacher.tname from course inner join teacher on course.teacher_id=teacher.tid; 

2、查询平均成绩大于八十分的同学的姓名和平均成绩

3、查询没有报李平老师课的学生姓名
4、查询挂科超过两门(包括两门)的学生姓名和班级

标签：dep,--,数据库,course,int,student,基本操作,id
From： https://www.cnblogs.com/zhangfanshixiaobai/p/17788174.html

相关文章

  • Apipost现已支持连接数据库！
    Apipost提供了数据库连接功能，在接口调试时可以使用数据库获取入参或进行断言校验。目前的Apipost支持：Mysql、SQLSever、Oracle、Clickhouse、达梦数据库、PostgreSQL、Redis、MongoDB8种数据库的连接操作新建数据库连接：在「项目设置」-「公共资源维护」-「连接数据库」中配置需......
  • Oracle数据库使用问题汇总
    新建用户问题。参考Oracle表空间和用户_oracle用户和表空间_you4580的博客-CSDN博客Oracle数据库如何给用户授权（oracle下赋权限）-数据库运维技术服务(dbs724.com)Oracle表空间和用户前言1、创建表空间2、删除表空间3、创建用户4、用户赋权限5、Oracle三个重要的角色1、创建......
  • Apipost现已支持连接数据库！
    Apipost提供了数据库连接功能，在接口调试时可以使用数据库获取入参或进行断言校验。目前的Apipost支持：Mysql、SQLSever、Oracle、Clickhouse、达梦数据库、PostgreSQL、Redis、MongoDB8种数据库的连接操作新建数据库连接：在「项目设置」-「公共资源维护」-「连接数据库」中配置......
  • KubeSphere v3.4.0 创建Mysql 8.1.0 数据库主从记录
    主要讲下KubeSphere中创建【有状态】的【工作负载】需要注意的配置问题。这里的创建过程也不详细描述，网上简单找一找也有。重点在使用存储里面的配置字典：1、先创建一个配置字典，参考名：mysql-config2、创建一主一从的配置项，键（主库）：master-1.cnf值（主库）：[mysqld]port......
  • 多款国产操作系统安装数据库干货文档汇总（含Oracle/MySQL/国产数据库等）
    随着国产化的逐步推进，越来越多的企业选择将数据库安装在国产操作系统上。为帮助大家了解国产操作系统上的数据库成功搭建案例与搭建方式，本文整理了墨天轮数据技术社区上用户分享的实操文档，涵盖银河麒麟、中标麒麟、统信UOS、openEuler等常见的国产操作系统，数据库以Oracle为主，此外......
  • 将nginx的access.log访问日志发送到rsyslog服务器并写入数据库
    nginx.conf（将原日志路径改为rsyslog服务器地址）access_logsyslog:server=10.10.14.64:514,facility=local6main;如果需要入库需要安装相应数据库的依赖包；mysql依赖：yuminstall-y rsyslog-mysql   pgsql依赖：yuminstall-y rsyslog-pgsql  还有很多其他依赖可以用......
  • 功能不够，SQL来凑，修改数据库的正确姿势？
    修改数据库是一项关键任务，需要小心谨慎地执行，以确保数据的完整性和准确性。下面是一个详细的步骤指南，介绍了正确修改数据库的姿势。第一步：备份数据库在进行任何数据库修改之前，务必备份数据库。这样，如果发生意外情况，可以恢复到修改之前的状态。使用数据库管理工具或命令行工具创......
  • ASP.NET Core已有数据库，却新建项目
    ASP.NETCore已有数据库，却新建项目,只需要构造出相应的类，DbContext，然后直接add-migrationinit即可！！而不用执行update-database，执行后者会报错:ErrorNumber:2714,State:6,Class:16Thereisalreadyanobjectnamed'xxxxxx'inthedatabase.另外，注意主键到底是int,还是long,这......
  • 大事务导致数据库恢复时间长
    背景客户的一套系统从凌晨开始出现运行缓慢，重启SQLServer服务后一个主要的数据库一直处在正在恢复的状态，多次重启SQLServer服务和服务器无果后请我们协助处理。现象在SSMS中看到数据库是正在恢复的状态，而且不能被访问。 分析启动SQLServer服务时数据库恢复要经过分......
  • 数据库绪论
    一、数据库系统概述1.1数据库四个基本概念1.1.1数据（data）  数据是数据库中存储的基本对象。描述事物的符号记录称为数据，描述事物的符号可以是文字、图形、图像、音频、视频等，数据有多重表现形式，它们都可以经过数字化后存入计算机。数据的含义称为数据的语义，例如：李明是数据，名......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99