这里是SQL的相关学习语句!
首先是表:emp, dept, t1, t10, t100, t500
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
/* 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');
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);