A single-table SQL query is performed only in one table.
Syntax
Syntax for single-table queries:
SELECT [ALL | DISTINCT] select_list FROM table_name
[ WHERE query_condition ]
[ GROUP BY group_by_expression ]
[ HAVING group_condition ]
[ ORDER BY column_list ][ASC | DESC]
[ LIMIT limit_clause ]
column_list:
column_name[,column_name...]
If you use the WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT keywords in a query, these clauses are executed following a strict sequence for executing these keywords:
The
FROMclause is executed to find the required table.The
WHEREclause is executed to specify conditions.The
GROUP BYclause is executed to group records. IfGROUP BYis not executed, all records are considered a group.The
HAVINGclause is executed to filter the grouped results.The
SELECTclause is executed.The
DISTINCTclause is executed to remove duplicate rows.The
ORDER BYclause is executed to sort the results in ascending or descending order.The
ROWNUMclause is executed to limit the number of records displayed per page.
The difference between the WHERE and HAVING clauses is that the WHERE clause filters data before grouping or aggregation, whereas the HAVING clause filters data after grouping and returns the entire SQL query result.
SELECT queries
Create tables named emp and dept and insert proper data to the tables.
CREATE TABLE dept(
deptno NUMBER(2,0),
dname VARCHAR(14),
location VARCHAR(13),
CONSTRAINT pk_dept PRIMARY KEY(deptno)
);
CREATE TABLE emp(
empno NUMBER(4,0),
empname VARCHAR(10),
job VARCHAR(9),
mgr NUMBER(4,0),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2,0),
age NUMBER(2,0),
CONSTRAINT PK_emp PRIMARY KEY (empno),
CONSTRAINT FK_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno)
);
INSERT INTO emp VALUES
(1369,'SMITH','CLERK',1902,DATE'1980-12-17',800.00,NULL,20,22),
(1499,'ALLEN','SALESMAN',1698,DATE'1981-02-20',1600.00,300.00,35,22),
(1566,'JONES','MANAGER',1839,DATE'1981-04-02',2975.00, NULL,40,22),
(1698,'BLAKE' ,'MANAGER',1839,DATE'1981-05-01',2850.00,NULL ,30,33),
(1788,'SCOTT','ANALYST',1566,DATE'1987-07-15',3000.00,NULL ,25,33),
(1902,'FORD','ANALYST',1566,DATE'1981-12-05',3000.00, NULL,45,22);
INSERT INTO dept VALUES
(20,'Finance','beijing'),
(35,'Administration','hangzhou'),
(40,'Development','xian'),
(30,'Workshop','guangzhou'),
(25,'Legal affairs','shanghai'),
(45,'Office','suzhou');
Query all columns
An asterisk (*) indicates to return all fields in a table. Example:
obclient> SELECT * FROM emp;
+-------+---------+----------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | AGE |
+-------+---------+----------+------+-----------+------+------+--------+------+
| 1369 | SMITH | CLERK | 1902 | 17-DEC-80 | 800 | NULL | 20 | 22 |
| 1499 | ALLEN | SALESMAN | 1698 | 20-FEB-81 | 1600 | 300 | 35 | 22 |
| 1566 | JONES | MANAGER | 1839 | 02-APR-81 | 2975 | NULL | 40 | 22 |
| 1698 | BLAKE | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL | 30 | 33 |
| 1788 | SCOTT | ANALYST | 1566 | 15-JUL-87 | 3000 | NULL | 25 | 33 |
| 1902 | FORD | ANALYST | 1566 | 05-DEC-81 | 3000 | NULL | 45 | 22 |
+-------+---------+----------+------+-----------+------+------+--------+------+
6 rows in set
The preceding sample code is equivalent to:
obclient> SELECT empname,empno,job,mgr,hiredate,sal,comm,deptno FROM emp;
+---------+-------+----------+------+-----------+------+------+--------+
| EMPNAME | EMPNO | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+---------+-------+----------+------+-----------+------+------+--------+
| SMITH | 1369 | CLERK | 1902 | 17-DEC-80 | 800 | NULL | 20 |
| ALLEN | 1499 | SALESMAN | 1698 | 20-FEB-81 | 1600 | 300 | 35 |
| JONES | 1566 | MANAGER | 1839 | 02-APR-81 | 2975 | NULL | 40 |
| BLAKE | 1698 | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL | 30 |
| SCOTT | 1788 | ANALYST | 1566 | 15-JUL-87 | 3000 | NULL | 25 |
| FORD | 1902 | ANALYST | 1566 | 05-DEC-81 | 3000 | NULL | 45 |
+---------+-------+----------+------+-----------+------+------+--------+
6 rows in set
Query specified columns
obclient> SELECT empname,deptno FROM emp;
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH | 20 |
| ALLEN | 35 |
| JONES | 40 |
| BLAKE | 30 |
| SCOTT | 25 |
| FORD | 45 |
+---------+--------+
6 rows in set
You can set aliases for columns in a query. Example:
obclient> SELECT empname AS Employee name, deptno AS Department No. FROM emp; +--------------+---------------+ | Employee name| Department No.| +--------------+---------------+ | SMITH | 20 | | ALLEN | 35 | | JONES | 40 | | BLAKE | 30 | | SCOTT | 25 | | FORD | 45 | +--------------+---------------+ 6 rows in setYou can remove duplicate rows in a query. Example:
obclient> SELECT age FROM emp; +------+ | AGE | +------+ | 22 | | 22 | | 22 | | 33 | | 33 | | 22 | +------+ 6 rows in set obclient> SELECT DISTINCT age FROM emp; +------+ | AGE | +------+ | 22 | | 33 | +------+ 2 rows in setYou can use the
ROWNUMclause to limit the number of rows returned per page. This feature is often used in pagination. Example:obclient> SELECT empname, deptno FROM emp WHERE ROWNUM<=3; +---------+--------+ | empname | deptno | +---------+--------+ | SMITH | 20 | | ALLEN | 30 | | JONES | 40 | +---------+--------+ 3 rows in set
Query calculated values
obclient> SELECT empname, sal-100,job FROM emp;
+---------+---------+--------+
| empname | sal-100 | deptno |
+---------+---------+--------+
| SMITH | 700.00 | 20 |
| ALLEN | 1500.00 | 30 |
| JONES | 2875.00 | 40 |
| BLAKE | 2750.00 | 30 |
| SCOTT | 2900.00 | 20 |
| FORD | 2900.00 | 40 |
+---------+---------+--------+
6 rows in set
You can apply functions to specified columns in the query. For example, in the following sample code, the LOWER function is used to convert job to lowercase. For more information, see "Use operators and functions in queries".
obclient> SELECT empname, sal-100, LOWER(job) FROM emp;
+---------+---------+---------------+
| empname | sal-100 | LOWER(deptno) |
+---------+---------+---------------+
| SMITH | 700.00 | 20 |
| ALLEN | 1500.00 | 30 |
| JONES | 2875.00 | 40 |
| BLAKE | 2750.00 | 30 |
| SCOTT | 2900.00 | 20 |
| FORD | 2900.00 | 40 |
+---------+---------+---------------+
6 rows in set
In addition, you can use the WHERE condition to query specified columns. For more information, see Conditional queries. For more information, see Use operators and functions in queries.