Oracle

（一）资源

1. 视频

2. 笔记

3. 函数

（二）简介

1.基础

MySQL + JavaSE + Navicat Premium 16 + Linux

2.资源

搜罗后，相对易入门的

3.声明

是个小白

（三）数据库

1.关系与分布

数据库名称	关系型数据库	分布式数据库	分布式部署
MySQL
Oracle
Redis
MongoDB

关系型：数据之间有联系
分布式：数据分散存储

注：通常关系型数据库并不具备分布式功能，但在后续的集群扩展往往可以实现分布式部署。

2.优势与应用

数据库名称	数据组织和存储方式	分布式部署	优势(关系型)	应用
MySQL	表格	MySQL Cluster...	事务处理-复杂查询	银行转账-多表关联
Oracle		Oracle RAC...
Redis	键值	Redis Cluster...	实时读写-数据缓存	在线游戏-购物车数据
MongoDB	文档	MongoDB Sharding...

注：分布式部署往往通过集群(Cluster)、分区/片实现

【拓展】
Redis-MongoDB的差异化应用：个性推荐-大数据存储

3.Oracle vs MySQL

1).基础差异

	Oracle	MySQL
数据库安装与连接	步骤更多
建库建表	各种授权
逻辑/物理结构	更复杂
–	–	–
数据文件格式	.dbf+…

2).差异展示

(1).看库z注：Oracle就只有一个大数据库

（四）Oracle

1.表空间

1).定义

管理物理存储和分配的基本单元
用于存储数据库对象（例如表和索引）和它们的数据

表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。

2).关系

数据库与表空间	表空间与数据文件
1：n	1：n

每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件

一个数据文件只能属于一个表空间

3).结构

既包含物理结构又包含逻辑结构。

从物理上看，表空间由一个或多个数据文件组成，这些数据文件存储了实际的表数据和索引数据等物理数据。
从逻辑上看，表空间是一个由Oracle管理的逻辑存储结构。表空间定义了一个逻辑和物理组合的存储结构，它作为数据库对象（如表、索引和视图等）的存储载体。通过创建不同的表空间，可以为不同的对象和用户提供彼此分离的独立空间，保证了数据安全性和数据隔离性。

【逻辑示意图】：

2.用户

1).关系

用户：表空间 = n：m

一个用户可以拥有多个表空间，但是每个用户必须有一个默认的表空间。
多个用户可以共享同一个表空间。

2).类型

自带用户

用户名	密码	说明	权限
scott	tiger	测试用户	CUDR
system		次级管理员	+创建用户
sys		超级管理员	+备份和恢复

注：scott等只有对自己创建的对象进行数据操作的权限和部分系统权限

3).指令

连接(cmd) ：sqlplus

-- 查看当前使用的数据库(唯一)
SELECT name FROM v$database;
-- 查看当前用户用户名
SELECT USER FROM dual;
-- 查看所有用户用户名
SELECT DISTINCT owner FROM all_tables;
-- 创建新用户
CREATE USER new_user IDENTIFIED BY user_password;
CREATE USER oracleTest IDENTIFIED BY 123456;
-- 重新连接到新用户
CONNECT oracleTest/123456
-- 赋予权限
GRANT DBA TO oracleTest;

#  【表空间】
-- 查看表空间
SELECT * FROM DBA_TABLESPACES;
-- 查看当前用户所属的表空间
SELECT * FROM USER_TABLESPACES;
-- 上述两个结果相同
-- 使用的表空间为数据库中的默认表空间

-- 新建表空间
create tablespace oracleTest
datafile 'c:\oracleTest.dbf'
size 100m
autoextend on
next 10m
-- oracleTest 为表空间名称
-- datafile 用于设置物理文件名称
-- size 用于设置表空间的初始大小
-- autoextend on 用于设置自动增长，如果存储量超过初始大小，则开始自动扩容
-- next 用于设置扩容的空间大小

-- 设置当前用户的表空间
ALTER USER now_user DEFAULT TABLESPACE tablespace_name;
ALTER USER oracleTest DEFAULT TABLESPACE oracleTest;

