current position：Home>MySQL__ Query of data processing

MySQL__ Query of data processing

2022-05-15 05:19:59【InfoQ】

1— basic  SELECT  sentence

*|{[DISTINCT] 
column
|
expression 
[
alias
],...}
table;
  • SELECT  Identifies which columns to select .
  • FROM  Identifies which table to choose from .
Select all columns
departments;
null
Select a specific column
department_id, location_id
null
notes   It means
  • SQL  Language
    Case insensitive
    .
  • SQL  It can be written on one or more lines
  • Keywords cannot be abbreviated or separated
  • Each clause should be written separately .
  • Use indentation to improve the readability of statements .

Alias of column

Alias of column :
  • Rename a column .
  • Easy to calculate .
  • Keep up with the column name ,
    You can also add keywords between column names and aliases  ‘AS’, Aliases use double quotes
    ,
    To include spaces or special characters in the alias and be case sensitive
    .

Use the alias

SELECT last_name AS name, commission_pct comm
FROM employees;
null
character string
  • The string can be  SELECT  A character in the list , Numbers , date .
  • Dates and characters can only appear in single quotes .
  • Whenever a row is returned , The string is output once .

Display table structure

Use  DESCRIBE  command , Represents the table structure
DESC[RIBE] 
tablename
DESCRIBE employees
null

2— Filtering and sorting data

Filter
  • Use WHERE  Clause , Filter out rows that do not meet the criteria .
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)];
WHERE  Clause in the wake of  FROM  Clause .
Filter rows in query
EMPLOYEES
null
Back in the  90 Information of all employees working in department No

null
WHERE  Clause
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90 ;
null
Comparison operations
null
SELECT last_name, salary
FROM employees
WHERE salary<= 3000;
null
Other comparison operations
null
BETWEEN
Use  BETWEEN  Operation to display the value in an interval
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;
Lower limit Upper limit
null
IN
Use  IN The operation displays the values in the list .
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201);
null
LIKE
  • Use  LIKE  The operation selects similar values
  • Selection criteria can contain characters or Numbers :
_%  Represents zero or more characters ( Arbitrary characters )
.
_  Represents a character
.
SELECT FROM WHERE
first_name employees
first_name LIKE 'S%';
LIKE
  • ‘%’ and ‘-’ Can be used at the same time .
SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';
NULL
Use  IS (NOT) NULL  Judge null .
SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;
null
Logical operations

AND
AND  The relationship between requirements and is true .
SELECT employee_id, last_name, job_id, salary
FROM employees
null
OR
OR  Requirement or relationship is true .
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
OR job_id LIKE '%MAN%';
null
NOT
SELECT last_name, job_id
FROM employees
WHERE job_id
NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');
null
ORDER BY Clause
  • Use  ORDER BY  Clause ordering
_ASC
（ascend）
:  Ascending
_DESC
（descend）
:  Descending
  • ORDER BY  Clause in SELECT End of statement .
ORDER BY hire_date
null
null
DESC
null
Sort by alias
annsal
null
Multiple column sort
  • according to ORDER BY  Order of the list .
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;
null
  • Can be used not in SELECT  Sort the columns in the list .

3 —  Group function

What is a grouping function
Grouping functions act on a set of data , And return a value for a set of data .
EMPLOYEES
null
Group function type
  • AVG()
  • COUNT()
  • MAX()
  • MIN()
  • SUM()
