MySQL Data manipulation language DML (management language)

Add action: INSERT INTO

One INSERT INTO statement can insert multiple records

INSERT into insert_student values (one, ' Jet Lee ', ' Male ', '), ("Buruce Lee ', ' Male ', '", "Jacky", 25), (45 , ' crystal ', ' Famale ', 26);

INSERT into supports determining whether a primary key (unique) is conflicting to perform an update

INSERT into inert_student values (Dragon girl ', ' female ', 23);

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/76/B6/wKiom1Zan0eSMgObAABUuZOIO4Y072.jpg "title=" 01.jpg "alt=" Wkiom1zan0esmgobaabuuzoio4y072.jpg "/>

Insert into Table name (field list) VALUES (value list) on duplicate key Update field = value, field = value;

INSERT into insert_student values (Dragon, ' girl ', ' female ', and all) on duplicate key update stu_name= ' Dragon girl ', gender= ' Female ', class_id=23;

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M02/76/B5/wKioL1ZaoP7D01OeAABUuZOIO4Y246.jpg "title=" 01.jpg "alt=" Wkiol1zaop7d01oeaabuuzoio4y246.jpg "/>

Note: There is no set after update!

Insert INTO table name SELECT statement

INSERT INTO Insert_student select * from Foreign_student;

//Insert all the same data as the table Foreign_student

Insert INTO Insert_student select null,sut_name,gender,class_id from Foreign_student;

//Copy part of field data from table Foreign_student

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M00/76/B6/wKiom1ZaolqT0hhmAABYSaRCyHw853.jpg "title=" 01.jpg "alt=" Wkiom1zaolqt0hhmaabysarcyhw853.jpg "/>

Note: It is not necessarily the same number of fields to complete the operation, as long as the number of fields consistent with the field type can be inserted!

To query the use of select:

Structure of the basic query statement:

Select field expression list [FROM clause] [WHERE clause] [GROUP BY clause] [ORDER BY clause] [limit clause];

Field expression:

1: If the field name, then the field name is a variable concept, you can participate in the operation

Select MD5 (class_id) from Insert_student;

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M02/76/B6/wKiom1Zapf3z_EXOAAB0OE7UXgE676.jpg "title=" 01.jpg "alt=" Wkiom1zapf3z_exoaab0oe7uxge676.jpg "/>

Therefore, you can use various operators to form an expression in SQL!

Can be a list:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/76/B6/wKiom1Zap2bQC5rnAAA0a9G_iP8029.jpg "title=" 01.jpg "alt=" Wkiom1zap2bqc5rnaaa0a9g_ip8029.jpg "/>

Each expression can have an alias:

The select results are named in the expression itself!

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M01/76/B6/wKioL1ZarHmQeEgrAAA6YQz19A4245.jpg "title=" 01.jpg "alt=" Wkiol1zarhmqeegraaa6yqz19a4245.jpg "/>

But there are two typical problems:

1: The expression is not easy to identify the name directly

2: If the name conflicts, cannot differentiate

Select + As A, now () as B,abs ( -10) as c,1+1 as D;

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M02/76/B7/wKiom1ZarOWzwoR7AAA3dk8OvmI868.jpg "title=" 01.jpg "alt=" Wkiom1zarowzwor7aaa3dk8ovmi868.jpg "/>

As keyword can be omitted, but not recommended!

FROM clause:

Indicates the source of the query, is the table!

You can write a list of table names and use commas to split them!

SELECT * from Select_student,select_class;

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M02/76/B6/wKioL1ZarkXSENJgAACPRl4taWo010.jpg "title=" 01.jpg "alt=" Wkiol1zarkxsenjgaacprl4tawo010.jpg "/>

If there is no condition at this time, it is equivalent to forming a cartesian product!

Cartesian product: Each element of a set has an association with each element of the B set!

A table records all records and is associated with all records in Table B!

All the fields are listed in the results, including the name of the duplicate (ID field in)

You can alias the corresponding field name:

Access to a field at this time requires the table name. The form of the field name!

Select Select_student.id as s_id,select_class.* from Select_student, Select_class;

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M01/76/B6/wKioL1ZasHLBb4-9AAB_gh6fJXI268.jpg "title=" 01.jpg "alt=" Wkiol1zashlbb4-9aab_gh6fjxi268.jpg "/>

Select Select_student.id as S_id,stu_name,gender,class_id,select_class.*from select_student,select_class;

If the table name is found more than once. The name of the field, you can alias the table name!

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M00/76/B7/wKiom1ZasQLClzd7AACWOB485DY850.jpg "title=" 01.jpg "alt=" Wkiom1zasqlclzd7aacwob485dy850.jpg "/>

Dual's question:

The virtual table name problem, make the syntax more canonical!

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M01/76/B7/wKiom1ZasdeBxc2DAAAj-Zw-cP4141.jpg "title=" 01.jpg "alt=" Wkiom1zasdebxc2daaaj-zw-cp4141.jpg "/>

WHERE clause, query condition clause

Where Condition expression

Where each record is found and the conditional expression is executed in turn, the data is returned according to the condition result!

form the basic elements of conditional expressions: data, (variables), operators, function calls!

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M02/76/B7/wKiom1Zas76g0vaUAABwwe_OjgA219.jpg "title=" 01.jpg "alt=" Wkiom1zas76g0vauaabwwe_ojga219.jpg "/>

Typical operators are:

Relationship: >,<,=,>=,<=,<>,!=

Relational operators like: Fuzzy queries

Not Like:like take counter

Between 3 and 5: Between 3 and 5 (contains 3 and 5)

In/not in: Inside/not within a set (finite elements together called set)

Note: In the bulk operation, In/not in the high rate of the mirror!

Null value judgment: You cannot use the normal operator because the result of the operation is null and cannot be used as an operation condition!

You should use is NULL or is NOT NULL to judge!

function IsNull () can also be done to determine

SELECT * from Select_student where IsNull (class_id);

MySQL Data manipulation language DML (management language)