04—查询数据

最新推荐文章于 2022-04-25 17:52:38 发布

YLNF!!

最新推荐文章于 2022-04-25 17:52:38 发布

阅读量470

点赞数

分类专栏： oracle数据库

本文链接：https://blog.csdn.net/chenxiliylnf/article/details/81433573

版权

oracle数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

查询数据

一、单表查询

需要建立表：

create table student(
	id char(36) primary key,
	name varchar2(8) not null,
	age number(3) default 0,
	mobile char(11),
	address varchar2(150)
)
insert into student values ('9b4435ec-372c-456a-b287-e3c5aa23dff4','张三',24,'12345678901','北京海淀');
insert into student values ('a273ea66-0a42-48d2-a17b-388a2feea244','李%四',10,'98765432130',null);
insert into student values ('eb0a220a-60ae-47b6-9e6d-a901da9fe355','张李三',11,'18338945560','安徽六安');
insert into student values ('6ab71673-9502-44ba-8db0-7f625f17a67d','王_五',28,'98765432130','北京朝阳区');
insert into student values ('0055d61c-eb51-4696-b2da-506e81c3f566','王_五%%',11,'13856901237','吉林省长春市宽平区');
commit;

1、语法：

select *|字段列表|表达式 from table_name [where 条件] [order by 字段列表]

说明：

1、*：相当于按照表中字段顺序罗列表中的所有字段

select * from student

2、字段列表：当查询结果只是表中部分字段时，可将要显示的字段罗列出来，字段之间以逗号间隔

select name,address from student

3、表达式：可以是算术运算符也可以是数据库中的函数

select age+1 from student;

字段别名：

select所选字段后面可以指定别名以使查出来的结果所显示的字段更好理解，字段名与别名之间使用空格或as关键字间隔;为了阅读方便，推荐使用as关键字这种方式定义字段别名。

select age+1 stu_age from student;

select length(name) as name_len from student;

在默认情况下，查询结果中显示的字段名为大写字母，如果别名中包含空格、特殊字符（例如%）或者对大小写敏感，则别名需要使用双引号引起来。

select age+1 "stu age" from student;
select length(name) as "name%len" from student;
--不加双引号，上面SQL语句均无法执行

4、table_name：表名，表名也可以有别名，表名与别名之间使用空格间隔（MySQL数据库中表的别名可以使用as关键字间隔，但Oracle数据库不允许）。

select student.name,student.address from student
select s.name,s.address from student s--表别名简化了SQL语句

注意：

a、表的别名最多可以有30个字符；

b、表别名的作用方法在整个select语句中都有效，离开select语句即无效。

5、where：指定查询过滤条件：

like：进行数据模糊查询

%：匹配0次或多次（意思是只要该列信息中含有%前的字就可以被查询到）

select * from student where name like '张%';--查询姓张的学生信息
select * from student where name like '%李%';--查询姓名中含有“李”字的学生信息

_：只匹配1次（_表示占位符）

select * from student where name like '张_';--查询两个字的张姓学生信息，_所在的地方必须有字符占位的数据才能被找到
select * from student where name like '_张_'--表示信息为三个字且第二个字是张，才能被查询到

escape：取消%或_字符的通配符特性

select * from student where name like '%#%%' escape '#'--查询姓名中含有%字符的学生信息，取消#后面的%的特殊意义
select * from student where name like '%$_%' escape '$'--查询姓名中含有%字符的学生信息，取消$后面的_的特殊意义
select * from student where name like '%_%_%' escape '_';--查询姓名以%%结尾的学生信息

注意：

1、escape后面单引号中只能是单个字符；

2、escape后面可以是字母、#、$、,、\、_等字符；

逻辑条件and、or

select * from student where name like '张%' and address like '%北京%';--查询张姓且地址中含有北京的学生信息
select * from student where name like '张%' or address like '%北京%';--查询张姓或地址中含有北京的学生信息

between 下限 and 上限，等同于“(column_name>=下限) and (column_name<=上限)”

select * from student where age between 10 and 28;--查询年龄在10~28之间的学生信息
select * from student where age >= 10 and age<=28;--上面SQL语句等效于该SQL语句

