mysql2_mysql2

1.数据导入

1.作用:把文件系统的内容导入到数据库中

2.语法

load data infile "绝对路径" into table 表名

fields terminated by "分隔符"

lines terminated by "\n";

4.操作步骤

1.在数据库中创建对应的表

2.将要导入的文件拷贝到数据库的默认搜索路径中

1.查看数据库的默认搜索路径

show variables like "secure_file_priv";

一般是:/var/lib/mysql-files/

2.sudo cp 原文件 /var/lib/mysql-files/

3.执行数据导入语句

===================================

scoreTable.csv导入练习

create table studb(

id int primary key auto_increment,

name char(10),

score float,

PhoneNumber bigint(11),

class char(10));

load data infile "/var/lib/mysql-files/scoreTable.csv" into table studb

fields terminated by "," lines terminated by "\n";

2.数据导出

1.作用:将数据库表中的记录保存到本地硬盘

2.语法格式

select ... from 表名 into outfile "绝对路径"

fields terminated by "分隔符"

lines terminated by "\n";

3.注意

1.导出的内容由SQL查询语句决定

2.执行导出命令时路径必须指定对应的数据库搜索路径,一般是"/var/lib/mysql-files/"

3.表的复制

1.语法格式

create table 表名 select 查询命令;

2.复制表结构

create table 表名 select 查询命令 where false;

3.注意:复制表的时候不会把原有表的key属性复制过来

4.嵌套查询(子查询)

1.定义:把内层的查询结果作为外层的查询条件

2.语法

select ... from 表名 where 条件(select ....);

3.示例

见示例2.25

5.连接查询

1.内连接

1.定义:只显示匹配到的记录

2.语法格式

select 字段名列表 from 表1

inner join 表2 on 条件;

[inner join 表3 on 条件]....

3.示例

见示例3.1.1

3.1.2

2.外连接

1.左连接

1.定义:以左表为主显示查询结果,左表全部显示,右表匹配显示,未匹配的为null

2.语法:

select 字段名列表 from 表1

left join 表2 on 条件;

[left [inner][right] join 表3 on 条件]....

3.示例:

示例3.1.3

示例3.1.4 #就把inner改为left

2.右连接

右连接跟左连接用法一样,主表为右边

示例:

示例3.1.5

示例3.1.6

3.注意:

1.完全显示的字段以最后一个左右连接为准,如最后一个左连接,就以前面生成的表格为主,最后一个右连接,就以最后连接进来的表格为主

6.多表查询(笛卡尔积)

select 字段名列表 from 表名列表; #笛卡尔积

select 字段名列表 from 表名列表 where 条件; #多表查询,等同于内连接

MySQL用户账户管理

1.开启MySQL远程连接(安装完成后默认关闭远程连接,需要开启远程连接)

1.获取root权限

sudo -i

2.cd到配置文件所在路径

cd /etc/mysql/mysql.conf.d/

3.vi mysqld.cnf

注释掉地址绑定

#bind-address = 127.0.0.1

4.重启mysql服务

/etc/init.d/mysql restart

2.添加授权用户

1.使用root用户连接到服务器

2.添加新的授权用户

create user "用户名"@"IP地址" identified by "密码";

IP地址表示方式:

1. % 代表任意IP

2.localhost 只能从本地连接

3.指定一个IP,只能从这个ip连接

3.给用户授权

1.语法

grant 权限列表 on 库.表 to "用户名"@"IP地址" with grant option;

2.给用户全部权限的示例:(这个用户拥有全部权限,相当于root)

grant all privileges on *.* to "tiger"@"%" with grant option;

3.权限列表

select,update,delete,insert,alter,drop,create,..

4.库.表:

*.* 表示所有的库所有表

5.查询user列表

select user,host from mysql.user

6.删除授权用户

drop user "用户名"@"IP地址";

数据备份与恢复

1.数据备份(mysqldump在linux终端中操作)

