数据库编程

一、概念

1） 数据库分类

小型数据库：access

中型数据库：sql server,Informix(安全性能高)

大型数据库：oracle，sybase，mysql

海量数据库：db2

2） 数据库范式

第一范式（1NF）:强调的是列的原子性，即列不能够再分成其他几列

第二范式（2NF）:首先是1NF,另外包含两部分内容。一是表必须有主键，二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分

第三范式（3NF）:首先是2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖

3） 数据库与表的管理

编码：

unicode编码：用两个字符表示一个英文字母和汉字（全球通用）

非unicode编码：用一个字符表示一个字母，用两个字符表示汉字

数据类型：

字符型：

char   定长   varchar  变长  ——非unicode编码

nchar  定长   nvarchar 变长 ——unicode编码 

特别说明

   一般带有汉字的段用nvarchar，全英文或符号的用varchar，因为nvarchar为unicode字符集，该类型的字段无论是单个字母还是单个汉字都占两个字节，而varchar，字母占一个字节，汉字占两个字节。nvarchar处理汉字或其他unicode字符集的速度比varchar字段快

数字型：

bit    范围0到1

int    范围为负的2的31次方到正的2的31次方- 1

bigint  范围为负的2的63次方到正的2的63次方- 1

float   存放小数，不推荐使用

numeric  存放小数

强烈建议，如果去存放小数，最好使用numeric

numeric(20,2)

整数和小数位数加起来有20个，小数点后有2位

日期类型：

datetime（表示日期）

timestamp（时间戳）

一般情况下，用datetime表示日期

4） Java操作mysql数据库

Statement和PreparedStatement的区别

①直接使用Statement，驱动程序一般不会对sql语句做处理而直接交给数据库，使用PreparedStatement，形成预编译的过程，并且会对语句作字符集的转换在java中就形成预编译，到数据库中直接进行操作，减小数据库的压力

②PreparedStatement还能有效的防止危险字符的注入，也就是注入漏洞问题，但要求用？赋值的方式才可以

JDBC基本开发步骤

1、注册驱动Driver

2、建立连接（Connection）

3、创建执行SQL的语句（Statement）

4、执行语句

5、处理执行结果（ResultSet）

6、释放资源

总结

增、删、改用Statement.executeUpdate来完成，返回整数（匹配的记录数）

这类操作相对简单

查询用Statement.executeQuery来完成，返回的是ResultSet对象。

ResultSet中包含了查询的结果

查询相对与增、删、改要复杂一些，因为有查询结果要处理。

public static void main(String[] args) throws Exception{

                  //(1)注册驱动程序（类路径上必须放置数据库驱动程序）

                  Class.forName("com.mysql.jdbc.Driver");             

                  //(2)获取数据库连接

                  Connectionconn=DriverManager

                  .getConnection("jdbc:mysql://127.0.0.1:3306/Test", "root","root");

                  //（3）创建SQL语句对象

                  Statementstmt=conn.createStatement();

                  //(4)执行查询，返回结果集

                  ResultSetrs=stmt.executeQuery("SELECT * FROM student");

                  //(5)处理结果集

                  while(rs.next()){

                          //rs.next()判断结果集中是否存在记录

                          System.out.println(rs.getString(1));

                          System.out.println(rs.getString(2));

                          System.out.println(rs.getString(3));

                          //System.out.println(rs.getInt(4));

                          //System.out.println(rs.getString(5));

                          //System.out.println(rs.getString(6));

                  }

                  //(6)关闭资源try...catch...finally

                  try {

                          stmt.close();

                          conn.close();

                  } catch(Exception e) {

                          e.printStackTrace();

                  }

         }

二、SQL语句

1)数据库

--创建数据库

create database 数据库名

--使用某个数据库（切换到某个数据库）

use 数据库名

go

2)表

--创建表

create table Hero

(heroId int,

heroName varchar(50),

sex char(2),

)

--删除表（把表的结构和表的数据一起删除）

drop table hero

3)CRUD

--添加数据

Insert into hero value(1,”宋江”，”男”)

insert into 表名 values(数值，数值)

insert into hero(heroId,sex) values(1,”男”)

insert into 表名(字段列表) value (值列表)

--删除数据

delete from hero where sex = “男”

delete from 表名 where 字段名 = “值” and 字段名 = “值”

--修改数据

update hero set sex = “女” wheresex = “男”

update 表名 set 字段名 = “值”，字段名 = “值” where 条件

--查询数据

select * from 表名

select * from 表名 where 条件

select 字段名，字段名 from 表名 where 条件

4)简单查询

--如何取消重复行（distinct只能消除完全一样的行，保留一行）

select distinct empno from emp

select distinct 字段 from 表名 where 条件

--如何处理null问题

select ename,sal*13+is null(comm,0)*13 年工资 fromemp

如果值为null，则返回0，否则返回自己

--使用like操作符(模糊查询)

