MySQL的使用（4）

# 创建emp 员工表
create table emp( 
empno int(4) primary key,
ename varchar(10),
job varchar(9),
mgr int(4),
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno int(2)
);
insert into emp values
(7369, 'Smith', 'clerk', 7902, '1980-12-17', 800, null, 20),
(7499, 'Allen', 'salesman', 7698, '1981-02-20', 1600, 300, 30),
(7521, 'Ward', 'salesman', 7698, '1981-02-22', 1250, 500, 30),
(7566, 'Jones', 'manager', 7839, '1981-04-02', 2975, null, 20),
(7654, 'Maritn', 'salesman', 7698, '1981-09-28', 1250, 1400, 30),
(7698, 'Blake', 'manager', 7839, '1981-05-01', 2850, null, 30),
(7782, 'Clark', 'manager', 7839, '1981-06-09', 2450, null, 10),
(7788, 'Scott', 'analyst', 7566, '1987-04-19', 3000, null, 20),
(7839, 'King', 'president', null, '1981-11-17', 5000, null, 10),
(7844, 'Turner', 'salesman', 7698, '1981-09-08', 1500, 0, 30),
(7876, 'Adams', 'clerk', 7788, '1987-05-23', 1100, null, 20),
(7900, 'James', 'clerk', 7698, '1981-12-03', 950, null, 30),
(7902, 'Ford', 'analyst', 7566, '1981-12-03', 3000, null, 20),
(7934, 'Miller', 'clerk', 7782, '1982-01-23', 1300, null, 10);

####################简单查询########################
1 查询所有字段 使用'*'通配符
select * from emp;
2 指定字段
select empno,ename,job,mgr from emp;
3 指定字段并调整字段顺序
select empno,ename,job,mgr,deptno,hiredate from emp;a
4 去除重复查询   distinct
select deptno from emp;
select distinct deptno from emp;  # 去除单字段重复
select distinct deptno,job from emp; # 在多字段中去除deptno job 都重复的数值
5 使用算术运算符 + - * / %   也可使用as 加入别名查询
select empno, job, sal*12 as yealsal from emp;
5 设置数据显示格式查询  concat 
select concat('姓名：',ename,'部门：',deptno,'职位：',job ,'年薪：',sal*12) from emp;
6 对查询结果排序 默认升序 desc 降序 需在指定字段后边加上order by
select deptno,ename,hiredate,sal from emp order by sal;
select deptno,ename,hiredate,sal from emp order by sal desc;
7 对指定的多字段排序 须在order by 后面指定多个字段 若排序字段值为null 则将该值作为最小值来处理
select deptno,ename,hiredate,sal,empno from emp order by empno desc,sal asc 
################条件查询###################
1 使用比较运算符查询 > < <= >= = !=
select * from emp where deptno=20;
select * from emp where deptno>=20;
### 使用'='运算符时不区分大小写 mysql 中不区分大小写
select * from emp where ename='jones'
select * from emp where ename='JONES'
### 若想区分大小写 使用binary 
select * from emp where ename='jones'
select * from emp where binary ename='JONES'
2 [not] between and 的范围查询
select * from emp where empno between 7000 and 8000;
select * from emp where sal not  between 1500 and 8000;
3 使用[not] in 查询指定集合
select * from emp where  deptno in (20,15);
select * from emp where  deptno  not in (20,15);
4 使用 is [not] null 的空值查询
select * from emp where comm is null;
select * from emp where comm is not null;
5 使用[not] like 的模糊查询 可使用%通配符匹配任意长度字符，_通配符匹配单个字符  \ 使用转义字符进行转义但必须配合escape 使用
select * from emp where ename like "%A";
select * from emp where ename like "%s";
select * from emp where ename like "A%";
select * from emp where ename like "%A%";
select * from emp where ename  not like "%A%";
select * from emp where ename like "_A%";
select * from emp where ename not like "_A%";
################多条件查询##################
1 使用and的多条件查询 相当于&&
select * from emp where deptno=20 and ename="jones"
select * from emp where deptno=20 && ename="jones"
2 使用or的多条件查询  相当于||
select * from emp where deptno=30 or ename = "jones"
select * from emp where deptno=30 || ename = "jones"
##################limit 分页查询#####################
1 不指定初始位置的限制查询 默认是从0开始
select * from emp where sal<3000 limit 3;
2 指定初始位置的限制查询 默认是从0开始 索引数(n-1)*分页数
select * from emp where sal<3000 limit 3, 3;
3 通常与order by 一起使用先排序在分页
select * from emp order by sal limit 3,3