CREATE TABLE `employee` (
`empid` int(11) NOT NULL,
`ename` varchar(30) DEFAULT NULL,
`job` varchar(30) DEFAULT NULL,
`leaderid` int(11) DEFAULT NULL,
`hiredate` datetime DEFAULT NULL,
`wage` decimal(10,2) DEFAULT NULL,
`prize` decimal(10,2) DEFAULT NULL,
`deptid` int(11) DEFAULT NULL,
PRIMARY KEY (`empid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `employee` VALUES
('2069', 'JALEN', 'CLERK', '7902', '2009-12-17 00:00:00', '18000.00', null, '20'),
('3099', 'WANRE', 'SALESMAN', '7698', '2010-02-20 00:00:00', '18000.00', '300.00', '30'),
('3021', 'FIKEN', 'SALESMAN', '7698', '2010-02-22 00:00:00', '16500.00', '500.00', '30'),
('3066', 'JONES', 'MANAGER', '7839', '2011-04-02 00:00:00', '16000.00', null, '20'),
('3054', 'RANKEE', 'SALESMAN', '7698', '2012-09-28 00:00:00', '16500.00', '1400.00', '30'),
('3098', 'BLAKE', 'MANAGER', '7839', '2013-05-01 00:00:00', '16000.00', null, '30'),
('1082', 'CALAN', 'MANAGER', '7839', '2014-06-09 00:00:00', '16000.00', null, '10'),
('2088', 'SCOTT', 'ANALYST', '7566', '2015-04-19 00:00:00', '16000.00', null, '20'),
('3039', 'DIVE', 'PRESIDENT', null, '2016-11-17 00:00:00', '15000.00', null, '10'),
('3044', 'TURNER', 'SALESMAN', '7698', '2016-09-08 00:00:00', '15000.00', '0.00', '30'),
('2076', 'JULI', 'CLERK', '7788', '2017-05-23 00:00:00', '11000.00', null, '20'),
('3000', 'JAMES', 'CLERK', '7698', '2017-12-03 00:00:00', '9500.00', null, '30'),
('2002', 'FAXI', 'ANALYST', '7566', '2017-12-03 00:00:00', '9000.00', null, '20'),
('1034', 'MOKA', 'CLERK', '7782', '2018-01-23 00:00:00', '8800.00', null, '10');
select cols
from tablename
where condition
group by
having
order by
limit
select * from employee;
select ename,job from employee;
select * from employee where ename = "FAXI";
select * from employee where wage < 10000;
select * from employee where wage >= 6000 and wage <= 10000;
select * from employee where wage <10000 or hiredate > '2016-01-01 00:00:00';
select * from employee where hiredate between '2015-01-01 00:00:00' and '2019-01-01 00:00:00';
select * from employee where hiredate >= '2015-01-01 00:00:00' and hiredate <= '2019-01-01 00:00:00';
select * from employee where deptid in (10,20);
select * from employee where deptid = 10 or deptid = 20;
select e.ename,e.job,e.wage from employee as e;
select e.ename,e.job,e.wage from employee e;
select e.ename as '姓名',e.job as '岗位',e.wage '薪资' from employee as e;
select distinct e.deptid from employee as e;
select distinct deptid from employee;
select * from employee where ename like "B%";
select * from employee where ename like "%A%";
select * from employee where ename like "A%";
select * from employee where ename like "_A%";
select * from employee where ename like "__A%";
select * from employee order by deptid;
select * from employee order by deptid asc;
select * from employee order by deptid desc;
select * from employee order by deptid desc ,hiredate asc ;
select * from employee order by hiredate,wage;
select * from employee limit 5;
select * from employee limit 0,5;
select * from employee limit 2,5;
select @a,@b;
select @a := 1,@b;
set @b = 2;
select @a,@b;
select @a := "a" ,@b := 12.35;
set @rank = 0;
select *, @rank := @rank + 1 from emp order by sal desc;
select deptno,avg(sal) avg from emp group by deptno;
select deptno,avg(sal) avg from emp group by deptno having avg > 2000;