黑马程序员技术交流社区
标题: 数据库阶段笔记 [打印本页]
作者: 李某 时间: 2018-12-10 14:46
标题: 数据库阶段笔记
day02-mysql1. 数据库概述数据库是存储和管理数据的仓库。
数据库软件和数据库的区别：
l 数据库大的范围，泛指所有存储和管理数据的数据库（数据库软件）
l 数据库软件：
app: application(应用)，就是一款软件（软件实现想要的功能）
数据库软件：管理和存储数据
l 使用统一的方式（指的sql）来操作所有的数据库。
2. mysql数据库安装错误：报这个状态码1006（1007）错误，原因是安装软件时，没有管理员权限。
解决安装出现1006（1007）的方案：先卸载软件，卸载完成以后，安装下面的步骤安装
第一步：以管理员命令打开 DOS命令窗口
第二步：msiexec /package mysql软件所在的路径，
比如：msiexec /package D:\soft\mysql-5.5.36-win32.msi
注意：
mysql数据库服务器软件 和 数据库 和 表 和 数据（表记录）关系：
1. 先有mysql数据库软件。
2. 在数据库软件上面，创建多个数据库(对应文件夹)
比如：mysql , performance_schema, test
3. 在数据库里面，创建多张表（对应的文件）
4. 在表（二维表格）里面，管理表记录（数据）
表区分表结构 和 表记录：
==表结构：表头（表的字段）和字段对应的类型（数据类型）
==表记录：表头（字段）对应的值
5. sql指令（掌握）5.1 sql概述什么是sql: 是一种结构化查询语言（sql不仅能从查询，还能进行添加，删除和修改操作）。
通俗的讲：sql就是一套规则（一套语法），通过sql对数据库进行查询 添加 删除和修改（crud）操作。
5.2 sql常见的分类---四类Ø DDL :数据库定义语言，作用创建数据库和数据表以及修改数据库和数据表（表结构）
Ø DCL: 数据库控制语言，作用给数据库和数据表进行授权操作（了解）
Ø DML: 数据库操作语言，作用对表中的记录（数据）进行添加 ，修改 删除操作。
Ø DQL: 数据库查询语言，作用：对表中的记录进行查询操作。
5.3 sql操作数据库和表== DDL 创建数据库和数据表（表的结构）以及修改等操作。关键词：create ,drop , alter
DDL的sql语句：truncate table 表名
== DML: 操作表记录（数据） u 添加数据： insert into
u 修改数据：update
u 删除数据： delete
== DQL: 查询表记录l 基础查询
l 条件查询
A. *模糊条件查询
B. *分组条件查询
C. *排序条件查询
D. *子查询
E. *聚合函数查询
注意：
1.字段 ，列名称，表头 ，都表示一个意思，都是表的Field
2.字符集，编码格式，编码表，都表示一个意思，都是编码
常见的编码：
n utf-8:万能的码表，支持所有的字符。
n gbk,gb2312,gb18030: 中文的码表，支持中文，还支持其它字符，但不包含日文，韩文等字符
n iso-8859-1:西欧的码表，支持西欧的字符，但不支持日文，韩文，中文等字符
day03-mysql1. 单表查询（掌握）1.0 模糊查询基本语法：where 字段 like ‘ %或者使用_’；
字符： % 表示任意字符（0-N个字符），_ 表示一个任意字符。
1.1 条件查询 ==字段 between 值1 and 值2 等同于 字段>=值1 and 字段<=值2
==字段 in(值1，----，值n) 等同于 字段=值1 or 字段=值2，---
==is null 如果值为null,不能使用=，要使用is null
==is not null 值不为null
1.2 排序查询 基本语法：-------- order by 字段 desc (asc)
1.3聚合函数查询n * sum(score)
n *avg(score)
n *count(score)
n *max(score)
n *min(score)
注意：
1. 聚合函数里面的参数：表的字段
2. 聚合函数在进行运算是，null值不参与运算。
3. 聚合函数一般是对int类型的进行运算，其它类型没有意义。
4. 特别在使用count(字段) 字段一般是主键字段（非空，唯一）,
1.4 分组查询==基本语法：group by 分组字段
注意：
1. 分组字段数据 要有 共同特点。
2. 分组查询时， select 分组字段，聚合函数 from ----
分组字段 和 聚合函数都具有共同特点
==查询 操作时，关键词的先后顺序：
1. select 字段
2. from 表名
3. where 分组前条件判断
4. group by 分组字段
5. having 分组后的条件判断
6. order by 排序字段
1.5分页查询：分页：数据太多，一次性查询出来完，展示效果不好，就是用户的体验不好。
分页操作，不同的数据库分页基本原理不一样：
mysql: limit 进行分页，其它数据库不能使用limit进行分页。
基本语法：
limit 参数1，参数2：
参数1 ：数据的起始位置（索引），从0 开始，表示第1条记录
参数2 ：每页显示的具体记录的个数。
规律 ： 参数1 = （参数2-1）* 参数2
1.6综合案例(作业)sql脚本：
-- 员工表
CREATE TABLE `employee` (
`empno`int(11) NOT NULL,
`ename`varchar(50) DEFAULT NULL,
`job`varchar(50) DEFAULT NULL,
`mgr`int(11) DEFAULT NULL,
`hiredate`date DEFAULT NULL,
`sal`decimal(7,2) DEFAULT NULL,
`COMM`decimal(7,2) DEFAULT NULL,
`deptno`varchar(11) DEFAULT NULL,
PRIMARY KEY(`empno`),
KEY`fk_emp` (`mgr`),
CONSTRAINT`fk_emp` FOREIGN KEY (`mgr`) REFERENCES `emp` (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17',50000, NULL, 10);
INSERT INTO emp VALUES (1004, '刘备', '经理', 1009,'2001-04-02', 29750, NULL, 20);
INSERT INTO emp VALUES (1006, '关羽', '经理', 1009,'2001-05-01', 28500, NULL, 30);
INSERT INTO emp VALUES (1007, '张飞', '经理', 1009,'2001-09-01', 24500, NULL, 10);
INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004,'2007-04-19', 30000, NULL, 20);
INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004,'2001-12-03', 30000, NULL, 20);
INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006,'2001-02-20', 16000, 3000, 30);
INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006,'2001-02-22', 12500, 5000, 30);
INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006,'2001-09-28', 12500, 14000, 30);
INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006,'2001-09-08', 15000, 0, 30);
INSERT INTO emp VALUES (1012, '程普', '文员', 1006,'2001-12-03', 9500, NULL, 30);
INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007,'2002-01-23', 13000, NULL, 10);
INSERT INTO emp VALUES (1011, '周泰', '文员', 1008,'2007-05-23', 11000, NULL, 20);
练习题：
-- 查询 薪水介于 8000到16000之间的 员工 的姓名 和 工作 ，薪水（两种方式）
SELECT ename , job,sal
FROM emp
WHERE sal>=8000 AND sal<=16000;
SELECT ename , job,sal
FROM emp
WHERE sal BETWEEN 8000 AND 16000;
-- 查询 受雇日期是 2000-12-17，2001-02-20 的员工姓名和受雇日期（两种方式）
SELECT ename,hiredate
FROM emp
WHERE hiredate = '2000-12-17' OR hiredate='2001-02-20';
SELECT ename,hiredate
FROM emp
WHERE hiredate IN ('2000-12-17' ,'2001-02-20');
-- 查询 工作不是文员的 薪水总和
SELECT SUM(sal)
FROM emp
WHERE job!='文员';
-- 查询受雇日期 早于 2007-05-23的 员工的薪水总和
SELECT SUM(sal)
FROMemp
WHEREhiredate <'2007-05-23';
-- 查询 每一种工作的薪水总和
SELECT job,SUM(sal)
FROM emp
GROUP BY job;
-- 查询 工作不是文员的每一种工作的薪水总和
SELECT job,SUM(sal)
FROM emp
WHERE job !='文员'
GROUP BY job;
-- 查询 查询受雇日期 早于 2007-05-23的 员工 ，并且按照部门分组，查询每个部门的薪水总和
SELECT SUM(sal),deptno
FROM emp
WHERE hiredate < '2007-05-23'
GROUP BY deptno
-- 查询 查询受雇日期 早于 2007-05-23的 员工 ，并且按照部门分组，查询每个部门的薪水总和，且部门的总薪水大于25000
SELECT SUM(sal),deptno
FROM emp
WHERE hiredate<'2007-05-23' -- 分组前条件
GROUP BY deptno -- 分组字段
HAVING SUM(sal)>95000 -- 分组后条件，只有分完组后，才能求每个部门的总薪水
-- 查询 查询受雇日期 早于 2007-05-23的 员工 ，并且按照部门分组，查询每个部门的薪水总和，且部门的总薪水大于25000，最终按照总薪水的大小进行降序排列
SELECT SUM(sal),deptno
FROM emp
WHERE hiredate<'2007-05-23' -- 分组前条件
GROUP BY deptno -- 分组字段
HAVING SUM(sal)>95000 -- 分组后条件，只有分完组后，才能求每个部门的总薪水
ORDER BY SUM(sal) DESC;-- 对最终符合条件的数据，进行排序
2. 约束（掌握）2.1约束概述：一种规范（限制），数据库约束就是对表中的数据进行规范，让数据具有某种特点。
2.2数据库的约束分类：l 主键约束
l 非空约束
l 唯一约束
l 外键约束
2.3 外键约束：==外键作用：让表与表建立关系（关联性），保证数据的有效性和正确性。
==外键特点： 1. 外键的值不能随意写。
2. 外键的值引用于另一张表的主键值
3. 外键的值可以为null(但是为null,没有任何意义)
==外键设计原则：n 主表
n 子表
多表之间的关联关系：
l 一对一
比如： 用户表 和 信息表
主表（子表）可以是任意一方。
-- 一对一：用户表和信息表
-- 分析：一个用户一个手机号，可以利用主键的唯一特点，可以把任何一方的主键设计成外键
-- 1. 用户表
CREATE TABLEtab_user(
uidINT PRIMARY KEY,
usernameVARCHAR(32)
);
-- 2.信息表
CREATE TABLE tab_info(
info_idINT PRIMARY KEY,
phoneVARCHAR(11),
CONSTRAINTinfo_user FOREIGN KEY(info_id) REFERENCES tab_user(uid)
);
l 一对多（多对一）
比如：部门表和员工表
主表：一方表,这里指的就是部门表
子表：多方表，这里指的就是员工表
外键设计在子表里面。
-- 一对多的关联（多对一） 外键的设计原则：外键放在多表里面
-- 1.设计一方表（主表）
CREATE TABLE tab_dept(
didINT PRIMARY KEY,
dnameVARCHAR(23)
);
-- 2. 设计多方表（子表），外键在多方表里面
CREATE TABLE tab_emp(
eidINT PRIMARY KEY,
enameVARCHAR(32),
dept_idINT ,-- 外键：让 tab_emp 和 tab_dept
CONSTRAINTemp_dept FOREIGN KEY(dept_id) REFERENCES tab_dept(did)
);
l 多对多（通俗来说，强化版 的 一对一）
比如：学生表和课程表
主表（子表）可以是任意一方（不推荐这样区分 和设计外键）
外键设计原则：提供一张中间表 。
-- 多对多： 学生和课程 ，设计原则 提供一张中间表 来关联 学生和课程
-- 1. 设计多方表 学生
CREATE TABLEtab_stu(
sid INT PRIMARY KEY,
snameVARCHAR(32)
);
-- 2. 设计多方表 课程
CREATE TABLE tab_course(
cidINT PRIMARY KEY,
cnameVARCHAR(32)
);
-- 3. 提供中间表：tab_stu_course
CREATE TABLE tab_stu_course(
s_id INT ,-- 外键，来关联 tab_stu
c_id INT, -- 外键，来关联 tab_course
CONSTRAINT s_c_1 FOREIGN KEY(s_id) REFERENCEStab_stu(sid),
CONSTRAINT s_c_2 FOREIGN KEY(c_id) REFERENCEStab_course(cid)
);
案例：黑马旅游商城项目-----设计多表之间的关联
设计表：
l 用户表：tab_user
l 旅游分类表：tab_category
l 旅游线路（产品）表： tab_route
l 用户收藏旅游线路表：tab_favorite
表与表之间的关联关联：
l tab_user 和 tab_favorite : 一对多
l tab_category 和tab_route : 一对多
l tab_favorite 和 tab_route: 多对多
淘宝商城项目，设计表与表之间关联
l 用户表： tab_taoUser
l 商品分类表： tab_taoCategory
l 商品表： tab_taoProduct
l 购物车表：tab_taoCart
== 级联操作(了解)u 级联操作概述：只有表与表之间有关联关系，才能进行级联操作。
作用：当主表的数据发生变化时，那么子表的数据会级联发生变化。
u 级联操作：一般情况下，操作是对update和delete.
Ø 分两种情况：
第一种情况：当主表的数据变化时，子表的数据和主表的数据保持一致。
语法： on update cascade on delete cascade
第二种情况：当主表的数据变化时，子表的数据（外键的数据）设置为null.
语法：on update set nullon delete set null.
3. 多表关联（掌握）
3.2 表的关联关系l 一对一（了解）
比如：人和身份证，用户表和身份证表
l 一对多（多对一）
比如：商品分类和商品，分类表和商品表
l 多对多
比如：老师和学生，学生和课程
4. 数据库的设计范式（了解）
5. 数据库的备份和还原（简单） day04-mysql1. 多表的查询（掌握）1.1 概述：多个表放在一起查询。
出现问题：出现笛卡尔积（就是出现的无用数据）
解决方法：消除不合理的数据（笛卡尔积）
通过主键和 外键 关联（因为外键的值 引用于主键的值）。
1.2 连接分类l 内连接
消除无效数据条件：外键的值=主键的值
l 外连接
*左外连接
*右外连接
消除无效数据条件：外键的值=主键的值
l 子查询
子查询的情况
第一种情况：子查询作为一个值（单行单列）
比如：salary = (select max(salary) from emp)
第二种情况：子查询作为一个数组（多行单列）
比如：dept_id in (SELECT id
FROM dept
where name in(‘销售部’，‘公关部’) )
消除无效数据条件：外键的值=主键的值
子查询常见的错误：
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOMEsubquery'
错误原因：子查询（subquery）不支持limit和in一块使用。
第三种情况：子查询作为一个表（多行多列的）
==案例（作业）：l 内连接的sql练习题：
-- 查询 员工和部们的信息
SELECT *
FROM emp e,dept d
WHERE e.dept_id = d.id;
-- 查询 员工姓名 ，部门编号 ，部门编号，部门名称
SELECT e.name,e.dept_id, d.id,d.name
FROM emp e,dept d
WHERE e.dept_id = d.id;
-- 查询 入职日期 早于2011年的员工姓名 ，以及所在的部门编号，部门名称
SELECT e.join_date,e.name,e.dept_id, d.id ,d.name
FROM emp e,dept d
WHERE e.dept_id = d.id ANDe.join_date<'2011';
-- 查询 不是 开发部的员工姓名 和 部门名称
SELECT
e.name,d.name
FROM emp AS e, dept AS d
WHERE e.dept_id = d.id ANDd.name<>'开发部'
-- 查询 没有部门的员工姓名 和部门编号
SELECT NAME,dept_id
FROM emp
WHERE dept_id IS NULL
-- 查询 有部门的员工姓名，部门编号 和 部门名称
SELECT
e.name,e.dept_id,d.id,d.name
FROM
emp e ,dept d
WHERE
(e.dept_id IS NOT NULL)AND (e.dept_id = d.id)
2. 事务（了解）== 事务的概述：是一组操作，这一组操作是不可以分割的单元，要不一起成功，或者一起失败
== 事务的四大特性：
l 原子性
l 持久性
l 隔离性
l 一致性
== 事务的三大读问题
l 脏读： 一个事务读到了另一个事务未提交的数据
l 不可重复读（虚读）:在一次事务中，前后读取的数据不一样。
l 幻读
== 事务的四大隔离级别（解决上面的三大读问题）
l read uncommitted：读未提交
解决：什么问题都没解决。
l read committed: 读已提交
解决：脏读
l repeatable read: 可重复读
解决：脏读，不可重复读
l serializable: 串行化
解决：所有问题都已经解决。
3. SQL分类：DCL(了解或不了解)
day05-jdbc今天的知识点介绍：
l JDBC概述
l JDBC常用的api
l JDBC工具类的封装
l 综合案例：通过jdbc对数据库进行crud操作
l 事务操作：通过jdbc完成事务的操作
jdbc常见的异常：
l java.sql.Exception: Accessdenied for user ‘root@localhost’(use password yes):
提示数据库的用户名或者密码错误。
一．jdbc（掌握）1. jdbc概述原理：jdbc其实就是一套规范（接口），不同的数据库厂商实现这一套规范，通过jdbc一套规范，操作不同的数据库。
jdbc的一套接口：java.sql.*
不同的数据库厂商实现一套接口：
//mysqlConnectionmysql数据库厂商提供的实现类
//Connection 就是sun官方提供的接口，通过操作接口，去调用相关的实现类的方法
// java.sql.Connection 接口 ，实现类：com.mysql.jdbc.MySqlConnection(mysql数据库提供的)
public MySqlConnection implements java.sql.Connection{
----------
}
操作Connection(多态)：
Connection con = new MySqlConnection();
2. jdbc的apil 1.java.sql.DriverManager(类)
作用1：注册驱动（不用），推荐使用：Class.forName(“com.mysql.jdbc.Driver”)
作用2：获取Connection con =getConnection(url,user,password);
l 2.java.sql.Connection(接口)
l 3.java.sql.Statement(接口)
l 4.java.sql.PreparedStatment(接口)
l 5.java.sql.ResultSet(接口)
3.案例<1>添加，删除，修改操作：² 存在的问题： 代码有冗余（重复度太高）
² 解决问题方案：可以把重复的代码提取出来，封装一个方法或者一个类。
² 冗余代码：
1. 第一部分：从注册驱动到获取连接对象，这些代码都是冗余
2. 第二部分：每次关闭资源（connection,statement）,这些代码也是冗余
package cn.itheima.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/*
解决：代码的冗余问题
1.从加载驱动到获取连接对象
2.关闭资源
*/
public class JDBCUtils { //工具类：简单方便
//1.提供一个方法： 获取连接对象
public staticConnection getMyConnection() throwsException {
//0.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//1.直接通过DriverManager
String url = "jdbc:mysql://localhost:3306/day04_mysql_moretab";// http://www.baidu.com:80
String user="root";
String password="root";
Connection con = DriverManager.getConnection(url,user,password);
return con;
}
//2.提供一个关闭资源的方法：closeResource
// Connection, Statement
public staticvoid closeResource(Statement stmt,Connection con) throwsException {
//1.关闭statement
if(stmt!=null){
stmt.close();
}
//2.关闭Conn
if(con!=null){
con.close();
}
}
}
上面的工具类：
问题1：获取连接对象的四个参数写死了。
*原因：四个参数写死了，后期在开发中，如果更换任意一个参数，需要修改源码，这种操作是不被允许的。
*细节：在开发中，遵循一个OOP原则。
OOP原则：对代码的修改时关闭的，对代码的扩展是开放的。
*解决方案：
步骤一：把四个连接参数配置到properties文件
步骤二：通过读取配置文件的四个参数获取连接对象。
/**
*
* @author Administrator
*1.需要把四个连接参数配置到properties
*2.通过读取配置文件，获取四个参数，得到连接对象了
*步骤一：1.在src下面创建一个config.properties
*/
public class JDBCUtlis2 {
//为了避免每次都加载配置文件，获取四个连接参数，需要把这些操作放在静态代码块（只加载一次）
private staticString driver=null;
private staticString url=null;
private staticString user=null;
private staticString password=null;
static{
try{
//----读取配置文件，获取四个连接参数
//创建Properties对象
Properties pro= new Properties();
//获取配置文件的字节输入流，让pro对象加载
ClassLoader loader=JDBCUtlis2.class.getClassLoader();
InputStream in= loader.getResourceAsStream("config.properties");
pro.load(in);
driver=pro.getProperty("driverUrl");
url= pro.getProperty("urla");
user=pro.getProperty("usera");
password=pro.getProperty("passworda");
}catch(Exceptione){
e.printStackTrace();
}
}
//1.提供一个方法： 获取连接对象
public staticConnection getMyConnection() throwsException {
//0.加载驱动
Class.forName(driver);
//1.直接通过DriverManager
Connection con = DriverManager.getConnection(url,user,password);
return con;
}
//2.提供一个关闭资源的方法：closeResource
// Connection, Statement
public staticvoid closeResource(Statement stmt,Connection con) throwsException {
//1.关闭statement
if(stmt!=null){
stmt.close();
}
//2.关闭Conn
if(con!=null){
con.close();
}
}
public staticvoid main(String[] args) throwsException{
getMyConnection();
}
}
day06-jdbc今日内容:（全部掌握）
l 数据库连接池
l 小框架（组件）spring的模版类：JDBCTemplate(crud操作)
1. 数据库连接池: DataSource ConnectionPool== 概述：
存储和管理Connection对象的容器，这个容器称之为数据库连接池
优点：
1. 不用每次都创建连接对象，可以从数据库连接池取
2. 在没有数据库连接池时，使用完connection以后，释放connection(销毁)
如果送数据库连接池里面取connection使用，使用完以后，归还给数据库连接池。
== 常见的数据库连接池：
l C3P0: 数据库连接池技术: ComboPooledDataSource
注意细节： 配置文件的名称必须是c3p0,并且放在src下面，所以在代码实现里不需要指出配置文件，因为会自动查找到。
l Druid: 数据库连接池技术（温少）
注意：配置文件的名称和位置没有要求，在代码实现里面，需要指出配置文件的路径并且加载配置文件。
l 数据库连接分析：
Ø 数据库连接池，初始化连接connection,需不需要导入数据库驱动包？
回答：除了数据库连接池自身的jar以外，使用哪种数据库，需要导入数据库的jar包
Ø 数据库连接池：需不要自己初始化（创建）connection ,存到池里面？
回答：需要自己初始化连接对象，初始化的配置在配置文件里面。
Ø 数据库连接池，初始化连接connection,需不需要四个连接参数？
回答：需要四个连接参数（driverClass,jdbcUrl ,user ,password）在配置文件里面
2.spring提供的模版类：JDBCTemplate ===模版类概述：
本质也是类，作用操作数据库，非常简单方便。
为什么这么简单方便？
原生的jdbc代码操作数据库时
1.加载驱动-->2.获取连接-->3.编写sql-->4.执行sql语句的对象-->5.执行sql处理返回值-->6.释放资源
模板类代码操作数据库时
编写sql-->加载驱动-->获取连接-->执行sql处理返回值-->释放资源
| 欢迎光临 黑马程序员技术交流社区 (http://bbs.itheima.com/) |
黑马程序员IT技术论坛 X3.2 |