//字符串的合并 使用 ||或者count(r1,r2)
select first_name||last_name from employees;
select concat(first_name,last_name) from employees;
//查询当前的系统日期
select sysdate from dual;
//别名的 使用,对于中文必须使用""
select salary*12 "年薪" from employees;
//空字符串的使用 is null
select * from employees where manager_id is null;
//去除重复的记录;
select distinct first_name from employees;
//查询Neena曾经从事过的工作----带有限制条件的查询
select * from employees where first_name='Neena' or last_name = 'Neena';
--比较运算符 注意: <> 不等于
//查询薪资在8000到15000之间? ----between 900 and 500
select * from employees where salary between 8000 and 15000;
// in 的使用
select * from employees where salary in (8000,9000);
select * from employees;
//like 的使用 _ 代表一个字符 % 代表多个字符
select * from employees where first_name like '_on%';
insert into username values (1);
select * from username;
/--order by的使用.默认是升序desc,降序是asc
select * from employees order by salary;
select * from employees order by salary asc;
--多字段的排序
select * from employees order by salary,first_name desc;
姓名中有e或者a的员工? 使用多行函数 concat
select * from employees where concat(first_name,last_name) like '%a%' or first_name||last_name like '%e%'
--大小写转换函数 Lower upper initcap(首字母大写)
select lower(first_name) from employees;
select upper(first_name),initcap (first_name) from employees;
--字符串截取 sub
select substr(first_name,1,3) from employees;
--返回字符串的长度
select lengthb(first_name) from employees where first_name like '%on%';
--lpad 在指定 的字符串 的左边添加字符 7代表中长度
select lpad('hellow',7,'*') from dual;
--在右边添加指定的字符串
select rpad ('hellow',8,'*') from dual;
--字符串的截取 trim
select trim ('s' from 'string') from dual;
--数字字符 --四舍五入 3 表示留3位小数
select round(45.55555,3) from dual;
--trunc 对数字进行截取
select trunc(23.77777777,3) from dual;
--mod 相除返回其余数
select mod(4,3) from dual;
---隐式转换
select '6666'||77 from dual;
select '6666'+77 from dual;
--显示转换
select to_char(110.110,'999.9900') from dual;
select to_char(salary,'$9999999.9') from employees;
select to_char(salary,'l999999.9') from employees;
--案例一:把¥3100.00 转回成数字
select to_number('¥3100.00','L99999.99') from dual;
--案例三:打印出"xxxx年xx月xx日 xx:xx:xx"格式的日期和时间
select to_char(sysdate,'yy"年"mm"月"dd"日" hh"小数" mm"秒"') from dual;
--增加日志
select add_months(sysdate,12) "明年今日" from dual;
select current_date from dual;//返回当前对话框的时间
--找出日期或间隔值的字段值
select extract(month from sysdate) from dual;
--返回月份中最后一天
select last_day(sysdate) from dual;
--next_day 返回下周的对应日期
select next_day(sysdate,'星期一') from dual;
--日期f和s间相差月数
select months_between(sysdate,'2017-02-02') from dual;
select months_between(sysdate,'04-5月-09')from dual;
--nvl将null值替换成什么
select NVL(1,'01') from dual;
如果这个人有奖金点,那就他的工资就是工资+奖金
如果这个人没有奖金点,那就他的工资就没有奖金
select nvl2(commission_pct,'sal+com','sal') from employees;
--nullif(f,t) 比较两个表达式 如果相等 返回空 不然返回第一个表达式
select nullif(3+3,3+2) from dual;
--返回表达式列表中第一个非空表达式的值
select coalesce(null,null,2,3) from dual;
--case表达式
SELECT last_name,
case job_id
when '总经理' then 1.15*salary
when '副总经理' then 10*salary
else salary
end 工资
FROM employees where last_name='OConnell';
--打印公司员工工资等级:5000以下D;5000-10000C;10000-15000B;15000以上A
--其中:nvl(x,y)用于将x为空的改为y值
--trunc用于除法
when 0 then 'DDDDD' 表示当结果值为0 的时候用DDDDD替换
select first_name ,salary ,
case trunc (nvl(salary,0)/5000)
when 0 then 'DDDDD'
else 'AAAAAAAA'
end "等级"
from employees;
select first_name,salary ,
case trunc(nvl(salary,0)/5000)
when 0 then 'AA'
when 1 then 'BB'
else 'CC'
end "等级"
from employees;
-- decode函数
select first_name,salary,
decode(trunc(nvl(salary,0)/5000),
0,'a',
1,'b',
'c' )
from employees;
--常见的分组函数
select avg(commission_pct),sum(commission_pct)/count(employee_id) from employees;
select avg(commission_pct) from employees;--平均奖金
select sum(commission_pct) from employees;
select count(salary) from employees;
select max(salary) from employees;
--先分组再统计:查询出每一个部门的最高工资
select max(salary) ,department_id from employees group by department_id;
select max(salary), department_id from employees group by department_id
--多表连接
--隐式连接
select * from employees ,department;
Select * from product, productdir;
--内连接
select * from employees e
left join employees em on(e.manager_id=em.employee_id)
--查询大于公司平均工资的员工姓名
--两部:先查询出平均工资
select avg(salary) from employees;
--将上一个查询条件当成下一个语句的条件
select first_name ,salary from employees where salary>(select avg(salary) from employees);
--请查询David与James的所有部门的所有员工
select first_name ,salary from employees
where department_id
in (select department_id from employees where first_name in ('David','James'));
--案例题一:查询1995,1996,1997,1998年各个部门新进员工数
--先查询出几个年份新进的员工
select to_char(hire_date,'yyyy') "年份" ,department_id,count(employee_id)
from employees where to_char(hire_date,'yyyy') in (1995,1996,1997,1998)
group by to_char(hire_date,'yyyy'),department_id;
--查询出 人员最多的三个部门
--分页需要使用top-n表达式,但是top-n并非关键字,只是一种语法 的称呼
select t.department_id,t."人数",rownum from (
select department_id,count(employee_id) "人数" from employees
group by department_id
order by "人数")t
where rownum<=5
//查询出每个部门的员工人数 并且安装人数进行排序
select department_id did,count(employee_id) nm from employees group by department_id order by nm
//取出前5的人员
select * from (
select t.did,t.nm ,rownum rm from (
select department_id did,count(employee_id) nm from employees group by department_id order by nm
)t where rownum <=10
) t2 where rm>5
Create table CJ(name char(10),subject char(10),result int);
insert into CJ(name,subject,result) values('张三','语文',99);
insert into CJ(name,subject,result) values('张三','数学',86);
insert into CJ(name,subject,result) values('张三','英语',75);
insert into CJ(name,subject,result) values('李四','语文',78);
insert into CJ(name,subject,result) values('李四','数学',85);
insert into CJ(name,subject,result) values('李四','英语',78);
---交叉表
select t.name,
sum(decode(trim(subject),'语文',t.result,0)) as "语文",
sum(decode(trim(subject),'数学',t.result,0)) as "数学",
sum(decode(trim(subject),'英语',t.result,0)) as "英语"
from cj t
group by name
---拷贝表
insert into employees2 select * from employees ;
create table employees3 as select * from employees;
create table cj_bak as select * from cj where 1<>1 //只拷贝表结构
--修改
update employees3 set salary=salary+1000;
--删除
delete employees3 where employee_id=2;
--合并
select * from order2014
union all
select * from order2015
---事务
insert into product_new(id,name) values (10,'音响');
SAVEPOINT undo1;
insert into product_new(id,name) values (11,'音响');
rollback to undo1;
--主键就可以直接这样写:
insert into employees(employees_id,last_name….)
values(emp_seq.nextval,’zhang’……)
select first_name||last_name from employees;
select concat(first_name,last_name) from employees;
//查询当前的系统日期
select sysdate from dual;
//别名的 使用,对于中文必须使用""
select salary*12 "年薪" from employees;
//空字符串的使用 is null
select * from employees where manager_id is null;
//去除重复的记录;
select distinct first_name from employees;
//查询Neena曾经从事过的工作----带有限制条件的查询
select * from employees where first_name='Neena' or last_name = 'Neena';
--比较运算符 注意: <> 不等于
//查询薪资在8000到15000之间? ----between 900 and 500
select * from employees where salary between 8000 and 15000;
// in 的使用
select * from employees where salary in (8000,9000);
select * from employees;
//like 的使用 _ 代表一个字符 % 代表多个字符
select * from employees where first_name like '_on%';
insert into username values (1);
select * from username;
/--order by的使用.默认是升序desc,降序是asc
select * from employees order by salary;
select * from employees order by salary asc;
--多字段的排序
select * from employees order by salary,first_name desc;
姓名中有e或者a的员工? 使用多行函数 concat
select * from employees where concat(first_name,last_name) like '%a%' or first_name||last_name like '%e%'
--大小写转换函数 Lower upper initcap(首字母大写)
select lower(first_name) from employees;
select upper(first_name),initcap (first_name) from employees;
--字符串截取 sub
select substr(first_name,1,3) from employees;
--返回字符串的长度
select lengthb(first_name) from employees where first_name like '%on%';
--lpad 在指定 的字符串 的左边添加字符 7代表中长度
select lpad('hellow',7,'*') from dual;
--在右边添加指定的字符串
select rpad ('hellow',8,'*') from dual;
--字符串的截取 trim
select trim ('s' from 'string') from dual;
--数字字符 --四舍五入 3 表示留3位小数
select round(45.55555,3) from dual;
--trunc 对数字进行截取
select trunc(23.77777777,3) from dual;
--mod 相除返回其余数
select mod(4,3) from dual;
---隐式转换
select '6666'||77 from dual;
select '6666'+77 from dual;
--显示转换
select to_char(110.110,'999.9900') from dual;
select to_char(salary,'$9999999.9') from employees;
select to_char(salary,'l999999.9') from employees;
--案例一:把¥3100.00 转回成数字
select to_number('¥3100.00','L99999.99') from dual;
--案例三:打印出"xxxx年xx月xx日 xx:xx:xx"格式的日期和时间
select to_char(sysdate,'yy"年"mm"月"dd"日" hh"小数" mm"秒"') from dual;
--增加日志
select add_months(sysdate,12) "明年今日" from dual;
select current_date from dual;//返回当前对话框的时间
--找出日期或间隔值的字段值
select extract(month from sysdate) from dual;
--返回月份中最后一天
select last_day(sysdate) from dual;
--next_day 返回下周的对应日期
select next_day(sysdate,'星期一') from dual;
--日期f和s间相差月数
select months_between(sysdate,'2017-02-02') from dual;
select months_between(sysdate,'04-5月-09')from dual;
--nvl将null值替换成什么
select NVL(1,'01') from dual;
如果这个人有奖金点,那就他的工资就是工资+奖金
如果这个人没有奖金点,那就他的工资就没有奖金
select nvl2(commission_pct,'sal+com','sal') from employees;
--nullif(f,t) 比较两个表达式 如果相等 返回空 不然返回第一个表达式
select nullif(3+3,3+2) from dual;
--返回表达式列表中第一个非空表达式的值
select coalesce(null,null,2,3) from dual;
--case表达式
SELECT last_name,
case job_id
when '总经理' then 1.15*salary
when '副总经理' then 10*salary
else salary
end 工资
FROM employees where last_name='OConnell';
--打印公司员工工资等级:5000以下D;5000-10000C;10000-15000B;15000以上A
--其中:nvl(x,y)用于将x为空的改为y值
--trunc用于除法
when 0 then 'DDDDD' 表示当结果值为0 的时候用DDDDD替换
select first_name ,salary ,
case trunc (nvl(salary,0)/5000)
when 0 then 'DDDDD'
else 'AAAAAAAA'
end "等级"
from employees;
select first_name,salary ,
case trunc(nvl(salary,0)/5000)
when 0 then 'AA'
when 1 then 'BB'
else 'CC'
end "等级"
from employees;
-- decode函数
select first_name,salary,
decode(trunc(nvl(salary,0)/5000),
0,'a',
1,'b',
'c' )
from employees;
--常见的分组函数
select avg(commission_pct),sum(commission_pct)/count(employee_id) from employees;
select avg(commission_pct) from employees;--平均奖金
select sum(commission_pct) from employees;
select count(salary) from employees;
select max(salary) from employees;
--先分组再统计:查询出每一个部门的最高工资
select max(salary) ,department_id from employees group by department_id;
select max(salary), department_id from employees group by department_id
--多表连接
--隐式连接
select * from employees ,department;
Select * from product, productdir;
--内连接
select * from employees e
left join employees em on(e.manager_id=em.employee_id)
--查询大于公司平均工资的员工姓名
--两部:先查询出平均工资
select avg(salary) from employees;
--将上一个查询条件当成下一个语句的条件
select first_name ,salary from employees where salary>(select avg(salary) from employees);
--请查询David与James的所有部门的所有员工
select first_name ,salary from employees
where department_id
in (select department_id from employees where first_name in ('David','James'));
--案例题一:查询1995,1996,1997,1998年各个部门新进员工数
--先查询出几个年份新进的员工
select to_char(hire_date,'yyyy') "年份" ,department_id,count(employee_id)
from employees where to_char(hire_date,'yyyy') in (1995,1996,1997,1998)
group by to_char(hire_date,'yyyy'),department_id;
--查询出 人员最多的三个部门
--分页需要使用top-n表达式,但是top-n并非关键字,只是一种语法 的称呼
select t.department_id,t."人数",rownum from (
select department_id,count(employee_id) "人数" from employees
group by department_id
order by "人数")t
where rownum<=5
//查询出每个部门的员工人数 并且安装人数进行排序
select department_id did,count(employee_id) nm from employees group by department_id order by nm
//取出前5的人员
select * from (
select t.did,t.nm ,rownum rm from (
select department_id did,count(employee_id) nm from employees group by department_id order by nm
)t where rownum <=10
) t2 where rm>5
Create table CJ(name char(10),subject char(10),result int);
insert into CJ(name,subject,result) values('张三','语文',99);
insert into CJ(name,subject,result) values('张三','数学',86);
insert into CJ(name,subject,result) values('张三','英语',75);
insert into CJ(name,subject,result) values('李四','语文',78);
insert into CJ(name,subject,result) values('李四','数学',85);
insert into CJ(name,subject,result) values('李四','英语',78);
---交叉表
select t.name,
sum(decode(trim(subject),'语文',t.result,0)) as "语文",
sum(decode(trim(subject),'数学',t.result,0)) as "数学",
sum(decode(trim(subject),'英语',t.result,0)) as "英语"
from cj t
group by name
---拷贝表
insert into employees2 select * from employees ;
create table employees3 as select * from employees;
create table cj_bak as select * from cj where 1<>1 //只拷贝表结构
--修改
update employees3 set salary=salary+1000;
--删除
delete employees3 where employee_id=2;
--合并
select * from order2014
union all
select * from order2015
---事务
insert into product_new(id,name) values (10,'音响');
SAVEPOINT undo1;
insert into product_new(id,name) values (11,'音响');
rollback to undo1;
--主键就可以直接这样写:
insert into employees(employees_id,last_name….)
values(emp_seq.nextval,’zhang’……)