简介说明

四大传统主流数据库

    Oracle MySQL SqlServer DB2

 非关系型数据库 Redis MongoDB

主流数据库是关系型数据库表与表之间是存在关联关系的

当我们说安装数据库的时候指的是安装数据库服务

创建数据库的时候指的是创建数据仓库

       可以给数据仓库分成若干块每一块就叫做一个表空间

        每个表空间都可以对应一个用户

初步使用数据库的时候先创建一个用户再给此用户指定一个表空间

如果不给用户提供创建表空间就默认使用Oracle的默认表空间为了安全期间应该为每个用户都创建一个表空间

.DBF就是表空间文件的意思表空间可以提高安全性。在表空间的用户只能访问存入当前表空间的内容

---

标准创建流程

        先创建表空间创建用户让用户名与此表空间关联

        再建表这样做的话新建的表就会创建在刚才的表空间中

        最后再赋予登录增删改权限即可

创建表空间---创建用户关联表空间---赋予登录权限---赋予增删改权限

查询所有表空间系统和自己的管理员操作

一、创建表空间管理员操作

create tablespace 表空间名
datafile '存储的路径'
size 10M
autoextend on
next 10M

datafile 用于设置物理文件名称

size 用于设置表空间的初始大小

autoextend on 用于设置自动增长如果存储量超过初始大小则自动扩容

next 用于设置扩容的空间大小

怎么删除表空间管理员操作

        注意必须先用语句执行再手动删除磁盘空间否则Oracle会崩溃

drop tablespace 表空间名称

二、创建用户管理员才可以创建用户

--创建用户
create user 用户名
identified by 密码
default tablespace 表空间名称


--修改用户的表空间
alter user 用户名default tablespace 表空间名称

identified by 用于设置用户的密码

default tablespace 用于指定默认表空间名称

用户创建成功了但是缺失某个权限导致登录失败

login denied 登录拒绝就是缺失登录权限

 三、赋予登录权限

grant connect to 用户名;

当建表时显示没有操作权限

 四、赋予增删改查的权限

grant resource to 用户名

回收权限

        注意同级别不能“互相伤害”就是删除回收平级用户

--回收登录权限
revoke connect from 用户名

--回收增删改权限
revoke resource from 用户名;

五、赋予收回管理员权限

--给管理员权限
grant dba to 用户名;

--收回管理员权限
revoke dba from 用户名;

---

其他流程操作语法

    Oracle有几个默认用户

        Syssystemscott

        Syssystem是管理员Scott是普通用户

重置普通密码管理员

sys as sysdba        //作为系统管理员身份登录
alter user 用户名 identified by 密码

用户被锁定解锁

--管理员执行
alter user 用户名 account unlock;

删除用户管理员

        删除用户时如果有表则删除失败因为安全起见先删除所有表再删除用户

--删除用户
drop user 用户名 cascade

---

数据定义语言DDLDDLData Definition Language

定义数据库对象语言其语句包括动词createdrop等

       Create创建

创建表空间

--创建表空间
create tablespace 表空间名
datafile '存储的路径'
size 10M
autoextend on
next 10M

创建用户

--创建用户
create user 用户名
identified by 密码
default table 表空间名称

创建表

default 向表中添加某个默认值

--创建表
create table 表名称(
字段名称 类型长度,
字段名称 类型长度default ''
);

 复制一个表

--利用现有的表创建新表复制一个表
create table 新表名称 as select * from 旧表名称;

复制一个表不复制表的信息只保留表的类型 

--只复制表的类型
--Where1=2的意思就是不相等复制表的时候就不复制表的信息只复制表的类型
Create table 新表名 as select * from 旧表名 where 1=2;

复制一个表剔除重复数据

--利用现有的表创建新表复制的表中数据不重复
create table 新表名称 as select distinct * from 旧表名称

        alter修改

新增约束

主键创建表的时候每个表都要有主键主键起到一个唯一识别的作用

约束的作用约束用于规定表中的数据规则如果存在违反约束的数据行为行为会被约束终止

