python mysql

mysql 练习

创建数据库 grade
```
create database grade;
```

数据库中创建表 student

create table student(id int primary key auto_increment,name varchar(32),age int,hobby set ('football','computer','running','basketabll'),score int,comment text);

支持中文

ALTER TABLE student CONVERT TO CHARACTER SET utf8mb4;

插入数据

insert into student values (null,'tom',7,'football',75,'上进青年');
insert into student values (null,'jane',5,'basketabll',100,'北漂少年');
insert into student values (null,'luay',4,'football,computer',80,'知识分子');
insert into student values (null,'per',14,'football,computer,running',75,'全能冠军');
insert into student values (null,'fland',13,'running',20,'天桥少年');
insert into student values (null,'uzi',12,'computer,running',99,'国内知名电竞选手');
insert into student values (null,'labasy',12,null,40,'差学生');

查找

查找所有年龄不到10岁或者大于14岁的同学

select * from student where age<10 or age >14;
select * from student where age not between 10 and 14;

查找兴趣爱好中包含computer的同学

select * from student where find_in_set('computer',hobby);

查找年龄大于等于15 又喜欢足球的同学

select * from student where age>=15 and find_in_set('computer',hobby);

查找不及格兴趣爱好又不为空的同学

select * from student where score<60 and hobby is not null;

查找成绩大于90分的所有同学,只看姓名和成绩
```
select name,score from student where score >90;
```

更新update

update class set name = '你在就好了' where id= 2;

删除表记录 delete

delete from student where score < 60;

表字段的操作(alter)

语法 ：alter table 表名 执行动作;

* 添加字段(add)
    alter table 表名 add 字段名 数据类型;
    alter table 表名 add 字段名 数据类型 first;
    alter table 表名 add 字段名 数据类型 after 字段名;
* 删除字段(drop)
    alter table 表名 drop 字段名;
* 修改数据类型(modify)
    alter table 表名 modify 字段名 新数据类型;
* 修改字段名(change)
    alter table 表名 change 旧字段名 新字段名 新数据类型;
* 表重命名(rename)
    alter table 表名 rename 新表名;

练习

重命名

alter table class rename class_1;

添加字段

 alter table class_1 add abc int;
 alter table class_1 add bcd int first;
 alter table class_1 add edf int after bcd;

删除字段

 alter table class_1 drop bcd;
 alter table class_1 drop bcd;
 alter table class_1 drop abc;

修改数据类型

 alter table class_1 modify abc varchar(32);

修改字段名称

 alter table l_user change user username varchar(32);

时间类型数据

时间和日期类型:

DATE，DATETIME和TIMESTAMP类型
TIME类型
年份类型YEAR

时间格式

date ：“YYYY-MM-DD”
time ：“HH:MM:SS”
datetime ：“YYYY-MM-DD HH:MM:SS”
timestamp ：“YYYY-MM-DD HH:MM:SS”
注意
1、datetime ：以系统时间存储
2、timestamp ：以标准时间存储但是查看时转换为系统时区，所以表现形式和datetime相同

e.g.
create table marathon (id int primary key auto_increment,athlete varchar(32),birthday date,registration_time datetime,performance time);

insert into class_1 values(null,'Jame',17,'m',90.5,'2019-4-28');
update class_1 set s = '2016-5-6' where name = 'barom';
update class_1 set s = '2016/5/6';
update class_1 set s = '2016-8-16' where id=1;
update class_1 set s = '2016-6-16' where id=5;
update class_1 set s = '2016-9-26' where id=7;

create table marathon (id int primary key auto_increment,name varchar(32),start_date datetime,times time);
insert into marathon values(null,'赵四','2019-3-28 14:28:36','2:28:57');

日期时间函数

now() 返回服务器当前时间,格式对应datetime类型

insert into marathon values(null,'王五',now(),'3:28:57');

curdate() 返回当前日期，格式对应date类型

insert into marathon values(null,'穷奇',curdate(),'2:28:57');

curtime() 返回当前时间，格式对应time类型

insert into marathon values(null,'七七',curtime(),curtime());

时间操作

查找操作

  select * from marathon where birthday>='2000-01-01';
  select * from marathon where birthday>="2000-07-01" and performance<="2:30:00";

日期时间运算
- 语法格式
  
  select * from 表名 where 字段名运算符 (时间-interval 时间间隔单位);
- 时间间隔单位： 2 hour | 1 minute | 2 second | 2 year | 3 month | 1 day

  select * from marathon where registration_time > (now()-interval 7 day);
  select * from marathon where times< (time('03:00:00')-interval 30 minute);

时间练习

select * from marathon where start_date >='2020-12-1' and start_date <= now();

select * from marathon where times< (time('03:00:00')-interval 30 minute);
select * from marathon where start_date> (now()-interval 1 day);

高级查询语句模糊查询和正则查询

LIKE用于在where子句中进行模糊查询
%来表示任意0个或多个字符
_表示任意一个字符

SELECt field1, field2,...fieldN 
FROM table_name
WHERe field1 LIKE condition1
e.g. 
mysql> select * from class_1 where name like 'A%';

