Oracle数据库和PLSQL可视化工具学习笔记

PLSQL指的是Plsqldeveloper，他是Oracle数据库客户端连接工具。PLSQL代码能在不同开发体系中使用，能够加快业务应用系统的开发过程，降低在不同的系统中重复开发相同模块的可能，现在用Oracle存储数据的项目，基本都是用PLSQL对Oracle数据表进行可视化操作。

2.1、创建表

格式：

create table 表名(

字段名1 类型 约束,

字段名2 类型 约束，

字段名3 类型 约束，

……

)

示例：在Oracle数据库中创建一张名为test的表，有9个字段，需要包括日期型、字符串、数字、文件(图片、音频……)等类型，该表需要建立在system表空间上。注：字段名字可以任意取，字段类型可重复使用，但是上述每个字段至少使用一种。

2.2、创建表常用的数据类型

2.2.1 数字类型型

整数 number(整数位)，只写number，表示无限制；

小数 number(总长度，小数位)。

上面的创表语句中：

id number是指id这个字段支持无数位的整数。

grade number(5,2)是指总长度为5位数，其中2是两位小数。

2.2.2 字符串类型型

字符串分为定长类型char和变长类型varchar。

（1）定长类型char

char的长度是固定的，比如说，定义了char(10)，即使只是在数据库中输入abc，不足10个字节，数据库也会在abc的后面自动自动加上7个空格，以补足10个字节。

char是区分中英文的，一个中文占2字节，一个英文只占1个字节。

char适用于长度比较固定的，一般在不包含中文情况下使用。

（2）变长类型varchar

变长类型的长度是不固定的，比如说，上面创建表的案例中varchar(500)，插入abc，则在数据库中只占3个字节。

varchar (500)，是指可以在address字段下存入250个汉字，英文字符是500个。

2.2.3 日期类型

(1)

在英文版本的Oracle中默认日期格式为'dd-mon-yy'，例如 '21-Jan-22' ；

在汉化的中文版本中Oracle默认日期格式为'日-月-年' ，例如 '21-8月-2022' ，或'21-8月-22'；

(2)

如果插入的日期字段不是系统默认的时间，需要使用to_date（）函数来调整顺序。

例如在上面创建好的表中插入一条数据，create_time要求是年-月-日类型。

这样插入的时间就是create_time字段下显示的时间就是 2022-10-02  10:54:04；

如果是需要插入当前时间，则用sysdate代替to_date。

2.2.4 blob类型

blob是数据库中用来存储二进制文件的字段类型，可以存储图片、文档、音频等文件。

2.3、删除表

drop是删除整个表，数据和表结构都删除。

格式：drop table 表名;

示例：把system空间下的test表删掉。

drop table system.test;

2.4、修改表

2.4.1 修改表名

alter table 原表名 rename to 新表名。

示例1：将test改为test1

alter table system.test rename to test1;

示例2：将test1改为test

alter table system.test1 rename to test;

2.4.2 修改字段名

alter table 表名 rename column 原列名 to 新列名。

示例：将字段name修改成names。

alter table system.test rename column name to names;

2.4.3 修改字段类型

alter table 表名 modify 字段名 字段类型。

示例1：将字段 mobile 字段的类型修改成 varchar 类型。

alter table system.test1 modify mobile varchar(11);

示例2：将字段mobile字段的类型修改成number类型。

alter table system.test1 modify mobile number(11);

2.4.4 增加字段

alter table 表名 add 字段名 字段类型;

示例：在system空间的test表中增加avg字段;

alter table system.test add avg number;

2.4.5 删除字段

alter table 表名 drop column字段名;

示例：在system空间的test表中删除avg字段;

alter table system.test drop column avg;

2.5、查看表

2.5.1 查看表的创建语句

按住ctrl键然后用鼠标左键去点击表名；

2.5.2 查看表的数据

select * from 表名;

示例：查询system空间下test表的全部数据;

select * from system.test;

3.1、增加1条语句

格式：部分字段设置值，值的顺序与给出的字段顺序对应；

insert into 表名(列1,列2……) values(值1,值2……)

示例： 往system表空间下的test表中插入任盈盈的信息；

3.2、增加多条语句

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

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

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

……

示例：往system.test表中插入2条数据；

4.1、drop

drop是删除整个表，数据和表结构都删除；

格式：drop table 表名;

drop table system.test;