Group function syntax
SELECT [column,] group_function(column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
AVG（ Average ） and SUM （ total ） function
It can be done to
Numerical data
Use AVG  and  SUM  function .
SELECT AVG(salary), MAX(salary),
MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
null
MIN（ minimum value ） and  MAX（ Maximum ） function
It can be done to
Any data type
Data usage of  MIN  and  MAX  function .
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;
null
COUNT（ Count ） function
COUNT(*)  Returns the total number of records in the table , Apply to
Any data type
.
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;
null
COUNT（ Count ） function
COUNT(
expr
)  return
expr
Not empty
Total records for
.
SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;
null

Grouped data

EMPLOYEES
null
Grouped data : GROUP BY  Clause Syntax
have access to GROUP BY Clause divides the data in a table into groups
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
clear ：WHERE It must be on FROM Back
GROUP BY  Clause
stay SELECT  All columns in the list that are not included in the group function should be included in  GROUP BY  clause .
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;
null
GROUP BY  Clause
Included in GROUP BY  Columns in Clause need not be included in SELECT  In the list
SELECT AVG(salary)
FROM employees
GROUP BY department_id ;
null
Use multiple columns to group
null
stay GROUP BY Clause contains multiple columns
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;
null
Illegal use of group functions
•&nbsp;
Can't be in  WHERE  Use group function in clause .
•&nbsp;
Can be in  HAVING  Use group function in clause .
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;
WHERE AVG(salary) > 8000
*
ERROR at line 3:
ORA-00934: group function is not allowed here
WHERE  Group function cannot be used in clause
Filter grouping
EMPLOYEES
null
Filter grouping ： HAVING  Clause
Use  HAVING  Filter grouping :
  • Rows have been grouped .
  • Group functions are used .
  • Satisfy HAVING  The grouping of conditions in the clause will be displayed .
SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY
column, group_function
table condition]
group_by_expression] group_condition] column];
HAVING  Clause
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;
null

4 —  Multi-table query

null
beauty surface  boys surface
null
  • If you want to query the goddess name and the corresponding male god name , What about swelling ？
Multiple table joins
grammar ：
select name,boyName from beauty,boys;
beauty surface  boys surface
null
null
Error condition of Cartesian set ：
select count(*) from beauty;
Suppose the output is 12 That's ok
select count(*)from boys;
Suppose the output is 4 That's ok
final result ：12*4=48 That's ok
Cartesian collection
The cartesian assembly occurs under the following conditions :
  • Omit the join condition. The join condition is invalid. All rows in all tables are connected to each other in order to avoid Cartesian set ,  Can be in  WHERE  Add a valid connection condition .
Mysql  Connect
Use join to query data in multiple tables .
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
  • stay  WHERE  Clause .
  • When there are the same columns in the table , Prefix the column name with the table name
beauty surface  boys surface
Multiple table joins
null
null
Equivalent connection
SELECT beauty.id,NAME,boyname FROM beauty ,boys
WHERE beauty.`boyfriend_id`=boys.i
Distinguish between duplicate column names
Use the table name prefix to distinguish the same columns in multiple tables .
  • Columns with the same column name in different tables can be used
    The table alias
    Distinguish .
  • If a table alias is used , It's in select Statement needs to use table alias instead of table name
  • Table aliases support up to 32 Character length , But the less advice the better
The table alias
  • Using aliases can simplify queries .
  • Using a table name prefix can improve execution efficiency .
SELECT bt.id,NAME,boyname
FROM beauty bt,boys b;
WHERE bt.`boyfriend_id`=b.id ;
Join multiple tables
EMPLOYEES
null
DEPARTMENTS
null
LOCATIONS
null
  • Connect &nbsp;
    n
    Tables , Need at least  n-1 Connection conditions
    .  for example ： Connect
Three tables , At least two join conditions are required .
practice ： Query the employee's  last_name, department_name, city
SQL99： Use ON  Clause to create a connection
  • In natural connection, columns with the same name are used as connection conditions .
have access to  ON  Clause specifies additional join conditions .
  • This connection condition is separate from other conditions .
  • ON  Clause makes the statement more readable
    .
Join Connect
  • classification ： Internal connection  [inner] join on External connection left external connection left [outer] join on Right connection  right [outer] join on
ON  Clause
SELECT bt.id,NAME,boyname
FROM beauty bt
Inner join boys b
On bt.`boyfriend_id`=b.id ;
null
Join multiple tables
EMPLOYEES
null
DEPARTMENTS
null
LOCATIONS
null
  • Connect &nbsp;
    n
    Tables , Need at least  n-1 Connection conditions
    .  for example ： Connect
Three tables , At least two join conditions are required .
practice ： Query the employee's  last_name, department_name, city
Use  ON  Clause to create a multi table join
SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;
JOIN ON JOIN ON
External connection
beauty surface  boys surface
null
null
null
John Connection summary
null
null
key word ：
java train

copyright notice
author[InfoQ],Please bring the original link to reprint, thank you.
https://en.cdmana.com/2022/131/202205111232226856.html

The sidebar is recommended

guess what you like

Random recommended