注意：

1、“between 下限 and 上限”一定是小值在前大值在后，否则查不出数据；

2、“between 下限 and 上限”查询数据包括边界值；

--关系条件 =、!=、<、=<、>、>=等

select * from student where age<28;--查询年龄小于28岁的学生信息

-in（value1,value2,value3......valuen），等同于“ column_name = value1 or column_name = value2 or column_name = value3...... or column_name = valuen ”

select * from student where age in(10,17,24);
select * from student where age = 10 or age=17 or age = 24;--上面SQL语句等效于该语句

null =包括is null 和 is not null

select * from student where address is null;--查询地址为空的学生信息

注意：is null不能写成 = null，同样，is not null不能写成!=null

6、order by：对查询结果进行排序，必须置于SQL语句的最后，语法：order by {column_name1, column_name2, column_name3, ......column_namen} [asc|desc]

说明：

a、{column_name1, column_name2, column_name3, ......column_namen}——指定需要排列的字段

b、[asc|desc]——指定排列策略，asc以升序排列，desc以降序排列，默认以升序排列。

不同数据类型，升序的含义如下：

①、数字类型：小值在前面显示；

②、日期类型：早的日期在前面显示；

③、字符类型：依据字母顺序显示，a在前，z最后；

④、空值：显示在最后；

select * from student order by age;--按照age升序排列
select * from student order by age asc;--按照age升序排列
select * from student order by age desc;--按照age降序排列

在order by 子句中，不仅可以使用字段，还可以使用字段别名进行排序，例如：

select id,name,age as stu_age,mobile,address from student order by stu_age;--按照age升序排列

多重排序：也称多列排序，即先以一个字段对查询结果进行排序，然后在这个排序的基础上再对另一个字段进行排序，最终显示经多次排序后的查询结果；

select * from student order by age desc,name asc;--先依据年龄降序排列，当年龄相同时再依据姓名升序排列

2、去掉重复行（去重）

数据库使用distinct关键字去除相同的行（“相同的行”指不同行之间的相同列中的数值相同）

例如：

select distinct type_name from book

select distinct price,vip_price from book

注意：该关键字必须紧跟select关键字的后面，即如下写法是错误的：select price, distinct vip_price from books

3、算术表达式

1、Oracle数据库中的算术运算符包括+、-、*和/，其中/运算符的结果为浮点类型的数值

例如：select vip_price + 1 new_price from book

2、Oracle数据库中没有取余运算符，如果要对数据进行取余运算只能借助数据库mod(x,y)来实现，其中x为被除数，y为除数

例如：select mod(1,3) as result from dual

3、Oracle数据库中可以对date或timestamp类型的数据进行加减操作（但不能进行乘除操作）

例如：select sysdate + 365 new_date from dual

4、连字运算符

Oracle数据库中使用||作为连字运算符，连字运算符可将字段与字段、字段与表达式、字段与常数值之间连接成单列显示出来。

例如：select id || '，' || name as book_info from book

注意：如果连接的常数值是数字则可以不使用单引号将其引起来，但如果是字符串日期类型，则必须使用单引号将其引起来、

5、dual表

dual表的所有者是sys用户，该表可以被所有用户使用，它只有一个dummy列，表中只有一条数据X。该表通常用于没有目标表的情况下，为了使select语法完整（select子句必须包括from子句）而使用的一个不需要从表中真实取数值的表。

二、内置函数

需要执行SQL语句

create table student(
id number(1,0) constraint studnet_id primary key,
name varchar2(8),
sex char(2) default '男' ,
age number(3) default 0
)

insert into student values ('1','王明','男',18);
insert into student values ('2','孙丽','女',17);
insert into student values ('3','王明','男',27);
insert into student (id,sex,age) values ('4','男',27);
commit;

单行函数

1、字符函数

