day38 mysql &pymysql

#! /usr/bin/env python
# -*- coding:utf-8 -*-
import  pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor=conn.cursor()
# exc=cursor.execute("update tb1 set user='88' ")
# exc=cursor.execute("insert into tb1(user,date) VALUES ('89','2017-07-20')")
# exc=cursor.execute("insert into tb1(user,date) VALUES (%s,%s)",('zj','2017-07-20'))
# exc=cursor.executemany("insert into tb1(user,date) VALUES (%s,%s)",
#                        [('zj','2017-07-20'),('nn','2017-07-20')])

# cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
# 把拿到的数据便成字典
# effect_row = cursor.execute("select * from tb1")
# conn.commit()
# ret=cursor.fetchall()
# print(ret)
# r=cursor.fetchone()
# print(r)
# cursor.fetchmany(2)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

连表

-- 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'

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

A表中所有数据罗列

B中只列A想对应的数据

A inner join B

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