0
0

Django web开发(二) - Mysql数据库

文章目录

Mysql数据库

Mysql的安装(CentOS7)

以

Mysql5.7.31

版本为例

下载

这里为了方便演示,使用

yum

进行下载(其他系统的请使用自己对应的安装命令,在Windows或者MacOS上安装,请去官网下载二进制安装包),不进行源码编译安装

cat> /etc/yum.repos.d/mysql57.repo <<EOF
[mysql-5.7-community]
name=MySQL 5.7 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql
EOFrpm--import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022  
yum -yinstall mysql-server 

# 启动mysql
systemctl start mysqld
# 查看默认密码cat /var/log/mysqld.log |grep password

# 登录Mysql
mysql -uroot-pwIu8_wS_nXpf# 修改密码
mysql>setpassword=password('Syz123!@#');# 如果日志内容报错[ERROR]
/usr/bin/mysql_upgrade -u root -p--force
Enter password:             ### 设定密码

修改配置文件

修改Mysql的数据存储目录

datadir

[mysqld]datadir=/usr/local/mysql/data
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

创建目录并重启

Mysql

服务

mkdir-p /usr/local/mysql/data
systemctl restart mysql

[root@hecs-33592 ~]# ll /usr/local/mysql/data
total 122940
-rw-r----- 1 mysql mysql       56 Dec  913:45 auto.cnf
-rw------- 1 mysql mysql     1676 Dec  913:45 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 Dec  913:45 ca.pem
-rw-r--r-- 1 mysql mysql     1112 Dec  913:45 client-cert.pem
-rw------- 1 mysql mysql     1680 Dec  913:45 client-key.pem
-rw-r----- 1 mysql mysql      436 Dec  913:45 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Dec  913:46 ibdata1
-rw-r----- 1 mysql mysql 50331648 Dec  913:46 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Dec  913:45 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Dec  913:46 ibtmp1
drwxr-x--- 2 mysql mysql     4096 Dec  913:45 mysql
drwxr-x--- 2 mysql mysql     4096 Dec  913:45 performance_schema
-rw------- 1 mysql mysql     1680 Dec  913:45 private_key.pem
-rw-r--r-- 1 mysql mysql      452 Dec  913:45 public_key.pem
-rw-r--r-- 1 mysql mysql     1112 Dec  913:45 server-cert.pem
-rw------- 1 mysql mysql     1680 Dec  913:45 server-key.pem
drwxr-x--- 2 mysql mysql    12288 Dec  913:45 sys

Mysql强制重置密码

[root@hecs-33592 ~]# vim /etc/my.cnf
skip-grant-tables    # 最下面加入这一行[root@hecs-33592 ~]# systemctl restart mysqld[root@hecs-33592 ~]# mysql -uroot -p
Enter password:        # 直接回车 

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user setauthentication_string=password('Syz123!@#') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

然后注释掉

/etc/my.cnf

中的

skip-grant-tables

重启mysql

mysql>createdatabase d1 DEFAULTCHARSET utf8 COLLATE utf8_general_ci;
ERROR 1820(HY000): You must reset your password usingALTERUSER statement before executing this statement.# 需要重新修改密码
mysql>set password=password('Syz123!@#');
Query OK,0rows affected,1 warning (0.00 sec)

远程可登录

如果要配置Mysql为远程可登录,可作如下操作:

host改为自己的网段

mysql> use mysql
mysql> update user sethost='192.168.10.%' where user='root';
mysql> flush privileges;

数据库管理

  • 创建数据库
createdatabase 数据库名字 DEFAULTCHARSET utf8 COLLATE utf8_general_ci;

Example

createdatabase d1 DEFAULTCHARSET utf8 COLLATE utf8_general_ci;
  • 删除数据库
dropdatabase 数据库名字;

数据表的管理

  • 进入数据库
use 数据库名称;
  • 查看所有数据表
showtables;
  • 创建表
createtable 表名称(
    列名称 类型,
    列名称 类型,
    列名称 类型
)defaultcharset=utf8;

Example:

createtable tb1(
    id int,
    name varchar(16)notnull,--不允许为空
    age intnull--允许为空)defaultcharset=utf8;

createtable tb1(
    id int,
    name varchar(16),
    age intdefault3--插入数据时,age列的默认值为3)defaultcharset=utf8;

