【总结】SQL语句

该文章为知识总结的文章，如果是初学者，建议先从专栏学习：数据库专栏

一、常用数据处理函数

1. substr

查询姓名以 M 开头所有的员工

select * from emp where substr(ename, 1, 1)=upper('m');

2. trim

会去首尾空格，不会去除中间的空格

取得工作岗位为 manager 的所有员工

select * from emp where job=trim(upper('manager '));

3. ifnull

select ifnull(comm,0) from emp; 

如果 comm 为 null 就替换为 0 在 SQL 语句当中若有 NULL 值参与数***算，计算结果一定是 NULL 为了防止计算结果出现 NULL，建议先使用 ifnull 空值处理函数预先处理。 以下 SQL 是计算年薪的：

select empno,ename,sal,(sal+ifnull(comm,0))*12 as yearsal from emp;  

4. case … when … then … else … end

如果 job 为 MANAGERG 薪水上涨 10%,如果 job 为 SALESMAN 工资上涨 50%

select empno, ename, job, sal, case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 end as newsal from emp;

其他的工资不动，需要加else

select e.*,sal ,case job when 'salesman' then sal*1.1 when 'clerk' then sal*1.2 else sal end as new_sal from emp e;

二、常用聚合函数

注意：

• 分组函数自动忽略空值，不需要手动的加 where 条件排除空值。
• select count(*) from emp where xxx; 符合条件的所有记录总数。
• select count(comm) from emp; comm 这个字段中不为空的元素总数。
• 聚合函数不能直接使用在 where 关键字后面。

三、分组查询

1. group by

取得每个工作岗位的工资合计，要求显示岗位名称和工资合计

select job, sum(sal) from emp group by job;

注意：在 SQL 语句中若有 group by 语句，那么在 select 语句后面只能跟聚合函数+参与分组的字段。

错误示范：select empno,deptno,avg(sal) from emp group by deptno;

2. having

如果想对分组数据再进行过滤需要使用 having 子句

取得每个岗位的平均工资大于 2000

select job, avg(sal) from emp group by job having avg(sal) >2000;

3. select 语句顺序总结

select 字段
from 表名
where …….
group by ……..
having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)
order by ……..

语句的执行顺序：

1. 首先执行 where 语句过滤原始数据

2. 执行 group by 进行分组

3. 执行 having 对分组数据进行操作

4. 执行 select 选出数据

5. 执行 order by 排序

原则：能在 where 中过滤的数据，尽量在 where 中过滤，效率较高。 having 的过滤是专门对分组之后的数据进行过滤的。

四、连接查询（重点）

也可以叫跨表查询， 需要关联多个表进行查询

SQL99语法相比92语法，将连接条件和where分离

内连接外连接的区别？

• 内连接：只有两张表相匹配的行才能出现在结果集
• 外连接：左连接以左面的表为准和右边的表比较，和左表相等的不相等都会显示出来，右表符合条件的显示

内连接

• 表 1 inner join 表 2 on 关联条件
• 做连接查询的时候一定要写上关联条件
• inner 可以省略

外连接

• 左外连接

  • 表 1 left outer join 表 2 on 关联条件

  • 做连接查询的时候一定要写上关联条件

  • outer 可以省略

• 右外连接

  • 表 1 right outer join 表 2 on 关联条件

  • 做连接查询的时候一定要写上关联条件

  • outer 可以省略

    • 左外连接（左连接）和右外连接（右连接）的区别：
    • 左连接以左面的表为准和右边的表比较，和左表相等的不相等都会显示出来，右表符合条件的显示

左外连接（左连接）和右外连接（右连接）的区别：

• 左连接以左面的表为准和右边的表比较，和左表相等的不相等都会显示出来，右表符合条件的显示,不符合条件的不显示
• 右连接恰恰相反，以上左连接和右连接也可以加入 outer 关键字，但一般不建议这种写法

五、视图的作用

• 某些频繁使用的查询语句(如级联查询)，可能很复杂，可以利用视图简化查询，重用sql

• 保护数据，只授予特定权限，如查询权限

• 视图本身不包含数据，只是对sql语句的一个封装，如果需要封装复杂的sql需要先测试性能

• 用于数据检索，不能更新数据

六、如何创建删除索引？

修改索引需要先删除在添加

1. 添加PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

2. 添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

3. 添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4. 添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

5. 添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

6. 删除索引

DROP INDEX login_name_index ON user;