数据库笔记
- PostgreSQL
- MongoDB
- MySQL
- Oracle
PostgreSQL
PostgreSQL是自由的对象-关系型数据库服务器(数据库管理系统),在灵活的BSD许可证下发行。它在其他开放源代码数据库系统(比如MySQL和Firebird),和专有系统(比如Oracle、Sybase、IBM的DB2和Microsoft SQL Server)之外,为用户提供了又一种选择。
安装
安装环境系统为CentOS7.6 X86_64, 安装方式为yum方式安装, 安装的数据库版本为 11.0 版本
添加源
1
yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7.6-x86_64/pgdg-centos11-11-2.noarch.rpm
安装软件
1
yum install postgresql11-client postgresql11-server postgresql11-contrib
初始化数据库
1
/usr/pgsql-11/bin/postgresql-11-setup initdb
启动服务
1
2
3
4systemctl start postgresql-11.service
systemctl status postgresql-11.service
firewall-cmd --permanent --add-service=postgresql
firewall-cmd --reload其他信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22# 配置文件位置
$ sudo vim /var/lib/pgsql/11/data/postgresql.conf
# 监听外部请求->修改配置文件
# - Connection Settings -
listen_addresses = '*' # 这里改为监听全网
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
#port = 5432 # 默认端口
# 允许外部访问,编辑下列配置文件,修改认证方法为md5
$ sudo vim /var/lib/pgsql/11/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust # 本地访问
# IPv4 local connections:
host all all 0.0.0.0/0 md5 # ipv4 允许全网访问,密码验证
# host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md51
2
3
4
5
6
7
- 配置用户,设置管理员用户密码
```bash
# 配置管理员(postgres)用户密码
[root@localhost ~]# su - postgres
-bash-4.2$ psql -c "alter user postgres with password '123456'" # 配置管理员密码,也可配置其他用户
shell操作
Windows版本有图形化管理界面pgAdmin直接使用即可
登录数据库
1
2
3
4
5
6psql -h localhost -p 5432 -U test_user -d test_db
# -h : 指定主机地址
# -p : 指定端口
# -U : 指定用户名
# -d : 指定数据库
# 如果不指定数据库,则会登录默认和用户名相同的数据库psql相关命令
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25# 登录psql 创建用户和数据库并赋予权限
su - postgres # 切换到数据库管理员账户
psql # 切换到数据库提示符下
\d # 查看数据库
create user <UserName> with password '<UserPassword>'; # 创建用户
create database <DataBaseName> owner <UserName>; # 创建数据库
grant all privileges on database <DBName> to <DBUser>;; # 赋予权限
\q # 退出
# 登录数据库看上面的命令
\h #查看所有的sql关键字
\? #命令行操作的帮助
\d #查看当前schema 中所有的表
\d+ #显示更多内容,比如可以查询分区表的信息
\q #退出pg命令行
\d #schema.table 查看表的结构
\x #横纵显示切换
\dT+ #显示扩展类型相关属性及描述
\dx #显示已安装的扩展插件
\l #列出所有的数据库
\timing #显示执行时间
\c database_name #切换数据库
set search to schema #切换schema
explain sql #解释或分析sql执行过程
事务操作
1 | # 使用事务操作,需要使用BEGIN和COMMIT关键字来包裹 |
OVER (PARTITION BY)
1 | SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary; |
常用SQL
1 | -- 查询值为空的时候设置默认值 |
特别记录
不熟练的操作记录
转换字符串浮点数到double
类型
使用case
根据正则判断然后转换数据
1 | WITH subquery AS (SELECT '1.1232'::text as value) |
随机数生成
按照指定范围生成整数或者小数
1 | -- 指定范围的小数 0.00001 <= x < 0.001 并保留 6位小数 |
查看正在执行的sql
用于解决莫名其妙的等待sql执行完成错误
1 | SELECT |
运行上面的sql会查看到当前正在执行sql语句,第一列是进程id,进入系统直接kill此进程,然后重启后台服务即可正常操作了
1 | kill -9 <ID> |
MongoDB
MonggoDB是一种以文档形式存储数据的数据库,适合大文档存取使用,数据是key, value 形式
MongoDB安装
本安装操作参考官网手册安装MongoDB Manual
环境: CentOS7 安装方式: yum
配置yum
因为官网的yum源访问速度太慢,这里切换到阿里云的yum源,注意示例的版本是4.0
1 | [mongodb-org-4.0] |
安装
1 | # 素质二连,建立缓存 |
服务启动
服务默认端口号是27017
1 | # 启动服务 |
修改配置文件
因为使用的是yum
方式安装,这里直接去/etc/
下修改mongod.conf
文件,找到下面这段该修改为0.0.0.0,::
来监听IPV4
和IPV6
全网段.修改完重启服务
1 | # network interfaces |
MongoDB常用操作
Linux
中在shell
中的任何地方使用mongo
进入MongoDB
的shell界面
shell中使用use创建数据库,但是库中必须有集合才会生效
库->集合->文档
可以把集合简单的理解为关系型数据库中的表,文档是关系型数据库中表中的记录
下图展示了向一个集合中插入一条数据的命令,不需要事先建立集合
连接mongo数据库
下面这三种方式是一样的
1 | mongo mongodb://mongodb0.example.com:28015 |
认证方式连接
指定了--password
参数,不输入密码,shell会自动提示输入密码
1 | mongo --username alice --password --authenticationDatabase admin --host mongodb0.examples.com --port 28015 |
Shell操作
mongo shell 是一个完备的Java script 解释器,可以执行任何JavaScript程序。
1 | # 显示数据库列表 |
MongoDB问题记录
创建管理账户: 消除以下告警
1 | Server has startup warnings: |
解决方式
1 | # 第一个警告是因为数据库没有配置管理员密码,创建管理员密码 |
MariaDB(MySQL)
MariaDB数据库的一些常用操作
安装
去官网找到yum
源配置点击这里,执行下面的命令替换为中科大的源
1 | sudo sed -i 's#yum.mariadb.org#mirrors.ustc.edu.cn/mariadb/yum#' /etc/yum.repos.d/mariadb.repo |
示例:
1 | [mariadb] |
执行安装
1 | yum install MariaDB-server MariaDB-client |
启动服务&防火墙设置
1 | # mariadb 10.4 版本使用这个命令启动: systemctl start mariadb |
运行初始化工具
1 | [root@localhost ~]# mysql_secure_installation |
字符集问题
- 修改表和表中左右字段的字符集方法
1
ALTER TABLE <table_name> CONVERT TO CHARACTER SET <character_name>
MySQL实现ROW_NUMBER() OVER (PARTITION BY ORDER BY )
1 | -- 创建测试数据 |
MySQL插入或者更新
在程序中,经常会用到插入或者更新这项功能,来确保数据的唯一性,mysql
支持两种插入更新方式
插入数据,如果存在则更新指定的字段进行更新
1
insert into score_info(id, score, class_name) values(1, 99, 5) on DUPLICATE key update class_name=100, score=55
插入数据,全部更新为新值
1
replace into score_info(id, score, class_name) values(1,1000, 5)
时间转换
- 日期类型转字符串: date 参数是合法的日期。format 规定日期/时间的输出格式。
1
select DATE_FORMAT(date,format) from table
- 字符串时间转日期
1
select str_to_date('2018-08-16 08:00:00','%Y-%m-%d %H:%i:%s')
格式 | 描述 |
---|---|
%a | 缩写星期名 |
%b | 缩写月名 |
%c | 月,数值 |
%D | 带有英文前缀的月中的天 |
%d | 月的天,数值(00-31) |
%e | 月的天,数值(0-31) |
%f | 微秒 |
%H | 小时 (00-23) |
%h | 小时 (01-12) |
%I | 小时 (01-12) |
%i | 分钟,数值(00-59) |
%j | 年的天 (001-366) |
%k | 小时 (0-23) |
%l | 小时 (1-12) |
%M | 月名 |
%m | 月,数值(00-12) |
%p | AM 或 PM |
%r | 时间,12-小时(hh:mm:ss AM 或 PM) |
%S | 秒(00-59) |
%s | 秒(00-59) |
%T | 时间, 24-小时 (hh:mm:ss) |
%U | 周 (00-53) 星期日是一周的第一天 |
%u | 周 (00-53) 星期一是一周的第一天 |
%V | 周 (01-53) 星期日是一周的第一天,与 %X 使用 |
%v | 周 (01-53) 星期一是一周的第一天,与 %x 使用 |
%W | 星期名 |
%w | 周的天 (0=星期日, 6=星期六) |
%X | 年,其中的星期日是周的第一天,4 位,与 %V 使用 |
%x | 年,其中的星期一是周的第一天,4 位,与 %v 使用 |
%Y | 年,4 位 |
%y | 年,2 位 |
常见错误记录
- Root用户远程登录失败
虽然开启了允许远程登录,但是有时候就是莫名其妙登录失败,无法连接数据库1
2
3use mysql; # 进入mysql库
update user set host = '%' where user = 'root'; #这一行执行可能会报错,不用管,直接执行下面这句
FLUSH PRIVILEGES;
Oracle
Oracle数据库的一些常用操作
创建表
1 | CREATE TABLE <库名>.<表名>( |
表字段修改删除添加操作
1 | alter table tablename add (column datatype default,….) -- 添加字段 |
表的相关操作
1 | create table table_name_new as select * from table_name_old where 1=2; -- 只复制表结构,删除where或者条件为True则复制表和表数据 |
时间转换
- to_date(‘string time’, ‘format’) 字符串时间转datetime
1
select to_date('2018-08-23 12:00:00','yyyy-mm-dd hh24:mi:ss') from dual
- to_char(datetime_column, ‘format’) datetime类型转字符串
1
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
格式 | 描述 |
---|---|
YYYY | 四位表示的年份 |
YYY,YY,Y | 年份的最后三位、两位或一位,缺省为当前世纪 |
MM | 01~12的月份编号 |
MONTH | 九个字符表示的月份,右边用空格填补 |
MON | 三位字符的月份缩写 |
WW | 一年中的星期 |
D | 星期中的第几天 |
DD | 月份中的第几天 |
DDD | 年所中的第几天 |
DAY | 九个字符表示的天的全称,右边用空格补齐 |
HH,HH12 | 一天中的第几个小时,12进制表示法 |
HH24 | 一天中的第几个小时,取值为00~23 |
MI | 一小时中的分钟 |
SS | 一分钟中的秒 |
SSSS | 从午夜开始过去的秒数 |
获取分组后记录详细数据
举例: 按表的A字段分组,获取B字段最大值的那条记录的其他信息
使用场景: 某表按场景id进行分组,现要求获取分组后最大值的详细记录时间,这时候就无法使用group by
进行分组了,因为group by
分组可以获取最大时间,但是无法获取这个最大时间对应的时间,使用max(time)
是错误的,如果把时间加入group by
会造成分组失败.
1 | select * from (select t.*, row_number() OVER (PARTITION BY <COL_1> ORDER BY <COL_2 desc) rn from (<TABLE>) t )where rn=1 |
SQL说明: 按<TABLE>
中的COL_1
进行分组,然后按COL_2
进行排序,升序或者降序并赋予行号, 最后外层SQL直接获取行号为1的就是最上面的一条结果
对数据进行分组计算
使用OVER(PARTITION BY 字段名)
,按照指定字段对数据进行分组,然后计算.例如获取一个月每天的数据之和
1 | select |
多张表同时关联
举例: 以A表为准left join
B表,C表,D表….
1 | select * from |
锁表查询和解锁
查询因为某种原因操作,表被锁死,只能查询无法插入数据,无法删除数据的问题
1 | -- 查询锁表信息 |
建立自增序列
Oracle并没有像MySQL一样的自增属性,只能通过建立序列和触发器来实现这个功能
- 序列参数说明
1
2
3
4
5
6INCREMENT BY: 指定序列号之间的间隔,该值可为正的或负的整数,但不可为0。序列为升序。忽略该子句时,缺省值为1。
START WITH:指定生成的第一个序列号。在升序时,序列可从比最小值大的值开始,缺省值为序列的最小值。对于降序,序列可由比最大值小的值开始,缺省值为序列的最大值。
MAXVALUE:指定序列可生成的最大值。
NOMAXVALUE:为升序指定最大值为1027,为降序指定最大值为-1。
MINVALUE:指定序列的最小值。
NOMINVALUE:为升序指定最小值为1。为降序指定最小值为-1026。 - 建立序列
1
2
3
4
5create sequence computer_room_id
increment by 1
start with 1
minvalue 1
maxvalue 999999999 - 修改序列
1
2
3
4alter sequence computer_room_id
increment by 2
minvalue 1
maxvalue 999999999 - 使用序列
1
2
3
4-- 获取
select computer_room_id.Nextval from dual
-- 插入时获取
insert into computer_room_info(id, city_name, room_name) values(computer_room_id.Nextval,'city','room')
同时插入多条数据
Oracle的insert和MySQL有点区别,insert后面的values不能写多个
1 | INSERT all |
带条件的INSERT ALL
1 | INSERT ALL |
插入或者更新
从T2表获取数据插入或者更新T1表的内容
1 | MERGE INTO TEST T1 |
字符串截取
- substr函数
1
2substr(截取字符, 开始位置, 结束位置)
substr(截取字符, 开始位置) -- 默认结束位置为末尾 - instr函数
1
2instr(原字符串, 目标字符串) -- 获取字符的开始位置
instr(原字符串, 目标字符串, 开始位置, 第几次出现)
字符串截取示例name
大概是这样子zhangsan_123
,以下sql截取出名字,使用substr和instr
1 | select * from tableName where substr(name, 0, instr(name, '_') - 1) = 'zhangsan' |
常见错误记录
- ORA-00932: 数据类型不一致: 应为 -, 但却获得 CLOB
数据库字段为clob
类型,直接where clob_column='string'
查询时,数据库抛出本错误.1
select * from table where to_char(clob_column)='string'