Python path-----mysql Operation II

I. PRIMARY KEY1. There is only one primary key for each table 2. Each primary key can consist of multiple columns. (If the primary key consists of multiple components, as long as there is a row value not equal)

CREATE TABLE NAME (    ID INT auto_increment,    name CHAR (),    Sid int,    PRIMARY KEY (ID, sid)); Into the data (1,'a', 1), the primary key value is: (max)  , insert (1,'b', 2), the primary key is Two primary key values are not exactly equal

PRIMARY Key Example two. Unique indexA unique index is also a constraint with the following constraints: 1. Can be empty, but cannot repeat 2. Speed up your query

CREATE TABLE name1 (    ID INT auto_increment,    name CHAR,    UNIQUE qu_name (name),    INDEX ( ID))

Unique Index ExampleThe value of a unique index can be multiple columns, known as the difference between a federated unique index and a primary key: 1. The primary key is not allowed to be empty, and the unique index can be empty 2. The primary key can be called outside the table, and a unique index can only be used within a table

three. Self-increment

1. Modify the self-increment column start value 1.1. View table Data DESC Hostinfo; 1.2. View commands when creating a table: Show CREATE TABLE tablename Show CREATE TABLE tablename \g display data vertically

CREATE TABLE ' More2 ' (' id ' int (one) not null auto_increment, ' Pc_name ' char () DEFAULT NULL, ' owner_id ' int (11
      
       ) DEFAULT Null,primary key (' id '), UNIQUE key ' Uq_more2 ' (' owner_id '), CONSTRAINT ' One2_more2 ' FOREIGN KEY (' owner_id ') REF Erences ' One2 ' (' id ')) engine=innodb auto_increment=6 DEFAULT Charset=utf8 
       

1.3. Modify the Auto_increment value of the table to make changes to the self-increment initial value. ALTER TABLE TableName auto_increment=9;　　This time, starting from 9, the newly inserted data self-increment column value is 9.　　2. Change the step size of MySQL and other database software, the step size of the database is table-level, you can set the step size for the table, and the step of MySQL is modified to the session level, the step of a connection modification is only valid for this connection, if you create a new connection, and the step is still 1.     2.1 Viewing step show session variables like '%auto_inc% '; #查看session变量

+--------------------------+-------+
| variable_name | Value |
+--------------------------+-------+
| auto_increment_increment |   1 | #步长
| Auto_increment_offset |       1 | #数量默认起始值
+--------------------------+-------+

2.2 Modify session level self-increment variable value set session auto_increment_increment=3;           #设置步长为3 set session auto_increment_offset=5; #设置自增列默认起始值为5 2.3 View the modified values

+--------------------------+-------+
| variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 3 |
| Auto_increment_offset | 5 |
+--------------------------+-------+

Create a new connection to see the self-increment start value and step size

+--------------------------+-------+
| variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| Auto_increment_offset | 1 |
+--------------------------+-------+

We send, by modifying the session variable, only this connection is valid, if you want to make the modified value permanently valid, you need to modify the global variable

2.4 View global auto-increment correlation variable values

Show global variables like '%auto_inc% ';

+--------------------------+-------+
| variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| Auto_increment_offset | 1 |
+--------------------------+-------+

2.5 Modifying the global variable

Set global auto_increment_increment=2; #修改自增步长

Set global auto_increment_offset=2; #修改自增列起始值

2.6 New Connection View, the configuration is still in effect. However, it is not recommended to use this method for modification

four. Foreign keyAdd: When the primary key is multiple, the foreign key can be associated with more than one

CREATE TABLE Morekey (    ID int auto_increment,    sid int,    name  char,    PRIMARY KEY (ID , sid)); CREATE table Morekey1 (    id1 int,    sid1  int,    name1 CHAR),    CONSTRAINT Fk_moremore FOREIGN KEY (ID1,SID1) REFERENCES Morekey (id,sid))

