# # # to review
SQL is classified as follows:
- Data Definition Language (DDL) : a Data Definition Language used to define database objects such as libraries, tables, and columns. CREATE, ALTER, DROP
- Data Manipulation Language (DML) : a Data Manipulation Language used to define database records. INSERT, UPDATE, DELETE
- Data Control Language (DCL) : a Data Control Language used to define access permissions and security levels.
- Data Query Language (DQL) : Data Query Language (mainly SELECT) used to Query records (Data).
The last article focused on DDL and DML, and today focuses on DQL.
###DQL- Data query language
The database does not make changes to the data by executing DQL statements, but instead has the database send the result set to the client (virtual, checked and stored in memory). The result set returned by the query is a virtual table.
Query keyword: SELECT
Basic syntax:selectThe column namefromThe table name [WHERE --> GROUP BY -->HAVING--> ORDER BY]Syntax:SELECT selection_list /* The column name to query */
FROM table_list /* The name of the table to be queried */
WHERE condition /* Line condition */
GROUP BY grouping_columns /* Group the results */
HAVING condition /* Line condition after grouping */
ORDER BY sorting_columns /* Group the results */
LIMIT offset_start, row_count /* The result is limited */
Copy the codeTo better illustrate, let’s take three tables:
SQL statement for table creation and data insertion:
Stu: create table stu (Sid char(6), sname varchar(50), age int, gender varchar(50)); insert into stu values('s_1001'.'liuyi', 35, 'male');
insert into stu values('s_1002'.'chener', 15, 'female');
insert into stu values('s_1003'.'zhangsan', 95, 'male');
insert into stu values('s_1004'.'lisi', 65, 'female');
insert into stu values('s_1005'.'wangwu', 55, 'male');
insert into stu values('s_1006'.'zhaoliu', 75, 'female');
insert into stu values('s_1007'.'sunqi', 25, 'male');
insert into stu values('s_1008'.'zhouba', 45, 'female');
insert into stu values('s_1009'.'wujiu', 85, 'male');
insert into stu values('s_1010'.'zhengshi', 5, 'female');
insert into stu values('s_1011'.'xxx', null, null); Employee Table EMP: Create table emp(empno int, ename varchar(50), job varchar(50), MGR int, hiredate date, sal decimal(7,2), Comm decimal(7,2), deptno int); insert into emp values(7369,'smith'.'clerk', 7902,'1980-12-17',800,null,20);
insert into emp values(7499,'allen'.'salesman', 7698,'1981-02-20', 1600300, 30); insert into emp values(7521,'ward'.'salesman', 7698,'1981-02-22', 1250500, 30); insert into emp values(7566,'jones'.'manager', 7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'martin'.'salesman', 7698,'1981-09-28', 1250140, 0, 30); insert into emp values(7698,'blake'.'manager', 7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'clark'.'manager', 7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'scott'.'analyst', 7566,'1987-04-19',3000,null,20);
insert into emp values(7839,'king'.'president',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'turner'.'salesman', 7698,'1981-09-08', 1500,0,30); insert into emp values(7876,'adams'.'clerk', 7788,'1987-05-23',1100,null,20);
insert into emp values(7900,'james'.'clerk', 7698,'1981-12-03',950,null,30);
insert into emp values(7902,'ford'.'analyst', 7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'miller'.'clerk', 7782,'1982-01-23',1300,null,10); Create table dept(deptno int, dname vARCHar (14), loc varchar(13)); insert into dept values(10,'ACCOUNTING'.'NEW YORK');
insert into dept values(20, 'RESEARCH'.'DALLAS');
insert into dept values(30, 'SALES'.'CHICAGO');
insert into dept values(40, 'OPERATIONS'.'BOSTON');
Copy the code##1 Basic query
Select * from stu; 1.2 Querying a specified column select SID, sname, age from STu;Copy the code###2 Conditional query
2.1 Introduction to Conditional Query A conditional query is a query WHERE clause, WHERE the following operators and keywords can be used: =,! =, <>, <, <=, >, >= BETWEEN... AND IN(set) IS NULL OR IS NOT NULL AND OR NOT 2.2 Select * from stu where age < 50 AND gender < 50 (AND keyword, ANDwhere gender='female'and age<50; 2.3 Querying the record whose student ID is S_1001 or name is liSi (or keyword) select * from stuwhere sid ='s_1001' or sname='liSi'; 2.4 Querying the Records about student IDS S_1001, S_1002, and S_1003 (inKeyword, including) select * from stuwhere sid in ('S_1001'.'S_1002'.'S_1003'); 2.5 Querying records whose Student IDS are not S_1001, S_1002, or S_1003 (notinSelect * from tab_studentwhere s_number not in ('S_1001'.'S_1002'.'S_1003'); 2.6 Querying Records whose Age is NULL (is keyword: Yes; Not null) select * from stuwhereage is null; Select * from student whose age is between 20 and 40; Between) select * from stuwhere age>=20 and age<=40;
或者
select * from stu whereage between 20 and 40; 2.8 Select * from stu select * from stuwheregender! ='male';
或者
select * from stu where gender<>'male';
或者
select * from stu where not gender='male'; 2.9 Select * from stu where name is not nullwhere sname is not null;
或者
select * from stu where not sname is null;
Copy the code###3 Fuzzy query
When you want fuzzy matching you need to use fuzzy queries. Fuzzy query requires the keyword LIKE.
Wildcards for fuzzy query: _ Any character % : Any 0 to N characters Example:'% a %' 'a _'Select * from stu select * from stuwhere sname like '_____'; Note: Fuzzy queries must use the LIKE keyword. Underscores (_) match any letter, and five underscores (_) represent five letters. 3.2 Select * from stu where name (s) contain 5 letters and the fifth letter is' Iwhere sname like '____i'; 3.3 Querying student records whose name starts with 'z' select * from stuwhere sname like 'z%'; Note: % contains 0 to N letters. 3.4 Querying student records where the second letter is "I" select * from stuwhere sname like '_i%'; 3.5 Querying student records whose name contains a select * from stuwhere sname like '%a%';
Copy the code###4 field controls query
4.1 Removing Duplicate Records Removing duplicate records (where the data on a series of records in two or more rows is the same). For example, the SAL field in an EMP table has the same record. SQL > select DISTINCT SAL from EMP; SQL > select DISTINCT SAL from EMP; Because the types of sal and COMM are numeric, we can add them. If you have a field in Sal or Comm that is not a numeric type, you will get an error. select *,sal+comm from emp; Note that the COMM column has many records with a value of NULL, and since anything added to NULL will result in NULL, the settlement result may be NULL. Select *,sal+ IFNULL (comm,0) from emp; Sal +IFNULL(comm,0) as total from emp; total: select *, sal+IFNULL(comm,0) as total from emp; Select *,sal+ifnull(comm,0) total from emp;Copy the codeOrder by asC (default) desc
Select * from STu order by sage ASC; Select * from stu order by sage; Select * from stu order by age desc; Select * from emp order by sal desc,empno asc;Copy the codeSum avg Max min count
The aggregate function is used for vertical operations: COUNT() : counts the number of rows in a specified column that are not NULL; MAX() : Computes the maximum value of the specified column, using string sort if the specified column is a string; MIN() : Evaluates the minimum value of the specified column, using string sort if the specified column is a string; SUM() : Evaluates the numeric SUM of the specified column. If the specified column type is not numeric, the result is 0. AVG() : Evaluates the average value of the specified column, 0 if the specified column type is not numeric;
6.1 COUNT: Use COUNT() when vertical statistics are required. Run the following command to query the number of records in the EMP table: select count(*) as CNT from emp; Select count(comm) CNT from emp; Note: Because the count() function gives the COMM column, only non-null rows in the COMM column are counted. Select count(*) from EMp where monthly salary > 2500wheresal > 2500; Select count(*) as CNT from emp select count(*) as CNT from empwheresal+ifnull(comm,0) > 2500; Select count(comm), count(MGR) from emp; 6.2 SUM and AVG: Use the SUM () function when vertical summation is required. Select sum(sal) from emp; Select sum(sal), sum(comm) from emp; Select sum(sal+ifnull(comm,0)) from emp; Select sal from emp where sal = null and sal = null; Select MAX (sal), MIN (sal) from emp;Copy the codeGroup query
The GROUP BY clause is used when GROUP queries are required, such as salary and salary for each department, indicating that groups are used BY department. Note: Column names that appear at the same time as aggregate functions must be written after group by
Select deptno, sum(sal) from emp group by deptno; select deptno from emp group by deptno; Select deptno,count(*) from emp group by deptno; select deptno from emp group by deptno; Select deptno,count(*) from emp. Select deptno,count(*) from empwheresal>1500 group by deptno; Select deptno, sum(sal) from emp group by deptno HAVING sum(sal) > 9000;Copy the codeHaving vs. where
- Having filters data after grouping. Where filters data before grouping
- Having can be followed by aggregate functions (statistical functions). Aggregate functions cannot be used after WHERE.
- If a row does not meet the conditions of the WHERE clause, it will not be grouped. HAVING is a constraint on grouped data.
###8 LIMIT
LIMIT specifies the start row and the total number of rows in the query result. Paging technology is involved.
MySQL has this LIMIT keyword, Oracle does not have this LIMIT keyword.
8.1 Querying 5 Rows where the start row starts from 0 select * from EMplimit0, 5; Select * from row 1; select * from row 2; select * from row 1; Select * from emp; select * from emplimit3, 10; 8.3 Paging Query If there are 10 records on a page and I want to view the records on page 3, how should I view the records on page 3? 1. Start behavior 0 is recorded on the first page, and 10 rows are queried. 2. The second page records the initial behavior 10, with a total of 10 rows; 3. The initial behavior 20 is recorded on page 3, with a total of 10 lines of query; Example: select * from emp; int currentpage = 3; -- Current page int pagesize = 3; Select * from emplimit(currentpage - 1) * pagesize, pagesize;Copy the codeQuery the order in which the code is written and executed
We use a graph to understand the process of the query (we can see that the query results are reduced, grouped, and ordered) :
Select -> FROM -> WHERE -> group by -> having -> order by -> limit from -> where -> group by -> having -> select -> order by -> limitCopy the codeNote: The order of sort and filter cannot be reversed, because the order of the data may be out of order after filtering.