数据库的基本操作

九天猎人 · 2024-6-13 21:06:09

基础知识

1. 数据库是什么？

步伐在运行的过程中，数据都是在内存中存储的

，但是内存一旦断电就会丢失数据。因此需要持久生存的数据会转交给硬盘。

在编程领域，最常用的方式是使用数据库批量的存储大量数据到硬盘。也就是说，数据库是一种电子化的资料柜。

数据库产物非常多，主流的包括：MySQL、SQLite、Oracle......这些不同的数据库都是由不同的软件公司开辟而来，每种数据库各有差异。IBM公司针对市面上各种不同厂家的数据库推出了统一的操作语言——SQL语言（语句）。

嵌入式领域中最常用的数据库产物是SQLite，本次学习的数据库就是SQLite。

SQLite是一款轻量级的数据库

，只需要数兆的体积，免安装即可使用，因此许多嵌入式相关的技术框架都会内置SQLite数据库。

2. 操作环境

本次学习为了简化操作，使用SQLiteSpy软件作为开辟环境，软件的布局如下。

SQLite数据库是以单文件进行存储的，一个数据库就是一个文件，文件的格式是.db或.db3，只需要使用SQLiteSpy软件打开.db或.db3文件即可。

打开之后可以看到有四张表：dept、emp、salgrade和bonus。

如果把数据库文件看做是excel文件，上面的四张表就相当于excel中的sheet。

3. 数据库操作类型

IBM把SQL语句分为以下几类：

DML（数据操作语言）

主要指的是数据的查询（DQL）和更新（增编削）操作，这是本次学习的重点。

DDL（数据界说语言）

用于界说数据库对象，主要用于筹划数据库表的布局。

DCL（数据控制语言）

用于控制数据库操作权限，一样平常用于高级的数据库，会由公司的数据库管理员负责。

4. 查看数据表

在SQLiteSpy软件中，只需要双击表名，就可以查询到表中的内容。

下面依次先容四张表。

4.1 dept表

存储公司的部门信息。

整理表布局如下所示。

SQLiteSpy软件双击表名查询内容，现实上是实行了下面的语句。

SELECT * FROM dept;

复制代码

把上面的语句粘贴到SQLiteSpy实行区域，手动实行也会有相同的效果。

编写SQL语句也可以在其它软件中进行，例如Notepad++，只需要切换到SQL模式即可。

4.2 emp表

emp表是雇员表，记录了公司里员工的信息。

整理表布局如下所示。

4.3 salgrade表

salgrade表是工资品级表。

4.4 bonus表

bonus表是奖金表。

bonus表是一张空表，只有表布局，没有内容。

5. 需要记着的内容

内容	掌握程度
表名	英汉互译
列名	英汉互译
员工的职位job对应的值	英译汉

一、单表查询

这个篇章主要解说的是在一个表中进行数据查询。

1. 简单查询（列查询）

简单查询的效果中包罗全部的条目（行），依次展示每条数据，唯一能做的是限制表中出现的列。

语法布局如下。

其中[]表示可选填内容

DISTINCT表示去掉重复行的数据

*表示查询全部的列，如果不想查询全部列，则编写详细的列名称。

【例子】查询全部雇员的编号（empno）、姓名（ename）、职位（job）、基本工资（sal）。

SELECT empno,ename,job,sal FROM emp
;

复制代码

【例子】查询每个雇员的编号、姓名和基本年薪（基本月薪x12）

SELECT empno,ename,sal*12 FROM emp
;

复制代码

也可以更改显示的表名，如下所示。

SELECT empno 编号,ename 姓名,sal*12 年薪 FROM emp
;

复制代码

→

【例子】查询每个雇员的编号、姓名、职位、年薪，每个雇员每个月有200元餐补、200元交通补助、夏天四个月有每月300元高温补贴，年底多发3个月基本工资。

SELECT empno,ename,job,12*(sal+400)+4*300+3*sal income
FROM emp
;

复制代码

【例子】查询全部雇员的职位有哪些。

可能会写出这样的语句。

SELECT job FROM emp
;

复制代码

现实上此题消除重复项更为合适，可以增长DISTINCT。

SELECT DISTINCT job FROM emp
;

复制代码

需要注意的是，只有全部内容都重复才算重复项。

2. 限定查询

限定查询是在简单查询的基础上限制显示的条数（行数），其语法格式如下。

主要通过WHERE子句限制行数，支持六种运算：

关系运算
取值范围运算
基数范围运算
暗昧查询
空判断
逻辑运算

2.1 关系运算

关系运算是最简单的运算符号，包括：

>、<、>=、<=、!=、<>（不等于）、==

【例子】查询公司工资高于2000的员工信息。

SELECT * FROM emp
WHERE sal>2000;

复制代码

【练习】

1.查询基本工资小于1600的全部雇员信息

SELECT * FROM emp
WHERE sal<1600;

复制代码

2.查询姓名是SMITH的雇员信息

SELECT * FROM emp
WHERE ename=='SMITH';

复制代码

文本数据要使用双引号或单引号包裹，建议在数据库中使用单引号，在编程中时使用双引号。

3.查询全部职位不是贩卖人员的信息

SELECT * FROM emp
WHERE job != 'SALESMAN';

复制代码

2.2 取值范围运算

取值范围运算使用BETWEEN...AND...

可以表示两个数的范围区间，这个区间是闭区间，包罗两头。

【例子】查询全部在1981年雇佣的雇员信息。

分析：1981年1月1日-1981年12月31日

SELECT * FROM emp
WHERE hiredate BETWEEN '1981-01-01' AND '1981-12-31';

复制代码

【练习】查询工资范围在1200-3000之间的雇员姓名和职位。

SELECT ename,job FROM emp
WHERE sal BETWEEN 1200 AND 3000;

复制代码

2.3 基数范围运算

基数范围运算使用IN关键字，表示符合给定的多个数字之一。

【例子】查询出雇员编号为7369、7566、7839、8899（不在）的雇员信息。

SELECT * FROM emp
WHERE empno IN (7369,7566,7839,8899);

复制代码

如果一个数据没有，可以使用NULL表示，IN给的待选数据中不允许出现NULL，NULL是一种特殊的数据状态，需要使用专用语句进行判断。

2.4 暗昧查询

暗昧查询可以制定查询的格式，无需进行完备的内容匹配，主要使用LIKE关键字，共同两个符号进行标志。

_

英文下划线，表示恣意一个字符。

%

百分号，表示恣意多个（0,1,2，......，n）字符。

【例子】查询全部姓名是A开头的雇员信息。

SELECT * FROM emp
WHERE ename LIKE 'A%';

复制代码

【练习】

1. 查询全部姓名第二个字母是A的全部雇员信息

SELECT * FROM emp
WHERE ename LIKE '_A%';

复制代码

2. 查询姓名中包罗A的全部雇员的信息

SELECT * FROM emp
WHERE ename LIKE '%A%';

复制代码

2.5 空判断