3.小练习①

1).初始化

-- 创建表
CREATE TABLE jobs (
  jobno NUMBER(2,0) CHECK(jobno >= 0),
  jobname VARCHAR2(10),
  sal DECIMAL(8, 2),
  comm DECIMAL(8, 2) 
);
-- 注释
COMMENT ON TABLE jobs IS '员工表';
COMMENT ON COLUMN jobs.jobno IS 'id';
COMMENT ON COLUMN jobs.jobname IS '姓名';
COMMENT ON COLUMN jobs.sal IS '薪水';
COMMENT ON COLUMN jobs.comm IS '津贴';
-- 插入数据
INSERT INTO jobs (jobno, jobname, sal, comm) VALUES (1, '经理', 5000.00, 500.00);
INSERT INTO jobs (jobno, jobname, sal, comm) VALUES (2, '总经理', 4000.00, 400.00);
INSERT INTO jobs (jobno, jobname, sal, comm) VALUES (3, '部门经理', 3000.00, 300.00);
INSERT INTO jobs (jobno, jobname, sal, comm) VALUES (4, '职员', 2000.00, 200.00);

2).字段对比

MySQL	Oracle
VARCHAR	VARCHAR2
jobno int(2) unsigned	jobno NUMBER(2,0) check (jobno >= 0)
sysdate()/now()	sysdate

3).需求

(1)添加列’hiredate’(入职时间),并插入一条数据：获取当前时间
(2)查询‘部门经理’的年薪（薪水+津贴;四舍五入计算）
(3)查询各个职位的年薪，如果是董事长按15薪统计，总经理按13薪统计

4).参考

SELECT * FROM jobs;
DROP TABLE jobs;
-- 1
ALTER TABLE jobs ADD hiredate DATE;
INSERT INTO jobs (jobno, jobname, sal, comm, hiredate) VALUES (1, 'manager', 5000, 1000, SYSDATE);
-- 2
SELECT ROUND((sal + comm) * 12) AS annual_salary FROM jobs WHERE jobname = '部门经理';
-- 3
SELECT jobname,
       CASE jobno
          WHEN 1 THEN ROUND((sal + comm) * 15)
          WHEN 2 THEN ROUND((sal + comm) * 13)
          ELSE ROUND((sal + comm) * 12)
       END AS annual_salary
FROM jobs;

4.JDBC：增删改

1).软件

Eclipse + Navicat + Oracle11 + jdk1.8 +ojdbc14.jar

2).思路

3).SQL

(1).表信息

(2).建表语句

CREATE TABLE T_OWNERS(
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(30),
ADDRESSID NUMBER,
HOUSENUMBER VARCHAR2(30),
WATERMETER VARCHAR2(30),
ADDDATE DATE,
OWNERTYPEID NUMBER
);

select * from T_OWNERS;

(3).图示

4).实操

(1)创建工程

(2)创建数据库连接

package cn.itcast.waterboss.dao;

import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * 基本访问类
 * @param: 获取数据库连接 - 关闭资源
 * @author Ty
 *
 */

