数据库知识复习

数据库知识复习

工作必备SQL复习，主要根据狂神的SQL课程做的笔记，加了一些有关空间数据库的操作。代码基本基于MySQL语法，但与空间数据库有关会基于PostgreSQL语法。

参考网址：

1. 狂神说MySQL系列 (qq.com)
2. PostgreSQL 13.1 手册
3. 工程项目需要记住；表示一个记录存在意义

2、数据库要了解的内容

1. DDL 数据库定义语言

2. DML 数据库操作语言

3. DQL 数据库查询语言

4. DCL 数据库控制语言

   

3、操作内容

1. 数据库
   1. 创建数据库 : create database [if not exists] 数据库名;
   2. 删除数据库 : drop database [if exists] 数据库名;
   3. 查看数据库 : show databases;
   4. 使用数据库 : use 数据库名;
2. 数据库中表
3. 数据库中表内数据

4、DDL

1.创建数据表 – CREATE

create table [if not exists] `表名`(
'字段名1' 列类型 [属性][索引][注释],
'字段名2' 列类型 [属性][索引][注释],
#...
'字段名n' 列类型 [属性][索引][注释]
)[表类型][表字符集][注释];

 --举例
 CREATE TABLE IF NOT EXISTS `student` (
     `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
     `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
     `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
     `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
     `birthday` datetime DEFAULT NULL COMMENT '生日',
     `address` varchar(100) DEFAULT NULL COMMENT '地址',
     `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
     PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 修改数据表 – ALTER

修改表名 :ALTER TABLE 旧表名 RENAME AS 新表名

添加字段 : ALTER TABLE 表名 ADD字段名 列属性[属性]

修改字段 :

• ALTER TABLE 表名 MODIFY 字段名 列类型[属性]

• ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]

删除字段 : ALTER TABLE 表名 DROP 字段名

3. 删除数据表 – DROP

DROP TABLE [IF EXISTS] 表名

5、 DML

1.添加 INSERT

INSERT命令

语法：

INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1'),('值2'),('值3')

注意 :

• 字段或值之间用英文逗号隔开 .
• ’ 字段1,字段2…’ 该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致 .
• 可同时插入多条数据 , values 后用英文逗号隔开. 一个括号表示一条数据。

--举例
INSERT INTO `grade`(`gradename`) VALUES('大四');
INSERT INTO `grade`(`gradename` ) VALUES('大三'),('大四');

2.修改 UPDATE

update命令

语法：

UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];

注意 :

• column_name 为要更改的数据列
• value 为修改后的数据 , 可以为变量 , 具体指 , 表达式或者嵌套的SELECT结果
• condition 为筛选条件 , 如不指定则修改该表的所有列数据

where条件子句

可以简单的理解为 : 有条件地从表中筛选数据

BETWEEN AND：闭区间

3.删除 DELETE/TRUNCAT

DELETE命令

语法：

DELETE FROM 表名 [WHERE condition];

注意：condition为筛选条件 , 如不指定则删除该表的所有列数据

-- 删除最后一个数据
DELETE FROM grade WHERE gradeid = 5

TRUNCATE命令

作用：用于完全清空表数据 , 但表结构 , 索引 , 约束等不变 ;

语法：

TRUNCATE [TABLE] table_name;
-- 清空年级表
TRUNCATE grade

注意：区别于DELETE命令

• 相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
• 不同 :
  • 使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器
  • 使用TRUNCATE TABLE不会对事务有影响 （事务后面会说）

5.1 空间类型修改

----设置字段属性----
ALTER TABLE security.sec_rescue_warehouse 
ALTER COLUMN z_geom type geometry(point, 4490);

ALTER TABLE pipe.traffic_parking_lot 
ALTER COLUMN z_geom type geometry(MultiPolygon, 4490);

traffic_parking_lot：qsdw 空格转null

----多线转单线：多设一个字段后续INSERT新的字段----
ALTER TABLE pipe_temp.danger_line_ys ADD COLUMN IF NOT EXISTS z_geom geometry(LineString,4490);
update pipe_temp.danger_line_ys set z_geom = ST_Transform(ST_GeometryN(geom, 1), 4490);

---

更新版空间类型修改！

-- 修改字段属性：多面为单面
ALTER TABLE pipe_temp."DXTCPY" ALTER COLUMN geom TYPE geometry(Polygon,4490)
USING ST_Transform(ST_GeometryN(geom, 1), 4490);

-- 修改字段属性：多线为单线
ALTER TABLE pipe_temp."wsjb_line_prj" ALTER COLUMN geom TYPE geometry(LineString,4490)
USING ST_Transform(ST_GeometryN(geom, 1), 4490);

-- 字段属性：排查日期由YYYY/MM/DD HH24:MI:SS转为yyyy-MM-dd
ALTER TABLE pipe_temp."wsjb_line_prj" ALTER COLUMN zjycqysj TYPE varchar(20)
USING to_char(to_date(zjycqysj, 'YYYY/MM/DD HH24:MI:SS'),'YYYY-MM-DD');

6、⭐DQL* – SELECT !!!

1.SELECT语法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
 [left | right | inner join table_name2]  -- 联合查询
 [WHERE ...]  -- 指定结果需满足的条件
 [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
 [HAVING]  -- 过滤分组的记录必须满足的次要条件
 [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
 [LIMIT {[offset,]row_count | row_countOFFSET offset}];
  -- 指定查询的记录从哪条至哪条

注意 : [ ] 括号代表可选的 , { }括号代表必选

2. AS 别名

3. DISTINCT 去重

4. 表达式

数据库中的表达式定义 :

一般由文本值 , 列值 , NULL , 函数和操作符等组成

应用场景 :

• SELECT语句返回结果列中使用
• SELECT语句中的ORDER BY , HAVING等子句中使用
• DML语句中的 where 条件语句中使用表达式

-- selcet查询中可以使用表达式
SELECT @@auto_increment_increment; -- 查询自增步长
SELECT VERSION(); -- 查询版本号
SELECT 100*3-1 AS 计算结果; -- 表达式

-- 学员考试成绩集体提分一分查看
SELECT studentno,StudentResult+1 AS '提分后' FROM result;

5. SELECT 中WHERE语句

作用：用于检索数据表中 符合条件 的记录

​ 搜索条件可由一个或多个逻辑表达式组成 , 结果一般为真或假.

逻辑操作符

注意：not!

-- 除了1000号同学,要其他同学的成绩
SELECT studentno,studentresult
FROM result
WHERE studentno!=1000;

-- 使用NOT
SELECT studentno,studentresult
FROM result
WHERE NOT studentno=1000;

6.模糊查询

比较操作符

-- =============================================
-- LIKE
-- =============================================
-- 查询姓刘的同学的学号及姓名
-- like结合使用的通配符 : % (代表0到任意个字符) _ (一个字符)

-- 查询姓名中含有特殊字符的需要使用转义符号 '\'
-- 自定义转义符关键字: ESCAPE ':'

-- =============================================
-- IN（后面要跟确定的值）
-- =============================================
-- 查询学号为1000,1001,1002的学生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);

7. 联表查询 JOIN…ON…

8. 自连接

定义：数据表与自身进行连接

需求：从一个包含栏目ID , 栏目名称和父栏目ID的表中

​ 查询父栏目名称和其他子栏目名称

9.排序 ORDER BY

ORDER BY {列名} ASC/DESC

10.分页 LIMIT

位置：所有语句的最后一排

语法 :

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

好处 : (用户体验,网络传输,查询压力)

推导:

第一页 : limit 0,5

第二页 : limit 5,5

第三页 : limit 10,5

…

第N页 : limit (pageNo-1)*pageSzie, pageSize

[pageNo:页码, pageSize:单页面显示条数]

n：当前页；数据总数/页面大小=总页数

11. 子查询

• 在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
• 嵌套查询可由多个子查询组成,求解的方式是由里及外;
• 子查询返回的结果一般都是集合,故而建议使用IN关键字;

12.函数 – 聚合函数

狂神说MySQL05：MySQL函数 (qq.com)

PostgreSQL 常用函数 | 菜鸟教程 (runoob.com)

加密：在数据库的时候加密比对 MD5()

13.分组和过滤

GROUP BY [列名]
HAVING ...

7、索引

1.定义

狂神说MySQL06：事务和索引 (qq.com)

2.索引的使用

1. 创建表的时候给字段增加索引
2. 创建完毕后，增加索引 ALTER
3. 直接创建