Mysql学习笔记

1. Python连接Mysql

import pymysql
import pandas as pd

conn = pymysql.connect(host = '172.17.0.1',port = 12306,user = 'root',password = '********',db = 'mysql',charset='utf8')

sql_query='select * from user'
content_df=pd.read_sql(sql_query,con=conn)

content_df.head()

	Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	...	max_questions	max_updates	max_connections	max_user_connections	plugin	authentication_string	password_expired	is_role	default_role	max_statement_time
0	localhost	root	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	Y	Y	Y	Y	Y	Y	Y	...	0	0	0	0	mysql_native_password	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	N	N		0.0
1	%	root	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	Y	Y	Y	Y	Y	Y	Y	...	0	0	0	0	mysql_native_password	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	N	N		0.0

2 rows × 47 columns

关闭连接

conn.close()

2. Mysql客户端连接和管理

%%bash
mysqladmin --version

mysqladmin  Ver 8.42 Distrib 5.7.29, for Linux on x86_64

%%bash
mysql -h 172.17.0.1 -P 12306 -uroot -p******** <<!
show databases;
!

Database
information_schema
mysql
performance_schema


mysql: [Warning] Using a password on the command line interface can be insecure.

3. 修改密码

在mysql的服务器本机上执行

mysqladmin -u root -p old_password password "new_password";

4. ipython连接管理mysql

%load_ext sql
%sql mysql+pymysql://root:********@172.17.0.1:12306

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

'Connected: root@None'

%%sql
show databases;

 * mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
3 rows affected.

Database
information_schema
mysql
performance_schema

%%sql
use mysql;

 * mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
0 rows affected.

[]

%%sql
show tables;

 * mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
31 rows affected.

Tables_in_mysql
column_stats
columns_priv
db
event
func
general_log
global_priv
gtid_slave_pos
help_category
help_keyword
help_relation
help_topic
index_stats
innodb_index_stats
innodb_table_stats
plugin
proc
procs_priv
proxies_priv
roles_mapping
servers
slow_log
table_stats
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
transaction_registry
user

%%sql
show columns from user;

 * mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
47 rows affected.

Field	Type	Null	Default
Host	char(60)	NO
User	char(80)	NO
Password	longtext	YES	None
Select_priv	varchar(1)	YES	None
Insert_priv	varchar(1)	YES	None
Update_priv	varchar(1)	YES	None
Delete_priv	varchar(1)	YES	None
Create_priv	varchar(1)	YES	None
Drop_priv	varchar(1)	YES	None
Reload_priv	varchar(1)	YES	None
Shutdown_priv	varchar(1)	YES	None
Process_priv	varchar(1)	YES	None
File_priv	varchar(1)	YES	None
Grant_priv	varchar(1)	YES	None
References_priv	varchar(1)	YES	None
Index_priv	varchar(1)	YES	None
Alter_priv	varchar(1)	YES	None
Show_db_priv	varchar(1)	YES	None
Super_priv	varchar(1)	YES	None
Create_tmp_table_priv	varchar(1)	YES	None
Lock_tables_priv	varchar(1)	YES	None
Execute_priv	varchar(1)	YES	None
Repl_slave_priv	varchar(1)	YES	None
Repl_client_priv	varchar(1)	YES	None
Create_view_priv	varchar(1)	YES	None
Show_view_priv	varchar(1)	YES	None
Create_routine_priv	varchar(1)	YES	None
Alter_routine_priv	varchar(1)	YES	None
Create_user_priv	varchar(1)	YES	None
Event_priv	varchar(1)	YES	None
Trigger_priv	varchar(1)	YES	None
Create_tablespace_priv	varchar(1)	YES	None
Delete_history_priv	varchar(1)	YES	None
ssl_type	varchar(9)	YES	None
ssl_cipher	longtext	NO
x509_issuer	longtext	NO
x509_subject	longtext	NO
max_questions	bigint(20) unsigned	NO	0
max_updates	bigint(20) unsigned	NO	0
max_connections	bigint(20) unsigned	NO	0
max_user_connections	bigint(21)	NO	0
plugin	longtext	NO
authentication_string	longtext	NO
password_expired	varchar(1)	NO
is_role	varchar(1)	YES	None
default_role	longtext	NO
max_statement_time	decimal(12,6)	NO	0.000000