delete from 表名是清空表里的数据，保留表结构，自增长字段不会恢复从1开始，删除部分数据时需要在表名后加where条件。

格式1：删除表中所有的数据：delete from 表名;

delete from system.test;

格式2：delete from 表名 where 条件;

delete from system.test where id = 1;

commit;

格式：update 表名 set 列1=值1,列2=值2……where 条件;

示例：修改system表空间下test表中name为小吴的数据，mobile改为17800000000，grade改为100。

update system.test set mobile=17800000000, grade=100 where  name=’小吴’;

commit;

6.1、查询所有数据

格式：select * from 表名;

示例：查询system.test表下的所有字段数据;

select*from system.test;

6.2、查询部分字段

格式：select 字段1,字段2,...from表名;

示例：查询system.test表下name和mobile字段的数据;

select name,mobile from system.test;

6.3、去重

格式：select distinct 字段1,字段2,……from 表名;

示例：查询system表中所有学生的姓名和电话，不显示重复的数据;

select distinct name,mobile from system.test;

6.4、条件查询

6.4.1、语法格式

使用where子句对表中的数据筛选，符合条件的数据会出现在结果集中。

格式1：条件查询 select*from 表名 where 条件;

示例1：查询地址在长春市南关区……的学生数据;

select*from system.test where address='长春市南关区……';

格式2：条件查询 select 字段1,字段2...from 表名 where 条件;

示例2：查询地址在长春市南关区……学生的姓名和电话;

select name,mobile from system.test where address='长春市南关区……';

6.4.2、比较运算符

等于：=

大于：>

大于等于：>=

小于：<

小于等于：<=

不等于：!= 或者<>

示例1：查询成绩等于100分的学生信息;

select * from system.test where grade=100;

示例2：查询成绩大于90分的学生信息;

select * from system.test where grade>100;

示例3：查询成绩大于等于90分的学生信息;

select * from system.test where grade>=100;

示例4：查询成绩不等于100分的学生信息;

select * from system.test where grade !=100;

示例5：查询地址不是杭州的学生信息;

select * from system.test where address !='杭州市西湖区……';

6.4.3、逻辑运算符

（1）and 交集（条件都成立）

示例：查询住址是杭州市西湖区……且成绩大于90分的同学;

select * from system.test where address=‘杭州市西湖区……’ and grade>90;

