我的SQL自学文档

我的SQL自学文档

数据库可视化操作工具：sequelpro 链接

基本操作

网页练习题

命令行操作
#链接数据库
mysql -u root -p # u 是用户名，p 需要用密码登录数据库

#查看数据库

show databases;

#选择数据库

use database_name;

#查看数据库中的table表

show table;

#查看表格的结构

desc table;

#查看表中（一定量）的数据

select * from table_name (limit 10);

#条件查询

select * from table_name
where condition 
	and/or condition

#通配符%
%AT% 代表AT前后可以有任意字符

#查询结果过滤和排序
distinct 属性列唯一返回,直接删除重复行

select distinct column 
from table_name where condition(s);

group by返回唯一行，按某个col_name对数据进行分组

SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression
GROUP BY column;

order by 对结果的属性列进行排序

select column from table_name where condition(s) 
order by column ASC/DESC;

#查看表中的特定位置从：offset开始的limit片段

select column from table_name where condition 
order by column 
limit num_limit offset num_offset;

#join多表联合查询

select *from table_name(主表)
join table1(另一个)
	on table_name.id = table1.id(main_key)
where condition(s)
order by column DESC
limit 1

为了不丢失两表不一样的属性，提出包含左集合left join，右集合right join，全集合full join的列联表

SELECT column, another_column, …（哪个列名写前面哪个列排前面）
FROM mytable
INNER/LEFT/RIGHT/FULL JOIN another_table
    ON mytable.id = another_table.matching_id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

对属性列进行计算后重新命名
对属性列的处理可以按照题目要求进行各种判断与计算，甚至包括在表中重新加入一列

按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色，数量，有无办公室,注意一个角色如果部分有办公室，部分没有需分开统计） ✓
SELECT count(*) as count,
	Role,
	building is not null as bn 
FROM employees 
group by Role,bn
SELECT col_expression AS expr_description, …
FROM mytable
INNER/LEFT/RIGHT/FULL JOIN another_table
    ON mytable.id = another_table.matching_id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

常用统计函数:
COUNT(), COUNT(column) 计数！COUNT() 统计数据行数，COUNT(column) 统计column非NULL的行数.
MIN(column) 找column最小的一行.
MAX(column) 找column最大的一行.
AVG(column) 对column所有行取平均值.
SUM(column) 对column所有行求和.

#Having对分组完的数据再筛选

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

排序函数
row_number,为每一行记录生成一个序号，依次排序且不会重复，使用时必须和over子句选择对某一列进行排序才能生成序号。over子句中的order by 要与Sql排序记录中的order by 保持一致，否则得到的序号可能不是连续的

select row_numbe() over(order by subtime desc) as row_num,*
from table1

rank用于返回结果集的分区内每行的排名，对查询出来的记录进行排名。rank函数考虑到over子句中字段相同的情况，over字句中相同的字段rank会跳过排下一个

select rank() over(order by userid) as rank,*
from table1

在这里插入图片描述

dense_rank函数出现相同排名时，将不跳过相同排名号，紧接着上一个排名的值，有两个第一名的时候仍然跟着第二名

select dense_rank() over(order by userid) as den_rank,*
from table1

ntile函数对序号进行分组处理，将有序分区中的行分发到指定数目的组中。对于每一个行ntile将返回此行所属的组的编号。相当于将查询出来的记录集放到指定长度的数组中，每一个数组元素存放一定数量的记录。ntile函数为每条记录生成的序号就是这条记录所有的数组元素的索引（从1开始）。ntile函数有一个参数，用来指定桶数。

select ntile(4) over(order by subtime) as ntile,*
from table1

在这里插入图片描述

完整的select查询

SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;

每一列的计算都只能使用原表格中的数据，本次操作前计算出的重命名的列不能直接拿来计算

 1.
SELECT sum(Domestic_sales+International_sales) as sum_sale,
	director,
	count(*) as count, 
	sum(Domestic_sales+International_sales)/count(*) as avg_sale 
FROM movies 
left join boxoffice 
	on movies.id = boxoffice.movie_id 
group by director 
having count > 1 
order by avg_sale desc 
limit 1

2.
SELECT 
 (SELECT (Domestic_sales+International_sales) as total_sale 
     FROM movies 
     left join boxoffice 
      on movies.id = boxoffice.movie_id 
     order by total_sale desc 
     limit 1) - (Domestic_sales+International_sales) as sale_diff,
     title 
FROM movies left 
join boxoffice 
 on movies.id = boxoffice.movie_id 
order by sale_diff desc

总结

SQL语句的书写并不难，难点是SQL逻辑，尤其是需要处理的数据联合了多个数据表或者涉及到的计算层次太多的时候。SQL语句的功能其实都很明白易懂，分组、条件、排序、分页等等这些都可以根据语句来理解，也没有太多的语法限制，简单的英语语句即可，在书写多层递进的SQL语句的时候，可以分开写出每个条件的SQL语句，再根据计算顺序，将每个计算部分的功能块镶嵌进去