数据库简单常识
1、存储过程，调用方式
存储过程：预编译的SQL语句，优点是允许模块化的设计，只需要创建一次，后续可以多次调用。
调用方式：用命令对象老调用存储过程；可以供外部程序调用。
2、存储过程的优缺点
| 优点 | 缺点 |
|---|---|
| 可以重复使用，减少开发人员工作量 代码直接存放在数据库中，通过存储过程名直接调用，减少网络通讯 安全性高，有权限限制 预编译程序，执行效率高 | 移植性差 |
3、存储过程和函数区别
| 存储过程 | 函数 |
|---|---|
| 用于数据库完成特定操作或任务 | 用于操作特定数据 |
| 声明头部为procedure | 头部声明用function |
| 程序头部声明时不需描述返回类型 | 需要描述返回类型，而且PL/SQL块中至少要包括一个有效的return语句 |
| 可作为独立的PL/SQL语句来执行 | 不能独立执行，必须作为表达式的一部分调用 |
4、索引
定义：索引是一种特殊的查询表，可以利用它加速对数据的检索；索引可以是唯一的；缺点是它减慢了数据录入的速度，增大数据库尺寸。
5、什么样的字段适合建索引
唯一、不空、经常查询
6、索引类型
| 逻辑上 | 物理上 |
|---|---|
| 单行索引 多行索引 唯一索引 非唯一索引 函数索引 域索引 | 分区索引 非分区索引 B数索引 B+树索引 位图索引 |
7、事务和锁
事务：即为绑定在一起作为一个逻辑单元的一组SQL语句（必须同时全部执行或全部不执行）。原子性，隔离性，一致性，持久性。
锁：实现事务的关键，保证事务的完整性和并发性。（和现实中的锁功能差不多）
8、视图和游标
视图view：虚拟的表，具有和物理表相同的功能，可以理解为查看的一个窗口（一条查询SQL语句），并没有相关数据的定义，但是可以进行操作。
| 优点 | 缺点 |
|---|---|
| 数据安全 查询简单 维护数据的独立性，视图可以从多个表检索数据 相同的数据可以产生不同的视图 | 查询视图时，必须将视图的查询转化成对基本表的查询，如果视图由一个复杂多表查询所定义，那么无法更改数据 修改限制 |
游标：可以从一个集合中提取单条记录的手段，可以将它看作是一个指针，定位到指定行；需要逐条处理数据的时候，游标特别重要。
9、主键和外键
主键在本表中唯一，不可空。
外键和另外一张表的主键关联，可重复可空。
10、数据库查询语句很慢，如何优化？
1）建立索引
2）减少表之间的关联
3）简化查询字段，尽量返回少量数据
4）优化SQL语句，尽量走索引，数据量大的表排前面
11、数据库三范式以及BCNF
1）第一范式：列不可再分
2）第二范式：行可以唯一区分，主键约束（每一行数据只做一件事情，如果数据列出现重复，就把表拆分开来）；非主键属性，完全依赖于主键属性
3）第三范式：表的非主属性不能依赖于其他表的非主属性（数据不能存在传递关系，每个属性都跟主键有直接关系而不是间接关系，比如：a–>b–>c，这种关系是不允许的），非主键属性无传递依赖
4）BCNF：满足三范式，消除每一个属性对候选码的传递依赖
12、union和union all区别
1）union会筛选掉重复的数据（一定不包含重复）
2）union all会将两个结果合并后返回，并不会检查其中的重复问题。（有可能包含重复）
13、char，varchar，varchar2,nchar区别
| 类型 | 区别 |
|---|---|
| char | 长度固定：定义多少长度，在内存中就占多少长度。 |
| varchar | 长度可变 |
| varchar2 | oracle定义的新类型，目前和varchar是同义词。将varchar存储空字符串的特性改为存储null值 |
| nchar | 表示存储的是Unicode数据类型的字符，即中文和英文都采用2个字符，防止中英文混用时出错 |
备注：null代表内存中根本不存在（没钱包）；空字符串也是字符串，有内存空间（有钱包，但是没钱）。
14、MySQL、SQL Server与Oracle区别
教学用SQL Server，自己玩用MySQL，花钱买放心就用Oracle。
| 数据库名 | 平台 | 安全性 | 性能 | 可操作性 | 适用范围 | 主要缺点 | 主要优点 |
|---|---|---|---|---|---|---|---|
| MySQL | 几乎所有平台适用 | 没有安全认证 | 采用多线程编程，能够充分利用CPU | 操作简单，有命令行和GUI | 中小企业，政府 | 安全系统 缺乏标准的RI机制 不支持热备份 | 开源 可移植性 支持多线程，充分利用CPU资源 有效提高查询速度 提供TCP/IP、ODBC、JDBC等多种数据库连接途径 |
| SQL Server | 只能在windows平台 | 没有任何安全认证 | 多用户时性能不佳 | 操作简单，只有GUI | 中小企业，政府 | 可移植性差 增加服务器负担 | 高可用性 提供管理工具 安全性加强 可伸缩性 |
| Oracle | 几乎所有平台适用 | 最高级ISO标准认证 | 性能高 | 操作复杂，有命令行和GUI | 大中型企业 | 操作复杂，价格昂贵 | 功能全，性能优异 跨平台 |
15、数据库语句类型
1）DDL(Data Definition Language)数据库定义语言：Create,Drop,Alter,Truncate
2）DML(Data Manipulation Language)数据库操作语言：Insert,Delete,Select,Update
3）DCL(Data Control Language)数据库控制语言：Grant,Revoke
16、行转列，列转行，去重
行转列：使用decode函数
列转行：使用case when语句
去重：distinct
17、序列seqence
Oracle使用序列来生成唯一编号，用来处理表中自增字段。简言之，就是一旦用户访问一个序列号，Oracle将在处理下一个请求之前自动递增下一个编号，从而确保不出现重复值。
作用：作为代理主键，唯一标识；记录数据库中最新动作。
18、Oracle基本数据类型
1）字符串：char,nchar,varchar,varchar2,nvarchar2
2）数字类型：integer,number
3）浮点类型：float,binary_float,binary_double
4）日期类型：data,timestamp
5）LOB类型：blob,clob,nclob,bfile
19、B+树索引和哈希索引
哈希索引：等值查询效率高，不能排序，不能进行范围查询
B+索引：数据有序，范围查询
20、关系型数据库，非关系型数据库
| 数据库类型 | 代表数据库 | 特性 | 优点 | 缺点 |
|---|---|---|---|---|
| 关系型数据库 | Oracle、DB2、Microsoft SQL Server、Microsoft Access、MySQL | 采用了关系模型来组织数据 事务一致性 | 容易理解 使用方便 易于维护 支持SQL，可用于复杂查询 支持事务 | 为了维护一致性所付出的代价就是读写性能较差 固定的表结构 不支持高并发读写需求 不支持海量数据的高效读写 |
| 非关系型数据库 | NoSql、Cloudant、MongoDb、redis、HBase | 使用键值对存储数据 分布式 一般不支持ACID特性 算是一种数据结构化存储方法的集合 | 数据没有耦合性，容易扩展 分布式 无需经过SQL层解析，读写性能高 | 不支持事务 不提供sql支持 |
21、数据库模式
1）外模式：也称用户模式，即为用户看到的数据库的数据视图，使用DML语言；可以有多个。
2）模式：也称逻辑模式或概念模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。使用DDL语言。
3）内模式：也称存储模式，是数据物理结构和存储方式的描述，只能有一个。
22、约束
| 字段 | 描述 |
|---|---|
| NOT NULL | 控制字段内容一定不能为空 |
| UNIQUE | 控制字段内容不能重复，一个表允许有多个Unique约束 |
| PRIMARY KEY | 控制字段不能重复，但一个表只能有一个 |
| FOREIGN KEY | 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列 |
| CHECK | 用于控制字段的值范围 |
| DEFAULT | 用于设置新记录的默认值 |
23、触发器
定义：是一类特殊的存储过程，不由用户直接调用，它在指定表中的数据发生变化时自动生效；它可以查询其他表，可以包含复杂的Transact-SQL语句，将触发器和触发它的语句作为可在触发器内回滚的单个事务对待，常常用于强制业务规则和数据完整性。
23、主属性，主码，候选码
（摘抄）先说候选码，候选码就是可以区别一个元组（即表中的一行数据）的属性或属性的集合，比如学生表student(id,name,age,sex,deptno),其中的id是可以唯一标识一个元组的，所以id是可以作为候选码的，既然id都可以做候选码了，那么id和name这两个属性的组合可不可以唯一区别一个元组呢？显然是可以的，此时的id可以成为码，id和name的组合也可以成为码，但是id和name的组合不能称之为候选码，因为即使去掉name属性，剩下的id属性也完全可以唯一标识一个元组，就是说，候选码中的所有属性都是必须的，缺少了任何一个属性，就不能唯一标识一个元组了，给候选码下一个精确的定义就是：可以唯一标识一个元组的最少的属性集合。而码是没有最少属性这个要求的。另外，一个表的候选码可能有多个，从这些个候选码中选择一个做为主码，至于选择哪一个候选码，这个是无所谓的，只要是从候选码中选的就行。
至于主属性，刚才提到了，一个表可以有多个候选码，那么对于某个属性来说，如果这个属性存在于所有的候选码中，它就称之为主属性
语法
1、DDL
1）对数据库操作：
create database test
create database test character set utf-8 #设置编码值
show databases #查看数据库
use test #使用数据库
select database() #查看当前使用的数据库
show create database test #查看数据库结构
drop database test #删除数据库
1234567
2）对数据表操作：
create table student( #建表
Sname char(8) not null unique,
Ssex char(2),
Sno int
)
drop table student #删除表
create table new_student select * from student #少量数据备份
rename table student to new_student #修改表名
alter table student add Sdept varchar(32) not null #添加列Sdept到表student，且列不为空
alter table student change Ssex new_Ssex #修改列名Ssex为new_Ssex
alter table student modify Sno varchar(10) #修改列Sno类型
alter table student drop Ssex #删除列Ssex
123456789101112
3）索引操作
create index name_index on student(Ssex) #创建索引
create unique index name_index on student(Ssex) #创建唯一索引
alter table student add unique name_index(student) #创建唯一索引
drop index name_index on student #删除索引
show index from student #查看索引
12345
2、DML
1）基本操作
#增
insert into student (Sname,Ssex,Sno) value (值1,值2,值3)
insert into student values (值1,值2,值3)
#删
delete from 表名
delete from 表名 where Ssex='男' and Sno>20
#改
updata student set Sname='大佬',Ssex='男' where Sno=1
#查
select Sname,Ssex from
12345678910
2）惊为天人
范围查找：between…and…
指定：in
模糊匹配：like
select * from student where Ssex='男' and Sname like '%伟%' or Sno between 20 and 40
1
3、order by
用法：写在where后，给多个字段排序的DQL查询语句。
语法：
selece 字段列表 from 表名 where 条件 order by 字段1 asc/desc,字段2 asc/desc …
例：
select Sno from student where Sno between 20 and 40 order by Sno desc
1
4、授权grant，拒绝deny，收回revoke
1）grant：赋予一个用户、一个组或所有用户访问权限
2）deny：禁止用户对数据表的更新权限
3）revoke：收回用户对数据表的删除权限
例：
1) grant update, delete on student to member with grant option
#允许用户member对数据表student进行update和delete操作，
#其中with grant option表示member用户可以向其他用户授予他拥有的权力
2) deny update on student to member cascade
#禁止用户member对数据表student的更新权限
3）revoke delete on student from student
收回用户member对student表的删除权限
1234567
5、group by与having
1）group by是分组查询，一般和聚合函数（count,sum,avg,max,min）配合使用。使用原则是：select后面所有列中，没有使用聚合函数的列必须出现在group by后。
2）where的作用是对查询结果进行分组前，将不符合where条件的行去掉，条件中不能包含聚组函数，使用where条件显示特定的行。
3）having子句的作用是筛选满足条件的组，分组后，使用having条件显示特定的组。
例：
select A count(B) from table group by A having count(B)>2
1
5、连接，左外连接，右外连接
1）内连接
内连接使用比较运算符依据两个表共有列的值匹配两个表中的行；典型的运算符有：=,<>
select * from table1 inner join table2 on table1.id=table2.id
#显示满足条件的数据，并不以哪个表为主
12
2）外连接
-
左连接 (left join)：左表所有数据+右表满足条件的
select * from table 1 left join table 2 on table1.id=table2.id #table1表中的数据全部返回；table2表中只返回满足where条件的数据 12 -
右连接 (right join)：右表所有数据+左表满足条件的
-
select * from table1 right join table2 on table1.id=table2.id #table2表中的数据全部返回，table1表中只返回满足where条件的数据 12例子：
a表：id name 1 周杰伦 2 林俊杰 3 王力宏 b表：
id name author_id 1 双截棍 1 2 一千年以后 2 3 壁虎漫步 4 -
内连接：
-
select a.,b. from a inner join b on a.id=b.author_id
结果是：1 周杰伦 1 双截棍 1
2 林俊杰 2 一千年以后 2 -
左连接：
-
select a.,b. from a left join b on a.id=b.author_id
结果是：1 周杰伦 1 双截棍 1
2 林俊杰 2 一千年以后 2
3 王力宏 null -
右连接：
-
select a.,b. from a right join b on a.id=b.author_id
结果是 ：1 周杰伦 1 双截棍 1
2 林俊杰 2 一千年以后 2
null 3 壁虎漫步 46、on,where,having
1）on：连接操作
2）where：条件过滤
3）having：分组操作执行顺序：on>where>聚合函数>having
性能：on>where>having7、distinct 去重,exists,in
1）distinct：去重。
2）exists：指定一个子查询，检测行的存在，强调是否返回结果集，不强调返回内容
3）in：确定给定的值与子查询或列表中的值相匹配；引导的子句只能返回一个字段
