SQL系列总结（三）：DML（数据操纵语言） 前排提示： 本篇博客篇幅较长，建议结合目录进行阅读！ 目录： 前言 准备数据 0x01.插入数据 1.插入元组 2.插入子查询结果 0x02.修改数据 1.普通修改 2.带有子查询的修改语句 0x03.删除数据 1.普通删除 2.带有子查询的删除语句 环境说明： 数据库：Mysql 5.5 连接软件：Navicat 前言 SQL总结系列目录： SQL系列总结（一）：DDL（数据定义语言）- Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com) SQL系列总结（二）：DQL（数据查询语言）- Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com) SQL系列总结（三）：DML（数据操纵语言） - Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com) SQL系列总结（四）：DCL（数据控制语言） - Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com) SQL系列总结（五）：TCL（事务控制语言） - Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com) 数据操纵语言（Data Manipulation Language, DML）是对于数据库中的数据的基本操作。具体操作增、删、改这三种，对应的关键词是：增——INSERT、删——DELETE、改——UPDATE。 在使用数据库的系统开发过程中，对于数据库的基本操作就是“增、删、改、查”，以“CRUD”(分别为 Create, Read, Update, Delete)来称呼。 准备数据 本篇博客中出现的SQL语句实例基于下面的三张数据表： {tabs} {tabs-pane label="学生表"} Student(Sno,Sname,Ssex,Sage,Sdept) -- 创建表： CREATE TABLE Student(Sno CHAR(6) Primary KEY, -- 学号 主键 Sname VARCHAR(20), -- 名字 Ssex CHAR(2), -- 性别 Sage INT, -- 年龄 Sdept VARCHAR(20) -- 系部 )ENGINE=INNODB DEFAULT CHARSET=utf8; -- 插入数据： INSERT INTO Student VALUES('202101','李勇','男',20,'计算机系'); INSERT INTO Student VALUES('202102','刘晨','女',19,'计算机系'); INSERT INTO Student VALUES('202103','王敏','女',18,'数学系'); INSERT INTO Student VALUES('202104','张立','男',18,'信息系'); {/tabs-pane} {tabs-pane label="课程表"} Course(Cno,Cname,Cpno,Ccredit) -- 创建表： CREATE TABLE Course(Cno CHAR(1) PRIMARY KEY, -- 课程号 主键 Cname VARCHAR(20), -- 课程名 Cpno CHAR(1), -- 前置学科课程号 Ccredit INT -- 学分 )ENGINE=INNODB DEFAULT CHARSET=utf8; -- 插入数据： INSERT INTO Course VALUES('1','数据库','5',4); INSERT INTO Course VALUES('2','数学','null',2); INSERT INTO Course VALUES('3','信息系统','1',4); INSERT INTO Course VALUES('4','操作系统','6',3); INSERT INTO Course VALUES('5','数据结构','7',4); INSERT INTO Course VALUES('6','数据处理','',2); INSERT INTO Course VALUES('7','C语言','6',4); {/tabs-pane} {tabs-pane label="学生选课表"} SC(Sno,Cno,Grade) -- 创建表： CREATE TABLE SC(Sno CHAR(6), -- 学号 主键 Cno CHAR(1), -- 课程号 主键 Grade INT, -- 成绩 PRIMARY key(Sno,Cno) -- 设置表级约束条件 )ENGINE=INNODB DEFAULT CHARSET=utf8; -- 插入数据： INSERT INTO SC VALUES('202101','1',92); INSERT INTO SC VALUES('202101','2',85); INSERT INTO SC VALUES('202101','3',88); INSERT INTO SC VALUES('202102','2',90); INSERT INTO SC VALUES('202102','3',80); {/tabs-pane} {/tabs} 0x01.插入数据 SQL数据插入语句有两种形式，一种是插入一个元组（即一行数据），另一种是插入子查询结果，子查询结果可以是多个元组（多行数据）。 1.插入元组 为表中所有字段都添加数据： INSERT INTO <表名> VALUES(<数据1>,<数据2> ···); INTO子句中并没有指明任何属性，表示给所有字段添加值，因此新插入的元组必须在每个属性列上都具有值。 VALUE子句对新元组的各属性列赋值，括号中属性列的次序与CREATE TABLE中的属性次序一一对应。 例：将一个新学生的全部数据插入到Student表中。(学号：202105，姓名：陈东，性别：男，所在系：信息系，年龄：18） INSERT INTO Student VALUES("202105","陈东","男",18,"信息系"); 当在INTO子句中指明部分属性列名时，表示仅对这几项属性进行赋值： INSERT INTO <表名> (<属性列1>,<属性列2> ···) VALUES(<数据1>,<数据2> ···); INTO子句指出了要在哪些属性上赋值，没有出现的属性类将默认取空值。其中，表定义时说明了NOT NULL的属性列不能取空值，否则会报错 VAlUE子句中属性的顺序可以与CREATE TABLE中的顺序不一样，但必须与INTO子句中的属性字段一一对应 例：向选课表中插入一条选课记录 （学号：202103，课程号：1） INSERT INTO SC (Sno,Cno) VALUES('202103','1'); 2.插入子查询结果 子查询不仅可以嵌套在SELECT语句中来构造父查询的条件，也可以嵌套在INSERT语句中用以生成要插入的批量数据。其语句格式为： INSERT INTO <表名> (<属性列1>,<属性列2> ···) [子查询语句]; -- 可以理解为在原来查询语句的基础上将VALUE子句变为了子查询语句 0x02.修改数据 1.普通修改 修改操作也称为更新操作，其语句的一般格式为： UPDATE <表名> SET <列名>=<表达式>,<列名>=<表达式>··· [WHERE <条件>]; 其功能是修改指定表中满足WHERE子句条件的元组 SET子句后的等式表示给要修改的属性赋予新值，用于取代原来的属性列值 例：将学生李勇的年龄改为22 UPDATE Student SET Sage='22' WHERE Sname='李勇'; 如果省略WHERE子句，则修改的对象为表中的所有元组 例：将所有的学生的年龄增加一岁 UPDATE Student SET Sage=Sage+1 2.带有子查询的修改语句 子查询也可以嵌套在UPDATE语句中，用以构造修改的条件。 例：将计算机系全体学生的成绩置零 UPDATE SC SET Grade=0 WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept="计算机系"); 0x03.删除数据 1.普通删除 删除语句的一般格式为： DELETE FROM <表名> WHERE <条件>; DELETE语句执行完之后会返回删除的行数以及WHERE条件匹配的行数。 例：删除学号为202103的学生记录 DELETE FROM Student WHERE Sno='202103'; 删除学号为202101，202102，202103的学生记录 DELETE FROM Student WHERE Sno IN ('202101','202102','202103'); WHERE子句可以省略，此时为清空全表数据。请注意是数据的删除，表的结构依然存在。如： 例：删除所有学生的选课记录 DELETE FROM SC;-- 执行结果就是表SC变成了一个空表 2.带有子查询的删除语句 同修改语句，子查询也可以嵌套在DELETE语句中，用来构造执行删除操作的条件。 例：删除系所有学生的选课记录 DELETE FROM SC WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept='信息系'); 主要参考资料：《数据库系统概论（第5版）》 王珊 萨师煊 编著

