【有书共读】《疯狂JAVA讲义》读书笔记13

在本周，我对mysql数据库和数据库接口JBDC有了一些接触，并有了自己的一些感悟，总结如下:

1、程序使用JDBC API以统一的方式来连接不同的数据库，然后通过Statement对象来执行标准的SQL语句，并可以获得SQL语句访问数据库的结果。

2、创建数据库：create database [if not exists] 数据库名； 删除数据库：drop database 数据库名；  建立了数据库，如果想使用该数据库：use 数据库名；   显示该数据库所有的数据表：show tables； 如果想查看指定数据库表的结构，查看多少列，每一列的数据类型： desc 表名。

3、SQL语句基础：SQL全称Structured Query Language，结构化查询语言。标准的SQL语句通常可以分为如下几种类型：

☞查询语句：主要由select关键字完成  ☞DML，Data Manipulation Language数据操作语言，主要由insert、update和delete三个关键字完成；  

☞DDL，Data Definition Language 数据定义语言，主要由create、alter、drop和truncate四个关键字完成。☞DCL，Data Control Language，数据控制语句，主要由grant和revoke两个关键字组成。 

☞事务控制语句：主要由commit、rollback和savepoint三个关键字完成。

truncate是一个特殊的关键字，它相当于先删除指定的数据库的表，然后再重建该数据表，即表里面的数据全都被清空。truncate比delete效率高，该操作会重设自动增长计数器。

DDL语句：表（table），约束（constraint，执行数据校验的规则，用于保证数据完整性规则），视图（view，一个或多个数据表里数据的逻辑显示，视图并不存储数据），索引（index，用于提高查询性能），函数（function，完成一次特定的计算，具有一个返回值），存储过程（procedure，用于完成一次完整的业务处理，没有返回值，但可以通过传出参数将多个值传给调用环境），触发器（trigger，相当于一个事件***，当数据库发生特定事件后，触发器被触发，完成相应的处理）。

4、表的基本类型：整型通常用int，小数点用decimal，普通长度varchar，大文本类型text，图片用blob，日期用datetime。

5、删除列：alter table 表名 drop 列名； 

重命名表：alter table 表名 rename to 新表名； 

 字段重命名：alter table 表名 change 旧字段名 新字段名 varchar(n)；  

删除表：drop table 表名；

6、大部分数据库支持5种完整性约束：①not null：非空约束；②unique：唯一约束，指定某列或者几列组合不能重复；③primary key：主键；④foreign key：外键，指定该行记录从属于主表中的某一条记录，主要用于保证参照完整性；⑤check：检查，指定一个布尔表达式，用于指定对应列的值必须满足该表达式。

7、①修改非空约束：alter table 表名 modify 列名 varchar(20) default '' not null;②唯一性约束：只需要在新建的列名后加上unique关键字即可，如：column_name varchar(20)unique；③联合约束：constraint unqui_name unique(col1,col2);

要求的是col1和col2两个组合的值不能重复。④列级主键：直接在列名后加primary key即可，表级主键：是指多列建立组合主键，只能使用表级：在建表的时候primary key(col1,col2)，或者在后期添加：alter table 表名 add primary key(col1,col2)，删除主键：alter table 表名 drop primary key。

8、MySQL支持整型的自动增长：id int auto_increment primary key

9、foreign key约束：外键约束主要用于保证一个或两个数据表之间的参照完整性，主要用来构建两个表或字段之间的参照关系：子（从）表外键列的值必须在主表被参照列的值范围之内，或者为空（也可以通过非空约束来限定不允许为空）。当主表的记录被从表记录参照时，主表的记录是不允许被删除的，必须先把从表里参照该记录的所有记录全部删除后，才可以删除主表的该记录。还有一种方式，删除主表记录时级联删除从表中所有参照该记录的从表记录。

从表外键参照列只能是主表的主键列或者唯一键列，这样才能保证从表记录可以准确定位到被参照的主表记录。同一个表可以拥有多个外键。增加外键列的表被称为从表，只要为外键列增加唯一约束就可以表示一对一的关联关系了。通常在一对多的情况下，在多的一端增加外键列。建立外键约束同样可以采用列级约束语法和表级约束语法：如果仅对单独的数据列建立外键约束，则使用列级约束语法即可：SQL的语法☞（teacher_table的主键是teacher_id），student_table表是从表，在建表的时候，从表句子如下：java_teacher int references teacher_table(teacher_id)。其中java_teacher是从表的某个列。MySQL的语法：foreign key(java_teacher)references teacher_table(teacher_id)，如果需要显示的指定外键约束的名字，应使用constraint关键字：constraint student_teacher_fk  foreign key(java_teacher)references teacher_table(teacher_id)。

如果需要建立多列组合的外键约束，则必须使用表级语法，SQL的语法☞：foreign key (java_teacher_name，java_teacher_pass) references teacher_table(teacher_name，teacher_pass)。

删除外键约束语法：alter table 表名 drop foreign key student_teacher_fk。增加外键约束使用：alter table 表名 add foreign key(col1，col2)references 表2 (col3，col4)。

