MySQL刷题简记

SQL的执行顺序：
–第一步：执行FROM
–第二步：WHERE条件过滤
–第三步：GROUP BY分组
–第四步：执行SELECT投影列（包括执行select里面的聚合函数）
–第五步：HAVING条件过滤
–第六步：执行ORDER BY 排序
–第七步：limit 结果

有join on的话：：先进行on的过滤, 而后才进行join, 这样就避免了两个大表产生全部数据的笛卡尔积的庞大数据.

limit

limit a  指选择前a条记录
limit a,b（相当于limit a offset b,效率较慢，优化的话，可以考虑用子查询） 指从第a+1条记录开始，选择b条记录

如查找employees里入职员工时间（对应属性列为hire_data)排名倒数第三的员工所有信息:

select *
from employees
order by hire_date desc
limit 2,1

order by

order by 列属性m desc/asc 把m按照降序/升序排序，应该放在筛选条件的后面

order by 里面可以有聚合函数（GROUP BY语句中的聚合函数一起使用ORDER BY子句）

内连接

用内连接（不存在主表和附表之分，又有外连接存在）而不用from的原因是：from两个表，相当于做两个表的笛卡尔积，如果两张表都是1万条记录，笛卡尔积就是1亿条记录，而内连接是拿着主表的数据去另一个表做比较
如：
有一个全部员工的薪水表salaries简况如下:

有一个各个部门的领导表dept_manager简况如下:

请你查找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列，以上例子输出如下:

select sa.*,de.dept_no
from dept_manager as de 
INNER JOIN salaries as sa 
on de.emp_no = sa.emp_no AND de.to_date = sa.to_date
order by sa.emp_no asc

当需要内连接多个表时，要使用多个INNER JOIN ,如：
获取非manager的员工的薪水

select de.dept_no,a.emp_no,s.salary
from 
(select emp_no
from employees 
where emp_no not in (select emp_no
from dept_manager)
) a 
inner join dept_emp de on a.emp_no=de.emp_no
inner join salaries s on a.emp_no=s.emp_no
where s.to_date='9999-01-01'

外连接

左连接

表1是主表，表2是附表，保留在表1中而不再表2中的悬浮数组

SELECT ..
FROM 表1
LEFT JOIN 表2 ON 条件

例：
有一个员工表，employees简况如下:

有一个部门表，dept_emp简况如下:

请你查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工，以上例子如下:

select emp.last_name,emp.first_name,dep.dept_no
from employees as emp
LEFT JOIN dept_emp as dep
on emp.emp_no = dep.emp_no;

group by

1、Group by后面跟的属性，一般在select后也会出现。
如果group by emp_no,但是select emp_no, last_name,会显示错误，可能如下表（名字只出现一个）：

emp_no	last_name
10001	张三
10002	李四

2、group_concat(字段名)，可以根据分组结果，来放置每一组的某字段的值的集合，如：

emp_no	group_concat
10001	张三、王五、李留
10002	李四 ，张武、赵柳

select emp_no,group_concat(name) from dept_emp group by emp_no
3、我们可以使用group by+聚合函数，做很多事，如：统计每个分组的某字段的集合，还可以对这个集合的值做一些操作，比如上例子中可以，Max(hire_data),每一组中雇佣最晚的人

SELECT emp_no, COUNT(emp_no) AS t 
FROM salaries 
GROUP BY emp_no HAVING t > 15

（此书如果有group by，应该是先执行group by having ,然后再select）

group by 属性1，属性2

原表：

SELECT class_id,gender,Count(*)
from students 
group by class_id,gender

结果：
会在第一个属性分组后，在各个分组再次对第二个属性分组

聚合函数

转载教程

重复

DISTINCT表示去掉重复的行
写法：：

select DISTINCT salary 
from salaries
order by salary desc

表的基本操作

定义表

创建数据表时，表名和字段名不需要用引号括起来：

CREATE TABLE actor(
actor_id smallint(5) primary key,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update date not null);

MYSQL创建数据表的三种方法：

1、常规创建

create table if not exists 目标表

2、复制表格

create 目标表 like 来源表

3、将table1的部分拿来创建table2

create table if not exists actor_name
(
first_name varchar(45) not null,
last_name varchar(45) not null
)
select first_name,last_name
from actor

插入

一次插入多条：

INSERT INTO actor(actor_id,
                 first_name,
                 last_name,
                 last_update)
VALUES(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
      (2,'NICK','WAHLBERG','2006-02-15 12:34:33');

碎笔记

排序

<> 与!=都是不等于的意思，但是一般都是用<>来代码不等于因为<>在任何SQL中都起作用但是!=在sql2000中用到，则是语法错误，不兼容的

1、RANK()
在计算排序时，若存在相同位次，会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，4······
2、DENSE_RANK()
这就是题目中所用到的函数，在计算排序时，若存在相同位次，不会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，2······
3、ROW_NUMBER()
这个函数赋予唯一的连续位次。
例如，有3条排在第1位时，排序为：1，2，3，4······
窗口函数用法：
<窗口函数> OVER ( [PARTITION BY <列清单> ]
ORDER BY <排序用列清单> ）
*其中[ ]中的内容可以忽略
例如：
对所有员工的薪水按照salary降序进行1-N的排名，要求相同salary并列且按照emp_no升序排列：

select emp_no, salary,
       dense_rank() over (order by salary desc) as rank
from salaries
where to_date='9999-01-01'
order by rank asc,emp_no asc;

不是用窗口函数：

SELECT
  s1.emp_no,
  s1.salary,
  (SELECT
    COUNT(DISTINCT s2.salary)
  FROM
    salaries s2
  WHERE s2.to_date = '9999-01-01'
    AND s2.salary >= s1.salary) AS `rank`  -- 去重：计算并列排名
FROM
  salaries s1
WHERE s1.to_date = '9999-01-01'
ORDER BY s1.salary DESC,
  s1.emp_no ;

查询where相关

查询为空的：： where name is NULL
查询是某个字符串时：where name='Action'

字符串拼接

||表示拼接，如'a'||'b' 等价于'ab' 

如选择一个表的first_name和last_name，拼接为一个name

select (last_name||' '||first_name) as name
from employees