数据库知识点

数据库

分类：

网状数据库；

层次结构数据库：

关系型数据库：mysql/oracle

非关系型数据库：es/redis/

SQL语法：结构化查询语言

mysql

图形化界面： sqlyog/navicat

数据库：database

 创建数据库：creat database 数据库名 character set utf8;
 查看数据库：show create database xajava_2112;
 选择数据库：use xajava_2112；
 修改数据库编码：
 删除数据库：drop database 数据库名

数据表：table

 创建数据表：
     creat table 表名(
         字段名 类型,
         字段名 类型,
         .....
         字段名 类型,
     )

数据类型：

 数值类型：
     int 整数
     float/double/decimal浮点数
     double(M,D):M表示长度;D表示小数位数
     
 字符类型：
     char(长度)：按固定长度存储
     varchar(长度)：按实际内容长度存储
 内容长度存储：
     blob二进制/text长文本
 日期类型：
     date    日期
     time    时间
     datetime
     timestamp

where条件里使用的关键字

 比较：> >= < <= =  不等于!= 不等于<>
 判空：is null/is not null

数据的增删查改：

• 新增：insert

 insert into 表名 values(值，值，值....)---赋值必须与表定义的字段顺序、个数、类型完全一致
 insert into sys_user(个别字段1，个别字段2)values(值1，值2);
 insert into 表名 values(值，值，值...),(值，值，值...),(值，值，值...),(值，值，值...),(值，值，值...);

• 删除：delete，删除表中数据，表是仍然存在的

 delete from 表名 [where 条件]

• 修改：update

 update 表名 set 字段=值，字段=值，....[where 条件]

• 查询：

 select .... from 表名 分组 过滤 排序 分页
 ​
 ​
 全表查询所有：select id,name,sex,department,age,address from student; *表示任意个任意字段;
 查所有字段： select * from student 或 select 所有字段的名称 from student;
 ​
 查询部分字段：select 部分字段名列出 from student；
 查询并对字段进行计算：select id,name,age+1,address,sex,department from student;
 查询分命名别名：select id,name,age+1 as age，address，sex，department from student；    as 关键字可以省略  select id，name，age+1 age，address，sex，department from student；
 去除重复的数据：distinct后的字段值完全相同才算重复
     select distinct department，age from student；
 查询并排序：order by
     select * from student order by 字段名 asc(默认升序)|desc(降序)；      注:如果有多个排序字段，需要用逗号隔开，并且在各字段名后添加自己的排序规则序（升/降序）
     
 分组：group by
     select sex,count(*)， avg(age) from student group by sex;   注：分组中不能使用*，分组时select后使用分组字段；
 分组并过滤筛选：
     select sex,count(*) c,avg(age) from student group by sex having c>2;
 限定条件的查询：
     select * from student where id>3 or sex="男"   //or表示或者，部分满足
     select * from student where id>3 and sex="男"； //and表示并且，同时满足
     select * from student where id in (1,3,5)      //表示出现在（）里中的任一一个即可；
     select ...from...between 值 and 值
 限定行数查询：limit 起始行数，个数
     limit 每页显示记录数*（第几页-1），每页显示记录数
     select * from student limit 1,4；   //起始行数从0开始计数
     应用：每页显示五条数据
     第一页：limit 0,5
     第二页：limit 5,5
     第三页：limit 10,5
 模糊查询：like
     通配符：%任意个任意字符；——表示一个任意字符；
     select * from student where name like "秦%"
 聚合查询：max最大 min最小 sum和 count个数 avg平均数     注：查询结果为一行一列的值；可以结合分组在组内进行聚合

• 约束：保证数据的合法

1. 实体完整性：保证每行数据不重复

查询：
	show index from 表
主键约束：primary key 唯一、非空;一个表中只有一个主键约束；
	建表时：
		字段名 类型 primary key
	建表外增加：
		alter table 表 add constraint primary key(主键字段)
	删除：
		alter table 表 drop primary key；
	演示：
		create table t1(
			id int primary key；//建表时
			name char(10)
		);
		//建表外增加：
			alter table t1 add constraint primary key(id)；
		//删除：
			alter table t1 drop primary key；
			
			
自增长约束：auto_increment 从1开始，每次增加1，可以不用赋值，默认自增；
	建表时：
		字段名 类型 primary key auto_increment
	建表外增加：
		alter table t1 modify id int auto_increment
	删除：
		alter table t1 modify id int；
		
		
