本文列举数据库的单表查询语法及实际使用,以员工表和部门表作为举例:
部门表:dept
点击查看创建部门表sql
create table `dept` (
`deptno` int(2) not null,
`dname` varchar(14),
`loc` varchar(13),
primary key (`deptno`)
);
员工表:emp
点击查看创建员工表sql
create table `emp` (
`empno` int(4) not null,
`ename` varchar(10),
`job` varchar(9),
`mgr` int(4),
`hiredate` date,
`sal` int(7),
`comm` int(7),
`deptno` int(2),
primary key (`empno`),
index `fk_deptno`(`deptno`),
constraint `fk_deptno` foreign key (`deptno`) references `dept` (`deptno`) on delete restrict on update restrict
);
1查询所有信息
select * from 表名;
2查询部分信息
select 字段1,字段名2 [...] from 表名;
3where子句查询
1、比较运算:> >= < <=
select * from emp where sal >2000;
2、范围:between and(包含边界值)
select * from emp where sal between 1000 and 2000;
3、逻辑运算:and or not
select * from emp where sal >=1000 and sal <=2500;
select * from emp where empno=
or empno=2 or empno=3;
4、集合查询:in、not in
select * from emp where empno in (1,2,3);
select * from emp where empno not in (1,2,3);
4取别名
select ename, sal+sal*0.5 as 提升后的薪资 from emp;
select ename 姓名,sal+sal*0.5 提升后的薪资 from emp;
5去重查询:distinct
去重查询所有职位信息:
select distinct job from emp;
6模糊查询:like
%:匹配0个或多个长度的任意字符
查询名字以s开头的员工信息 select * from emp where ename like 's%
查询名字中包含s的员工信息 select * from emp where ename like '%s%
;`
_ :匹配一个长度的任意字符
查询第二字符为i的所有员工信息 select * from emp where ename like '_i%';
7排序:asc(默认)、desc
对所有员工薪资进行升序排序 select * from emp oder by sal;
对所有员工薪资进行降序排序 select * from emp oder by sal desc;
根据员工的工资降序排,如果工资一致,则按照员工编号降序排序 select * from emp oder by sal desc,empno desc;
8限制结果集数量:limit m,n(m:索引开始值;n:取值长度)
查询部门编号为10,薪资最高的第一个员工信息 select * from emp where deptno=10 order by sal desc limit 0,1