MySQL学习笔记（三）

前面杂七杂八说几句：三四天的内容我合并了一下，因为Day3与Day4有很多是练习题讲解

练习题链接：https://www.cnblogs.com/wupeiqi/articles/5729934.html

练习题答案：https://www.cnblogs.com/wupeiqi/articles/5748496.html  （有一些答案是错的）

一、前文补充

1. 三元运算

　　我也是第一次接触到这个概念，就简单记一下例子。 AVG(if(isnull(num), 0, num)) ，这是select中的一列，是在分组后计算分组后的num这一列的平均值，表示如果num为空值的话，则把num当作0，不是空值的话就是原数值。

2. 条件语句

　　在select时可以使用条件语句，对每列的值进行一个变换，如 

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select course_id, min(num), case when min(num)<10 then 0 else min(num) end, max(num) from score GROUP BY course_id
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
select course_id, avg(num), sum(case when num<60 then 0 else 1 end)/sum(1) as 及格率 from score GROUP BY course_id ORDER BY AVG(num) asc, 及格率 desc

 

其实和三元运算有点像昂，不过例子1中这里是新增了一列， 表示如果num这一列的数值小于10，就变为0，不然就写作1。例子二中就是在计算时变换一下。

3. 笛卡尔积合并

　　 SELECT s2.student_id FROM score as s1, score as s2 可以使两个表依照笛卡尔积的形式进行合并。

二、 Python中的pymysql的使用

1. 安装

pip3 install pymysql -i https://pypi.douban.com/simple 
#也可以换成其他镜像（参考另一篇随笔里面的各种镜像的网址）

2. python中pymysql的链接与关闭

#导入MySQL模块
import pymysql

#用户登录，进行数据库链接
conn = pymysql.connect(host="localhost",user='root',password='',database="db1")
cursor = conn.cursor()


#在对数据进行完操作之后，需要关闭与数据库之间的链接
cursor.close()
conn.close()

此时，cursor就是一个Cursor对象，这个cursor是一个实现了迭代器（def __iter__()）和生成器（yield）的MySQLdb对象，这个时候cursor中还没有数据，只有等到fetchone()或fetchall()的时候才返回一个元组tuple，才支持len()和index()操作，这也是它是迭代器的原因。但同时为什么说它是生成器呢？因为cursor只能用一次，即每用完一次之后记录其位置，等到下次再取的时候是从游标处再取而不是从头再来，而且fetch完所有的数据之后，这个cursor将不再有使用价值了，即不再能fetch到数据了。

3. 在python中进行查询（使用execute()时，需要注意sql注入的问题）

#若在userinfo表中存在一个叫alex的用户，其登录密码是123，现在该用户要进行登录

user = "alex" #input("username:")，该方法可以实现其他用户的输入登录
pwd = "123" #input("password:")

#编写sql语句
sql = "select * from userinfo where username='%s' and password='%s'" %(user,pwd)

#使用exercute()执行sql语句
cursor.execute(sql)

#抓取执行语句后结果中的第一条
result = cursor.fetchone()

但是如果采用字符串拼接的方式的话，会存在字符串注入的问题，即非正常方式也可以登录成功。如在 user = input("username") 中，当输入为 uuu' or 1=1 -- (注意在--后面有一个空格) 时，可以成功登录数据库。因此，为了避免由字符串拼接带来的sql注入问题，我们可以使用 execute() 里的内置参数进行传递，如写成以下形式：

#为了方便，直接设置用户名（已存入数据库中）
user = 'alex'
pwd = '123'

#链接
conn = pymysql.connect(host='localhost', user='root', passwd='', database='db1')
cursor = conn.cursor()

sql = "select * from t1 where name = %s and password = %s"

#print(sql)
cursor.execute(sql,(user, pwd,)) #在后面进行参数的传递，此时就不能使用上述方法进行sql的注入了

#参数的传递还可以以字典，列表的形式进行传递

  #cursor.execute(sql,[user,pwd])    
  #sql = "select * from userinfo where username=%(u)s and password=%(p)s"
  #cursor.execute(sql,{'u':user,'p':pwd})

result = cursor.fetchone()
print(result)

if result:
    print("登陆成功")  
else:
    print("登录失败")

cursor.close()
conn.close()

在上述代码中，查询完成后若需要查看数据的返回值，需要使用 cursor.fetchone() 查看返回值，该表达式只查看返回的第一条数据，若再次执行 cursor.fetchone() ，则会返回查询到的第二条数据，cursor在其中有一个指针的作用。指针也可以用以下函数进行调节：

cursor.scroll(1,mode='relative')  # 相对当前位置移动一个，即若有三条数据，在取出第一条数据后使用该函数，则再次取出的是第三条数据，
                                  #其中1可以为负值，表示向前取值

cursor.scroll(1,mode='absolute') # 相对绝对位置移动1个，即如果有三条数据，则取出来是第二条数据

倘若想要一次取多条数据，可以使用 cursor.fetchmany(n) ，其中n表示需要的数据条数（如果不设定n，则会自动返回查询到的第一条数据）。如果想要返回查询到的全部数据，可以使用 cursor.fetchall() 。

不过在返回多个时，其返回形式为元祖形式，元祖内部又有多个元祖，每一个元祖都是一条数据。元祖形式的数据不能查看数据表示的含义，如果使用 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) ，则其返回形式为字典形式，可以查看数据的含义。返回结果形式如下：

conn = pymysql.connect(host='localhost', user='root', passwd='', database='db1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql = "select * from t1 where name = %s and password = %s"

cursor.execute(sql, ['meng', '777'])
result = cursor.fetchall()
print(result)

#[{'id': 23, 'name': 'meng', 'password': '777'}, {'id': 25, 'name': 'meng', 'password': '777'}, {'id': 38, 'name': 'meng', 'password': '777'}]

4. 在python中进行数据的添加

pay attention：在写完添加数据的sql语句之后，需要使用 conn.commit() 才能把数据真正添加进去（增删改都要在最后加上这个语句）。以下是插入一个新用户的写法：

conn = pymysql.connect(host="localhost",user='root',password='',database="db1")
cursor = conn.cursor()
sql = "insert into userinfo(username,password) values('root','123123')"

cursor.execute(sql)

conn.commit() #完成写入数据的操作
cursor.close()
conn.close()

可以使用 cursor.executemany() 一次插入多条数据，如：（其实我发现一次查询多个的时候用这个函数也不会报错，但是它只会给你查最右边的那一个）

sql = "insert into t1(name,password) values(%s,%s)"

cursor.executemany(sql, [('alex', '123'), ('meng', '777')])

conn.commit()  #不要忘记！！！不然写不进去！！！

在此过程中，可以将cursor.execute(sql)的值存入一个变量，如 r = cursor.execute(sql) ，其中r是受到影响的行数，如插入了几行值，查询结果是几行等。

4. 获取插入数据的自增ID

在设计表的时候，会设计某一列为自增ID，即不输入时该列自动增加。

使用 cursor.lastrowid 可以获得最后一条插入数据的自增ID。