唯一约束:unique唯一
	索引：可以提高查询效率，但是过多的索引又会导致查询较慢；
	建表：
		字段名 类型 unique
	建表外增加：
		alter table 表 add constraint 约束名 unique(唯一约束字段)
	删除：
		drop index 唯一约束名 on 表

2. 域完整性：保证数据的正确性

非空约束： not null 数据不能为null
	建表：
		字段名 类型 not null
	建表外添加：
		alter table 表名 modify 字段 类型 not null；
	删除：
		alter table 表名 modify 字段 类型 null；
		
默认值约束：default
	建表：
		字段名 类型 default 值
	建表外添加：
		alter table 表名 alter 字段 set default 值；
	删除：
		alter table 表名 alter 字段 drop default；

3. 参照完整性：表之间的关联数据

外键约束：foreign key 当前从表中的字段取值来自于参照的主表字段
注：删除外键约束后不再查看表结构，直接使用数据验证外键的正确性；
	主表：含有主键的表，例：teacher
	从表：含有外键的表，例：course
	建表时：
		外键字段 类型，
		constraint 外键名 foregin key(外键字段) references 主表(主键字段名)；
	建表外添加：
		alter table 表add constraint 外键名 foreign key(外键字段) references 主表(主键)；
	删除：
		alter table 表 drop foreign key 外键名；

• 修改表结构：alter.....

新赠一个列：
	alter table 表 add column  列名 类型
修改列类型：
	alter table 表 modify 列名 新类型
修改列名：
	alter table 表 change 原列名 新列名 类型
删除列：
	alter table 表 drop 列名

• 联合查询：

1. 等值连接查询：where 等值条件

select 字段 from 表1，表2，表3...where 等值条件；
注：等值条件是----主表的主键字段值=从表的外键字段
如果不添加等值条件，属于笛卡尔积结果；

2. 内连接查询：inner join...on

select 字段 from 表1 inner join 表2 on 等值条件 inner join 表3 on 等值条件.....

3. 外连接查询：

   • 左外连接：left join...on 保证左表的数据完整性，如果右表没有匹配的数据，则为null

   • 右外连接：right join...on保证右表的数据完整性，如果左表没有匹配的数据，则为null

4. 子查询：

子查询结果是一行一列：将一个字段的值与子查询结果直接比较

子查询结果是多行一列：

不等值

any/some：与至少一个结果比较

all：与所有结果相比较

等值：

in：查询语句中where字段in(子查询)

• 别名：

字段别名：select name username form student；

表别名：select id，name，form student s1，score s2，where 等值条件

• **视图：

view，方便查询；保护数据安全；

创建视图：    如果视图不存在，直接创建；否则执行替换
	create view [or peplace] view 视图名 as select select...语句
删除视图：
	drop view 视图
注：sql语法同样适用于视图执行增删改查语句

• 语言的分类：

DDL：数据定义语言； creat/alter

DML：数据操纵语言；insert/delete/update

DQL：数据查询语言；select

jdbc

java database conectivity连接技术；实现统一管理(不同数据库厂商提供的驱动类)；

DriverManager---连接数据库，并且获得一个连接对象

Connection---表示连接对象，接下来所有的操作都在连接对象基础上开始

Statement---执行sql，返回结果 int/结果集

ResultSet---查询的结果集

资源释放

环境配置：添加数据库的驱动文件.jar，添加到工程-->add as libaray

开发步骤：

• 注册驱动：将驱动类读入到内存中

Class.forName("全类名")

• 创建连接：DriverManager.getConnection(url地址，用户名root，密码)；

Connection
connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/数据库名"，user用户，pass密码)；

• 获得执行sql的对象：creatStatement()；

int res = statement.executeUpdate(sql);
Resultset resliSet = statement.executeQuery(sql)；

• 结果集处理：

取出每一行的所有列；
先判断有没有下一行next(),如果有取出getxxx();
if(resultSet.next()){//取一行
result.getInt(字段名|字段序号)；//取一列
result.getString(字段名|字段序号)；//取一列
result.getDate(字段名|字段序号)；//取一列
result.getFloat(字段名|字段序号)；//取一列

}

• 释放资源

调用close()方法
注：资源关闭后不可以再使用

• sql注入问题

产生注入原因：
	1.传入的内容中含有sql关键字
	2.当sql编译执行时使得出现了条件恒成立的情况；
解决方案：
	先编译，在传值

• PreparedStatement对象：预编译sql对象

