mysql(三)

1、创建表

 1 # 创建表student：
 2 # 主键
 3 # 主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一
 4 student ='''
 5     create table student(
 6         stdid int primary key not null,
 7         stdname varchar(100) not null,
 8         gender enum('F','M'),
 9         age int
10     ):
11 '''
12 tmp='''
13     set @a=0
14     create table tmp as select (@a :=@a+1)as id from information_schema.tables limit 10:
15 
16 '''
17 
18 if __name__=="__main__":
19     cnx = connect_mysql()
20     print(cnx)
21     print(dir(cnx))
22     cus=cnx.cursor()
23     try:
24         cus.execute(student)
25         cus.execute(tmp)
26         cus.close()
27         cnx.commit()
28     except Exception as e:
29         cnx.rollback()
30         raise e
31     finally:
32         cnx.close()

2、增加数据

 1 #增加数据
 2 # insert into 表 (列名,列名...) values (值,值,值...)
 3 # insert into 表 (列名,列名...) select (列名,列名...) from 表
 4 if __name__ == '__main__':
 5   cnx = connect_mysql()
 6   students = '''set @i := 10000;
 7             insert into Student select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 3 + floor(rand() * 75)), case floor(rand()*10) mod 2 when 1 then 'M' else 'F' end, 25-floor(rand() * 5)  from tmp a, tmp b, tmp c, tmp d;
 8         '''
 9 try:
10     cus_students = cnx.cursor()
11     cus_students.execute(students)
12     cus_students.close()
13     cnx.commit()
14 except Exception as e:
15     cnx.rollback()
16     raise e
17 finally:
18     cnx.close()

3、查询

 1 #查询数据
 2 #select * from 表
 3 # 条件查询
 4 # select * form 表 where 条件
 5 # 通配符查询
 6 # select * from 表 where name like 'a%'以a开头的若个字符
 7 # 限制查询
 8 # select * from 表 limit 5;仟伍行
 9 # select * from 表 order by 列 asc - 根据某一列从小到大排序（从大到小 desc -)
10 # 分组
11 # select num,id from 表 group by num,id
12 if __name__ == '__main__':
13     cnx = connect_mysql()
14 
15     sql = '''select * from Student where StdName in (select StdName from Student group by StdName having count(1)>1 ) order by StdName;'''
16     #把所有名字重复的学生列出来
17     try:
18         cus = cnx.cursor()
19         cus.execute(sql)
20         result = cus.fetchall()
21         # 取出内容
22         #写入到select。txt中
23         with codecs.open('select.txt', 'w+') as f:
24             for line in result:
25                 f.write(str(line))
26 
27                 f.write('\n')
28         cus.close()
29         cnx.commit()
30     except Exception as e:
31         cnx.rollback()
32         print('error')
33         raise e
34     finally:
35         cnx.close()

 

posted @ 2017-11-23 10:26  依哈  阅读(110)  评论(0)  编辑  收藏  举报