基础SQL语法语句大全（一篇学会所有SQL语句）

向右看齐 2023-09-23 18:25 97阅读 0赞

![9c9ae2d98607407c8f1961cc9c79dc61.png][]

![66ba91d9cd014cf78ba79cbfb38b4969.png][]

![0663b25c3f69476e81c34b5a98671243.png][]

![cd90d586012342ee872ab2cd4c3b4fa8.png][]

![d244dca01cda4816b7d4f791eb8e51dd.png][]

![cea1de5a6fb9481d8b349c6ec4aa4c4e.png][]

![efd555c44dbf42eeb376f4e417f6dc05.png][]

![04c8a01bbb7949ec856afcd04e9a479c.png][]

![54a1abd502cd46ed8158dc089dcf46d0.png][]

### 如：select distinct name from student; ###

![02a85ed3c35b4263a09da8904cfe7f84.png][]

![0593763a0668463198e440731079b56d.png][]

### 如：select name,salary from employee where deptno = 3; ###

![a1c6a0bd2bee427494bd534733063e6f.png][]

### 如：select \* from employees where deptno = 3 and salary>-5000;(两个条件同时满足) ###

### select \* from employees where (deptno =3 or depton =1) and salary < 5000; ###

![fb772e8914004979be46258a965d2723.png][]

### 如：select \* from dept where loc like '一楼%'；（表示查找loc字段中以一楼开头的数据，显示满足条件的所有字段对应的信息）。类似的还有%一楼%（数据中包含一楼的），%一楼（数据以一楼结尾的） ###

![a110ad5b604547a2bd8e372407435ad1.png][]

### 如：select name from employees where empno not in (3,5); ###

### select name from employees where deptno in (select depton from dept where loc like '一楼%'); ###

![f0860dc724674bb5b81d59fd81955bd3.png][]

### 如：select name from employees where hiredate between '2013-01-01' and '2013-12-31'; ###

### 否定句：select name from employees where hiredate not between '2013-01-01' and '2013-12-31'; ###

![2487748201454f66af0bbc39d1d07f6e.png][]

### 如：select name, hiredate from employees order by hiredate;（查询employees表中的name，hiredate字段的所有数据，按照hiredate字段进行默认的降序排序）   注意：order by 要写在条件或分组的后面，因为要先查询出需要的数据，才会对需要的数据进行排序。 ###

![a558ab7bb0c94e6398006767d144eff2.png][]

### 如：update employees set deptno = 2,manager = 4 where empno =4;（更新employees表中empno=4的相应字段的数据） ###

![e13bcefe159d445380b8e4cd36283d77.png][]

### 如:delete from employees where empno = 9;（删除employees表中empno=9的那行数据） ###

![ef297c0100fe4a00b5f582492dde5fea.png][]

### 如：create index in\_name on employees(name);（在employees表中的name字段上创建一个名为in\_name的索引） ###

![600bf135c6f14b5a9548fbf1f8bfca47.png][]

### 如：创建一个名为employees\_2015的视图，视图内容为hiredate<'2015-01-01'的name、salary字段的数据 ###

### create view employees\_2015 as select name,salary from employees where hiredate < '2015-01-01'; ###

![001ef145afb647e594e1e0a65ca8aec0.png][]

![b2d00f59258845489091bf3e1ca2fd51.png][]

### 注意：修改视图的数据，本质上是从底层进行修改，所以修改视图的数据，原始数据也被修改了。 ###

![93b16e3a9efe42609411f2706c3eea47.png][]

### 如：select \* from employees where manager is not null; ###

![4714797b788d4c91bc21ff25551db845.png][]

### 如：select name 姓名, salary 工资 from employees e;（表示设置字段name的别名为姓名，salary的别名为工资，表employees的别名为e） ###

### 注意：设置别名还可以用于函数中，如： ###

### select count(\*) 总人数 from employees; ###

![426a452d9b924a45acafcd355ac0791f.png][]

