MySQL insertion and query statement operations (MYSQL statement operation tutorial 1)

Inserting, browsing, modifying and deleting records in the data table can be completed using sql statements in the MySQL command line. The following describes how to execute basic SQL statements in the MySQL command line.

1. Insert records

When establishing an empty database and data table, the first thing you need to consider is how to add total data to the data table. This operation can be used insert statement to complete.

The syntax format is as follows:

insert into data table name(column_name,column_name2,...)value(value1,value2,...)

In MySQL, Multiple rows of records can be inserted at the same time. The value list of each row of records is separated by commas after the VALUES keyword, while standard SQL statements can only insert one row of records at a time.

2. Query database records

To query data from the database, you must use Select the data query statement. The select statement is the most commonly used query statement. Its use is somewhat complex, but its function is powerful.

The syntax format of the select statement is as follows:

select selection_list                                       //要查询的内容，选择哪些列
from 数据表名                                               //指定数据表
where primary_constraint                            //查询时需要满足的条件，行必须满足的条件
group by grouping_columns                       //如何对结果进行分组
order by sorting_columns                           //如何对结果进行排序
having secondary_constraint                     //查询时满足的第二个条件
limint count                                                //限定输出的查询结果

The clauses used will be introduced one by one later. First, we will introduce the simple application of the select statement.

Use select statement to query a data table

When using select statement, first determine the column to be queried. "*" represents all columns.

For example: Suppose there is a table called admin. We want to query all the data in this table. The syntax is

select * from admin;

This is all the data in the query table For column operations, you can also query a certain column or multiple columns in the table.

Query one or more columns in the table

Query for multiple columns in the table, Just specify the column name to be queried after select, and separate multiple columns with commas.

For example: Suppose there is a table named admin. We want to query the id, user, password and email fields in the table, and specify the query condition as the user ID number is 1.

select id,user,password,email from admin where id = 1;

This is a query method for one column of the table.

Multiple table query

To query multiple data tables, the key is in the where clause In the setting of query conditions, the field name to be searched is best represented by "table name, field name". This can prevent the inability to know which table the field belongs to due to the same field name between tables. The linkage relationship formed between them should be written in the following form:

Table 1. Field = Table 2. Field and other query conditions

SQL statement format for multi-table query As follows:

select field name from table 1, table 2...where table 1. field = table 2. field and other query conditions

For example: query students table and grade table, the query condition is that the userid of the student table is equal to the sid of the grade table, and the userid of the student is equal to 001. The specific code is as follows:

select * from student,sscore where student.userid = sscore.sid and student.userid =001

Note: Regarding the select statement, there are many forms of application, which are introduced here It is only the simplest content, and those who are interested can conduct in-depth research on it. The use of SQL statements is a very rich knowledge point. Proper use of SQL statements can improve the speed of program query and data output. Since this book focuses on learning the PHP language, there will no longer be an in-depth explanation of the knowledge of SQL statements here.

The above is the detailed content of MySQL insertion and query statement operations (MYSQL statement operation tutorial 1). For more information, please follow other related articles on the PHP Chinese website!