SQL 与JDBC概要总结

一.SQL概念

1.DBS:DB,DBMS,用户，管理员，系统平台
  DBMS:定义，操纵，运行管理，建立与维护
  人工-文件-数据库系统
  DBS特点：数据结构化，数据共享性高，冗余度低，易扩充，数据独立性高，数据有DBMS统一管理
  DBMS：数据的安全保护，数据的完整性检查，并发控制，数据库恢复


2.数据模型：层次模型，网状模型，关系模型，面向对象数据模型，对象关系模型，SLSEVER属于关系模型
  模型结构：层次 网状  关系
3.E-R图 1：1 1：n m:n
实体：行   属性：列
主键  外键
完整性约束：实体完整性（主键不能为空）  参照完整性  用户自定义完整性

范式  规范化  第一范式：行列不可再分   第二范式：非主属性依赖于主键

二。SQL基本查询

1.查询SELECT 定义CREATE DROP ALTER 操纵 INSERT UPDATE DELETE 控制GRANT REVORK

2.数据库三级模式：外模式，模式，内模式      credit学分

3.数据定义：数据库 表 视图 索引

4.CASCADE级联：全删    RESTRICT限制：有表拒删

5.CREATE TABLE Student
(Sno CHAR(9) PRIMERY KEY,    //主码可由多属性构成，但必需作为表级完整性进行定义，即设表间外键
Sname CHAR(20) UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20),
FORING KEY (Sdept) REFERENCES Student(Sno)
);

6.DROP TABLE Student [CASCADE];DROP TABLE Student[restrict];删除表

7.INSERT INTO Student (Sno,Sname,Sage) VALUES('101','发狂'，18);

8.UPDATE Student SET Sage=22 WHERE Sno='123456';单行
  UPDATE Student Set Sage=Sage+1;


9.DELETE FROM Student WHERE Sno=123456;


10.查询：单表查询 连接查询 嵌套查询 集合查询
   SELECT *FROM Student
   SELECT Sname NAME,'Year of Birth:' BIRTH(改变查询结果标题),2016-Sage,LOWER(Sdept) FROM Student;

11. 消除重复：DISTINCT
   SELECT (ALL)Sno FROM SC;全显示出来
   SELECT DISTINCT Sno FROM SC;

12.WHERE
   LIKE 字符匹配
   IS NULL 空值
   
   SELECT Sno,Sname FROM Student WHERE Sage BETWEEN 20 AND 23;

13.确定集合：谓词 IN
   SELECT Sname ,Ssex FROM Student WHERE Sdept IN('IS','MA','CS');
   字符匹配：LIKE
   SELECT Sname FROM Student WHERE Sname LIKE '控呢_'；（姓控呢且全名为3个字）
   SELECT Sname FROM Student WHERE Sname LIKE '_控%'；（第二个字为控的名字）

   SELECT * FROM Course WHERE Cname LIKE 'DB\_&I__'ESCAPE'\';
   以DB开头且倒数第三个字符为I的数据

14.NULL
   SELECT * FROM SC WHERE Grade IS NOT NULL AND Sage<20;
   SELECT Sno FROM SC WHERE Grade IS NULL;没成绩的人

15.多重条件查询

   SELECT Sname,Ssex FROM Student WHERE Sdept IN('IS','MA');
   SELECT Sname ,Ssex FROM Student WHERE Sdept='IS' OR Sdept='MA';


16.ORDER BY
  ASC:升序 SESC:降序      缺省值为升序
 
  SELECT * Student ORDER BY Sdept,Sage DESC;

17.聚集函数
   SELECT COUNT(*) FROM Student;查总人数
   SELECT COUNT(DISTINCT Sno) FROM SC;  查询选修课程的学生
   SELECT AVG(Grade) FROM SC WHERE Cno='1';1 号课程的平均成绩