a、lower(input)：将大写字符转换为小写，例如：select lower('AbC') from dual——> abc
b、upper(input)：将小写字符转换为大写，例如：select upper('AbC') from dual——>ABC
c、initcap(input)：将每个单词首字母大写，例如：select initcap('i love you') from dual——> I Love You
d、concat(input1,input2)：连接两个字符串，等价于连接运算符||，例如：select concat('lan','qiao') from dual——> lanqiao
e、substr(input,m[,n])：从m（m>=1）位置开始截取字符串，如果n被忽略则取到字符串结尾，否则取n个字符长度，例如：
select substr('OracleDB',1) from dual——>OracleDB select substr('OracleDB',1,6) from dual——>Oracle
f、lengthc(input)：获取以字符为单位的字符串长度，例如：select lengthc('lan桥') from dual——> 4
g、lengthb(input)：获取以字节为单位的字符串长度，例如：select lengthb('lan桥') from dual——> 5(注：一个汉字2字节)
h、length(input)：获取字符串的字符数，等效于lengthc，例如：select length('lan桥') from dual——> 4
i、replace(input,char1,char2)：将字符串中的char1字符串替换为char2，例如：select replace('AbAcA','A','a') from dual——> abaca
j、lpad(input,n,char)：使用给定字符串从input左边进行填充以使旧字符串的长度达到n，例如：select lpad('Oracle',9,'*') from dual—***Oracle
k、rpad(input,n,char)：使用给定字符串从input右边进行填充以使旧字符串的长度达到n，例如：select rpad('Oracle',9,'**') from dual——> Oracle***
l、instr(input,char[,m][,n]) ：获取char字符串在input字符串中的位置，m用于指定查找的开始位置，n用于指定char字符串第n次出现。m与n的默认值为1，即从input字符串开头开始查找，获取char字符串第一次出现的位置，例如：
select instr('DBOracleDB','DB') from dual——>1（从DBOracleDB字符串开头查找DB第一次出现的位置）
select instr('DBOracleDB','DB',3) from dual——>9（从DBOracleDB字符串第3个字符查找DB第一次出现的位置）
select instr('DBOracleDB','DB',1,2) from dual——>9（从DBOracleDB字符串第1个字符查找DB第二次出现的位置）

2、数字函数

a、round(input[,n])：将数值四舍五入，参数n表示有效小数位，如果忽略则无小数位部分，

例如：

select round(1.945) from dual——2

select round(1.945,2) from dual——1.95
b、trunc(input[,n])：将数值截断，参数n表示有效小数位，如果忽略则无小数位部分，

例如：

select trunc(1.945) from dual——1

select trunc(1.945,2) from dual——1.94
c、mod(m,n)：返回m除以n的余数，将m与n中间逗号理解成Java中%即可，例如：select mod(1,3) from dual——1

3、转换函数

a、to_number(char[,'fmt'])：将字符串类型的数据转换成数字类型的数据，
b、to_char(input[,'fmt'])：将日期或数字类型的数据转换为字符串
将日期类型的数据转换为字符串：
日期格式元素

SQL函数概述
   数据库中的函数与Java中的函数类似，都是完成一定功能的代码的集合。根据函数操作数据行数可将SQL函数分为单行函数和多行函数：
   单行函数仅对单条数据中的列进行操作并且返回一个结果，例如：select length('lanqiao') from dual——获取字符串字符个数
多行函数可以操作成组的多条数据，每组返回一个结果，所以多行函数又称之为组函数，例如：
select sex,count(id) from student group by sex——获取student表中男女人数
单行函数
   单行函数根据操作对象的不同分为字符函数、数字函数、转换函数、日期函数和通用函数。
1、字符函数
   a、lower(input)：将大写字符转换为小写，例如：select lower('AbC') from dual——> abc
