数据库

数据库

本质是文件系统
可以实现数据的持久化存储

常见的DBMS

• 非关系型（语法各不相同）

• 关系型（SQL语法）

    SQL语法不同于java（java是强类型的），SQL是不区分大小写的
  

系统	公司	适用场景
MySQL	Oracle	中小型
Oracle	Oracle	大型
DB2	IBM	大型
SQLServer	微软	大型
SQLite		移动端嵌入式小型数据库

数据库语言

DD（define）L 数据定义语言
create、 drop 、alter…

DM（manipulation）L 数据操作语言
insert、 into 、delete、 update、 set…

DQ（Query）L 数据查询语言
select 、where、 in 、Order by 、between and、 limit、 having 、group by、 like …

DC（control）L 数据库控制语言
revoke、 grant…

数据库与java编码

数据库管理系统包含多个数据库；每个数据库包含多个表；每个表中包含多条记录；每一条记录包含多个字段；

学生类	对照	学生表
类的属性（姓名、ID、性别、年级、班级、成绩…）	<------>	表的字段（姓名、ID、性别、年级、班级、成绩…）
新增一个属性 学籍	<------>	新增一个字段 学籍（一列）
创建一个类的对象 学生A	<------>	表中新增一条记录 学生A（一行）
修改某属性值 A的年级	<------>	修改一条记录的某字段值 A这一行，年级这一列对应的值修改

基础语句

Create Retrieved/read Update Delete

辅助命令

修改字符集： set names ;
进入数据库：mysql -u账户 -p密码

操作库

建库
create database 库名（character set utf-8）;

删库
drop database 库名;

改库（的编码格式）
alter database character set gbk（不允许改库名（数据安全等考虑），只能改编码格式）

查看已有的全部数据库
show databases;

使用库
use 库名；

操作表

• 表

查看已有的全部表
show tables;

建表
create table 表名（字段名 数据类型，字段名 数据类型）

	varchar（20）最多接收20个字符的可变长度的字符串

删表
drop table 表名；

	drop table if exists 表名;
	安全删除表（避免删除已经不存在的表，会导致java中报异常）

查看表结构
desc 表名；

• 结构/字段

添加字段
alter table 表名 add 新列名 类型 [约束]；

删除字段
alter table 表名 drop 列名；
修改字段数据类型
alter table 表名 modify 列名 类型；
修改字段（可以顺带修改数据类型）
alter table 表名 change 旧列名 新列名 新类型 ；

• 数据/记录

向指定字段添加数据
insert into 表名（字段名1，字段名2，…） values（字段值1，字段值2，…）；

	varchar类型可以接受数字类型（数字类型也可以接收字符串）
	varchar 默认值是空字符，可以通过update将其改为null；
	SQL语法比较宽松，但仍旧建议使用正确的类型；

增加整条数据记录
insert into 表名 values(字段值1，字段值2…),(字段值1，字段值2…),(字段值1，字段值2…)…;

修改某个数据
update 表名 set 列名=新值 where 条件；

删除数据

删除表
truncate table 表名;

	相当于删除后重新创建一个与原来结构相同的表
	自增长的主键会重新从1开始

删除数据（逐条删除）
delete from 表名 where 条件；
delete from 表名；（删除所有）

	 只清除数据记录，不清除表结构
	 自增长的主键接着清空前的值继续增长

• 若想仅删某个字段的某个值——用update将该数据置空

