数据库高级查询

                                                                                            查询相关

1.判断某个属性为null时,用is null;

2.判断某个属性为非null时,用 is not null;

3.别名 :

selcet sname as '姓名',sal as '工资' from emp

  selcet sname '姓名',sal '工资' from emp

selcet sname 姓名,sal 工资 from emp

4.去掉重复的distinct,把distinct加到去掉重复的字段之前

查询所有的职位去掉重复的:

select distinct job from emp;

5.and 和java中的&&一样  or和java中||一样

6.查询工资为1500,5000,3000的员工信息

select * from emp where sal=1500||sal=3000||sal=5000;

select * from emp where sal in (1500,3000,5000);

7.between x and y 在x和y之间,包含x,y

查询工资在2000到4000之间的员工信息(包含2000和4000)

select * from emp where sal between 2000 and 4000;

查询工资小于2000,大于4000的员工信息

select * from emp where sal not between 2000 and 4000;

8.模糊查询 like

_ 代表单个未知字符            %代表0或多个未知字符

举例:

以a开头  a%            以a结尾  %a        包含a  %a%     

        倒数第三个字符是a  %a__    第二个字母是a最后字母是b    _a%b

        查询标题title中包含记事本的商品标题:

select title from t_item where title like '%记事本%';

查询标题title中不包含记事本的商品标题:

select title from t_item where title not like '%记事本%';

9.排序: 如果有条件写在条件的后面 没有条件写在表名的后面

默认是升序  desc降序    asc升序

查询员工姓名和工资按照工资降序排序:

select ename,sal from emp order by sal desc;

多字段排序: order by 属性 desc/asc,属性 desc/asc...

查询员工的姓名工资部门编号,按照部门编号降序如果编号相同按照工资升序排序

select ename,sal,deptno from emp order by deptno desc,sal asc;

分页查询:limit x,y 第一个参数代表跳过的条数,第二参数代表每页的数量.

每页10条,第一页: limit 0,10      每页5条第8页:  limit 5*7,8;

每页10条,第三页: limit 20,10      每页12条第3页: limit 2*12,12

limit 关键字通常写在sql语句的最后面

查询工资前三名的员工姓名和工资

select ename,sal from emp order by sal desc limit 0,3;

10.数值计算 + - * /  5%3等效mod(5,3)

查询所有的员工姓名,工资及年终奖(工资*5)

select ename,sal,sal*5 年终奖 from emp;

11.ifnull(x,y)函数

age=ifnull(x,y)如果x的值为null则赋值y,如果不为null,则赋值x

将emp表中奖金为null的全部改为0

update emp set comm = ifnull(comm,0);

12.聚合函数 对多行数据进行统计

求和: sum(求和的字段名) 

查询所有员工的工资总和

select sum(sal) from emp

平均值: avg(字段名)

查询10号部门的平均工资

select avg(sal) from emp where deptno=10;

最大值: max(字段名)

查询30号部门的最高工资

select max(sal) from emp where deptno=30;

最小值: min(字段名)

查询dell商品中最便宜的商品价格

select min(price) from t_item where title like '%dell%';

统计数量:count(字段名/*)

查询工资大于等于3000的员工数量

select count(*) from emp where sal>=3000;

13.日期相关函数

select 函数  ,可以执行函数

获取当前的年月日时分秒  now()  select now();

获取当前年月日 curdate()

获取当前时分秒 curtime()

提取时间分量 年 月 日 时 分 秒

select extract(year from now());

select extract(month from now());

select extract(day from now());

select extract(hour from now());

select extract(minute from now());

select extract(second from now());

                                                                                            日期格式化函数

(标准格式转为非标准格式):date_format(日期,format) 

format:  %Y四位年  %y两位年

  %m两位月  %c一位月

  %d日

  %H 24小时  %h 小时

  %i 分      %s秒

把now()格式改成  年月日时分秒

select date_format(now(),'%Y年%m月%d日%H时%i分%s秒');

(非标准格式转为标准格式):str_to_date(非标准时间,fromat)

        14.08.2008 08:00:00转为标准格式

select str_to_date('14.08.2018 08:00:00','%d.%m.%Y %H:%i:%s');

14.字符串相关函数

    1.字符串拼接 concat(s1,s2);

查询员工姓名和工资,要求工资以元为单位

select ename,concat(sal,'元') from emp; 

    2.获取字符串的长度 char_length(str)

查询员工姓名和名字的长度

select ename,char_length(ename) from emp;

    3.获取字符串在另一个字符串出现的位置 instr(str,substr) || locate(substr,str)

    4.插入字符串 insert(str,start,length,newstr)

select insert('sdgjhashdaj',2,3,'xiao');

结果:sxiaohashdaj

    5.去空白 trim(str)

    6.截取字符串 left(str,start)从左边截  rigth(str,start)从右边截

                  substring(str,start,length)

    7.重复repeat(str,count)

select repeat('ab',2);

结果: abab

    8.replace(str,odl,new)

    9.反转 reverse(str)

select reverse('abc');

结果: cba

15.数学相关函数

    1.向下取整    floor(num)

select floor(3.58);

结果:3

    2.四舍五入  round(num)

select round(2.33);

结果:2

      四舍五入    round(num,m)  m代表小数位数

select round(23.889,2);

结果:23.89

    3.非四舍五入  truncate(num,m)  m代表小数位数

select truncate(23.889,2);

结果:23.88

    4.随机数  rand 0-1的随机数

select floor(rand()*9);

结果:8