Oracle SQL DEVELOPER设置:

工具->首选项->代码编辑器->字体

工具->首选项->代码编辑器->完成设置->弹出速度

复习

创建用户:create user anna identified by anna;

给用户授权:grant connect, resource to anna;

修改密码:alter user anna identified by anna;

alter user sys identified by root;

删除用户:drop user anna cascade;

以下熟记:

修改当前会话的日期格式为英文

(nls: native language support)

alter session set nls_date_language = english; --要求牢记

select sysdate from dual;

SELECT to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') 系统时间 from dual; --熟记

约束

约束使用关键字constraint,有下面5种约束

not null 非空约束

check 检查约束

unique  唯一约束

primary key  主键约束

foreign key  外键约束

null:空值,不存在的意思

not null 非空约束: 必须提供值,不能为空

check 检查约束: 限定列的取值范围,例如限制性别只能是'male'或者'female'

unique  唯一约束: 限定列的值不能重复,但是可以为NULL(可以没有值)

primary key  主键约束: 不能重复,并且不能为NULL(不能为空)

一般不使用管理员账号添加数据

--创建表

create table t_temp1(

    name varchar2(100) PRIMARY key,

    sno number(9) unique,

    -- 0表示女,1表示男

    gender number(1) DEFAULT 0 check(gender=0 or gender =1),

    age number(3),

    class_no number(3)

);

约束:也是一种对象,可以使用默认名称,也可以人为命名

查询当前用户下面的约束

select constraint_name from user_constraints;

下面是默认命名的约束,都是以SYS_开头的

SYS_C006997

SYS_C006998

SYS_C006999

给约束起名: constraint 约束名 约束类型

建议约束的命名方式 表名_列名_约束类型缩写

常用缩写pk primary key, uk unique, ck check, nn not null, fk foreign key

create table t_temp2(

name varchar2(100) constraint t_temp2_name_pk  primary key,

sno number(9) constraint t_temp2_sno_uk  unique,

gender number(1) default 0 constraint t_temp2_gender_ck check(gender=0 or gender=1),

age number(3),

class_no number(3)

);

约束可以分为列级约束和表级约束

列级约束:上述五种约束都可以使用列级约束,紧跟在列的后面

表级约束:除了非空约束,其余四种都可以作为表级约束;表级约束单独写一行,指定要修饰的列

列级约束和表级约束的功能是一样的,只是书写的位置不同

查看所有约束

select * from user_constraints;

--查询外键约束

select * from user_constraints where constraint_type = 'R';

关闭约束

alter table table_name disable constraint 约束名;

打开约束（启用约束)

alter table table_name enable constraint  约束名;

外键约束 foreign key

外键约束 foreign key 表示表与表之间的这样一种关系,如果一个表的某个列关联到另外一个表的某个列(primary key或unique),可以添加外键约束;关联的列只能取被关联的列存在的值或者为空(null).

被引用的表称为父表,外键所在的表称为子表

创建表的时候先创建父表,再创建子表

表与表之间的关系:

(1)一对一关系:例如丈夫和妻子的关系,外键无论加在丈夫表,妻子表都行

(2)一对多关系:

外键加在多的这一侧.例如学生和成绩的关系,一个学生可以有多个成绩,但是一个成绩只能对应一个学生;外键加在成绩表这一侧.

(3)多对多关系:

学生和老师的关系: 一个同学对应多个老师,一个老师对应多个同学,这样就是多对多的关系.

建立表时,通常会建立一个中间的桥梁表

学生表-选课表-老师表

学生-选课:一对多关系,一个学生可选修多门课程,外键加在选课表

老师-选课:一对多关系,一个老师可教授多门课程,外键加在选课表

数据库范式(通常设计数据库只要满足第三范式即可)

(1)第一范式:列的值应该是原子性的,不可再分的,例如地址:省市区街道门牌号楼层房间联系人电话,应该拆分为多个字段,不能整体作为一个字段,而是拆分为:省,市,区,街道,门牌号,楼层房间,联系人,电话

(2)第二范式:每个表都应该添加与业务无关的列作为主键.例如不使用学号作为学生表的主键

(3)第三方式:当一个表关联到另外一个表时,不要引用除了主键以外的其它字段