%表示0到多个字符     _表示单个字符

select * from emp where ename like “S%”

select * from emp where ename like “__o%”

--使用in操作符(多号码查询)

select * from emp where empno in(123,456,789)

--使用is null操作符

select from emp where mgr is null

--使用order by语句

order by 默认是升序排序(asc),降序排序为(desc)

select from emp order by sal asc

select from emp order by sal asc,deptno desc

使用列的别名排序

select ename，sal*13 年薪 fromemp order by 年薪 asc

5)复杂查询

--数据分组——max，min，avg，sum，count(个数)

select ename，sal from emp wheresal=(select min(sal) from emp)

select avg(sal) 平均工资，sum(sal) 总工资 fromemp

select count(*) from emp

--group by 和having字句

group by 用于对查询的结果分组统计

having字句用于限制分组显示结果

having往往和group by结合使用，可以对分组查询结果进行筛选

select deptno，avg(sal),max(sal)from emp group by deptno having avg(sal)<2000 order by sal

对数据分组总结：

1、分组函数只能出现在选择列表，having，order by子句中

2、如果在select语句中同时包含有groupby，having，order by，那么他们的顺序是group by，having，order by

3、在选择列中如果有列，表达式和分组函数，那么这些列和表达式必须有一个出现在group by子句中，否则就会出错

6)多表查询

--多表查询

select from 表名一，表名二 where 条件

由于select语句从右到左读取，所有线读取表二，再读取表一

select emp.deptno from emp，dept whereemp.dname = “sales” and emp.deptno = dept.deptno(两张表关联的关系)

如果两张表或多张表都有相同的字段，则需要带表名（别名）

表名.字段名

--自连接（指在同一张表的连接查询）

select ename from emp where empno = (selectmgr from emp where ename = “ford”)

分析，把emp 看作两张表，分别是worker ，boss

Select worker.ename,boss.ename from empworker,emp boss where worker.mgr = boss.ename

--外连接（左外连接和右外链接）

--左外连接：指左边的表的记录全部显示（所有字段信息），如果没有匹配的记录就用null填

Select worker.ename,boss.ename from empworker left join emp boss on worker.mgr = boss.ename

--右外连接：指右边的表的记录全部显示（所有字段信息），如果没有匹配的记录就用null填

Select worker.ename,boss.ename from empworker right join emp boss on worker.mgr = boss.ename

--子查询

指嵌入其他sql语句中的select语句，也叫嵌套查询

--单行子查询，指只返回一行数据的子查询语句

select from emp where deptno = (selectdeptno from emp where ename = “smith”)

--多行子查询，指返回多行数据的子查询

select from emp where job in (selectdistinct job from emp where deptno = 10)

--在from字句中使用子查询（看作是一张表）

select emp.ename,emp.sal,temp.myavg fromemp,(select avg(sal) myavg,deptno from emp group by deptno) temp whereemp.deptno = temp.deptno and emp.sal > temp.myavg

总结：

   当在from子句中使用子查询时，该子查询会被作为一个临时表来对待，当在from字句中使用子查询时，必须给子查询指定表别名和字段别名

--分页查询

top后面的数表示要取出几条记录(而且还是前几条)

显示第5个到第10个入职的雇员

select top 6 from emp where empno not in(select top 4 empno from emp order by hiredate) order by hiredate

7)--如何删除一张表重复记录

用查询结果创建新表，这个命令是一种快捷的建表方法

select *(这里可以选择字段) into 另一个表名 from 表

--把cat的记录distinct后的结果，放入到#temp表

select distinct into #temp from cat

--把cat表的记录清空

delete from cat

--把#temp表的数据（没有重复的记录），插入到cat表

insert into cat select * from #temp

--删除#temp表

drop table #temp

8)--维护数据的完整性——约束

约束包括：not null,unique,primary key,foreign key 和check 5种

--not null(非空)

如果在列上定义了not null，那么当插入数据时，必须为列提供数据

--unique(唯一)

   当定义了唯一的状态，该列值是不能重复的，但是可以为null，但是最多只能有一个null

--primary key(主键)

   用于唯一的表示表行的数据，当定义主键约束后，该列不但不能重复而且不能为null

注意事项

   一张表最多只能有一个主键，但是可以有多个unique约束

行级定义：把列的名字，类型写完后，直接加特点

      testNamevarchar(30) unique

表级定义：写完后再去定义主外键

      primary key（testId，testName）

--foreign key(外键)

用于定义主表和从表之间的关系，外键的约束要定义在从表上，主表则必须具有主键的约束或是unique约束，一张表可以有多个外键

主键(primary key)

1、不能够重复出现

2、必须赋值，即主键不允许为null

外键(foreign key)

1、外键只能指向主键

2、外键和主键数据类型要一致

create table dept

(deptno int primary key identify(1,1),

name nvarchar(30))

 

create table emp