查询数据
select 字段名/* from 表名 where 条件；
select count（字段名/*） from 表名 where 条件；

	count不计算空值

约束

1. 不能添加对应约束的场景

    字段上已经存在不符合约束的情况时，不能添加约束：
    如字段已经存在空值/重复字段值时，不能将该字段设置非空/唯一约束！
   

2. 建表时添加约束语句

    create table 表名（字段名 字段类型 约束类型，字段名2 字段类型2 约束类型... ） ;
   

3. 对已有字段添加约束语句

    alter table 表名 modify 字段名 字段类型 约束类型；
   

4. 删除约束

    删除非空约束：alter table 表名 modify 字段名 字段类型；
    删除唯一约束：alter table 表名 drop index 字段名；
    删除主键约束：alter table stus drop primary key;（主键约束删除后自动将该字段留下非空约束）
   

5. 注意事项
   主键
   唯一+非空
   在一张表里只能有1个主键，一般放在第一列
   只有（类型为数字的）主键约束可以设置自增长，默认从当前值的最大值开始以1为单位自增长；当前最大值若是浮点数5.5，自增长后是6；
   不能与default约束并存，否则会存在主键字段中，有多个值相同（都是默认值）

   唯一
   唯一约束可以为null；允许多个null
   根据NULL的定义，NULL表示的是未知，因此两个NULL比较的结果既不相等，也不不等，结果仍然是未知。根据这个定义，多个NULL值的存在应该不违反唯一约束，所以是合理的，
   不能与default约束并存，否则会存在唯一字段中，有多个值相同（都是默认值）

查询语句

select
	查询结果的展示字段
from
	表名
where
	分组前的查询条件
group by
	分组字段
having
	分组后的查询条件
order by
	查询结果的展示顺序

SELECT depart,avg(esalary),count(*)  
from emp 
where gender='男' 
GROUP BY depart 
having  AVG(esalary )>5000 
order by avg(esalary) desc
//从emp表中查询各部门中平均薪资大于5000的部门的男生数量，按平均薪资降序展示

从emp表中——from emp
各部门——group by depart
记录的属性是性别为男——where 性别为男
部门的属性是平均薪资大于5000——having 平均薪资大于5000
按平均薪资降序展示——order by 平均薪资

where条件语句

运算符	含义	注解
*	通配符	表示查询符合条件的全部列
and &&	逻辑与	条件的交集
or ||	逻辑或	条件的并
=	等于
>	大于	不包含
<	小于	不包含
<> !=	不等
<=	大于等于
>=	小于等于
between 值1 and 值2	指定范围之间	含值1和值2； 值1必须小于值2，即不能between 90 and 70，只能between 70 and 90；
in(值1，值2…)	指定列表内	列表内数据的顺序不限
not in(值1，值2…)	不在指定列表内
is null	为空
is not null	不为空
_	模糊查询（like）中的单个任意字符	where Sname like ‘a’ 名字长度为3个字符且最中间为a的
%	模糊查询（like）中的任意个任意字符	where Sname like ‘%a%’ 名字中任意位置含a的
like ’ ’	模糊查询	匹配规则必须用单引号包裹 ’'内的字符不区分大小写，都可以匹配出来

聚合函数

• 含义
  将一整列作为一个整体运算
• 函数列表
  一般用法： SELECT 函数名(字段名) from 表名；

函数名	含义	注意事项
max	列内最大值	字段名
min	列内最小值	字段名
avr	列内平均值	字段名
sum	列内值之和	字段名
count	计数	主键字段名 或 * （也可以放其他字段但不建议，因为count不计算null值，放某个字段容易遗漏；）

• 子查询
  即把 一个where的结果 作为 另一个where 的 参数

  SELECT * from stus where (Sscore<(SELECT avg(Sscore) from stus));

排序查询 Order by

SELECT Sage from stus where Sscore >70 and Sscore<90 order by Sscore desc,Sage asc 

单条件
升序

SELECT * from stus order by Sscore desc

降序

SELECT * from stus order by Sscore (asc)

asc默认不写也可

多条件

SELECT * from stus order by Sscore desc,Sage asc

分组查询 group by

查询各部门平均工资

select avg(esalary),depart from emp GROUP BY depart

分组查询的结果字段，一般为 分组字段（depart）及聚合函数（avg(esalary)）
如果放* 或者其他字段，只能显示一条记录。

查询各性别中工资低于4000 的人数

select count(*),gender from emp where esalary<4000 group by gender

查询各部门中平均工资大于5000的部门中的人员数量

SELECT depart,avg(esalary),count(*)  from emp GROUP BY depart having  AVG(esalary )>5000

分组后的条件筛选用having,having可以查询聚合函数（where不能）
where用于分组前进行筛选

根据语义理解：
having的主语是个字段，…having…表示主语 在…状态下
where的主语是整个表，…where… 表示主语中 符合 （字段符合…条件/属性）条件的记录

分页查询 limit

limit 起始索引 展示记录数量

limit 0,5 第1页：第1-5条
limit 5,5 第2页：第6-10条
limit 10,5 第3页： 第11-15条
… …

每页6条，展示第3页
limit （3-1*6,6） 即 limit 12,6

JDBC

java database connectivity
jdbc封装了一系列 java.sql接口的实现类,是面向关系型数据库的

//加载驱动包
		Class.forName("com.mysql.jdbc.Driver");
		//创建连接对象
		Connection con=DriverManager.getConnection("jdbc:mysql://IP:3306/homework", "root", "1127cb56");
		//编写SQL语句
		String sql ="insert into emp(ename,eage) values('小黑',22)";
		//创建执行对象
		Statement stmt=con.createStatement();
		//执行SQL语句
		stmt.executeUpdate(sql);
		//处理结果
		if(i==0){
			System.out.println("处理失败");
		}else {
			System.out.println("处理成功");
		}
		//释放资源
		con.close();
		

Statement

方法名	可以执行的操作	参数列表	返回值
executeUpdate	增删改	String SQL语句	返回int的被影响的记录数量
executeQuery	查	String SQL语句	返回set的查询结果的集合
execute	增删改查	String SQL语句	返回Boolean的是否有记录被影响

PreparedStatement

	预编译的statement，预先避免了or、and等有特殊含义的字符被视为关键字（如果是拼接，在SQL语句中就会直接被视为关键字）
	是statement的实现类

背景：解决注入问题的安全隐患

注入问题：即用户输入的内容中包含 ’ or 等特殊字符，会导致与已有的字符串拼接作为SQL语句时语义改变
如当 用户输入的内容中包含 or+恒等式 时，永远为真

例：SQL语句为：

//用字符串的拼接，将用户输入的用户名和密码，在数据库中查找，查到则登陆成功，否则登录失败
String selectSql="select * from users where username='"+inputname+"' and password ='"+password+"'";
//当用户输入的内容包含or+恒等式 时，如下
String password ="qwrdtfyguhj or '1'='1";
//SQL语句变成
String selectSql="select * from users where username='"+inputname+"' and password ='qwrdtfyguhj or '1'='1'";
//即输入任意用户名密码都可以登录成功，是安全隐患！

用法：占位符

？占位符
代替所有需要拼接的用户输入内容
占位符不能被’‘包裹，被’'包裹的任何字符不起特殊作用，视为普通字符

set方法
PreparedStatement对象.set数据类型（int ?的位置，字段数据类型 用户输入内容）

public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/homework", "root", "1127cb56");
		//？是preparedstatement的占位符，代替用户需要传进来的值；后续使用set方法设置
		String selectSql="select * from users where username = ?  and password = ? ";
		//创建执行对象时直接绑定SQL语句
		PreparedStatement pstmt=con.prepareStatement(selectSql);
		//根据参数位置放置参数
		pstmt.setString(1, "qwesssr");
		pstmt.setString(2, "1234");//此处即便写 "111 or '1'='1'"（or加恒等式），也会失败
		//执行创建时无需再传SQL语句
		ResultSet set=pstmt.executeQuery();
		if(set.next()){
			System.out.println("查询成功");
		}else {
			System.out.println("查询失败");
		}
			
	}

与statement的区别

	Statement	PreparedStatement
SQL语句字符串的完成方式	字符串拼接	占位符
创建		需要参数：预编译的SQL语句字符串（含占位符的） 占位符需要使用setXXX（位置，数据）来替换
执行	需要参数：SQL语句字符串
特点	可能存在注入问题	预编译了，不存在注入问题