DQL: Start with SELECT

Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

Starting from the SELECT

Retrieving a single column

select name from user

If you do not explicitly sort the query results (described in the next chapter), the order of the data returned has no special meaning.

The returned data may or may not be in the order in which it was added to the table. As long as the same number of rows are returned, this is normal

Retrieve multiple columns

select id,name,age,sex from user

When selecting multiple columns, always place commas between column names, but not after the last column name.

If you add a comma after the last column name, an error occurs.

Retrieve all columns

select * from user

In general, it is best not to use * wildcards unless you really need every column in a table.

While using wildcards may save you the trouble of explicitly listing the desired columns, retrieving unwanted columns often degrades retrieval and application performance.

There is one big advantage to using wildcards. Because column names are not explicitly specified (because an asterisk retrieves every column), columns with unknown names can be retrieved.

Retrieve DISTINCT rows

select distinct classid from user

The DISTINCT keyword, as the name implies, indicates that MySQL returns only DISTINCT values

The DISTINCT keyword applies to all columns, not just the column that precedes it.

If SELECT DISTINCT vend_id, prod_price is given, all rows will be retrieved unless both columns specified are different

+### LIMIT results

Select * from user limit 3,4

LIMIT 3, 4 means 4 rows from line 3 (skip the first 3 rows and take 4 rows)

The alternative syntax LIMIT 4 OFFSET 3 means to take 4 rows from line 3, just like LIMIT 3, 4.

Use fully qualified table names

select name from user

select user.name from user

select user.name from itxdl.user

Sort data ORDER BY

select * from user order by age

By default, the queried data is not sorted randomly. If sorting is not specified, the data will be displayed in the order in which it appears in the underlying table

Relational database design theory states that if the sorting order is not clearly specified, the order of retrieved data should not be assumed to be meaningful

In general, the columns used in the ORDER BY clause will be the columns selected for display.

However, this does not have to be the case; it is perfectly legal to sort data with non-retrieved columns.

Sort by multiple columns

select * from user order by classid,age

When multiple columns of data need to be sorted, column names are separated by commas and sorted in sequential order

The order by default is ascending. You can use DESC to set descending order

select * from user order by classid,age DESC

Sort classid in ascending order and age in descending order

Pay attention to

The ORDER BY clause should be given after the FROM clause.

If LIMIT is used, it must come after ORDER BY. Using clauses in the wrong order produces an error message

Filter data WHERE

Database tables typically contain a large amount of data, and it is rarely necessary to retrieve all rows in the table.

Typically, only a subset of the table data is extracted based on the needs of a particular operation or report.

Search criteria, also known as filter conditions, need to be specified to retrieve only the required data. In the SELECT statement, the data is filtered based on the search criteria specified in the WHERE clause.

select name from user where age = 22

When using both the ORDER BY and WHERE clauses, you should place the ORDER BY after WHERE, otherwise an error will occur

The WHERE clause operator

The operator	instructions
=	Is equal to the
< >! =	Is not equal to
<	Less than
< =	Less than or equal to
>	Is greater than
> =	Greater than or equal to
BETWEEN .. and..	Specifies between two values
IS NULL	A null value

Combine the WHERE clause

MySQL allows multiple WHERE clauses.

These clauses can be used in two ways: as an AND clause OR as an OR clause. Also called logical operators

select name from user where age = 22 and sex = 'm'

AND is used as a keyword in the WHERE clause to indicate that rows that meet all given criteria are retrieved

select name from user where age = 22 or sex = 'm'

OR is used to retrieve rows that match any given condition

AND with OR

WHERE can contain any number of AND AND OR operators. Allow a combination of the two for complex and advanced filtering.

However, the combination of AND AND OR presents an interesting problem.

For example, I need to look up 18 or 19 students in the database and ask them to be women

select name from user where classid=18 or classid =19 and sex='m'

The above statement does not retrieve the correct data as expected. What is the problem?

The reason lies in the order of the calculations. SQL processes the AND operator before processing the OR operator.

When SQL sees the above WHERE clause, it understands that it is all the girls in the class of Phase 19, or all the students in the class of phase 18, regardless of gender. In other words, because AND has higher precedence in the computation order, the operators are incorrectly combined

So the solution is to raise the priority, using parentheses to explicitly group the corresponding operators

select name from user where (classid=18 or classid =19) and sex='m'

Because parentheses have a higher evaluation order than the AND OR operators, the database first filters the OR conditions inside parentheses.

At this point, the SQL statement becomes get 18 or 19 trainee and ask for girls

And IN the NOT

