当前位置:网站首页>SQL memo

SQL memo

2021-05-04 14:49:43 Fengx

Preface

alas , be supposed to Java Development , In fact, most of them are SQL, I'm a little bit fascinated , I am SQL Engineers .... Don't say the , Let's write a memo , On the one hand, we should consolidate the , On the one hand, it is convenient for you to inquire , If you don't have any, you'll find out what's missing , Later, it may be based on different databases , scene , Add oil and vinegar to the performance ...

Explain

This SQL The memo is mainly used for MySQL, Other databases may be slightly different , I'll summarize later ...

DDL( Data definition language )

Create a new database
CREATE DATABASE [ Database name ]
 Copy code 
Delete database
DROP DATABASE [ Database name ]
 Copy code 

Empathy , Create and delete tables in the same way ,DATABASE Change to TABLE

Create index
CREATE INDEX [ Index name ] ON [ Table name ]
 Copy code 
Delete index
DROP INDEX [ Index name ]
 Copy code 

DML( Data operation language )

insert( Insert )

Single insertion
insert into students (class_id,name,gender,score) values (2,' Daniel ','M',80);
 Copy code 
Multiple inserts
insert into students (class_id,name,gender,score) values 
(2,' Daniel ','M',80),
(3,' Calf ','S',30);
 Copy code 

delete( Delete )

Single
delete from students where id = 1;
 Copy code 
multiple
delete from students where id >= 5 and id <= 7;
 Copy code 

to update (update)

Single
update students set name = ' Daniel ',score = 66 where id = 1;
 Copy code 
multiple
update students set name = ' Calf ',score = 77 where id >= 5 and id <= 10;
 Copy code 
Use expressions
update students set score = score + 10 where id <= 20;
 Copy code 

Cough , Soon! , The key is coming.

Inquire about (select)

select Common complete sentences

select [ Target list ] 
from [ Data table name ]
[where  Line conditional expression ]
[group by [ Name ]]
[having [ Group expressions ]]
[order by [ Name ] [asc/desc]]
 Copy code 

Classic query

Query all
select * from students;
 Copy code 

Try not to use... In actual projects , Affect performance

Query a single line 、 Multiple lines
select name,class_id from students where id = 10;
 Copy code 
select name,class_id from students where id > 10 and id < 20;
 Copy code 

Projection query

Let the result set contain only the specified columns SELECT Column 1, Column 2, Column 3 FROM ... Example :

SELECT id, score, name FROM students;
 Copy code 

names SELECT Column 1 Alias 1, Column 2 Alias 2, Column 3 Alias 3 FROM ...; You can alias each column , The column name of the result set can be different from that of the original table Example :

SELECT id stu_id, score points, name stu_name FROM students;
 Copy code 

WHERE

SELECT id, score points, name FROM students WHERE gender = 'M';
 Copy code 

Sort

ORDER BY

ASC Ascending
SELECT id, name, gender, score FROM students ORDER BY score;
 Copy code 

The default collation is ASC:“ Ascending ”, From small to large ,ASC It can be omitted , namely ORDER BY score ASC = ORDER BY score

DECS Descending
SELECT id, name, gender, score FROM students ORDER BY score DESC;
 Copy code 
Order more

When score Columns have the same data , To further sort , You can continue to add column names , For example, using ORDER BY score DESC, gender First press score Reverse order , If you have the same score , Press again gender Column sorting :

SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
 Copy code 

WHERE If there is WHERE Clause ,ORDER BY The clause should be placed in WHERE After clause , Find out the order first

SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;
 Copy code 

Paging query

LIMIT OFFSET

or LIMIT N,M ( example :limit 0,20: The first 0 Start of records , take 20 Data )

Realization : To achieve paging , It's actually showing the second... From the result set 1 To 100 This record serves as part of 1 page , According to the first 101 To 200 This record serves as part of 2 page , And so on . Sort first :

SELECT id, name, gender, score FROM students ORDER BY score DESC;
 Copy code 

Now? , We paginate the result set , each page 3 Bar record . To get the first 1 Page records , have access to LIMIT 3 OFFSET 0:

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;
 Copy code 

The above query LIMIT 3 OFFSET 0 Express , For the result set from 0 Record number one begins , Most take 3 strip . Be careful SQL The index of the recordset is from 0 Start . If you want to query the second 2 page , So we just need “ skip ” head 3 Bar record , That is to say, from 3 No. records start to query , hold OFFSET Set to 3:

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;
 Copy code 

LIMIT 3 It means “ most 3 Bar record ”.

OFFSET If the maximum number of queries is exceeded, no error will be reported , Instead, we get an empty result set . OFFSET It's optional , If only write LIMIT 15, So it's equivalent to LIMIT 15 OFFSET 0. stay MySQL in ,LIMIT 15 OFFSET 30 It can also be abbreviated as LIMIT 30, 15.

Use LIMIT OFFSET When paging , With N More and more big , Query efficiency will also be lower and lower .

Aggregate query

SELECT COUNT(*) FROM students;
 Copy code 

COUNT(( asterisk )) Represents the number of rows to query all columns , Note that the aggregate result is a number , But the result of the query is still a two-dimensional table , It's just that this two-dimensional table has only one row and one column , And it's listed as COUNT(( asterisk )).

Usually , When using aggregate queries , We should give the column name an alias , Easy to process results :

SELECT COUNT(*) num FROM students;
 Copy code 