• 主键约束唯一性非空性
• 唯一约束唯一性可以为空但只能有一个
• 检查约束对该列数据的范围、格式的限制年龄性别
• 非空约束该列不允许包含空值

    PK主键Primary KeyUQ唯一Unique 约束
	CK检查Check 约束FK外键(Foreign Key)约束

--约束语法可以直接在追加表后面
alter table xx
add constraint 约束名 约束类型 具体约束说明
	
例如
alter table product add constraint PK_PNO primary key (pno);

--添加年龄约束15~40
alter table stuInfo
add constraint checkAge check(stuAge>15 and stuAge<40);

删除约束

--删除约束
alter table 表名
drop constraint 约束名

用户被锁定

alter user 用户名 account unlock

        drop删除 

删除表

--删除表
drop table 表名

---

数据操作语言DMLData Manipulation Language

包括insert插入update更新delete删除

数据查询语言DQLData Query Language

也叫数据检索语句用于从表中获取数据确定数据怎样在应用程序给出。

关键字select是所有sql用的最多的动词其他DQL常用关键字有whereorder bygroup byhaving。这些关键字常与其他类型的SQL语句一起使用

        Insert插入

插入数据

--向表中插入数据
insert into 表名 values('xxx',to_date('2020-02-02','yyyy-mm-dd hh24:mi:ss'));

--如果插入的是部分数据
insert into 表名字段名称字段名称…… values('xxx','xxx');

        select选择

select语句的作用是从数据库中返回信息主要有以下作用

• 列选择投影操作

能够使用select语句的列选择功能选择表中的列这些列是我们想要用查询返回的当我们查询时可以选择的查询表中指定的列

• 行选择选择操作

能够使用select语句的行选择功能选择表中的行这些行是我们想要用查询返回的能够使用不同的标准限制所看见的行

• 连接多表操作

能够使用select语句的连接功能来集合数据这些数据虽然被存储在不同的表中但是我们可以通过连接查询到该数据

基本select语句

在最简单的形式中select语句必须包含以下内容

        一个select子句指定被显示的列

        一个from子句指定表该表包含select子句中的字段列表

select语法解释

select是一个或多个字段的列表

* 选择所有的列

distinct 关键字表示禁止重复

column|expression 选择指定的字段或表达式

alias 给所选择的列不同的标题

from table 指定包含列的表

select语法基本结构

select *| {[distinct]column|expression [alias]....} from table

select 确定哪些列

from 确定哪张表

select语句的执行顺序

from子句哪张表       

where子句 什么要求   

select子句 怎么投影       

order by子句怎么排序

查询表中所有信息

--查询表中所有信息
select * from 表名

 查询表结构

--查看表结构
select * from user_tab_columns where table_name='departments'

--命令行窗口下查看表结构
--desc 表名

查询指定的信息

--查询指定的信息
select * from 表名 where 主键='xx';

--查询名称King或者pat的员工的工作编号
select job_id from employees where last_name='King' or first_name='Pat'

选择指定的行列显示

--指定数据进行 行和列 显示
select xx,xx from 表名

筛选重复的行

--显示筛选重复的行
select distinct xx,xx from 表名

按照某个规则排序

--按照某个规则排序(年龄降序学号升序)
select * from 表名 order by xxage DESC,xxno ASC;

--例如
--ascending 升序
select * from sc order by 要升序的字段名 asc;

--descending 降序
select * from sc order by 字段名 desc;

--默认显示是asc可以为空

 别名显示

--列表别名就是自定义个名称
--'S'|| 学生编号前面有个S
select 'S'||stuNO 学号stuName"姓 名" FROM 表名

 例如别名显示后降序排序

查询不包括某条件的字段

--not in 范围         =不等于具体的值
-- !=的效率比not in高not in 会造成全盘扫描造成索引异常

--部门编号不等于10  !=
select * from emp where deptno!=10;
--not in 
select * from emp where  deptno not in('10','20');

查询某个时间等于某

--查询emp表2000-1-13入职的员工
select * from employees where hire_date=to_date('2000/1/13','YYYY-MM-DD')

        delete删除

