Oracle Series: (6) WHERE clause

Find employee information for unit 20th in the EMP table

SELECT * from emp where deptno = 20;

The query name is Smith's employee, the string uses ' ', the content is case sensitive

SELECT * from emp where ename = ' SMITH ';

Summary: Which of the techniques you have learned is case sensitive, which is case insensitive

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/86/EF/wKiom1fPCe7RWSW3AAAYHml8RBY341.png "title=" 001. PNG "alt=" Wkiom1fpce7rwsw3aaayhml8rby341.png "/>

For December 17, 1980 employees, note the Oracle default date format (DD-MON-RR represents a 2-bit year)

SELECT * from emp where hiredate = ' 1 July-December-80 ';

Query employees with a salary greater than 1500

SELECT * from emp where sal > 1500;

Inquire about employees with wages not equal to 1500 "!= or <>"

SELECT * from emp where Sal <> 1500;

Find employees with salaries between 1300 and 1600, including 1300 and 1600 "between applied to numbers "

SELECT * from emp where (sal>=1300) and (sal<=1600);

Or

SELECT * from emp where Sal between 1300 and 1600;

Query employees who are not paid between 1300 and 1600, excluding 1300 and 1600 "notbetween"

SELECT * from emp where Sal is not between 1300 and 1600;

Check in the employee "between applies to date " between "198 January-February-20" to "198 February-January-23"

SELECT * from emp where hiredate between ' 20月-February -81 ' and ' 2 March-January-82 ';

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/86/EF/wKiom1fPDHPxM2F9AAA1PsY6e7c508.png "title=" 002. PNG "alt=" Wkiom1fpdhpxm2f9aaa1psy6e7c508.png "/>

Attention: 1) for numeric values, decimal values before, large values in the rear 2) for the date type, the older value is in front, the year decimal value is in the rear

Query employees in department 20th or 30th, for example: according to the ID number, the selected employees, bulk delete"in"

SELECT * from emp where (deptno=20) or (deptno=30);

Or

SELECT * from EMP where deptno in (30,20);

The query is not an employee of department number 20th or30th.

SELECT * from EMP where deptno not in (30,20);

Query employee whose name begins with capital letter S, use% to represent 0, 1 or more characters "likeFuzzy query "

SELECT * from emp where ename like ' S ';

Equivalent

SELECT * from emp where ename = ' S ';

SELECT * from emp where ename like ' s% ';

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/86/EE/wKioL1fPDerQZlPIAAAPqHq8dcs256.png "title=" 003. PNG "alt=" Wkiol1fpderqzlpiaaapqhq8dcs256.png "/>

Attention: General exact query with = symbol For any imprecise query using the like notation, we are often called fuzzy queries

Query employee whose name ends with capital Letter N

SELECT * from emp where ename like '%N ';

Query name The first letter is T, the last letter is the employee of R

SELECT * from emp where ename like ' t%r ';

The query name is a 4-character employee, and the second character is I, with _ can only represent 1 characters and cannot represent 0 or more characters

SELECT * from emp where ename like ' _i__ ';

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/86/EF/wKiom1fPDuej1A_GAAAMLxxqu0k717.png "title=" 004. PNG "alt=" Wkiom1fpduej1a_gaaamlxxqu0k717.png "/>

Insert an employee named ' T_im ', salary 1200

INSERT into EMP (empno,ename) VALUES (1111, ' t_im ');

Query employees whose names contain ' _ ' and use the \ Escape character to return the characters to the original meaning "like '%\_% ' escape '"

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

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/86/EF/wKiom1fPEQKCxFdvAAARRmROt5g708.png "title=" 005. PNG "alt=" Wkiom1fpeqkcxfdvaaarrmrot5g708.png "/>

Insert an employee whose surname is named '

INSERT into EMP (empno,ename) VALUES (2222, "");

Insert an employee with a name called '

INSERT into EMP (empno,ename) VALUES (2222, "");

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/86/EE/wKioL1fPEhGy1dceAAAMTGLGlds859.png "style=" float: none; "title=" 006.PNG "alt=" Wkiol1fpehgy1dceaaamtglglds859.png "/>

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/86/EE/wKioL1fPEhHhAi0ZAABHgY-8y84483.png "style=" float: none; "title=" 007.PNG "alt=" Wkiol1fpehhhai0zaabhgy-8y84483.png "/>

Query all employee information, use% or percent

SELECT * FROM Emp;select * from EMP where ename like '% ', select * from emp where ename like '%_% ';

Query for employee with commission NULL "isnull"

SELECT * FROM EMP where comm is null;

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/86/EF/wKiom1fPEybRs8h8AAA6V_3wphw310.png "title=" 008. PNG "alt=" Wkiom1fpeybrs8h8aaa6v_3wphw310.png "/>

Note : null cannot participate in = Operation

Null can participate in NUMBER/DATE/VARCHAR2 type operations

Query for employees with a non-null commission of "is notnull"

SELECT * FROM EMP where comm are NOT null;

Find employees who have no commissions and pay more than 1500

SELECT * from EMP where (comm is null) and (SAL>1500);

Employees who inquire about a salary of 1500 or 3000 or 5000

SELECT * from emp where Sal in (4000,10000,1500,3,300,3000,5000);

Search for positions that are "MANAGER" or employees who are not "ANALYST" (Way one, using! = or <>)

Select *from empwhere (job= ' MANAGER ') or (job<> ' ANALYST ');

Query position is "MANAGER" or employee who is not "ANALYST" (mode two, use not)

Select *from empwhere (job= ' MANAGER ') or (not (job= ' ANALYST '));

Oracle Series: (6) WHERE clause