当前位置:   article > 正文

Sql常用查询操作_sql server查询信息

sql server查询信息

1.查询语句模板:

需要注意的是:

  1.  FROM 才是 SQL 语句执行的第一步,并非 SELECT 。
  2. SELETC 是在WHERE语句执行之后执行的,所以不能再WHERE语句后使用SELECT中设置的别名
  3. WHERE是对分组前进行的过滤,HAVING是对分组后进行过滤。

2.sql中的 与 或 非

与--and  或--or 非--not  注意的是 :and 优先级高于 or   A and B or C and D  最后执行or

3.设置别名:

select sal*12 as "年薪" from  表名 t ;  sa "年薪" 给 列设置别名 , t 是给表设置别名

4.查询空值

select * from emp where comm is null (is not null)

5.模糊查询:

%(通配符)表示任意长度的字符 ,表示一个字符。 例如以S开头的 任意结束  (S%)

select 字段名 from 表名 where 字段名 like '%s%' ; 

6.分组:

通过性别不同分组查数据:

select sex, avg(age) as 平均年龄 from user group by sex;

注意:

 

  1. 使用分组后select 后面只能写分组条件(group by后面的值)或者组函数
  2. 分组函数  count  min  max avg sum   
  3. 要筛选结果 可以先使用where 再用group by 或者先用group by 再用having
  4. group by 执行过程如下:

7.单行函数: 

1)数字函数 

  1. --四舍五入 ROUND(input[,n]) n表示小数位如果n被忽略则无小数位
  2. SELECT ROUND(12.111) FROM dual;
  3. SELECT ROUND(12.111,1) FROM dual;
  4. SELECT ROUND(12.164,1) FROM dual;
  5. --截取数字 TRUNC (input[,n]) 将数字值截取,n代表截取的小数位,如果n被忽略则默认0
  6. SELECT TRUNC(12.113) FROM dual;
  7. SELECT TRUNC(12.113,2) FROM dual;
  8. --返回m除于n的余数 MOD(m,n)
  9. SELECT MOD(3,2) FROM dual

