oracle中的常用sql语句

常用的sql语句,如建表、建索引、建约束条件;以及增删改查和高级查询操作

---------------------------第三章 三建语句-------------------------------------------------
--3.1 新建表
--3.1.1 数据类型
--3.1.2 新建表语法
--3.1.3 删除表
--3.1.4 修改表结构
--3.2 新建约束
--3.2.1 约束类型
--3.2.2 新建约束
--3.2.3 删除约束
--3.2.4 修改约束
---------------------------第四章 数据CURD操作-----------------------------------
--4.1 插入数据
--4.1.1 insert语句
--4.1.2 一次插入多行
--4.2 删除数据
--4.2.1 使用delete语句
--4.2.2 使用truncate语句
--4.2.3 delete和truncate的同异
--4.3 修改数据
--4.3.1 update语句
--4.4 基本查询
--4.4.1 select语句
--4.4.2 基本查询
--4.4.2.1 包含算数表达式
--4.4.2.2 包含连接表达式
--4.4.2.3 空值的使用
--4.4.2.4 字段别名、表别名
--4.4.2.5 文本字符串
--4.4.2.6 去掉重复值
--4.4.3 带有限制条件的查询
--4.4.3.1 where语句
--4.4.3.2 where中的比较操作符
--4.4.3.3 where中的逻辑运算符
--4.4.4 给查询结果排序

---------------------------第五章 高级查询-----------------------------------
--5.1 函数查询
--5.1.1 单行函数
--5.1.1.1 字符函数
--5.1.1.2 数字函数
--5.1.1.3 日期函数
--5.1.1.4 转换函数
--5.1.1.5 其他函数
--5.1.2 分组函数
--5.1.2.1 常用分组函数
--5.1.2.2 使用group by对数据分组
--5.1.2.3 使用having对组结果进行限制
--5.2 多表连接查询
--5.2.1 多表链接分类
--5.2.2 等值连接
--5.2.3 非等值连接
--5.2.4 外连接
--5.2.5 自连接
--5.3 子查询
--5.3.1 子查询类型
--5.3.2 单行子查询
--5.3.3 多行子查询
--5.3.4 多列字查询


--3.1 新建表
--3.1.1 数据类型
字符：varchar2 char nvarchar2 nchar
数字：number
日期：date

--3.1.2 新建表语法
语法：
create table table_name
(
列名1 类型1 default 值1,
列名2 类型2 default 值2,
...
列名n 类型n
);

--新建用户表bbs_user
create table bbs_user
(
user_id number(10), --编号(无任何意义的主键)
user_name varchar2(100) not null, --登录名
user_pass varchar2(20) not null, --密码
user_nickname varchar2(200) not null,--昵称
user_email varchar2(200) not null, --电子邮箱
user_register_date date default sysdate, --注册日期(默认值为系统当前时间)
user_socre number(10) default 0, --积分
user_ge
nder char(4) default '女', --性别
user_online_status number(3),--在线状态
user_last_online_date date, --上一次登录时间
user_last_online_ip varchar2(50),--上

常用的sql语句,如建表、建索引、建约束条件;以及增删改查和高级查询操作

一次登录ip
user_birthday date--用户生日
);

--新建主贴表bbs_topic
create table bbs_topic
(
topic_id number(10),
topic_title varchar2(500) not null,
topic_content varchar2(2500) not null,
topic_date date default sysdate,
topic_user_id number(10), --发贴人编号（外键）
topic_section_id number(10),--帖子所在地板块编号（外键）
topic_click_count number(10) default 0,
topic_reply_count number(10) default 0,
topic_last_reply_user_id number(10), --最后一次回复该贴的用户编号(外键)
topic_last_reply_date date--最后一次回复的时间
);

--新建回复表bbs_reply
create table bbs_reply
(
reply_id number(10),
reply_content varchar2(2500) not null,
reply_date date default sysdate,
reply_topic_id number(10), --回帖的主贴编号（外键）
reply_user_id number(10), --回帖人编号（外键）
reply_section_id number(10)--回帖所在地板块编号（外键）
);