createtable tb1(
    id intprimarykey,--主键(不允许为空,不允许重复)
    name varchar(16),
    age int)defaultcharset=utf8;

createtable tb1(
    id intauto_incrementprimarykey,--内部维护,自增
    name varchar(16),
    age int)defaultcharset=utf8;

一般情况下,我们都会这样写:

createtable tb1(
    id intnotnullauto_incrementprimarykey,
    name varchar(16),
    age int)defaultcharset=utf8;
  • 删除表
droptable 表名称;

查看表结构

mysql>desc tb1;+-------+-------------+------+-----+---------+----------------+| Field |Type|Null|Key|Default| Extra          |+-------+-------------+------+-----+---------+----------------+| id    |int(11)|NO| PRI |NULL|auto_increment|| name  |varchar(16)| YES  ||NULL||| age   |int(11)| YES  ||NULL||+-------+-------------+------+-----+---------+----------------+

常用数据类型

  • tinyint
有符号, 取值范围: -128 ~ 127(有正有负)
无符号, 取值范围: 0 ~ 255(只有正)

createtable tb1(
    id intnotnullauto_incrementprimarykey,
    age tinyint--有符号, 取值范围: -128 ~ 127)defaultcharset=utf8;

createtable tb1(
    id intnotnullauto_incrementprimarykey,
    age tinyintunsigned--无符号, 取值范围: 0 ~ 255)defaultcharset=utf8;
  • int
有符号, 取值范围: -2147483648 ~ 2147483647(有正有负)
无符号, 取值范围: 0 ~ 4294967295(只有正)
  • bigint
有符号, 取值范围: -9223372036854775808 ~ 9223372036854775807(有正有负)
无符号, 取值范围: 0 ~ 18446744073709551615(只有正)
  • float
  • double
  • decimal
createtable tb1(
    id intauto_incrementprimarykey,--内部维护,自增
    name varchar(16),
    salary decimal(8,2)--一共8位(整数位数+小数点位数), 保留小数点后2位)defaultcharset=utf8;
  • char
定长字符串, 默认固定用 11 个字符串进行存储,哪怕字符串个数不足,也按照11个字符存储
最多能存储255个字节的数据
查询效率高
  • varchar
变长字符串,默认最长 11 个字符,真实数据多长就按多长存储
最多能存储 65535 个字节的数据,中文可存储 65535/3 个汉字
相对 char 类型,查询效率低
  • text
保存变长的大字符串,可以最多到 65535 个字符
一般用于文章和新闻
  • mediumtext
  • longtext
  • datetime
YYYY-MM-DD HH:MM:SS (1000-01-01 00:00:00/9999-12-31 23:59:59)
  • date
YYYY-MM-DD (1000-01-01/9999-12-31)

数据管理

添加数据

  • 插入数据
insertinto 表名称(字段1, 字段2,...)values(1,"张三",...);

Example:

insertinto tb1(name,age)values("张三",25);

查询数据

select 字段名(或者*)from 表名称;select 字段名(或者*)from 表名称 where 条件;

Example:

mysql>select*from tb1;+----+--------+------+| id | name   | age  |+----+--------+------+|1| 张三   |25|+----+--------+------+

mysql>select name from tb1;+--------+| name   |+--------+| 张三   |+--------+

mysql>select*from tb1 where id =1;+----+--------+------+| id | name   | age  |+----+--------+------+|1| 张三   |25|+----+--------+------+

删除数据

deletefrom 表名称;--删除所有数据deletefrom 表名称 where 条件;--删除指定数据

Example:

deletefrom tb1 where id =1;deletefrom tb1 where id =1and name ="张三";deletefrom tb1 where id =1or id =100;deletefrom tb1 where id >100;deletefrom tb1 where id !=50;deletefrom tb1 where id in(10,15);

修改数据

update 表名称 set 列 = 值;--修改一列update 表名称 set 列 = 值, 列 = 值;--修改多列update 表名称 set 列 = 值 where 条件;--修改某行某列

Example:

update tb1 set name="李四"where id =1;update tb1 set age=age+10where name=""李四;

员工管理

命令实现