（2）or  并集(符合一个条件即可）

示例：查询住址是杭州市西湖区……以及地址是长春市南关区……且成绩大于90分的同学;

select * from system.test where address=‘杭州市西湖区……’ or address=‘长春市南关区……’and grade>90;

（3）not 非（不符合这个条件的）

示例：查询住址不是杭州市西湖区……的学生信息;

select * from system.test   where not address ='杭州市西湖区……';

6.4.4、模糊查询

（1）%表示多个任意字符

示例1：查询杭州市的学生信息;

select * from system.test  where address like '杭州%';

（2）_表示一个任意字符

示例2：查询长春市南关区的学生信息;

select * from system.test  where address like '_春%';

6.4.5、范围查询

（1）in表示在一个非连续的范围内

示例：查询成绩等于60分和100分的学生信息;

select * from system.test where grade in (60,100);

（2）between…and…

between…and…表示在一个连续的范围内，范围是闭区间。

示例：查询年龄为17至20的学生;

select * from system.test where age between 17 and 20;

6.4.6、空判断

注意：

1、null与 '' 是不同的

2、判空is null

示例：查询没有填写电话号码的学生;

select * from system.test where mobile is null;

6.5、排序

为了方便查看数据，可以对数据进行排序。

格式：select * from 表名 order by 列1 asc | desc,列2 asc | desc,……;

将行数据按照列1进行排序，如果某些行列的值相同时，则按照列2排序，以此类推。

asc从小到大排列，即升序；

desc从大到小排序，即降序；

不填asc | desc则是默认按照列值从小到大排序。

示例1：查询所有学生信息，按成绩进行从小到大排序;

select * from system.test  order by grade;

示例2：查询杭州市的学生信息，按创建时间进行降序；

示例2：查询杭州市的学生信息，按创建时间进行降序;

select * from system.test  where address like '杭州%' order by create_time;

6.6、Rowid

查询语句加了rowid，点击小锁就可以直接在表里编辑了。

6.7、聚合函数

使用聚合函数更方便进行数据统计

注意：聚合函数不能在where中使用

（1）count()：查询总记录数

count(*)表示计算总行数，括号中也可以使用字段名;

示例1：查询学生总数;

select count(*) from system.test;

示例2：查询mobile字段下的数据的总数;

select count(mobile) from system.test;

（2）max(列名)：表示求此列的最大值

示例：查询成绩大于90分学生的最大年龄;

select max(age) from system.test where grade > 90;

（3）min(列名)表示求此列的最小值

示例：查询成绩大于90分学生的最小年龄;

select min(age) from system.test where grade > 90;

（4）sum(列名)：表示对此列的数据进行求和

示例：把长春市学生的总分算出来;

select sum(grade) from system.test where address like '长春市%';

（5）avg(列)表示求此列的平均值

示例：查询长春市学生的平均成绩;

select avg(grade) from system.test where address like '长春市%';

6.8、分组

6.8.1、分组查询

分组的目的是对每一组的数据进行统计(使用聚合函数)

按照字段分组，此字段相同的数据会被放到一个组中

select 字段1，字段2，聚合…from 表名 group by 字段1，字段2……;

示例1：查询男女生的人数;

select sex,count(*) from system.test group by sex;

示例2：查询各个城市男女性别的人数;

select address,sex,count(*) from system.test group by address,sex;

6.8.2、分组后的数据筛选

select 字段1，字段2，聚合……from 表名;

group by 字段1，字段2，字段3……;

having 字段1，……聚合……;

having 后面的条件运算符与where的相同；

where 是对 from 后面指定的表进行数据筛选，属于对原始数据的筛选；

having 是对 group by 的结果进行筛选；

having 后面的条件可以使用聚合函数，where 后面不可以。

示例：查询男生总人数;

方案1：select count(*) from system.test where sex='男';

方案2：select sex,count(*)from system.test groupby sex having sex='男';

6.9、分页

6.9.1、分页

当数据量过大时，在一页中查看数据是一件非常麻烦的事情；

格式：select * from 表名 where rownum < n;

注释：这个n填自己想填的行数;

示例：查询前5行学生信息;

select * from system.test where rownum<=5;

若数据量很大，要分好几百页，如果一次将结果都查询出来，速度会很慢，可以使用下面的办法来解决。

select * from(select t.*,rownum new_ rownum from表名 t) where new_ rownum>n and new_ rownum<=n+m;

示例：查询出第100到第200的100条数据;

select * from (select t.*,rownum my_rownum fromsystem.test t) where my_rownum>100 and my_rownum <=200;

6.10、子查询

6.10.1、子查询介绍

子查询：在一个 select 语句中，嵌入了另外一个 select 语句，那么嵌入的 select 语句称之为子查询语句；

主查询：外层的 select 语句称之为主查询语。

6.10.2、主查询和子查询介绍

子查询是嵌入到主查询中的；

子查询是辅助主查询的，要么充当条件，要么充当数据源；

子查询是可以独立使用的语句，是一条完整的 select 语句。

6.10.1、子查询充当条件

示例1：查询大于班级学生平均年龄的学生信息；

方法1：分两步走：

第1步先用avg函数查出班级学生的平均年龄;

select avg(age) from system.test;

然后第2步再根据大于班级平均年龄条件进行查询班级学生信息;

select * from system.test where age> 39.1428571428571;

方法2：使用子查询语句一次查询;

select * from system.test where age > (select avg(age) from system.test);

示例2：查询和小吴在同地区、同龄的学生信息；

方法1先将小吴的地区和年龄查询出来，然后再根据小吴的信息查询其他同学的信息。

select address,age from system.test where name='小吴';

select * from system.test where ADDRESS='杭州市西湖区……'  and age =22;

方法2：使用子查询语句一次查询；

select * from system.test where address=(select address from system.test  where name='小吴') and age=(select age from system.test  where name='小吴');

6.10.2、子查询充当数据

查询杭州市年龄介于17到23岁的学生信息;

select * from system.test where address='杭州市西湖区……' and age in (select age from system.test where age between 17 and 23);

6.10.3、子查询的意义

虽然上面两种方法都把数据查询出来了，但是从软件性能角度看，对数据库访问一次完事，就不要访问两次，不然会导致查询慢页面率高的问题。

create index test_1 on system.test(name);

查询索引的格式：select * from user_indexes where table_name ='表名';

select * from all_indexes where table_name=system.test;

格式：select * from user_ind_columns where table_name='表名';

select * from user_ind_columns where table_name =system.test;