--新建板块表bbs_section
create table bbs_section
(
section_id number(10),
section_name varchar2(100) not null,
section_description varchar2(500) not null,
section_topic_count number(10)
);

--新建版主表bbs_user_section
create table bbs_user_section
(
us_id number(10),
us_user_id number(10), --用户编号
us_section_id number(10), --板块编号
us_comment varchar2(500)
);

--3.1.3 删除表
语法：drop table table_name;

drop table bbs_user;

--3.1.4 改表结构

--修改列名
alter table bbs_user_section rename column us_comment to us_comm;
--添加一个新列
alter table bbs_user_section add (us_status number(10));
--修改列的类型
alter table bbs_user_section modify (us_status char(100));
--删除新列
alter table bbs_User_Section drop column us_status;

--3.2 新建约束
--3.2.1 约束类型

--主键约束：一张表只能有一个主键，它是唯一区分实体
--外键约束：一张表中可以有N个外键，和另外一张表的主键做关联
--非空约束：表示该列的值不可以是空
--唯一约束：表示该列的值不可以重复
--检查约束：用户自定义约束

--3.2.2 新建约束
--新建主键约束
alter table bbs_user
add constraint pk_user_id prim
ary key (user_id);
alter table bbs_reply
add constraint pk_reply_id primary key(reply_id);
alter table bbs_topic
add constraint pk_topic_id

常用的sql语句,如建表、建索引、建约束条件;以及增删改查和高级查询操作

primary key(topic_id);
alter table bbs_section
add constraint pk_section_id primary key(section_id);
alter table bbs_user_section
add constraint pk_us_id primary key(us_id);

--外键约束(由子表(存在外键的表)发向主表)
alter table bbs_topic add constraint fk_topic_user_id
foreign key(topic_user_id) references bbs_user(user_id);
alter table bbs_topic add constraint fk_topic_section_id
foreign key(topic_section_id) references bbs_section(section_id);
alter table bbs_reply add constraint fk_reply_user_id
foreign key(reply_user_id) references bbs_user(user_id);
alter table bbs_user_section add constraint fk_us_user_id
foreign key(us_user_id) references bbs_user(user_id);
alter table bbs_user_section add constraint fk_us_section_id
foreign key(us_section_id) references bbs_section(section_id);
alter table bbs_reply add constraint fk_reply_topic_id
foreign key(reply_topic_id) references bbs_topic(topic_id);
alter table bbs_reply add constraint fk_reply_section_id
foreign key(reply_section_id) references bbs_section(section_id);

--唯一约束
alter table bbs_user add constraint uni_user_name unique(user_name);
alter table bbs_user add constraint uni_user_email unique(user_email);
alter table bbs_section add constraint uni_section_name unique(section_name);

--检查约束
alter table bbs_user add constraint ck_user_name check( length(user_name) >= 6 );
alter table bbs_user add constraint ck_user_pass check(length(user_pass)>=6 );
alter table bbs_user add constraint ck_user_email check( user_email like '%@%' and user_email like '%.%' );
alter table bbs_user add constraint ck_user_gender check( user_gender = '男' or user_gender = '女' );

--3.2.3 删除约束
alter table bbs_user drop constraint ck_user_name;

--3.2.4 修改约束
alter table bbs_user modify constraint ck_user_name disable;
alter table bbs_user modify constraint ck_user_name enable;

---------------------------第四章 数据CURD操作-----------------------------------
--4.1 插入数据
--4.1.1 insert语句
--4.1.2 一次插入多行
--4.2 删除数据
--4.2.1 使用delete语句
--4.2.2 使用truncate语句
--4.2.3 delete和truncate的同异
--4.3 修改数据
--4.3.1 update语句
--4.4 基本查询
--4.4.1 select语句
--4.4.2 基本查询
--4.4.2.1 包含算数表达式
--4.4.2.2 包含连接表达式
--4.4.2.3 空
值的使用
--4.4.2.4 字段别名、表别名
--4.4.2.5 文本字符串
--4.4.2.6 去掉重复值
--4.4.3 带有限制条件的查询
--4.4.3.1 where语句
--4.4.3.2 where中的比较操作符
--4.4.3.3 where中的逻辑运算符