NULL是一种特殊的数值，使用IS NULL来进行判断。

在数据库中，反转逻辑效果使用NOT关键字，IS NULL的反转格式有两种：

NOT IS NULL
IS NOT NULL

【例子】查询不领取佣金(comm)的雇员信息。

SELECT * FROM emp
WHERE comm IS NULL;

复制代码

【例子】查询领取佣金的雇员信息。

-- 注意NOT的位置
SELECT * FROM emp
WHERE NOT comm IS NULL;

复制代码

SELECT * FROM emp
WHERE comm IS NOT NULL;

复制代码

2.6 逻辑运算

计算机中都有逻辑运算：与或非。

与：全部的条件都满足，效果才满足，在数据库中与使用AND表示。

或：只要满足一个条件，效果就满足，在数据库中或使用OR表示。

非：效果反转，在数据库中非使用NOT表示。

【例子】查询全部工资高于1300的贩卖信息。

分析：需要同时满足职位是贩卖且工资大于1300

SELECT * FROM emp
WHERE job='SALESMAN' AND sal>1300;

复制代码

【练习】

1. 查询出工资范围不在1200~1300（闭区间）之间的员工信息。

SELECT * FROM emp
WHERE sal NOT BETWEEN 1200 AND 1300;

复制代码

2. 查询出10部门的经理信息。

SELECT * FROM emp
WHERE deptno=10 AND job='MANAGER';

复制代码

3. 查询出工资高于3000，或者职位是柜员的全部雇员信息。

SELECT * FROM emp
WHERE job='CLERK' OR sal>3000;

复制代码

4. 查询出全部职位不是柜员的信息

SELECT * FROM emp
WHERE NOT job='CLERK';

复制代码

3. 查询排序

SQLiteSpy提供了一键排序的功能，只需要点击表名就可以切换次序或倒序排序的方式。

由于以后数据库是在代码中操作的，因此仍然需要学习语法规则，查询排序的语法布局如下所示。

两种排序的方式：

升序 ASC
降序 DESC

【例子】查询全部雇员的信息，要求按照工资从高到低排序。

分析：只要是排序题，排序永世是最后一步。

SELECT * FROM emp
ORDER BY sal DESC
;

复制代码

可以看到有的人工资是一样，排序支持多个字段排序，如果第一字段值相同，则使用第二字段值的排序规则。

【例子】查询全部雇员的信息，要求按照工资从高到低排序，如果工资相同，则按照雇佣日期从早到晚排序。

SELECT * FROM emp
ORDER BY sal DESC
,hiredate ASC;

复制代码

【练习】

1. 查询全部雇员的信息，效果按照年薪（基本工资x12）排序，年薪越高的越靠前。

注意：SELECT中出现了*之后，不允许写其他列名。

SELECT empno,ename,job,mgr,hiredate,sal*12 sal,comm,deptno
FROM emp
ORDER BY sal DESC

复制代码

2. 查询全部柜员的信息，同时按照工资从高到低排序。

SELECT * FROM emp
WHERE job='CLERK' ORDER BY sal DESC
;

复制代码

3. 查询全部20部门的雇员信息，要求按照雇员日期从晚到早排序。

SELECT * FROM emp
WHERE deptno=20 ORDER BY hiredate DESC;

复制代码

4. 查询全部10部门和30部门的雇员信息，要求按照雇员编号降序排序。

SELECT *
FROM emp
WHERE deptno=10 OR deptno=30
ORDER BY empno DESC;

复制代码

4. 练习

--1. 查询部门30的所有员工信息。
SELECT * FROM emp
WHERE deptno=30;
--2. 列出所有柜员（CLERK）的姓名、编号和部门编号。
SELECT ename,empno,deptno FROM emp
WHERE job='CLERK';
--3. 找出部门30中所有经理和部门20中所有柜员的详细资料。
SELECT * FROM emp
WHERE deptno=30 AND job='MANAGER' OR deptno=20 AND job='CLERK';
--4. 收取佣金的职位有哪些？
SELECT DISTINCT job FROM emp
WHERE comm IS NOT NUll;
--5. 查询不收取佣金或佣金低于100的员工信息。
SELECT * FROM emp
WHERE comm IS NULL OR comm<100;

复制代码

5. 分页查询

为了更好地显示大量的数据，可以一次性只展示一部分数据，采用多页的形式，这种查询就是分页查询。

SQLite中使用LIMIT子句与OFFSET子句限制查询的返回数量，实现分页查询，语法格式如下。

其中LIMIT表示效果的数量，OFFSET表示扬弃前几条记录，可以视环境使用。

【例子】

1. 查询前五个雇员的信息。

SELECT * FROM emp
LIMIT 5;

复制代码

2. 查询工资薪水最高的三个雇员信息。

分析：讲课过程中不会把分页查询加入到最基础的子句次序中，但是现实上其实行次序在ORDER BY之后，全部分页查询题可以先不考虑分页的问题。

此题可以先变为“查询全部人的信息，按照工资降序排布”

SELECT * FROM emp
ORDER BY sal DESC
;

复制代码

最后，筛选出前三个数据。

SELECT * FROM emp
ORDER BY sal DESC
LIMIT 3;

复制代码

3. 查询公司前一半雇佣（早期雇佣）的雇员信息。

SELECT * FROM emp
ORDER BY hiredate ASC LIMIT 7;

复制代码

此题能做出的条件是已知14人，如果对总人数未知，则需要先获得总人数。总人数的获取在后续课程中会先容相关的函数。

4. 查询公司薪金第四名到第八名的雇员信息。

--查询公司全部雇员的信息，按照薪金从高到底排序。SELECT * FROM emp
ORDER BY sal DESC
;
--生存五个人SELECT * FROM emp
ORDER BY sal DESC
LIMIT 5;--向后移动三个人SELECT * FROM emp
ORDER BY sal DESC
LIMIT 5 OFFSET 3;

复制代码

【练习】

1. 查询公司员工编号前5个员工的员工编号和姓名。

SELECT empno,ename FROM emp
ORDER BY empno ASC LIMIT 5;

复制代码

2. 查询全部雇员的信息，要求显示工资最低的三个人。

SELECT * FROM emp
ORDER BY sal ASC LIMIT 3;

复制代码

3. 查询公司最早雇佣的三个人的信息。

SELECT * FROM emp
ORDER BY hiredate LIMIT 3;

复制代码

4. 查询公司工资第6-10名的雇员信息。

SELECT * FROM emp
ORDER BY sal DESC
LIMIT 5 OFFSET 5;

复制代码

真正要使用分页查询可以基于以下的公式：

设每页显示的条数为n，当前页码为m，得分页查询公式为：

SELECT * FROM 表名 LIMIT n OFFSET (m-1)*n;

复制代码

【例子】每页显示6条数据，查询第二页的内容。

分析：n=6，m=2

SELECT * FROM emp
LIMIT 6 OFFSET 6;

复制代码

【练习】

1. 每页显示3条数据，查询第五页的内容。

