Oracle database learning notes (II) — simple query

Time:2021-10-20

1. Syntax of simple query statement

Select column name, column name,..., column name from table name;

Select... From: it is the keyword of the query statement
Select is followed by the data you want to get from the database
From is followed by the table name, indicating from which you want to read the data

For example:
--Query the employee's number, name, job and salary in EMP table
select empno,ename,job,sal from emp;

--Query the information of all columns in the EMP table
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;

If you want to query the information of all columns in the table, the column names between select and from can be represented by *. Note: * the execution efficiency of the * sign is lower than that when writing column names, (* when executing SQL statements, Oracle will query the data dictionary in the database, query the columns of the table, and then find out the data of all columns of the table)

2. Notes

Single line note: – (two underscores)

Multiline comment: start with / * and end with * /

3. Alias in SQL

Alias syntax:
      Object name alias
      Object name alias
For example:
Select empno as employee number, ename name, job, Mgr superior number, hiredate, Sal salary, comm Commission, deptno department number from EMP;

Note: as cannot be added when aliasing tables and views
select e.* from emp e; --select * from emp; 
select e.empno,e.ename,e.job,e.sal from emp e;

select emp.empno,emp.ename,emp.job,emp.sal from emp;

4. Query statement with condition

Select column name, column name,..., column name from table name where conditional expression;

Where: condition keyword, followed by condition expression. This condition is generally related to columns in the database

4.1 conditional expression

<、>、<=、>=、=、!=、<> (! =, < > means not equal to)

--Query employee information with salary greater than 1500
select * from emp where sal>1500;
--Query the information of an employee named Smith
select * from emp where ename='SMITH';

Note: in Oracle database, the number type can be written directly, and the string type data should be enclosed in single quotation marks

--Query the information of employees who are not department 10
select * from emp where deptno!=10;
select * from emp where deptno<>10;

4.2 in and notin

In: followed by a set or subquery
In Oracle, it represents a simple set (element, element, element,...) such as (1,2,3,4,5) ('a ',' B ',' C ','d')
--In the EMP table, query the employee information whose salary is 800, 1600, or 3000

select * from  emp  where sal in(800,1600,3000);

Not in: and in are opposite
select * from  emp  where sal not in(800,1600,3000);

4.3 fuzzy query like, ‘%’, ” And not like

Like: it is only used in character string columns, indicating fuzzy matching of strings
     There are two dot markers:
           %: indicates that there can be 0 or more characters where this% appears
           _:  Indicates in_ There can only be one arbitrary character where it appears
--Query employee information whose name ends in S
select * from emp where ename like '%S';

--The second character of the employee name in the query EMP table is the employee information of L
select * from emp where ename like '_L%';

--The third character of the employee name in the query EMP table is the employee information of A
select * from emp where ename like '__A%';

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7311,'qer_q%oasf','etl',7369,sysdate,3000,200,null);

--Query employee name_ Employee information for

select * from emp where ename like '%_%';
Escape characters in like:\
In the new version of Oracle, the escape character needs to be defined manually
Escape character, it will treat the character immediately after it as an ordinary character
select * from emp where ename like '%\_%' escape '\';

select * from emp where ename like '___\_%' escape '\';

select * from emp where ename like '%a%%'  escape 'a';

Not like: Contrary to like

--Query employee information without s in name
select * from emp swhere ename not like '%S%';

4.4 all、any

All, any: followed by a set or subquery
>All: indicates that it is larger than the largest element in the set > all (1,2,3,4,5) is equivalent to > 5
< all: indicates less than the smallest element in the set. < all (1,2,3,4,5) is equivalent to < 1
>Any: indicates that it is larger than the smallest element in the set > any (1,2,3,4,5) is equivalent to > 1
< any: indicates less than the largest element in the set. < any (1,2,3,4,5) is equivalent to < 5
--Query employee information and require that the employee's salary is 16002003000 lower than the following values
select * from emp where sal <all(1600,2000,3000);

--Query employee information with higher salary than any of the following (16002003000)
select * from emp where sal>any(1000,2000,3000);

--Query employee information whose salary is higher than everyone (16002003000)
select * from emp where sal > all(1600,2000,3000);

4.5 exists and not exists

Exists: it is not used with any column, followed by a sub query (query statement select statement). If the sub query can find the result, this condition is true, and the condition that the result cannot be found is not true
Not exists: opposite to exists

select * from emp where exists(select * from emp where 1=0);

select * from emp where exists(select * from emp);

4.6 conditional connector

And: indicates that the total condition is valid only when the two conditions connected with and are true at the same time. As long as one condition is not true, the whole is not true
Or: indicates or. When one of the two conditions connected by or is true, the overall condition is true. Only when both conditions are not true, the overall condition is not true
Between value1 and Value2: the value of value1 is smaller than that of Value2. Value1 and Value2 are generally numeric or date, indicating that the value is between value1 and Value2
         Its value contains value1 and Value2.
--Query the employee information of department No. 10 whose salary is greater than 1000
select * from emp where deptno=10 and sal>1000;


--Query the information of department No. 10 or employees whose salary is less than 2000
select * from emp where deptno=10 or sal<2000;

--Query employee information with salary between 1000 and 3000
select * from emp where sal>=1000 and sal<=3000;

select * from emp where sal between 1000 and 3000;

--Query employee information with salary greater than 1000 and less than 3000
select * from emp where sal>1000 and sal<3000;

select * from emp where sal between 1000 and 3000 and sal!=1000 and sal!=3000; 

Is null: indicates that the condition holds when the value is null
Is not null: indicates that the condition holds when the value is not null

select * from emp where comm is null;

select * from emp where comm is not null;

4.7 dual table

Dual table: it is a built-in single row table in Oracle,

select 1 from dual;
select 'abc' from dual;
select 2*3 from dual;
select 1,2,3,4 from dual;

select 1 from emp;
--Query employee's annual salary
select sal*12 from emp;
Select 'annual salary', Sal * 12 from EMP;