b、upper(input)：将小写字符转换为大写，例如：select upper('AbC') from dual——>ABC
c、initcap(input)：将每个单词首字母大写，例如：select initcap('i love you') from dual——> I Love You
d、concat(input1,input2)：连接两个字符串，等价于连接运算符||，例如：select concat('lan','qiao') from dual——> lanqiao
e、substr(input,m[,n])：从m（m>=1）位置开始截取字符串，如果n被忽略则取到字符串结尾，否则取n个字符长度，例如：
select substr('OracleDB',1) from dual——>OracleDB select substr('OracleDB',1,6) from dual——>Oracle
f、lengthc(input)：获取以字符为单位的字符串长度，例如：select lengthc('lan桥') from dual——> 4
g、lengthb(input)：获取以字节为单位的字符串长度，例如：select lengthb('lan桥') from dual——> 5(注：一个汉字2字节)
h、length(input)：获取字符串的字符数，等效于lengthc，例如：select length('lan桥') from dual——> 4
i、replace(input,char1,char2)：将字符串中的char1字符串替换为char2，例如：select replace('AbAcA','A','a') from dual——> abaca
j、lpad(input,n,char)：使用给定字符串从input左边进行填充以使旧字符串的长度达到n，例如：select lpad('Oracle',9,'*') from dual——> ***Oracle
k、rpad(input,n,char)：使用给定字符串从input右边进行填充以使旧字符串的长度达到n，例如：select rpad('Oracle',9,'**') from dual——> Oracle***
       l、instr(input,char[,m][,n]) ：获取char字符串在input字符串中的位置，m用于指定查找的开始位置，n用于指定char字符串第n次出现。m与n的默认值为1，即从input字符串开头开始查找，获取char字符串第一次出现的位置，例如：
select instr('DBOracleDB','DB') from dual——>1（从DBOracleDB字符串开头查找DB第一次出现的位置）
select instr('DBOracleDB','DB',3) from dual——>9（从DBOracleDB字符串第3个字符查找DB第一次出现的位置）
select instr('DBOracleDB','DB',1,2) from dual——>9（从DBOracleDB字符串第1个字符查找DB第二次出现的位置）
2、数字函数
a、round(input[,n])：将数值四舍五入，参数n表示有效小数位，如果忽略则无小数位部分，例如：
select round(1.945) from dual——2 select round(1.945,2) from dual——1.95
b、trunc(input[,n])：将数值截断，参数n表示有效小数位，如果忽略则无小数位部分，例如：
select trunc(1.945) from dual——1 select trunc(1.945,2) from dual——1.94
c、mod(m,n)：返回m除以n的余数，将m与n中间逗号理解成Java中%即可，例如：select mod(1,3) from dual——1
3、转换函数
a、to_number(char[,'fmt'])：将字符串类型的数据转换成数字类型的数据，
b、to_char(input[,'fmt'])：将日期或数字类型的数据转换为字符串
   将日期类型的数据转换为字符串：
       日期格式元素

时间格式元素

       e.g
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
fm、th和sp的使用
       fm：
           i、删除填补的空格，例如：select lengthb(to_char(sysdate,'fmmonth')) from dual
           ii、删除前导0，例如：select to_char(sysdate,'fmmm') from dual
th：表示序数，例如：select to_char(sysdate,'Qth') from dual
sp：将数字变为英文，例如：select to_char(sysdate,'Qsp') from dual
注意：为了达到更好的输出效果，可以在格式模板中直接使用标点符号作为各元素之间的间隔，还可以在格式模板中增加字符串，但是该字符串需要用双引号引起来，例如
         select to_char(sysdate,'cc"世纪" yyyy-mm-dd hh24:mi:ss') from dual；
将数字类型的数据转换为字符串：
c、to_date(char[,'fmt'])：将字符类型转为日期类型
   fx：用于限制所传入的日期字符串必须和格式模板精确匹配（包括标点符号和空格）
           select to_date('09 03,2006','fxMM DD,YYYY') from dual;--去掉fx才可以执行
           select to_date('9 3,2006','fxMM DD,YYYY') from dual;-- 去掉fx才可以执行
           select to_date('09 03,2006','fxMM DD;YYYY') from dual;--可以执行
4、日期函数
a、months_between(date1,date2)：返回两个日期之间的月数，例如
select months_between(to_date('2017-3-21','yyyy-mm-dd'), to_date('2017-1-21','yyyy-mm-dd')) from dual——2

5、通用函数
a、nvl(expr,value)：如果expr为空，则返回value的值；否则返回expr
b、nvl2(expr,value1,value2)：如果expr1为非空，则返回value1的值，否则返回value2的值；

许小力问题：
select nvl(sysdate,1)from dual
select nvl('1',sysdate)from dual
select nvl('aa',1)from dual
select nvl2('aa',1,sysdate)from dual
c、coalesce(expr1, expr2,.. exprn)：如果expr1为非空，则返回expr1的值；如果expr1为空，则返回expr2的值，依次类推，如果前面表达式都为空，则返回exprn的值。
6、其它