使用Mysql内置工具(命令)

  • 创建数据库: unicom
  • 创建数据表: admin - 表名称: admin- 列: - id 整型 自增 主键- username: 字符串 不为空- password: 字符串 不为空- mobile: 字符串 不为空
mysql>showdatabases;+--------------------+|Database|+--------------------+| information_schema || d1                 || mysql              || performance_schema || sys                || unicom             |+--------------------+6rowsinset(0.00 sec)

mysql>use unicom
Database changed
mysql>createtable admin (-> id intauto_incrementprimarykey,-> username varchar(30)notnull,-> password varchar(30)notnull,-> mobile varchar(20)notnull)defaultcharset=utf8;
Query OK,0rows affected (0.01 sec)

mysql>desc admin;+----------+-------------+------+-----+---------+----------------+| Field    |Type|Null|Key|Default| Extra          |+----------+-------------+------+-----+---------+----------------+| id       |int(11)|NO| PRI |NULL|auto_increment|| username |varchar(30)|NO||NULL||| password |varchar(30)|NO||NULL||| mobile   |varchar(20)|NO||NULL||+----------+-------------+------+-----+---------+----------------+4rowsinset(0.00 sec)

Python管理数据库

添加数据

代码实现

Python 代码实现:

  • 添加用户
  • 删除用户
  • 查看用户
  • 更新用户信息

安装pymysql包

pip3 install pymysql

编辑python文件

#!/usr/bin/env python3import pymysql

# 1.连接Mysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='Syz123!@#', charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 2.发送指令
cursor.execute("insert into admin(username, password, mobile) values('poker', '123456', '12345678912');")
conn.commit()# 3.关闭
cursor.close()
conn.close()

运行

/bin/python3 /root/python/Mysql/createData.py

验证

mysql>select*from admin;+----+----------+----------+-------------+| id | username | password | mobile      |+----+----------+----------+-------------+|3| poker    |123456|12345678912|+----+----------+----------+-------------+1rowinset(0.00 sec)

优化

#!/usr/bin/env python3import pymysql

# 1.连接Mysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='Syz123!@#', charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 2.发送指令
sql ="insert into admin(username, password, mobile) values(%s, %s, %s);"
cursor.execute(sql,['toker','123456','12355674325'])
conn.commit()# 3.关闭
cursor.close()
conn.close()

注意: sql语句不要使用字符串格式化,有会SQL注入的风险,需要使用 cursor.execute(sql, [参数1, 参数2, …])

查询数据

#!/usr/bin/env python3import pymysql

# 1.连接Mysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root',
                       passwd='Syz123!@#', charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 2.发送指令
sql ="select * from admin where id > %s"
cursor.execute(sql,[2,])# data_list = cursor.fetchall()        查询一条数据,为字典
data_list = cursor.fetchall()# 查询所有符合条件的数据,为列表套多个
字典
for row_dict in data_list:print(row_dict)# 3.关闭
cursor.close()
conn.close()

输出结果如下

[root@hecs-33592 ~]# /bin/python3 /root/python/Mysql/searchData.py {'id':3, 'username':'poker', 'password':'123456', 'mobile':'12345678912'}{'id':4, 'username':'toker', 'password':'123456', 'mobile':'12355674325'}

删除数据

删除 

id

大于 3 的行

#!/usr/bin/env python3import pymysql

# 1.连接Mysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root',
                       passwd='Syz123!@#', charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 2.发送指令
sql ="delete from admin where id > %s"
cursor.execute(sql,[3,])
conn.commit()# 3.关闭
cursor.close()
conn.close()

修改数据

#!/usr/bin/env python3import pymysql

# 1.连接Mysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root',
                       passwd='Syz123!@#', charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 2.发送指令
sql ="update admin set mobile=%s where id = %s"
cursor.execute(sql,['12332145665',3])
conn.commit()# 3.关闭
cursor.close()
conn.close()

案例: Flask + Mysql

main.py

from flask import Flask, render_template, request
import pymysql

app = Flask(__name__)@app.route("/add/user", methods=['GET','POST'])defaddUser():if request.method =='GET':return render_template("addUser.html")else:
        username = request.form.get('user')
        password = request.form.get('pwd')
        mobile = request.form.get('mobile')# 1.连接Mysql
        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root',
                            passwd='Syz123!@#', charset='utf8', db='unicom')
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 2.发送指令
        sql ="insert into admin(username, password, mobile) values(%s, %s, %s);"
        cursor.execute(sql,[username, password, mobile])
        conn.commit()# 3.关闭
        cursor.close()
        conn.close()return"添加成功"if __name__ =='__main__':
    app.run(host='0.0.0.0', port=5200, debug=True)

