//* 进行查询,运行效率偏低。可读性差。
select * from 表名
select id,name,name,email... from 表名
//对列的内容进行运算 (+-*/)
select last_name,salary*12 from employees;//列起别名 alias,as关键字可以省略
select 列名 [as] 别名,列名 [as] 别名 from employees;//多列内容的连接
select first_name||last_name as name , salary from employees;
1.2 条件查询where/order by
比较查询 ><=!=>=<=
逻辑运算 and or
特殊谓词 in 或者 not in
between .... and 在一个范围 或者 区间中 (闭区间)
not between 100 and 200//(闭区间)
is null/is not null
like '%A%'//单列排序/多列排序
select name,salary from employees
where salary >600
order by salary desc,name asc;//desc降序 默认asc升序。
1.3 内置函数
//1 sysdate 获取当前系统时间
select sysdate from employees
//oracle中的虚表,哑表 dual
select sysdate from dual
//2 to_char(日期,’日期格式’) 把一个日期类型,转换成一个字符串 //yyyy/mm/dd/day/hh/mi/ss 日期格式
select to_char(sysdate,'mm') from dual;//3 to_date(‘字符串’,‘日期格式’)把字符串转换成日期
to_date函数 主要应用在数据的插入过程。
1.4 组函数
针对于一组数据的操作。默认在不分组的情况下,会把一张表的数据划分成一个组。
//最大值 max()/min()/avg()/sum()/count(*)
select deepartment_id,count(*) from employees group by department_id;//查询1997年每个月份入职的员工人数
select to_char(hire_date,'mm'),count(*)
from employees
where to_char(hire_date,'yyyy')='1997'
group by to_char(hire_date,'mm');
小结:
1. 只有出现在group by中的列名,才可以书写在select字句中。
2. 如果列名出现在组函数中,也可以书写在select字句中。
3. 普通的内置函数,如果应用在了group by子句中,可以书写在select字句中。
1.5 having 的使用
分组之后的条件判断
//平均工资大于5000 这些部门
select department_id,avg(salary)
from employees
group by department_id
having avg(salary)>5000// having的使用依据: 如果你的条件判断中,涉及到应用组函数的条件判断,则需要使//用having,其他条件判断 需使用 where//查询1997年个月中 大于2人 入职的这些月份及其员工数
select to_char(hire_date,'mm') asmouth,count(*)
from employees
where to_char(hire_date,'yyyy')='1997'
group by to_char(hire_date,'mm')
having count(*)>2;
1.6 关键字的使用顺序
select *
from
where
group by
having
order by ...
二、子查询
在一个SQL语句中,嵌套另一个SQL语句。查询效率低,尽量避免使用.
2.1 where子查询
//查询Last_name 为Abel 这个员工的部门名称
select department_name
from departments
where department_id=(select department_id
from employees where last_name='Able')//多值where子查询 //查询last_name 叫做 ‘King’ 这个员工的部门
select department_name
from departments
where department_id in (select department_id
from employees where last_name='King')
2.2 from 子查询
//让sql语句的查询结果,充当表概念。//from 表名 from (sql 语句)
select * from (select employees_id,first_name,last_name,from employees);
三、伪列
特殊列:不需要开发人员手工创建的列,oracle自动创建。
不主动发起查询,看不到伪列的数据内容
3.1 rowid
行数据 在数据库中的唯一标示,代表行数据所对应的物理存储空间
select t.ID,t.NAME,t.PASSWORD,t.ROWID from T_USER t;
注意:可以基于rowid进行查询,查询效率最快。
3.2 rownum 分页操作
对本次查询结果,符合要求的数据,进行标号.
rownum 不能做 > >= = 操作
select t.*,ROWnum from T_USER t where ROWnum<6;
如何应用rownum 做 >= > = 操作?
//前提 对ROWnum进行预处理
select * from (
select t.*,ROWnum r from T_USER t
) where r>3
四、多表操作
外键(foreign key):多张表的关系的体现
4.1 内连接(innner join )
//只会连接2张表中可以连接的数据,不能连接的数据不连接
select e.*,d.*
from employees e
inner join department d
on e.department_id=d.departmnet_id
4. 2 左外连接 (left outer join)
//左边的表全部查询出来,右边的能够连接的连接处来,不能连接的补null。
select e.*,d.*
from employees e
left outer join department d
on e.department_id=d.departmnet_id
4. 3 右外连接 (right outer join)
//右边的表全部查询出来,左边的能够连接的连接处来,不能连接的补null。
select e.*,d.*
from employees e
left outer join department d
on e.department_id=d.departmnet_id
4. 4 多表连接语法。
select a.*,b.*,c.*
from A a
inner join
B b on a._=b._
inner join
C c on b._=c._
4. 5 自连接
自己连接自己,特殊多表连接。
五、DDL创建表
DDL (Data Define Language) 数据的定义语言 表的定义 修改 删除
5.1 建表
create table T_USER
(
id NUMBER(20) not null,
name VARCHAR2(30),
password NUMBER(10),
age NUMBER(10));
alter table T_USER add primary key (ID)
5.2 删表
drop table t_user
如果删除某一张表时,有别的表与其建立了外键关系,则应该先删除子表,在删除父表
可以进行级联删除 在删除表的同时,把约束(外键)同时删除
drop table t_user cascade constraints;
5.3 修改表
1.修改列的类型
alter table 表名 modify 列名 类型
前提条件 列 没有存储数据。
2.修改列的名字
alter table 表名 rename column 老列名 to 新列名
3.追加新列
alter table 表名 add 列名 类型;
六 DML 数据的修改
DML(Data Modify Language) 表中数据的插入 修改 删除
i.插入数据
1.insert into t_user (id,name,birthday) values (1,’suns’,sysdate);
注意:DML 语句 必须执行 commit操作 才可以同步数据库
处理日期 to_date函数处理
2.insert into t_user values(1,’suns’,sysdate);
ii.删除数据
1.delete from t_user 全表所有的数据全部删除
2.delete from t_user where ...条件删除
delete from t_user where id =?
delete from t_user where age in (10,30)
delete from t_user where name like ‘a%’
iii.修改数据
1.update t_user set name=xxx,birthday=xxx,column_name=value 修改全表数据
注意:主键 不能进行修改
update t_user set name=xxx,birthday=xxxx where ....
update t_user set name=xxx,birthday=xxx where id =?
update t_user set name=xxx,birthday=xxx where name =? or id=?
udate t_user set name=xxx,birthday=xxx where name like ‘suns%’
作用:主键的自动生成。
i.创建sequence create sequence suns_seq start with100 increment by 2
ii.如何使用 suns_seq.nextval
insert into t_user values (suns_seq.nextval,’xiaohei’,sysdate);
iii.删除序列
drop sequence suns_seq
8.2 视图 View
把查询的sql语句,命名,存储在数据库中便于后续的重复使用。
i.创建View create view emp_dept_view as (select语句)
ii.如何使用视图
select * from emp_dept_view where ....
注意:1 视图存储的是什么?
视图存储的是sql语句 效率上 和原来书写程序 一样。
2 不建议应用视图进行增删改(DML)的操作
iii.drop view emp_dept_view;
8.3 索引 Index
提高sql语句查询效率 (B+tree,BitMap,FullText)
i.创建索引 create index name_index on t_user(name);
ii.查询 select name from t_user;
注意:1 索引的创建,是占用存储空间(硬盘),不能简单的就创建索引。
2 oracle自动的为pk ,唯一列 加入索引。
iii.drop index name_index;