Ubuntu下的mysql(零基础操作)
ubuntu下的mysql1.mysql服务端与客户端的安装2.查看修改登录密码3.卸载Mysql并重新安装4.python操作数据库5.安装MySQL-python1.mysql服务端与客户端的安装1.下载安装mysql的服务端:sudo apt-get install mysql-server2.切换成root用户后执行下面的命令,否则可能权限不够,想换回普通用户只需ctrl+D(可选)sud
·
1.mysql服务端与客户端的安装
1.下载安装mysql的服务端:
sudo apt-get install mysql-server
2.切换成root用户后执行下面的命令,否则可能权限不够,想换回普通用户只需ctrl+D(可选)
sudo su -
3.下载安装mysql的客户端:
sudo apt-get install mysql-client
sudo apt-get install libmysqlclient-dev
4.安装netstat
sudo apt install net-tools
- 用netstat查看mysql是否安装成功
sudo netstat -tap | grep mysql
5. 查看mysql工作状态,如果没有成功,输入命令:
sudo systemctl start mysql
systemctl status mysql.service
6. 进入mysql数据库
mysql -u root -p #进入数据库
其中 -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 (c) 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
输出结果如下:
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
- 登录debian-sys-maint用户
mysql -u debian-sys-maint -p
注意:此时密码为刚刚查到的密码:H94w6ifjvjoIdCGS
- 设置root密码
use mysql;
update mysql.user set authentication_string=password('1234') where user='root' and Host ='localhost';
#上面1234为新设置的密码
update user set plugin='mysql_native_password';
flush privileges;
quit;
对于新版本,使用上面语法修改密码会发生错误:
use mysql;
flush privileges;
alter user 'root'@'localhost' identified by '123456';
flush privileges;
quit;
#重启
service mysql restart
- 重新进入:
mysql -u root -p #进入数据库
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
4.python操作数据库
(1)查看已有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
(2) 创建数据库
mysql> create database sqltest; #sqltest是数据库名
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)
(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(5) 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)连接数据库
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()
更多推荐
已为社区贡献1条内容
所有评论(0)