SQL，基础语法，增删改查

初识数据库

数据库基础模型

• 数据库按照数据结构来组织、存储和管理数据，实际上，数据库一共有三种模型：

1. 层次模型：层次模型就是以“上下级”的层次关系来组织数据的一种方式，层次模型的数据结构看起来就像一颗树。
   

2. 网状模型：网状模型把每个数据节点和其他很多节点都连接起来，它的数据结构看起来就像很多城市之间的路网。
   

3. 关系模型：关系模型把数据看作是一个二维表格，任何数据都可以通过行号+列号来唯一确定，它的数据模型看起来就是一个Excel表。
   

• 不过随着时间的推移和市场竞争，最终，基于关系模型的关系数据库获得了绝对市场份额。因为相比层次模型和网状模型，关系模型理解和使用起来最简单。

主流关系型数据库

• 目前，主流的关系数据库主要分为以下几类：

1. 商用数据库，例如：Oracle，SQL Server，DB2等；
2. 开源数据库，例如：MySQL，PostgreSQL等；
3. 桌面数据库，以微软Access为代表，适合桌面应用程序使用；
4. 嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序。

数据库基础术语

• 数据库中，有以下基础术语：

1. 数据库(database) ：保存有组织的数据的容器（通常是一个文件或一组文件）。
2. 数据表(table) ：某种特定类型数据的结构化清单。
3. 模式（schema）：关于数据库和表的布局及特性的信息。模式定义了数据在表中如何存储，包含存储什么样的数据，数据如何分解，各部分信息如何命名等信息。数据库和表都有模式。
4. 列(column) ： 表中的一个字段。所有表都是由一个或多个列组成的。
5. 行(row) ： 表中的一个记录。
6. 主键(primary key) ： 一列（或一组列），其值能够唯一标识表中每一行。

初识SQL

SQL是什么

• SQL 指结构化查询语言，全称是 Structured Query Language。
• SQL 让您可以访问和处理数据库，包括数据插入、查询、更新和删除。
• SQL 在1986年成为 ANSI（American National Standards Institute 美国国家标准化组织）的一项标准，在 1987 年成为国际标准化组织（ISO）标准。
• 需要注意，SQL虽然是编程语言，但是目前数据库通常只用来进行数据管理；SQL虽然是编程语言，但是目前数据库通常只用来进行数据管理；数据库连接资源有限，用完即关闭。

编写SQL语句的注意事项

1. SQL 对大小写不敏感：SELECT 与 select 是相同的。但是，针对不同的数据库，对于表名和列名，有的数据库区分大小写，有的数据库不区分大小写。建议，SQL关键字总是大写，以示突出，表名和列名均使用小写。
2. SQL 语句后建议添加分号：分号是在数据库系统中分隔每条 SQL 语句的标准方法。建议，在每条 SQL 语句的末端使用分号，这样就可以在对服务器的相同请求中执行一条以上的 SQL 语句。
3. SQL语句中的注释：--用于单行注释，具体格式为--注释内容；/* */用于多行注释，/*用于注释文字的开头，*/用于注释文字的结尾。

一些重要的SQL命令

打开第一个数据库表

• 一个数据库通常包含一个或多个表。每个表有一个名字标识（例如:"Websites"）,表包含带有数据的记录（行）。
• 可通过以下命令查看 "Websites" 表的数据：

USE runoob;
SET names utf8;
SELECT * FROM Websites;

• USE runoob; 命令用于选择数据库。
• SET names utf8; 命令用于设置使用的字符集。
• SELECT * FROM Websites; 命令用于读取数据表的信息。

查询数据

基本查询

• SELECT * FROM <表名>; ，用于查询某张表中的所有列，SELECT是关键字，表示将要执行一个查询，*表示“所有列”，FROM表示将要从哪个表查询。
• 举例如下：

--查询students表的所有数据
SELECT * FROM students; 

投影查询

• SELECT 列1, 列2, 列3 FROM ，用于查询返回某张表中的某些列的数据，而不是所有列的数据。
• 举例如下：

--从students表中返回id、score和name这三列
SELECT id, score, name FROM students; 

设置别名

• SELECT 列1 AS 别名1, 列2 AS 别名2, 列3 AS 别名3 FROM <表名>;，可在进行查询时，可使用AS为查询列重新设置一个新的别名，这样，结果集的列名就可以与原表的列名不同。
• 在实际使用过程中，可省略AS，即SELECT 列1 AS 别名1 FROM <表名>; 等效于 SELECT 列1 别名1 FROM <表名>;
• 举例如下：

--将列名score重命名为points，而id和name列名保持不变
SELECT id, score AS points, name FROM students; 

--将列名id重命名为user_id，而score和name列名保持不变
SELECT id user_id, score, name FROM students; 