常用的sql语句,如建表、建索引、建约束条件;以及增删改查和高级查询操作

--4.4.4 给查询结果排序



--4.1 插入数据
--4.1.1 insert语句
语法：
insert into table_name(column_1,column_2,...,column_n) values(value_1,value_2,...,value_n)

--板块信息
insert into bbs_section values(1,'谈天说地','大家在这里谈天说地都可以哇',0);
insert into bbs_section values(2,'谈情说爱','大家在这里干神马都可以',0);
insert into bbs_section values(3,'音乐相关','只允许发布音乐主题哦',0);
insert into bbs_section values(4,'Android开发','Android开发很流行啊',0);
insert into bbs_section values(5,'Java基础','这里是你们这些菜鸟的天堂',0);
insert into bbs_section values(6,'论坛意见','给论坛提意见',0);

--注册新用户
insert into bbs_user(user_id,user_name,user_pass,user_nickname,user_email,user_register_date,user_socre,user_gender,
user_online_status,user_last_online_date,user_last_online_ip,user_birthday)
values(1,'barrywey','123456','木有木有','hello@',sysdate,default,default,1,sysdate,'192.168.1.111',null);

insert into bbs_user
values(2,'helloworld','123456','巴神2','world@',sysdate,default,default,1,sysdate,'192.168.1.111',null);

insert into bbs_user
values(3,'kitty_cat','123456','木有22','bashen2@',sysdate,default,default,1,sysdate,'192.168.1.110',null);

insert into bbs_user
values(4,'black_smith','123456','木有222','bahen222@',sysdate,default,default,1,sysdate,'192.168.1.113',null);

insert into bbs_user
values(5,'whatever_however','123456','木有942','bahen942@',sysdate,default,default,1,sysdate,'192.168.1.115',null);


--发布新帖
insert into bbs_topic
values(1,'哇，木有真的很二也！','我发现巴神真的有点2哦。他总是喜欢说你爷爷的，为什么不说你奶奶的呢？',
sysdate,5,1,0,0,null,null);

insert into bbs_topic
values(2,'关于一点点心得','你跟他说与国际接轨，他就跟你说中国国情。你跟他说中国国情，他就要跟你扯与国际接轨',
sysdate,4,1,0,0,null,null);

insert into bbs_topic
values(3,'你爷爷的','哎，又来了，又是你爷爷的',
sysdate,5,1,0,0,null,null);

insert into bbs_topic
values(4,'大家一起来讨论下木有2不2','木有2不2
，大家一起讨论下吧！',
sysdate,3,1,0,0,null,null);

insert into bbs_topic
values(5,'【木有】我承认我很2','大家不要再讨论鸟，我承认我很2',
sysdate,1,1,0,0,null,null);

常用的sql语句,如建表、建索引、建约束条件;以及增删改查和高级查询操作

insert into bbs_topic
values(6,'Android中关于布局的一点问题','Android布局文件需要写xml文件啊，好麻烦，但是可扩展性提高了，不是硬代码',
sysdate,2,2,0,0,null,null);

insert into bbs_topic
values(7,'Android刷机','怎么刷机啊',
sysdate,4,2,0,0,null,null);

insert into bbs_topic
values(8,'有木有，有木有','有木有，有木有有木有，有木有有木有，有木有有木有，有木有有木有，有木有有木有，有木有有木有，有木有有木有，有木有',
sysdate,1,3,0,0,null,null);

insert into bbs_topic
values(9,'介个比较有意思哇','我也不晓得怎么说鸟，反正就是很有意思，自己看下面的图吧：哈哈哈哈哈哈哈哈哈哈哈哈',
sysdate,4,3,0,0,null,null);

insert into bbs_topic
values(10,'我也晕了','这么多数据，真的让人讨厌哇。。。。。',
sysdate,2,4,0,0,null,null);

insert into bbs_topic
values(11,'管他捏','阿斯顿发送地方撒旦发送的法律阿三点击发送等级分类阿喀琉斯焦点房逻辑绿卡四大佛教绿卡第三届',
sysdate,2,4,0,0,null,null);

insert into bbs_topic
values(12,'建议发奖品','木有奖品，论坛怎么吸引人捏？',
sysdate,4,5,0,0,null,null);

