pymysql笔记

创建数据库

import pymysql

db = pymysql.connect(host='localhost', user='root', password='zxc123456', port=3306)
cursor = db.cursor()
cursor.execute("CREATE DATABASE datatime DEFAULT CHARACTER SET utf8")
db.close()

创建 关系表

import pymysql

db = pymysql.connect(host='localhost', user='root', password='zxc123456', port=3306，db='datatime')
cursor = db.cursor()
sql = 'CREATE TABLE IF NOT EXISTS students (id VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, age INT NOT NULL, PRIMARY KEY (id))'
cursor.execute(sql)
db.close()

增
i

mport pymysql

db = pymysql.connect(host='localhost', user='root', password='zxc123456', port=3306,db='datatime')
cursor = db.cursor()
id='20'
user='李白'
age='18'
sql = 'INSERT INTO students(id, name, age) values(%s, %s, %s)'
cursor.execute(sql, (id, user, age))
db.commit()
db.close()

改

import pymysql

db = pymysql.connect(host='localhost', user='root', password='zxc123456', port=3306,db='datatime')
cursor = db.cursor()
id='20'
user='李白'
age='18'
sql = 'UPDATE students SET age = %s WHERE name = %s'
cursor.execute(sql, (25, '李白'))
db.commit()
db.close()

查

import pymysql

db = pymysql.connect(host='localhost', user='root', password='zxc123456', port=3306,db='datatime')
cursor = db.cursor()
sql = 'SELECT * FROM students '
cursor.execute(sql)
print('Count:', cursor.rowcount)#个数
one = cursor.fetchone()
print('One:', one)#结果
results = cursor.fetchall()
print('Results:', results)#其他
print('Results Type:', type(results))
for row in results:
    print(row)

删

import pymysql

db = pymysql.connect(host='localhost', user='root', password='zxc123456', port=3306,db='datatime')
cursor = db.cursor()


sql = 'DELETE FROM  students WHERE age > 20'

cursor.execute(sql)
db.commit()

cmd mysql语句

mysql -u root -p #登录
显示数据库列表。
show databases;
显示库中的数据表：
show tables;
显示数据表的结构：
describe 表名;
建库：
create database 库名;
使用数据库
use test;
建表：
use 库名；
create table 表名 (字段设定列表)；
删库和删表:
drop database 库名;
drop table 表名；
将表中记录清空：
delete from 表名;(这个清空表只是把数据表内容数据清掉，自增id不会被清掉，自增id会保留)
truncate table 表名；（成功返回0）（自增id也一同会被清掉）
修改密码
update user set password=PASSWORD('新密码') where user='用户名'