Oracle 基础查询
1. 查询内容区分大小写
-- 通过lower屏蔽大小写字母的差别
select * from scott.emp where lower(ename) = 'smith';
-- MySQL不区分大小写(如果要区分使用binray关键字在where字段前)
select * from t_user where binary name = 'tom';
2. 别名
-- 如果别名中含有空格需要使用""
-- 列别名的as可以省略,表别名不加as
-- 列别名不参与where,但是可以order by
select e.empno as "emp no", ename, e.sal esal from scott.emp e
where e.sal >= 2000
order by esal desc;
3. distinct(去重)
-- 所有列都相同的该行会在查询中去重
select distinct * from scott.emp;
-- 单个列/多个列
select distinct job from scott.emp;
select distinct deptno, job from scott.emp order by deptno asc;
4. ±*/
统统都是数学运算(+不是字符串拼接,如果字符串值无法转为数值,则报错)
5. '单引号转义
create table t_user (name varchar(10));
insert into t_user values ('tom''s dog');
insert into t_user values ('tom is dog');
select * from t_user where name = 'tom''s dog';
select * from t_user where name like '%''%';
6. char的坑
create table t_user (name char(10));
insert into t_user values ('tom');
insert into t_user values ('tom12');
select * from t_user where name = 'tom';
select '[' || name || ']' from t_user;
select * from t_user where name like 'tom%__';
7. &转义
&在PL/SQL中是变量输入提示符!!!
create table t_user (
id int,
name varchar(10)
);
insert into t_user values (7788, 'P&G');
insert into t_user values (&userid, '&username');
insert into t_user values (7788, 'P' || '&' || 'G');
insert into t_user values (7788, 'P' || chr(38) || 'G');
8. like中_的转义
create table t_user (name varchar(10));
insert into t_user values ('tom12');
insert into t_user values ('tom12__');
select * from t_user where name like 'tom%\_\_' escape '\';
-- tom12__
9. 数据的导入和导出
注意:导入或者导出表的列的个数或者格式要与目标表匹配!
-- 全表的导出
create table emp as select * from scott.emp;
-- 如何只导出表结构,而不要其中的数据(因为这个表我是要作为日后的增量备份的表,当前已有的数据不需要)
create table emp_bak (eno, ename, esal) as select empno, ename, sal from scott.emp where 1 < 0;
-- 现在我要将10号部门的雇员都备份到emp_bak表
insert into emp_bak (ename, esal, eno) select ename, sal, empno from scott.emp where deptno = 10;