windows下mysql5.7安装
mysql5.7官方下载：https://www.mysql.com/
可参考教程：https://blog.csdn.net/qq_39715000/article/details/123534326?
注意：一：
my.ini配置文件：如果保存目录以t开头，默认会将t转义为空格(解决方法加这个 \\)：
[mysqld]
#端口号
port = 3306
#mysql-5.7.27-winx64的路径
basedir=D:\toos\MySQL\mysql-5.7.42-winx64
#mysql-5.7.27-winx64的路径+\data
datadir=D:\\toos\\MySQL\\mysql-5.7.42-winx64\\data
#skip-grant-tables
#编码
character-set-server=utf8
default-storage-engine=INNODB
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysql]
#编码
default-character-set=utf8
二：
初始化有data目录：
命令：mysqld --initialize-insecure
用法
创建数据库
create database 数据库名
删除数据库
drop database 数据库名
查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| study |
| sys |
+--------------------+
5 rows in set (0.00 sec)
创建表
mysql> create table tb1(
-> id bigint auto_increment primary key,
-> salary int,
-> age tinyint
-> ) default charset=utf8;
Query OK, 0 rows affected (0.32 sec)
查看表结构
mysql> desc tb1;
+--------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| salary | int(11) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+--------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
给表中插入数据
mysql> insert into tb1(salary,age) values(10000,18);
Query OK, 1 row affected (0.12 sec)
mysql> insert into tb1(salary,age) values(20000,19);
Query OK, 1 row affected (0.03 sec)
mysql> insert into tb1(salary,age) values(20000,21),(30000,22);
Query OK, 2 rows affected (0.03 sec)
查看数据
mysql> select * from tb1;
+----+--------+------+
| id | salary | age |
+----+--------+------+
| 1 | 10000 | 18 |
| 2 | 20000 | 19 |
| 3 | 20000 | 21 |
| 4 | 30000 | 22 |
+----+--------+------+
4 rows in set (0.10 sec)
mysql>
删除表内数据：
delete from 表名;
delete from 表名 where 条件;
删除表
mysql> drop table test1;
Query OK, 0 rows affected (0.18 sec)
python操作数据库
创建数据
# 1 连接数据库
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='你的密码', charset='utf8', db='study')
cursor = conn.cursor()
# 2 发送指令
cursor.execute('insert into tb1 (salary, age) values (1000, 55)')
conn.commit()
# 3 关闭连接
cursor.close()
conn.close()
# 或者
db = pymysql.connect(host="43.139.184.232", port=3306, user="python4fcf6", password="xxxxx", db="python4fcf6",
charset='utf8')
cursor = db.cursor() # 游标 能获得连接的游标，这个游标可以用来执行SQL查询
sql = "INSERT INTO gpt_new (ip, time, question, answer) VALUE (%s, %s, %s, %s)"
gpt_data = (ip, self.__time, self.__msg, info)
with contextlib.suppress(Exception):
cursor.execute(sql, gpt_data) # 执行sql语句
db.commit() # 提交至数据库
# 关闭光标对象
cursor.close()
# 关闭连接
db.close()
查询数据
import pymysql
# 1 连接数据库
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='xxxx', charset='utf8', db='study')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 加这个输出方式为字典，不加默认元组输出
# 2 执行查询指令
sql = "select * from tb1"
cursor.execute(sql)
data_list = cursor.fetchall() # 获取数据 如果要获取符合条件的第一条数据，fetchone即可
print(data_list)
# 3 关闭连接
cursor.close()
conn.close()
删除数据
import pymysql
# 1 连接数据库
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='xxxx', charset='utf8', db='study')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 加这个输出方式为字典，不加默认元组输出
# 2 删除
cursor.execute("delete from tb1 where id = %s", [3, ])
conn.commit()
# 3 关闭连接
cursor.close()
conn.close()
修改数据
import pymysql
# 1 连接数据库
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='xxx', charset='utf8', db='study')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 加这个输出方式为字典，不加默认元组输出
# 2 修改
cursor.execute("update tb1 set salary=%s where id=%s", [20000, 5])
conn.commit()
# 3 关闭连接
cursor.close()
conn.close()
总结：在进行 新增、删除、修改时，需要加
conn.commit()，否则数据库无数据 查询时，不需要，单，需执行
fetchall/fetchone
