xml地图|网站地图|网站标签 [设为首页] [加入收藏]
MySQL如何判别InnoDB表是独立表空间还是共享表空间
分类:数据库

 

基本语法:select 查询列表 from 表名
查询列表可以是表中字段、常量值、表达式、函数;查询的结果是一个虚拟的表格。
注意:
①sql语言大小写不敏感
②关键字不能分行或略写
③一般书写方式为换行缩进
一、基础查询
1.经典查询(查询表中的字段)
①查询单个字段
select 字段1 from 表1;
②查询多个字段
select 字段1,字段2, . . .字段n from表1;
③查询所有字段(用“”表示所有字段或者用枚举所有的字段)
Select
from 表1;
Select 字段1,字段2,字段3,...,字段last from 表1;
2.查询常量值
select 520;
select ‘chenzui’;
3.查询表达式
select 100/20;
4.查询函数
select version();
5.起别名
①方式1用as
Select 100/20 as “结果”;
②方式2 用” ”空格
Select 100/20 结果;
6.去重distinct
SELECT DISTINCT department_id FROM employees;
7.mysql中+的功能(运算符)
①两个都是数值型,做加法运算。Select 200+100;返回300。
②一个是字符型一个是数值型,将字符型转换为数值型,若能转换成功,则继续运算,转换失败,将字符型转换为0,继续运算。
Select “120”+120;返回240;select “120aaa”+120;返回240;select “aaa120”+120;返回120。
③一方是null,结果返回null。select null+100;返回null。
二、条件查询
语法:select 查询列表 from 表名 where 筛序条件;
1.按条件表达式筛选
简单条件运算符:>、<、=、 !=、<>、>=、<=
案例1 查询工资大于1000的员工信息
Select * from employees where salary>1000;
案例2 查询部门编号等于80,的员工名字和部门id
Select last_name,department_id from employees where department_id = 80;
2.逻辑表达式筛选
逻辑运算符
作用:用于连接条件表达式 && || ! and or not
and 或 && 两个条件都为true,结果为true,否者返回false
or 或 || 只有一个条件为true,结果为true,否者为false
not 或 ! 连接条件本身为true,结果为false,否者为true
案例 查询工资在10000-15000的员工名字和工资
Select last_name , salary from employees where salary>10000 and salary<15000;
3.模糊查询
like、between and 、in、is null
①like 一般和通配符搭配使用
通配符:‘%’任意多个,包括0个,’_’任意单个。
案例1 查询名字中包含”c”的员工信息
select * from employees where last_name like “%c%”;
案例2 查询名字以a开头第4个字母为z的员工信息
select * from employees where last_name like “a__z%”;
案例3 查询第二个字符为””的员工名字
select last_name from employees where laset_name like “
$_%”;
②between and 可以使sql语句更简洁,包含临界值,两个临界值不能调换顺序
案例1 查询员工编号在150-200之间的员工信息
select * from employees where employee_id between 150 and 120;
select * from employees where employee_id >=150 and employee_id <=120;
③in 判断某段的值是否属于in列表中的某一项,提高了代码的简洁度,in列表的值必须一致或者兼容,in列表不支持通配符。
案例1 获取工种编号为aaa、bbb、ccc中一个的员工信息
select * from employees where job_id in(“aaa”,’bbb’,’’ccc);
select * from employees where job_id=”aaa” or job_id = “bbb” or job_id = “ccc”;
④=或者<>不能判断null值,is null和is not null 可以用于判断null值
案例1 查询没有奖金的员工信息
select * from employees where commission_pct is null;
⑤<=>安全等于
查询工资为10000的员工名字
Select last_name from employees where salary <=>10000;
⑥is null 和 <=>
is null:只可以判断null值,可读性较高
<=>:既可以判断null值,又可以判断普通数值,可读性较差。
三、常见函数
1、单行函数
①字符函数:length(获取字节个数)、concat(拼接字符串)、upper(转化成大写)、lower(转换为小写)、substring、instr(返回字符串第一次出现的索引,不存在返回0)、trim(去除前后空格)、lpad(用指定字符实现左填充)、rpad(用指定的字符实现右填充)、replace(替换)
图片 1
②数学函数
Round():四舍五入
Ceil():向上取整
Floor():向下取整
Truncate():截断
Mod():取余,mod(a,b) : a-a/bb
图片 2
③日期函数
NOW():返回当前日期加时间
Curdate():返回当前日期
Curtime():返回当前时间
Str_to_date(字符类型,日期类型): 将字符类型转换为日期类型
Date_format(日期类型,字符类型): 将日期类型转换为字符类型;
图片 3
④流程控制函数
if函数:其效果类似于if else
图片 4
Case函数用法一[类似于switch用法]
语法:
Case 要判断的字段或者表达式
When 常量1 then 要显示的值1或语句1;
When 常量2 then 要显示的值2或语句2;
When 常量3 then 要显示的值3或语句3;

