数据库理论

数据库知识

MySQL查询语句

1. 去掉重复值：distinct可以将指定列的重复值剔除

   语法结构：select distinct col_name form tab_name;

2. 查询语句中使用列的别名：AS可以为列起别名，AS也可以省略不写

   语法结构：select col_name AS alias_name,col_name2 alias_name2 from tab_name;

3. between and:表示介于两者之间

4. in查询：用于没有规律的范围查询，相当于多个or的组合

   例如：查询公司职位为项目经理，项目组长的员工：select * from emp where job in(‘项目经理’，’项目组长’)；

5. 模糊查询like:模糊查询使用通配符%和_实现，%表示任一个任意字符， _表示任意一个字符

6. 排序order by子句：用于排序，升序ASC,降序DESC,默认ASC

   1. 例如：按照工资从高到低查询员工信息，工资相同再按照奖金升序排序
   2. 语法：select * from emp order by sal DESC,comm asc;

7. 聚合函数查询：将多个数据聚合成一个数据，共有sum、max、min、avg、count五个。

   1. 例如：查询最高工资，最低工资，平均工资，总工资，公司总人数
   2. 语法：select max(sal) 最高工资，min(sal) 最低工资，avg(sal) 平均工资，sum(sal) 总工资，count(sal) 公司总人数 from emp ;

8. 分组查询group by子句：分组是按照指定的列，将列中相同的值分为一组，一组用一条记录来表示。分组后，可以对每组中的数据进行聚合查询。

   1. 例如：统计每个部门的员工数量

   2. 语法：select deptno 部门编号，count(*) 人数 from emp group by deptno;

      注意：

      1. select出现的字段，必须出现在group by后面。
      2. 分组函数只能出现在select、order by子句、having子句中，不能出现在where、Group by子句中。

9. 分组后筛选having子句：分组后，对每组的数据进行筛选。

   1. 例如：统计部门员工数量超过3人的部门
   2. 语法：select deptno 部门编号，count(*) 员工数量 from emp Group by deptno having count( *)>3;

10. 多表查询

    1. 等值连接查询：效率最低

       例如：查询每个部门的所有员工

       语法：select dept.dname,emp.ename from emp,dept where emp.deptno =dept.deptno;

    2. 内连接查询：使用inner join关键字实现，inner可以省略

       例如：查询每个部门的所有员工

       语法：select dept.dname,emp.ename from emp inner join dept on emp.deptno=dept.deptno;

    3. 外连接：分为左外连接(left join)和右外连接(right join)

       1. 例如：查询每个部门的所有员工
       2. 语法：select dept.dname,emp.ename from emp right join dept on emp.deptno=dept.deptno;

11. 子查询

    1. 定义：为了给主查询语句提供数据而先执行的查询语句称为子查询。
    2. 单行子查询：
       1. 例如：查询软件部门下的所有员工
       2. 语法：select * from emp e where e.deptno=(select d.deptno from dept d.dname=’软件部’);
    3. 多行子查询：
       1. 例如：统计所有的员工都分布在哪些部门信息
       2. 语法：select * from dept d where e.dept IN (select e.deptno from emp e);
       3. 注意：多行子查询返回多行记录，就需要多行记录的操作符，常见的：IN、ANY、ALL等。

12. 分页查询limit子句

    1. 当表的数据很多时，可以使用分页查询，降低对数据库服务器的压力。
    2. 例如：分页查询员工信息
    3. 语句：select * from emp limit 0,2;
    4. 注意：0代表从第几条开始查询，2代表查询多少条数据。

13. 综合查询示例

    1. 需求：查询2000年以后入职，部门员工数量超过2人以上的部门，按照部门人数从多到少排序输出，分页显示，每页5条。
    2. 语句：select * from emp where hirdate>=’2000-01-01’ group by deptno having count(*)>=2 order by DESC limit 0,5;
    3. 注意：当SQL语句中有多个子句时，子句的书写顺序和执行顺序如下：| 子句 | select | from | where | group | having | order | limit |
       | ——– | —— | —– | —– | —– | —— | —– | —– |
       | 书写顺序 | （1） | （2） | （3） | （4） | （5） | （6） | （7） |
       | 执行顺序 | （5） | （1） | （2） | （3） | （4） | （6） | （7） |

