一、MySQL存储引擎的配置
MySQL存储引擎:MySQL服务软件自带的功能程序,处理表的处理器
不同存储引擎有不同功能和数据存储方式
MySQL服务体系结构
管理工具:命令
连接池:检查服务器有无空闲进程、cpu、内存供客户端的连接,有就进入mysql>
SQL接口:在输入select、insert等命令,通过mysqld服务去执行
分析器
优化器
查询缓存:缓存曾经查找过的数据,存储空间从系统的物理内存中来
客户端向服务器发起请求—>服务器先在缓存查数据—>如果有直接从缓存给
客户端—>没有就从硬盘找—>先存到缓存—>给客户端—>如果有另一个客
户端查询同样数据—>直接从缓存给
存储引擎
文件系统
列出可用的存储引擎类型
mysql> show engines;
常用:
MyISAM:支持表级锁,不支持事务、事务回滚、外键
表名.MYI:存储表里字段的排列信息 表名.MYD:存储表里的数据 表名.frm
InnoDB(默认):支持事务、事务回滚、外键、行级锁
表名.ibd:存储表里数据和索引信息 表名.frm
事务日志文件:ibdata1、ib_logfile0、ib_logfile1
(在启动数据库服务自动创建出来的,记录所有的innodb存储引擎的表操作)
表名.frm:存储表的表结构
设置默认存储引擎
# vim /etc/my.cnf
在[mysqld]下面添加default-storage-engine=myisam
# systemctl restart mysqld
mysql> show engines;
mysql> create database db3;
mysql> use db3;
mysql> create table t1(id int);
另开终端验证:
# cd /var/lib/mysql/db3
# ls
mysql> create table t2(name char(3))engine=innodb;
另开终端验证:
# cd /var/lib/mysql/db3
# ls
mysql> alter table t1 engine=innodb; //可以修改,但是有数据的时候尽量不用MySQL锁机制
锁粒度
表级锁:一次直接对整张表进行加锁
行级锁:只锁定某一行
页级锁:对整个页面(MySQL管理数据的基本存储单元)进行加锁
锁类型
读锁(共享锁):支持并发锁
写锁(互斥锁、排它锁):是独占锁,上锁期间其他线程不能读表或写表
查看当前锁状态
mysql> show status like 'table_lock%';
Table_locks_immediate:产生表级锁定的次数;
Table_locks_waited:出现表级锁定争用而发生等待的次数;
MyISAM
对MyISAM表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;
对MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作;
InnoDB
对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁;事务可以通过以下语句显示给记录集加共享锁或排他锁
共享锁(S):SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
排他锁(X):SELECT * FROM table_name WHERE … FOR UPDATE
用SELECT … IN SHARE MODE获得共享锁,主要用在需要数据依存关系时来确认某行记录是否存在,并确保没有人对这个记录进行UPDATE或者DELETE操作
但是如果当前事务也需要对该记录进行更新操作,则很有可能造成死锁,对于锁定行记录后需要进行更新操作的应用,应该使用SELECT… FOR UPDATE方式获得排他锁
事务特性(ACID)
原子性:事务的整个操作是一个整体,不可分割,要么全部成功,要么全部失败
一致性:事务操作的前后,表中的记录没有变化
隔离性:事务操作是相互隔离不受影响的
持久性:数据一旦提交,不可改变,永久改变表数据
事务:从连接数据库服务开始到断开的过程
事务回滚:当在一次访问过程中,如果任意一步没有执行成功,都会恢复到之前的所有操作
例:(两个终端,两个会话)
# mysql -u root -p123456
mysql> desc t2;
mysql> show variables like "autocommit"; //终端1
mysql> set autocommit=off;
# mysql -u root -p123456
mysql> show variables like "autocommit"; //终端2
mysql> insert into t2 values("bob");
mysql> insert into t2 values("bob"); //终端1
mysql> select * from t2;
mysql> use db3;
mysql> select * from t2; //终端2
mysql> commit; //终端1
mysql> select * from t2; //终端2
mysql> delete from t2;
mysql> select * from t2; //终端1
mysql> select * from t2; //终端2
mysql> rollback;
mysql> select * from t2; //终端1
查询访问多的表适合使用myisam存储引擎,节省系统资源
写访问多的表适合使用innodb存储引擎,并发访问量大
二、数据导入/导出
1.数据导入:把文件的内容保存到表里
功能:批量把数据存储到数据库的表里
基本用法:
Load data infile “目录名/文件名”
into table 表名
fields terminated by “分隔符”
lines terminated by “\n”;
注意:字段分隔符要与文件内的一致;指定导入文件的绝对路径;导入数据的表字段类型
要与文件字段匹配;禁用Selinux保护机制
例:
mysql> show variables like "secure_file_priv";
//默认使用目录:/var/lib/mysql-files/
mysql> create table user(
-> name char(30),
-> password char(1),
-> uid int,
-> gid int,
-> comment char(150),
-> homedir char(150),
-> shell char(50)
-> );
mysql> desc user;
mysql> system cp /etc/passwd /var/lib/mysql-files/
mysql> system ls /var/lib/mysql-files/ //在mysql命令行执行系统命令
mysql> load data infile "/var/lib/mysql-files/passwd"
-> into table db3.user
-> fields terminated by ":"
-> lines terminated by "\n";
mysql> select * from user;
mysql> alter table user
-> add
-> id int primary key auto_increment first;
//要在建完表才添加,不然数据的表字段类型不与文件字段匹配
mysql> select * from user;
修改搜索路径:
# vim /etc/my.cnf
在[mysqld]下面添加secure_file_priv="/mydata"
# mkdir /mydata
# chown mysql /mydata
# systemctl restart mysqld
# mysql -u root -p123456
mysql> show variables like "secure_file_priv";
建在/root会出问题,尽量不要把目录建在管理员的家目录(ls -ld /root ls -ld /)
数据导出:把表记录存储到系统文件里面
基本用法:
SQL查询 into outfile “目录名/文件名”
Fields terminated by “分隔符” //不打默认空格隔开
Lines terminated by “\n”; //不打默认一行一条记录
注意:导出的内容由SQL查询语句决定;导出的是表中的记录,不包括字段名;禁用Selinux
例:
mysql> select * from db3.user into outfile
-> "/mydata/db3_user.txt";
mysql> system ls /mydata
mysql> system cat -b /mydata/db3_user.txt
(cat -b与cat -n类似,只不过对于空白行不编号)
管理表记录
增加表记录
1)添加一条记录,给所有字段赋值:
Insert into 表名 values(字段值列表);
例:
mysql> insert into db3.user values(
->42,"bob","x",3001,3001,"test user","/home/bob","/bin/bash"
->);
2)添加N条记录,给所有字段赋值;
Insert into 表名 values
(字段值列表),
…
…;
添加一条记录,给指定字段赋值:
Insert into 表名(字段名列表) values(字段值列表);
例:
mysql> insert into db3.user(name,uid,gid) values(“tom”,4001,4002);
添加N条记录,给指定字段赋值:
Insert into 表名(字段名列表) values
(字段值列表),
…
…;
查询表记录
1)select 字段1,… …,字段N from 表名;
例:
mysql> select * from db3.user;
2)select 字段1,… …,字段N from 表名 where 条件表达式;
例:
mysql> select name,uid from db3.user where uid<=10;
注意:使用*可以匹配所有字段;指定表名时,可采用 库名.表名 的形式
更新表记录
更新表内所有记录
Update 表名 set 字段1=字段1值,字段2=字段2值,字段N=字段N值;
例:
mysql> update db3.user
-> set password="A",comment="student";
mysql> select * from db3.user;
2)只更新符合条件的记录
Update 表名 set 字段1=字段1值,字段2=字段2值,字段N=字段N值 where 条件表达式;
例:
mysql> update db3.user
-> set password="x",comment="root"
-> where id=1;
mysql> select * from db3.user where id=1;
删除表记录
仅删除符合条件的记录
Delete from 表名 where 条件表达式;
例:
mysql> delete from db3.user where shell is null;
mysql> select * from db3.user;
删除所有表记录
Delete from 表名;
四、查询及匹配条件
基本匹配条件
1.数值比较
=、>、>=、<、<=、!=
例:
mysql> select name from db3.user where uid=500;
mysql> select name from db3.user where id=1;
mysql> select name from db3.user where id<=10;
mysql> select name,uid,gid from db3.user where uid=gid;
mysql> select name,uid,gid from db3.user where uid!=gid;
mysql> select name,uid,gid from db3.user where 1<2;
字符比较/匹配空/非空
=、!=、is null、is not null
例:
mysql> select name from db3.user where uid=500;
mysql> select name,shell from db3.user where shell!="/bin/bash";
mysql> select id,name,uid from db3.user where uid is null;
mysql> select id,name,uid from db3.user where uid is not null;
逻辑匹配
Or—逻辑或、and—逻辑与、!或not—逻辑非、()—提高优先级
例:
mysql> select name,uid,shell from db3.user
-> where name="root" and uid=1 and shell="/bin/bash";
mysql> select name,uid,shell from db3.user
-> where name="root" or uid=1 or shell="/bin/bash";
范围内匹配/去重显示
In (值列表)—在…里、not in (值列表)—不在…里、
betwenn 数字1 and 数字2—在…之间、distinct—去重显示
例:
mysql> select name from db3.user
-> where name in ("daemo","root","adm","bob");
mysql> select name,uid from db3.user
-> where uid in (1,7,3,40,50);
mysql> select name,shell from db3.user
-> where shell not in("/bin/bash","/sbin/nologin");
mysql> select * from db3.user
-> where id between 10 and 20;
mysql> select distinct shell from db3.user;
高级匹配条件
模糊查询
Where 字段名 like ‘通配符’
_匹配单个字符、%匹配0~N个字符
例:
mysql> select name from db3.user where name like "%a%";
mysql> select name from db3.user where name like "a%";
mysql> select name from db3.user where name like "a%a";
mysql> select name from db3.user where name like "___";
mysql> select name from db3.user where name like "_a_";
mysql> select name from db3.user where name like "__%__";
正则表达式
Where 字段名 regexp ‘正则表达式’
^ $ . [] * |
例:
mysql> select name from db3.user where name regexp '^a|^t';
mysql> select name,uid from db3.user where uid regexp '^....$';
mysql> select name,uid from db3.user where name regexp '^a.*t$';
四则运算
+、-、*、/、%
例:
mysql> select name,uid,gid from db3.user;
mysql> update db3.user set uid=uid+1,gid=gid-1;
mysql> select name,uid,gid from db3.user;
mysql> alter table db3.user
-> add age int default 18 after name;
mysql> select name,age,2018-age start from db3.user;
mysql> select name,uid,gid,(uid+gid)/2 pjz from db3.user where name="bin";
mysql> select name,uid from db3.user
-> where name="root" and uid=1 or uid=2;
mysql> select name,uid from db3.user where name="root" and (uid=1 or uid=2);
()—提高优先级
操作查询结果
1.聚集函数
Avg(字段名) //统计字段名
Sum(字段名) //统计字段之和
Min(字段名) //统计字段最小值
Max(字段名) //统计字段最大值
Count(字段名) //统计字段值个数
例:
mysql> select count(name) from db3.user;
mysql> select count(name) from db3.user
-> where shell!="/bin/bash";
mysql> select min(uid) from db3.user;
mysql> select min(uid) from db3.user
-> where uid<=1000;
mysql> select max(uid) from db3.user;
mysql> select avg(uid) from db3.user;
mysql> select sum(uid) from db3.user;
查询结果排序
SQL查询 order by 字段名 [asc | desc];
(通常是数值类型字段,desc降序)
例:
mysql> select name,uid from db3.user order by uid;
mysql> select name,uid from db3.user order by uid desc;
查询结果分组
SQL查询 group by 字段名;
(通常是数值类型字段)
例:
mysql> select gid from db3.user group by gid;
mysql> select shell from db3.user group by shell;
mysql> select shell from db3.user
-> where uid>10 and uid<=2000 group by shell;
查询结果过滤
SQL查询 having 条件表达式;
SQL查询 where 条件 having 条件表达式;
SQL查询 group by 字段名 having条件表达式;
例:
mysql> select name from db3.user
-> where shell!="/bin/bash"
-> having name="mysql";
mysql> select name from user
-> where shell!="/bin/bash" and name="mysql";
//查询结果一样,执行过程不一样,第一种效率高
5.限制查询结果显示行数
SQL查询 limit N; //显示查询结果前N条记录
SQL查询 limit N,M; //显示指定范围内的查询记录
SQL查询 where 条件查询 limit 3; //显示查询结果前3条记录
SQL查询 where 条件查询 limit 3,3; //从第4条开始,共显示3条
例:
mysql> select name,uid from db3.user;
mysql> select name from db3.user where id<=8 limit 1,3;
mysql> select name from db3.user where id<=8 limit 1,1;
mysql> select * from db3.user
-> where shell!="/bin/bash" limit 3,3;