MySQL Memo point (bottom)

junction table

Create a junction from table 1, table 2 is similar to the inner join; if you lose the WHERE clause, a Cartesian product appears

Inner Junction INNER Join

Advanced Junction

Self-junction example: Select field B from table where field a = (SELECT field A from table where field B conditions), a single SQL text references a table multiple times

Outer Junction left OUTER join right OUTER join full join There are three different joins, representing the result of the table can be empty, or can be empty

Combination Query

and SQL1 UNION SQL2 The result set field names in SQL1 and SQL2 must be exactly the same

Eliminate duplicate and SQL1 UNION all SQL2 SQL1 and SQL2 result set field names must be exactly the same

and sort SQL1 UNION all SQL2 the order by field this sort is to sort the combined total result set

Inserting Data Multi- line INSERT, insert search value more　　　　　　

Single-line Insert "value" can be null, try to fully list the field names, rather than relying on their own order

INSERT  into Table name            (Field name 1, field name 2, field name 3)     VALUES             (value 1, value 2, value 3);    

MultiRow Insert This is the easiest way to avoid using multiple "single-line insert" statements

INSERT  into Table name            (Field name 1, field name 2, field name 3)     VALUES             (value 1x, value 2x, value 3x),        (value 1y, value 2y, value 3y),        (value 1z, value 2z, value 3z)    

Insert Retrieve value Insert-select statement, insert section and select section of the column name can not match, as long as the type consistent guarantee not error can be,

INSERT  into Table name            (Field name 1, field name 2, field name 3)     SELECT             Field name 1, field name 2, field name 3    from             table name x    

For example:

INSERT into student (NO)

SELECT Student_no from people

Update Delete Data "Update", "delete" are more commonly used, avoid the use of the update, DELETE statements without the Where condition, they are too dangerous　　　　　　

Update

UPDATE SET         Field 1= value 1,    field 2= value 2WHERE         condition

Delete

DELETE  from Table name         WHERE             conditions    

Creating and manipulating tables

Create a table

CREATE TABLE table name (            notnull  auto_increment,        nullDEFAULT'  deolin',        ...,        PRIMARYKEY  (field 1)    ) ENGINE=InnoDB        

Field name type null value? Self-increment? Default value,
Primary key
Engine type

Update table

ALTER TABLE table name (            ADD         DROP    )        

Delete a table

DROP  Table Name

Renaming a table

TABLE  to table name 2

View the webquery of coop time　　　　　　　　

Create a View

CREATE VIEW  as                                         SELECT statement                                

The common function of views is to reuse SQL statements, especially SELECT statements

In general, views are not associated with insert, UPDATE, DELETE

Cursor　　　　　　　　

A result set used to manipulate SQL text, a common use might be paging

Trigger

Create a Trigger

CREATE TRIGGER INSERT  on CSTs           for Each ROW             BEGIN            SELECT ' Success '    END    

Begin-end is used to hold multiple SQL statements, not required

Delete Trigger

DROP TRIGGER Csts_del

Insert Trigger

CREATE TRIGGER INSERT  on CSTs           for Each ROW             SELECT new.no    

After can be replaced with Before,before for the purification of pre-insertion data, after for insert confirmation

The New keyword refers to a virtual table in which the data that is inserted by the trigger INSERT statement is stored in the table.

Delete Trigger

CREATE TRIGGER INSERT  on CSTs           for Each ROW             SELECT old.no

The old keyword is similar to new and stores the data deleted by the trigger DELETE statement

UPDATE trigger

CREATE TRIGGER  on CSTs              for Each ROW                 SET = Upper (New.name);        

The Upper () function returns the updated value of the parameter field

MySQL Memo point (bottom)