事务：ACID

• 事务由多个数据操作(增删改查)组合
• 事务是数据操作的最小单元
• 事务中所有操作全部成功,事务整体成功
• 当事务中一项操作失败,事务整体失败

1.事务定义：事务是一组工作单元，他有一条或多条sql语句组成，一个事物可以在操作的数据对象上执行一个或多个操作

2.事务的特点
1.原子性：事务要么都成功执行，要么都不执行
2.一致性：事务必须保持数据库数据在一致状态（如：要删除DEPT表数据，但是EMP中还有该部门员工，就拒绝此操作）
3.隔离性：一个事物的执行不能被其它事务干扰
4.永久性：一个事务的执行一旦提交，它对数据库的数据的改变就应该是永久性的
3.事务控制
1.显示提交：使用commit
2.隐式提交：执行DDL,DCL或程序正常退出
3.显示回滚：使用rollback
4.自动回滚：程序异常或正常退出
4.oracle 自动提交
1.设置自动提交：set autocommit on
2.update delete

数据库原理知识

SQL语句由如下命令组成：

1. 数据定义语言（DDL）:包括create(创建)、alter（修改）、drop（删除）命令等
2. 数据操纵语言（DML）:包括insert、update、delete、select命令等
3. 数据查询语言（DQL）:包括基本查询语句、Order By子句、Group By子句等
4. 数据控制语言（DCL）:grant(授权)、revoke（撤销）命令
5. 事务控制语言（TCL）:包括commit（提交）、savepoint（保存点）、rollback（回滚）命令。

Mysql的约束有五种：主键，外键，非空，默认，检查（mysql没有实现）

存储过程： 一些SQL的集合,执行速度快,执行后会存储在数据库中,方便以后调用
索引： 加快查询速度,不过,但索引的创建有一定的限速,超过了就会减慢降低效率
视图： 视图有点像快照,可以用来查看复杂的结构,它是一张虚表,只能用于做查询用
触发器： 用于保证主、外键的安全性，是隐式调用的，主要是行级触发，语句触发和替代触发器。

各自的优点：

• 存储过程：方便调用，整体化
• 索引：优化搜索，对于大量数据，能减少很多时间
• 视图：方便交叉查看多个表的数据
• 触发器：方便多个表的数据一致

数据持久化

1. 持久数据就是将数据保存到数据库。数据持久化就是将内存中的数据模型转换为存储模型,以及将存储模型转换为内存中的数据模型的统称;数据模型可以是任何数据结构或对象模型,存储模型可以是关系模型、ＸＭＬ、二进制流等;

2. 瞬时状态:保存在内存的程序数据，程序退出后，数据就消失了，称为瞬时状态

   持久状态:保存在磁盘上的程序数据，程序退出后依然存在，称为程序数据的持久状态

   持久化:将程序数据在瞬时状态和持久状态之间相互转换的机制

3. 为什么要持久化？

   持久化技术封装了数据访问细节，为大部分业务逻辑提供面向对象的API。

   1. 通过持久化技术可以减少访问数据库数据次数，增加应用程序执行速度；
   2. 代码重用性高，能够完成大部分数据库操作；
   3. 散耦合，使持久化不依赖于底层数据库和上层业务逻辑实现，更换数据库时只需修改配置文件而不用修改代码。

Java持久化技术中三层架构搭建

1. 创建数据库，数据表
2. 创建java项目，添加数据库jar包
3. 分包：数据访问层dao、业务逻辑层service、界面层ui、实体类
4. 创建实体类：用于三层包之间的数据传递
5. 创建DBHelper类：执行SQL语句
6. 创建Dao类：拼写SQL语句，交给DBHelper类来执行
7. 创建业务逻辑Service类
8. 创建界面UI类
9. 测试运行
10. 在调试模式下单步运行程序，观察三层间调用过程。

原生JDBC操作数据库的步骤

1. 注册数据库驱动
2. 获取数据库连接
3. 获取发送执行sql的对象
4. 定义SQL语句
5. 执行sql并返回结果
6. 打印执行结果
7. 关闭资源。