a、decode (条件,值1,返回值1,值2,返回值2,...值n,返回值n,默认值)

create table user_info(
name varchar2(8),
sex number(1)
)
insert into user_info(name,sex) values ('张三',1); 
insert into user_info(name,sex) values ('李四',0); 
insert into user_info(name,sex) values ('王五',2); 
commit;
select name,decode(sex,0,'女',1,'男','其它') sex_name from user_info

多行函数

多行函数又称组函数，这类函数用于对多行数据进行操作，在使用时需要注意一下几点：
           1、组函数忽略空值——可以通过nvl、nvl2或coalesce函数用一个值代替空值；
2、组函数默认考虑重复值——可以通过distinct关键字使组函数不考虑重复值；
常用组函数：
           avg(input)：求平均值，例如：select avg(age) from student——计算学生平均年龄，包括重复的年龄
           max(input)：求最大值，例如：select max(age) from student——获取学生表中最大年龄
           min(input)：求最小值，例如：select min(age) from student——获取学生表中最小年龄
           sum(input)：求和，例如：select sum(age) from student——计算学生表中年龄之和
           count(*|input)：求行数，如果使用*则不会忽略空值的行，例如：
select count(name) from student——3，student表中有一条数据中的name为空
               select count(distinct name) from student——2，student表中有一条数据中的name为空，有两条数据name的值重复
select count(nvl(name,' ')) from student——4，将name为空的值置为1个空格
select count(*) from student——4，*不会忽略空值的行
stddev(input)：求标准差
variance(input)：求方差
注意：count、max和min组函数操作的数据的数据类型可以是char、varchar2、number或date，但不能为clob；avg、sum、stddev和varlance仅能用在数字类型的数据上。
group by：
           group by用于将表中数据划分为若干个组，group by后面用于指定分组的依据，例如：
           select sex,count(id) from student group by sex——将student表学生按照sex分组，然后统计每组中的人数
注意：
a、只有对应列相同的多行数据才会归为一组，如下：

create table fruit(
       name varchar2(4),
       address varchar2(12),
       type_name varchar2(6)
)

insert into fruit values ('香蕉','广西','大香蕉');
insert into fruit values ('苹果','山东','红富士');
insert into fruit values ('香蕉','菲律宾','小香蕉');--帝王蕉比较小
insert into fruit values ('苹果','山西','青苹果');
insert into fruit values ('苹果','山西','国光');

执行上面SQL语句后表中数据如下：

i、执行select name from fruit group by name SQL语句，其查询结果如下：

ii、执行select name,address from fruit group by name,address SQL语句，其查询结果如下：

b、如果select语句中使用group by进行了分组，则select子句中只可以有组函数和分组字段，不能含有其他字段，否则SQL语句报错；

c、如果group by子句后面跟着order by子句，则order by子句用于排序的字段必须是组函数或分组字段；

d、如果select语句中含有where、order by，那么group by需放在where之后order by之前，即先获取符合where条件的“有效”数据，再依据group by对数据进行分组，最后再排序；

having：

where后面不能使用多行函数，只能使用单行函数和字段，having关键字弥补了这一不足：having子句用于对分组结果进行约束，例如：

select name from student group by name having count(name)>1——查询哪些名字重名了

注意：

a、having子句必须和group by子句一起使用，否则出现如下错误，例如：

select name from student having count(name)>1

b、having子句必须放在group by子句之后，order by子句之前

三、子查询与多表查询

数据准备：

--user_info表
create table user_info(
      id number(2) primary key,
      user_name varchar2(12) unique,
      password varchar2(15) not null,
      real_name varchar2(8) not null,
      age number(3)
);

--address表
create table address(
      id number(2) primary key,
      user_id number(2) not null,
      real_name varchar2(8),
      mobile char(11),
      address varchar2(150)
);

