任务：
六周三次课（11月22日）
12.8 创建表
12.9/12.10 增加数据
12.11 查询数据
笔记：
1.创建 建表
数据库键表，直接在python代码中执行，
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Time : 2017/5/5 13:56
# @Author : llill
# @File : aa.py
import MySQLdb
def connect_mysql():
db_config = {
'host': '192.168.48.128',
'port': 3306,
'user': 'xiang',
'passwd': '123456',
'db': 'python',
'charset': 'utf8'
}
cnx = MySQLdb.connect(**db_config)
return cnx
if __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(thearch)
cus.execute(tmp)
cus.close()
cnx.commit()
except Exception as e:
cnx.rollback()
print('error')
raise e
finally:
cnx.close()
结果：
mysql> show tables;
+------------------+
| Tables_in_python |
+------------------+
| Course |
| Score |
| Student |
| Teacher |
| tmp |
+------------------+
1 rows in set (0.00 sec)
没有任何异常，在数据库中查看表，出现这五个表。说明这五个表已经创建成功。
既然我们要搞，就尽可能的接近实战，我们来把数据搞大一点，语句设计的复杂一点，这样对我们以后接触到简单的sql语句时，就有很大的帮助。
首先我们先来了解一下information_schema这个库，这个在mysql安装时就有了，提供了访问数据库元数据的方式。那什么是元数据库呢？元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。
information_schema数据库表说明:
SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。
TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。
COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。
STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。
USER_PRIVILEGES（用户权限）表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。
SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。
TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。
COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。
CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER 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表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。
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，查看tmp的数据：
mysql> select * from tmp;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (0.00 sec)
我们只是从information_schema.tables表中取10条数据，任何表有10条数据也是可以的，然后把变量@i作为id列的值，分10次不断输出，依据最后select的结果，创建表tmp。
2. 增加数据
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Time : 2017/5/5 13:56
# @Author : llill
# @File : aa.py
import MySQLdb
def connect_mysql():
db_config = {
'host': '192.168.48.128',
'port': 3306,
'user': 'xiang',
'passwd': '123456',
'db': 'python',
'charset': 'utf8'
}
cnx = MySQLdb.connect(**db_config)
return cnx
if __name__ == '__main__':
cnx = connect_mysql()
students = '''set @i := 10000;
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;
'''
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 e
finally:
cnx.close()
结果：
mysql> select count(*) from Student;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.01 sec)
mysql> select count(*) from Course;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from Score;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from Teacher;
+----------+
| count(*) |
+----------+
| 100 |
+----------+
1 row in set (0.00 sec)
如图所示，在Student的表中增加了10000条数据，id是从10000开始的。count函数时用来统计个数的。
解释；
我们知道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字段：case floor(rand()*10) mod 2 when 1 then 'M' else 'F' end,就代表的是，
floor(rand()*10)代表0-9随机取一个数
floor(rand()*10) mod 2 就是对0-9取得的随机数除以2的余数，
case floor(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的多少次幂。
3. 查数据
10000条数据，可能会有同学的名字是一样的，我们的需求就是在数据库中查出来所有名字有重复的同学的所有信息，然后写入到文件中。
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Time : 2017/5/5 13:56
# @Author : llill
# @File : mm.py
import codecs
import MySQLdb
def connect_mysql():
db_config = {
'host': '192.168.48.128',
'port': 3306,
'user': 'xiang',
'passwd': '123456',
'db': 'python',
'charset': 'utf8'
}
cnx = MySQLdb.connect(**db_config)
return cnx
if __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(' ')
cus.close()
cnx.commit()
except Exception as e:
cnx.rollback()
print('error')
raise e
finally:
cnx.close()
结果：
本地目录出现一个select.txt文件，内容如下：
(19844L, u'315', u'F', 24)
(17156L, u'315', u'F', 25)
(14349L, u'48f', u'F', 25)
(17007L, u'48f', u'F', 25)
(12629L, u'afd', u'F', 25)
(13329L, u'afd', u'F', 24)
(10857L, u'e31', u'F', 23)
(14476L, u'e31', u'M', 21)
(16465L, u'ee5', u'M', 22)
(18570L, u'ee5', u'M', 21)
(17056L, u'ef0', u'M', 23)
(16946L, u'ef0', u'F', 24)
解释：
- 我们先来分析一下select查询这个语句：
select * from Student where StdName in (select StdName from Student group by StdName having count(1)>1 ) order by StdName;'
- 我们先来看括号里面的语句：select StdName from Student group by StdName having count(1)>1；这个是把所有学生名字重复的学生都列出来，
- 最外面select是套了一个子查询，学生名字是在我们（）里面的查出来的学生名字，把这些学生的所有信息都列出来。
- result = cus.fetchall()列出结果以后，我们通过fetchall()函数把所有的内容都取出来，这个result是一个tuple
- 通过文件写入的方式，我们把取出来的result写入到select.txt文件中。得到最终的结果。
4. 删除数据
有些老师不好好上次，导致课程的及格率太低，最后名最差的5名老师将会被开除。
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Time : 2017/5/5 13:56
# @Author : llill
# @File : delete.py
import codecs
import MySQLdb
def connect_mysql():
db_config = {
'host': '192.168.48.128',
'port': 3306,
'user': 'xiang',
'passwd': '123456',
'db': 'python',
'charset': 'utf8'
}
cnx = MySQLdb.connect(**db_config)
return cnx
if __name__ == '__main__':
cnx = connect_mysql()
sql = '''delete from Teacher where TID in(
select TID from (select Course.CouID, Course.TID, Teacher.TName, count(Teacher.TID) as count_teacher from Course
left join Score on Score.Grade < 60 and Course.CouID = Score.CouID
left join Teacher on Course.TID = Teacher.TID
group by Course.TID
order by count_teacher desc
limit 5) as test )
'''
try:
cus = cnx.cursor()
cus.execute(sql)
result = cus.fetchall()
cus.close()
cnx.commit()
except Exception as e:
cnx.rollback()
print('error')
raise e
finally:
cnx.close()
结果：
程序正常执行，没有报错
解释：
- 先查询出Course表中的Course.TID和Course.TID
- left join 是关联Score表，查出Score.Grade > 59，并且，课程ID和课程表的CouID要对应上
- left join Teacher 是关联老师表，课程中的了老师ID和老师表中的老师ID对应上
- select中加上老师的名字Teacher.Tname和count(Teacher.TID)
- group by Course.TID，在根据老师的的TID进行分组
- oder by 最后对count_teacher进行排序，取前5行，
- 在通过套用一个select子查询，把所有的TID搂出来
- 然后delete from Teacher 最后删除TID在上表中的子查询中。
5. 修改数据
首先，我们把分数低于5分的成绩所有都加60分。
代码如下：
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Time : 2017/5/5 13:56
# @Author : llill
# @File : update.py
import codecs
import MySQLdb
def connect_mysql():
db_config = {
'host': '192.168.48.128',
'port': 3306,
'user': 'xiang',
'passwd': '123456',
'db': 'python',
'charset': 'utf8'
}
cnx = MySQLdb.connect(**db_config)
return cnx
if __name__ == '__main__':
cnx = connect_mysql()
sql = '''select *, (grade+60) as newGrade from Score where Grade <5;'''
update = '''update Score set grade = grade + 60 where grade < 5; '''
try:
cus_start = cnx.cursor()
cus_start.execute(sql)
result1 = cus_start.fetchall()
print(len(result1))
cus_start.close()
cus_update = cnx.cursor()
cus_update.execute(update)
cus_update.close()
cus_end = cnx.cursor()
cus_end.execute(sql)
result2 = cus_end.fetchall()
print(len(result2))
cus_end.close()
cnx.commit()
except Exception as e:
cnx.rollback()
print('error')
raise e
finally:
cnx.close()
结果：
321
0
解释：
1．刚开始，我们可以查到分数小于5分的总个数有321个
2．select *, (grade+60) as newGrade from Score where Grade <5;这个sql是把所有的成绩小于5的都列出来，然后最后加一列分数加60分的结果。
3．update Score set grade = grade + 60 where grade < 5;是把分数小于5的所有成绩都加60分
4．最后在检查分数小于5的个数为0，说明所有低于5分的分数都发生了改变。