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)

其它用法可参考视频：https://www.bilibili.com/video/BV1rT4y1v7uQ?p=29&spm_id_from=pageDriver&vd_source=5fed6e8a7e3ad9f10860bf7a4540ba71

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()