创建对象并编译sql：PreparedStatement ps = connection.prepareStatement(sql语句);//在sql中保证SQL语法的正确性，定义了一些占位符?----从左向右，从1开始数数

传值：给所有占位符传值
	setXXXX(占位符的序号，值)---setInt/setString/...方法取决于字段的类型
	
再交提交：ps.executeQuery();

• 封装连接数据库的工具类：

加载驱动：自动加载(加载时机---创建对象/创建子类对象/调用类属性/调用类方法/Class.format())
static{
	Class.format("com.mysql.jdbc.Driver")
}

获得连接：静态方法
 public static Connection getConnection() {
        try {
            //连接数据库
            return DriverManager.getConnection("jdbc:mysql://localhost:3306/work1", "root", "123456");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

释放资源：静态方法(需要将要关闭的资源以参数传入)
 public static void close(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {
        try {
            if (resultSet != null)
                resultSet.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (preparedStatement != null)
                    preparedStatement.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (connection != null)
                        connection.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }

            }

        }

    }

• 封装增删改的父类

Class Base{
	//调用子类传入的参数
	//sql：要执行增删改语句
	//params sql中的占位符对应的值
	public	int modify(String sql,Object...params){
        try{
        Connection 获得连接
        ParparedStatement p = connection.parpareStatement(sql);
        //根据params的遍历判断执行sql中占位符赋值的次数
        	for(int i = 0; i < params.length;i++){
        		//调用setObject(占位符的序号，params数组中的元素)
        		p.setObject(i+1，params[i])
        	}
        	//提交处理
        	p.executeUpdate();

        }catch(){
        
        }finally(){
        
        }
	}
}
Class StudentDaoImpl extends Base implement StudentDao{
//增删改方法调用父类的modify方法：区别执行各自的sql语句和占位符不同
}

• 查询方法的工具类：

ResultsetMetaData:结果集的元数据，包含了返回结果的所有信息
	ResultsetMetData mataData = resultSet.getMetaData();
获得结果表中的字段个数：
	int count = metaData.getColumnCount()
遍历结果集中的所有字段
	for(int i = 1;i<count;i++){
	
	}
逐个取出每个字段的字段名，同时作为实体类的属性名
	//如果表中字段名与实体属性名不一致，在sql中通过字段名的方式保存一致；
	String label = metaDate.getColumnLabel(i);
取出属性的类型：Class
	Class type = class.getDeclardField(label).getType();
根据属性名推出set方法，并给这个属性赋值
	String methodName = "set"+label.substring(0,1).toupperCase()+label.substring(1);
	Method method = class.getDeclaredMethod(methodName,参数类型类对象type);
 	method.invoke(实例，resultSet.getObject(label))

• 事务：

执行多个增删改语句，将其定义为一个事务，即最小执行单位；在整个事务执行过程中，如果有事务执行失败，即使先执行成功的sql也需要将执行结果回滚，除非整个事务全部执行成功，最终事务的所有sql才成功提交
事务的原理：
	数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)，一个事务中所有的增删改语句的执行结果都会在回滚段中，只有当事务中所有的sql语句均正常结束(commit),才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败，整个事务将回滚(roolback)。
事务的特性：
	A 原子性：事务是执行的一个整体，其中包含了多久DML操作
	C 一致性：事务中的操作有一个失败时，其中所有执行过的操作全部回滚到初始状态
	I 隔离性：访问的数据要么是一个事务更改前的状态，要么是一个事务更改后的状态，不会访问中间状态
	D 持久性：事务中DML操作对数据库中持久生效的
	
JDBC中事务的提交方式：自动提交；
事务提交：commit
事务回滚：rollback
在JDBC中添加事务：
	开启手动提交事务的方式：
	connection.setAutoCommit(false);
	手动提交事务：
		connection.commit();
	手动回滚事务：
		connection.rollback();

• 业务层：service

调用一个或多个DAO的操作时；
当执行多个DML的调用时，此时封装事务；
事务定义在业务层；
因为在业务要开启事务时，要使用连接对象Connection,所以在连接1数据库的工具类中封装了对事务开启/提交/回滚的方法；但是，现在的问题是无法保证连接对象的一致性；
在连接数据库的工具类中保证Connection的唯一：
	ThreadLocal<Connection> t = new ThreadLocal<>();
	获得的连接对象添加到ThreadLocal中：t.set(连接对象);
	如果连接对象关闭的情况下，需要移动：t.remove