数据库入门-CSDN博客

本文链接：https://blog.csdn.net/zzzzzxm__/article/details/148765097

一、数据库简介

1、数据库

SQLiteSpy使用不同的颜色表示数据的类型：

白色数据表示TEXT（字符串）类型；

绿色数据表示INTEGER（整数）类型；

红色数据表示NULL；

紫色数据表示REAL（小数）类型。

2、学习数据

2.1emp 雇员表

2.2 dept 部门表

2.3 salgrade 工资等级表

2.4 bonus 奖金表

空表

二、查询操作

1、简单查询（列查询）

a.其中[ ]表示可选填内容

b.DISTINCT表示去掉重复行的数据

c.*表示查询所有的列，如果不想查询所有列，则编写具体的列名称。

eg：查询雇员表所有列

SELECT *
FROM emp;

eg：查询所有雇员的编号（empno）、姓名（ename）、职位（job）、基本工资（sal）

SELECT empno,ename,job,sal
FROM emp;

练习

1、查询所有雇员的编号、姓名和基本年薪（基本月薪x12）

SELECT empno,ename,sal*12
FROM emp;

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

SELECT empno,ename,job,sal*15+200*12+200*12+300*4
FROM emp;

3、查询公司的职位有哪些？

SELECT job
FROM emp;

查询结果出现重复项，可以通过DISTINCT关键字进行结果去重

SELECT DISTINCT job
FROM emp;

只有每一列的数据都重复，结果才算做重复。

即查询的列数中某两行数据完全相同才算这两行重复。

2、限定查询

限定查询在简单查询的基础上可以限制行数，语法格式如下：

可以通过WHERE子句筛选行数，支持以下六种运算：

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

2.1关系运算

关系运算是最简单的运算符号，包含：>、<、>=、<=、<>、!=、==、=

eg：查询基本工资高于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...

计算条件是否落在两个范围之间，闭区间（包含两端）。

eg：查询所有在1981年雇佣的雇员信息。

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

练习：查询工资范围在1200-1500之间的雇员信息。

SELECT *
FROM emp
WHERE sal BETWEEN 1200 AND 1500;

2.3 基数范围运算

使用关键字IN，可以筛选出多选一的条件。需要注意的是IN不能NULL一起使用。

例如：错误用法

SELECT * 
FROM table_name 
WHERE column_name IN ('A', NULL);

这种写法无法筛选出 column_name 为 NULL 的记录，正确判断 NULL 的方法是使用 IS NULL：

SELECT * 
FROM table_name
WHERE column_name IS NULL;

eg：查询出编号为7369和7566和7839和8899（不存在）的雇员信息

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

eg：查询出编号不是7369、7566、7839、8899的雇员信息

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

eg：查询出部门10和20的雇员姓名。

SELECT ename
FROM emp
WHERE deptno IN(10,20);

2.4 模糊查询

可以指定文字的一部分内容进行查询，使用LIKE关键字配合两个通配符：

% 0个，1个或n个字符 _（下划线）一个字符

eg：查询姓名长度为5的雇员信息

SELECT *
FROM emp
WHERE ename LIKE '_____';

eg：查询第二个字母是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表示空值，空值表示没有数据，不同于0。

在SQLiteSpy中NULL值的颜色是粉红色：

必须使用专用的关键字进行NULL值判断：

为空：IS NULL
不为空：IS NOT NULL、NOT IS NULL

eg：查询没有领导的雇员信息。

SELECT *
FROM emp
WHERE mgr IS NULL;

eg：查询有领导的雇员信息。

SELECT *
FROM emp
WHERE mgr IS NOT NULL;

或

SELECT *
FROM emp
WHERE NOT mgr IS NULL;

练习

1. 查询领取佣金的雇员信息。

SELECT *
FROM emp
WHERE comm IS NOT NULL;

2. 查询不领取佣金的雇员部门号。