注意:范式越高意味着表的划分更细，一个数据库中需要的表也就越多，用户不得不将原本相关联的数据分摊到多个表中。

数据的添加

(1)语法1:

insert into 表名(列名1,列名2 ... 列名n) values (值1,值2 ... 值n);

注意点:

 1. 列名的顺序没有要求

2. 值的顺序要求与列名的顺序一一对应

3. 插入数据以后需要手动提交,执行commit;来提交数据的修改

(要么提交数据commit,要么回滚数据ROLLBACK)

不仅仅是插入数据需要commit;增删改都需要commit;提交数据

(2)语法2:

insert into 表名

values (值1,值2 ... 值n);

如果表名后面省略了(列名1,列名2 ... 列名n),那么直接指定值的时候需要注意一个列都不能少,而且必须和定义表的顺序是一致的.

查询

SELECT * from 表名;

查询表中的所有列的数据,*代表所有的列,一般不推荐这样的写法.为什么不推荐?有下面2个原因:

1.效率不高,可读性低

2.容易造成潜在编程错误,例如数据库中表是Student(id,name,age)对应着一个Java类Student(String id,String name,int age),后来对数据的表进行了修改,添加或删除了几个列,如果使用"SELECT * from 表名"就不能和Java类完全对应了.

查询当前时间

select sysdate from dual;

sysdate表示当前系统日期时间,date类型

dual是Oracle中的一个虚表

select 2+3 from dual;  --正确的

select 2+3; --错误的,没有写from dual

熟记

修改当前会话的日期格式为英文

nls: native language support

alter session set nls_date_language = english; --要求牢记

select sysdate from dual;

SELECT to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') 系统时间 from dual; 

导入数据

导入数据

1.留心当前使用的用户,不要将数据导入到管理员账号下

2.日期时间格式设置为英文格式

alter session set nls_date_language = english;

3.导入脚本: start 文件路径 或者 @文件路径

注意路径中不要包含中文,空格,特殊字符

show user;

alter session set nls_date_language = english;

start d:/anna.sql

如果出现问题,将用户anna删除,重写导入

基本查询

select 查询列表 from 表名;

1.select 和 from 是查询语句必须有的两个子句;

2.执行流程: from确定表->select查询列或表达式

3.select * from 表名; 表示查询所有的列,可读性不高,效率也不高

4.列,数值,日期型可以做算术运算,加减乘除等

5.列值如果为空(NULL),空值参与运算,结果仍然为空

6.空值转换函数,NVL(表达式1,参数2),如果表达式1不为空,取表达式1本身的值; 如果表达式1为空,取参数2的值

7.查询时可以使用AS关键字给列取别名,AS关键字可以省略不写

8.如果别名中有空格或特殊字符,必须使用双引号括起来

9.拼接字符串使用符号||

10.distinct 关键字用来去除重复数据

11.可以直接查询常量表达式,为了满足select...from...的语法,使用dual表示虚表

单引号和双引号的区别

--Oracle中用单引号表示字符串,双引号用于起别名

正确的:select id, first_name||' '||last_name as "姓 名" from s_emp;

错误的: select id,first_name||""||last_name as "姓 名" from s_emp;

错误的: select id,first_name||''||last_name as '姓 名' from s_emp;

NVL函数  NVL(值1,值2) 如果值1存在,使用值1;值1为null,选用值2

select id, first_name||' '||last_name "姓 名", salary * (1 + NVL(commission_pct,0)/100) income

from s_emp;

排序

order by 排序,语法如下:

select 查询列表

from 表名

order by 列1 asc|desc, 列2 asc|desc ...;

1.执行流程:from确定表->select查询列表->order by对结果集进行排序

2.order by 永远是最后执行,语句也是查询语句的最后

3.asc升序,desc降序,如果asc和desc都省略不写,默认就是升序排序

条件语句

(1)使用where关键字后面加上条件语句,进行行筛选. 语法如下:

select 查询列表

from 表名

where 条件语句

order by 排序;

执行流程:from确定表->where行筛选->select确定列->order by对结果集进行排序;

(2)

在条件语句中可以进行条件运算,常见运算符如下:

