数据库知识复习
工作必备SQL复习,主要根据狂神的SQL课程做的笔记,加了一些有关空间数据库的操作。代码基本基于MySQL语法,但与空间数据库有关会基于PostgreSQL语法。
参考网址:
- 狂神说MySQL系列 (qq.com)
- PostgreSQL 13.1 手册
- 工程项目需要记住;表示一个记录存在意义
2、数据库要了解的内容
-
DDL 数据库定义语言
-
DML 数据库操作语言
-
DQL 数据库查询语言
-
DCL 数据库控制语言
3、操作内容
- 数据库
- 创建数据库 : create database [if not exists] 数据库名;
- 删除数据库 : drop database [if exists] 数据库名;
- 查看数据库 : show databases;
- 使用数据库 : use 数据库名;
- 数据库中表
- 数据库中表内数据
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.函数 – 聚合函数
PostgreSQL 常用函数 | 菜鸟教程 (runoob.com)
加密:在数据库的时候加密比对 MD5()
13.分组和过滤
GROUP BY [列名]
HAVING ...
7、索引
1.定义
2.索引的使用
- 创建表的时候给字段增加索引
- 创建完毕后,增加索引 ALTER
- 直接创建