数据库总结（二）：基本查询

数据库总结（一）：基本SQL

数据库总结（二）：基本查询

数据库总结（三）：分组，联结

数据库总结（四）：表设计之关联关系

数据库总结（五）：视图，约束，索引

查询常用指令

导入*.sql*数据到数据库中

• window系统

1. 把*.sql 文件保存到 d盘的根目录
2. 在命令行中执行 source d:/tables.sql

• linux系统

1. 把*.sql 文件保存到桌面
2. 在命令行中执行 source /home/soft01/桌面/tables.sql

is null 和 is not null

1. 查询奖金为null的员工姓名、工资、奖金
   select ename,sal,comm from emp where comm is null;
2. 查询没有上级领导mgr的员工信息(所有信息)
   select * from emp where mgr is null;
3. 有上级领导的员工姓名、工资和领导编号
   select ename,sal,mgr from emp where mgr is not null;
4. 查询有奖金的员工信息
   select * from emp where comm is not null and comm>0;

别名

1. 查询所有员工的姓名
   select ename as ‘姓名’ from emp;
   select ename ‘姓名’ from emp;
   select ename 姓名 from emp;
2. 查询工资大于2000的员工信息要求每个字段的名称改成中文
   select empno 编号,ename 姓名,job 工作,mgr 领导编号,hiredate 入职日期,sal 工资,comm 奖金,deptno 部门编号 from emp where sal>2000;

去重 distinct

1. 查询员工从事的所有职位
   select distinct job from emp;
2. 查询员工所属的部门编号有哪几个
   select distinct deptno from emp;

比较运算符 >,<,>=,<=,=,!=和<>

1. 查询工资小于1600的所有员工的姓名和工资
   select ename,sal from emp where sal<1600;
2. 查询部门编号为20的所有员工的姓名，职位，和部门编号
   select ename,job,deptno from emp where deptno=20;
3. 查询职位是manager的员工姓名，工资，职位
   select ename,sal,job from emp where job=‘manager’;
4. 查询不是10号部门的所有员工的姓名和部门编号（两种方式）
   select ename,deptno from emp where deptno!=10;
   select ename,deptno from emp where deptno<>10;
5. 查询商品表t_item中单价price 等于23的商品信息
   select * from t_item where price=23;
6. 查询商品表中单价不等于8443的商品标题和价格
   select title,price from t_item where price!=8443;

and和or

• and 和java 中的&&效果类似
• or 和java 中的||效果类似

1. 查询不是10号部门并且工资小于3000的员工信息
   select * from emp where deptno!=10 and sal<3000;
2. 查询有上级领导并且工资低于2000的员工信息
   select * from emp where mgr is not null and sal<2000;
3. 查询部门编号为30或者上级领导为7698的员工姓名、工资、部门编号、上级领导编号
   select ename,sal,deptno,mgr from emp where deptno=30 or mgr=7698;
4. 查询没有上级领导或者工资小于1000的员工信息
   select * from emp where mgr is null or sal<1000;

in

• 当查询某个字段的值为多个值的时候使用

1. 查询员工工资为5000，800，950的员工信息
   select * from emp where sal=5000 or sal=800 or sal=950;
   -使用in
   select * from emp where sal in(5000,800,950);
2. 查询员工工资不为5000，800，950的员工信息
   select * from emp where sal not in(5000,800,950);

between x and y

• 查询某个字段的数值在 x和y之间 包含x和y

1. 查询工资在1000到2000之间的员工信息
   select * from emp where sal>=1000 and sal<=2000;
   select * from emp where sal between 1000 and 2000;
2. 查询商品表中，单价price在50到100之间的商品价格
   select price from t_item where price between 50 and 100;
3. 查询工资在1000到4000之外的员工姓名和工资
   select ename,sal from emp where sal<1000 or sal>4000;
   select ename,sal from emp where sal not between 1000 and 4000;

模糊查询 like

• _:代表单个未知字符
• %：代表0或多个未知字符
• 举例：
  1. 以a开头 a%
  2. 以b结尾 %b
  3. 以x开头y结尾 x%y
  4. 包含abc %abc%
  5. 第二个字母是b _b%
  6. 第一个字母是m 倒数第三个是d m%d__
• 使用方式：

1. 查询名字以k开头的员工姓名和工资
   select ename,sal from emp where ename like ‘k%’;
2. 查询员工表中，职位名称包含s的职位名称
   select distinct job from emp where job like ‘%s%’;
3. 查询员工姓名中第三个字母是n的员工信息
   select * from emp where ename like ‘__n%’;
4. 查询名字倒数第二个字母是e的员工姓名
   select ename from emp where ename like ‘%e_’;
5. 查询商品表中标题包含记事本的商品信息
   select title from t_item where title like ‘%记事本%’;
6. 查询有赠品的dell商品(有赠品：卖点字段中包含赠字,
   dell商品：标题字段中包含dell)
   select * from t_item where sell_point like ‘%赠%’ and title like ‘%dell%’;
7. 查询价格在50到200之间的得力商品
   select * from t_item where price between 50 and 200 and title like ‘%得力%’;
8. 查询有图片的得力商品 (有图片：图片字段不为null)
   select * from t_item where image is not null and title like ‘%得力%’;
9. 查询商品标题中不包含得力的商品信息
   select * from t_item where title not like ‘%得力%’;
10. 查询商品分类为238，917并且商品标题中不包含广博的商品信息
    select * from t_item where category_id in (238,917) and title not like ‘%广博%’;

排序 order by 字段名

• 字段名后面 什么都不加默认为升序 添加 desc为降序 asc升序