### 内连接：两个表同时满足条件的数据，即交集。 ###

### 左连接：左表中所有数据+右表中符合条件的数据。 ###

### 右连接：右表中所有数据+左表中符合条件的数据。 ###

### 如： ###

### 内连接：select name,title from employees **inner join** managers on employees.empno = managers.empno; ###

### 左连接：select name,title from employees **left join** managers on employees.empno = managers.empno; ###

### 内连接：select name,title from employees **right join** managers on employees.empno = managers.empno; ###

![f4f83644170e4eb68954163a15d234d3.png][]

### ![46172f41328d424496b0c39cd1f66eca.png][]也就是嵌套查询 ###

### 如：select name from employees where deptno in (select deptno from dept where loc like '二楼%')； ###

### select \* from managers where empno not in (select empno from employees); ###

![66a8d2214f9f423e9794537da2ed9b38.png][]

![8a8934d2704641fdac14221890711a66.png][]

![c0564378e0f04744838cb146726e094d.png][]

### SQL 中五个常用函数：count（）、max（）、min（）、avg（）、sum（）； ###

![1f32fd7020c540cfb2cfb211c6445061.png][]

**1****）案例实操：**

（1）计算  emp  表每个部门的平均工资

hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by
    t.deptno;

（2）计算  emp  每个部门中每个岗位的最高薪水

hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t
    group by t.deptno, t.job;

### ![bb87e3879b2d446bad64b45a95a73d9b.png][] 注意：having和where的功能差不多，但处理的对象不同，having专门处理group by分组的数据，通常情况下，有group by的语句中才会有having。 ###

**1** **）** **having**  **与**  **where**  **不同点**

（1） where  后面不能写分组函数，而  having  后面可以使用分组函数。

（2） having  只用于  group by  分组统计语句。

**2** **）案例实操**

（1）求每个部门的平均薪水大于  2000  的部门

求每个部门的平均工资

hive (default)> select deptno, avg(sal) from emp group by deptno;

求每个部门的平均薪水大于  2000  的部门

hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno
    having avg_sal > 2000;

> 参考b站视频：[38分钟学会SQL语句sql\_哔哩哔哩\_bilibili][38_SQL_sql_bilibili]

