Basic operation of MySQL database via SQL statement

First, the basic operation of the database

1 CREATE DATABASE   Mybookstore; 2      DROP DATABASE  Mybookstore; 　　　

second, the basic operation of the table

　　1. Create a table

　　　　　Insert into table name (field name 1, field name 2) VALUES (value 1, value 2);

1 CREATE TABLE Student (2          INT , 3          VARCHAR (+) 4     );

　　2. Delete a table

Delete from table name where statement;

DROP TABLE student;

　3, modify the table

Update table name SET field Name 1 = field value, field name 2 = field value WHERE statement

Update set dept_name=' Personnel Department 'where id=4

　　4. Enquiry Form

　　　　　　Select Field Name 1, field name 2 from table name where statement

SELECT *  from WHERE id=3;

Third, table query key words

1. Keyword usage and SQL statement order

Select Column Name 1, column Name 2 ...

From table 1

Join Table 2

On connection condition

where filter conditions

GROUP BY grouping condition 1, grouping condition 2

Having aggregation function filtering

Order by sort column 1, row sequence 2

Limit paging

2, execution order: From->on->join->where->group By->with->having->select->distinct->order by-> Limit

Iv. constraints

1, primary key [primary key]: can be unique to represent a row of data, you can quickly retrieve a piece of data! ( unique, non-null, can be referenced )

2. Self-increment: auto_increment: Self-increment only applies to values of numeric type!

3, non-empty: NOT NULL: Indicates that the current field value cannot be null

4, Unique: Unique: Indicates that the current field value must be unique

5, note: The constraint must be placed after the column name type, as to the order of multiple constraints do not matter!

6, FOREIGN key:

A foreign key association refers to associating a column in one table with the primary key of another table!　　　　

1) One-to-one
Classic case: Couple

1 /*One to one : Couples*/2 CREATE TABLEHusband (3IdINT PRIMARY KEYAuto_increment,4' Name 'VARCHAR( -) not NULL5 );6 7 INSERT  intoHusbandVALUES(1,'Zhang San');8 INSERT  intoHusbandVALUES(2,'Harry');9 Ten CREATE TABLEWife ( OneIdINT PRIMARY KEYAuto_increment, A' Name 'VARCHAR( -) not NULL, -     FOREIGN KEY(ID)REFERENCESHusband (ID) - ); the INSERT  intoWifeVALUES(1,'John Doe'); - INSERT  intoWifeVALUES(2,'Zhao Liu'); - INSERT  intoWifeVALUES(1,'Li Jing');/*This row insertion failed due to one-to-one constraints*/

2) One-to-many [many-to-one]

　　　　　　　It is usually the primary key that is associated with one end of the many! The foreign key is usually built on one end of the many!

Classic Case: Departmental table and employee table dept& EMP

1 /*One-to- many: employees-departments*/2 DROP TABLEDept;3 CREATE TABLEDept (4IdINT PRIMARY KEYAuto_increment,5Dept_nameVARCHAR(Ten) not NULL UNIQUE6 );7 INSERT  intoDeptVALUES(NULL,'Development Department');8 9 DROP TABLEEMP;Ten CREATE TABLEEMP ( OneIdINT PRIMARY KEYAuto_increment, A' Name 'VARCHAR( -) not NULL UNIQUE, -dept_idINT , -     FOREIGN KEY(dept_id)REFERENCESDept (ID) the ); - INSERT  intoEmpVALUES(NULL,'Zhang San',1); - INSERT  intoEmpVALUES(NULL,'John Doe',1); - INSERT  intoEmpVALUES(NULL,'Harry',1);

3) Many-to-many

Classic Case: Teacher students

1 /*Many-to-many: teachers-students*/2  CREATE TABLETeacher (3IdINT PRIMARY KEYAuto_increment,4' Name 'VARCHAR( -) not NULL5  );6 INSERT  intoTeacherVALUES(NULL,'teacher Li');7 INSERT  intoTeacherVALUES(NULL,'Mr. Liu');8 INSERT  intoTeacherVALUES(NULL,'Miss Wang');9  Ten  CREATE TABLEStudent ( OneIdINT PRIMARY KEYAuto_increment, A' Name 'VARCHAR( -) not NULL -  ); - INSERT  intoStudentVALUES(NULL,'Shantao'); the INSERT  intoStudentVALUES(NULL,'Yang Tao'); - INSERT  intoStudentVALUES(NULL,'Sheng'); -   -  CREATE TABLETea_stu ( +tea_idINT, -stu_idINT, +     FOREIGN KEY(tea_id)REFERENCESTeacher (ID), A     FOREIGN KEY(stu_id)REFERENCESStudent (ID) at  ); - INSERT  intoTea_stuVALUES(1,1); - INSERT  intoTea_stuVALUES(1,2); - INSERT  intoTea_stuVALUES(1,3); - INSERT  intoTea_stuVALUES(2,1); - INSERT  intoTea_stuVALUES(2,1);

Basic operation of MySQL database via SQL statement