30 minutes review MySQL syntax (bottom)

11. Add Data

An existing student table is designed as follows:

Sno Primary KEY and self-increment, saddress represents the address, set the default value: ' Guangdong province Guangzhou '

1. Add a complete insert into tables values (Val1,val2,..., valn)

INSERT into student values (NULL, ' Li Bing ', ' Male ', ' [email protected] ')

Attention:

A. It is necessary to ensure that the value in values () corresponds to the sequential order one by one of the columns in the table, with the same number

B.sno primary key setting self-increment, can be handed over to MySQL automatic processing, you can pass null value, at this time the record's Sno is automatically generated

Disadvantage: Insert will error when the table structure is changed

　　

2. Add INSERT INTO table_name (COLUMN1,COLUMN2,..., COLUMNN) values according to the specified column (Val1,val2,..., valn)

INSERT into student (Sname,ssex) VALUES (' Wang Ming ', ' Male ')

Attention:

A. It is also necessary to ensure that the number of values in value corresponds to the order of the columns specified earlier

Advantages:

A. The order of the columns can be specified by itself, and the value also corresponds to the column name, and the INSERT statement will not be affected when new columns are added to the table.

B. Compared to inserting a complete line, inserting the specified column, some unnecessary values, can not pass, only need to pass some of the required value, the SQL statement becomes more concise

3. Add multiple rows of records Insert into table_name valuesvalues (Val1,val2,..., valn), values (Val1,val2,..., valn), values (Val1,val2,..., VALN)

INSERT into student values (null, ' Li Ling ', ' female ', ' liling163.com '), (null, ' big ', ' male ', null), (NULL, ' carefree ', ' male ', ' [email protected] ')

On the basis of 1, 2, you only need to keep up with more than one set of data after values, insert multiple rows

Advantages: There are two forms of inserting multiple lines, single-line insert execution multiple times, multi-row Insert execution 1 times, compared to the former, multi-row Insert Execution 1 times, performance time will be greatly reduced

4. Insert the data queried by the SELECT statement

INSERT into student (sname,ssex,semail,saddress)

Select Sname,ssex,semail,saddress from Student

This approach does not require the column names identified by the Select to match the column names of the table where the data is to be inserted, just ensure that the order is consistent and the data is compatible

Insert improves database performance:

1. In production, there may be concurrent access to the database, the situation of the increase and deletion of the investigation, the general situation is higher priority. When you insert at the same time, it takes a long time to build an index, and it can also result in lower performance for subsequent SELECT statements

You can improve performance by reducing the priority of the INSERT statement, syntax format: Insert low_priority into .... Also applies to delete update statements

2. Multi-row Insert execution 1 performance more than one row insert execution more than one time

Security: You can disable the user's insert permissions, limit the user's ability to insert to ensure data security

12. Update, delete data

The UPDATE statement must be used with the WHERE clause, otherwise updating will update all the data in the entire table, which can cause unimaginable results ....

Update single-column update tablename set column = newvalue where condition

Update student Set semail = ' [email protected] ' where sname = ' Wang Ming '

Update multi-column update tablename set column1 = newValue1, Column2 = newValue2,... column_n = Newvaluen Where Condition

Update student Set semail = ' [email protected] ', saddress = ' Guangdong province Dongguan ' where sname = ' Wang Ming '

30 minutes review MySQL syntax (bottom)