Else 要显示的值n或者语句n;
End
图片 5
Case函数用法二[类似于多重if]
语法:
Case
When 条件1 then 要显示的值1或语句1
When 条件2 then 要显示的值2或语句2

Else 要显示的值n或语句n
End
图片 6
⑤其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
2、分组函数(聚合函数、统计函数、组函数)
分类:
求和(sum)、avg(平均值)、max(最大值)、min(最小值)、count(计算个数)
特点:
①sum和avg一般处理数值型,max、min、count又可以处理任何类型
②分组函数都忽略null值
③可以和distinct搭配实现去重运算
④count(
)用于统计数量
⑤和分组函数一同查询的字段要求是group by后的字段
案例1
select sum(salary) avg(salary) min(salary) max(salary) count(salary) from employees;
案例2 函数的适用性
SELECT SUM(last_name) ,AVG(last_name) FROM employees; 返回值是0,0
案例3 忽略null值
SELECT
SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;
案例4 和distinct结合使用 会自动去重
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
案例5 count()的使用
SELECT COUNT(salary) FROM employees;
SELECT COUNT(
) FROM employees;
SELECT COUNT(1) FROM employees;
案例6 和分组函数一同查询的字段有限制
SELECT AVG(salary),employee_id FROM employees;
四、排序查询
语法:select 查询列表 from 表名 [where 筛选条件] order by 排序的字段或者表达式
1.排序函数的用法
①asc表示升序,可以省略,desc是降序
②order by可以支持单个字段、别名、表达式、函数、多个字段
③order by 子句在查询语句最后面,除了limit子句
2.案例
select * from employees order by salary desc;
select * from employees where department_id >= 100 order by department_id;
SELECT last_name , salary12(1+IFNULL(commission_pct,0)) FROM employees ORDER BY salary12(1+IFNULL(commission_pct,0));
SELECT last_name , salary12(1+IFNULL(commission_pct,0))AS "年薪" FROM employees ORDER BY '年薪';
SELECT department_id,AVG(salary) FROM employees GROUP BY department_id ORDER BY AVG(salary);
SELECT last_name,salary FROM employees ORDER BY last_name DESC ,salary ASC;
五、group by
语法:select 字段1,字段2,… from 表名 [where 筛选条件] group by 字段1,字段2,..[having 筛选条件 limit 分页条件];
查询列表必须来自分组的字段,保证逻辑的一致性。
使用方式:
①group by 和聚合函数配合使用得时候,分组后计算
Select avg(salary),last_name from employees group by department_id;
②与having配合使用得时候,分组后过滤;
Select department_id,min(salary) from employees group by department_id having salary>3000;
③group by中聚合函数同时和非聚合函数一起使用时,非聚合函数取第一个匹配到时的字段内容。

 

 

 InnoDB采用按表空间(tablespace)的方式进行存储数据, 默认配置情况下会有一个初始大小为10MB, 名字为ibdata1的文件, 该文件就是默认的表空间文件(tablespce file),用户可以通过参数innodb_data_file_path对其进行设置,可以有多个数据文件,如果没有设置innodb_file_per_table的话, 那些Innodb存储类型的表的数据都放在这个共享表空间中,而系统变量innodb_file_per_table=1的话,那么InnoDB存储引擎类型的表就会产生一个独立表空间,独立表空间的命名规则为:表名.idb. 这些单独的表空间文件仅存储该表的数据、索引和插入缓冲BITMAP等信息,其它信息还是存放在共享表空间中,那么如何判别数据库中哪些表是独立表空间,哪些表是共享表空间呢?