MySQL与Oracle区别

相同点：都是目前比较流行的关系型数据库

不同点：

1. Mysql里可以用双引号包起字符串，Oracle里只可以用单引号包起字符串。

2. Mysql可以设置自增，oracle不支持自增，但可以通过创建自增的序列号来完成自动增长。
            3. mysql对sql语句的扩展性和灵活性比oracle要好，比如limit功能，insert可以一次插入多条数据。
         4. mysql默认自动提交，oracle需要手动提交。
                5. Oracle是大型数据库而Mysql是中小型数据库，Oracle市场占有率达40%，Mysql只有20%左右，同时Mysql是开源的而Oracle价格非常高。
               6. Oracle支持大并发，大访问量，是OLTP(On-Line Transaction Processing联机事务处理系统)最好的工具。
                      7. Mysql的非空字段也有空的内容，ORACLE里定义了非空字段就不容许有空的内容。
                     8. MySQL处理翻页的SQL语句比较简单，用LIMIT开始位置，记录个数；Oracle处理翻页的SQL语句就比较繁琐。每个结果集只有一个ROWNUM字段标明它的位置，并且只能用ROWNUM<100，不能用ROWNUM>80。

DB2、Oracle、mysql和sql server对比

性能对比

 平台：
 Mysql、oracle、DB2都是可以在当下几乎所有主流平台上使用的；
 而sql server只能在windows平台，没有丝毫的开放性；

 安全性：
 sql server没有获得任何的安全认证；
 oracle、DB2都获得了最高级别的ISO标准认证；

 性能：
 sql server在多用户时性能不佳
 DB2适用于数据仓库和在线事物处理性能较高
 oracle性能最高

 可操作性：
sql server操作简便，但只有GUI；
oracle操作比较复杂，同时有GUI与命令行操作，windows.NT及unix下一样；
DB2及mysql操作比较简单，同时有GUI与命令行，windows.NT及unix下一样；

table操作

1,增加列:相同  
　　alter table test add mail varchar(128);  
2,删除列:  
　　oracle 与mysql相同:alter table test drop column mail;  
　　db2 ：alter table test drop column mail 删除列后需要reorg table（重组表）  
3,更改列名  
　　oracle : alter table test rename column mail to mail2;  
　　mysql : alter talbe test change mail mail2 varchar(128);  
　　db2 : 不提供更改列名功能(解决办法同删除，或者通过建立一个新视图解决)  
4,更改列类型  
　　oracle :alter table test modify column (mail2 integer);  
　　mysql :alter table test modify column mail2 integer;  
　　db2 :alter table test alter mail varchar(256) 只可以加宽，不能更改类型  
5,更改列的限制(主键、非空)  
　　db2 :alter table test alter mail null/not null;  
　　mysql :alter table test modify mail2 varchar(29) not null;  
　　oracle:alter table test modify mail2 null/not null;
6,更改列的长度
　　db2:ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE VARCHAR(new_length);  
　　mysql:ALTER TABLE table_name MODIFY column_name VARCHAR(new_length);  
　　oracle:ALTER TABLE table_name MODIFY column_name VARCHAR2(new_length BYTE);

各种数据结构的优缺点比较

1. 数组： 优点：插入快，如果知道了下标可以非常快速的存取数据 缺点：查找，删除慢，大小固定
2. 有序数组 优点：比无序数组查找快 缺点：删除，插入慢，大小固定
3. 栈 优点：提供后进先出方式的存取 缺点：存取其他项很慢
4. 队列 优点：提供先进先出方式的存取 缺点：存取其他项很慢
5. 链表 优点：插入快，删除快 缺点：查找慢
6. 二叉树 优点：如果树保持平衡，查找，插入，删除都快 缺点：删除算法复杂
7. 红黑树 优点：查找，插入，删除都快，树总是平衡的 缺点：算法复杂
8. 哈希表 优点：如果关键字已知则存取，插入极快 缺点：删除慢，如果不知道关键字则存取很慢，对存储空间使用不充分
9. 堆 优点：插入，删除快，对最大数据项的存取很快 缺点：对其他数据项存取慢
10. 图 优点：对现实世界建模 缺点：有些算法慢且复杂

