Java数据库知识点总结(部分还有很多没整合进去)

Java数据库知识点总结(部分还有很多没整合进去) ? JDBC中的PreparedStatement相比Statement的好处 答:一个sql命令发给服务器去执行的步骤为:语法检查，语义分析，编译成内部指令，缓存指令，执行指令等过程。 select * from student where id =3----缓存--,xxxxx二进制命令 select * from student where id =3----直接取-,xxxxx二进制命令 select * from student where id =4--- -,会怎么干, 如果当初是select * from student where id =?--- -,又会怎么干, 1、上面说的是性能提高 2、可以防止sql注入。 ? 大数据量下的分页解决方法。 答:最好的办法是利用sql语句进行分页，这样每次查询出的结果集中就只包含某页的数据内容。在sql语句无法实现分页的情况下，可以考虑对大的结果集通过游标定位方式来获取某页的数据。 mysql: String sql = "select * from students order by id limit " ze; + pageSize*(pageNumber-1) + "," + pageSi oracle: String sql = "select * from " + (select *,rownum rid from (select * from students order by postime desc) where rid<=" + pagesize*pagenumber + ") as t" + "where t>" + pageSize*(pageNumber-1); select * from (select * from talk order by id) where rownum <= 5 and id not in (select id from (select * from talk order by id) where rownum <= 5*(1- 1)); sql server: String sql = "select top " + pageSize + " * from students where id not in" + "(select top " + pageSize * (pageNumber-1) + " id from students order by id)" + "order by id"; ? 说出一些数据库优化方面的? 1、 根据sql命令的缓存功能，用PreparedStatement 一般来说比Statement性能高。 还有sql语句全部大写，特别是列名和表名都大写 一个sql 发给服务器去执行，涉及步骤:语法检查、语义分析， 编译，缓存 “inert into user values(1,1,1)”-,二进制 “inert into user values(2,2,2)”-,二进制 “inert into user values(?,?,?)”-,二进制 2、如果程序能够保证数据的完整性，那在设计数据库时就去掉外键，因为它会影响插入和删除性能，。比喻:就好比免检产品，就是为了提高效率，充分相信产品的制造商 (对于hibernate来说，就应该有一个变化:empleyee->Deptment对象，现在设计时就成了employee,deptid) 3、表中允许适当冗余，譬如，主题帖的回复数量和最后回复时间等 4、看mysql帮助文档子查询章节的最后部分，例如，根据扫描的原理，下面的子查询语句要比关联查询的效率高: 1. select e.name,e.salary where e.managerid=(select id from employee where name='zxx'); 2. select e.name,e.salary,m.name,m.salary from employees e,employees m where e.managerid = m.id and m.name='zxx'; 5、将姓名和密码单独从用户表中独立出来。这可以是非常好的一对一的案例哟～ 6、索引能提高查询的性能 。。。。。。。。。。。。 ? 数据库三范式是什么?(一共有7个范式) 第一范式(1NF):字段具有原子性,不可再分。比如年龄之类的。 如果每列都是不可再分的最小数据单元(也称为最小的原子单元)，则满足第一范式 第二范式(2NF):如果一个关系满足1NF，并且除了主键以外的其他列，都依赖与该主键，则满足第二范式(2NF) 即第二范式要求每个表只描述一件事情。 比如订单表中包含订单编号和订单日期，但是如果还包含产品编号和产品价格，就不满足二 范式了 第三范式(3NF):如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式。简而言之，第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。 例如，帖子表中只能出现发帖人的id，而不能既出现发帖人的id，还出现发帖人姓名，否则，只要出现同一发帖人id的所有，它们中的姓名部分都必须严格保持一致，这就是数据 冗余。 ? Class.forName的作用?为什么要用? 答:按参数中指定的字符串形式的类名去搜索并加载相应的类，如果该类字节码已经被加载过，则返回代表该字节码的Class实例对象，否则，按类加载器的委托机制去搜索和加载该类，如果所有的类加载器都无法加载到该类，则抛出ClassNotFoundException。加载完这个Class字节码后，接着就可以使用Class字节码的newInstance方法去创建该类的实例对象了。 有时候，我们程序中所有使用的具体类名在设计时(即开发时)无法确定，只有程序运行时才能确定，这时候就需要使用Class.forName去动态加载该类，这个类名通常是在配置文件中配置的，例如，spring的ioc中每次依赖注入的具体类就是这样配置的，jdbc的驱动类名通常也是通过配置文件来配置的，以便在产品交付使用后不用修改源程序就可以更换驱动类名。 创建一个名称为 wx的用户，其密码为wx123 CREATE USER wx IDENTIFIED BY wx123 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; GRANT 命令可用于为用户分配权限或角色 CONNECT角色允许用户连接至数据库，并创建数据库对象 GRANT CONNECT TO wx; RESOURCE角色允许用户使用数据库中的存储空间 GRANT RESOURCE TO wx; 此系统权限允许用户在当前模式中创建序列，此权限包含在CONNECT角色中9 GRANT CREATE SEQUENCE TO wx; 授予用户 MARTIN 操作TEST表对象的权限 允许用户查询 TEST 表的记录 GRANT SELECT ON TEST TO MARTIN; 允许用户更新 TEST 表中的记录 GRANT UPDATE ON TEST TO MARTIN; 允许用户插入、删除、更新和查询 TEST 表中的记录 GRANT ALL ON TEST TO MARTIN; ALTER USER 命令可用于更改口令 修改 MARTIN 用户的密码 ALTER USER wx IDENTIFIED BY PWDOFWX; DROP USER 命令用于删除用户 删除 MARTIN 用户模式 DROP USER wx CASCADE; 示例: #把CONNECT、RESOURCE角色授予用户 epetGRANT CONNECT,RESOURCE TO epet; #撤销用户epet的RESOURCE角色 REVOKE RESOURCE FROM epet; #允许用户查看 EMP 表中的记录 GRANT SELECT ON emp TO epet; #允许用户更新 EMP 表中的记录 GRANT UPDATE ON emp TO epet; Char 固定长度的非 Unicode 字符数据 Varchar/varchar2 可变长度非 Unicode 数据 CREATE TABLE stuInfo ( sid char(13) NOT NULL , --学号 sName varchar(8) NOT NULL, 姓名-- sSex char(2) NULL ,--性别 sEbackground char(4) DEFAULT '高中' CHECK(sEbackground='高中' or sEbackground='大专' or sEbackground='本科') NULL ,学历-- sAddress varchar(50) NULL, 地址-- constraint PK_sid primary key (sid) ) CREATE TABLE stuMarks ( smid int NOT NULL, --编号 sid CHAR(13) NOT NULL, --学号 computer decimal(4,2) DEFAULT '0' CHECK(computer>=0 and computer<=100) NULL计算机基础, --分 C decimal(4,2) DEFAULT '0' CHECK(C>=0 and C<=100) NULL , --C语言分 JAVA decimal(4,2) DEFAULT '0' CHECK(JAVA>=0 and JAVA<=100) NULL, --JAVA语言分 SQL decimal(4,2) DEFAULT '0' CHECK(SQL>=0 and SQL<=100) NULL, --SQL语言分 JSP decimal(4,2) DEFAULT '0' CHECK(JSP>=0 and JSP<=100) NULL, --JSP语言分 project decimal(4,2) DEFAULT '0' CHECK(project>=0 and project<=100) NULL, -- 项目分 CONSTRAINT PK_smid PRIMARY KEY(smid), CONSTRAINT FK_sid FOREIGN KEY(sid) REFERENCES stuInfo(sid) ); 什么是数据完整性, 数据库创建表的过程是规定 数据 列 的 属性的过程，同时也是实施 数据完整性(包括域完整性、实体完整性、引用完整性和自定义完整性等)保证的过程。 数据库中的五种约束: 主键约束(Primary Key Constraint):要求主键列数据唯一，并且不允许为空 唯一约束(Unique Constraint):要求该列唯一，允许为空，但只能出现一个空值。 默认约束(Default Constraint):某列的默认值，如我们的男性学员较多，性别默认为“男”检查约束(Check Constraint):某列取值范围限制、格式限制等，如有关年龄的约束 外键约束(Foreign Key Constraint):用于两表间建立关系，需要指定引用主表的那列 ALTER TABLE stuInfo ADD CONSTRAINT PK_stuNo PRIMARY KEY (stuNo); ADD CONSTRAINT UQ_stuID UNIQUE (stuID); ALTER TABLE stuInfo ADD CONSTRAINT CK_stuAge CHECK(stuAge >= 15 AND stuAge <= 40); ALTER TABLE stuMarks ADD CONSTRAINT FK_stuNo FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo); ALTER TABLE stuInfo modify(stu_address varchar2(200) default '地址不详'); ALTER TABLE 表名 DROP CONSTRAINT 约束名 创建数据库表的过程实际上就是实施完整性约束的过程 SQL(Structured Query Language):结构化查询语言 SQL组成: DML(数据操作语言) ——查询、插入、删除和修改数据库中的数据; ——SELECT、INSERT、 UPDATE 、DELETE等; DCL(数据控制语言) ——用来控制存取许可、存取权限等; ——GRANT、REVOKE、DENY 等; DDL(数据定义语言) ——用来建立数据库、数据库对象和定义其列 ——CREATE TABLE 、DROP TABLE 、ALTER TABLE等 变量说明、控制、功能函数 ——定义变量、判断、分支、循环结构等 ——日期函数、数学函数、字符函数、系统函数等 创建序列 CREATE SEQUENCE emp_sequence START WITH 1 -- 从1开始计数 INCREMENT BY 1 -- 每次加几个 MAXVALUE( NOMAXVALUE ) -- 设置最大值 MINVALUE -- 设置最小值 (如果循环的话，是从最大值变为最小值再循环) NOCYCLE -- 一直累加，不循环 CACHE 10; 一旦定义了emp_sequence，你就可以用CURRVAL，NEXTVAL CURRVAL=返回 sequence的当前值 NEXTVAL=增加sequence的值，然后返回 sequence 值 比如: emp_sequence.CURRVAL emp_sequence.NEXTVAL ‘_’ 一个字符 % 任意长度的字符串 1、INSERT INTO„SELECT的使用 1)建表时多条语句同时插入 CREATE TABLE users ( cardid int not null, userName varchar2(10) null ); INSERT INTO users(cardid,userName) SELECT 16,'张三' FROM dual UNION SELECT 23,'李四' FROM dual UNION SELECT 25,'王五' FROM dual UNION SELECT 29,'六子' FROM dual UNION SELECT 30,'赵七' FROM dual )如果插入的数据不是固定的数据项，而是从其它表中筛选数据再插入， 2 可使用INSERT INTO„SELECT结构 INSERT INTO Account (Account.CardID, Account.Score) SELECT Users.CardID,2 FROM Users LEFT JOIN Account ON (Account.CardID= Users.CardID) WHERE Account.CardID IS NULL 2、快速的复制一张表 create table b as select * from a 3、算平均值的时候，空值不参与计算 从大到小排序时，空值位于最前列 4、模糊查询中的 between 25 and 5 是错的 5、内连接、左外连接、右外连接、全外连接、 交叉连接 没有空 右允许空 左允许空 左右允许空 很少用，可用于产生随机数 6、一般来说，表连接都可以用子查询替换，但有的子查询却不能用表连接替换 三张表以内，子查询效率较高。多余三张表，用表连接 子查询返回的值不能保证只有一个，所以用IN比用=安全点。 Oracle 数据库对象又称模式对象 数据库对象是逻辑结构的集合，最基本的数据库对象是表 其他数据库对象包括:同义词、序列、视图、索引 Create or replace public synonym xxx_syn for scott.emp; Create sequence emp_seqence Start with 1; Create or replace view xxxx as Select * from emp order by posttime; Create index article_index on article(aTitle,aCdate) 同义词 如果你每次查询的时候不想都输入很长的表名，可以考虑用同义词 同义词是现有对象的一个别名，作用: 1、简化SQL语句 2、隐藏对象的名称和所有者 3、提供对对象的公共访问 同义词共有两种类型:私有同义词(只能在其模式内访问，且不能与当前模式的对象同名 )和公有同义词(被所有的数据库用户访问) 创建私有同义词和公有同义词 CREATE SYNONYM emp_syn FOR SCOTT. emp; CREATE PUBLIC SYNONYM emp_syn FOR SCOTT.emp; 创建或替换现有的同义词 CREATE OR REPLACE SYNONYM emp_syn FOR SCOTT.emp; 删除同义词 DROP SYNONYM emp; 创建或删除同义词的时候如果权限不够，可以先赋权 比如: grant create synonym to 用户 序列 序列是用于生成唯一、连续序号的对象 序列可以是升序的，也可以是降序的 使用CREATE SEQUENCE语句创建序列: CREATE SEQUENCE emp_sequence START WITH 1 -- 从1开始计数 INCREMENT BY 1 -- 每次加几个 MAXVALUE( NOMAXVALUE ) -- 设置最大值 MINVALUE -- 设置最小值 (如果循环的话，是从最大值变为最小值再循环) NOCYCLE -- 一直累加，不循环 CACHE 10; 通过序列的伪列来访问序列的值 1、NEXTVAL 返回序列的下一个值 2、CURRVAL 返回序列的当前值 SQL> INSERT INTO toys (toyid, toyname, toyprice) VALUES ( toys_seq.NEXTVAL, ‘TWENTY’, 25); SQL> INSERT INTO toys (toyid, toyname, toyprice) VALUES ( toys_seq.NEXTVAL, ’MAGIC PENCIL’, 75); SQL>SELECT toys_seq.CURRVAL FROM dual; 使用ALTER SEQUENCE语句修改序列，不能更改序列的START WITH参数 SQL> ALTER SEQUENCE toys_seq MAXVALUE 5000 CYCLE; 使用DROP SEQUENCE语句删除序列 SQL> DROP SEQUENCE toys_seq; 视图 当一个查询是多表连接，且该查询使用频繁时，可以考虑创建视图 视图以经过定制的方式显示来自一个或多个表的数据 视图可以视为“虚拟表”或“存储的查询” 创建视图所依据的表称为“基表” 视图的优点有: 提供了另外一种级别的表安全性 隐藏的数据的复杂性 简化的用户的SQL命令 隔离基表结构的改变 通过重命名列，从另一个角度提供数据 创建视图的语法: CREATE [OR REPLACE] [FORCE] VIEW view_name [(alias[, alias]...)] AS select_statement [WITH CHECK OPTION] [WITH READ ONLY]; 使用 WITH CHECK OPTION 选项创建视图 CREATE OR REPLACE VIEW pause_view AS SELECT * FROM order_master WHERE ostatus = 'p' WITH CHECK OPTION CONSTRAINT chk_pv; 使用 ORDER BY 子句创建视图 CREATE OR REPLACE VIEW ord_ven AS SELECT * FROM v endor_master ORDER BY venname; 创建带有错误的视图 CREATE FORCE VIEW ven AS SELECT * FROM venmaster; 在视图上也可以使用修改数据的DML语句，如INSERT、UPDATE和DELETE 索引 索引的优点: 索引是与表相关的一个可选结构 用以提高 SQL 语句执行的性能 减少磁盘I/O 使用 CREATE INDEX 语句创建索引 在逻辑上和物理上都独立于表的数据 Oracle 自动维护索引 索引的类型: 1、索引 创建:CREATE INDEX item_index ON itemfile (itemcode) 修改:ALTER INDEX item_index REBUILD; 删除:DROP INDEX item_index; 2、唯一索引 唯一索引确保在定义索引的列中没有重复值 Oracle 自动在表的主键列上创建唯一索引 CREATE UNIQUE INDEX item_index ON itemfile (itemcode); 3、组合索引 组合索引是在表的多个列上创建的索引 索引中列的顺序是任意的 如果SQL语句的WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索 速度 Create index article_index on article (aTitle, aCdate); 4、反向键索引 5、位图索引 6、基于函数的索引