SQL

这里是SQL的相关学习语句！

首先是表：emp, dept, t1, t10, t100, t500

/*
Navicat MySQL Data Transfer

Source Server         : localhost:80
Source Server Version : 50626
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50626
File Encoding         : 65001

Date: 2016-01-13 12:39:54
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(10) unsigned NOT NULL,
  `dname` varchar(20) NOT NULL,
  `loc` varchar(30) NOT NULL,
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('10', 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES ('20', 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES ('30', 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES ('40', 'OPERATIONS', 'BOSTON');

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(10) unsigned NOT NULL,
  `ename` varchar(20) NOT NULL,
  `job` varchar(20) NOT NULL,
  `mgr` int(6) DEFAULT NULL,
  `hiredate` varchar(11) NOT NULL,
  `sal` int(5) NOT NULL,
  `comm` int(4) DEFAULT NULL,
  `deptno` int(11) NOT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '17-DEC-1980', '800', null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '20-FEB-1981', '1600', '300', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7839', '22-FEB-1981', '1250', '500', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7698', '02-APR-1981', '2975', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7839', '28-SEP-1981', '1250', '1400', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7698', '01-MAY-1981', '2850', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '09-JUN-1981', '2450', null, '10');
INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', null, '09-DEC-1982', '3000', null, '20');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', '7698', '17-NOV-1981', '5000', null, '10');
INSERT INTO `emp` VALUES ('7844', 'TRNER', 'SALESMAN', '7698', '08-SEP-1981', '1500', '0', '30');
INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '12-JAN-1983', '1100', null, '20');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '03-DEC-1981', '950', null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '03-DEC-1981', '3000', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '23-JAN-1982', '1300', null, '10');

-- ----------------------------
-- Table structure for t1
-- ----------------------------
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of t1
-- ----------------------------
INSERT INTO `t1` VALUES ('1');

-- ----------------------------
-- Table structure for t10
-- ----------------------------
DROP TABLE IF EXISTS `t10`;
CREATE TABLE `t10` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of t10
-- ----------------------------
INSERT INTO `t10` VALUES ('1');
INSERT INTO `t10` VALUES ('2');
INSERT INTO `t10` VALUES ('3');
INSERT INTO `t10` VALUES ('4');
INSERT INTO `t10` VALUES ('5');
INSERT INTO `t10` VALUES ('6');
INSERT INTO `t10` VALUES ('7');
INSERT INTO `t10` VALUES ('8');
INSERT INTO `t10` VALUES ('9');
INSERT INTO `t10` VALUES ('10');

-- ----------------------------
-- Table structure for t100
-- ----------------------------
DROP TABLE IF EXISTS `t100`;
CREATE TABLE `t100` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of t100
-- ----------------------------
INSERT INTO `t100` VALUES ('1');
INSERT INTO `t100` VALUES ('2');
INSERT INTO `t100` VALUES ('3');
INSERT INTO `t100` VALUES ('4');
INSERT INTO `t100` VALUES ('5');
INSERT INTO `t100` VALUES ('6');
INSERT INTO `t100` VALUES ('7');
INSERT INTO `t100` VALUES ('8');
INSERT INTO `t100` VALUES ('9');
INSERT INTO `t100` VALUES ('10');
INSERT INTO `t100` VALUES ('11');
INSERT INTO `t100` VALUES ('12');
INSERT INTO `t100` VALUES ('13');
INSERT INTO `t100` VALUES ('14');
INSERT INTO `t100` VALUES ('15');
INSERT INTO `t100` VALUES ('16');
INSERT INTO `t100` VALUES ('17');
INSERT INTO `t100` VALUES ('18');
INSERT INTO `t100` VALUES ('19');
INSERT INTO `t100` VALUES ('20');
INSERT INTO `t100` VALUES ('21');
INSERT INTO `t100` VALUES ('22');
INSERT INTO `t100` VALUES ('23');
INSERT INTO `t100` VALUES ('24');
INSERT INTO `t100` VALUES ('25');
INSERT INTO `t100` VALUES ('26');
INSERT INTO `t100` VALUES ('27');
INSERT INTO `t100` VALUES ('28');
INSERT INTO `t100` VALUES ('29');
INSERT INTO `t100` VALUES ('30');
INSERT INTO `t100` VALUES ('31');
INSERT INTO `t100` VALUES ('32');
INSERT INTO `t100` VALUES ('33');
INSERT INTO `t100` VALUES ('34');
INSERT INTO `t100` VALUES ('35');
INSERT INTO `t100` VALUES ('36');
INSERT INTO `t100` VALUES ('37');
INSERT INTO `t100` VALUES ('38');
INSERT INTO `t100` VALUES ('39');
INSERT INTO `t100` VALUES ('40');
INSERT INTO `t100` VALUES ('41');
INSERT INTO `t100` VALUES ('42');
INSERT INTO `t100` VALUES ('43');
INSERT INTO `t100` VALUES ('44');
INSERT INTO `t100` VALUES ('45');
INSERT INTO `t100` VALUES ('46');
INSERT INTO `t100` VALUES ('47');
INSERT INTO `t100` VALUES ('48');
INSERT INTO `t100` VALUES ('49');
INSERT INTO `t100` VALUES ('50');
INSERT INTO `t100` VALUES ('51');
INSERT INTO `t100` VALUES ('52');
INSERT INTO `t100` VALUES ('53');
INSERT INTO `t100` VALUES ('54');
INSERT INTO `t100` VALUES ('55');
INSERT INTO `t100` VALUES ('56');
INSERT INTO `t100` VALUES ('57');
INSERT INTO `t100` VALUES ('58');
INSERT INTO `t100` VALUES ('59');
INSERT INTO `t100` VALUES ('60');
INSERT INTO `t100` VALUES ('61');
INSERT INTO `t100` VALUES ('62');
INSERT INTO `t100` VALUES ('63');
INSERT INTO `t100` VALUES ('64');
INSERT INTO `t100` VALUES ('65');
INSERT INTO `t100` VALUES ('66');
INSERT INTO `t100` VALUES ('67');
INSERT INTO `t100` VALUES ('68');
INSERT INTO `t100` VALUES ('69');
INSERT INTO `t100` VALUES ('70');
INSERT INTO `t100` VALUES ('71');
INSERT INTO `t100` VALUES ('72');
INSERT INTO `t100` VALUES ('73');
INSERT INTO `t100` VALUES ('74');
INSERT INTO `t100` VALUES ('75');
INSERT INTO `t100` VALUES ('76');
INSERT INTO `t100` VALUES ('77');
INSERT INTO `t100` VALUES ('78');
INSERT INTO `t100` VALUES ('79');
INSERT INTO `t100` VALUES ('80');
INSERT INTO `t100` VALUES ('81');
INSERT INTO `t100` VALUES ('82');
INSERT INTO `t100` VALUES ('83');
INSERT INTO `t100` VALUES ('84');
INSERT INTO `t100` VALUES ('85');
INSERT INTO `t100` VALUES ('86');
INSERT INTO `t100` VALUES ('87');
INSERT INTO `t100` VALUES ('88');
INSERT INTO `t100` VALUES ('89');
INSERT INTO `t100` VALUES ('90');
INSERT INTO `t100` VALUES ('91');
INSERT INTO `t100` VALUES ('92');
INSERT INTO `t100` VALUES ('93');
INSERT INTO `t100` VALUES ('94');
INSERT INTO `t100` VALUES ('95');
INSERT INTO `t100` VALUES ('96');
INSERT INTO `t100` VALUES ('97');
INSERT INTO `t100` VALUES ('98');
INSERT INTO `t100` VALUES ('99');
INSERT INTO `t100` VALUES ('100');

-- ----------------------------
-- Table structure for t500
-- ----------------------------
DROP TABLE IF EXISTS `t500`;
CREATE TABLE `t500` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of t500
-- ----------------------------
INSERT INTO `t500` VALUES ('1');
INSERT INTO `t500` VALUES ('2');
INSERT INTO `t500` VALUES ('3');
INSERT INTO `t500` VALUES ('4');
INSERT INTO `t500` VALUES ('5');
INSERT INTO `t500` VALUES ('6');
INSERT INTO `t500` VALUES ('7');
INSERT INTO `t500` VALUES ('8');
INSERT INTO `t500` VALUES ('9');
INSERT INTO `t500` VALUES ('10');
INSERT INTO `t500` VALUES ('11');
INSERT INTO `t500` VALUES ('12');
INSERT INTO `t500` VALUES ('13');
INSERT INTO `t500` VALUES ('14');
INSERT INTO `t500` VALUES ('15');
INSERT INTO `t500` VALUES ('16');
INSERT INTO `t500` VALUES ('17');
INSERT INTO `t500` VALUES ('18');
INSERT INTO `t500` VALUES ('19');
INSERT INTO `t500` VALUES ('20');
INSERT INTO `t500` VALUES ('21');
INSERT INTO `t500` VALUES ('22');
INSERT INTO `t500` VALUES ('23');
INSERT INTO `t500` VALUES ('24');
INSERT INTO `t500` VALUES ('25');
INSERT INTO `t500` VALUES ('26');
INSERT INTO `t500` VALUES ('27');
INSERT INTO `t500` VALUES ('28');
INSERT INTO `t500` VALUES ('29');
INSERT INTO `t500` VALUES ('30');
INSERT INTO `t500` VALUES ('31');
INSERT INTO `t500` VALUES ('32');
INSERT INTO `t500` VALUES ('33');
INSERT INTO `t500` VALUES ('34');
INSERT INTO `t500` VALUES ('35');
INSERT INTO `t500` VALUES ('36');
INSERT INTO `t500` VALUES ('37');
INSERT INTO `t500` VALUES ('38');
INSERT INTO `t500` VALUES ('39');
INSERT INTO `t500` VALUES ('40');
INSERT INTO `t500` VALUES ('41');
INSERT INTO `t500` VALUES ('42');
INSERT INTO `t500` VALUES ('43');
INSERT INTO `t500` VALUES ('44');
INSERT INTO `t500` VALUES ('45');
INSERT INTO `t500` VALUES ('46');
INSERT INTO `t500` VALUES ('47');
INSERT INTO `t500` VALUES ('48');
INSERT INTO `t500` VALUES ('49');
INSERT INTO `t500` VALUES ('50');
INSERT INTO `t500` VALUES ('51');
INSERT INTO `t500` VALUES ('52');
INSERT INTO `t500` VALUES ('53');
INSERT INTO `t500` VALUES ('54');
INSERT INTO `t500` VALUES ('55');
INSERT INTO `t500` VALUES ('56');
INSERT INTO `t500` VALUES ('57');
INSERT INTO `t500` VALUES ('58');
INSERT INTO `t500` VALUES ('59');
INSERT INTO `t500` VALUES ('60');
INSERT INTO `t500` VALUES ('61');
INSERT INTO `t500` VALUES ('62');
INSERT INTO `t500` VALUES ('63');
INSERT INTO `t500` VALUES ('64');
INSERT INTO `t500` VALUES ('65');
INSERT INTO `t500` VALUES ('66');
INSERT INTO `t500` VALUES ('67');
INSERT INTO `t500` VALUES ('68');
INSERT INTO `t500` VALUES ('69');
INSERT INTO `t500` VALUES ('70');
INSERT INTO `t500` VALUES ('71');
INSERT INTO `t500` VALUES ('72');
INSERT INTO `t500` VALUES ('73');
INSERT INTO `t500` VALUES ('74');
INSERT INTO `t500` VALUES ('75');
INSERT INTO `t500` VALUES ('76');
INSERT INTO `t500` VALUES ('77');
INSERT INTO `t500` VALUES ('78');
INSERT INTO `t500` VALUES ('79');
INSERT INTO `t500` VALUES ('80');
INSERT INTO `t500` VALUES ('81');
INSERT INTO `t500` VALUES ('82');
INSERT INTO `t500` VALUES ('83');
INSERT INTO `t500` VALUES ('84');
INSERT INTO `t500` VALUES ('85');
INSERT INTO `t500` VALUES ('86');
INSERT INTO `t500` VALUES ('87');
INSERT INTO `t500` VALUES ('88');
INSERT INTO `t500` VALUES ('89');
INSERT INTO `t500` VALUES ('90');
INSERT INTO `t500` VALUES ('91');
INSERT INTO `t500` VALUES ('92');
INSERT INTO `t500` VALUES ('93');
INSERT INTO `t500` VALUES ('94');
INSERT INTO `t500` VALUES ('95');
INSERT INTO `t500` VALUES ('96');
INSERT INTO `t500` VALUES ('97');
INSERT INTO `t500` VALUES ('98');
INSERT INTO `t500` VALUES ('99');
INSERT INTO `t500` VALUES ('100');
INSERT INTO `t500` VALUES ('101');
INSERT INTO `t500` VALUES ('102');
INSERT INTO `t500` VALUES ('103');
INSERT INTO `t500` VALUES ('104');
INSERT INTO `t500` VALUES ('105');
INSERT INTO `t500` VALUES ('106');
INSERT INTO `t500` VALUES ('107');
INSERT INTO `t500` VALUES ('108');
INSERT INTO `t500` VALUES ('109');
INSERT INTO `t500` VALUES ('110');
INSERT INTO `t500` VALUES ('111');
INSERT INTO `t500` VALUES ('112');
INSERT INTO `t500` VALUES ('113');
INSERT INTO `t500` VALUES ('114');
INSERT INTO `t500` VALUES ('115');
INSERT INTO `t500` VALUES ('116');
INSERT INTO `t500` VALUES ('117');
INSERT INTO `t500` VALUES ('118');
INSERT INTO `t500` VALUES ('119');
INSERT INTO `t500` VALUES ('120');
INSERT INTO `t500` VALUES ('121');
INSERT INTO `t500` VALUES ('122');
INSERT INTO `t500` VALUES ('123');
INSERT INTO `t500` VALUES ('124');
INSERT INTO `t500` VALUES ('125');
INSERT INTO `t500` VALUES ('126');
INSERT INTO `t500` VALUES ('127');
INSERT INTO `t500` VALUES ('128');
INSERT INTO `t500` VALUES ('129');
INSERT INTO `t500` VALUES ('130');
INSERT INTO `t500` VALUES ('131');
INSERT INTO `t500` VALUES ('132');
INSERT INTO `t500` VALUES ('133');
INSERT INTO `t500` VALUES ('134');
INSERT INTO `t500` VALUES ('135');
INSERT INTO `t500` VALUES ('136');
INSERT INTO `t500` VALUES ('137');
INSERT INTO `t500` VALUES ('138');
INSERT INTO `t500` VALUES ('139');
INSERT INTO `t500` VALUES ('140');
INSERT INTO `t500` VALUES ('141');
INSERT INTO `t500` VALUES ('142');
INSERT INTO `t500` VALUES ('143');
INSERT INTO `t500` VALUES ('144');
INSERT INTO `t500` VALUES ('145');
INSERT INTO `t500` VALUES ('146');
INSERT INTO `t500` VALUES ('147');
INSERT INTO `t500` VALUES ('148');
INSERT INTO `t500` VALUES ('149');
INSERT INTO `t500` VALUES ('150');
INSERT INTO `t500` VALUES ('151');
INSERT INTO `t500` VALUES ('152');
INSERT INTO `t500` VALUES ('153');
INSERT INTO `t500` VALUES ('154');
INSERT INTO `t500` VALUES ('155');
INSERT INTO `t500` VALUES ('156');
INSERT INTO `t500` VALUES ('157');
INSERT INTO `t500` VALUES ('158');
INSERT INTO `t500` VALUES ('159');
INSERT INTO `t500` VALUES ('160');
INSERT INTO `t500` VALUES ('161');
INSERT INTO `t500` VALUES ('162');
INSERT INTO `t500` VALUES ('163');
INSERT INTO `t500` VALUES ('164');
INSERT INTO `t500` VALUES ('165');
INSERT INTO `t500` VALUES ('166');
INSERT INTO `t500` VALUES ('167');
INSERT INTO `t500` VALUES ('168');
INSERT INTO `t500` VALUES ('169');
INSERT INTO `t500` VALUES ('170');
INSERT INTO `t500` VALUES ('171');
INSERT INTO `t500` VALUES ('172');
INSERT INTO `t500` VALUES ('173');
INSERT INTO `t500` VALUES ('174');
INSERT INTO `t500` VALUES ('175');
INSERT INTO `t500` VALUES ('176');
INSERT INTO `t500` VALUES ('177');
INSERT INTO `t500` VALUES ('178');
INSERT INTO `t500` VALUES ('179');
INSERT INTO `t500` VALUES ('180');
INSERT INTO `t500` VALUES ('181');
INSERT INTO `t500` VALUES ('182');
INSERT INTO `t500` VALUES ('183');
INSERT INTO `t500` VALUES ('184');
INSERT INTO `t500` VALUES ('185');
INSERT INTO `t500` VALUES ('186');
INSERT INTO `t500` VALUES ('187');
INSERT INTO `t500` VALUES ('188');
INSERT INTO `t500` VALUES ('189');
INSERT INTO `t500` VALUES ('190');
INSERT INTO `t500` VALUES ('191');
INSERT INTO `t500` VALUES ('192');
INSERT INTO `t500` VALUES ('193');
INSERT INTO `t500` VALUES ('194');
INSERT INTO `t500` VALUES ('195');
INSERT INTO `t500` VALUES ('196');
INSERT INTO `t500` VALUES ('197');
INSERT INTO `t500` VALUES ('198');
INSERT INTO `t500` VALUES ('199');
INSERT INTO `t500` VALUES ('200');
INSERT INTO `t500` VALUES ('201');
INSERT INTO `t500` VALUES ('202');
INSERT INTO `t500` VALUES ('203');
INSERT INTO `t500` VALUES ('204');
INSERT INTO `t500` VALUES ('205');
INSERT INTO `t500` VALUES ('206');
INSERT INTO `t500` VALUES ('207');
INSERT INTO `t500` VALUES ('208');
INSERT INTO `t500` VALUES ('209');
INSERT INTO `t500` VALUES ('210');
INSERT INTO `t500` VALUES ('211');
INSERT INTO `t500` VALUES ('212');
INSERT INTO `t500` VALUES ('213');
INSERT INTO `t500` VALUES ('214');
INSERT INTO `t500` VALUES ('215');
INSERT INTO `t500` VALUES ('216');
INSERT INTO `t500` VALUES ('217');
INSERT INTO `t500` VALUES ('218');
INSERT INTO `t500` VALUES ('219');
INSERT INTO `t500` VALUES ('220');
INSERT INTO `t500` VALUES ('221');
INSERT INTO `t500` VALUES ('222');
INSERT INTO `t500` VALUES ('223');
INSERT INTO `t500` VALUES ('224');
INSERT INTO `t500` VALUES ('225');
INSERT INTO `t500` VALUES ('226');
INSERT INTO `t500` VALUES ('227');
INSERT INTO `t500` VALUES ('228');
INSERT INTO `t500` VALUES ('229');
INSERT INTO `t500` VALUES ('230');
INSERT INTO `t500` VALUES ('231');
INSERT INTO `t500` VALUES ('232');
INSERT INTO `t500` VALUES ('233');
INSERT INTO `t500` VALUES ('234');
INSERT INTO `t500` VALUES ('235');
INSERT INTO `t500` VALUES ('236');
INSERT INTO `t500` VALUES ('237');
INSERT INTO `t500` VALUES ('238');
INSERT INTO `t500` VALUES ('239');
INSERT INTO `t500` VALUES ('240');
INSERT INTO `t500` VALUES ('241');
INSERT INTO `t500` VALUES ('242');
INSERT INTO `t500` VALUES ('243');
INSERT INTO `t500` VALUES ('244');
INSERT INTO `t500` VALUES ('245');
INSERT INTO `t500` VALUES ('246');
INSERT INTO `t500` VALUES ('247');
INSERT INTO `t500` VALUES ('248');
INSERT INTO `t500` VALUES ('249');
INSERT INTO `t500` VALUES ('250');
INSERT INTO `t500` VALUES ('251');
INSERT INTO `t500` VALUES ('252');
INSERT INTO `t500` VALUES ('253');
INSERT INTO `t500` VALUES ('254');
INSERT INTO `t500` VALUES ('255');
INSERT INTO `t500` VALUES ('256');
INSERT INTO `t500` VALUES ('257');
INSERT INTO `t500` VALUES ('258');
INSERT INTO `t500` VALUES ('259');
INSERT INTO `t500` VALUES ('260');
INSERT INTO `t500` VALUES ('261');
INSERT INTO `t500` VALUES ('262');
INSERT INTO `t500` VALUES ('263');
INSERT INTO `t500` VALUES ('264');
INSERT INTO `t500` VALUES ('265');
INSERT INTO `t500` VALUES ('266');
INSERT INTO `t500` VALUES ('267');
INSERT INTO `t500` VALUES ('268');
INSERT INTO `t500` VALUES ('269');
INSERT INTO `t500` VALUES ('270');
INSERT INTO `t500` VALUES ('271');
INSERT INTO `t500` VALUES ('272');
INSERT INTO `t500` VALUES ('273');
INSERT INTO `t500` VALUES ('274');
INSERT INTO `t500` VALUES ('275');
INSERT INTO `t500` VALUES ('276');
INSERT INTO `t500` VALUES ('277');
INSERT INTO `t500` VALUES ('278');
INSERT INTO `t500` VALUES ('279');
INSERT INTO `t500` VALUES ('280');
INSERT INTO `t500` VALUES ('281');
INSERT INTO `t500` VALUES ('282');
INSERT INTO `t500` VALUES ('283');
INSERT INTO `t500` VALUES ('284');
INSERT INTO `t500` VALUES ('285');
INSERT INTO `t500` VALUES ('286');
INSERT INTO `t500` VALUES ('287');
INSERT INTO `t500` VALUES ('288');
INSERT INTO `t500` VALUES ('289');
INSERT INTO `t500` VALUES ('290');
INSERT INTO `t500` VALUES ('291');
INSERT INTO `t500` VALUES ('292');
INSERT INTO `t500` VALUES ('293');
INSERT INTO `t500` VALUES ('294');
INSERT INTO `t500` VALUES ('295');
INSERT INTO `t500` VALUES ('296');
INSERT INTO `t500` VALUES ('297');
INSERT INTO `t500` VALUES ('298');
INSERT INTO `t500` VALUES ('299');
INSERT INTO `t500` VALUES ('300');
INSERT INTO `t500` VALUES ('301');
INSERT INTO `t500` VALUES ('302');
INSERT INTO `t500` VALUES ('303');
INSERT INTO `t500` VALUES ('304');
INSERT INTO `t500` VALUES ('305');
INSERT INTO `t500` VALUES ('306');
INSERT INTO `t500` VALUES ('307');
INSERT INTO `t500` VALUES ('308');
INSERT INTO `t500` VALUES ('309');
INSERT INTO `t500` VALUES ('310');
INSERT INTO `t500` VALUES ('311');
INSERT INTO `t500` VALUES ('312');
INSERT INTO `t500` VALUES ('313');
INSERT INTO `t500` VALUES ('314');
INSERT INTO `t500` VALUES ('315');
INSERT INTO `t500` VALUES ('316');
INSERT INTO `t500` VALUES ('317');
INSERT INTO `t500` VALUES ('318');
INSERT INTO `t500` VALUES ('319');
INSERT INTO `t500` VALUES ('320');
INSERT INTO `t500` VALUES ('321');
INSERT INTO `t500` VALUES ('322');
INSERT INTO `t500` VALUES ('323');
INSERT INTO `t500` VALUES ('324');
INSERT INTO `t500` VALUES ('325');
INSERT INTO `t500` VALUES ('326');
INSERT INTO `t500` VALUES ('327');
INSERT INTO `t500` VALUES ('328');
INSERT INTO `t500` VALUES ('329');
INSERT INTO `t500` VALUES ('330');
INSERT INTO `t500` VALUES ('331');
INSERT INTO `t500` VALUES ('332');
INSERT INTO `t500` VALUES ('333');
INSERT INTO `t500` VALUES ('334');
INSERT INTO `t500` VALUES ('335');
INSERT INTO `t500` VALUES ('336');
INSERT INTO `t500` VALUES ('337');
INSERT INTO `t500` VALUES ('338');
INSERT INTO `t500` VALUES ('339');
INSERT INTO `t500` VALUES ('340');
INSERT INTO `t500` VALUES ('341');
INSERT INTO `t500` VALUES ('342');
INSERT INTO `t500` VALUES ('343');
INSERT INTO `t500` VALUES ('344');
INSERT INTO `t500` VALUES ('345');
INSERT INTO `t500` VALUES ('346');
INSERT INTO `t500` VALUES ('347');
INSERT INTO `t500` VALUES ('348');
INSERT INTO `t500` VALUES ('349');
INSERT INTO `t500` VALUES ('350');
INSERT INTO `t500` VALUES ('351');
INSERT INTO `t500` VALUES ('352');
INSERT INTO `t500` VALUES ('353');
INSERT INTO `t500` VALUES ('354');
INSERT INTO `t500` VALUES ('355');
INSERT INTO `t500` VALUES ('356');
INSERT INTO `t500` VALUES ('357');
INSERT INTO `t500` VALUES ('358');
INSERT INTO `t500` VALUES ('359');
INSERT INTO `t500` VALUES ('360');
INSERT INTO `t500` VALUES ('361');
INSERT INTO `t500` VALUES ('362');
INSERT INTO `t500` VALUES ('363');
INSERT INTO `t500` VALUES ('364');
INSERT INTO `t500` VALUES ('365');
INSERT INTO `t500` VALUES ('366');
INSERT INTO `t500` VALUES ('367');
INSERT INTO `t500` VALUES ('368');
INSERT INTO `t500` VALUES ('369');
INSERT INTO `t500` VALUES ('370');
INSERT INTO `t500` VALUES ('371');
INSERT INTO `t500` VALUES ('372');
INSERT INTO `t500` VALUES ('373');
INSERT INTO `t500` VALUES ('374');
INSERT INTO `t500` VALUES ('375');
INSERT INTO `t500` VALUES ('376');
INSERT INTO `t500` VALUES ('377');
INSERT INTO `t500` VALUES ('378');
INSERT INTO `t500` VALUES ('379');
INSERT INTO `t500` VALUES ('380');
INSERT INTO `t500` VALUES ('381');
INSERT INTO `t500` VALUES ('382');
INSERT INTO `t500` VALUES ('383');
INSERT INTO `t500` VALUES ('384');
INSERT INTO `t500` VALUES ('385');
INSERT INTO `t500` VALUES ('386');
INSERT INTO `t500` VALUES ('387');
INSERT INTO `t500` VALUES ('388');
INSERT INTO `t500` VALUES ('389');
INSERT INTO `t500` VALUES ('390');
INSERT INTO `t500` VALUES ('391');
INSERT INTO `t500` VALUES ('392');
INSERT INTO `t500` VALUES ('393');
INSERT INTO `t500` VALUES ('394');
INSERT INTO `t500` VALUES ('395');
INSERT INTO `t500` VALUES ('396');
INSERT INTO `t500` VALUES ('397');
INSERT INTO `t500` VALUES ('398');
INSERT INTO `t500` VALUES ('399');
INSERT INTO `t500` VALUES ('400');
INSERT INTO `t500` VALUES ('401');
INSERT INTO `t500` VALUES ('402');
INSERT INTO `t500` VALUES ('403');
INSERT INTO `t500` VALUES ('404');
INSERT INTO `t500` VALUES ('405');
INSERT INTO `t500` VALUES ('406');
INSERT INTO `t500` VALUES ('407');
INSERT INTO `t500` VALUES ('408');
INSERT INTO `t500` VALUES ('409');
INSERT INTO `t500` VALUES ('410');
INSERT INTO `t500` VALUES ('411');
INSERT INTO `t500` VALUES ('412');
INSERT INTO `t500` VALUES ('413');
INSERT INTO `t500` VALUES ('414');
INSERT INTO `t500` VALUES ('415');
INSERT INTO `t500` VALUES ('416');
INSERT INTO `t500` VALUES ('417');
INSERT INTO `t500` VALUES ('418');
INSERT INTO `t500` VALUES ('419');
INSERT INTO `t500` VALUES ('420');
INSERT INTO `t500` VALUES ('421');
INSERT INTO `t500` VALUES ('422');
INSERT INTO `t500` VALUES ('423');
INSERT INTO `t500` VALUES ('424');
INSERT INTO `t500` VALUES ('425');
INSERT INTO `t500` VALUES ('426');
INSERT INTO `t500` VALUES ('427');
INSERT INTO `t500` VALUES ('428');
INSERT INTO `t500` VALUES ('429');
INSERT INTO `t500` VALUES ('430');
INSERT INTO `t500` VALUES ('431');
INSERT INTO `t500` VALUES ('432');
INSERT INTO `t500` VALUES ('433');
INSERT INTO `t500` VALUES ('434');
INSERT INTO `t500` VALUES ('435');
INSERT INTO `t500` VALUES ('436');
INSERT INTO `t500` VALUES ('437');
INSERT INTO `t500` VALUES ('438');
INSERT INTO `t500` VALUES ('439');
INSERT INTO `t500` VALUES ('440');
INSERT INTO `t500` VALUES ('441');
INSERT INTO `t500` VALUES ('442');
INSERT INTO `t500` VALUES ('443');
INSERT INTO `t500` VALUES ('444');
INSERT INTO `t500` VALUES ('445');
INSERT INTO `t500` VALUES ('446');
INSERT INTO `t500` VALUES ('447');
INSERT INTO `t500` VALUES ('448');
INSERT INTO `t500` VALUES ('449');
INSERT INTO `t500` VALUES ('450');
INSERT INTO `t500` VALUES ('451');
INSERT INTO `t500` VALUES ('452');
INSERT INTO `t500` VALUES ('453');
INSERT INTO `t500` VALUES ('454');
INSERT INTO `t500` VALUES ('455');
INSERT INTO `t500` VALUES ('456');
INSERT INTO `t500` VALUES ('457');
INSERT INTO `t500` VALUES ('458');
INSERT INTO `t500` VALUES ('459');
INSERT INTO `t500` VALUES ('460');
INSERT INTO `t500` VALUES ('461');
INSERT INTO `t500` VALUES ('462');
INSERT INTO `t500` VALUES ('463');
INSERT INTO `t500` VALUES ('464');
INSERT INTO `t500` VALUES ('465');
INSERT INTO `t500` VALUES ('466');
INSERT INTO `t500` VALUES ('467');
INSERT INTO `t500` VALUES ('468');
INSERT INTO `t500` VALUES ('469');
INSERT INTO `t500` VALUES ('470');
INSERT INTO `t500` VALUES ('471');
INSERT INTO `t500` VALUES ('472');
INSERT INTO `t500` VALUES ('473');
INSERT INTO `t500` VALUES ('474');
INSERT INTO `t500` VALUES ('475');
INSERT INTO `t500` VALUES ('476');
INSERT INTO `t500` VALUES ('477');
INSERT INTO `t500` VALUES ('478');
INSERT INTO `t500` VALUES ('479');
INSERT INTO `t500` VALUES ('480');
INSERT INTO `t500` VALUES ('481');
INSERT INTO `t500` VALUES ('482');
INSERT INTO `t500` VALUES ('483');
INSERT INTO `t500` VALUES ('484');
INSERT INTO `t500` VALUES ('485');
INSERT INTO `t500` VALUES ('486');
INSERT INTO `t500` VALUES ('487');
INSERT INTO `t500` VALUES ('488');
INSERT INTO `t500` VALUES ('489');
INSERT INTO `t500` VALUES ('490');
INSERT INTO `t500` VALUES ('491');
INSERT INTO `t500` VALUES ('492');
INSERT INTO `t500` VALUES ('493');
INSERT INTO `t500` VALUES ('494');
INSERT INTO `t500` VALUES ('495');
INSERT INTO `t500` VALUES ('496');
INSERT INTO `t500` VALUES ('497');
INSERT INTO `t500` VALUES ('498');
INSERT INTO `t500` VALUES ('499');
INSERT INTO `t500` VALUES ('500');

View Code

Let's Begin：

1. 检索记录

1.1 从表中检索所有行和列

select * from emp;
#或者 select empno,ename,job,sal,mgr,hiredate,comm,deptno from emp;

1.2 从表中检索部分行

select * from emp where deptno=10;

　支持通用运算符，=、<、>、<=、>=、!、<>

1.3 查找满足多个条件的行

 select *
 from emp
 where deptno=10
 or comm is not null
  or sal<=2000 and deptno=20;

　一般将条件和条件之间用()括起来

select * from emp where (deptno=10) or (comm is not null) or (sal<=2000 and deptno=20);

1.4 从表中检索部分列　

select ename,deptno, sal from emp;

1.5 为列取有意义的名称

select ename,sal as salary,comm as commission from emp; # as 是关键字

1.6 在where子句中引用取别名的列

select ename, sal as salary, comm as commission from emp having salary < 5000;

select * from (select ename,sal as salary, comm as commission from emp) tmp where salary<5000;

　　tip: where 子句是在select之前进行处理的，这样，在处理求解“问题”查询的where子句之前，salary和commission并不存在，要到where子句处理完成之后，别名才生效。然而from子句在where子句之前处理的。将原查询放在from子句中，那么在最外层的where子句“看到”别名之前，就已经生成了查询结果。

1.7 连接列值

　　查询出形如：

CLARK WORKS AS A MANAGER
KING WORKS AS A PRESIDENT
MILLER WORKS AS A CLERK

　　的结果, 需要使用concat 函数

　　DB2,Oracle,PostgreSQL

select ename||' WORKS AS A '||job as msg from emp where deptno=10;

　　MYSQL

select concat(ename, ' WORKS AS A ', job) as msg from emp where deptno=10;

　　SQL Server　

select ename+" WORKS AS A "+job as mag frm emp where deptno=10;

1.8 在select语句中使用条件逻辑

select ename,sal,
case when sal<=2000 then "UNDERPAID"
         when sal>=4000 then "OVERPAID"
         else "OK"
         end as status
from emp;

　　tip:case表达式可以针对查询的返回值执行条件逻辑。可以给CASE表达式取别名，使结果集更易读。如果没有else，对于不满足条件的行，CASE表达式返回NULL;

1.9 限制返回的行数

　　DB2 使用 FETCH FIRST

select * from emp fetch first 5 rows only;

　　MySQL 和 PostgreSQL 使用 LIMIT

select * from emp limit 5;

　　Oracle 使用 rownum

select * from emp where rownum<=5;

　　SQL Server

select top 5 * from emp;

1.10 从表中随机返回N条记录

DB2

select ename,job 
from emp 
order by rand() fetch first 5 rows only.

MySQL

select ename,job 
from emp order by rand() limit 5;

PostgreSQL

select ename,job 
from emp 
order by random() limit 5;

Oracle

select * 
from (
    select ename,job 
    from emp 
    order by dbms_random.value() 
    )
where rownum <=5;

SQL Server

select top 5 ename,job 
form emp 
order by newid()

1.11 查找空值

查找值为空的所有行

select * 
from emp 
where comm is null;# is null 判断是某列是否为空

1.12 将空值转化为实际值

select coalesce(comm,0) from emp;#将列comm 中为Null的值转化为0

1.13 按模式搜索

// 返回匹配特定字串或模式的行，LIKE 模式操作匹配

select ename,job
from emp
where ename like "%I%" or job like "%ER%";

提示：在LIKE匹配模式中，"%" 运算符可以匹配任何字符序列。多数SQL实现中也提供 "_" 运算符，来匹配单个字符。使用 "%" 运算符将搜索模式 "I"括起来，就会返回任何包含"I"的字符串，不管"I"在什么位置。如果不用"%"运算符将搜索模式"I"括起来，那么，这个运算符就会影响查询结果。例如，要查找以"ER"结尾的职务，可以在"ER"的前面加上前缀"%"运算符；如果需要查找以"ER"起始的职务，则将"%"放在"ER"的后面。

2. 查询结果排序

2.1 以指定次序返回查询结果

显示姓名，职务，工资，并且按工资升序（ASC）排列。 // 与之相对应的降序（DESC）

select ename,job,sal
from emp
where deptno=10
order by sal asc;

select ename,job,sal
from emp
where deptno=10
order by 3 desc;#这条语句查询了ename(序号1),job(序号2),sal(序号3)这3列,语句中的order by 3的3表示sal列，就是查询的列的序号。

2.2 按多个字段排序

select empno,deptno,sal,ename,job
from emp
order by deptno,sal desc;#部门编号（deptno）升序，工资（sal）降序

2.3 按字串排序

在emp表中，返回员工名字，职务，按照职位最后两个字符排序；

#MySQL, DB2, Oracle, PostgreSQL, 使用 substr(field, position)
select ename,job
from emp
order by substr(job,length(job)-2);

posted @ 2016-01-13 13:02 Zell~Dincht 阅读(242) 评论(0) 编辑收藏举报

刷新页面返回顶部

SQL

Let's Begin：

1. 检索记录

2. 查询结果排序

公告