SQL开发

SQL开发

---

第1章：SQL概述

SQL：Structured Query Language结构化查询语言，它是使用关系模型的数据库应用语言，由IBM上世纪70年代开发出来。后由美国国家标准局（ANSI）开始着手制定SQL标准，先后有SQL-86，SQL-89，SQL-92，SQL-99等标准。

1. SQL的语言规范

• mysql对于SQL语句不区分大小写，SQL语句关键字尽量大写
• SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
• 关键字不能被缩写也不能分行
• 值，除了数值型，字符串型和日期时间类型使用单引号（’ '）
• 别名，尽量使用双引号（" "），而且不建议省略as
• 所有标点符号使用英文状态下的半角输入方式
• 必须保证所有(),单引号，双引号是成对结束的
• 可以使用（1）#单行注释 （2）–空格单行注释 （3）/* 多行注释 */

2. 命名规则

• 数据库、表名不得超过30个字符，变量名限制为29个
• 必须只能包含 A–Z, a–z, 0–9, _共63个字符
• 不能在对象名的字符间留空格
• 必须不能和用户定义的其他对象重名
• 必须保证你的字段没有和保留字、数据库系统或常用方法冲突
• 保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型了
• 在命令行中的要求：

说明：一个语句可以分开多行编写，以;或\g结束

3. SQL分类

• DDL（Data Definition Languages)：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。

  • 主要的语句关键字包括CREATE、DROP、ALTER等。

• DML（Data Manipulation Language）：数据操作语言，用于添加、删除、更新和查询数据库记录，并检查数据完整性。

  • 主要的语句关键字包括INSERT、DELETE、UPDATE、SELECT等。
  • SELECT是SQL语言的基础，最为重要。

• DCL（Data Control Language）：数据控制语言，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。

  • 主要的语句关键字包括GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等。

---

第2章：数据处理之查询

2.1 基本的SELECT语句

2.1.1 SELECT … FROM

SELECT   标识选择哪些列
FROM     标识从哪个表中选择

• 选择全部列：

SELECT *
FROM   departments;

• 选择特定的列：

SELECT department_id, location_id
FROM   departments;

2.1.2 列的别名

• 重命名一个列

• 便于计算

• 紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。

使用别名

SELECT last_name AS name, commission_pct comm
FROM   employees;

SELECT last_name "Name", salary*12 "Annual Salary"
FROM   employees;

2.1.3 去除重复行

默认情况下，查询会返回全部行，包括重复行。

SELECT department_id
FROM   employees;

在SELECT语句中使用关键字DISTINCT去除重复行。

SELECT DISTINCT department_id
FROM   employees;

2.1.4 空值参与运算

• 所有运算符或列值遇到null值，运算的结果都为null

SELECT employee_id,salary,commission_pct,
12 * salary * (1 + commission_pct) "annual_sal"
FROM employees;

2.1.5 显示表结构

使用DESCRIBE 或 DESC 命令，表示表结构。

DESCRIBE employees;

2.2 过滤数据

2.2.1 SELECT … FROM … WHERE

背景：

• 使用WHERE 子句，将不满足条件的行过滤掉
• WHERE子句紧随 FROM子句

SELECT employee_id, last_name, job_id, department_id
FROM   employees
WHERE  department_id = 90 ;

2.2.2 比较运算符

① 基本操作符

操作符	含义
=	等于(不是==)
>	大于
>=	大于、等于
<	小于
<=	小于、等于
<> 或 !=	不等于

说明：赋值符号使用 :=

SELECT last_name, salary
FROM   employees
WHERE  salary <= 3000;

② 其它比较运算符

操作符	含义
BETWEEN … AND	在两个值之间(包含边界)
IN(set)	等于值列表中的一个
LIKE	模糊查询
IS NULL	空值

1）BETWEEN … AND

使用 BETWEEN 运算来显示在一个区间内的值

SELECT last_name, salary
FROM   employees
WHERE  salary BETWEEN 2500 AND 3500;

2）IN

使用 IN运算显示列表中的值。

SELECT employee_id, last_name, salary, manager_id
FROM   employees
WHERE  manager_id IN (100, 101, 201);

3）LIKE

• 使用 LIKE 运算选择类似的值

• 选择条件可以包含字符或数字:

  • % 代表零个或多个字符(任意个字符)。
  • _ 代表一个字符。

SELECT	first_name
FROM 	employees
WHERE	first_name LIKE 'S%';

• ‘%’和‘_’可以同时使用。

SELECT last_name
FROM   employees
WHERE  last_name LIKE '_o%';

ESCAPE