比较运算符: < <= > >= = != <>(不等于)

between...and... 表示在两个值之间

is null 表示值为空,如果需要判断值为空一定要使用is null, 千万不要使用=null, 或者 == null

like 模糊匹配

_ 表示任意的一个字符

% 表示0个或多个任意字符

关键字 escape,表示转义,等下举例说明

not 表示取反

not between 值1 and 值2 表示值不在区间[值1,值2]之内

not in(值1,值2....值n)  表示不等于列出这些值中

is not null 表示非空

not like 不匹配

and 表示并且,条件1 and 条件2,条件1和条件2都成立,结果才成立

or  表示或者,条件1 or 条件2,条件1和条件2中只要有一个成立,结果就成立

优先级: not > and > or

--查询没有提成的员工信息

select id, first_name||' '||last_name emp_name, salary,commission_pct

from s_emp

where commission_pct is null; --正确的

!!!Oracle不区分大小写:书写SQL语句不区分大小写,但是文本字符串的内容是区分大小写的

--查询员工的first_name以b或者B开头的有哪些?

select first_name

from s_emp

where first_name like 'b%' or first_name like 'B%';

--查询first_name包含字母A的员工信息

select first_name

from s_emp

where first_name like '%A%';

--在MYSQL中utf8mb4_general_ci不区分大小写,utf8mb4_bin区分大小写

(重点内容!!!!!!!!)序列

(1)序列的概念:序列使用关键字sequence,它是一个序列号生成器,可以自动生成序列值,产生一组等间隔的数值.其主要作用是生成表的主键,可以在插入或其它语句中使用,也可以通过查询获取当前值,或者使序列产生下一个值.序列不占用实际的存储空间.

(2)创建序列的语法如下,中括号部分可以省略:

create sequence sequence_name

[increment by 1|n]

[start with n]

[maxvalue n|nomaxvalue]

[minvalue n|nominvalue]

[cycle| nocycle]

[cache n|nocache]

[order|noorder];

序列语法的说明:

sequence_name:指出创建的序列名称,需要遵守标识符命名规则;

increment by:定义序列增长的步长,默认是1.n必须是一个整数,如果n是负值,代表序列值是按照指定步长递减;

start with:指定序列的起始值,如果不指定,默认是1;

maxvalue:指定序列的最大值,nomaxvalue是默认选项,代表没有最大值限制;

minvalue:指定序列的最小值,nominvalue是默认选项,代表没有最小值限制;

cycle和nocycle:指定该序列在到大最大值或最小值之后是否循环;cycle表示循环,nocycle表示不循环.如果循环,当序列到达最大值时,继续循环使用最小值;当序列递减到最小值时,循环使用最大值;如果不循环,到达限制的最大值或最小值时,继续产生新的值就会发生错误.

cache和nocache:指出数据库是否在内存中预分配一定数量的序列值进行缓存,预分配的序列值可以提高访问速度,提高性能.参数n的最小值是2,默认值是20.

order和noorder:指出是否按照请求顺序生成序列号,默认为noorder

下面举例讲解序列:

首先创建一个序列:

create sequence books_seq

start with 10

increment by 1

maxvalue 100000

cache 10

nocycle;

再创建表:

create table books(

  bookid number(6) primary key,

  booknum varchar2(11),

  bookname varchar2(100),

  author varchar2(20),

  publish varchar2(50),

  bookprice number(6,2),

  category varchar2(20),

  booktime date

);

创建序列后,可以通过序列的两个属性currval和nextval来引用序列的值,它们分别用来获取序列的当前值和下一个值.调用方式为: 序列对象.currval 或 序列对象.nextval

经常使用序列来生成主键的值

--第一次运行books_seq.nextval,初始化序列,第一次获取的值等于start with 后面的值

insert into books

values (books_seq.nextval,'1001','c语言入门','Anna','人民邮电',55.5,'计算机',sysdate);

insert into books

values (books_seq.nextval,'1002','java语言入门','Bill','人民邮电',80,'计算机',sysdate);

(3)修改序列

使用alter sequence语句对序列进行修改,序列的修改有下面的限制:

1.不能修改序列起始值;