insert into bbs_topic
values(13,'建议加强论坛管理','建议加强论坛管理,好多垃圾贴。',
sysdate,1,5,0,0,null,null);

insert into bbs_reply values(1,'你爷爷的，我怎么知道啊？呵呵。',sysdate,1,2,4);
insert into bbs_reply values(2,'哇哈哈哈哈哈。',sysdate,2,3,1);
insert into bbs_reply values(3,'呃呃呃呃呃呃呃呃呃呃呃呃呃呃',sysdate,3,2,3);
insert into bbs_reply values(4,'啊的算法第三方。',sysdate,4,3,3);
insert into bbs_reply values(5,'权威热舞确认。',sysdate,5,4,2);
insert into bbs_reply values(6,'下次vzcxvzxc。',sysdate,6,5,1);
insert into bbs_reply values(7,'你进口环节考核公开化公开。',sysdate,1,5,2);
insert into bbs_reply values(8,'你没vnmnbmnv呵。',sysdate,2,4,5);
insert into bbs_reply values(9,'23412431324。',sysdate,8,4,4);
insert into bbs_reply values(10,'回家
哈啊打算vcad。',sysdate,9,2,3);

insert into bbs_user_section

常用的sql语句,如建表、建索引、建约束条件;以及增删改查和高级查询操作

values(1,1,1,null);
insert into bbs_user_section values(2,1,2,null);
insert into bbs_user_section values(3,2,1,null);
insert into bbs_user_section values(4,3,2,null);
insert into bbs_user_section values(5,4,3,null);
insert into bbs_user_section values(6,5,2,null);

--4.1.2 一次插入多行

--新建表
create table emp_test as
select * from scott.emp where 1=2;

--插入多行
insert into emp_test(empno,ename,job,mgr)
select empno,ename,job,mgr from scott.emp;

--4.2 删除数据
--4.2.1 使用delete语句
语句：
delete from 表名 where 条件;

delete from bbs_topic where topic_id > 10;
delete from bbs_topic; --删除所有数据

--4.2.2 使用truncate语句
语法：
truncate table 表名; --将表中的数据清空
truncate table bbs_topic;

--4.2.3 delete和truncate的同异
区别：
You cannot roll back a TRUNCATE statement. --不能回滚（自动提交数据）
Oracle also deallocates all space used by the removed rows --释放空间

--4.3 修改数据
--4.3.1 update语句
语法：
update 表名 set 列=新值[,列2=新值2,..,列n=新值n] where 条件;
update bbs_user set user_gender = '男' where user_id = 2 or user_id = 4;

update bbs_user set user_birthday = sysdate where user_id = 1;
update bbs_user set user_birthday = to_date('1990-01-13','yyyy-mm-dd') where user_id = 2;
update bbs_user set user_birthday = to_date('1989-03-28 14:48:32','yyyy-mm-dd hh24:mi:ss') where user_id = 3;
update bbs_user set user_birthday = to_date('1987-05-24 18:17:00','yyyy-mm-dd hh24:mi:ss') where user_id =
4;
update bbs_user set user_birthday = to_date('1985-09-12 20:34:48','yyyy-mm-dd hh24:mi:ss') where user_id = 5;

常用的sql语句,如建表、建索引、建约束条件;以及增删改查和高级查询操作

to_date(字符串,格式) --将字符串按照指定的格式转换成date数据类型

--4.4 基本查询
--4.4.1 select语句
select 列1,列2,...,列n from 表 where 条件 order by 列
select user_id,user_name,user_pass,user_birthday,user_nickname from bbs_user;
select * from bbs_user; --效率低

--4.4.2 基本查询
--4.4.2.1 包含算数表达式(number、null、date)
select user_id,user_name,user_pass,user_birthday,user_birthday+10
from bbs_user;

select user_id *0.25 + 100 ,user_id,user_pass,user_name
from bbs_user;

select topic_id,topic_title,topic_user_id,
(topic_user_id + 100)/2.5*0.35,
(topic_last_reply_user_id + 100)*3.5/0.34
from bbs_topic;

--4.4.2.2 包含连接表达式
select topic_id,topic_title,topic_title || '-->这里是测试数据'
from bbs_topic;

select topic_id,topic_title,topic_title || '-->这里是测试数据' || '-->系统时间：' || to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
from bbs_topic;

--4.4.2.3 空值的使用
--在null值身上进行任何操作得到结果均为null

--4.4.2.4 字段别名、表别名
select * from bbs_user;

select user_id as 编号,user_name as 登录名,user_pass as 密码,user_nickname as 昵称
from bbs_user;

select user_id as "my name",user_name as "我的 登录名",user_pass as 密码,user_nickname as 昵称
from bbs_user;

select er_id as 编号, er_name as 登录名
from bbs_user u;

--4.4.2.5 文本字符串
select user_id as 编号, '你爷爷' as 姓名
from bbs_user;

--4.4.2.6 去掉重复值
select * from bbs_user_section;

select distinct us_user_id from bbs_user_section;

--4.4.3 带有限制条件的查询
--4.4.3.1 where语句
语法：
select 列1,列2,...,列n
from 表
where 条件

--4.4.3.2 where中的比较操作符
> < >= <= <> =
select * from bbs_user;
select * from bbs_user where user_gender = '男';
select * from bbs_user where user_gender <> '男';

select * from bbs_user
where user_birthday> to_date('1989-12-31','yyyy-mm-dd');

select * from bbs_topic;
select * from bbs_reply;

--4.4.3.3 where中的逻辑运算符
and or not

select * from bbs_user
where us
er_gender <> '男' and user_birthday> to_date('1989-12-31','yyyy-mm-dd');

like -- 通配符 %(任意长度的任意字符) _(一个长度的任意字符)
select * from bbs_use

常用的sql语句,如建表、建索引、建约束条件;以及增删改查和高级查询操作

r
where user_name like '%A%'; --字符串大小写敏感

select * from bbs_user where user_name like '%a%' or user_name like '%A%';

select * from bbs_user where user_name not like '%a%';

select * from bbs_user where user_nickname like '_张%';

in -- 指定在某个范围之内
select * from bbs_user where user_gender in ('男','女');

select * from bbs_topic where topic_reply_count in (0,5);

select * from bbs_user where user_gender not in ('男','女');

--查询空值
select * from bbs_topic;

select * from bbs_topic where topic_last_reply_date is null;

select * from bbs_topic where topic_last_reply_date is not null;

--
select * from bbs_user
where user_birthday >= to_date('1985-01-01','yyyy-mm-dd')
and
user_birthday <= to_date('1989-12-31','yyyy-mm-dd');

select * from bbs_user
where user_birthday between to_date('1985-09-12 00:00:00','yyyy-mm-dd hh24:mi:ss')
and to_date('1989-03-28 23:59:59','yyyy-mm-dd hh24:mi:ss');
--4.4.4 给查询结果排序

语法：
select 列名
from 表
where 条件
order by 列 [ASC | DESC]

select * from bbs_topic order by topic_id desc;
select * from bbs_topic order by topic_date asc;

select * from bbs_topic
order by topic_date, topic_id; --按照topic_date排序，若有相同值则按照topic_id排序


---------------------------第五章 高级查询-----------------------------------
--5.1 函数查询
--5.1.1 单行函数
--5.1.1.1 字符函数
--5.1.1.2 数字函数
--5.1.1.3 日期函数
--5.1.1.4 转换函数
--5.1.1.5 其他函数
--5.1.2 分组函数
--5.1.2.1 常用分组函数
--5.1.2.2 使用group by对数据分组
--5.1.2.3 使用having对组结果进行限制
--5.2 多表连接查询
--5.2.1 多表链接分类
--5.2.2 等值连接
--5.2.3 非等值连接
--5.2.4 外连接
--5.2.5 自连接
--5.3 子查询
--5.3.1 子查询类型
--5.3.2 单行子查询
--5.3.3 多行子查询
--5.3.4 多列字查询



--5.1 函数查询
--5.1.1 单行函数
--5.1.1.1 字符函数

lower(char)
select lower('THIS IS A VERY NICE MORNING. hello') as 问好 from dual;
select lower('大家早上好，你们好吗？') as 问好 from dual;
upper(char)
select upper('i love this game. NBA.') from dual;
initcap(ch
ar)
select initcap('i love this game. NBA. 我爱这个游戏！') from dual;
select initcap('i love this game.') || 'NBA' from dual;
ascii(char

常用的sql语句,如建表、建索引、建约束条件;以及增删改查和高级查询操作

)
select ascii('你') from dual; --14990752
chr(number)
select chr(14990752) from dual; --你
concat(char1,char2)
select concat('妈的','是个大好淫') from dual;
ltrim(char)
select ltrim(' 你爷爷的 你爷爷 ') from dual;
rtrim(char)
select rtrim(' 你爷爷的 你爷爷 ') from dual;
lpad(char1,number[,char2])
select lpad('%',10) from dual;
select lpad('%',20,'*') from dual;
rpad(char1,number[,char2])
select rpad('%',10) from dual;
select rpad('%',20,'*') from dual;

select user_id as 编号,user_name as 登录名,
user_birthday as 畜生年月,
to_char(sysdate,'yyyy') - to_char(user_birthday,'yyyy') as 年龄,
rpad('_', to_char(sysdate,'yyyy') - to_char(user_birthday,'yyyy'),'_' ) as 条形
from bbs_user;
length(char)
select length(rpad('%',20,'*')) from dual;
select length('hello') from dual;
select length('hel你lo') from dual;
replace(char1,string1[,string2])
select replace('中华人民共和国中央人民政府','人') from dual;
select replace('中华人民共和国中央人民政府','人','不是人') from dual;
instr(string,substring[,position][,occurrence])
select instr('中华人民共和国中央人民政府','人') from dual;
select instr('中华人民共和国中央人民政府','人',4) from dual;
select instr('中华人民共和国中央人民政府','人',1,3) from dual;
select instr('中华人民共和国中央人民政府','人',-9,1) from dual;
substr(string,number[,number])
select substr('中华淫民共和国中央淫民政府',4) from dual;
select substr('中华淫民共和国中央淫民政府',-4) from dual;
select substr('中华淫民共和国中央淫民政府',4,4) from dual;
select substr('中华淫民共和国中央淫民政府',4,-4) from dual;

--5.1.1.2 数字函数
abs
tan
cos
sin
power(m,n)
select power(2,4) from dual;
sqrt(n)
select sqrt(2) from dual;
floor(n)--地板 小于或等于n的最
大整数
select floor(1.34) from dual;
select floor(-1.34) from dual;
ceil(n)--天花板 大于或等于n的最小整数
select ceil(1.34) from dual;

常用的sql语句,如建表、建索引、建约束条件;以及增删改查和高级查询操作

select ceil(-1.34) from dual;
round(number[,integer])--四舍五入
select round(3.1415926) from dual;
select round(3.1415926,3) from dual;
trunc(number[,integer]) --截取数字
select trunc(3.1415926) from dual;
select trunc(3.1415926,3) from dual;
dbms_random.value([number,number]) --产生随机数
select dbms_random.value from dual; [0,1)
select dbms_random.value(10,20) from dual; [number1 ~ number2)

--5.1.1.3 日期函数
round(date[,fmt])
select round(sysdate) from dual;
select round(sysdate,'year') from dual;
trunc(date[,fmt])
select trunc(sysdate) from dual;
select trunc(sysdate,'year') from dual;
sysdate
select sysdate from dual;
months_between(date1,date2)
select round(abs(months_between(to_date('2011-01-01','yyyy-dd-mm'),sysdate)),1) from dual;
add_months(date,number) --在date日期上加number个月以后的结果
select add_months(sysdate,10) from dual;
next_day(date,char) --下一个星期char
select next_day(next_day(sysdate,'星期日'),'星期日') from dual;
last_day(date) --包括date在内地月份中最后一天
select last_day(sysdate) from dual;

--5.1.1.4 转换函数
to_char(date[,fmt])
to_char(number[,fmt])
select '当前时间为：' || to_char(sysdate) from dual;
select '当前时间为：' || to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

select '年龄为：' || 100 from dual;
select '年龄为：' || to_char(100) from dual;
select '年龄为：' || to_char(100,'C999,999,999.99') from dual;
select '年龄为：' || to_char(100,'L000,000,000.00') from dual;
select to_char(50,'rn') from dual;
select to_char(3999,'RN') from dual;

to_date(char[,fmt])


to_number(char[,fmt])
select to_number('12345') + 5 from dual;
select to_number('12345.456') from dual;

--5.1.1.5 其他函数
user
select user from dual;
uid
select uid from dual;
select uid as 编号,user as 用户名 from dual;
nvl(expr1,expr2)
select nvl(null,'是空的') from dual;
select nvl('不空','时空的') from dual;

select topic_id as 编号,topic_title as 标题,
nvl(to_char(topic_last_reply_user_id),'无') as 最后回复用户ID
from bbs_topic;
nvl2(expr1,expr2,expr3)

--5.1.2 分组函数
--5
.1.2.1 常用分组函数
--聚合函数：count avg sum min max
select user_id as 编号,user_name as 登录名,user_birthday as 畜生年月,
to_char(sysdate,'yyyy') - to_char(use

常用的sql语句,如建表、建索引、建约束条件;以及增删改查和高级查询操作

r_birthday,'yyyy') as 年龄
from bbs_user
order by 年龄 desc; --等所有数据过滤完成之后，在产生的结果集身上执行排序

select max(user_birthday),min(user_birthday) from bbs_user;

select max(to_char(sysdate,'yyyy') - to_char(user_birthday,'yyyy')) as 最大年龄,
min(to_char(sysdate,'yyyy') - to_char(user_birthday,'yyyy')) as 最小年龄,
avg(to_char(sysdate,'yyyy') - to_char(user_birthday,'yyyy')) as 平均年龄,
sum(to_char(sysdate,'yyyy') - to_char(user_birthday,'yyyy')) as 年龄和
from bbs_user;

select max(losal),min(losal),sum(losal),avg(losal),count(losal), sum(losal)/count(losal)
from scott.salgrade;

--select count(*) from bbs_user;
select count(user_id) from bbs_user; --数据行

--创建表(附带数据)
create table salgrade_2 as
select * from scott.salgrade;

--创建表(木有数据)
create table salgrade_22 as
select * from scott.salgrade where 1=2;

--一次插入多行
insert into salgrade_22
select * from scott.salgrade where grade >= 3;

--5.1.2.2 使用group by对数据分组

select topic_id,topic_title,topic_date,topic_user_id,topic_section_id
from bbs_topic;

select count(topic_id) from bbs_topic where topic_section_id = 1;

select topic_section_id as 板块编号,count(topic_id) as 帖子数
from bbs_topic
group by topic_section_id,topic_id,topic_title;

select deptno as 部门编号, count(empno) as 雇员人数
from emp
group by deptno;

--5.1.2.3 使用having对组结果进行限制

select * from emp;

--先从整个结果集中过滤一部分数据再分组
select deptno as 部门编号, count(empno) as 雇员人数
from emp
where sal >= 2000 --1
group by deptno --2

--having语句在分组以后组内起作用
select deptno as 部门编号, count(empno) as 雇员人数
from emp
group by deptno
having count(empno) >= 5;


select column_name
from table_name
where condition --整个结果集中过滤数据
group by column_name having condition --在组中过滤数据
order by column_name --在最后结果集上排序


--5.2 多表连接查询
--5.2.1 多表链接分类
等值连接、非等值连接、外连接、自连接
--SQL92标准 8i
--Oracle标准

--5.2.2 等值连接
oracle:
select er_name as 登录名,t.topic_title as 帖子标题,t.topic_date as 发布时间
from bbs_
user u,bbs_topic t
where er_id = t.topic_user_id;
sql92:
select er_name as 登录名,t.topic_title as

常用的sql语句,如建表、建索引、建约束条件;以及增删改查和高级查询操作

帖子标题,t.topic_date as 发布时间
from bbs_user u inner join bbs_topic t on (er_id = t.topic_user_id);

oracle:
select _id as 编号, _user_id as 版主编号 , er_name as 版主名, s.section_name as 板块名
from bbs_user_section us, bbs_user u,bbs_section s
where _user_id = er_id and _section_id = s.section_id;
sql92:
select _id as 编号, _user_id as 版主编号 , er_name as 版主名, s.section_name as 板块名
from bbs_user_section us inner join bbs_user u on(_user_id = er_id)
inner join bbs_section s on (_section_id = s.section_id);

--5.2.3 非等值连接
> < >= <= <>
select er_name as 登录名,t.topic_title as 帖子标题,t.topic_date as 发布时间
from bbs_user u,bbs_topic t
where er_id <> t.topic_user_id;

--5.2.4 外连接
oracle:
select t.topic_id as 主贴编号,t.topic_title as 主贴标题,t.topic_date as 发贴时间,
r.reply_content as 回帖内容,r.reply_date as 回帖时间
from bbs_topic t,bbs_reply r
where t.topic_id = r.reply_topic_id(+);
sql92:
select t.topic_id as 主贴编号,t.topic_title as 主贴标题,t.topic_date as 发贴时间,
r.reply_content as 回帖内容,r.reply_date as 回帖时间
from bbs_topic t left outer join bbs_reply r on(t.topic_id = r.reply_topic_id);


select t.topic_id as 主贴编号,t.topic_title as 主贴标题,t.topic_date as 发贴时间,
nvl(r.reply_content,'<木有回帖>') as 回帖内容,r.reply_date as 回帖时间
from bbs_topic t,bbs_reply r
where t.topic_id = r.reply_topic_id(+);

--5.2.5 自连接

select
t1.empno as 雇员编号, t1.ename as 雇员姓名 , t1.job as 职务,
t2.empno as 上司编号, nvl(t2.ename,'BOSS') as 上司姓名
from emp t1, emp t2
where t1.mgr = t2.empno(+);


--5.3 子查询
--5.3.1 子查询类型
单行子查询
多行子查询
多列子查询
1、子查询会先于父查询执行
2、子查询允许嵌套，最里面的子查询最先执行
3、子查询一般出现在比较操作符的右边

--5.3.2 单行子查询

select * from bbs_user
where user_birthday < (select user_birthday from bbs_user where user_name = 'kitty_cat');

select * from bbs_topic
where topic_user_id = (select user_id from bbs_user where user_name = 'barrywey');

select * from emp
where sal >
(select sal from emp where ename = upper('allen'))
and deptno = (select deptno from emp where ename = upper('allen'));

--5.3.3 多行子查询

an

常用的sql语句,如建表、建索引、建约束条件;以及增删改查和高级查询操作

y all in
> any -- 大于子查询找到的最小值
< any -- 小于子查询找到的最大值
> all -- 大于子查询找到的最大值
< all -- 小于字查询找到的最小值

select * from bbs_topic
where topic_user_id > any (select user_id from bbs_user where user_name like '%a%');

select * from bbs_topic
where topic_user_id < any (select user_id from bbs_user where user_name like '%a%');

select * from bbs_topic
where topic_user_id < all (select user_id from bbs_user where user_name like '%a%');

select * from bbs_topic
where topic_user_id > all (select user_id from bbs_user where user_name like '%a%');

--5.3.4 多列字查询

select * from emp where
sal = (select sal from emp where ename = upper('allen'))
and
deptno = (select deptno from emp where ename = upper('allen'))
and
ename <> upper('allen');

select * from emp
where (sal,deptno) in (select sal,deptno from emp where ename = upper('allen'))
and ename <> upper('allen');

--数据分页
页码：page_num 1
页大小：page_size 3

rownum : 伪列 查询结果集的序列

select * from bbs_topic;

--第一种分页
select b.*
from (select rownum as rn, a.*
from (select * from bbs_topic order by topic_date) a) b
where b.rn >= 7 and b.rn <= 9;

select b.*
from (select rownum as rn, a.*
from (select * from bbs_topic order by topic_date) a) b
where b.rn between 7 and 9;

--第二种分页(效率高)
select b.* from
( select rownum as rn, a.* from
(select * from bbs_topic order by topic_date) a
where rownum <= 9 ) b
where b.rn >= 7;

本文来源：https://www.bwwdw.com/article/p4rj.html