Insert records
There are two basic syntaxes for inserting records
Inserting basic syntax one
##Example insert into user values(2,'Xiao Ming','Male') Example description # Insert the value id into the user table as 2, the name is Xiao Ming, and the gender is maleInsert Basic Grammar 2
##Category | Detailed explanation |
Basic syntax | insert into table values(value 1, value 2, value n); |
Explanation
The difference between basic syntax 1 and basic syntax 2 is:
How many insert statements are there in the table for basic syntax 1? How many values must be inserted into each field. No one can be more, and no one can be less. If there is a default value and you don’t want to pass it, you can write null.
In basic syntax 2, unless there are required fields, values must be written. If you don't want to write a default value, you can ignore it. mysql will automatically supplement the default value.
In basic syntax 2, the order of user(id,username,sex) fields is the order of values.
Assume that there is a table called the user table. We describe the fields, field descriptions, types, optional fields and required states. The table structure is as follows:
Category | Detailed Explanation |
Basic syntax | insert into table (field 1, field 2, field n) values (value 1, Value 2, value n); |
Example | ## insert into user(id, username,sex) values(213,'小方',1); |
Example description | Insert the id as 213 into the user table, the username as Xiaofang, and the gender as 1 |
Field | id | username | email | password | sex |
Chinese Description | Username | Email | Password | Gender | |
Type Description | ##int | varchar(50) | |||
Auto-increment | Required | Optional field, the default value is 123@php.com | Optional fields | Required fields |
Write the insert statement in the above table according to the basic syntax:
insert into user values(null,'小明','xiaoming@php.com',null ,1);
Note
You can not specify the field name, but after values The order should be consistent with the sorting of table fields.
Fields with default values do not need to be written, then they will be default values.
If there is a default value or a nullable field and you do not want to pass in a specific value, you can write null.
The data format must be consistent with the data format specified in the table.
Write the insert statement in the above table according to basic syntax 2:
insert into user(username,sex) values('小明',1);
Note that
ID is an auto-incremented one There is no need to pass in a value for the segment. The value of this field will be automatically increased by 1 each time it is inserted.
Fields with default values and nullable values do not need to be passed
Subject to the insertion order of table user(username,sex)
Basic syntax 2 is the more common usage
Basic syntax variant: insert multiple records at one time
insert into user(username,password,sex) values('黄晓明', 'abcdef', 1), ( 'angelababy', 'bcdeef', 0), ( '陈赫', '123456', 1), ('王宝强', '987654', 1);
Query records
Before explaining the query, I prepared a data table for everyone. This table stores the bank's balance and basic information about the user.
We have defined a table structure named php.
The statement to create the table is as follows:
CREATE TABLE money ( id INT NOT NULL AUTO_INCREMENT , username VARCHAR(50) NOT NULL , balance FLOAT NOT NULL , province VARCHAR(20) NOT NULL , age TINYINT UNSIGNED NOT NULL , sex TINYINT NOT NULL , PRIMARY KEY (id(10)) ) ENGINE = InnoDB CHARACTER SET utf8;
The table structure and data are displayed as follows:
id | username | balance | province | age | sex |
1 | Xiao Ming | 1500 | Anhui | 30 | 1 |
2 | 小方 | 532 | 山东 | 18 | 1 |
3 | 小红 | 543 | Zhejiang | 14 | 0 |
4 | 小白 | 8764 | 北京 | 27 | 1 |
Note:
balance refers to the balance
province refers to the province
Basic query
##Example description Query all results in all fields in the php tableCategory | Detailed explanation |
Basic syntax | select * from table; |
##Example | select * from php; |
CategoryDetailed explanationBasic syntaxselect field from table; ##Example##Example description
select id,username, balance from php; | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Query all results in the id, username, and balance fields in the money table | Conditional query where ##Query all results with age 30 in the php tableConditions that can be followed by where Comparison operations The records that meet the conditions are listed in the result set. In the above example, the field after where is the ‘=’ of a field. In addition, you can also use comparison operators such as >, <, >=, <=, !=, etc.;
Logical operators Multiple conditions can also use logical operators such as or and and to perform multi-condition joint queries
|