SELECT * FROM emp
LIMIT 3 OFFSET 12;

复制代码

2. 每页显示5条数据，分别查询每一页的内容。

SELECT * FROM emp
LIMIT 5;
SELECT * FROM emp
LIMIT 5 OFFSET 5;SELECT * FROM emp
LIMIT 5 OFFSET 10;

复制代码

3. 取出emp表中9-12行的数据。

SELECT * FROM emp
LIMIT 4 offset 8;

复制代码

4. 假设京东显卡的数据库表名为xianka，每页显示60条数据，查询第12页的数据。

SELECT * FROM xianka LIMIT 60 offset 660;

复制代码

二、函数

函数是计算步伐中一段预先设置好的功能，可以满足反复调用的需求。SQLite中提供了众多函数，本次选取一些常见的函数进行解说。

一个函数只需要关注三个部分：

输入参数：需要什么数据
函数名称：如何处置惩罚数据
返回值：处置惩罚后的效果是什么

1. 字符串函数

用于处置惩罚TEXT类型的数据。

1.1 巨细写转换函数

UPPER

输入参数：英文的字符串

函数名称：转换大写

返回值：转换大写后的字符串

LOWER

输入参数：英文的字符串

函数名称：转换小写

返回值：转换小写后的字符串

【例子】

1. 把'Hello'转换为全大写和全小写。

SELECT UPPER('Hello'),LOWER('Hello');

复制代码

2. 查询全部雇员的姓名，要求姓名全小写。

分析：先查询全部雇员的姓名，最后套用转大写函数。

SELECT LOWER(ename) FROM emp
;

复制代码

【练习】

1. 查询全部雇员的姓名和职务，要求全部字母小写。

SELECT LOWER(ename),LOWER(job) FROM emp
;

复制代码

2. 查询全部部门的名称，要求全部字母小写。

SELECT LOWER(dname) FROM dept;

复制代码

1.2 去除左右空格

TRIM

输入参数：字符串
函数名称：去除字符串前后的空格
返回值：处置惩罚后的字符串

【例子】去除' hello world '前后的空格。

SELECT TRIM(' hello world ');

复制代码

2. 数字函数

2.1 四舍五入

ROUND

输入参数1：数字
输入参数2：生存的小数位，可省略，省略默认为生存整数位
函数名称：四舍五入
返回值：四舍五入后的数字

【例子】将123.456四舍五入生存两位小数。

SELECT ROUND(123.456,2);

复制代码

2.2 取模

可以简单地认为取模运算就是取余。

MOD

输入参数1：被除数

输入参数2：除数

函数名称：求模

返回值：计算后的数字

【例子】求出10对4取模的效果。

分析：即求出10除以4的余数。

SELECT MOD(10,4);

复制代码

2.3 绝对值

ABS

输入参数：数字

函数名称：绝对值

返回值：取绝对值之后的数字

【例子】求出-23的绝对值

SELECT ABS(-23);

复制代码

3. 日期函数

3.1 提取函数

DATE

输入参数：符合格式的日期时间字符串
函数名称：只提取日期
返回值：只包罗日期的字符串

【例子】查询全部雇员的雇用日期，要求不显示时间。

SELECT DATE(hiredate) FROM emp
;

复制代码

同理，也可以只显示时间，使用下面的函数。

TIME

输入参数：符合格式的日期时间字符串
函数名称：只提取时间
返回值：只包罗时间的字符串

也可以都显示，使用下面的函数。

DATETIME

输入参数：符合格式的日期时间字符串
函数名称：提取时间和日期
返回值：包罗日期和时间的字符串

3.2 获取本地时间

也可以使用DATETIME、DATE或TIME提取本地时区的当前时间和日期。

函数还是3.1中的三个函数，但是参数换为以下两个参数

输入参数1：'now'

输入参数2：'localtime'

【例子】获得当前时区的时间和日期。

SELECT DATE('now','localtime'),TIME('now','localtime'),DATETIME('now','localtime');

复制代码

上面的函数还可以省略第二个参数'localtime'，表示基于国际尺度时间（格林威治时间）。

SELECT DATE('now'),TIME('now'),DATETIME('now') FROM emp
;

复制代码

3.3 儒略日

儒略日（Julian Day）是公元前4713年1月1日。

JULIANDAY

输入参数：'now'或一个日期

函数名称：计算机输入日期与儒略日的差值，单位天

返回值：相差的天数，精确到小数

【例子】求出每个雇员的姓名和雇佣年龄。

SELECT ename,(JULIANDAY('now')- JULIANDAY(hiredate))/365 FROM emp
;

复制代码

也可以直接使用日期作差：

SELECT ename,DATE('now')-DATE(hiredate) FROM emp
;

复制代码

3.4 时间格式化

可以通过STRFTIME函数提取需要的时间或日期数据。

输入参数1：格式，如下所示。

输入参数2：基于哪个日期时间

返回值：提取的数据，需要注意返回的数据是文本，例如'12'

【例子】查询1月雇佣的雇员信息。

分析：先提取月份，再比对。

-- 注意STRFTIME返回值的TEXT，文本与文本比对，更改为'01'
SELECT * FROM emp
WHERE STRFTIME('%m',hiredate)='01';

复制代码

-- 也可以把等式左边的数值转换为数字
SELECT * FROM emp
WHERE CAST(STRFTIME('%m',hiredate) AS DECIMAL)=1;

复制代码

【练习】

1. 现在是几点几分（可以使用任何时区）。

SELECT STRFTIME('%H:%M',TIME('now','localtime'));

复制代码

2. 查询在上半年雇佣的雇员信息。

SELECT * FROM emp
WHERE STRFTIME('%m',HIREDATE) IN ('01','02','03','04','05','06');

复制代码

SELECT * FROM emp
WHERE STRFTIME('%m',hiredate) <= '06';

复制代码

3. 查询在每个月1号雇佣的雇员信息。

SELECT * FROM emp
WHERE CAST(STRFTIME('%d',hiredate)AS DECIMAL)=01;

复制代码

4. 查询在1981年雇佣的雇员信息。

SELECT * FROM emp
WHERE STRFTIME('%Y', hiredate)= '1981';

复制代码

SQLite数据库相比于其它数据库，处置惩罚日期和时间的能力较弱。

4. 空值函数

【例子】查询全部雇员的姓名和月收入（薪金+佣金）。

SELECT ename,sal+comm FROM emp
;

复制代码

可以发现，任何数字与NULL进行数学计算，效果都会同化为NULL。空值函数IFNULL就是用来处置惩罚这种问题的，可以把NULL等效为某个数字。

输入参数1：可能为空的数据

输入参数2：等效数字

函数名称：如果参数1为NULL，则更换为参数2的等效数字；如果参数1不为NULL，不进行更换。

返回值：处置惩罚后数值（要么是NULL等效后的数字，要么原数字）

给当前上面的SQL语句套入空值处置惩罚函数，如下所示。

SELECT ename,sal+IFNULL(comm,0) FROM emp
;

