SQL笔记

SQL

四种常用关系型数据库及其对应SQL语言分别是

  1. MySQL(mysql)

  2. Oracle(sqlplus)

  3. SQL Server(ssms)

  4. PostgreSQL(psql)。

 

 

SQL基础知识

SQL的注释

--单行注释
/* 多行注释
    这是新的一行      
    结束行 */
select name,salary from tablename   ##运用注释调试sql语句
-- where salary > 10000
order by salary; 
​

null值

null并不代表0和空，它所表达的意思是未知。

select 1+null;
##结果是null，毕竟未知加未知不就是未知嘛
​
select 1 where null is null;
##结果是1，说明is null是用来判断值是否为null的，不能用=号
​
SELECT 1 WHERE '' is null
##结果是空
​
SELECT 1 WHERE '' is not null
##结果是1
​

 

 

增删改查

select(查询)

完整的查询

##############################################################################
SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]
#################################################################################

 

 

 

select * from tablename      ##查询全部
select name,(salary+1000)*12 from tablename     ##数字类型可以进行计算
​
​
######################################别名
##别名不能用关键词，或者别名中不能有特殊字符如%等，硬要使用需用引号
select name 姓名,salary "one month" from tablename    ##别名之间有空格用引号引起来
select name as 姓名,salary as 月薪 from tablename       ##别名设置1
select name 姓名,salary 月薪 from tablename             ##别名设置2
​
######################################去重  distinct  经常配合聚合函数使用
##场景，在调岗表中查询有多少员工换过岗位，去重多次换岗的
select distinct id from job_history 
​
​
######################################条件过滤 where
SELECT 5 WHERE 1<>2;   ##当1不等于2时查询5这个数，结果是5
select name,salary,hire_date from tablename where hire_date<'2020-02-01';##以时间查询
​
######################################and、or、not运算符
##and优先级高于or，所以先看and，直接用括号括起来条件也行，not就是取补集相当于!号
select name,salary,hire_date from tablename where not(salary>18000 or hire_date<'2020-02-01'and salary>10000)##过滤掉工资大于18000，以及入职日期在2020-02-01以前且工资大于10000的员工
​
######################################in条件，相当于or重复多次，也存在not in
select name,salary,hire_date from tablename where name in('李四','王五');##名字在两者之一就行
select name,salary from tablename where name not in ('李四','王五');##若有数据名字为空值,not in则无法对空值数据进行判断，会出现查询不返回全部数据的问题，解决方法也很简单，对not in字段进行not is null过滤即可。
​
######################################between指定范围 ，也存在not between
select name,hire_date from tablename where hire_date between '2020-02-01' and '2021-02-01'
select name,hire_date from tablename where hire_date >= '2020-02-01' and hire_date <= '2021-02-01'  ##等价于上面的between。
​
######################################like 模糊查询
##      %表示任意长度字符串包括0长度，_表示单个字符
select name from tablename where name like '%国%' ##查询名字里带有国字的人
select name from tablename where name like '_国%' ##查询名字第二个字是国字的人
​
######################################order by排序
select name,salary from tablename order by salary;##按照月薪排序，默认升序asc
select name,salary from tablename order by salary desc;##按照月薪降序排序
select name,salary 'one month' from tablename order by 'one month';##可以使用别名进行排序
select name,salary,age from tablename order by salary desc,age age;##多排序,越前面优先级越高
​

 

insert into (插入)

 

## 在tablename表中插入列名1为值1、列名2为值2、列名3为值3...的数据
insert into tablename(列名1,列名2,列名3...) values(值1,值2,值3...);
##如果列名有默认值还给它插入一个null，结果将是存入null。

 

 

update set(更新)

 

##在tablename表中更新列2为Y的列1为X。不加where会修改所有数据
update tablename set 列1=X where 列2=Y; 
update tablename set 列1=X,列2=Y where 列3=Z; ##可以修改多个字段
update tablename set 列1=default where 列2=Y; ##可以改成默认值
​
#######################################使用子查询
update tablename set 列1=X where 列2=(select ...); ##select只返回一个列名时使用
update tablename set 列1=X where 列2 in (select ... or ...); ##select返回多个列名时使用
​
​

 

 

delete from(删除)

 

##删除操作，不加where会删除此表中所有数据
delete from tablename where ...
##删除数据还有truncate table tablename它是DDL和drop table tablename一样,不过它更倾向于表重置
truncate table tablename  ##这个方法是直接把空间回收，所以删除表数据后无法回滚，但是速率非常快
##########################子查询
delete from tablename where  列1 = (select...)   ##select只返回一个列名时使用
delete from tablename where  列1 in (select...)  ##select返回多个列名时使用
​

 

进阶

 

多表查询

##基础多表查询
select * from tablename1,tablename2 where tablename1.列x=tablename2.列y
##字段在两个表中都有可以用表名.字段名来表示如a.name,采用别名可以大大简化sql的编写
select a.name salary from tablename1 a,tablename2 b where a.列x=b.列y
​

外连接

left join  	-- 显示左边
right join  -- 显示右边
full join 	-- 都显示

交叉连接

cross join	-- 得到两表乘积数的数据

集合联合

union  		-- 去重后的联合
union all 	-- 不去重
intersect 	-- 取交集

 