编写一个简单的前端页面添加数据

<!DOCTYPEhtml><htmllang="en"><head><metacharset="UTF-8"><title>Document</title></head><body><h1>添加用户</h1><formmethod="post"action="/add/user"><inputtype="text"name="user"placeholder="用户名"><inputtype="text"name="pwd"placeholder="密码"><inputtype="text"name="mobile"placeholder="手机号"><inputtype="submit"value="提 交"></form></body></html>

在这里插入图片描述
在这里插入图片描述

mysql>select*from admin;+----+----------+----------+-------------+| id | username | password | mobile      |+----+----------+----------+-------------+|3| poker    |123456|12332145665||5| roker    |123456|4563112345|+----+----------+----------+-------------+

案例: 查询所有用户

main.py

from flask import Flask, render_template, request
import pymysql

app = Flask(__name__)@app.route("/add/user", methods=['GET','POST'])defaddUser():if request.method =='GET':return render_template("addUser.html")else:
        username = request.form.get('user')
        password = request.form.get('pwd')
        mobile = request.form.get('mobile')# 1.连接Mysql
        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root',
                            passwd='Syz123!@#', charset='utf8', db='unicom')
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 2.发送指令
        sql ="insert into admin(username, password, mobile) values(%s, %s, %s);"
        cursor.execute(sql,[username, password, mobile])
        conn.commit()# 3.关闭
        cursor.close()
        conn.close()return"添加成功"@app.route("/show/user", methods=['GET','POST'])defshowUser():
    username = request.form.get('user')
    password = request.form.get('pwd')
    mobile = request.form.get('mobile')# 1.连接Mysql
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root',
                        passwd='Syz123!@#', charset='utf8', db='unicom')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 2.发送指令
    sql ="select * from admin"
    cursor.execute(sql)
    data_list = cursor.fetchall()# 3.关闭
    cursor.close()
    conn.close()return render_template("showUser.html", data_list=data_list)if __name__ =='__main__':
    app.run(host='0.0.0.0', port=5200, debug=True)

编写HTML文件

<!DOCTYPEhtml><htmllang="en"><head><metacharset="UTF-8"><title>Document</title></head><body><h1>用户列表</h1><tableborder="1"><thead><tr><th>ID</th><th>姓名</th><th>密码</th><th>手机号</th></tr></thead><tbody>
            {% for item in data_list %}
            <tr><td>{{ item.id }}</td><td>{{ item.username }}</td><td>{{ item.password }}</td><td>{{ item.mobile }}</td></tr>
            {% endfor %}
        </tbody></table></body></html>

在这里插入图片描述
优化

加入 

bootstrap.css

<!DOCTYPEhtml><htmllang="en"><head><metacharset="UTF-8"><title>Document</title><linkrel="stylesheet"href="../static/plugins/bootstrap-3.4.1/css/bootstrap.css"></head><body><divclass="container"><h1>用户列表</h1><tableclass="table table-bordered"><thead><tr><th>ID</th><th>姓名</th><th>密码</th><th>手机号</th></tr></thead><tbody>
                {% for item in data_list %}
                <tr><td>{{ item.id }}</td><td>{{ item.username }}</td><td>{{ item.password }}</td><td>{{ item.mobile }}</td></tr>
                {% endfor %}
            </tbody></table></div></body></html>

在这里插入图片描述

标签： mysql 数据库 django
本文转载自: https://blog.csdn.net/qq_43139145/article/details/128250617
版权归原作者 ShangCode 所有， 如有侵权，请联系我们删除。
登录后发布评论

“Django web开发(二) - Mysql数据库”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

【保姆级教程】实操 Linux 磁盘管理：硬盘选型 & 分区挂载

通过visual studio进行dump文件调试和分析

开源低代码平台-Microi吾码-V8函数列表-后端

rabbitMQ

web渗透测试漏洞复现：Springboot Actuator未授权漏洞复现

如何给SaaS应用做安全

【软件测试】自动化常用函数

文章导航