Linux数据库管理——day3——存储引擎、匹配条件、select完整命令

SQL知识点
   1. 在查询中用 % 实现统配，可在所在位置匹配任意多个(包括0个)任意字符
   2. 添加一个字段值为 '' 代表0个字符，不是空
      只有 NULL 代表字段为空

MySQL体系结构的8个组件：

组件		介绍
连接池	接口	接收命令
	分析器	检查语法正确性 和 是否拥有访问权限
	优化器	做基本的存储检索的优化
	查询缓存	存储部分被查询的数据，当有人访问的时候把返回给用户的数据存在内存中
		如果用户查询的数据在内存空间的缓存中有，就直接从缓存中把数据反馈给客户端
存储引擎		MySQL服务自带的功能程序，处理表的处理器
		不同的存储引擎有不同的功能和数据存储方式
管理工具		MySQL软件附带的诸多管理命令
硬盘文件系统		物理存储部分

---

存储引擎
    查看mysql可用引擎：

show egins;

    查看某表使用什么存储引擎：

show create table 表名\G;

    配置默认存储引擎，修改配置文件

system vim /etc/my.cnf
# 在[mysqld]后添加一行： default-storage-engine=想修改的存储引擎

两个常用存储引擎的相关文件
myisam	存储一个表信息有3个文件(/var/lib/mysql/表)	表.frm(表结构)、 表.myd(表数据)、 表.myi(索引信息)
innodb	存储一个表信息有2个文件(/var/lib/mysql/表)	表.frm(表结构)、 表.ibd(数据和索引)
	启用该存储类型的时候就会创建2个事物日志文件(/var/lib/mysql)	iblogfile、ibdata(用来存储回滚时需要的数据)

两个常用存储引擎的特点和适用情况
myisam	支持表级锁定	不支持事务、事务回滚、外键
innodb	支持行级锁定	支持事务、事务回滚

  使用情况分析：
    查询操作多的表适用myisam引擎，可以起到节省系统资源的作用
        因为myisam引擎使用的是表级锁，有人读的时候，就会全表加读锁，一次加锁就支持多人并发访问；
        但如果写的操作多就不适合用该引擎，因为一个人写，就全表加写锁，谁都不能读或者写了。
    相反写操作多的表适用innodb引擎，可以加大同步访问量
        因为innodb引擎使用的时行级锁，所以有人写的时候，只会限制保护一行数据，不影响其他行的读写，这样提高并发量
        但是如果读操作多，每有一行被读就加个读锁，无故浪费资源。

---

MySQL锁机制
   作用：主要是为了保护表数据的准确性和数据不被同时修改出冲突

锁粒度
表级锁	一个锁对整个表加以限制
行级锁	只锁定某一行
页级锁

锁类型
读锁	共享锁	支持并发读，但不能写
	触发的操作有：select
写锁	排他锁、互斥锁	独占操作，不能写和读
	触发的操作有：insert update delete

   查看锁状态：

show status like 'table_lock%';

---

MySQL事务
   可以把一次操作当作是一个事务。

事务特性(ACID)
Atomic	原子性	事务整个操作是一个整体，要不全部成功代表成功，要不一个失败代表全部失败
Consistency	一致性	事务操作前后，表中记录没有变化
Isolation	隔离性	事务操作是隔离，相互不受影响
Durability	持久性	数据一旦提交不可改变，但是如果引擎支持回滚，数据提交前可以回滚找回数据

   查看事务的提交状态:

show variables like 'autocommit'
# 临时在当前终端关闭自动提交，如果想永久设置就修改主配置文件，不过不建议，因为一旦忘记提交，数据有丢失的风险
set  autocommit=off 

   数据回滚

rollback;

   提交数据

commit;

附： 可以在mysql下执行系统命令，只需要在正常命令前加上system
      例如 在mysql界面使用 system vim /etc/my.cnf 就可以修改主配置文件

---

数据导入

   0. 配置搜索路径(可以使用默认的路径)
       0.0 查看当前搜索路径

show variables like 'secure_file_priv';

       0.1 修改搜索路径，就需要修改系统中mysql的配置文件/etc/my.cnf

system vim /etc/my.cnf
# 在[mysqld]后添加一行： secure_file_priv='新的搜索目录'

       0.2 创建新的搜索目录并且赋权和重启服务

system mkdir 新的搜索目录
system chown mysql 新的搜索目录
system systemctl restart mysqld

   1. 创建库和准备存储数据的表，表结构必须和将被导入的数据结构完全相同，并且每个数据都支持对应字段的数据类型

create table 表 (
字段的各个信息,
………………
);

   2. 导入数据 (这里分隔符号必须根据原数据文件数据的分隔标示进行配置)

load data infile "/必须是系统配置的搜索目录/导入数据的文件名"
into table 已经创建好、每列数据类型相符的表
fields terminated by "列分隔符"
lines terminated by "行分隔符(一般都是 \n 代表换行)";

# 创建好后，我们可以给表中所有行加上序号
# alter table 表 add id int(2) primary key auto_increment first;

数据导出

   0. 配置搜索路径(可以使用默认的路径)

   1. 导出数据 (这里的分隔符号，根据个人喜好进行定义)

SQL查询语句 into outfile "/必须是系统配置的搜索目录/导出文件名"
fields terminated by "列分隔符"
lines terminated by "行分隔符(一般都是 \n 代表换行)";

---

匹配条件

数值比较
=	等于		!=	不等于
>	大于		<	小于
>=	大于等于		<=	小于等于
可以直接使用两个字段比较  a<b   也可以字段和具体数字比较 a<=0

