SQL语句

Select

SQL 执行顺序

1. from
2. on
3. join
4. where
5. group by
6. having
7. select
8. distinct
9. order by
10. limit

WHERE

字段比较

代码	作用
=	等于
<>、!=	不等于
<、<=	小于
>、>=	大于
between … and …	两个值之间
is null、is not null	值是否为null
and	并且
name=‘liu’ or name=‘yang’	或者
in(a,b,c) 、not in()	包含、不包含(不在这个范围中)
like ‘_A%’	模糊查询,%匹配多个字符，下划线匹配一个字符

字段函数处理

作用	函数
字符转小写	lower(name)
字符转大写	upper(name)
字符串截取子串	substr(name,1,length)
获取字符串长度	length(name)
字符串拼接	concat(name1,name2)
字符串去空格	trim(name)
四舍五入保留整数	round(1.5621,0)
随机数	round(rand()*100,0)
避免空参与运算	ifnull(数据，当作那个值)

ORDER BY

asc 升序排列，desc 降序。
多个字段排序，只有前一个字段相等时才会启用后一个字段排序。

select name,sal 
from emp
order by sal asc,name asc;

GROUP BY

• select 中字段要在group by 中声明
• 或者 使用分组函数 sum(field)
• having 对分组后数据进行过滤。不能代替where
• 对相同数据进行分组，去重

作用	函数
计数	count(salary)
求和	sum(salary)
平均值	avg(salary)
最大值	max(salary)
最小值	min(salary)

• 分组函数自动忽略NULL
• count(*) ：统计表的总行数，整行不是空就统计

select field1,field2,max(field3) from emp
group by field1,field2;

DISTINCT

select distinct **,** from tablename;
//distinct只能出现在字段最前方 后面字段联合去重

INNER JOIN

等值连接

select e.ename,d.dname
 from emp e
 inner join dept d
 on e.deptno=d.deptno
 where 筛选条件

---

表自关联——表中所属情况

select a.ename as '员工名',b.ename as '领导名'
 from emp a
 join emp b
 on a.mgr=b.empno// 员工领导编号=领导的员工编号

表自关联——表中记录排序取前几名

SELECT * FROM Employee AS E1 
LEFT JOIN Employee AS E2 ON E1.salary<E2.salary AND E1.departmentId=E2.departmentId
#查出E1表中存放小的数据，E2表中存放大的数据

SELECT Department.`name` AS Department , E1.`name` AS Employee ,E1.salary AS Salary  FROM Employee AS E1 
LEFT JOIN Employee AS E2 ON E1.salary<E2.salary AND E1.departmentId=E2.departmentId
INNER JOIN Department ON E1.departmentId=Department.id
GROUP BY Department,Employee,Salary
HAVING COUNT(DISTINCT E2.salary)<=2
#先分组再过滤 HAVING COUNT(DISTINCT E2.salary)<=2
#过滤掉大表中数据个数小于等于2的，代表我E1表中的数据 最多只有2个大于它。

LEFT JOIN

左连接查询：主查左表，附带根据条件查询右表，匹配不上null表示。

select *
from student a
left join sc b
on a.sid=b.sid
ORDER BY a.sname

RIGHT JOIN

右连接查询：主查右表，附带根据条件查询左表，匹配不上null表示。

select *
from student a
right join sc b
on a.sid=b.sid
ORDER BY a.sname

select *
from sc a
right join student b
on a.sid=b.sid
ORDER BY b.sname

子查询

select *
from sc a
right join student b
on a.sid=b.sid
where (a.score)=(select max(score) from sc)
ORDER BY b.sname
-- 查询的成绩 包含在子查询中(最高成绩)
select *
from sc a
right join student b
on a.sid=b.sid
where (a.cid,a.score) in (select sc.cid, max(score) from sc group by sc.cid)
ORDER BY b.sname

select *
from sc a
right join student b
on a.sid=b.sid
where (a.cid,a.score)not in (select sc.cid, max(score) from sc group by sc.cid)
ORDER BY b.sname

排序函数