[9c9ae2d98607407c8f1961cc9c79dc61.png]: https://img-blog.csdnimg.cn/9c9ae2d98607407c8f1961cc9c79dc61.png
[66ba91d9cd014cf78ba79cbfb38b4969.png]: https://img-blog.csdnimg.cn/66ba91d9cd014cf78ba79cbfb38b4969.png
[0663b25c3f69476e81c34b5a98671243.png]: https://img-blog.csdnimg.cn/0663b25c3f69476e81c34b5a98671243.png
[cd90d586012342ee872ab2cd4c3b4fa8.png]: https://img-blog.csdnimg.cn/cd90d586012342ee872ab2cd4c3b4fa8.png
[d244dca01cda4816b7d4f791eb8e51dd.png]: https://img-blog.csdnimg.cn/d244dca01cda4816b7d4f791eb8e51dd.png
[cea1de5a6fb9481d8b349c6ec4aa4c4e.png]: https://img-blog.csdnimg.cn/cea1de5a6fb9481d8b349c6ec4aa4c4e.png
[efd555c44dbf42eeb376f4e417f6dc05.png]: https://img-blog.csdnimg.cn/efd555c44dbf42eeb376f4e417f6dc05.png
[04c8a01bbb7949ec856afcd04e9a479c.png]: https://img-blog.csdnimg.cn/04c8a01bbb7949ec856afcd04e9a479c.png
[54a1abd502cd46ed8158dc089dcf46d0.png]: https://img-blog.csdnimg.cn/54a1abd502cd46ed8158dc089dcf46d0.png
[02a85ed3c35b4263a09da8904cfe7f84.png]: https://img-blog.csdnimg.cn/02a85ed3c35b4263a09da8904cfe7f84.png
[0593763a0668463198e440731079b56d.png]: https://img-blog.csdnimg.cn/0593763a0668463198e440731079b56d.png
[a1c6a0bd2bee427494bd534733063e6f.png]: https://img-blog.csdnimg.cn/a1c6a0bd2bee427494bd534733063e6f.png
[fb772e8914004979be46258a965d2723.png]: https://img-blog.csdnimg.cn/fb772e8914004979be46258a965d2723.png
[a110ad5b604547a2bd8e372407435ad1.png]: https://img-blog.csdnimg.cn/a110ad5b604547a2bd8e372407435ad1.png
[f0860dc724674bb5b81d59fd81955bd3.png]: https://img-blog.csdnimg.cn/f0860dc724674bb5b81d59fd81955bd3.png
[2487748201454f66af0bbc39d1d07f6e.png]: https://img-blog.csdnimg.cn/2487748201454f66af0bbc39d1d07f6e.png
[a558ab7bb0c94e6398006767d144eff2.png]: https://img-blog.csdnimg.cn/a558ab7bb0c94e6398006767d144eff2.png
[e13bcefe159d445380b8e4cd36283d77.png]: https://img-blog.csdnimg.cn/e13bcefe159d445380b8e4cd36283d77.png
[ef297c0100fe4a00b5f582492dde5fea.png]: https://img-blog.csdnimg.cn/ef297c0100fe4a00b5f582492dde5fea.png
[600bf135c6f14b5a9548fbf1f8bfca47.png]: https://img-blog.csdnimg.cn/600bf135c6f14b5a9548fbf1f8bfca47.png
[001ef145afb647e594e1e0a65ca8aec0.png]: https://img-blog.csdnimg.cn/001ef145afb647e594e1e0a65ca8aec0.png
[b2d00f59258845489091bf3e1ca2fd51.png]: https://img-blog.csdnimg.cn/b2d00f59258845489091bf3e1ca2fd51.png
[93b16e3a9efe42609411f2706c3eea47.png]: https://img-blog.csdnimg.cn/93b16e3a9efe42609411f2706c3eea47.png
[4714797b788d4c91bc21ff25551db845.png]: https://img-blog.csdnimg.cn/4714797b788d4c91bc21ff25551db845.png
[426a452d9b924a45acafcd355ac0791f.png]: https://img-blog.csdnimg.cn/426a452d9b924a45acafcd355ac0791f.png
[f4f83644170e4eb68954163a15d234d3.png]: https://img-blog.csdnimg.cn/f4f83644170e4eb68954163a15d234d3.png
[46172f41328d424496b0c39cd1f66eca.png]: https://img-blog.csdnimg.cn/46172f41328d424496b0c39cd1f66eca.png
[66a8d2214f9f423e9794537da2ed9b38.png]: https://img-blog.csdnimg.cn/66a8d2214f9f423e9794537da2ed9b38.png
[8a8934d2704641fdac14221890711a66.png]: https://img-blog.csdnimg.cn/8a8934d2704641fdac14221890711a66.png
[c0564378e0f04744838cb146726e094d.png]: https://img-blog.csdnimg.cn/c0564378e0f04744838cb146726e094d.png
[1f32fd7020c540cfb2cfb211c6445061.png]: https://img-blog.csdnimg.cn/1f32fd7020c540cfb2cfb211c6445061.png
[bb87e3879b2d446bad64b45a95a73d9b.png]: https://img-blog.csdnimg.cn/bb87e3879b2d446bad64b45a95a73d9b.png
[38_SQL_sql_bilibili]: https://www.bilibili.com/video/BV1634y1R77q/?p=1&share_medium=iphone&share_plat=ios&share_session_id=5045F9F5-5482-4F81-B109-E71F3CAD8A4F&share_source=COPY&share_tag=s_i&timestamp=1650856288&unique_k=yywyDgc&vd_source=cd1022f068ce9ec092d3068164329eec