数据库基础--01

数据库：数据的文件系统
阶段分类：
层次数据库
网状数据库
关系数据库
对象型数据库

主流数据库： Oracle、MySQL、DB2、SQLService
(安装过程不要有中文名称)

select简单查询：
显示列表：“通配符”、字段别名
算术表达式： null值的处理
distinct关键字：过滤重复
where条件：比较运算、逻辑运算

@当前数据库的分类？什么是关系型数据库？什么是非关系型数据库？
当前数据库分为关系型数据库和非关系型数据库
关系型数据库：
指采用了关系模型来组织数据的数据库，关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。
关系型数据库的优点：
容易理解：二维表结构是非常贴近逻辑世界的一个概念，关系模型相对网状、层次等其他模型来说更容易理解。
使用方便：通用的SQL语言使得操作关系型数据库非常方便
易于维护：丰富的完整性（实体完整性、参照完整性和用户定义的完整性）大大减低了数据冗余和数据不一致的概率。
十大主流的关系型数据库：
Oracle、Microsoft SQL Server、MySQL、PostgreSQL、DB2、Microsoft Access、SQLite、Teradata、MariaDB、SAP
非关系型数据库：
指非关系型的，分布式的，且一般不保证遵循ACID原则的数据存储系统。
非关系型数据库的优点：
用户可以根据需要添加自己需要的字段，为了获取用户不同的信息，仅需要根据id取出相应的value值就能完成查询。

@select语句查询：根据业务需求写查询语句？

@Oracle数据库中单引号和双引号的区别？
双引号“ ”使用场合：
表示其内部的字符串严格区分大小写
用于特殊字符或关键字
不受标识符规则限制
会被当成一个列来处理
当出现在to_char的格式字符串中时，双引号有特殊作用，就是将非法的格式符包装起来。
单引号’ ’ 使用场合：
表示字符串常量
字符串中的双引号仅仅当做一个字符串处理，可以在单引号中使用双引号
如果字符串常量中包含了单引号，那么需要使用两个单引号表示一个单引号常量

@查询语句中对于null值的处理？运算表达式中对于null值的处理？运算表达式中MySQL和Oracle不一样
查询语句：
不加关照的情况下，我们可以把null值假想为所有内容中值最大的，因此，升序排序后null值在最后，倒序排序后null值在最前。
特殊关照的情况下，当指定“null first”时，无论是升序排序还是倒序排序，null值都会排列在最前面。当指定“null last”时，无论升序排序还是倒序排序，null值都会排列在最后面。
运算表达式：
Oracle的运算表达式中，如果表达式的某个值为null，则整个表达式的结果就会为null。使用nvl();
SQL中，null值与其他任何值相比较都是为false。应该使用 is null

@distinct关键字的用法
distinct关键字过滤掉多余的重复记录只保留一条，返回不重复记录的条数。
作用于单列：select distinct name from emp；
作用于多列：select distinct name , id from emp;
COUNT统计：select count(distinct name) from emp;
distinct必须放在开头
distinct语句中select显示的字段只能是distinct指定的字段，其他指端是不可能出现的。

@查询结果如何排序？ASC/DESC 多字段排序
select ename , sal from emp order by sal ASC/DESC;
ASC升序，默认为升序排列。DESC降序
select ename , deptno , hiredate from emp order by deptno , hiredate ;

@MySQL和Oracle查询结果中字段拼接的方式？
MySQL：使用concat函数进行拼接 concat（字段1，字段2…）
Oracle：使用 || 进行拼接

@数据库常用的函数：字符串函数、数值函数、日期函数：分别列举MySQL和Oracle三者之间的转换函数
Oracle:
日期转换为字符串：to_char(syadate , ‘yyyy-mm-dd hh:mi:ss’)
字符串转换为日期：to_date(‘19980108’ , ‘yyyy-mm-dd’)
日期转换为数字类型：to_num(to_char(sysdate , ‘yyyy’))
数字转换为字符函数：to_char(number , ’ $99,999.999’)
字符转换为数字函数：to_number(’$1,000’ , ‘$9999’)
MySQL：
日期转换为字符串：select date_format(now() , ‘%Y-%m-%d’)
字符串转换为日期：select str_to_date(‘1998-01-08’ , ‘%Y-%m-%d %H’)