18.GROUP BY
   SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)>3;
   SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno;求课程号及相应的选课人数

 HAVIN短语G作用于GROUP BY后（一定有）的分组数据筛选，从中选择满足条件的组
 WHERE子句作用于基表和视图，从中选择满足条件的元组

三。函数
1.视图：SELECT语句构成的查询定义的虚拟表，并不在数据库中以数据值集的形式存在
  视图特点：安全，grant语句授权  查询性能提高  修改方便工作量少
  create view v_detail
  as
  select*from table

2.索引
create [unique] idex index_name on tab_name(column_list)
drop index index_name on table_name

3.存储过程：SQL语句集（可编程函数）
 特点：增强SQL灵活性，可完成复杂运算
       可以被重复调用，标准组件是编程
       执行速度快
       存储过程流量少
       可以被用作安全机制
  变量定义 Declare @empld  int  变量赋值：set

4.事务
ACID:隔离性（事务单独） 持续性：原子性  一致性（银行算法） 
begin transaction, end transaction 语句

事务处理
1：直接写入
Create PROECDURE SP_INSERTSTUDENTS
AS
SET NOCOUNT ON
BEGIN
DECLARinsert intoE @MyErrorCount int
set @MyErrorCount=0
begin tran
insert into Student(Stuld,NAME) values('N15','TEST');
IF(@@ERROR>0)
 set @MyErrorCount=@MyErrorCount+1
 into Student(Stuld,NAME) values('N16','TEST');

IF(@@ERROR>0)
 set @MyErrorCount=@MyErrorCount+1
IF(@@ERROR=0) BEGIN
            commit tran
            PRINT 'OK'
   END
ELSE
   BEGIN
    ROLLBACK TRAN

2:JDBC
  conn.setAutoCommit(FALSE);
  conn.commit();
  conn.rollback();
   PRINT 'ERROR'
   END
END

5.Trigger 触发器（特殊的存储过程）

Create Table Student( --学生表 StudentID int primary key,--学号
StudentName varchar(20)
)
Create Table BorrowRecord( --学生借书记录表 BorrowRecordint identity(1,1),--流水号 StudentIDint ,--学号
BorrowDate datetime,--借出时间
ReturnDAte Datetime--归还时间
)
用到的功能有:
1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也
就是同时更改借书记录表的学号); 2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书
记录。

Global EduTech Management

这时候可以用到触发器。对于1，创建一个Update触发器：
Create Trigger truStudent
On Student --在Student表中创建触发器
for Update --为什么事件触发
As --事件触发后所要做的事情 if Update(StudentID)
begin
Update BorrowRecord
Set StudentID=i.StudentID
From BorrowRecord br , Deleted d ,Inserted i
--Deleted和Inserted临时表 Where br.StudentID=d.StudentID
end

对于2，创建一个Delete触发器 Create trigger trdStudent On Student
for Delete As
Delete BorrowRecord
From BorrowRecord br , Delted d Where br.StudentID=d.StudentID
从这两个例子我们可以看到了触发器的关键：A.2个临时的表；B.触发机制。
这里我们只讲解最基本的触发器。


临时表     Deleted         Insert
新增数据   不存储           存放
删除时     存放被删数据     不存储
更新       存放旧数据        存放新数据

innert过程：新数据放进isert表  ，旧数据放进 delete表   然后删除表里数据 并写入新数据

四。多表查询

1. 多表连接：等值与非等值链接 自身连接  外连接  复合条件连接

2.等值连接 R&S 运算符“=”从中取属性相等的元组
 SELECT Student.*,SC.*FROM Student, SC WHERE Student.Sno=SC.Sno;

 自然连接是特殊的等值连接，R S 比较的分量必须是相同的属性组且结果去掉重复属性列


  AELECT Student.Sno,Sname,Ssex FROM Student ,SC WHERE Student.Sno=SC.Sno;