SELECT deptno，
FROM emp
WHERE comm IS NULL;

2.6 逻辑运算

与 AND：所有的条件都满足，结果才满足

或 OR：所有的条件满足一个，结果就满足

非 NOT：取反

eg：查询所有基本工资高于1300并且职位是销售的雇员信息。

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

eg：查询所有基本工资高于1300和职位是销售的雇员信息。

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

2—作业

查询出工资范围不在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 sal>3000 OR job='CLERK';

4.查询出所有职位不是柜员的信息

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

5.选择部门30的所有员工

SELECT *
FROM emp
WHERE deptno=30;

6.列出所有柜员（CLERK）的姓名、编号和部门编号。

SELECT ename,empno,deptno
FROM emp
WHERE job='CLERK';

7.找出部门30中所有经理和部门20中所有柜员的详细资料。

SELECT *
FROM emp
WHERE deptno=30 AND job='MANAGER' OR deptno=20 AND job='CLERK';

8.找出部门30中所有经理、部门20中所有柜员以及既不是经理又不是柜员并且薪金大于等于2000的员工的详细资料

SELECT *
FROM emp
WHERE deptno=30 AND job='MANAGER' OR deptno=20 AND job='CLERK' OR job!='MANAGER' AND job!='CLERK' AND SAL>=2000;

9.收取佣金的工作有哪些

SELECT DISTINCT job
FROM emp
WHERE comm IS NOT NULL;

10.找出不收取佣金或佣金低于100的员工。

SELECT ename
FROM emp
WHERE comm IS NULL OR comm<100;

11.显示不带有A的员工的姓名

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

3. 查询排序

可以使用ORDER BY子句控制查询结果的顺序，语法格式如下：

两种排序的方式：

ASC：升序（默认）
DESC：降序
- 技巧：如果题目要求过多，可先不增加排序，将其余工作搞定之后再进行排序

eg：查询所有雇员的信息，要求按照基本工资从高到低排序。

SELECT *
FROM emp
ORDER BY sal DESC；

eg：查询所有雇员的信息，要求按照基本工资从高到低排序，如果工资相同，就按照雇佣日期从早到晚排序。

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

eg：按照年薪排序，年薪越高的越靠前。

SELECT ename,sal*12 income   //可以使用别名
FROM emp
ORDER BY income DESC；        //别名可将原本的名称替代

SELECT ename,sal*12
FROM emp
ORDER BY sal*12 DESC；   //直接使用sal*12无法正常运行

练习

1. 查询所有柜员的信息，要求按照工资从高到低排序。

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

2. 查询所有不是经理和总裁的雇员信息，要求按照雇佣日期从早到晚排序。

SELECT *
FROM emp
WHERE job!='MANAGER' AND job!='PRESIDENT'
ORDER BY hiredate ASC;

或

SELECT *
FROM emp
WHERE job NOT IN ('MANAGER','PRESIDENT')
ORDER BY hiredate ASC;

3. 查询所有名字中包含A的雇员信息，要求按照基本工资从高到低排序，如果基本工资相同，则按照名称的名字的字母顺序，从小到大排序。

SELECT *
FROM emp
WHERE ename LIKE '%A%'
ORDER BY sal DESC,ename ASC;

易错：包含某字母用LIKE衔接，而不是=

三、函数

1、函数的概念

函数（function）是编程领域专用的一个术语，数据库中的函数是一些程序员已经写好的代码逻辑，不同的函数可以实现不同的功能，我们在使用时可以直接调用。

函数三要素：

名称：描述了函数的功能
输入参数：处理的原始数据
返回值：处理后的数据

2、字符串函数

2.1大小写转换函数

函数名称：UPPER（大写）、LOWER（小写）

输入参数：字符串数据，例如姓名、职位等

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

eg：查询所有部门的名称，要求显示为小写

SELECT LOWER(dname)
FROM dept;

练习：查询雇员的姓名和职务，要求所有字母小写

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