@Oracle的通用函数：nvl（重点掌握）、nvl2、case-when、decode函数
NVL函数：
nvl(expr1 , expr2);如果Oracle第一个参数为空，那么就显示第二个参数的值。null值参与运算结果为空，所以使用nvl函数来解决问题。
NVL2函数：
nvl(E1 , E2 , E3 ) 如果E1表达式为null，则函数返回E3的值，否则返回E2的值。
CASE-WHEN函数：

case sex
when '1' then '男'
when '2' then '女'
else '其他' end;
​
case 
when sex='1' then '男'
when sex='2' then '女'
else '其他' end;

DECODE函数：
使用decode判断字符串是否一样。可以用来比较大小，进行数据分段

decode(value , if1 , then1 , if2 , then2 ,..., else);   
select empno , decode(empno , 7369 , 'smith' , '7499' , 'allen' , 'unknow');

@什么是外连接？有几种？
左外连接、右外连接、全外连接。笛卡尔连接也属于外连接（不常用）
左外连接：左边的表不加限制
右外连接：右边的表不加限制
全外连接：左右两张表都不加限制

@什么是等值连接？常用的等值连接有哪几种？
等值连接：等值连接是关系运算，连接运算的一种常用的连接方式。是条件连接在连接运算符为’ = '时的一个特例。根据表A和表B相同的值进行连接，相同的值并不一定在相同的字段。
where等值连接、join-on等值连接、自然等值连接、using等值连接
等值连接与自然连接的区别：
等值连接必须要有等值的条件，当条件不同时连接的结果也不相同。两个关系可以没有明确的属性列。
自然连接必须要有相同的属性列才能进行，即等值连接之后要取出相同的属性列。

@什么是不等值链接？举例说明

@什么是分组查询？分组函数
分组查询：
按照一定的规则进行分组，分组以后数据会聚合。分组的关键字是：group by 。将查询结果按一个或多个进行分组，字段值相同的为一组。
分组函数：count、sum、avg、max、min… 注意：分组查询函数会自动过滤null值，可以使用nvl进行预处理。

@如何进行分组查询？group by如何使用？什么是having语句，如何使用？
select t.stuid , t.sex from t_student t group by sex ; //按照性别进行分组
where使用在分组查询之前，where后面不能使用聚合函数，where是在分组前对数据进行过滤的。
having是分组之后再过滤，having紧跟group by，有having一定有group by ， 但是有group by 不一定有having。
执行顺序：where > group by > having > order by

@分组查询需要注意的语法点：分组查询中select部分显示的字段，要么是分组函数，要么是出现在group by字段之后的字段。

select max(adminid),adminsex from tbadmininfo group by adminsex;

@什么是子查询？exists关键字如何使用？
子查询：
子查询是一种常用的计算机Select-SQL语言中嵌套查询下层的程序模块。当一个查询是另一个查询的条件时，称之为子查询。在一条select语句中嵌入了另外一条select语句，被嵌入的语句称之为子查询。
exist关键字：
用于检查一个子查询是否至少返回一行数据（检测行的存在），返回值为true或false。
将外查询表的每一行，带入内查询作为检验，如果内查询返回的结果为非空值，则exist子句返回true，这一行可作为外查询的结果航，否则不能作为结果。
insert into table1(id , stuname) values(‘03’ , ‘grade 4’ ) where not exists (select * from table1 where id = ‘03’);
exists与in的区别：
in 引导的子句还能返回一个字段，exists子句可以有多个字段
in适合于外表大而内表小的情况，exists适合于外表小而內表大的情况。

@什么是多行子查询和单行子查询？
多行子查询：any、all、in以及>、=、<组合结果返回多行。
单行子查询：=、>、>=、<、<=、<>结果只返回一行

@rowid和rownum伪列对象的异同点？
rowid伪列：
是物理存在的，rowid是一种数据类型，它使用基于64位编码的18个字符来唯一标识一条记录物理位置的id。类似于java中一个对象的哈希码。rowid虽然可以在表中查询，但是其值并未存储在表中，所以不自持增删改操作。
rownum伪列：
是Oracle首先进行查询获取到结果集之后加上去的一个伪列，这个伪列对符合条件的结果添加一个从1开始的序列号。