public class BaseDao {
	// 1.加载驱动
	static {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	// 2.获取数据库连接
	public static java.sql.Connection getConnection(){
	// thin: 不安装oracle的额外应用 即可连接服务器	
		try {
			return DriverManager.getConnection("jdbc:oracle:thin:@192.168.10.154:1521:orcl", "oracleTest", "123456");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
		
	}
	// 3.关闭资源 ： 
	// 参数(结果集 ,执行对象,连接对象)
	
	public static void closeAll(java.sql.ResultSet rs,java.sql.Statement stmt,java.sql.Connection conn) {
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		if(stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}	
	}	
}

(3)创建实体类

package cn.itcast.waterboss.entity;

import java.util.Date;

/**
 * 实体类
 * @author Ty
 *
 */


public class Owners {
	private Long id;
	private String name;
	private Long addressid;
	private String housenumber;
	private String watermeter;
	private Date adddate;
	private Long ownertypeid;
	
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Long getAddressid() {
		return addressid;
	}
	public void setAddressid(Long addressid) {
		this.addressid = addressid;
	}
	public String getHousenumber() {
		return housenumber;
	}
	public void setHousenumber(String housenumber) {
		this.housenumber = housenumber;
	}
	public String getWatermeter() {
		return watermeter;
	}
	public void setWatermeter(String watermeter) {
		this.watermeter = watermeter;
	}
	public Date getAdddate() {
		return adddate;
	}
	public void setAdddate(Date adddate) {
		this.adddate = adddate;
	}
	public Long getOwnertypeid() {
		return ownertypeid;
	}
	public void setOwnertypeid(Long ownertypeid) {
		this.ownertypeid = ownertypeid;
	}
}

(4)构建增删改方法

package cn.itcast.waterboss.dao;

import java.sql.SQLException;

import cn.itcast.waterboss.entity.Owners;



public class OwnersDao {

	//	增
	public static void add(Owners owners) {
		// 1.初始化执行/连接对象为空
		java.sql.Connection conn = null;
		// 预处理：防止注入攻击
		java.sql.PreparedStatement stmt = null;

		try {
			// 2.获取数据库连接
			conn = BaseDao.getConnection();
			// 3.赋值
			stmt = conn.prepareStatement("insert into T_OWNERS values(?,?,?,?,?,?,?)");
			// 参数索引从1开始
			stmt.setLong(1, owners.getId());
			stmt.setString(2, owners.getName());
			stmt.setLong(3, owners.getAddressid());
			stmt.setString(4, owners.getHousenumber());
			stmt.setString(5, owners.getWatermeter());
			// 日期转换
			stmt.setDate(6, new java.sql.Date(owners.getAdddate().getTime()));
			stmt.setLong(7, owners.getOwnertypeid());
			// 4.语句执行
			stmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 5.关闭
			BaseDao.closeAll(null, stmt, conn);
		}
	}

	//  改
	public static void update(Owners owners) {

		java.sql.Connection conn = null;
		java.sql.PreparedStatement stmt = null;

		try {
			conn = BaseDao.getConnection();
			stmt = conn.prepareStatement("update T_OWNERS set name=?,addressid=?,housenumber=?,"
					+ "watermeter=?,adddate=?, ownertypeid=? where id=?");
			stmt.setString(1, owners.getName());
			stmt.setLong(2, owners.getAddressid());
			stmt.setString(3, owners.getHousenumber());
			stmt.setString(4, owners.getWatermeter());
			stmt.setDate(5, new java.sql.Date(owners.getAdddate().getTime()));
			stmt.setLong(6, owners.getOwnertypeid());
			// where限制id在最后 
			stmt.setLong(7, owners.getId());
			stmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(null, stmt, conn);
		}
	}

	//  删
	public static void delete(Long id) {

		java.sql.Connection conn = null;
		java.sql.PreparedStatement stmt = null;

		try {
			conn = BaseDao.getConnection();
			stmt = conn.prepareStatement("delete from T_OWNERS where id=?");
			stmt.setLong(1, id);
			stmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(null, stmt, conn);
		}
	}
	
}

(5)代码测试

// 增
package cn.itcast.waterboss.test;

import java.util.Date;

import cn.itcast.waterboss.dao.OwnersDao;
import cn.itcast.waterboss.entity.Owners;

public class testAdd {
	public static void main(String[] args) {
		Owners owners = new Owners();
		owners.setId(1L);
		owners.setName("王强");
		owners.setAddressid(1L);
		owners.setHousenumber("2-2");
		owners.setWatermeter("1234");
		owners.setAdddate(new Date());
		owners.setOwnertypeid(1L);
		OwnersDao.add(owners);
	}
}

// 改
package cn.itcast.waterboss.test;

import java.util.Date;

import cn.itcast.waterboss.dao.OwnersDao;
import cn.itcast.waterboss.entity.Owners;

public class testUpdate {
	public static void main(String[] args) {
		Owners owners = new Owners();
		owners.setId(1L);
		owners.setName("王强");
		owners.setAddressid(1L);
		owners.setHousenumber("2-1");
		owners.setWatermeter("1234");
		owners.setAdddate(new Date());
		owners.setOwnertypeid(1L);
		OwnersDao.update(owners);
	}
}

// 删
package cn.itcast.waterboss.test;

import cn.itcast.waterboss.dao.OwnersDao;

public class testDelete {
	public static void main(String[] args) {
		OwnersDao.delete(1L);
	}
}

效果图示

参考资源

5.伪x

1).伪列

(1).特征

是否存储在表中	0
是否可以增删改	0
查询	1
实例	ROWID - ROWNUM

(2).实例

1.ROWID

实质：多了个标识

2.ROWNUM

实质：加序号

运算符(支持)：<= ;<
应用：分页查询

2).分页查询

