Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of DML language operation in MySQL, which is very detailed and has certain reference value. Friends who are interested must finish it!

Additional note, foreign keys: do not use foreign keys, all foreign key concepts are solved in the application layer.

In addition, the columns of the database, that is, field names, should be marked with floating symbols as far as possible.

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

Databases: rows (data), columns (fields)

Note: this page solves the data problem of the row. The previous page is to resolve the field problem of the column.

DML language: data manipulation language

1. Add (insert)

2. Update (update)

3. Delete (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 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')

Note:

1. The symbol above the indication and field name is: gone with the Wind

2. The symbol above the value is: single quotation mark'

two。 Modify update

2.1 symbols for judging statements

Operator meaning range result = equal to 5=6false or! = not equal to 56true > greater than 1 OR 1 > 2true

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 values under all name fields are BeiHang

2.3 modify the value of a field and specify conditions

There are conditions for change. Format: UPDATE `Table name `SET `Field name` = value WHERE id= value 1 OR id= value 2

UPDATE `student`SET `name` = 'DongDa' WHERE id=1 OR id=3-the value under all name fields with id of 1 and 3 is DongDa

2.4 modify the values of multiple fields and specify conditions

There are conditions for change. Format: UPDATE `table name `SET `field name 1` = 'value 1', `field name 2` =' value 2' WHERE field name BETWEEN Numeric 1 AND Numeric 2

UPDATE `student`SET `name` = 'BeiHang', `sex` =' man' WHERE age BETWEEN 20 AND 25-effect is in the range of 20 to 25 for all age

The name inside becomes BeiHang,sex and becomes 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 delete the student table completely

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

3.2 method 2:truncate command to delete all data in the table

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

3.3 difference between two commands to delete all data in a table

After the delete is deleted, the data is added to the table. The self-increasing id will continue the previous downward sorting.

After the truncate is deleted, the self-increasing id will not be sorted down.

Therefore, it is recommended that you use truncate when you want to delete all data in the table.

The above is all the contents of the article "sample Analysis of DML language Operations in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.