The IN operator is used to specify a range of conditions IN which each condition can be matched.

Select name from user where classid in (18,19)

The keyword used IN the IN WHERE clause to specify a list of values to match, which is equivalent to OR

Why use the IN operator? Its advantages are as follows.

The syntax of the IN operator is clearer and more intuitive when using a long list of legal options. When IN is used, the order of calculations is easier to manage (because fewer operators are used). The IN operator is generally faster than the OR operator list. The biggest advantage of IN is that it can include other SELECT statements, making it possible to build WHERE clauses more dynamically.

The keyword used to negate the followed condition in the NOT WHERE clause

Select name from user where classid not in (18,19)

Why NOT? For simple WHERE clauses, there is really no advantage to using NOT.

But in more complex clauses, NOT is very useful.

For example, when used IN conjunction with the IN operator, NOT makes it easy to find lines that do NOT match a list of conditions.

LIKE and wildcard characters

All of the operators described above filter against known values.

However, this filtering method does not work all the time.

For example, how do you search for all products that have the text anvil in their name? Using a simple comparison operator will not work; you must use wildcards.

To use wildcards in search clauses, you must use the LIKE operator.

LIKE instructs MySQL, followed by the search pattern, to use wildcard matching instead of direct equality matching for comparison.

Percentage (%) wildcard In a search string, % represents any number of occurrences of any character

`select name from user where name like 'a%'`

`select name from user where name like '%a'`

`select name from user where name like '%a%'`
Copy the code

The underscore (_) wildcard underscore serves the same purpose as %, but underscores only match single characters instead of multiple characters

The technique of using wildcards

> As you can see, MySQL wildcards are useful. > > But this functionality comes at a cost: Wildcard searches generally take longer to process than the other searches discussed earlier. > > Here are some tips to keep in mind when using wildcards don't overuse them. Other operators should be used if they serve the same purpose. When you do need to use wildcards, don't use them at the beginning of a search pattern unless absolutely necessary. Placing wildcards at the beginning of a search pattern is the slowest. Pay close attention to the wildcard positions. If misplaced, the desired data may not be returnedCopy the code

Learn about MySQL’s regular REGEXP

> All kinds of programming languages, text editors, operating systems, and so on support the regular expression 'select name from user where name regexp '[0-5] ABC' 'Copy the code

Field concatenation, alias, calculation

Data stored in database tables is generally not in the format required by the application. Here are a few examples

If you want to display both the company name and the company address in one field, the two information is typically contained in separate table columns. City, state, and zip code are stored in separate columns (as they should be), but the mail label printer needs to retrieve them as a properly formatted field. Column data is case – mixed, but the reporting program needs to represent all data in uppercase. The item order table stores the price and quantity of items, but does not need to store the total price of each item (price multiplied by quantity). The total price of the item is required to print the invoice. You need to make totals, averages, or other calculations based on table data

Computed fields do not actually exist in database tables. Computed fields are created at run time within the SELECT statement

Stitching Concat

The Vendors table contains the vendor name and location information. If you want to generate a vendor report, you need to list the vendor location in the name of the vendor in a format like name(location)

The solution is to concatenate the two columns. In MySQL SELECT statements, you can use the Concat() function to concatenate two columns

select concat(vend_name,'(',vend_country')') from vendors order by vend_name

Use the alias AS

The >SELECT statement concatenates the address field well. But what is the name of this new computed column? It doesn't actually have a name, it's just a value. > > This is fine if you just look at the results in the SQL query tool. > > However, an unnamed column cannot be used in a client application because there is no way for the client to reference it. > > To solve this problem, SQL supports column aliases. An alias is an alternate name for a field or value. The alias is given with the AS keywordCopy the code

select concat(vend_name,'(',vend_country')') as vend_title from vendors order by vend_name

Aliases also have other uses. Common uses include renaming the actual table column name when it contains nonconforming characters (such as Spaces), augmenting the original name when it is ambiguous or easily misunderstood, and so on.

To calculate

> In mysql, words in columns can be evaluated, using basic arithmetic operators, and parentheses can be used to distinguish precedence. | | operators that | | -- - | -- - | | | and | | - | | reduction * by | | | | in addition to / | | > SELECT provides testing and test function and the calculation of a very good method. > > Although SELECT is commonly used to retrieve data FROM a table, you can omit the FROM clause for easy access to and processing of expressions. > > For example, SELECT 3*2; SELECT Trim(' ABC '); Will return ABC, and SELECT Now() returns the current date and time using the Now() function. > > From these examples, you can see how to experiment with SELECT as neededCopy the code