伪列的实际应用

思路：通过子查询

– 1.基本查询(T_ACCOUNT里面：10-20)
– 2.基于排序的分页(降序)
基于排序要在嵌套一层子查询
ROWNUM：在排序前生成(一查询即生成)

-- 1.分页查询(T_ACCOUNT里面：10-20)
SELECT *
FROM (
SELECT ROWNUM r, t.*
FROM T_ACCOUNT t 
)
WHERE r < 20 AND r > 10

-- 2.基于排序的分页

SELECT *
FROM(
SELECT ROWNUM r,t.*
FROM (
SELECT *
FROM T_ACCOUNT t 
ORDER BY USENUM
)t )
WHERE r > 10 AND r < 20

示意图

3).伪表

不实际存在的表
提供了各种系统和元数据信息
用于查询和管理数据库中的对象和引用

示例	内容	SQL
DUAL	只包含一个列和一行数据	SELECT * FROM dual;
USER_TABLES	当前用户拥有的所有表的列表	SELECT TABLE_NAME FROM USER_TABLES;
USER_TAB_COLUMNS	当前用户拥有的所有表中列	SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ‘MY_TABLE’;

4).

6.函数

Oracle遵循更为严格SQL语法,SELECT后面一定要跟FROM
在函数上也和MySQL有一定差异
如

1).常用函数

字符串截取(MySQL → Oracle)
substring → substr
当前日期
current_date → sysdate

2).日期函数

需求：
1.获取当前时间
2.在当前日期基础上加指定的月
3.求所在月最后一天

select sysdate from dual;
select add_months(sysdate,2) from dual;
select last_day(sysdate) from dual;

3).

7.集合运算

运算	关键字
交集	INTERSECT
并集	UNION
差集	MINUS

不去掉重复记录：UNION ALL
MySQL中，MINUS被替换为了EXCEPT

差集实例：

select * from t_owners where id<=7
minus
select * from t_owners where id>=5;

*待完善

8.事务(MySQL举例)

