Python development MySQL: Table relationship & Single table simple query

One by one-to-many, many-to-one

11.1establish a multi-pair, one-to-many relationship needs attention2 The associated table is created first, and the associated field must be guaranteed to be unique.3 when you create an associated table, the associated fields must be repeatable4 51.2example;6 Publishing House Many to one, multiple teachers may be in a publishing house7 polygamy one-to-many8 CREATE TABLE DEP (. The associated field must be guaranteed to be unique9 ID int primary key auto_increment,TenName varchar (20), OneComment varchar (50) A     ); -  - CREATE TABLE EMP ( the ID int primary key auto_increment, -Name varchar (20), - dep_id int, the associated field must be guaranteed to be repeatable. - constraint fk_depid_id foreign key (dep_id) references dep (ID) + foreign KEY (DEP_ID) This Table association field - references followed by the specified associated table, must be unique + On update Cascade A On Delete Cascade at);

Two to one

12.1example. user table, Administrator table2 CREATE TABLE User (3 UID int primary key auto_increment,4Name varchar (20)5     );6Insert into user (name) VALUES ('Egon');7 8 CREATE TABLE Admin (9 ID int primary key auto_increment,Ten user_id int unique, unique OnePassword varchar (20), A constraint foreign key (user_id) refreences User (UID) The associated field must be unique - On update Cascade - On Delete Cascade the     ); -Insert into admin (User_id,password) VALUES (3,'alex3714'); -  -2.2Note that the associated field must be unique to the associated field +  -2.3sample students and customers, convert customers to students +A student must be a client, but the client is not necessarily a student

Three many-to-many, two-way multi-to-one, will become many-to-many

13.1example. Author, book2 CREATE TABLE book (3 ID int primary key auto_increment,4Name varchar (20)5Price varchar (20)6     );7 8 CREATE TABLE Book2author (9 ID int primary key auto_increment,Ten book_id int, One author_id int, A constraint foreign KEY (book_id) references book (ID), - constraint foreign KEY (author_id) references author (id) - On update Cascade the On Delete CASCADE, - Unique (book_id,author_id) union only -     ); -  + CREATE TABLE author ( - ID int primary key auto_increment, +Name varchar (20) A);

Four simple single-table query

11Simple Query2SELECT * fromT1, find the table first, find the record, test it with3Select Name,id fromT1;4 52 Where Condition and> < = = betweenor inch  is  not6Select Name,id fromT1 where ID > 3first look for the table, in the go condition, then the field7Select Name,id fromT1 where ID > 3 andID <10. Multi-Criteria8Select Name,id fromT1 where ID between 3 and10; In.. Between notbetween9Select ID fromT1 where id=3orId=4orId=5;TenSelect ID fromT1 where IDinch(3,4,5); OneSelect ID fromT1 where ID isnull; You can tell if it is empty"'is not empty so simple, only null to use is to judge,"'with = =Judging ASelect Name fromT1 where name like'%n%'; -Select Name fromT1 where name like'E__n'; _ represents a match to a -  the3GROUP BY group -Select Stu_id,group_concat (name) fromT1 GROUP by stu_id; grouping by ID - Group_concat (name) The aggregation function is needed to see who is in the group -Select Stu_id,count (ID) fromT1 GROUP by stu_id; See how many people are inside each group +Select Stu_id,max (ID) fromT1 GROUP by stu_id; View the maximum ID in each group - Max min sum avg avg +  A Delete a field at ALTER TABLE T1 drop age; - ALTER TABLE T1 change ID ID (INT3); Can be replaced with modify - ALTER TABLE T1 add primary key (Id,age); Who to set as the primary key -ALTER TABLE T1 drop PRIMARY key; Delete primary key

Python development MySQL: Table relationship & Single table simple query