☞外键约束不仅可以参照其它表，还可以参照自身，叫做自关联：foreign key (col1) references foreign_name(col2)。

☞如果想定义当删除主表记录时，从表的记录也会随之删除，则需要在建立外键约束后添加：on delete cascade 或添加：on delete set null，第一种是删除主表记录，参照该表的从表记录也会全部级联删除；第二种是指定当删除主表记录时，把参照该主表记录的从表的外键设为null。

check约束：语法如下：check(userName_id > 0)，但是MySQL支持得不够好。

索引：索引是存放在模式schema中的一个数据库对象，创建索引的唯一作用就是加速对表的查询，使用快速路径访问方法来快速定位数据，从而减少了磁盘的I/O。索引不能独立存在，必须属于某个表。建索引语句：create index index_name on 表名 (col1,col2...)，删除索引：drop index index_name on 表名。增加索引会增加系统的维护工作，系统开销，还需要消耗一定的磁盘空间。

DML语句，由insert into、update和delete from三个命令组成。

①insert into每次只能插入一条记录，表名后可以用括号列出需要插入值的列名，values后括号列出对应需要插入的值。如果省略了表名后面的括号及括号里的列名列表，默认将为所有列都插入值，则需要为每一列都指定一个值。

insert into 表1 (username,password)values('张三','123')；根据前面介绍的外键约束规则，外键列里的值必须是被参照列里已有的值，所以向表中插入记录之前，通常应该先向主表中插入记录，否则从表的外键列只能为null。

☞使用子查询的值插入：insert into 表1(col1) select col2 from 表2。一次可以插入多条记录。

②update语句用于修改记录，每次可修改多条记录。update 表1 set col1=value1,col2=value2...where condition

③delete from语句用于删除记录。delete from 表1 where condition。当主表记录被从表记录参照时，主表记录不能被删除，只有先将从表中参照主表记录的所有记录全部删除后，才可以删除主表记录。还有一种情况是设置了级联的删除on delete cascade或者使用了on delete set null，会执行相应的功能。 

查询语句：select。

①查询的结果还可以先执行函数，再输出：select userid+5 from 表1 where userid*3>10；

②concat函数用来进行字符串连接运算（MySQL支持）select concat(userName,'University')from 表1。

③as关键字使得查询出来的列名可以更改，还可以为表名更改查询的名字。

④MySQL和Oracle都支持dual虚表，比如：select 5*2 from dual。

⑤distinct关键字用来去除重复的行。

⑥between A and B：表示大于等于A，小于等于B，in关键字是在里面的所有范围；like关键字是字符串匹配，通配符；is null要求指定值等于null。SQL语句中可以使用两个通配符：下划线_和百分号%，下划线可以代表一个任意的字符，百分号可以代表任意多个字符。如果需要匹配这2个符号，请使用\作为转义字符。

⑦判断某个值是否为空，不要使用=null，因为null=null返回null，应该使用is null判断：select * from 表1 where userName is null；就查出了所有userName为null的记录。

⑧and、or关键字：where A>1 and B<9 ；where not userName like '\_%'查出名字不以下划线开头的所有记录；where (userid >3 or userName >'张')and java_teacher>1 使用括号强制先计算or运算。

⑨asc、desc：asc是升序，desc是降序。记法：a在前面，后面都会比a大，从小到大排序，升序。

分组和组函数：常用5个函数：①avg ([distinct|all]expr)：计算多行expr的平均值，必须是数值类型，使用distinct修饰表示不计算重复值，all使用与否效果都一样。②count({*|[distinct|all]expr})：计算多行expr的总数，星号*表示统计所有的行数，distinct表示不计算重复值。③max(expr)：计算多行的最大值。④min(expr)：计算多行的最小值。⑤sum([distinct|all]expr)：计算多行expr的总和，distinct表示不计算重复的。类型必须是数值类型。

例子：计算userName列总共有多少个值：select count(distinct userName)from 表1。

注：distinct和*不同时使用，否则出错。

对于大多数的数据库而言，分组计算有严格的规则：如果查询列表中使用了组函数，或者select语句中使用了group by分组子句，则要求出现在select列表中的字段，要么使用组函数包装起来，要么必须出现在group by子句中。

having：后面也会有一个表达式，只有满足该条件的表达式的分组才会被选出来。过滤组必须使用having子句，不能在where子句中使用组函数，having子句才可以使用组函数。select * from 表1 group by userName having count(*)>2：查询userName这个字段为一组且数量大于2的所有记录，所有列。select userId from 表1 where userId>5 group by userId having count(userId)>1：查询userId大于5的重复的userId。

子查询：

使用子查询注意：①子查询要用括号括起来；②把子查询当成数据表时（出现在from之后），可以为该子查询起别名，尤其是作为前缀来限定数据列时，必须给子查询起别名；③把子查询当成过滤条件时，将子查询放在比较运算符的右边，这样可以增加可读性；④把子查询当成过滤条件时，单行子查询使用单行运算符，多行子查询使用多行运算符。