删除表中所有信息

--删除表中所有字段信息
delete from 表名

 删除表中的某个信息

-- 删除表中的某个信息
delete from 表名 where 主键='xx'


--删除某个信息,多个信息可以and连接
delete from 表名 where xx=xx and xx=xx

        update更新   

 修改表中的某个信息

--修改表中的某个信息
--一般都根据主键来修改数据
update 表名 set xx=xx,xx=xx where 主键=''

例如
update 表名 set sname='james' where sid=2023;

---

事务控制语言TCLTransaction Control Language

它的语句能确保被DML语句影响的表的所有行得以及时更新

        Commit提交

事务提交

        savepoint保存

设置回滚点

        pollback回拨

事务回滚

---

数据控制语言DCLDCLData Control Language

它的语句通过grant和revoke获取许可确定单个用户和用户组对数据库对象的访问

        Grant授予

赋予登录权限

grant connet to 用户名

赋予增删改权限

--赋予增删改权限
grant resource to 用户名

赋予管理员权限

--赋予管理员权限
grant dba to 用户名

        revoke撤销 

回收管理员权限

--回收管理员权限
revoke dba from 用户名

回收登录权限

--回收登录权限
revoke connect from 用户名

回收增删改权限

--回收增删改权限
revoke resource from 用户名

---

聚合函数 

注查询的emp表在scott用户里

AVG 求平均值

--avg()平均值

---求所有人平均工资
select avg(sal) from emp;

--求20号部门的平均工资
select avg(sal) 平均工资 from emp where deptno=20;

--求20号部门的平均工资 和 部门编号
select deptno,avg(sal) 平均工资 from emp where deptno=20 group by deptno;

--按照部门编号进行分组求每组的平均工资
select deptno,avg(sal) from emp group by deptno;

MIN求最小值

--min()最小值

--求本公司薪资最低的人的工资
select min(sal) from emp;

--求20号部门工资最低的人
select min(sal) from emp where deptno=20;

--求本公司每个部门的最低工资
select deptno,min(sal) from emp group by deptno;

--分组就添加group by

MAX求最大值

--max()最大值

--求本公司薪资最高的人的工资
select max(sal)最高工资 from emp;

--求30号部门工资最高的人
select max(sal)部门最高工资 from emp where deptno=30;

--求本公司每个部门的高工资
select deptno,max(sal) from emp group by deptno;

SUN求和

--sun()求和

--求公司每月要发的总工资
select sum(sal)总工资 from emp;

--求每个月给30号部分发的总工资
select sum(sal) from emp where deptno=30;

--求本公司每个部门的总工资
select deptno 部门编号,sum(sal)总工资 from emp group by deptno;

count累加

--count()累加

--求公司总人数
select count(*) from emp;

--求20号部门的人数
select count(*) from emp where deptno=20;

--最好不要用count**代表查找所有字段
select deptno,count(*) from emp group by deptno;

--求每个部门的总人数
select deptno,count(empno) from emp group by deptno;


--每个部门的。。。值只要2000以上的
select deptno 部门编号,max(sal) 最大值,AVG(sal) 平均值,min(sal) 最小值
from emp
group by deptno
having avg(sal)>2000;

group by子句

用于将信息划分为更小的组每一组行返回针对该组的单个结果分组就添加group by

--group by子句

SELECT deptno,MAX(sal) maxSal,
AVG(sal) avgSal,
MIN(sal) minSal
 FROM emp
GROUP BY deptno;

order by子句 

一般是用来依照查询结果的某一列或多列属性进行排序升序ASC降序DESC默认为升序order by 子句在select语句的最后

在排序中也可以使用没有包括在select子句定义的列排序就是没有做投影

当排列为空值时升序排序显示在最后降序排序显示在最前面

选择多个列属性进行排序然后排序的顺序是从左到右依次排序。

如果前面列属性有些是一样的话再按后面的列属性排序。前提一定要满足前面的属性排序因为在前面的优先级高

--按照某个规则排序(年龄降序学号升序)
select * from xxx order by xxage DECS,xxno ASC;

