MySQL学习2：查询操作

一.SELECT语句简介

• SELECT DISTINCT ‘学生’,last_name AS “学生姓名”,salary*(1+IFNULL(commission_pct,0)) FROM employees WHERE department_id=90;

1.基本查询结构

• SELECT… FROM… WHERE…

①注意*是表示所有字段

2.列的别名

• AS定义别名，""双引号里面可以带有空格的别名

3.去除重复行

• SELECT DISTINCT

4.定义常数

• 直接添加SELECT '学生’自动匹配行数

5.DESCRIBE显式表结构

• 显式了表字段的详细内容

6.过滤数据

• where

二.运算符

1.注意字符串的隐式转换

• 100+‘1’=101
  100+‘a’=100
  100+null=null
  即字符串碰到等于加法会隐式转化，如果转换成功则返回值，如果转换不成功，则返回0

2.注意等号的运用

• 1='1’返回值1，因为字符串的隐式转换
  0='a’返回值1，因为字符串的隐式转换
  ‘a’='b’返回值0，此时不涉及到隐式转换即左右两边都是字符

3.注意null的运用

无论什么与null相等结果都为无结果

4.具体的运用

①is null\is not null

• #查询不是null的绩效
  SELECT commission_pct
  FROM employees
  WHERE commission_pct IS NULL;

②LEAST\GREAST

• #查询两个id号中小id的email并返回小的id
  SELECT email,LEAST(manager_id,department_id) AS id
  FROM employees;
  WHERE LEAST(manager_id,department_id);

③BETWEEN AND

• #查询工资10000到50000的人
  SELECT salary
  FROM employees
  WHERE salary BETWEEN 10000 AND 50000;

④IN\NOT IN

• #查出department_id是10和50的人
  SELECT department_id
  FROM employees
  WHERE department_id IN(50,10);

⑤LIKE

• #查询以e为倒数第二个字符的姓名
  SELECT first_name
  FROM employees
  WHERE first_name LIKE ‘%e_’;

⑥OR || AND && NOT !

• #查询manager_id为123或者124的人
  SELECT manager_id
  FROM employees
  WHERE manager_id=123 || manager_id=124;
• #注意此意思是不是123号和是124的人
  SELECT manager_id
  FROM employees
  WHERE NOT manager_id=123 || manager_id=124;

三.排序和分页

SELECT first_name,last_name,salary AS wage ,manager_id AS id
FROM employees
ORDER BY wage,id DESC
LIMIT 0,20

1.使用order by来实现排序操作

①asc 升序（默认） desc 降序

②列的别名可以在order by使用，不能在where中使用

即注意执行顺序 from -where -select -order -by

③注意二级排序，即第二个参数使用二级排序

2.使用limit来进行分页操作

①第一个参数为偏移量

②第二个参数为显式几行数据

③公式 limit (pageno-1)*pagesize,pagesize即为显式第pageeno页，显示pagesize个数据

四.多表查询

1.各个表之间有联系，故需要使用多表查询

2.初探多表查询

①笛卡尔积

返回107*27（即两张表做了一个笛卡尔积的运算即两张表全连）
SELECT employee_id
FROM employees,departments

②找到员工的位置信息

总共106行数据,即所有员工有位置信息的都查出（还有一个员工没有部门）
SELECT em.employee_id,em.last_name,de.location_id
FROM departments AS de,employees AS em
WHERE de.department_id=em.department_id

③规矩

字段前尽量都指明其所在的表
给表起了别名后尽量用别名
n个表至少n-1个连接条件

3.多表查询的分类

①等值连接和非等值连接

非等值连接：例如求员工工资的等级（此时没有直接对应，而是有一个区间，需要用区间来比较）
SELECT em.employee_id,em.last_name,jb.grade_level
FROM employees em,job_grades jb
WHERE em.salary>=jb.lowest_sal&&em.salary<=highest_sal

②自连接和非自连接

非自连接：例如求员工的id与其管理者的id（此时并不是两者表，而是一张表）
SELECT em.employee_id,em.first_name,ma.first_name
FROM employees em,employees ma
WHERE em.manager_id=ma.employee_id

五.多表查询分类的重点：内连接和外连接

1.内连接：满足数据关系的查出并列出表格

2.外连接：不满足数据关系的列也查出

3.详解重点外连接

①例题解析：查询所有员工的last_name,department_name（）

②注意sql92的外连接：

左外连接，右外连接，满外连接的（+和full）

③sql99的语法使用join on解决外连接的问题

• a表 left join b表 意思是a表作为左表来去匹配b表的某个字段
  即a左外连接b，a表匹配b表并加上a的信息
  即a右外连接b，b表匹配a表并加上b的信息
  （右外连接后面匹配前面，左外连接前面匹配后面）
  搜索所有的题目。。。信息 一定是外连接

• 左外连接和右外连接：左外连接理解为左表去匹配右表某个字段的相同，相同则填入信息，不同则填入右表信息null，右外连接同理

• 左外连接：LEFT JOIN ON
  SELECT em.last_name,de.department_name,de.department_id
  FROM employees AS em LEFT JOIN departments AS de
  ON em.department_id=de.department_id

• 右外连接：RIGHT JOIN ON
  SELECT em.last_name,de.department_name,de.department_id
  FROM employees AS em RIGHT JOIN departments AS de
  ON em.department_id=de.department_id

• 左小块和右小块

• 左小块：去除匹配上的相同的部分（相同部分为de.department_id不为null）
  SELECT em.last_name,de.department_name,de.department_id
  FROM employees AS em LEFT JOIN departments AS de
  ON em.department_id=de.department_id
  WHERE de.department_id IS NULL
  可改为 WHERE NOT de.department_id IS NOT NULL（下面都可改为这样理解）

• 右小块：去除匹配上的相同的部分（相同部分为em.department_id不为null）
  SELECT em.last_name,de.department_name,de.department_id
  FROM employees AS em RIGHT JOIN departments AS de
  ON em.department_id=de.department_id
  WHERE NOT em.department_id IS NOT NULL

③其他的都可通过四者匹配得出结果

• 匹配关键字：UNION ALL
  例如满外连接：
  SELECT em.last_name,de.department_name,de.department_id
  FROM employees AS em LEFT JOIN departments AS de
  ON em.department_id=de.department_id
  UNION ALL
  SELECT em.last_name,de.department_name,de.department_id
  FROM employees AS em RIGHT JOIN departments AS de
  ON em.department_id=de.department_id
  WHERE NOT em.department_id IS NOT NULL