复制代码

【练习】

计算全部雇员的姓名和年薪，如果不领取佣金，则每个月发400的补助。

SELECT ename,(sal+IFNULL(comm,400))*12 FROM emp
;

复制代码

   5. 统计函数

常用的统计函数包括：

计数

COUNT

求和

SUM

均匀值

AVG

最大值

MAX

最小值

MIN

这五个函数的输入参数都是一些数据列（通常为数字类型，COUNT可以支持非数字类型），返回值都是统计后的数字。

【例子】查询公司的均匀工资、人数、每个月员工基本工资成本，最高工资和最低工资，工资只算基础工资。

SELECT AVG(sal),COUNT(*),SUM(sal),MAX(sal),MIN(sal) FROM emp
;

复制代码

练习：

1. 求出公司最早和最晚的雇佣日期。

SELECT MIN(hiredate),MAX(hiredate) FROM emp
;

复制代码

2. 求出公司20部门最高的工资。

SELECT MAX(sal) FROM emp
WHERE deptno=20;

复制代码

3. 求出全部经理中最高的工资。

SELECT MAX(sal) FROM emp
WHERE job='MANAGER';

复制代码

4. 求出全部贩卖的均匀基本工资。

SELECT AVG(sal) FROM emp
WHERE job='SALESMAN';

复制代码

5. 求出全部柜员的数量。

SELECT COUNT(*) FROM emp
WHERE job='CLERK';

复制代码

【例子】COUNT(*)、COUNT(列名)、COUNT(DISTINCT 列名)的区别。

COUNT(*)表示使用全部列作为一条数据有效性的依据，即一条数据只要有任何一列有数据，就到场计数。

COUNT(列名)不会对输入列出现NULL的环境计数，重复的数据分别计数。

COUNT(DISTINCT 列名)只会统计输入列且不重复的次数，重复的数据只管帐数一次。

                                    四、多表查询

之前的查询都是基于一张表进行的，本章多表查询指的是基于多张表进行查询，主要是针对FROM子句进行拓展。

更改之前的语法格式为：

1. 原理

【例子】分别统计emp表和dept表的数据量。

SELECT COUNT(*) FROM emp
;

复制代码

SELECT COUNT(*) FROM dept;

复制代码

【例子】同时对emp表与dept表统计数据量。

SELECT COUNT(*) FROM emp
,dept;

复制代码

可以看到56=14x4，两张表相乘了，这种环境被称为笛卡尔积。

每个雇员的数据在内部都会尝试跟各个部门的数据联合，但是在联合的过程中，由于雇员只能有一个部门，因此只有一个联合是有效的，其它三个联合的数据都是无效数据。

直接进行两个表的查询，可以看到这些联合的数据。

多表查询一定要消除无效的笛卡尔积，使用WHERE子句通过关联列的等值关系可以消除无效数据，在上面的例子中这个关联列就是部门号deptno，即只需要判断两个表的deptno是否相等即可。

SELECT * FROM emp
,dept WHERE emp.deptno=dept.deptno;

复制代码

也可以通过下面的写法进行改善。

-- 起别名
SELECT * FROM emp
e,dept d
WHERE e.deptno=d.deptno
;

复制代码

【例子】查询全部雇员的编号，姓名，职位，工资，部门编号，部门名称，部门位置。

分析：

第一步，确定FROM子句。

emp表：empno,job,sal,deptno

dept表：dname,loc

FROM emp
e,dept d

复制代码

第二步，如果是多表查询，确定多表之间的关系，消除笛卡尔积。

WHERE e.deptno=d.deptno

复制代码

第三步，查看全部人还是一部分人，如果是一部分人，则继承使用WHERE子句筛选。

第四步，确定SELECT子句。

SELECT empno,job,sal,deptno,dname,loc

复制代码

第五步，确定ORDER BY子句。

第六步，拼接SQL语句，拼接时注意多表的公共列。

SELECT empno,job,sal,e.deptno,dname,locFROM emp
e,dept d
WHERE e.deptno=d.deptno
;

复制代码

3. 非等式关联

多表之间建立关联并非一定需要等式关系。

【例子】查询出每个雇员的编号，姓名，职位，基本工资，雇佣日期，工资品级，效果按照基本工资从高到低排序。

分析：

第一步，确定FROM子句。

emp表：empno,ename,job,sal,hiredate

salgrade表：grade

FROM emp
e,salgrade s

复制代码

第二步，如果是多表查询，确定多表之间的关系，消除笛卡尔积。

WHERE e.sal BETWEEN s.losal AND s.hisal

复制代码

第三步，查看全部人还是一部分人，如果是一部分人，则继承使用WHERE子句筛选。

第四步，确定SELECT子句。

SELECT empno,ename,job,sal,hiredate,grade

复制代码

第五步，确定ORDER BY子句。

ORDER BY sal DESC

复制代码

第六步，拼接SQL语句，拼接时注意多表的公共列。

SELECT empno,ename,job,sal,hiredate,grade
FROM emp
e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
ORDER BY sal DESC
;

复制代码

【思考】查询每个雇员的编号、姓名、职位、雇佣日期、工资、工资品级、所在部门以及位置。

分析：

第一步，确定FROM子句。

emp表：empno,ename,job,hiredate,sal,deptno

salgrade表：grade

dept表：loc

FROM emp
e,salgrade s
,dept d

复制代码

第二步，如果是多表查询，确定多表之间的关系，消除笛卡尔积。

WHERE sal BETWEEN losal AND hisal AND e.deptno=d.deptno

复制代码

第三步，查看全部人还是一部分人，如果是一部分人，则继承使用WHERE子句筛选。

第四步，确定SELECT子句。

SELECT empno,ename,job,hiredate,sal,deptno,grade,loc

复制代码

第五步，确定ORDER BY子句。

第六步，拼接SQL语句，拼接时注意多表的公共列。

SELECT empno,ename,job,hiredate,sal,e.deptno,grade,locFROM emp
e,salgrade s
,dept dWHERE sal BETWEEN losal AND hisal AND e.deptno=d.deptno
;

复制代码

4. 连接方式

多表查询有不同的连接方式，可以分为：

但是SQLite数据库有些功能不支持：

右外连接与全外连接是不支持

4.1 内连接

之前的篇章使用的多表查询就是内连接，且使用的基于SQL89尺度的语法，除此之外常用的写法还有SQL99尺度。

【例子】查询全部雇员的姓名和部门名称。

-- SQL89SELECT ename,dname FROM emp
e,dept d
WHERE e.deptno=d.deptno
;

复制代码

-- SQL99
SELECT ename,dname FROM emp
e JOIN dept d ON e.deptno=d.deptno;

复制代码

4.2 外连接

为了更好地观察内连接与外连接的区别，需要在emp表增长一个没有部门的雇员。

INSERT INTO emp(empno,ename,job,hiredate,sal)
VALUES(6666,'JASON','MANAGER',DATETIME('now'),10000);

复制代码