associating multiple foreign keys1. One-to-many when we establish a constraint relationship by a foreign key on two tables, the value of table a corresponds to multiple values for table B, and the value of B table corresponds to only a value of table A, which is a pair. For example, Department tables and employee tables. One employee corresponds to only one department, while one department can correspond to multiple employees.

CREATE TABLE Morekey (    ID int auto_increment,    sid int,    name  char,    PRIMARY KEY (ID , sid)); CREATE table Morekey1 (    id1 int,    sid1  int,    name1 CHAR),    CONSTRAINT Fk_moremore FOREIGN KEY (ID1,SID1) REFERENCES Morekey (id,sid))

One -to-many examples2. One-to-one when the value of table B is constrained by the a table, and the value of a table can only be used once for table B, the values of table A and table B are single. For example, company asset registration, a PC can only be used by one employee.　　　　or password login management, the user name is not allowed to repeat. We know that the purpose of a unique index is to constrain the values in the table so that they are unique within the table and do not allow duplication. We can do a one-to-one table design with foreign keys and unique indexes

-- PC number CREATE TABLE hostinfo (    ID int auto_increment PRIMARY KEY,    host_name CHAR); -- User asset information Create TABLE employ (    ID int auto_increment PRIMARY KEY,    name Char,    pc_id int,    UNIQUE Uq_hostinfo_employ (pc_id),    CONSTRAINT fk_hostinfo_employ FOREIGN KEY (pc_id) REFERENCES hostinfo (id));

One-to-one example3. Many-to-many when the value of a and table B correspond to more than one another, that is, there are many-to-many relationships between the two tables.　　　　For example, the operator to the company's server host management, a person can log on more than one server, a server can also log on multiple people. We need to use the third table to record many-to-many relationships between A and B tables.

-- Host Information table CREATE table pcinfo (    ID int auto_increment PRIMARY KEY,    host_name char); -- Administrator Information table CREATE table admininfo (    ID int auto_increment PRIMARY KEY,    name Char); -- Administrator host table create TABLE ADMIN_PC (    ID int auto_increment PRIMARY KEY,    pc_id int,    user_id int,    UNIQUE uq_user_pc (pc_id,user_id),    CONSTRAINT fk_admin_pc FOREIGN KEY (pc_id) REFERENCES pcinfo (ID),    CONSTRAINT fk_admin_user FOREIGN KEY (user_id) REFERENCES admininfo (id));

Many-to-many examples

Five. Operation Table content Advanced Application Supplement

　　1. Cartesian product

The Cartesian product is a concept in relational algebra that represents any combination of rows of data in two tables. For example, there are three data in table A, and three data in table B, the command is used:

SELECT * from a A, produces nine results. Each value in table A will produce a corresponding value for each value in table B. The diagram is as follows:

　　　　　　　　　　　　

　2. Temporary table, the filtered data is temporarily used as a table

* FROM (SELECT SID from Score) as-B left JOIN student on student.sid=b.sid; The data filtered from the score table and the student table are connected to the table operation

3.insert Inserting filtered table content

class (caption) SELECT name from Class2;ps: It is important to note that the columns you insert are consistent with the columns of the table

4. Conditional statements

Sentence pattern: case when condition then condition is positive return esle condition is false return end

else 0 end;   # returns 1 if the ID is less than 10, otherwise returns 0

5. Use Constants to Count

 from tablename Groub by ID;

6. Inserting dynamic values

SELECT                         student_id,                        from and course_id = 1) as language,                        from   and course_id = 2) as mathematics,                        from andcourse_id = 3) as English                       from score as S1;

The value of the S1 table can then be used within the loop. Similar to:

 for inch S1:      for inch S2:        s2=s1

7. Ternary operation

if (condition, for true return, for false return)

E.g:if (IsNull (XX), 0, 1) If NULL, returns 0, otherwise 1

　　

Python path-----mysql Operation II