@常用的集合操作有哪些？
集合运算是一种二目运算，一共有四种运算符：并、差、交、笛卡尔积 [ UNION | UNIONALL | INTERSECT | MINUS ]
UNION并集：返回若干个查询结果，但是重复的不显示。

select * from dept
union
select * from dept where deptno = 10;

UNIONALL并集：返回的若干个查询结果中，重复的也会显示
MINUS差集：返回若干结果中不同的部分

select * from dept
minus
select * from dept where deptno = 10;

INTERSECT交集：显示查询结果中相同的部分

select * from dept
intersect
select * from dept where deptno = 10;

@Oracle和MySQL的分页查询如何实现？
Oracle：集合操作 rowid伪对象+子查询
MySQL：limit关键字

@delete与truncate的异同点

drop table_name;
truncate table_name;
delete table_name [where column_name = value ]

drop：删除内容和定义，释放空间。把整个表都会删掉。
truncate：删除内容、释放空间都是不删除定义。只是清空表数据。
delete：删除内容不删除定义，不释放空间。
相同：都是用来做删除数据的
不同点：
truncate删除数据，都是不备份，删除后无法回滚。delete会备份，可以回滚。
truncate删除非常快，删除之后，自增id从0开始统计。delete删除慢，自增id继续累加。
truncate是DDL语句，相当于重新定义一张新表，把原先的表丢弃了，所以执行速度快。delete是DML语句，逐条删除数据，所以执行速度较慢。
truncate不写服务器log，delete写服务器log
truncate不激活trigger触发器，但是会重置identify，相当于自增列会被设置为初始值，由重新从1开始记录。delete删除以后，identify依旧会接着被删除的最近的那一条记录id加一后进行记录。

@DML是受事务管理的
DML是数据操纵语言。适用范围：对数据库进行一些简单操作，如insert、delete、update、select等。
DDL是数据定义语言。适用范围：对数据库中的某些对象（database、table）进行管理，如create、alter、drop等。

@什么是事务？事务的特点？ACID
事务：要么全部完成，要么全不完成。是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作。这些操作作为一个整体向系统提交，要么都执行，要么都不执行。事务是一组不可再分割的操作集合。
特点：原子性、一致性、隔离性、持久性
原子性(Atomicity)：事务是数据库的逻辑工作单位。要么都做，要么都不做。
一致性(Consistency)：事务执行的结果必须是使数据库从一种正确状态转换成另一种正确状态。
隔离性(Isolation)：在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务，事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务100%隔离，需要牺牲速度。
持久性(Durability)：事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

@@事务的隔离级别？常用的有4个隔离级别，每个隔离级别可能产生的问题？
顺序读是安全的
读取未提交(Read Uncommitted)—脏读。最低级别，任何情况都无法保证。
读取提交内容(Read Committed)—不可重复读。可避免脏读的发生。（默认隔离级别，但不是MySQL默认的）
可重读(Repeatable Read)—幻读。可避免脏读，不可重复度的发生。
可串行化(Serializable)—可避免脏读、不可重复度。幻读的发生。存在超时现象和锁竞争

脏读：某个事物已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务读物的数据就会是不正确的。
不可重复读：在一个书屋的两次查询之中数据不一致，这坑是两次查询过程中插入了一个事务更新的原有的数据。
幻读：在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有激烈数据是他先前没有的。

@常用的约束有哪些？
非空约束（NOT NULL）、唯一性约束（UNIQUE）、主键约束（PRIMARY KEY）、外键约束（FOREIGN KEY）、自定义check约束。

---添加主键约束
alter table user add constraint PK_userID primary key (userID);

---添加唯一约束
alter table user add constraint UQ_IDNumber unique(userIDNumber);

---添加默认约束
alter table user add constraint DF_userAddress default('地址不详') for userAddress;

---添加检查约束
lter table user add constraint CK_userAge check (userAge between 0 and 100);
lter table user add constraint CK_userSex check (userSex='男' or userSex='女')