字符比较
=	等于		!=	不等于
is null	匹配空值		is not null	匹配非空
可以直接两个字段比较  a!=b
也可以字段和具体字符比较，但是具体字符串必须用' '或者" "引起来  a='hello'
在数据库中，值为空，是用NULL代表，而不是什么都不写，所以  a is null 不等价于 a='' ，也不能用 a = null 来代替。

范围匹配
in (值1,值2)	匹配这几个值中的值
	select 所有想显示的字段 from 表 where 字段 in (值1,值2);
not in (值1,值2)	匹配不在这几个值的任意值
	select 所有想显示的字段 from 表 where 字段 not in (值1,值2);
between 数字1 and 数字2	匹配数字在这范围内的所有
	可以等价于： ( 字段>=数字1 and 字段<=数字2 )
	select 所有想显示的字段 from 表 where 字段 between 数字1 and 数字2;
distinct 字段	去除重复显示
	select distinct 所有想显示的字段 from 表
	唯一的一种匹配，只能用于select，其他命令都不能用
	不过建议不要多个字段，因为在去重显示的时候，为了显示所有不同数据，可能有些字段还是会出现重复

逻辑匹配(用来连接多个匹配条件)
or	逻辑或	即or前匹配失败并且or后匹配失败才算失败，其他都是算匹配成功
and	逻辑与	即and前匹配成功并且and后匹配成功才算成功，其他都是算匹配失败
! 、 not	逻辑非	即让逻辑符号后的匹配的结果取反，原来匹配成功的当作不成功
()	提高优先级	可以理解为四则运算中的括号，不管任何优先级，先执行括号内的匹配，如果括号内还有括号，就再先执行括号中的括号内的匹配操作
逻辑匹配的优先级，括号提高优先级所以这里面匹配是最先考虑的，总体上是从左往右依次匹配，但是and的优先级高于or，所以要先执行and两边的匹配操作，然后再拿结果和or进行结合。

---

模糊查询
   用法： SQL命令 where 字段 like '通配符'
   通配符： _ 代表任意的单个字符，可以理解为正则中的 .
             % 代表匹配任意多个任意字符，可以理解为正则中的 .*

   特殊情况：
    1. where 字段 like '__%'    # 两个_和一个%组成
         代表字段中至少有2个字符的数据会匹配成功  
    2. where 字段 like '____'   # 4个_组成
         代表字段中有且只有4个字符的数据会匹配成功
    3. where 字段 like 'a%b'
         代表字段中以a开头b结尾的数据会匹配成功
    4. where 字段 like '%'
         代表字段中所有非NULL值都会匹配成功，详细分析就是
           字段中 0个字符('') 会匹配成功 (也就是 where 字段 = ''; 匹配出来的字段数据)
       字段中为空的数据不会匹配成功 (也就是 where 字段 is null; 匹配出来的字段数据)

正则查询 ( 数据库中的数据不区分大小写 )
   用法： SQL命令 where 字段 regexp '正则表达式'
   支持扩展正则
   其使用的含义就是，匹配正则表达式的所有字段的数据就会判定为满足匹配条件。

五则运算 ( 加减乘除、取余)
   条件： 字段的数据类型必须是数值类型中的一种，最好是一种整型
   符号分别是 + - * / %
   用法：字段1 =  一个数或者一个字段   +-*/%   一个数或者一个字段

聚集函数
     用法： select 聚集函数(字段名) from 表名

聚集函数
avg(字段名)	统计字段平均值
sum(字段名)	统计字段和
min(字段名)	统计字段最小值
max(字段名)	统计字段最大值
count(字段名)	统计字段的个数
全部的统计操作都是不计算NULL的
因为聚集函数中的字段必须是数值类型，而只有字符类型的字段才存在 0字符 的情况，所以聚集函数不需要考虑在个特殊情况

    拓展：
      1.  如何显示某个字段最大值的行的所有信息

select * from 表名 where 字段 = (select max(字段) from 表名);

      2. 如果聚集函数和组连用，那么就是对组内数据进行操作

select count(*),字段 from 表名 group by 字段;
# 就是按照某个字段进行分组，然后输出的就是，每个组内有多少个数据

---

对查询结果进行排序
   用法： SQL命令 order by 字段 [ asc|desc ]

这后缀的含义是
asc	升序(不写默认该值)	字段数据会按照从小到大排列
desc	降序	字段数据会按照从大到小排列

对字段进行分组
   用法：SQL命令 group by 字段
   按照字段分组，字段内容一样的分为一组

对查询结果过滤
   用法：SQL命令 having 条件

限制查询结果的函数
   用法：SQL命令 limit N,M;
   解释：如果没有N，那就默认N为0，就是从第N+1行开始，共显示M条，最后一定显示N+M行。

---

指定别名
   如果要给字段设置别名 ，在字段后面空格+别名
   给表加别名也是这样，表名后面空格+别名，只不过表名的别名只是让这次sql语句执行的更方便

select 字段 as 别名 from 表名 别名;

   举例子：

select t1.c1 name , t2.c2 age from student t1 left join school t2 where t1.sid=t2.sid;
# 这里面就是student表别名为t1，school表别名为t2
# t1表(student表)的c1字段别名为name，而t2表的c2字段别名为age

结合起来，SQL命令顺序是
select 字段 字段别名 from 表 表别名 where 条件 group by 字段 having 条件 order by 字段 limit N,M
执行顺序，也是从左往右，一个一个执行，前一个成功才会进行下一个。