常用sql大全

常见的sql大全

入门sql

1.两个字段升序排列，且优先以第一个字段排序
select device_id,gpa,age
from user_profile
order by gpa,age
2.现在运营想要取出用户信息表中对应的数据，并先按照gpa、年龄降序排序输出，请取出相应数据
select device_id,gpa,age
from user_profile
order by gpa desc,age desc
3.请你查找employees里最晚入职员工的所有信息
select * from
employees
where
hire_date = (select max(hire_date) from employees)
4.请你查找employees里入职员工时间排名倒数第三的员工所有信息
SELECT *
FROM employees
WHERE hire_date = (
SELECT DISTINCT hire_date
FROM employees
ORDER BY hire_date DESC – 倒序
LIMIT 1 OFFSET 2 – 去掉排名倒数第一第二的时间，取倒数第
)
（以下的两种方式均表示取2,3,4三条条数据。
1.select* from test LIMIT 1,3;
当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量。

2.select * from test LIMIT 3 OFFSET 1;(在mysql 5以后支持这种写法)
当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量 ）

5.请你查找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列
select s.emp_no, salary, s.from_date, s.to_date, dept_no
from salaries s join dept_manager dm
on s.emp_no = dm.emp_no
and dm.to_date=‘9999-01-01’
and s.to_date=‘9999-01-01’
order by s.emp_no
6.请你查找所有已经分配部门的员工的last_name和first_name以dept_no，未分配的部门的员工不显示
SELECT e.last_name, e.first_name, de.dept_no
FROM dept_emp AS de JOIN employees AS e – 内联结
ON de.emp_no = e.emp_no
WHERE de.dept_no IS NOT NULL – 筛选已经分配部门的员工
7.请你查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t，以上例子输出如下:
select emp_no,count(emp_no) as t
from salaries
group by emp_no
having t > 15
8.请你找出所有员工具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显
select distinct(salary) from salaries
group by salary
order by salary desc;
9.请你找出所有非部门领导的员工emp_no，以上例子输出:
select emp_no
from employees
where emp_no not in (select emp_no from dept_manager);
10.获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示，以上例子如下:
select e.emp_no,m.emp_no
from dept_emp as e inner join dept_manager as m
on e.dept_no=m.dept_no
where e.emp_no!=m.emp_no
11.查询某个年龄段的数据
1.
select *
from user_profile
WHERE
age between 20 and 23
2.
select *
from user_profile
WHERE
20 <= age and age<= 23
12.现在运营想查看所有大学中带有北京的用户的信息，请你取出相应数据。
LIKE
匹配串中可包含如下四种通配符：
_：匹配任意一个字符；
%：匹配0个或多个字符；
[ ]：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表 达 )；
[^ ]：不匹配[ ]中的任意一个字符。
select device_id,age,university
from user_profile
where university like ‘%北京%’
13.现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。
where子句在聚合前先筛选记录，也就是说作用在group by 子句和having子句前，而 having子句在聚合后对组记录进行筛选
select university, avg(question_cnt) as avg_question_cnt,avg(answer_cnt) as avg_answer_cnt
from user_profile
group by university
having avg_question_cnt < 5 or avg_answer_cnt < 20
14.题目：现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。
union 和union all的区别
union是合并两个查询语句的结果集，并排除重复项
union all是不排除重复项的，（符合题目要求）
union使用前提
使用union合并两个表时，需要两个表的结果集字段完全一样；
SELECT device_id,gender,age,gpa
FROM user_profile
where university=‘山东大学’
UNION ALL
SELECT device_id,gender,age,gpa
FROM user_profile
where gender=‘male’
15.题目：现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量
本题注意：age为null 也记为 25岁以下
（1）用if
SELECT IF(age>=25,“25岁及以上”,“25岁以下”) AS age_cut,count() AS number
FROM user_profile
GROUP BY age_cut;
（2）case的写法。
select
(case
when age>=25 then ‘25岁及以上’
else ‘25岁以下’ end) as age_cut,
count() as number
from user_profile
group by age_cut

进阶sql

1.完整sql执行顺序（每天看一遍，不信记不住）:
1、SELECT DISTINCT

2、FROM
<left_table><join_type>
3、JOIN
<right_table> ON <join_condition>
4、WHERE
<where_condition>
5、GROUP BY
<group_by_list>
6、HAVING
<having_condition>
7、ORDER BY
<order_by_condition>
8、LIMIT
<limit_params>
2.书写顺序
1、SELECT DISTINCT

2、FROM
<left_table><join_type>
3、JOIN
<right_table> ON <join_condition>
4、WHERE
<where_condition>
5、GROUP BY
<group_by_list>
6、HAVING
<having_condition>
7、ORDER BY
<order_by_condition>
8、LIMIT
<limit_params>

高级sql

