python学习—Day28—创建表、增加查询数据

创建表：

#@File :mysql_one.pyimport MySQLdb# conn=MySQLdb.connect(host="192.168.172.131",user="xiao",passwd="123456",db="python",charset="utf8",post=3306)def connect_mysql():    db_config = {        'host' : '192.168.172.131',        'port' : 3306,        'user' : 'xiao',        'passwd' : '123456',        'db' : 'python',        'charset' : 'utf8'    }    try:        cnx = MySQLdb.connect(**db_config)    except Exception as e:        raise e    return cnxstudent = '''    create table student(        stdid int primary key not null,        stdname varchar(100) not null,        gender enum('F', 'M',        age int    );'''course = '''    create table course(        couid int primary key not null,        cname varchar(100) not null,        tid int not null    );'''score = '''    create table score(        sid int primary key not null,        std int not null,        couid int not null,        grade int not null    );'''teacher = '''    create table teacher(        tid int primary key not null,        tname varchar(100) not null    );'''tmp = '''    set @a = 0;    create tbale tmp as select (@a := @a +1) as id from informatiom_schema.tables limit 10;'''if __name__ == "__main__":    cnx = connect_mysql()    print(cnx)    print(dir(cnx))    cus = cnx.cursor()    try:        cus.execute(student)        cus.execute(course)        cus.execute(score)        cus.execute(teacher)        cus.execute(tmp)        cus.close()        cnx.commit()    except Exception as e:        cnx.rollback()        print('Error')        # raise e    finally:        cnx.close()

这样书写并没有输出，对于数据库 并没有修改，但是资料样例是可以正常执行的，如下。暂时不知道问题在哪里。

#@File :mysql_mode.pyimport MySQLdbdef connect_mysql():    db_config = {        'host': '192.168.172.131',        'port': 3306,        'user': 'xiao',        'passwd': '123456',        'db': 'python',        'charset': 'utf8'    }    cnx = MySQLdb.connect(**db_config)    return cnxif __name__ == '__main__':    cnx = connect_mysql()    cus = cnx.cursor()    # sql  = '''insert into student(id, name, age, gender, score) values ('1001', 'ling', 29, 'M', 88), ('1002', 'ajing', 29, 'M', 90), ('1003', 'xiang', 33, 'M', 87);'''    student = '''create table Student(            StdID int not null,            StdName varchar(100) not null,            Gender enum('M', 'F'),            Age tinyint    )'''    course = '''create table Course(            CouID int not null,            CName varchar(50) not null,            TID int not null    )'''    score = '''create table Score(                SID int not null,                StdID int not null,                CID int not null,                Grade int not null        )'''    teacher = '''create table Teacher(                    TID int not null,                    TName varchar(100) not null            )'''    tmp = '''set @i := 0;            create table tmp as select (@i := @i + 1) as id from information_schema.tables limit 10;        '''    try:        cus.execute(student)        cus.execute(course)        cus.execute(score)        cus.execute(teacher)        cus.execute(tmp)        cus.close()        cnx.commit()    except Exception as e:        cnx.rollback()        print('error')        raise e    finally:        cnx.close()

最终结果查看是进入到数据库中，使用show与select来验证的。

use python;

show tables;

select * from tmp;

如此查看到结果。

补充：

了解一下information_schema这个库，这个在mysql安装时就有了，提供了访问数据库元数据的方式。那什么是元数据库呢？元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。

information_schema数据库表说明:

SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是showdatabases的结果取之此表。

TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。

COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。

STATISTICS表：提供了关于表索引的信息。是showindex from schemaname.tablename的结果取之此表。

USER_PRIVILEGES（用户权限）表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。

SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。

TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。

COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。

CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOWCHARACTER SET结果集取之此表。

COLLATIONS表：提供了关于各字符集的对照信息。

COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。

TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。

KEY_COLUMN_USAGE表：描述了具有约束的键列。

ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。

VIEWS表：给出了关于数据库中的视图的信息。需要有showviews权限，否则无法查看视图信息。

TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表

而TABLES在安装好mysql的时候，一定是有数据的，因为在初始化mysql的时候，就需要创建系统表，该表一定有数据。

set @i := 0;
create table tmp as select (@i := @i + 1) as id from information_schema.tables limit 10;

mysql中变量不用事前申明，在用的时候直接用“@变量名”使用就可以了。set这个是mysql中设置变量的特殊用法，当@i需要在select中使用的时候，必须加:，这样就创建好了一个表tmp

增加数据：insert

（一）

#@File :insert_demo.pyselect * from tmp;                      \\本身10行数据select * from tmp a, tmp b, tmp c;      \\这里是1000行数据=10*10*10增加的数据是随机数据，rand() 函数生成0-1的一个随机数sha1() 对数字进行加密，然后就生活层楼一堆字符串concat() 拼接多个字符出的函数substr()取多少个字符获得随机整数的设计rand() * 50   0-50floor()  这个函数是代表的是去尾法取整数男女的设计：rand() * 10 /2    最后取余数如果余数为1，就设置为M如果余数为0，就设置为Fstudent = '''    set @a = 10000;    insert into student select @a := @a + 1, substr(concat(sha1(rand()), sha1(rand())), 1, 5*floor(rand()* 50)), case floor(rand()*10) mod 2 when 1 then "M" else "F" end, 20+floor(rand()*8) from tmp a, tmp b, tmp c, tmp d;'''

