ubuntu下的mysql
1.mysql服务端与客户端的安装
2.查看修改登录密码
3.卸载Mysql并重新安装
4.python操作数据库
5.安装MySQL-python
1.mysql服务端与客户端的安装
1.下载安装mysql的服务端:
sudo apt-get install mysql-server
1
2.切换成root用户后执行下面的命令,否则可能权限不够,想换回普通用户只需ctrl+D(可选)
sudo su -
1
3.下载安装mysql的客户端:
sudo apt-get install mysql-client
sudo apt-get install libmysqlclient-dev
1
2
4.安装netstat
sudo apt install net-tools
1
用netstat查看mysql是否安装成功
sudo netstat -tap | grep mysql
1
5. 查看mysql工作状态,如果没有成功,输入命令:
sudo systemctl start mysql
systemctl status mysql.service
1
2
6. 进入mysql数据库
mysql -u root -p #进入数据库
1
其中 -u 表示选择登陆的用户名,-p 表示登陆的用户密码,输入密码后就可以登录到mysql中,第一次登录,密码为空,啥都不用输入,直接回车,如下:
jhon@jhpnComputer:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.30-0ubuntu0.18.04.1 (Ubuntu)
Copyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>
2.查看修改登录密码
Ubuntu在安装MySQL时会为用户创建一个debian-sys-maint用户我们可以通过这个用户来设置;
1.查看debian-sys-maint密码
sudo cat /etc/mysql/debian.cnf
1
输出结果如下:
jhon@jhpnComputer:~$ sudo cat /etc/mysql/debian.cnf
[sudo] jhon 的密码:
Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = H94w6ifjvjoIdCGS
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = H94w6ifjvjoIdCGS
socket = /var/run/mysqld/mysqld.sock
1
登录debian-sys-maint用户
mysql -u debian-sys-maint -p
1
注意:此时密码为刚刚查到的密码:H94w6ifjvjoIdCGS
设置root密码
use mysql;
update mysql.user set www.64z206.comauthentication_string=password(‘1234’) where user=‘root’ and Host =‘localhost’;
#上面1234为新设置的密码
update user set plugin=‘mysql_native_password’;
flush privileges;
quit;
重新进入:
mysql -u root -p #进入数据库
1
3.卸载Mysql并重新安装
第一步:
sudo apt-get remove mysql-*
dpkg -l |grep ^rc|awk ‘{print $2}’ |sudo xargs dpkg -P
sudo apt purge mysql-server mysql-server-5.7
第二步:
sudo rm /var/lib/mysql/ -R
sudo rm /etc/mysql/ -R
sudo apt-get autoremove mysql* --purge
sudo apt-get remove apparmor
第三步:
reboot
sudo apt-get install mysql-server mysql-common
1
2
4.python操作数据库
(1)查看已有数据库
mysql> show databases;
1
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
4 rows in set (0.00 sec)
(2) 创建数据库
mysql> create database sqltest; #sqltest是数据库名
1
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sqltest |
| sys |
±-------------------+
5 rows in set (0.00 sec)
(3) 查看指定数据库中的表:
mysql> use sqltest; #首先要选择数据库,会告诉你换好了Database changed
mysql> show tables; #查看test库下面的表,此时为空表Empty set (0.00 sec)
1
2
(4) 创建表
table1: 对于现在的我也不知道下面语句是什么意思,不要慌;语法之后学;
mysql> CREATE TABLE table1(
id INT NOT NULL AUTO_INCREMENT,
date DATE,
pigid VARCHAR(5) NOT NULL,
diarrhea VARCHAR(5) NOT NULL,
fromid VARCHAR(5) NOT NULL,
pic1 VARCHAR(100),
pic2 VARCHAR(100),
PRIMARY KEY (id))ENGINE=InnoDB DEFAULT CHARSET=utf8;
table2:
mysql> CREATE TABLE table2(
id INT NOT NULL AUTO_INCREMENT,
date DATE,pigid VARCHAR(5) NOT NULL,
diarrhea VARCHAR(www.fdouyf.com) NOT NULL,
count VARCHAR(5) NOT NULL,
pic1 VARCHAR(100),
pic2 VARCHAR(100),
PRIMARY KEY (id))ENGINE=InnoDB DEFAULT CHARSET=utf8;
查看是否创建成功
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
±------------------+
| Tables_in_sqltest |
±------------------+
| table1 |
| table2 |
±------------------+
2 rows in set (0.00 sec)
(5)查看表格中的段属性
mysql> desc table1;
mysql> desc table2;
5.安装MySQL-python
在python中使用mysql数据库:
python安装pymysql;
sudo pip install pymysql
1
(1)连接数据库
python #终端输入
import pymysql #导入包
打开数据库连接
db = pymysql.connect(host=‘localhost’, user=‘root’, password=‘1234’, port=3306, db=‘sqltest’, charset=‘utf8’ )
cursor=db.cursor() # 使用cursor()方法获取操作游标
sql1 = “DELETE FROM table1” #SQl语句
SQL 删除语句
#sql1 = “DELETE FROM table1” #删除全表 数据
try:
执行SQL语句
cursor.execute(sql1)
提交修改
db.commit()
except:
发生错误时回滚
db.rollback()
关闭数据库连接
db.close()