1.编写 SQL 语句，返回顾客 ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。提示：需要使用函数、拼接和别名。
select cust_id,cust_name,
upper(concat(left(cust_contact,2),left(cust_city,3)))
as user_login
from Customers;
/*
upper():小写转大写
concat(s1,s2):连接两个字符串
left(column_name,int size):取字符串左边 size个字符
*/
2.
join—连接表，对列操作
union–连接表，对行操作。
union–将两个表做行拼接，同时自动删除重复的行。
union all—将两个表做行拼接，保留重复的行
3.使用union组合查询时，只能使用一条order by字句，他必须位于最后一条select语句之后，因为对于结果集不存在对于一部分数据进行排序，而另一部分用另一种排序规则的情况。
4.插入记录的方式汇总：

普通插入（全字段）：INSERT INTO table_name VALUES (value1, value2, …)
普通插入（限定字段）：INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …)
多条一次性插入：INSERT INTO table_name (column1, column2, …) VALUES (value1_1, value1_2, …), (value2_1, value2_2, …), …
从另一个表导入：INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value]
5.replace 等同于 delete where
replace into examination_info value
(null,9003,‘SQL’,‘hard’,90,‘2021-01-01 00:00:00’)
6.修改记录的方式汇总：
设置为新值：UPDATE table_name SET column_name=new_value [, column_name2=new_value2] [WHERE column_name3=value3]
根据已有值替换：UPDATE table_name SET key1=replace(key1, ‘查找内容’, ‘替换成内容’) [WHERE column_name3=value3]
7.删除记录的方式汇总：

根据条件删除：DELETE FROM tb_name [WHERE options] [ [ ORDER BY fields ] LIMIT n ]
全部删除（表清空，包含自增计数器重置）：TRUNCATE tb_name
时间差：

TIMESTAMPDIFF(interval, time_start, time_end)可计算time_start-time_end的时间差，单位以指定的interval为准，常用可选：
SECOND 秒
MINUTE 分钟（返回秒数差除以60的整数部分）
HOUR 小时（返回秒数差除以3600的整数部分）
DAY 天数（返回秒数差除以3600*24的整数部分）
MONTH 月数
YEAR 年数
8.表的创建、修改与删除：

1.1 直接创建表：
CREATE TABLE
[IF NOT EXISTS] tb_name – 不存在才创建，存在就跳过
(column_name1 data_type1 – 列名和类型必选
[ PRIMARY KEY – 可选的约束，主键
| FOREIGN KEY – 外键，引用其他表的键值
| AUTO_INCREMENT – 自增ID
| COMMENT comment – 列注释（评论）
| DEFAULT default_value – 默认值
| UNIQUE – 唯一性约束，不允许两条记录该列值相同
| NOT NULL – 该列非空
], …
) [CHARACTER SET charset] – 字符集编码
[COLLATE collate_value] – 列排序和比较时的规则（是否区分大小写等）
1.2 从另一张表复制表结构创建表： CREATE TABLE tb_name LIKE tb_name_old

1.3 从另一张表的查询结果创建表： CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options

2.1 修改表：ALTER TABLE 表名 修改选项 。选项集合：
{ ADD COLUMN <列名> <类型> – 增加列
| CHANGE COLUMN <旧列名> <新列名> <新列类型> – 修改列名或类型
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } – 修改/删除 列的默认值
| MODIFY COLUMN <列名> <类型> – 修改列类型
| DROP COLUMN <列名> – 删除列
| RENAME TO <新表名> – 修改表名
| CHARACTER SET <字符集名> – 修改字符集
| COLLATE <校对规则名> } – 修改校对规则（比较和排序时用到）
3.1 删除表：DROP TABLE [IF EXISTS] 表名1 [ ,表名2]。
9.索引创建、删除与使用：
1.1 create方式创建索引：
CREATE
[UNIQUE – 唯一索引
| FULLTEXT – 全文索引
] INDEX index_name ON table_name – 不指定唯一或全文时默认普通索引
(column1[(length) [DESC|ASC]] [,column2,…]) – 可以对多列建立组合索引
1.2 alter方式创建索引：ALTER TABLE tb_name ADD [UNIQUE | FULLTEXT] [INDEX] index_content(content)

2.1 drop方式删除索引：DROP INDEX <索引名> ON <表名>

2.2 alter方式删除索引：ALTER TABLE <表名> DROP INDEX <索引名>

3.1 索引的使用：

索引使用时满足最左前缀匹配原则，即对于组合索引(col1, col2)，在不考虑引擎优化时，条件必须是col1在前col2在后，或者只使用col1，索引才会生效；
索引不包含有NULL值的列
一个查询只使用一次索引，where中如果使用了索引，order by就不会使用
like做字段比较时只有前缀确定时才会使用索引
在列上进行运算后不会使用索引，如year(start_time)<2020不会使用start_time上的索引
细节剖析：
在duration列创建普通索引idx_duration；
在exam_id列创建唯一性索引uniq_idx_exam_id；
在tag列创建全文索引full_idx_tag；