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:\Users\Administrator\Desktop\stu.sql
- 恢复命令格式
mysql -uroot -p 目标库名 < stu.sql
从所有库备份中恢复某一个库(–one-database)mysql -uroot -p --one-database 目标库名 < all.sql
# 插入之前先创建
create database student
# 导入表
mysql -uroot -p student< C:\Users\Administrator\Desktop\stu.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'ico\desk.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表,包含用户名和密码字段
- 应用程序模拟注册和登录功能
- 注册则输入用户名密码将用户名密码存入到数据库 用户名不能重复
- 登录则进行数据库比对,如果有该用户则打印登录成功否则让重新输入
完成:
创建一个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%s\t%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()