接手一个成旧的项目进行二次开发，没有文档，什么都需要自己去查找，很是费时间。这次我接手一个java项目，但是只有数据库，源码我暂时又看不到，想删除测试数据，只有找他们（业务太复杂）。但是他们也删除不完全，无奈我只有慢慢的去数据库查看是哪个表哪个字段的查询，费时费力。
百度了mysql 的数据库全表查询，好像是没有找到现成的，只有自己动手了。 
       好了，废话不多说进入主题。
 
       information_schema数据库有一个COLUMNS表，它存储所有表字段信息，我们通过通过查询表中数据库名，来获取该数据库下的所表结构信息。同时，还需要读者自己熟悉一下mysql 游标的操作。
 
       
 
       
 
BEGIN
	
	DECLARE num int;
	DECLARE tableName,columnName,searchSql VARCHAR(500);
	DECLARE done INT DEFAULT FALSE; -- 自定义控制游标循环变量,默认false  
	DECLARE databaseName VARCHAR(500) DEFAULT 'test';
	DECLARE rs CURSOR FOR SELECT TABLE_NAME,COLUMN_NAME FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA =databaseName;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- 绑定控制变量到游标,游标循环结束自动转true  

	/* 打开游标 */
        OPEN rs;
	
	/* 创建零时表 */

	DROP TABLE  IF EXISTS tmp;

	CREATE TEMPORARY TABLE tmp(
		tablname VARCHAR(500),
		columnname varchar(500),
		selectsql VARCHAR(500)
	);

	/* 逐个取出字段的值，需要进行最大值的判断 */
	FETCH rs INTO tableName,columnName;

	REPEAT

		/* 遍历数据表 */
		IF NOT done THEN
			SET @searchSql=CONCAT('SELECT count(*) into @tmp_num FROM ','`',databaseName,'`','.',tableName,' WHERE ','`',columnName,'`',' LIKE ',' "%fff%" ');
		
			PREPARE stm FROM @searchSql;
			EXECUTE  stm;
			DEALLOCATE PREPARE stm;
		
			SET num=@tmp_num;
		
			IF  num THEN
				INSERT INTO tmp(tablname,columnname,selectsql) VALUES(tableName,columnname,@searchSql);
			END IF;
     
    END IF;  

		FETCH rs INTO tableName,columnName;
 
   UNTIL done END REPEAT;


   /* 关闭游标 */
   CLOSE rs;
	
   /* 查询到的数据 */
   SELECT * FROM tmp;