2.2 获取长度

函数名称：LENGTH(参数)

输入参数：字符串

返回值：字符串的长度

eg：查询所有姓名长度为5的雇员信息。

SELECT *
FROM emp
WHERE LENGTH(ename)=5;

eg：查询所有部门名称长度在10个字符以内（包含10）的部门名称。

SELECT dname
FROM dept
WHERE LENGTH(dname)<=10;

eg：查询所有雇员的姓名和职位，要求只显示职位信息大于5个长度的人。

SELECT ename,job
FROM emp
WHERE LENGTH(job)>5;

2.3 截取

函数名称：SUBSTR(参数1,参数2,参数3)

输入参数1：字符串

输入参数2：起始点，数据库从1开始

输入参数3：[可选]，长度，如果不写参数3则截取到最后。

返回值：把参数1的字符串从参数2的位置开始，截取参数3个字符串。

eg：查询所有销售的姓名和职位，要求职位简写为SALES

SELECT ename,SUBSTR(job,1,5)
FROM emp
WHERE job='SALESMAN';

练习：查询所有雇员的姓名，要求显示最后的三个字母。

SELECT SUBSTR(ename,LENGTH(ename)-2)  //函数嵌套，没有写参数3
FROM emp;

SELECT SUBSTR(ename,-3)  //支持负数，-3表示倒数第三个位置
FROM emp;

3. 数字函数

2.1 四舍五入（取整函数）

函数名称：ROUND(参数1,参数2)

输入参数1：要处理的数字

输出参数2：[可选]，保留小数位，默认为整数位

返回值：四舍五入之后的数字

eg：计算所有雇员的日薪（一个月工作时间21.75天），要求四舍五入整数。

SELECT ename,ROUND(sal/21.75)  //默认整数位（左图）
FROM emp;

SELECT ename,ROUND(sal/21.75,2)  //保留两位数（右图）
FROM emp;

2.2 绝对值

函数名称：ABS(参数)

输入参数：要处理的数字返回值：绝对值之后的数字

eg：查询公司所有人与BLAKE的收入差距（BLAKE的收入是2850）。

SELECT ename,ABS(sal-2850)
FROM emp;

4. 日期函数

4.1 提取数据

函数名称：DATE(参数)、TIME(参数)、DATETIME(参数)

输入参数：符合日期和时间格式的字符串

返回值：只包含日期的字符串、只包含时间的字符串、同时包含时间和日期的字符串

eg：查询所有人的雇佣日期，要求不显示时间。

SELECT ename,DATE(hiredate)
FROM emp;

练习：查询所有人的雇佣时间，要求不显示日期。

SELECT ename,TIME(hiredate)
FROM emp;

4.2 获取当前日期和时间数据

函数名称：DATE(参数1,参数2)

TIME(参数1,参数2)

DATETIME(参数1,参数2)

输入参数1：'now'

输入参数2：[可选]，'localtime'表示本地（东八区）数据，默认为格林威治时间eg：如果想单纯使用一个函数，不查询任何表，可以只写SELECT语句。

-- 展示东八区日期和时间
SELECT DATETIME('now','localtime');
-- 展示格林威治时间
SELECT TIME('now');

4.3 儒略日

函数名称：JULIANDAY(参数)

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

返回值：返回输入日期与儒略日的天数差

eg：今天与儒略日的差

SELECT JULIANDAY('now');

练习：查询每个雇员的名称和雇佣的年数（工龄）。

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

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

4.4 格式化

函数名称：STRFTIME(参数1,参数2)

输入参数1：数据格式，如下所示 记得加 ' '

输入参数2：数据来源，一个日期或时间，例如hiredate、4.1和4.2的返回值等。

返回值：提取后的固定格式数据，返回的类型是TEXT（字符串）

eg：查询所有在六月雇佣的雇员信息。

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

练习

