mysql数据库查询SQL语句总结

查询语句的基础建表和数据语句:

CREATE TABLE `ssh_employee` (  `ID` int(11) NOT NULL AUTO_INCREMENT,  `LAST_NAME` varchar(255) DEFAULT NULL,  `EMAIL` varchar(255) DEFAULT NULL,  `BIRTH` datetime DEFAULT NULL,  `CREATE_TIME` datetime DEFAULT NULL,  `DEPARTMENT_ID` int(11) DEFAULT NULL,  `salary` decimal(10,2) DEFAULT NULL,  `bonus` decimal(10,2) DEFAULT NULL,  `address` varchar(100) DEFAULT NULL,  PRIMARY KEY (`ID`),  KEY `FK_kfaoihyj5oll835mvidvgsxp` (`DEPARTMENT_ID`),  CONSTRAINT `FK_kfaoihyj5oll835mvidvgsxp` FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES `ssh_department` (`ID`)) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;-- ------------------------------ Records of ssh_employee-- ----------------------------INSERT INTO `ssh_employee` VALUES ('7', '都是T', 'wsw@QQ.com', '2015-12-29 00:00:00', '2016-01-08 14:03:24', '2', '1000.00', '200.00', '上海');INSERT INTO `ssh_employee` VALUES ('12', 'Jim', null, '1993-08-20 00:00:00', '2016-01-08 14:09:26', '3', '3000.00', '400.00', '北京');INSERT INTO `ssh_employee` VALUES ('13', 'Tim', 'Tim@168.com', '1992-12-12 00:00:00', '2016-01-07 00:00:00', '3', '5000.00', '600.00', '重庆');INSERT INTO `ssh_employee` VALUES ('16', 'Tim', '', '1993-03-15 00:00:00', '2016-01-07 18:31:54', '2', '7000.00', '800.00', '重庆');以上是建表语句，后面所有查询语句都是基于以上数据表格而建立;
/* 查询语句 */	/*1、查询所有列(SELECT、FROM)*/SELECT * FROM ssh_employee;	/*2、查询指定列*/SELECT EMAIL ,BIRTH FROM ssh_employee;	/*3、查询时指定别名(AS),多表查询时常用表别名，此处只举列别名*/SELECT ID AS '编号',LAST_NAME AS '名字' from ssh_employee;  /*4、查询时添加上常量列，例子:在查询时加上性别列+"男"*/SELECT ID,LAST_NAME,EMAIL ,BIRTH,'男' AS '性别' FROM ssh_employee;	/*5、查询时合并列：查询员工工资和奖金和*/SELECT ID,LAST_NAME,(salary+bonus)AS '工资' FROM ssh_employee;	/*5、查询时去除(某个字段)重复记录 (DISTINCT):查员工来自那些省市*/SELECT DISTINCT(address) FROM ssh_employee;	/*6、条件查询(WHERE)*/			/*6.1、逻辑条件(AND 、OR)				需求:查询条件编号为16且名字为 Tim的员工的所有信息			*/			SELECT * FROM ssh_employee WHERE ID=16 AND LAST_NAME='Tim';			/*6.2、比较条件(大于> 小于< 大于等于>= 小于等于<= 不等于<> between and)				 需求查询工资高于4000的员工所有信息			*/			SELECT * FROM ssh_employee WHERE (salary+bonus)>4000;			/*工资大于3000且小于6000的员工信息*/			SELECT * FROM ssh_employee WHERE (salary+bonus)>3000 AND (salary+bonus)<6000;			SELECT * FROM ssh_employee WHERE (salary+bonus) BETWEEN 3000 AND 6000;			/*6.3、判空条件(NULL 空字符串)：is null/is not null/=''/<>''			查询无邮箱的员工(注意:NULL 表示没有值；""表示值为空字符串)				*/			SELECT * FROM ssh_employee WHERE EMAIL='' OR EMAIL is NULL;			/*邮箱不为空的员工*/			SELECT * FROM ssh_employee WHERE EMAIL<>'' AND EMAIL IS NOT NULL;			/*6.4、模糊条件(LIKE)				通常使用以下替换标记					%：表示任意个字符					_: 表示一个字符				需求 查询 名字第一个字母为 T的员工			*/			SELECT * FROM ssh_employee WHERE LAST_NAME LIKE 'T%';			/*需求 查询 名字中包含 T 字母的员工*/			SELECT * FROM ssh_employee WHERE LAST_NAME LIKE '%T%';上面代码为最基础的单表查询。更多查询语句请转下篇。