day38 mysql &pymysql

 1 #! /usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 import  pymysql
 4 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
 5 cursor=conn.cursor()
 6 # exc=cursor.execute("update tb1 set user='88' ")
 7 # exc=cursor.execute("insert into tb1(user,date) VALUES ('89','2017-07-20')")
 8 # exc=cursor.execute("insert into tb1(user,date) VALUES (%s,%s)",('zj','2017-07-20'))
 9 # exc=cursor.executemany("insert into tb1(user,date) VALUES (%s,%s)",
10 #                        [('zj','2017-07-20'),('nn','2017-07-20')])
11 
12 # cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
13 # 把拿到的数据便成字典
14 # effect_row = cursor.execute("select * from tb1")
15 # conn.commit()
16 # ret=cursor.fetchall()
17 # print(ret)
18 # r=cursor.fetchone()
19 # print(r)
20 # cursor.fetchmany(2)
21 # 关闭游标
22 cursor.close()
23 # 关闭连接
24 conn.close()

View Code

连表

-- CREATE table person(
--     nid int not NULL auto_increment,
--     name VARCHAR(32) not NULL,
--     email VARCHAR(32) not NULL,
--     part1_nid int not NULL ,
--   PRIMARY KEY (nid),
--     CONSTRAINT fk_person_part1 FOREIGN KEY (part1_nid) REFERENCES part1(nid)
-- )
-- alter table person drop foreign key fk_person_part1 ;

-- 连表
SELECT 
    person.name,
    part.part
FROM
person
LEFT JOIN part on person.part1_nid=part.nid 
WHERE
part.part='jj'

View Code

A left join B on a.xx=b.xx

A表中所有数据罗列

B中只列A想对应的数据

A inner join B

自动忽略未建立关系的数据