MySQL第三篇
1、锁(MySQL自动加锁和释放锁)
目的 :解决客户端并发访问的冲突问题
2、锁分类
1、锁类型
1、读锁(共享锁)
select :加读锁之后别人可以查询,但是不能更改(update)
2、写锁(互斥锁、排他锁)
update :加写锁之后,别人不能查、不能改
2、锁粒度
1、行级锁 :可加读锁、写锁
2、表级锁 :可加读锁、写锁
3、存储引擎(处理表的处理器)
1、查看所有存储引擎 show engines;
2、查看已有表存储引擎 show create table 表名;
3、创建表时指定存储引擎 create table 表名(…)engine=MyISAM;
4、已有表时修改存储引擎 alter table 表名 engine=InnoDB;
4、常用存储引擎特点
1、InnoDB特点
1、支持外键、行级锁、事务
2、共享表空间
3、表名.frm :表结构和索引信息
表名.ibd :表记录
2、MyISAM特点
1、支持表级锁
2、独享表空间
3、表名.frm :表结构
表名.MYD :表记录
表名.MYI :索引信息
3、MEMORY特点
1、表结构存储在硬盘,表记录存储在内存
2、服务/主机重启后,表记录消失
5、如何选择使用哪个存储引擎
1、执行查询操作多的表用MyISAM(使用InnoDB浪费资源)
2、执行写操作多的表用InnoDB
6、数据备份(mysqldump,在Linux终端中操作)
1、mysqldump -u用户名 -p 源库名 > XXX.sql
2、源库名表示方式
--all-databases 备份所有库
库名 备份单个库
-B 库1 库2 库3 备份多个库
库名 表1 表2 表3 备份指定库的多个表
3、示例
1、备份所有库,放到mydata目录下:all.sql
mysqldump -uroot -p --all-databases > all.sql
2、备份db4库,db4.sql
mysqldump -uroot -p db4 > db4.sql
3、备份MOSHOU库和db4库,md.sql
mysqldump -uroot -p -B MOSHOU db4 > md.sql
4、备份db3库中sheng、city、xian三张表,scx.sql
mysqldump -uroot -p db3 sheng city xian > scx.sql
7、数据恢复(Linux终端中操作)
1、mysql -u用户名 -p 目标库名 < XXX.sql
2、示例
1、先创建db3库
mysql> create database db3 character set utf8;
mysql> exit();
终端 :mysql -uroot -p --one-database db3 < db3.sql
3、注意
1、恢复库时,会将表中数据覆盖,新增表不会删除
2、恢复时,如果要恢复的库不存在,则先创建空库
8、数据导入
1、作用 :把文件系统的内容导入到数据库中
2、语法格式
load data infile "文件名"
into table 表名
fields terminated by "分隔符"
lines terminated by "\n";
e.g.
1、在数据库创建对应的表
create table score(
id int,
name varchar(20),
score float(5,2),
phnumber char(11),
class char(7)
)charset=utf8;
2、把文件拷贝到数据库的搜索路径中
1、查看搜索路径
show variables like "secure_file_priv"; # /var/lib/mysql-files
2、拷贝文件
sudo cp scoreTable.csv /var/lib/mysql-files/
3、执行数据导入语句
load data infile "/var/lib/mysql-files/scoreTable.csv" #绝对路径
into table score
fields terminated by ","
lines terminated by "\n";
9、数据导出
1、把数据库表的记录到处到系统文件里
2、语法格式
select ... from 表名
into outfile "文件名"
fileds terminated by "分隔符"
lines terminated by "\n";
e.g.
select name,age,score from stu.math
into outfile "/var/lib/mysql-files/math.csv"
fields terminated by ","
lines terminated by "\n";
10、表的复制
1、语法
create table 表名 select … from 表名 where 条件;
2.复制表结构
create table 表名 select … from 表名 where false;
11、MySQL用户管理
1、开启远程连接
1、sudo -i
2、cd /etc/mysql/mysql.conf.d/
3、subl mysqld.cnf
44行 :bind-address = 127.0.0.1
4、/etc/init.d/mysql restart
2、添加授权用户
1、查看用户
select Host,User from mysql.user;
2、创建用户
create user "user_name"@"host" identified by "password";
et:
create user "tiger"@"localhost" identified by "123456"; 只能本机登录
create user "dog"@"%" identified by "123456"; 允许远程连接
3、授权
grant 权限列表 on 库.表 to "用户名"@"%"
identified by "密码"
with grant option;
权限列表 :all privileges 、select 、drop、update
库.表 :库.* 、 *.*(所有库所有表)
4.删除用户
drop user "user_name"@"host"
5.设置与更改用户密码
set password for "user_name"@"host" = password("新密码");
6.撤销用户权限
revoke privileges on 库.表 form "user_name"@"host"
12、事务和事务回滚
1、定义 :一件事从开始发生到结束的过程
2、作用 :确保数据的一致性
3、事务和事务回滚应用
SQL命令默认自动提交到数据库执行
show variables like "autocommit"
1、开启事务
mysql>begin;
2、终止事务
mysql>commit; | rollback;
4、注意:
事务只针对于表记录操作(增删改),对库和表的操作无效
13、pymysql
1、创建库db5,utf8
create database db5 charset utf8;
2、创建表t1
use db5;
create table t1(
id int primary key auto_increment,
name varchar(20),
score float(5,2)
)charset=utf8;
3、插入3条记录
insert into t1 values
(1,"李白",60),
(2,"杜甫",70),
(3,"白居易",80);
4、pymysql使用流程
1、数据库连接对象 :db = pymysql.connect(host="localhost",user="root",
password="123456",database="db5",charset="utf8")
2、游标对象 :cur = db.cursor()
3、执行命令 :cur.execute('sql命令')
4、提交 :db.commit()
5、关闭游标 :cur.close()
6、关闭数据库连接 :db.close()
5、数据库连接对象(db)方法
1、db.commit() :提交到数据库执行
2、db.rollback() :回滚
3、db.close() :断开与数据库连接
4、db.cursor() :创建游标对象
6、游标对象(cur)方法
1、cur.execute(sql命令) :执行sql命令
2、cur.close() :关闭游标对象
3、cur.fetchone() :取第1条(查询)
4、cur.fetchmany(n) :取n条(查询)
5、cur.fetchall() :取所有记录(查询)
7、connect()连接对象的参数
1、host :主机地址
2、user :用户名
3、password :密码
4、database :库名
5、charset :字符集(推荐utf8)
6、port :端口号(3306)