/*
 Navicat Premium Data Transfer

 Source Server         : MySQL
 Source Server Type    : MySQL
 Source Server Version : 80032
 Source Host           : localhost:3306
 Source Schema         : ssm

 Target Server Type    : MySQL
 Target Server Version : 80032
 File Encoding         : 65001

 Date: 28/06/2023 17:17:46
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `id` int NOT NULL,
  `name` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '图书名称',
  `number` int NOT NULL COMMENT '馆藏数量',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1019 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci COMMENT = '图书表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES (15, '云计算', 20);
INSERT INTO `book` VALUES (1000, 'Java程序设计', 10);
INSERT INTO `book` VALUES (1001, '数据结构', 10);
INSERT INTO `book` VALUES (1002, '设计模式', 10);
INSERT INTO `book` VALUES (1003, '编译原理', 10);
INSERT INTO `book` VALUES (1005, '操作系统', 11);
INSERT INTO `book` VALUES (1006, '计算机网络', 12);
INSERT INTO `book` VALUES (1007, '计算机网络', 12);
INSERT INTO `book` VALUES (1008, '计算机组成与原理', 12);
INSERT INTO `book` VALUES (1010, '编译原理', 15);
INSERT INTO `book` VALUES (1011, 'Java程序设计', 10);
INSERT INTO `book` VALUES (1012, '数据结构', 10);
INSERT INTO `book` VALUES (1013, '设计模式', 10);
INSERT INTO `book` VALUES (1014, '编译原理', 10);
INSERT INTO `book` VALUES (1015, '操作系统', 11);
INSERT INTO `book` VALUES (1016, '计算机网络', 2);
INSERT INTO `book` VALUES (1017, '计算机网络', 22);
INSERT INTO `book` VALUES (1018, '计算机组成与原理', 12);
INSERT INTO `book` VALUES (1019, '编译原理', 15);

SET FOREIGN_KEY_CHECKS = 1;

需求：
1017向1016借若干本书
分析：
借多少？够不够借？借完后两者如何变化？
思路：
使用事务(三部曲：开启-提交-回滚)实现
核心步骤：
事务开启 - 借书 -
(无异常)：提交事务
(有异常)：事务回滚

// 核心代码(测试类)
public class BorrowBooks {
    public static void main(String[] args) {
    
        int borrowerId = 1017;
        int lenderId = 1016;
        int borrowAmount = 10;

        // 开启数据库连接
        Connection conn = BookDao.getConnection();

        try {
            // 开启事务
            conn.setAutoCommit(false);

            // 借阅书籍
            borrowBooks(conn, lenderId, borrowerId, borrowAmount);

            // 提交事务
            conn.commit();


        } catch (SQLException e) {
            e.printStackTrace();
            try {
                // 回滚事务
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            // 关闭数据库连接
            BookDao.closeAll(null, null, conn);
        }
    }

    private static void borrowBooks(Connection conn, int lenderId, int borrowerId, int borrowAmount) throws SQLException {
        // 查询借出书籍数量
        String querySql = "SELECT number FROM book WHERE id = ?";
        PreparedStatement queryStmt = conn.prepareStatement(querySql);
        queryStmt.setInt(1, lenderId);
        ResultSet resultSet = queryStmt.executeQuery();

        int availableBooks = 0;
        if (resultSet.next()) {
            availableBooks = resultSet.getInt("number");
        }

        if (availableBooks < borrowAmount) {
            System.out.println("书籍数量不足！剩余 " + availableBooks + " 本书。无法借阅 " + borrowAmount + " 本书。");
            return;
        } else {


            // 扣除借出书籍数量
            String lendSql = "UPDATE book SET number = number - ? WHERE id = ?";
            PreparedStatement lendStmt = conn.prepareStatement(lendSql);
            lendStmt.setInt(1, borrowAmount);
            lendStmt.setInt(2, lenderId);
            lendStmt.executeUpdate();

            // 增加借入书籍数量
            String borrowSql = "UPDATE book SET number = number + ? WHERE id = ?";
            PreparedStatement borrowStmt = conn.prepareStatement(borrowSql);
            borrowStmt.setInt(1, borrowAmount);
            borrowStmt.setInt(2, borrowerId);
            borrowStmt.executeUpdate();

            // 关闭语句
            lendStmt.close();
            borrowStmt.close();
            System.out.println("成功借阅书籍！");
        }
    }
}

// BaseDao(工具类)
public class BookDao {
    
    static {
        try {
           Class.forName("com.mysql.cj.jdbc.Driver");

        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    // 2.获取数据库连接

    public static java.sql.Connection getConnection(){
        try {
            return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm", "root", "123456");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;

    }

    // 3.关闭资源 ：
    // 参数(结果集 ,执行对象,连接对象)

    public static void closeAll(java.sql.ResultSet rs,java.sql.Statement stmt,java.sql.Connection conn) {
        if(rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

        }
        if(stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

}

// 实体类
@TableName("book")
@Data
public class Book {
    @TableId(type = IdType.AUTO)
    private int id;
    @TableField("name")
    private String name;
    @TableField("number")
    private int number;

    public Book(int id, String name, int number) {
        this.id = id;
        this.name = name;
        this.number = number;
    }

    public Book() {

    }
}

代码结构：
思考：
为什么上述的业务需求中要使用事务？
如果不使用事务(三部曲)可能会发生什么情况？

事务的特性
思考上面的问题需要了解一下事务的特性
首先事务具有一致性-隔离性
事务前后的数据，保持业务上的一致；
事务进行，其他事务受限;

即当a在借书操作的业务未完成时,b借书的业务无法被响应,
且a在借书前后可通过事务命令来保证数据前后的一致性。

如果不使用事务即数据的一致性难以被保持。
两种操作：
更新借出方的书籍数量和更新借入方的书籍数量。
如果其中一个操作成功，而另一个操作失败（如网络中断、系统崩溃等），会导致数据不一致的情况，例如借出方书籍减少了，但借入方的书籍数量未增加

9.多表练习

1.建表语句

CREATE TABLE student(
Sid VARCHAR2(10) PRIMARY KEY,
Sname NVARCHAR2(10) NOT NULL,
Ssex NVARCHAR2(10) NOT NULL,
Sbirthday DATE,
Sclass VARCHAR2(5)
);

INSERT INTO student VALUES('108', '曾华', '男', TO_DATE('1977-09-01', 'YYYY-MM-DD'), '95033');
INSERT INTO student VALUES('110', '张三', '男', TO_DATE('1977-09-01', 'YYYY-MM-DD'), '95032');
INSERT INTO student VALUES('105', '匡明', '男', TO_DATE('1975-10-02', 'YYYY-MM-DD'), '95031');
INSERT INTO student VALUES('107', '王丽', '女', TO_DATE('1976-01-23', 'YYYY-MM-DD'), '95033');
INSERT INTO student VALUES('101', '李军', '男', TO_DATE('1976-02-20', 'YYYY-MM-DD'), '95033');
INSERT INTO student VALUES('109', '王芳', '女', TO_DATE('1975-02-10', 'YYYY-MM-DD'), '95031');
INSERT INTO student VALUES('103', '陆君', '男', TO_DATE('1974-06-03', 'YYYY-MM-DD'), '95031');

CREATE TABLE course(
Cid VARCHAR2(10) PRIMARY KEY,
Cname NVARCHAR2(10) NOT NULL,
Tid VARCHAR2(10) NOT NULL
);

INSERT INTO course VALUES('3-105', '计算机导论', '825');
INSERT INTO course VALUES('3-245', '操作系统', '804');
INSERT INTO course VALUES('6-166', '数据电路', '856');
INSERT INTO course VALUES('9-888', '高等数学', '831');

CREATE TABLE teacher(
Tid VARCHAR2(10) PRIMARY KEY,
Tname VARCHAR2(10),
Tsex VARCHAR2(10),
Sbirthday DATE,
Prof VARCHAR2(10),
Depart VARCHAR2(10)
);

INSERT INTO teacher VALUES('804', '李诚', '男', TO_DATE('1958-12-02', 'YYYY-MM-DD'), '副教授', '计算机系');
INSERT INTO teacher VALUES('856', '张旭', '男', TO_DATE('1969-03-12', 'YYYY-MM-DD'), '讲师', '电子工程系');
INSERT INTO teacher VALUES('825', '王萍', '女', TO_DATE('1972-05-05', 'YYYY-MM-DD'), '助教', '计算机系');
INSERT INTO teacher VALUES('831', '刘冰', '女', TO_DATE('1977-08-14', 'YYYY-MM-DD'), '助教', '电子工程系');

CREATE TABLE sc(
Sid VARCHAR2(10),
Cid VARCHAR2(10),
score NUMBER(3, 1),
CONSTRAINT pk_sc_test3 PRIMARY KEY (Sid, Cid),
FOREIGN KEY (Cid) REFERENCES course(Cid),
FOREIGN KEY (Sid) REFERENCES student(Sid)
);

INSERT INTO sc VALUES('103', '3-245', 86);
INSERT INTO sc VALUES('105', '3-245', 75);
INSERT INTO sc VALUES('109', '3-245', 68);
INSERT INTO sc VALUES('103', '3-105', 92);
INSERT INTO sc VALUES('105', '3-105', 88);
INSERT INTO sc VALUES('109', '3-105', 76);
INSERT INTO sc VALUES('101', '3-105', 64);
INSERT INTO sc VALUES('107', '3-105', 91);
INSERT INTO sc VALUES('108', '3-105', 78);
INSERT INTO sc VALUES('101', '6-166', 85);
INSERT INTO sc VALUES('107', '6-166', 79);
INSERT INTO sc VALUES('108', '6-166', 81);

图示：

2.需求

-- 1.查询没学过"张三"老师讲授的任一门课程的学生姓名

-- 2、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录

-- 3、查询和学号为108的同学同年出生的所有学生的Sid、Sname和Sbirthday列

-- 4、查询选修某课程的同学人数多于5人的教师名

-- 5、查询成绩比该课程平均成绩低的同学的成绩表

-- 6、查询至少有2名男生的班号

-- 7.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

-- 8.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

-- 9. 查询没有学全所有课程的同学的信息

3.实现

-- 4、查询选修某课程的同学人数多于5人的教师名

-- 思路
-- 1)先查询'选修某课程的同学人数多于5人'
-- 2)分析：某课程的同学人数由'cid'决定
-- 3)			要对聚合后某列进行操作：Having聚合筛选	
-- 4)最终结果：'Tname',而'cid'位于'sc' 和 'course'表中
-- 5)分析：从	'cid'关联'Tname' → 子查询/外连接
-- 6)		  'course'表的'cid'无法获取到'选修某课程的同学人数'
--          (由表结构和插入数据分析)
-- 7)       故只能由'sc'表关联'teacher'表,而'course'表的'Tid'刚好可以作为媒介

SELECT Tname 
FROM 
(SELECT cid FROM sc GROUP BY cid HAVING COUNT(*) > 5)sc,
COURSE co,TEACHER te 
WHERE co.CID = sc.CID
AND te.TID = co.tid

-- 5、查询成绩比该课程平均成绩低的同学的成绩表

-- WHERE sc1.cid = sc2.cid ：确保是同一门课的成绩进行比较
-- ROUND(AVG(score), 1)：保留小数点后1位

SELECT *
FROM(SELECT sid,cid,score FROM sc)sc1,
(SELECT cid,ROUND(AVG(score), 1) avg_score FROM sc GROUP BY cid)sc2
WHERE sc1.cid = sc2.cid 
AND sc1.score < sc2.avg_score

-- 6、查询至少有2名男生的班号

SELECT ssex,sclass,COUNT(*) count_sclass
FROM STUDENT
GROUP BY ssex,sclass
HAVING COUNT(*) >=2 
AND ssex = '男'

-- 7.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT sc1.avg_score, st.sid, st.sname
FROM
(SELECT sid,AVG(score) avg_score 
FROM sc 
GROUP BY sid 
HAVING AVG(score) >= 60)sc1
LEFT JOIN STUDENT st 
ON sc1.sid = st.sid 

-- 8.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

-- 子查询
SELECT sc1.avg_score, sc2.score
FROM(
SELECT sid,AVG(score) avg_score 
FROM sc 
GROUP BY sid) sc1,
(SELECT sid,score FROM sc) sc2 
WHERE sc1.sid = sc2.sid 
ORDER BY sc1.avg_score DESC, sc2.score DESC
-- 外连接
SELECT sc.sid, sc.score, avg_score.avg_score
FROM sc
INNER JOIN (
  SELECT sid, AVG(score) avg_score 
  FROM sc 
  GROUP BY sid
) avg_score ON sc.sid = avg_score.sid
ORDER BY avg_score.avg_score DESC;

-- 9. 查询没有学全所有课程的同学的信息

SELECT st.SID, st.SNAME,st.SSEX,st.SBIRTHDAY,st.SCLASS
FROM student st, sc
WHERE st.Sid = sc.Sid
GROUP BY st.SID, st.SNAME,st.SSEX,st.SBIRTHDAY,st.SCLASS
HAVING COUNT(sc.Cid) < (SELECT COUNT(*) FROM course);