--男同学的年龄升序输出asc,降序desc
select sno 男同学编号,sage 男同学年龄 from student where ssex='男' order by sage desc

用列号排序 

可以使用投影的列的序号指定排序列但是不推荐

--对员工名称薪资进行排序
--不建议使用列号排序如果变动就失效了
select last_name,job_id,salary from employees order by 1 asc,3 desc

 同列别名排序

可以使用列的列表名指定排序列

--使用列别名进行排序
select last_name as name,job_id,salary from employees order by name asc
select last_name as name,job_id as id,salary from employees order by id desc

多列排序 

多列排序可以用多列排序查询结果。在order by 子句中多个指定的列名之间用逗号分开。如果想要对某个列倒序排序需则在该列名后面指定desc

--多列排序注意逗号隔开
select last_name as name,job_id as id,salary from employees order by name asc,id asc

having子句

用于指定group by 子句检索行的条件

having和where的区别

只有满足HAVING短语指定条件的组才输出。

HAVING短语与WHERE子句的区别作用对象不同。

        1》WHERE子句作用于基表或视图从中选择满足条件的元组。

        2》HAVING短语作用于组从中选择满足条件的组。

理解要用having的话前面的语句必须要有having的字段否则会报错而where不用

因为having是从前筛选的字段再筛选而where是从数据表中的字段直接进行的筛选的。

--having子句
--条件可以用and追加

SELECT deptno,
MAX(sal) maxSal,
AVG(sal) avgSal,
MIN(sal) minSal
FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000;

where子句

select *| {[distinct]column|expression [alias]....} from table [where condition(s)]

where 子句跟着from子句

where 限制查询满足条件的行

condition 由列名、表达式、常数、比较操作组成

 选择限制的行

        可以用where子句限制从查询返回的行。一个where子句包含一个必须满足的条件where子句紧跟着from子句。如果条件是true返回满足条件的行。

        where子句能够比较列值、文字值、算术表达式或者函数where子句由三个元素组成

• 列名
• 比较条件
• 列名、常量或者值列表

 Not in和= 的区别

 语句执行顺序

执行顺序: where -> group by -> select ... from ... -> order by..

SQL函数

        SQL函数分为单行函数、聚合函数、分析函数

单行函数

        单行函数对于从表中查询的每一行只返回一个值可以出现在select子句中和where子句中

        单行函数大致可以划分为

• 日期函数
• 数字函数
• 字符函数
• 转换函数
• 其他函数

转换函数

        转换函数将值从一种数据类型转换成另一种数据类型

常用的转换函数有

• TO_CHAR
• TO_DATE
• TO_NUMBER 

--如下

SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日" HH24:MI:SS')
FROM dual;

SELECT TO_CHAR (123456.03,'099,999.99') FROM dual;　

SELECT TO_DATE('2005-12-06' , 'yyyy-mm-dd')
FROM dual;

SELECT TO_NUMBER('100') FROM dual;

 select语句中的算术表达式

用算术表达式创建数字和日期数据的表达式+ - * /

注意如果对日期进行计算我们只能对date和timestamp数据类型使用加和减操作

运算符的优先级* / + -

• 乘法和除法比加法和减法的优先级高
• 相同优先级的运算符从左到右计算
• 圆括号用于强转优先计算并且使语句更加清晰

--算数表达式练习

--计算employees表的员工年薪+100以后是多少
select employee_id,first_name,salary*12+100 from employees;

--然后进行对薪水进行降序排序
select employee_id 员工id,first_name 员工名称,salary*12+100 年薪 from employees order by salary desc;

--计算employees表的员工薪水+100以后的年薪是多少升序排序
select employee_id,first_name,(salary+100)*12 from employees order by salary asc;

连字运算符 

连字运算符

• 连接列或者字符串到其他的列
• 用两个竖线表示||
• 构造一个字符表达式的合成列

我们能够用连字运算符进行列与列之间、列与算术表达式之间或者列与常数值之间的连接来创建一个字符表达式连字运算符两边的列被合并成一个单个的输出列

--连字运算符 ||
--连接e表中的员工名称

