MySQL增删改查和修改
数据库是一个存储数据的仓库,主要用在:金融机构、游戏网站、购物网站、论坛网站,现在的主流数据库有:mysql、sql_server、oracle、mariadb、db2、mongodb ...
那么我们在生产环境中,如何选择使用哪个数据库
1. 是否开源
- 开源软件:mysql、mariadb、mongodb
- 商业软件:oracle、db2、sql_server
2. 是否跨平台
- 不跨平台:sql_server
- 跨平台:mysql、mariadb、mongodb、db2、oracle
3. 公司的类型
- 商业软件:*部门、金融机构
- 开源软件:游戏网站、购物网站、论坛网站... ...
mysql的特点
关系型数据库,关系型数据库的特点
- 数据是以行和列的形式去存储的
- 这一系列的行和列称为表
- 表中的每一行叫一条记录
- 表中的每一列叫一个字段
- 表和表之间的逻辑关联叫关系
关系型数据库存储:二维表
姓名 | 年龄 | 班级 |
牛郎 | 25 | aid1803 |
织女 | 23 | aid1801 |
2、非关系型数据库中存储:键值对 {"姓名":"牛郎","年龄":25,"班级":"aid1803","班主任":"卢大大"}
跨平台:可以在unix、linux、windows上运行数据库服务
支持多种编程语言:python、java、php ... ...
mysql的安装
ubuntu安装mysql服务 redhat(红帽)、centos、ubuntu
- 安装服务端:sudo apt-get install mysql-server
-
安装客户端:sudo apt-get install mysql-client
- 配置文件:/etc/mysql
- 命令集:/use/bin
- 数据库存储目录:/varlib/mysql
windows安装mysql服务
- 下载mysql安装包(windows):mysql-installer***5.7.***.msi
- 双击、按照教程安装即可
最好安装msi版本,不要装逼去安装解压包,你自己又不会调。
启动和连接mysql服务
1. 服务端启动
查看mysql服务状态:sudo /etc/init.d/mysql status
停止、启动、重启mysql服务:sudo /etc/init.d/mysql stop | start | restart
2. 客户端连接
mysql -h主机地址 -u用户名 -p密码
mysql -hlocalhost -uroot -p123456
本地连接可以省略 -h 选项
mysql -u用户名 -p密码
mysql -uroot -p123456
3. 退出
exit 或者 ctrl+z 或者 ctrl+d
基本sql命令
每条sql命令必须以分号;结尾, sql命令关键字不区分字母大小写, 使用 \c 来终止命令的执行 (linux中 ctrl + c), 所有的数据都是以文件的形式存放在数据库目录下, 数据库目录:/var/lib/mysql
数据库操作
查看已有的库:show databases;
创建库(指定字符集):create database 库名 [character set utf8];
e.g. 创建stu数据库,编码为utf8
create database stu character set utf8;
create database stu charset=utf8;
查看创建库的语句(字符集):show create database 库名;
e.g. 查看stu创建方法:show create database stu;
查看当前所在库:select database();
切换库:use 库名;
e.g. 使用stu数据库:use stu;
删除库:drop database 库名;
删除test数据库:drop database test;
库名的命名规则
- 数字、字母、下划线,但不能使用纯数字
- 库名区分字母大小写
- 不能使用特殊字符和mysql关键字
数据表的格式
1. 表结构设计初步
- 分析存储内容
- 确定字段构成
- 设计字段类型
2. 数据类型
- 整数类型(精确值) - int, integer, smallint, tinyint, mediumint, bigint
- 浮点类型(近似值) - float, double
- 定点类型(精确值) - decimal
- 比特值类型 - bit
字符串类型:
- char和varchar类型
- binary和varbinary类型
- blob和text类型
- enum类型和set类型
char 和 varchar
- char: 定长,效率高,一般用于固定长度的表单提交数据存储,默认1字符
- varchar: 不定长,效率偏低
text 和blob
- text: 用来存储非二进制文本
- blob: 用来存储二进制字节串
enum 和 set
- enum: 用来存储给出的一个值
- set: 用来存储给出的值中一个或多个值
表的基本操作
创建表(指定字符集)
create table 表名(
字段名 数据类型,
字段名 数据类型,
......
字段名 数据类型);
- 如果你想设置数字为无符号则加上 unsigned
- 如果你不想字段为 null 可以设置字段的属性为 not null, 在操作数据库时如果输入该字段的数据为null ,就会报错。
- 表示设置一个字段的默认值
- auto_increment定义列为自增的属性,一般用于主键,数值会自动加1。
- primary key关键字用于定义列为主键。主键的值不能重复。
mysql> create table class(id int primary key auto_increment, -> name varchar(32) not null, -> age int not null, -> sex enum("w", "m"), -> score float default 0.0); mysql> create table interest( -> id int primary key auto_increment, -> name varchar(32) not null, -> hobby set("sing", "dance", "draw"), -> price decimal(7,2), -> level char not null, -> comment text);
查看数据表 show tables;
查看已有表的字符集 show create table 表名;
查看表结构 desc 表名;
删除表 drop table 表名;
数据基本操作
插入(insert)
insert into 表名 values (记录1),(记录2),...; insert into 表名(字段1,字段2...) values(记录1),...;
insert into class_1 values (2,'baron',10,'m',91),(3,'jame',9,'m',90);
查询(select)
select * from 表名 [where 条件];
select 字段1,字段名2 from 表名 [where 条件];
select * from class_1; select name,age from class_1;
where子句
where子句在sql语句中扮演了重要角色,主要通过一定的运算条件进行数据的筛选
mysql 主要有以下几种运算符:
- 算术运算符
- 比较运算符
- 逻辑运算符
- 位运算符
算数运算符
运算符 | 作用 |
+ | 加法 |
- | 减法 |
* | 乘法 |
/ 或 div | 除法 |
% 或 mod | 取余 |
select * from class_1 where age % 2 = 0;
比较运算符
符号 | 描述 |
= | 等于 |
<>,!= | 不等 |
> | 大于 |
< | 小于 |
<= | 小于等于 |
>= | 大于等于 |
between 10 and 20 | 在10-20两值之间 |
not betwen 10 and 20 | 不在10-20两值之间 |
in (16,17) | 在集合(16,17) |
not in (16,17) | 不在集合(16,17) |
<=> | 严格比较两个null值是否相等 |
link | 模糊匹配 |
regexp 或 rlike | 正则匹配 |
is null | 为空 |
is not null | 不为空 |
select * from class_1 where age > 8; select * from class_1 where between 8 and 10; select * from class_1 where age in (8,9);
逻辑运算符
运算符号 | 作用 |
not 或 1 | 逻辑非 |
and | 逻辑与 |
or | 逻辑或 |
xor | 逻辑异或 |
select * from class_1 where sex='m' and age>9;
练习:
1. 创建收据库 grade
create database grade charset=utf8;
2. 数据库中创建表 student
3. 表字段如下:id name age hobby score comment
mysql> use grade; mysql> create table student ( -> id int primary key auto_increment, -> name varchar(32), -> age int, -> hobby set('football','basketball','computer','running'), -> score float, -> comment text);
4. 插入若干收据
- age: 4--16
- score: 0--100
- hobby: football computer running basketball
insert student into values (1,"小高",8,"basketball,computer",87.5,"ok"); insert student into values (2,"小红",8,"football",87.5,"ok"); insert student into values (3,"小明",16,"running",90,"ok"); insert student into values (2,"小亮",8,"computer",64.7,"ok");
5.查找
- 查找所有年龄不到10岁或则大于14岁的同学 select * from student where age
- 查找兴趣爱好中包含computer的同学
- 查找年龄大于等于15又喜欢足球的同学
- 查找不及格兴趣爱好又不为空的同学 select * from student where score<60 and hobby is not null;
- 查找成绩大于90分的所有同学,只看姓名和成绩 select name,score from student where score >90;
更新表记录(update)
update 表名 set 字段1=值1,字段2=值2,... where 条件;
update class_1 set age=11 where name='abby';
删除表记录(delete)
delete from 表名 where 条件;
注意: delete语句后如果不加where条件,所有记录全部清空
delete from class_1 where name='abby';
字段 操作(alter)
语法 : alter table 表名 执行动作;
* 添加字段(add)
alter table 表名 add 字段名 数据类型;
alter table 表名 add 字段名 数据类型 first; # 增加到第一个位置
alter table 表名 add 字段名 数据类型 after 字段名; # 增加到某一个字段名后面
alter table interest add data cha(10); alter table interest add data cha(10) first; alter table interest add date date cha(10) after course;
* 删除字段(drop)
alter table 表名 drop 字段名;
* 修改字段类型(modify)
alter table 表名 modify 字段名 新数据类型;
* 修改字段名(change)
alter table 表名 change 旧字段名 新字段名 新数据类型;
alter table class change sex gender enum("m","w");
* 表 重命名(rename)
alter table 表名 rename 新表名;
alter table class rename chass_1;
时间类型数据
类型 |
大小 (字节) |
格式 | 用途 |
---|---|---|---|
date | 3 | yyyy-mm-dd | 日期值 |
time | 3 | hh:mm:ss | 时间值或持续时间 |
year | 1 | yyyy | 年份值 |
datetime | 8 | yyyy-mm-dd hh:mm:ss | 混合日期和时间值 |
timestamp | 4 | yyyy-mm-dd hh:mm:ss | 混合日期和时间值,时间戳 |
注意
- datetime :不给值默认返回null值
- timestamp :不给值默认返回系统当前时间
日期时间函数
- now() 返回服务器当前时间
- curdate() 返回当前日期
- curtime() 返回当前时间
- date(date) 返回指定时间的日期
- time(date) 返回指定时间的时间
查找操作
select * from timelog where date = "2018-07-02"; select * from timelog where date>="2018-07-01" and date<="2018-07-31";
日期时间运算
- 语法格式
select * from 表名 where 字段名 运算符 (时间-interval 时间间隔单位);
时间间隔单位: 1 day | 2 hour | 1 minute | 2 year | 3 month
# 一天前的数据 select * from timelog where shijian > (now()-interval 1 day);
高级查询语句
模糊查询和正则查询
like用于在where子句中进行模糊查询, sql like 子句中使用百分号 %字符来表示任意字符。
使用 like 子句从数据表中读取数据的通用语法:
select field1, field2,...fieldn from table_name where field1 like condittion1
mysql> select * from class_1 where name like 'a%';
mysql中对正则表达式的支持有限,只支持部分正则元字符
select field1, field2,...fieldn from table_name where field1 regexp condition1
e.g.
select * from class_1 where name regexp 'b.+';
排序
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;
分页
limit 子句用于限制由 select 语句返回的数据数量 或者 update, delete语句的操作数量带有 limit 子句的 select 语句的基本语法如下:
select column1, column2, columnn from table_name where field limit [num]
联合查询
union 操作符用于连接两个以上的 select 语句的结果组合到一个结果集合中。多个 select 语句会删除重复的数据。union 操作符语法格式:
select expression1,... expression_n from tables [where conditions] union [all | distinct] select expression1,... 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;
多表查询
多个表数据可以联合查询,语法格式如下
select 字段1,字段2... from 表1,表2... [where 条件]
select class_1.name,class_1.age,class_1.sex,interest.hobby from class_1,interest where class_1.
数据备份
1. 备份命令格式
mysqldump -u用户名 -p 源库名 > ~/***.sql
--all-databases 备份所有库
库名 备份单个库
-b 库1 库2 库3 备份多个库
库名 表1 表2 表3 备份指定库的多张表
2. 恢复命令格式
mysql -uroot -p 目标库名 < ***.sql
从所有库备份中恢复某一个库(--one-database)
mysql -uroot -p --one-database 目标库名 < all.sql
python操作mysql数据库
pymysql安装:pip install pymysql
使用pymysql之前都要手动的创建数据库,以及表.
pymysql使用流程
- 建立数据库连接 db = pymysql.connect(...)
- 创建游标对象 c = db.cursor()
- 游标方法: c.execute("insert ....")
- 提交到数据库 : db.commit()
- 关闭游标对象 : c.close()
- 断开数据库连接 : db.close()
常用函数
db = pymysql.connect(参数列表)
参数:
- host :主机地址,本地 localhost
- port :端口号,默认3306
- user :用户名
- password :密码
- database :库
- charset :编码方式,推荐使用 utf8
数据库连接对象(db)的方法
- db.commit() 提交到数据库执行
- db.rollback() 回滚
- cur = db.cursor() 返回游标对象,用于执行具体sql命令
- db.close() 关闭连接
游标(cursor)的方法
- cur.execute(sql命令,[列表]) 执行sql命令
- cur.close() 关闭游标对象
- cur.fetchone() 获取查询结果集的第一条数据
- cur.fetchmany(n) 获取n条 ((记录1),(记录2))
- cur.fetchall() 获取所有记录
写数据
import pymysql # 连接数据库 db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='stu', charset='utf8') cur = db.cursor() # 获取游标(操作数据库,执行sql语句) sql = "insert into class_1 values (7,'emma',17,'w',76.5,'2019-8-8');" # 执行sql语句 cur.execute(sql) # 执行sql语句 db.commit() # 将"写操作"一同提交;读操作不用提交 cur.close() # 关闭浮标 db.close() # 关闭数据库
查询数据(读数据)
import pymysql # 连接数据库 db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='stu', charset='utf8') cur = db.cursor() # 获取游标 (操作数据库,执行sql语句) # 获取数据库数据 sql = "select name,age from class_1 where gender='m';" cur.execute(sql) # 执行正确后cur调用函数获取结果 one_row = cur.fetchone() # 获取一个查询结果 print(one_row) # 元组 many_row = cur.fetchmany(2) # 获取2个查询结果 print(many_row) all_row = cur.fetchall() # 获取所有查询结果 print(all_row) cur.close() # 关闭游标 db.close() # 关闭数据库
二进制文件存储
import pymysql # 连接数据库 db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='stu', charset='utf8') cur = db.cursor() # 获取游标 (操作数据库,执行sql语句) # 存储图片 # with open('image.jpg','rb') as f: # data = f.read() # try: # sql = "update class_1 set image = %s where name='jame';" # cur.execute(sql,[data]) # db.commit() # except exception as e: # db.rollback() # print(e) # 获取图片 sql = "select image from class_1 where name='jame'" cur.execute(sql) data = cur.fetchone() with open('girl.jpg','wb') as f: f.write(data[0]) cur.close() # 关闭游标 db.close() # 关闭数据库
pymysql写操作
import pymysql # 连接数据库 db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='stu', harset='utf8') cur = db.cursor() # 获取游标 (操作数据库,执行sql语句) # 写数据库 try: # 写sql语句执行 # 插入操作 name = input('name:') age = input('age:') score = input('score:') # 将变量插入到sql语句合成最终操作语句 sql = "insert into class_1 (name,age,score) values ('%s',%d,%f)" % (name, age, score) # 或者 # sql = "insert into class_1 (name,age,score) values (%s,%s,%s)" # 可以使用列表直接给sql语句的values 传值 cur.execute(sql,[name,age,score]) #执行 # 修改操作 sql = "update interest set price=11800 where name = 'abby'" cur.execute(sql) sql = "delete from class_1 where score<80" # 删除操作 cur.execute(sql) db.commit() # 提交 except exception as e: db.rollback() # 退回到commit执行之前的数据库状态 print(e) cur.close() # 关闭游标 db.close() # 关闭数据库
字典dict.txt在github上,将词典中的词输入到数据库中的代码
import pymysql import re f = open('dict.txt') # 打开文件 # 连接数据库 db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='dict', charset='utf8') cur = db.cursor() # 获取游标 (操作数据库,执行sql语句) sql = "insert into words (word,mean) values (%s,%s)" for line in f: # 获取单词和解释 tup = re.findall(r"(\s+)\s+(.*)", line)[0] # [('a', 'indef art one'), ('abandonment', 'n.abandoning')...] try: cur.execute(sql, tup) db.commit() except: db.rollback() f.close() cur.close() # 关闭游标 db.close() # 关闭数据库
数据库注册登录程序
import pymysql # 连接数据库 db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='stu', charset='utf8') cur = db.cursor() # 获取游标 (操作数据库,执行sql语句) # 注册,判断用户名是否重复 def register(): name = input("用户名:") passwd = input("密 码:") sql = "select * from user where name='%s'" % name cur.execute(sql) result = cur.fetchone() # 获取查询结果集的第一条数据 if result: # 如果用户名存在 return false try: sql = "insert into user (name, passwd) values (%s,%s)" cur.execute(sql, [name, passwd]) db.commit() return true except: db.rollback() # 回滚 return false # 登录 def login(): name = input("用户名:") passwd = input("密 码:") sql = "select * from user where name='%s' and passwd='%s'" % (name, passwd) cur.execute(sql) result = cur.fetchone() # 获取查询结果集的第一条数据 if result: return true while true: print(""" =============== 1.注册 2.登录 ===============""") cmd = input("输入命令:") if cmd == '1': # 执行注册 if register(): print("注册成功") else: print("注册失败") elif cmd == '2': # 执行登录 if login(): print("登录成功") break else: print("登录失败") else: print("我也做不到啊") cur.close() # 关闭浮标 db.close() # 关闭数据库
上一篇: Linux下非交互式提权详解
下一篇: 世人只知卧龙凤雏,司马懿的绰号你知道吗?