Sql

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
intinteger         --> number(10)
                     --> bigint、number(20)
                     --> float、double、decimal
charvarchar        --> varchar2、text
datetimestamp      --> time


mysql:
                     --> binary、blob、longblob
                     --> bigint
intinteger
                     --> float、double、decimal
datetimestamp      --> time、datetime、
charvarchar        --> text、longtext


oracle:
                     --> binary_float、binary_double、blob
intinteger         --> number(10)
                     --> number(20)
                     --> float、double precision、decimal
datetimestamp
charvarchar        --> varchar2、clob


pg:
                    --> bytea
intinteger
                    --> bigint、int8
                    --> float4、float8、double precision、decimal
datetimestamp     --> time
charvarchar       --> 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()                         返回0到1内的随机值
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
posted @   rbcd  阅读(257)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)
点击右上角即可分享
微信分享提示