SQL database-DQL and sqldql

SQL database-DQL and sqldql

At first glance, I was a primary school student in the Java industry. I have just been studying for half a year.

Today, the instructor spoke about the database operation statements. Here, I will share with you the knowledge I have learned. If there are any deficiencies, please point them out and make progress together to improve them together!

1. Various symbols in the database

%: Matches 0 or multiple characters

_: Match a single character

In: wildcard, a keyword for fuzzy search. Some tables or values contain a value. Example: select * from student where studentname in 'zhao, Qian, sun, li ';

Like: keyword for fuzzy search, which is usually used with % and. Example: select * from student where name like 'Lee %'

Between and: A value in a certain range. Example: select * from result where score between 80 and 100;

Where filters rows, order by sorts columns, and having sorts columns.

 

2. Aggregate functions:

Count (): return the data rows of the current table. You can put column names or any numbers in brackets. This is a habit of: count (1), which is faster.

Avg (): average score of the current value column. Example: avg (score)

Sum (): The sum of the data in the current numeric column.

Max (): the maximum value of the current value column.

Min (): the minimum value of the current value column.

 

3. SELECT query syntax

Select [ALL | DISTINCT]

{* | Table .*}

From table_name [as table_alias] # an alias is given to the current table in brackets.

[Left | right | relationship between inner join table_name on tables] # joint Query
[Where...] # query Conditions

[Group by...] # specify the fields in the result for grouping and query. The column must be a group column.

[Having...] # filter group conditions

[Order by...] # query records are sorted by one or more conditions. asc is the default ascending order, and desc is the descending order.

[Limit m, n | n offset m] # m indicates the record from which the query starts, and n indicates the formula for calculating the start position m for n rows of records displayed on the current page: (pageindex-1) * pagesize

 

The syntax is like this. It is optional in [] and mandatory in. #, --,/**/Can be used as the comment symbol of MYSQL.