近日在做一个区块链积分转代币的APP,牵涉到数据库中表的记录删除问题, 如果一条条删除那可真是累人。遂考虑直接进入mysql直接清空表或者删除表中数据。

 

本文记录一下这2种操作模式的区别,目标对象是表wp_comments,里面的所有留言均是垃圾留言,均可删除。然后便有了以下2种方式(进入mysql操作界面后):

 

  1. truncate table wp_comments;
  2. delete * from wp_comments;

 

其中truncate操作中的table可以省略,delete操作中的*可以省略。这两者都是将wp_comments表中数据清空,不过也是有区别的,如下:

方法1:通过ibd文件判别

  • truncate是整体删除(速度较快), delete是逐条删除(速度较慢)。
  • truncate不写服务器log,delete写服务器log,也就是truncate效率比delete高的原因。
  • truncate不激活trigger(触发器),但是会重置Identity(标识列、自增字段),相当于自增列会被置为初始值,又重新从1开始记录,而不是接着原来的ID数。而delete删除以后,Identity依旧是接着被删除的最近的那一条记录ID加1后进行记录。
  • 如果只需删除表中的部分记录,只能使用DELETE语句配合where条件。 DELETE FROM wp_comments WHERE……

 

 

 

如果表的存储引擎是InnoDB,而且表空间(tablespace)是共享表空间的话,那么数据库对应目录下面是没有"表名.ibd"文件的。独立表空间的表的话,则有"表名.ibd"文件。只是这个方法很笨,对于生产环境,大量的表通过这种方式判别,确实不是一个好方法。

 

mysql> show variables like 'innodb_file_per_table';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | ON    |

+-----------------------+-------+

1 row in set (0.01 sec)

 

mysql> use MyDB;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> create table Independent_tablespace(name  varchar(64));

Query OK, 0 rows affected (0.03 sec)

 

mysql> exit

 

[root@DB-Server ~]# cd /data/mysql/MyDB/

[root@DB-Server MyDB]# ls -lrt Independent_tablespace*

-rw-rw---- 1 mysql mysql  8560 Aug 21 22:05 Independent_tablespace.frm

-rw-rw---- 1 mysql mysql 98304 Aug 21 22:05 Independent_tablespace.ibd

[root@DB-Server MyDB]# 

 

在配置文件my.cnf里面设置innodb_file_per_table=0,重启MySQL服务,创建表common_tablespace,你会在数据目录看到只有common_tablespace.frm文件。

 

mysql> show variables like 'innodb_file_per_table';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | OFF   |

+-----------------------+-------+

1 row in set (0.00 sec)

 

mysql> use MyDB;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> create table common_tablespace(name varchar(64));

Query OK, 0 rows affected (0.02 sec)

 

mysql> exit

Bye

[root@DB-Server MyDB]# ls -lrt common_tablespace*

-rw-rw---- 1 mysql mysql 8560 Aug 21 22:08 common_tablespace.frm

[root@DB-Server MyDB]# 

 

 

方法2:使用INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES来判别。

 

 

MySQL 5.6

 

MySQL 5.6 INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES 关于这个系统表提供了有关表格的格式和存储特性,包括行格式,压缩页面大小位级别的信息(如适用),INNODB的表空间信息。

 

The INNODB_SYS_TABLESPACES table provides metadata about InnoDB tablespaces, equivalent to the information in the SYS_TABLESPACES table in the InnoDB data dictionary.

 

   花了点时间了解了一下INFORMATION_SCHEMA数据库下面的INNODB_SYS_TABLESPACES这个表,遂写了一个SQL来判断那些InnoDB引擎表是独立表空还是共享表空间

本文由澳门新葡亰手机版发布于数据库,转载请注明出处:MySQL如何判别InnoDB表是独立表空间还是共享表空间

上一篇:下载安装,SQL数据库基础 下一篇:关于SSMS显示select出来的数据行的疑问,SERVER中隐
猜你喜欢
热门排行
精彩图文