-- 以L 开头的 人名--
select * from class_1 where name like 'L%';
--包含om的--
select * from class_1 where name like '%om%';

-- 以x?m结尾--
select * from class_1 where name like 't_m';

mysql中对正则表达式的支持有限，只支持部分正则元字符

SELECt field1, field2,...fieldN 
FROM table_name
WHERe field1 REGEXP condition1
e.g. 
select * from class_1 where name regexp '^B.+';

--以B开头的--
select * from class_1 where name regexp 'bc.*';

排序

ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

使用 ORDER BY 子句将查询数据排序后再返回数据：

SELECt field1, field2,...fieldN from table_name1 where field1
ORDER BY field1 [ASC [DESC]]

默认情况ASC表示升序，DESC表示降序

select * from class_1 where sex='m' order by age;
--按年龄升序排序--
select * from class_1 order by age;

分页(限制)

LIMIT 子句用于限制由 SELECt 语句返回的数据数量或者 UPDATE,DELETE语句的操作数量

带有 LIMIT 子句的 SELECT 语句的基本语法如下：

SELECT column1, column2, columnN 
FROM table_name
WHERe field
LIMIT [num]

select * from class_1 limit 1;
# 查询第一个报名的
select * from class_1 order by s limit 1 ;

联合查询

UNIOn 操作符用于连接两个以上的 SELECt 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

UNIOn 操作符语法格式：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERe conditions]
UNIOn [ALL | DISTINCT]
SELECt expression1, expression2, ... expression_n
FROM tables
[WHERe conditions];

expression1, expression2, … expression_n: 要检索的列。
tables: 要检索的数据表。
WHERe conditions: 可选，检索条件。
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNIOn 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。

select * from class_1 where sex='m' UNIOn ALL select * from class_1 where age > 9;
# 查找男生 和年龄大于17岁的
select * from class_1 where sex = 'w';
select * from class_1 where age > 17;

select * from class_1 where sex = 'w' union select * from class_1 where age > 17;

select * from class_1 where sex = 'w' union all select * from class_1 where age > 17;

select name,age from class_1  where sex = 'w' union select name,age from class_1 where age > 17;

数据备份

备份命令格式

mysqldump -u用户名 -p 源库名 > ~/stu.sql

–all-databases 备份所有库
库名备份单个库
-B 库1 库2 库3 备份多个库
库名表1 表2 表3 备份指定库的多张表

# mysql 备份
mysqldump -uroot -p stu > C:UsersAdministratorDesktopstu.sql

恢复命令格式

mysql -uroot -p 目标库名 < stu.sql
从所有库备份中恢复某一个库(–one-database)

mysql -uroot -p --one-database 目标库名 < all.sql

# 插入之前先创建
create database student
# 导入表
mysql -uroot -p student< C:UsersAdministratorDesktopstu.sql

Python操作MySQL数据库 pymysql安装

sudo pip3 install pymysql

pymysql使用流程

建立数据库连接(db = pymysql.connect(…))
创建游标对象(cur = db.cursor())
游标方法: cur.execute(“insert …”)
提交到数据库或者获取数据 : db.commit()/db.fetchall()
关闭游标对象：cur.close()
断开数据库连接：db.close()

常用函数

db = pymysql.connect(参数列表)

host ：主机地址,本地 localhost
port ：端口号,默认3306
user ：用户名
password ：密码
database ：库
charset ：编码方式,推荐使用 utf8

数据库连接对象(db)的方法

cur = db.cursor() 返回游标对象,用于执行具体SQL命令
db.commit() 提交到数据库执行
db.rollback() 回滚，用于当commit()出错是回复到原来的数据形态
db.close() 关闭连接

游标对象(cur)的方法

cur.execute(sql命令,[列表]) 执行SQL命令
cur.fetchone() 获取查询结果集的第一条数据，查找到返回一个元组否则返回None
cur.fetchmany(n) 获取前n条查找到的记录，返回结果为元组嵌套元组， ((记录1),(记录2))。
cur.fetchall() 获取所有查找到的记录，返回结果形式同上。
cur.close() 关闭游标对象

pycharm 读取操作

"""
mysql
pymysql 基本数据库基本流程演示
"""

import pymysql

# 连接数据库
db = pymysql.connect(
    host='localhost',
    port=3306,
    password='123456',
    user='root',
    database='stu',
    charset='utf8',
)

# 获取游标 (操作数据库,执行sql语句)
cur = db.cursor()
sql =  "select name,age from class_1 where sex = 'm'"

# 执行语句
cur.execute(sql)

# 获取一个查询结果
print(cur.fetchone())
print('*'*50)
# 获取一个查询结果
print(cur.fetchmany(2))
print('*'*50)

# 获取所有结果
for result in cur.fetchall():
    print(result)

cur.close()
db.close()

pycharm 写操作

"""
mysql
pymysql 基本数据库基本流程演示
"""

import pymysql

# 连接数据库
db = pymysql.connect(
    host='localhost',
    port=3306,
    password='123456',
    user='root',
    database='stu',
    charset='utf8mb4',
)