END

 
 

 
 
      给了大家一个思路，里面实现的有一些粗糙，请根据自己实际情况做相应的修改。

        表空间TABLESPACE在主流数据库的存储设计中，通常出现在逻辑存储设计的最底层。在ORACLE 和MYSQL中，表空间再往下走，就直接指向了文件系统（或者ASM）上的一个或者一组文件集合。DB2 （下面全部指ZOS版）在表空间和物理文件中，还有一层存储空间组（SG），DB2 的在表空间定义的时候指定其使用的SG，SG定义的时候，指定其使用的物理卷组。当表空间定义的时候，会在卷组里产生对应的文件。这一层可以说既是物理文件的集合,逻辑上是数据表的集合,承上启下的重要位置。
         我个人比较喜欢在做数据库存储设计的时候，从表空间这里开始。因为无论是哪种数据库，表空间的敲定，就决定了数据库表、表的数据文件存储上的隔离度。整个系统定几个表空间？哪些表？哪些索引？以什么样的方式抽象、归类并存放于不同的表空间，大体上也就对整个数据库存储体系的设计打下基础。对于ORACLE，存储在不同的表空间，一定是意味着放在不同的文件的。然而，这也是ORACLE在文件层面上隔离一个表，唯一的手段。就是说，我要两个表，存在不同的文件，我一定要让他存在两个不同的表空间。因为ORACLE的表空间下，虽然可以挂多个数据文件，DBA却不能明确的敲定，我的某个表使用哪个文件。非常神奇的事情是，ORACLE确是一个支持一个表，存储在多个表空间上的数据库。DBA可以精确的指定分区表的某一个分区使用哪个表空间。同一个表的不同分区，可以使用不同的表空间。如果想让不同的分区，在不同的文件。这是在ORACLE上唯一的实现方式。
         在文件上隔离分区，这件事情对于一个纯粹的ORACLE DBA来说，可能稍微有点莫名其妙。但是，如果一个尝试过，把DB2 上的分区表，迁移到ORACLE上来的DBA，这件事情的思路就变得非常好理解。要想在DB2 上定义一个分区的表，那DBA必须给DB2 定义一个表专用的表空间。并且要清楚的告诉DB2，这个表空间上会有多少的分区。在未来，这个表建立以后，DB2会为这个表准备和分区数一样的文件数，并且文件和分区一一对应。在DB2上，分区的表，天然就是文件隔离的，不同的分区，自然而然的使用者不同的数据文件。MYSQL更像是在其中搞了个折中方案，折中后那更像ORACLE,或者MYSQL更想做成ORACLE的样子。可以看出ORACLE和IBM对表空间的理解是不同的。ORACLE对表空间的理解，更像是一个物理文件集合后的逻辑地址。这里集合是非常非常重要的。表空间像是村庄，它里面有好多好多房子（磁盘上的文件），住了好多好多人（数据表或者其他对象）。至于里面怎么住，哪个人（分区）住哪个房间（文件），用户不要管，反正让你可以住就好了。而IBM的表空间，更像是一栋房子。一家人（一个表）就住一栋房子（表空间），不同的家人（分区），可以用不同的房间（文件）。由很多很多表空间组成的SG，才更像一个是村庄。
         对表空间的理解不同，其实可以衍生到对分区概念上面。我很认同一个理念就是，作为数据库的使用者而言，分表和分区，是一样的。其实分表的概念，很多时候是出自早期开源MYSQL的版本上面。为什么要分表的原因，也很简单，因为早期的MYSQL版本不支持分区。数据库分区到底是一个逻辑隔离的概念，还是一个有物理隔离的需求？IBM对于DB2的处理就很明确，不同的分区，就是不同的物理文件。而ORACLE选择了一种很模糊的态度，也就是通常情况下，ORACLE认为分区只是逻辑隔离，毕竟不同的分区还是在不同的段（SEGMENT）上面的。但是，它毕竟留了一条路，就是前文说的，DBA可以把不同的分区存在不同的表空间上。粗暴的解释，有一家两个人，对村长说，我们不要住在一栋房子离。村长说，不行，是随机分配的。但是，我可以让你住到别的村子里去。
         DB2能不能住到别的村子去呢?答案是可以,不同的分区也可以存储到不同的SG上面去。这种做法我从来没有使用过,但是可以想象到的好处是显而易见的,比如不同物理存储介质提高并发性能这种。我可以隐隐约约的感觉到,这种不同的设定,大概还会与ZOS,ASM以及普通linux的文件系统之间的差异存在一些关联。比如说,文件的概念。但是,这个内容,并不是很了解,也可能是我想多了。
         ORACLE在11g的时代，还比较推荐用户，在不同功能的数据库表，划分在不同的数据库表空间。另外也推荐，数据表，和表的索引使用的表空间不要放在一起。随着大表空间的兴起，以及12C、18C往后的版本来看。ORACLE越来越推从使用者不要关心存储。ORACLE试图给DBA一种气质，ORACLE很牛，你把所有的东西都给我一个表空间，文件怎么放，ORACLE来搞定。你把乱七八糟的东西，都往我一个大的表空间里堆，也没什么大不了的。继续以房子比喻,DB2给你一个毛胚房,希望你自己安排的特别适合自己。ORACLE则励志于做精装,你住就好了。
         关于MYSQL的存储引擎就非常有意思的来认知表空间。此处的MYSQL主要说的是InnoDB的存储引擎，就我个人感觉上。MYSQL在被ORACLE收购以前，或者说，它的主要存储引擎还不是InnoDB的时代，我觉得MYSQL都还不算一个真正合格的OLTP系统的存储引擎。此处先不多说。MYSQL有一个默认的共享表空间，名为ibdata1，这个表空间下自然可以挂很多很多文件。可以自动增长。所有InnoDB存储引擎的表，都会记录到该共享表空间里去，看到这里是不是很有ORACLE的气质。然而，MYSQL有一个参数，叫做innodb_file_per_table，这个参数设为ON的时候，每个InnoDB存储引擎的表都可以产生一个独立的表空间。NANI？是不是有一种一秒钟变成DB2的感觉。不对，还没看分区对不对。再看分区，更神奇了，MYSQL一旦使用了分区之后。表不再由一个ibd文件组成了，而是由建立分区时的各个分区ibd文件组成。简直就是DB2 存储的翻版有没有。看到这个设定之后，我个人觉得，大型的OLTP系统，还是老老实实的innodb_file_per_table=ON，毕竟MYSQL自己一副没底气的样子。那么使用的DBA，还是要更加精细一点。在DB2 迁移往ORACLE的过程中，经常会查资料或者问，这个表堆在一起有没有问题，那个这么粗放有没有性能不好。无论ORACLE官方还是民间资料都显得十分有底气，答案都是没有问题。而关于MYSQL表空间都用默认表空间好不好，无论是书籍还是各类资料，随便查查，都有这样那样不好的可能性，什么文件碎片、性能问题等等。总之感觉，让人用的不会那么放心。好吧，以上内容就是关于表空间的一些总结，就此。
 
 
 
 