1. 查询所有在1981年雇佣的雇员信息。

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

2. 查询所有在23号雇佣的雇员信息。

SELECT *
FROM emp
WHERE STRFTIME('%d',hiredate)='23';

3. 查询所有在上半年雇佣的雇员信息。

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

4. 查询所有在周三雇佣的雇员信息。

SELECT *
FROM emp
WHERE STRFTIME('%w',hiredate)='3';

5、空值函数

NULL表示一种虚无的状态，任何数字与NULL进行计算都会把结果同化为NULL。因此需要使用专用函数先处理NULL值，再数学计算。

函数名称：IFNULL(参数1,参数2)

输入参数1：可能为NULL的数据，例如comm

输入参数2：如果参数1为NULL，则替换为一个数字，通常为0

返回值：如果参数1为NULL，则返回值为参数2；如果参数1不为NULL，返回原数据。

eg：查询所有雇员的姓名和月到手收入（包含薪金和佣金）

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

练习：计算所有雇员的年薪，年薪包含sal和comm，按照年薪排序（收入高的在前面），只显示部门30的雇员信息。

SELECT *,(sal+IFNULL(comm,0))*12 income
FROM emp
WHERE deptno=30
ORDER BY income DESC;

5—作业：

1.显示姓名字段的任何位置包含E的所有员工的详细资料，显示的结果按照基本工资从高到低排序，如果基本工资相同则按照雇佣时间从早到晚排序，如果雇佣的日期相同，则按照职位的字母顺序排序。

SELECT *
FROM emp
WHERE ename LIKE '%E%'
ORDER BY sal DESC,hiredate ASC,job;

2.显示非经理员工的姓名、编号、部门编号和总收入INCOME（薪金+佣金），按照总收入从高到低排序；如果总收入相同，按照薪金从高到低排序；如果薪金相同，按照部门号从小到大排序。

SELECT ename,empno,deptno,sal+IFNULL(comm,0) INCOME
FROM emp
WHERE job!='MANAGER'
ORDER BY INCOME DESC,sal DESC,deptno ASC;

6、统计函数（组函数）

与Excel相同的是，数据库也是包含五种常用的统计函数：

最大值：MAX(参数)
最小值：MIN(参数)
平均值：AVG(参数)
求和：SUM(参数)
计数：COUNT(参数)

这些函数都需要传入对应的统计或计算数据，返回值五种统计和计算数据。

eg：统计公司的雇员人数、支付的总工资（只算sal）、平均工资、最高工资和最低工资。

//统计人数通常以第一列为标准
SELECT COUNT(empno),SUM(sal),AVG(sal),MAX(sal),MIN(sal)
FROM emp;

练习—可以分多次查询

1. 分别统计10部门、20部门和30部门的雇员人数，支付的总工资、平均工资、最高工资和最低工资。

SELECT deptno,COUNT(empno),SUM(sal),AVG(sal),MAX(sal),MIN(sal)
FROM emp
WHERE deptno=10;

SELECT deptno,COUNT(empno),SUM(sal),AVG(sal),MAX(sal),MIN(sal)
FROM emp
WHERE deptno=20;

SELECT deptno,COUNT(empno),SUM(sal),AVG(sal),MAX(sal),MIN(sal)
FROM emp
WHERE deptno=30;

2. 分别统计五种岗位的平均工资、最高工资和最低工资。

SELECT job,AVG(sal),MAX(sal),MIN(sal)
FROM emp
WHERE job='CLERK';

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

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

SELECT job,AVG(sal),MAX(sal),MIN(sal)
FROM emp
WHERE job='ANALYST';

SELECT job,AVG(sal),MAX(sal),MIN(sal)
FROM emp
WHERE job='PRESIDENT';

3. 查询公司高于平均工资的雇员信息。

错误写法：

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

正确写法1：

SELECT AVG(sal)
FROM emp;              //先计算平均值,计算结果为2073.214

