第一章 数据库介绍
1.1、简介
数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种 用户或应用共享的数据集合。
数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软 件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。 用户通过数据库管理系统访问数据库中的数据。
数据库软件应该为数据库管理系统，数据库是通过数据库管理系统创建和操作的。
数据库：存储、维护和管理数据的集合。
1.2、常见数据库管理系统
- Oracle：Oracle数据库被认为是业界目前比较成功的关系型数据库管理系统。Oracle数据库可以运行 在UNIX、Windows等主流操作系统平台，完全支持所有的工业标准，并获得最高级别的ISO标准安全性 认证。
- MySQL：MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle旗下 产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。
- DB2：DB2是IBM公司的产品，DB2数据库系统采用多进程多线索体系结构，其功能足以满足大中公司 的需要，并可灵活地服务于中小型电子商务解决方案。
Microsoft SQL Server：SQL Server 是Microsoft 公司推出的关系型数据库管理系统。具有使用方便 可伸缩性好与相关软件集成程度高等优点。
(MySQL使用最多)
1.3、三大范式(规范)
什么是三大范式：
第一范式：无重复的列。当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一 范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的 关系模式中实现不了。
第二范式：属性完全依赖于主键 [ 消除部分子函数依赖 ]。如果关系模式R满足第一范式，并且R得所有 非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。第二范式（2NF）是 在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第 二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上 一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。
第三范式：属性不依赖于其它非主属性 [ 消除传递依赖 ]。设R是一个满足第一范式条件的关系模式，X 是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF. 满足 第三范式（3NF）必须先满足第二范式（2NF）。第三范式（3NF）要求一个数据库表中不包含已在其 它表中已包含的非主关键字信息。
注：关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性
第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是 否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非 主键列。
第二章 SQL语言
2.1 概述
SQL：Structure Query Language（结构化查询语言），SQL被美国国家标准局（ANSI）确定为关 系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准。
各数据库厂商都支持ISO的SQL标准，普通话
各数据库厂商在标准的基础上做了自己的扩展，方言
SQL 是一种标准化的语言，它允许你在数据库上执行操作，如创建项目，查询内容，更新内容， 并删除条目等操作。
Create, Read, Update, and Delete 通常称为CRUD操作。
2.2、SQL语句分类
DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等。
- DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）增删改。
DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。
- DQL（Data Query Language）：数据查询语言，用来查询记录（数据）查询。
注意：sql语句以;结尾
mysql中的关键字不区分大小写
2.3 DDL操作数据库
1、创建
CREATE DATABASE语句用于创建新的数据库：
编码方式:gb2312,utf-8,gbk,iso-8859-1
//create database 数据库名
CREATE DATABASE mydb1;
//create database 数据库名 character set 编码方式
CREATE DATABASE mydb2 character SET GBK;
//create database 数据库名 set 编码方式 collate 排序规则
CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;
2、查看数据库
1、创建
CREATE DATABASE语句用于创建新的数据库：
编码方式:gb2312,utf-8,gbk,iso-8859-1
//create database 数据库名
CREATE DATABASE mydb1;
//create database 数据库名 character set 编码方式
CREATE DATABASE mydb2 character SET GBK;
//create database 数据库名 set 编码方式 collate 排序规则
CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;
2 、查看数据库
查看当前数据库服务器中的所有数据库
show databases;
查看前面创建的mydb2数据库的定义信息
//show create database 数据库名;
Show CREATE DATABASE mydb2;
3 、修改数据库‘
alter database 数据库名 character set 编码方式
查看服务器中的数据库，并把mydb2的字符集修改为utf8;
ALTER DATABASE mydb2 character SET utf8;
4、删除数据库
drop database 数据库名
DROP DATABASE mydb3;
5、其他语句
查看当前使用的数据库
Select database();
切换数据库: use 数据库名
USE mydb2;
2.4、DDL操作表
CREATE TABLE语句用于创建新表。
语法：
CREATE TABLE 表名(
列名1 数据类型 [约束],
列名2 数据类型 [约束],
列名n 数据类型 [约束]
);
说明:表名，列名是自定义，多列之间使用逗号间隔，最后一列的逗号不能写 [约束] 表示可有可无
示例：
REATE TABLE Employees(
id INT ,
age INT ,
first VARCHAR(255),
last VARCHAR(255)
);
常用数据类型：
int：整型
double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；默认支持四舍五入
char：固定长度字符串类型； char(10) 'aaa ’ 占10位
varchar：可变长度字符串类型； varchar(10) ‘aaa’ 占3位
text：字符串类型，比如小说信息；
blob：字节类型，保存文件信息(视频，音频，图片)；
date：日期类型，格式为：yyyy-MM-dd；
time：时间类型，格式为：hh:mm:ss timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值
datetime:日期时间类型 yyyy-MM-dd hh:mm:ss
2.4.1、其他操作
drop table 表名;
DROP TABLE table_name;
当前数据库中的所有表
show tables;
SHOW TABLES;
查看表的字段信息
desc 表名；
DESC employee;
增加列:在上面员工表的基本上增加一个image列。
alter table 表名 add 新列名 新的数据类型
ALTER TABLE employee ADD image blob;
修改job列，使其长度为60。
alter table 表名 change 旧列名 新列名 新的数据类型
ALTER TABLE employee MODIFY job varchar(60);
ALTER TABLE employee change job job varchar(60);
列名name修改为username
ALTER TABLE user CHANGE name username varchar(100);
删除image列,一次只能删一列。
alter table 表名 drop 列名
ALTER TABLE employee DROP image;
修改表名,表名改为user。
alter table 旧表名 rename新表名
alter table user rename user;
查看表格的创建细节
show create table 表名
show create table user
修改表的字符集为gbk
alter table 表名 character set 编码方式
alter table 表名 character set gbk
2.5、DML操作
DML是对表中的数据进行增、删、改的操作。不要与DDL混淆了
主要包括：INSERT 、UPDATE、 DELETE
小知识：
在mysql中，字符串类型和日期类型都要用单引号括起来。
空值：null’
（1）插入操作：INSERT
insert into 表名 values(数据值)
insert into student(stuname,stuage,stusex,birthday) values('张三1',18,'a','2000-1-1');
注意:1多列和多个列值之间使用逗号隔开 2.列名要和列值一一对应
非数值的列值两侧需要加单引号
常见错误: Data too long for column ‘stusex’ at row 1
添加数据的时候可以将列名省略->当给所有列添加数据的时候
此时列值的顺序按照数据表中列的顺序执行
nsert into student values(‘李四’,12,‘1111’,189.98,‘2000-1-1’,‘男’,‘2007-1-1’);
同时添加多行
insert into 表名(列名) values(第一行数据),(第二行数据),(),();
insert into student(stuname,stuage,stusex,birthday)
values('张三3',18,'a','2000-1-1'),
('张三4',18,'a','2000-1-1'),
('张三5',18,'a','2000-1-1'),
('张三6',18,'a','2000-1-1'),
('张三7',18,'a','2000-1-1'),
('张三8',18,'a','2000-1-1');
注意：列名与列值的类型、个数、顺序要一一对应。
参数值不要超出列定义的长度。
如果插入空值，请使用null
插入的日期和字符一样，都使用引号括起来。
练习准备
create table emp(
id int primary key,
name varchar(100) not null,
gender varchar(10) not null,
birthday date,
salary float(10,2),
entry_date date,
resume text
);
INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(1,'zhangsan','female','1990-5-10',10000,'2015-5-5-','goodgirl');
INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(2,'lisi','male','1995-5-10',10000,'2015-5-5','good boy');
INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(3,'你好','male','1995-5-10',10000,'2015-5-5','good boy');
sql中的运算符：
（1）算术运算符:+，-，*，/(除法),求余(%) 示
例：
5/2
5%2
2/5
2%5
（2）赋值运算符:=
注：赋值方向：从右往左赋值
示例： name=‘张三’
（3）逻辑运算符：
and(并且),or(或者),not（取非）
作用:用于连接多个条件时使用
（4）关系运算符：
>,<,>=,<=,!=(不等于),=(等于),<>(不等于)
补充:查询所有数据:select * from 表名
2.5.1、修改（更新）操作：
语法：UPDATE 表名 SET 列名1=列值1,列名2=列值2 … WHERE 列名=值 练习：
将所有员工薪水修改为5000元。
将姓名为’zs’的员工薪水修改为3000元。
将姓名为’aaa’的员工薪水修改为4000元,resume改为ccc。
将wu的薪水在原有基础上增加1000元。
2.6 DCL
1、创建用户
create user 用户名@指定ip identified by 密码;
create user test123@localhost IDENTIFIED by 'test123'
create user 用户名@客户端ip identified by 密码; 指定IP才能登陆
create user test456@10.4.10.18 IDENTIFIED by 'test456'
create user 用户名@‘% ’ identified by 密码 任意IP均可登陆
create user test7@'%' IDENTIFIED by 'test7'
2、用户授权：
grant 权限1,权限2,…,权限n on
数据库名.* to 用户名@IP; 给指定用户授予指定指定数据库指定权限
grant select,insert,update,delete,create on chaoshi.* to 'test456'@'127.0.0.1';
grant all on . to 用户名@IP 给指定用户授予所有数据库所有权限
grant all on *.* to 'test456'@'127.0.0.1'
3、用户权限查询
show grants for 用户名@IP;
show grants for 'root'@'%';
4、撤销用户权限
revoke 权限1,权限2,…,权限n on 数据库名.* from 用户名@IP;
REVOKE SELECT ON *.* FROM 'root'@'%' ;
5、删除用户
drop user 用户名@IP；
drop user test123@localhost;
第三章 DQL数据查询
DQL数据查询语言（重要）
数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。
查询返回的结果集是一张虚拟表。
查询关键字：SELECT
语法： SELECT 列名 FROM 表名 【WHERE --> BROUP BY–>HAVING–> ORDER BY】
* 表示所有列
SELECT 要查询的列名称
FROM 表名称
WHERE 限定条件 /*行条件*/
GROUP BY grouping_columns /*对结果分组*/
HAVING condition /*分组后的行条件*/
ORDER BY sorting_columns /*对结果分组*/
LIMIT offset_start, row_count /*结果限定*/
操作：
1>创建学生表并添加数据
#创建表stu
CREATE TABLE stu (
sid CHAR(6),
sname VARCHAR(50),
age INT,
gender VARCHAR(50)
);
#添加数据
INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);
2>创建雇员表并添加数据
#创建雇员表
CREATE TABLE emp2(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm decimal(7,2),
deptno INT
) ;
#添加数据
INSERT INTO emp2 values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp2 values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp2 values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp2 values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp2 values(7654,'MARTIN','SALESMAN',7698,'1981-09-
28',1250,1400,30);
INSERT INTO emp2 values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp2 values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp2 values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp2 values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp2 values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp2 values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
3>创建部门表并添加数据
创建部门
CREATE TABLE dept(
deptno INT,
dname varchar(14),
loc varchar(13)
);
#添加数据
INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');
3.1 简单查询
查询所有列
select * from stu;
查询指定列
select sid.sname.age from stu;
3.2 条件查询
条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字： =、!=、<>、<、<=、>、>=; BETWEEN…AND； IN(set)； IS NULL； AND；OR； NOT；
(1)查询性别为女，并且年龄50以内的记录
select * from stu where gender='female' and age<50;
(2)查询学号为S_1001，或者姓名为liSi的记录
select * from stu where sid='s_1001' or sname='lisi';
(3)查询学号为S_1001,S_1002,S_1003的记录
列名 in (列值1,列值2)
SELECT * FROM stu where sid IN('S_1001','S_1002','S_1003');
(4)查询学号不是S_1001,S_1002,S_1003的记录
select * from tab_student where sid not in ('s1001','s1002','s_1003')
(5)查询年龄为null的记录
select * from stu where age is null;
(6)查询年龄在20到40之间的学生记录
SELECT * FROM stu WHERE age>=20 AND age<=40;
或者:列名 between 开始值 and 结束值；//注意:1.开始值<结束值 2.包含临界值的
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;
(7) 查询性别非男的学生记录
SELECT * FROM stu WHERE gender!='male';
或者
SELECT * FROM stu WHERE NOT gender='male';
(8)查询姓名不为null的学生记录
SELECT * FROM stu WHERE NOT sname IS NULL;
或者
SELECT * FROM stu WHERE sname IS NOT NULL;
3.3 模糊查询
当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。
语法: 列名 like ‘表达式’ //表达式必须是字符串
通配符:
_(下划线): 任意一个字符 %：任意0~n个字符,‘张%’
(1)查询姓名由3个字构成的学生记录
select * from stu where sname like '___';
模糊查询必须使用LIKE关键字。其中 “”匹配任意一个字，3个“”表示3个任意字。
(2)查询姓名由5个字母构成，并且第5个字母为“i”的学生记录
select * from stu where sname like '____i'
(3)查询姓名以“z”开头的学生记录
SELECT * FROM stu WHERE sname LIKE 'z%';
其中“%”匹配0~n个任何字母。
(4)查询姓名中第2个字母为“i”的学生记录
SELECT * FROM stu WHERE sname LIKE '_i%';
(5)查询姓名中包含“a”字母的学生记录
SELECT * FROM stu WHERE sname LIKE '%a%';
3.4 字段控制查询
(1)去除重复记录
去除重复记录（两行或两行以上记录中系列的上的数据都相同），例如emp表中sal字段就存在相同的 记录。当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用 DISTINCT：
SELECT DISTINCT sal FROM emp;
(2))查看雇员的月薪与佣金之和
因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不 是数值类型，那么会出错。
SELECT *,sal+comm FROM emp;
comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出 现NULL。下面使用了把NULL转换成数值0的函数IFNULL：
SELECT *,sal+IFNULL(comm,0) FROM emp;
(3)给列名添加别名
在上面查询中出现列名为sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为 total：
select *,sal+IFNULL(comm,0) AS total from emp;
给列起别名时，是可以省略AS关键字的:
select *,sal+IFNULL(comm,0) total FROM emp;
3.5 排序
语法： order by 列名 asc/desc //asc
升序 desc 降序 默认不写的话是升序
(1) 查询所有学生记录，按年龄升序排序
select *
from stu
order by age asc;
或者
SELECT *
FROM stu
ORDER BY age;
(3) 查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
多列排序：当前面的列的值相同的时候，才会按照后面的列值进行排序
SELECT * FROM emp
ORDER BY sal DESC,empno ASC;
3.6 聚合函数
聚合函数是用来做纵向运算的函数：
COUNT(列名)：统计指定列不为NULL的记录行数；
MAX(列名)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN(列名)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM(列名)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG(列名)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
（1）count
当需要纵向统计时可以使用count()
查询emp表中记录数
SELECT COUNT(*) AS cnt FROM emp;
查询emp表中有佣金的人数
SELECT COUNT(comm) cnt FROM emp;
注意，因为count()函数中给出的是comm列，那么只统计comm列非NULL的行数。
查询emp表中月薪大于2500的人数：
SELECT COUNT(*) FROM emp WHERE sal > 2500;
统计月薪与佣金之和大于2500元的人数：
SELECT COUNT(*) AS cnt FROM empWHERE sal+IFNULL(comm,0) > 2500;
查询有佣金的人数，以及有领导的人数：
SELECT COUNT(comm), COUNT(mgr)FROM emp;
(2) SUM和AVG
当需要纵向求和时使用sum()函数。
查询所有雇员月薪和：
SELECT SUM(sal) FROM emp;
查询所有雇员月薪和，以及所有雇员佣金和：
SELECT SUM(sal), SUM(comm) FROM emp;
查询所有雇员月薪+佣金和：
SELECT SUM(sal+IFNULL(comm,0))FROM emp;
统计所有员工平均工资：
SELECT AVG(sal) FROM emp;
(3) MAX和MIN
查询最高工资和最低工资：
SELECT MAX(sal), MIN(sal) FROM emp;
3.7、分组查询
当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部分来分组。
注意:如果查询语句中有分组操作，则select后面能添加的只能是聚合函数和被分组的列名
3.7.1 分组查询
查询每个部门的部门编号和每个部门的工资和：
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;
查询每个部门的部门编号以及每个部门的人数：
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;
查询每个部门的部门编号以及每个部门工资大于1500的人数：
SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;
3.7.2 HVING子句
查询工资总和大于9000的部门编号以及工资和：
select deptno sum(sal)
from emp
group by deptno
having sum(sal)>9000;
注：having与where的区别:
1.having是在分组后对数据进行过滤,where是在分组前对数据进行过滤
2.having后面可以使用分组函数(统计函数)
where后面不可以使用分组函数。
WHERE 是对分组前记录的条件，如果某行记录没有满足where子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。
补充：多列分组
统计出stu表中每个班级的男女生各多少人
select gradename,gender ,count(*)
from stu
group by gender，gradename
3.8 LIMIT
LIMIT用来限定查询结果的起始行，以及总行数。
limit 开始下标,显示条数；//开始下标从0开始
limit 显示条数；//表示默认从0开始获取数据
1.查询5行记录，起始行从0开始
SELECT * FROM emp LIMIT 0, 5;
注意，起始行从0开始，即第一行开始！
2.查询10行记录，起始行从3开始
SELECT* FROM emp LIMIT 3, 10;
第三页记录起始行为20，一共查询10行； limit 20,10
pageIndex 页码值 pageSize 每页显示条数
limit (pageindex-1)*pagesize,pagesize;
查询语句书写顺序：select – from- where- groupby- having- order by-limit
查询语句执行顺序：from - where -group by -having - select - order by-limit
第四章 数据完整性
1.1 数据库的完整性
用来保证存放到数据库中的数据是有效的,即数据的有效性和准确性
确保数据的完整性 = 在创建表时给表中添加约束
完整性的分类：
实体完整性(行完整性):
域完整性(列完整性):
引用完整性(关联表完整性):
主键约束：primary key
唯一约束：unique [key]
非空约束：not null
默认约束：default
自动增长：auto_increment
外键约束: foreign key
建议这些约束应该在创建表的时候设置
多个约束条件之间使用空格间隔
示例：
create table student(
studentno int primary key auto_increment,
loginPwd varchar(20) not null default '123456',
studentname varchar(50) not null,
sex char(2) not null,
gradeid int not null,
phone varchar(255) not null,
address varchar(255) default '学生宿舍',
borndate datetime,
email varchar(50)
);
1.2 实体完整性
实体：即表中的一行(一条记录)代表一个实体（entity） 实体完整性的作用：标识每一行数据不重复。 约束类型：
主键约束（primary key）
唯一约束(unique)
自动增长列(auto_increment)
1.2.1 主键约束（primary key）
注：每个表中要有一个主键。
特点：数据唯一，且不能为null
示例： 第一种添加方式：
CREATE TABLE student( id int primary key, name varchar(50) );
第二种添加方式：此种方式优势在于，可以创建联合主键
CREATE TABLE student( id int, name varchar(50), primary key(id) );
CREATE TABLE student( classid int, stuid int, name varchar(50), primary key(classid,stuid) )
第三种添加方式：
CREATE TABLE student( id int, name varchar(50) );
ALTER TABLE student ADD PRIMARY KEY (id);
1.2.2、唯一约束(unique)
特点：数据不能重复。
CREATE TABLE student( Id int primary key, Name varchar(50) unique );
1.2.3、自动增长列(auto_increment)
sqlserver数据库 (identity-标识列)
oracle数据库(sequence-序列)
给主键添加自动增长的数值，列只能是整数类型
CREATE TABLE student( Id int primary key auto_increment, Name varchar(50) );
INSERT INTO student(name) values(‘tom’)
1.3、域完整性
域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较
域代表当前单元格
域完整性约束：数据类型 非空约束（not null） 默认值约束(default)
check约束（mysql不支持）check(sex=‘男’ or sex=‘女’)
1.3.1、数据类型
数值类型
| 类型 | 大小 | 范围（有符号） | 范围（无符号 ） | 用途 |
| ------------- | ---------------- | ----------------------- | ---------------------- | ---------- |
| tinyint | 1 字节 | (-128，127) | (0，255) | 小整数值 |
| smallint | 2 字节 | (-32 768，32 767) | (0，65 535) | 大整数值 |
| mediumint | 3 字节 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT| 4 字节 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| bigint | 8 字节 | (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744073 709 551 615) | 极大整数值 |
| float | 4 字节 | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度浮点数值 |
| double | 8 字节 | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4E-308，1.797 693 134 862 315 7 E+308) | 双精度浮点数值 |
日期类型:
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。TIMESTAMP类型有专有的自动更新特性
| 类型 | 大小(字节) | 范围 | 格式 | 用途 |
| --------- | ------ | ---------------------------------------- | ------------------- | ------------ |
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 结束时间是第 *2147483647* 秒，北京时间 *2038-1-19 11:14:07*，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 ,当更新数据的时候自动添加更新时间
字符串类型:
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET
| 类型 | 大小 | 用途 |
| ---------- | ----------------- | ------------------ |
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65 535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。
1.3.2、非空约束
not null
CREATE TABLE student( Id int primary key, Name varchar(50) not null, Sexvarchar(10) );
INSERT INTO student values(1,’tom’,null);
1.3.3、默认值约束
default
CREATE TABLE student( Id int primary key, Name varchar(50) not null, Sexvarchar(10) default '男' );
insert intostudent1 values(1,'tom','女');
insert intostudent1 values(2,'jerry',default);
1.4、引用完整性
（参照完整性）
外键约束：FOREIGN KEY
示例：
CREATE TABLE student(id int primary key, name varchar(50) not null, sex varchar(10) default '男' );
create table score(
id int primary key,
score int,
sid int ,
constraint fk_score_sid foreign key(sid) references student(id) );
constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)
外键列的数据类型一定要与主键的类型一致
第二种添加外键方式。
ALTER TABLEscore1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id)
第五章 多表查询
多个表之间是有关系的，那么关系靠谁来维护?
多表约束：外键列
5.1、多表的关系
5.1.1、一对多/多对一的关系
客户和订单，分类和商品，部门和员工
一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键.
5.1.2、多对多关系
学生和课程
多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一 方的主键.
5.1.3、一对一关系
在实际的开发中应用不多.因为一对一可以创建成一张表.
两种建表原则：
唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外 键设置为unique.
主键对应：让一对一的双方的主键进行建立关系.
5.2、多表查询
多表查询有如下几种：
1. 合并结果集:UNION 、 UNION ALL
2. 连接查询
2.1内连接 [INNER] JOIN ON
2.2外连接 OUTER JOIN ON
-左外连接 LEFT [OUTER] JOIN
- 右外连接 RIGHT [OUTER] JOIN
- 全外连接（MySQL不支持）FULL JOIN
2.3 自然连接 NATURAL JOIN
3.子查询
5.2.1、合并结果集
作用：合并结果集就是把两个select语句的查询结果合并到一起！
合并结果集有两种方式：
l UNION：去除重复记录，例如：SELECT* FROM t1 UNION SELECT * FROM t2；
l UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。
注意：被合并的两个结果：列数、列类型必须相同。
5.2.2、连接查询
连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。
连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1), (a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。
那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过 条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。
示例 1：现有两张表
emp-员工表，dept-部门表
CREATE TABLE dept1(
deptno int primary key,
dname varchar(14),
loc varchar(13)
);
insert into dept1 values(10,'服务部','北京');
insert into dept1 values(20,'研发部','北京');
insert into dept1 values(30,'销售部','北京');
insert into dept1 values(40,'主管部','北京');
CREATE TABLE emp1(
empno int,
ename varchar(50),
job varchar(50),
mgr int,
hiredate date,
sal double,
comm double,
deptno int
);
insert into emp1 values(1001,'张三','文员',1006,'2019-1-1',1000,2010,10);
使用主外键关系做为条件来去除无用信息
上面查询结果会把两张表的所有列都查询出来，也许你不需要那么多列，这时就可以指定要查询的列
了。
insert into emp1 values(1002,'李四','程序员',1006,'2019-2-1',1100,2000,20);
insert into emp1 values(1003,'王五','程序员',1006,'2019-3-1',1020,2011,20);
insert into emp1 values(1004,'赵六','销售',1006,'2019-4-1',1010,2002,30);
insert into emp1 values(1005,'张猛','销售',1006,'2019-5-1',1001,2003,30);
insert into emp1 values(1006,'谢娜','主管',1006,'2019-6-1',1011,2004,40);
select * from emp,dept;
使用主外键关系做为条件来去除无用信息
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
上面查询结果会把两张表的所有列都查询出来，也许你不需要那么多列，这时就可以指定要查询的列 了。
SELECT emp.ename,emp.sal,emp.comm,dept.dname
FROM emp,dept
WHERE emp.deptno=dept.deptno;
一、内连接
上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言！
语法：
select 列名
from 表1
inner join 表2
on 表1.列名=表2.列名 //外键列的关系
where.....
等价于
select 列名
from 表1,表2
where 表1.列名=表2.列名 and ...(其他条件)
注:<1>表1和表2的顺序可以互换
<2>找两张表的等值关系时,找表示相同含义的列作为等值关系。
<3>点操作符表示“的”,格式:表名.列名
<4>可以使用as,给表名起别名,注意定义别名之后,统一使用别名
示例：
//查询学生表中的学生姓名和分数表中的分数
select name,score
from student as s
inner join scores as c
on s.studentid=c.stuid
等价于：
select name,score
from student as s,scores as c
where s.studentid=c.stuid
三表联查：
语法：
select 列名 from 表1
inner join 表2 on 表1.列名=表2.列名
inner join 表3 on 表1或表2.列名=表3.列名 where
等价于:
select 列名 from 表1,表2,表3
where 表1.列名=表2.列名 and 表1/表2.列名=表3.列名
SQL标准的内连接为：
SELECT *
FROM emp e
INNER JOIN dept d
ON e.deptno=d.deptno;
内连接的特点：查询结果必须满足条件。
练习：
student2
stuid 学员id int 主键，自增长
stuname 学员名字 varchar(10)
password 登录密码 varchar(10)
birthday 出生日期 date
数据：
1 花儿 111111 1990-02-09
2 少年 222222 1989-03-12
3 小胡 333333 1989-09-12
subject2 —科目表
subjectid 科目编号 int 主键，自增长
subjectName 科目名称 varchar(10)
数据：
1 java
2 mysql
3 html
scores2
sid 分数主键 int 主键，自增长
score 分数 int
subject 科目 int
studentid 学生编号 int
数据：
1 89 1 1
2 90 2 1
3 87 2 2
4 98 3 3
需求：1.显示出花儿的考试成绩以及对应科目
2.显示出所有考试学生的信息
3.查询出mysql的考试信息
4.查询出考试学员的总分
5.查询每科的平均分
二、外连接
包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。
显示还没有员工的部门名称?
- 外联查询
左外联:select 列名 from 主表 left join 次表 on 主表.列名=次表.列名
1.主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示null
2.主表和次表不能随意调换位置
使用场景:一般会作为子查询的语句使用
select depname,name from
(select e.*,d.depname from department d left join employee e
on e.depid=d.depid
) aa
where aa.name is null;
右外联:select 列名 from 次表 right join 主表 on 主表.列名=次表.列名
左外连接
SELECT * FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;
左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的 显示NULL。
insert into emp values(1007,'何炅','主管',1006,'2019-6-1',1011,2004,50);
b.右外连接 右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在 dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相 应的员工信息为NULL。
insert into dept values(60,'颜值部','成都');
SELECT * FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;
连接查询心得：
连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。
两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。
三、自然连接
自然连接（NATURAL INNER JOIN）：自然连接是一种特殊的等值连接，他要求两个关系表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列。.下面给出几个例子。
select * from emp e natural join dept d;
5.2.3、子查询
一个select语句中包含另一个完整的select语句。
子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么
就是子查询语句了。
l 子查询出现的位置：
a. where后，作为条为被查询的一条件的一部分；
b. from后，作表；
l 当子查询出现在where后作为条件时，还可以使用如下关键字：
a. any
b. all
l 子查询结果集的形式：
a. 单行单列（用于条件）
b. 单行多列（用于条件）
c. 多行单列（用于条件）
d. 多行多列（用于表）
示例：
\1. 工资高于JONES的员工。
分析： 查询条件：工资>JONES工资，其中JONES工资需要一条子查询。
第一步：查询JONES的工资
SELECT sal FROM emp WHERE ename='JONES';
第二步：查询高于甘宁工资的员工
SELECT * FROM emp WHERE sal > (第一步结果);
结果：
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');
\2. 查询与SCOTT同一个部门的员工。
子查询作为条件
子查询形式为单行单列
分析：
查询条件：部门=SCOTT的部门编号，其中SCOTT 的部门编号需要一条子查询。
第一步：查询SCOTT的部门编号
SELECT deptno FROM emp WHERE ename='SCOTT';
第二步：查询部门编号等于SCOTT的部门编号的员工
SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename='SCOTT');
第六章、拓展
6.1、多行新增
insert into 表名(列名) values (列值),(列值),(列值)；
6.2、多表更新
(1)update 表1,表2 set 列名=列值 where 表1.列名=表2.列名 and 其他限定条件
(2)update 表1
inner join 表2 on 表1.列名=表2.列名
set 列名=列值
where 限定条件
示例：update employee e,salary s
set title='助工',salary=1000
where e.empid=s.empid and name='李四'
6.3 多表删除
语法：
delete 被删除数据的表 from 删除操作中使用的表
where 限定条件
注:多张表之间使用逗号间隔
示例：
//删除人事部的信息
delete d,e,s from department d,employee e,salary s
where d.depid=e.depid and s.empid=e.empid and depname='人事部'
6.4、日期运算函数
now() 获得当前系统时间
year(日期值) 获得日期值中的年份
date_add(日期,interval 计算值 计算的字段);
注：计算值大于0表示往后推日期，小于0表示往前推日期
示例：
date_add(now(),interval -40 year);//40年前的日期
第7章、数据库优化
1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫 描，如：
select id from t where num is null
最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.
备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。
3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致 引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or Name = 'admin'
可以这样查询：
select id from t where num = 10
union all
select id from t where Name = 'admin'
1、union: 对两个bai结果集du进行并集操作, 不包括重复zhi行,相当于distinct, 同时进行默认dao规则的排序;
2、union all: 对两个结zhuan果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复;
5、in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了： s
elect id from t where num between 1 and 3
很多时候用 exists 代替 in 是一个好的选择
第八章、事物
事务（Transaction）是由一系列对系统中数据进⾏访问与更新的操作所组成的⼀个程序执行逻辑单元。
(1) 事务的 语法
(2) 事务的特性
(3) 事务并发问题
(4) 事务隔离级别
(5) 不同隔离级别的锁的情况(了解)
(6) 隐式提交(了解)
8.1、事物的语法
1. start transaction; begin;
2. commit; 使得当前的修改确认
3. rollback; 使得当前的修改被放弃
例子：
select * from grade
insert into grade values(4,'aa');//添加数据
零时窗口可查看，dos中窗口也可查看
BEGIN
insert into grade values(5,'a');
零时窗口可查看，dos中窗口查看不到
BEGIN
insett into grade values(5,'a');
commit;//提交
零时窗口可查看，dos中窗口能查看
BEGIN
insett into grade values(5,'a');
ROLLBACK;//事物在未提交之前都可以回滚
8.2、事物的ACID特性
\1. 原⼦性（Atomicity）
事务的原⼦性是指事务必须是⼀个原子的操作序列单元。事务中包含的各项操作在⼀次执⾏过程中，只 允许出现两种状态之一。
（1）全部执行成功
（2）全部执行失败
事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执⾏过程中出错， 会回滚到事务开始前的状态，所有的操作就像没有发⽣一样。也就是说事务是⼀个不可分割的整体，就 像化学中学过的原子，是物质构成的基本单位。
-
⼀致性（Consistency）
-
事务的一致性是指事务的执⾏不能破坏数据库数据的完整性和一致性，一个事务在执⾏之前和执行之后，数据库都必须处以⼀致性状态。
比如：如果从A账户转账到B账户，不可能因为A账户扣了钱，⽽B账户没有加钱。
-
持久性（Duration） 事务的持久性是指事务⼀旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服 务器宕机等故障。只要数据库重新启动，那么一定能够将其恢复到事务成功结束后的状态
8.3、事物的并发问题
脏读：读取到了没有提交的数据, 事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的 数据是脏数 据。 不可重复读：同⼀条命令返回不同的结果集（更新）.事务 A 多次读取同一数据，事务 B 在事务A 多次读取的 过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，结果不一致。 幻读：重复查询的过程中，数据 就发⽣了量的变化（insert， delete）。
create table users(
id int auto_increment primary key,
name varchar(10),
age int,
account int
)
insert into users values(null,'张三',25,10000),(null,'李李四',20,100),(null,'王五',23,0);
8.4、事物隔离级别
4种事务隔离级别从上往下，级别越高，并发性越差，安全性就越来越高。 ⼀般数据默认级别是 读以提交或可重复 读
查看当前会话中事务的隔离级别
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.93 sec)
设置当前会话中的事务隔离级别 mysql> set session transaction isolation level read uncommitted; Query OK, 0 rows affected (0.00 sec)
1. 读未提交（READ_UNCOMMITTED）
读未提交，该隔离级别允许脏读取，其隔离级别是最低的。换句话说，如果一个事务正在处理理某一数 据，并对其进⾏了更新，但同时尚未完成事务，因此还没有提交事务；而以此同时，允许另一个事务也 能够访问该数据。 脏读示例：
在事务A和事务B同时执行时可能会出现如下场景：
创建表格查看
讲i改为0并回转
2.读已提交（READ_COMMITTED）
读已提交是不同的事务执行的时候只能获取到已经提交的数据。 这样就不会出现上面的脏读的情况了。 但是在同一个事务中执行同一个读取,结果不一致 不可重复读示例
可是解决了脏读问题，但是还是解决不了可重复读问题。
事务A其实除了了查询两次以外，其它什什么事情都没做，结果钱就从1000变成0了了，这就是不不可重复读的 问 题。
3.可重复读（REPEATABLE_READ）
可重复读就是保证在事务处理理过程中，多次读取同一个数据时，该数据的值和事务开始时刻是一致的。 因此该事务级别限制了不可重复读和脏读，但是有可能出现幻读的数据。
幻读 幻读就是指同样的事务操作，在前后两个时间段内执行对同一个数据项的读取，可能出现不一致的结果。 诡异的更新事件
在这里插入图片描述
在修改前查询，数据不增加为原有，执行后出现新的一条数据
第九章 JDBC
1、简介
JDBC（Java DataBase Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序
Java 具有坚固、安全、易于使用、易于理解和可从网络上自动下载等特性，是编写数据库应用程序的杰出语言。所需要的只是 Java应用程序与各种不同数据库之间进行对话的方法。
JDBC可以在各种平台上使用Java，如Windows，Mac OS和各种版本的UNIX。
JDBC库包括通常与数据库使用相关的下面提到的每个任务的API。
- 连接数据库。 -
- 创建SQL或MySQL语句。 -
- 在数据库中执行SQL或MySQL查询。 -
- 查看和修改生成的记录。
2、JDBC体系结构
JDBC API支持用于数据库访问的两层和三层处理模型，但通常，JDBC体系结构由两层组成：
- JDBC API：这提供了应用程序到JDBC管理器连接。 -
JDBC驱动程序API：这支持JDBC管理器到驱动程序连接。
JDBC API使用驱动程序管理器和特定于数据库的驱动程序来提供与异构数据库的透明连接。
3、JDBC核心组件
DriverManager：
此类管理数据库驱动程序列表。使用通信子协议将来自java应用程序的连接请求 与适当的数据库驱动程序匹配。
Driver:
此接口处理与数据库服务器的通信，我们很少会直接与Driver对象进行交互。而是使用 DriverManager对象来管理这种类型的对象。
Connection：
该界面具有用于联系数据库的所有方法。连接对象表示通信上下文，即，与数据库 的所有通信仅通过连接对象。
Statement：
使用从此接口创建的对象将SQL语句提交到数据库。除了执行存储过程之外，一些派 生接口还接受参数。
ResultSet：
在使用Statement对象执行SQL查询后，这些对象保存从数据库检索的数据。它作为一 个迭代器，允许我们移动其数据。
SQLException：
此类处理数据库应用程序中发生的任何错误
4、CRUD语法介绍
SQL 是一种标准化的语言，它允许你在数据库上执行操作，如创建项目，查询内容，更新内容，并 删除条目等操作。
Create, Read, Update, and Delete 通常称为CRUD操作。
CREATE DATABASE语句用于创建新的数据库：
SQL> CREATE DATABASE DATABASE_NAME;
DROP DATABASE语句用于删除现有数据库：
SQL> DROP DATABASE DATABASE_NAME;
CREATE TABLE语句用于创建新表。语法是 -
SQL> CREATE TABLE Employees (
id INT NOT NULL,
age INT NOT NULL,
first VARCHAR(255),
last VARCHAR(255),
PRIMARY KEY ( id )
);
DROP TABLE语句用于删除现有表。
SQL> DROP TABLE table_name;
INSERT的语法类似于以下内容，其中column1，column2等表示要显示在相应列中的新数据
SQL> INSERT INTO table_name VALUES (column1, column2, ...);
SELECT语句用于从数据库中检索数据。SELECT的语法是 -
SQL> SELECT column_name, column_name, ... FROM table_name WHERE conditions;
WHERE子句可以使用比较运算符，例如=，！=，<，>，<=和> =，以及BETWEEN和LIKE运算符。 UPDATE语句用于更新数据。
SQL> UPDATE table_name
SET column_name = value, column_name = value, ...
WHERE conditions;
WHERE子句可以使用比较运算符，例如=，！=，<，>，<=和> =，以及BETWEEN和LIKE运算符。 DELETE语句用于从表中删除数据。
SQL> DELETE FROM table_name WHERE conditions;
WHERE子句可以使用比较运算符，例如=，！=，<，>，<=和> =，以及BETWEEN和LIKE运算符。
5、使用步骤
构建JDBC应用程序涉及以下六个步骤：
导入包：
需要包含包含数据库编程所需的JDBC类的包。大多数情况下，使用import java.sql.*就足够 了。
注册JDBC驱动程序：
要求您初始化驱动程序，以便您可以打开与数据库的通信通道。
打开连接：
需要使用DriverManager.getConnection（）方法创建一个Connection对象，该对象表 示与数据库的物理连接。
执行查询：
需要使用类型为Statement的对象来构建和提交SQL语句到数据库。
从结果集中提取数据：
需要使用相应的ResultSet.getXXX（）方法从结果集中检索数据
释放资源：
需要明确地关闭所有数据库资源，而不依赖于JVM的垃圾收集。
6、 JDBC连接步骤
一旦获得了连接，我们可以与数据库进行交互。JDBC Statement和PreparedStatement接口定义了使 您能够发送SQL命令并从数据库接收数据的方法和属性。
6.2、 Statement
创建语句对象
在使用Statement对象执行SQL语句之前，需要使用Connection对象的createStatement（）方法创建 一个，如下例所示：
Statement stmt = null;
try {
stmt = conn.createStatement( );
. . .
}
catch (SQLException e) {
. . .
}
finally {
. . .
}
创建Statement对象后，您可以使用它来执行一个SQL语句，其中有三个执行方法之一。
boolean execute（String SQL）
如果可以检索到ResultSet对象，则返回一个布尔值true; 否则返 回false。使用此方法执行SQL DDL语句或需要使用真正的动态SQL时。
int executeUpdate（String SQL）
返回受SQL语句执行影响的行数。使用此方法执行预期会影响 多个行的SQL语句，例如INSERT，UPDATE或DELETE语句。
ResultSet executeQuery（String SQL）
返回一个ResultSet对象。当您希望获得结果集时，请使 用此方法，就像使用SELECT语句一样。
关闭Statement对象
就像我们关闭一个Connection对象以保存数据库资源一样，由于同样的原因，还应该关闭Statement对 象。
一个简单的调用close（）方法将执行该作业。如果先关闭Connection对象，它也会关闭Statement对 象。但是，应始终显式关闭Statement对象，以确保正确清理。
Statement stmt = null;
try {
stmt = conn.createStatement( );
. . .
}
catch (SQLException e) {
. . .
}
finally {
stmt.close();
}
实例：
package com.demo01;
import java.sql.*;
public class Demo01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//1 加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2 获得链接
String userName = "root";
String passWord = "123456";
String url = "jdbc:mysql://localhost:3306/y?serverTimezone=UTC";
connection = DriverManager.getConnection(url, userName, passWord);
//3 定义sql，创建状态通道(进行sql语句的发送)
statement = connection.createStatement();
//返回受影响的行数
int result = statement.executeUpdate("insert into money(userid,usermoney) values (5,2000)");
if (result>0){
System.out.println("执行成功");
}else {
System.out.println("执行失败");
}
//resultSet = statement.executeQuery("select * from money");//executeQuery(sql)
//4.去除结果集信息
/* while (resultSet.next()) {//判断是否有下一条数据
//取出数据：resulet.gerXXX(列名）；xxx表示数据类型
System.out.println("编号：" + resultSet.getString("userid") + ",金钱：" + resultSet.getInt("money"));
}*/
}catch (ClassNotFoundException e){
e.printStackTrace();
}catch (SQLException throwables){
throwables.printStackTrace();
}finally {
//5 关闭资源
if (resultSet != null) {
resultSet.close();
}
if ( statement != null) {
statement.close();
}
if ( connection != null) {
connection.close();
}
}
}
}
6.3 SQL注入
就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执 行恶意的SQL命令。具体来说，它是利用现有应用程序，将（恶意的）SQL命令注入到后台数据库引擎 执行的能力，它可以通过在Web表单中输入（恶意）SQL语句得到一个存在安全漏洞的网站上的数据 库，而不是按照设计者意图去执行SQL语句。比如先前的很多影视网站泄露VIP会员密码大多就是通过 WEB表单递交查询字符暴出的，这类表单特别容易受到SQL注入式攻击。
String username ="admin";
String password=" 'abc' or 1=1 ";
String sql="select * from users where username= '"+username+"' and password="+password;
用户名密码不正确还能登录成功
7、PreparedStatement(预状态通道)
该PreparedStatement的接口扩展了Statement接口，它为您提供了一个通用的Statement对象有两 个优点附加功能。
此语句使您可以动态地提供参数。
PreparedStatement pstmt = null;
try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn.prepareStatement(SQL);
. . .
}
catch (SQLException e) {
. . .
}
finally {
. . .
}
JDBC中的所有参数都由？符号，这被称为参数标记。在执行SQL语句之前，必须为每个参数提供值。
所述的setXXX()方法将值绑定到所述参数，其中XXX代表要绑定到输入参数的值的Java数据类型。如果 忘记提供值，将收到一个SQLException。
每个参数标记由其顺序位置引用。第一个标记表示位置1，下一个位置2等等。该方法与Java数组索引不 同，从0开始。
关闭PreparedStatement对象
就像关闭Statement对象一样，由于同样的原因，还应该关闭PreparedStatement对象。
一个简单的调用close（）方法将执行该作业。如果先关闭Connection对象，它也会关闭 PreparedStatement对象。但是，应始终显式关闭PreparedStatement对象，以确保正确清理。
PreparedStatement pstmt = null;
try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn.preparedStatement(SQL);
. . .
}catch (SQLException e) {
. . .
}finally {
pstmt.close();
}
对比statement和PreparedStatement;
(1)statement属于状态通道，PreparedStatement属于预状态通道
(2)预状态通道会先编译sql语句,再去执行，比statement执行效率高
(3)预状态通道支持占位符?,给占位符赋值的时候，位置从1开始
(4)预状态通道可以防止sql注入，原因:预状态通道在处理值的时候以字符串的方式处理
实例
import java.sql.*;
public class Demo02 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
PreparedStatement pps = null;
try {
//1 加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2 获得链接
String userName = "root";
String passWord = "123456";
String url = "jdbc:mysql://localhost:3306/y?serverTimezone=UTC";
connection = DriverManager.getConnection(url, userName, passWord);
//3 定义sql，创建状态通道(进行sql语句的发送)
String sql = "select * from money where userid=? and usermoney=?";
pps = connection.prepareStatement(sql);
String userid = "11";
String usermoney = "500";
//给占位符赋值(下标，内容)从1开始
pps.setString(1,userid);
pps.setString(2,usermoney);
//执行sql
resultSet = pps.executeQuery();
if (resultSet.next()){
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
}catch (ClassNotFoundException e){
e.printStackTrace();
}catch (SQLException throwables){
throwables.printStackTrace();
}finally {
//5 关闭资源
if (resultSet != null) {
resultSet.close();
}
if ( pps != null) {
pps.close();
}
if ( connection != null) {
connection.close();
}
}
}
}
8、ResultSet
SELECT语句是从数据库中选择行并在结果集中查看行的标准方法。该java.sql.ResultSet中的接口表示 结果集数据库查询。
ResultSet对象维护指向结果集中当前行的游标。术语“结果集”是指包含在ResultSet对象中的行和列数 据。
如果没有指定任何ResultSet类型，您将自动获得一个TYPE_FORWARD_ONLY。
9、JAVA操作两表关系
四种:双向一对一，一对多，多对一，多对多
多表关系处理数据
(1) 数据库通过外键建立两表关系
(2) 实体类通过属性的方式建立两表关系
实体类要求:类名=表名，列名=属性名
10、数据库事务
一组要么同时执行成功，要么同时执行失败的SQL语句。是数据库操作的一个执行单元。
10.1、事务概述
数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执 行，要么完全地不执行。 事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更 新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误 恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、 隔离性和持久性）属性。事务是数据库运行中的逻辑工作单位，由DBMS中的事务管理子系统负责事务 的处理。
10.1.1、事务开始于
- 连接到数据库上，并执行一条DML语句insert、update或delete
- 前一个事务结束后，又输入了另一条DML语句
10.1.2、事物结束于
- 执行commit或rollback语句。
-执行一条DDL语句，例如create table语句，在这种情况下，会自动执行commit语句。
- 执行一条DDL语句，例如grant语句，在这种情况下，会自动执行commit。 - 断开与数据库的连接
- 执行了一条DML语句，该语句却失败了，在这种情况中，会为这个无效的DML语句执行rollback语 句。
10.2、事务的四大特点
（ACID）
- actomicity(原子性)
表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败
- consistency(一致性)
表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态
isolation(隔离性)
事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修 改它之后的状态，事务不会查看中间状态的数据。
durability(持久性)
持久性事务完成之后，它对于系统的影响是永久性的
10.3、JDBC中事务应用
如果JDBC连接处于自动提交模式，默认情况下，则每个SQL语句在完成后都会提交到数据库。
事务使您能够控制是否和何时更改应用于数据库。它将单个SQL语句或一组SQL语句视为一个逻辑单 元，如果任何语句失败，则整个事务将失败。
要启用手动事务支持，而不是JDBC驱动程序默认使用的自动提交模式，请使用Connection对象的 setAutoCommit（）方法。如果将boolean false传递给setAutoCommit（），则关闭自动提交。我 们可以传递一个布尔值true来重新打开它。
10.4.事务的提交和回滚
完成更改后，我们要提交更改，然后在连接对象上调用commit（）方法，如下所示：
conn.commit( );
否则，要使用连接名为conn的数据库回滚更新，请使用以下代码 -
try{
//Assume a valid connection object conn
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
String SQL = "INSERT INTO Employees values (106, 20, 'Rita', 'Tez')";
stmt.executeUpdate(SQL);
//Submit a malformed SQL statement that breaks
String SQL = "INSERTED IN Employees VALUES (107, 22, 'Sita', 'Singh')";
stmt.executeUpdate(SQL);
// If there is no error.
conn.commit();
}catch(SQLException se){
// If there is any error.
conn.rollback();
}
10.5 Savepoints
新的JDBC 3.0 Savepoint接口为您提供了额外的事务控制。
设置保存点时，可以在事务中定义逻辑回滚点。如果通过保存点发生错误，则可以使用回滚方法来撤消 所有更改或仅保存在保存点之后所做的更改。
Connection对象有两种新的方法来帮助您管理保存点 - - setSavepoint（String savepointName）：定义新的保存点。它还返回一个Savepoint对象。
releaseSavepoint（Savepoint savepointName）：删除保存点。请注意，它需要一个Savepoint 对象作为参数。此对象通常是由setSavepoint（）方法生成的保存点。
try{
//Assume a valid connection object conn
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
Savepoint savepoint1 = conn.setSavepoint("Savepoint1");
String SQL = "INSERT INTO Employees VALUES (106, 20, 'Rita', 'Tez')";
stmt.executeUpdate(SQL);
String SQL = "INSERTED IN Employees VALUES (107, 22, 'Sita', 'Tez')";
stmt.executeUpdate(SQL);
conn.commit();
}catch(SQLException se){
conn.rollback(savepoint1);
}
1、要取消掉JDBC的自动提交：void setAutoCommit(boolean autoCommit)
2、执行各个SQL语句，加入到批处理之中
3、如果所有语句执行成功，则提交事务 commit()；如果出现了错误，则回滚：rollback()
try {
connection.setAutoCommit(false);
add(connection);
// int i = 1/0;
sub(connection);
System.out.println("===============");
connection.commit();
} catch (Exception e) {
// TODO Auto-generated catch block
System.out.println("---------------");
try {
connection.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
以上就是java代码利用jdbc操作数据库的最简单版本,数据库事务通常要借助补捉异常语句
10.6 事务案例:转账
package com.demo01;
import java.sql.*;
public class Demo03 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//1 加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2 获得链接
String userName = "root";
String passWord = "123456";
String url = "jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
connection = DriverManager.getConnection(url, userName, passWord);
//设置为手动，不设置为手动无法确保同时成功或者同时失败，就算运行失败程序仍然执行反馈与数据库上
connection.setAutoCommit(false);
//3 定义sql，创建状态通道(进行sql语句的发送)
statement = connection.createStatement();
//返回受影响的行数
int result = statement.executeUpdate("update money set yue = yue -100 where userid = 1");
int result2 = statement.executeUpdate("update money set yue = yue +100 where userid = 2");
//代码方式提交事务
connection.commit();
if (result2 > 0) {
System.out.println("执行成功");
} else {
System.out.println("执行失败");
}
//resultSet = statement.executeQuery("select * from money");//executeQuery(sql)
//4.去除结果集信息
/* while (resultSet.next()) {//判断是否有下一条数据
//取出数据：resulet.gerXXX(列名）；xxx表示数据类型
System.out.println("编号：" + resultSet.getString("userid") + ",金钱：" + resultSet.getInt("money"));
}*/
} catch (ClassNotFoundException e) {
e.printStackTrace();
try {
connection.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//5 关闭资源
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
}
}
}
}
11.JDBC批处理
批量处理允许您将相关的SQL语句分组到批处理中，并通过对数据库的一次调用提交它们。 当您一次向数据库发送多个SQL语句时，可以减少连接数据库的开销，从而提高性能。
11.1 Statement批处理
以下是使用语句对象的批处理的典型步骤序列
使用createStatement（）方法创建Statement对象。
使用setAutoCommit（）将auto-commit设置为false 。
使用addBatch（）方法在创建的语句对象上添加您喜欢的SQL语句到批处理中。
在创建的语句对象上使用executeBatch（）方法执行所有SQL语句。
最后，使用commit（）方法提交所有更改。
Statement stmt = conn.createStatement();
conn.setAutoCommit(false);
//sql1
String SQL = "INSERT INTO Employees (id, first, last, age) VALUES(200,'Zia',
'Ali', 30)";
stmt.addBatch(SQL);
//sql2
String SQL = "INSERT INTO Employees (id, first, last, age) VALUES(201,'Raj',
'Kumar', 35)";
stmt.addBatch(SQL);
//sql3
String SQL = "UPDATE Employees SET age = 35 WHERE id = 100";
stmt.addBatch(SQL);
int[] count = stmt.executeBatch();
conn.commit();
11.2 PreparedStatement批处理
-
使用占位符创建SQL语句。
-
使用prepareStatement（） 方法创建PrepareStatement对象。
-
使用setAutoCommit（）将auto-commit设置为false 。
-
使用addBatch（）方法在创建的语句对象上添加您喜欢的SQL语句到批处理中。
-
在创建的语句对象上使用executeBatch（）方法执行所有SQL语句。
-
最后，使用commit（）方法提交所有更改。
String SQL = "INSERT INTO Employees (id, first, last, age) VALUES(?, ?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(SQL);
conn.setAutoCommit(false);
// Set the variables
pstmt.setInt( 1, 400 );
pstmt.setString( 2, "Pappu" );
pstmt.setString( 3, "Singh" );
pstmt.setInt( 4, 33 );
// Add it to the batch
pstmt.addBatch();
// Set the variables
pstmt.setInt( 1, 401 );
pstmt.setString( 2, "Pawan" );
pstmt.setString( 3, "Singh" );
pstmt.setInt( 4, 31 );
// Add it to the batch
pstmt.addBatch();
//add more batches
//Create an int[] to hold returned values
int[] count = stmt.executeBatch();
//Explicitly commit statements to apply changes
conn.commit();
预状态
12.反射处理结果集
接口：
//查询所有的学生信息
public List<Student> findallstudent(Class cla);
实现类：
package student;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class Demo implements findallstudent{
@Override
public List<Student> findallstudent(Class cla) {
Connection con = null;
PreparedStatement pps = null;
ResultSet rs =null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/yhp", "root", "123456");
List list=new ArrayList();
String sqla = "select * from student";
pps = con.prepareStatement(sqla);
rs = pps.executeQuery();
//得到数据库中的所有的列有哪些？
ResultSetMetaData metaData = rs.getMetaData();//返回数据库中的相关信息
int count=metaData.getColumnCount();//得到列数
String[] columnnames=new String[count];
for (int i = 0; i < count; i++) {
// System.out.println(metaData.getColumnName(i+1));//列的位置从1开始
columnnames[i]=metaData.getColumnName(i+1);
}
//得到实体类中的所有的方法
Method[] methods =cla.getDeclaredMethods();
while(rs.next()){
Object s=cla.newInstance();//调取无参构造创建对象
for (String columnname : columnnames) {
String name="set"+columnname;//setstuid
for (Method method : methods) {
if(method.getName().equalsIgnoreCase(name)){
method.invoke(s,rs.getObject(columnname));//执行了对应的set方法
break;
}
}
}
list.add(s);
}
System.out.println("执行成功");
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (pps != null) {
pps.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
}
13、工具类的定义
package com.demo01;
import java.sql.*;
import java.util.List;
public class Demo05 {
//1.定义需要的工具类对象
protected Connection connection=null;
protected PreparedStatement pps=null;
protected ResultSet rs=null;
protected int k=0;//受影响的行数
private String url="jdbc:mysql://localhost:3306/yhp";
private String username="root";
private String password="123456";
//2.加载驱动
static{
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//3.获得连接
protected Connection getConnection(){
try {
connection= DriverManager.getConnection(url,username,password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//4.创建通道
protected PreparedStatement getPps(String sql){
try {
getConnection();//insert into users values(?,?,?,?,)
pps=connection.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return pps;
}
//5.给占位符赋值 list中保存的是给占位符所赋的值
private void setParams(List list){
try {
if(list!=null&&list.size()>0){
for (int i=0;i<list.size();i++) {
pps.setObject(i+1,list.get(i));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//6.增删改调取的方法
protected int update(String sql,List params){
try {
getPps(sql);
setParams(params);
k= pps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return k;
}
//7.查询的时候调取一个方法
protected ResultSet query(String sql,List list){
try {
getPps(sql);
setParams(list);
rs=pps.executeQuery();
return rs;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//8.关闭资源
protected void closeall(){
try {
if (rs != null) {
rs.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
13.1 properties文件保存数据库信息-特点:key-value存储方式
db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/yhp
user=root
password=123456
工具类中读取属性文件
InputStream inputStream = 当前类名.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
dirverName = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("user");
password = properties.getProperty("password");
方法2
static{
//参数只写属性文件名即可，不需要写后缀
ResourceBundle bundle = ResourceBundle.getBundle("db");
driver = bundle.getString("driver");
url = bundle.getString("url");
username = bundle.getString("user");
password = bundle.getString("password");
}
说明:
使用ResourceBundle访问本地资源
在设计时，我们往往需要访问一些适合本地修改的配置信息，如果作为静态变量，那么每次修改都 需要重新编译一个class，.config保存此类信息并不适合，这时我们需要ResourceBundle。
通过ResourceBundle，我们需要访问位于/WEB-INF/classes目录下的一个后缀名为properties的文本 类型文件，从里面读取我们需要的值。
4090
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