insert into user_info values(1,'浅唱灬幸福','8912@321','王晓明',12);
insert into address values(1,1,'王小明','15516472282','山西太原');
insert into address values(2,1,'王鑫','18404905139','山西大同');
insert into address values(3,1,'任建','15333021730','山西晋城');
commit;

insert into user_info values(2,'ぅ浮生若梦〤','56701wz','王楠',36);
insert into address values(4,2,'王楠','15010303314','北京海淀');
insert into address values(5,2,'赵婕','18435224278','山西长治');
commit;

insert into user_info values(3,'街角の风铃','27w4921','李晓飞',9);
commit;
insert into address values(6,6,'刘倩','13159775555','吉林长春');
commit;

1、子查询

什么是子查询：查询中的查询即为子查询，一般使用括号将子查询sql括起来，如下SQL语句：

select * from (select * from user_info) users

什么时候用子查询：当在查询过程中需要知道一个已知量的不确定数据时使用子查询

--查询出来姓名为王晓明的所有地址

 select * from address where user_id=(select id from user_info where real_name='王晓明');--使用子查询先通过姓名获取id，然后再通过用户id查询地址表

怎么使用子查询

1、=:如果子查询只返回一个结果，则可以使用=,也可以使用in；但是如果确定子查询永远只返回一个结果，则不建议使用in，例如上面SQL语句可变为如下SQL语句

 select * from address where user_id in(select id from user_info where real_name='王晓明');

2、如果子查询返回多个结果，则使用in、any、all：

a、in:相当于or连接多个条件

--获取到姓氏为王姓的所有用户的所有地址

 select * from address where user_id in(select id from user_info where real_name like '王%');

b、any (某一个的)

<any:小于子查询结果中的某一个，即小于最大的，比如 age <any(12,36,9)，这时只要age小于36即可

>any:大于子查询结果中的某一个，即大于最小的，比如age >any(12,36,9)，这时只要age大于9即可

=any:等于子查询结果中的某一个，等同于in

注意：any运算符等同于some运算符

c、all（所有的）

<all：小于子查询结果中所有的一个，即小于最小的，比如age <all(12,36,9)，这时只要age小于9即可

>all：大于子查询结果中所有的一个，即大于最大的，比如age >all(12,36,9)，这时只要age大于36即可

2、多表查询

多个表关联查询需要依据多表之间列关系将其连接起来，这种连接方式分为三种：内连接(inner join)、外连接(outer join)及交叉连接(cross join)

内连接(inner join)

如果依据多个表之间列关系进行内连接，查询结果集仅包括满足连接条件的数据行。内连接分为等值连接、不等值连接以及自然连接

等值连接：在连接条件中使用等号(=)运算符比较被连接列的列值是否相等，分显式等值连接和隐式等值连接：

显式等值连接：

 select ui.*,addr.* from user_info ui [inner] join address addr on ui.id = addr.user_id;

隐式等值连接：

select ui.*,addr.* from user_info ui,address addr where ui.id = addr.user_id;

外连接(outer join)

如果依据多表之间列关系进行外连接，查询结果集不仅仅包括满足on连接条件的数据行,还包括不满足on连接条件的数据行。

左外连接（left [outer] join）：返回的结果集中不仅包含表之间满足on连接条件的全部数据行，还包含左表（“left [outer] join”关键字左边的表）不满足on连接条件的数据行；

select ui.*,addr.* from user_info ui left join address addr on ui.id = addr.user_id;

右外连接（right [outer] join）：返回的结果集中不仅包含表之间满足on连接条件的全部数据行，还包含右表（即“right [outer] join”关键字右边的表）不满足on连接条件的数据行；

select ui.*,addr.* from user_info ui right join address addr on ui.id = addr.user_id;

全外连接（full [outer] join）：返回的结果集中不仅包含表之间满足on连接条件的全部数据行，还包含左表（“full [outer] join”关键字左边的表）和右表（“full [outer join”关键字右边的表）中不满足on连接条件的数据行；

 select ui.*,addr.* from user_info ui full join address addr on ui.id = addr.user_id;

YLNF!!

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
04—查询数据

查询数据一、单表查询需要建立表：create table student( id char(36) primary key, name varchar2(8) not null, age number(3) default 0, mobile char(11), address va...
复制链接

扫一扫