Some special query statements about tables in ORACLE
1: how to determine the value of a field: which data contains lowercase letters or upper and lower case letters
A record of lowercase letters in the value of the judgment field NAME
Method 1:
SELECT NAME FROM TEST WHERE regexp_like (NAME,' [[: lower:]]')
Mode 2
SELECT NAME FROM TEST WHERE regexp_like (NAME,' [a murz]')
A record of uppercase letters in the value of the judgment field NAME
Method 1:
SELECT NAME FROM TEST WHERE regexp_like (NAME,' [[: upper:]]')
Mode 2:
SELECT NAME FROM TEST WHERE regexp_like (NAME,' [Amurz]')
2: how to determine that the value in the field contains special characters
For example, I want to find records that contain the special character & in the field NAME of table TEST
SELECT NAME FROM ESCMOWNER.TEST WHERE regexp_like (NAME,' [&]')
3: how to determine whether there is a space before or after the value in the field
1: record information with spaces in front of or after the field:
SELECT * FROM TEST WHERE length (NAME) > length (trim (NAME))
2: record information with a space before the field value:
SELECT * FROM ESCMOWNER.TEST WHERE LENGTH (LTRIM (NAME)) < LENGTH (NAME)
3: when there is a space after the field value:
SELECT * FROM ESCMOWNER.TEST WHERE LENGTH (RTRIM (NAME)) < LENGTH (NAME)
4: how to determine that the value in the field contains spaces
It has always been a very difficult and troublesome thing to think that a field contains spaces (spaces are located anywhere), but in front of ORACLE's regular expression function REGEXP_LIKE, everything is fine.
SELECT * FROM TEST WHERE REGEXP_LIKE (NAME,'() +')
Add: let's take a look at the single table query statement of Oracle
Test forms that use the Oracle database:
-- single table query data syntax select (query) * all / query fields, multiple fields separated by from (from) table names-query all employees' information select * from scott.emp;select * from dept;-- query all employees' numbers, names and position specified field names look up data select empno,ename,job from emp;-- query all employees' numbers, names and annual salaries select * from emp Select empno,ename,sal*12 from emp;-- does not modify the original data by using the operator through the query-- it just performs the operation at the time of the query, showing a new data table, select empno,ename,sal*12 from emp;-- calculation. A virtual table is provided. Dual-- dual is a virtual table, designed for testing use-- aliases can be given to fields, select 1, AS result1 from dual;select sysdate AS DATES from dual;--, aliases sal*12 to income as can be omitted--''single quotes represent strings "" aliases, if aliases contain special characters, use "" wrap select empno,ename,sal*12 "annual salary" from emp. -- query the names, positions and salaries of all users, and display them as follows-- name: xxx, position: xxx, salary: xxx-- string concatenation use | | connection is not + select 'name:' | | ename |, position:'| | job |', salary:'| | sal info from emp;-- wants to display all positions, so there can be no repetition excluding repetition-- to reuse distinctselect distinct job,ename from emp. -- sort ascending and descending (asc desc)-- sort the fields that need to be specified by default is ascending order asc-- can sort numeric date string types select * from emp order by job asc -- descending by salary, and then ranking older employees by descending date if the salary is the same-- sort by multiple fields. Order by only needs to write once, and subsequent sorting fields are used, separated by select empno,ename,hiredate,sal from emp order by sal desc, hiredate desc;-- query all employees' numbers, names, annual salaries, sort by annual salary-- sort by alias select empno,ename,sal*12 income from emp order by income desc.
Summary
The above are some special query sentences about the table in ORACLE introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply you in time. Thank you very much for your support to the website!