emp表中就有15个雇员了。

此时再次实行4.1节中的内连接语句。

可以发现少了一个人的数据，由于JASON的deptno为NULL，所以不满足部门号相等的条件。为了让没有部门的雇员显示，需要改为外连接。

SQLite只支持基于SQL99尺度的左外连接：

-- LEFT表示让左边的表完整显示
SELECT ename,dname FROM emp
e LEFT JOIN dept d ON e.deptno=d.deptno;

复制代码

【练习】

查询全部雇员的姓名、月综合收入（sal+comm）与位置，要求显示全部雇员的信息。

SELECT ename,sal+IFNULL(comm,0),loc
FROM emp
LEFT JOIN dept ON emp.deptno=dept.deptno;

复制代码

4.3 自连接

自连接是一种特殊环境，表示多表查询的过程使用多次同一张表。

【例子】查询每个雇员的编号、姓名、职位、领导姓名。

分析：

第一步，确定FROM子句。

emp表（雇员表）：empno,ename,job

emp表（领导表）：ename

FROM emp
e JOIN emp m

复制代码

第二步，如果是多表查询，确定多表之间的关系，消除笛卡尔积。

ON e.mgr=m.empno

复制代码

第三步，查看全部人还是一部分人，如果是一部分人，则继承使用WHERE子句筛选。

第四步，确定SELECT子句。

SELECT empno,ename,job,ename

复制代码

第五步，确定ORDER BY子句。

第六步，拼接SQL语句，拼接时注意多表的公共列，自连接的每列都是公共列。

SELECT e.empno,e.ename,e.job,m.enameFROM emp
e JOIN emp m
ON e.mgr=m.empno
;

复制代码

第七步，确定是否使用外连接。

SELECT e.empno,e.ename,e.job,m.enameFROM emp
e LEFT JOIN emp m ON e.mgr=m.empno
;

复制代码

【思考】查询全部雇员的姓名、工资品级，以及他们的领导的姓名。

SELECT e.ename,grade,m.enameFROM emp
e LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisalLEFT JOIN emp m ON e.mgr=m.empno
;

复制代码

   5. 其它连接写法

   5.1 交叉连接

能够产生笛卡尔积，以下两种写法等效。

SELECT * FROM emp
CROSS JOIN dept;
SELECT * FROM emp
,dept;

复制代码

5.2 天然连接

可以主动找到多表之间的关联字段消除笛卡尔积，属于内连接。

-- 自动建立emp与dept的等值连接
SELECT * FROM emp
NATURAL JOIN dept;

复制代码

5.3 USING指定关联字段

使用USING指出多表查询的关联字段。

SELECT * FROM emp
JOIN dept USING(deptno);

复制代码

5.4 交集与并集

5.4.1 并集（不显示重复记录）

使用UNION关键字，可以将两个查询效果合并，且不显示重复记录（橙色区域只显示一遍）。

【例子】查询全部20部门或者工资高于1500的雇员信息。

分析：

查询一：查询全部20部门的雇员信息。

SELECT * FROM emp
WHERE deptno=20;

复制代码

查询二：查询全部工资高于1500的雇员信息。

SELECT * FROM emp
WHERE sal>1500;

复制代码

并集。

SELECT * FROM emp
WHERE deptno=20UNIONSELECT * FROM emp
WHERE sal>1500;

复制代码

5.4.2 并集（显示重复记录）

使用UNION ALL关键字，可以将两个查询效果合并，且显示重复记录（橙色区域显示两遍）。

以5.4.1的例题为例，改为使用UNION ALL合并。

SELECT * FROM emp
WHERE deptno=20UNION ALLSELECT * FROM emp
WHERE sal>1500;

复制代码

5.4.3 交集

使用INTERSECT可以获得两个查询效果的交集。

【例子】查询部门20且工资高于1500的雇员信息。

SELECT * FROM emp
WHERE deptno=20INTERSECTSELECT * FROM emp
WHERE sal>1500;

复制代码

交集与并集的使用需要注意让两个查询的格式相同，查询效果的列能逐一对应。

删除之前添加的数据。

DELETE FROM emp
WHERE empno=6666;

复制代码

五、分组查询

什么环境需要分组？

班级里男生一组，女生一组；公司研发部一组、市场组一组、测试部一组......

分组的条件是某些举动和特点具有同一性，好比在emp表中job和deptno等字段可以进行分组。一条数据也能独占一组，但是每组都是一条数据则毫无意义。

1. 概念

分组查询使用关键字GROUP BY实现，语法规则如下：

【例子】按照职位分组，查询每个职位的均匀工资、最高工资、最低工资和人数。

分析：

第一步，确定FROM子句。

emp：sal,sal,sal,empno

FROM emp

复制代码

第二步，如果是多表查询，确定多表之间的关系，消除笛卡尔积。

第三步，查看全部人还是一部分人，如果是一部分人，则继承使用WHERE子句筛选。

第四步，确定GROUP BY子句。

GROUP BY job

复制代码

第五步，确定SELECT子句。

SELECT job,AVG(sal),MAX(sal),MIN(sal),COUNT(empno)

复制代码

第六步，确定ORDER BY子句。

第七部，拼接SQL语句。

SELECT job,AVG(sal),MAX(sal),MIN(sal),COUNT(empno)
FROM emp
GROUP BY job
;

复制代码

【练习】

1. 统计全部部门的均匀工资和最高工资。

SELECT deptno,AVG(sal),MAX(sal)FROM emp
GROUP BY deptno;

复制代码

2. 统计10部门和20部门中各个职位的人数。

SELECT job,COUNT(empno) FROM emp
WHERE deptno=10 OR deptno=20GROUP BY job
;

复制代码

3. 统计公司全部部门的均匀工资，按照均匀工资降序排序。

SELECT deptno,AVG(sal)FROM emp
GROUP BY deptnoORDER BY AVG(sal) DESC;

复制代码

可以看到之前学习的统计函数与分组查询密切相关。

2. 多表+分组

多表查询和分组查询可以一起使用。

【例子】查询出每个部门的名称、部门人数、均匀工资。

-- 推荐使用题目中需要显示的内容作为分组字段
SELECT dname,COUNT(empno),AVG(sal)
FROM dept d LEFT JOIN emp e ON d.deptno=e.deptno
GROUP BY dname;

复制代码

-- 这种写法在SQLite中也可以，但是其它数据库中可能失败
SELECT dname,COUNT(empno),AVG(sal)
FROM dept d LEFT JOIN emp e ON d.deptno=e.deptno
GROUP BY e.deptno
;

复制代码

【思考】查询出每个部门的编号、部门名称、位置、部门人数、均匀工资。

SELECT d.deptno,dname,loc,COUNT(ename),AVG(sal)
FROM dept d LEFT JOIN emp e ON e.deptno=d.deptno
GROUP BY e.deptno
;

复制代码

3. 作业

1. 查询公司工资品级在3以上（包罗3），的雇员编号和姓名。

SELECT empno,ename