B树和B+树主要用于构建磁盘中的高速索引，比如文件系统和数据库。
 
数据库中数据的储存主要有两种方式，一种是行存储，一种是列存储。
 
行存储，就是数据库中的一行数据存储在一段连续的磁盘块上。同理，列存储，就是一列数据存储在一起。
 
行存储的优点就是，单挑记录集中，适合事务。缺点是，聚合查询慢。因为聚合涉及多条行记录，那么就要从磁盘的多个非连续的块中读取数据。多次IO访问，导致查询慢。
 
列存储的有点是，聚合查询快，聚合计算快。因为它的列数据是放在一起的，IO读取一次或几次，就能把数据加载到内存。缺点也很明显，不适合事务。如果要更新每张表中的几个字段，那么就要读写几次磁盘，因为这些字段的数据不是连续存储的。那更新的时候，在单机环境，我们可以上锁，用内存屏障，但是在分布式环境下想保持一致性，就很难搞，开销非常大。
 
索引是对数据库表中一个或多个列（如student表的姓名 (name) 列）的值进行排序的结构，d独立于表的结构，方便快速查找定位数据。我们知道二分查找非常快，但前提是数据排序好的。如果直接索引直接利用表中的某一列数据，那么在往表中插入数据的时候，为了保持顺序一致，我们就要移动其他数据，这样开销就会很大。
 
索引的数据结构
 
二叉搜索树，有的是查找效率高，缺点不适合磁盘查找，还是那个老瓶颈，因为二叉搜索树的太高了，越高访问磁盘的次数就阅读，而磁盘的访问速度（准确讲是寻道速度）太慢了。所以，在二叉搜索树记性改进，降低树高，成为一个研究的方向，也由此诞生了B树和B+树。
 
B树，降低了树高，节点多存储些数据。B 树的每个节点是一个索引条目（例如：一个 <订单 ID，序号> 的组合），如果是行数据库可以索引到一条存储在磁盘上的记录。如下图所示
 
 
 B+树，是对B树的改进。它只有叶子节点存储数据，其他节点只做查询。这样的好处是，牺牲了部分空间，也就是非叶子节点，但删除和插入效率大大提高了。删除的时候，往往只需要删除叶子节点就好了，非常快。不像B树，要进行非常复杂的变形。插入的话，B+树也最多只涉及树的一条路径。这里就很好的以空间换时间这种思想。
 
 
