Oracle basic query statement

Oracle basic query statement sqlplus sys/Manager as sysdba connects to the database alter user scott account unlock as an administrator; changes the table structure of the user desc emp; (queries information of all employees whose department number is 10) SQL> select * from emp where deptno = 10; empno ename job mgr hiredate sal comm ---------- --------- ---------- -------------- ---------- DEPTNO ---------- 7782 clark manager 7839 09-6 month-81 2450 10 7839 king president 17-11 month-81 5000 10 7934 MILLER cler77 82 23-1-82 1300 10 (query the employee information with the name "CLARK"); SQL> select * from emp where ename = 'clark '; empno ename job mgr hiredate sal comm ---------- --------- ---------- -------------- ---------- DEPTNO ---------- 7782 clark manager 7839 09-6 month-81 2450 10 (query the names and salaries of employees with salaries greater than 1500) SQL> select ename, sal from emp where sal> 1500; (the query salary is greater than 1500) ENAME SAL ------------ ALLEN 1600 JONES 2975 blke 2850 CLARK 2450 SCOTT 3000 KING 5000 FORD 3000 has selected 7 rows. (Query the names and salaries of employees whose team number is not 10) SQL> select ename, sal from emp where deptno <> 10; (query team number is not 10) ename sal ---------- SMITH 800 ALLEN 1600 WARD 1250 JONES 2975 MARTIN 1250 BLAKE 2850 SCOTT 3000 TURNER 1500 ADAMS 1100 JAMES 950 FORD 3000 has selected 11 rows. (Query the names and salaries of employees whose names do not contain "ABC".) SQL> select ename, sal from emp where ename> 'ba'; (the ename does not contain ABC) ename sal ---------- SMITH 800 WARD 1250 JONES 2975 MARTIN 1250 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 JAMES 950 FORD 3000 MILLER 1300 has selected 11 rows. (Query the salaries and names of employees with salaries ranging from 800 to 1500) SQL> select ename, sal from emp where sal between 800 and 1500; (the salary range is 800-1500 ); ename sal ---------- SMITH 800 WARD 1250 MARTIN 1250 TURNER 1500 ADAMS 1100 JAMES 950 MILLER 1300 has selected 7 rows. (Query the name of an employee whose salary is 800-1500) SQL> select ename, sal from emp where sal> = 800 and sal <= 1500; (the salary is between and) ename sal ---------- SMITH 800 WARD 1250 MARTIN 1250 TURNER 1500 ADAMS 1100 JAMES 950 MILLER 1300 has selected 7 rows. (Query ename and sal in the emp table and the bonus) SQL> select ename, sal, comm from emp; (query ename, sal, comm) ename sal comm ---------- SMITH 800 ALLEN 1600 300 WARD 1250 500 JONES 2975 MARTIN 1250 1400 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 0 ADAMS 1100 ename sal comm -------- ---------- JAMES 950 FORD 3000 MILLER 1300 has selected 14 rows. (Query the names of employees in the emp table. Employees whose salaries and bonuses are not empty) SQL> select ename, sal, comm from emp where comm is null; (for null processing) ename sal comm ---------- SMITH 800 JONES 2975 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300 has selected 10 rows. (The query bonus is an empty employee name and salary.) SQL> select ename, sal, comm from emp where comm is not null; (not empty) ename sal comm ---------- ALLEN 1600 300 WARD 1250 500 MARTIN 1250 1400 TURNER 1500 0 (sal is 2000,) person SQL> select ename, sal, comm from emp where sal in (2000, 800); ename sal comm ---------- SMITH 1500 TURNER 0 (any of these people) SQL> select ename, sal, comm from emp where ename I N ('Smith ', 'King', 'abc'); ename sal comm ---------- SMITH 800 KING 5000 (for date queries, pay attention to the date writing format) (query the names and salaries of employees whose employment date is greater than-81) SQL> select ename, sal, hiredate from emp where hiredate> '20--81 '; ename sal hiredate ---------- ------------ WARD 1250 22-2 month-81 JONES 2975 02-month-81 MARTIN 1250 28-9 month-81 blke 2850 01-5 month-81 CLARK 2450 09-6 month-81 SCOTT 3000 19-4 month -87 KING 5000-11-81 TURNE R 1500 month-81 ADAMS 1100 month-87 JAMES 950 month-81 month-81 FORD 3000 month-81 month-81 ename sal hiredate ---------- -------------- MILLER 1300 month-82 12 rows selected. (Deptno is 10, and sal is> 1000) SQL> select ename, sal from emp where deptno = 10 and sal> 1000; ename sal ---------- CLARK 2450 KING 5000 MILLER 1300 (deptno is 10, or sal is greater than 1000) SQL> select ename, sal from emp where deptno = 10 or sal> 1000; ename sal ---------- ALLEN 1600 WARD 1250 JONES 2975 MARTIN 1250 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 ADAMS 1100 FORD 3000 ename sal ------------------ -- MILLER 1300 has selected 12 rows. (Query the information of employees whose salaries are not 800-1500) SQL> select ename, sal from emp where sal not in (); (sal is not,) ename sal ---------- ALLEN 1600 WARD 1250 JONES 2975 MARTIN 1250 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 ADAMS 1100 JAMES 950 FORD 3000 ename sal ---------- MILLER 1300 has selected 12 rows. (Query employee names whose names contain "ALL") SQL> select ename from emp where ename like '% ALL %'; (the characters contain ALL) ENAME----------ALLEN (query the employee information with the second letter "A" in the name) SQL> select ename from emp where ename like '_ A %'; (the second character is 'A ') ENAME----------WARDMARTINJAMES (for reference to escape characters) SQL> select ename from emp where ename like '% \ % '; unselected row SQL> select ename from emp where ename like '% $ % '; unselected row SQL> select ename from emp where ename like '% $ %' escape '$'; unselected row