oracle_oracle 某字段和另一张表中某字段比较是否为开头-优快云博客

本文全面总结了Oracle基础语法，涵盖建表、数据类型、删除表等基础操作，介绍了主键约束等约束条件，还阐述了查询相关内容，如distinct关键字、where子句等，同时给出了在Navicat中的操作快捷命令、SQL Select语句执行顺序以及Oracle存储过程中is和as的区别。

Oracle基础语法总结

1 基础

SQL功能	命令动词
数据定义	create,drop,alter
数据操纵	select,insert,update,delete
数据控制	grant,revoke,commit,rollback

数据定义语句

操作对象	创建	删除	修改
表	create table	drop table	alter table
视图	create view	drop view
索引	create index	drop index

SQL中的数据定义主要针对数据库三级模式结构所对应的各种数据对象进行定义的,标准SQL中的数据对象主要是表,视图,索引.当然再oracle中还有触发器,游标,过程,程序包等.
视图是基于表的虚表,索引依附在基表上,因此视图和索引不提供修改操作,想修改只能通过删除再创建,但是再Oracle中可以通过alter view对视图进行修改

1.1 建表的语法

create table 表名(  
　字段名 字段类型 [约束 默认值],  
　...  
　字段名 字段类型 [约束 默认值],
  [表级完整性约束条件]  
)

注意
- 数据一定是区分大小写的
- 表名、字段名(列名)、关键字大小写不敏感
- 表名、字段名不能使用关键字
- 表名、字段名不能以数字开头，中间不能出现特殊符号
- 表名、字段名长度不能超过30个字符
- 表名、字段名定义需要有含义

1.2 创建表t_user举例

姓名 name, 字符串
性别 gender, 字符串
年龄 age, 数字型

create table t_user(
    name varchar2(30),
    gender varchar2(5),
    age number(4)
)

注意
- desc命令需要在命令窗口执行
desc t_user

1.3 Oracle中常见的数据类型

字符串类型

varchar2(size type):size最大为4000，type可为char或者byte，默认是byte, varchar2最多存放4000字节的数据，不管type是char还是byte。所以如果你设置varchar2(4000 char)，你可以存入4000个字母，但是不能存入4000个汉字。如果数据库字符集编码是GBK，那么varchar2最多能存放2000个汉字，如果字符集编码是UTF-8，那么则只能最多存放1333个汉字了。
nvarchar2(size):size最大值为2000，单位是字符，而且不管是汉字还是字母，每个字符的长度都是2个字节。所以nvarchar2类型的数据最多能存放2000个汉字，也最多只能存放2000个字母。并且nvarchar2不同于varchar2,他不受数据库字符集的影响。n 表示 Unicode 字符，即所有字符都占两个字节，nchar，nvarchar
varchar(length):基本等价于varchar2, 没什么太大的区别,varchar2用null代替了varchar的空字符串,且varchar2是Oracle独有的

BYTE和CHAR有什么区别?

BYTE,用字节指定：VARCHAR2(10 BYTE)。这能支持最多10字节的数据，在一个多字节字符集中，这可能只是两个字符。采用多字节字符集时，字节与字符并不相同。
CHAR,用字符指定：VARCHAR2(10 CHAR)。这将支持最多10字符数据，可能是多达40字节的信息。另外，VARCHAR2(4000 CHAR)理论上支持最多4000个字符的数据，不过由于Oracle中字符串数据类型限制为4000字节，所以可能无法得到全部4000个字符。

总结:英文字符最好使用varchar2,中文字符最好使用nvarchar2,使用varchar2时最好默认,如varchar2(10),表示可以存储10个字节的数据,10个英文字符,5个中文字符.而nvarchar2(10),表示可以存储20个字节的数据,或者说是10个字符的数据,也就是无论中英文都是存十个字符.

数字类型

number(p,s):p代表数字的长度(不包括小数点),s表示小数点后面的尾数长度.
举例: number(5, 2), 表示小数点前面有三个数字,后面两个数字, 像 122.22/12.11都是合法的, 而1234.10/1.241都是不合法的

日期类型

date:七个字节, 英文DD-MOR-RR "11-JUN-15"中文11-6月-15

1.4 删除表 drop table 表名

1.5 创建员工表

create table staff(
    id number(11),
    name nvarchar2(15) not null,
    salary number(10, 2),
    birth date,
    job varchar2(30),
    gender char(3) default 'm'
)
/*设置了
姓名不为空
性别默认为m*/

2 约束条件

主键约束:primary key

主键约束注意事项

一张表中只能有一个主键约束
主键约束不能为空且具有唯一性
关系型数据库中使用主键约束唯一标识一行数据
主键约束分为两类:行级约束, 表级约束

<!-- 建表时定义的主键约束 -->
create table 表名(
    字段名 类型 primary key, /*列级约束只能作用一个字段*/
    ...

)

<!-- 建表时定义表级约束 -->
create table 表名(
    字段1 类型,
    字段2 类型,
    ...
    constraint 表名_字段名_PK primary key(字段1, 字段2)/* 字段1和字段2联合主键*/
)

3 查询

//查询employees表中的所有数据
select * from employees;

//查询方案hr的countries表中的所有的行数据,该方案再安装Oracle时就已经自动创建了,可以直接使用.
select * from hr.countries;