%%sql
select * from user;

 * mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
2 rows affected.

Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	Delete_history_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections	plugin	authentication_string	password_expired	is_role	default_role	max_statement_time
localhost	root	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y					0	0	0	0	mysql_native_password	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	N	N		0.000000
%	root	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y					0	0	0	0	mysql_native_password	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	N	N		0.000000

%%sql
show table status from mysql like 'user'

 * mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
1 rows affected.

Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment	Max_index_length	Temporary
user	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	None	VIEW	None	None

5. mysql用户管理

%%sql
use mysql;
create user test identified by 'test123';

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
 * mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
0 rows affected.
0 rows affected.

[]

%sql mysql+pymysql://test:test123@172.17.0.1:12306

'Connected: test@None'

%sql show databases;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
 * mysql+pymysql://test:***@172.17.0.1:12306
1 rows affected.

Database
information_schema

%sql use information_schema;show tables;

 * mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
0 rows affected.
77 rows affected.

Tables_in_information_schema
ALL_PLUGINS
APPLICABLE_ROLES
CHARACTER_SETS
CHECK_CONSTRAINTS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
COLUMN_PRIVILEGES
ENABLED_ROLES
ENGINES
EVENTS
FILES
GLOBAL_STATUS
GLOBAL_VARIABLES
KEY_CACHES
KEY_COLUMN_USAGE
OPTIMIZER_TRACE
PARAMETERS
PARTITIONS
PLUGINS
PROCESSLIST
PROFILING
REFERENTIAL_CONSTRAINTS
ROUTINES
SCHEMATA
SCHEMA_PRIVILEGES
SESSION_STATUS
SESSION_VARIABLES
STATISTICS
SYSTEM_VARIABLES
TABLES
TABLESPACES
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TRIGGERS
USER_PRIVILEGES
VIEWS
GEOMETRY_COLUMNS
SPATIAL_REF_SYS
CLIENT_STATISTICS
INDEX_STATISTICS
INNODB_SYS_DATAFILES
USER_STATISTICS
INNODB_SYS_TABLESTATS
INNODB_LOCKS
INNODB_MUTEXES
INNODB_CMPMEM
INNODB_CMP_PER_INDEX
INNODB_CMP
INNODB_FT_DELETED
INNODB_CMP_RESET
INNODB_LOCK_WAITS
TABLE_STATISTICS
INNODB_TABLESPACES_ENCRYPTION
INNODB_BUFFER_PAGE_LRU
INNODB_SYS_FIELDS
INNODB_CMPMEM_RESET
INNODB_SYS_COLUMNS
INNODB_FT_INDEX_TABLE
INNODB_CMP_PER_INDEX_RESET
user_variables
INNODB_FT_INDEX_CACHE
INNODB_SYS_FOREIGN_COLS
INNODB_FT_BEING_DELETED
INNODB_BUFFER_POOL_STATS
INNODB_TRX
INNODB_SYS_FOREIGN
INNODB_SYS_TABLES
INNODB_FT_DEFAULT_STOPWORD
INNODB_FT_CONFIG
INNODB_BUFFER_PAGE
INNODB_SYS_TABLESPACES
INNODB_METRICS
INNODB_SYS_INDEXES
INNODB_SYS_VIRTUAL
INNODB_TABLESPACES_SCRUBBING
INNODB_SYS_SEMAPHORE_WAITS

%sql mysql+pymysql://root:********@172.17.0.1:12306/mysql

'Connected: root@mysql'

%sql select * from user;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
 * mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
   mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
3 rows affected.

Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	Delete_history_priv	plugin	authentication_string	password_expired	is_role
localhost	root	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	mysql_native_password	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	N	N
%	root	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	mysql_native_password	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	N	N
%	tjc	*28F13C854B563783661E6DC37017E94C41CBC595	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	mysql_native_password	*28F13C854B563783661E6DC37017E94C41CBC595	N	N

