A preliminary study of the database (understanding of basic concepts and the charm of query statements)

The SQL language consists of four parts:

Data definition Language (DDL): For example,create, DROP, alter and other statements;

Data manipulation Language (DML): For example,Insert, UPDATE, delete and other statements;

Data Query Language (DQL): For example,select statement;

Data Control Language (DCL): For example,Grand, REVOKE, commit, rollback and other statements.

* Important query statement syntax and how to use it:

order By default ascending order (ASC)

ORDER BY desc descending order

order by can be sorted by different fields

That is: SELECT * FROM table name order by (fields requiring ascending order), Dese (fields requiring descending)

If you want to query a table but a field is empty, you cannot use 0 to indicate that you want to use IsNull

For example:

Count each person's annual salary and sort from low to high

Select Person Name field (Sal+isnull (comm,0)) * 12 year salary from table name order by annual salary

Common functions:max (), Min (), AVG (), sum (), COUNT ()

Number of enquiries:

--Select COUNT (*) from table name

Check the display of names and wages of employees above average wage:

--select name, sal from table name where sal> (select AVG (SAL) from table name)

Having often combined with group by, you can filter the results of a grouped query

Query the department number and its average salary below 2000 of the average salary:

Select AVG (SAL), Department number from table name GROUP by department number having AVG (SAL) <2000

More knowledge of the database, next time more.

A preliminary study of the database (understanding of basic concepts and the charm of query statements)