MySQL 数据库 4
数据备份(在Linux终端操作)
1、命令格式
mysqldump -u用户名 -p 源库名 > 路径/XXX.sql
2、源库名的表示方式
--all-databases 备份所有库
库名 备份单个库
-B 库1 库2 ... 备份多个库
库名 表1 表2 ... 备份指定库的指定表
3、练习
1、备份所有库为all_mysql.sql放到主目录下mydata目录中
2、备份三个库,放到mydata目录中
3、备份省、市、县三张表
ctrl + shift + t^C
mkdir mydata
ls mydata/
mysqldump -uroot -p db3 > ~/mydata/db3.sql
cd mydata/
#
mysqldump -uroot -p --all-databases > /home/tarena/mydata/all_mysql.sql
#
mysqldump -uroot -p -B db1 db2 db3 > /home/tarena/mydata/san_mysql.sql
#
mysqldump -uroot -p db3 sheng city xian > /home/tarena/mydata/db3_san.sql
数据恢复(在Linux终端操作)
1、命令格式
mysql -uroot -p 目标库名 < 路径/XXX.sql
2、从所有库的备份文件中恢复某一个库(--one-database)
mysql -uroot -p --one-database 目标库名 < 路径/all_mysql.sql
3、练习
1、先备份db3库,删除省、市、县三张表
2、在db3库中创建一张表 t888
create table t888(id int);
3、恢复db3库
4、注意
1、恢复库时如果恢复到原有库会将表中数据覆盖,新增的表不会删除
2、在恢复时如果要恢复的库不存在,则先要创建空库
mysql> show databases like 'db3';
+----------------+
| Database (db3) |
+----------------+
| db3 |
+----------------+
1 row in set (0.00 sec) mysql> show databases like '%db3%';
+------------------+
| Database (%db3%) |
+------------------+
| db3 |
+------------------+
1 row in set (0.00 sec) mysql> show databases like '%db%';
+-----------------+
| Database (%db%) |
+-----------------+
| db1 |
| db2 |
| db3 |
+-----------------+
3 rows in set (0.00 sec) mysql> drop database db3;
Query OK, 16 rows affected (3.48 sec) mysql> show databases like '%db3%';
Empty set (0.00 sec) mysql> 开始恢复 db3 \c
mysql>
mysql> create database db3;
Query OK, 1 row affected (0.03 sec) mysql> 1,创建空库\c
mysql> use db3
Database changed
#恢复db3
#cd mydata
#mysql -uroot -p db3 < db3.sql
Enter password:
#
mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| bjtab |
| city |
| jftab |
| new_t2 |
| new_t2_t2 |
| sheng |
| t1 |
| t2 |
| t3 |
| tt1 |
| tt2 |
| userinfo |
| userinfo2 |
| userinfo3 |
| userinfo4 |
| xian |
+---------------+
16 rows in set (0.02 sec) mysql> select * from sheng;
+----+--------+--------------------+
| id | S_ID | S_name |
+----+--------+--------------------+
| 1 | 130000 | 河北省 |
| 2 | 140000 | 山西省 |
| 3 | 150000 | 内蒙古自治区 |
| 4 | 160000 | 辽宁省 |
| 5 | 170000 | 黑龙江省 |
+----+--------+--------------------+
5 rows in set (0.00 sec) mysql> select * from city;
+----+--------+-----------------+------------+
| id | C_ID | C_name | CFather_ID |
+----+--------+-----------------+------------+
| 1 | 131100 | 石家庄市 | 130000 |
| 2 | 131101 | 沧州市 | 130000 |
| 3 | 131102 | 廊坊市 | 130000 |
| 4 | 131103 | 衡水市 | 130000 |
| 5 | 131104 | 太原市 | 140000 |
| 6 | 131105 | 呼和浩特市 | 150000 |
| 7 | 131106 | 包头市 | 150000 |
| 8 | 131107 | 沈阳市 | 160000 |
| 9 | 131108 | 大连市 | 160000 |
| 10 | 131109 | 无锡市 | 320000 |
| 11 | 131110 | 徐州市 | 320000 |
| 12 | 131111 | 常州市 | 320000 |
+----+--------+-----------------+------------+
12 rows in set (0.05 sec) mysql>
###
mysql> drop database db3;
Query OK, 16 rows affected (0.43 sec) mysql>
mysql> create database db3;
Query OK, 1 row affected (0.00 sec) mysql> use db3;
Database changed
#
mysql -uroot -p --one-database db3 < ~/mydata/all_mysql.sql
#
mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| bjtab |
| city |
| jftab |
| new_t2 |
| new_t2_t2 |
| sheng |
| t1 |
| t2 |
| t3 |
| tt1 |
| tt2 |
| userinfo |
| userinfo2 |
| userinfo3 |
| userinfo4 |
##
mysql>
mysql> use db3;
Database changed
mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| bjtab |
| city |
| jftab |
| new_t2 |
| new_t2_t2 |
| sheng |
| t1 |
| t2 |
| t3 |
| tt1 |
| tt2 |
| userinfo |
| userinfo2 |
| userinfo3 |
| userinfo4 |
| xian |
+---------------+
16 rows in set (0.00 sec) mysql> drop table sheng,city,xian;
Query OK, 0 rows affected (0.29 sec) mysql> create table t888(id int);
Query OK, 0 rows affected (0.22 sec) mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| bjtab |
| jftab |
| new_t2 |
| new_t2_t2 |
| t1 |
| t2 |
| t3 |
| t888 |
| tt1 |
| tt2 |
| userinfo |
| userinfo2 |
| userinfo3 |
| userinfo4 |
+---------------+
14 rows in set (0.00 sec)
##
mysql -uroot -p db3 < ~/mydata/db3.sql
##
mysql> 恢复之后\c
mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| bjtab |
| city |
| jftab |
| new_t2 |
| new_t2_t2 |
| sheng |
| t1 |
| t2 |
| t3 |
| t888 |
| tt1 |
| tt2 |
| userinfo |
| userinfo2 |
| userinfo3 |
| userinfo4 |
| xian |
+---------------+
17 rows in set (0.00 sec) mysql> t888还存在\c
mysql> select * from sheng;
+----+--------+--------------------+
| id | S_ID | S_name |
+----+--------+--------------------+
| 1 | 130000 | 河北省 |
| 2 | 140000 | 山西省 |
| 3 | 150000 | 内蒙古自治区 |
| 4 | 160000 | 辽宁省 |
| 5 | 170000 | 黑龙江省 |
+----+--------+--------------------+
5 rows in set (0.03 sec) mysql> select * from city;
+----+--------+-----------------+------------+
| id | C_ID | C_name | CFather_ID |
+----+--------+-----------------+------------+
| 1 | 131100 | 石家庄市 | 130000 |
| 2 | 131101 | 沧州市 | 130000 |
| 3 | 131102 | 廊坊市 | 130000 |
| 4 | 131103 | 衡水市 | 130000 |
| 5 | 131104 | 太原市 | 140000 |
| 6 | 131105 | 呼和浩特市 | 150000 |
| 7 | 131106 | 包头市 | 150000 |
| 8 | 131107 | 沈阳市 | 160000 |
| 9 | 131108 | 大连市 | 160000 |
| 10 | 131109 | 无锡市 | 320000 |
| 11 | 131110 | 徐州市 | 320000 |
| 12 | 131111 | 常州市 | 320000 |
+----+--------+-----------------+------------+
12 rows in set (0.04 sec) mysql> select * from xian;
+----+--------+-----------+------------+
| id | X_ID | X_name | XFather_ID |
+----+--------+-----------+------------+
| 1 | 132100 | 河东区 | 131100 |
| 2 | 132101 | 正定县 | 131100 |
| 3 | 132102 | 固安县 | 131102 |
| 4 | 132102 | 香河县 | 131102 |
| 5 | 132103 | 哈哈 | 131112 |
+----+--------+-----------+------------+
5 rows in set (0.00 sec) mysql>
ER模型&ER图
1、定义
ER模型即实体-关系模型,ER图即实体-关系图
2、三个概念
1、实体
1、定义：现实世界中任何可以被认知、区分的事物
2、示例
1、学校 ：学生、教师、课程、班主任 ...
2、企业 ：职工、产品
2、属性
1、定义 ：实体所具有的特性
2、示例
1、学生属性：学号、姓名、年龄、性别、专业 ...
2、产品属性：产品编号、产品名称、规格 ...
3、关系
1、定义 ：实体之间的关系
2、分类
一对一关系(1:1) 班级和班长
一对多关系(1:n) 公司和职工、班级和学生
多对多关系(m:n) 学生和课程、商店和顾客
4、ER图的绘制
1、矩形框代表实体, 菱形框代表关系, 椭圆形代表属性
2、示例
学生选课系统
##########
workbench(图形化界面操作数据库) 5.7版本
##########
事务&事务回滚
1、事务定义
一件事从开始发生到结束的整个过程
2、属性
1、原子性：一个事务是不可分割的工作单位,事务中的各个操作要么都做,要么就都不做
2、一致性：事务必须从一个一致性状态到另一个一致性状态
3、隔离性：一个事务的执行不能被其他并发事务干扰
4、持久性：一个事务一旦提交,它对数据库的改变是永久性的
3、事务及事务回滚的应用
1、mysql中默认sql语句会自动commit到数据库
show variables like "autocommit";
2、事务应用
1、开启事务
mysql> start transaction;
## 此时autocommit被禁用,SQL命令不会对数据库中数据做修改
2、终止事务
mysql> commit; （提交） | rollback; （回滚，什么也没做）
3、注意
事务&事务回滚只针对对表记录的操作:增加、删除、修改,对创建库、创建表是无效的
4、案例
1、背景
你 ： 建行卡
你朋友：工商卡
你在建行的自动提款机给你朋友(工商银行卡)转账
2、过程
表1、建行：CCB
表2、工行：ICBC
###开始转账
mysql>start transaction;
mysql>update CCB set money=95000 where name="你";
mysql>update ICBC set ... 断电了...;
mysql>rollback
###验证
mysql>select * from CCB;
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.98 sec) mysql> start transaction;
Query OK, 0 rows affected (0.04 sec) mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec) mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| bjtab |
| city |
| jftab |
| new_t2 |
| new_t2_t2 |
| sheng |
| t1 |
| t2 |
| t3 |
| t888 |
| tt1 |
| tt2 |
| userinfo |
| userinfo2 |
| userinfo3 |
| userinfo4 |
| xian |
+---------------+
17 rows in set (0.04 sec) mysql> insert into t888 values(1),(2),(3);
Query OK, 3 rows affected (0.27 sec)
Records: 3 Duplicates: 0 Warnings: 0 mysql> commit;
Query OK, 0 rows affected (0.00 sec) mysql>
[]+ 已停止 mysql -uroot -p123456
tarena@tedu:~$ mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.22-0ubuntu0.16.04.1 (Ubuntu) Copyright (c) 2000, 2018, 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> ues db3;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ues db3' at line 1
mysql>
mysql>
mysql> use db3;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec) mysql> update t888 set id=10 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> insert into t888 values(20);
Query OK, 1 row affected (0.00 sec) mysql> roolback;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'roolback' at line 1
mysql>
mysql> rollback;
Query OK, 0 rows affected (0.07 sec) mysql> 回滚\c
mysql>
mysql> start transtaction;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'transtaction' at line 1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec) mysql> create table t999(id int);
Query OK, 0 rows affected (0.55 sec) mysql> create database dbover;
Query OK, 1 row affected (0.02 sec) mysql> rollback;
Query OK, 0 rows affected (0.00 sec) mysql>
##############
mysql> use db3;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Database changed
mysql> select * from t888;
Empty set (0.00 sec) mysql> select * from t888;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec) mysql> mysql> select * from t888;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec) mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| bjtab |
| city |
| jftab |
| new_t2 |
| new_t2_t2 |
| sheng |
| t1 |
| t2 |
| t3 |
| t888 |
| t999 |
| tt1 |
| tt2 |
| userinfo |
| userinfo2 |
| userinfo3 |
| userinfo4 |
| xian |
+---------------+
18 rows in set (0.00 sec) mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| MoShou |
| SANGUO |
| db1 |
| db2 |
| db3 |
| dbover |
| mysql |
| performance_schema |
| sys |
+--------------------+
10 rows in set (0.00 sec) mysql> show tables like 't999';
+----------------------+
| Tables_in_db3 (t999) |
+----------------------+
| t999 |
+----------------------+
1 row in set (0.04 sec) mysql> show database like 'dbover';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database like 'dbover'' at line 1
mysql> show databases like 'dbover';
+-------------------+
| Database (dbover) |
+-------------------+
| dbover |
+-------------------+
1 row in set (0.03 sec) mysql>
mysql> create table CCB
-> (name varchar(20),
-> money int
-> )default charset=utf8;
Query OK, 0 rows affected (0.15 sec) mysql> insert into CCB values('转钱',100000);
Query OK, 1 row affected (0.00 sec) mysql> create table ICBC(
-> name varchar(20),
-> money int
-> )default charset=utf8;
Query OK, 0 rows affected (0.12 sec) mysql> insert into ICBC values('借钱',2000);
Query OK, 1 row affected (0.04 sec) mysql> 开始转账\c
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec) mysql> update CCB set money=95000 where name='转钱';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> update ICBC set money=断电了，宕机了；
-> ；
->
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '；' at line 3
mysql>
mysql> rollback;
Query OK, 0 rows affected (0.01 sec) mysql> select * from CCB;
+--------+--------+
| name | money |
+--------+--------+
| 转钱 | 100000 |
+--------+--------+
1 row in set (0.00 sec) mysql> select * from ICBC;
+--------+-------+
| name | money |
+--------+-------+
| 借钱 | 2000 |
+--------+-------+
1 row in set (0.00 sec) mysql>
Python数据库编程
1、python数据库接口(Python DB-API)
1、定义：为开发人员提供的数据库应用编程接口
2、支持的数据库服务软件
MySQL、Oracle、SQL_Server、Sybase、Mongodb ...
3、Python提供的操作MySQL的模块
模块名：pymysql (python3) ,mysqldb(python2)
4、pymysql的使用流程
1、建立数据库连接
2、创建游标对象
3、使用游标对象的方法和SQL语句操控MySQL数据库
4、提交commit
5、关闭游标
6、关闭数据库连接
5、建立数据库连接
1、语法格式
对象名(db) = pymysql.connect("主机地址","用户名","密码","库名",charset="utf8")
2、connect对象(db)的方法
1、cursor() 创建一个游标对象db.cursor()
2、commit() 提交到数据库 db.commit()
3、rollback() 回滚 db.rollback()
4、close() 关闭与数据库的连接 db.close()
3、cursor游标对象(cur)的方法
1、execute() 执行sql命令
2、fetchone() 取得结果集的第一条记录
3、fetchmany(数字) 取得结果集的 几条 记录
4、fetchall() 取得结果集的所有行
5、close() 关闭游标对象
属性：rowcount : 返回命令执行所影响的条数( cur.rowcount )
pymysql.connect --->>db ---> db.cursor() ----> 执行
import pymysql #1,打开数据库连接
db = pymysql.connect("localhost",'root',
'',charset="utf8")
#创建一个游标对象
cur = db.cursor()
#创建库
cur.execute("create database if not exists python;")
#切换库
cur.execute("use python;")
#创建表
cur.execute("create table if not exists t1(\
id int primary key,\
name varchar(20),\
score tinyint unsigned)default charset=utf8;")
#在t1中插入５条记录
cur.execute("insert into t1 values\
(1,'貂蝉',88),\
(2,'赵子龙',100),\
(3,'诸葛亮',80),\
(4,'张飞',60),\
(5,'司马懿',99);")
#提交到数据库
db.commit()
#关闭游标
cur.close()
#关闭数据库
db.close()
#####
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| MoShou |
| SANGUO |
| db1 |
| db2 |
| db3 |
| dbover |
| mysql |
| performance_schema |
| python |
| sys |
+--------------------+
11 rows in set (0.01 sec) mysql> use python;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Database changed
mysql> show tables;
+------------------+
| Tables_in_python |
+------------------+
| t1 |
+------------------+
1 row in set (0.03 sec) mysql> select * from t1;
+----+-----------+-------+
| id | name | score |
+----+-----------+-------+
| 1 | 貂蝉 | 88 |
| 2 | 赵子龙 | 100 |
| 3 | 诸葛亮 | 80 |
| 4 | 张飞 | 60 |
| 5 | 司马懿 | 99 |
+----+-----------+-------+
5 rows in set (0.04 sec) mysql> desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| score | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.12 sec) mysql>
fetchone
import pymysql
#
db = pymysql.connect('localhost','root','',
'python',charset='utf8')
#创建游标
cur = db.cursor()
#mysql语句
sql_select = 'select * from t1;'
cur.execute(sql_select) data = cur.fetchone()
print('fetchone的结果为：',data) db.commit()
cur.close()
db.close()
###
$ python3 Fetchone.py
fetchone的结果为： (1, '貂蝉', 88)
Fetchmany-Fetchall
import pymysql
db = pymysql.connect('localhost','root','',\
'python',port=3306,charset='utf8')
cur = db.cursor()
sql_select = 'select * from t1;'
cur.execute(sql_select)
data = cur.fetchmany(2)
print("fetchmany的结果是：",data)
data2 = cur.fetchall()
print('fetchall的结果为：',data2)
db.commit()
cur.close()
db.close()
####
python3 Fetchmany-all.py
fetchmany的结果是： ((1, '貂蝉', 88), (2, '赵子龙', 100))
fetchall的结果为： ((3, '诸葛亮', 80), (4, '张飞', 60), (5, '司马懿', 99))
test
cur.rowcount
import pymysql
db = pymysql.connect('localhost','root','', \
'python',port=3306,charset='utf8')
cur = db.cursor()
sql_select = 'select * from t1;'
cur.execute(sql_select)
print('select记录的个数：',cur.rowcount)
####
$ python3 Fetchmany-all.py
select记录的个数： 5
rowback
import pymysql
db = pymysql.connect('localhost','root','' \
'db3',charset='utf8')
cur = db.cursor()
try:
cur.execute('update CCB set money=95000 \
where name="转钱";')
cur.execute('update ICBC set money=7000 \
where name="借钱";')
db.commit()
print('ok')
except Exception as e:
print(e)
db.rollback()
#db.commit()
cur.close()
db.close()
python3 pyrollback.py
ok
:~/python-mysql$ python3 pyrollback.py
(1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'=7000 where name="借钱"\' at line 1')
:~/python-mysql$ python3 pyrollback.py
(1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'=7000 where name="借钱"\' at line 1')
出现错误，已经回滚。
:~/python-mysql$
fengzhuang
from pymysql import * class MysqlPython:
def __init__(self,host,port,db,user, \
passwd,charset='utf8'):
self.host = host
self.port = port
self.db = db
self.user = user
self.passwd = passwd
self.charset = charset def open(self):
self.con = connect(host=self.host, \
port=self.port,db=self.db, \
user=self.user,passwd=self.passwd, \
charset=self.charset)
self.cursor = self.con.cursor() def close(self):
self.cursor.close()
self.con.close() def zhixing(self,sql):
self.open()
self.cursor.execute(sql)
self.con.commit()
self.close() def all(self,sql):
try:
self.open()
self.cursor.execute(sql)
data = self.cursor.fetchall()
self.close() return data
except Exception as e:
print(e)
#### from MysqlPython import MysqlPython
#update
name = input("请输入要修改的学生姓名：")
score = int(input("请输入该学生的新成绩："))
sql = "update t1 set score='%s' where name='%s'" \
%(score,name)
sqlH = MysqlPython('localhost',3306,'python', \
'root','')
sqlH.zhixing(sql)