mysql知识点整理

1. MySQL：文件管理的软件
2. 三部分：
  - 服务端
  - SQL语句
  - 客户端
3. 客户端：
  - mysql
  - navicat

4. 授权操作
  - 用户操作
  - 授权操作
5. SQL语句
  - 数据库操作
    - create database xx default charset utf8;
    - drop database xx;
  - 数据表
    - 列
      - 数字
        整数
        小数
      - 字符串
      - 时间
      - 二进制
    - 其他：引擎，字符编码，起始值

    - 主键索引
    - 唯一索引
    - 外键
      - 一对多
      - 一对一
      - 多对多
  - 数据行
    - 增
    - 删
    - 改
    - 查
      - in   not in
      - between and
      - limit
      - group by  having
      - order by
      - like "%a"
      - left join xx on  关系
      - 临时表
        select * from (select * from tb where id< 10) as B;

      -
        select
          id,
          name,
          1,
          (select count(1) from tb)
        from tb2

        SELECT
          student_id,
          (select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文,
          (select num from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学,
          (select num from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语
        from score as s1;

      - 条件
        select
          course_id,
          max(num),
          min(num),
          min(num)+1,
          case when min(num) <10 THEN 0 ELSE min(num) END as c  
        from score GROUP BY course_id

        select course_id,avg(num),sum(case when num <60 THEN 0 ELSE 1 END),sum(1),sum(case when num <60 THEN 0 ELSE 1 END)/sum(1) as jgl from score GROUP BY course_id order by AVG(num) asc,jgl desc;
  PS: 数据放在硬盘上



思想：
  - 操作
  - 设计

返回顶部

pymysql模块初识以及SQL注入

注意：一定不能自己做字符串拼接，会被sql注入
注入语句示例

import pymysql

user = input("username:")
pwd = input("password:")

conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor()
sql = "select * from userinfo where username=%s and password=%s"
#sql = "select * from userinfo where username=%(u)s and password=%(p)s"
cursor.execute(sql,user,pwd)
# cursor.execute(sql,[user,pwd])
# cursor.execute(sql,{'u':user,'p':pwd})
result = cursor.fetchone()
cursor.close()
conn.close()
if result:
    print('登录成功')
else:
    print('登录失败')

注入语句示例片段

user = input("username:")
pwd = input("password:")

conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor()
sql = "select * from userinfo where username=‘%s’ and password=‘%s’" %（user,pwd）
cursor.execute(sql)

哪果用户直接在用户名处输入如下内容:

test' or 1=1 --

而不用输入密码就可以登陆了。因为mysql的注释符为：--
所以字符串拼接就成了：

select * from userinfo where username='test' or 1=1 --' and password=‘%s’
#相当于--之后的内容变成了mysql的注释

返回顶部

pymysql模块操作数据库详细

增删改

cursor.execute()

返回受影响的行数

import pymysql

conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor()
sql = "insert into userinfo(username,password) values('root','123123')"
# r为返回的受影响的行数
r = cursor.execute(sql)
conn.commit()  #增加，删，改都需要commit
cursor.close()
conn.close()

返回顶部

cursor.executemany()

也是返回受影响的行数
executemany只适合insert时使用

conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor()
# sql = "insert into userinfo(username,password) values(%s,%s)"
# cursor.execute(sql,(user,pwd,))

sql = "insert into userinfo(username,password) values(%s,%s)"
# r为返回的受影响的行数
r = cursor.executemany(sql,[('egon','sb'),('laoyao','BS')])
conn.commit()
cursor.close()
conn.close()

返回顶部

查询

cursor.fetchone()

每次返回一条查询结果

cursor.fetchall()

返回查询的所有数据

conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)    #设定返回的数据为字典形式
sql = "select * from userinfo"
#sql = "select * from userinfo limit 10"
cursor.execute(sql)

cursor.scroll(1,mode='relative')  # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动
result = cursor.fetchone()
print(result)
result = cursor.fetchone()
print(result)
result = cursor.fetchone()
print(result)
result = cursor.fetchall()
print(result)

cursor.fetchmany() 不常用

result = cursor.fetchmany(4)   #指定返回的条数
print(result)
cursor.close()
conn.close()

返回顶部

cursor.lastrowid获取新插入数据的自增id

#新插入数据的自增ID: cursor.lastrowid
import pymysql

conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor()
sql = "insert into userinfo(username,password) values('asdfasdf','123123')"
cursor.execute(sql)
conn.commit()
print(cursor.lastrowid)  #获取自增id
cursor.close()
conn.close()

返回顶部

pymysql总结

pymysql模块：
  pip3 install pymysql -i https://pypi.douban.com/simple
  Python模块：对数据库进行操作（SQL语句）

  1. Python实现用户登录
  2. MySQL保存数据

  - 连接、关闭（游标）
  - execute()   -- SQL注入
  - 增删改： conn.commit()
  - fetchone fetchall
  - 获取插入数据自增ID

返回顶部

pymysql练习


	权限管理

		权限表：
			1   订单管理
			2   用户管理
			3   菜单管理
			4   权限分配
			5   Bug管理

		用户表：
			1   Alex
			2   egon

		用户权限关系表：
			1    1
			1    2
			2    1

	Python实现：

		某个用户登录后，查看自己拥有所有权限