MySQL增删改查和修改

程序员文章站 2022-04-23 08:08:51

数据库是一个存储数据的仓库，主要用在：金融机构、游戏网站、购物网站、论坛网站，现在的主流数据库有：MySQL、SQL_Server、Oracle、Mariadb、DB2、MongoDB ... 那么我们在生产环境中，如何选择使用哪个数据库１. 是否开源开源软件：MySQL、Mariadb、Mon ......

数据库是一个存储数据的仓库，主要用在：金融机构、游戏网站、购物网站、论坛网站，现在的主流数据库有：mysql、sql_server、oracle、mariadb、db2、mongodb ...

那么我们在生产环境中，如何选择使用哪个数据库

１. 是否开源

开源软件：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服务

１. 服务端启动
　　查看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,字段２...) 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 或　１	逻辑非
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()      # 关闭数据库

上一篇：哈尔滨投入5亿元发展云计算上网带宽将提至40M

下一篇： Netty 系列一（核心组件和实例）.

MySQL增删改查和修改

mysql的特点

mysql的安装

启动和连接mysql服务

基本sql命令

数据库操作

数据表的格式

表的基本操作

数据基本操作

插入(insert)

查询(select)

where子句

算数运算符

比较运算符

逻辑运算符

更新表记录(update)

删除表记录(delete)

字段　操作(alter)

时间类型数据

高级查询语句

数据备份

python操作mysql数据库

pymysql使用流程

常用函数

Python操作MySQL数据库实例详解【安装、连接、增删改查等】

Mysql查看最大连接数和修改最大连接数的讲解

php5.4以上 mysqli 实例操作mysql 增,删,改,查

mysql触发器之触发器的增删改查操作示例

Ubuntu查看修改mysql的登录名和密码、安装phpmyadmin

MySQL5.7.20解压版安装和修改root密码的教程

MySQL增删改查语句

python cx_Oracle的基础使用方法(连接和增删改查)

Django 笔记（六）mysql增删改查

Python面向对象之类和对象属性的增删改查操作示例

MySQL增删改查和修改

mysql的特点

mysql的安装

启动和连接mysql服务

基本sql命令

数据库操作

数据表的格式

表的基本操作

数据基本操作

插入(insert)

查询(select)

where子句

算数运算符

比较运算符

逻辑运算符

更新表记录(update)

删除表记录(delete)

字段 操作(alter)

时间类型数据

高级查询语句

数据备份

python操作mysql数据库

pymysql使用流程

常用函数

Python操作MySQL数据库实例详解【安装、连接、增删改查等】

Mysql查看最大连接数和修改最大连接数的讲解

php5.4以上 mysqli 实例操作mysql 增,删,改,查

mysql触发器之触发器的增删改查操作示例

Ubuntu查看修改mysql的登录名和密码、安装phpmyadmin

MySQL5.7.20解压版安装和修改root密码的教程

MySQL增删改查语句

python cx_Oracle的基础使用方法(连接和增删改查)

Django 笔记（六）mysql增删改查

Python面向对象之类和对象属性的增删改查操作示例

字段　操作(alter)