SQL系列总结（二）：DQL（数据查询语言） 前排提示： 本篇博客篇幅较长，建议结合目录进行阅读！ 目录： 前言 准备数据 0x01.单表查询 1.基本查询 2.条件查询 比较大小 确定范围 确定集合 字符匹配 判断是否为空 多重条件 3.分页查询 0x02.连接查询 ——待完成 0x03.嵌套查询 ——待完成 0x04.集合查询 ——待完成 0x05.基于派生表的查询 ——待完成 环境说明： 数据库：Mysql 5.5 连接软件：Navicat 前言 SQL总结系列目录： SQL系列总结（一）：DDL（数据定义语言）- Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com) SQL系列总结（二）：DQL（数据查询语言）- Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com) SQL系列总结（三）：DML（数据操纵语言） - Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com) SQL系列总结（四）：DCL（数据控制语言） - Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com) SQL系列总结（五）：TCL（事务控制语言） - Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com) 数据查询是数据库的核心操作。因此，数据查询语言DQL（Data Query Language）是SQL中的核心部分，它允许用户查询数据，这也是通常最频繁的数据库日常操作。 SQL提供了SELECT进行语句查询，该语句具有灵活的使用方式和丰富的功能。SELECT语句既可以完成简单的单表查询，也可以完成复杂的连接查询和嵌套查询。 准备数据 本篇博客中出现的SQL语句实例基于下面的三张数据表： {tabs} {tabs-pane label="学生表"} Student(Sno,Sname,Ssex,Sage,Sdept) -- 创建表： CREATE TABLE Student(Sno CHAR(6) Primary KEY, -- 学号 主键 Sname VARCHAR(20), -- 名字 Ssex CHAR(2), -- 性别 Sage INT, -- 年龄 Sdept VARCHAR(20) -- 系部 )ENGINE=INNODB DEFAULT CHARSET=utf8; -- 插入数据： INSERT INTO Student VALUES('202101','李勇','男',20,'计算机系'); INSERT INTO Student VALUES('202102','刘晨','女',19,'计算机系'); INSERT INTO Student VALUES('202103','王敏','女',18,'数学系'); INSERT INTO Student VALUES('202104','张立','男',18,'信息系'); {/tabs-pane} {tabs-pane label="课程表"} Course(Cno,Cname,Cpno,Ccredit) -- 创建表： CREATE TABLE Course(Cno CHAR(1) PRIMARY KEY, -- 课程号 主键 Cname VARCHAR(20), -- 课程名 Cpno CHAR(1), -- 前置学科课程号 Ccredit INT -- 学分 )ENGINE=INNODB DEFAULT CHARSET=utf8; -- 插入数据： INSERT INTO Course VALUES('1','数据库','5',4); INSERT INTO Course VALUES('2','数学','null',2); INSERT INTO Course VALUES('3','信息系统','1',4); INSERT INTO Course VALUES('4','操作系统','6',3); INSERT INTO Course VALUES('5','数据结构','7',4); INSERT INTO Course VALUES('6','数据处理','',2); INSERT INTO Course VALUES('7','C语言','6',4); {/tabs-pane} {tabs-pane label="学生选课表"} SC(Sno,Cno,Grade) -- 创建表： CREATE TABLE SC(Sno CHAR(6), -- 学号 主键 Cno CHAR(1), -- 课程号 主键 Grade INT, -- 成绩 PRIMARY key(Sno,Cno) -- 设置表级约束条件 )ENGINE=INNODB DEFAULT CHARSET=utf8; -- 插入数据： INSERT INTO SC VALUES('202101','1',92); INSERT INTO SC VALUES('202101','2',85); INSERT INTO SC VALUES('202101','3',88); INSERT INTO SC VALUES('202102','2',90); INSERT INTO SC VALUES('202102','3',80); {/tabs-pane} {/tabs} 0x01.单表查询 单表查询是指仅涉及一个表的查询。是DQL的基础部分。 1.基本查询 基本表的查询很简单，查询指定表的所有数据： SELECT * FROM <表名>; -- 查询结果是一个二维表格 例：查询全体学生的详细信息 SELECT * FROM Student; 也可以查询指定列： SELECT <列名1>,<列名2>··· FROM <表名>; 例：查询全体学生的学号姓名信息 SELECT Sno,Sname FROM Student; 有时候查询出来的列会有重复值，可以用DISTINCT来消除它们： SELECT DISTINCT Sno,Sname FROM Student; 或者在查询中加入计算表达式。 例：查询全体学生的姓名、出生时间信息 SELECT Sname,2021-Sage FROM Student; SELECT语句也可以去掉FROM子句，如： SELECT 1; # 返回结果1 SELECT 1+1; # 返回结果2 这种只有表达式却没有FROM子句的SELECT语句会直接计算出表达式的结果并返回一个列名为表达式、值为计算结果的1*1表格。可以用来判断当前连接与数据库的连接是否有效。 2.条件查询 大部分查询数据中，我们只是需要部分数据，而不是全部数据。因此就需要加上一些条件来筛选掉不需要的数据，可以通过`WHERE关键字后加入相应的查询条件来实现。 WHERE子句常用到的查询条件如下： 查询条件 谓词 比较 =、>、<、>=、<=、!=、<>、!>、!< 确定范围 BETWEEN ... AND... 、NOT BETWEEN ... AND ... 确定集合 IN、NOT IN 字符匹配 LIKE、NOT LIKE 判断是否为空 IS NULL、IS NOT NULL 多重条件/逻辑运算 AND、OR、NOT 比较大小 例1：查询李勇同学的详细信息 SELECT * FROM Student WHERE Sname='李勇'; 例2：查询考试成绩不及格的学生的学号 SELECT Sno FROM SC WHERE Grade<60; 例3：查询所有年龄在20岁以下的学生姓名及其年龄 SELECT Sname,Sage FROM WHERE Sage<20; 确定范围 BETWEEN···AND··· 和NOT BETWEEN···AND···可以用来查找属性值在（或不在）指定范围的元组，其中BETWEEN后是范围的下限（即低值），AND后是范围的上限（即高值）。 例：查询年龄在18~23岁的学生的学号、姓名 SELECT Sno,Sname FROM Student WHERE Sage BEWEEN 18 AND 23; 确定集合 谓词IN可以用来查找属性值属于指定集合的元组。 例：查询计算机系全体学生的名单 SELECT Sname FROM Student WHERE Sdept IN ('计算机系'); 字符匹配 谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下： [NOT] LIKE '<匹配串>' [ESCAPE '<换码字符>'] <匹配串>可以是一个完整的字符串，也可以含有`通配符%和_。字符匹配规则如下： %（百分号）代表任意长度的字符串。 例如a%b表示以a开头，以b结尾的任意长度的字符串，如abc、abdewc、ab等都满足该匹配串 _（下划线）代表任意单个字符 例如a_b表示以a开头，以b结尾的长度为3的任意字符串。如abc、afb等都满足该匹配串。 例1：查询所有姓“刘”的学生的学号、姓名和性别 SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE '刘%'; 例2：查询姓“欧阳”且全名为三个字的学生的姓名和学号 SELECT Sname,Sno from Student WHERE Sname LIKE='欧阳_'; 例3：查询所有不姓“王”的学生的姓名、学号和性别 SELECT Sname,Sno,Ssex WHERE Sname NOT LIKE '王%'; 若用户要查询的字符串本身就含有通配符%或者_，这时就要使用ESCAPE ‘<换码字符>’短语对通配符进行转义了。 例1：查询DB_Design这门课程的课程号和学分 SELECT Cno,Ccredit FROM Course WHERE Cname='DB\_Designer' ESCAPE '\'; 例2：查询以“DB_”开头，且倒数第三个字符为i的课程的详细情况 SELECT * FROM Course WHERE Cname='DB\_%i__' ESCAPE '\'; 判断是否为空 IS NULL与IS NOT NULL用来判断条件是否为空 例：查询成绩表中只有选课记录却没有成绩的学生的学号和课程号 SELECT Sno,Cno FROM SC WHERE Grade IS NULL; 多重条件 逻辑运算符AND和OR可用来连接多个查询条件。其中AND的优先级高于OR，但可以通添加括号来改变优先级。 例：查询计算机系年龄在20岁以下的学生姓名 SELECT Sname FROM Student WHERE Sage<20; 3.分页查询 在进行表的查询时，若一次查询出来的数据数量很多的话，放在一个页面显示的话数据量太大，不如分页显示，每次显示n条，这就是分页查询。 要实现分页功能，实际上就是从结果集中显示第1~n条记录作为第1页，显示第n+1~2n条记录作为第2页，依次类推。 因此，分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT <M> OFFSET <N>子句实现。 SELECT * FROM <表名> LIMIT <M> OFFSET <N>; 例：在学生表中查询第二页学生数据，每一页三项数据 SELECT * FROM Student LIMIT 4 OFFSET 6; 0x02.连接查询 ——待完成 0x03.嵌套查询 ——待完成 0x04.集合查询 ——待完成 0x05.基于派生表的查询 ——待完成 主要参考资料：《数据库系统概论（第5版）》 王珊 萨师煊 编著

