python----mysql数据库

Ananconda使用指南

管理虚拟环境

conda create -n env_name  [packages...] python=version
source activate env_name
source deactivate
 列出所有已有环境;
conda env list
conda info -e
 删除一个已有的环境;
conda env remove -n env_name
conda remove --name env_name --all
 分享代码的时候,同时也需要将运行环境分享给大家;
conda env export > env.yaml
 用对方分享的 YAML 文件来创建一摸一样的运行环境;
conda env create -f env.yaml
管理包
conda install package_name
conda install pack1 [pack2 ...]
conda install pack=version
conda remove package_name
conda update package_name
conda list
conda  search search_term
管理指定虚拟虚拟环境的包
conda install package_name -n env_name
设置国内镜像
参考国内镜像源(清华anaconda镜像源)
conda config --add channels https://mirrors.tuna.tsinghua.edu.cn/anaconda/pkgs/free/conda

config --add channels       https://mirrors.tuna.tsinghua.edu.cn/anaconda/pkgs/free/
conda config --add channels https://mirrors.tuna.tsinghua.edu.cn/anaconda/pkgs/main/
conda config --set show_channel_urls yes
 注:这些设置存储在~/.condarc文件中;

安装mysql
yum search mariadb
软件包
yum install mariadb mariadb-server -y
软件和client软件

启动mariadb服务
systemctl start mariadb
systemctl enable mariadb
mariadb监听的端口
netstat -antlpe | grep mysql
ss -antlpe | grep mysql
vim /etc/services
只允许本地连接,阻断所有来自网络的连接
vim /etc/my.cnf
skip-networking=1
systemctl restart mariadb
查找与mariadb有关的
安装mariadb的server
所有服务与端口默认的对
2. mariadb的初始化
设置mysql的登陆密码
mysql_secure_installation
mysql -uroot -p
mysql基本操作语句
show databases;
含多个表
use mysql;
show tables;
显示数据库,类似于目录,里面包
进入名称为mysql的数据库
显示该数据库中的表
desc user;
select * from user;
select Host,User,Password from user; 显示表的结构
显示user表中的内容
显示表中某几列
create
create
创建以数据库名称为westos
创建一表
database westos;
table westosuser(
username varchar(10) not null,
passwd varchar(6) not null
);File: /home/kiosk/Desktop/补课/RH254/MYSQL/mariadb.md
Page 2 of 3
insert into westosuser values ('user1','123');
向表中插入内容
insert into westosuser(passwd,username) values("456","user2");
按照指定顺序向表中插入数据
update westosuser set passwd='456' where username="user1";
更新表中的内容
alter table westosuser add sex varchar(3);
添加sex列到westosuser表中
delete from westosuser where username="user1";
删除表中用户名为user1的记录
drop table westosuser;
删除表
drop database westos;
删除数据库
用户和访问权限的操作
create user hello@localhost identified by 'hello';
创建用户hello,可在本机登陆,密码为hello
create user hello@'%' identified by 'hello';
创建用户hello,可在远程登陆,密码为hello
create database mariadb;
创建一数据库mariadb,对普通用户进行
grant all on mariadb.* to hello@localhost;
给hello@localhost用户授权,如果为all,授权所有权限
(insert,update,delete,select,create)
flush privileges;
刷新,重载授权表
show grants for hello@localhost;
查看用户授权
revoke delete,update on mariadb.* from hello@localhost;
删除指定用户授权
drop user hello@localhost;
删除用户
4. 忘记mysql用户密码时,怎么找回?File: /home/kiosk/Desktop/补课/RH254/MYSQL/mariadb.md
1. 关闭mariadb服务
systemctl stop mariadb
2. 跳过授权表
mysqld_safe --skip-grant-table &
3. 修改root密码
mysql
> update mysql.user set Password=password('westos')
User='root';
4. 关闭跳过授权表的进程,启动mariadb服务,使用新密码即可
ps aux | grep mysql
kill -9 pid
mysql -uroot -p
Page 3 of 3
where
5. mysql的备份与恢复
备份:
mysqldump -uroot -p mariadb >mariadb.dump
mysqldump -uroot -pwestos --no-data mariadb > `date +%Y_%m_%
d`_mariadb.dump
mysqldump -uroot -pwestos --all-databases >mariadb4.dump
恢复:
mysqladmin -uroot -pwestos create mariadb2
mysql -uroot -pwestos mariadb2< mariadb.dump

