MySQL Basic Learning II

One, database operations

Primary key

The primary key is one of the indexes and is a unique index and must be defined as primary key. A table has only one primary key, and the primary key can consist of multiple columns.

The keyword that declares the primary key is: primary key

A simple example of declaring a primary key:

Create Table int Primary key) Engine=default CharSet=UTF8  primary key is the declared keyword.

FOREIGN key

If a field of one entity points to the primary key of another entity, it is called a foreign key.

The entity that is pointed to is called the primary entity (the primary table), also called the parent entity (the parent table).
The entity that is responsible for pointing, called from the entity (from the table), also called the Child Entity (child table).

Role: Used to constrain entities that are within a relationship. When adding a child table record, is there a corresponding parent table record, which cannot be inserted from the table if the primary table has no related records.

Foreign key Sample code:

#创建被关联的子表Create TableClass (CIDintAuto_incrementPrimary Key, ClnameChar( -)) engine=InnoDBdefaultCharSet=UTF8; #插入数据Insert  intoClass (Clname)Values("Third Grade"), ("First Grade"), ("Grade Four"), ("second year") ; #创建主表 (parent table)Create TableStudent (SIDintAuto_incrementPrimary Key, snameChar( A), class_idint,constraintFk_id_classForeign Key(class_id)ReferencesClass (CID)) engine=InnoDBdefaultCharSet=utf8;#constraintFk_id_classForeign Key(class_id)ReferencesClass (CID) keyword,ForeignThe key is followed by the parent table column name, reference and the child table column name. Insert  intoStudent (SNAME,CLASS_ID)Values("STU1"), ("Stu2"), ("Stu3");

FOREIGN key variants:

Both the foreign key and the primary key have unique indexes and cannot be duplicated. The difference is that the primary key cannot be empty, and the unique index of the foreign key can be empty.

Foreign key variants are divided into the following types of relationships:

One to many, one to one, many to many

1, one-to-one relationship:

                Create Tableuserinfo1 (IDintAuto_incrementPrimary Key, nameChar(Ten), GenderChar(Ten), emailvarchar( -) ) engine=InnoDBdefaultCharSet=UTF8; Create TableAdmin (IDint  not NULLAuto_incrementPrimary Key, usernamevarchar( -) not NULL, PasswordVARCHAR( -) not NULL,                    user_id int  not NULL,                    UniqueUQ_U1 (user_id), #关键字, specify a one-to-one relationship for the foreign keyCONSTRAINTFk_admin_u1FOREIGN Key(user_id)REFERENCESuserinfo1 (ID)) engine=InnoDBdefaultCharSet=UTF8;

2, one-to-many relationships

3, many-to-many relationships

Create TableUserinfo2 (IDintAuto_incrementPrimary Key, nameChar(Ten), GenderChar(Ten), emailvarchar( -) ) engine=InnoDBdefaultCharSet=UTF8; Create TableHost (IDintAuto_incrementPrimary Key, hostnameChar( -) ) engine=InnoDBdefaultCharSet=UTF8; Create Tableuser2host (IDintAuto_incrementPrimary Key, UserIDint  not NULL, HostIDint  not NULL,                    UniqueUq_user_host (Userid,hostid), #关键字, specified as a many-to-many relationship statementCONSTRAINTFk_u2h_userFOREIGN Key(userid)REFERENCESUserinfo2 (ID),CONSTRAINTFk_u2h_hostFOREIGN Key(HostID)REFERENCEShost (ID)) engine=InnoDBdefaultCharSet=UTF8;

Operation of the data table:

Insert, modify, select, delete data:

　　　

#一次插入多条数据的方式:Insert  intoStudent (SNAME,CLASS_ID)Values(" stu1"), ("Stu2"), ("Stu3"); #修改表内数据UpdateStudentSetSname="Stu10" #操作显示表数据SelectSid,sname fromstudent; #显示学生表的id与学生名字SelectSid,sname fromStudentwhereSid>3the student ID and name of the table with a SID greater than 3 # Delete table dataDelete  fromStudentwhereId=1; #删除表中id为1的数据

Where Condition statement

Where: A conditional constraint statement that requires the use of a where statement to conditionally filter data in a data operation. In use, Xu Note:

An aggregate function condition is not allowed after a where function condition

Syntax: where conditional expression

Operators for conditional expressions

Keyword like for fuzzy matching

Fuzzy Match Supplement _ underline refers to an arbitrary value after the character, example: where username like "A_"

Self-increment data operations

In the data table will often set the self-increment data, the self-increment data can be modified to view the operation. Since MySQL's stride size is session-based, each modification is for each session instead of the global, and if you need to modify the global

View:

Show Session Variables like 'auto_inc%';+--------------------------+-------+|Variable_name|Value|+--------------------------+-------+|Auto_increment_increment| 1     ||Auto_increment_offset| 1     |+--------------------------+-------+2Rowsinch Set,1Warning (0.00Sec

To modify the self-increment step:

 SetSession Auto_increment_increment=2; Query OK,0Rows Affected (0.00sec) Show Session variables like 'auto_inc%';+--------------------------+-------+|Variable_name|Value|+--------------------------+-------+|Auto_increment_increment| 2     ||Auto_increment_offset| 1     |+--------------------------+-------+2Rowsinch Set,1Warning (0.00Sec

Global View:

Global view is based on the keyword global.

Set global Step value: Set Globalsauto_increment_increment=2;

Show global variables like 'auto_inc%';+--------------------------+-------+|Variable_name|Value|+--------------------------+-------+|Auto_increment_increment| 1     ||Auto_increment_offset| 1     |+--------------------------+-------+2Rowsinch Set,1Warning (0.00Sec

MySQL Basic Learning II