# 获取游标 (操作数据库,执行sql语句)
cur = db.cursor()

# 写数据库
try:
    # 写sql 语句执行
    # 插入操作
    # name = input('Name:')
    # age = int(input('Age:'))
    # score = float(input('Score:'))
    # sql = "insert into class_1 values (null,'%s','%d','w','%f',now())" % (name, age, score)
    # print(sql)
    # cur.execute(sql)

    # # 插入操作
    # name = input('Name:')
    # age = input('Age:')
    # score = input('Score:')
    # sql = "insert into class_1 values (null,%s,%s,'w',%s,now())"
    # print(sql)
    # cur.execute(sql, [name, age, score])

    # # 修改操作
    # price = 11880
    # name = 'Abby'
    # sql = "update interset set price =%d where name = '%s'" % (price, name)
    # print(sql)
    # cur.execute(sql)
    # 删除操作
    score = 80
    sql = "delete from class_1 where score<%d" % score
    print(sql)
    cur.execute(sql)
except Exception as e:
    db.rollback()
    print(e)
else:
    db.commit()

cur.close()
db.close()

练习:将单词本存入数据库

创建数据库 dict (utf8)
创建数据表 words 将单词和单词解释分别存入不同的字典里面
将单词存入words单词表

create table dict(id int primary key auto_increment,word varchar(32),lexicon text);

"""
mysql
pymysql 基本数据库基本流程演示
"""
import re

import pymysql

# 连接数据库
db = pymysql.connect(
    host='localhost',
    port=3306,
    password='123456',
    user='root',
    database='stu',
    charset='utf8mb4',
)

# 获取游标 (操作数据库,执行sql语句)
cur = db.cursor()

# 写数据库
try:
    k = 0

    with open('dict.txt')as f:
        for result in f:
            k += 1
            # word = result.split(' ')[0]
            # lexicon = result[len(word):].strip()
            p = """
            (S+) #1个及以上 非空的字符
            s+ #1个及以上 空字符
            (.*) #表示任何东西
            """ 
            # 解释 : findall 结果是元组 元组的个数 等于 () 的个数 
            # p里面有2个括号所以结果就是2个
            # print(result)
            reg = re.compile(p, re.X)
            tuple_ = reg.findall(result)[0]
            sql = "insert into dict values (null,%s,%s)"
            cur.execute(sql, tuple_)
    print(k)
except Exception as e:
    db.rollback()
    print(e)
else:
    pass
    db.commit()

cur.close()
db.close()

插入图片

# 先添加一个 二进制的 字段
alter table class_1 add image longblob

sql = "update class_1 set  image=%s where id=1"
f = open(r'icodesk.ico','rb')
cur.execute(sql, [f.read(),])

读取图片

sql = "select image from class_1 where id = 1"
cur.execute(sql)
data = cur.fetchone()
print(data)
with open('ico.ico','wb')as f:
    f.write(data[0])

最终练习登录退出练习:

编写一个登录程序模拟注册和登录的过程

创建一个user表,包含用户名和密码字段
应用程序模拟注册和登录功能
1. 注册则输入用户名密码将用户名密码存入到数据库用户名不能重复
2. 登录则进行数据库比对,如果有该用户则打印登录成功否则让重新输入

完成:

创建一个user表,包含用户名和密码字段

create table l_user(id int primary key auto_increment,user varchar(32),password varchar(32));
# 我不小心写错了字段名称
#修改字段
alter table l_user change user username varchar(32);

"""
注册和登录演示
"""

import pymysql

# 连接数据库
db = pymysql.connect(
    host='localhost',
    port=3306,
    password='123456',
    user='root',
    database='stu',
    charset='utf8mb4',
)

cur = db.cursor()
while True:
    print("""n
    *****欢迎来到tom的登录系统****
    ******请输入对应的命令玩******
    *****1表示登录,2表示注册******n
    """)
    result_str = input('请输入:')
    try:
        if not result_str:
            print('游戏结束,欢迎再次光临!')
            break
        if result_str == '2':
            result_usename = input('请输入用户名:')
            sql = "select * from l_user where username = %s"
            cur.execute(sql, [result_usename, ])
            if cur.fetchone():
                print(result_usename, ':账号已经存在')
            else:
                result_password = input('请输入密码:')
                sql = "insert into l_user values(null,%s,%s)"
                cur.execute(sql, [result_usename, result_password])
                db.commit()
                print('插入成功,请记住你的账号密码:t%st%s' % (result_usename, result_password))
        elif result_str == '1':
            result_usename = input('请输入用户名:')
            result_password = input('请输入密码:')
            sql = "select * from l_user where username = %s and password = %s"
            cur.execute(sql, [result_usename, result_password])
            if cur.fetchone():
                print('恭喜您登录成功!')
            else:
                print('账号或密码错误')
        else:
            print('命令有误!!!!!!n请按提示输入命令')


    except Exception as e:
        print(e)
        db.rollback()

cur.close()
db.close()

python mysql

Python相关栏目本月热门文章