GBase8a命令
iceberg命令
dm命令
pg命令
mysql命令
gauss命令
表编辑
视图编辑
存储过程编辑
常用命令
1、查询所有的数据库：show databases;
2、查询当前所在数据库：show current databases;
2、查询当前数据库下所有的表：show tables;
3、切换/使用特定的数据库：use + 数据库名;
4、创建数据库：create database + 数据库名;
5、删除数据库：drop database + 数据库名;
内部表的话采用上面的命令行可以实现彻底删除；
如果是外部表，仅drop的话只能删掉表的结构，数据依然在HDFS上。
如果想彻底删掉外部表，需要在drop前先执行命令：ALTER TABLE xxx SET TBLPROPERTIES('EXTERNAL'='False');
6、查看表中有哪些字段：desc + 表名;
查看更详细的表字段信息：desc extended / formatted + 表名; （这里formatted格式上会更规整好看）
7、查看建表语句：show create table + 表名;
删除表数据
drop：删除表定义及其所有数据；
truncate：删除表中的所有数据，但表结构及其列、约束、索引等都保持不变，向表中再次添加数据时，自增字段的初始值重新从1开始；
delete：删除表中部分数据；
查询表数据量
count(1)、count(*) 返回表格中所有存在的行的总数包括值为null的行
count(列名) 返回结果中除去null以外的所有行的总数
distinct 列名 得到的结果是除去值为null和重复数据后的结果。
count(1)与count(*) 查询效率比较基本没差别，count(*)自动会优化指定到哪一个字段
count(主键) 执行效率最优
表结构变更(部分仅mysql使用)
drop table tableName; 删除表
alter table oldName rename to newName; 修改表名
desc tableName; 查看表结构
alter table T drop column sex; 删除列
alter table T add column sex varchar(50) not null default ''; 添加一列
alter table T rename column sex to sexA; 修改列名
alter table T modify column sex char(2); 修改列类型
alter table T change column sex sexA int; 修改列名及类型
alter table tableName add primary key(id); 添加主键
alter table tableName drop primary key; 删除主键
alter table tableName add primary key(column_list); 添加主键
alter table tableName drop primary key; 删除主键
create unique index indexName on tableName(name); 创建索引
drop index indexName; 删除索引
sql常用函数
sum(col) 所有列值之和
max(col) 列的最大值
min(col) 列的最小值
avg(col) 列的平均值
count(col) 列非NULL值个数
截取字段的值的子串，可越界，如下含义是获取从第2个字符开始的3个字符
select substr('1234', 2, 3) ->> 234
获取字段长度函数
length('1') 获取字段的字符的长度（一个汉字算三个字符，一个数字/字母算一个字符）
char_length('1') 获取字段的字符的长度（不管是一个汉字、数字、字母都算是一个字符）
Lower('aA')--》aa, Upper('aA')--》AA （适合Oracle、mysql、SQL Server）
Lcase('aA')--》aa, Ucase('aA')--》AA： （适合mysql）
创建表 pg主键自增长
create sequence if not exists table_name_id_seq;
create table if not exists table_name
(
id integer default nextval('table_name_id_seq'::regclass),
name varchar(32),
primary key (id)
);
comment on column table_name.id is '自增主键';
comment on column table_name.name is '名称';
comment on table table_name is '表';
创建表 pg、oracle、dm
create table table_name (
id Integer not null,
name varchar(32)
);
comment on column table_name.id is '主键';
comment on column table_name.name is '名称';
comment on table table_name is '表';
alter table table_name add constraint table_name_pkey primary key(id);
create index idx_table_name_i1 on table_name(name);
查询字段截取子串的最大值，适合dm、oracle、pg、mysql、gp等等
select * from
(SELECT substr(code,1,2) as column_value FROM table_name) t
where column_value is not null
and length(column_value)=2
order by column_value desc
pg数据库序列操作
https://www.sjkjc.com/postgresql-ref/lastval/ 参考地址
drop sequence if exists my_sequence; 删除序列
create sequence my_sequence; 创建序列
create sequence my_sequence start 100; 创建序列并设置起始值
select nextval('my_sequence'); 获取序列值
SELECT setval('my_sequence', 200); 设置序列值
select currval('my_sequence'); 获取当前序列值，需要有调用过nextval()函数，否则会报错
创建/修改序列
create/alter sequence "user_id_seq"
start with 1 --开始值
increment by 1 --每次新增数
no minvalue --无最小值
no maxvalue --无最大值
cache 1; --缓存序列数量
常用数据库字段类型对比
dm:
--> binary、blob
int、integer --> number(10)
--> bigint、number(20)
--> float、double、decimal
char、varchar --> varchar2、text
date、timestamp --> time
mysql：
--> binary、blob、longblob
--> bigint
int、integer
--> float、double、decimal
date、timestamp --> time、datetime、
char、varchar --> text、longtext
oracle:
--> binary_float、binary_double、blob
int、integer --> number(10)
--> number(20)
--> float、double precision、decimal
date、timestamp
char、varchar --> varchar2、clob
pg:
--> bytea
int、integer
--> bigint、int8
--> float4、float8、double precision、decimal
date、timestamp --> time
char、varchar --> text
gbase8a 数据类型
tinyint
smallint
int
bigint
float
double
decimal
numeric
char(100)
varchar(100)
text
blob
binary
varbinary(100)
longblob
date
datetime
time
timestamp
达梦数据库
-- 报错：没有[V$DM_INI]对象的查询权限
grant select on V$DM_INI to "用户名" --授权sql
-- 查询表名大小写敏感
select case_sensitive
-- 数据库参数查询
ALTER TABLE qa_job_log enable USING long ROW;--报错记录超长时需设置表启用超长记录
SELECT PAGE; --查询页大小
-- json操作
json_value(a.detail,'$.ruleName')
select json_value('{"opening":"Sicilian",
"variations":["pelikan","dragon","najdorf"]}','$.opening')
-- 数组操作
alter table tableName add column "parent_ids" integer[];
insert into tableName (id, parent_ids) VALUES(99999998, ARRAY[1,2,3,4,5])
select * from tableName where parent_ids && array[2]
select array_to_string(parent_ids,',') from tableName where id=99999999;
select regexp_split_to_array(',1,2,3,4,5,',',');
select array_to_string(regexp_split_to_array(',1,2,3,4,5,',','),',')
select regexp_split_to_array(null,',');
select * from tableName where regexp_split_to_array(parent_ids,',') && array['14924']
select regexp_split_to_array(',1,2,3,4,5,',',');
select * from tableName order by id desc limit 100;
postgres
pg表给用户授权
grant all privileges on table tableName to userName;
-- 创建连接
String dbDriver = "org.postgresql.Driver";
String dbUrl = "jdbc:postgresql://127.0.01:5432/dbName";
Properties props = new Properties();
props.put("user", "");
props.put("password", "");
props.put("remarksReporting", "true");
Class.forName(dbDriver);
Connection connection = DriverManager.getConnection(dbUrl, props);
-- 表结构变更sql
alter table qa_job_old_copy1 rename to qa_job; 重命名表名
alter index qa_job_pkey rename to qa_job_old_pkey; 重命名索引
alter sequence qa_job_id_seq rename to qa_job_old_id_seq; 重命名序列
-- pg序列
SELECT currval('table_id_seq');
SELECT lastval()
SELECT setval('table_id_seq', 145)
SELECT nextval('table_id_seq');
-- pg库参数查询
service postgresql-9.6 restart pg重启
SELECT * from pg_stat_activity 查看postgresql的连接数
show max_connections 查看pg最大连接数限制
show idle_in_transaction_session_timeout 查询pg库连接自动断开时间
--清理pg数据库连接：
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity
WHERE datname='highgo' AND pid<>pg_backend_pid();
select pg_terminate_backend(6492); 断开一个pg连接
-- json操作
select * from mdm_act_table_edit_ms_copy1
where data_info::json->>'columnName' like '%namaa%'
-- pg获取主键是否为自增长字段nextval('watermark_seq'::regclass)
select column_name, column_default
from information_schema.columns
where table_catalog = 'dbName'
and table_schema = 'schemaName'
and table_name = 'tableName'
and column_name = 'columnName'
-- 达梦：获取主键是否为自增长字段extract_log_id_seq.nextval
select column_name, data_default
from all_tab_columns
where owner='dbName'
and table_name = 'tableName'
and column_name='columnName'
-- mysql：获取主键是否为自增长字段auto_increment
SELECT `COLUMN_NAME`, `EXTRA`
from `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA`='dbName'
and `TABLE_NAME` = 'tableName'
and `COLUMN_NAME`='columnName'
输出
COLUMN_NAME EXTRA
id auto_increment
-- pg数据库备份与恢复
PGPASSWORD=pwd pg_dump -h 127.0.0.1 -p 5432 -U userName -d dbName -f /backupFile.sql 备份库
PGPASSWORD=pwd pg_dump -h 127.0.0.1 -p 5432 -U userName -d dbName -f /backupFile.sql -n schemaName 备份schema
PGPASSWORD=pwd pg_dump -h 127.0.0.1 -p 5432 -U userName -d dbName -f /backupFile.sql -t tableName 备份表/视图
PGPASSWORD=pwd psql -h 127.0.0.1 -p 5432 -U userName -d dbName -f /backupFile.sql 恢复
-- schema下所有序列
select * from information_schema.sequences
where sequence_schema = 'schemaName'
and sequence_catalog='dbName';
-- schema下所有视图
select * from information_schema.views
where table_schema = 'schemaName'
and table_catalog = 'dbName';
-- schema下的所有表
SELECT * FROM information_schema.tables
where table_schema = 'schemaName'
and table_catalog = 'dbName';
-- 索引占用空间查询
SELECT table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FRO
(SELECT table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes
mysql
-- 1、yum安装mysql
yum install mysql-server -y yum安装
service mysqld start 启动mysql的服务
chkconfig mysqld on 将mysql服务设置成开机启动
mysql 进入到mysql的命令行
在mysql命令行修改mysql密码
--切换数据库
use mysql
--查看表
show tables
--查看表的数据
select host,user,password from user;
--插入权限数据
grant all privileges on *.* to 'root'@'%' identified by '123' with grant option
--删除本机的用户访问权限（可以执行也可以不执行）
delete from user where host!='%'
--刷新权限或者重启mysqld的服务
service mysqld restart; --（重启mysql服务）
flush privileges; --(刷新权限)
-- 2、登录数据库
mysql -hlocalhost -uroot -p
-- 3、mysql命令
show databases; 显示所有数据库
drop database 库名 删除数据库
use 数据库名； 切换数据库
show tables; 显示数据库中的所有表
create database 表名； 创建数据库
desc 表名； 查看表结构
show create table 表名； 查看表定义
help 命令 查看帮助
--备份命令
mysqldump --single-transaction -h 127.0.0.1 -P 3306
-u root -p123456 dbName tableName view_course > /data/rbc/a.sql
--恢复命令
mysql -h 10.25.72.24 -P 3306 -u root -p123456 dbName < /data/rbc/a.sql
--备份修复命令
ln -s /data/mysql/mysql/mysql.sock /var/lib/mysql/mysql.sock
-- 4、复制表结构
create table newstudent like t_student; 复制表结构（创建的表完全一样，包含主键、索引等）
create table newstudent2 select * from t_student where id<4; 复制表结构及数据（创建的表不一样，不包含主键、索引等）
-- 5、复制表数据
insert into 新表 select * from 旧表
insert into 新表(字段1,字段2,...) select 字段1,字段2,... from 旧表
-- 6、删除表
truncate, delete, drop 比较：
truncate table： 删除内容、释放空间但不删除定义。
delete table: 删除内容不删除定义，不释放空间。
drop table： 删除内容和定义，释放空间。
truncatetable在功能上与不带where子句的delete语句相同：二者均删除表中的全部行。但truncatetable比delete速度快，且使用的系统和事务日志资源少。
-- 7、数据备份与恢复
D:\mysql\bin>mysqldump -u root -p123456 mydata > d://mydataback.sql
D:\mysql\bin>mysql -u root -p123456 mydata2 < d://mydataback.sql
source d://sql.txt //加载并执行外部文件中的sql
-- 8、case函数
case [expression to be evaluated]
when [val 1] then [result 1]
when [val 2] then [result 2]
......
when [val n] then [result n]
else [default result]
end
-- 9、数据类型转换函数
select cast(now() as signed integer)
-- 10、加密函数md5
md5(str)
-- 11、日期和时间函数
curdate()或current_date()或date(now()) 2016-09-13
curtime()或current_time()或time(now()) 11:53:17
now() 2016-09-13 11:53:17
date_format(date, '%Y-%m-%d') 2016-09-13
-- 12、json操作
SELECT JSON_EXTRACT(CAST(name as JSON), '$."opening"')
from user_copy1
where JSON_EXTRACT(CAST(name as JSON), '$."opening"') like '%ci%'
-- 13、函数
abs(x) 返回x的绝对值
rand() 返回０到１内的随机值
concat(s1,s2...,sn) 将s1,s2...,sn连接成字符串
lcase(str)或lower(str) 返回将字符串str中所有字符改变为小写
length(s) 返回字符串str中的字符数
ltrim(str) 从字符串str中切掉开头的空格
position(substr,str) 返回子串substr在字符串str中第一次出现的位置
repeat(str,srchstr,rplcstr) 返回字符串str重复x次的结果
reverse(str) 返回颠倒字符串str的结果
rtrim(str) 返回字符串str尾部的空格
trim(str) 去除字符串首部和尾部的所有空格
-- 14、没有数据插入，有数据更新的sql语句
INSERT INTO telesale.t_tel_return_count(custId, userId, visitTag, createTime, updateTime)
VALUES(#{item}, #{loginId}, 9, now(), now())
onduplicatekeyupdate visitTag=9, userId=#{loginId}, updateTime=now()
-- 15、sql的执行顺序
查询中用到的关键词主要包含六个，他们书写的顺序依次为
select--from--where--group by--having--order by
其中select和from是必须的，其他关键词是可选的，这六个关键词的执行顺序
与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行
from--where--group by--having--select--order by,
from:需要从哪个数据表检索数据
where:过滤表中数据的条件
group by:如何将上面过滤出的数据分组
having:对上面已经分组的数据进行过滤的条件
select:查看结果集中的哪个列，或列的计算结果
order by :按照什么样的顺序来查看返回的数据
-- 16、三范式
1NF：列具有原子性，不可再分解，即列的信息，不能分解，只有是关系型数据库，自动满足
2NF：表中记录是唯一的，就满足2NF，通常我们设计一个主键来实现
3NF：表中不要有冗余数据，就是说，如果能够被推导出来，就不应该单独设计一个字段来存放
-- 17、show status所有的mysql数据库参数
show status like 'uptime' mysql工作时间
show [session | global] status like 'com_select' 执行insert语句的数量
show [session | global] status like 'com_delete'
show [session | global] status like 'com_update'
show [session | global] status like 'com_insert'
show status like 'connections' 查询连接数
show status like 'slow_queries' 显示慢查询次数
-- 18、show variables 变量参数
定位慢查询： 构建大表（400万数据）存储过程构建
默认情况下，mysql任务10秒才是慢查询
显示慢查询时间：show variables like 'long_query_time'
更改慢查询时间：set long_query_time=1;
如果要查看慢查询，可以生产慢查询日志文件
-- 19、explain解释
id --查询序列号
select_type --查询类型
table --查询的表名
type --扫描的方式，all全表扫描
possible_keys --可能使用到的索引
key --实际使用到的索引
key_len
ref
rows --该语句扫描了多少行，或可能检索多少行记录
Extra --sql语句的额外信息，比如排序方式
type类型：从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
explain 可以帮助我们不真正执行某个sql语句时，就知道怎样执行，有利于分析sql，进行优化
-- 20、查看索引的使用情况
show status like 'Handler_read%'
Handler_read_key 值越大越好，表示使用到的次数
Handler_read_rnd_next 值越小越好，说明查询率高
-- 21、数据库存储引擎
MyIsam：对事物要求不高，同时是以查询和添加为主，比如：回复表、发帖表
Innodb：对事物要求高，保存的都是重要数据，比如：订单、账户表
Memory：数据变化非常频繁，不需要入库，同时又频繁的查询和修改（数据在内存里面存储）
myIsam 与 Innodb的区别
1、事物安全
2、查询和添加速度
3、支持全文索引
4、锁机制
5、外键 MyIsam 不支持外键，INNODB 支持外键
如果数据库引擎是myIsam，一定要定时进行碎片整理
因为删除数据时，数据库磁盘就不会减少
碎片整理语句： optimize table 表名
-- 22、表的水平分割
分表的原则：表结构不发生变化，例如分三张表：id%3=值，数据放入第几张表
例如：三种表：table0、table1、table2
id%3=0的数据放入表table0
id%3=1的数据放入表table1
id%3=2的数据放入表table2
添加数据时，编号id应该提前确认，通常会借助辅助表来生成编号id
查询时：select * from table+id%3
重要是：分表的标准、规则
查询时限制条件（可以查询定位到一张分表）
提供检索的是，应该业务的需求，找到分表的标准并在检索界面，约束用户的检索方式->分页，如果有大表检索的需求，也是少数
-- 23、表的垂直分割
把某个表的某些字段，这些字段，在查询时，并不是特别关心，但是数据量很大，我们建议大家可以，把这些字段单独放到另外一张表中，从而提高查询效率
特点：单独建表，建立关联关系，会改变表结构
单个字段数内容量太大，会拖垮查询速度，并且这个字段对查询结果不太关心的，可以新建一张这个字段的表
-- 24、代码连接
// 连接Mysql的方式
String driver = "com.mysql.jdbc.Driver";
String url =
"jdbc:mysql://localhost:3306/jxcgl?useUnicode=true&characterEncoding=utf-8";
String uid = "rbc";
String pwd = "rbc";
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, uid, pwd);
} catch (Exception e) {
e.printStackTrace();
}
try {
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("select * from student");
while (rs.next()) {
System.out.println(rs.getString(1) + " " + rs.getString(2) + "" +
rs.getString(3) + " " + rs.getString(4));
}
//给存储过程赋值并执行存储过程
CallableStatement c = conn.prepareCall("{call zz(?,?,?)}");
c.setInt(1, 60);
c.registerOutParameter(2, Types.VARCHAR); //给存储过程返回值赋值的
c.registerOutParameter(3, Types.VARCHAR);
c.execute();
System.out.println(c.getString(2) + " " + c.getString(3));
} catch (Exception e) {
e.printStackTrace();
}
-- 25、mysql8连接报错 the driver has not received any packets from the server
在url后面加useSSL=false立马就可以连接上了
通过查找资料发现在高版本的MySQL里面是需要指明useSSL为true或者false的。
设置数据库连接的超时时间
数据库设置统计的连接超时时间，没设置的话，使用的数据库的默认超时时间，像pg的默认超时是10秒，mysql的是20秒，设置超时时间是参数：DriverManager.setLoginTimeout();
pg数据库关联表更新数据语法
update A a
SET name=b.name
from B b
WHERE a.t_id=b.t_id
国产化数据库迁移步骤
数据库版本确认，人大金仓版本需要支持PG、MySQL的兼容模式；
数据库字符集与编码确认，需设置和原库字符集、大小写敏感一致；
安装国产化操作系统及安装人大金仓数据库；
人大金仓数据库数据化，创建新库及模式；
安装官方迁移工具KDTS；
使用KDTS工具迁移，配置原库目标库信息及勾选需迁移的表视图函数并配置数据差异类型映射；
启动迁移任务，监控进度及日志，处理失败记录（如主键冲突、类型不匹配）；
迁移后数据一致性检查（行数对比、抽样检查关键字段数据）；
业务功能测试（运行核心业务SQL，对比结果是否一致）；
业务系统使用人大金仓库看系统是否正常；
浙公网安备 33010602011771号