1.命令格式

mysqldump -uroot -p 源库名 > 路径/xxx.sql

2.源库名的表示方式

--all-databases 所有库

库名 单个库

-B 库1 库2 ... 多个库

库名 表1 表2 ... 备份指定库的指定表

2.数据恢复

1.命令格式

mysql -u用户名 -p 目标库名 < 路径/xxx.sql

3.备份分为 完全备份 和增量备份

完全备份:mysqldump

增量备份:binlog日志,xbackup工具

4.从所有库的备份文件中恢复某一个库(--one-database)

mysql -u用户名 -p --one-database 目标库名 < 路径/xxx.sql

5.注意:

1.恢复库时库中新增的表不会被删除

2.恢复时必须先创建空库

事务和事务回滚

1.定义:一件事从开始发生到结束的整个过程

2.作用:确保数据的一致性

3.事务和事务回滚的应用

1.mysql中默认sql语句会自动commit到数据库

查看方式:show variables like "autocommit";

2.事务应用:

1开启事务

start transaction; #事务开启

...sql命令 #此时autocommit被禁用,命令不会提交,也不会对数据库做修改

2.终止事务

commit; #提交,此时命令才被提交,才对数据库做动作

或

rollback; #回滚,此时前面输入的命令作废

3.注意

1.事务回滚rollback只针对于对表记录的操作,如增删改记录,对创建库,创建表的操作无效

存储引擎

1.定义:用来处理表的处理器

2.存储引擎基本操作

1.查看已有表的存储引擎

show create table 表名; #ENGINE=InnoDB (默认InnoDB)

2.创建表时指定存储引擎

create table 表名()engine=存储引擎

3.查看所有的存储引擎

show engines;(工作中一般InnoDB,MyISAM常用)

4.常用存储引擎特点

1.innodb特点

1.共享表空间

表名,frm 表结构

表名.ibd 表记录和索引信息

2.支持行级锁

2.myisam特点

1.独享表空间

表名.frm 表结构

表名.myd 表记录

表名.myi 索引信息

2.支持表级锁

5.锁

1.加锁的目的

解决客户端并发访问的冲突问题

2.锁类型

1.读锁(select)也叫共享锁

加读锁之后不能更改内容,但可以进行查询

2.写锁(insert,update,delete)也叫互斥锁,排他锁

3.锁粒度

1.表级锁

2.行级锁

4.注意:操作完成后会自动释放锁

6.如何决定使用哪种存储引擎(innodb,myisam)

1.执行查询操作多的表使用myisam(使用innodb浪费资源)

2.执行写操作比较多的表使用innodb

7.更改表的默认存储引擎

1.sudo -i

2.cd /etc/mysql/mysql.conf.d/

3.vi mysqld.cnf

[mysqld]

defalut-storage-engine = myisam

4./etc/init.d/mysql restart

MySQL调优

1.选择合适的存储引擎

1.经常用来读的表使用myisam

2.其余的表都用innodb

2.SQL语句调优(尽量避免全表扫描)

1.在select,where,order by常涉及到的字段上建立索引

2.where子句中尽量不适用 != ,如使用,将放弃使用索引,将进行全表扫描

3.尽量避免用null值判断,否则会放弃索引,进行全表扫描

示例:select id from t1 where number is null;

优化:在number字段设置默认值0

4.尽量避免用 or 来连接条件,否则将全表扫描

示例(优化前):select id from t1 where id=10 or id=20;

优化后:select id from t1 where id=10 union all select id from t1 where id=20;

用 union all 把后一次扫描的结果连接到前一次的结果

5.模糊查询中尽量避免使用前置 % ,也会导致全表扫描

示例:select id from t1 where name like "%a%"

6.尽量避免 in 和 not in ,也会导致全表扫描

7.尽量避免用 select * ...,最好用具体的字段列表代替 * ,不要返回用不到的任何字段