SELECT *
FROM emp
WHERE sal>2073.214    //将结果带入查询

正确写法2：

SELECT *
FROM emp
WHERE sal>(SELECT AVG(sal)   FROM emp);  //将两部分合并

练习

1. 查询公司支付的总年薪和平均年薪。（算入comm）

SELECT 12*SUM(sal+IFNULL(comm,0)),12*AVG(sal+IFNULL(comm,0))
FROM emp;

2. 查询公司最早和最晚雇佣的日期。

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

3. 查询公司高于20部门平均工资的雇员信息。

SELECT *
FROM emp
WHERE sal>(SELECT AVG(sal)  FROM emp   WHERE deptno=20);

4. 查询10部门中高于本部门平均工资的雇员信息。

SELECT *
FROM emp
WHERE sal>(SELECT AVG(sal)  FROM emp   WHERE deptno=10) AND deptno=10;

四、分组

1、分组查询

分组需要具有相同的特征，例如emp表中的job和deptno非常适合分组。

分组查询使用GROUP BY子句，语法执行顺序如下：

eg：按照职位分组，显示每个职位的平均工资、最高工资、最低工资和人数

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

练习

1、按部门分组，统计出每个部门的平均工资、最高工资、最低工资和人数，要求按照平均工资从高到低排序。

SELECT deptno,AVG(sal),MAX(sal),MIN(sal),COUNT(empno)
FROM emp
GROUP BY deptno
ORDER BY AVG(sal) DESC;

2、按照职位分组，统计出三个高端职位的平均工资、人数和最早雇佣日期，要求按照雇佣日期从早到晚排序。

SELECT job,AVG(sal),COUNT(empno),MIN(hiredate)
FROM emp
WHERE job='MANAGER' OR job='PRESIDENT' OR job='ANALYST'
          //注意使用OR而非AND
GROUP BY job
ORDER BY hiredate ASC;

3、按照部门分组，统计出10和20部门的平均工资、人数和最早雇佣日期，要求按照人数从多到少排序。

方法1：

SELECT deptno,AVG(sal),COUNT(empno),MIN(hiredate)
FROM emp
WHERE deptno=10 OR deptno=20
GROUP BY deptno
ORDER BY COUNT(empno) DESC;

方法2：使用替换名+使用关键字IN

SELECT deptno,AVG(sal),COUNT(empno) num,MIN(hiredate)
FROM emp
WHERE deptno IN (10,20)
GROUP BY deptno
ORDER BY num DESC;

2、分组筛选

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

分析：按照目前学过的内容，会写出下面的语句

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

运行后会出现如图所示情况

原因：SQL规定不允许在WHERE子句中使用统计函数（AVG、MAX、MIN、COUNT、SUM）

此时需要引入HAVING子句解决上面的问题。

WHERE子句无法完成任务的情况：

必须分组后筛选的情况，因为WHERE是对个体筛选，HAVING分组后的群体筛选。
如果筛选条件中包含：AVG、SUM、MAX、COUNT、MIN，因为WHERE子句不支持五种函数

加入HAVING子句后，最终的查询格式：

eg：按照职位分组，统计每个职位的平均工资，要求显示平均工资高于2000的职位。

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

练习

1. 统计每个部门的平均工资，要求显示平均工资低于2000的部门。

SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal)<2000

2. 统计每个职位的最高工资，要求显示职位人数大于3的职位。

SELECT job,MAX(sal)
FROM emp
GROUP BY job
HAVING COUNT(empno)>3;

2—作业

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

SELECT deptno,COUNT(empno)
FROM emp
GROUP BY deptno
HAVING COUNT(empno)>=4;

2. 列出雇佣日期早于1981年6月12日的雇员编号、姓名

SELECT empno,ename,hiredate
FROM emp
WHERE hiredate<'1981-6-12';

3. 列出最低工资大于1500的职位名称和此职位的人数。

