从零开始学习Oracle_SQL部分

2022-06-09 04:36:01

1. 环境情况

CentOS 7 + oracle 18c

文中将使用oracle中的scott用户进行相关sql的执行。

# login as scott through sqlplus
sqlplus scott/tiger

查询我们可用的表

SQL> select table_name from user_tables;

从零开始学习Oracle_SQL部分

2. 查询（DQL）

查看emp、dept的表结构

SQL> desc emp
SQL> desc dept

从零开始学习Oracle_SQL部分

从零开始学习Oracle_SQL部分

2.1. 简单查询

1）查询emp表所有数据

SQL> select * from emp;

2）指定列名查询

SQL> select ename,sal,comm from emp;

3）查询中拼接列（ || 符的使用）

SQL> select ename||‘ ‘||sal result from emp;

从零开始学习Oracle_SQL部分

4）查询结果去重（distinct关键字）

SQL> select distinct job from emp;

5）条件查询（where关键字）

SQL> select ename, sal, job from emp where job=‘CLERK‘;
SQL> select ename, sal, job from emp where sal between 2000 and 3000;

6）模糊查询（like关键字，%表示0或多个字符，_表示单个字符）

SQL> select * from emp where ename like ‘SM%‘;

7）判断一个表达式/列是否为空

SQL> select ename, sal, comm from emp where comm is null;
SQL> select ename, sal, comm from emp where comm is not null;

8）条件字句中的运算符（or in and 等）

SQL> select ename, sal, job, deptno from emp where sal>1000 or job=‘CLERK‘;
SQL> select ename, sal, job from emp where job in (‘CLERK‘, ‘SALESMAN‘, ‘MANAGER‘);

9）查询结果排序（默认升序asc，降序desc）

SQL> select ename, sal, comm from emp order by sal desc;
SQL> select ename, sal, comm from emp order by 2 desc;    -- 使用列序号表示降序排列的字段
SQL> select ename name, sal salary, comm from emp order by sal desc, comm asc;    -- 优先根据sal降序排序，再根据comm升序排序

10）查询中使用定义的变量

&：每次执行SQL，都需要为变量赋值

&&：第一次执行SQL，需要为变量赋值，以后不需要（退出sqlplus，变量失效）

SQL> select ename, sal, comm from emp where sal>&x;
SQL> select &a, &b from &c where &d;
SQL> define var1=3000;    -- 定义变量var1
SQL> undefine var1;    -- 取消变量var1

11）分批输出查询结果

SQL> select ename, sal from emp order by sal fetch first 5 rows only;    -- fetch最开始的5行
SQL> select ename, sal from emp order by sal offset 2 rows fetch first 3 rows only;    -- 从2开始fetch3行
SQL> select ename, sal from emp order by sal offset 2 rows fetch first 3 rows with ties;

12）分组查询（group by）

SQL> select deptno, avg(sal), max(sal), min(sal), sum(sal) from emp where sal>2000
     group by deptno having avg(sal)>2000 and avg(sal)<4000 order by 2;

13）并（集合查询）

SQL> select distinct job from emp where deptno=30
     union
     select distinct job from emp where deptno=20;
SQL> select distinct job from emp where deptno=30
     union all
     select distinct job from emp where deptno=20;    -- 允许重复值

14）交（集合查询）

SQL> select distinct job from emp where deptno=30
     intersect
     select distinct job from emp where deptno=20;

15）差（集合查询）

SQL> select distinct job from emp where deptno=30
     minus 
     select distinct job from emp where deptno=20;

2.2. 查询中伴随常用函数

1）initcap（使给定字符串每个词的首字母大写）

SQL> select initcap(‘this is a test string‘) from dual;

从零开始学习Oracle_SQL部分

2）lower（将字符串转为小写，upper则相反会转为大写）

SQL> select lower(ename), lower(job) from emp;

3）concat（连接字符串/列）

SQL> select ename, sal, concat(ename,sal) from emp;

从零开始学习Oracle_SQL部分

4）substr（取子字符串）

SQL> select ename, substr(ename, 2, 3) from emp;    -- ename由第2位开始取3个字符

从零开始学习Oracle_SQL部分

5）instr（查找字符/串在目标字符串中的位置）

SQL> select ename, instr(ename, ‘A‘) from emp;    -- 返回A的索引位置，从1开始，未找到返回0

6）lpad（在左边进行填充，rpad则是右边）