%sql drop user test;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
 * mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
0 rows affected.

[]

%sql select * from user;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
 * mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
2 rows affected.

Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	Delete_history_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections	plugin	authentication_string	password_expired	is_role	default_role	max_statement_time
localhost	root	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y					0	0	0	0	mysql_native_password	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	N	N		0.000000
%	root	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y					0	0	0	0	mysql_native_password	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	N	N		0.000000

刷新权限

%sql FLUSH PRIVILEGES;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
 * mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
0 rows affected.

[]

6. 创建和删除数据库

%sql mysql+pymysql://root:********@172.17.0.1:12306

'Connected: root@None'

%%sql
create database testdb;

 * mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
1 rows affected.

[]

%%sql
#drop database testdb;
drop database tjcdb;

 * mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
0 rows affected.
0 rows affected.

[]

%sql flush privileges

 * mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
0 rows affected.

[]

#如果数据库存在就不创建，不存在就创建，并将字符集设置为utf8
%sql CREATE DATABASE IF NOT EXISTS tjcdb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

 * mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
1 rows affected.

[]

创建对应数据库用户，并授予全部权限

%%sql
use tjcdb;
grant all privileges on tjcdb.* to tjc identified by '********';

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
 * mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
   mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
0 rows affected.
0 rows affected.





[]

%sql select * from mysql.user;

 * mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
3 rows affected.

Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	Delete_history_priv	plugin	authentication_string	password_expired	is_role
localhost	root	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	mysql_native_password	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	N	N
%	root	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	mysql_native_password	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	N	N
%	tjc	*28F13C854B563783661E6DC37017E94C41CBC595	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	mysql_native_password	*28F13C854B563783661E6DC37017E94C41CBC595	N	N

%sql mysql+pymysql://tjc:********@172.17.0.1:12306/tjcdb

'Connected: tjc@tjcdb'

7. 创建管理数据库表

