SELECT 6;SELECT 'a';
SELECT "aaa";SELECT 100 * 98;SELECT VERSION();
SELECT DATABASE();
SELECT USER();SELECT 'A' AS B;
SELECT 'A' B;
SELECT last_name AS 姓, last_name AS 名 FROM employees;别名有特殊字符，加双引号
SELECT last_name AS "SELECT #" FROM employees;查员工表涉及的部门编号，字段前加上 DISTINCT
SELECT DISTINCT department_id FROM employees;加号的作用是数学运算不能连接字符串，两个操作数都为数值，则进行运算，其中一方为字符型，则试图将字符转换为数值，如果转换成功，继续运算，如果转换失败，则字符型的值转换为 0，只要其中一方为 NULL，结果为 NULL
SELECT NULL + 10;
SELECT last_name + first_name AS 姓名
FROM employees;
SELECT CONCAT(last_name, first_name) AS 姓名 FROM employees AS 姓名;拼接
SELECT CONCAT('a', 'b', 'c');NULL 和任何值拼接，结果都是 NULL
SELECT CONCAT('a', 'b', NULL);ifnull() 如果为空值
SELECT IFNULL(NULL, 'default') AS ifn;SELECT * FROM employees WHERE salary > 12000;
SELECT * FROM employees WHERE department_id <> 90;
SELECT * FROM employees WHERE salary >= 10000 AND salary <= 20000;
SELECT * FROM employees WHERE department_id < 90 OR department_id > 110 OR salary > 15000;
SELECT * FROM employees WHERE NOT(department_id >= 90 AND department_id <= 110) OR salary > 15000;百分号代表通配符，任意字符，也包含 0 个字符，下划线 _ 代表任意单个字符，
SELECT * FROM employees WHERE last_name LIKE '%o%';查询第三个字母是 n，第五个字母是 n
SELECT * FROM employees WHERE last_name LIKE '__n_l%';查询第二个字符就是下划线的特殊情况，要进行转义或切换通配符，否则直接__%查询会把所有查出
SELECT * FROM employees WHERE last_name LIKE '_\_%';
SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';BETWEEN AND 可以提高简捷度，结果包含区间值，临界值的位置不能颠倒，否则零行
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
SELECT * FROM employees WHERE salary NOT BETWEEN 8000 AND 17000;IN 值必须相同或兼容，不能使用通配符
SELECT * FROM employees WHERE job_id IN ('IT_PROG', 'AD_VP');查询 NULL 用 IS / IS NOT，等于和不等于号不能判断空值
SELECT * FROM employees WHERE commission_pct IS NULL;
SELECT * FROM employees WHERE commission_pct IS NOT NULL;安全等于可用于普通值也可以判断空值
SELECT * FROM employees WHERE commission_pct <=> NULL;升序， ASC 可以省略
SELECT * FROM employees ORDER BY salary ASC; 降序
SELECT * FROM employees ORDER BY salary DESC;条件排序
SELECT * FROM employees WHERE department_id >= 90 ORDER BY hiredate ASC;表达式排序
SELECT
last_name,
first_name,
salary * 12 * (1 + IFNULL(commission_pct, 0)) AS annual_salary
FROM
employees
ORDER BY annual_salary ASC;按照姓名长度
SELECT
LENGTH(CONCAT(last_name, first_name)) AS length,
last_name,
first_name,
salary
FROM
employees
ORDER BY length;多字段排序
SELECT * FROM employees ORDER BY salary ASC, employee_id DESC;出现在其他语句中的查询语句就是子查询，也叫内查询。
子查询分为几种
子查询的使用规则
WHERE、HAVING 子句中的条件判断。=、<> 等）使用。IN、ANY、SOME、ALL）使用。子查询位置与支持情况
SELECT 后面：仅支持标量子查询。FROM 后面：支持表子查询。WHERE 和 HAVING ：支持标量子查询、列子查询，并且能支持行子查询。EXISTS ：后面支持表子查询，通常用于判断某个条件是否存在。工资大于107号员工的人
SELECT last_name FROM employees WHERE salary > (
SELECT salary FROM employees WHERE employee_id = 107
)与141号相同工种，工资比143号大的人
SELECT employee_id, last_name, job_id, salary FROM employees
WHERE job_id = (
select job_id FROM employees WHERE employee_id = 141
)
AND salary > (
SELECT salary FROM employees WHERE employee_id = 143
)哪个部门最低工资大于50号部门的最低工资
SELECT job_id, MIN(salary) AS min FROM employees GROUP BY job_id
HAVING min > (
SELECT MIN(salary) FROM employees WHERE department_id = 50
)工资最少
select * from employees WHERE salary = (select MIN(salary) FROM employees )location_id 是1400,1700的部门的员工
SELECT last_name, department_id FROM employees WHERE department_id IN (
SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400,1700)
) 比job_id 是 ‘IT_PROG’的任意一个员工工资少的其他工种的员工
SELECT * FROM employees WHERE salary < ANY(
SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG'比job_id 是 ‘IT_PROG’的所有的员工工资都少的其他工种的员工
SELECT * FROM employees WHERE salary < ALL(
SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG'查询编号最小，工资最高的人 行子查询
SELECT * FROM employees WHERE (employee_id, salary) = (
SELECT MIN(employee_id), MAX(salary) FROM employees
)每个部门员工个数
SELECT
dept.department_id AS deptId,
(SELECT COUNT(*) FROM employees WHERE department_id = dept.department_id) AS count
FROM departments dept102号员工的部门名
SELECT
employees.employee_id,
employees.last_name,
(SELECT department_name FROM departments WHERE department_id = employees.department_id) AS deptName
FROM employees WHERE employee_id = 102from后的子查询
每个部门平均工资等级
SELECT
jg.grade_level AS lavel ,
avg_dep.salary AS salary,
avg_dep.deptId AS deptId
FROM job_grades jg
RIGHT JOIN
(SELECT AVG(salary) AS salary, department_id AS deptId FROM employees GROUP BY department_id) avg_dep
ON avg_dep.salary BETWEEN jg.lowest_sal AND highest_sal相关子查询，布尔类型，看查询是否有值，括号内写查询语句
SELECT EXISTS(
SELECT * FROM employees
)
SELECT EXISTS(
SELECT * FROM employees WHERE salary = 251526
)查询有员工的部门
SELECT department_name FROM departments WHERE EXISTS (
SELECT last_name, department_id FROM employees WHERE employees.department_id = departments.department_id
)没有配偶的男生
SELECT * FROM boys WHERE NOT EXISTS (
SELECT * from beauty WHERE boyfriend_id = boys.id
)连接查询的分类:
按年代
按功能
所有行全部连接，因缺少条件造成
select `name`,boyName from beauty, boys员工名和部门名
SELECT last_name, department_name FROM departments, employees
WHERE employees.department_id = departments.department_id有奖金的员工名，工种号和工种名
SELECT last_name, jobs.job_id, job_title FROM jobs, employees
WHERE employees.job_id = jobs.job_id
AND employees.commission_pct is not null每个城市的部门数量
SELECT COUNT(departments.department_id) , locations.city FROM locations, departments
WHERE departments.location_id = locations.location_id
GROUP BY locations.city
SELECT salary, employee_id ,grade_level from employees, job_grades AS grades
WHERE salary BETWEEN grades.lowest_sal AND grades.highest_sal员工名和上级的名字
SELECT emp.last_name , emp.employee_id , emp.manager_id
FROM employees emp , employees mang
WHERE emp.manager_id = mang.employee_idSELECT job_title, last_name FROM employees emp
INNER JOIN jobs job ON job.job_id = emp.job_idINNER省略
SELECT job_title, last_name FROM employees emp
JOIN jobs job ON job.job_id = emp.job_idSELECT salary, employee_id ,grade_level from employees
INNER JOIN job_grades AS grades
ON salary BETWEEN grades.lowest_sal AND grades.highest_sal外连接
查一个表有一个表没有，主表都显示，副表和主表匹配的显示出来，没有匹配的用空填充。
left join 左边是主表
select beauty.`name`, boys.boyName FROM beauty
LEFT OUTER JOIN boys ON beauty.boyfriend_id = boys.idright join 右边的是主表
select beauty.`name`, boys.boyName FROM boys
RIGHT OUTER JOIN beauty ON beauty.boyfriend_id = boys.id没有员工的部门
select emp.last_name , dept.department_id FROM departments dept
LEFT OUTER JOIN employees emp
ON dept.department_id = emp.department_id WHERE emp.employee_id is NULL笛卡尔积
SELECT beauty.*, boys.* FROM beauty
CROSS JOIN boys 此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。