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