1、select * from (select * from student_table)  t  where t.java_teacher_id>1 //子查询创建了一个临时视图t，别名是t。

2、把子查询当成where条件中的值，如果子查询返回单行、单列值，则被当成一个标量值使用，也就可以使用单行记录比较运算符：select * from student_table where java_teacher > (select teacher_id from teacher_table where teacher_name ='biandan');粗体部分将返回一个单行、单列值。如果子查询返回多个值，则需要使用in、any和all关键字，in可以单独使用：select * from 表1 where student_id in (select teacher_id from 表2);只要student_id 与该值列表中的任意一个值相等，就可以选出这条记录。any关键字，如果 “=any”与in关键字一样，<any小于任何一个都行，>any只要大于任何一个都行：select * from 表1 where student_id > any(select teacher_id from 表2)。关键字all，<all 要求值列表的最小，就是比最小的还要小，>all 要求大于值列中的最大值，就是必须大于里面最大的那个值：select * from 表1 where student_id >all(select teacher_id from teacher_id)。其实还可以使用多行、多列的组合，中间用逗号隔开。

集合运算：select语句查询的结果是一个包含多条数据的结果集，类似于数据里的集合，可以进行并union、交intersect和差minus运算。两个集合的运算必须满足：①两个结果集所包含的数据列的数量必须相等；②两个结果集所包含的数据列的类型必须一一对应。

1、union运算：select 语句1 union select 语句2。

2、minus运算：select 语句1 minus select 语句2。MySQL并不支持minus运算，因此只能使用子查询来完成了。

3、intersect运算：select 语句1 intersect select 语句2。MySQL并不支持intersect 运算，因此只能使用子查询来完成了。

JDBC的典型用法：

☞DriverManager：用于管理JDBC驱动的服务类，该类主要获取Connection对象，包含的方法是：public static synchronized Connection getConnection(String url,String user,String password)throws SQLException

☞Connection：代表数据库连接对象，每个Connection代表一个物理连接会话，想要访问数据库，必须获得连接，该接口的常用方法：

①Statement createStatement()throws SQLException：该方法返回一个Statement对象；

②PreparedStatement prepareStatement(String sql)throws SQLException：该方法返回预编译的Statement对象，即将SQL语句提交到数据库进行预编译；

③CallableStatement prepareCall(String sql)：该方法返回一个CallableStatement 对象，用于调用存储过程。②③是①的子类。

除此之外，Connection还有如下几个用于控制事务的方法：

①Savepoint setSavepoint()：创建一个保存点；②Savepoint setSavepoint(String name)：以指定名字来创建一个保存点；③void setTransactionIsolation(int level)：设置事务的隔离级别；④void rollback()：回滚事务；⑤void rollback(Savepoint savepoint)：将事务回滚到指定的保存点；⑥void setAutoCommit(boolean autoCommit)：关闭自动提交，打开事务；⑦void commit()：提交事务。Java 7为Connection新增了setSchema(String schema)、getSchema()两个方法，用来控制该Connection访问的数据库Schema。同时还新增了setNetworkTimeout(Executor executor，int millisecondes)、getNetworkTimeout()两个方法来控制数据库连接的超时行为。

☞Statement：用于执行SQL语句的工具接口。该对象既可以执行DDL、DCL语句，还可以执行DML语句和SQL语句。当执行SQL语句时，放回结果集，方法如下：①ResultSet executeQuery(String sql)：该方法用来执行查询语句，并返回查询结果对应的ResultSet对象，该方法只能用于查询语句。②int executeUpdate(String sql)：该方法用来执行DML语句（数据操作语句，由insert into、update和delete from三个命令组成），用来返回影响的行数，该方法执行DDL（数据定义语句，数据定义语言，主要由create、alter、drop和truncate四个关键字完成）将返回0。③boolean execute(String sql)：该方法可执行任何SQL语句。如果执行后第一个结果集为ResultSet对象，则返回true；如果执行后第一个结果为受影响的行数或没有任何结果，则返回false。Java 7为Statement新增了closeOnCompletion()方法，如果Statement执行了该方法，则当所有依赖于该Statement的ResultSet关闭时，该Statement会自动关闭。

☞PreparedStatement：预编译的Statement对象。性能较好，它比Statement多了如下方法：void setXXX(int parameterIndex，XXX value)：该方法根据传入参数值的类型不同，需要使用不同的方法，如int、String类型，传入的值根据索引传给SQL语句中的指定位置的参数，index从1开始计算，而不是0，PreparedStatement同样有Statement的三个方法。

☞ResultSet：结果集对象。ResultSet可以通过索引或者列名获得列数据。它包含了如下常用方法来移动记录指针：①void close()：释放ResultSet对象；②boolean next()：将ResultSet的记录指针定位到下一行，如果移动后的记录指针指向一条有效记录，则该方法返回true。还有很多方法。。。ResultSet可以通过getXXX(int columnIndex)或getXXX(String name)方法来获取当前行、指定列的值，前者根据列索引获取值，后者根据列名获取值。