SELECT job,COUNT(empno)
FROM emp
GROUP BY job
HAVING MIN(sal)>1500;

4. 列出各个部门中CLERK的最低工资。

SELECT deptno,MIN(sal)
FROM emp
WHERE job='CLERK'
GROUP BY deptno;

5. 求出部门名称中包含S的部门名称和位置。

SELECT DNAME,LOC
FROM dept
WHERE DNAME LIKE '%S%';

3、手动分组

GROUP BY子句无法完成想要的分组时可以手动分组。

在数据库中使用交集和并集前提是两个查询返回值的列是相同的顺序和数量。

3.1并集

eg：查询部门20或者基本工资大于1500的雇员信息

//一般写法
SELECT *
FROM emp
WHERE deptno=20 OR sal>1500;

利用并集求解：

步骤一：查询部门20的人

SELECT * 
FROM emp 
WHERE deptno=20;

步骤二：查询基本工资高于1500的人

SELECT * 
FROM emp 
WHERE sal>1500;

步骤三：整合

①两条件都符合的雇员只显示一次，即交集算作一份用UNION

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

②两条件都符合的雇员显示两次，满足条件1显示一次，满足条件2再显示一次

即交集算作两份用UNION ALL

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

3.2交集

交集使用 INTERSECT

eg：查询部门20且基本工资大于1500的雇员信息。

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

练习：统计出公司领取佣金和不领取佣金的人数与平均工资

方法一：

SELECT COUNT(empno),AVG(sal)
FROM emp
WHERE comm!=0         //领取佣金的人
UNION
SELECT COUNT(empno),AVG(sal)
FROM emp
WHERE comm=0;        //不领取佣金的人

方法二：

SELECT COUNT(empno),AVG(sal) 
FROM emp WHERE comm IS NOT NULL     //领取佣金的人
UNION
SELECT COUNT(empno),AVG(sal) 
FROM emp WHERE comm IS NULL;        //不领取佣金的人

五、增删改（表中数据）

首先复制一张新emp表以防数据被破坏，新表叫做myemp

CREATE TABLE myemp AS SELECT * FROM emp;

-- CREATE TABLE myemp 表示创建的新表名称

-- AS 表示直接去Copy，这个拷贝只包含数据和类型，不包含约束

-- SELECT * FROM emp 表示把查询的结果信息拷贝到新表中

1、插入操作

语法格式如下：

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

字符串：TEXT

使用' '或" "去包裹内容

" "包裹的是列名或表名（当标识符包含特殊字符、空格或与保留关键字冲突时）

整数：INTEGER

直接写数字

浮点数：REAL

直接写数字

时间与日期：固定格式的TEXT
- 如果是当前日期或时间，参数需要有'now'
- 如果是指定的日期或时间，则使用固定格式的字符串

eg：向myemp表中插入数据

使用完整格式插入

--没有操作的列通常使用默认值，默认值通常为NULL
--用到那列写哪列数据，没写的默认为NULL
INSERT INTO myemp(empno,ename,job,hiredate,sal,deptno)
VALUES(6666,'张三','MANAGER',DATETIME('now'),10000,10);

使用简便格式插入

--需要把所有的列数据都按照顺序写上，如果不需要数据也要手动写NULL
INSERT INTO myemp 
VALUES(6666,'张三','MANAGER',NULL,DATETIME('now'),10000,NULL,10);

2、更新操作

语法格式如下：

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

更新操作中的WHERE子句与查询操作的WHERE子句完全相同。需要注意的是，如果不写WHERE子句表示更新所有数据。

eg：把SMITH的工资修改为5000，佣金修改为2000

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

UPDATE myemp SET sal=5000,comm=2000;  //未写WHERE子句

练习

1. 将所有销售人员的工资改为2000.

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

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

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

将公司工资最低的雇员工资改为公司的平均工资。

