SQL学习笔记
2021-08-11 13:35:49 33 举报AI智能生成
SQL学习笔记主要记录了我在学习和实践SQL过程中的心得体会，包括SQL的基本语法、常用函数、表连接、子查询、存储过程等内容。通过学习，我掌握了如何使用SQL语句进行数据的增删改查操作，以及如何优化查询性能。此外，我还学习了如何使用事务处理数据，以及如何使用触发器和存储过程实现自动化操作。在实际项目中，我运用所学知识解决了多个数据库相关的问题，提高了工作效率。总之，通过不断地学习和实践，我对SQL有了更深入的理解，为今后的工作打下了坚实的基础。
作者其他创作
大纲/内容
SQL执行顺序
select 函数，列名 5
from 表面 1
where 筛选条件 2
group by 分组字段 3
having 分组筛选条件 4
order by 排序条件 6
from 表面 1
where 筛选条件 2
group by 分组字段 3
having 分组筛选条件 4
order by 排序条件 6
窗口函数
select *,row_num() over (partition by class order by score ) as rn
from table
where rn <=3
from table
where rn <=3
partition by 分组字段
row_num() over(将前面的视窗函数传递给这里面的数据)
更多精彩微信搜一搜《数据与编程之美》
网站的数据存储地方
SQL语句
1、拼接字符串
concat()
select concat(first_name, ',', last_name) from table
2、判断空值
ifnull()
select ifnull(salary_pct,0), salary_pct from table
3、排序
order by
多字段排序
select * from table order by emp_id desc, name asc
按昵称排序
select length(name) 名字长度 from table order by 名字长度
4、单行函数
字符函数
length()
获取字节长度【utf-8中文占3个字节，gbk中文占2个字节】
concat()
upper()
lower()
substr()
数据库索引是从1开始
substr(“数据与编程之美”,5) ===> 之美
substr(“数据与编程之美”,1,3)
从索引第一个开始，截取3个字符
instr('str1','str2')
返回str2在str1第一次出现的索引
trim()
去掉字符串前后特定字符
trim('a' from 'aaaaa数据与编程之美aaaa')
lpad('龙傲天',10,'*')
左填充
replace('str1','str2','str3')
用str3替换str1中的str2
数学函数
round(1.65，2)
四舍五入
ceil(1.01) ===> 2
向上取整
floor(9.99) ====> 9
向下取整
truncate(1.224,,1) ===> 1.2
截断函数
mod()
取余
日期函数
now()
当前日期包含时间
curdate()
当前日期不包含日期
curtime()
当前时间
year('1996-11-02')
1996
str_to_date('1996-11-02','%Y-%m-%d')
date_format(now(),'%y年%m月%d日')
datediff(now(),'1996-11-02')
流程控制函数
select if(pct is null,'哈哈','呵呵')
case 要判断的字段和表达式
when 常量1 then 要显示的值；
when 常量2 then 要显示的值；
when 常量1 then 要显示的值；
when 常量2 then 要显示的值；
select salary,dept_id,
case dept_id
when 30 then salary *1.1
when 40 then salary*2.2
else salary*3.3
end as new_salary
from table
case dept_id
when 30 then salary *1.1
when 40 then salary*2.2
else salary*3.3
end as new_salary
from table
select salary,
case
when salary>2000 then 'a'
when salary>1500 then 'b'
else 'c'
end as '级别'
from table
case
when salary>2000 then 'a'
when salary>1500 then 'b'
else 'c'
end as '级别'
from table
5、分组函数
sum()
select sum(salary) from table
忽略null值
avg()
select avg(age) from table
忽略null值
max()
min()
count()
忽略null值
select count(*) from table
统计行数
select count(distinct salary) from table
5、分组查询
select max（salary),job_id from table
group by job_id
group by job_id
select avg(salary),dept_id from table
where age>=20
group by dept_id
where age>=20
group by dept_id
select max(salary),dept_id from table
group by dept_id
having max(salary)>15000
group by dept_id
having max(salary)>15000
having 放在 group by后
select count(*).length(last_name) len_name
from table
group by len_name
having count(*)>5
from table
group by len_name
having count(*)>5
group by中还可以使用 函数
select avg(salary) dept_id,job_id
from employees
group by job_id,dept_id
from employees
group by job_id,dept_id
按多字段分组
select avg(salary) ,dept_id,job_id
from table
group by dept_id,job_id
order by avg(salary) desc
from table
group by dept_id,job_id
order by avg(salary) desc
分组和排序
6、连接查询
select city,count(*)
from table1 a
inner join table2 b
on a.id=b.id
group by a.city
where count(*)>3
from table1 a
inner join table2 b
on a.id=b.id
group by a.city
where count(*)>3
内连接，取并集
外连接，用来查询一个表中有，一个表中没有的数据
7、子查询
出现在其他语句中select语句，称为子查询
可以放在：select 语句后面，from后面，where和having后面，exists后面
查询工资比abel的高的人？
select * from
employee
where salary > (
select salary from employee where name='abel'
)
employee
where salary > (
select salary from employee where name='abel'
)
工资最少的员工有哪些？
select name,salary
from TABLE1
where salary = (select min(salary) from TABLE1)
from TABLE1
where salary = (select min(salary) from TABLE1)
查询每个部门最低工资高于dept_id为50的部门最低工资的信息
select min(salary),dept_id
from TABLE1
group by dept_id
HAVING min(salry)>(select min(salary) from TABLE1 where dept_id=50)
from TABLE1
group by dept_id
HAVING min(salry)>(select min(salary) from TABLE1 where dept_id=50)
查询location_id为1400或者1700的部门中所有的员工信息
select * from table1 where dept_id in (
select distinct dept_id from table2 where location_id in (1400,1700)
)
select distinct dept_id from table2 where location_id in (1400,1700)
)
查询每个部门的员工个数（在部门表后面加上一个该部门的员工个数）
select d.*,(
select count(*) from
employ_table e
where d.dept_id=e.dept_id
from dept_table d;
select count(*) from
employ_table e
where d.dept_id=e.dept_id
from dept_table d;
放在select中的子查询的条件可以和父查询中的字段连用
查询每个部门的平均工资等级
select ag_table.*,g.grad
from (
selct avg(salary) ag_salary,depart_id
from dept_table d
group by depart_id
) as ag_table
inner join grad_table g
where ag_table.ag_salary between lowest_sal and large_sal
from (
selct avg(salary) ag_salary,depart_id
from dept_table d
group by depart_id
) as ag_table
inner join grad_table g
where ag_table.ag_salary between lowest_sal and large_sal
放在from中的子查询结果为多行多列的数据，类似一个临时表
exists的使用
exists中的查询语句是否有结果
0表示没有结果
1表示有结果
有员工的部门名
select dept_name
from table1 a
where exists(
select * from table2 b
where a.dept_id = b.dept_id
)
from table1 a
where exists(
select * from table2 b
where a.dept_id = b.dept_id
)
select dept_name
from departments
where employ_id in (
select employ_id from employment
)
from departments
where employ_id in (
select employ_id from employment
)
查询工资高于本部门的平均工资
select e.last_name,salary,g.ag_salary,e.dept_id
from employment e
inner join(
select avg(salary) ag_salary,dept_id
from employment
group by dept_id
) g
on e.dept_id=g.dept_id
where e.salary>g.ag_salary
from employment e
inner join(
select avg(salary) ag_salary,dept_id
from employment
group by dept_id
) g
on e.dept_id=g.dept_id
where e.salary>g.ag_salary
8、分页查询
数据过多，不能一次性显示完成
limit offset，size
offset 要显示的条目的其实索引，从0开始
size 要显示的条目个数
查询第11条到第25条数据
select * from employments limit 10,15;
有奖金的员工信息，并且工资较高的前10名
select * from employments
where comission_pct not null
order by salary desc
limit 10
where comission_pct not null
order by salary desc
limit 10
常见面试题总结
0 条评论
下一页
