Shulou(Shulou.com)05/31 Report--

This article will explain in detail what the basic knowledge about mysql is. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

One: connect mysql

I won't talk about the download and installation of mysql here. The first step is to connect to our mysql server, open the cmd command and change to the bin directory where you installed MySQL Server, and then type mysql-h localhost-u root-p

Where-h represents your host address (native is localhost, remember not to take the port number)-u is the connection database name-p is the connection password. The following figure indicates that the connection is successful

Second: commonly used sql statements

2.1Create database create database database name

2.2: delete database drop database database name

2.3: query the database show databases in the system

2.4: use the database use database name

2.5: query the table show tables of the database

Query table structure desc + table name

Query the sql statement show create table + table name that creates the table

Delete table drop + table name

Delete multiple table records at a time: delete T1 from T2 from T2 [where condition] if aliases are used after from, then aliases are also needed after delete

Update multiple tables at one time update T1 Magi T2... tn set t1.fieldbooks expr1retn.exprnbooks exprn

Three: inquiry

3.1:select general query

Here I create a data and put two tables, look at the following figure

3.2: query does not repeat records

Use the keyword distinct as shown below

3.3: sorting and restriction

Sort with the keyword order by, desc descending, asc ascending, and limit keyword to restrict output

Order by is followed by a field (order by is written once to rank the first field, then the second, and so on, the first number after limit is simply, and the second is the number of output).

Four: aggregation operation

In many cases, users need to do some statistics, such as counting the number of people in the whole company or department, then the aggregation operation will be used. Aggregate operation syntax into the play

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

Where condition

Group by field1,field2...fieldn

With rollup

Having condition

Fun_name is called aggregate function or aggregate operation. Common ones are sum (summation), count (*) records, max (maximum), and min (minimum).

Group by indicates that the fields to be grouped together, such as the number of employees by department, should be written after group by.

With rollup is an optional syntax that indicates whether the combination after classification aggregation is summarized.

Having means to filter the classified results for the second time.

4.1: count the number of classes according to the course number

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

Rollup is a summary of the number of people, as we can see from the figure.

4.3: count the number of people aged not less than 20

The difference between having and where: having filters the aggregated results, while where filters records when aggregating money. If logic allows, use where to filter records first, which will reduce the result set, greatly improve the efficiency of aggregation, and then filter according to having.

Five: table connection

If you need to display fields from multiple tables at the same time, you can use table joins to do so. From the general category, 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, while the outer join will filter out other mismatched records. We often use the inner join.

5.1: inquire about the courses chosen by students

The outer connection is divided into left connection and right connection.

Left join (contains all records in the left table and even no records matching it in the right table)

Right join (contains all records in the right table or even records that do not match it in the left table)

It can be seen that the left join is dominated by the left table, and the right join is dominated by the right table.

Six: subquery

In some cases, when making a query, the condition required is the result of another select statement, and a subquery is used. The keywords used for the subquery mainly include in, not in, =,! =, exist, not exist, etc.

For example, use in to query

But the above effect can also be achieved by using internal connection.

However, the efficiency of inner join is higher than that of subquery in many cases, so inline is preferred if the business logic is not affected.

Seven: unite

The data of the two tables are queried according to certain rules, and the results are displayed together. At this point 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 removes duplicate records from the filtered result set.

Keep in mind that you cannot join two tables that do not match, as follows

If we query 2 fields for each table

Eight: common functions

8.1:concat

Cancat function: concatenates the passed parameters into a string, and the result of any concatenation of the string with null is null, as shown below

The 8.2:insert (str,x,y,instr) function, which starts the string str at position X, and replaces the string with y characters long by replacing the string with you under instr. Replace the four characters after the fifth character of the string hellomysql2016

8.3:Lower (Str) and Upper (Str) convert strings 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 second parameter is null, no characters are returned.

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

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

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

8.8:trim (str) removes the spaces at the beginning and end

8.9:substring (str,x,y): returns a string of y string length from the xth position in the string str.

This is the end of this article on "what is the basic knowledge of mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.