Oracle - 基本操作之查询与创建操作

一.Oracle 数据库名称公司的名称（甲骨文）

神谕
优点：
1.适合管理大批量的数据
2.数据有安全保障
3.查找数据效率高
4.支持多用户同时访问
关于客户端/服务器 client/server
Oracle 企业版 快捷版（XE）
数据中存储数据的单位 "表" table
table 结构是 行 Row，列 Column


table  Student
name    age    sex   
sunls 35男
...

二.使用Oracle数据库

客户端：
1.命令行工具（sqlplus）
SQL 是管理数据库数据的一种语言，结构化查询语言的简称。
2.web 界面的管理工具
在浏览器上输入地址：
http://127.0.0.1:8080/apex
http://localhost:8080/apex
3.plsql（第三方工具访问）
4.查询命令
//查询数据库用户中是否有表
select table_name from user_tables;
desc 表名 （非sql命令）
1）基本语法
select 列名1，列名2，...列名n from 表名;
(sql 语句中不区分大小写，后面必须写“;”)
select employee_id from employees;
select employee_id,first_name from employees;

select * from employees;
*代表表中的所有列
*缺点：可能查出不需要的列
2）表别名
select employee_id,first_name from employees;
select e.employee_id,e.first_name from employees e;
语法：
from 表 表别名;
3)列别名
select employee_id empId,first_name name from employees;
select 列 列别名
4）where(条件查询)
select * from employees where employee_id=179;
等于 =
大于 >
大于等于 >=
不等于 != ,<>
逻辑运算符
逻辑与 逻辑或逻辑非
and ornot


//工资大于5000的所有员工信息
select * from employees where salary >5000;
//工资在2000-6000之内的所有员工信息
select * from employees where 5000>salary>2000;//error!
select * from employees where salary>2000 and salary<6000;
//查询员工first_name 叫 james的员工信息
select * from employees where first_name='James';
对于字符串
1)用单引号''表示字符串
2）严格区分大小写。
运算符
//工资在2000-6000之内的所有员工信息
between... and...
select * from employees where salary between 2500 and 6000;
1)小值放在前面
2）包括了=条件

in(值列表 )  in（值1，值2...）
//查询 员工部门为50和60员工信息
select * from employees where department_id=50 or department_id=60;
select * from employees where department_id in(50,60);

like(模糊查询)
//first_name 以 'J'开头的员工
select * from employees where first_name like 'J%';
//first_name 中含有m的员工信息
select * from employees where first_name like '%m%';
//first_name 中第二个字母为a的员工信息
select * from employees where first_name like '_a%';
//first_name 中第二个字母为a且 只有四个字符组成的员工信息
select * from employees where first_name like '_a__';

特殊表：
dual表，只有一行一列
+ - * /
算数函数：
求余数：mod(10,3)
select mod(10,3) from employees;
select mod(10,3) from dual;
select 100+200 from dual;
求绝对值：abs(-1)
select abs(-1) from dual;
随机数：dbms_random.random()
select dbms_random.random() from dual;
日期函数：
to_char(date,'时间的格式字符串')
将日期按照指定的格式转化成字符串
--年--rr,yyyy,year
--月--mm,mon,month
--日--dd,ddsp,ddth,ddspth
--星期--d,dy,day
--小时--hh am. hh24
--分钟--mi
--秒--ss
比较大小：日期只能和日期比较
字符串只能和字符串比较
to_date(str,'时间的格式字符串')
将字符串按照时间格式转化成DB中承认的date类型值
查询详细时间
select to_char(hire_date,'yyyy-mm-dd,day hh24:mi:ss') from employees;
找到1981年入职的员工
解决思路1: (比较字符串)
select * from employees where to_char(hire_date,'yyyy') = '1981';
解决思路2: (比较日期)
select * from employees where hire_date between to_date('1998-01-01','yyyy-mm-dd') 
and to_date('1998-12-31','yyyy-mm-dd');
求字符串长度：length('')
select length(phone_number) from employees;
null值：
select * from employees where commission_pct is not null;
select * from employees where commission_pct is null;
排序： order by
asc  升序(默认)
desc 降序
select .... from .... order by 排序条件(字段名，别名) [asc(默认)/desc]
按照入职时间排序
select * from employees order by hire_date [asc(默认)/desc];
多列排序：
select .... from .... order by 字段1,字段2... [asc(默认)/desc]
先根据字段1排序，若字段1相等，则按照字段2排序
组函数：
1.常见
avg() ---平均值
select avg(salary) from employees;
sum() ---求和
select sum(salary) from employees;
max() ---最大值
select max(salary) from employees;
min() ---最小值
count()--计数器、统计总数
select count(salary) from employees;
select count(commission_pct) from employees;
select count(*) from employees;
注意：count(),只统计有效数据，即非空值。
分组语句： group by
select ... from ... where ... group by ... order by ...
各部门的平均工资：
select department_id,avg(salary) from employees group by department_id;
group by 语法规定： 首尾呼应、前后一致
1) 只有出现在group by里的字段，才能出现在select后面和order by的子句中;
2) 没有出现在group by里的字段，只有配合组函数才能出现在select和order by里面;
3) 如果在group by里的字段应用了单行函数，那么在select后面和order by子句中也要用同样的单行函数
分组条件查询： having
平均工资大于10000的部门
select department_id,avg(salary) from employees group by department_id having avg(salary) > 10000;
执行的顺序：
1.from --确定来源表
2.where--对基础数据的筛选
3.group by--分组
4.having--对分组数据的在筛选
5.select--查询
6.order by --对查询结果的排序
表连接： 左外连接  left [outer] join......on......
select * from Employees left join departments 
on employees.Department_Id = departments.department_id 
where employees.employee_id = 160;
5.建表
create table 表名(
列名1 数据类型,
列名2 数据类型,
列名3 数据类型,
列名4 数据类型
);
create table yueh_student(
s_number number(10) primary key,
name varchar2(10) not null unique,
age number(5) unique,
sex varchar2(2) check(sex in('1','2')),--1代表 男 2 代表女
score number(10),
zip_code number(10) check(length(zip_code)==6),
email varchar2(6) not null,
address varchar2(10),
birthday date,
teacher_id number references yueh_teacher(id)
);
create table yueh_teacher(
id number
);
学生编号
姓名
年龄
性别
成绩
邮编
邮箱
住址
生日
6.常见的数据类型
number 数字类型
varchar2 字符串类型它的长度表示最多能够存储的英文字符数
char 字符串 定长的