2)字符函数

  1. select * from emp;
  2. --转换为大写
  3. SELECT UPPER(e.ename) FROM emp e;
  4. --转换为小写
  5. SELECT LOWER(e.ename) FROM emp e;
  6. --首字母转换为大写
  7. SELECT INITCAP(ename) FROM emp
  8. --连接第一个字符到第二个字符等价于“||”
  9. SELECT CONCAT(ename,empno) EMPINFO FROM emp
  10. SELECT ename ||' ' || empno EMPINFO FROM emp
  11. --获取字符串中指定的字符,SUBSTR(input,m,[n]) 从m位置开始,取n个字符长度,如果n被忽略,则取到字符串结尾处
  12. SELECT SUBSTR(ename,1,3FROM emp
  13. SELECT SUBSTR(ename,1FROM emp
  14. --返回字符串的字符数
  15. SELECT ename,LENGTH(ename) as "str_length" FROM emp
  16. --返回字符值中查找字符串char的数字位置,m作为查找的开始,n代表第n次发现,m,n默认值为1,及默认是从开始位置查找,报告第一个查询到的位置INSTR(input,char[,m][.n])
  17. SELECT ename,INSTR(ename,'A') as "第一次出现a的位置" FROM (SELECT UPPER(ename) as ename FROM emp )
  18. --从字符串中查找字符char1,找到则替换为char2
  19. SELECT REPLACE(ename,'A','哈哈') FROM emp
  20. --左补全函数
  21. SELECT LPAD('EMPNO',12,'*') FROM emp
  22. --右补全函数
  23. SELECT RPAD('EMPNO',12,'*')FROM emp

3)日期函数

  1. /*
  2. ORACLE默认的日期格式是DD-MON-RR 如 17-JUN-13 PL/SQL格式为YYYY/MM/DD 如 2013/6/17
  3. 1-12月 JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
  4. 周一到周日 SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY
  5. */
  6. --返回俩个日期之间的月数,返回的是数值型
  7. SELECT MONTHS_BETWEEN('17-JUN-13','21-JAN-11') FROM dual;
  8. SELECT MONTHS_BETWEEN('17-APR-2013','21-JUN-2011') FROM dual;
  9. --1.查看当前时间(SQL Server 使用 GETDATE()方法)
  10. SELECT SYSDATE FROM dual;
  11. --2.计算在指定日期之后的下一个周char指定天的日期,char可以是一个表示星期的数或字符串
  12. SELECT NEXT_DAY(SYSDATE,'MONDAY') FROM dual;
  13. SELECT NEXT_DAY(SYSDATE,2) FROM dual;
  14. --3.添加n个日历月后的日期
  15. SELECT ADD_MONTHS('17-JUN-13',3) FROM dual;
  16. --4.计算包含指定日期所在月的最后一天日期
  17. SELECT LAST_DAY('17-JUN-13') FROM dual;
  18. --5.四舍五入日期 日期1日到15日结果在当前月的第一天,16到31为下一月的最后一天 1月到6月为当年,7到12为下一年1月1日
  19. SELECT ROUND(SYSDATE) FROM dual;
  20. SELECT ROUND(SYSDATE,'MONTH') FROM dual;
  21. SELECT ROUND(SYSDATE,'YEAR') FROM dual;
  22. --6.截取日期
  23. SELECT TRUNC(SYSDATE) FROM dual;
  24. SELECT TRUNC(SYSDATE,'MONTH') FROM dual;
  25. SELECT TRUNC(SYSDATE,'YEAR') FROM dual;
  26. --7.查看雇员表中 员工编号为7876的员工截止至今工作了多少个周
  27. SELECT e.empno ||' '||e.ename as "EmployeeInfo", ROUND((sysdate-e.hiredate)/7) as "workWeeks" FROM
  28. emp e where e.empno='7876';
  29. --8查询受雇日期在1981年1月1日到1982年1月1日的雇员,要查的信息包括雇员编号,已经雇佣的月数,三个月试用期结束的日期,
  30. --提交入职资料的日期(雇佣日下一周周一),首月结算薪资日期(受雇月最后一天)
  31. SELECT empno as "员工编号",MONTHS_BETWEEN(SYSDATE,e.hiredate) as 已雇月数,ADD_MONTHS(e.hiredate,3) as 试用期结束,
  32. NEXT_DAY(e.hiredate,'MONDAY') as 提交入职资料,LAST_DAY(e.hiredate) as 结算薪资 FROM emp e;

4)转换函数

  1. /*
  2. 在ORACLE中对于直接赋值,ORACLE能自动将VARCHAR2或CHAR转换为NUMBER或者DATE类型,也能将NUMBER和DATE自动转换为VAARCHAR2类型
  3. */
  4. --日期转换为CHAR TO_CHAR(date[,'fmt']) 格式模板必须放在单引号里,并且大小写敏感
  5. SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD,HH24-MI-SS') FROM dual;
  6. SELECT TO_CHAR(SYSDATE,'fmYYYY/MM/DD,HH24-MI-SS') FROM dual;--fm删除填补的空或者前导的0
  7. SELECT TO_CHAR(SYSDATE,'Y,YYY/MM/DD,HH24-MI-SS') FROM dual;
  8. SELECT TO_CHAR(SYSDATE,'YY/MM/DD,Q,HH24-MI-SS') FROM dual;
  9. SELECT TO_CHAR(SYSDATE,'YEAR/MM/DD,HH24-MI-SS') FROM dual;
  10. --数字使用TO_CHAR
  11. SELECT TO_CHAR(1234,'$999999') FROM dual;--前面有两个空格 $1234
  12. SELECT TO_CHAR(1234,'L999999') FROM dual; --
  13. SELECT TO_CHAR(1234,'9999.99') FROM dual;-- 1234.00
  14. SELECT TO_CHAR(1234,'999,999') FROM dual;-- 1,234
  15. SELECT TO_CHAR(-1234,'999999MI') FROM dual;--负数右边显示负号 1234-
  16. SELECT TO_CHAR(-1234,'999999PR') FROM dual;--负数加上括号 <1234>
  17. SELECT TO_CHAR(-1234,'999999EEEE') FROM dual;--科学计数法 -1E+03
  18. --字符串使用TO_NUMBER
  19. SELECT TO_NUMBER('1234','9999') FROM dual;
  20. SELECT TO_NUMBER('1234','999') FROM dual; --报错
  21. SELECT TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'),'9999') FROM dual;
  22. --字符转换为日期TO_DATE
  23. SELECT TO_DATE('17-JUN-18') FROM dual;
  24. SELECT TO_DATE('Jan 03,2016','fxMon DD,YYYY') FROM dual;--fx精确匹配
  25. SELECT TO_DATE('Jan 3,2016','Mon DD,YYYY') FROM dual;
  26. SELECT TO_DATE('Jan 03,2016','Mon DD,YYYY ') FROM dual;
  27. SELECT TO_DATE('Jan 03,2016','Mon DD;YYYY') FROM dual;
  28. --查询雇佣日期为1980/12/17的雇员,显示雇员名字和雇佣日期
  29. SELECT ename,e.hiredate FROM emp e where e.hiredate=TO_DATE('17-DEC-1980','DD-MON-YYYY');
  30. SELECT ename,e.hiredate FROM emp e where e.hiredate=TO_DATE('1980-12-17','YYYY-MM-DD');
  31. SELECT ename,e.hiredate FROM emp e where TO_CHAR(e.hiredate,'DD-MM-YYYY')='17-12-1980';

 5)通用函数:

  1. -------非空判断
  2. ---NVL函数 相当于IF语句 NVL(可能为空的列名,value)用一个指定的值value替换一个NULL
  3. SELECT comm from emp
  4. SELECT NVL(comm,0) from emp
  5. ---NVL2函数 相当于IF ELSE语句 nvl(列名,vlaue,value) 检查第一个参数,如果该参数的值不为空,则该函数返回第二个参数的值,否则返回第三个参数的值
  6. SELECT e.empno,e.ename,NVL2(comm,1,0) FROM emp e--不为空返回1为空返回0
  7. -------复杂的逻辑判断 case when then
  8. SELECTCASE e.comm
  9. WHEN 300 THEN 8888
  10. WHEN 500 THEN 6666
  11. ELSE 0
  12. ENDAS COMM FROM emp e

