MySQL DQL statement

MySQL DQL statement

DQL is the data query language of MySQL

Query all columns

 SELECT * FROM Table name;

Query specified column

 SELECT Column 1 [, Column 2, ... column N] FROM Table name;

Completely duplicate records only once

As like as two peas of the query result, the one row is displayed. Generally, this is rarely the case when querying all columns, but when querying only one column (or several columns), it may be large!

 SELECT DISTINCT * | Column 1 [, Column 2, ... column N] FROM Table name;
 -- Query the salary of the employee table. If the same salary exists, it will be displayed only once
 SELECT DISTINCT sal FROM emp;

Column operation: addition, subtraction, multiplication and division can be performed on quantity type columns

 SELECT sal*1.5 FROM emp;
 SELECT sal+comm FROM emp;

String types can be used for continuous operations

 SELECT CONCAT('$', sal) FROM emp;

Convert NULL value

Sometimes we need to convert NULL to other values. For example, when com+1000, if there is a NULL value in the com column, NULL+1000 is still NULL, and we want to calculate NULL with the current 0.

 /*IFNULL(comm, 0): If there is a NULL value in comm, it is calculated as 0*/
 SELECT IFNULL(comm, 0)+1000 FROM emp;
 /*If student_ If the name column is null, 'no name' is returned*/
 select ifnull(student_name,'No name') from student_table;
 /*If student_ If the name column is equal to 'Zhang San', null is returned*/
 select nullif(student_name,'Zhang San') from student_table;
 /*If student_ If the name column is null, 'no name' is returned; otherwise, 'with name' is returned*/
 select if(isnull(student_name),'No name','Have a name') from student_table;

List aliases for

You may have noticed that when column operation is used, the column names in the query result set are not good-looking. At this time, we need to alias the column names so that the alias will be displayed in the result set

-- among AS Can be omitted
SELECT IFNULL(comm, 0)+1000 AS bonus FROM emp;

Condition control (condition query)

Like UPDATE and DELETE statements, SELECT statements can use the WHERE clause to control records

SELECT empno,ename,sal,comm FROM emp WHERE sal > 10000 AND comm IS NOT NULL;
SELECT empno,ename,sal FROM emp WHERE sal BETWEEN 20000 AND 30000;
SELECT empno,ename,job FROM emp WHERE job IN ('manager', 'chairman');

Fuzzy query

-- Fuzzy queries require operators: LIKE，among_Match an arbitrary character. Note that only one character is matched, not multiple. The following statement queries employees whose surname is Zhang and whose name consists of two words
SELECT * FROM emp WHERE ename LIKE 'Zhang_';
-- An employee whose name consists of three words
SELECT * FROM emp WHERE ename LIKE '___'; 
-- among%Match 0~N Any character, so the above statement queries all employees surnamed Zhang
SELECT * FROM emp WHERE ename LIKE 'Zhang%';
-- Never think that the above statement is an employee with a word in the middle of the query name, because%Match 0~N Characters, so employees whose names begin and end with a will also be queried
SELECT * FROM emp WHERE ename LIKE '%Ah%';
-- This condition is equivalent to nonexistence, but if the name is NULL The query cannot be found
SELECT * FROM emp WHERE ename LIKE '%';

sort

 -- Press sal Sort, ascending,among ASC It can be omitted
 SELECT * FROM emp ORDER BY sal ASC;
 -- Press comm Sort, descending,among DESC Cannot be omitted
SELECT * FROM emp ORDER BY comm DESC;
-- use sal Ascending if sal When the same, use comm Descending order of
SELECT * FROM emp ORDER BY sal ASC, comm DESC;

Aggregate function: aggregate functions are used to perform vertical operations on a column

  • COUNT
-- calculation emp All columns in the table are not NULL Number of rows of records
SELECT COUNT(*) FROM emp;
-- calculation emp In the table comm Column is not NULL Number of rows of records
SELECT COUNT(comm) FROM emp;
  • MAX
 -- Query maximum wage
 SELECT MAX(sal) FROM emp;
  • MIN
-- Query minimum wage
SELECT MIN(sal) FROM emp;
  • SUM
 -- Query salary contract
 SELECT SUM(sal) FROM emp;
  • AVG
-- Query average salary
SELECT AVG(sal) FROM emp;

Grouping query grouping query is to group records using a column, and then query group information.

-- use deptno Group, query the department number and the number of records in each department
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
-- use job Group and query the maximum salary of each job
SELECT job, MAX(SAL) FROM emp GROUP BY job;
-- Group by department and query the number of records in each group. The condition is that the number of records is greater than 3,Group conditions can only be grouped fields or grouped functions
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*) > 3;