varchar2(5)   abc
char(5)  abc__

date 日期类型
7.约束
一种语法：用来保证数据的有效性和完整性
主键约束： primary key 用来保证某一列的取值唯一且不为空
注意：每张表只允许有一个主键
非空约束： not null 用来保证这一列数据必须赋值
唯一约束： unique 用来保证这一列的取值唯一
检查约束：check(length(zip_code)==6)
外键(foreign key)约束：保证两个列的数据必须匹配，有关联关系

references

三.练习 

CREATE TABLE F_CLIENT(    ID NUMBER(12) PRIMARY KEY,--用户编号    USERNAME VARCHAR2(20) NOT NULL,--用户姓名    CLIENT_CERTIFICATE_NO VARCHAR2(20) NOT NULL UNIQUE,--证件号码    BORN_DATE DATE,--出生日期    FAMILY_REGISTER_ADDRESS VARCHAR2(200),--家庭住址    NOW_ADDRESS VARCHAR2(200) NOT NULL,--现在住址    CONTACT_MODE VARCHAR2(50) NOT NULL,--联系方式    URGENCY_CONTACT_MODE VARCHAR2(50) NOT NULL,--紧急联系方式    CREATE_DATE DATE NOT NULL--创建时间);insert into F_CLIENT values(1,'sawyer','1593224056',TO_DATE('1980-10-10','yyyy-mm-dd'),'北京市海淀区东北旺','peaking','123456789','987654321',sysdate); insert into F_CLIENT values(2,'yangly','1593224057',TO_DATE('1980-10-11','yyyy-mm-dd'),'北京市海淀区东北旺','peaking','123456789','987654321',sysdate); insert into F_CLIENT values(3,'gaozhy','1593224058',TO_DATE('1980-12-12','yyyy-mm-dd'),'湖北省洪山区黄家湖','shanghai','123456789','987654321',sysdate); insert into F_CLIENT values(4,'wangmj','1593224059',TO_DATE('1980-04-13','yyyy-mm-dd'),'湖北省洪山区黄家湖','hubei','123456789','987654321',sysdate); insert into F_CLIENT values(5,'lijing','1593224060',TO_DATE('1980-07-14','yyyy-mm-dd'),'湖北省洪山区黄家湖','tianjing','123456789','987654321',sysdate); insert into F_CLIENT values(6,'wangpl','1593224061',TO_DATE('1980-09-15','yyyy-mm-dd'),'北京市朝阳区百子湾','peaking','123456789','987654321',sysdate); insert into F_CLIENT values(7,'lixiao','1593224062',TO_DATE('1980-09-16','yyyy-mm-dd'),'北京市朝阳区百子湾','henan','123456789','987654321',sysdate); insert into F_CLIENT values(8,'zhaoyf','1593224063',TO_DATE('1980-07-17','yyyy-mm-dd'),'北京市海淀区五道口','henan','123456789','987654321',sysdate); insert into F_CLIENT values(9,'suyibo','1593224064',TO_DATE('1980-09-18','yyyy-mm-dd'),'北京市海淀区东北旺','hebei','123456789','987654321',sysdate); insert into F_CLIENT values(10,'luojh','1593224065',TO_DATE('1980-09-19','yyyy-mm-dd'),'北京市朝阳区百子湾','heilongjiang','123456789','987654321',sysdate); insert into F_CLIENT values(11,'mazhb','1593224066',TO_DATE('1980-08-10','yyyy-mm-dd'),'北京市海淀区东北旺','peaking','123456789','987654321',sysdate); insert into F_CLIENT values(12,'gaohf','1593224067',TO_DATE('1980-08-18','yyyy-mm-dd'),'北京市朝阳区百子湾','peaking','123456789','987654321',sysdate); insert into F_CLIENT values(13,'chenb','1593224068',TO_DATE('1980-08-13','yyyy-mm-dd'),'北京市海淀区西二旗','peaking','123456789','987654321',sysdate); --一.运行以上语句，创建客户表，完成以下操作。--1)查询所有客户select * from F_CLIENT;--2)查询所有客户的编号以及证件号码select ID,CLIENT_CERTIFICATE_NO from F_CLIENT;--3)打印所有的用户名及出生日期select username,born_date from F_CLIENT;--4)查询所有住在‘北京市海淀区东北旺’的用户的名称、出生日期以及现在住址select username,born_date,FAMILY_REGISTER_ADDRESS from F_CLIENT where FAMILY_REGISTER_ADDRESS='北京市海淀区东北旺';--5)查询所有现居住在‘peaking’的用户select username,born_date,FAMILY_REGISTER_ADDRESS from F_CLIENT where FAMILY_REGISTER_ADDRESS='peaking';--6)查询所有证件号码尾数小于‘7’的用户select * from F_CLIENT where substr(CLIENT_CERTIFICATE_NO,length(CLIENT_CERTIFICATE_NO),length(CLIENT_CERTIFICATE_NO))<7;--7)查询所有证件号码尾数大于‘6’且家庭住址在‘北京市海淀区东北旺’的用户select * from F_CLIENT where substr(CLIENT_CERTIFICATE_NO,length(CLIENT_CERTIFICATE_NO),length(CLIENT_CERTIFICATE_NO))>6 and FAMILY_REGISTER_ADDRESS = '北京市海淀区东北旺';--8)查询所有出生在‘1980-07-15’到‘1980-09-15’之间的用户select * from F_CLIENT where BORN_DATE between to_date('1980-07-15','yyyy-mm-dd') and to_date('1980-09-15','yyyy-mm-dd');--8)查询所有住在‘北京市海淀区’的用户select * from F_CLIENT where FAMILY_REGISTER_ADDRESS like '%北京市海淀区%';--9)查询所有证件号码以‘7’结尾的用户select * from F_CLIENT where CLIENT_CERTIFICATE_NO like '%7';--经营信息表CREATE TABLE F_OPERATION(  ID NUMBER(12) PRIMARY KEY,--资产编号  CLIENT_ID NUMBER(12)  REFERENCES F_CLIENT(ID),--用户编号  OPERATE_INFOMATION_DESCRIBE VARCHAR2(120) NOT NULL,--资产信息描述  POSSESS_SHAREHOLDING NUMBER(4,2) NOT NULL,--持股比例  OPERATE_ADDRESS VARCHAR2(50) NOT NULL,--经营地址  YEAR_AVG_INCOME VARCHAR2(50) NOT NULL,--年收入  BEGIN_OPERATION_TIME DATE NOT NULL,--开始经营日期  EMPLOYEES_AMOUNT NUMBER(6) NOT NULL,--员工总数  LEGAL_PERSON_NAME VARCHAR2(20) NOT NULL,--法人姓名  LEGAL_PERSON_CRD_NUMBER VARCHAR2(50) NOT NULL,--法人证件号码  OPERATE_CRD_NUMBER VARCHAR2(50) NOT NULL,--经营证件号码  UPDATE_TIME DATE  NOT NULL--更新时间)insert into F_OPERATION values(1,1,'是一份资产的信息',0.33,'北京市海淀区东北旺','10000000',TO_DATE('1999-09-10','yyyy-mm-dd'),100,'yangll','1567843925','1567843925',sysdate); insert into F_OPERATION values(2,2,'是一份资产的信息',0.50,'北京市海淀区西二旗','100000000',TO_DATE('1999-04-17','yyyy-mm-dd'),500,'yangdd','1567678978','1567678978',sysdate); insert into F_OPERATION values(3,1,'是一份资产的信息',0.20,'北京市海淀区东北旺','15000000',TO_DATE('1999-09-18','yyyy-mm-dd'),70,'yangmm','15468756778','15468756778',sysdate); insert into F_OPERATION values(4,2,'是一份资产的信息',0.16,'湖北省洪山区黄家湖','18000000',TO_DATE('1999-10-20','yyyy-mm-dd'),10,'wangss','68743368594','68743368594',sysdate); insert into F_OPERATION values(5,4,'是一份资产的信息',0.09,'北京市海淀区东北旺','10000000',TO_DATE('1997-09-05','yyyy-mm-dd'),120,'sangbb','47389434043','47389434043',sysdate); insert into F_OPERATION values(6,1,'是一份资产的信息',0.05,'湖北省洪山区黄家湖','100000000',TO_DATE('1999-06-13','yyyy-mm-dd'),1000,'lihh','90893472119','90893472119',sysdate); insert into F_OPERATION values(7,7,'是一份资产的信息',0.50,'北京市海淀区东北旺','10000000',TO_DATE('1998-08-19','yyyy-mm-dd'),19,'hehe','15654783034','15654783034',sysdate); insert into F_OPERATION values(8,13,'是一份资产的信息',0.70,'北京市朝阳区百子湾','1000000',TO_DATE('1999-09-10','yyyy-mm-dd'),40,'heihei','15678445645','15678445645',sysdate); insert into F_OPERATION values(9,4,'是一份资产的信息',1.00,'北京市海淀区东北旺','1000000',TO_DATE('1999-09-10','yyyy-mm-dd'),80,'hahaha','15098239749','15098239749',sysdate); --运行以上语句，创建资产表，完成以下操作。--1)查询所有经营信息select * from F_OPERATION;--2)查询所有1号客户的经营信息select * from f_operation where CLIENT_ID=1;--3)查询所有1号客户且年收入大于10000000的经营信息select * from f_operation where client_id=1 and YEAR_AVG_INCOME>10000000;--4)查询所有位于‘北京市海淀区东北旺’的经营信息select * from f_operation where OPERATE_ADDRESS='北京市海淀区东北旺';--5)查询所有持股比例大于0.20的经营信息select * from f_operation where POSSESS_SHAREHOLDING>0.20;--6)查询所有2号客户的经营信息，以及他的年收入select * from f_operation where client_id = 2;select YEAR_AVG_INCOME from f_operation where client_id = 2;--7)查询所有位于‘黄家湖’的经营信息select * from f_operation where OPERATE_ADDRESS like '%黄家湖%';--8)查询所有成立于1999年的经营信息select * from f_operation where BEGIN_OPERATION_TIME between to_date('1999-01-01','yyyy-mm-dd') and to_date('1999-12-31','yyyy-mm-dd');--8)查询所有法人姓名以‘yang’开头的经营信息select * from f_operation where LEGAL_PERSON_NAME like 'yang%';--9)查询年收入最高的经营信息select * from f_operation where YEAR_AVG_INCOME=(select max(YEAR_AVG_INCOME) from f_operation);--10)查询所有的经营信息以及其所属的客户select * from f_operation f left join  f_client c on f.client_id=c.id;--11)查询所有1号客户的信息以及经营信息select * from f_operation f left join f_client c on f.client_id=c.id where c.id=1;--资产表CREATE TABLE F_ASSET(    ID NUMBER(12) PRIMARY KEY,--资产编号    CLIENT_ID NUMBER(12) FOREIGN KEY REFERENCES F_CLIENT(ID),--用户编号    ASSET_NAME VARCHAR2(20) NOT NULL,--资产名称    ASSET_DESCRIBE VARCHAR2(150),--资产简介    ASSET_ADDRESS VARCHAR2(150) NOT NULL,--资产所在地    ASSET_CREDENTIALS_NAME VARCHAR2(50) NOT NULL,--    ASSET_CREDENTIALS_NUMBER VARCHAR2(50) NOT NULL,  ASSET_MARKET_VALUATION NUMBER(12,2) NOT NULL,  PLEDGE NUMBER(1) NOT NULL,  UPDATE_TIME DATE NOT NULL--更新日期)--添加十条资产数据--授信信息表CREATE TABLE F_CREDIT(  ID NUMBER(12) PRIMARY KEY,  CLIENT_ID NUMBER(12) REFERENCES F_CLIENT(ID),--用户编号  NOW_CREDIT_LIMIT NUMBER(11,2) NOT NULL,  FROZEN_LIMIT NUMBER(11,2) NOT NULL,  LIMIT_SPACE NUMBER(11,2) NOT NULL,  UPDATE_TIME DATE NOT NULL)--学生表 ： create table y_student(  id number(10) primary key,  sname varchar2(20) not null,  spass varchar2(20) not null check(length(spass)=8),  sex varchar2(2) not null,  email varchar2(20),  zip_code number(10) check(length(zip_code)=6),  address varchar2(20),);