UPDATE myemp SET sal=(SELECT AVG(sal) FROM myemp) 
WHERE sal=(SELECT MIN(sal) FROM myemp);

错误做法：

UPDATE myemp SET sal=AVG(sal) //必须通过子查询获取平均值
                                    //不能直接在SET后写AVG(列名)
WHERE sal=(SELECT MIN(SAL) FROM myemp);

4. 把所有雇员的雇佣日期改为今天。

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

3、删除操作

语法格式如下：

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

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

eg：删除所有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;

如果需要数据恢复功能，可以把上面的物理删除改为逻辑删除：

为每个表增加一列，这一列表示数据是否删除。
例如1表示未删除，0表示已删除。
删除操作就变成了把一列的值从1改为0。
恢复操作就变成了把这一列的值从0改为1。
所有的查询和更新操作都增加一个判断此列为1的条件
所有的插入操作，都默认此列的值为1

六、DDL（数据定义语言）

DDL（数据定义语言），数据定义语言主要包含对数据库本身操作，例如：

创建—CREATE

例如创建一张表

删除—DROP

例如删除一张表

修改—ALTER

例如给某表增加一列

1、创建表

语法格式如下：

CREATE TABLE 表名称(

列名类型 [DEFAULT] 默认值,

......

列名类型 [DEFAULT] 默认值);

当前的时间和日期的默认值可以使用CURRENT_TIME（当前时间）、CURRENT_DATE（当前日期）和CURRENT_TIMESTAMP（当前日期和时间）

eg：创建一张新表并增加几名成员

//创建一张新表
CREATE TABLE member(
	id		INTEGER,
	name	TEXT		DEFAULT		'无名',
	birth	TEXT		DEFAULT		CURRENT_DATE,
	sex		TEXT 		DEFAULT		'男',
	age		TEXT		DEFAULT		18,
	sal		REAL		
);
//增加成员
INSERT INTO member(id,name,birth,sex,age,sal)
VALUES(001,'赵一','2001-1-1','女',24,12000);

INSERT INTO member(id,name,sex,sal)
VALUES(002,'钱二','女',5300);

INSERT INTO member(id,name,birth,age,sal)
VALUES(003,'孙三','2003-11-8',21,6000);

INSERT INTO member(name,birth,sex,age,sal)
VALUES('李四','2002-6-1','男',23,1200);

INSERT INTO member(id,birth,sex,age)
VALUES(005,'2001-1-1','女',24);

INSERT INTO member(id,name,birth,sex,age,sal)
VALUES(006,'吴六','2006-1-10','男',19,9000);

2、重命名

语法格式如下：

ALTER TABLE 旧表名 RENAME TO 新表名;

eg：把member表改为user表

ALTER TABLE member RENAME TO user;

3、增加列

SQLite不支持修改和删除列，只能重新创建表来间接修改和删除列。但是支持增加列，语法格式如下：

ALTER TABLE 表名 ADD COLUMN 列名数据类型 DEFAULT [默认值];

eg：给myemp增加性别列。

ALTER TABLE myemp ADD COLUMN sex TEXT DEFAULT '女';

4、删除表

语法格式如下：

DROP TABLE 表名;

eg：删除表myemp

DROP TABLE myemp;

5、约束

是表中的列上的强制规则，可以限制表中的列数据，确保准确性和可靠性。

约束类型：

PRIMARY KEY 非空且唯一，通常是给第一列设置的序号，会自动增长。
DEFAULT 设置默认值
UNIQUE 确保某列中的值都不同
NOT NULL 确保某些不能为空值
CHECK 设置一个数据录入的条件

eg：创建一张表，包含上面五种约束，并测试

--创建表
CREATE TABLE member(
	id		INTEGER	PRIMARY KEY,
	name	TEXT		UNIQUE,
	birth	TEXT		DEFAULT		CURRENT_DATE,
	sex		TEXT 		DEFAULT		'男'			NOT NULL,
	age		TEXT		DEFAULT		18,
	sal		REAL	 	CHECK(sal>=2200)
);

