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

一时失言乱红尘 2022-01-25 16:57 469阅读 0赞

## 1、安装sql和Workbench ##

参考：[https://www.cnblogs.com/QingXiaxu/p/7631661.html][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创建数据库 ##

## ![20190530094656693.png][] ##

## ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RpYW5hX1o_size_16_color_FFFFFF_t_70][] ##

## ![20190530094739279.png][] ##

## 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][https_stackoverflow.com_questions_7864276_cannot-connect-to-database-server-mysql-workbench]

终端登陆数据库

![20190530102059596.png][]

在数据库输入

![20190530102124412.png][]

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

### 划重点 密码是：password 划重点 ###

3.4 新建表

![20190530102308199.png][]

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

![20190530102359205.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RpYW5hX1o_size_16_color_FFFFFF_t_70 1][]

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

![20190530102820153.png][]

选择apply之后就创建成功了

![20190530102909429.png][]

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

![20190530103325659.png][]

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

![20190530103407349.png][]

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

# 4、python连接数据库 #

参考：[https://www.runoob.com/python/python-mysql.html][https_www.runoob.com_python_python-mysql.html]

推荐使用包 MySQLdb

参考：[https://blog.csdn.net/samxx8/article/details/6285013][https_blog.csdn.net_samxx8_article_details_6285013]

![20190530104340636.png][]

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

![20190530104607453.png][]

虚拟环境人就无法打开

![20190530104804387.png][]

参考：[https://blog.csdn.net/csdn\_\_\_\_\_\_\_/article/details/84246712][https_blog.csdn.net_csdn_article_details_84246712]

直接在虚拟环境中pip Mysqlclient

![20190530110122972.png][]

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

#!/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)

![20190530110610360.png][]

# 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][https_www.jianshu.com_p_d31929bda86e]

进入虚拟环境之后执行

sudo apt-get install python3-dev libmysqlclient-dev

然后执行安装

pip install mysqlclient

![20190610202758469.png][]

# 5、windows系统 #

## 5.1 下载一个navicat for mysql ##

地址：[https://www.xpgod.com/soft/6690.html\#onxzdz][https_www.xpgod.com_soft_6690.html_onxzdz]

## 5.2 在python中安装一个pymysql ##

![20190530140930764.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RpYW5hX1o_size_16_color_FFFFFF_t_70 2][]

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

# 6、错误 #

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

安装报错解决方法

[https://blog.csdn.net/lq497028254/article/details/90409930][https_blog.csdn.net_lq497028254_article_details_90409930]

[https_www.cnblogs.com_QingXiaxu_p_7631661.html]: https://www.cnblogs.com/QingXiaxu/p/7631661.html
[20190530094656693.png]: /images/20220126/01459336f75e496cafcb3ae6a119f295.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RpYW5hX1o_size_16_color_FFFFFF_t_70]: /images/20220126/a476a137ff56479fa9c298a3cda9b67f.png
[20190530094739279.png]: /images/20220126/58683aff0d9e4475a8888bbb1dbcf623.png
[https_stackoverflow.com_questions_7864276_cannot-connect-to-database-server-mysql-workbench]: https://stackoverflow.com/questions/7864276/cannot-connect-to-database-server-mysql-workbench
[20190530102059596.png]: /images/20220126/b8ed950964204fdb98d65f10afb0843e.png
[20190530102124412.png]: /images/20220126/1fb4e3a5f65d4f0db2c430a31337bd98.png
[20190530102308199.png]: /images/20220126/6f1c84571b4f48689927a67bb7c936aa.png
[20190530102709975.png]: https://img-blog.csdnimg.cn/20190530102709975.png
[20190530102359205.png]: https://img-blog.csdnimg.cn/20190530102359205.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RpYW5hX1o_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20190530102419522.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RpYW5hX1o=,size_16,color_FFFFFF,t_70
[20190530102820153.png]: https://img-blog.csdnimg.cn/20190530102820153.png
[20190530102909429.png]: https://img-blog.csdnimg.cn/20190530102909429.png
[20190530103325659.png]: https://img-blog.csdnimg.cn/20190530103325659.png
[20190530103407349.png]: https://img-blog.csdnimg.cn/20190530103407349.png
[https_www.runoob.com_python_python-mysql.html]: https://www.runoob.com/python/python-mysql.html
[https_blog.csdn.net_samxx8_article_details_6285013]: https://blog.csdn.net/samxx8/article/details/6285013
[20190530104340636.png]: https://img-blog.csdnimg.cn/20190530104340636.png
[20190530104607453.png]: https://img-blog.csdnimg.cn/20190530104607453.png
[20190530104804387.png]: https://img-blog.csdnimg.cn/20190530104804387.png
[https_blog.csdn.net_csdn_article_details_84246712]: https://blog.csdn.net/csdn_______/article/details/84246712
[20190530110122972.png]: https://img-blog.csdnimg.cn/20190530110122972.png
[20190530110610360.png]: https://img-blog.csdnimg.cn/20190530110610360.png
[https_www.jianshu.com_p_d31929bda86e]: https://www.jianshu.com/p/d31929bda86e
[20190610202758469.png]: https://img-blog.csdnimg.cn/20190610202758469.png
[https_www.xpgod.com_soft_6690.html_onxzdz]: https://www.xpgod.com/soft/6690.html#onxzdz
[20190530140930764.png]: https://img-blog.csdnimg.cn/20190530140930764.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RpYW5hX1o_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20190530140944582.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RpYW5hX1o=,size_16,color_FFFFFF,t_70
[https_blog.csdn.net_lq497028254_article_details_90409930]: https://blog.csdn.net/lq497028254/article/details/90409930