--关键字是不区分大小写的
--比如:SELECT FROM DROP CREATE alter where like
--除了关键字之外的东西都区分大小写:通过双引号区分大小写
--强制大小写,默认情况下,全部大写
--比如:
create table "sTock"
(
stock_code char(8),
stock_name varchar2(50),
price number(18,2),
"category" varchar(50)
)
insert into sTock
values
('sz603189','网达软件','39.70','信息技术')
create table sTock
(
stock_code char(8),
stock_name varchar2(50),
price number(18,2),
"category" varchar(50)
);
SELECT * FROM "STOCK";
select *from "stock";
select stock_code,stock_name,price,"category" from "stock";
--别名:可以使用as 或者加空格,AS方式只能对列进行别名化处理,不能对表别名处理
select * from emp;
--计算实发工资 :基本工资+奖金
select t.*,(t.sal+t.comm) as 实发工资 from emp t;
select t.*,(t.sal+t.comm) "实发 工资" from emp t;
SELECT 'hello,world',100 FROM dual;
select 'ssfsf' from dual;
select length('') from dual;
select sysdate from dual;
--空值问题:
--空值:不等于空格,也不等于0
' ',0
--''==null
--进行数学运算的时候,只要遇到空值,那么返回的结果也是空值
--进行函数调用的时候,也是一样的
--查询空值的条件如何写:
select * from emp where comm is null;--or is not null;
--字符串的拼接:
select concat(concat('sss','aa'),'dd') from dual;-- 一次性只能拼接两个字符,
--可以用||进行拼接,没有个数的限制,可以无限的拼接
select 'adfsf'||'asfdf'||'sdfsdf' from dual;--||+
select 'ad"fsf'||'asf'' df'||'sdfsdf' from dual;
--单引号的转义:/'/"
--两个单引号,表示一个单引号
select * from user_tables;
select * from tablename;
--去掉重复行,用distinct 关键字
select distinct * from stock;
select * from stock group by stock_code,stock_name,price,"category";
--BETWEEN n AND m;包含n和m
--类似>=n and <=m
--not BETWEEN n AND m;
--like
--_匹配一个字符%匹配任意字符,包含一个,或者多个
select * from vehicle where trim(veh_no) like '湘AR95_3';
select * from vehicle where trim(veh_no) like '湘AR9%';
--rownum和rowid伪列
--rownum:序号,主要是用来分页,临时产生,查询的时候才出来,随着查询条件的变化,这个序号
--也是跟着变化的--整数
--rowid:唯一标识,固定不变,数据插入的时候产生,物理地址,逻辑编号,表示表里的行地址
--数据类型:18位长的字符
--limit n,m,1,20,21,20
select rowid,rownum,t.* from stock t;
--两者的使用场景:
--rowid
select * from stock for update;
select * from emp for update;--锁表
select rowid,t.* from emp t--可以在线编辑,不锁表
select t.* from emp t
select distinct * from stock;
select * from stock group by stock_code,stock_name,price,"category";
select * from stock where rowid in (
select min(rowid) as ri from stock t group by stock_code,stock_name,price,"category"
)
--删除重复行,留下有效数据
select * from stock;
delete from stock where rowid not in (
select min(rowid) as ri from stock t group by stock_code,stock_name,price,"category"
)
--另外一种删除重复行;
--1.先建临时表,正式的数据复制到临时表
create table stock_tmp
as
select distinct * from stock;
--2.delete from stock
--3.create stock as
select * from stock_tmp;
--rownum--只能写<=,=1,不能写等于非1的数据和> >=
select rownum,t.* from stock t where rownum = 1;
select rownum,t.* from stock t where rownum = 2
--查询前n行数据
select rownum,t.* from stock t where rownum <=5;
--查询第n 行到第m 行的数据5,10
--查询速度最快,效益最高
select * from (
select rownum as rn, t.* from stock t where rownum <= 10
) where rn>=5
--通用写法:
select * from (
select rownum rn,a.* from (
----------------------------------
select t.* from stock t
----------------------------------
) a where rownum<=10
) where rn>=5
--
select a.*
from (select rownum rn, t.* from stock t) a
where rn between 5 and 10
--比如:SELECT FROM DROP CREATE alter where like
--除了关键字之外的东西都区分大小写:通过双引号区分大小写
--强制大小写,默认情况下,全部大写
--比如:
create table "sTock"
(
stock_code char(8),
stock_name varchar2(50),
price number(18,2),
"category" varchar(50)
)
insert into sTock
values
('sz603189','网达软件','39.70','信息技术')
create table sTock
(
stock_code char(8),
stock_name varchar2(50),
price number(18,2),
"category" varchar(50)
);
SELECT * FROM "STOCK";
select *from "stock";
select stock_code,stock_name,price,"category" from "stock";
--别名:可以使用as 或者加空格,AS方式只能对列进行别名化处理,不能对表别名处理
select * from emp;
--计算实发工资 :基本工资+奖金
select t.*,(t.sal+t.comm) as 实发工资 from emp t;
select t.*,(t.sal+t.comm) "实发 工资" from emp t;
SELECT 'hello,world',100 FROM dual;
select 'ssfsf' from dual;
select length('') from dual;
select sysdate from dual;
--空值问题:
--空值:不等于空格,也不等于0
' ',0
--''==null
--进行数学运算的时候,只要遇到空值,那么返回的结果也是空值
--进行函数调用的时候,也是一样的
--查询空值的条件如何写:
select * from emp where comm is null;--or is not null;
--字符串的拼接:
select concat(concat('sss','aa'),'dd') from dual;-- 一次性只能拼接两个字符,
--可以用||进行拼接,没有个数的限制,可以无限的拼接
select 'adfsf'||'asfdf'||'sdfsdf' from dual;--||+
select 'ad"fsf'||'asf'' df'||'sdfsdf' from dual;
--单引号的转义:/'/"
--两个单引号,表示一个单引号
select * from user_tables;
select * from tablename;
--去掉重复行,用distinct 关键字
select distinct * from stock;
select * from stock group by stock_code,stock_name,price,"category";
--BETWEEN n AND m;包含n和m
--类似>=n and <=m
--not BETWEEN n AND m;
--like
--_匹配一个字符%匹配任意字符,包含一个,或者多个
select * from vehicle where trim(veh_no) like '湘AR95_3';
select * from vehicle where trim(veh_no) like '湘AR9%';
--rownum和rowid伪列
--rownum:序号,主要是用来分页,临时产生,查询的时候才出来,随着查询条件的变化,这个序号
--也是跟着变化的--整数
--rowid:唯一标识,固定不变,数据插入的时候产生,物理地址,逻辑编号,表示表里的行地址
--数据类型:18位长的字符
--limit n,m,1,20,21,20
select rowid,rownum,t.* from stock t;
--两者的使用场景:
--rowid
select * from stock for update;
select * from emp for update;--锁表
select rowid,t.* from emp t--可以在线编辑,不锁表
select t.* from emp t
select distinct * from stock;
select * from stock group by stock_code,stock_name,price,"category";
select * from stock where rowid in (
select min(rowid) as ri from stock t group by stock_code,stock_name,price,"category"
)
--删除重复行,留下有效数据
select * from stock;
delete from stock where rowid not in (
select min(rowid) as ri from stock t group by stock_code,stock_name,price,"category"
)
--另外一种删除重复行;
--1.先建临时表,正式的数据复制到临时表
create table stock_tmp
as
select distinct * from stock;
--2.delete from stock
--3.create stock as
select * from stock_tmp;
--rownum--只能写<=,=1,不能写等于非1的数据和> >=
select rownum,t.* from stock t where rownum = 1;
select rownum,t.* from stock t where rownum = 2
--查询前n行数据
select rownum,t.* from stock t where rownum <=5;
--查询第n 行到第m 行的数据5,10
--查询速度最快,效益最高
select * from (
select rownum as rn, t.* from stock t where rownum <= 10
) where rn>=5
--通用写法:
select * from (
select rownum rn,a.* from (
----------------------------------
select t.* from stock t
----------------------------------
) a where rownum<=10
) where rn>=5
--
select a.*
from (select rownum rn, t.* from stock t) a
where rn between 5 and 10