1.SQL简明说明【以MySQL为例】
1.建表(复制表结构和内容)DDL
Create table xxx as select * from yyy 把yyy的内容和结构复制到xxx中了
alter table 旧表名 rename to 新表名
alter table xx add(name int ,id int)//可多个同时修改
新增约束和已有数据冲突的时候,修改失败
增加一列,要加上默认值,否则会出错
2.约束:Check :检查,指定一个布尔表达式,用于指定对应列的值必须满足该表达式
但是mysql并没有这个约束;mysql 的information--schema 库中的table--Constraints 保存了约束信息
3.索引:
默认指定一个列名的索引,可多列使用一个索引
sysql特有的删除索引 drop index 名称
一个表中只能有一个主键索引
create index 索引名 on 表名(列名)
drop index 索引名 on 表名
4.级联:删除 当主表记录从表记录参照时,主表记录不能被删除,只能先从表中参照主表的内容全部删除后,记录才能删除;
foregin ... on delete Cascade //级联操作或者 on delete set null
主表删除记录,外表把对应的数据清空,或置空
定义外键约束时定义主表和从表的级联操作 on delete cascade 或者 on delete set null
5.视图:本质上就是一组被命名的命令行语句
create or replace view 试图名
as subquery
一般不能用来修改视图数据 可以通过with check option 强制指定
注意:
unique 允许拥有多个空值 null != null
6.语法:
distinct 去处重复行,(后面字段的重复),而不是比较对应记录在数据里面是否重复
is null 指定值等于一
sql中判断两个字段是否相等 使用单等号 =
不能使用 <>
赋值使用:=
标准sql 并没有提供\具有转义的作用
like'\_%' escape '\'
默认升序排列
条件判断语法:
if(isnull,expr1,expr2) ?:
case value
when compare1 then result1
when compare2 then result2
...
else result3
end
第二种用法:
case
when boolean then result1
when boolean2 then result2
...
else
result 3
end
having 与where的区别:
1.不能在where 自居中过滤组,where子句仅用来过滤行,过滤组必须使用having
2.不能在where 子句使用主函数,having子句才能使用组函数
子查询:
1.from 后面作为临时视图 必须起一个别名
2.where 后面过滤条件
=any() :in 相似
>any() :大于最小值即可
<any():小于最大值即可
集合运算:
1.两个结果即所包含的数据列的数量必须相等
2.两个结果即所包含的数据列的数据类型也必须一一对应
union 合
minus 差 select 列 form表 minus select 列from 表 注意: mysql并不支持minus
select 列 from 表 where(,,,) not in (select ,,,from 表名)
intersect mysql也不支持
select 列,列 from表 join 表on 列=列and 列=列
7.函数
sin
char_length
Date_ADD(日期,interval 2 month)
ADDDATE(日期,日)
curdate()
curtime()
md5()
isnull(列)
但是尽量不要使用数据库中的特定函数,否则不能跨平台
2.JDBC:
1.使用JDBC对象作用:
callablestatement preparecall
Connection:关于供职事务的方法:
SavePoint setpoint() 创建一个节点
SavePoint setpoint(String name)
settransactionIsolation(int level 设置事务的隔离级别)
rollback() 回滚事务
rollback(savepoing )将事务回滚到指定的保存点
setAutoCommit(boolean) 是否关闭自动提交
commit()提交
mysql的驱动并不能支持所有的api方法
executeLargeUpdate();//支持大于integer.maxValue的更新,但是驱动并没有实现
jdbc:指定数据库驱动:// hostname:port/database
2.使用说明
connection statemtn result 都重写了autocloseable 接口 可以通过try语句关闭,try(){}即可
preparestatemt 的引入:
1加快执行效率预编译
2.防止sql 注入
3.无须拼接字符串
注: 占位符只能代表普通值,不能用来代指列名 和表名 更不能代指关键字
3.存储过程定义和JDBC调用:
重新定义分隔符
delimiter//
create procedure xxx(a int ,b int ,out c int )
begin
set c =a+b;
end
;
//
{call xxx(?,?,?)}
st.registeroutparameter(3,Types.INTEGER)
resultset 被称为可以可滚动的结果集
注:可以通过查询结果重新更新,重新写入到数据库中;通过 updatexxx() 然后通过updaterRow 提交修改该
注: a.如果创建可更新的结果即,则使用查询语句的数据通过长只能来一个数据表,而且查询结果即中的数据类必须包含主键列,否则会引起更新失败
b. blob Clob 以流的形式进行读写
4.结果集
resultsetMetadata 可以通过resultset 的getMetaData()方法得到,
常用方法:
int getColumnCount() //返回列数量
String getColumnName(int column)//返回列名
int getColunmType(int) 返回指定列的返回类型
resultsetMetadata的使用增大了系统的开销,如果已经知道每列的类型可以 省略
rowset 默认可滚动、可更新、可序列化的、可离线 ,已经将数据集度读到内存,进行离线操作,操作完成后在同步到底层数据源
rowset 的实现类 jdbcrowset(内部专用,以后可能删除) 没有广泛推广,在java1.7中增加 rowsetProvider 用来获取rowsetfactory 通过,rowsetfactory来得到 rowset,
rowsetfactory.createJdbcRowSet()
cachedRowset()
等得到rowset可以通过seturl,setusername,setpassword
使用resultset 的弊端:
1.将resultset 结果集写入javabean中,比较麻烦
2.直接将结果集显示,但是connection要一直打开,或者不能出现不可控情况
rowset 可以很好的解决
cachedRowset crs .populate(result);// 对 resultset进行重新装载为rowset
并且rowset 可更新、可序列化的、可离线
rs.updateString("列名","更改后";)
rs.updateRow();
con.setAutoCommit(false);
rs.acceptChanges(conn);对连接中出现的变化,提交给数据库
未防止大量数据读入内存, rowset 提供分页功能
rowset.setjpagesize()
rowset.populate(rs,起始页码)
rowset.nextpage() //conn 并没有关闭
rowset.priviouspage()//conn 并没有关闭
5.手写JDBC连接程序
a.过程
加载驱动类:jdbc具体实现类// 驱动类:就是实现的具体jdbc的jar文件
建立连接
发送sql 查询
得到查询结果
实现了Driver接口
b.实现
Class.forName("")//根据具体路径加载指定类
DriverManager.getConnection(Url)//通过接口DriveManager来建立用户和驱动之间的连接
conn.creteStatemnt();//执行语句,返回结果
sql="";
stmt.execute(sql);
注意:
建立连接就是一个socket连接,建立连接是比较耗时的
连接对象内部其实包含了socket对象,是一个远程的连接,比较好使,这是connection对管理的一个要点
真正开放中,为了提高效率,都会使用连接池来管理连接对象
statement接口:
statement(父)
PreparedStatement(子)预编译 效率高,防止sql注入
CallableStatement(子)
statement只能以拼接字符串的形式传参
容易发生sql注入的危险
sql中使用占位符,?必须与前面的类型相同
PreparedStatement ps = con.preparstament()
ps.setString(1,"");//参数索引是从1开始而不是0
ps.setString(2,"1234")
hibernate是从0开始的
setobject(1,"")
setDate引用的是sql.date
execute 的重载
excute()返回boolean是否有结果集
excuteUpdate 返回更新的行数 //insert update delete
excuteQuery()//select
ResultSet 迭代器
ResultSet set = ps.excuteQuery();
实际就是一个迭代器
与jdbc相关,如果连接终端ResultSet将不存在
while(re.next())是否含有下一条,从0开始
rs.getint(1)取出第一列
rs.getString(2)二列..
关闭连接:
finally{
conn.close();
}
resultset->statment->connection
将三个trycatch分开写;一行的一列
Batch
对于大量的批处理,建议使用statement,因为prepare
statement预编译处理有限,如果数据量特别大时,会出现异常
conn.setAutoCommit(false);//设为手动提交
stmt o conn.createStatement();
for(int if=0;i<20000;i++){
stmt.addBatch(" insert into t_user(t_user()valuse('gao'+i,now()))")
}
stmt.excuteBatch();
事务:
conn.rollback()
conn.setAutoCommit()//默认true 自动提交
rollback写在catch中
Clob和Blob:
CLOB(charater large object)
大量的文本数据 常通过流的形式处理
在数据库中有相应的类型:tinytext、text、mediumtext、longtext 存储 文章,小说
ps.setColb(2,new FileRead( new FIle("sjk:/jkjk")))// 将程序中字符传入到数据库中的clob
ps.setColb(2,new BufferedReader(Nnew InputStaremReader(new ByteArrayinputStream("字符串".getbytes()))))
clob c = rs.getClob("myinfo";
reader r = c.getCharcterSteram();
while((temp=r.read())!=-1)
{
(char) temp;
}
)
大文件一般直接存在文件服务器中,很少使clob和blob
tinyBolb、blob、mediumblob、longblob 存储小图片、电影 可以将文本文件流输入到数据库中
控制台无法展示图片
资源文件 db.properties folder
读取配置文件 使用properties pros =null;//读取和
static Properties pros = null;//可以帮助读取和处理资源文件中的信息
static {
pro = new properties();
pro.load(Thread.currentThread().getContextClassLoader.getResourceAsSteraem("db.properties"))
}
pro.getPropertieS("名称")
3.事务
要么同时成功,要么同时失败,是一个执行单元,起始于DML语句
结束于:
1.commit 或 rollback
2.ddl语句 Create table 自动执行commmit
3.执行dcl语句 grant语句 自动执行commmit
4.断开与数据库的连接
5执行一条dml但失败,自动执行rollback语句
事务的四大特点ACID
原子性 :同时执行,同时失败
一致性:一条语句操作失败,会滚到原有状态
隔离性: 事务运行过程中产生的结果不被别人查看
持久性:事务成功结束,将一直保持在硬盘中
级别:读取已提交(默认)
提交: 显示提交: commint
隐式提交: 执行ddl 或者dcl
回滚: 显式回滚 rollback
自动回滚: 系统错误 或者强制退出
一个mysql 命令行装口代表一个连接session,在改穿够设置的set autocommit =0,相当于关闭该练级的session 的自动提交,对其他连接不会产生影响
临时使用一下 事务 但不关闭自动提交
mysql 可以使用begin 或者start transaction
可以通过设置savepoint 保存中间点
savepoint a;
rollback to a ; 回滚到中间点
普通的提交奥、回滚都会结束当前事务,但会滚到指定中间点因为依然处在事务中,所以不会结束当前事务
JDBC管理事务 通过connection
conn.setAutoCommit(false);
conn.commint;
conn.rollback;
实际上,当Connection 遇到一个未处理的sqlexception异常时,系统将会非正常退出,事务也会自动回滚,但如果程序捕获了该异常,则需要
setsavepoint() Connection - savepoint
setsavepoint(String) Connection
rollback(savepoint save)
批量更新: 在resultsetmetadata中 查看是否支持 supportsBatchUpdate();
批处理 要当成事务进行处理
stmt.addBatch(sql1);
stmt.addBatch(sql2);
最新驱动中并不支持excuteLargeBatch();
4.连接池
DBCP: apache 开发 ,但是依赖另外的jar文件,可整合到Tomcat服务器上,也可以和应用和程序独立使用
C3P0:hibernate 推荐使用这个连接池,可以自动关闭不再使用的连接,不用导入 其他依赖的jar 文件
5.ORM
ORM基本思想(object relationship Mapping)
表结构跟类对应,表中的字段和类的属性对应,表中记录和对象对应
SORM框架(自定义的)
使用list<Object[]> 存储多条记录
使用map封装list<map> map<map>
使用javabean list<bean> 最常用
map<string ,object>
string 代指列名
object 代指行列值
优势:通过反射机制进行对应