MySQL Learning 13: Working with records in data sheets (i)

Records that manipulate data tables in a MySQL database include: Record insertions, record changes, record deletions, and recorded queries. In simple terms, it

Is the record in the data table additions and deletions to change.

One insert record

The data tables in the MySQL database are inserted in three forms, and we have already used the record inserts when we were working on the data table.

That's just one of the most common ways we use it, so let's look at three ways to insert records:

(1) Insert command

The first syntax format for inserting records in a data table in a MySQL database is also the most commonly used syntax format:

INSERT [INTO] table_name [(Col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...), (...),...;

Example:

While we are writing records to a data table, we first create a data table and see all the fields:

CREATE TABLE Users3 (

ID SMALLINT UNSIGNED auto_increment PRIMARY KEY,

username VARCHAR () not NULL,

Password VARCHAR (+) not NULL,

Age TINYINT UNSIGNED not NULL DEFAULT 10,

Sex BOOLEAN

);

DESC Users3;

1) Omit all fields

The following records insert lists the various cases in which the fields are inserted:

INSERT users3 VALUES (NULL, ' Tom ', ' 123 ',1);

INSERT users3 VALUES (NULL,' John ', ' 223 ',DEFAULT, 0);

INSERT users3 VALUES (DEFAULT, ' Rose ', ' 323 ',1);

INSERT users3 VALUES (DEFAULT, ' Paul ', ' 123 ',23-5+1, 1);

SELECT * from Users3;

INSERT Users3 VALUES (DEFAULT, ' Tom ', ' 123 ', 25);

The above error indicates that the inserted record does not match the number of columns, so if we do omit all field names when inserting the record, we want to

sequentially Insert a value equal to the number of columns, otherwise the record's write is unsuccessful, keep this in mind when writing to the data table.

2) Insert the specified field:

INSERT Users3 (username,password,age) VALUES (' Kobe ', ' 111 ', at all);

SELECT * from Users3;

3) insert more than one record:

INSERT Users3 VALUES (null, ' Dave ', ' 456 ', 23,0), (null, ' Jack ', ' 456 ', 24,1);

SELECT * from Users3;

(2) INSERT set command

The second syntax format for inserting records into a data table in a MySQL database is:

INSERT [into] table_name SET col_name={expr | DEFAULT},...;

Explanation: The difference from the first way of inserting records is that this method can use a subquery (subquery). There are three cases of raising a subquery,

there is a situation in called a subquery caused by a comparison operator, where an equal sign (=) can also be considered a typical comparison operator. This method also has a

the difference between one method is that the first one can insert multiple records at once, but the second method only inserts a single piece of Records.

Example:

INSERT users3 SET username= ' Jord ', password= ' 345 ';

SELECT * from Users3;

(3) INSERT Select command (super-outline)

The third syntax format for inserting records into a data table in a MySQL database is:

INSERT [INTO] table_name [(Col_name,...)] SELECT ...;

Explanation: This method inserts the query results into the specified data table. Since we now know less about the SELECT statement, it is not a good idea

The solution, but we still write the example as usual, after we have finished the query we can go back and learn again.

Example:

1 before inserting a record, create an empty data table test:

CREATE TABLE Test (

ID TINYINT UNSIGNED PRIMARY KEY auto_increment,

Username VARCHAR (20)

);

2 We insert the username of the record with the age greater than 25 in the data table Users3 into the datasheet test:

INSERT Test (username) SELECT username from Users3 WHERE age >;

3 Querying the data table Test record:

SELECT * from test;

Two update history

MySQL Database update data table record is also a very common thing, simple is to change some of the records of some fields, or re-assign the value

Wait a minute. The way to update records includes single-table updates and multiple-table updates, where we only learn single table updates, and multi-table updates are described later.

Take a look at the syntax format for single-table updates:

Updtae [low_priority] [IGNORE] table_reference SET COL_NAME1={EXPR1 | DEFAULT} [, col_name2=

{EXPR2 | DEFAULT}] ... [WHERE where_condition];

(1) Omitting the keyword where

If the keyword where is omitted, all records will be updated.

Example:

1) Update a column: SELECT * from Users3;

UPDATE users3 SET age = age + 5;

SELECT * from Users3;

2) Update multiple columns:

SELECT * from Users3;

UPDATE users3 SET age = age-id,sex = 0;

SELECT * from Users3;

(2) There are keywords where under specify a few records update the situation

UPDATE users3 SET age = Age + ten WHERE ID% 2 =0;

SELECT * from Users3;

three delete records

Since the data tables in the MySQL database are inserted and updated, there is a case for deleting records, and there are two ways to delete records: Single

Table deletion and multi-table deletion, here is also only said single table delete.

Syntax format for single-table deletions:

DELETE from table_name [WHERE where_condition];

Example:

Specifies that the condition is deleted, and if you do not specify which records to delete, all will be deleted.

DELETE from Users3 WHERE id = 6;

SELECT * from Users3;

Suppose we re-insert a record, what is the value of the ID field of the inserted record?

INSERT users3 VALUES (NULL, ' Bob ', ' 234 ', 34,1);

SELECT * from Users3;

MySQL Learning 13: Working with records in data sheets (i)