SQL系列总结（一）：DDL（数据定义语言） 前排提示： 本篇博客篇幅较长，建议结合目录进行阅读！ 目录 前言 SQL简介 数据字典 定义： 0x01.模式 创建模式——CREATE SCHEMA 删除模式——DROP SCHEMA 0x02.基本表 数据类型 创建表 删除表 修改表 0x03.索引 索引类型 建立索引 修改索引名称 删除索引 0x04.视图 定义 特征 几个概念 创建视图 查询视图 更新视图 删除视图 总结 环境说明： 数据库：Mysql 5.5 连接软件：Navicat 前言 SQL总结系列目录： SQL系列总结（一）：DDL（数据定义语言）- Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com) SQL系列总结（二）：DQL（数据查询语言）- Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com) SQL系列总结（三）：DML（数据操纵语言） - Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com) SQL系列总结（四）：DCL（数据控制语言） - Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com) SQL系列总结（五）：TCL（事务控制语言） - Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com) SQL简介 SQL（Structured Query Language），称为结构化查询语言，是关系数据库的标准语言。其功能不仅仅是查询，而是包括数据库模式创建、数据库数据的插入与修改、数据库安全性完整性控制等一系列功能。 目前没有一个关系数据库系统（RDBMS）能够支持SQL标准的所有概念和特性。大部分数据库系统能支持SQL/92标准的大部分功能以及SQL99、SQL2003中的部分新概念。同时许多软件厂商对SQL基本命令集还进行了不同程度的扩充和修改，又可以支持标准以外的一项功能特性。因此，使用具体数据库系统时还需要参考相应的官方文档。 SQL总共由以下几部分组成： 数据查询语言（DQL: Data Query Language）：其语句也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。 数据操纵语言（DML：Data Manipulation Language）：用于添加、修改和删除。 数据控制语言（DCL：Data Control Language）：实现权限控制，确定单个用户和用户组对数据库对象的访问。 数据定义语言（DDL：Data Definition Language）：在数据库中创建新表或修改、删除表（CREATE TABLE 或 DROP TABLE）；为表加入索引等。 数据字典 定义： 数据字典是关系型数据库内部的一组系统表，他记录数据库中所有的定义信息，包括关系模式定义、视图定义、索引定义、完整约束定义、各类用户对数据库的操作权限、统计信息等。 关系型数据库在执行SQL的数据定义语句时，实际上就是更新数据库字典表中的相应信息。 进行查询优化和查询处理时，数据字典中的信息是其重要依据。 本篇只涉及到DDL，即数据定义语言。如无特别说明，本篇博客中方括号内容表示可选内容。 SQL中的数据定义功能包括模式定义、表定义、视图和索引定义。 0x01.模式 创建模式——CREATE SCHEMA CREATE SCHEMA <模式名> AUTHORIZATION <用户名>; 若不指定模式名，则默认为用户名 用户需要有数据库管理员权限或者获得了管理员授予的CREATE SCHEMA权限才能创建模式 定义模式实际上定义了一个命名空间，用户在创建模式的同时可以在这个模式中创建基本表、视图、定义授权等。即： CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>]; 删除模式——DROP SCHEMA DROP SCHEMA <模式名> <CASCADE|RESRICT>; # CASCADE（级联）和 RESTICT（限制）两者必选其一 0x02.基本表 数据类型 数据类型 含义 CHAR(n)，CHARACTER(n) 长度为n的定长字符串 VARCHAT(n)，CHARACTERVARYING(n) 最大长度为n的变长字符串 CLOB 字符串大对象 BLOB 二进制大对象 INT，INTEGER 长整数（4字节） SMALLINT 短整数（2字节） BIGINT 大整数（8字节） NUMERIC(p,d) 定点数，由p位数字（不包括小数点、符号）组成，小数点后面有d位数字 DECIMAL(p,d)，DEC(p,d) 同NUMERIC REAL 取决于机器精度的单精度浮点数 DOUBLE PRECISION 取决于机器精度的双精度浮点数 FLOAT(n) 可选精度的浮点数，精度至少为n位数字 BOOLEAN 布尔类型 DATE 日期，包含年、月、日，格式为YYYY-MM-DD TIME 时间，包含一日的时、分、秒，格式为HH:MM:SS TIMESTAMP 时间戳 INTERVAL 时间间隔类型 这里要说明的是，不同的数据库产品支持的数据类型并不完全相同，具体使用时还需参考官方文档。 创建表 CREATE TABLE <表名> (<列名1> <数据类型> [列级完整性约束条件1], <列名2> <数据类型> [列级完整性约束条件2], ... [<表级完整性约束条件>]); 附：常用到的与表有关的约束条件： NOT NULL：非空约束 UNIQUE：唯一约束 PRIMARY KEY：主键约束 FROEIGN KEY：外键约束 CHECK：校验约束 查看当前数据库有多少表： # 选中某一个数据库 USE <数据库名>; # 查看该数据库的所有表 SHOW TABLES; 删除表 DROP TABLE <表名> [RESTRICT|CASCADE]; RESTRICT与CASCADE的区别： RESTRICT指限制删除，表示该表的删除是有限制条件的：即该表不能被其他表的约束所引用（如CHECK，FOREIGN KEY等约束），不能存在依赖于该表的对象，比如视图、触发器、存储过程或者函数等。只有当这些限制条件不存在时，才能允许删除。 CASCADE指级联删除，加上此参数之后则该表的删除没有限制条件。在删除基本表的同时，相关的对象，例如视图等，都将被一起删除。 如果不指定删除类型时，默认是RESTRICT。 修改表 这里的修改针对的是基本表的结构（如添加删除列、或者修改数据类型），并不是基本表的数据。对于基本表数据的修改属于DML的范围，本篇博客只涉及到DDL。 添加新列 ALTER TABLE <表名> ADD [COLUMN] <新列名> <数据类型> [完整性约束]; # 给已存在的列添加列级完整性约束 ALTER TABLE <表名> ADD [列级完整性约束条件]; 添加新的表级约束条件 ALTER TABLE <表名> ADD <表级完整性约束条件>; 删除指定列 ALTER TABLE <表名> DROP [COLUMN] <列名> [CASCADE|RESTRICT]; 删除指定的完整性约束条件 ALTER TABLE <表名> DROP CONSTRAINT <完整性约束名> [RESTRICT|CASCADE]; 修改表中已存在的列 ALTER TABLE <表名> ALTER COLUMN <列名> <数据类型>; 0x03.索引 建立索引的目的：加快查询速度 缺点：索引虽然能够加速数据库查询，但需要占用一定的存储空间，并且当基本表更新时，索引也需要进行相应的维护。这些都会增加数据库的负担，因此要根据实际应用的需要有选择地创建索引。 索引类型 目前SQL标准中没有涉及索引，但商用关系数据库系统一般都会支持索引机制，且不同数据库支持的索引类型不尽相同。 顺序文件上的索引：针对按指定属性值升序和降序存储的关系，在该属性上建立一个顺序索引文件，索引文件由属性值和相应的元组指针组成。 B+树索引：将索引属性组织成 B+树的形式，B+树的叶节点为属性值和相应的元组指针。B+树索引具有动态平衡的优点。 散列(hash)索引：建立若干个桶，将索引属性按照其散列函数映射到相应桶中，桶中存放索引属性和相应的元组指针。散列 索引具有查找速度快的特点。 位图索引：用位向量记录索引属性中可能出翔的值，每个位向量对应一个可能值。 建立索引 CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名1>[<次序1>],···); <表名>是要建索引的基本表的名字 索引可以建立在该表的一列或者多列上，各列名之间用逗号分隔没每个列名后妈可以用<次序>指定索引值的排列次序，可选ASC(升序)或者DESC(降序)。默认ASC。 UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。 CLUSTER此索引是聚簇索引。 修改索引名称 ALTER INDEX <旧索引名> RENAME TO <新索引名>; 要修改索引本身的话，建议删除再重建。 删除索引 DROP INDEX <索引名> 索引一经建立就由系统使用和维护，无需用户干预。 删除索引是由于数据库频繁进行增、删、改，系统便会花费许多时间来维护索引，从而降低查询效率，这是便可以删除一些不必要的索引。 索引删除后，数据字典上关于索引的描述也会被删除。 0x04.视图 定义 视图是从一个或几个基本表（或者视图）导出的表。 视图一经定义，就可以和基本表一样被查询、被删除。也可以在一个视图上再定义新的视图，但对视图的更新（增、删、改）操作则有一定的限制。 特征 数据库只存放视图的定义，而不存放视图中对应表的数据（否则数据库中便存放了很多相同的数据），这些数据仍存放在原来的基本表中。 归根到底，视图与“图”无关，其实质上还是表。只不过由于不存放数据，只存放定义，因此称其为“虚表”。 几个概念 行列子集视图：建立在基本表之上，只是去掉了基本表的某些行和列，但保留了主键的这类视图。 分组视图：带有聚集函数和GROUP BY子句的查询的视图。 带表达式的视图：简单来说就是视图中存在基本表中不实际存在的列，即虚拟列。这些列是由基本表中的数据列经过各种计算派生出来的。 创建视图 CREATE VIEW <视图名> (<列名>,<列名>,<列名> ...) AS <子查询> [WITH CHECK OPTION]; <子查询>是针对基本表的SELECT语句，即从建立视图的基本表中选取部分数据，而不是全部数据 [WITH CHECK OPTION]是一个条件表达式，有这个条件表达式时，对视图进行UPDATE、INSERT和DELETE时如果要操作的行不满足这里的条件，则不允许进行 视图不仅可以建立在单个基本表上，也可以建立在多个基本表上 数据库执行CREATE VIEW语句的结果只是把视图的定义存入数据字典，并不执行其中的SELECT语句。只有在进行视图的查询时，才会执行SELECT语句。 组成视图的属性列名全部指定或者全部省略，没有第三种选择。 以下三种情况必须要指明视图的列名： 某个目标列并不是单纯的属性名，而是聚集函数或者列表达式 多表连接时选出了几个同名列作为视图的字段 需要在视图中为某个列启用新的更适合的名字 查询视图 视图其本质上还是表，因此可以对其进行查询。查询视图与查询表的语句基本相同。详见[DQL]()。 在视图查询的过程中，会经过视图消解，将对视图的查询转换为对基本表的查询。 视图消解：关系型数据库执行视图的查询操作时，首先进行有效性检查，即确定查询中涉及到的表、视图等是否都存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。这一转换过程称之为视图消解。 局限：目前多数关系数据库对行列子集视图都能正确地转换。但对非行列子集视图的查询就不一定能做转换了，因此这类查询应该直接对基本表进行。 非行列子集视图：图中的部分列由其他表的列经过运算得出。 视图查询与基于派生表的查询的区别： 视图一旦定义，其定义将永久保存在数据字典中，之后的所有查询都可以直接饮用该视图。 而派生表知识在语句执行时临时定义，语句执行还定义即被删除。 更新视图 视图的更新包括INSERT、DELETE、UPDATE，其操作语句与表的操作语句基本相同。此处不再详述。详见[DML]()。 类似于视图的查询，对视图的更新同样是通过视图消解，转换为对基本表的更新操作。 目前各个关系数据库一般只允许对行列子集视图进行更新，而且不同的数据库对视图的更新还有更进一步的规定。由于各数据库系统实现方法上的差异，这些的规定也不尽相同。 删除视图 DROP VIEW <视图名> [CASCADE]; 视图删除实质上是将视图的定义从数据字典中删除。 CASCADE是可选的 若要删除的视图还导出了其他视图，那么加上CASCADE参数之后将会把该视图导出的视图一块删除。 总结 SQL可以分为数据定义(DDL)、数据查询(DQL)、数据更新(DML)、数据控制(DCL)四大部分。 综上，DDL中的基本操作可以用表格简单总结一下： 操作对象 创建 删除 修改 模式 CREATE SCHEMA DROP SCHEMA 表 CREATE TABLE DROP TABLE ALTER TABLE 视图 CREATE VIEW DROP VIEW 索引 CREATE INDEX DROP INDEX ALTER INDEX 主要参考资料：《数据库系统概论（第5版）》 王珊 萨师煊 编著