数据库编程
mariadb安装与使用
[root@server0 ~]# yum
install mariadb-server -y
[root@server0 ~]# systemctl
start mariadb
[root@server0 ~]# mysql_secure_installation
[root@server0 ~]# mysql -uroot -pwestos
数据库基本操作
[root@server0 ~]# mysql -uroot -pwestos
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
MariaDB [(none)]> show databases;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| mysql
|
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> create database westos;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use westos;
Database changed
MariaDB [westos]> show tables;
Empty set (0.00 sec)
MariaDB [westos]> create table userinfo( username varchar(10), passwo
rd varchar(10));Query OK, 0 rows affected (0.16 sec)
MariaDB [westos]> desc userinfo;
+----------+-------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | YES |
| NULL
|
|
| password | varchar(10) | YES |
| NULL
|
|
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
MariaDB [westos]> insert into userinfo values("user1", "123");
Query OK, 1 row affected (0.02 sec)
MariaDB [westos]> select * from userinfo;
+----------+----------+
| username | password |
+----------+----------+
| user1
| 123
|
+----------+----------+
1 row in set (0.00 sec)
MariaDB [westos]> insert into userinfo values("程序员1", "123");
Query OK, 1 row affected, 1 warning (0.02 sec)
MariaDB [westos]> select * from userinfo;
+----------+----------+
| username | password |
+----------+----------+
| user1
| 123
|
| ???1
| 123
|
+----------+----------+
2 rows in set (0.00 sec)
MariaDB [westos]> update userinfo set password="345" where username
="user1";
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [westos]> select * from userinfo;+----------+----------+
| username | password |
+----------+----------+
| user1
| 345
|
| ???1
| 123
|
+----------+----------+
2 rows in set (0.00 sec)MariaDB [westos]> delete from userinfo where username="user1";
Query OK, 1 row affected (0.06 sec)
MariaDB [westos]> select * from userinfo;
+----------+----------+
| username | password |
+----------+----------+
| ???1
| 123
|
+----------+----------+
1 row in set (0.00 sec)
MariaDB [westos]> drop table userinfo;
Query OK, 0 rows affected (0.04 sec)
MariaDB [westos]> drop database westos;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| mysql
|
| performance_schema |
+--------------------+
3 rows in set (0.01 sec)
MariaDB [(none)]> quit
mariadb中文编码问题
当使用mysql数据库时,作为纯正的爱国者(实质是英语看不懂),当然会使用中文存储一些数据,但
是默认情况下存储的中文是 ?????? 这种外星文文字,这是要搞什么?
因此如何修改数据库中的编码格式设置呢?
数据库编码格式的查看
登陆数据库
$ mysql -uroot -p
通过下面命令查看mysql的编码格式;MariaDB [(none)]> show variables like 'character_set_%';
+--------------------------+----------------------------+
| Variable_name
| Value
|
+--------------------------+----------------------------+
| character_set_client
| utf8
|
| character_set_connection | utf8
|
| character_set_database
| latin1
|
| character_set_filesystem | binary
|
| character_set_results
| utf8
|
| character_set_server
| latin1
|
| character_set_system
| utf8
|
| character_sets_dir
| /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
临时修改数据库的编码格式
修改除了character_set_filesystem之外的其他变量的编码格式为utf8;
MariaDB [(none)]> set character_set_server='utf8';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> set character_set_database = 'utf8';
Query OK, 0 rows affected (0.00 sec)
但是,很坑的是,当重启服务后,刚才的设置就直接失效,也是挺崩溃的;大招在下面.
永久修改数据库的编码格式
配置mysql的配置文件,配置文件在/etc/my.cnf.d/目录,设置编码格式:
第一个文件修改: /etc/my.cnf.d/client.cnf
7 [client]
8
default-character-set=utf8
第二个文件修改: /etc/my.cnf.d/server.cnf
11 # this is only for the mysqld standalone daemon
12 [mysqld]
13
character-set-server=utf8
上面设置完成,看似很完美,但是重启mariadb服务后,character_set_database并没有进行修
改,因此,为了一步到位,直接设置character_set_database编码格式为utf8,如下:
MariaDB [(none)]> set character_set_database = 'utf8';
Query OK, 0 rows affected (0.00 sec)
一切ok,终于搞定!多条语句执行
In [2]: import MySQLdb as mysql
In [4]: conn = mysql.connect(user='root', passwd=