MySQL (ii)

A one-to-many foreign key for MySQL even table operation

To complete the following situation, a of partment in the 1 is the research and development department, 2 belongs to the development department, let the two tables generate contact

nid   name     email         partment1     aa       [email protected]     12     BB       [Email protected]      1 3     VV       [email protected]      14     dd       [email protected]     2

Create a Table a

Nid      caption1         Development Dept. 2       Dev Dept.

another table B

A in the partment of 1 belong to the research and development department, 2 belongs to the development department. A and B are an association. A is binding on B, that is, if you add a data that is not in a, the foreign key in a is the primary key in B.

The database table pair has the following characteristics:

The foreign key is the primary key of the other table, and note that the foreign key and the primary key must be the same type that you think creates the association and has constraints

View CodeSecond, navicat create foreign key

Create a foreign key

1. Create two forms first

CREATE table B as content and let nid increment

Create second Table A note that the primary key type here is the same as the type of the foreign key of the previous one

2, create a foreign key in B, design the table, notice that the front column is the foreign key to be set in B, the reference field after the key is a

3, first fill in a primary key and content, and then in B to fill in the foreign key

Fill in the content in B

This completes the creation of the foreign key

Third, create a foreign key by command

int NULL auto_increment primary key,caption varchar (null  );

CREATE TABLE One

int NULL , name varchar (null, email varchar (+nullint  null, PRIMARY key (NID), CONSTRAINT fk_person1_part1 FOREIGN KEY (Part1_nid) REFERENCES part1 (NID) );

CREATE table Two, and create a foreign key

CONSTRAINT fk_person1_part1 FOREIGN Key (Part1_nid) REFERENCES part1 (NID) Fk_person1_part1: This is the custom name FOREIGN KEY (part1_ NID): Set key foreign key references Part1 (NID): Sets the primary key for associating another table

NoteIv. methods

ALTER TABLE from TABLE ADD constraint foreign key name (shape: fk_ from Table _ Main Table) foreign key from table (foreign key field) references Main Table (primary key field); Delete foreign key: ALTER TABLE name drop foreign Key foreign key name such as: ALTER TABLE person1 drop foreign key fk_person1_part1;  Deleted created foreign key ALTER TABLE Person1 ADD constraint fk_person1_part1 foreign key (Part1_nid) references part1 (NID); foreign Key created

1. Add foreign keys

question: How do I get the CEO owner's name in part? Thought: List the names in B, and the positions to be found in a, in the combination of B and a, let the foreign key in B and the primary key in a be associated with the positionin a, control mysql >     select  b.name,a.caption   from B left join A on b.partment=a.nidwherea.caption="CEO " ; +------+---------+| name | Caption |+------+---------+| AA   | CEO     | | bb   | CEO     |+------+---------+2inset (0.01 sec)

2. Even tableFive, even table:

1, even a single table

A (foreign key) left JOIN B (primary key) on a.xx=b.xx is dominated by a, listing all the data in a for B is shown in a corresponding data B (primary key)) left join a (foreign key) on a.xx=  B.xx this is the opposite of the above a (foreign key) right join  B (primary key) on a.xx=b.xx to B, with a As a supplement, the data in B is listed in a INNER join B on a.xx=  B.XX automatically ignores non-relational data

2. Connect multiple tables

If the foreign key has data in this table, then you want to set up a linked list, connect a table of primary key, this time when the external key to create a table when the outer key to allow this value is empty,
1452 : Cannot add or update a child row:a FOREIGN KEY constraint fails

elect Persion.nid asPID, Persion.name asPName, Part.caption asCP, Corlor.title astitlefrom persionleft JOIN part on Persion.part_nid=part.nidleft JOIN corlor on persion.color_id=Corlor.nidwhere part.caption="CEO"and corlor.title="Red"(analytic: The above shows the PID pname CP title from (Persion and part of the Persion.part_nidData for =part.nid and Persion.color_id=color.nid in persion and color)

View Code

Vi. multiple-to-many operation of the table

  Many-to-many ideas: create a third table. The foreign key in the third table corresponds to the primary key A of the first two tables:  1   AA  2   BB  3   DdB  1   a  Span style= "COLOR: #800080" >2   b  3  Span style= "COLOR: #000000" > c how can you select multiple between A and B? The ID of the CA ID b  1  2  1  1  2  2  2  1   above is the 1 in a corresponding to 1 in B and the 2  

View Code

Insert data into these three tables -insert INTO man (name) VALUES ("xxoo")– INSERT INTO woman ( Name) VALUES ("xxoo") INSERT into Man_to_woman (man_id,woman_id) VALUES (1 ,2) Create a man table here

Below woman table

Third sheet

Requirements: Find the contents of B corresponding to AA

SELECT * from Man_to_woman left JOIN man on Man_to_woman.man_id=man.nidleft JOIN woman on MAN_TO_WOMAN.WOMAN_ID=WOMAN.NIDW Here Man.name= "a"

Vii. SQL injection

if the string in the code below is spliced, enter the following pyrene '    –a 1=1  –d    A will be logged in successfully the reason is because the background string concatenation, this is the problem in all languages will appear

Injection Principle

Pymysql inside has actually done a part of processing directly with Cursor.execute ("selectfromwhere name= '%s ' and password = '%s ' ", (USERNAME,PWD)), which prevents SQL injection because Pysql does the same thing by removing (') quotes and the following special characters:select from where name='ad \'  or 1=1--a' and password=' a "'

pymysql Anti-SQL injection principle

Here is the Pymysql login registration code

<! DOCTYPE html>"en">"UTF-8"> <title></title>"/index"Method="Post"> <input type="text"Name="username"Placeholder="User name"/> <input type="text"Name="Password"Placeholder="Password"/> <input type="Submit"/> </form></body>Front-end code
#/usr/bin/env pythonimport tornado.ioloopimport tornado.webimport pymysqlclassLoginhandler (Tornado.web.RequestHandler): DefGet(self): Self.render ("index.html") def post (self,*args,**Kwargs): Username=self.get_argument ("username") PWD=self.get_argument ("Password") Conn= Pymysql.connect (host="127.0.0.1", port=3306, user="Root", password="123456", db="DB1") Cursor=conn.cursor () # temp="select name from UserInfo where Name= '%s ' and password= '%s '"%(username,pwd) # #做了字符串拼接 # print (temp) # Effect_row=Cursor.execute (temp) #上面是因为字符串拼接造成了能够sql注入 Effect_row=cursor.execute ("select name from UserInfo where Name= '%s ' and password= '%s '", (username,pwd)) #如果匹配下面就登录成功 result=Cursor.fetchone () conn.commit () Cursor.close () Conn.close ( )ifResult:self.write ("Login Successful")        Else: Self.write ("Logon Failure") Settings= {    'Template_path':' views',    'Static_path':'Static',}# The first parameter below is the parameter mapping in HTML Application=Tornado.web.Application ([(R"/index", Loginhandler),],**settings)if__name__ = ="__main__": Application.listen (8000) tornado.ioloop.IOLoop.instance (). Start ()
Background code:
MySQL (ii)