• 回避特殊符号的：使用转义符。例如：将[%]转为[ %]、[]转为[ ]，然后再加上[ESCAPE‘$’]即可。

SELECT job_id
FROM   jobs
WHERE  job_id LIKE ‘IT\_%‘;

如果使用\表示转义，要省略ESCAPE。如果不是\，则要加上ESCAPE。

SELECT job_id
FROM   jobs
WHERE  job_id LIKE ‘IT$_%‘ escape ‘$‘;

4）NULL

使用 IS (NOT) NULL 判断空值。

SELECT last_name, manager_id
FROM   employees
WHERE  manager_id IS NULL;

2.2.3 逻辑运算符

操作符	含义
&& （或AND）	逻辑且
||（或OR）	逻辑或
NOT	逻辑否
XOR	逻辑异或

1）&& （或AND）

AND要求并的关系为真。

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary >=10000
AND    job_id LIKE '%MAN%';

2）||（或OR）

OR要求或关系为真。

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary >= 10000
OR     job_id LIKE '%MAN%';

3）NOT

SELECT last_name, job_id
FROM   employees
WHERE  job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

4）XOR

select last_name,department_id,salary 
from employees
where department_id in (10,20) XOR salary > 8000;

2.2.4 算术运算符

运算符	说明
+	加法
-	减法
*	乘法
/ （或div）	除法
%（或mod）	取模

SELECT employee_id,salary,department_id
FROM employees
WHERE department_id MOD 2 = 0;

2.3 排序数据和分页

2.3.1 排序数据

• 使用 ORDER BY 子句排序

  • ASC（ascend）: 升序
  • DESC（descend）:降序

• ORDER BY 子句在SELECT语句的结尾。

SELECT   last_name, job_id, department_id, hire_date
FROM     employees
ORDER BY hire_date ;

SELECT   last_name, job_id, department_id, hire_date
FROM     employees
ORDER BY hire_date DESC ;

SELECT employee_id, last_name, salary*12 annsal
FROM   employees
ORDER BY annsal;

SELECT last_name, department_id, salary
FROM   employees
ORDER BY department_id, salary DESC;

• 可以使用不在SELECT列表中的列排序。

2.3.2 分页

• MySQL中使用limit实现分页

• 背景：查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？

• 分页原理

  所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。

--前10条记录：
SELECT * FROM table LIMIT 0,10;

--第11至20条记录：
SELECT * FROM table LIMIT 10,10;

--第21至30条记录： 
SELECT * FROM table LIMIT 20,10;

• 公式**：（当前页数-1）每页条数，每页条数*

SELECT * FROM table 
LIMIT(PageNo - 1)*PageSize,PageSize;

• 注意：limit子句必须放在整个查询语句的最后！

2.4 多表查询

2.4.1 笛卡尔积错误

举例：

从多个表中获取数据：

select last_name, department_name
from employees, departments;

演示笛卡尔积的错误情况：

select count(employee_id) from employees;
输出107行
select count(department_id)from departments;
输出27行
select 107*27 from dual;

• 笛卡尔积会在下面条件下产生：

  • 省略连接条件
  • 连接条件无效
  • 所有表中的所有行互相连接

• 为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。

MySQL连接

使用连接在多个表中查询数据。

SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column1 = table2.column2;

• 在 WHERE子句中写入连接条件。

• 在表中有相同列时，在列名之前加上表名前缀。

2.4.2 分类1：等值连接 vs 非等值连接

① 等值连接

SELECT employees.employee_id, employees.last_name, 
       employees.department_id, departments.department_id,
       departments.location_id
FROM   employees, departments
WHERE  employees.department_id = departments.department_id;

多个连接条件与 AND 操作符

区分重复的列名

• 使用表名前缀在多个表中区分相同的列。

• 在不同表中具有相同列名的列可以用表的别名加以区分。

表的别名

• 使用别名可以简化查询。

• 使用表名前缀可以提高执行效率。

SELECT e.employee_id, e.last_name, e.department_id,
       d.department_id, d.location_id
FROM   employees e , departments d
WHERE  e.department_id = d.department_id;

连接多个表

连接 n个表,至少需要n-1个连接条件。
例如：连接三个表，至少需要两个连接条件。

练习：查询出公司员工的 last_name,department_name, city

② 非等值连接

SELECT e.last_name, e.salary, j.grade_level
FROM   employees e, job_grades j
WHERE  e.salary BETWEEN j.lowest_sal AND j.highest_sal;

2.4.3 分类2：自连接 vs 非自连接

• 当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询

题目：查询employees表，返回“Xxx works for Xxx”

SELECT CONCAT(worker.last_name ,' works for '</