select first_name||last_name 员工名称 from employees;

 文字字符串

• 文字字符串是包含在select列表中的一个字符串一个数字或者一个日期
• 日期和字符的文字字符串值必须用单引号括起来
• 每个文字字符串在每行输出一次

文字字符串不是列名或者别名对每个返回行打印一次。任意格式的文本文字字符串能够被包含在查询结果中并且作为select列表中的列处理日期和字符文字必须放在单引号中数字不需要

--文字字符串文字需要加'' 数值不需要

select first_name||' is a '||job_id as 员工职务 from employees;

select first_name||0744||job_id as 员工职务 from employees;

 distinct去除重复行

在select语句中用distinct关键字除去相同的行。

为了在结果中除去相同的行在select子句中的select关键字后面紧跟distinct关键字

--去除e表中的department_id重复数据
select distinct department_id from employees order by department_id desc;

--注意 distinct 剔除的数据后面还有数据的话这两个结果将构成一个条件来执行distinct关键字
select distinct department_id,first_name from employees order by department_id desc;

 注意两个结果将构成一个条件来执行distinct关键字所以还会重复

字符串和日期

• 字符串和日期的值放在单引号中
• 字符串区分大小写日期值是格式敏感的
• 日期的默认格式是 YYYY-MM-DD

用的很少前端拿到的数据是string类型保存在数据库的话要转换成 日期类型

从数据库拿数据展示到页面就需要把日期类型的数据转换成字符串

比较条件

条件运算符

运算	含义
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
<>	不等于也可以使用= 和 ^= 来表示

--查询薪水小于等于3000的员工
select first_name 员工名称,salary 薪水 
from employees where salary<=3000 order by salary desc

 其他比较条件

操作	含义
between...and...	在两个值之间包含
isset	匹配一个任意值列表
like	匹配一个字符模板
is null	是一个空值

使用between条件

between条件可以用between范围条件显示基于一个值的范围的行。指定的范围包含一个上限和一个下限。between...and...实际上是由Oracle服务器转变为and条件a>=上限anda<=下限所以使用between...and...并没有性能的提高只是逻辑上简单

--between...and..下限,上限
--查询薪水2500~3000的员工
select first_name 员工名称,salary 薪水 
from employees where salary between 2500 and 3500 order by salary desc

--between只是对条件进行简化处理实际上还是会被解析成and....
select first_name 员工名称,salary 薪水 
from employees where salary>=2500 and salary<=3500 order by salary asc

使用in条件

in条件用in条件在指定的一组值中进行选择in...实际上是由Oracle服务器转变为一组or条件a=valuel OR a=value2 OR a=value3所以使用in...并没有得到性能的提高只是逻辑上简单

注意in只能做相等的判断多条件的值最终会被转换成or

--int(...)多个选择
--查询所有经理号为100,101,102的雇员的信息
select manager_id,last_name,salary from employees 
where manager_id in (100,101,102,103)

--or用法
select manager_id,last_name,salary from employees 
where manager_id=101 or manager_id=102 or manager_id=103

使用like条件

使用like条件执行有效搜索串值的通配符搜索

搜索条件既可以包含文字也可以包含数字

• %表示零个或多个字符
• _表示一个占位符字符

        like条件我们也许不能总知道要搜索的确切的值但能够选择那些用like条件匹配一个字符模板的行。字符模板匹配运算涉及通配符查询。有两个符号 %和 _ 可以用来构造搜索串

--like 查询名称带a的
--% 表示零个多个字符-表示一个字符
select first_name from employees where first_name like '%a%';

--名称首字母是a的,注意对大小写敏感
select first_name from employees where first_name like 'C%';

--名称第二个字母是d
select first_name from employees where first_name like '_d%'

escape标识符可以用escape标识符搜索实际的 % 和 _ 符号。使用escape选项该选项指定换码符是什么。如果你想要搜索包含‘sa_’的字符串可以使用escape对\表示该符号为转义符号 

like'%sa\_%'escape'\'; 意思就是把 '\' 定义成转移符号