条件查询

-SELECT * FROM <表名> WHERE <条件表达式>; ，SELECT语句可以通过WHERE条件来设定查询条件，查询结果是满足查询条件的记录。

• <条件表达式>可以是单一条件，或者是多个条件的级联，可使用AND，OR，NOT和()对目标条件进行级联。
• 举例如下：

--查询分数在80分或以上的学生
SELECT * FROM students WHERE score >= 80;

--查询分数在80分或以上的男同学
SELECT * FROM students WHERE score >= 80 AND gender = 'M';

--查询1班和3班的学生信息
SELECT * FROM students WHERE class_id = 1 OR class_id = 3;

--查询不是2班的学生信息
SELECT * FROM students WHERE NOT class_id = 2;

--其实NOT语句也可写成
SELECT * FROM students WHERE class_id <> 2;

-- 查询分数在80至90之间的男同学
SELECT * FROM students WHERE (score < 80 AND score > 90) AND gender = 'M';

模糊查询

• SELECT * FROM <表名> WHERE <列名> LIKE <模糊查询要求>，LIKE关键字可用于在 WHERE 语句中进行模糊匹配，它会将给定的匹配模式和某个字段进行比较，匹配成功则选取，否则不选取。
• LIKE 子句可以和通配符一起使用：

• 举例如下：

-- 查找所有姓张的学生名单
SELECT * FROM students WHERE name LIKE '张%';

-- 查询id字段中，第二个和第三个字符都是 0 的值
SELECT * FROM students	WHERE id LIKE '_00%';

-- 查找id字段中以 2 开头，且长度至少为 3 的任意值
SELECT * FROM students	WHERE id LIKE '2_%_%';

-- 查找名字中至少含有“厉 害 了”其中一个字符的记录
SELECT * FROM students	WHERE name LIKE '[厉害了]';

-- 查找名字中不包含任何英文字母的记录
SELECT * FROM students	WHERE name LIKE '[!a-zA-Z]';

查询排序

• SELECT * FROM <表名> ORDER BY 列 ，ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。
• 默认的排序规则是ASC：“升序”排序，即从小到大。ASC可以省略，即ORDER BY score ASC和ORDER BY score效果一样。
• 如果要反过来，按照从大到小排序，我们可以加上DESC表示“倒序”排序。
• 举例如下：

--将成绩从低到高进行排序
SELECT * FROM students ORDER BY score;

--将成绩从高到低进行排序
SELECT * FROM students ORDER BY score DESC;

--先按score列倒序排序，如果有相同分数的，再按gender列正序排序
SELECT * FROM students ORDER BY score DESC, gender ASC;

查询条数限制

• SELECT TOP <数量> * FROM <表名>;--SQL Server ，SELECT * FROM <表名> LIMIT <数量>;--MySQL ，SELECT * FROM <表名> WHERE ROWNUM<= <数量>;--Oracle 。TOP/ LIMIT/ROWNUM 关键字用于限定要返回的记录的数据数，可以是一个具体的数字，也可以是一个百分数。对于拥有成千上万条记录的大型数据表来说，TOP 子句非常有用，它能够压缩结果集的大小，提高程序查询效率。
• 每个数据库都有属于自己的查询条数限制语句，例如：SQL Servers使用 TOP 子句获取指定数量的记录；MySQL 使用 LIMIT 子句获取指定数量的记录；Oracle 使用 ROWNUM 子句获取指定数量的记录。
• 举例如下：

-- 截取返回前3条数据，SQL Server 
SELECT TOP 3 * FROM students;

查询分页

• SELECT * FROM <表名> LIMIT <N-M> OFFSET <M> ，果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示一部分。分页操作实际上就是从结果集中“截取”出第M~N条记录。
• 在使用LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低。
• 举例如下：

--截取显示第1至5条记录
SELECT * FROM students ORDER BY score DESC LIMIT 5 OFFSET 0;

--截取显示第6至10条记录
SELECT * FROM students ORDER BY score DESC LIMIT 5 OFFSET 5;

去重查询

• SELECT DISTINCT 列1 FROM <表名>; ，DISTINCT 关键词用于返回唯一不同的值，可用于查询表内某个字段有哪些不同的数值类型。
• 举例如下：

--查询分别有那几个班级的同学
SELECT DISTINCT class_id FROM students;

聚合查询

• SELECT <函数> FROM <表名>; ，SQL提供了专门的聚合函数，用于方便用户对于一些统计量进行快速的查询，如统计总数，平均值之类。
• 要特别注意：如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL。
• 举例如下：

--查询统计学生的数量，并设置别名为num
SELECT COUNT(*) num FROM students;