//同上
select region_id,country_name from hr.countries;

//对表中的所有人员的薪金增加10%
select employee_id, first_name, last_name, salary*(1+0.1) from hr.employees;

//指定了新工资的列名
select employee_id, first_name, last_name, salary*(1+0.1) new_salary from hr.employees;

3.1 distinct关键字

//加入distinct关键字可以筛选重复数据,比如像看部门的id, 不加distinct由于多人属于同一部门就会出现很多重复的部门id号.
select distinct department_id from hr.employees;

3.2 where子句

判断语句中A!=B或A<>B表示若A的值不等于B
A LIKE B表示A的值匹配到B的值,则为真, 在Oracle的通配符%代表 0个,1个或多个任意字符,"_"代表一个任意字符.
NOT运算符用于对判断条件取反是否符合

//查询first_name列中以B开头的雇员
select employee_id,first_name, last_name from hr.employees where first_name like 'B%';

3.3 连接运算符and/or

//查询出所有属于it部门(department_id=60),且薪金大于2000的雇员.注意等于号!!!
select employee_id, first_name, last_name,salary from hr.employees where departmen_id=60 and salary>2000;

3.4 关于NULL

insert into hr.departments(department_id,department_name, manager_id) values(300, '数据库', NULL);
//查不到数据,=判断方式错误
select department_id,department_name,manager_id from hr.departments where manager_id = NULL;
//这才是正确操作
select department_id,department_name,manager_id from hr.departments where manager_id is NULL;

3.5 order by

//对查询到的结果进行排序输出,ASC按照升序,DESC按照降序
select column_list from table_name order by[{order_by_expression[ASC|DESC]}...]

// 查询工资升序排列,从上往下升.当然不加asc也行,因为默认是就是asc
select employee_id, first_name,last_name, salary from hr.employees where salary>2000 order by salary asc;

//如果order by制定了多个列名,首先根据第一列进行排序,当第一列的值相同时,在对第二列进行比较排序,
select last_name, job_id, salary from hr.employees where salary>2000 order by job_id,salary desc;

3.6 group by

用于在查询结果中对记录进行分组,以汇总数据或者为整个分组显示单行的汇总信息.

//这种就是先把employees表按照job_id进行分组,之后根据每个组计算相应的平均工资, 工资总和,最大工资和每个组的记录数
select job_id, avg(salary), sum(salary), max(salary), count(job_id) from employees group by job_id;

//错误示例, select后出现了salary,但是却没出现在group by子句后,所以该句是错误的
select job_id,salary, avg(salary), sum(salary), max(salary), count(job_id) from employees group by job_id;

//在group by中还可以使用运算符rollup/cube,功能是在查询结果中附加一行汇总信息
select job_id, avg(salary), sum(salary). max(salary), count(*) from employees group by rollup(job_id);

注意使用groupby子句必须满足下列条件:

在select子句后面只可以有两类表达式:统计函数和进行分组的列名.
在select子句中的列名必须是进行分组的列,但是group by子句后面的列名可以不出现在select子句中
默认情况下,是按照升序排列,想要从新排序,可以使用order by子句进行新的排序.

3.7 having 子句

having通常与group by子句一起使用,在完成对分组结果的统计后,可以使用having对分组的结果做进一步的筛选.如果不适用group by时,having的功能和where子句一样.不同之处就在于having与组相关,而where是与单个的行相关.
如果没有使用group by 而是在where子句后面直接使用了having,那就将having子句应用到where子句的输出上,将整个输出看作一个组.
如果没有指定where子句,也没指定group by,那么having子句将应用于from子句的输出,并将其看作是一个组.
//列出平均薪资大于100000的工作信息
select job_id, avg(salary), sum(salary), max(salary), count(*) from employees group by job_id having avg(salary)>10000;

3.8 多表连接查询

为了避免笛卡尔乘积造成的资源浪费,需要加上where限定条件
select employee_id, last_name, department_name from employees,departments where employees.department_id=departments.department_id order by EMPLOYEE_ID asc;
表别名注意的问题
select em.employee_id, em.last_name, dep.department_name from employees em, departments dep where em.department_id=dep.department_id and dep.department_name='Shipping';
以上简便了输入
select employees.employee_id, employees.last_name, dep.department_name from employees em, departments dep where em.department_id=dep.department_id and dep.department_name='Shipping';
现在就会报错,这和select语句先后执行的顺序有关,先执行from, employees就已经被代替成em了,最后再执行select时就会出错
join连接
除了逗号之外还可以使用join
from join_table1 join_type join_table2
连接包括内连接,自然连接,外连接,自连接.最常用的是内连接也就是inner join,也可省略join就可表示内连接. on是连接的条件
//使用内连接查询雇员信息和雇员所在的部门名称
select em.employee_id, em.last_name, dep.department_name from employees em inner join departments dep on em.department_id=dep.department_id where em.job_id='AD_ASST';

完整的顺序

select [all|distinct] from table_name where** group by** having** order by**

常用的统计函数

函数	描述
count	返回找到的记录数
min	返回一个数字列或者计算列的最小值
max	返回一个数字列或者计算列的最大值
sum	返回一个数字列或者计算列的总和
avg	返回一个数字列或者计算列的平均值