Shulou(Shulou.com)06/01 Report--

DML: Data Manipulation Language, mainly used to achieve insert add, delete, update change operations on tables

1. insert add record

1. Adding records adds rows.

When the intra-table field modifier NO NULL is specified, the field must specify a value when adding records. Fields without default values must specify values.

Syntax: INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)

Chinese: INSERT table name [column name...] VALUES(record 1),(record 2),... Specify the field names to be assigned, and then assign values in sequence with values(). If you assign values to all fields, you can omit writing the field names.

Assignment notes: 1, string must use quotation marks. 2, do not allow empty fields must be assigned, if there is no value, you can add tag information, all records will be added tag information for later maintenance. 3. Primary key column values cannot be duplicate. 4. Fields without default values must specify values.

2. insert operation example

Method 1: Specify data manually.

INSERT INTO vmlab values(2,'Hong qigong', 60,' M', 3,4); assign values to all words INSERT INTO vmlab (id,name,age,classID) values(3,'Huang Yaoshi', 56,3); add a row to assign values to specified fields. INSERT INTO vmlab (id,name,age,classID) values(6,'Huang Yaoshi', 56,3),(4,'Ou yangfeng', 60,4),(5,'Duan zhixing', 65,2); Add multiple row assignment to specified fields.

Method 2: Set direct assignment

INSERT INTO vmlab set id=7,name='Guo Jing', age=23; assign values directly to specified fields with set.

Method 3: Extract data from other tables and insert them in batches

Insert into vmlab (id,name,Age,Gender) select tid,name,age,gender from teachers; Extract all rows of specified fields from teachers table and insert them into vmlab table. This method requires that the field order of operation of two tables corresponds, and the field data type is the same. Insert into vmlab (id,name,Age,Gender) select tid,name,age,gender from other.teachers; Extract all rows of specified fields from teachers table of other database and insert them into vmlab table. This method requires that the field order of operation of two tables corresponds, and the field data type is the same. Example: Looking up data from one table and inserting another.

insert into t1 select * from t2;

Delete Delete records:

Syntax: delete from table name where matching condition (usually the record value corresponding to the primary key field); meaning: delete data from a table that successfully matches based on where matching condition. Note: delete must use where to qualify the matching condition, otherwise the entire table will be emptied of data. Examples:

a. DELETE FROM vmlab where id >10

b. Delete FROM hellodb.vmlab where id >10 Empty table: TRUNCATE TABLE students;

III. Update the revision record

Syntax: UPDATE table name SET field name ="Value"WHERE matching condition (usually the record value corresponding to the primary key field); Meaning: Update the value of a table WHERE matching condition, and the assignment comes from the assignment expression specified by SET. Notes:

UPDATE must be scoped with WHERE, otherwise the entire table will be modified. Examples:

UPDATE vmlab SET NAME='Guo Jing' where id=2; Change the name field value of the second record in the vmlab table to Guo Jing

IV. Best practices

To avoid mishandling, it is strongly recommended that you use the mysql -U --safe->updates security update option when connecting to the database, or add safe-updates to the [client] configuration item in/etc/my.cnf to force the security update option to be enabled. After the security update option is enabled, the modification requires that the table must define the primary key, and then when updating the record, the modification range is limited based on the column value after the primary key. When security update is enabled, if the table does not have a primary key, the following prompt appears when updating is executed

After defining the primary key, when executing UPDATE to modify records in security update mode, the where condition must be limited based on the primary key, and other fields cannot be limited as the where condition. The following prompt will appear:

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.