MySQL四种常用存储引擎

• MyISAM存储引擎

不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有 要求或者以select，insert为主的应用基本上可以用这个引擎来创建表。

• InnoDB存储引擎

该存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM引擎，写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。
InnoDB存储引擎的特点：支持自动增长列，支持外键约束

• MEMORY存储引擎

Memory存储引擎使用存在于内存中的内容来创建表。每个memory表只实际对应一个磁盘文件，格式是.frm。memory类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。

• MERGE存储引擎

Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询，更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。

MySQL优化

• 选择正确的存储引擎

MySQL中的两个主要存储引擎是MyISAM和InnoDB。每个人都有自己的优点和缺点。

MyISAM很适合阅读量大的应用程序，但是当有大量的写操作时，它的扩展就不太好了。即使您正在更新一行中的一个字段，整个表也会被锁定，在该查询完成之前，任何其他进程都无法从中读取。MyISAM在计算SELECT(*)类型查询方面非常快速。

InnoDB往往是一个更复杂的存储引擎，在大多数小型应用程序中可能比MyISAM慢。但是它支持基于行的锁定，这样可以更好地扩展。它还支持一些更高级的特性，如事务。

• 避免select *

从表中读取的数据越多，查询就会变得越慢。它会增加磁盘操作所需的时间。用具体的字段代替“*”。

• 建立合适的索引

索引少了查询慢；索引多了占用空间大，执行增删改语句的时候需要动态维护索引，影响性能 选择率高（重复值少）且被where频繁引用需要建立B树索引；
一般join列需要建立索引；复杂文档类型查询采用全文索引效率更好；

在哪些字段使用索引？

1. 较频繁的作为查询条件字段应该创建索引
2. 唯一性太差的字段不适合创建索引，尽管频繁作为查询条件，例如gender性别字段
3. 更新非常频繁的字段不适合作为索引
4. 不会出现在where子句中的字段不该创建索引

• 查询数据Limit 1

当只需要一条数据时使用LIMIT 1.我们作为开发者，是能够知道我们需要的数据的条数的，若已经知道结果只有一条的时候，一定要使用limit 1 ，这样一来，MySQL在查询到一条数据之后，会立即停止搜索，这会带来性能上的提升。

• 使用enum而不是varchar

实际上，enum保存的是tinyint类型，但其显示为字符串。用这个字段来作一些选项列表就变得很合适了。比如你有一个字段，比如“性别”、“状态”或“所属部门”等，你知道这些字段的值是固定且有限的，那么可以考虑使用enum。

• 选取最适用的字段属性

  1. 数据库中的表越小，查询速度越快。因此，在创建表的时候，为了获得更好的性能，可以将表中字段的宽度设得尽可能小。
  2. 尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
  3. 例如“省份”或者“性别”，我们可以将它们定义为enum类型。因为在MySQL中，enum类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多,提高数据库的性能。

• 使用连接（JOIN）来代替子查询

连接（JOIN）..之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

• JOIN字段建议建立索引

一般JOIN字段都提前加上索引。

• 尽可能的使用varchar/nvarchar代替char/nchar

因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。char是一种固定长度的类型，varchar则是一种可变长度的类型。

• 使用UNION ALL替代UNION

UNION ALL的执行效率比UNION高，UNION执行时需要排重；UNION需要对数据进行排序。

数据库软件

PL/SQL Developer

PL/SQL Developer是一个集成开发环境，专门面向Oracle数据库存储程序单元的开发。不能连接其他数据库。

1、PL/SQL Developer 是什么？

PL/SQL Developer是一个集成开发环境，专门面向Oracle数据库存储程序单元的开发。如今，有越来越多的商业逻辑和应用逻辑转向了Oracle Server，因此，PL/SQL编程也成了整个开发过程的一个重要组成部分。PL/SQL Developer侧重于易用性、代码品质和生产力，充分发挥Oracle应用程序开发过程中的主要优势。

Oracle 数据库并不提供图形界面的开发环境，只是自带了一个类似于 CMD 的 SQL Plus 开发工具，所以需要一款 PL/SQL Developer这样的集成开发软件。