Mysql操作
参考林海峰博客：http://www.cnblogs.com/linhaifeng/articles/7356064.html
一、初识mysql数据库
1、Mysql介绍
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下公司。MySQL 最流行的关系型数据库管理系统，在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
mysql是什么
#mysql就是一个基于socket编写的C/S架构的软件 #客户端软件 mysql自带：如mysql命令，mysqldump命令等 python模块：如pymysql
数据库管理软件分类
#分两大类： 关系型：如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用 非关系型：mongodb，redis，memcache #可以简单的理解为： 关系型数据库需要有表结构 非关系型数据库是key-value存储的，没有表结构
2、下载安装
Linux版本
#二进制rpm包安装 yum -y install mysql-server mysql
1.解压tar包 cd /software tar -xzvf mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz mv mysql-5.6.21-linux-glibc2.5-x86_64 mysql-5.6.21 2.添加用户与组 groupadd mysql useradd -r -g mysql mysql chown -R mysql:mysql mysql-5.6.21 3.安装数据库 su mysql cd mysql-5.6.21/scripts ./mysql_install_db --user=mysql --basedir=/software/mysql-5.6.21 --datadir=/software/mysql-5.6.21/data 4.配置文件 cd /software/mysql-5.6.21/support-files cp my-default.cnf /etc/my.cnf cp mysql.server /etc/init.d/mysql vim /etc/init.d/mysql #若mysql的安装目录是/usr/local/mysql,则可省略此步 修改文件中的两个变更值 basedir=/software/mysql-5.6.21 datadir=/software/mysql-5.6.21/data 5.配置环境变量 vim /etc/profile export MYSQL_HOME="/software/mysql-5.6.21" export PATH="$PATH:$MYSQL_HOME/bin" source /etc/profile 6.添加自启动服务 chkconfig --add mysql chkconfig mysql on 7.启动mysql service mysql start 8.登录mysql及改密码与配置远程访问 mysqladmin -u root password 'your_password' #修改root用户密码 mysql -u root -p #登录mysql,需要输入密码 mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'your_password' WITH GRANT OPTION; #允许root用户远程访问 mysql>FLUSH PRIVILEGES; #刷新权限 源码安装mysql
1. 解压 tar zxvf mariadb-5.5.31-linux-x86_64.tar.gz mv mariadb-5.5.31-linux-x86_64 /usr/local/mysql //必需这样，很多脚本或可执行程序都会直接访问这个目录 2. 权限 groupadd mysql //增加 mysql 属组 useradd -g mysql mysql //增加 mysql 用户 并归于mysql 属组 chown mysql:mysql -Rf /usr/local/mysql // 设置 mysql 目录的用户及用户组归属。 chmod +x -Rf /usr/local/mysql //赐予可执行权限 3. 拷贝配置文件 cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf //复制默认mysql配置 文件到/etc目录 4. 初始化 /usr/local/mysql/scripts/mysql_install_db --user=mysql //初始化数据库 cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql //复制mysql服务程序 到系统目录 chkconfig mysql on //添加mysql 至系统服务并设置为开机启动 service mysql start //启动mysql 5. 环境变量配置 vim /etc/profile //编辑profile,将mysql的可执行路径加入系统PATH export PATH=/usr/local/mysql/bin:$PATH source /etc/profile //使PATH生效。 6. 账号密码 mysqladmin -u root password 'yourpassword' //设定root账号及密码 mysql -u root -p //使用root用户登录mysql use mysql //切换至mysql数据库。 select user,host,password from user; //查看系统权限 drop user ''@'localhost'; //删除不安全的账户 drop user root@'::1'; drop user root@127.0.0.1; select user,host,password from user; //再次查看系统权限，确保不安全的账户均被删除。 flush privileges; //刷新权限 7. 一些必要的初始配置 1）修改字符集为UTF8 vi /etc/my.cnf 在[client]下面添加 default-character-set = utf8 在[mysqld]下面添加 character_set_server = utf8 2）增加错误日志 vi /etc/my.cnf 在[mysqld]下面添加： log-error = /usr/local/mysql/log/error.log general-log-file = /usr/local/mysql/log/mysql.log 3) 设置为不区分大小写，linux下默认会区分大小写。 vi /etc/my.cnf 在[mysqld]下面添加： lower_case_table_name=1 修改完重启：#service mysql restart 源码安装mariadb
Windows版本
#1、下载：MySQL Community Server 5.7.16 http://dev.mysql.com/downloads/mysql/ #2、解压 如果想要让MySQL安装在指定目录，那么就将解压后的文件夹移动到指定目录，如：C:\mysql-5.7.16-winx64 #3、添加环境变量 【右键计算机】--》【属性】--》【高级系统设置】--》【高级】--》【环境变量】--》【在第二个内容框中找到 变量名为Path 的一行，双击】 --> 【将MySQL的bin目录路径追加到变值值中，用 ； 分割】 #4、初始化 mysqld --initialize-insecure #5、启动MySQL服务 mysqld # 启动MySQL服务 #6、启动MySQL客户端并连接MySQL服务 mysql -u root -p # 连接MySQL服务器 安装
上一步解决了一些问题，但不够彻底，因为在执行【mysqd】启动MySQL服务器时，当前终端会被hang住，那么做一下设置即可解决此问题： 注意：--install前，必须用mysql启动命令的绝对路径 # 制作MySQL的Windows服务，在终端执行此命令： "c:\mysql-5.7.16-winx64\bin\mysqld" --install # 移除MySQL的Windows服务，在终端执行此命令： "c:\mysql-5.7.16-winx64\bin\mysqld" --remove 注册成服务之后，以后再启动和关闭MySQL服务时，仅需执行如下命令： # 启动MySQL服务 net start mysql # 关闭MySQL服务 net stop mysql 将MySQL服务制作成windows服务
3、Mysql软件基本管理
1）查看启动
[root@egon ~]# systemctl start mariadb #启动 [root@egon ~]# systemctl enable mariadb #设置开机自启动 Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service. [root@egon ~]# ps aux |grep mysqld |grep -v grep #查看进程，mysqld_safe为启动mysql的脚本文件，内部调用mysqld命令 mysql 3329 0.0 0.0 113252 1592 ? Ss 16:19 0:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr mysql 3488 0.0 2.3 839276 90380 ? Sl 16:19 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock [root@egon ~]# netstat -an |grep 3306 #查看端口 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN [root@egon ~]# ll -d /var/lib/mysql #权限不对，启动不成功，注意user和group drwxr-xr-x 5 mysql mysql 4096 Jul 20 16:28 /var/lib/mysql
安装完mysql 之后，登陆以后，不管运行任何命令，总是提示这个 mac mysql error You must reset your password using ALTER USER statement before executing this statement. 解决方法： step 1: SET PASSWORD = PASSWORD('your new password'); step 2: ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER; step 3: flush privileges;
2）登录，设置密码
初始状态下，管理员root，密码为空，默认只允许从本机登录localhost 设置密码 [root@egon ~]# mysqladmin -uroot password "123" 设置初始密码 由于原密码为空，因此-p可以不用 [root@egon ~]# mysqladmin -uroot -p"123" password "456" 修改mysql密码,因为已经有密码了，所以必须输入原密码才能设置新密码 命令格式: [root@egon ~]# mysql -h172.31.0.2 -uroot -p456 [root@egon ~]# mysql -uroot -p [root@egon ~]# mysql 以root用户登录本机，密码为空
3）忘记密码
linux平台下，破解密码的两种方式
[root@egon ~]# rm -rf /var/lib/mysql/mysql #所有授权信息全部丢失！！！ [root@egon ~]# systemctl restart mariadb [root@egon ~]# mysql
[root@egon ~]# vim /etc/my.cnf #mysql主配置文件 [mysqld] skip-grant-table [root@egon ~]# systemctl restart mariadb [root@egon ~]# mysql MariaDB [(none)]> update mysql.user set password=password("123") where user="root" and host="localhost"; MariaDB [(none)]> flush privileges; MariaDB [(none)]> \q [root@egon ~]# #打开/etc/my.cnf去掉skip-grant-table,然后重启 [root@egon ~]# systemctl restart mariadb [root@egon ~]# mysql -u root -p123 #以新密码登录
windows平台下，5.7版本mysql，破解密码的两种方法
#1 关闭mysql #2 在cmd中执行：mysqld --skip-grant-tables #3 在cmd中执行：mysql #4 执行如下sql： update mysql.user set authentication_string=password('') where user = 'root'; flush privileges; #5 tskill mysqld #或taskkill -f /PID 7832 #6 重新启动mysql
#1. 关闭mysql，可以用tskill mysqld将其杀死 #2. 在解压目录下，新建mysql配置文件my.ini #3. my.ini内容,指定 [mysqld] skip-grant-tables #4.启动mysqld #5.在cmd里直接输入mysql登录，然后操作 update mysql.user set authentication_string=password('') where user='root and host='localhost'; flush privileges; #6.注释my.ini中的skip-grant-tables，然后启动myqsld，然后就可以以新密码登录了
4）在windows下，为mysql服务指定配置文件
强调：配置文件中的注释可以有中文，但是配置项中不能出现中文
#在mysql的解压目录下，新建my.ini,然后配置 #1. 在执行mysqld命令时，下列配置会生效，即mysql服务启动时生效 [mysqld] ;skip-grant-tables port=3306 character_set_server=utf8 default-storage-engine=innodb innodb_file_per_table=1 #解压的目录 basedir=E:\mysql-5.7.19-winx64 #data目录 datadir=E:\my_data #在mysqld --initialize时，就会将初始数据存入此处指定的目录，在初始化之后，启动mysql时，就会去这个目录里找数据 #2. 针对客户端命令的全局配置，当mysql客户端命令执行时，下列配置生效 [client] port=3306 default-character-set=utf8 user=root password=123 #3. 只针对mysql这个客户端的配置，2中的是全局配置，而此处的则是只针对mysql这个命令的局部配置 [mysql] ;port=3306 ;default-character-set=utf8 user=egon password=4573 #！！！如果没有[mysql],则用户在执行mysql命令时的配置以[client]为准
5）统一字符编码
#1. 修改配置文件 [mysqld] default-character-set=utf8 [client] default-character-set=utf8 [mysql] default-character-set=utf8 #mysql5.5以上：修改方式有所改动 [mysqld] character-set-server=utf8 collation-server=utf8_general_ci [client] default-character-set=utf8 [mysql] default-character-set=utf8 #2. 重启服务 #3. 查看修改结果： \s show variables like '%char%'
4、初始sql语句
SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型： #1、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER #2、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT #3、DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE
#1. 操作文件夹 增：create database db1 charset utf8; 查：show databases; 改：alter database db1 charset latin1; 删除: drop database db1; #2. 操作文件 先切换到文件夹下：use db1 增：create table t1(id int,name char); 查：show tables 改：alter table t1 modify name char(3); alter table t1 change name name1 char(2); 删：drop table t1; #3. 操作文件中的内容/记录 增：insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3'); 查：select * from t1; 改：update t1 set name='sb' where id=2; 删：delete from t1 where id=1; 清空表： delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。 truncate table t1;数据量大，删除速度比上一条快，且直接从零开始， auto_increment 表示：自增 primary key 表示：约束（不能重复且不能为空）；加速查找
二、库相关操作
1、系统数据库
information_schema： 虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象
mysql： 授权库，主要存储系统用户的权限信息
test： MySQL数据库系统自动创建的测试数据库
2、创建数据库
1 语法(help create database)
CREATE DATABASE 数据库名 charset utf8;
2 数据库命名规则：
可以由字母、数字、下划线、＠、＃、＄
不区分大小写 唯一性 不能使用关键字如 create select 不能单独使用数字 最长128位
3、数据库相关操作
1 查看数据库 show databases; show create database db1; select database(); 2 选择数据库 USE 数据库名 3 删除数据库 DROP DATABASE 数据库名; 4 修改数据库 alter database db1 charset utf8;
三、表相关操作
1、存储引擎介绍
查看存储引擎
MariaDB [(none)]> show engines\G #查看所有支持的存储引擎 MariaDB [(none)]> show variables like 'storage_engine%'; #查看正在使用的存储引擎
使用存储引擎
方法1：建表时指定
MariaDB [db1]> create table innodb_t1(id int,name char)engine=innodb; MariaDB [db1]> show create table innodb_t1;
方法2：在配置文件中指定默认的存储引擎
/etc/my.cnf [mysqld] default-storage-engine=INNODB innodb_file_per_table=1
参考博客：http://www.cnblogs.com/linhaifeng/articles/7213670.html
2、创建表
#语法： create table 表名( 字段名1 类型(宽度) 约束条件, 字段名2 类型(宽度) 约束条件, 字段名3 类型(宽度) 约束条件 ); #注意： 1. 在同一张表中，字段名是不能相同 2. 宽度和约束条件可选 3. 字段名和类型是必须的
mysql> create database db4; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db3 | | db4 | | mysql | | performance_schema | | sys | +--------------------+ 8 rows in set (0.00 sec) mysql> use db4; Database changed mysql> create table t1(id int,name varchar(20),age int); Query OK, 0 rows affected (0.04 sec) mysql> show tables; +---------------+ | Tables_in_db4 | +---------------+ | t1 | +---------------+ 1 row in set (0.00 sec) mysql> desc t1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> select * from t1; Empty set (0.00 sec)
mysql> create database db4; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db3 | | db4 | | mysql | | performance_schema | | sys | +--------------------+ 8 rows in set (0.00 sec) mysql> use db4; Database changed mysql> create table t1(id int,name varchar(20),age int); Query OK, 0 rows affected (0.04 sec) mysql> show tables; +---------------+ | Tables_in_db4 | +---------------+ | t1 | +---------------+ 1 row in set (0.00 sec) mysql> desc t1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> select * from t1; Empty set (0.00 sec)
3、查看表结构
# 查看表结构 mysql> desc t1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) # 查看详细表结构 mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql>
4、数据类型
#1. 数字： 整型：tinyinit int bigint 小数： float ：在位数比较短的情况下不精准 double ：在位数比较长的情况下不精准 0.000001230123123123 存成：0.000001230000 decimal：（如果用小数，则用推荐使用decimal） 精准 内部原理是以字符串形式去存 #2. 字符串： char（10）：简单粗暴，浪费空间，存取速度快 root存成root000000 varchar：精准，节省空间，存取速度慢 sql优化：创建表时，定长的类型往前放，变长的往后放 比如性别 比如地址或描述信息 >255个字符，超了就把文件路径存放到数据库中。 比如图片，视频等找一个文件服务器，数据库中只存路径或url。 #3. 时间类型： 最常用：datetime #4. 枚举类型（enum）与集合类型(set)
参考博客：http://www.cnblogs.com/linhaifeng/articles/7233411.html
5、表完整性约束
约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
主要分为：
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录 FOREIGN KEY (FK) 标识该字段为该表的外键 NOT NULL 标识该字段不能为空 UNIQUE KEY (UK) 标识该字段的值是唯一的 AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键） DEFAULT 为该字段设置默认值 UNSIGNED 无符号 ZEROFILL 使用0填充
说明：
1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值 2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值 sex enum('male','female') not null default 'male' age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20 3. 是否是key 主键 primary key 外键 foreign key 索引 (index,unique...)
参考博客：http://www.cnblogs.com/linhaifeng/articles/7238814.html
6、修改表ALTER TABLE
语法： 1. 修改表名 ALTER TABLE 表名 RENAME 新表名; 2. 增加字段 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…], ADD 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; 3. 删除字段 ALTER TABLE 表名 DROP 字段名; 4. 修改字段 ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
示例： 1. 修改存储引擎 mysql> alter table service -> engine=innodb; 2. 添加字段 mysql> alter table student10 -> add name varchar(20) not null, -> add age int(3) not null default 22; mysql> alter table student10 -> add stu_num varchar(10) not null after name; //添加name字段之后 mysql> alter table student10 -> add sex enum('male','female') default 'male' first; //添加到最前面 3. 删除字段 mysql> alter table student10 -> drop sex; mysql> alter table service -> drop mac; 4. 修改字段类型modify mysql> alter table student10 -> modify age int(3); mysql> alter table student10 -> modify id int(11) not null primary key auto_increment; //修改为主键 5. 增加约束（针对已有的主键增加auto_increment） mysql> alter table student10 modify id int(11) not null primary key auto_increment; ERROR 1068 (42000): Multiple primary key defined mysql> alter table student10 modify id int(11) not null auto_increment; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 6. 对已经存在的表增加复合主键 mysql> alter table service2 -> add primary key(host_ip,port); 7. 增加主键 mysql> alter table student1 -> modify name varchar(10) not null primary key; 8. 增加主键和自动增长 mysql> alter table student1 -> modify id int not null primary key auto_increment; 9. 删除主键 a. 删除自增约束 mysql> alter table student10 modify id int(11) not null; b. 删除主键 mysql> alter table student10 -> drop primary key; 示例
7、复制表
复制表结构＋记录 （key不会复制: 主键、外键和索引） mysql> create table new_service select * from service; 只复制表结构 mysql> select * from service where 1=2; //条件为假，查不到任何记录 Empty set (0.00 sec) mysql> create table new1_service select * from service where 1=2; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create table t4 like employees;
8、删除表
DROP TABLE 表名;
四、记录相关操作
1、插入数据insert
1. 插入完整数据（顺序插入） 语法一： INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n); 语法二： INSERT INTO 表名 VALUES (值1,值2,值3…值n); 2. 指定字段插入数据 语法： INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…); 3. 插入多条记录 语法： INSERT INTO 表名 VALUES (值1,值2,值3…值n), (值1,值2,值3…值n), (值1,值2,值3…值n); 4. 插入查询结果 语法： INSERT INTO 表名(字段1,字段2,字段3…字段n) SELECT (字段1,字段2,字段3…字段n) FROM 表2 WHERE …;
2、更新数据update
语法： UPDATE 表名 SET 字段1=值1, 字段2=值2, WHERE CONDITION; 示例： UPDATE mysql.user SET password=password(‘123’) where user=’root’ and host=’localhost’;
3、删除数据delete
语法： DELETE FROM 表名 WHERE CONITION; 示例： DELETE FROM mysql.user WHERE password=’’; 练习： 更新MySQL root用户密码为mysql123 删除除从本地登录的root用户以外的所有用户
4、查询数据select
1）单表查询 参考：http://www.cnblogs.com/linhaifeng/articles/7267592.html
2）多表查询 参考：http://www.cnblogs.com/linhaifeng/articles/7267596.html
5、权限管理
#授权表 user #该表放行的权限，针对：所有数据，所有库下所有表，以及表下的所有字段 db #该表放行的权限，针对：某一数据库，该数据库下的所有表，以及表下的所有字段 tables_priv #该表放行的权限。针对：某一张表，以及该表下的所有字段 columns_priv #该表放行的权限，针对：某一个字段 #按图解释： user：放行db1，db2及其包含的所有 db：放行db1，及其db1包含的所有 tables_priv:放行db1.table1，及其该表包含的所有 columns_prive:放行db1.table1.column1，只放行该字段
五、数据备份、pymysql模块
1、mysql数据备份
1）使用mysqldump实现逻辑备份
#语法： # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql #示例： #单库备份 mysqldump -uroot -p123 db1 > db1.sql mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql #多库备份 mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql #备份所有库 mysqldump -uroot -p123 --all-databases > all.sql
2）恢复逻辑备份
#方法一： [root@egon backup]# mysql -uroot -p123 < /backup/all.sql #方法二： mysql> use db1; mysql> SET SQL_LOG_BIN=0; mysql> source /root/db1.sql #注：如果备份/恢复单个库时，可以修改sql文件 DROP database if exists school; create database school; use school;
3）数据库迁移
务必保证在相同版本之间迁移 # mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h 目标IP -uroot -p456
2、pymysql模块
1）查询
import pymysql user=input('用户名: ').strip() pwd=input('密码: ').strip() #链接 conn=pymysql.connect(host='localhost',user='root',password='123',database='egon',charset='utf8') #游标 cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示 #cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #执行sql语句 sql='select * from userinfo where name=%s and password=%s' print(sql) res=cursor.execute(sql，（user, pwd）) #执行sql语句，返回sql查询成功的记录数目 print(res) cursor.close() conn.close() if res: print('登录成功') else: print('登录失败')
2）增、删、改：conn.commit()
import pymysql #链接 conn=pymysql.connect(host='localhost',user='root',password='123',database='egon') #游标 cursor=conn.cursor() #执行sql语句 #part1 sql='insert into userinfo(name,password) values("root","123456");' res=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数 print(res) #part2 sql='insert into userinfo(name,password) values(%s,%s);' res=cursor.execute(sql,("root","123456")) #执行sql语句，返回sql影响成功的行数 print(res) #part3 sql='insert into userinfo(name,password) values(%s,%s);' res=cursor.executemany(sql,[("root","123456"),("lhf","12356"),("eee","156")]) #执行sql语句，返回sql影响成功的行数 print(res) conn.commit() #提交后才发现表中插入记录成功 cursor.close() conn.close()
3）查：fetchone，fetchmany，fetchall
import pymysql #链接 conn=pymysql.connect(host='localhost',user='root',password='123',database='egon') #游标 cursor=conn.cursor() #执行sql语句 sql='select * from userinfo;' rows=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数rows,将结果放入一个集合，等待被查询 # cursor.scroll(3,mode='absolute') # 相对绝对位置移动 # cursor.scroll(3,mode='relative') # 相对当前位置移动 res1=cursor.fetchone() res2=cursor.fetchone() res3=cursor.fetchone() res4=cursor.fetchmany(2) res5=cursor.fetchall() print(res1) print(res2) print(res3) print(res4) print(res5) print('%s rows in set (0.00 sec)' %rows) conn.commit() #提交后才发现表中插入记录成功 cursor.close() conn.close() ''' (1, 'root', '123456') (2, 'root', '123456') (3, 'root', '123456') ((4, 'root', '123456'), (5, 'root', '123456')) ((6, 'root', '123456'), (7, 'lhf', '12356'), (8, 'eee', '156')) rows in set (0.00 sec) '''
4）获取插入的最后一条数据的自增ID
import pymysql conn=pymysql.connect(host='localhost',user='root',password='123',database='egon') cursor=conn.cursor() sql='insert into userinfo(name,password) values("xxx","123");' rows=cursor.execute(sql) print(cursor.lastrowid) #在插入语句后查看 conn.commit() cursor.close() conn.close()