python第十六次笔记 2018.5.8

 import pymysql


#创建连接
conn = pymsql.connect(host="192.168.1.123",port="3306",user="root",passwd="3328998ccj",db="test")
#创建游标    游标是处理数据的一种方法
cus = conn.cursor()
#定义sql
sql = "select * from table"


#执行
#cus.execute(sql)
#取所有结果,取结果之前,一定要先执行sql
#cus.fetchall()
#取一个结果
#cus.fetchone()
#取10行数据
#cus.fetchmany(size=10)
#关闭游标
#cus.close
try:
    cus.execute(sql)
    result = cus.fetchall()
    print(result)
except Exception as e:
    raise e
finally:
    cus.close()
    conn.close()






create table tablename(
    列名 数据类型 not null
    ......
);
学号 年龄 姓名 分数 性别


create table studenttable(
stdid int not null,
stdname varchar(100),
age int,
sex enum('M','F'),
score int);


#区别
'123'       varchar(10)
'123       '   char(10)


select 列名 from 表名 where 条件判断


select * from student where id>1000


select * from student  group by stdname;


select * from student  group by name having count(1)>1;


select * from student  group by name having count(1)>1 order by id desc;


select * from a,c where a.id = c.组id


select a.id a.name from a join c on c.组id = a.id


select id,name  禁止用*


slsect * from student where id in (10,20,30)  #in效率更高


insert into tablename (id,name,age) value (1,jack,20)




show create tablename  查看创建表结构


desc tablename




truncate 只清空数据,不清空表结构
drop   全删掉


update tablename set 列名=xxx where 条件判断


create index 库名_表名_列名1_列名2 (列名1 ,列名2)


alter table test2 add index


explain select * from test2 where name = 'aaa'; #查看有没有索引