另一个方面，它的叶子节点之间用链表连接，这样就能很好的支持范围查找。当然B-Tree也很好的支持范围。HashMap，哈希表单条查找效率是非常高的，并且它不需要数据排序，但是这种数据结构，对于范围查找，聚合查找是不支持的，排序和模糊匹配也不支持。
 
 
 
MySQL中支持多种存储引擎，InnoDB引擎，用的就是B+树，在一些特定数据中，访问很频繁的行数据，可能会附加基于hashmap的引擎，不过这些都是MySQL内部做的事情了。
 
 
 
参考:
 
[0] 数据库索引
 
[1] 数据库文件系统实例：MySQL 中 B 树和 B+ 树有什么区别？
 
[2] 高性能MySQL
 
 

 理解数据库和关系型数据库的概念



 什么是数据库
数据库就是一个文件系统，通过标准的SQL语句获取数据



什么是MySQL数据库





什么是关系型数据库



关系型数据库存放的是实体之间的关系





 常见的关系型数据库
l MySQL
l Oracle
l SQLServer
l DB2
l SyBase


 什么是服务器
服务器要从硬件和软件两个方面来说：
l 硬件：指的就是一台计算机。
l 软件：需要在这台电脑上安装数据库服务器。



 存储方式的描述



 



 


一台数据库服务器中会创建很多数据库（一个项目，会创建一个数据库）。在数据库中会创建很多张表（一个实体会创建一个表）。在表中会有很多记录（一个对象的实例会添加一条新的记录）。



 什么是SQL



 






 SQL的分类

 


 DDL：数据定义语言
create，drop，alter..
 DCL：数据控制语言
grant，if…
 DML：数据操纵语言
insert，update，delete…
 DQL：数据查询语言
select



 SQL对数据库进行操作


     创建数据库



 
 
 语法：
 
create database 数据库名称 [character set 字符集 collate 字符集校对规则];
 
 



 


 查看数据库
 语法：
 查看数据库服务器中所有的数据库：show databases;




 查看某个数据库的定义信息： show create database 数据库名称;





 修改数据库
 语法：
 alter database 数据库名称 character set 字符集 collate 校对规则;




 删除数据库
 语法:
 drop database 数据库名称;





 其他数据库操作
 切换数据库：use 数据库名称;





l 查看当前正在使用的数据库：





 SQL对数据库表进行操作
 SQL创建表
l 语法：
n create table 表名称(字段名称 字段类型(长度) 约束,字段名称 字段类型(长度) 约束…);
l 字段类型
n 一个实体对应一个表，一个实体属性对应表的一个字段。
Java中的类型 MySQL中的类型
byte/short/int/long tinyint/smallint/int/bigint
float float
double double
boolean bit
char/String char和varchar类型
 char和varchar的区别：
 * char代表是固定长度的字符或字符串。
 * 定义类型char(8),向这个字段存入字符串hello，那么数据库使用三个空格将其补全。
 * varchar代表的是可变长度的字符串。
 * 定义类型varchar(8), 向这个字段存入字符串hello,那么存入到数据库的就是hello。
Date date/time/datetime/timestamp
 datetime和timestamp区别
 * datetime就是既有日期又有时间的日期类型，如果没有向这个字段中存值，数据库使用null存入到数据库中
 * timestamp也是既有日期又有时间的日期类型，如果没有向这个字段中存值，数据库使用当前的系统时间存入到数据库中。
