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

原创

原小明呢 2023-05-07 15:47:58 ©著作权

文章标签 oracle 基本操作字符串数据字段 文章分类 Python 后端开发

©著作权归作者所有：来自51CTO博客作者原小明呢的原创作品，请联系作者获取转载授权，否则将追究法律责任

一.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 or not

//工资大于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),
);