【学习笔记】MySQL和Redis

MySQL

修改表格的编码集：alter table <表名> charset=<新编码集>;
修改字段的编码集：alter table <表名> modify <字段名> <类型> charset <新编码集>;

import pymysql
import hashlib

db = pymysql.connect(host='localhost', user='root', password='123456',
                     port=3306, charset='utf8', database='python')

cursor = db.cursor()
cursor.execute('select * from stud')
cursor.close()
db.commit()

data = cursor.fetchall()  # 获取所有数据
for info in data:
    print(info)

username = input('please input username: ')
password = input('please input password: ')
h = hashlib.md5()
h.update(password.encode('utf8'))
password = h.hexdigest()

cursor = db.cursor()
# sql注入
# 如果输入用户名为 zhangsan"# , 则mysql语句为 select * from user where name="zhangsan"#" and password="%s"
# cursor.execute('select * from user where name="%s" and password="%s"' % (username, password))

sql = 'select * from user where name=%s and password=%s'
cursor.execute(sql, (username, password))
cursor.close()
db.commit()

db.close()
result = cursor.fetchone()  # 获取一行数据

if not result:
    print('用户名或密码错误!')
else:
    print('Welcome, {}!'.format(username))

redis

import redis

client = redis.Redis()

client.set('name', 'zhangsan')
print(client.get('name'))
client.delete('name')

client.hset('dog', 'name', 'keji')
client.hset('dog', 'age', 3)
client.hset('dog', 'color', 'yellow')
print(client.hkeys("dog"))  # 取hash中所有的key
print(client.hget("dog", "name"))  # 单个取hash的key对应的值
print(client.hmget("dog", "name", "age", "color"))  # 多个取hash的key对应的值
client.hdel("dog", "name", "age", "color")

client.lpush("list1", 11, 22, 33)
print(client.lrange('list1', 0, -1))
client.rpush("list2", 44, 55, 66)  # 在列表的右边，依次添加44,55,66
print(client.llen("list2"))  # 列表长度
print(client.lrange("list2", 0, -1))
while True:
    client.blpop(["list1", "list2"], timeout=2)
    print(client.lrange("list1", 0, -1), client.lrange("list2", 0, -1))

SQL执行顺序

from -> join on -> where -> group by -> having + 聚合函数 -> select -> order by -> limit