--escape标识符,转义符号,任意符号都可以设置
--查询employee表的工作id包含sa_的员工姓名

--'_'符号是占位符号不是文本的所有就要设置新的转义字符
select first_name,job_id from employees where job_id like'SA\_%' escape'\'

 使用null条件

null条件null条件中包括 is null 条件和 is not null 条件。is null 条件用于空值测试。空值的意思是难以获得的、未指定的、未知的或者不适用的。因此不能用=判断因为null不能等于或不等于任何值。is not null 测试不是空值

--查询有佣金的员工
select last_name,job_id,commission_pct from employees 
where commission_pct is not null

--查询没有佣金的员工
select last_name,job_id,commission_pct from employees 
where commission_pct is null

逻辑条件关系

运算	含义
and	如果两个组成部分的条件都为真则返回true
or	如果两个组成部分中的任何一个条件为真则返回true
not	如果跟随的条件为假则返回true

逻辑条件逻辑条件组合两个比较条件的结果来产生一个基于这些条件的单个的结果或者逆转一个单一条件的结果。当所有条件的结果为真时返回行

SQL的三个逻辑运算符是and、or、not

and

andand要求两个条件同时为真

--查询工作岗位包含‘MAN’ 并且收入大于等于1000,降序输出
select employee_id,job_id,last_name,salary from employees 
where job_id like'%MAN%' and salary>=10000 order by salary desc

or

oror操作要求两者之一为真即可

--查询工作岗位包含‘MAN’ 或者收入大于等于1000,升序输出
select employee_id,job_id,last_name,salary from employees
where job_id like'%MAN%' or salary>=10000 order by salary asc

not

not取反not运算符也可以用于另一个SQL运算符。例如between、like、null

--查询工作岗不是it_prog,st_clerk,sa_rep的雇员
select last_name,job_id from employees
where job_id!='IT_PROG' and job_id!='SA_REP' and job_id!='IT_PROG'

select last_name,job_id from employees
where job_id not in('IT_PROG','SA_REP','IT_PROG')

运算优先规则

求值顺序数字越小优先级越高	说明
1	算术运算
2	连字运算
3	比较运算
4	isnotnulllikenotin
5	notbetween
6	not逻辑条件
7	and逻辑条件
8	or逻辑条件

优先规则优先规则定义表达式求值和计算的顺序表中列出了默认的优先顺序。可以用圆括号括住想要先计算的表达式来覆盖默认的优先顺序

--查询工作岗位是sa_rep,ad_pres并且它们的薪水大于15000的员工
select last_name,job_id,salary from employees 
where job_id in('SA_REP','AD_PRES') and salary>15000

--查询工作岗位是sa_rep或者 工作岗位是ad_pres并且薪水大于15000的员工
--建议加上括号 可读性更好更严谨预防数据查的到底对不对
select last_name,job_id,salary from employees 
where job_id='SA_REP' or (job_id='AD_PRES' and salary>15000)

SQL语句语法要求

• SQL语句对大小写不敏感
• SQL语句可以写成一行或者多行
• 关键字不能简写或者分开拆行
• 子句通常放在不同的行
• 缩进用于增强可读性 

--遇到百分号怎么办
--显示所有佣金为20%的雇员需要把百分号转换成小数
--三种写法
select last_name,commission_pct from employees
where commission_pct=.2
where commission_pct=0.2
where commission_pct=0.20

SQL空值说明

空值是一个未分配、未知的、或不适用的值

空值不是0也不是空格

空值

如果一行中的某个列缺少数据值则该值被置为空值空值和0或空格不相同0是一个数字空格是一个字符。任何数据类型的列都可以包含空值。可是某些约束如not nullprimary key 可以防止在列中使用空值

算数表达式中的空值

如果算数表达式中有个字段的类型为空值如commission_pct某些员工的佣金为空值则计算结果一定为空

--计算e表中的员工年薪+年佣金

select employee_id,first_name,salary*12*commission_pct from employees order by salary desc,employee_id asc;

 从此表可以看出有些值为空值因为佣金是空值进行运算后也变为空值