sql刷题知识点总结

 一，索引排序 order by 字段 （acs：升叙  desc：倒叙）

1.mysql单个字段降序排序

select * from table order by id desc;

2.mysql单个字段升序排序

select * from table order by id asc;

3.mysql多个字段降序排序

select * from table order by id desc,name desc;

---

二，分组函数GROUP BY 和 HAVING的使用

1.单个group by 分组函数使用样例

select   字段    from   表名   where    条件     group   by       字段

2.group by 和 having使用样例

select   字段    from   表名   group  by    字段    having    过滤条件

GROUP BY子句之后使用Having子句

        可应用限定条件进行分组，以便系统仅对满足条件的组返回结果。因此，在GROUP BY子句后面包含了一个HAVING子句。HAVING类似于WHERE（唯一的差别是WHERE过滤行，HAVING过滤组）AVING支持所有WHERE操作符。

比如查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t

select emp_no,count(emp_no) t
from salaries
group by
emp_no
having
    t>15

---

三，去重distinct方法

  distinct一般是用来去除查询结果中的重复记录的

找出所有员工具体的薪水salary情况

select distinct(salary) from salaries order by salary desc

---

四，聚合函数

函 数	说 明
AVG（）	返回某列的平均值
COUNT()	返回某列的行数
MAX（）	返回某列的最大值
MIN()	返回某列的最小值
SUM（）	返回某列值之和

1.avg(),max(),min()

avg()求平均值

SELECT AVG(users.user_age) AS avg_users FROM users

max()求最大值

SELECT MAX(users.user_age) AS USER_BEST FROM users;

min()求最小值

SELECT MIN(users.user_age) AS USER_BEST FROM users;

2.COUNT()

COUNT(*)对表中所有行的数目进行计数，包括null

select COUNT(*) AS 别名 from 表名;--包括列为null的行

COUNT(column)对特定列进行计数。

select COUNT(列名) AS 别名 from 表名--不包括列为null的行

3.SUM()函数

   用于返回指定-----列的和。

select SUM(列名*列名) AS 别名 from 表名--忽略列值为null的行。

---

五，limit

在使用数据库过程中，常会遇到查询或者导出某个数据表或者查询集的前几条或者后几条记录，LIMIT可以很好的满足需求。

1.检索前5条记录(1-5)

SELECT * FROM orange LIMIT 0,5; 

2.检索记录11-25

 SELECT * FROM orange  LIMIT 3,2;

3.检索记录11-25

SELECT * FROM orange LIMIT 10,15;

---

六，concat()函数

concat()：将多个字符串连接成一个字符串。

语法：concat(str1, str2,...)

举例：

将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

select CONCAT(last_name," ",first_name) as name from employees

---

七，尝龟创建表格，索引，视图，构造触发器，修改表名

1.尝龟create table创表

CREATE table actor_name(    
    first_name  varchar(45) NOT NULL,
   last_name  varchar(45) NOT NULL);
   insert into actor_name (first_name,last_name) values 
   ('PENELOPE','GUINESS'),
   ('NICK','WAHLBERG');

2.在已有表添加索引

① 普通索引

create index index_name
             on t_dept(name);

② 唯一索引

create unique index index_name
              on t_dept(name);

③ 全文索引

create fulltext index index_name
              on t_dept(name);

④ 多列索引

create index index_name_no
               on t_dept(name,no)

3.尝龟创建视图（CREATE VIEW）

create view  
actor_name_view(first_name_v,last_name_v) 
as 
select first_name,last_name
from actor

4.尝龟构造触发器 

在MySQL中，创建触发器语法如下：

CREATE TRIGGER trigger_name
trigger_time trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

其中：

• trigger_name：标识触发器名称，用户自行指定；
• trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
• trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
• tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
• trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句，每条语句结束要分号结尾。

5.修改表名

rename table 旧表名 to 新表名

---

八，替换函数replace()

REPLACE(String,from_str,to_str) 即：将String中所有出现的from_str替换为to_str

举例：

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变

update titles_test set emp_no = replace(emp_no,10001,10005) where id =5

---

九，length(arg)函数

length(arg)函数， arg是字符串， 返回的是所求的字符串的长度。

经典例题：

思路：

把串 "10,A,B" 中的 逗号用空串替代， 变成了 "10AB"

然后原来串的长度 - 替换之后的串的长度 就是 被替换的 逗号的个数

select (length("10,A,B") - length(replace("10,A,B",",","")) )
as cnt;

---

十，substring()函数

substring（）函数从特定位置开始的字符串返回一个给定长度的子字符串

两种形式：

SUBSTRING(string,position);
SUBSTRING(string FROM position);

• string参数是要提取子字符串的字符串。
• position参数是一个整数，用于指定子串的起始字符，position可以是正或负整数。

例题：获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

select e.first_name from employees e order by SUBSTRING(first_name,-2);

---

十一，round函数

round函数用于数据的四舍五入

格式：

round(x,d)  ，x指要处理的数，d是指保留几位小数（d不写默认为0）

查询: select round(1123.26723,2);

结果：1123.27

---

十二，date_format()函数

date_format()将日期值格式化为特定格式

语法：

DATE_FORMAT(date,format);

• date：是要格式化的有效日期值
• format：是由预定义的说明符组成的格式字符串，每个说明符前面都有一个百分比字符(%)。有关预定义说明符的列表

format类型详见：format类型

---

十三，with 表名 as 创建临时表

with 临时表名 as (查询的临时表)

---

十四，开窗函数 OVER(PARTITION BY)

        开窗函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

1.over函数的写法：

ver（partition by class order by sroce）
按照sroce排序进行累计，order by是个默认的开窗函数，按照class分区。

举例：查询每个班的第一名的成绩

SELECT * FROM (select t.name,t.class,t.sroce,rank() 
over(partition by t.class order by t.sroce desc) 
mm from T2_TEMP t) where mm = 1;

2.OVER(partition by abc ORDER BY  zxc)的使用说明和意义

先把一组数据按照制定的字段(abc)进行分割成各种组，然后组内按照某个字段(zxc)排序。

---

十五，sum() over() 函数的使用

    over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()，sum()等一起使用。

例子1：

sum(sal) over (partition by deptno order by ename) 
部门连续求和,各部门的薪水"连续"求和

例子2：

用户的名字，以及截止到某天，累计总共通过了多少题，并且查询结果先按照日期升序排序，再按照姓名升序排序，有登录却没有刷题的哪一天的数据不需要输出，上面的例子查询结果如下:

select u.name,ps.date,sum(ps.number) over (partition by u.name order by date)
from user u
inner join passing_number ps on ps.user_id=u.id
ORDER BY ps.date,u.name

---

十六，case when then else 用法

解释：

SELECT
case ————-如果
when sex=’1’ then ‘男’ ————-sex=’1’，则返回值’男’
when sex=’2’ then ‘女’ ————-sex=’2’，则返回值’女’
else 0 ————-其他的返回’其他’
end ————-结束
from sys_user ——–整体理解： 在sys_user表中如果sex=’1’，则返回值’男’如果sex=’2’，则返回值’女’ 否则返回’其他’

例子1：

SELECT
CASE WHEN STATE = ‘1’ THEN ‘成功’
WHEN STATE = ‘2’ THEN ‘失败’
ELSE ‘其他’ END
FROM SYS_SCHEDULER

例子2：

SELECT STATE
CASE WHEN ‘1’ THEN ‘成功’
WHEN ‘2’ THEN ‘失败’
ELSE ‘其他’ END
FROM SYS_SCHEDULER