数据到内存(无法永久保存)>数据到文件(效率低)>数据到DB软件(永久保存/查询管理)
数据库软件(存储管理数据的仓库):基于关系(Oracle-MySql-SQLServer-DB2)-基于基于对象 mangoDB(noSQL)
MySql数据库
MySql安装(zip/msi)>CMD命令/MySql客户端管理工具(navicat/sqlyog)
(数据库名>基字符集utf8>数据排序规则utf8_bin)
MySQL.msi安装:Typical默认/Complete完全/Custom自定义>安装目录>root.password
>配置(Detailed Configuration)>服务类型>用途>网站并发连接数>TCP/IP端口>编码(UTF-8)
卸载MySQL.msi:关闭服务>卸载程序>清空安装目录>删除隐藏目录C:ProgramData/MySQL
MySQL.zip安装:
配置环境变量(MYSQL_HOME+path:%MYSQL_HOME%\bin;)>管理员cd MySQL\bin> mysqld --initialize-insecure --user=mysql#生成\data目录 >启动服务net start mysql
>配置(新建)my.ini
[client]
port=3306 # 设置监听端口
default-character-set=utf8
[mysqld]
# 设置MYSQL的安装目录
basedir=D:\mysql-5.7.20-winx64
# 设置MYSQL的数据目录
datadir=D:\mysql-5.7.20-winx64\data
port=3306
character_set_server=utf8
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER
#开启查询缓存
explicit_defaults_for_timestamp=true
skip-grant-tables
>登录设置root密码:
update mysql.user set authentication_string=password('root') where user='root';
flush privileges;
quit;
Linux下安装MySql:
- 下载MySql的RPM安装包:MySQL-client/devel/server-linux_64.rpm
解决安装包依赖冲突:rmp -e 冲突包 -nodeps
- 启动MySql服务:service mysqld start
- 配置MySql,通过SQLyog操作MySql
登陆管理MySQL:mysql -u<root> -p<password>
mysql> show databases; #显示所有数据库
mysql> use t38; #使用t38数据库
mysql> show tables;
mysql> desc tb_user;
mysql> select * from tb_user; |insert|udpate|delete
mysql> exit;
create database t38; --创建数据库
create table ‘tb_user’( --创建表
‘id’ int(4),
‘username’ VARCHAR(20),
‘password’ VARCHAR(20)
);
alter table tb_user drop password ; --删除字段
insert into tb_user values(1,”admin”,”123”); --插入
update tb_user set password=”111” where id=1; --修改
delete from tb_user where id=1; --删除
select * from tb_user; --查询
select username as name,password as pwd from tb_user as t;--别名
select count(*) from tb_user;
select username,rolename
from tb_user t1 inner join tb_role t2
on t1.role_id=t2.id --内连接查询
MySQL常用数据类型:数值类型(BIT/BOOL/BOOLEAN/INT/FLOAT/DOUBLE)-文本二进制类型(char/varchar/text)-时间日期(DATE/DATETIME/TimeStamp)
结构化查询语句SQL:数据定义语句DDL(create/alter/drop)-数据操作语句DML(insert/update/delete/truncate)-数据查询语句DQL(select/show)
增C-删D-改U-查R
MySQL基础:
管理数据库:
增: create database 数据库名 default character set 字符集;
修: alter database 数据库名 default character set 新的字符集;
删: drop database 数据库名;
查: show databases;
管理表:
增: create table 表名(字段名1 字段类型,字段名2 字段类型......);
修:
增加字段: alter table 表名 add column 字段名 字段类型;
修改字段类型: alter table 表名 modify column 字段名 新的字段类型;
修改字段名: alter table 表名 change column 旧字段名 新的字段名 字段类型;
修改表名: alter table 表名 rename to 新表名;
删: drop table 表名;
查: show tables; desc 表名;
管理数据:
增: insert into 表名(字段名1,字段名2....) values(值1,值2.....);
修: update 表名 set 字段名1=值1,字段名2=值2...... where 条件;
删: delete from 表名 where 条件;
truncate table 表名;
查:
a)所有字段:select * from 表名;
b)指定字段: select 字段名1,字段名2 from 表名;
c)指定别名: select 字段名1 as 别名1,字段名2 as 别名2 from 表名;
d)添加常量列: select 常量值 as 别名 from 表名;
e)合并列: select (字段名1+字段名2+....) as 别名 from 表名;
f)去除重复: select distinct 字段名 from 表名;
g)条件查询:
逻辑条件: where 条件1 and/or 条件2;
比较条件: where 字段名 >/>=/</<=/=/<> 值
where 字段名 between 值1 and 值2;
判空条件:
null:where 字段名 is null/is not null;
空字符串:where 字段名=''/<>''
模糊条件: where 字段名 like 值
%:代表任意个字符
_: 代表一个字符
h)聚合查询:max()/min()/avg(平均值)/count(统计数量)
i)分页查询:limit 起始行,查询行数
j)排序:order by 字段名 asc升序/desc降序
h)分组查询:group by 字段名
k)分组后筛选:group by 字段名 having 条件;
MySQL加强:
数据约束:默认值default-唯一unique-非空not null-主键primary key(唯一+非空)-自增长auto_increment-外键foreign key
级联操作:级联更新(on update cascade)-级联删除(on delete cascade)
多表查询:内连接查询(inner join)-左外连接(left outer join)-右外连接(right outer join)
数据库设计:
第一范式:要求表的每个字段都必须独立的不可分割的最小单元
第二范式:要求表的除主键外的其他字段都和主键有依赖关系(一张表表达一个意思)
第三范式:要求表的除主键外的其他字段都只能由主键决定
MySql事务:原子性-一致性-隔离性-持久性
set autocommit = 0;--关闭MySql的自动提交
start transaction--开始事务,标记事务的起始点
commit--提交事务给数据库
rollback--事务回滚
set autocommit = 1;--还原自动提交
MySql备份:mysqldump客户端
mysqldump -h 主机名 -u 用户名 -p[options] 数据库名 [table] >path/bk.sql
mysqldump -uroot -p123 t38 tb_user > d:/sqlbk.sql
Oracle数据库
数据库(存储数据的仓库)-数据库管理系统(操作数据库的平台)
表 === 类,数据库存储数据的基本单元
字段 === 属性
记录 === 对象
关系 === 表A的某个字段数据全部来自表B的指定字段
数据库实例:后台进程,一个实例对应一个数据库
SID:唯一标识Oracle数据库的名称
安装数据库:
OracleService<SID>服务:数据库服务
OracleOraDb<11>g_home<1>TNSListener服务:监听器服务
客户端:tnsnames.ora(监听协议 地址 端口号 服务名)
服务器:listener.ora(监听协议 地址 端口号 全局数据库名称)
连接数据库:SQL*Plus方式 PL/SQL Developer方式
- 设置网络服务名:连接命名
- TCP/IP协议:设置主机名IP+端口号port
- 服务名:设置数据库SID
表空间Tablespaces:虚拟的目录,最大的逻辑单位;永久性、临时性、撤销表空间
默认表空间:
SYSTEM表空间 SYSAUX表空间
UNDO(撤销)表空间:事物的临时存储
USERS表空间:scott
TEMP(临时)表空间: 辅助查询所产生临时信息,查询结束则删除临时信息
创建表空间:
CREATE TABLESPACE tablespace
DATAFILE ‘filename’ [SIZE integer [K|M] ]
[AUTOEXTEND [OFF|ON] ]
数据库用户:SYS(超级用户) SYSTEM(系统管理员) SCOTT(示范账户)
创建用户:
CREATE USER user
IDENTIFIED BY password
[DEFAULT TABLESPACE tablespace]
[TEMPORARY TABLESPACE tablespace]
数据库权限管理:
系统权限:CREATE SESSION(连接数据库)|TABLE|VIEW|SEQUENCE(序列)
对象权限:CONNECT(临时用户) RESOURCE DBA
授予权限:GRANT 权限|角色 TO 用户名;
撤销权限:REVOKE 权限|角色 FROM 用户名;
Oracle数据类型:
字符类型:CHAR VARCHAR2 LONG
数值类型:NUMBER(p精度,s范围)
日期时间类型:DATE TIMESTAMP
LOB类型:CLOB(大量字符数据) BLOB(二进制对象) BFILE(二进制文件)
创建数据库表:
CREATE TABLE命令:
CREATE TABLE [schema.]table
(column datatype);
ALTER TABLE命令:
修改:ALTER TABLE <tablename> MODIFY (column definition);
添加:ALTER TABLE <tablename> ADD (column definition);
删除:ALTER TABLE <tablename> DROP COLUMU column;
查看表结构:DESC <tableanme>
数据完整性约束:
添加约束:ALERT TABLE
ADD CONSTRAINT约束名 约束类型 具体的约束说明
//约束名:约束类型_约束字段(PK_tno|UQ_name|CK_gendar|FK_deptno)
主键约束(Primary Key):
alert table 表名
add constraint pk_** primary key(主键列)
唯一约束(Unique):
alert table 表名
add constraint uq_** unique (唯一列)
检查约束(check):
alert table 表名
add constraint ck_** check (判断表达式)
外键约束(foreign key):
alert table 表名
add constraint fk_** foreign key (引用列) references 被引用表(被引用列)
删除约束:ALTER TABLE 表名 DROP CONSTRAINT 约束名
查看约束:SELECT * FROM USER_CONSTRAINTS
WHERE table_name=’STUINFO’;
CREATE TABLE stuMarks --学员成绩表
(
ExamNo CHAR(7) NOT NULL, --考号
stuNo CHAR(6) NOT NULL, --学号
writtenExam NUMERIC(3,0) NOT NULL, --笔试成绩
LabExam NUMERIC(3,0) NOT NULL --机试成绩
);
----为stuMarks添加主键约束
ALERT TABLE stuMarks
ADD CONSTRAINT PK_ExamNo PRIMARY KEY (ExamNo);
SQL编程
SQL语言:定义语言DDL 控制语言DCL 操纵语言DML 事务语言TCL:COMMIT
DML:检索、插入和修改数据库信息(INSERT插入、UPDATE更新、SELECT选择、DELETE删除)
INSERT命令:
INSERT INTO 表名 [(column)]
VALUES (value);
UPDATE命令:
UPATE table
SET column_name=value
[WHERE condition];
SELECT命令:
SELECT *|{ [DISTINCT] column|expression [alias] }
FROM table
[WHERE condition]
[GROUP BY column [HAVING condition]]
[ORDER BY column(ASC|DESC)];
算术操作符(+ - * /) 比较操作符(= != < > <= >= BETWEEN...AND|IN|LIKE|IS NULL) 逻辑操作符(AND OR NOT)
DELETE命令:
DELETE [FROM] table
[WHERE condition];
SQL函数
单行函数:
字符函数:INITCAP(char) LOWER(char) UPPER(char)
数字函数:ABS(n)取绝对值 ROUND(m,n)四舍五入
日期函数:TRUNC()截取
转换函数:TO_CHAR|TO_DATE|TO_NUMBER
其他函数:NVL(exp1,exp2)//若exp1值为null,则返回exp2的值
NVL2(exp1,exp2_非null替换值,exp3_null替换值)
DECODE(value,if1,then1,if2,then2,else)
聚合函数(分组函数):多行进行运算,返回一个结果
SELECT SUM()|AVG()|COUNT()|MAX()|MIN() FROM Table
分析函数:每个分组进行运算,对应的结果可以多个
函数名([参数]) OVER([分区子句] [排序子句])
SELECT ROW_NUMBER()[相同数据123]|DENSE_RANK()[112]|RANK()[113]
FROM TABLE
子查询:相关子查询|不相关子查询;将一个查询包含到另一个查询
父查询
WHERE子句 [NOT] IN|比较运算符|<>ANY/ALL|[NOT] EXISTS(子查询)
连接查询:合并多个数据表中的列
SELECT ···
FROM 主表 别名
JOIN 关联表 别名 ON 条件列
内连接[inner] join:等值连接|不等值连接|自然连接 //显示公共部分
外连接Left|right [outer] join:左外连接|右外连接//显示全部
自连接Self join
交叉连接(笛卡儿积)cross join
集合查询:合并多个数据表中的行
SELECT子句
UNION [ALL]并集:列的个数+类型完全对应
INTERSECT交集
MINUS差集
SELECT子句
分页查询:分批显示结果
### 内部子查询:添加查询条件+排序 ###
### 外部子查询:固定rownum列 ###
SELECT 别名.*,伪列:ROWID(每行记录唯一ID)|ROWNUM(行次序,用于分页显示) rn
FROM 表名 别名;
SELECT *
FROM (SELECT e.*,rownum rn
FROM (SELECT * FROM emp ORDER BY SAL DESC
) e
)
WHERE rn>=6 AND rn<=9;
PL/SQL编程及Oracle体系结构
PL/SQL:过程语言/SQL,Procedural Language/SQL
PL/SQL体系结构:SQL语句执行器|过程语句执行器>PL/SQL引擎>Oracle服务器
PL/SQL块:
[DECLARE]
--声明部分:声明变量、类型及游标,以及局部的存储过程和函数
BEGIN
--执行部分:过程及SQL语句,即程序的主要部分
[EXCEPTION]
--异常处理部分:错误处理
when 异常类型 then 异常处理代码
END;
PL/SQL分类:无名块或匿名块 命名块 子程序 触发器 程序包
常量和变量:
变量声明:variable_name data_type[(size)] [:=init_value];
常量声明:variable_name CONSTANT data_type :=value;
v_name VARCHAR2(20):=’Ftmy’;
v_num NUMBER(7,2);
数据类型:
标量类型:CHAR VARCHAR2 NUMBER(p,s) LONG DATE BOOLEAN
LOB类型:BFILE BLOB CLOB NCLOB
属性类型:%type(对表或字段的类型引用) %ROWTYPE(对表的整行引用)
注释:
行注释:v_sal NUMBER(12,2);--人员的工资变量
块注释:/* */
控制语句
条件控制:
IF语句:
if 判断条件 then
true代码;
else
false代码;
end if;
if 判断条件 then 代码1;
elsif 判断条件 then 代码2;
elsif 条件 then 代码3;
else 代码4;
end if;
CASE语句:
case [条件表达式]
when 值1 then
代码1;
when 值2 then
代码2;
[else 代码3;]
end case;
SELECT
case
when 条件 then 值/‘字符串1’
when 条件 then 值/‘字符串2’
else 值/‘字符串3’
FROM table
循环控制:
LOOP循环:
loop 循环代码;
exit when 判断条件;
end loop
WHILE循环:
while 循环判断条件 loop
循环代码;
end loop;
FOR循环:
for 中间变量 in 下限 .. 上限
循环代码;
end loop;
顺序控制:GOTO语句 NULL语句
错误处理:预定义错误 非预定义错误 用户定义错误
EXCEPTION
WHEN 异常名称 THEN 处理代码;
WHENM OTHERS THEN 处理代码;--其它异常
END;
DECLARE
v_name teacher.tname%type;
v_sal teacher.sal%type;
/*v_start NUMBER:=0;*/
BEGIN
select t.tname,t.sal into v_name,v_sal from teacher t where t.tno=1;
dbms_output.put_line(v_name||’:’ ||v_sal);
/*LOOP
INSERT INTO dept_loop
VALUES(sq_dept_loop.nextval,’部门’||v_Start,’地址’||v_start);
v_satrt:=v_start+1;
EXIT WHEN v_satrt>100;
END LOOP;*/
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('**');
WHEN OTHERS THEN
dbms_output.put_line('未知异常!');
END;
游标及事物处理
游标:数据缓冲区,存放SQL语句的执行结果;对查询结果集的引用
游标类型:静态游标(编译时知道SELECT语句 显示游标|隐式游标)
动态游标(运行时动态决定何种查询 强类型|弱类型)
游标:
游标的属性:%FOUND %NOTFOUND %ROWCOUNT %ISOPEN
- 声明游标:CURSOR currsor_name [(parameter)]
[RETURN return_type] IS select_statement;
- 打开游标:OPEN currsor_name [(parameter)];
- 提取游标(+loop+游标属性):FETCH currsor_name INTO variables;
- 关闭游标:CLOSE currsor_name;
--游标的读取
DECLARE
CURSOR cs_emp
IS SELECT ename FROM scott.emp;--1.声明游标
v_ename scott.emp.ename%TYPE;--存储的中间变量
BEGIN
--2.打开游标
OPEN cs_emp;
--3.抓取游标+loop
LOOP
FETCH cs_emp INTO v_ename;
EXIT WHEN cs_emp%NOTFOUND;
dbms_output.put_line('编号:'||cs_emp%ROWCOUNT||
'姓名: '||v_ename);
END LOOP;
--4.关闭游标
CLOSE cs_emp;
END;
--游标的读取:for循环
DECLARE
CURSOR cs_emp
IS SELECT ename FROM scott.emp;--1.声明游标
BEGIN
--2.抓取数据:for循环
--for循环每次以'行'的形式抓取
FOR v_row IN cs_emp LOOP
dbms_output.put_line('编号:'||cs_emp%ROWCOUNT||
' 姓名:'||v_row.ename);
END LOOP;
END;
事务:
ACID:原子性Atomicity、一致性Consistency、隔离性Isolation、持久性Durability
事务控制语句:
COMMIT--事务提交
ROLLBACK--事务回滚
SAVEPOINT--事务保存点,只能与回滚结合使用、
ROLLBACK TO<SavePoint_Name>--回滚到指定保存点位置(部分回滚)
SET TRANSACTION—设置事务属性
--插入数据保存在撤销表空间
INSERT INTO scott.dept VALUES(50,'a',NULL);
INSERT INTO scott.dept VALUES(60,'b',NULL);
SAVEPOINT a;
INSERT INTO scott.dept VALUES(70,'c',NULL);
ROLLBACK TO SAVEPOINT a; --回滚到保存点a
SELECT * FROM scott.dept;
ROLLBACK; --全部回滚
SELECT * FROM scott.dept;
锁:自动锁|显示锁 排他锁|共享锁 DML锁|DDL锁
--锁
SELECT * FROM scott.dept FOR UPDATE;
UPDATE scott.dept SET DNAME='Tom' WHERE DEPTNO=20;--SQL Plus端执行
DML锁:TM锁(表级锁)| TX锁(事务锁或行级锁)
锁定表:LOCK TABLE <table_name> In <lock_mode> MODE [NOWATT];
子程序和触发器
子程序:过程-函数;声明部分 可执行部分 异常处理部分;模块化、可重用性、可维护性、安全性
存储过程:命名的PL/SQL代码块
CREATE [OR REPLACE] PROCEDURE 存储过程名
(
参数 [IN|OUT|IN OUT] 参数类型,
--参数传递模式:IN(输入,默认)、OUT(输出[return 返回值])、IN OUT(输入/输出)
参数1 参数类型, --参数只声明类型不声明大小:name VARCHAR2;
参数2 参数类型 --参数默认值:default 默认值;若使用默认值,只能不赋值
)--按位置传递参数:有默认值的参数放在后面;按名称传递参数:参数=>值(salary=>2000)
AS|IS
变量 变量类型;
BEGIN
EXCEPTION
when 异常名 then
异常处理代码
END [过程名];
存储过程的调用:
- 命令窗口:execute 存储过程[(...)];
- PL/SQL代码块:
begin
存储过程(...);
end;
函数:
CREATE [OR REPLACE] FUNCTION <function name>
[(param1,param2)] --函数名(参数)
RETURN 返回类型<datatype>
IS|AS
变量 变量类型 :=初始值 --变量声明
BEGIN
Executable Statements;
RETURN result; --返回值
EXCEPTION
Exception handlers;
END;
程序包:Package|包;包括规范和主体
CREATE [OR REPLACE] PACKAGE BODY package_name
[IS|AS]
数据类型定义、常量变量声明
BEGIN
执行部分(初始化部分)
END;
DROP PACKAGE [BODY] package_name;-- 删除程序包
触发器:
行级触发器、语句级触发器、INSTEAD OF触发器、模式触发器、数据库级触发器
启用和禁用触发器:ALTER TRIGGER trigger_name {ENABLED|DISABLED};
触发器删除:DROP TRIGGER <trigger_name>;
DML触发器:触发时机、触发事件、触发类型、触发条件
Oracle体系结构:实例、内存结构、进程结构、数据库
Oracle存储结构:
物理结构:主要文件(数据文件|日志文件|控制文件) 其他文件(参数文件|临时文件|密码文件)
逻辑结构:数据库->表空间->段->区->数据块
Oracle实例
Oracle内存结构:系统全局区SGA(共享池|数据缓冲区|日志缓冲区) 程序全局区PGA 用户全局区UGA
Oracle进程结构:用户进程 服务器进程
数据库备份与恢复
备份:物理备份|逻辑备份 完全备份|增量备份|差异备份
恢复:实例恢复 介质恢复|完全恢复|不完全恢复
导入与导出:四种模式(完全数据库 表 用户 表空间)
EXP命令导入:exp username/password[PARAMETER=value]
IMP命令导出:inp username/password[PARAMETER=value]
冷备份和热备份:归档模式和非归档模式
数据库设计和SQL查询优化
索引:与表关联的可选结构,提高检索数据的效率
B树索引:默认索引[平衡树:数据均匀分配]
反向键索引:适用于自增长的列
位图索引:适用于低基数列(重复数据很多)
唯一索引和非唯一索引:没有任何两行有重复值
分区索引和非分区索引:适用于大型表
组合索引:由两个及两个以上的列共同构成一个索引
基于函数的索引:已经创建过索引的列一旦跟函数相结合,则该索引失效
创建索引:create index 索引名 on 表 (函数(列));
序列:生成有规律的数据
create sequence 序列名
start with 起始值|种子 –起始值>=最小值
increment by 增量|步长
访问序列:nextval(下一个值|初始值) currval(当前值,在执行nextval之后)
更改序列:不能修改start with参数
drop sequence 序列名--删除序列
视图:虚表,数据基于基本表或实际表
CREATE [OR REPLACE] VIEW 视图名
AS select_statment
表分区:范围分区range 散列分区hash 列表分区list 复合分区
同义词:使用对象的别名,隐藏对象的名称和所有者;简化SQL语句
私有同义词 公有同义词