File BLOB/TEXT
l 约束
n 约束作用：保证数据的完整性
n 单表约束分类：
u 主键约束：primary key 主键约束默认就是唯一 非空的
u 唯一约束：unique
u 非空约束：not null
建表语句：
create database web_test1;
use web_test1;
create table user(
	id int primary key auto_increment,
	username varchar(20) unique,
	password varchar(20) not null,
	age int,
	birthday date
);
 SQL查看表
 查看某个数据库下的所有的表
 语法：show tables;





 查看某个表的结构信息
 语法：desc 表名;





 SQL删除表
  删除表
  语法：drop table 表名;




 SQL修改表
 修改表：添加列
 alter table 表名 add 列名 类型(长度) 约束;





 修改表：修改列类型，长度和约束
 alter table 表名 modify 列名 类型(长度) 约束;


 
修改表：删除列
 alter table 表名 drop 列名;






修改表：修改列名称
 alter table 表名 change 旧列名 新列名 类型(长度) 约束;




 修改表：修改表名
 rename table 表名 to 新的表名;




 修改表：修改表的字符集
 alter table 表名 character set 字符集;





 SQL对数据库表的记录进行操作（重点）
 SQL添加表的记录
语法：
 向表中插入某些列：insert into 表名 (列名1,列名2,列名3…) values (值1,值2,值3…)
 向表中插入所有列：insert into 表名 values (值1,值2,值3…);
 注意事项
1.值的类型与数据库中表列的类型一致。
 2.值的顺序与数据库中表列的顺序一致。
 3.值的最大长度不能超过列设置最大长度。
 4.值的类型是字符串或者是日期类型，使用单引号引起来。
添加记录
 添加某几列
insert into user (id,username,password) values (null,'aaa','123');

添加所有列
insert into user values (null,'bbb','123',23,'1993-09-01');

添加中文记录
insert into user values (null,'张三','123',23,'1993-09-01');




直接向数据库中插入中文记录会出现错误！！！
解决方法：
show variables like '%character%';  --查看数据库中与字符集相关参数：
需要将MySQL数据库服务器中的客户端部分的字符集改为gbk。
找到MySQL的安装路径：my.ini文件，修改文件中[client]下的字符集


* 重新启动MySQL的服务器
 services.msc


 SQL修改表的记录
 语法:
 update 表名 set 列名=值,列名=值 [where 条件];
注意事项
 1.值的类型与列的类型一致。
 2.值的最大长度不能超过列设置的最大长度。
 3.字符串类型和日期类型添加单引号。
 修改某一列的所有值
update user set password = 'abc';



按条件修改数据
update user set password = 'xyz' where username = 'bbb';


l 按条件修改多个列

update user set password='123',age=34 where username='aaa';





 SQL删除表的记录
 语法：
 delete from 表名 [where 条件];
 注意事项
 1.删除表的记录，指的是删除表中的一行记录。
2.删除如果没有条件，默认是删除表中的所有记录。
删除某一条记录
delete from user where id = 2;



删除表中的所有记录
delete from user;




 删除表中的记录有两种做法:
 delete from user;
 删除所有记录，属于DML语句，一条记录一条记录删除。事务可以作用在DML语句上的
 truncate table user;
删除所有记录，属于DDL语句，将表删除，然后重新创建一个结构一样的表。事务不能控制DDL的


 SQL查看表的记录（重点）
 基本查询
 语法:select [distinct] *|列名 from 表 [条件];
 环境的准备：
create table exam(
	id int primary key auto_increment,
	name varchar(20),
	english int,
	chinese int,
	math	int
);

insert into exam values (null,'张三',85,74,91);
insert into exam values (null,'李四',95,90,83);
insert into exam values (null,'王五',85,84,59);
insert into exam values (null,'赵六',75,79,76);
insert into exam values (null,'田七',69,63,98);
insert into exam values (null,'李老八',89,90,83);
查询所有学生考试成绩信息
select * from exam;


查询所有学生的姓名和英语成绩

select name,english from exam;

查询英语成绩信息（不显示重复的值）
select distinct english from exam;