WHERE

SELECT COUNT(*) boys FROM students WHERE gender = 'M';
 Copy code 

except COUNT() Out of function ,SQL The following aggregation functions are also provided :

 function   explain 
SUM  Calculate the total value of a column , The column must be of numeric type 
AVG  Calculate the average of a column , The column must be of numeric type 
MAX  Calculate the maximum value of a column 
MIN  Calculate the minimum value of a column 
 Copy code 

MAX() and MIN() Functions are not limited to numeric types . If it's a character type ,MAX() and MIN() Will return the last sorted and the first sorted characters .

SELECT AVG(score) average FROM students WHERE gender = 'M';
 Copy code 

If the aggregate query WHERE The condition does not match any lines ,COUNT() Returns the 0, and SUM()、AVG()、MAX() and MIN() Returns the NULL:

Group aggregation

GROUP BY

SELECT COUNT(*) num FROM students GROUP BY class_id;
 Copy code 

Execute this query ,COUNT() The result is no longer a , It is 3 individual , This is because ,GROUP BY Clause specifies by class_id grouping , therefore , Execute this SELECT When the sentence is , Will be able to class_id The same columns are grouped first , Then calculate separately , therefore , Got it 3 Row result . But this 3 OK, which three classes are the results , It doesn't look good , So we can take class_id Columns are also put in the result set :

SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
 Copy code 

You can also use multiple columns for grouping . for example , We want to count the number of boys and girls in each class :

SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
 Copy code 

The above query result set consists of 6 Bar record , Corresponding to the number of boys and girls in each class .

Multi-table query

Query multiple tables
SELECT * FROM students, classes;
 Copy code 

students Each row of the table is associated with classes Each row of the table is spelled in pairs and returned . The number of columns in the result set is students Table and classes The sum of the columns of a table , The number of lines is students Table and classes The product of the number of rows in a table .

This kind of multi table query is also called Cartesian query , Be very careful when using Cartesian queries , Because the result set is the product of the number of rows in the target table , For each of the two 100 A Cartesian query on a table with row records will return 1 Ten thousand records , For each of the two 1 A Cartesian query on a table with ten thousand rows of records will return 1 One hundred million records .

Proper use

SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;
 Copy code 

Table allows you to set aliases

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;
 Copy code 

WHERE

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;
 Copy code 

Link query

Join query is another type of multi table query . Join queries on multiple tables JOIN operation , In short , First, determine a main table as the result set , then , Select rows from other tables “ Connect ” On the main table result set .

SELECT s.id, s.name, s.class_id, s.gender, s.score FROM students s;
 Copy code 

The result set above is just class_id Column , Lack of corresponding class name Column . The most commonly used internal connection ——INNER JOIN To achieve :( Internal connection )

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;
 Copy code 

INNER JOIN The query is written as :

 First make sure the main table , Still use FROM < surface 1> The grammar of ;
 Then determine the tables that need to be connected , Use INNER JOIN < surface 2> The grammar of ;
 Then determine the connection conditions , Use ON < Conditions ...>, The condition here is s.class_id = c.id, Express students Tabular class_id Column and classes Tabular id Columns of the same row need to be joined ;
 Optional : add WHERE Clause 、ORDER BY Equal clause .
 Copy code 

It's not necessary to use aliases , But it can simplify the query statement better .

function

total

select count as totalcount from table1
 Copy code 

Sum up

select sum(field1) as sumvalue from table1
 Copy code 

Average

select avg(field1) as avgvalue from table1
 Copy code 

Maximum

select max(field1) as maxvalue from table1
 Copy code 

Minimum

select min(field1) as minvalue from table1
 Copy code 

Advanced query operators

UNION Operator

UNION Operator by combining the other two result tables ( for example TABLE1 and TABLE2) And eliminate any duplicate rows in the table to derive a result table .

When ALL along with UNION When used together ( namely UNION ALL), Don't eliminate duplicate lines .

In both cases , Each row of the derived table does not come from TABLE1 Is from TABLE2.

EXCEPT Operator

EXCEPT Operator by including all in TABLE1 But not in TABLE2 The result table is derived by eliminating all duplicate rows .

When ALL along with EXCEPT When used together (EXCEPT ALL), Don't eliminate duplicate lines .

INTERSECT Operator

INTERSECT Operators are passed by including only TABLE1 and TABLE2 There are rows in and all duplicate rows are eliminated to derive a result table .

When ALL along with INTERSECT When used together (INTERSECT ALL), Don't eliminate duplicate lines .

notes : The result lines of several queries using operands must be consistent .

Use external connections

left(outer)join

The left outer join ( Left connection ): The result set includes the matching rows of the join table , It also includes all the rows of the left join table .

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
 Copy code 

right(outer)join

Right connection ( The right connection ): The result set includes both the matching join rows of the join table , It also includes all rows of the right join table .

full/cross(outer)join

Full outer join : Not only the matching rows of the symbolic join table , It also includes all the records in the two connection tables .

grouping Group by

A watch , Once grouped After completion , Only group related information can be obtained after query .

Group related information :( Statistics ) count,sum,max,min,avg Criteria for grouping )

stay select Fields in statistical functions , It can't be put together with normal fields .

版权声明
本文为[Fengx]所创,转载请带上原文链接,感谢
https://chowdera.com/2021/05/20210504144301069D.html

随机推荐