示例表：
tb_manager表
DROP TABLE IF EXISTS `tb_manager`;CREATE TABLE `tb_manager` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(30) DEFAULT NULL,`PWD` varchar(30) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;INSERT INTO `tb_manager` VALUES ('1', 'mr', 'mrsoft');INSERT INTO `tb_manager` VALUES ('2', 'mingrisoft', 'mingrisoft');INSERT INTO `tb_manager` VALUES ('3', 'admin', '111');
tb_bookcase 表
DROP TABLE IF EXISTS `tb_bookcase`;CREATE TABLE `tb_bookcase` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(30) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;INSERT INTO `tb_bookcase` VALUES ('4', '左A-1');INSERT INTO `tb_bookcase` VALUES ('5', '左A-2');INSERT INTO `tb_bookcase` VALUES ('6', '右A-1');
tb_bookinfo 表
DROP TABLE IF EXISTS `tb_bookinfo`;CREATE TABLE `tb_bookinfo` (`id` int(11) NOT NULL AUTO_INCREMENT,`barcode` varchar(30) DEFAULT NULL,`bookname` varchar(70) DEFAULT NULL,`typeid` int(10) unsigned DEFAULT NULL,`author` varchar(30) DEFAULT NULL,`translator` varchar(30) DEFAULT NULL,`ISBN` varchar(20) DEFAULT NULL,`price` float(8,2) DEFAULT NULL,`page` int(10) unsigned DEFAULT NULL,`bookcase` int(10) unsigned DEFAULT NULL,`inTime` date DEFAULT NULL,`operator` varchar(30) DEFAULT NULL,`del` tinyint(1) DEFAULT '0',PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;INSERT INTO `tb_bookinfo` VALUES ('7', '9787302210337', 'Java Web开发实战宝典', '4', '王国辉', '', '302', '89.00', '834', '4', '-02-24', 'mr', '0');INSERT INTO `tb_bookinfo` VALUES ('8', '9787115195975', 'Java Web开发典型模块大全', '4', '王国辉、王毅、王殊宇', '', '115', '89.00', '752', '5', '-02-24', 'mr', '0');INSERT INTO `tb_bookinfo` VALUES ('9', '9787115195966', 'Java Web程序设计慕课版', '5', '明日科技', '', '115', '49.80', '350', '4', '-02-24', 'mr', '1');INSERT INTO `tb_bookinfo` VALUES ('10', '9787115195988', 'Android程序设计慕课版', '4', '明日科技', '', '111', '49.80', '360', '4', '-02-24', 'mr', '1');
tb_borrow 表
DROP TABLE IF EXISTS `tb_borrow`;CREATE TABLE `tb_borrow` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`readerid` int(10) unsigned DEFAULT NULL,`bookid` int(10) DEFAULT NULL,`borrowTime` date DEFAULT NULL,`backTime` date DEFAULT NULL,`operator` varchar(30) DEFAULT NULL,`ifback` tinyint(1) DEFAULT '0',PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;INSERT INTO `tb_borrow` VALUES ('7', '4', '7', '-02-24', '-03-16', 'mr', '1');INSERT INTO `tb_borrow` VALUES ('8', '4', '7', '-02-24', '-03-16', 'mr', '0');INSERT INTO `tb_borrow` VALUES ('9', '5', '8', '-02-24', '-04-05', 'mr', '0');
基本查询语句Usage：SELECT selection_list FROM 数据表名 WHERE primary_constraint GROUP BY grouping_columns ORDER BY sorting_cloumns HAVING secondary_constraint LIMIT count GROUP BY grouping_columns #分组ORDER BY sorting_cloumns#排序HAVING secondary_constraint特殊限制LIMIT count#限制查询数量单表查询比较运算符：=><=><=!=<>IS NULLIS NOT NULLBETWEENINNOT INLIKENOT LIKEREGEXP主要内容：查询所有字段查询指定字段查询指定数据带IN关键字的查询带BETWEEN AND的范围查询带LIKE的字符匹配查询用IS NULL关键字查询空值带AND的多条件查询带OR的多条件查询用DISTINCT关键字去除结果中的重复行用ORDER BY关键字对查询结果排序用GROUP BY关键字分组查询用LIMIT限制查询结果的数量查询所有字段Usage：SELECT * FROM 表名;exampel:select * from tb_manager查询指定字段Usage：SELECT 字段名 FROM 表名;exampel:select name,PWD from tb_manager查询指定数据Usage：SELECT 字段名 FROM 表名 where primary_constraint;exampel:select name,PWD from tb_manager where id=2;select name,PWD from tb_manager where name='mr';select id,name,PWD from tb_manager where id>=2;带IN关键字的查询Usage：SELECT * FROM 表名 WHERE 条件 [NOT] IN(元素1,元素2,…,元素n);exampel:select name,PWD from tb_manager where name in('admin','mr');select name,PWD from tb_manager where name not in('admin','mr');带BETWEEN AND的范围查询Usage：SELECT * FROM 表名 WHERE 条件 [NOT] BETWEEN 取值1 AND 取值2;exampel:select id,bookname,inTime from tb_bookinfo where id between '9' and '10';select id,bookname,inTime from tb_bookinfo where inTime not between '-02-23' and '-02-24';带LIKE的字符匹配查询Usage：%#匹配多字符_#匹配单字符exampel:select id,bookname,inTime from tb_bookinfo where bookname like 'java%';select * from tb_bookcase where name like '_A%';select * from tb_bookcase where name not like '_A-1';用IS NULL关键字查询空值Usage：IS [NOT] NULLexampel:select * from tb_admin where createtime is null;select * from tb_admin where createtime is not null;带AND的多条件查询（与运算）Usage：SELECT * FROM 数据表名 WHERE 条件表达式1 AND 条件表达式2 […AND 条件表达式n];exampel:select * from tb_admin where createtime is null and password like '%2%' and id >2;带OR的多条件查询（或运算）Usage：SELECT * FROM 数据表名 WHERE 条件1 OR 条件2 […OR 条件表达式n];exampel:select * from tb_admin where user='yi' or id >4;用DISTINCT关键字去除结果中的重复行(查询的所有列表都相同才会去重)Usage：SELECT DISTINCT 字段名 FROM 表名;exampel:select distinct price from tb_bookinfo;select distinct price,bookname from tb_bookinfo where price >50;用ORDER BY关键字对查询结果排序Usage：ORDER BY 字段名 [ASC|DESC];exampel:select id,name,PWD from tb_manager where id>=2 order by id;#默认根据id升序排序select id,name,PWD from tb_manager where id>=2 order by id desc;#根据id降序排序select distinct price from tb_bookinfo order by price desc; select distinct id,price,bookcase from tb_bookinfo order by price,bookcase desc ;#先price升序排序然后再bookcase降序排序用GROUP BY关键字分组查询exampel:select bookcase,count(*) from tb_bookinfo group by bookcase;#根据关键字bookcase进行分组，结果bookcase值为4的有3个，bookcase值为5的有1个，类似于unic -c，其中函数count()为统计数select group_concat(bookcase),count(*) from tb_bookinfo group by bookcase;#group_concat()函数为列出所有字段值，然后用逗号进行分割用LIMIT限制查询结果的数量exampel:select id,bookcase,bookname from tb_bookinfo limit 2;select id,bookcase,bookname from tb_bookinfo order by id desc limit 2; #注意limit需要放在最后，否则会报错聚合函数查询特点：根据一组数据求出一个值，聚合函数的结果值只根据选定行不是NULL的值来进行计算，如果这个值本身是NULL就会被忽略常见的聚合函数：COUNT()函数SUM()函数AVG()函数MAX()函数MIN()函数COUNT()函数：是聚合函数查询，COUNT()函数用于对除“*”以外的任何参数，返回所选择集合中非NULL值的行的数目。example：select count(*) from tb_bookinfo;select count(bookname) from tb_bookinfo where bookname like 'java%';SUM()函数:SUM()函数可以求出表中某个数值类型字段取值的总和。example:select sum(id) from tb_bookinfo where bookname like 'java%';AVG()函数:AVG()函数可以求出表中某个数值类型字段取值的平均值。example:select avg(price) from tb_bookinfo where bookname like 'java%';MAX()函数:MAX()函数可以求出表中某个数值类型字段取值的最大值。example:select max(price) from tb_bookinfo where bookname like 'java%';MIN()函数:MIN()函数可以求出表中某个数值类型字段取值的最小值。example：select min(price) from tb_bookinfo where bookname like 'java%';连接查询：把不同表的记录连到一起，然后获取到最终的查询结果内连接查询：最普遍的一种连接类型而且是匀称的，因为它要求构成连接的每一部分的每个表都匹配，如果不匹配那么就被删除掉example:select tb_manager.name,tb_bookcase.name from tb_manager,tb_bookcase; 多表查询，查询结果总条数为量表条数相乘select tb_bookinfo.id,tb_bookinfo.bookname,tb_bookinfo.author,tb_bookinfo.price,tb_borrow.borrowTime,tb_borrow.backTime,tb_borrow.operator from tb_bookinfo,tb_borrow where tb_bookinfo.id=tb_borrow.bookid;外连接查询Usage: 关键字 [OUTER] JOINSELECT 字段名称 FROM 表名1 [LEFT]|RIGHT JOIN 表名2 ON 表名1.字段名1=表名2.属性名2;#[LEFT] JOIN（默认为左连接）：将左表中的所有数据分别与右表中的每条数据进行连接组合，返回的结果除内连接的数据外还包括左表中不符合条件的数据，并在右表的相应列中添加NULL#RIGHT JOIN（右连接）：将右表中的所有数据分别与左表中的每条数据进行连接组合，返回的结果除内连接的数据外还包括右表中不符合条件的数据，并在左表的相应列中添加NULLexample:select bookid,borrowTime,backTime,bookname,price from tb_borrow join tb_bookinfo on tb_borrow.bookid=tb_bookinfo.id;#左连接此时主表为左表tb_borrow，会显示坐标所有的字段值，如果有值为空则显示为NULLselect bookid,borrowTime,backTime,bookname,price from tb_borrow right join tb_bookinfo on tb_borrow.bookid=tb_bookinfo.id;#右连接此时主表为右表tb_bookinfo，会显示右表所有的字段值，如果有值为空则显示NULL复合条件连接查询：在连接时可以增加其它的限制条件，通过多个条件的复合查询可以使查询结果更加准确，使用and关键字exanple:select bookid,borrowTime,backTime,bookname,price from tb_borrow [left] join tb_bookinfo on tb_borrow.bookid=tb_bookinfo.id and backTime like '%5';#注意这里是否加上left显示的是不同的，如果不加left会只显示符合条件的结果，如果加上left也会把不符合条件的值输出成NULL全部显示出来子查询:子查询就是指在一个查询语句中，还包括另一个查询语句。在外一层的查询中使用里面一层查询产生的结果集。主要内容:（适用方法在于子查询返回多少值，如果返回一个值可以用比较，如果多个可以用IN等。。）带IN关键字的子查询example:select id,bookname from tb_bookinfo where id in(select bookid from tb_borrow );带比较运算符的子查询example:select id,bookname from tb_bookinfo where id = (select bookid from tb_borrow limit 1 );select id,bookname from tb_bookinfo where id <= (select sum(bookid) from tb_borrow where bookid >7);带EXISTS关键字的子查询:EXISTS关键字用于检查子查询是否至少返回一行数据,如果有数据返回则为真,外层查询执行;该子查询如果“没有数据结果”（没有任何数据返回），则该EXISTS()结果为：false，外层查询不执行.EXISTS后面的子查询不返回任何实际数据，只返回真或假，当返回真时where条件成立。注意：EXISTS关键字，比IN关键字的运算效率高，因此，在实际开发中，特别是大数据量时，推荐使用EXISTS关键字,并且NULL也算结果，所以为真example:select id,bookname from tb_bookinfo where exists (select sum(bookid) from tb_borrow where bookid > '10000');select id,bookname from tb_bookinfo where exists (select 1=NULL);select id,bookname from tb_bookinfo where exists(select bookid from tb_borrow where bookid >10);带ANY关键字的子查询：表示满足其中任意一个条件。example:select * from tb_borrow where id = any(select id from tb_bookinfo where id >6 );select * from tb_borrow where id > any(select id from tb_bookinfo where id >6 );带ALL关键字的子查询:表示表示满足所有条件example:select * from tb_borrow where id > all(select id from tb_bookinfo where id <9 );合并查询结果: 合并查询结果是将多个SELECT语句的查询结果合并到一起关键字:UNION | UNION ALLexample:select name from tb_manager union select operator from tb_borrow; 去重select name from tb_manager union all select operator from tb_borrow; 显示所有定义表和字段的别名：为表取别名关键字：as,as可以省略example:未使用别名：select tb_borrow.bookid,tb_borrow.backTime,tb_borrow.borrowTime,tb_bookinfo.bookname,tb_bookinfo.price,tb_bookinfo.author from tb_bookinfo,tb_borrow where tb_bookinfo.id=tb_borrow.bookid;使用别名：select br.bookid,br.backTime,br.borrowTime,info.bookname,info.price,info.author from tb_bookinfo as info ,tb_borrow as br where info.id=br.bookid;为字段取别名select bookname as name from tb_bookinfo;使用正则表达式查询关键字：REGEXP匹配指定字符中的任意一个：[ ]example：select * from tb_manager where name regexp '[ai]';使用“*”和“+”来匹配多个字符“*”:匹配0到多个“+”：匹配1到多个example：select * from tb_manager where name regexp 'm*r';select * from tb_manager where name regexp 'm+r';匹配开头和结尾"^":匹配开头"$":匹配结尾example：select * from tb_manager where name regexp '^mi';select * from tb_manager where name regexp 'n$';
