一、数据库的介绍
1.1 目前市场上的数据库种类
- MYSQL：开源免费的数据库，小型的数据库，已经被Oracle收购了。MYSQL6.x版本也开费；
- Orcale：收费的大型数据库，Orcale公司的产品。Oracle收购SUN公司，收购了MYSQL。
- DB2：IBM公司的数据库产品，收费的。常应用与银行系统中。
- SQLServer：微软公司收费的中型的数据库。C#、.net等语言常使用。
- SyBase：已经逐渐退出历史舞台。
- SQLite：嵌入式的小型数据库，应用于手机端。
- 常用的数据库：MYSQL、Orcale
1.2 数据库的基本概念
- 数据库的英语单词：DataBase，简称DB；
- 数据的作用：用于存储和管理数据的仓库；
- 数据库的特点：
- 持久化存储数据。其实数据库就是一个文件系统；
- 方便存储和管理数据；
- 使用了统一个方式操作数据库；
1.3 数据的启动、关闭、登录、退出
- 数据库的打开关闭
- 使用管理员权限打开cmd
- net start mysql：开启mysql的服务；
- net stop mysql：关闭mysql服务；
- mysql的登录：
- （1）mysql -u数据库名称 -p数据库密码；（连接的是自己本机的数据库）；
- （2）mysql -u数据库名称 -p 【回车】，输入密码（连接的是自己本机的数据库）；
- （3）mysql -u数据库名称 -h【目标ip】 -p目标的密码（连接的是别人的数据库）
- mysql的退出：
二、SQL
2.1 什么是SQL
2.1.1 数据类型
- timestamp：时间错类型，包含年月日时分秒 ；yyyy-MM-dd HH：mm：ss；
- 如果将来不给这个字段赋值，或者赋值为null，则默认使用当前的系统时间，来自动赋值；
2.2 SQL通用语法
2.2.1 书写格式
- SQL语句可以单行或者多行书写，用分号结尾；
- 可以使用空格和缩进来增强语句的可读性；
- MySQl数据库的SQL语句不区分大小写。
2.2.2 SQL的注释
2.3 SQL的分类
2.3.1 DDL数据定义语言
- 用来定义数据库对象：数据库、表、列等；
- 关键字：create、drop、alter等；
2.3.2 DML数据操作语言
- 用来对数据库中的表的数据进行增删改。
- 关键字：insert、delete、update等；
2.3.3 DQL数据查询语言
- 用来查询数据库中表的记录。
- 关键字：select、where等
2.3.4 DCL数据控制语言
- 用来定义数据库的访问权限和安全级别，及创建用户。
- 关键字：GRANT、REVOKE等；
2.4 DDL：操作数据库、表
2.4.1 操作数据库【CRUD】
-
C(create)：创建数据库；
-
R（retrieve）：查询；
- 查询所有数据库的名称
- 查询某个数据库的字符集；查看某个数据的创建语句；
- SHOW CREATE DATABASE 数据库名称；
-
U（update）：修改；
- 修改数据库的字符集
- alter database 数据库名称 character set 字符集名称；
-
D（delete）：删除；
- 删除数据库；
- 判断数据库是否存在，存在则删除；
- drop database if exists 数据库名称；
-
使用数据库
2.4.2 操作表
-
C(create)：创建表
-
格式：
-
create table 表名 ( ? 列名1 数据类型， ? 列名2 数据类型， ? …………………… ? 列名n 数据类型**【最后一行不要逗号】** );
-
【练习】：创建一张表 create table student( ? id int, ? name varchar(32), ? age int, ? score double(4,1), ? birthday date, ? insert_time timestamp );
-
R（retrieve）：查询表
-
U（update）：修改
- 修改表名；
- alter table 表名 rename to 新的表名；
- 查看表的字符集；
- 修改表的字符集；
- alter table 表名 character set 字符集名称；
- 添加一列；
- alter table 表名 add 列名 数据类型；
- 修改列的名称、类型；
- 即修改列名，也修改该列的数据类型
- alter table 表名 change 列名 新的列名 新的数据类型；
- 只修改列的数据类型；
- alter table 表名 modify 列名 新的数据类型；
- 删除列；
-
D（delete）：删除
- 删除表
- 判断表是否存在，若存在则删除；
- drop table if exists 表名称；
-
复制表（复制的是格式，而不是内容）
- create table 表名 like 已存在的表的名称；
2.5 DML增删改表中的数据
2.5.1 添加数据
- 语法
- insert into 表名(列名1，列名2，……列名n) values(值1，值2，……，值n)；
- 注意：
- 列名和表名要一一对应。
- 如果表名后面不定义列名，则默认给所有列添加值；
- insert into 表名 values(值1，……值n)；【有多少列，就得给多少个值】
- 除了数字类型。其他类型都需要使用引号（单双都可以）括起来；
2.5.2 删除数据
-
删除满足条件的信息
-
删除所有的信息
- delete from 表名；
- truncate table 表名；
- 删除表，然后再创建一个一模一样的表。
- 这个删除表中所有信息方式的效率要比第一个删除表中所有信息方式的效率高；
2.5.3 修改数据、
- 语法
- update 表名 set 列名1 = 值1，列名2 = 值2，……，列名n = 值n where 条件；
- 注意：
2.6 DQL：查询表中的记录
2.6.1 语法;
-
select ? 字段列表 from ? 表名列表 where ? 条件列表 group ? 分组字段 having ? 分组之后的条件 order by ? 排序 limit ? 分页限定
2.6.2 基础查询
-
多个字段的查询
- select 字段1，字段2，…… from 表名；
-
去除重复
- select distinct 字段名 from 表名；
- 【练习】：select 地址 from stu；
- 结果就是会在查询到的结果集中，重复的地址名只保留一个。
- 【练习】：select distinct 姓名，地址 from stu；
- 只有当姓名和地址都重复时，才会将该重复的值只保留一个。
-
计算列
- 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）；
- ifnull(表达式1，表达式2)：null参与的运算结果都为null；
- 表达式1：判断该字段的值是否为null
- 如果该字段为null，则用表达式2将表达式2的null替换掉；
-
起别名
- 【练习】：计算数学和英语的和值；
- select name，math ， English， ifnull（math，0）+ ifnull（English，0） from stu；
- 注意事项：
- 如果有null参与计算，那么结果都为null。
- 解决方案：ifnull，该语句的作用就是，如果判断出该列的某个值为null，则将该列的值改为指定的值。例如ifnull(math,0)，意思就是，如果某个人的数学成绩为null，那么我们就将null改为0，再进行计算，从而避免加null参与运算。
- 这样到时会将math+English当做列名，名称表达的意思不明确。因此需要改名称。
- 【练习】：计算数学和英语的和值，并将和值所在列的名称改为”总分“；
- name，math ， English， ifnull（math，0）+ ifnull（English，0）as 总分 from stu；
- as可以省略；
-
条件查询
-
where子句后面跟条件 -
运算符
- 大于、小于、>=、<=、=、<>(不等于)
- between…and
- in(集合)
- like：模糊查询
- 占位符
- _：可以代替任意一个字符；
- %：可以代替任意多个字符；
- and 或 &&
- or 或 ||
- not 或 ！
-
【练习】：
- 查询年龄大于等于20
- select * from stu where age >= 20;
- 查询年龄不等于20
- select * from stu where age <> 20;
- select * from stu where age != 20;
- 查询年龄大于等于20，小于等于30的；
- select * from stu where age between 20 and 30；
- select * from stu where age >= 20 && age <= 30;
- 查询年龄22岁、19岁、25岁；
- select * from stu where age = 22 or age = 19 or age = 25；
- select * from stu where age in (22, 19, 25);
- 查询英语成绩为null；
- select * from stu where English is null；
- 注意事项：不能写=null；
- 查询英语成绩不为null；
- select * from stu where English is not null；
- 注意事项：不能写!=null;
- 查询性马的人：
- select * from stu where name like ‘马%’；
- 查询名字里面第二个字是【化】的人；
- select * from stu where name like ‘_化%’；
- 查询名字是三个字的人；
- select * from stu where name like ‘_ _ _’;
- 查询名字里面包含马的人；】
- select * from stu where name like ‘%马%’；
2.6.3 排序查询
2.6.4 聚合查询
2.6.5 分组查询
- 语法：group by 子句；
- 注意事项：
- 分组之后的查询字段：分组字段、聚合函数；
- where 和having 的区别：
- where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来；
- where后面不能跟聚合函数，having可以跟聚合函数的判断；
- 【练习】：按照性别分组，分别查询男生和女生的平均成绩；注意null值
- select sex，avg(ifnull(english,0)) from stu group by sex;
- 【练习】：按照性别分组，分别查询男生和女生的平均成绩，以及人数；注意null值
- select sex, avg(ifnull(english,0))，count(ifnull(id,0)) from stu group by sex;
- 【练习】：按照性别分组，分别查询男生和女生的平均成绩，以及人数。要求：分数低于70分的人不参与分组；注意null值
- select sex, avg(ifnull(english,0))，count(ifnull(id,0)) from stu where english > 70 group by sex;
- 【练习】：按照性别分组，分别查询男生和女生的平均成绩，以及人数。要求：分数低于70分的人不参与分组,且分组之后，该组人数要大于2；注意null值
- select sex, avg(ifnull(english,0))，count(ifnull(id,0)) from stu where english > 70 group by sex having count(ifnull(id,0)) > 2;
- select sex, avg(ifnull(english,0))，count(ifnull(id,0)) as 人数 from stu where english > 70 group by sex having 人数 > 2;
2.6.6 分页查询
2.7 约束
- 概念：对表中的数据进行限定，保证数据的正确性、有效性、完整性。
2.7.1 非空约束
-
关键字：not null； -
作用：某一列的值不能为null； -
创建表时添加约束； create table stu(
name varchar(4) not null
);
-
表创建好后，添加非空约束； --创建好一张表
create table stu(
name varchar(4)
);
--添加约束
alter table stu modify name varchar(4) not null;
-
删除约束
alter table stu modify name varchar(4);
2.7.2 唯一约束
2.7.3 主键约束
-
关键字：primary key； -
作用：非空且唯一； -
创建表时添加主键约束； create table stu(
id int primary key
);
-
表创建好后添加主键约束； alter table stu modify id int primary key;
-
删除主键 alter table stu drop primary key;
-
注意事项：
- 一张表只能有一个字段为主键；
- 但是可以设置为多个字段为主键，也即联合主键；
- 主键就是表中记录的唯一标识；
2.7.4 自动增长
-
关键字：auto_increment； -
作用：如果某一列是数值型的，可以完成值的自动增长。 -
在创建表时添加自动增长； create table stu (
id int auto_increment
);
-
表创建好后添加自动增长 alter table stu modify id int auto_increment;
-
删除自动增长 alter table stu modify id int;
--这样不会删除掉主键约束
-
注意事项：
- 这个值的增长，是按照上一条的数据进行增长。如果上一条数据是5，那么下一条就是6。
- 一般情况下，自动增长和主键一起使用；
2.7.5 外键约束
-
关键字：foreign key； -
作用 -
在创建表时添加外键约束 create table emp(
dep_id int
constraint 起一个新的名称 foreign key (外键名称) references 主表名称(主表列名称);
);
-
【练习】：添加外键 --主表
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_id INT,--外键名称
CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);
-
创建表后添加外键； alter table employee add CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id);
-
删除外键； alter table employee drop foreign key emp_dep_fk;
-
级联操作
-
添加外键，设置级联更新； alter table employee add CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) on update cascade;
-
添加外键，设置级联更新，设置级联删除； alter table employee add CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) on update cascade on delete cascade;
2.7 DCL：管理用户、授权
2.7.1 管理用户
2.7.1.1 添加用户
-- 语法：create user '用户名'@'主机名' identified by '密码'；
create user '周佳辉' @ 'localhost' identified by '123';
2.7.1.2 删除用户
-- 语法：drop user '用户名' @ '主机名'；
drop user '周佳辉' @ 'localhost';
2.7.1.3 修改用户密码
-- 方法一：
update user set password = password('新密码') where user = '用户名';
-- 方法二：
set password for '用户名'@'主机名' = password('新密码');
2.7.1.4 查询用户
-
先切换到mysql数据库 use mysql;
-
查询user表 select * from user;
2.7.1.5 在忘掉数据库密码的情况下修改用户密码
三、数据库的设计
3.1 多表之间的关系
3.1.1 一对一
3.1.2 一对多（多对一）
3.1.3 多对多
-
【例子】
- 学生和课程：
- 分析：一个学生可以选择很多门课，一个课程也可以被很多学生选择；
-
实现方式：多对多需要借助第三张中间表。 -
实现方式的图解
3.2 数据库设计的范式
3.2.1 第一范式（1NF）
-
每一列都是不可分割的原子数据； -
第一范式的举例，以及第一范式存在的问题；
3.2.2 第二范式（2NF）
- 在1NF的基础上，非码属性必须完全依赖于候选码；（在1NF基础上消除非主属性对主码的部分函数依赖）
- 几个基本概念
- 函数依赖：A–>B，如果通过A属性(属性组)的值，可以确定唯一B属性的值，则称B依赖于A；
- 【例如】：学好–>姓名。（学号，课程名称）–>分数；
- 完全函数依赖：A–>B，如果A是一个属性组，则B属性的值确定需要依赖于A属性组中所有的属性值；
- 部分函数依赖：A–>B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中的某一些值即可；
- 传递函数依赖：A–>B，B–>C，如果A属性(属性组)的值，可以确定唯一B属性的值，再通过B属性的(属性组)的值可以确定唯一C属性的值，则称C传递依赖于A。
- 码：如果在一张表中，一个属性或属性组，被其他所有属性完全依赖，则称这个属性(属性组)为该表的码。
- 【例如】：该表中的码为：(学号，课程名称)；
- 主属性：码属性组中的所有属性；
- 非主属性：除过码属性组的属性；
- 解决的问题：解决了第一范式中存在的的第一个问题；（问题在上述截图中）
3.2.3 第三范式（3NF）
- 在2NF的基础上，任何非主属性不依赖于其他非主属性。（在2NF基础上消除传递依赖）
- 解决的问题：解决了第一范式中存在的第二个和第三个问题；（问题在上述截图中）
3.3 数据库的备份和还原
- 命令行运行：
- 语法：
- 备份：mysqldump -u用户名 -p密码 要备份的数据库名称 > 保存路径；
- 还原：
- 登录数据库：-u用户名称 -p密码；
- 创建数据库：create database 数据库名称；
- 使用数据库：use 数据库名称；
- 执行文件：source 文件路径；
- 图形化工具还原；
四、多表查询
4.1 笛卡尔积
- 有两个集合A、B，取这两个集合的所有组成情况。
- 要完成多表查询，需要消除无用的数据。
4.2 多表查询的分类
4.2.1 内连接
4.2.1.1 隐式连接
-
使用where条件消除无用数据； -
【练习】：查询所有员工信息和对应的部门信息； select * from emp, dept where emp.dept_id = dept.id;
-
【练习】：查询员工表的姓名，性别、部门表的名称； select emp.name, emp.sex, dept.name from emp, dept where emp.dept_id = dept.id;
--简化,通过对表起个别名进行简化；
--标准SQL语句写法；
select
t1.name,
t1.sex,
t2.name
from
emp t1,
dept t2
where
t1.dept_id = t2.id;
4.2.1.2 显示内连接
4.2.2 外连接
4.2.2.1 左外连接
-
语法：select 字段列表 from 表1 left outer join 表2 on 条件；【注意：outer可以省略不写】 -
查询的范围：查询的是左表所有的信息，以及其与右表的交集部分； -
【练习】： select t1.*, t2.name from emp t1 left join dept t2 on t1.dept = t2.id;
4.2.2.2 右外连接
-
语法：select 字段列表 from 表1 right outer join 表2 on 条件；【注意：outer可以省略不写】 -
查询的范围：查询的是右表所有的信息，以及其与左表的交集部分； select t1.* t2.name from emp t1 right join dept t2 on t1.dept = t2.id;
4.2.3 子查询
4.2.3.1 子查询的不同情况
-
子查询的结果是单行单列的；
-
子查询的结果是多行单列的；
-
子查询的结果是多行多列的；
-
子查询可以作为一张虚拟的表参与查询； -
【练习】：查询员工入职日期是2011-11-11日之后的员工信息和部门信息； select * from dept t1, (select * from emp where emp.date > "12-11-11") t2 where t1.id = t2.dept_id;
-- 普通查询
select * from emp t1, dept t2 where t1.id = t2.dept_id and t1.date > "2011-11-11";
4.3 练习
4.3.1 练习代码
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(20),
loc VARCHAR(20)
);
CREATE TABLE job(
id INT PRIMARY KEY AUTO_INCREMENT,
jname VARCHAR(10),
description INT
);
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
job_id INT,
FOREIGN KEY (job_id) REFERENCES job(id),
joindate DATE,
salary VARCHAR(10),
bonus INT,
dep_id INT,
FOREIGN KEY (dep_id) REFERENCES dept(id)
);
CREATE TABLE sala(
grade INT PRIMARY KEY AUTO_INCREMENT,
losalary INT,
hisalary INT
);
4.3.2 练习题
五、事物
5.1 事物的基本介绍
- 概念：如果一个包含多个步骤的业务操作，被事物管理，那么这些操作要么同时成功，要么同时失败；
5.1.1 事物的操作
-
操作步骤：
-
开启事物：start transaction; -
回滚：rollback; -
提交：commit;
-
MySQL数据库中事物默认自动提交；
-
事务提交的两种方式：
- 自动提交
- mysql就是自动提交的；
- 一条DML（增删改）语句会自动提交一次事物；
- 手动提交：
-
修改事务的提交方式：
5.2 事物的四大特征
- 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败；
- 持久性：当事务提交或回滚后，数据库会持久化的保存数据；
- 隔离性：多个事物之间，相互隔离；
- 一致性：事务操作前后，数据总量不变；
5.3 事务的隔离级别（了解）
5.4 权限的管理
5.4.1 查询权限
- 语法：show grants for ‘用户名’@‘主机名’;
- show grants for ‘list’@‘localhost’;
5.4.2 授予权限
5.4.3 撤销权限
- revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’；
六、JDBC（数据库连接）
6.1 JDBC基本概念
- jdbc：java database connectivity ，java数据库连接，使用java语言来操作数据库；
- jdbc的本质：是sun公司定义的一套操作所有数据库的规则，即接口。各个数据库厂商实现这套接口，提供数据库驱动jar包。我们可以使用这套接口（jdbc）编程，真正执行的代码是驱动jar包中的实现类。
6.2 jdbc的使用
6.2.1 使用步骤
- 导入驱动jar包；
- 注册驱动；
- 获取数据库连接对象；Connection
- 定义SQL；
- 获取执行SQL语句的对象；Statement
- 执行SQL，接收返回结果；
- 处理结果；
- 释放资源；
6.2.2 详解各个对象
6.2.2 练习题【登录】
存在SQL漏洞
6.3 JDBC控制事务
- 事务：一个包含多个步骤的业务操作。如果这个业务操作被事务管理，则这多个步骤要么同时成功，要么同时失败。
- 操作
- 开启事务：setAutoCommit(boolean autoCommit)：调用该方法，设置参数为false，即开启事务；
- 提交事务：commit();
- 回滚事务：rollback();
七、数据库连接池
7.1 概念
- 其实就是一个容器，存放数据库连接的容器；（当系统初始化好后，容器被创建，容器中会申请一些连接对象，当用户来访问数据库时，从容器中获取连接对象，用户访问完后，会将对象归还给容器）
- 好处：
- 实现：
- 标椎接口：DataSoure javax.sql包下的；
- 方法：
- 获取连接
- getConnection()
- 归还连接：Connection.close()。如果连接对象Connection是从连接池中获取的，那么调用Conn.close()方法，则不会再关闭连接了，而是归还连接；
- 一般我们不去实现它，有数据库厂商来实现；
- C3P0 ：数据库连接池技术；
- Druid：数据库连接池实现技术，由阿里巴巴提供；
7.2 C3P0：数据库连接池技术
7.2.1 步骤
- 导入jar包：c3p0和mchange-commons-java(别忘记导入数据库驱动jar包)
- 定义配置文件：
- 名称：c3p0.properties或者c3p0-config.xml
- 路径：直接将文件放在src目录下即可。
- 创建核心对象，数据库连接池对象：ComboPoolDataSource
- 获取连接：getConnection
7.3 Druid数据库连接池
7.3.1 步骤
- 导入jar包：druid
- 定义配置文件：
- 是properties形式的；
- 可以叫任意的名字，可以放在任意目录下；
- 加载配置文件：Properties；
- 获取数据库连接池对象：通过工厂来获取：DruildDtaSourceFactory;
- 获取连接：getConnection;
7.3.2 定义工具类
- 定义一个类：JDBCUtils
- 提供静态代码块加载配置文件，初始化连接池对象；
- 提供方法；
- 获取连接方法：通过数据库连接池获取连接；
- 释放资源；
- 获取连接池方法；
7.4 Spring JDBC
- 介绍：Spring框架对JDBC的简单封装。提供了一个JDBCTemplate对象简化JDBC的开发；
7.4.1 步骤
- 导入jar包；
- 创建jdbcTemplate对象。依赖于数据源DataSource
- JdbcTemplate template = newJdbcTemplate(ds);
- 调用JdbcTemplate的方法来完成CRUD的操作；
- update()：执行DML语句。增、删、改语句；
- queryForMap()：查询结果将结果封装为map集合；
- queryForList()：查询结果将结果封装为list集合；
- query()：查询结果，将结果封装为JavaBean()对象；
- queryForObject()：查询结果，将结果封装为对象；
- 练习
- 需求：
- 修改1号数据的salary 为 10000
- 添加一条记录；
- 删除刚才添加的记录；
- 查询id为1的记录，将其封装为，map集合；（将列名封装为key，值为value，将这个记录封装为一个map集合）
- 查询所有记录，将其封装为list
- 将每一条查询结果封装为一个map集合，再将map集合装载到list里面；
- 查询所有记录，将其封装为Emp对象的list集合；
- 查询总记录数；
|