MySQL 使用笔记，python连接，Workbench 使用，ubuntu和windows

1、安装sql和Workbench

参考：https://www.cnblogs.com/QingXiaxu/p/7631661.html

安装MySQL

sudo apt-get install mysql-server

安装Workbench

sudo apt-get install mysql-workbench

2、本地安装数据库

 

3、Workbench使用

3.1连接

 

3.2创建数据库

3.3 卡死之后连接遇到问题

Cannot Connect to Database Server

Your connection attempt failed for user 'root' from your host to server at 
127.0.0.1:3306:Can't connect to mysql server on '127.0.0.1'(10061)

Please:

 1. Check that mysql is running on server 127.0.0.1
 2. Check that mysql is running on port 3306 (note: 3306 is the default, but this can 
    be changed)
 3. Check the root has rights to connect to 127.0.0.1 from your address (mysql rights 
    define what clients can connect to the server and from which machines) 
 4. Make sure you are both providing a password if needed and using the correct 
    password for 127.0.0.1 connecting from the host address you're connecting from

解决方法：

参考：https://stackoverflow.com/questions/7864276/cannot-connect-to-database-server-mysql-workbench

终端登陆数据库

在数据库输入

然后在Workbench会需要重新输入密码

划重点 密码是：password 划重点

3.4 新建表

选中数据库，然后右键，选择 “Set as default schmas” 或者双机，数据库会变成黑粗

PK: primary key (column is part of a pk)  主键

 NN: not null (column is nullable)  是否为空

-UQ: unique (column is part of a unique key)  外键

 AI: auto increment (the column is auto incremented when rows are inserted)  自动增加

  BIN: binary (if dt is a blob or similar, this indicates that is binary data, rather than text)   二进制

 UN: unsigned (for integer types, see docs: “10.2. Numeric Types”)

- ZF: zero fill (rather a display related flag, see docs: “10.2. Numeric Types”)

选择apply之后就创建成功了

将鼠标放到btable这个表上，表右侧会出现三个按钮，选择最右的，打开表

在里面填充内容之后，，点击右下角的apply，发现无法提交并且报错

然后改称英文再次提交，提交成功

4、python连接数据库

参考：https://www.runoob.com/python/python-mysql.html

推荐使用包 MySQLdb

参考：https://blog.csdn.net/samxx8/article/details/6285013

直接命令安装,但是由于是sudo按住嗯的，所以安装在系统环境中，

虚拟环境人就无法打开

参考：https://blog.csdn.net/csdn_______/article/details/84246712

直接在虚拟环境中pip Mysqlclient

安装成功了，然后就是连接数据库

 

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "root", "password", "bbb", charset='utf8' )

# 使用cursor()方法获取操作游标
cursor = db.cursor()

# 使用execute方法执行SQL语句
cursor.execute("SELECT * from btable")

# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()

print(data)

4.1第二次安装MySQLdb报错，无法安装上

执行pip3 install mysqlclient之后报错

    ERROR: Complete output from command python setup.py egg_info:
    ERROR: /bin/sh: 1: mysql_config: not found
    Traceback (most recent call last):
      File "<string>", line 1, in <module>
      File "/tmp/pip-install-wvamwvp8/mysqlclient/setup.py", line 16, in <module>
        metadata, options = get_config()
      File "/tmp/pip-install-wvamwvp8/mysqlclient/setup_posix.py", line 51, in get_config
        libs = mysql_config("libs")
      File "/tmp/pip-install-wvamwvp8/mysqlclient/setup_posix.py", line 29, in mysql_config
        raise EnvironmentError("%s not found" % (_mysql_config_path,))
    OSError: mysql_config not found
    ----------------------------------------
ERROR: Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-install-wvamwvp8/mysqlclient/

参考：https://www.jianshu.com/p/d31929bda86e

进入虚拟环境之后执行

sudo apt-get install python3-dev libmysqlclient-dev

然后执行安装

pip install mysqlclient

 

5、windows系统

5.1 下载一个navicat for mysql

地址：https://www.xpgod.com/soft/6690.html#onxzdz

5.2 在python中安装一个pymysql

然后就可以跟ubuntu一样使用了

 

6、错误

1）(1264, "Out of range value for column 'IMAGE_NAME' at row 1")

安装报错解决方法

https://blog.csdn.net/lq497028254/article/details/90409930