FROM emp
,salgrade

WHERE sal BETWEEN losal AND hisal AND grade>=3;

SELECT empno,ename

FROM emp
JOIN salgrade ON sal BETWEEN losal AND hisal

WHERE grade>=3;

2. 查询公司全部雇员的姓名，雇员编号，部门名称，要求部门名称小写且显示全部部门。

SELECT ename,empno,LOWER(dname)

FROM dept d

LEFT JOIN emp e ON e.deptno=d.deptno;

3. 要求算出部门30的全部人员的姓名、雇佣年限（要求四舍五入到一位小数）、部门位置。

SELECT ename, ROUND((JULIANDAY('now')-JULIANDAY(hiredate))/365,1),loc

FROM emp
e JOIN dept d ON e.deptno=d.deptno

WHERE e.deptno=30;

4. 查询出全部雇员和其领导的姓名和年薪。

SELECT e.ename,m.ename,(m.sal+IFNULL(m.comm,0))*12

FROM emp
e LEFT JOIN emp m ON e.mgr=m.empno
;

5. 统计贩卖和经理的职位的均匀月综合收入、最高月综合收入、职位人数。

SELECT job, AVG(sal+IFNULL(comm,0)),MAX(sal+IFNULL(comm,0)),COUNT(empno)

FROM emp
e WHERE job='MANAGER' OR job='SALESMAN'

GROUP BY job
;

4. HAVING子句——分组后筛选

【思考】按照职位分组，统计每个职位的均匀工资，显示均匀工资高于2000的职位。

分析：按照之前的做法，可能会写出下面的SQL语句。

SELECT job,AVG(sal)FROM emp
WHERE AVG(sal)>2000GROUP BY job
;

复制代码

实行后，直接报错。

意思是WHERE子句中不允许使用AVG函数，现实上WHERE中不允许出现任何统计函数，由于WHERE是在GROUP BY之前实行的，如果是在分组后进行筛选，应该引入HAVING子句来完成这个功能。

增长HAVING子句到实行次序中，语法格式如下。

修改上一道题的答案为：

SELECT job,AVG(sal)FROM emp
GROUP BY job
HAVING AVG(sal)>2000;

复制代码

再次分析一下WHERE和HAVING的区别。

总结：

WHERE是在分组之前对个体进行筛选，HAVING是分组之后对群体进行筛选。
WHERE可以独立存在，HAVING必须依靠于GROUP BY子句。
WHERE通常不实用统计函数，HAVING通常使用统计函数

【练习】

查询每个部门的名称、部门人数、最高工资和最低工资，要求显示最低工资高于1000的部门信息。

分析：

第一步，确定FROM子句。

FROM dept d JOIN emp e

复制代码

第二步，确定关联字段。

ON d.deptno=e.deptno

复制代码

第三步，确定WHERE子句，是否有针对个体的筛选。

第四步，确定GROUP BY子句。

GROUP BY e.deptno

复制代码

第五步，确定HAVING子句，是否有针对组的筛选。

HAVING MIN(sal)>1000

复制代码

第六步，确定SELECT子句。

SELECT dname,COUNT(empno),MAX(sal),MIN(sal)

复制代码

第七步，确定ORDER BY子句。

第八步，拼接并检查。

SELECT dname,COUNT(empno),MAX(sal),MIN(sal)FROM dept d JOIN emp e
ON d.deptno=e.deptno
GROUP BY e.deptno
HAVING MIN(sal)>1000;

复制代码

【练习】查询公司各个工资品级的人数与均匀工资，要求显示均匀工资高于1500的职位信息，且按照均匀工资降序排布。

SELECT grade,COUNT(empno),AVG(sal)
FROM salgrade JOIN emp ON sal BETWEEN losal AND hisal
GROUP BY grade
HAVING AVG(sal)>1500
ORDER BY AVG(sal) DESC;

复制代码

【思考】统计出公司领取佣金和不领取佣金的人数与均匀工资（sal）。

分析：如果按照通例分组来做，如下所示。

可以看到效果不符合题意，这种环境下需要手动分组，使用UNION子句。

-- 公司领取佣金的人数与均匀工资SELECT COUNT(empno),AVG(sal) FROM emp
WHERE comm IS NOT NULL;-- 公司不领取佣金的人数与均匀工资SELECT COUNT(empno),AVG(sal) FROM emp
WHERE comm IS NULL;

复制代码

手动合并分组效果。

SELECT '领取佣金',COUNT(empno),AVG(sal) FROM emp
WHERE comm IS NOT NULLUNIONSELECT '不领取佣金',COUNT(empno),AVG(sal) FROM emp
WHERE comm IS NULL;

复制代码

六、子查询

子查询指的是在一个查询中嵌入多个小的查询，即查询的嵌套，子查询一定要出现()

子查询在绝大多数环境下符合如下规律：

子查询返回的效果是单行单列（一个数据）：HAVING、WHERE
子查询返回的效果是单行多列（一行）：WHERE
子查询返回的效果是多行多列（一个表）：FROM

其它子句使用子查询的环境较少。

1. 在WHERE子句中使用子查询

WHERE子句的功能是控制显示的行数，支持以下几种子查询：

单行单列
多行单列
多行多列

1.1 返回单行单列

【例子】统计出全部高于公司均匀工资的雇员信息。

分析：

第一步，求出公司的均匀工资。

SELECT AVG(sal) FROM emp
;

复制代码

第二步，把第一步的效果嵌入到WHERE子句中。

SELECT * FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp
);

复制代码

【练习】统计出公司最早雇佣的雇员信息（使用子查询完成）。

SELECT * FROM emp
WHERE hiredate=(SELECT MIN(hiredate) FROM emp
);

复制代码

1.2 返回单行多列

【例子】显示出公司雇佣最早且工资最低的雇员（刚好存在）。

分析：

第一步，统计出公司最早的雇佣日期和最低的工资。

SELECT MIN(hiredate) FROM emp
;SELECT MIN(sal) FROM emp
;

复制代码

第二步，将上面的两个效果嵌套在WHERE子句中显示满足条件的信息。

SELECT * FROM emp
WHERE hiredate=(SELECT MIN(hiredate) FROM emp
)AND sal=(SELECT MIN(sal) FROM emp
);

复制代码

【练习】求出与SCOTT工资相同且职位相同的雇员信息。

SELECT * FROM emp
WHERE job=(SELECT job FROM emp
WHERE ename='SCOTT') AND sal=(SELECT sal FROM emp
WHERE ename='SCOTT')AND ename!='SCOTT';

复制代码

               1.3 返回多行单列

相当于提供了一些查询范围，通常与IN这种操作联合使用。

【例子】显示工资跟各个经理相同的雇员信息（包罗各个经理本身）。

分析：

第一步，查询出各个经理的工资。

SELECT sal FROM emp
WHERE job='MANAGER';

复制代码

第二步，将上面的SQL语句嵌入到WHERE子句中搭配IN操作使用。