---添加外键约束  主表user和从表userOrder建立关系，关联字段userID
lter table userOrder add constraint FK_userID_userID foreign key(userID) references user(userID)

@了解权限、角色，如何创建Oracle用户？如何赋予权限和剥夺权限？权限的传递性？
权限分类：
系统权限：系统规定用户使用数据库的权限
实体权限：某种权限用户对其他用户的表或试图的存取权限。
创建用户：

---创建用户  create user 用户名 identified by 密码
create user user01 identified by oracle;

---授予权限
SQL>grant connect ,resource ,dba to user01;

系统权限命令：
---系统权限授予：
SQL>grant connect,resource to user01;

---系统权限传递
SQL>grant connect,resource to user01 with admin option;

---系统权限回收
SQL>Revoke connect,resource from user01;

系统权限无级联，即A授予B，B授予C，如果A收回对B的权限，C的权限不影响。系统权限可以跨用户回收，即A可以直接回收C用户的权限。

实体权限命令：
实体权限分类：select、update、insert、alter、index、delete，all(所有权限)。execute(执行存储过程权限)

---为用户授予权限
SQL>grant select ,update,insert on product to user01
SQL>grant all on product to user01

---将表的操作权限授予全体用户
SQL>grant all on product to public;

---实体权限传递
SQL>grant select ,update on product to user with grant option;

---实体权限回收
SQL>Revoke select ,update on product from user01;

如果取消某个用户的对象权限，那么对于这个用户使用with grant option 授予权限的用户来说，同样还会取消这些用户的相同权限，也就是说取消授权时级联的权限。

@什么是视图？如何创建视图？视图和物理表之间有什么差异性？
视图：
视图是数据库用于展示数据的一种逻辑结构，视图是一种缓存，将查询结果集缓存起来作为一个罗技表使用，使用方式和物理表一样。
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据，但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
创建视图：

 create [or replace] [force|noforce] view 视图名 as 子查询
 [with check option [constraint 约束名]]
[with read only]

差异性：
视图时已经编译好的SQL语句，而表不是
视图没有实际的物理记录，而表有
视图是窗口，表是内容
表占用物理空间而视图不占用物理空间，视图只是逻辑概念的存在。表可以随时的修改，但是视图只有创建的时候可以修改。
表是内模式，视图是外模式。表属于全局模式的表，属于实表，视图属于局部模式的表，属于虚表。
视图的删除和建立只影响属性本身，不影响对应的基本表。

@什么是序列？
序列是按照设定的步长从初始值自增的一个计数器。
计数器每次都获取下一个值
计数器只能向前取值：nextval、currval
计数器第一次使用时应先调用nextval获取下一个值，然后再使用currval获取当前值。
一般一个序列是为一个表中的一个字段提供自增数值服务
不建议多个字段或者多个表公用一个序列

@什么是索引？索引的作用？索引在什么情况下可以使用？什么情况下不建议使用？
索引：
索引是数据库的一个对象，用于提高查询速度，类似于书籍的目录。数据库会给主键或者唯一键自动创建索引。
语法：
create index 索引名字 on 表名（需要创建索引的字段列表）
索引的作用：
保证数据的准确性。唯一的索引值对应着唯一的数据
加快检索速度。索引可以极大加快检索速度
提高系统性能。索引可以有效提高系统性能
需要创建索引的情况：
列中数据值分布范围很广
列中包含大量空置
列经常在where子句或链接条件中出现
表经常被访问而且数据量很大，访问的数据大概占数据总量的2%-4%
不需要创建索引的情况：
表数据量很小
列不经常作为连接条件或出现在where子句中
查询的数据大于2%到4%
表经常更新()

@什么是存储过程？什么是函数？有什么异同点？参数的类型有哪些？
存储过程：
存储过程是PL编程中一段可以独立执行的程序代码，可以传递参数。
函数：
函数是PL编程中的可以独立执行的一段程序代码，可以传递参数。
区别：
存储过程没有返回值，函数可以有返回值。
参数的类型：
out：只能从里往外传值
in：只能从外往里传值
in out：可以从里往外也可以从外往里传值。