MySQL Basic exercise/down

MySQL Base exercise

I. Self-replenishment

Desc (table name) T1;  View table Information content table information Show create TABLE T1 (table name): Also view information, not much is landscape view show  create table T1 \g; vertical view self-increment information alter TABLE T1 auto_increment=3; You can modify the self-increment

MySQL: Self-increment step  Based on session level:

Show session variables like "auto_inc%; View global variables set session auto_increment_increment=2; Sets the drawing step  set global auto_increment_offset=10; Indicates the increment of the self-growth field by a value of 1;

Based on the global level:

Show global variables like ' auto_inc% ';    View global variable set globals auto_increment_increment=2;     Set session Step # set global auto_increment_offset=10;

Supplemental PRIMARY key: A table has only one primary key, but the primary key can be composed of multiple columns; 　　

CREATE TABLE ' T5 ' (                  ' nid ' Int (one) not null auto_increment,                  ' pid ' int (one) ' NOT NULL,                  ' Num ' int (one) DEFAULT NULL,                  PRIMARY KEY (' nid ', ' pid ')                 ENGINE=innodb auto_increment=4, step =2 Default charset= =utf8  //Set step size and auto increase

Two Unique index

Unique index: The value of the indexed column must be unique, but a null value is allowed. If it is a composite index, the combination of column values must be unique.

PRIMARY KEY index: null values are not allowed. The primary key index is typically created at the same time as the table.

Unique unique index name (column name, column name)//Federated index, unique UQ_U1 (user_id),//Unique index　

CREATE TABLE t1 (            ID int ....,            num int,            xx int,            // Union index            constraint ...        )

Three variants of foreign keys a : User tables and Departmental tables (one-to-many forms)

User:                 1 Alex     1                2 root       1                3 Egon       2                4 Laoyao   3                             Department:                1 service                2 Security                 3 pr             = = = "one to many

View Code b : User table and Blog table (one-to-one form)

User table:                 1 Alex                    2 root                       3 Egon                       4 Laoyao               Blog table:                                  + unique                1   /yuanchenqi/   4                2    /alex3714/    1                3    /asdfasdf/    3                4    / ffffffff/    2 ===> One-on-one                            

View Code

CREATE TABLE userinfo1 (ID int auto_increment primary key, name char (10), Gender char (10), email varchar (64) ) engine=innodb default charset=UTF8; CREATE TABLE admin (id int notNULL Auto_increment PRIMARY KEY, username varchar (64) notnull, password VARCHAR (64) notnull, user_id int notnull, unique UQ_U1 (user_id),//Unique index CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1 (ID)) engine
    =innodb default Charset=utf8;

View Code C: User table (Lily net) Blind Date record table

Example 1: User Table Matchmaking Example 2: User table host Table User Host Relationship Table==="multi-to-many CREATE table Userinfo2 (ID int auto_increment primary key, name char ( /c6>10), Gender char (10), email varchar (64) ) engine=innodb default charset=UTF8; CREATE TABLE host (ID int auto_increment primary KEY, hostname char (64) ) engine=innodb default charset=UTF8; CREATE TABLE user2host (ID int auto_increment PRIMARY key, UserID int notnull, HostID int notnull, unique Uq_user_host (Userid,hostid),//Federated Unique CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES Userinfo2 (ID), CONST Raint fk_u2h_host FOREIGN Key (HostID) REFERENCES host (ID)) engine=innodb default Charset=utf8;

View Code

Four , SQL statement data row operation SupplementCreate:

CREATE TABLE TB12 (                ID int auto_increment primary key,                name varchar (+), age                int            ) engine =innodb default Charset=utf8;

Increase:

Insert into TB11 (name,age) VALUES ('Alex', ' n ');  single-line insert into  tb11 (name,age) VALUES ('Alex', ' V '), ('root  ', 18); multiple lines are  inserted from TB11;//the contents of tb11 inside table are inserted into tb12; 

By deleting:

   from-tb12 where ID!=2 from TB12 where ID =2 from TB12 where ID > 2 from tb12 where ID >=2fromor name='  Alex'

Change:

Update tb12 set name='Alex' and name='xx'   Update TB12 set name='Alex' and name='xx' 

Check:

    from from or name = ' xxx '  from or name ='xxx';  as can modify the sequence name; Select Name,age, from TB12;  View a different table

Other:

A: Condition select* fromTB12 WHERE id! = 1Select* fromTB12 where IDinch(1,5,12); View table ID is 1,5, 12 of the Select* fromTB12 where ID not inch(1,5,12);//view table ID is not 1,5, 12 of the Select* fromTB12 where IDinch(SELECT ID fromtb11) Select* fromTB12 where ID between 5 and12; //the B: wildcard for the range of closed bays: Select* fromTB12 where name like"a%"A all that starts with a (%multiple strings) Select* fromTB12 where name like"A_"//A (-one character) C: Limit (paging): Select* fromTB12 limit 10; //first 10 rows Select* fromTB12 limit 0, 10; //10 rows starting from 1 rows Select* fromTB12 limit 10, 10; //10 rows starting from 10 rows Select* fromTB12 limit offset 20; //20 rows starting from line 10th D: Sort Select* fromtb12 ORDER BY id desc; Big to small select* fromTB12 ORDER by ID ASC; Small to large Select* fromtb12 ORDER by age Desc,id desc; Priority first, if the data is the same at the beginning of the second starting from the size of the 10 data (sorted first in the Fetch data) Select* fromTB12 ORDER BY id DESC limit 10; e: grouping:****select count (ID), part_id fromuserinfo5 GROUP BY Part_id;countmaxminsumavgIf you are filtering on the results of an aggregate function for a second time, you must use the having*****select count (ID), part_id fromUSERINFO5 GROUP BY PART_ID have count (id) > 1; select count (ID), part_id fromUserinfo5 where ID > 0 GROUP by PART_ID have count (id) > 1; Special: Group by must be in the where, before order by F: Even table:********Select* fromUserinfo5 left join department5 on userinfo5.part_id =Department5.idselect* fromDepartment5 left join Userinfo5 on userinfo5.part_id =department5.id//Usernfo5 left all displayed//Department5 left all displayed#SELECT * from Userinfo5 right join department5 on userinfo5.part_id = Department5.id#DEPARTMENT5 to the right of all displayuserinfo5 Table All displays, if there is no correspondence in DEPARTMENT5, the value is NULL select* fromUserinfo5 Innder Join department5 on userinfo5.part_id =department5.id will appear null when a row is hidden

other

MySQL Basic exercise/down