通过本章学习,您将可以:
* 基本的SELECT语句
* 过滤和排序数据
* 分组函数
* 分组查询
* 多表查询
* 分页查询
基本SELECT语句:
SELECT *|{[DISTINCT] column|expression} FROM table;
SELECT 标识选择哪些列
FROM 表示从哪个表中选择
SELECT * #*表示选择全部列 FROM departments; SELECT department_id,location_id #选择特定的列
FROM departments;
SQL语言大小写不敏感,SQL可以写在一行或者多行,关键字不能被缩写也不能分行,各子句一般要分行写。使用缩进提高语句的可读性。
日期和字符只能在单引号中出现
列的别名:
重命名一个列,能便于计算。
SELECT first_name AS "名字",salary "工资" #去掉AS也可以 取别名 FROM employees;
使用DESCRIBE命令,显示表结构
DESCRIBE employees
过滤和排序数据
使用WHERE子句,将不满足条件的行过滤,WHERE子句紧跟随FROM子句
SELECT *|{[DISTINCT] column|expression[alias],...} FROM table [WHERE
condition(s)]; 比较运算符
操作符 含义
= 等于
> 大于
>= 大于等于
< 小于
<= 小于等于
<> 不等于(也可以是!=)
其中还有一个不常用的<=>符号,表示可能等于,虽然可以用,但不推荐,通常使用<=> null
SELECT last_name,salary FROM empoyees WHERE salary <=3000; 其他比较运算符
操作符 含义
BETWEEN..AND 在两个值之间(包含边界)
IN(set) 等于之列表中的一个
LIKE 模糊查询
IS NULL 空值
BETWEEN..AND..的用法
SELECT last_name,salary FROM employees WHERE salary BETWEEN 2500 AND 3500;
IN的用法
SELECT employee_id,last_name,salary,manager_id FROM employees WHERE manager_id
IN(100,101,201);
LIKE的用法
SELECT first_name FROM employees WHERE first_name LIKE 'S%';
NUL的用法
SELECT last_name,manager_id FROM employees WHERE manager_id IS NULL;
逻辑运算
操作符 含义
AND 逻辑并
OR 逻辑或
NOT 逻辑否
AND的语法
SELECT employee_id,last_name,job_id,salary FROM employees WHERE salary >=100
AND job_id LIKE '%MAN%';
OR的语法
SELECT employee_id,last_name,job_id,salary FROM employees WHERE salary >=
10000 OR job_id LIKE '%MAN%';
NOT的语法
SELECT last_name,job_id FROM employees WHERE job_id NOT IN
('IT_PROG','ST_CLERK','SA_REP');
ORDER BY子句
* ASC(ascend):升序
* DESC(descend):降序 SELECT last_name,job_id,department_id,hire_date FROM
employees ORDER BY hire_date; #默认是升序,若想要改为降序则为ORDER BY hire_date DESC;
按别名排序
SELECT employee_id,last_name,salary*12 AS annsal FROM employees ORDER BY
annsal;
多个列排序
SELECT last_name,department_id,salary FROM employees ORDER BY
department_id,salary DESC;
分组函数
* AVG()求平均
* COUNT()计数
* MAX()最大值
* MIN()最小值
* SUM()求和 SELECT AVG(salary),MAX(salary),MIN(salary),SUM(salary) FROM
employees;
COUNT(计数)语法:
SELECT COUNT(*) #也可以写作SELECT COUNT(1) FROM employees WHERE department_id =50;
SELECT COUNT(commission_pct) FROM employees WHERE department_id =80;
#查询的依旧是depatment_id=80个数而不是commission_pct
分组数据:GROUP BY子句语法
SELECT department_id, AVG( salary ) FROM employees GROUP BY department_id;
在GROUP BY子句中包含多个列
SELECT department_id,job_id,SUM(salary) FROM employees GROUP BY
department_id,job_id;
不可以再WHERE子句中使用组函数,可以在HAVING子句中使用组函数。
过滤分组:HAVING子句,加在GROUP BY之后
SELECT department_id,MAX(salary) FROM employees GROUP BY department_id HAVING
MAX(salary) >10000;
笛卡尔集
* 省略连接条件
* 连接条件无效
* 所有表中的所有行互相连接
为了彼岸笛卡尔集,可以再WHERE加入有效的链接条件。
等值链接
SELECT beauty.id,NAME,boyname FROM beauty,boys WHERE beauty.boyfriend_id =
boys.id;
在表中有相同列时,再列名之前加上表明前缀
区分重复的列名
* 使用表名前缀在多个表中区分相同的列
* 在不同表中具有相同列名的列可以用表的别名加以区分
* 如果使用了表别名,则在select与剧中需要使用表别名代替表名
* 表别名最多支持32个字符长度,但建议越少越好。 SELECT b.name,bo.boyName FROM boys bo,beauty b
WHERE b.boyfriend_id = bo.id;
SQL192语法,仅仅适用于内链接
#查询员工名和对应的工种名
SELECT first_name,job_title FROM employees e,jobs j WHERE e.job_id = j.job_id
#查询有奖金的员工名和部门名
SELECT first_name,department_name,commission_pct FROM employees e,departments
d WHERE e.department_id=d.department_id AND commission_pct is not null;
#查询每个城市的部门个数
#查询每个城市的部门个数 SELECT count(*),city from locations l,departments d where
l.location_id=d.location_id GROUP BY city ORDER BY count(*);
三表连接
#查询员工名、部门名、所在的城市
SELECT first_name,department_name,city FROM employees e,departments
d,locations l WHERE e.department_id=d.department_id AND
d.location_id=l.location_id;
非等值连接
#查询员工的工资和工资级别
SELECT salary,grade_level,first_name FROM employees e,job_grades j WHERE
salary BETWEEN j.lowest_sal AND j.highest_sal;
自连接
相当于等值连接
#查询员工名和上级的名称
SELECT e.first_name AS 员工,m.first_name AS 上级 FROM employees e,employees m
WHERE e.manager_id = m.employee_id;
SQL99:使用ON子句创建连接
内链接:inner join on 等值连接,非等值连接,自连接 (92、99语法都可以)
#查询女神名和对应的男神名(可对比之前的92语法,inner join 相当于",",on 相当于"where")
SELECT name,boyName FROM beauty b INNER JOIN boys bo ON b.boyfriend_id=bo.id;
#查询员工名和对应的部门名
SELECT first_name,department_name FROM employees e INNER JOIN departments d ON
e.department_id=d.department_id;
外连接:left join on、right join on (仅仅99语法)
#查询女神名和对应的男神名(beauty表为主表)
SELECT name,boyName FROM beauty b LEFT JOIN boys bo ON b.boyfriend_id=bo.id;
SELECT name,boyName FROM boys bo RIGHT JOIN beauty b ON b.boyfriend_id=bo.id;
#查询男朋友不在男神表的女神名
SELECT name FROM beauty b LEFT JOIN boys bo ON b.boyfriend_id =bo.id WHERE
boyName IS NULL;
#查询哪个部门没有员工
SELECT department_name,first_name FROM departments d LEFT JOIN employees e ON
d.department_id = e.department_id WHERE first_name IS NULL;
交叉连接:cross join (99语法去实现笛卡尔乘积,但要避免...作用不大)
SELECT name,boyName FROM beauty CROSS JOIN boys;
热门工具 换一换