The first 5 Chapter MySQL Add, delete, modify and check the table data

5-1 DML Statement insert table data
5-2 Worm replication
5-3 DML Statement of the table data modification and deletion
5-4 DQL Simple query of statement

5-1 DML Statement insert table data

DML sentence

DML(Data Manipulation Language Data operation language It is used to add, delete and modify the data in the database . keyword :insert,
delete, update etc.

insert record

1. Keyword description

INSERT INTO Table name - Indicates which table to add data to
( Field name 1, Field name 2,–)– Which fields to change
VALUES( value 1, value 2,–);– Not setting the value of several bodies

2. Be careful

• The value must correspond to the field , The number is the same , The same type
• The data size of the value must be within the length of the field
• In addition to numerical types , Values of other field types must be quoted .( Single quotation marks are recommended )
• If you want to insert a null value , You don't have to write fields , Or insert null

Insert all fields

• All field names are written out
  INSERT INTO Table name （ Field name 1, Ning Duan name 2, Field name 3…) VALUES ( value 1, value 2, value 3);
• Do not write the field name
  INSERT INTO Table name VALUES C value 1, value 2, value 3-);

Insert partial data

INSERT INTO Table name ( Field name 1, Field name 2,…) VALUES ( value 1, value 2,…);
Fields that do not have data added will use NULL

Specific operation :

establish db2 database , And use .

CREATE DATABASE db2 ;
usE db2;

Create a complete student information table , Including the trainees id, full name , Age , Gender , Home address , Phone number , Birthday , Math scores , English scores

CREATE TABLE student (
id int,
name varchar(20),
age int,
sex char(1) ,
address varchar(200),
phone varchar(20),
birthday date,
math doub1e,
english double
);

Insert partial data , Add... To the student list id, name, age, sex,address data

INSERT INTO student (id , name ,age,sex, address)values(1, ‘ Zhang San ’,19,‘ male ’,' The Beijing municipal ");

Tips : Use SELECT * FROM Table name ; Test whether the insertion is successful

5-2 Worm replication

What is worm replication

Based on the existing data , Copy the original data , Insert into the corresponding table

Grammar format :INSERT INTO Table name 1SELECT * FROM Table name 2;

effect : Table name 2 Copy the data in to the table name 1 in

Specific operation :

• establish student2 surface ,student2 The structure and student The table structure is the same

CREATE TABLE student LIKE student2;

• take student The data in the table is added to student2 In the table

INSERT INTO student SELECT * FROM student2;

Be careful : If you just want to copy student In the table name,age Field data to student2 The following format is used in the table

INSERT INTO student2(name ,age）SELECT name ,age FROM student;

5-3 DML Statement of the table data modification and deletion

Update table records
1. Modify data without conditions

UPDATE Table name SET Field name = value ;

2. Modify data conditionally

UPDATE Table name SET Field name = value WHERE Field name = value ;

3. Keyword description

UPDATE: Modifying data
SET: Modify which fields
WHERE: Specified conditions

4. Specific operation :

Modify data without conditions , Change all gender to female

UPDATE student SET sex=‘ Woman ’;

Modify multiple columns at once , hold id by 3 Of the students , Change the age to 26 year ,address Change to Beijing

UPDATE student SET age=26,address=‘ Beijing ’ WHERE id=3;

Delete table record

1. Delete data without conditions DELETEFROM Bereavement ;
2. Conditional division data DELETEFROM Table name WHERE Field name = value ;

3. Specific operation

• Delete data conditionally , Delete id by 3 The record of
  DELETE FROM student WHERE id=3 ;
• Delete data without conditions , Delete all data in the table
  DELETE FROM student;

I
truncate Delete table record

TRUNCATE TABLE Table name ;

truncate and delete The difference between :

• delete Is to add a piece of data in the table — Delete
• truncate Is to destroy the whole table , Recreate a new table , The new table structure as like as two peas table tables are identical.

5-4 DQL Simple query of statement

**DQL（Data Query Language）** Data query language
Used to query the records of tables in the database （ data ）. keyword ：select,where etc.

Be careful ： The query does not modify the data in the database , It's just a form of displaying data

Query all data in the table

1、 Use * Represents all columns ：SELECT * FROM Table name ;

2、 Write out the field name of each column to be queried ：SELECT id,name,age FROM Table name ;

Query data for a specified column ：SELECT id,name,age,…… FROM Table name ;

notes ：2 Than 1 Efficient ,1 To retrieve all information except fields

Alias query

1、 Give columns when querying 、 The alias specified in the table needs to use AS keyword

2、 The advantage of using aliases is that it is convenient to view and process the queried data

SELECT Field name 1 AS Alias , Field name 2 AS Alias …… FROM Table name ;

among AS You can save writing

Clear duplicate values

1. Query the specified column and no duplicate data appears in the result

SELECT DISTINCT Field name 1, Field name 2 FROM Table name ;

2. Query results participate in operations

1、 A column of data and fixed value operation ：SELECT Name 1+ Fixed value FROM Table name ;

2、 A column of data and other column data participate in the operation ：SELECT Name 1+ Name 2 FROM Table name ;

Be careful ： The operation must be of numeric type

demand ： mathematics + English scores

select math+english (as) Total score from student;

demand ： Age +10

select name full name ,age+10 as Age from student;