The where clause is used to find records from tables or temporary datasets that meet the specified conditions, and can be used for conditions in select, update, and delete statements.

1, Generate test data

Use the following SQL to create the super girl basic information table (t? Girl) and insert some test data.

create table T_GIRL
(
  id        char(4)         not null,   -- number
  name      varchar2(10)    not null,   -- Full name
  yz        varchar2(10)        null,   -- Level of appearance
  sc        varchar2(10)        null,   -- figure
  weight    number(4,1)     not null,   -- weight
  height    number(3)       not null,   -- height
  birthday  date            not null,   -- time of birth
  memo      varchar2(1000)      null    -- Remarks
);
insert into T_GIRL(id,name,yz,birthday,sc,weight,height,memo)
  values('0101','Xi Shi','Well done!',to_date('2000-01-01 01:12:35','yyyy-mm-dd hh24:mi:ss'),
         'Hot and spicy',48.5,170,'This is a very beautiful girl. Her husband is husband, and her boyfriend is Fan Li.');
insert into T_GIRL(id,name,yz,birthday,sc,weight,height,memo)
  values('0102','Diao Chan','Well done!',to_date('1997-08-02 12:20:38','yyyy-mm-dd hh24:mi:ss'),
         'slim',45.2,168,'Wang Yunzhen is not a man, but Dong Zhuo pushes the beauty into the fire pit, a sinner of all ages.');
insert into T_GIRL(id,name,yz,birthday,sc,weight,height,memo)
  values('0103','Da already','Well done!',to_date('1998-03-03 10:50:33','yyyy-mm-dd hh24:mi:ss'),
         'Hot and spicy',53.6,172,'If the business really died because of me, what did your men do?');
insert into T_GIRL(id,name,yz,birthday,sc,weight,height,memo)
  values('0104','Lotus Babe','Pork chop',to_date('1980-05-05 10:11:55','yyyy-mm-dd hh24:mi:ss'),
         'are plump and sturdy',85.8,166,'If you don't study hard, you will marry sister Furong in the future.');
insert into T_GIRL(id,name,yz,birthday,sc,weight,height,memo)
  values('0105','Shenmi cat girl',null,to_date('1989-12-08 12:10:35','yyyy-mm-dd hh24:mi:ss'),
         null,48.5,171,'I don't know who it is. She has one on her face%Symbol, very mysterious.');

2, Syntax of where clause

select field name 1, field name 2,... Field name n from table name where field name comparison operator value;
select * from table name where field name comparison operator value;

Example:

select id,name,yz,sc,height from T_GIRL where id='0101';

select id,name,yz,sc,height from T_GIRL where height=172;

The above example shows the simplest use of where clause. Next, I will introduce Oracle's logical operators and comparison operators to enhance the function of where clause.

3, Logical operators

Logical operators have priority, but I don't want to introduce it. In practical application, use parentheses to solve all priority problems.

Example:

select name,yz,sc,height from T_GIRL where yz='Well done!' and sc='Hot and spicy';

select name,yz,sc,height from T_GIRL where yz='Well done!' and (sc='Hot and spicy' or sc='slim');

Look at the effect of not, but rarely.

4, Comparison operator

Pay attention to several problems:

1) Comparison operators can be used with not, but in, like and is null are often used with not in practical applications, and other comparison operators are rarely used with not, which makes it uncomfortable.

2) When using like, use the percentage sign% to match multiple characters, and underline ﹣ to match one character. However, if we want to treat% and ﹣ as common characters, we can use the escape character method. The usage of the escape character is as follows:

In C language, backslash \ escape is used. In Oracle, escape keyword is used to define escape character.

escape 'chr' defines the escape character. When the escape character is placed before the wildcard, the wildcard is interpreted as a normal character, for example:

select * from T_GIRL where memo like '%people%/%%' escape '/';

The first, second, and fourth% are wildcards, and the third% is a normal character.

5, Advanced usage of where clause

The value of the where clause condition can be a constant (fixed value) or an expression, or the return value of a function or the result set of a select statement.

If it is an in comparison operator, the value of the condition can be a result set of multiple rows, and other comparison operators must be a result set of single row records.

1) The value of the condition is an expression.

select name,yz,sc,height from T_GIRL where height>100+68;

2) The value of the condition is a function and an expression.

select name,yz,sc,height from T_GIRL where birthday>sysdate-(30*365);

3) The value of the condition is the result set of a single row

select empno,ename,job,sal from EMP
 where deptno=(select deptno from DEPT where dname='ACCOUNTING');

If the value of the condition is multiline and there is a logical problem, an error is prompted.

4) The value of the condition after the comparison operator in supports a multiline result set.

6, Use operations and functions on columns of where clause

In the where clause, you can operate on columns and use functions.

1. Operate on columns

select id,name,height from T_GIRL where height-170>0;

2. Use functions on columns

select id,name,to_char(birthday,'yyyy-mm-dd hh24:mi:ss') from T_GIRL
 where to_char(birthday,'yyyy-mm-dd hh24:mi:ss')='1998-03-03 10:50:33';

3. Existing problems

In the first SQL, the height column is calculated, and in the second SQL, the to'char function is used for the birthday column. Zero point will be obtained for the two methods. Because in the where clause, if you use functions or operations on columns, SQL statements can't use indexes (except for function indexes), and the performance is very low (table scan is used). Programmers must not make such low-level errors.

The correct way to write is to operate on the value of the condition or use the function, as follows:

select id,name,height from T_GIRL where height>170;
select id,name,to_char(birthday,'yyyy-mm-dd hh24:mi:ss') from T_GIRL
 where birthday=to_date('1998-03-03 10:50:33','yyyy-mm-dd hh24:mi:ss');

As a supplementary note, the operation of column names after the select keyword and the use of functions have no impact on the performance of SQL statements.

7, Copyright notice

C language technology net original article, reprint please explain the article source, the author and the original link.
Source: C language technology network (www.freecplus.net)
Author: Manon Youdao

If this article is helpful to you, please like support or forward my article in your blog, thank you!!!
If there is a mistake in the article, or there is a mistake in the content, or other suggestions and opinions, please leave a message for correction, thank you very much!!!