--计算男生平均成绩，并设置别名为avg_boy
SELECT AVG(score) avg_boy FROM students WHERE gender = 'M';

--查询成绩的最大值，并设置别名max_score
SELECT MAX(score) max_score FROM students;

多表查询

SELECT * FROM <表1> <表2> ，SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。

• 多表查询的结果也是一个二维表，即<表1>的每一行与<表2>的每一行都两两拼在一起返回。结果集的列数是<表1>和<表1>的列数之和，行数是<表1>和<表1>的行数之积。
• 在进行多表查询的过程中，可能会出现两张表中都有同样命名的列，如id列。为了在查询时进行明确标识，可采用<表名>.<列名>的命名方式，并可考虑为新生成的列设置别名。
• 多表查询又称笛卡尔查询，使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积，对两个各自有100行记录的表进行笛卡尔查询将返回1万条记录，对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。
• 举例如下：

- 对students表和classes表进行多表查询
SELECT * FROM students, classes;

- 抽取students表和classes表中的部分列进行多表查询，并取别名
SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;

- 还可以写成
SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;

连接查询

基础语法

• SELECT * FROM <表1> <???> JOIN <表2> ON <条件...> ，可实现对多个表进行JOIN运算。首先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。
• 具体的步骤如下：

1. 先确定主表，仍然使用FROM <表1>的语法；
2. 再确定需要连接的表，使用 JOIN 选择连接<表2>的方法；
3. 然后确定连接条件，使用ON <条件...>，例如条件是students.class_id = classes.id，表示students表的class_id列与classes表的id列相同的行需要连接；
4. 可选：还可加上WHERE子句、ORDER BY等子句。

• 举例如下：

-- 查询出所有学生，同时返回班级名称，采用内联
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

连接方法

• 具体该选用啥样的 JOIN方法将两表进行连接，该如果选择呢？具体有如下几种可选的连接方式。
• 假设查询语句为：

SELECT ... FROM tableA <???> JOIN tableB ON tableA.column1 = tableB.column2;

• 并把tableA看作左表，把tableB看成右表

1. INNER JOIN是选出两张表都存在的记录
   
2. LEFT OUTER JOIN是选出左表tableA存在的记录
   
3. RIGHT OUTER JOIN是选出右表tableB存在的记录
   
4. FULL OUTER JOIN则是选出左右表都存在的记录
   

修改数据

插入(INSERT)

• INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...); ，用于向目标表插入一条新的记录，先列举出需要插入的字段名称，然后在VALUES子句中依次写出对应字段的值。
• 如果一个字段有默认值，那么在INSERT语句中也可以不出现。
• 字段顺序不必和数据库表的字段顺序一致，但值的顺序必须和字段顺序一致。
• 还可以一次性添加多条记录，只需要在VALUES子句中指定多个记录值，每个记录是由(...)包含的一组值，并用逗号隔开。
• 举例如下：

-- 添加一条新记录
INSERT INTO students (class_id, name, gender, score) 
VALUES (2, '大牛', 'M', 80);

-- 一次性添加多条新记录
INSERT INTO students (class_id, name, gender, score) VALUES
  (1, '大宝', 'M', 87),
  (2, '二宝', 'M', 81);

更新(UPDATE)

• UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...; ，用于更新目标表中的指定记录。WHERE子句中填写需要更新的行的筛选条件。
• 如果WHERE条件没有匹配到任何记录，UPDATE语句不会报错，也不会有任何记录被更新。
• 在执行UPDATE语句时要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用UPDATE更新。
• 举例如下：

-- 更新id=1的记录
UPDATE students SET name='渣渣晖', score=59 WHERE id=1;

-- 使用表达式，更新score<80的记录，给他们加10分
UPDATE students SET score=score+10 WHERE score<80;

删除(DELETE)

• DELETE FROM <表名> WHERE ...; ，用于删除目标表中的指定记录。WHERE子句中填写需要更新的行的筛选条件。
• 和UPDATE类似，果WHERE条件没有匹配到任何记录，UPDATE语句不会报错，也不会有任何记录被更新。
• 和UPDATE类似，在执行DELETE语句时要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用DELETE删除。
• 举例如下：

-- 更新id=1的记录
DELETE FROM students WHERE id=1;

-- 删除整个表的数据
DELETE FROM students;

重命名(RENAME)

• ALTER TABLE <旧表名> RENAME TO <新表名>; 或RENAME <旧表名> TO <新表名>;，RENAME关键字可用具对标进行重命名。
• 举例如下

-- 将表students重命名为tb_students
ALTER TABLE students RENAME TO tb_students;

-- 将表students重命名为tb_students
RENAME students TO tb_students;

参考：
廖雪峰 - SQL教程
RUNOOB.COM - SQL教程