Basic mysql knowledge literacy and mysql literacy

Basic mysql knowledge literacy and mysql literacy

This article mainly introduces some basic knowledge about mysql and prepares for subsequent SQL optimization.

I. Connect to mysql

I will not talk about mysql download and installation here. The first step is to connect to our mysql Server and open the cmd command to switch to the bin directory where you install MySQL Server, enter mysql-h localhost-u root-p

Here,-h indicates your host address (the local host is localhost, remember not to include the port number)-u is the name of the Connection database-p is the connection password. The connection is successful if "yes" appears.

Ii. Common SQL statement 2.1: create database name 2.2: delete database drop database name 2.3: Query System database show databases2.4: use database Name 2.5: query the database table show tables2.6: query the table structure desc + table Name 2.7: query the SQL statement show create table + table Name 2.8: Delete the table drop + table Name 2.9: delete multiple table records at a time: delete t1, t2 from t1, t2 [where condition] If an alias is used after the from clause, alias 3.0 is also required after the delete clause: update Tables t1, t2 multiple times at a time... tn set t1.field = expr1, tn. exprn = exprn; 3: Query

3.1: select common Query

Here I created a data table and put two tables.

3.2: Query non-Repeated Records

Use the keyword distinct as shown in Figure

3.3: sorting and Restriction

Sort by keyword order by desc descending asc ascending order, limit output

Followed by the field after order by (order by can be written only once to first rank the first field and then the second, and so on. The first number after limit is the sum of the numbers of outputs ).

4. aggregation operations in many cases, users need to make some statistics, such as counting the number of people in the company or the number of people in the department, then the aggregation operation will be used. Select [field1, field2... fieldn: fun_name from table name where condition group by field1, field2... fieldnwith rolluphaving condition fun_name is called an aggregate function or an aggregate operation. Common conditions include sum (sum), count (*), max (maximum), and min (minimum ). Group by indicates the fields to be classified and aggregated. For example, if the number of employees is calculated by department, the Department should be written after group by with rollup, which is an optional syntax, indicates whether to summarize the combination of classification and aggregation. having indicates that the results of classification are filtered. 4.1: the number of students in the class is counted by course number. 4.2: the number of students in the grade, and the total number of rollup is the total number of people, we can see that. 4.3: the difference between having and where is the number of people whose age is not less than 20: having is used to filter the aggregated results, while where is used to filter records when the aggregated amount is used, if the logic permits, try to use where to filter records first, which will reduce the result set, greatly improve the aggregation efficiency, and then filter records based on having. 5. Table join can be used to display fields in multiple tables at the same time. There are two main types of connections: inner join and outer join. The main difference is that inner join only filters out matching records of two tables, the outer connection filters out other unmatched records. We often use the inner connection. 5.1: the external connections selected by students are divided into left connections and right connections. Left join (including all records in the left table or even records that do not match it in the right table) Right join (including all records in the right table or even records that do not match it in the left table) we can see that the left join is based on the table on the left, and the right join is based on the table on the right. 6. subquery in some cases, when performing a query, the required condition is the result of another select statement. In this case, the subquery is used, the keywords used for subqueries mainly include in, not in, =, and ,! =, Exist, not exist, and so on. If you use in for query, but the use of Inner Connections can also achieve the above results, the efficiency of inner connections is in many cases higher than that of subqueries, therefore, inline is preferred if the business logic is not affected. 7. Join the two tables to query the data according to certain rules and display the results together. In this case, 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 in the filtered result set. Do not join two tables if they do not match. If we query two fields in each table

For more information, see mysql.