Shulou(Shulou.com)06/01 Report--

Alias the list

Purpose We often use the expression SELECT enamme,sal*12 FROM emp when querying a column.

The downside of this is that the second column used after the query is sal*12. This kind of readability is relatively poor, so we will give aliases to the list to increase readability.

The alias itself is not case-sensitive. If you want to distinguish, the alias needs to use double quotes. When the alias contains spaces, you should also use double quotation marks.

SELECT ename,sal*12 "Annual Salary" FROM emp

WHERE clause

Used to filter records in the process of querying data, only records that meet the conditions in the WHERE clause will be queried. When the database queries the table, each record has to be filtered by WHERE.

Check those with a salary of less than 2000

SELECT ename,sal FROM emp WHERE salTO_DATE ('1980mast YYYY')

Query sal is greater than 1000 and works as clerk

SELECT ename,sal,job FROM emp WHERE sal > 1000 AND job='CLERK'

Query CLERK with wages greater than 1500 or all SALESMAN with unlimited wages

SELECT * FROM emp WHERE sal > 1500 AND job='CLERK' OR job=' SALESMAN'

Raising priority means checking CLERK and SALESMAN with a salary greater than 1500.

SELECT * FROM emp WHERE sal > 1500 AND (job='CLERK' OR job=' SALESMAN')

Looking at the second letter of ename in the emp table is the name of A.

SELECT * FROM emp WHERE ename LIKE'_ A%'

The IN comparator can be compared to one of the columns

SELECT * FROM emp WHERE job IN ('MANAGER','CLERK'); = SELECT * FROM emp WHERE job='MANAGER' OR job='CLERK'

Query is not equal to all the work of MANAGER and CKERK

SELECT * FROM emp WHERE job NOT IN ('MANAGER','CLERK')

SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000

SELECT * FROM emp WHERE sal > ANY (3000 jade 2000 pr 4000)

SELECT * FROM emp WHERE sal*12 > 50000

SELECT ename,sal FROM emp WHERE UPPER (ename) = upper ('rose')

The DISTINCT keyword is used to remove duplicate data from a given column to see what positions the company has, and to remove duplicates.

SELECT DISTINCT job FROM emp

When we de-repeat multiple columns, it means that the combination of the values of these columns is not duplicated

SELECT DISTINCT deptno,job FROM emp

ORDER BY clause, used to sort the result set

The ORDER BY clause can only appear at the end of the SELECT statement.

Several fields can be specified after ORDER BY, and the priority is from left to right

ASC stands for ascending order. The default is ascending order, so you don't have to write.

DESC stands for descending order

Sort the result set from the knife according to the department number

SELECT * FROM emp ORDER BY deptno

Query the salaries and names of the departments in the emp table for 10 and sort them in descending order

SELECT ename,sal FROM emp WHERE deptno = 10 ORDER BY sal DESC

When sorting a field with a null value, the null value is considered infinite, so when sorting in descending order, the null value appears first.

-

Aggregate function

MAX and MIN

SELECT MAX (sal) FROM emp

* * all aggregate functions ignore null values.

SELECT MAX (sal) as "maximum", MIN (sal) as "minimum" FROM emp

SELECT AVG (NVL (comm,0)) as "average" FROM emp

SELECT AVG (sal), SUM (sal) FROM emp

SELECT COUNT (comm) FROM emp;-- statistics number, not statistical value

SELECT COUNT (*) FROM emp;-- counts how many records there are in the whole table

The GROUP BY clause is used to group the data in the table. The grouping principle is that records with the same value of the fields given after GROUP BY are treated as a group.

View the maximum wage and minimum wage of each department:

SELECT MAX (sal), MIN (sal), AVG (sal), SUM (sal), deptno FROM emp GROUP BY deptno

Aggregate function is also called group function

SQL has a request.

If a group function appears in the select clause red, then the other fields that are not in the group function must appear in the GROUP BY clause, but the reverse is not necessary

If each suffering value in the field is not repeated, the field should not normally be used as a reference for grouping in the GROUP BY clause.

If the GROUP BY clause is specified, other fields in the SELECT clause that are not in the group function and are not in the GROUP BY clause cannot appear

The difference between WHERE and HAVING

Similarly, they are all used for filtering, except that HAVING is used for the second filter, and WHERE is used for filtering conditions the first time the table is queried.

HAVING is filtered again on the basis of the results obtained after the first query.

-

Query priority

From clause, execution order, from back to front, from right to left with a small amount of data

The execution order of the where clause is from top to bottom, from right to left, and the condition unloading the where clause that can filter out the maximum number of records is the rightmost.

Group by execution order grouping from left to right

Select clause, use fewer * signs and try to get field names.

Association query

The joint query of N tables must have at least one join condition, otherwise it will produce Cartesian product: the total number of records of table A * the total number of records of table B, meaningless result set

Join two tables to query and add at least one join condition. Because it is difficult to avoid fields with the same name in two tables when querying, to solve this problem, you can

Use the table name. Field to confirm

If the alias is longer, it will be simplified by the upper alias.

SELECT e.enameree.salred.dnameparame.deptno FROM emp edept d WHERE e.deptno = d.pdeptno

SELECT e.enameree.salred.dnameparame.deptno FROM emp e JOIN dept d ON (d.deptno=e.deptno)

In the SQL89 standard, the disadvantage is that the join conditions and filter conditions are written in the where clause, and the readability is relatively poor, especially when there are many filter conditions and multi-table queries.

SELECT e.enamered.DNAME FROM emp eRect d WHERE d.DEPTNO=e.DEPTNO AND d.DNAMERENTS'

SQL92 recommends using SELECT for multi-table joins. FROM JOIN...ON... WHERE (write conditions only)

SELECT e.ename.dname FROM emp e JOIN dept d ON e.deptno = e.deptno WHERE d.dname = 'SALES'

The SQL92 standard recommends that we use the internal connection form when winning the prize.

In this way, we will find that the join condition is defined separately in the ON clause, while the filter condition unloading the WHERE clause is more readable.

SELECT e.enamered.dname FROM emp e JOIN dept d ON e.deptno=d.deptno WHERE d.dnameroom sales'

External connection

Self-connection

The data in the current table is associated with other data in the table. It forms a self-connection. Usually tables are designed as self-joins to indicate that there is a superior-subordinate relationship between the same type of data (tree-structured data is usually designed as self-joins.)

For example: the category of Taobao. Is designed to be self-connecting.

When designing tables and associating relationships, there are usually two fields that are very important. They are called primary keys, foreign keys.

Usually the first field of each table is the primary key, the data stored by the primary key has nothing to do with the data, it is only used to identify the uniqueness of each piece of data, so the requirement of the primary key is

The stored value is not empty and unique.

Foreign key, which is used to hold the value of the primary key recorded in the relational table, such as

There is a field in the department table called dept, which is used to hold the value of the primary key of a record in the dept table, so the dept of the emp table is the foreign key.

In an association, the table that holds the foreign key is usually the "many" party in "one-to-many"

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.