Java总结：JDBC连接操作数据库(一) 目录： 前言 一、JDBC结构 1.Java程序 2.JDBC管理器 3.驱动程序 4.数据库 二、JDBC编程的步骤 1、导入包 2、加载驱动程序 3、设置JDBC的连接地址信息 4、创建数据库连接 5、使用SQL语句操作数据库 5.1.Statement接口方法创建表： 5.2.PreparedStatement接口： 6、关闭连接 三、相关的类与方法 1、DriverManager类 ——管理驱动 2、Connection接口 ——建立连接 3、Statement接口 ——执行SQL语句 4、PreparedStatement接口 ——执行SQL语句 5、ResultSet接口 ——存放查询之后返回的结果 前言 Java Database Connectivity简称JDBC，属于Java核心API的一部分，是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口。支持ANSI SQL-92标准，通过调用这些类和接口提供的成员方法，我们可以方便地连接各种不同的数据库，进而使用标准的SQL命令对数据库进行查询、插入、删除、更新等操作。 一、JDBC结构 1.Java程序 主要功能是根据JDBC方法实现对数据库的访问和操作。 主要任务有：请求与数据库建立连接，向数据库发送SQL请求，为结果集定义存储应用和数据类型，查询结果，处理错误，控制传输、提交及关闭连接等。 2.JDBC管理器 即驱动程序管理器，动态地管理和维护数据库查询查询所需要的所有驱动程序对象，实现Java程序与特定驱动程序的连接。 主要任务有：为特定数据库选择驱动程序，处理JDBC初始化调用，为每个驱动程序提供JDBC功能的入口，为JDBC调用执行参数等 3.驱动程序 主要任务：建立与数据库的连接，向数据库发送请求，用户程序请求是执行编译，将错误代码格式化成标准的JDBC错误代码。 编程所使用的数据库系统不同，所需要的驱动程序也有所不同。 4.数据库 按数据结构来存储和管理数据的计算机软件。 常见的数据库比如mysql、Oracle、SqlServer等。 二、JDBC编程的步骤 一次完整的JDBC实现过程分为以下几步： 1、导入包 在程序首部将相关的包导入程序 import java.sql.*; 2、加载驱动程序 使用Class.forName()方法来加载相应的驱动程序。不同数据库所需要加载的驱动程序也不同： // 加载mysql的驱动程序 Class.forName("com.mysql.jdbc.Driver"); //加载oracle的驱动程序 Class.forName("oracle.jdbc.driver.oracleDriver"); 3、设置JDBC的连接地址信息 指定JDBC要连接的地址、端口、数据库、用户名、密码等信息 String username = "root"; String password = "root"; String url = "jdbc:mysql://localhost:3306/test"; // 如果要往表中插入中文，还需要设置编码为utf-8 String url = "jdbc:mysql://localhost:3306/test?seUnicode=true&characterEncoding=utf8"; 其中，"jdbc:mysql"是连接协议，“localhost”是连接地址，“3306”是mysql的连接端口（mysql的默认连接端口是3306），“test”是要连接操作的数据库。 4、创建数据库连接 DriverManager 类中的getConnection() 方法用上一步设置好的url作为参数来创建一个连接对象，并返回这个连接对象给Connection的实例。 //url是上一步创建的连接地址 Connection conn = DriverManager.getConnection(url,username,password); 5、使用SQL语句操作数据库 JDBC中执行SQL语句可以使用Statement接口以及其子接口PreparedStatement接口，下面分别使用不同接口来举例简单说明其用法： 5.1.Statement接口方法创建表： /** * 例子：Statement接口执行创建表，并且插入一组数据 */ // 在当前数据库下创建一个学生表，表中包含主键字段id、姓名name、以及更新时间updatetime String sql1 = "create table student(id int NOT NULL AUTO_INCREMENT primary key,name char(10),updateTime Datetime)"; // 向创建的student表添加一组信息 String sql2 = "insert into student(name,updatetime) values('qwe',sysdate())"; // 创建一个Statement对象 Statement st = conn.createStatement(); // 用executeUpdate()函数执行不返回任何内容的sql语句，如INSERT、UPDATE、DELETE以及其他DDL（数据定义语言）等。其参数为SQL语句 // 执行建表SQL语句 st.executeUpdate(sql1); // execute()函数可以执行传进来的任意SQL语句 // 执行插入数据的SQL语句 st.execute(sql2); // 释放资源 st.close(); 5.2.PreparedStatement接口： /** * 例子：PreparedStatement接口执行查询表中数据的SQL语句 */ String sql3 = "select * from student"; // 创建一个PreparedStatement对象，同时对传入的SQL语句进行预编译 PreparedStatement ps = conn.prepareStatement(sql3); // PreparedStatement接口中的execute()方法是没有参数的，因为SQL语句在创建对象时已传入并且预编译了 ResultSet result = ps.executeQuery(); while(rs.next()){ // 通过索引来获取查询到的值 int id = rs.getInt(1); String name = rs.getString(2); // 通过列名来获取查询到的值 Date date = rs.getDate("updateTime"); } // 释放资源 ps.close(); 6、关闭连接 用完就要释放所连接的数据库及JDBC资源，关闭与数据库的连接 conn.close(); 上面就是是JDBC编程的基本流程，下面对这个过程中涉及到的一些类与方法做简单介绍： 三、相关的类与方法 1、DriverManager类 ——管理驱动 用于管理一组JDBC驱动程序的基本服务。 官方文档介绍：DriverManager (Java Platform SE 8 ) (langp.wang) 其常用成员方法如下： 返回值 方法体 说明 static Connection getConnection(String url) 用指定的数据库URL来创建连接 static Connection getConnection(String url, Properties info) 用指定的数据库URL和相关信息（用户名、用户密码等属性列表）来创建连接 static Connection getConnection(String url, String user, String password) 用指定的数据库URL、用户名和用户密码来创建连接 static Driver getDriver(String url) 定位在给定URL下的驱动程序。 DriverManager尝试从已注册的JDBC驱动程序集中选择适当的驱动程序。 static void deregisterDriver(Driver driver) 从DriverManager的已注册驱动程序列表中删除指定的驱动程序。 static void println(String message) 将消息输出到当前JDBC日志流。 static void setLoginTimeout(int seconds) 驱动程序尝试连接数据库时将等待的最长时间，以秒为单位。 2、Connection接口 ——建立连接 负责建立与指定数据库的连接。 默认情况下，Connection对象处于自动提交模式，这意味着它在执行每个语句后自动提交更改。 如果禁用了自动提交模式，则必须显式调用方法commit()方法才能提交更改；否则，将不会保存数据库更改。 官方文档介绍： Connection (Java Platform SE 8 ) (langp.wang) 其常用成员方法如下： 返回值 方法体 说明 Statement createStatement() 创建一个Statement对象，用于将SQL语句发送到数据库。 PreparedStatement prepareStatement(String sql) 创建一个PreparedStatement对象，用于将参数化的SQL语句发送到数据库。 void close() 立即释放此Connection对象的数据库和JDBC资源，而不是等待它们自动释放。 void commit() 使自上一次提交/回退以来进行的所有更改永久生效，并释放此Connection对象当前持有的所有数据库锁。 void rollback() 撤销对数据库执行的添加、删除或者修改记录等操作，并释放此Connection对象当前持有的所有数据库锁。 3、Statement接口 ——执行SQL语句 用于执行静态SQL语句并返回其产生的结果的对象。 默认情况下，每个Statement对象只能同时打开一个ResultSet对象。 因此，如果一个ResultSet对象的读取与另一个的读取交错，则每个都必须由不同的Statement对象生成。 如果当前存在打开的语句，Statement接口中的所有执行方法都会隐式关闭该语句的当前ResultSet对象。 官方文档介绍： Statement (Java Platform SE 8 ) (langp.wang) 其常用成员方法如下： 返回值 方法体 说明 Connection getConnection() 检索产生此Statement对象的Connection对象 void close() 立即释放此Statement对象的数据库和JDBC资源，而不是在自动关闭时等待它发生 boolean execute(String sql) 执行给定的SQL语句，该语句可能返回多个结果 ResultSet executeQuery(String sql) 执行给定的SQL语句，该语句返回一个ResultSet对象 int executeUpdate(String sql) 执行给定的SQL语句，该语句可以是INSERT，UPDATE或DELETE语句，也可以是不返回任何内容的SQL语句，例如SQL DDL语句 ResultSet getResultSet() 以ResultSet对象的形式检索当前结果 executeUpdate()、executeQuery()与execute()方法的区别： execute()函数：可以执行所有SQL语句。 当执行查询语句时，返回的boolean值指示查询结果的形式，返回值为true时表示查询结果为ResultSet，反之为false（即认为没有查到）；执行其他语句时，如果第一个结果是更新计数或不存在任何结果，则返回false executeUpdate()：执行insert、update、delete等不返回任何内容的非查询语句。 executeQuery()：用于执行select语句。返回一个ResultSet对象，其中包含由给定查询产生的数据； 永不为空 4、PreparedStatement接口 ——执行SQL语句 表示预编译的SQL语句的对象。是Statement的子接口。 创建PreparedStatement对象时需传入一个SQL语句，该SQL语句已预编译并存储在PreparedStatement对象中。然后可以使用该对象多次有效地执行该语句。 官方文档介绍： PreparedStatement (Java Platform SE 8 ) (langp.wang) 其常用成员方法如下： 返回值 方法体 说明 boolean execute() 在此PreparedStatement对象中执行SQL语句，可以是任何类型的SQL语句 ResultSet executeQuery() 在此PreparedStatement对象中执行SQL查询，并返回查询生成的ResultSet对象 int executeUpdate() 在此PreparedStatement对象中执行SQL语句，该对象必须是SQL数据操作语言（DML）语句，例如INSERT，UPDATE或DELETE； 或不返回任何内容的SQL语句，例如DDL语句 5、ResultSet接口 ——存放查询之后返回的结果 表示数据库结果集的数据表，通常通过执行查询数据库的语句来生成。 ResultSet对象有一个游标，该游标指向其当前数据行。 最初，光标位于第一行之前。next()方法可将光标移动到下一行，当ResultSet对象中没有更多行时它将返回false，因此可以在while循环中使用它来迭代结果集。例如： // 假设rs是前面进行查询操作返回的ResultSet对象 while(rs.next()){ // 输出结果 } 用next()方法可以实现访问每一个数据行，那么如何获取数据行中的每一列数据呢？ResultSet接口提供了用于从当前行中检索列值的getter方法，方法名是get+类型，如getBoolean()，getInt()。 getter方法的参数可以是列的索引值或者列的名称，对应的是用索引或者列名来从当前数据行中检索列值。 通常，使用列索引会更有效。 列从1开始编号。为实现最大的可移植性，应按从左到右的顺序读取每一行中的结果集列，并且每一列只能读取一次。 getter方法用列名检索时传入的列名称不区分大小写。 当多个列具有相同的名称时，将返回第一个匹配列的值。 对于在查询中未明确命名的列，最好使用列的索引。 如果使用了列名，则应注意确保它们唯一地引用了预期的列，这可以通过SQL AS子句来确保。 例如： // 假设rs是前面进行查询操作返回的ResultSet对象 while(rs.next()){ // 使用索引来检索 int id = rs.getInt(1); // 使用列的名称来检索 String name = rs.getString("name"); // 且列名不区分大小写 Date updateTime = rs.getDate("UPDATETIME"); } 官方文档介绍： ResultSet (Java Platform SE 8 ) (langp.wang) 其常用成员方法如下： 返回值 方法体 说明 boolean absolute(int row) 将光标移动到此ResultSet对象中的给定行号 boolean first() 将光标移动到此ResultSet对象的第一行 void beforeFirst() 将光标移动到此ResultSet对象的前面，紧挨着第一行 boolean isFirst() 检索光标是否在此ResultSet对象的第一行上 boolean last() 将光标移动到此ResultSet对象的最后一行 void afterLast() 将光标移动到此ResultSet对象的末尾，紧接在最后一行之后 boolean isLast() 检索光标是否在此ResultSet对象的最后一行 boolean next() 将光标从当前位置向前移动一行 void insertRow() 将插入行的内容插入到此ResultSet对象和数据库中 void updateRow() 使用此ResultSet对象的当前行的新内容更新底层数据库 void deleteRow() 从此ResultSet对象和底层数据库中删除当前行 void update类型(int ColumnIndex,类型 x) 使用给定类型x更新指定列 int get类型(int ColumnIndex) 以Java类型的形式获取此ResultSet的对象的当前行中指定列的值 主要参考资料： 《数据库系统概论（第5版）》 王珊 萨师煊 编著 Java SE 1.8 官方文档

【踩坑实录】win10下在控制台中无法使用 mysql 命令：mysql : 无法将“mysql”项识别为 cmdlet、函数、脚本文件 环境说明： 系统：win10专业版 mysql 5.7 问题 win10 在使用 mysql 命令登录数据库时，出现报错信息： 解决办法 系统中如果正常安装了 MySQL 的话，没有添加 MySQL 的环境变量的话，在 MySQL 安装目录 bin 文件夹以外的路径下执行 mysql 命令的时候就会报这个错误。解决办法就是添加环境变量： 右键 “开始菜单” -> “系统” -> “高级系统设置” -> “环境变量”，向 Path 中添加环境变量，值为 MySQL 安装目录下的 bin 文件夹： D:\Program\Environment\MySQL\MySQL Server 5.7\bin 配置完之后再使用 mysql 命令时就不报错了