%%sql
create table if not exists `memberinfo`(
    `memberid` int unsigned auto_increment comment '自动编号',
    `membername` varchar(10) not null comment '姓名',
    `gender` char(1) not null comment '性别',
    `birthday` date  comment '出生年月日',
    `address` varchar(100) not null comment '家庭住址',
    `latitude` float(12,9) null comment '纬度',
    `longitude` float(12,9) null comment '经度',
    `phone` varchar(20) not null comment '电话',
    `baptism` char(1) not null comment '水洗，Y或N',
`spirit` char(1) not null comment '灵洗,Y或N',
`group` varchar(10) not null comment '组别',
`area` varchar(10) not null comment '片区 ',
`church` varchar(10) not null comment '所属堂点 ',
`health` varchar(10) not null comment '身体状态：正常，无法出门，已故 ',
`fatherid` int(10) not null comment '父亲编号 ',
`motherid` int(10) not null comment '母亲编号 ',
`remarks` varchar(200) not null comment '备注 ',
    primary key(memberid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
0 rows affected.

[]

%%sql
show tables;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
1 rows affected.

Tables_in_tjcdb
memberinfo

%sql show table status from tjcdb

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
1 rows affected.

Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment	Max_index_length	Temporary
memberinfo	InnoDB	10	Dynamic	0	0	16384	0	0	0	1	2020-03-11 09:20:15	None	None	utf8_general_ci	None			0	N

%sql desc memberinfo

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
17 rows affected.

Field	Type	Null	Key	Default	Extra
memberid	int(10) unsigned	NO	PRI	None	auto_increment
membername	varchar(10)	NO		None
gender	char(1)	NO		None
birthday	date	YES		None
address	varchar(100)	NO		None
latitude	float(12,9)	YES		None
longitude	float(12,9)	YES		None
phone	varchar(20)	NO		None
baptism	char(1)	NO		None
spirit	char(1)	NO		None
membergroup	varchar(10)	YES		None
area	varchar(10)	NO		None
church	varchar(10)	NO		None
health	varchar(10)	NO		None
fatherid	int(10)	NO		None
motherid	int(10)	NO		None
remarks	varchar(200)	NO		None

修改表

%%sql
alter table memberinfo change column `group` membergroup varchar(10)

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
0 rows affected.

[]

8. 数据的插入、删除、更新、查询、条件查询

%sql mysql+pymysql://tjc:********@172.17.0.1:12306/tjcdb

'Connected: tjc@tjcdb'

插入

%%sql
insert into memberinfo (membername,gender,birthday,address,phone,baptism,spirit,membergroup,area,church,health,fatherid,motherid,remarks) 
values('何以勒','m','1983-12-20','厦门市思明区罗宾森广场一期琥珀阁2207室','13959248164','Y','Y','三组','火车站','厦港堂','正常',0,0,'')

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb



---------------------------------------------------------------------------

IntegrityError                            Traceback (most recent call last)

/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1245                     self.dialect.do_execute(
-> 1246                         cursor, statement, parameters, context
   1247                     )


/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    580     def do_execute(self, cursor, statement, parameters, context=None):
--> 581         cursor.execute(statement, parameters)
    582 


/opt/conda/lib/python3.7/site-packages/pymysql/cursors.py in execute(self, query, args)
    169 
--> 170         result = self._query(query)
    171         self._executed = query


/opt/conda/lib/python3.7/site-packages/pymysql/cursors.py in _query(self, q)
    327         self._clear_result()
--> 328         conn.query(q)
    329         self._do_get_result()


/opt/conda/lib/python3.7/site-packages/pymysql/connections.py in query(self, sql, unbuffered)
    516         self._execute_command(COMMAND.COM_QUERY, sql)
--> 517         self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    518         return self._affected_rows


/opt/conda/lib/python3.7/site-packages/pymysql/connections.py in _read_query_result(self, unbuffered)
    731             result = MySQLResult(self)
--> 732             result.read()
    733         self._result = result


/opt/conda/lib/python3.7/site-packages/pymysql/connections.py in read(self)
   1074         try:
-> 1075             first_packet = self.connection._read_packet()
   1076 


/opt/conda/lib/python3.7/site-packages/pymysql/connections.py in _read_packet(self, packet_type)
    683         packet = packet_type(buff, self.encoding)
--> 684         packet.check_error()
    685         return packet


/opt/conda/lib/python3.7/site-packages/pymysql/protocol.py in check_error(self)
    219             if DEBUG: print("errno =", errno)
--> 220             err.raise_mysql_exception(self._data)
    221 


/opt/conda/lib/python3.7/site-packages/pymysql/err.py in raise_mysql_exception(data)
    108     errorclass = error_map.get(errno, InternalError)
--> 109     raise errorclass(errno, errval)


IntegrityError: (1062, "Duplicate entry '何以勒-厦门市思明区罗宾森广场一期琥珀阁2207' for key 'idx_member'")

The above exception was the direct cause of the following exception:

IntegrityError                            Traceback (most recent call last)

<ipython-input-203-69d7c965a6c3> in <module>
----> 1 get_ipython().run_cell_magic('sql', '', "insert into memberinfo (membername,gender,birthday,address,phone,baptism,spirit,membergroup,area,church,health,fatherid,motherid,remarks) \nvalues('何以勒','m','1983-12-20','厦门市思明区罗宾森广场一期琥珀阁2207室','13959248164','Y','Y','三组','火车站','厦港堂','正常',0,0,'')\n")


/opt/conda/lib/python3.7/site-packages/IPython/core/interactiveshell.py in run_cell_magic(self, magic_name, line, cell)
   2350             with self.builtin_trap:
   2351                 args = (magic_arg_s, cell)
-> 2352                 result = fn(*args, **kwargs)
   2353             return result
   2354 


</opt/conda/lib/python3.7/site-packages/decorator.py:decorator-gen-157> in execute(self, line, cell, local_ns)


/opt/conda/lib/python3.7/site-packages/IPython/core/magic.py in <lambda>(f, *a, **k)
    185     # but it's overkill for just that one bit of state.
    186     def magic_deco(arg):
--> 187         call = lambda f, *a, **k: f(*a, **k)
    188 
    189         if callable(arg):


</opt/conda/lib/python3.7/site-packages/decorator.py:decorator-gen-156> in execute(self, line, cell, local_ns)


/opt/conda/lib/python3.7/site-packages/IPython/core/magic.py in <lambda>(f, *a, **k)
    185     # but it's overkill for just that one bit of state.
    186     def magic_deco(arg):
--> 187         call = lambda f, *a, **k: f(*a, **k)
    188 
    189         if callable(arg):


/opt/conda/lib/python3.7/site-packages/sql/magic.py in execute(self, line, cell, local_ns)
     93 
     94         try:
---> 95             result = sql.run.run(conn, parsed['sql'], self, user_ns)
     96 
     97             if result is not None and not isinstance(result, str) and self.column_local_vars:


/opt/conda/lib/python3.7/site-packages/sql/run.py in run(conn, sql, config, user_namespace)
    338             else:
    339                 txt = sqlalchemy.sql.text(statement)
--> 340                 result = conn.session.execute(txt, user_namespace)
    341             _commit(conn=conn, config=config)
    342             if result and config.feedback:


/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py in execute(self, object_, *multiparams, **params)
    980             raise exc.ObjectNotExecutableError(object_)
    981         else:
--> 982             return meth(self, multiparams, params)
    983 
    984     def _execute_function(self, func, multiparams, params):


/opt/conda/lib/python3.7/site-packages/sqlalchemy/sql/elements.py in _execute_on_connection(self, connection, multiparams, params)
    285     def _execute_on_connection(self, connection, multiparams, params):
    286         if self.supports_execution:
--> 287             return connection._execute_clauseelement(self, multiparams, params)
    288         else:
    289             raise exc.ObjectNotExecutableError(self)


/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_clauseelement(self, elem, multiparams, params)
   1099             distilled_params,
   1100             compiled_sql,
-> 1101             distilled_params,
   1102         )
   1103         if self._has_events or self.engine._has_events:


/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1248         except BaseException as e:
   1249             self._handle_dbapi_exception(
-> 1250                 e, statement, parameters, cursor, context
   1251             )
   1252 


/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1474                 util.raise_from_cause(newraise, exc_info)
   1475             elif should_wrap:
-> 1476                 util.raise_from_cause(sqlalchemy_exception, exc_info)
   1477             else:
   1478                 util.reraise(*exc_info)


/opt/conda/lib/python3.7/site-packages/sqlalchemy/util/compat.py in raise_from_cause(exception, exc_info)
    396     exc_type, exc_value, exc_tb = exc_info
    397     cause = exc_value if exc_value is not exception else None
--> 398     reraise(type(exception), exception, tb=exc_tb, cause=cause)
    399 
    400 


/opt/conda/lib/python3.7/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    150             value.__cause__ = cause
    151         if value.__traceback__ is not tb:
--> 152             raise value.with_traceback(tb)
    153         raise value
    154 


/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1244                 if not evt_handled:
   1245                     self.dialect.do_execute(
-> 1246                         cursor, statement, parameters, context
   1247                     )
   1248         except BaseException as e:


/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    579 
    580     def do_execute(self, cursor, statement, parameters, context=None):
--> 581         cursor.execute(statement, parameters)
    582 
    583     def do_execute_no_params(self, cursor, statement, context=None):


/opt/conda/lib/python3.7/site-packages/pymysql/cursors.py in execute(self, query, args)
    168         query = self.mogrify(query, args)
    169 
--> 170         result = self._query(query)
    171         self._executed = query
    172         return result


/opt/conda/lib/python3.7/site-packages/pymysql/cursors.py in _query(self, q)
    326         self._last_executed = q
    327         self._clear_result()
--> 328         conn.query(q)
    329         self._do_get_result()
    330         return self.rowcount


/opt/conda/lib/python3.7/site-packages/pymysql/connections.py in query(self, sql, unbuffered)
    515                 sql = sql.encode(self.encoding, 'surrogateescape')
    516         self._execute_command(COMMAND.COM_QUERY, sql)
--> 517         self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    518         return self._affected_rows
    519 


/opt/conda/lib/python3.7/site-packages/pymysql/connections.py in _read_query_result(self, unbuffered)
    730         else:
    731             result = MySQLResult(self)
--> 732             result.read()
    733         self._result = result
    734         if result.server_status is not None:


/opt/conda/lib/python3.7/site-packages/pymysql/connections.py in read(self)
   1073     def read(self):
   1074         try:
-> 1075             first_packet = self.connection._read_packet()
   1076 
   1077             if first_packet.is_ok_packet():


/opt/conda/lib/python3.7/site-packages/pymysql/connections.py in _read_packet(self, packet_type)
    682 
    683         packet = packet_type(buff, self.encoding)
--> 684         packet.check_error()
    685         return packet
    686 


/opt/conda/lib/python3.7/site-packages/pymysql/protocol.py in check_error(self)
    218             errno = self.read_uint16()
    219             if DEBUG: print("errno =", errno)
--> 220             err.raise_mysql_exception(self._data)
    221 
    222     def dump(self):


/opt/conda/lib/python3.7/site-packages/pymysql/err.py in raise_mysql_exception(data)
    107         errval = data[3:].decode('utf-8', 'replace')
    108     errorclass = error_map.get(errno, InternalError)
--> 109     raise errorclass(errno, errval)


IntegrityError: (pymysql.err.IntegrityError) (1062, "Duplicate entry '何以勒-厦门市思明区罗宾森广场一期琥珀阁2207' for key 'idx_member'")
[SQL: insert into memberinfo (membername,gender,birthday,address,phone,baptism,spirit,membergroup,area,church,health,fatherid,motherid,remarks) 
values('何以勒','m','1983-12-20','厦门市思明区罗宾森广场一期琥珀阁2207室','13959248164','Y','Y','三组','火车站','厦港堂','正常',0,0,'')]
(Background on this error at: http://sqlalche.me/e/gkpj)

%sql select * from memberinfo;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
1 rows affected.

memberid	membername	gender	birthday	address	latitude	longitude	phone	baptism	spirit	membergroup	area	church	health	fatherid	motherid	remarks
1	何以勒	m	1983-12-20	厦门市思明区罗宾森广场一期琥珀阁2207室	None	None	13959248164	Y	Y	三组	火车站	厦港堂	正常	0	0

%sql update memberinfo set membergroup='中坚三组' where memberid=1;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
1 rows affected.

[]

%sql select * from memberinfo where membername='何以勒'

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
1 rows affected.

memberid	membername	gender	birthday	address	latitude	longitude	phone	baptism	spirit	membergroup	area	church	health	fatherid	motherid	remarks
1	何以勒	m	1983-12-20	厦门市思明区罗宾森广场一期琥珀阁2207室	None	None	13959248164	Y	Y	中坚三组	火车站	厦港堂	正常	0	0

正则查询

%sql select * from memberinfo where phone regexp '[^7]'

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
1 rows affected.

memberid	membername	gender	birthday	address	latitude	longitude	phone	baptism	spirit	membergroup	area	church	health	fatherid	motherid	remarks
1	何以勒	m	1983-12-20	厦门市思明区罗宾森广场一期琥珀阁2207室	0.0	0.0	13959248164	Y	Y	中坚三组	火车站	厦港堂	正常	0	0

9. Alter 修改操作

修改表名

%sql alter table memberinfo rename to memberinfo_bak;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
0 rows affected.

[]

%sql show tables;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
1 rows affected.

Tables_in_tjcdb
memberinfo_bak

%sql rename table memberinfo_bak to memberinfo;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
0 rows affected.

[]

%sql show tables

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
1 rows affected.

Tables_in_tjcdb
memberinfo

%sql desc memberinfo;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
17 rows affected.

Field	Type	Null	Key	Default	Extra
memberid	int(10) unsigned	NO	PRI	None	auto_increment
membername	varchar(10)	NO		None
gender	char(1)	NO		None
birthday	date	YES		None
address	varchar(100)	NO		None
latitude	float(12,9)	YES		None
longitude	float(12,9)	YES		None
phone	varchar(20)	NO		None
baptism	char(1)	NO		None
spirit	char(1)	NO		None
membergroup	varchar(10)	YES		None
area	varchar(10)	NO		None
church	varchar(10)	NO		None
health	varchar(10)	NO		None
fatherid	int(10)	NO		None
motherid	int(10)	NO		None
remarks	varchar(200)	NO		None

修改字段属性

%%sql 
update memberinfo set latitude=0,longitude=0 where 1=1;
alter table memberinfo modify latitude float(12,9) not null default 0;
alter table memberinfo modify longitude float(12,9) not null default 0;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
1 rows affected.
0 rows affected.
0 rows affected.





[]

%sql select * from memberinfo

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
1 rows affected.

memberid	membername	gender	birthday	address	latitude	longitude	phone	baptism	spirit	membergroup	area	church	health	fatherid	motherid	remarks
1	何以勒	m	1983-12-20	厦门市思明区罗宾森广场一期琥珀阁2207室	0.0	0.0	13959248164	Y	Y	中坚三组	火车站	厦港堂	正常	0	0

10. 创建索引

%sql create unique index idx_member on memberinfo(membername,address)

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
0 rows affected.

[]

%sql create index idx_phone on memberinfo(phone)

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
0 rows affected.

[]

%sql show index from memberinfo

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
4 rows affected.

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Sub_part	Packed	Index_type
memberinfo	0	PRIMARY	1	memberid	A	None	None	BTREE
memberinfo	0	idx_member	1	membername	A	None	None	BTREE
memberinfo	0	idx_member	2	address	A	None	None	BTREE
memberinfo	1	idx_phone	1	phone	A	None	None	BTREE

11. 复制表

%sql show create table memberinfo;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
1 rows affected.

Table	Create Table
memberinfo	CREATE TABLE `memberinfo` ( `memberid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自动编号', `membername` varchar(10) NOT NULL COMMENT '姓名', `gender` char(1) NOT NULL COMMENT '性别', `birthday` date DEFAULT NULL COMMENT '出生年月日', `address` varchar(100) NOT NULL COMMENT '家庭住址', `latitude` float(12,9) NOT NULL DEFAULT 0.000000000, `longitude` float(12,9) NOT NULL DEFAULT 0.000000000, `phone` varchar(20) NOT NULL COMMENT '电话', `baptism` char(1) NOT NULL COMMENT '水洗，Y或N', `spirit` char(1) NOT NULL COMMENT '灵洗,Y或N', `membergroup` varchar(10) DEFAULT NULL, `area` varchar(10) NOT NULL COMMENT '片区 ', `church` varchar(10) NOT NULL COMMENT '所属堂点 ', `health` varchar(10) NOT NULL COMMENT '身体状态：正常，无法出门，已故 ', `fatherid` int(10) NOT NULL COMMENT '父亲编号 ', `motherid` int(10) NOT NULL COMMENT '母亲编号 ', `remarks` varchar(200) NOT NULL COMMENT '备注 ', PRIMARY KEY (`memberid`), UNIQUE KEY `idx_member` (`membername`,`address`), KEY `idx_phone` (`phone`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

Table

Create Table

memberinfo

CREATE TABLE `memberinfo` (
`memberid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自动编号',
`membername` varchar(10) NOT NULL COMMENT '姓名',
`gender` char(1) NOT NULL COMMENT '性别',
`birthday` date DEFAULT NULL COMMENT '出生年月日',
`address` varchar(100) NOT NULL COMMENT '家庭住址',
`latitude` float(12,9) NOT NULL DEFAULT 0.000000000,
`longitude` float(12,9) NOT NULL DEFAULT 0.000000000,
`phone` varchar(20) NOT NULL COMMENT '电话',
`baptism` char(1) NOT NULL COMMENT '水洗，Y或N',
`spirit` char(1) NOT NULL COMMENT '灵洗,Y或N',
`membergroup` varchar(10) DEFAULT NULL,
`area` varchar(10) NOT NULL COMMENT '片区 ',
`church` varchar(10) NOT NULL COMMENT '所属堂点 ',
`health` varchar(10) NOT NULL COMMENT '身体状态：正常，无法出门，已故 ',
`fatherid` int(10) NOT NULL COMMENT '父亲编号 ',
`motherid` int(10) NOT NULL COMMENT '母亲编号 ',
`remarks` varchar(200) NOT NULL COMMENT '备注 ',
PRIMARY KEY (`memberid`),
UNIQUE KEY `idx_member` (`membername`,`address`),
KEY `idx_phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

12. MySql元数据

#服务器版本信息
%sql select version()

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
1 rows affected.

version()
10.4.8-MariaDB-1:10.4.8+maria~bionic

#当前数据库名
%sql select database()

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
1 rows affected.

database()
tjcdb

%sql select user();
#%sql show status;
#%sql show variables;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
1 rows affected.

user()
tjc@172.17.0.1

13. 数据导入、导出

数据导出

%sql mysql+pymysql://tjc:********@172.17.0.1:12306/tjcdb

'Connected: tjc@tjcdb'

%%sql
select *  into outfile 'memberinfo.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
from memberinfo;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://root:***@172.17.0.1:12306/tjcdb
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
1 rows affected.

%%sql 
mysql+pymysql://root:********@172.17.0.1:12306/tjcdb
select * from mysql.user;

3 rows affected.

Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	Delete_history_priv	plugin	authentication_string	password_expired	is_role
localhost	root	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	mysql_native_password	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	N	N
%	root	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	mysql_native_password	*F2C8A97A21E42B3735A4C88136E139460F40B4FC	N	N
%	tjc	*28F13C854B563783661E6DC37017E94C41CBC595	N	N	N	N	N	N	N	N	N	Y	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	mysql_native_password	*28F13C854B563783661E6DC37017E94C41CBC595	N	N

%sql grant file on *.* to tjc;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
 * mysql+pymysql://root:***@172.17.0.1:12306/tjcdb
   mysql+pymysql://test:***@172.17.0.1:12306
   mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
0 rows affected.





[]

%%bash 
mysqldump -utjc -h 172.17.0.1 -P 12306 -p******** tjcdb >tjcdb.dmp

mysqldump: [Warning] Using a password on the command line interface can be insecure.

数据导入

%sql truncate table memberinfo;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
 * mysql+pymysql://root:***@172.17.0.1:12306/tjcdb
   mysql+pymysql://test:***@172.17.0.1:12306
   mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
0 rows affected.





[]

%sql select * from memberinfo;

   mysql+pymysql://root:***@172.17.0.1:12306
   mysql+pymysql://root:***@172.17.0.1:12306/
   mysql+pymysql://root:***@172.17.0.1:12306/mysql
   mysql+pymysql://root:***@172.17.0.1:12306/tjcdb
   mysql+pymysql://test:***@172.17.0.1:12306
 * mysql+pymysql://tjc:***@172.17.0.1:12306/tjcdb
1 rows affected.

memberid	membername	gender	birthday	address	latitude	longitude	phone	baptism	spirit	membergroup	area	church	health	fatherid	motherid	remarks
1	何以勒	m	1983-12-20	厦门市思明区罗宾森广场一期琥珀阁2207室	0.0	0.0	13959248164	Y	Y	中坚三组	火车站	厦港堂	正常	0	0

%%bash
mysqlimport -utjc -p******** -h 172.17.0.1 -P 12306 -r --local --fields-enclosed-by='"' --fields-terminated-by="," \
   --lines-terminated-by="\n" tjcdb memberinfo.txt
#mysqlimport|grep -i table

tjcdb.memberinfo: Records: 1  Deleted: 1  Skipped: 0  Warnings: 0

mysqlimport: [Warning] Using a password on the command line interface can be insecure.

14. Mysql函数（略）

详见