Detailed introduction to mysql basic knowledge literacy (picture)

This article mainly introduces some very basic knowledge about mysql. Has very good reference value. Let’s take a look with the editor below

This article mainly introduces some very basic knowledge about mysql to prepare for the subsequent sql optimization.

1: Connect to mysql

About the download and installation of mysqlI won’t talk about it here. The first step is to connect our mysql server, open the cmd command and switch to the bin directory where you installed MySQL Server, then enter mysql -h localhost -u root -p

where -h represents your host address (this machine is localhost, remember Do not bring the port number) -u is Connect to the databasename -p is the connection password. The following picture appears, indicating that the connection is successful

2: Commonly used sql statements

2.1: Create database create database database name

2.2: Delete the database drop database database name

2.3: Query the database in the system show databases

2.4: Use the database use database name

2.5: Query Database tables show tables

2.6: Query table structure desc + table name

2.7: Query sql statement to create table show create table + table name

2.8: Delete table drop + table name

2.9: Delete multiple table records at one time: delete t1,t2 from t1,t2[where condition] If an alias is used after from, then an alias needs to be used after delete

3.0 : Update multiple tables at one time update t1, t2 ...tn set t1.field=expr1,tn.exprn=exprn;

3: Query

3.1 :Select ordinary query

Here I created a data and put 2 tables, see the picture below

## 3.2: Query unique records

Use the keyword distinct as shown below

3.3: Sort and limit

Use the keyword order by Sort desc descending asc ascending, limit keyword to limit the output

order by followed by fields (order by only needs to be written once to sort the first field first and then the second By analogy, the first number after limit is the limit, and the second number is the number of outputs).

Four:

AggregationOperation

In many cases, users need to perform some statistics, such as counting the number of people in the entire company or the number of departments, which will be used at this time Aggregation operations. The aggregation operation syntax is introduced

select [field1, field2...fieldn] fun_name from table name

where condition

group by field1, field2...fieldn

with rollup

having condition

fun_name is called an aggregation function or aggregation operation. Common ones include sum (sum), count (*) number of records, max (maximum value) , min (minimum value).

group by indicates the field to be classified and aggregated. For example, the number of employees counted according to department classification. The department should be written after group by.

with rollup is an optional syntax, indicating whether to aggregate the classification. The final combination is summarized

having means that the classified results are filtered

4.1: Count the number of people in the class according to the course number

4.2: Count the number of people by grade and count the total number of people

rollup is to summarize the number of people, as we can see from the picture.

4.3: Count the number of people aged no less than 20

The difference between having and where: having is to filter the aggregated results, while where is When aggregating, the records are filtered. If the logic allows, use where to filter the records first. This will reduce the result set and greatly improve the efficiency of aggregation, and then filter based on having.

5: Table connection

If you need to display fields in multiple tables at the same time, you can use table connection to achieve such a function. From a broad perspective, it can be divided into

inner join and outer join. The main difference between them is that the inner join only filters out the records that match each other in the two tables, while the outer join will filter out other unmatched records. What we often use is inner connection.

5.1: Query the courses selected by students

Outer joins are divided into left joins and right joins.

Left join (contains all records in the left table even if there are no matching records in the right table)

Right join (contains all The records in the right table even have no matching records in the left table)

It can be seen that the left join is based on the left table, and the right join is based on the right The table is the main one.

6: Subquery

In some cases, when querying, the required condition is the result of another select statement. In this case, subquery is used. Query, the keywords used for subqueries mainly include in, not in, =, !=, exist, not exist, etc.

If you use in to query

But using inner joins can also achieve the above effects

But the efficiency of inner joins is higher than that of subqueries in many cases, so if it does not affect the business logic Under the premise, inline is given priority.

Seven: Union

Query the data of the two tables according to certain rules, and merge the results to display them together. At this time we can use union or union all. The specific syntax is as follows

select * from t1 union\union all select * from t2 union\union all select * from tn;

The difference between union and union all is that union is the result set being filtered Remove duplicate records.

Remember not to join two tables if they do not match, as follows

If we query each table 2 fields

Eight: Common functions

8.1: concat

cancat function: pass in The parameters are connected into a string. The result of concatenating any string with null is null, as shown below

8.2: The insert(str,x,y,instr) function replaces the string str starting at the Replace with you

8.3: Lower(Str) and Upper(Str) convert the string to lowercase or uppercase.

8.4: left(str,x) and right(str,x) return the leftmost x characters and the rightmost x characters of the string respectively. If the The two parameters are null and no characters are returned

8.5: ltrim(str) and rtrim(str) remove the characters on the left or right side of the string

8.6: repeat(str,x): Returns the result of str repeated x times

8.7: replace(str, a, b) Replace all occurrences of string a in string str with string b.

8.8: trim(str) removes leading and trailing spaces

8.9: substring(str,x, y): Returns a string of y string lengths starting from the x-th position in the string str.

The above is the detailed content of Detailed introduction to mysql basic knowledge literacy (picture). For more information, please follow other related articles on the PHP Chinese website!