2.序列的最小值不能大于当前值CURRVAL;

3.序列的最大值不能小于当前值CURRVAL;

例如修改序列books_seq_temp

alter sequence books_seq_temp increment by -10;

alter sequence books_seq maxvalue 999999;

(4)删除序列

使用drop sequence 语句来删除序列

drop sequence books_seq_temp;

多表查询

select 列1,列2...

from 表1,表2...

where 关联条件

and 其它条件;

下面是一些注意事项:

1.from 表,如果不写关联条件,会产生笛卡尔积;如果只有1张表,笛卡尔积是其本身;如果有2张表,笛卡尔积是列相加,行相乘;

2.两张表至少需要一个关联条件,n张表至少需要n-1个关联条件;

3.如果出现同名的列,例如s_emp中有id,s_dept中也有id,需要做区分,通过表名.列名的方式来区分;

4.在多表查询时,建议给每个字段都加上表名作为前缀,可以提交效率,增加可读性;

5.如果表名较长,可以给表取别名,一旦取了别名就只能使用别名,不能使用原来的表名了.

  select a.列1,a.列2...b.列1,b.列2...

  from 表1 a, 表2 b

  where a.m = b.n ;

6.给表起别名时,不能使用AS关键字

连接查询

连接查询可以分为两类:内连接和外连接(左外连接,右外连接,全外连接)

内连接inner join:列出数据表中与连接条件相匹配的数据行,只有满足条件的记录才会出现在查询的结果集中

外连接outer join,可以分为left outer join , right outer join 和 full outer join

left outer join 左外连接,简称左连接,获取左表所有的记录,即使右表没有对应的匹配记录,表现为NULL

right outer join 右外连接,简称右连接,获取右表所有的记录,即使左表没有对应的匹配记录,表现为NULL

full outer join 基于等价连接,同时包含左表和右表没有查询出来的记录

左外连接:基于等价连接(内连接)的结果,同时包含左表没有匹配的记录,在Oracle中有两种语法

标准语法:

select 查询列表

from 左表 left [outer] join 右表

on 关联条件

where 条件语句

and 其它条件;

Oracle特色语法:

select 查询列表

from 表1, 表2

where 表1.字段名=表2.字段名(+)

and 其它条件;

右外连接:基于等价连接(内连接)的结果,同时包含右表没有匹配的记录,也有两种语法

标准语法:

select 查询列表

from 左表 right [outer] join 右表

on 关联条件

where 条件语句

and 其它条件;

Oracle特色语法:

select 查询列表

from 表1,表2

where 表1.字段名(+)=表2.字段名

and 其它条件;

左外连接和右外连接可以相互转换,一般使用左外连接;

全外连接,简称全连接

full [outer] join 基于等价连接,同时包含左表和右表没有查询出来的记录

只有标准语法:

select 查询列表

from 左表 full outer join 右表

on 关联条件

where 条件

and 其它条件;

内连接:和上面讲的等价连接是效果一样的

语法1:

select 查询列表

from 表1 inner join 表2

on 关联条件

where 条件语句

and 其它条件;

语法2:

select 列1,列2...

from 表1,表2...

where 关联条件

and 其它条件;

内连接有一种特殊情况,叫自连接,自己这张表和自己关联

外键是引用的自己表的列,通过这个外键寻找关系,就需要自连接

--查询每个员工的姓名,以及其领导的姓名

--总裁Carmen,是老大,没有领导,可以使用左外连接; 这张表是自己和自己关联,称为自连接

select e1.first_name|| ' ' ||e1.last_name emp_name, e2.first_name|| ' ' ||e2.last_name manager_name

from s_emp e1 left outer join s_emp e2

on e1.manager_id = e2.id;

--查询入职时间早于其领导的员工

--例如: 张三先入职,然后李四在后面入职的,李四做了张三的领导

select e1.FIRST_NAME||' '||e1.LAST_NAME emp_name, e1.START_DATE emp_start_date,

      e2.FIRST_NAME||' '||e2.LAST_NAME manager_name, e2.START_DATE manager_start_date

from S_EMP e1 , S_EMP e2

where e1.MANAGER_ID = e2.ID

and e1.START_DATE < e2.START_DATE;