select
    id 
   ,score
   ,rank() over(order by score desc) rank               --按照成绩排名，纯排名
   ,dense_rank() over(order by score desc) dense_rank   --按照成绩排名，相同成绩排名一致
   ,row_number() over(order by score desc) row_number   --按照成绩依次排名
   ,ntile(3) over (order by score desc) ntile         --按照分数划分成绩梯队
from scores;

Case语句

CASE WHEN SC.score>=60 AND SC.score<=100 THEN 1 END

SELECT *
FROM Student a
LEFT JOIN SC b
ON a.sid=b.sid

SELECT a.*
       ,SUM(CASE WHEN b.cid='01' THEN b.score ELSE 0 END) AS s01
       ,SUM(CASE WHEN b.cid='02' THEN b.score ELSE 0 END) AS s02
       ,SUM(CASE WHEN b.cid='03' THEN b.score ELSE 0 END) AS s03
       ,AVG(CASE WHEN b.score IS NULL THEN 0 ELSE b.score END) avs
FROM Student a
LEFT JOIN SC b
ON a.sid=b.sid
GROUP BY 1,2,3,4
ORDER BY avs DESC;

小数点保留

FORMAT（clumn，2）

CAST类型转换

CAST(
COUNT((CASE WHEN SC.score>=90 AND SC.score<=100 THEN 1 END)) AS FLOAT
)

日期处理

date日期类型： %Y-%m-%d

datetime日期类型：%Y-%m-%d %h:%i:%s

now() ：获取当前时间 年月日 时分秒

CURDATE() ：获取当前年月日

year(date)：获取传入日期的年份

TIMESTAMPDIFF(YEAR,Student.birth,CURDATE())：距离今天多少年

WEEKOFYEAR(Student.birth)：获取日期的周数

MONTH(Student.Sage)：获取日期的月份

插入日期处理

MySQL 默认日期格式 %Y-%m-%d 1999-10-01

函数：str_to_date(‘字符串日期’,‘日期格式’)；

str_to_date('01-10-1999','%d-%m-%Y'));

查询日期处理

函数：date_format(日期数据，‘日期格式’)

select sid,sname,date_format(sage,'%m/%d/%Y') as birth from student;

Oracle

表空间创建

-- 表空间
CREATE TABLESPACE DATA_LIUYANG
LOGGING 
DATAFILE '/home/oracle/app/oracle/oradata/helowin/EXAM_LIUYANG_DATA.dbf'
SIZE 50M 
AUTOEXTEND ON 
NEXT 50M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

-- 临时表空间
CREATE TEMPORARY TABLESPACE TEMP_LIUYANG
TEMPFILE '/home/oracle/app/oracle/oradata/helowin/EXAM_LIUYANG_TEMP.dbf'
SIZE 50M 
AUTOEXTEND ON 
NEXT 50M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

用户创建

CREATE USER EXAM_LIUYANG IDENTIFIED BY 123456

用户绑定表空间

--绑定表空间和临时表空间
ALTER USER EXAM_LIUYANG DEFAULT TABLESPACE DATA_LIUYANG TEMPORARY TABLESPACE TEMP_LIUYANG;

授权

--授予 权限 TO user
GRANT DBA TO EXAM_LIUYANG;

备份还原

-- 备份
Exp  jdp/jdp@192.168.0.133:1521/project.bm1oracle.com file=liuyang_20211110.dmp;
-- 还原
Imp EXAM_LIUYANG/123456@110.40.242.86:1521/helowin file=’D:\liuyang_2020104.dmp’;

connect by进行级联查询

--SUBSTR 截取字符串
--SYS_CONNECT_BY_PATH 展示以指定column和分隔符delimiter表示从根节点到当前节点的路径。
--START WITH 根节点
--CONNECT BY条件
--PRIOR 接条件为parent_id等于上级的id，查找到下一级记录后又会找parent_id等于下一级记录的id的记录
SELECT  SUBSTR(SYS_CONNECT_BY_PATH(DEPT_NAME,'->'),3) NAME_PATH
FROM    JDP_OU_DEPT
        START WITH PARENT ='root'
        CONNECT BY PRIOR DEPT_ID=PARENT 

小数个数

ROUND( column, 2 ) 

日期月份处理

ADD_MONTHS（column，5）；当前月份 +5