(empno int primary key,

deptno int foreign key referencesdept(deptno))

外键，指向dept表的的deptno字段

identify(1,1)表示该字段自增长，从1开始，每次+1

（字符串类型不能增长）

--check

   用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000~2000之间，如果不在，就会提示出错

--default(默认)

create table goods(

goodsId nvarchar(50) primary key,

goodsName nvarchar(80) not null,

goodsPrice numeric(10,2) check(goodPrice> 0),

goodsSorts nvarchar(4) check (goodsSorts in(“食物”，“日用品”)) default “食物”，

email nvarchar(100) unique,

customerId navrchar(50) foreign keyreferences customer(customerId)

)

三、事务

1)什么是事务

作为单个逻辑工作单元执行的一系列操作，满足四大特性：

1. 原子性（Atomicity）：事务作为一个整体被执行 ，要么全部执行，要么全部不执行；
2. 一致性（Consistency）：保证数据库状态从一个一致状态转变为另一个一致状态；
3. 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行；
4. 持久性（Durability）：一个事务一旦提交，对数据库的修改应该永久保存。

2）事务的并发问题有哪几种？

1. 第一类丢失更新(lost update)： 在完全未隔离事务的情况下，两个事物更新同一条数据资源，某一事物异常终止，回滚造成第一个完成的更新也同时丢失。
2. 脏读(dirty read)：如果第二个事务查询到第一个事务还未提交的更新数据，形成脏读。
3.不可重复读(unrepeated read)：一个事务两次读取同一行数据，结果得到不同状态结果，如中间正好另一个事务更新了该数据，两次结果相异，不可信任。

4．幻读(phantom read)：一个事务执行两次查询，第二次查询比第一次多出或少一些数据，造成两次结果不一致。只是另一个事务在这两次查询中间插入或者删除了数据造成的。
5. 第二类丢失更新(second lost updates)：是不可重复读的特殊情况，如果两个事务都读取同一行，然后两个都进行写操作，并提交，第一个事务所做的改变就会丢失

3）事务的隔离级别有哪几种？

1. Serializable 串行化：可避免脏读、不可重复读、幻读的发生
2. Repeatable Read 重复读：可避免脏读、幻读的发生
3. Read Commited 读已提交：可避免脏读的发生
4. Read Uncommited 读未提交：最低级别，任何情况都无法保证

4）数据库中的锁有哪几种？

共享锁（Shared locks, S-locks）
共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

排它锁（Exclusive locks, X-locks）

如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的锁。获得排他锁的事务既能读数据，又能修改数据。

更新锁（简记为U锁）：

更新锁可以防止通常形式的死锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则两个事务都需要转换共享锁为排它锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。

若要避免这种潜在的死锁问题，请使用更新锁。一次只有一个事务可以获得资源的更新锁。如果事务修改资源，则更新锁转换未排它锁。否则，锁转换为共享锁。

悲观锁

每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁，悲观锁的实现，往往依靠数据库提供的锁机制。

用法：一个典型的依赖数据库的悲观锁调用：select * from account where name=”Erica” forupdate这条 sql 语句锁定了 account 表中所有符合检索条件（ name=”Erica” ）的记录。本次事务提交之前（事务提交时会释放事务过程中的锁），外界无法修改这些记录。悲观锁，也是基于数据库的锁机制实现。
　　在Hibernate使用悲观锁十分容易，但实际应用中悲观锁是很少被使用的，因为它每次发送的SQL语句都会加上"for update"用于告诉数据库锁定相关数据，大大限制了并发性：

乐观锁
相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候才会正式对数据的冲突与否进行检测。乐观锁大多是基于数据版本（Version）记录机制实现的。

用法：为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个"version"字段来实现。
　　乐观锁的工作原理：读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

四、索引

1）概念

1、什么是索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息；

2、 优、缺点

优点：

①  大加快数据的检索速度

②  可以加速表和表之间的连接

缺点：

①  需要物理空间

③  表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

3、 定义与删除

Create unique index 索引名 on 表名（字段名 次序，字段名 次序……）

Drop index 索引名

4、 分类

普通索引、唯一索引（允许有空值）、主键索引（不允许有空值）、组合索引

5、 底层数据结构：B+树

使用 B+ 树的原因：查找速度快、效率高，在查找的过程中，每次都能抛弃掉一部分节点，减少遍历个数。（此时，你应该在白纸上画出什么是 B+ 树）

2）索引优化

①创建索引，一下情况不适合建立索引

1、 表记录太少

2、 经常插入、删除修改的表

3、 数据重复且分部平均的表字段

②复合索引

如果一个表中的数据在查询时有多个字段总是同时出现则这些字段就可以作为复合索引

3）MyISAM和InnoDB区别

MyISAM表不支持事务、不支持行级锁、不支持外键

五、数据库优化（SQL层面和表设计层面）

六、高并发、大数据情况下的数据库开发