SELECT * FROM emp
WHERE sal IN (SELECT sal FROM emp
WHERE job='MANAGER');

复制代码

【例子】显示工资跟各个贩卖不相同的雇员信息。

SELECT * FROM emp
WHERE NOT sal IN (SELECT sal FROM emp
WHERE job='SALESMAN');

复制代码

               2. HAVING子查询

如果有HAVING子句，就一定分组统计。

HAVING子句中出现的子查询只能是单行单列的。

【例子】查询出高于公司均匀工资的部门编号和这些部门的均匀工资。

分析：

第一步，计算出公司的均匀工资。

SELECT AVG(sal) FROM emp
;

复制代码

第二步，分组后在HAVING子句中嵌入第一步的SQL语句。

SELECT deptno,AVG(sal)FROM emp
GROUP BY deptnoHAVING AVG(sal)>(SELECT AVG(sal) FROM emp
);

复制代码

【思考】查询均匀工资最低的职位的信息（此职位的名称、人数与此职位的均匀工资）

分析：

第一步，查询每个职位的均匀工资。

SELECT AVG(sal) FROM emp
GROUP BY job
;

复制代码

第二步，查询均匀工资最低的职位的均匀工资。

SELECT MIN(asal)FROM (SELECT AVG(sal) asal FROM emp
GROUP BY job
);

复制代码

注意需要起别名asal才能在外部使用。

第三步，使用上一步的效果作为HAVING子句中子查询的条件。

SELECT job,COUNT(empno),AVG(sal)FROM emp
GROUP BY job
HAVING AVG(sal)=( SELECT MIN(asal) FROM (SELECT AVG(sal) asal FROM emp
GROUP BY job
));

复制代码

别的，此题还可以使用其他解法。

SELECT job,COUNT(empno),AVG(sal) FROM emp
GROUP BY job
HAVING sal ORDER BY AVG(sal) ASC LIMIT 1;

复制代码

               3. SELECT子查询

出现频率较低，使用需求较低。

【例子】显示全部雇员的姓名、职位、部门名称与部门位置。

分析：

正常解法就是一个平凡的多表查询。但是此处也可以使用SELECT子查询完成。

SELECT e.ename,e.job,(SELECT dname FROM dept WHERE deptno=e.deptno),(SELECT loc FROM dept WHERE deptno=e.deptno)FROM emp
e;

复制代码

这种方法无需掌握。

4. FROM子查询

FROM子查询通常返回的是多行多列，可以把子查询返回的效果看做是一张单独的表。

【例子】查询出每个部门的编号、名称、位置、部门人数、均匀工资。

实现方式一：直接使用之前的多表查询。

SELECT d.deptno,dname,loc,COUNT(empno),AVG(sal)FROM dept d LEFT JOIN emp e ON d.deptno=e.deptno
GROUP BY d.deptno;

复制代码

实现方式二：FROM子查询

第一步，单独查询出全部部门的部门号（两个表都有），部门人数（

emp表），均匀工资（emp表）

SELECT deptno,COUNT(empno),AVG(sal)FROM emp
GROUP BY deptno;

复制代码

第二步，把上一步查询的效果作为一张表与dept表进行多表查询。

SELECT d.deptno,dname,loc,temp.rs,temp.asalFROM dept d LEFT JOIN( SELECT deptno,COUNT(empno) rs,AVG(sal) asal FROM emp
GROUP BY deptno)temp ON d.deptno=temp.deptno;

复制代码

只管搞明确，实在搞不懂的必须掌握实现方式一。

现实的开辟中，有可能会遇到巨大的数据量，不妨把当前emp与dept表的数据量扩大100倍，即emp表中有1400条数据，dept表中有400条数据。

实现方式一的数据量

直接使用多表查询，会在背景天生笛卡尔积，其数量为1400*400=560000

实现方式二的数据量

第一步（内嵌的子查询）：操作数据量为1400，返回的数据量为最多为400

第二步（子查询与dept表多表查询）：400*400=160000

总数据量 = 160000+1400 = 161400

实现方式二的理论性能是实现方式一的1/3，结论是：

小的数据量使用实现方式一，大的数据量使用实现方式二。

5. 作业

1. 列出至少有四个员工的全部部门编号、部门名称、部门人数

SELECT d.deptno,dname,COUNT(empno)

FROM emp
e JOIN dept d ON e.deptno=d.deptno

GROUP BY d.deptno

HAVING COUNT(empno)>=4;

2. 列出雇佣日期早于其领导的全部员工的编号，姓名，部门名称，领导名称。

SELECT e.empno,e.ename,d.dname,p.ename

FROM emp
e LEFT JOIN emp p ON e.mgr=p.empno JOIN dept d ON e.deptno=d.deptno

WHERE p.hiredate>e.hiredate;

3. 列出各个部门的CLERK（柜员）的最低薪金。

SELECT deptno,MIN(sal)

FROM emp

WHERE job='CLERK'

GROUP BY deptno;

4. 列出工资比SMITH多的全部员工

SELECT * FROM emp
WHERE sal>(

SELECT sal FROM emp
WHERE ename='SMITH'

);

5. 列出在部门“SALES”工作的员工姓名和工资

SELECT ename,sal

FROM emp
e JOIN dept d ON e.deptno=d.deptno

WHERE d.dname="SALES";

6. 求出部门名称中带‘S’字符的部门,其员工的工资合计和部门的人数

SELECT dname,SUM (sal),COUNT(e.deptno)

FROM emp
e JOIN dept d ON e.deptno=d.deptno

GROUP BY dname

HAVING dname LIKE '%S%';

                                                                                                               七、数据更新操作

针对DML（数据操作语言）分为两类：查询（DQL）和更新，更新分为三种操作：

增长 INSERT
修改 UPDATE
删除 DELETE

数据更新操作会直接更改数据库中的数值，为了防止emp表的原始数据被粉碎，在学本章知识点之前，先复制一份emp表，新的emp表叫myemp，实行如下操作：

CREATE TABLE myemp AS SELECT * FROM emp
;

复制代码

myemp只是数据与emp完全相同，但是一些束缚关系不相同。

本章后续的操作都基于myemp表。如果不鉴戒粉碎了emp表，可以去群文件中重新下载scott.db文件。

1. 插入数据

插入数据的语法格式如下所示：

INSERT INTO 表名[(列1,列2,...)] VALUES(值1,值2,...)

复制代码

针对目前已经打仗过的数据类型：

字符串：要求使用英文半角的单引号或双引号包括内容
数字：直接写数字
日期和时间
- 如果是当前日期时间，可以使用DATETIME('now')等写法，如果是本地日期和时间别忘了第二个参数增长'localtime'
- 也可以使用固定格式的字符串，例如'1992-01-06 02:33:33'

【例子】在myemp表中增长一条数据。

实现方式一：使用完备的格式

INSERT INTO myemp(empno,ename,job,mgr,hiredate,sal,deptno)
VALUES(8888,'张三','厨师',7839,DATETIME('now','localtime'),10000,40);