Use of functions

Like most other computer languages, SQL supports the use of functions to process data.

Functions are generally performed on data, which provides convenience for data conversion and processing.

Text processing function

| | function that | | -- -- -- -- -- -- -- -- -- -- - | -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - | | Left () | | of string on the Left side of the character | Length () | returns the Length of a string of | | Locate a substring () | find string | | The Lower () | convert strings to lowercase | | LTrim () | removes white Spaces at the list on the left side of the | | Right () | | string on the Right side of the character | RTrim () | removes white Spaces at the string of the Right of the | | the SubString () | | returns the SubString characters | The Upper () | convert string to Upper case | | Concat | | splicing stringsCopy the code

Date and time handlers

| | function that | | -- -- -- -- -- -- -- -- -- -- -- -- - | -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - | | AddTime () | add a time, when (classification) | | CurDate () | returns the current date | | CurTime | () returns the current time | | | Date () returns the Date/time of the Date of | | DateDiff () | calculating the difference between the two Date | | Date_Add () | | the Date of the highly flexible operation function | Date_Format () | returns a Date or time format string | | Day () | part returns a date the number of days | | DayOfWeek () | for a date, Returns the corresponding week | | Hour () | back one Hour of time section | | Minute | () returns a part of the minutes of time | | the Month () | back a Month of the date section | | Now | | () returns the current date and time | Second () | returns a Time of the second part of the | | Time | () returns a date/Time of part Time | | Year () Year parts | | returns a dateCopy the code

Numerical processing function

Several | | letter said Ming | | -- - | -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - | | Abs () | returns the absolute value of a number | | Cos () | returns an Angle cosine | | Exp () | returns the index value of a number | | Mod () | return in addition to operating the remainder | | (Pi) | return Pi | | Rand | () returns a random number | | Sin () | returns a perspective of sine | | Sqrt () | returns the square root of a number of | | Tan () the tangent | | returns a AngleCopy the code

Use of aggregate functions

We often need to summarize data without actually retrieving it, and MySQL provides special functions for that.

Using these functions, MySQL queries can be used to retrieve data for analysis and report generation

Determines the number of rows in the table (or the number of rows that satisfy a condition or contain a particular value). Gets the sum of row groups in the table. Find the maximum, minimum, and average values of table columns (or all rows or some specific rows).

All of these examples require a summary of the data in the table, rather than the actual data itself.

Therefore, returning actual table data is a waste of time and processing resources

function	instructions
COUNT()	Returns the number of rows in a column
MAX()	Returns the maximum value of a column
MIN()	Returns the minimum value of a column
SUM()	Returns the sum of the values of a column
AVG()	Returns the average value of a column

Pay attention to

When count is used, rows with a null value for the specified column are ignored if the column name is specified, but not if the count () function uses an asterisk (*)

Data groups GROUP BY and HAVING

GROUP BY

SQL aggregation functions can be used to aggregate data. This allows us to count rows, calculate and average, and get maximum and minimum values without having to retrieve all the data

All of the calculations so far have been done on all of the table’s data or data that matches a particular WHERE clause.

The following example returns the number of products provided by vendor 1003:

select count(*) as num_prods from products where vend_id = 1003

But what if you want to return the number of products provided by each vendor? Or return products from suppliers that provide only one item, or return products from suppliers that provide more than 10 items?

This is where you need to use grouping, which allows you to divide data into logical groups so that you can perform aggregation calculations on each group.

select vend_id,count(*) as num_prods from products group by vend_id

HAVING

In addition to grouping data BY groups, MySQL allows filtering of groups, specifying which groups are included and which are excluded.

For example, you might want to list all customers with at least two orders. To derive this data, you must filter on the basis of a complete grouping, not individual rows

In fact, all the types of WHERE clauses you’ve seen so far can be replaced with HAVING.

The only difference is WHERE filters rows and HAVING filters groups.

select cust_id,count(*) as orders from orders group by cust_id;

select cust_id,count(*) as orders from orders group by cust_id having count(*) >= 2;

Summarize the SELECT clause and its order

clause	instructions	Whether must
SELECT	The column or expression to return	is
FROM	A table from which data is retrieved	Used only when selecting data from a table
WHERE	Row-level filter	no
GROUP BY	Group show	Use only when calculating aggregation by group
HAVING	Group level filter	no
ORDER BY	Output sort order	no
LIMIT	Number of rows to retrieve	no

Finally, welcome to pay attention to my personal wechat public account “Little Ape Ruochen”, get more IT technology, dry goods knowledge, hot news