Django web开发(二) - Mysql数据库
短信预约 -IT技能 免费直播动态提醒
文章目录
Mysql数据库
Mysql的安装(CentOS7)
以Mysql5.7.31版本为例
下载
这里为了方便演示,使用
yum进行下载(其他系统的请使用自己对应的安装命令,在Windows或者MacOS上安装,请去官网下载二进制安装包),不进行源码编译安装
cat > /etc/yum.repos.d/mysql57.repo < set password=password('Syz123!@#');# 如果日志内容报错[ERROR]/usr/bin/mysql_upgrade -u root -p --forceEnter password: ### 设定密码 修改配置文件
修改Mysql的数据存储目录datadir
[mysqld]datadir=/usr/local/mysql/datasocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid创建目录并重启Mysql服务
mkdir -p /usr/local/mysql/datasystemctl restart mysql[root@hecs-33592 ~]# ll /usr/local/mysql/datatotal 122940-rw-r----- 1 mysql mysql 56 Dec 9 13:45 auto.cnf-rw------- 1 mysql mysql 1676 Dec 9 13:45 ca-key.pem-rw-r--r-- 1 mysql mysql 1112 Dec 9 13:45 ca.pem-rw-r--r-- 1 mysql mysql 1112 Dec 9 13:45 client-cert.pem-rw------- 1 mysql mysql 1680 Dec 9 13:45 client-key.pem-rw-r----- 1 mysql mysql 436 Dec 9 13:45 ib_buffer_pool-rw-r----- 1 mysql mysql 12582912 Dec 9 13:46 ibdata1-rw-r----- 1 mysql mysql 50331648 Dec 9 13:46 ib_logfile0-rw-r----- 1 mysql mysql 50331648 Dec 9 13:45 ib_logfile1-rw-r----- 1 mysql mysql 12582912 Dec 9 13:46 ibtmp1drwxr-x--- 2 mysql mysql 4096 Dec 9 13:45 mysqldrwxr-x--- 2 mysql mysql 4096 Dec 9 13:45 performance_schema-rw------- 1 mysql mysql 1680 Dec 9 13:45 private_key.pem-rw-r--r-- 1 mysql mysql 452 Dec 9 13:45 public_key.pem-rw-r--r-- 1 mysql mysql 1112 Dec 9 13:45 server-cert.pem-rw------- 1 mysql mysql 1680 Dec 9 13:45 server-key.pemdrwxr-x--- 2 mysql mysql 12288 Dec 9 13:45 sysMysql强制重置密码
[root@hecs-33592 ~]# vim /etc/my.cnfskip-grant-tables# 最下面加入这一行[root@hecs-33592 ~]# systemctl restart mysqld[root@hecs-33592 ~]# mysql -uroot -pEnter password:# 直接回车 mysql> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> update user set authentication_string=password('Syz123!@#') where user='root';Query OK, 1 row affected, 1 warning (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 1mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)然后注释掉/etc/my.cnf中的skip-grant-tables
重启mysql
mysql> create database d1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.# 需要重新修改密码mysql> set password=password('Syz123!@#');Query OK, 0 rows affected, 1 warning (0.00 sec)远程可登录
如果要配置Mysql为远程可登录,可作如下操作:
host改为自己的网段
mysql> use mysqlmysql> update user set host='192.168.10.%' where user='root';mysql> flush privileges;数据库管理
- 创建数据库
create database 数据库名字 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;Example
create database d1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;- 删除数据库
drop database 数据库名字;数据表的管理
- 进入数据库
use 数据库名称;- 查看所有数据表
show tables;- 创建表
create table 表名称(列名称 类型,列名称 类型,列名称 类型) default charset=utf8;Example:
create table tb1(id int,name varchar(16) not null,--不允许为空age intnull--允许为空) default charset=utf8;create table tb1(id int,name varchar(16),age int default 3--插入数据时,age列的默认值为3) default charset=utf8;create table tb1(id int primary key,--主键(不允许为空,不允许重复)name varchar(16),age int) default charset=utf8;create table tb1(id int auto_increment primary key,--内部维护,自增name varchar(16),age int) default charset=utf8;一般情况下,我们都会这样写:
create table tb1(id int not null auto_increment primary key,name varchar(16),age int) default charset=utf8; - 删除表
drop table 表名称;查看表结构
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(只有正)create table tb1(id int not null auto_increment primary key,age tinyint--有符号, 取值范围: -128 ~ 127) default charset=utf8;create table tb1(id int not null auto_increment primary key,age tinyint unsigned--无符号, 取值范围: 0 ~ 255) default charset=utf8;- int
有符号, 取值范围: -2147483648 ~ 2147483647(有正有负)无符号, 取值范围: 0 ~ 4294967295(只有正)- bigint
有符号, 取值范围: -9223372036854775808 ~ 9223372036854775807(有正有负)无符号, 取值范围: 0 ~ 18446744073709551615(只有正)- float
- double
- decimal
create table tb1(id int auto_increment primary key,--内部维护,自增name varchar(16),salary decimal(8,2)--一共8位(整数位数+小数点位数), 保留小数点后2位) default charset=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)数据管理
添加数据
- 插入数据
insert into 表名称(字段1, 字段2, ...) values(1, "张三", ...);Example:
insert into 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 |+----+--------+------+删除数据
delete from 表名称;--删除所有数据delete from 表名称 where 条件;--删除指定数据Example:
delete from tb1 where id = 1;delete from tb1 where id = 1 and name = "张三";delete from tb1 where id = 1 or id = 100;delete from tb1 where id > 100;delete from tb1 where id != 50;delete from 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+10 where name=""李四;员工管理
命令实现
使用Mysql内置工具(命令)
- 创建数据库: unicom
- 创建数据表: admin
- 表名称: admin
- 列:
- id 整型 自增 主键
- username: 字符串 不为空
- password: 字符串 不为空
- mobile: 字符串 不为空
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || d1 || mysql || performance_schema || sys || unicom |+--------------------+6 rows in set (0.00 sec)mysql> use unicomDatabase changedmysql> create table admin ( -> id int auto_increment primary key, -> username varchar(30) not null, -> password varchar(30) not null, -> mobile varchar(20) not null) default charset=utf8;Query OK, 0 rows 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 | |+----------+-------------+------+-----+---------+----------------+4 rows in set (0.00 sec)Python管理数据库
添加数据
代码实现
Python 代码实现:
- 添加用户
- 删除用户
- 查看用户
- 更新用户信息
安装pymysql包
pip3 install pymysql编辑python文件
#!/usr/bin/env python3import pymysql# 1.连接Mysqlconn = 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 |+----+----------+----------+-------------+1 row in set (0.00 sec)优化
#!/usr/bin/env python3import pymysql# 1.连接Mysqlconn = 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.连接Mysqlconn = 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.连接Mysqlconn = 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.连接Mysqlconn = 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, requestimport pymysqlapp = Flask(__name__)@app.route("/add/user", methods=['GET', 'POST'])def addUser(): 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)编写一个简单的前端页面添加数据
Document 添加用户
mysql> select * from admin;+----+----------+----------+-------------+| id | username | password | mobile |+----+----------+----------+-------------+| 3 | poker | 123456 | 12332145665 || 5 | roker | 123456 | 4563112345 |+----+----------+----------+-------------+案例: 查询所有用户
main.py
from flask import Flask, render_template, requestimport pymysqlapp = Flask(__name__)@app.route("/add/user", methods=['GET', 'POST'])def addUser(): 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'])def showUser(): 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文件
Document 用户列表
ID 姓名 密码 手机号 {% for item in data_list %} {{ item.id }} {{ item.username }} {{ item.password }} {{ item.mobile }} {% endfor %}
优化
加入
bootstrap.css
Document 用户列表
ID 姓名 密码 手机号 {% for item in data_list %} {{ item.id }} {{ item.username }} {{ item.password }} {{ item.mobile }} {% endfor %}
来源地址：https://blog.csdn.net/qq_43139145/article/details/128250617
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/45.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