3.自身连接 ：表自身和表间都可以链接

  查询先修课
 SELECT FIRST.Cno,SECOND.Cpno FROM Course FIRST,Course SECOND WHERE FIRST.Cpno=SECOND.Cno;

4.外连接：补NULL（）保留舍弃元组

 左外连接：保留左表元组补NULL
 右外连接:同上
 SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student LEFT outrJOIN SC ON(Student.Sno=DC.Sno);

5.复合条件连接

 SELECT Student.Sno,Sname,Cname,Grade
 FROM Student,SC,COURSE
WHERE Student.Sno=SC.Sno and SC.Cno=Course.Sno and Grage>80;

6.（多重）嵌套查询:外层查询（上层，父查询） 内层查询（下层查询，子查询）
 子查询不能用 order by子句，一般先子后父
SELECT Sname
FROM Student
WHERE Sno IN(SELECT Sno FROM SC SC WHERE Cno='2');


带IN的子查询
SELECT Sno,Sname,Sdept 
FROM Student
WHERE Sdept IN(SELECT Sdeot FROM Student WHERE Sname='刘东')；



五。JDBC

1.驱动4种类型：
JDBC-ODBC桥驱动程序
Native-API驱动程序
JDBC-NET通过服务器中间件与数据库通信
原生协议以及纯Java驱动程序

1.连接
DBC中通过提供DriverManager类和Connection对象实现数据库的连接。
JDBC API 可帮我们建立与数据库的连接、执行SQL语句 并能得到SQL语句执行后的结果。


DriverManager 类：依据数据库的不同，管理JDBC驱动 
Connection 接口：负责连接数据库并担任传送数据的任务 
Statement 接口：由 Connection 产Th、负责执行SQL语句
 ResultSet接口：负责保存Statement执行后所产Th的查询结果

2.
Statement:
JDBC执行SQL方法：
    1.executeUpdate()：insert,update, delete,执行完insert后返回的是该SQL语句所影响记录的总行数
    2.executeQuery():select,返回结果集
    3.execute：其他语句，多条纪录被影响时或有多个ResultSet

3.ResultSet
   1. ResultSet 对象包含了Statement和PreparedStatement的 executeQuery方法中SELECT查询的结果集，即符合指定 SQL 语句中条件的所有行
   2.  ResultSet中的next方法用于移动到 ResultSet 中的下一行， 使下一行成为当前行。结果集一般是一个表，其中有查询所 返回的列标题及相应的值

4.配置
一、搭建实验环境 ：
1、创建表和增加数据。
2、新建一个Java工程，并导入数据驱动。
 二、编写程序，在程序中加载数据库驱动 Class.forName(“….”);保证相应的Driver类已经被加载到 jvm中，并且完成了类的初始化工作就行了.
 三、建立连接(Connection)Connection conn = DriverManager.getConnection(url,user,pass);
 四、创建用于向数据库发送SQL的Statement对象，并发送sqlStatement st = conn.createStatement(); ResultSet rs = st.executeQuery(sql); 
五、从代表结果集的ResultSet中取出数据并处理
六、断开与数据库的连接，并释放相关资源


5.PreparedStatement
  他继承并重载Statement的方法，是预编译的

6.SP:CallableStatement
  调用存储过程，处理参数

7.事务：一组DML语句+一个DDL语句+一个DCL语句  后面两个用一个因为DDL和DCL都。回之时事务立即提交
  操作执行完后提交事务（commit显示提交，自动提交），若有操作执行失败应返回回滚事务（rollback显示回滚：coon.rollback()，自动回滚：强行推出或系统错误）
  Connection的setAutoCommit方法来关闭自动提交，开启事务，例如: coon.aetAutoCommit(false);
  coon.commit();用来开启事务（）；

8.批量更新
  executeBatch同时执行这些SQL语句

9.数据库连接池

10.开源数据源：C3P0,可自动清理不再使用的connection,Statement,ResultSet

11.MVC:model-view-controller,软件射击典范：数据显示和业务逻辑分离