复习：

    1. MySQL：文件管理的软件

    2. 三部分：

        - 服务端

        - SQL语句

        - 客户端

    3. 客户端：

        - MySQL

        - navicat

    4. 授权操作：

        - 用户操作

        - 授权操作

    5. SQL语句

        - 数据库操作

            - 创建数据库要指定字符编码：

                create database xx default charset=utf8;

            - 删除数据库：

                drop database xx;

        - 数据表操作

            - 列

                - 数字

                    整数

                    小数

                - 字符串

                - 时间

                    datatime

                - 二进制

            - 其他：引擎、字符编码、起始值

            - 主键索引

            - 唯一索引

            - 外键

                - 一对多

                - 一对一

                - 多对多

        PS：数据放在硬盘上

        - 数据行操作

            - 增

            - 删

            - 改

            - 查

                - in    not in

                - between and

                - limit

                - group by    having

                - order by

                    -desc

                    -asc

                - like '%a'

                - left join xx on 关系

                - inner join

                - 临时表

                    select * from(select * from tb1 where id<10) as B;

        PS：笛卡尔积

        a:三条数据  1 2 3

        select * from a as a1,a as a2;

        1 1、1 2、1 3、2 1、2 2、2 3、3 1、3 2、3 3

    1. 去重：distinct    -- 效率不高

        SELECT distinct student.sid,student.sname from score LEFT JOIN student ON score.student_id=student.sid where score.num<60;

import pymysql

'''

user = input('username:')

pwd = input('possword:')

conn = pymysql.connect(host='localhost', user='root', password='lpg123456', database='db1')     # 打开数据库

cursor = conn.cursor()          # 游标

# 连接数据库成功

sql = "select * from userinfo where username = %(u)s and password = %(p)s "

cursor.execute(sql, {'u': user, 'p': pwd})

result = cursor.fetchone()

cursor.close()

conn.close()

if result:

    print('登录成功')

else:

    print('登录失败')

'''

# 增 删 改

'''

conn = pymysql.connect(host='localhost',user='root',password='lpg123456',database='db1')

cursor = conn.cursor()

sql = "insert into userinfo(username, password) values('egon','123456')"

cursor.execute(sql)

conn.commit()           # 提交  增 删 改  需要用commit

cursor.close()

conn.close()

'''

'''

user = 'wusir'

pwd = '123456'

conn = pymysql.connect(host='localhost',user='root',password='lpg123456',database='db1')

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,[('laoyao','sb'),('taibai','SB')])       # 只适用于insert

conn.commit()           # 提交  增 删 改  需要用commit

cursor.close()

conn.close()

'''

# 查

'''

conn = pymysql.connect(host='localhost',user='root',password='lpg123456',database='db1')

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)         # 默认cursor为None，改成以字典形式打印

sql = "select * from userinfo limit 10"

# r：受影响的行数

cursor.execute(sql)

# result = cursor.fetchone()        # 一次取一个

# print(result)

#

# result = cursor.fetchone()

# print(result)

#

# result = cursor.fetchone()

# print(result)

# result = cursor.fetchmany(4)        # 一次取多个

# print(result)

result = cursor.fetchall()          # 一次取所有

print(result)

# [{'id': 1, 'username': 'alex', 'password': '123456'}, {'id': 2, 'username': 'egon', 'password': '123456'}, {'id': 5, 'username': 'wusir', 'password': '123456'}, {'id': 6, 'username': 'laoyao', 'password': 'sb'}, {'id': 7, 'username': 'taibai', 'password': 'SB'}]

cursor.close()

conn.close()

'''

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

conn = pymysql.connect(host='localhost',user='root',password='lpg123456',database='db1')

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql = "insert into userinfo(username,password) values('asdsafd','467467')"

cursor.execute(sql)

conn.commit()

print(cursor.lastrowid)

cursor.close()

conn.close()

        - 连接、关闭(游标)

        - execute()   --> SQL注入问题

        - 增删改 ： conn.commit()

        - fetchone()

        - fetchmany()

        - fetchall()

        - 获取插入数据的自增ID：lastrowid