第一部分 Oracle数据库
1. Oracle安装与卸载
1. 卸载
①打开**注册表 (regedit) -> HKEY_LOCAL_MACHINE -> SYSTEM -> ControlSet001 -> Services -> 把所有的Oracle开头**的全部删除,然后重启
②安装目录app删除
③删除Program Files \ Oracle
2.安装
安装步骤如下：
https://pan.baidu.com/s/1CkI-4OlM13eKerSCYrJwBg
提取码: d6jn
安装成功之后 运行 sqlplus 查看是否装成功
当忘记密码时，①sqlplus /nolog ** 进入不需要密码的方式 ②conn /as sysdba** 连接到超级管理员 ③**alter user [username] identified by [password] **来更改密码
3.Oracle 密码过期修改
1、进入sqlplus模式sqlplus / as sysdba;
2、查看用户密码的有效期设置(一般默认的配置文件是DEFAULT)
SELECT * FROM dba_profiles
WHERE profile=‘DEFAULT’
AND resource_name=‘PASSWORD_LIFE_TIME’;
3、将密码有效期由默认的180天修改成“无限制”，修改之后不需要重启动数据库，会立即生效
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED ;
4、帐户再改一次密码
alter user 用户名 identified by 原密码;
5、使用修改后的用户登录，如果报“ORA-28000:用户已被锁”，解锁
alter user db_user account unlock;
commit;
4.常见错误及解决办法
- edit无法使用
右键以管理员身份运行**D:\app\eleven\product\11.2.0\dbhome_1\BIN\sqlplus.exe**
- ora -01033
ora-01033oracle initialization or shutdown in progress；
1.打开cmd中输入sqlplus /NOLOG
2.SQL>connect sys/change_on_install as sysdba
提示：已成功
3.SQL>shutdown normal
提示：数据库已经关闭 已经卸载数据库 ORACLE 例程已经关闭
4.SQL>startup mount
5.SQL>alter database open;
提示：有可能会出现以下错误
第1 行出现错误: ORA-01157: 无法标识/锁定数据文件6 - 请参阅DBWR 跟踪文件
ORA-01110: 数据文件6: ‘’’‘C:\oracle\oradata\oradb\TABLE_USER.ORA’’
这个提示文件部分根据每个人不同情况有点差别。
6.SQL>alter database datafile 6 offline drop;
7.重复使用第五第六步，直到出现“数据库已更改”的提示，然后如下图，
继续输入shutdown normal，startup mount就OK啦
如果还不行，就把Oracle的相关服务都重新启动、就可以正常登录。
- 忘记密码
1.运行 = sqlplus /nolog
2.conn /as sysdba
3.alter user system identified by etoak;
4.conn system/etoak;
- 协议适配器错误
sqlplus：协议适配器错误；navicat：无监听程序
请检查服务是否开启：
1.打开服务 ->services.msc = 服务
2.右键启动以下服务：
OracleOraDb11g_home1TNSListener
OracleServiceORCL
- 修改SQL语句
当SQl语句出错时，可以执行edit，在记事本里更改保存后，在Sqlplus里执行 / 就行了；
2.SQL语言介绍
1. SQL语言简介
SQL：结构化查询语言 (Structured Query Language)；包括DDL、DML、DQL、DCL、TCL
1.1 Oracle常用数据类型
- 字符型
| 数据类型 | 取值范围 | 含义 |
|---|---|---|
| varchar2(20) | 0-4000字节 | 可变长度的，会根据传入的字符串的长度进行添加 |
| char(20) | 0-2000字节 | 定长类型的 |
| long |
- 数值型
| 数据类型 | 取值范围 | 含义 |
|---|---|---|
| number() | 不常用 | |
| number(5) | 0-99999 | 五位数 |
| number(5,2) | 0.01-999.99 | 总共五位有两位小数 |
| numer(5,-2) | **不常用，**把后两位变成00，如输入12345则变成12300 |
3.日期型
| 数据类型 | 含义 |
|---|---|
| date | 日期类型(时间戳) |
2. DDL
**DDL(Data Definetion Language)**数据定义语言，create(新建)、alter(修改)、drop(删除)、truncate(截断)
- create
--创建新用户
create user ET identified by dream;
--创建表
create table taecher(
t_name varchar2(20),
t_sal number(8,2),
t_birthday date
);
--查看当前表结构
desc teacher;
- alter
--修改用户的状态
alter user scott account lock;
alter user scott account unlock;
--修改用户密码
alter user scott identified by dream;
--修改表名
alter table teacher rename to tea;
--修改字段名
alter table tea rename column t_sal to t_salary;
--增加字段
alter table tea add aihao varchar2(20);
--修改数据类型
alter table tea modify aihao varchar2(50);
--删除字段
alter table tea drop column aihao;
- drop
--删除用户
drop user ET;
--删除表结构和表数据
drop table tea;
--删除表结构和表空间
drop table tea parse;
- truncate
--删除表数据和表空间
truncate table tea;
drop 和 truncate 的区别
3. DML
**DML(Data Manipulation Language)**数据操纵语言
insert (插入)、update(修改)、delete(删除)
- insert
--当要插入所有数据时可以不用写，每个字段的名称
insert into tea values('admin',123456.12 sysdate);
--按照顺序插入
insert into tea(t_name,t_sal) values('zhangsan',123456);
- update
--更新数据
update tea set t_sal = t_salary + 1000 where name = 'zhaoxin';
- delete
--删除所有数据
deledt from tea;
4. DQL
**DQL(Data Query Language)**数据查询语言,select(查询)
- select
--查询用户的所有表
select table_names from user_tables;
--查询表内所有数据
select * from tea;
--限制条件进行查询
select * from tea where t_name = 'zhaoxin';
--用select查询的方式更新数据
select ename,sal * 1.3 sal from emp where ename like 'S%' or ename like 'W%';
--第二个sal是别名为了sal * 1.3 好看
- like
--模糊查询
-- % 代表任意位上的任意字符
-- _ 代表一位上的任意字符
select * from emp where tname like '周%';
- escape
--逃离符 通过制定一个字符位进行逃离，来保证like后的字符看作是普通字符
select tname,tsal from tea where name like 'ET,_%' escape ',';
5. DCL
**DCL(Data Control Language)**数据控制语言
grant(授权)、revoke(取消授权)
- grant
--切换用户
conn system/etoak;
--给用户授权
grant dba to scott;
2.revoke
--取消授权
revoke dba from scott;
6. TCL
**TCL(Translation Control Language)**事物控制语言
commit(提交)、rollback(回滚)、savepoint(保存还原点)，只会影响DML操作。
在一些列操作中有多个步骤，只有所有的步骤都成功才算操作成功，如果之间有一个环节失败，那么整个操作都算失败。
commit：提交 将数据持久化到数据库中
rollback：回滚 将事务中的操作回滚到第一步操作之前，就当什么 都没发生
savepoint：还原点 可以回退到指定的位置显示提交：在事务中手动commit
隐式提交：在事务中，如果正在执行DML操作，突然做了一个DDL 操作，数据库会自动在DDL操作之前隐式的做一个commit操作当发生以下时间时，事务将结束
1.利用commit/rollback进行事务提交和回滚
2.执行DDL语句时，事务将自动提交
3.如果使用sqlplus时，正常退出事务会自动提交，非正常退出事务 回滚Oracle在sqlplus中开始事务
set autocommit off；关闭自动提交
set autocommit on；开启自动提交
| 事务特性 | 解释 |
|---|---|
| 原子性 | 事务不可再分 |
| 一致性 | 数据类型保持一致 |
| 持久性 | 事务能将数据持久化到数据库中 |
| 隔离性 | 多个事务之间会产生一些隔离性问题 |
6.1隔离性问题⚠️
- 脏读😄
事务B读取到了事务A中未提交的数据，然后事务A队刚才的操作进行了回滚操作，那么事务B读取到的数据就无效了
- 不可重复读
事务A队表内数据提交后，事务B再次读取数据，发现个之前读取的数据不一样。
- 幻读
事务A对表新增数据并提交，事务B再次读取这张表，发现数据多了，就像发生了幻觉一样。
相同的查询条件，在别的事务添加或删除之后，再次查询不一样
6.2隔离级别
数据库事务的隔离级别一共有4个，由低到高依次为**Read uncommitted,Read committed,Repeatable read,Serializable,**这四个隔离级别可以逐个解决脏读，不可查重复读，幻读这几个问题。
更改数据库的隔离级别必须在开启事务的第一句话来更改
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted(未提交度) | 会 | 会 | 会 |
| Read committed(提交读)[默认级别] | 不会 | 会 | 会 |
| Repeatable read(可重复读)[mysql默认] | 不会 | 不会 | 会 |
| Serializable(序列化读) | 不会 | 不会 | 不会 |
1.ISOLATION_READ_UNCOMMITTED:【很少应用，效果很差，效 率也没高哪去】
这个是事务最低的隔离级别，它允许另外一个事务可以看到这个事 务未提交的数据。
2.ISOLATION_READ_COMMITTED:【Oracle默认的隔离级别 大多 数数据库默认】
保证一个事务修改的数据只有在提交以后别的事务才能读取。
3.ISOLATION_REPEATABLE_READ：【Mysql默认的隔离级别】
4.ISOLATION_SERIALIZABLE：【根治所有问题 但是牺牲效率】
花费高代价但是最可靠的事务隔离级别，事务被处理为顺序执行
7. 锁
- 锁可以防止事务之间的破坏性交互，约束了最大程度的并发性，数据的完整性
| 锁的分类 | 介绍 |
|---|---|
| 排它锁（X锁） | 防止资源共享，也就是当一个事务正在操作数据时，其他事务不可以操作这个事务的数据。 |
| 共享锁（S锁） | 被锁住的数据只能被读取，但是不能修改。 |
| 锁的类型 | 介绍 |
|---|---|
| DML锁 | 也就数据锁，用于保护数据，事务在最开始时添加，通过commit或者rollback释放。 |
| DDL锁 | 可以保护数据对象的结构。Oracle自动的施加的释放。 |
| 内部闩锁 | 保护数据库的内部结构，完全自动调用。 |
| 行级锁 | 也叫事务锁，防止数据被同时多个事务进行修改，直到commit或者rollback。 |
| 表级索 | 防止在修改数据的时候，表结构发生变化。 |
select name,salary from student for update;
--会对student表进行加锁，此时只允许当前session对已经存在的数据进行更新，其他session仍可以进行insert操作。
锁等待和死锁
- 锁等待也叫锁冲突，锁等待会严重影响数据库的性能和日常工作。
死锁，也就锁等待的一种，但是死锁会让事务一直处于锁等待的状态。
-- 查看是否有死锁
select sid,serial#,username from v$session where sid in (select blocking_session from v$session);
-- 查看死锁的语句
select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in(select session_id from v$locked_object));
/*
解决死锁：
https://localhost:1158/em
性能-->其他监视链接-->实例锁
*/
3. 函数
--运算符 + - * / mod(12,5)
select mod(12,5) for dual; -- 取模2
--与空判断
--是空 is null
--非空 is not null
1. 多行函数
也叫组函数，聚簇函数，聚组函数
| 函数 | 含义 |
|---|---|
| max() | 求最大值 |
| min() | 求最小值 |
| avg() | 求平均数 |
| sum() | 求和 |
| count() | 求记录数 |
--求最大值
select max(salary) from tea;
--求最小值
select min(salary) from tea;
--求平均值
select avg(salary) from tea;
--求和
select sum(salary) from tea;
--求记录数
select count(*) from tea;
2 .单行函数
| 函数 | 含义 |
|---|---|
| ceil() | 向上取整 |
| floor() | 向下取整 |
| round() | 四舍五入 |
| trunc() | 截断 |
| sign() | 求符号位 |
| abs() | 求绝对值 |
| power() | 求a的b次方 |
| sqrt() | 求正平方根 |
--ceil()向上取整,返回大于等于X的最小整数
select ceil(12.3) from dual; --13
--floor()向下取整，返回小于等于x的最小整数
select floor(12.3) from dual; --12
--round()四舍五入
round(12.3) --12
round(3.1415926,4) --3.1416
--sign()求符号位整数1负数-1
select sign(666) from dual; --1
--abs()求绝对值
select abs(-666) from dual; --666
--power(a,b)求a的b的次方
select power(2,3) from dual;--8
--sqrt()求正平方根
select sqrt(16) from dual;--4
3.日期函数
日期可以加减运算 (整数)，以天为单位
| 日期格式 | 含义 |
|---|---|
| yyyy | 年 |
| mm | 月 |
| dd | 日 |
| hh24 | 24小时制 |
| mi | 分钟 |
| ss | 秒 |
其中 ddd 表示一年中的第几天 dd 表示一月中的第几天 d 表示一周中的第几天。xff 表示毫秒 ff3 表示毫秒数保留三位
| 函数 | 含义 |
|---|---|
| add_months() | 在给定日期上添加多个月 |
| months_between() | 两个日子之间有多少个月 |
| next_day() | 查找出下一个周几 |
| last_day() | 计算给定日期是所在月份的第几天 |
select add_months(sysdate,3) from dual;
select months_between(
sysdate,to_date('20200126121212','yyyy-mm-dd hh24:mi:ss')) from dual;
select next_day(sysdate,'星期三') from dual;
select last_day(sysdate) from dual;
4. 转换函数
| 函数 | 含义 |
|---|---|
| to_number() | 将一个字符类型的数值转换成数值类型 |
| to_char() | 1.将数值类型转换成字符串 |
| to_char() | 2.常用在货币单位格式化字符串 |
| to_char() | 3.日期转换to_char(日期,‘yyyy-mm-dd’) |
| to_date() | 字符类型日期转换成日期格式 |
--to_number
select name,email from student where to_number(email) = 1;
--to_char() 1.将数值类型转换成字符串
select name,salary from student where to_char(salary) = '5400';
--to_char() 2.常用在货币单位格式化字符串
select to_char(123456789,'999,999,999,999.99') from dual;
--to_char() 3.日期转换to_char(日期,'yyyy-mm-dd')
select to_char(sysdate,'yyyy-mm-dd') from dual;
--to_date(字符类型的日期，日期格式)
select to_date('20200202202020','yyyy-mm-dd hh24:mi:ss') from dual;
--日期期间的查询
select name,birthday from student where birthday between to_date(
'20200201','yyyy-mm-dd') and to_date('20200205','yyyy-mm-dd');
5. 字符函数
| 函数 | 作用 |
|---|---|
| lower() | 转换成小写 |
| upper() | 转换成大写 |
| initcap() | 首字母大写 |
| length() | 求长度 |
| substr(str,int i,int j) | 从str的第i个字符截取长度为 j 的字符喜欢 |
| replace(str,a1,a2) | 把 str 中的 a1 内容替换成 a2，a2不写则替换成空 |
| instr(a1,a2,a3,a4) | a2:要找的字符串;a3:从哪开始找;a4第几次出现的a3,a4默认为1 |
| concat(str1,str2) | 拼接字符串,用||也可以 |
| lpad(a1,a2,a3) | 左侧补全，a2补全到多少位，a3用什么补全 |
| rpad(a1,a2,a3) | 右侧补全 |
| trim() | 去除空格 |
| trim(a1 from a2) | 从a2两侧去除a1 |
--转换大写，转换小写，首字母大写，字符串长度
ename,lower(ename),upper(ename),initcap(ename),length(ename) from emp;
--substr(a1,a2,a3)：截取字符串
--a1：原字符串
--a2：从哪个位置开始截取
--a3：截取长度 默认截取到最后
select substr('woshizhizhuxia',3) from dual;
select substr('woshizhizhuxia',3,9) from dual;
--replace(a1,a2,a3)：替换
--a1：原字符串
--a2：被替换的字符串
--a3：替换的字符串
select replace('woshizhizhuxia','z') from dual;
select replace('woshizhizhuxia','z','*') from dual;
--instr(a1,a2,a3,a4)：索引字符串
--a1：原字符串
--a2：希望找到的字符
--a3：从哪开始找 默认是1
--a4：第几次出现 默认是1
select instr('woshizhizhuxia','z',5,2) from dual;
select instr('woshizhizhuxia','z',5) from dual;
select instr('woshizhizhuxia','z') from dual;
--concat(a1,a2)：拼接字符串
select concat(ename,sal) from emp;
--拼接字符串野路子 ||
select '123' || '456' from dual;
----------以下的知道就行了--------------
--lpad(a1,a2,a3)：左侧补全
--rpad(a1,a2,a3)：右侧补全
--a1：原字符串
--a2：补全到多少位
--a3：用什么来补全
select lpad(sal,11,'138') from emp;
--trim()：去除两侧空格
--trim(a1 from a2)：默认把a2的两侧去除a1
select trim('a' from 'aaaababaaa') from dual;
--ltrim()：左侧去除
--rtrim()：右侧去除
select ltrim(' abc') from dual;
select ltrim(' abc',' ') from dual;
6.通用函数
| 函数 | 含义 |
|---|---|
| nvl(字段，是空展示什么) | 空值处理 |
| nvl2(字段，不是空展示什么，是空展示什么) | 空值处理er |
select ename,comm,nvl(comm,0) from emp;
select ename,comm,nvl2(comm,comm,0) from emp;
7. 排序和分组
- order by
- DESC 降序，ASC默认的升序
- 可以通过多个字段进行排序
select ename,sal from emp order by sal DESC;
select ename,sal from emp order by sal ASC;
- group by
- 根据在某一列或多个列上相同的值划分为一个组，该表就要被分为多个组
- 如果以字段A分组，那么只能查询字段A或者以组函数的形式统计其他字段
- 当想要加条件的时候要用 having
select deptno,count(ename) from emp group by deptno;
select deptno,ename from emp group by deptno,ename;
select deptno,count(ename)
from emp
group by deptno
having count(ename) >= 5;
- distinct
- 去重，支持单列和多列的去重
select distinct deptono from emp;
select distinct deptno,job from emp;
- 查询关键字的优先级
select 列名 -- 优先级高于order by
from 表名 -- 优先级最高
连表查询
where 条件 -- 优先级次高
group by 分组 -- 优先级次于where
having 条件 -- 优先级一定在group by之后
order by 排序 -- 优先级最低
8.扩展 decode()
decode(c1,c2,c3...cx,cx+1)
-- c1 :拿来比较的条件
--从第二个参数开始，每两个参数看作是一组，拿每一组的前一个参数和 c1 做对比，如果相同则返回后一个参数
--如果参数的个数是奇数个且最终判断没有相同的值，则返回空
--如果参数的个数是偶数个，并且最终判断没有相同的值，则返回最后一个参数的值
--例题
--如果部门为10，那么给员工涨薪200
--如果部门为20，那么给员工减薪200
-- 其他部门涨薪500
select ename,deptno,sal,
decode(deptno,10,sal+200,20,sal-200,sal+500) a
from emp order by deptno;
条件取值语句
(case -- 条件
when -- 如果条件成立
then -- 则返回
else -- 默认值
end) -- 结束
select ename,deptno,sal,
(case deptno when 10 then sal + 200
when 20 then sal - 200
end) a
from emp order by deptno;
select ename,deptno,sal,
(case deptno when 10 then sal + 200
when 20 then sal - 200
else sal + 500
end) a
from emp order by deptno;
4.约束
1.主键约束 primary key[常用]
-
主键列在一个表中能够唯一定位一条数据列，非空且唯一
-
一张表只能有一个主键，联合主键除外
--constraint 约束名 可以不写 create table test( id number(5) constraint zj primary key, name varchar2(20));
2.外键约束foreign key ： references[常用]*
- 在**子表中如果有一列引用了母表中的主键列，那么这个列在字表中被称为外键**
- 一张表可以有多个外键
3.非空约束、唯一约束、检查约束
非空约束 not null 检查约束check 一般在前端就完成了检查不需要传到后台数据库
唯一约束 unique
4.约束的使用
--建表以后，如何添加约束
alter table test add constraint pk_zj primary key (id);
--查看当前用户下有哪些约束：
select constraint_name,constraint_type from user_constraints;
--如何删除约束：
alter table test drop constraint pk_zj;
--数据库中约束越多，表越健壮，效率越低
练习表
create table teacher(
id number(5) primary key,
name varchar2(20),
salary number(6) check(salary <= 20000));
insert into teacher values(1,'zhoulaoshi',12222);
insert into teacher values(2,'zhanglaoshi',13333);
insert into teacher values(3,'lulaoshi',14444);
insert into teacher values(4,'laoda',666);
create table class(
id number(5) primary key,
name varchar2(20),
tid number(5) references teacher(id));
insert into class values(1,'ET1912',2);
insert into class values(2,'ET1911',1);
insert into class values(3,'ET1910',3);
insert into class values(4,'ET1909',4);
create table school(
id number(5) primary key,
name varchar2(20) not null unique,
phone varchar2(20) check(length(phone) = 11));
insert into school values(1,'山大','13356785678');
insert into school values(2,'山科','13378907890');
insert into school values(3,'临大','15567896789');
insert into school values(4,'北大','15523456789');
create table student(
id number(5) primary key,
name varchar2(20) unique,
salary number(5) check(salary between 5000 and 10000),
email varchar2(50) unique,
birthday date,
sid number(5) references school(id),
cid number(5) references class(id));
insert into student values(1,'葫芦娃',5000,'hlw@163.com',sysdate,2,3);
insert into student values(2,'蜘蛛侠',6000,'zzx@126.com',to_date('19951213121212','yyyy-mm-dd hh24:mi:ss'),1,2);
insert into student values(3,'哪咤',7000,'nz@ali.com',to_date('19961213121212','yyyy-mm-dd hh24:mi:ss'),2,1);
insert into student values(4,'白龙',8000,'bl@yahoo.com',to_date('19981213121212','yyyy-mm-dd hh24:mi:ss'),4,4);
5. 连表查询
1. 自查询
--自查询效率最低，不建议使用
select empno,ename,dname from emp,dept,salno where emp.deptno = dept.deptno and job = 'CLERK';
2. 子查询
子查询 = 嵌套查询 = 某些条件是通过查询得到的
select 子查询 from 子查询 where 子查询
--谁和葫芦娃一个学校的？
--1）葫芦娃是哪学校的？
select sid from student where name = '葫芦娃';
--2）谁还是葫芦娃这个学校的？
select name,sid from student where sid = 2;
select name,sid from student where sid = (select sid from student where name = '葫芦娃') and name != '葫芦娃';
--学生表的学生都上哪些学校？
select name,(select name from school where school.id = student.sid) schoolname from student;
--2查询所有工种为CLERK的员工的工号、员工名和部门名。
select empno,ename,(select dname from dept where dept.deptno = emp.deptno) dname from emp where job = 'CLERK';
3.内连接
- inner join on inner通常不写，通过关联关系能够得到部分数据
select student.name,school.name
from student join school
on student.sid = school.id;
4.外连接
- outer join on : 不仅包含有关联关系的数据，还包含没有关联关系的数据
-
左外连接[left join on]
from 左表 left join 右表 on 关联关系
**以左表为主，**左表的所有数据都展示，关联不上的右表的数据以空展示左表关联上右表的数据 + 关联不上的左表的数据
select school.name,student.name from school left join student on student.sid = school.id; -
右外连接[right join on]
from 左表 right join 右表 on 关联关系
以右表为主，右表的所有数据都展示，关联不上的左表的数据以空展示
*：左表关联上右表的数据 + 关联不上的右表的数据select student.name,school.name from student right join school on student.sid = school.id; -
多表查询时的嵌套使用左右外连
多表查询时可能会用到多次左连接或右连接，此时应先将前边的当做一个整体在看后边的是左连接还是右连接
当使用多张表进行连表查询时，外键多的放在最上边
select school.name,student.name,teacher.name,class.name from student left join class on student.cid = class.id left join school on student.sid = school.id left join teacher on class.tid = teacher.id;以下两个用处不大
-
全外连接[full join on]**
两张表的数据都展示
from 左表 full join 右表 on 关联关系
*：左表关联上右表的数据 + 关联不上的左表的数据 + 关联不上的右表的数据select student.name,school.name from student full join school on student.sid = school.id; -
交叉连接[cross join]
两张表的数据一一对应，交叉形成最后结果，相当于笛卡尔积
select student.name,book.bname from student cross join book;什么时候使用多表连接？什么时候使用子查询？
- 如果查询的数据出现在多个表中，那么建议使用多表查询
- 如果不需要展示表A中的列，但是要用表A中的列作为条件，使用子查询
- 子查询中用到in some any all关键字的时候，可以转换为多表连接
6. 伪列和联合关键字
1. 伪列
- 假列，根本不存在的列，数据库中查询表的时候添加的列
1.rownum
- 行号，是Oracle根据插叙结果动态的编号，用来实现分页查询
- 有序的整数列，每多一条自动加1
- 不能和order by在同一个查询语句中，order by会打乱查询的顺序
- 不能使用表名.rownum的写法
- 如果rownum用在where之后，rownum >= 1 rownum<= 任意值
当想要查询从指定数到指定数的分页的时候需要给查询设置别名
例如
select rn,e from(
select rownum rn,ename e from emp) em
where em.rn between 5 and 10;
当rownum直接在where后边时，必须从1开始，否则会出现未指定列，原因是因为在分页的时候查出来的第一个的rownum为1，不符合where的条件，被筛选出去之后再次查找的rownum依旧为1
--1.实现第一页数据，编号1-10
select rownum,substr(content,1,10) || '...' from lyric where
rownum between 1 and 10;
select rownum,content from lyric where rownum between 1 and 10;
--2.实现第二页数据，编号11-20
select * from (select rownum rn,content from lyric where rownum <= 20) a where a.rn >= 11;
--3.实现第三页数据，编号21-30
select * from (select rownum rn,content from lyric where rownum <= 30) a where a.rn >= 21;
--4.实现带有排序效果的分页
select * from (select rownum rn,content from (select * from lyric order by content) where rownum <= 20) a where a.rn >= 11;
与java进行连接的时候用法，分页公式
在于前端页面进行连接的时候，在java中一般会定义两个参数
curpage代表当前页，pagesize代表每页显示多少行
则当前页开始数为： (curpage - 1) * pagesize + 1
当前页截止数为 ： curpage * pagesize
2.rowid
- 是映射数据库中的每一条数据物理地址的唯一标识
- 适用于删除完全重复的数据
- min(rowid) 表示先添加的数据 max(rowid) 表示后添加的数据
delete from lyric where rowid not in (select min(rowid) from lyric group by content);
例题 eg：
--需要添加day03/Sql/lyric.sql
--删除表中第5-10条数据
delete from lyric
where rowid in(
select rd from (
select rownum rn,rowid rd,substr(content,1,10)||'...' cont
from lyric) a
where a.rn between 5 and 10
);
2. 联合关键字
1. union[结果唯一]
--查询结果北大，临大，山大，山科
select name from school where name like '%大'
union
select name from school where name like '山%';
2. union all[结果不唯一]
--查询结果 北大，临大，山大，山大，山科
select name from school where name like '%大'
union all
select name from school where name like '山%';
3. intersect[求交集]
--查询结果山大
select * from school where name like '%大'
intersect
select * from school where name like '山%';
4.minus
- 从第一个查询结果集中减去第二个查询结果中重复的数据
--查询结果 临大，北大
select * from school where name like '%大'
minus
select * from school where name like '山%';
查询结果123 234
| 关键字 | 结果 |
|---|---|
| union | 1234 |
| union all | 123234 |
| intersect | 23 |
| minus | 1 |
5. in / not in
- in 表示符合查询结果中的任意一个值，not in 表示条件不能是查询结果中的任意一个值
--条件 in(值1，值2，...)
--相当于条件 = 值1 or 条件 = 值2 or ...
select * from school where id in (select sid from student);
--条件 not in (值1，值2...)
--相当于：条件 <> 值1 and 条件 <> 值2 and ...
select * from school where id not in (select sid from student);
6.some / any[不重要]
- in用在无符号的情况，some/any 用在有符号的情况下
- some 用在肯定范围内时效率较快，不知道确定范围时用any
- 与in 用法相同
select * from school where id = some (select sid from student);
select * from school where id < any (select sid from student);
7. all[不重要]
- 表示比所有值都大或者都小
select * from school where id > all (select sid from student);
其中 >any == >max; <any == <max;>all == >max;<all == <min
8.exists/not exists[不重要]
- 在某些情况下，in的效率较低，可以用exists 代替
select * from school where exists (select sid from student where student.sid = school.id);
select * from school where not exists (select sid from student where student.sid = school.id);
7.序列[seqence]
- 一个单独的数据对象，每多一条自动加一
- Oracle通过调用序列来实现主键自增
- 在一个新的会话中，必须调用下一个值才能查看当前值
- 序列调用下一个值，会作为下次调用的初始值
--创建序列：
create sequence seq_test -- 序列名
increment by 1 -- 一次增长1
start with 1 -- 从1开始
minvalue 1 -- 最小值
maxvalue 100 -- 最大值
cycle -- 循环 默认不循环
nocache; --不缓存 默认缓存20
--查看序列的下一个值：
select seq_test.nextval from dual;
--查看序列的当前值：
select seq_test.currval from dual;
-------------------------------------------
--1.消除延迟段创建特性
alter system set deferred_segment_creation = false;
--2.创建表
create table test2(id number(3),name varchar2(20));
--3.创建序列
create sequence seq_test2;
--4.新增数据
insert into test2 values(seq_test2.nextval,'橘子');
insert into test2 values(seq_test2.nextval,'苹果');
--5.查询数据
select * from test2;
--查看当前用户下有哪些序列：
select * from user_sequences;
--如何修改序列：
alter sequence seq_test increment by 50;
--如何删除序列：
drop sequence seq_test;
8.视图[view]
视图[view] ,相当于一张假表，用查询结果动态的生成的一张表。
视图是编译后将查询结果保存在数据库中，下次再次查询的时候不用编译，可以直接从视图中获取数据
--创建视图
--as后边的语句必须是可以独立执行的语句
create view v_name as
select student.name stuname,school.name
from student join school
on student.sid = school.id;
--查看视图
select * from v_name
--删除视图
drop view v_name
为什么要使用视图
- 节省编译时间，提高查询效率
- 屏蔽原表字段，避免没有权限的用户进行查看
- 简单视图的数据能够动态的来源于原表，复杂视图可以手动编译**(alter view 视图名 compile;)**
9.索引[index]
- 索引相当于目录，数据库会在具有唯一约束的列上自动添加唯一性索引
--创建索引
--create index 索引名 on 表名(列名)
create index ind_name on student(salary);
--删除索引
drop index ind_name;
--如何查看索引：
select table_name,index_name,uniqueness,status from user_indexes
where table_name = 'STUDENT';
1. 普通索引[normal]
--create index 索引名 on 表名(列名);
create index ind_name on student(salary);
2. 唯一性索引[unique]
--create unique index 索引名 on 表名(列名);
create unique index ind_cid on student(cid);
3. 位图索引[bitmap]
- 适合数据量比较大，但是基数比较小的，例如性别
--create bitmap index 索引名 on 表名(列名)
create bitmap index ind_sid on student(sid);
4.函数索引
--create index 索引名 on 表名(函数(列名));
create index ind_length on student(length(name));
5.创建索引的优缺点
- 能够更快的查询数据，有效的提高查询效率
- 数据量大，查询较多，增删改查少的列适合添加索引
- 增删改查数据的时候，数据库会浪费资源去维护索引
10存储过程[procedure]
- 在服务器端能够被一个或者多个程序调用的sql语句集
- 存储过程可以没有参数，如果没有参数的话则过程名之后不要出现括号
- 传入参数用in表示，传出参数用out表示
- 存储过程没有返回值，而是通过传出参数进行返回数据,可以多个传入参数，也可以有多个传出参数
- 创建存储过程：
create procedure 存储过程名(参数名 in 参数类型,参数名 out 参数类型)
as
变量名 变量类型 := 值;
begin
sql语句集;
end;
例题：
如果传入参数是1，则返回你好；如果传入参数是2，则返回再见
create procedure pro_hi(cr in number,cc out varchar)
as
begin
if cr = 1 then cc := '你好';
else if cr = 2 then cc := '再见';
end if;
end if;
end;
/
- 调用存储过程
--调用存储过程
declare 变量 变量类型 := 初始值;
begin
sql 语句集;
end;
--开启输出行：
set serveroutput on;
declare val varchar2(20) := '';
begin
pro_hi(1,val);
dbms_output.put_line(val);
pro_hi(2,val);
dbms_output.put_line(val);
end;
/
11 触发器[trigger]
- for each row 行级触发器
- Oracle通过触发器调用序列来实现主键自增
--创建触发器：
create or replace trigger 触发器名
before/after insert/delete/update on 表名
for each row
begin
sql语句集;
end;
练习
--1.创建表
create table test(id number(5) primary key,name varchar2(20))
--2.创建序列
create sequence seq_test;
--3.创建触发器
create or replace trigger tri_id
before insert on test
for each row
begin
select seq_test.nextval
into:new.id
from dual;
end;
/
--4.插入数据
insert into test (name) values('橘子');
insert into test (name) values('苹果');
--5.查询数据
select * from test;
12 SQL语句优化
- 建议少用 ***** 代替所有列名
- 用 exists 代替 in
- 多表连接时，尽量少用表的查询次数
- 删除全表数据的时候用truncate代替delete
- 合理利用索引，详见索引
- SQL语句尽量大写，Oracle会默认把小写转换成大写
- 在保证语句完整的情况下，多使用commit(begin…end…)
- 优化group by，将不需要的数据尽量在分组之前就过滤掉
- 连表查询的时候尽量使用标的别名，减少解析时间
- 表连接在where之前，where条件过滤顺序，能够更多的过滤数据的放在前面
13不重要的部分
1. 表空间[tablespace]
-
在一段内存中因为多数存储的是表，所以称为表空间
-
Oracle当中每个用户都有属于自己的表空间;[用户;普通用户;系统用户]
为什么要给普通用户创建属于自己的表空间？
项目中很多时候会遇到多个项目访问同一个数据库，多个用户在访问同一个数据库的时候会产生资源争用问题，给不同的用户指定不同的表空间，就可以解决资源争用问题。
--创建表空间：
create tablespace table_user
datafile 'D:\table_user.dbf' -- 文件位置
size 5M -- 初始大小
autoextend on next 5M -- 下一次拓展多少
maxsize 100M; -- 最大值
--创建用户指定默认的表空间：
create user ET191201 identified by etoak
default tablespace table_user;
--创建用户未指定表空间：
create user ET191202 identified by etoak;
--创建用户以后修改指定表空间：
alter user ET191202 default tablespace table_user;
--删除表空间：
/*
删除表空间后，原先指向该表空间的用户仍然指向该表空间
需要通过alter user命令将用户的表空间重新指定到有效表空间
*/
drop tablespace table_user including contents and datafiles;
2. 复制表
--1.复制表结构和表数据
create table dept2 as select * from dept;
--2.复制表结构不复制表数据
create table dept3 as select * from dept where 1 = 2;
--3.复制表，复制指定的几个列
create table dept4 as select deptno,dname from dept;
--4.复制表，将新表中表的列名更改为其他名称
create table dept5(deptno1,dname1) as select deptno,dname from dept;
--5.向已经存在的表中插入数据
insert into dept3(deptno,dname) select deptno,dname f###rom dept;
第二部分 MySQL数据库
MySQL是一个关系型数据库管理系统，是由瑞典的MySQLAB公司研发，之后被Oracle 公司收购，
目前是甲骨文旗下的产品。
MySQL是目前最流行的关系型数据库管理系统之一在小型的web应用中，
MySQL是最好的RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个
大仓库内，这样就增加了速度并提高了灵活性。
但是注意，MYSQL中存在大量的database，用户首先选择database，之后在database中
建立表，这一点与Oracle不同。MySQL同样使用SQL语言，此语言是用于访问数据库的最常用标准化语言。
MySQL软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，
尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。由于其社区版的性能卓越，安装简便，搭配Apache旗下的Tomcat
等工具可以组成良好的开发环境。
1.MySQL的安装与卸载
1. 卸载
打开服务 找到Mysql 双击卸载，然后在安装目录下删除mysql文件夹和Program Data下的Mysql文件下
2. 安装
详见 MySQL文件夹下MySQL5.6安装及卸载安装及卸载
2. MySQL的使用
- 使用CMD连接MySQL
mysql -u root -p etoak
--如果出现MySQL不是内部或外部命令
/* 打开系统的环境变量，在Path里边新建一条 把MySQL安装目录吓得bin目录填写进入即可*/
--D:\Program Files\MySQL\MySQL Server 5.6\bin
--在cmd中使用mysql命令时，我们可以直接指定要连接的数据源
mysql -u root -p etoak test
- 在开始菜单中连接mysql
--开始->mysql->Line Client->enter password
- 基本操作
--查看当前有哪些可用的数据源：
show databases;
--创建数据源：
create database etoak;
--使用某个数据源：
use etoak;
--查看当前数据源下有哪些表：
show tables;
--查看表结构：
desc 表名;
explain test;
show colums from test;
--mysql中的自动增长：
create table test(
tid int primary key auto_increment,
tname varchar(10),
birth date
);
--添加下列数据
insert into test(tid,tname,birth) values(null,'葫芦娃','2018-04-16');
insert into test values(null,'金刚','2018-04-16');
insert into test(tid,tname,birth) values(null,'喜洋洋','2018-04-16');
insert into test(tid,tname,birth) values(null,'美羊羊','2018-04-16');
insert into test(tid,tname,birth) values(null,'懒洋洋','2018-04-16');
insert into test(tid,tname,birth) values(null,'沸羊羊','2018-04-16');
insert into test(tid,tname,birth) values(null,'灰太狼','2018-04-16');
insert into test(tid,tname,birth) values(null,'暖洋洋','2018-04-16');
insert into test(tid,tname,birth) values(null,'慢羊羊','2018-04-16');
insert into test(tid,tname,birth) values(null,'潇洒哥','2018-04-16');
insert into test(tid,tname,birth) values(null,'黑大帅','2018-04-16');
--删除数据
delete from test where tid=3;
--修改数据
update test set tname = '金刚' where tid = 2;
--删除表：
drop table if exists test;
--删除数据保留表结构：
truncate test;
- 分页[limit]
--如果limit之后只有一个值，从第一条开始数五条数据
select * from test limit 5;
--如果有两个参数：第一个数为>5开始,第二个数多少个条数
--相当于从下标5开始截取长度为5
select * from test limit 5,5;
- 默认值
default
3.Oracle 和 MySQL 的区别
| 区别 | Oracle | MySQL |
|---|---|---|
| 主键 | 没有自动增长类型，主键一般使用序列，插入记录时将序列号的下一个值赋给该字段即可。 | 一般使用自动增长类型，在创建表的时候只要指定表的主键为auto_increment, 插入记录时，不需要再指定该记录的主键值； |
| 分页 | 通过rownum字段标明位置进行分页。 | limit开始位置，记录个数 |
| 引号 | 一般不准使用双引，用了会报error。 | 单引号，双引号都可以； |
| 分组 | select语句后必须有group by后分组的字段。 | group by 语句可以select 没有被分组的字段； |
| 转换数据 | decode() | case when; |
| 空值处理 | 定义了非空字段就不容许有空的内容。 按照mysql的not null来定义oracle的表结构，导数据时候会产生错误； 因此导数据时要对空字符进行判断，如果为null或空字符，需要把它改成一个空格的字符串。 | 非空字段也有空的内容； |
122
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