查看学生姓名和学生的总成绩
select name,english+chinese+math from exam;





别名查询
select name,english+chinese+math as sum from exam;

条件查询
使用where子句
> , < , >= , <= , <> ,=
like:模糊查询
in:范围查询
条件关联:and , or ,not
查询李四学生的成绩：
select * from exam where name = '李四';

查询名称叫李四学生并且英文大于90分
select * from exam where name = '李四' and english > 90;

查询姓李的学生的信息
like可以进行模糊查询,在like子句中可以使用_或者%作为占位符。_只能代表一个字符，而%可以代表任意个字符。
	* like ‘李_’		：名字中必须是两个字，而且是姓李的。
	* like ‘李%’		：名字中姓李的学生，李子后可以是1个或任意个字符。
	* like ‘%四’		：名字中以四结尾的。
	* like ‘%王%’	：只要名称中包含这个字就可以。

select * from exam where name like '李%';

查询英语成绩是69,75,89学生的信息
select * from exam where english in (69,75,89);

排序查询
使用order by 字段名称 asc/desc;
查询学生信息，并且按照语文成绩进行排序：
select * from exam order by chinese;

查询学生信息，并且按照语文成绩倒序排序：
select * from exam order by chinese desc;

查询学生信息，先按照语文成绩进行倒序排序，如果成绩相同再按照英语成绩升序排序
select * from exam order by chinese desc,english asc;

查询姓李的学生的信息，按照英语成绩降序排序
select * from exam where name like '李%' order by english desc;



分组统计查询
聚合函数使用
sum();
获取所有学生的英语成绩的总和:
select sum(english) from exam;

获取所有学生的英语成绩和数学成绩总和：
select sum(english),sum(math) from exam;

查询姓李的学生的英语成绩的总和
select sum(english) from exam where name like '李%';

查询所有学生各科的总成绩：
select sum(english)+sum(chinese)+sum(math) from exam;

select sum(english+chinese+math) from exam;
与上面的语句有什么不同？
* 上面的语句是按照列的方式统计，英语成绩总和+语文成绩总和+数学成绩总和。
* 下面的语句先计算英语+数学+语文然后再求和。
	* 使用ifnull的函数

count();
获得学生的总数
select count(*) from exam;

获得姓李的学生的个数
select count(*) from exam where name like '李%';

max();
获得数学成绩的最高分：
select max(math) from exam;

min();
获得语文成绩的最小值
select min(chinese) from exam;

avg();
获取语文成绩的平均值
select avg(chinese) from exam;

分组查询
语法：使用group by 字段名称;
环境准备
create table orderitem(
	id int primary key auto_increment,
	product varchar(20),
	price double
);
insert into orderitem values (null,'电视机',2999);
insert into orderitem values (null,'电视机',2999);
insert into orderitem values (null,'洗衣机',1000);
insert into orderitem values (null,'洗衣机',1000);
insert into orderitem values (null,'洗衣机',1000);
insert into orderitem values (null,'冰箱',3999);
insert into orderitem values (null,'冰箱',3999);
insert into orderitem values (null,'空调',1999);
按商品名称统计，每类商品所购买的个数：
select product,count(*) from orderitem group by product;


按商品名称统计，每类商品所花费的总金额：

select product,sum(price) from orderitem group by product;





按商品名称统计，统计每类商品花费的总金额在5000元以上的商品
***** where的子句后面不能跟着聚合函数。如果现在使用带有聚合函数的条件过滤（分组后条件过滤）需要使用一个关键字having
select product,sum(price) from orderitem  group by product having sum(price) > 5000;

按商品名称统计，统计每类商品花费的总金额在5000元以上的商品，并且按照总金额升序排序
select product,sum(price) from orderitem  group by product having sum(price) > 5000 order by sum(price) asc;


 总结
 S(select)… F(from)…W(where)…G(group by)…H(having)…O(order by);