数据库_抻透

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 #删除数据库

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

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 #查看索引

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 

2）惊为天人

范围查找：between…and…
指定：in
模糊匹配：like

select * from student where Ssex='男' and Sname like '%伟%' or Sno between 20 and 40

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

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表的删除权限

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

5、连接，左外连接，右外连接

1）内连接
内连接使用比较运算符依据两个表共有列的值匹配两个表中的行；典型的运算符有：=,<>

select * from table1 inner join table2 on table1.id=table2.id
#显示满足条件的数据，并不以哪个表为主

2）外连接

• 左连接 (left join)：左表所有数据+右表满足条件的

  select * from table 1 left join table 2 on table1.id=table2.id
  #table1表中的数据全部返回；table2表中只返回满足where条件的数据
  

• 右连接 (right join)：右表所有数据+左表满足条件的

• select * from table1 right join table2 on table1.id=table2.id
  #table2表中的数据全部返回，table1表中只返回满足where条件的数据
  

  例子：
  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    壁虎漫步    4

  6、on,where,having

  1）on：连接操作
  2）where：条件过滤
  3）having：分组操作

  执行顺序：on>where>聚合函数>having
  性能：on>where>having

  7、distinct 去重,exists,in

  1）distinct：去重。
  2）exists：指定一个子查询，检测行的存在，强调是否返回结果集，不强调返回内容
  3）in：确定给定的值与子查询或列表中的值相匹配；引导的子句只能返回一个字段