MySQL DML language operation example

Additional explanation, foreign keys: Do not use foreign keys, all foreign key concepts are solved at the application layer.

Additional note: Database columns, that is, field names, should be prefixed with floating symbols as much as possible.

The purpose of database existence: data storage and data management.

Database: rows (data), columns (fields)

Note: This page solves the data problem of the row. The previous page is about solving the column field problem.

DML language: Data Manipulation Language

1. Add (insert)

2. Update

3. Delete

1. Add insert (add data to the row)

-- Insert statement (add)
-- Format:

INSERT INTO `table name`(`field name 1`,`field name 2`,`field name 3`) VALUES ('value 1','value 2','value 3'),('value 1','value 2','value 3'),('value 1','value 2','value 3')
INSERT INTO `student`(`name`,`age`,`sex`) VALUES ('xiaoming','22','man'),('xiaohong','20','woman'),('xiaoli','25','man')

Notice:

1. The symbol above the field name is: float`

2. The symbol above the value is: single quote '

2. Modify update

2.1 Symbols of judgment statements

2.2 Modify the value of a field, unconditionally

-- Change, unconditionally. Format: UPDATE `table name` SET `field name` = value

UPDATE `student` SET `name` = 'BeiHang' -- The effect is that the value of all name fields is BeiHang

2.3 Modify the value of a field and specify conditions

-- Change, conditional. Format: UPDATE `table name` SET `field name` = value WHERE id=value1 OR id=value2

UPDATE `student` SET `name` = 'DongDa' WHERE id=1 OR id=3 -- The effect is that the value of the name field of all students with id 1 and 3 is DongDa

2.4 Modify the values ​​of multiple fields and specify conditions

-- Change, conditional. Format: UPDATE `table name` SET `field name 1` = 'value 1', `field name 2` = 'value 2' WHERE field name BETWEEN value 1 AND value 2

UPDATE `student` SET `name` = 'BeiHang',`sex` = 'man' WHERE age BETWEEN 20 AND 25 -- The effect is all students whose age is between 20 and 25

The name in the text box changes to BeiHang, and sex changes to man.

3. Delete (delete command or truncate command)

3.1 Method 1: delete command deletes all data and specified data in the table

DELETE FROM `student`; -- The effect is to completely delete the student table

DELETE FROM `student` WHERE id=1; -- The effect is to delete the specified data in the student table

3.2 Method 2: truncate command deletes all data in the table

TRUNCATE `student`; -- The effect is to completely delete the student table

3.3 The difference between the two commands to delete all data in the table

After deleting, if you add data to the table again, the auto-incremented id will continue to be sorted downwards as before;

After truncate is deleted, the auto-incremented id will not be sorted downwards.

Therefore, if you want to delete all data in a table, it is recommended to use truncate.

The above is the details of the MySQL DML language operation example. For more information about MySQL DML language (addition, deletion and modification), please pay attention to other related articles on 123WORDPRESS.COM!