MySQL——4

复习

1.Mysql 文件管理软件
2.三部分
-服务端
-Mysql
-客户端
3.客户端
-Mysql
-navicat
4.授权操作
-用户操作
-授权操作 grant
5.sql语句
-数据库操作
-create database xx default charset utf8;
-drop database xx;
-表操作
-列
-数字类型
整数
小数
-字符串类型 char varcher
-时间 datatime
-二进制
-其他:引擎,字符编码,起始值
-约束:
-主键索引
-唯一索引
-外键
-一对一
-一对多
-多对多 三张表(关系表)
-行操作
-增
-删
-改
-查
-where
-in /not in
-limit
-grou by
-order by desc/asc
-having
-between and
-like 'a_'/'a%'
-left join xx on relation
-() as tb1

学习内容

1.Mysql 练习题,
2.python 模块,对数据库进行操作
1.实现用户登录
2.连接、关闭(cursor)
3.增删改,conn.commit()
4.fetchone,fetchall
5.获取插入数据自增ID

 

 

代码区

1.pymysql

import pymysql

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

conn = pymysql.connect(host='localhost',user='root',password='',database='db666')
cursor=conn.cursor()
sql = 'select * from user_info where username=%(u)s and password=%(p)s'
cursor.execute(sql,{'u':user,'p':pwd})
result = cursor.fetchone()
if result:
    print(result)
else:
    print('landing failed')

cursor.close()
conn.close()

2.增

import pymysql

user='egon'
pwd=123
conn = pymysql.connect(host='localhost',user='root',password='',database='db666')
cursor = conn.cursor()
sql = 'insert into user_info(username,password) values (%s,%s)'
cursor.execute(sql,(user,pwd))
cursor.executemany(sql,[(user,pwd),(user+'1',pwd+1)])
conn.commit()
cursor.close()
conn.close()

3.查

import pymysql
conn=pymysql.connect(host='localhost',user='root',password='',database='db666')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'select * from user_info'
cursor.execute(sql)
result = cursor.fetchall()
print(result)
cursor.close()
conn.close()

4.新插入的自增ID

import pymysql
conn = pymysql.connect(host='localhost',user='root',password='',database='db666')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql='insert into user_info(username,password) values ("sss",123)'
r = cursor.execute(sql)
conn.commit()
print(cursor.lastrowid,r)
cursor.close()
conn.close()

 

posted @ 2019-05-31 20:42  王二被占用  阅读(101)  评论(0)    收藏  举报