6)ROW_NUMBER() OVER 

row_number()从1开始,为每一条分组记录返回一个数字;

例1:ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序,再为降序以后的每条xlh记录返回一个序号。 

例2:row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)

8.多表查询--子查询

1)适用场景:查询的结果集中只涉及一个表的列(子查询的结果集只能有一列)

2)单行子查询:(内部查询结果集只有一条记录)   可以用到的符号= 、!=、  > 、 <、<>

select * from emp where deptno=(select  from dept)

3)多行子查询:(内部查询结果集有多条记录)可以用到的符号(in,any,all)

select * from emp where salary  in (100,200,300)

9.多表查询--表连接

1)适用场景:查询结果集中涉及到两个表中的列的时候

2内连接内连接,也被称为自然连接,只有两个表相匹配的行才能在结果集中出现。返回的结果集选取了两个表中所有相匹配的数据,舍弃了不匹配的数据。

等值内连接:等值内连接inner join 返回两个表中主外键匹配的数据(两个表中不为空的数大的记录数)

select * from emp e inner join dept d on e.deptno=d.deptno 

select * from emp e,dept d where e.deptno=d.deptno   简化上面的写法

非等值内连接(不是用=进行连接的)

select  e.ename ,s.grade from emp e inner join salgrade s on e.sal between s.losal  and  s. hisal

3)外连接:

外连接不仅包含符合连接条件的行,还包含左表(左连接时)、右表(右连接时)或两个边接表(全外连接)中的所有数据行。

SQL外连接共有三种类型:左外连接(关键字为LEFT OUTER JOIN)、右外连接(关键字为RIGHT OUTER JOIN)和全外连接(关键字为FULL OUTER JOIN)。

外连接的用法和内连接一样,只是将INNER JOIN关键字替换为相应的外连接关键字即可。

左连接(返回内连接的结果,同时返回左表为匹配的行)

select * from emp e left outer join dept d on e.deptno=d.deptno                        Oracle数据库可以在从表加(+)

右连接(返回内连接的结果,同时返回右表没有匹配的行)

select *  from emp e right outer  join dept d on e.deptno=d.deptno

全连接(返回所有信息包括内连接和左右连接的值)

select * from emp e full outer join dept d on e.depton=d.depton

10.集合操作符:

1)并集 UNION(合并两个或多个 SELECT 语句的结果集。)

  1. SELECT column_name(s) FROM table_name1
  2. UNION
  3. SELECT column_name(s) FROM table_name2

union all 与 union区别:

union 去除重复的数据 union all 不去除重复的数据

union 会对结果集进行排序,union all不会排序,sql优化时用union all 避免不必要的排序操作

2)交集INTERSECT(默认去除重复的数据)

合并两个SELECT语句,但只从第一个SELECT语句返回完全相同于第二个SELECT语句结果的所有行。这意味着INTERSECT是由两个SELECT语句返回相同的行(唯一)。

3)差集MINUS(默认去除重复的数据)

MINUS 指令是运用在两个 SQL 语句上。它先找出第一个 SQL 语句所产生的结果,然后看这些结果有没有在第二个 SQL 语句的结果中。如果有的话,那这一笔资料就被去除,而不会在最后的结果中出现。如果第二个 SQL 语句所产生的结果并没有存在于第一个 SQL 语句所产生的结果内,那这笔资料就被抛弃。

A minus B就意味着将结果集A去除结果集B中所包含的所有记录后的结果,即在A中存在,而在B中不存在的记录

如有问题,多多指正!

 

 

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/小蓝xlanll/article/detail/703816
推荐阅读
相关标签
  

闽ICP备14008679号