SQLラーニング:mysqlデータベースのバックアップとログリカバリ+pythonを使用してmysqlメソッドを呼び出す(4)
5081 ワード
<span style="font-size:14px;">--EmpAndDept5
-- 、 、 、 python mysql
-- select
create table emp2 as
select empno,sal from emp where 1=0;
-- , where false
-- depno 20 110%, , update
update emp set sal=sal*1.1 where deptno=10;
-- emp_bonus (1.1 )
update emp set sal=sal*1.1 where empno in (select empno from emp_bonus);
-- exists in
update emp e set sal=sal*1.1 where exists(select null from emp_bonus eb where e.empno=eb.empno);
-- where,
-- , insert , ,empno , ,
insert into emp(ename,job,mgr,hiredate,sal,comm,deptno) values('smith','clerk',7902,1980-12-27,1064.80,null,10);
delete from emp where empno not in (select max(empno) from emp group by ename);
-- delete , ( 。。。)
-- !
delete from emp where empno not in (select * from (select max(empno) from emp group by ename) a );
--
--delete drop ,delete , ,drop
delete from pet;
drop table pet;
--
delete from emp where empno=1000;
--
-- :
--No.1 : mysql ( mysql data ), ....
--
-- : windows ,
--No.2 mysqldump
--cmd bin :mysqldump -u root -p basename > backup.sql
--basename ,backup , bin back.sql , backup.sql :C:\ \backup.sql
-- locktable basename --skip-lock-tables
--
-- cmd , ,
create database restoretest;
use RestoreTest;
source C:/User/backup.sql; -- : , source
-- / \,source , \\
-- :
-- :
--No.3 ( )
--
-- sql , , , ,
-- ,
-- :
show global variables like '%log%';
show global variables like 'log_bin';
show binary logs; --
show master status; -- ( )
-- , my.ini , :http://blog.csdn.net/databatman/article/details/49951853
-- : mysql5.6 , , mysql5.6.x
-- http://blog.csdn.net/alvine008/article/details/9097105
-- , nice , , ,
-- , :
-- 1. ,
-- 2. ,
-- 3.
-- 4.
--
drop database if exists Heros; -- ,
-- python mysql
-- :
-- :python-mysql.py
</span>
python呼び出しmysql:
pythonとmysqlの関連ライブラリをインストールします.pythonのscriptフォルダで、cmdモードで入力します.
pip install mysql-connector-python --allow-external mysql-connector-python
#!usr/bin/env python
#-*- coding:utf-8 -*-
import mysql.connector #mysql-connector-python
#connect
#cursor.execute create、insert、update、select
# con、cursor
con=mysql.connector.connect(host='localhost',user='root',password='1991423',database='cookbook',use_unicode=True)
cursor=con.cursor()
#
sql1=u"create table pytest (id int primary key,name varchar(20),sex nchar(1) default ' ' check(sex in (' ',' ')))"
cursor.execute(sql1)
#
#1、 , mysql , mysql '',
# execute '', :
# ① "",
# ② \'
cursor.execute(u"insert into pytest values(1,'bob',' ')")
#2、 !
# , ,
sql2=u'insert into pytest values(%s,%s,%s)'
parameter=[2,'Kate',' ']
cursor.execute(sql2,parameter)
# insert
con.commit()
# selcet * from pytest id=2 ( )
# , unicode
# update
sql3=u'update pytest set sex=%s where id=%s'
parameter=[u' ',2]
cursor.execute(sql3,parameter)
con.commit()
# select
cursor.execute('select * from pytest')
# fetchall
# fetchone ,fetchmany ,scroll
# list, tuple
values=cursor.fetchall()
n=cursor.rowcount # execute , select 2 , n=2
print values
print n
# : select fetchall, ,
# cursor conn,
cursor.close()
con.close()
# , try except finally
python呼び出しmysql主に廖雪峰の大きなブログを参考にして、私は自分で少し理解しました.
http://www.liaoxuefeng.com/wiki/0014316089557264a6b348958f449949df42a6d3a2e542c000/0014320107391860b39da6901ed41a296e574ed37104752000
mysqlコードgithubアドレス:
https://github.com/databatman/SQLCookBook-MysqlVersion
PS:すべてのmysqlのEmpAnd 1-nコードがこのフォルダにアップロードされます.ついでに注目してください.まだ菜鳥ですが、ああ.