LIMIT clause (dialect): LIMIT is used to LIMIT the starting row and total row number of query results.

-- The query start line is line 5, and a total of 3 lines of records are queried, where 4 means to start from line 5, and 3 means to query 3 in total    that 's ok. That is, lines 5, 6 and 7.
SELECT * FROM emp LIMIT 4, 3;
-- Number of records on one page: 10 lines, query page 3 (formula:(Current page-1) * Records per page)
select * from emp limit 20, 10；

supplement

-- query teacher_table In the table teacher_name Character length of the column
select char_length(teacher_name) from teacher_table;
-- calculation teacher_name The character length of the column sin value
select sin(char_length(teacher_name)) from teacher_table;
-- Calculation 1.57 of sin Value, approximately equal to 1
select sin(1.57);
-- Adds a certain time for the specified date,In this usage interval Is a keyword. You need a value and a unit
select date_add('1998-01-02',interval 2 month);
-- This usage is simpler
select adddate('1998-01-02',3);
-- Get current date
select curdate();
-- Get current time
select curtime();
-- Get a character through MD5 Encrypted data
select md5('testing');
-- If java_teacher Is 1, returns'java teacher'，If it is 2, return'Ruby teacher'，Otherwise return'Other teachers'
select student_name, case java_teacher
when 1 then 'java teacher'
when 2 then 'Ruby teacher'
else 'Other teachers'
end
from student_table;

multi-table query

Multi table query classification

  • Merge result set (understand)
  • join query
  • Subquery

Merge result set

  • In the table to be merged, the type and number of columns are the same
  • UNION, remove duplicate lines
  • UNION ALL, do not remove duplicate lines

join query

  • Inner connection
  • External connection: left external connection, right external connection, all external connection (MySQL does not support it)
  • Natural connection (a simplified way): Resources https://blog.csdn.net/JCY1009015337/article/details/53024912

1. Inner connection

-- dialect
SELECT * FROM Table 1 alias 1, Table 2 alias 2 WHERE Alias 1.xx=Alias 2.xx
-- standard
SELECT * FROM Table 1 alias 1 INNER JOIN Table 2 alias 2 ON Alias 1.xx=Alias 2.xx
-- natural
SELECT * FROM Table 1 alias 1 NATURAL JOIN Table 2 alias 2
-- All records queried by internal connection meet the conditions

2. External connection

  • Left outer
SELECT * FROM Table 1 alias 1 LEFT OUTER JOIN Table 2 alias 2 ON Alias 1.xx=Alias 2.xx

The records in the left table will be queried regardless of whether they meet the conditions, while the records in the right table can be queried only if they meet the conditions. The records in the left table that do not meet the conditions are NULL in the right table

  • Left outer nature
SELECT * FROM Table 1 alias 1 NATURAL LEFT OUTER JOIN Table 2 alias 2 ON Alias 1.xx=Alias 2.xx
  • Right outer
SELECT * FROM Table 1 alias 1 RIGHT OUTER JOIN Table 2 alias 2 ON Alias 1.xx=Alias 2.xx

Records in the right table will be queried regardless of whether they meet the conditions, while records in the left table can be queried only if they meet the conditions. For records whose right table does not meet the conditions, the left table part is NULL

  • Right outer nature
SELECT * FROM Table 1 alias 1 NATURAL RIGHT OUTER JOIN Table 2 alias 2 ON Alias 1.xx=Alias 2.xx
  • Full link: you can use UNION to complete the full link

Subquery

There are queries in the query (view the number of select keywords!)

1. Where the subquery appears:

* where Post exists as a condition
* from Exists as a table after(Multi row and multi column)

2. Subquery criteria

  • Single row and single column
SELECT * FROM Table 1 alias 1 WHERE Column 1 [=,>,<,>=,<=,!=] (SELECT column FROM Table 2 alias 2 WHERE condition)
  • Multiple rows and single column
SELECT * FROM Table 1 alias 1 WHERE Column 1 [IN, ALL, ANY] (SELECT column FROM Table 2 alias 2 WHERE condition)
  • Single row multi column
SELECT * FROM Table 1 alias 1 WHERE (Column 1,Column 2) IN (SELECT Column 1, Column 2 FROM Table 2 alias 2 WHERE condition)
  • Multi row and multi column
SELECT * FROM Table 1 alias 1 , (SELECT ....) Alias 2 WHERE condition

Condition order in multi table query

  • where
  • group by
  • having
  • order by
  • limit

Keywords: MySQL

Added by yashvant on Fri, 14 Jan 2022 16:37:24 +0200

Popular Keywords

©2025 Programming VIP