SQL> select ename, lpad(ename, 10, ‘#‘) from emp;

从零开始学习Oracle_SQL部分

7）round（四舍五入）

SQL> select round(45.456, 2) from dual;    -- 保留小数点后2位，结果为45.46
SQL> select round(45.456, -1) from dual;    -- 对各位进行四舍五入，结果为50

8）trunc（截取日期或数字）

SQL> select trunc(45.456, 2) from dual;    -- 截取到小数点后2位，其余直接舍弃，结果为45.45
SQL> select trunc(45.456, 0) from dual;    -- 舍弃小数部分，结果为45
SQL> select trunc(45.456, -1) from dual;    -- 舍弃个位，结果为40

9）month_between（日期之间相差的月数）

SQL> select months_between(‘19-MAY-21‘,‘19-MAY-20‘) from dual;    -- 结果为12

10）add_months（加12个月的日期）

SQL> select add_months(sysdate,12) from dual;

11）next_day（下一个周几）

SQL> select next_day(sysdate,2) from dual;    -- 1代表周日（下一个周日），2代表周一（下一个周一）
SQL> select next_day(sysdate, ‘SATURDAY‘) from dual;    -- 下一个周六

12）last_day（对应月份的最后一天）

SQL> select last_day(sysdate) from dual;    -- sysdate所在月份的最后一天

13）to_char（转为字符串类型）

SQL> select to_char(sysdate, ‘YYYY-MM-DD HH24:MI:SS‘) from dual;    -- 将日期转为字符串类型
SQL> select to_char(sal, ‘$9,999.99‘) from dual;    -- 将数值转为字符串类型

14）to_date（将字符串转换为日期）

SQL> select to_date(‘2021-06-01 13:22:14‘, ‘YYYY-MM-DD HH24:MI:SS‘) from dual;

15）nvl（空值处理）

SQL> select ename, sal, comm, sal+nvl(comm,0) from emp;    -- 如果comm为空，则为0

16）nvl2（空值处理）

SQL> select ename, sal, comm, nvl2(comm, sal+comm, sal) from emp;    -- 如果comm非空，则值为sal+comm，否则为sal

17）聚合函数

SQL> select avg(sal), max(sal), min(sal), sum(sal), count(sal) from emp;
SQL> select count(distinct job) from emp;
SQL> select avg(sal), max(sal), min(sal), sum(sal), count(sal) from emp where deptno=20;
SQL> select deptno, avg(sal), max(sal), min(sal), sum(sal), count(sal) from emp group by deptno;

2.3. 常用查询

1）查询当前用户

SQL> select user from dual;

2）查询当前系统时间

SQL> select sysdate from dual;

3）查询当前用户所有的数据库对象（表、视图、索引、约束等）

SQL> select table_name from user_tables;
SQL> select index_name from user_indexes;
SQL> select constraint_name, constraint_type from user_constraints where table_name=‘EMP‘;

2.4. 连接查询

1）自然连接

SQL> select empno, ename, dname from emp natural join dept;

2）等值连接

SQL> select empno, ename, dname from emp join dept using(deptno);    -- 两个表的相关列列名相同时
SQL> select empno, ename, dname from emp join dept on(emp.deptno=dept.deptno);    -- 两个表的相关列列名不相同时
SQL> select empno, ename, dname from emp, dept where emp.deptno=dept.deptno;

3）非等值连接

SQL> select ename, sal, grade from emp, salgrade where sal between losal and hisal;
SQL> select ename, sal, grade from emp join salgrade on (sal between losal and hisal);

4）自连接

SQL> select employee.ename, manager.ename
     from emp employee, emp manager
     where employee.mgr=manager.empno;
SQL> select employee.ename, manager.ename
     from emp employee join emp manager on (employee.mgr=manager.empno);

5）左外连接

SQL> select empno, ename, dname
     from emp left outer join dept on (emp.deptno=dept.deptno);

6）右外连接

SQL> select empno, ename, dname
     from emp right outer join dept on (emp.deptno=dept.deptno);

7）全外连接

SQL> select empno, ename, dname
     from emp full outer join dept on(emp.deptno=dept.deptno);

8）交叉连接（笛卡尔积）

SQL> select empno, ename, dname from emp, dept;
SQL> select empno, ename, dname from emp cross join dept;

2.5. 子查询

SQL> select ename, sal from emp
     where sal > (select sal from emp where ename=‘SMITH‘);
SQL> select ename, sal from emp
     where sal > (select avg(sal) from emp where deptno=10);
SQL> select ename, sal from emp
     where sal > all (select avg(sal) from emp group by deptno);
SQL> select * from dept where not exists
     (select * from emp where emp.deptno=dept.deptno);

3. 插入、修改及删除（DML）

3.1. 插入

SQL> insert into dept values(50, ‘NETWORK‘, ‘SHANGHAI‘);
SQL> insert into dept(deptno) values(60);
SQL> insert into dept values(70, null, null);

-- create table
SQL> create table dept_1(
     deptno number(2),
     dname varchar2(14),
     loc varchar2(13)
     );
SQL> insert into dept_1
     select * from dept;    -- 将dept查询出来的结果集插入到dept_1中

3.2. 修改

SQL> update emp set sal = (select avg(sal) from emp where deptno=10)
     where ename=‘SMITH‘;    -- 修改表
SQL> update v1 set sal=sal+100 where ename=‘SMITH‘;    -- 修改视图

3.3. 删除

delete：DML，可以指定条件来删除特定的数据，没有提交之前是可以回滚的，产生针对每行选定的数据的重做日志，不释放空间

truncate：DDL，不可指定条件，用来删除表中的所有数据，会立即提交并释放空间，不产生重做日志

SQL> delete from emp where deptno=30;
SQL> truncate table dept_1;

?

4. 数据定义（DDL）

4.1. create

1）创建表

SQL> create table student(
     id number(4) not null,
     name char(10) not null,
     age number(2) default 20
     );
SQL> create table dept_2
     as 
     select * from dept;    -- 使用子查询创建表
SQL> create table xyz
     as
     select deptno, avg(sal) avg_sal, sum(sal) sum_sal, min(sal) min_sal from emp group by deptno;

约束（constraint）的类型：

a. NOT NULL：非空约束，要求所在列的值不能为空

b. UNIQUE：唯一性约束，要求所在列的数据不能重复，会在列上自动产生一个索引

c. PRIMARY KEY：主键约束，一个列或多个列的组合，能够唯一表示一条记录，非空且唯一

d. FOREIGN KEY：一个列或多个列的组合，不单独存在，需要引用另一个表的主键

e. CHECK：自定义约束，要求所在列上的值满足该自定义条件要求

SQL> create table temp(
     id number(4) constraint stu_id_pk primary key,
     name char(10) constraint stu_name_nn not null,
     gender char(1) constraint stu_gen_ck check(gender in (‘F‘,‘M‘)),
     age number(2) constraint stu_age_ck check(age>=17 and age<=25),
     deptno number(2) reference dept(deptno)
     );

-- 约束条件的表级定义
SQL> create table temp(
     id number(4),
     name char(10),
     gender age(1),
     age number(2),
     deptno number(2),
     constraint stu_id_pk primary key(id),
     constraint stu_gen_ck check(gender in (‘F‘,‘M‘)),
     constraint stu_age_ck check(age>=17 and age<=25),
     constraint stu_deptno_fk foreign key(deptno) references dept(deptno)
     );

2）创建视图

SQL> create view v1 
     as
     select ename, sal, comm, job from emp;
SQL> create view v2
     as
     select ename, sal, comm, dname, loc from emp, dept where emp.deptno=dept.deptno;
SQL> create view v3
     as
     select deptno, avg(sal) avg_sal, min(sal) min_sal, max(sal) max_sal, sum(sal) sum_sal from emp group by deptno;
SQL> create or replace view v4
     as
     select ename, sal, comm from emp where sal>2000 with check option;    -- 创建或修改视图时使用

3）创建索引

SQL> create index idx1 on emp(ename);
SQL> create index idx2 on emp(sal);

4）创建序列

SQL> create sequence s1
     start with 10000 increment by 5 maxvalue 100000 cycle cache 20;

4.2. alter

1）调整表

SQL> alter table temp 
     add(
     mobile char(11),
     qq char(10)
     );
SQL> alter table temp modify name varchar2(50);
SQL> alter table temp drop column qq;

2）调整约束

无约束情况下使用alter添加

SQL> create table temp (
     id number(4),
     name char(10),
     gender char(1),
     age number(2),
     dept_id number(3)
     );
SQL> alter table temp
     add (
     constraint stu_id_pk primary key(id),
     constraint stu_gen_ck check(gender in (‘F‘,‘M‘)),
     constraint stu_age_ck check(age>=17 and age<=25),
     constraint stu_dept_id_fk foreign key(dept_id) references department(dept_id)
     )

调整表内约束

SQL> alter table temp modify name constraint stu_name_nn not null;

从零开始学习Oracle_SQL部分

上一篇：cocos2dx 实现flappybird


下一篇：android进程优先级&有了Thread为什么还要使用service