第六周笔记
第六周
周一
范式
-
范式是符合某一种级别的关系模式的集合
-
关系数据库中的关系必须满足一定的要求，满足不同程度的要求的为不同范式
-
范式的种类：
- 第一范式（1NF）
- 第二范式（2NF）
- 第三范式（3NF）
- BC范式（BCNF）
范式的目的
1、消除冗余
1NF第一范式
表中没有多值字段
2NF 第二范式
-
不存在非关键字段对任一侯选关键字段的部分函数依赖
选课表（学号、姓名、年龄、课程名称、成绩、学分） “不满足”
-
反过来说：在关系模式里面，一个候选键能够完全决定所有的非关键字段
候选键：学号、课程名称
非关键字段：其余全部
部分函数依赖：如成绩
拆分后的关系 "满足"
- 学生（学生，姓名，年龄）
- 课程（课程名称，学分）
- 选课表（学号，课程名称，成绩）
3NF 第三范式
-
没有非关键字段传递依赖主键
-
非关键字段只依赖于主键
学生（学号，姓名，系名，系主任名）
没有传递范式就满足第三范式
- 不符合第三范式的需要拆分关系
- 学生（学号，姓名，系名）
- 系（系名，系主任名）
总结：一个关系模式就只描述一个事情
SQL
Oraacle体系结构
一个表空间可以建立多个用户、一个用户下可以建立多个表。
1、实例：一个数据库可以有n个实例。
2、用户：在实例下建立，是管理表的基本单位。
3、表空间：对数据文件的逻辑映射。
4、数据文件：.dbf、.ora文件。 数据文件是数据库的物理存储单位
5、表：
SQL> select instance_name from v$instance;
INSTANCE_NAME
--------------------------------
orcl
创建表空间
create tablespace 名字 datafile '路径/stx.dbf' size 64m;
stx.dbf是表示文件名字
SQL> create tablespace stx datafile 'F:\Oracle\database\stx.dbf' size 64m;
表空间已创建。
创建用户
create user 用户名 identified by 密码 default tablespace stx;
SQL> create user ljy identified by ljy123456 default tablespace stx;
用户已创建。
授权
grant dba to 用户名
SQL> grant dba to ljy;
授权成功。
SQL语法
SQL:{
DQL--->查询 （R）
- 基本查询
- 条件查询
- 排序
- 聚合函数
- 联表
DML--->增、删、改（C、D、U）
DDL
DCL
}
warchar2(10) utf-8一个汉字占三个字节
number
注：warchar2（）、number（） （）里面都可能指定大小
创建表
create table 表名 (
id varchar2(128),
name varchar2(256),
gender varchar2(3)
);
简单的查询与条件查询
-- 基本查询
-- 查询所有数据信息
SELECT * FROM t_student;
-- 只查询学生姓名和院系 -
SELECT stu_name , college FROM T_STUDENT;
--给字段名起别名
SELECT stu_name 姓名,college 院系 FROM T_STUDENT;
--**************************************
-- 条件查询
-- 查询所有男生
SELECT * FROM T_STUDENT WHERE GENDER = '男';
SELECT * FROM t_student ts WHERE PROVINCE ='广西';
-- 查询分数在80-90之间的学生姓名和分数
SELECT STU_NAME,SCORE FROM T_STUDENT WHERE SCORE >80 AND SCORE < 90;
-- 查询姓名中包含宇的
SELECT STU_NAME FROM T_STUDENT WHERE STU_NAME LIKE '%宇%';
-- 查询姓名只有两个字的人
SELECT stu_name, college , major FROM T_STUDENT WHERE stu_name LIKE '__';
周二
排序
--排序 降序：DESC 升序：默认不写 或者ASC
SELECT STU_NAME ,SCOREFROM T_STUDENT ORDER BY SCORE DESC;
-- 2、按院系和成绩排序
SELECT stu_name, college,SCORE FROM T_STUDENT ts ORDER BY COLLEGE asc, SCORE DESC ;
聚合函数
/* 聚合函数 去重：distinct*/
-- 1、统计学生个数
SELECT COUNT(1) 问天一部人数 FROM T_STUDENT ts ;
--count（xx） xx是表示我们要统计的字段名 （一般是* 或者1 也能是具体的字段名）
SELECT COUNT(STU_NAME) 问天一部人数 FROM T_STUDENT ts ;
--2、统计智能工程学院有多少学生
SELECT COUNT(STU_ID) 智能工程学院的人数 FROM t_student WHERE COLLEGE = '智能工程学院';
--3、统计软件工程专业有多少学生
SELECT COUNT(1) 软件工程专业的人数 FROM T_STUDENT ts WHERE MAJOR = '软件工程';
--4、统计有多少个院系 "去重复" 关键字：distinct
SELECT COUNT(DISTINCT(college)) 院系个数 FROM T_STUDENT ts ;
--5、求平均分
SELECT avg(score) 平均成绩 FROM T_STUDENT ts ;
分组查询
分组关键字 ：GROUP BY
having是用在group by 函数之后， where是放在group by前面 having是聚合函数，过滤的是我们查询出来的结果
/*分组查询 分组关键字 ：GROUP BY*/
--1、统计每个院系有多少人
SELECT college ,count(stu_id) FROM T_STUDENT ts GROUP BY COLLEGE ;
--1.1、统计每个院系有多少人并按照人数降序排列
SELECT college ,count(stu_id) FROM T_STUDENT ts GROUP BY COLL EGE ORDER BY count(stu_id) DESC ;
--2、统计各院系的平均分数
SELECT college , avg(score) 平均分数 FROM T_STUDENT ts GROUP BY COLLEGE ;
--3、统计各院系的平均分数，并按照平均分排序。
SELECT college ,avg(score) 平均分数 FROM T_STUDENT ts GROUP BY COLLEGE ORDER BY avg(score);
--3.1、统计各院系的平均分数取2位，并按照平均分排序。
SELECT college ,round(avg(score),2) 平均分数 FROM T_STUDENT ts GROUP BY COLLEGE ORDER BY avg(score);
--3.2、统计各专业的平均分数取2位，并按照平均分排序。
SELECT MAJOR ,round(avg(score),2) 平均分 ,COUNT(STU_ID) FROM T_STUDENT ts GROUP BY MAJOR ORDER BY avg(SCORE) desc;
--4、统计各院系的平均分数，只统计人数在2人以上的院系 having是用在group by 函数之后， where是放在group by前面 having是聚合函数，过滤的是我们查询出来的结果
SELECT college ,COUNT(1) 人数, avg(score) 平均分
FROM T_STUDENT ts GROUP BY college having count(1)>2 ;
阅读ppt 303 where 与 having的区别
联表查询
周三
插入
insert into 表名 [(列名1，列名2)] values(值1，值2)
/*插入*/
--插入数据，不写列名，默认为所有列插入数据，Values后面的值与列名一一对应
INSERT INTO T_COLLEGE VALUES ('1','远景学院');
--插入数据，指定列名
INSERT INTO T_COLLEGE (college_name) VALUES ('大数据');
--把学生表里的信息插入college里面 (院系不重复)
--插入数据，值来源于一个查询结果集。语法有小差别，没有values关键字
INSERT INTO T_COLLEGE (college_name)
SELECT DISTINCT (college) FROM T_STUDENT ts;
删除
delete from 表名 [where....]
--删除数据
DELETE FROM T_COLLEGE tc WHERE COLLEGE_ID = '1';
DELETE FROM T_COLLEGE tc WHERE COLLEGE_NAME = '大数据';
DELETE FROM T_COLLEGE tc ;
更新
--修改
UPDATE T_STUDENT SET COLLEGE_ID ='4' WHERE COLLEGE = '外国语学院';
UPDATE T_STUDENT st ,T_COLLEGE col
SET st.COLLEGE_ID = col.COLLEGE_ID
WHERE st.COLLEGE = col.COLLEGE_NAME ;
UPDATE T_STUDENT SET COLLEGE_ID ='1' WHERE COLLEGE = '大数据与软件学院';
UPDATE T_STUDENT SET COLLEGE_ID ='2' WHERE COLLEGE = '淬炼商学院';
UPDATE T_STUDENT SET COLLEGE_ID ='3' WHERE COLLEGE = '通信与物联网工程学院';
UPDATE T_STUDENT SET COLLEGE_ID ='5' WHERE COLLEGE = '智能工程学院';
UPDATE T_STUDENT SET COLLEGE_ID ='6' WHERE COLLEGE = '远景学院';
语法差别：
-- mysql 语法
UPDATE T_STUDENT stu, T_COLLEGE col
SET stu.COLLEGE_ID = col.COLLEGE_ID
WHERE stu.COLLEGE = col.COLLEGE_NAME ;
-- oracle 语法一：
UPDATE T_STUDENT stu
SET stu.COLLEGE_ID = (SELECT col.college_id
FROM T_COLLEGE col WHERE col.COLLEGE_ID = stu.COLLEGE_ID)
WHERE EXISTS (SELECT 1 FROM T_COLLEGE col WHERE stu.COLLEGE_NAME = col.COLLEGE_NAME);
from多表查询
注意字段前加别名，防止多个表中都有该字段
/* 多表查询*/
--第一种错误，出现笛卡尔积
SELECT stu_name,college_name
FROM T_STUDENT ,T_COLLEGE tc ;
SELECT stu_name,college_name
FROM T_STUDENT stu,T_COLLEGE col
WHERE stu.college_id = col.college_id;
--查询每个学生成绩 (Java基础的成绩) 三表连接
SELECT stu_name, course_name, ts2.SCORE
FROM T_STUDENT ts ,T_COURSE tc ,T_SCORE ts2
WHERE ts.STU_ID = ts2.STU_ID AND tc.COURSE_ID = ts2.COURSE_ID;
内连接
注意字段前加别名，防止多个表中都有该字段
/*inner join 内连接*/
SELECT stu_name , college_name
FROM T_STUDENT stu
INNER JOIN T_COLLEGE col ON stu.COLLEGE_ID = COL .COLLEGE_ID ;
--三表查询
SELECT stu.stu_name, course.course_name , sc.score
FROM T_STUDENT stu INNER JOIN t_score sc
ON stu.STU_ID =sc.STU_ID
INNER JOIN T_COURSE course
ON course.COURSE_ID = sc.COURSE_ID ;
周四
JDBC（Java Data Base Connectivity）
JDBC是一种规范
使用JDBC连接Oracle数据库的步骤
0、将驱动jar包加到项目的classpath里面
1、加载驱动 //在新版本中可省略
Class.forName("oracle.jdbc.driver.OracleDriver");
2、建立连接
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "LJY", "ljy123456");
3、创建语句对象（与CRUD）
Statement stmt = connection.createStatement();
4、执行SQL语句
5、释放资源 必须释放
插入语句练习
public class InsertDemo {
public static void main(String[] args) throws Exception {
// 1、装载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2、创建连接
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "LJY", "ljy123456");
// 3、创建语句对象
Statement stmt = connection.createStatement();
// 4、执行SQL语句
stmt.execute("insert into t_college values ('7','艺术传媒学院')");
// 5、释放资源
stmt.close();
connection.close();
}
}
//3 4 有小变化的
public static void main(String[] args) throws Exception {
// 1、装载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2、创建连接
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","LJY","ljy123456");
// 3、创建语句对象
PreparedStatement statement = connection.prepareStatement("insert into t_college values ('8','艺术传媒学院')");
// 4、执行SQL语句
statement.execute();
// 5、释放资源
statement.close();
connection.close();
}
查询语句练习
public class QueryDemo {
public static void main(String[] args) throws Exception {
//装载驱动
Class.forName("oracle.jdbc.OracleDriver");
//创建连接
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "LJY", "ljy123456");
//创建语句对象
String sql = "select college_name from t_college";
PreparedStatement statement = connection.prepareStatement(sql);
//执行sql
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
System.out.println(resultSet.getString("college_name"));
}
resultSet.close();
statement.close();
connection.close();
}
}