--测试id
INSERT INTO member(name,sal)
VALUES('赵一',12000);
INSERT INTO member(name,sal)
VALUES('钱二',3000);

--测试name
INSERT INTO member(name,sal)
VALUES('孙三',12000);
INSERT INTO member(name,sal)
VALUES('孙三',3000);

--测试birth
INSERT INTO member(name,sal)
VALUES('李四',3000);

--测试sex
INSERT INTO member(name,sal,sex)
VALUES('周五',3000,NULL);

--测试age

--测试sal
INSERT INTO member(name,sal)
VALUES('1',2200);            //2200数据可以被写入
INSERT INTO member(name,sal)
VALUES('2',200);            //200数据小于2200 无法写入

6、综合练习

创建一张表myemp，要求不能直接复制（AS），仿照原来的emp表设置各种列（包括约束），要求增加一列用于逻辑删除。

创建完成后，仿照emp表的内部数据插入14个人，对这14个人进行修改、删除、查询和数据恢复。

--创建表
CREATE TABLE myemp(
   empno    INTEGER PRIMARY KEY,
   ename    TEXT    NOT NULL,
   job      TEXT    NOT NULL,
   mgr      TEXT,
   hiredate TEXT,
   sal	    INTEGER,
   comm	    INTEGER,
   deptno   INTEGER,
   vaild    INTEGER DEFAULT  1
);

--增加雇员
INSERT INTO myemp VALUES(7369,'赵','CLERK',7902,DATETIME('1980-12-17'),800,NULL,20,1);
INSERT INTO myemp VALUES(7499,'钱','SALESMAN',7698,DATETIME('1981-02-20'),1600,300,30,1);
INSERT INTO myemp VALUES(7521,'孙','SALESMAN',7698,DATETIME('1981-02-22'),1250,500,30,1);
INSERT INTO myemp VALUES(7566,'李','MANAGER',7839,DATETIME('1981-04-02'),2975,NULL,20,1);
INSERT INTO myemp VALUES(7654,'周','SALESMAN',7698,DATETIME('1981-09-28'),1250,1400,30,1);
INSERT INTO myemp VALUES(7698,'吴','MANAGER',7839,DATETIME('1981-05-01'),2850,NULL,30,1);
INSERT INTO myemp VALUES(7782,'郑','MANAGER',7839,DATETIME('1981-06-09'),2450,NULL,10,1);
INSERT INTO myemp VALUES(7788,'王','ANALYST',7566,DATETIME('1987-04-19'),3000,NULL,20,1);
INSERT INTO myemp VALUES(7839,'冯','PRESIDENT',NULL,DATETIME('1981-11-17'),5000,NULL,10,1);
INSERT INTO myemp VALUES(7844,'陈','SALESMAN',7698,DATETIME('1981-09-08'),1500,0,30,1);
INSERT INTO myemp VALUES(7876,'褚','CLERK',7788,DATETIME('1987-05-23'),1100,NULL,20,1);
INSERT INTO myemp VALUES(7900,'卫','CLERK',7698,DATETIME('1981-12-03'),950,NULL,30,1);
INSERT INTO myemp VALUES(7902,'蒋','ANALYST',7566,DATETIME('1981-12-03'),3000,NULL,20,1);
INSERT INTO myemp VALUES(7934,'沈','CLERK',7782,DATETIME('1982-01-23'),1300,NULL,10,1);

--修改：把20部门的人都变成30部门
UPDATE myemp SET deptno=30 WHERE deptno=20;

--查询：查询部门10的雇员信息
SELECT *
FROM myemp
WHERE deptno=10;

--逻辑删除：删除部门10的雇员
UPDATE myemp SET vaild=0 WHERE deptno=10;

--数据恢复
UPDATE myemp SET vaild=1 WHERE deptno=10 AND vaild=0;