1. 查询所有员工姓名 按照名字排序
   select ename from emp order by ename;
2. 查询所有员工的姓名，工资 按照工资进行升序排序
   select ename,sal from emp order by sal ;
3. 查询所有员工的姓名，工资 按照工资进行降序排序
   select ename,sal from emp order by sal desc;
4. 查询员工表中有上级领导并且名字中包含a的员工姓名，工资，上级领导编号，按照工资降序排序
   select ename,sal,mgr from emp where mgr is not null and ename like ‘%a%’ order by sal desc;

• 多字段排序，如果需要多个字段排序则在order by 后面写多个字段名通过逗号分隔即可

5. 查询所有员工的姓名，工资，部门编号 按照部门编号升序排序,如果部门一致则按照工资降序排序
   select ename,sal,deptno from emp order by deptno,sal desc;

分页查询

• 格式: limit 跳过的条数,请求的条数/每页的条数
• 请求前20条 limit 0,20
• 请求第三页每页8条 limit 16,8 //(3-1)*8,8
• 请求第5页每页4条 limit 16,4 //(5-1)*4,4

1. 查询商品表中的前20条数据
   select * from t_item limit 0,20;
2. 查询员工表第三页每页两条数据
   select * from empt limit 4,2;
3. 查询工资前三名的员工姓名和工资
   select ename,sal from emp order by sal desc limit 0,3;
4. 查询奖金最高员工信息
   select * from emp order by comm desc limit 0,1;

数值计算 + - * / 7%2 mod(7,2)

1. 查询每个员工的姓名，工资，年终奖(年终奖=工资5)
   select ename,sal,sal5 年终奖 from emp;
2. 查询商品表中商品单价，库存和总金额(单价库存)
   select price,num,pricenum 总金额 from t_item;
3. 给20号部门的员工涨薪10块钱
   update emp set sal=sal+10 where deptno=20;

日期相关的函数

• sql的helloWord
  select ‘helloword’;

1. 获取当前的日期+时间 now()
   select now();
2. 获取当前的年月日 curdate(); current当前
   select curdate();
3. 获取当前的时间 curtime();
   select curtime();
4. 从完整年月日时分秒中提取年月日 和提取时分秒
   select date(now()),time(now());
5. 从完整年月日时分秒中提取时间分量 extract()
   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());

• 查询每个员工的姓名和入职的年份
  select ename,extract(year from hiredate) year from emp;

6. 日期格式化 date_format(日期,格式)

• format：
• %Y 四位年 %y 两位年
• %m 两位月 %c 一位月
• %d 日
• %H 24小时 %h 12小时
• %i 分
• %s 秒
• 练习： 把now()转换成 2019年01月16号 15点45分22秒
  select date_format(now(),’%Y年%m月%d号 %H点%i分%s秒’);

7. 把自定义时间格式转回标准格式 str_to_date(自定义的字符串时间,格式)

• 练习： 14.08.2008 08:00:00
  select str_to_date(‘14.08.2008 08:00:00’,’%d.%m.%Y %H:%i:%s’);

ifnull(x,y)函数

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

1. 修改emp表中奖金为null的改为0，不为null则不变
   update emp set comm=ifnull(comm,0);

聚合函数

• 对多行数据进行统计，可以统计平均值，最大值，最小值，求和，统计数量

1. 平均值 avg(字段名)

• 统计20号部门的平均工资
  select avg(sal) from emp where deptno=20;
• 查询得力商品的平均单价
  select avg(price) from t_item where title like ‘%得力%’;

2. 最大值 max(字段名)

• 统计所有员工中的最高奖金
  select max(comm) from emp;
• 查询30号部门的最高工资
  select max(sal) from emp where deptno=30;

3. 最小值 min(字段名)

• 统计10号部门的最低工资
  select min(sal) from emp where deptno=10;
• 查询商品表中的最低商品单价
  select min(price) from t_item;

4. 求和 sum(字段名)

• 统计20号部门每个月共发多少工资
  select sum(sal) from emp where deptno=20;
• 统计员工表中所有员工的奖金总和
  select sum(comm) from emp;

5. 统计数量 count(字段名)

• 统计emp表中有多少员工
  select count(*) from emp;
• 查询30号部门工资大于1500的员工数量
  select count(*) from emp where deptno=30 and sal>1500;

字符串相关的函数

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

• 案例：查询员工姓名和工资 要求在工资的后面添加单位元
  select ename,concat(sal,‘元’) from emp;

2. 获取字符串的长度 char_length(str)
   select char_length(‘abc’);

• 案例： 查询每个员工的姓名和员工姓名的长度
  select ename,char_length(ename) from emp;

3. 获取字符串在另外一个字符串中出现的位置
   -格式一： instr(str,substr)
   select instr(‘abcdefg’,‘d’);
   -格式二： locate(substr,str);
   select locate(‘d’,‘abcdefg’);
4. 插入字符串 insert(str,start,length,newstr)
   select insert(‘abcdefg’,3,2,‘m’);
5. 转大写 转小写
   select upper(‘nba’),lower(‘ABC’);
6. 去两端空白 trim
   select trim(’ a b ');
7. 截取字符串
   • 从左边截取
     select left(‘abcdefg’,2);
   • 从右边截取
     select right(‘abcdefg’,2);
   • 任意位置截取
     select substring(‘abcdefg’,2); //从2截取到最后
     select substring(‘abcdefg’,2,3);//从2开始 长度为3
8. 重复 repeat(str,count)
   select repeat(‘ab’,2);
9. 替换 replace(str,old,new)
   select replace(‘abcefg’,‘e’,‘mm’);
10. 反转 reverse(str)
    select reverse(‘abc’);