聚合函数

avg()		-- 平均
count()		-- 数量，不统计空值
min()		-- 最低
max()		-- 最高
sum()		-- 汇总

 

通常遇到计算财务问题需要保留小数点后两位也就是分

select cast(avg(salary) as decimal(10,2) as '工资') from tablename; -- 使用cast函数中的decimal来确定数字的小数点后面几位。

 

分组

group by	-- 分组
having 		-- 过滤分组

分组查询标准格式:

select 字段列表 from 表名 [where 分组前条件限定]group by 分组字段名[having 分组后条件过滤];

-- 编写思路：
select from 表名 group by 分组字段名;再写查询内容字段填入select from之间，有前提条件的话在group by前面加where条件，有过滤条件在group by后加having条件。

-- 分组之后查询的字段为分组字段和聚合函数，查询其他字段没有意义。执行顺序：where>聚合函数>having

为什么要使用having

查询如果使用了聚合函数并对其进行了分组，如果想要对得到的聚合值进行过滤则需要使用having，因为使用where会出现条件字段不存在的情况，条件是需要聚合之后再得到的，直接使用where不适用。

 

 

子查询

将完整的查询语句嵌套在查询语句中，就运用到了子查询，一般用在from后面作为完整的表，where后面的条件得到条件的结果不止一个则可以用in，表的连接也能使用子查，甚至字段也可以用子查询表示。

传统子查询

##案例
select * from (select ...) where in (select ...) group by ... having ... order by ...

 

all关键字

-- 表示的意思是，查询tablename表中filed1不符合(select ...1)全部元素的数据
-- 比如查询出1812班不是特长生的全部学生可以使用all归纳子查询
select * from tablename where field1 <> all(select ...1);

 

any关键字

any后接子查询，表示子查询中任意一个，便于使用大于小于比较一个子查询

-- 表示的意思是，查询tablename表中filed1(条件)   any【任意】 (select ...1)全部元素的数据
-- 比如查询出1812班小于任意女生年龄的所有男生
select * from tablename where field1 >/< any (select ...1);

 

相关子查询

这里简单提一下，相关子查询的子查询中会使用条件判断

查询员工大于其部门平均工资的命令，多了where deptno=e.deptno表达的意思也变得不一样，从全部门平均工资变成了各部门平均工资。

 

 

 

高级

 

窗口函数

partition by来袭：

说起partition by，这个在Mysql 8里面新加的特性，窗口函数可以干之前不能干的东西。

partition by窗口函数与group by都起到了分区的作用，其区别是group by会将结果聚合成一条，partition by则不会，所以partition by得到的数据更全面

例子：

-- 这里使用了窗口函数,聚合函数后面的over接(partition by deptno)表示各部门总工资按部门分组
select name,deptno,salary,max(salary) over (partition by deptno) from tablename;
-- (partition by deptno,name)窗口函数可以不为单一字段

-- 使用over()不加任何东西，整个表将作为一个分区被处理
select name,deptno,salary,max(salary) over () from tablename;

-- 还可以使用order by进行排序
select name,deptno,salary,max(salary) over (partition by deptno order by salary) from tablename;


-- first_value、last_value可以获取首尾的数据
select name,deptno,salary,
first_value(salary) over (partition by deptno order by salary) '第一条数据',
last_value(salary) over (partition by deptno order by salary) '最后一条数据'
from tablename;


-- lead、lag获取下一条数据和获取上一条数据，这里是按部门分组的
## 使用场景：一般用于活跃用户的判断以及上次签到的时间差等等
select name,deptno,salary,
lead(salary) over (partition by deptno order by salary) '下一条数据',
lag(salary) over (partition by deptno order by salary) '上一条数据'
from tablename;

 

窗口函数的排序

下面是作为字段的排序

-- 分组排序，并且按照team分组，在每个分组里面按照年龄降序排列。
select members,team,age,
RANK() over(partition by team order by age desc) rn , 
DENSE_RANK() over(partition by team order by age desc) dense_rn ,
ROW_NUMBER() over(partition by team order by age desc) row_num
from Teams order by team

RANK()

rank() over() 1 2 2 4 4 6 (计数排名，跳过相同的几个，eg.没有3没有5)

DENSE_RANK()

dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)

ROW_NUMBER()

row_number() over() 1 2 3 4 5 6 (赋予唯一排名)

percent_rank()

percent_rank() over (order by .....)返回某列或某列组合后每行的百分比排序如下:with cte as( SELECT ROWNUM as n FROM DUAL CONNECT BY ROWNUM <= 10)select n,percent_rank() over (order by n) as prkfrom cte;

percent_rank() 含义就是 当前行-1 / 当前组总行数-1，反映其所在位置百分比

 

 

CASE WHEN

 

/* CASE WHEN的使用,对field的结果进行归纳
select field1,field2,field,
case field
when 1 then 'xxx'
when 2 then 'xxx'
when 3 then 'xxx'
else 'xxx' end fieldname
from tablename;
*/
select name,salary,deptno,
case deptno
when 1 then '开发部'
when 2 then '测试部'
when 3 then '销售部'
else '其他部门' end deptname
from tablename;
 
posted @ 2023-10-09 15:58  haohao2036  阅读(35)  评论(0)  编辑  收藏  举报