首先是student表的函数

可以尝试在数据库端操作验证命令行是否正确。查看：select count(*) from student;

实验操作之后清空：truncate table student;

course = '''set @i := 10;            insert into Course select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 5 + floor(rand() * 40)),  1 + floor(rand() * 100) from tmp a;        '''score = '''set @i := 10000;            insert into Score select @i := @i +1, floor(10001 + rand()*10000), floor(11 + rand()*10), floor(1+rand()*100) from tmp a, tmp b, tmp c, tmp d;        '''theacher = '''set @i := 100;        insert into Teacher select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 5 + floor(rand() * 80)) from tmp a, tmp b;    '''try:    cus_students = cnx.cursor()    cus_students.execute(students)    cus_students.close()    cus_course = cnx.cursor()    cus_course.execute(course)    cus_course.close()    cus_score = cnx.cursor()    cus_score.execute(score)    cus_score.close()    cus_teacher = cnx.cursor()    cus_teacher.execute(theacher)    cus_teacher.close()    cnx.commit()except Exception as e:    cnx.rollback()    print('error')    raise efinally:    cnx.close()

对于设计的整张表，数据的增加。

解释；

我们知道Student有四个字段，StdID，StdName，Gender，Age；我们先来看这个select语句：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;

StdID字段：@i就代表的就是，从10000开始，在上一句sql中设置的；

StdName字段：substr(concat(sha1(rand()),sha1(rand())), 1, floor(rand() * 80))就代表的是，

substr是一个字符串函数，从第二个参数1,开始取字符，取到3+ floor(rand() * 75)结束

floor函数代表的是去尾法取整数。

rand()函数代表的是从0到1取一个随机的小数。

rand() * 75就代表的是：0到75任何一个小数，

3+floor(rand() *75)就代表的是：3到77的任意一个数字

concat()函数是一个对多个字符串拼接函数。

sha1是一个加密函数，sha1(rand())对生成的0到1的一个随机小数进行加密，转换成字符串的形式。

       concat(sha1(rand()),sha1(rand()))就代表的是：两个0-1生成的小数加密然后进行拼接。

substr(concat(sha1(rand()),sha1(rand())), 1, floor(rand() * 80))就代表的是：从一个随机生成的一个字符串的第一位开始取，取到（随机3-77）位结束。

Gender字段：casefloor(rand()*10) mod 2 when 1 then 'M' else 'F' end,就代表的是，

       floor(rand()*10)代表0-9随机取一个数

       floor(rand()*10)mod 2 就是对0-9取得的随机数除以2的余数，

casefloor(rand()*10) mod 2 when 1 then 'M' else 'F' end,代表：当余数为1是，就取M，其他的为F

Age字段：25-floor(rand() *5)代表的就是，25减去一个0-4的一个整数

现在有一个问题，为什么会出现10000条数据呢，这10000条数据时怎么生成的呢，虽然字段一一对应上了，但是怎么出来这么多数据呢？

先来看个例子：

select * from tmp a, tmp b, tmp c;

最终是1000条数据，试试有一些感觉了呢，a， b， c都是tmp表的别名，相当于每个表都循环了一遍。所以最终的数据是有多少个表，就是10的多少次幂。

查询数据：

#@File :select_mysql.pyimport codecsimport MySQLdbdef connect_mysql():    db_config = {        'host': '192.168.172.131',        'port': 3306,        'user': 'xiao',        'passwd': '123456',        'db': 'python',        'charset': 'utf8'    }    cnx = MySQLdb.connect(**db_config)    return cnxif __name__ == '__main__':    cnx = connect_mysql()    sql = '''select * from Student where StdName in (select StdName from Student group by StdName having count(1)>1 ) order by StdName;'''    try:        cus = cnx.cursor()        cus.execute(sql)        result = cus.fetchall()        with codecs.open('select.txt', 'w+') as f:            for line in result:                f.write(str(line))                f.write('\n')        cus.close()        cnx.commit()    except Exception as e:        cnx.rollback()        print('error')        raise e    finally:        cnx.close()

解释：

1.     我们先来分析一下select查询这个语句：

select * from Student where StdName in(select StdName from Student group by StdName having count(1)>1 ) order byStdName;'

2.     我们先来看括号里面的语句：select StdName from Student group by StdName having count(1)>1；这个是把所有学生名字重复的学生都列出来，

3.     最外面select是套了一个子查询，学生名字是在我们（）里面的查出来的学生名字，把这些学生的所有信息都列出来。

4.     result = cus.fetchall()列出结果以后，我们通过fetchall()函数把所有的内容都取出来，这个result是一个tuple

通过文件写入的方式，我们把取出来的result写入到select.txt文件中。得到最终的结果。

感觉这章没咋学懂。之后再复习重温下吧。