复制代码

实现方式二：使用简易格式

INSERT INTO myemp VALUES(1000,'罗翔','律师',8888,'2000-03-30 09:44:34',1,NULL,10);

复制代码

第二种方式需要注意，如果列数据为NULL，需要手动写上插入的数据为NULL。

推荐使用第一种方式。

【练习】

分别使用两种方式插入自己和同桌的信息。

INSERT INTO myemp(empno,ename,job,mgr,hiredate,sal,deptno)
VALUES(6666,'IKUN','唱跳rap篮球',7839,DATETIME('now','localtime'),135,11);
INSERT INTO myemp VALUES(230,'小黑子','鸡你太美',6666,'2022-09-30 09:44:34',123,NULL,10);

复制代码

            2. 修改数据

修改数据的语法如下：

UPDATE 表名 SET 列名1=值1,列名2=值2,...[WHERE 更新条件(s)]

复制代码

需要注意的是，如果不写WHERE子句表示更新全部数据。

【例子】将‘SMITH’的工资修改为5000，佣金修改为2000

UPDATE myemp SET sal=5000,comm=2000 WHERE ename='SMITH';

复制代码

【例子】将全部贩卖的工资修改为2000

UPDATE myemp SET sal=2000 WHERE job='SALESMAN';

复制代码

【练习】

1. 将公司最早雇佣的员工的工资增长20%

UPDATE myemp SET sal=1.2*sal ORDER BY hiredate ASC LIMIT 1;

复制代码

UPDATE myemp SET sal=1.2*sal WHERE hiredate=(SELECT MIN(hiredate) FROM emp
);

复制代码

2. 将公司全部雇员的雇佣日期改为现在。

UPDATE myemp SET hiredate=DATETIME('now');

复制代码

3. 删除数据

删除数据的语法格式如下：

DELETE FROM 表名 [WHERE 删除条件(s)]

复制代码

需要注意的是，如果不写WHERE子句，表示删除全部数据！

【例子】删除全部30部门的雇员。

DELETE FROM myemp WHERE deptno=30;

复制代码

第一次实行，显示

；再次实行，显示，由于这些数据已经被删除了。

【练习】

1. 删除公司工资最高的雇员。

DELETE FROM myemp WHERE sal=(SELECT MAX(sal) FROM myemp);

复制代码

2. 删除没有领导的雇员。

DELETE FROM myemp WHERE mgr IS NULL;

复制代码

3. 删除全部雇员。

DELETE FROM myemp;

复制代码

在现实的开辟中，为了保护数据，随时可以恢复数据，通常实行逻辑删除，而不实行物理删除。

物理删除就是使用DELETE语句进行删除；

逻辑删除指的是，在筹划表时可以增长一列，用这一列的数据表示当前的数据的可用性（例如1为数据可用，0为数据不可用）。那么在查询的时间多增长一个判断为1的条件，表示查询全部数据。删除时，可以把这一列的数据置为0，使用UPDATE操作来进行逻辑删除。

八、表的创建与管理

之前的全部学习都是以DML（数据操作语言）为主，如果想对表本身进行维护，则需要使用DDL（数据界说语言），DDL通常在项目的初期使用。

1. 数据类型

大多数数据库使用的都是静态数据类型，即值的类型由它的容器（存储值的列）决定。但是SQLite使用的是动态数据类型，值本身决定数据类型，不与容器相关。

SQLite常用的存储类有：

存储类又可以动态地细分为不同的亲和类型，仅做相识。

2. 创建表

创建一张表的语法格式如下：

CREATE TABLE 表名(
列名1 类型 [DEFAULT],
列名2 类型 [DEFAULT],
...,
列名n 类型 [DEFAULT]
);

复制代码

如果创建表的语句较短，也可以写成一行：

CREATE TABLE 表名(列名1 类型 [DEFAULT],列名2 类型 [DEFAULT],...,列名n 类型 [DEFAULT]);

复制代码

【例子】创建一张表。

CREATE TABLE hqyj23032(
id INTEGER,
name TEXT DEFAULT '佚名',
birth TEXT DEFAULT CURRENT_DATE,
sal REAL DEFAULT 0
);

复制代码

可以对这个表进行正常的增编削查。

可以使用下面的语句删除表。

DROP TABLE 表名;

复制代码

               3. 束缚

束缚是在表的数据列中增长欺压实行的规则，例如上一节中的默认值，如果一列没有默认值束缚且不手动添加数值，则会使用NULL表示这个数据。

除了默认值以外，还有其它的束缚，这些束缚可以保障数据表的准确性和可靠性。

一共有如下束缚类型：

3.1 非空束缚

【例子】创建一个表，包罗非空束缚的列，并测试。

CREATE TABLE member(id INTEGER,name TEXT NOT NULL);

复制代码

尝试插入数据，使name列的数值为NULL。

INSERT INTO member VALUES(1,NULL);
INSERT INTO member(id) VALUES(2);

复制代码

插入失败。

3.2 唯一束缚

【例子】创建一个表，包罗唯一束缚的列，并测试。

CREATE TABLE member(id INTEGER,name TEXT UNIQUE);

复制代码

-- 可以执行
INSERT INTO member VALUES(1,'张三');
-- 不可以执行
INSERT INTO member VALUES(2,'张三');

复制代码

当重复时显示

3.3 主键束缚

主键束缚 ≈ 非空束缚+唯一束缚

作为每个记录的唯一标识，通常把第一列（序号）作为主键。

与其它数据库不同的是，为了非空特性，当强行插入NULL时，会主动添补序列。

【例子】创建一个表，包罗主键束缚的列，并测试。

-- 先删除
DROP TABLE member;
-- 再建表
CREATE TABLE member(id INTEGER PRIMARY KEY,name TEXT);
-- 正确
INSERT INTO member VALUES(1,'张三');
-- 错误：id列重复了
INSERT INTO member VALUES(1,'李四');

复制代码

上面第9行代码会报错。

-- 自动填充序列
INSERT INTO member VALUES(NULL,'王五');

复制代码

主键束缚还可以增长AUTOINCREMENT关键字，设置为自增长。

-- 先删除
DROP TABLE member;
-- 再建表，id列自增长
CREATE TABLE member(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT);
-- 测试
INSERT INTO member(name) VALUES('张三');
INSERT INTO member(name) VALUES('李四');
INSERT INTO member(name) VALUES('王五');

复制代码

3.4 条件检查束缚

【例子】创建一个表，包罗条件检查束缚的列，并测试。

-- 先删除
DROP TABLE member;
-- 再建表
CREATE TABLE member(id INTEGER,sal INTEGER CHECK(sal>1000));
-- 测试：成功
INSERT INTO member VALUES(1,1001);
-- 测试：失败
INSERT INTO member VALUES(2,999);

复制代码

其它内容可以自学：

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

		自动登录	找回密码
密码			立即注册

数据库的基本操作

本帖子中包含更多资源

0 个回复

快速回复

楼主热帖

标签云