标签：常用 01 草稿 -- 数据库 mysql SQL 类型 col

 

仅针对mysql、sqlserver、oracle、人大金仓、达梦

数据库

数据库是一组文件，位于磁盘，用于存储数据

表名/字段规范

/*
表名/字段长度：
Oracle：最多30个字符
SQL Server：最多128个字符
MySQL：最多64个字符
​
表名规范：
1.以字母开头，只能使用A-Z、a-z、0-9、[#$不建议]
    MySQL中可以以数字开头，但不提倡；Oracle、SQL Server
2.除了"_" 下划线可以加，其它特殊字符一律不能加；
3.如果带其它特殊字符，能创建；但无法通过sql进行删除；如“test++table”
​
Oracle、SQL Server在创建表时，表名会自动转大写，Oracle对表名大小写不敏感
如果在定义表名时含有特殊符号，或者用小写字母来定义表名则需要在表名两侧添加双引号
*/

查询

/*
1.sql大小写不敏感
2.表名大小写不敏感(前提是按规范创建才行，如oracle、达梦的表名规范是大写)
*/
create table "test_sex_02" ( -- 如oracle这样创建，则创建出来时小写的，查询时，*大小写敏感*,且查询时候要带双引号去查询
    "id" NUMBER(10,0),
    sex char(5),
    age NUMBER(3,0)
)

查询数据库版本

# MySQL
select version()
select @@version
​
# SQL Server
select @@version
​
# Oracle、达梦
SELECT * FROM v$version
​
# 人大金仓
select version()

获取系统时间

# MySQL、人大金仓
select now()
​
# SQL Server
select getdate()
​
# Oracle、达梦
select sysdate from dual;  -- dual表是一个单行单列的虚拟表。
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;  -- 将date类型转换成字符串
​
​
/*
oracle的dual表是一个单行单列的虚拟表、常用在没有目标表的Select语句块中
如：MySQL中可以直接通过select进行计算，select 1 + 1，
但再oracle就直接报错："SQL 错误 [923] [42000]: ORA-00923: 未找到要求的 FROM 关键字"
select 1 +1 from dual;
*/

单双引号

Oracle、SQL Server、达梦、人大金仓 中对字段字符类型的值修改必须使用单引号，MySQL对引号没有限制

字段约束(constraint)

sqlserver和oracle、mysql声明约束时，推荐使用constraint关键字声明

约束是保证数据准确性的最后一道屏障，保证了表中的数据有效性

约束分类：

• 列级约束：只能作用在一个列上，跟在列的定义后面

• 表级约束：可以作用在多个列上，不与列一起，而是单独定义

--
create table t_test_01(mysql_id int not null primary key auto_increment)

默认约束(default)

给字段指定默认值

CREATE TABLE `t_test_03` (
  `mysql_id` int(11) NOT NULL AUTO_INCREMENT,
  `mysql_varchar` varchar(30) DEFAULT '默认值',
  PRIMARY KEY (`mysql_id`)
) ENGINE=MyISAM

非空约束（not null）

特点

• 默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型

• 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空

• 一个表可以有很多列都分别限定了非空

create table test_t_01(my_int int not null) # 列级约束
​
create table test_t_03 (
    my_int int,
    my_int02 int,
    unique (my_int,my_int02)  # 表级约束
)

唯一性约束（unique）

• 唯一约束可作用在单列或多列上，对于这些列或列组合，唯一约束保证每一行的唯一性

• 唯一性约束的字段不能重复，但可以为null且允许出现多个空值

特点

• 同一个表可以有多个唯一约束。

• 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。

• 唯一性约束允许列值为空。

• 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。

• MySQL会给唯一约束的列上默认创建一个唯一索引。

create table 表名称(
    字段名  数据类型,
    字段名  数据类型  unique,  
    字段名  数据类型  unique key,   # 列级约束
    字段名  数据类型
);
​
create table 表名称(
    字段名  数据类型,
    字段名  数据类型, 
    [constraint 约束名] unique key(字段名1,字段名2...)   # 表级约束
);

not null 和 unique联合使用

/* 
在MySQL当中，如果一个字段同时被not null和unique约束的话，该字段自动会设置成主键字段
在Oracle和sqlserver中，不会被当做主键
*/
create table test_t_02 (my_int int unique not null)
desc test_t_02    # 可以看出my_int是PRI，被设置成了主键
​
# MySQL当中如果多个字段同时这也约束的话，因为一个表中只能允许一个主键，所以后面的my_int02字段会被定义为unique字段 且不能为空，但不是主键了
create table test_t_03 (my_int int unique not null,my_int02 int unique not null)
​

主键约束

特点

• 主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值

• 一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建

• 主键约束对应着表中的一列或者多列（复合主键）

  • 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复

# 声明主键时区别
# MySQL中的主键声明使用PRIMARY KEY关键字，Oracle、sqlserver中的主键声明使用CONSTRAINT关键字。
​
create table 表名称(
    字段名  数据类型  primary key, #列级约束
    字段名  数据类型
);
create table 表名称(
    字段名  数据类型,
    字段名  数据类型,  
    [constraint 约束名] primary key(字段名) #表级约束
);
​
​
​
CREATE TABLE `t_test_03` (
  `mysql_id` int(11) NOT NULL AUTO_INCREMENT,
  `mysql_varchar` varchar(30) DEFAULT '默认值',
  constraint mysql_t_test_03_pk PRIMARY KEY (`mysql_id`)
) 
​
​
#  复合主键
create table 表名称(
    字段名  数据类型,
    字段名  数据类型,  
    primary key(字段1，字段2...)  # 字段1、字段2、字段...的组合唯一
);

外键约束(foreign key)

外键约束：不能跨存储引擎使用

/*
父表：被引用的是父表
子表：引用的是子表
​
引用父表字段注意事项：
1、子表中的外键引用父表中的某个字段，被引用的这个字段不一定要求是主键，但至少具体unique约束的。如引用的外键重复了，那引用的到底是哪个，就不清楚；
2、外键可以为null
*/
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样
​
create table 从表名称(
    字段1  数据类型  primary key,
    字段2  数据类型,
    [CONSTRAINT 外键约束名称] FOREIGN KEY（子表字段) references 父表(字段)
);
​
create table t_class( # 父表
    class_no int primary key,
    classname varchar(255)
);
​
create table t_student( # 子表
    student_no int primary key auto_increment,
    name varchar(255),
    cno int comment '定义的外键,引用t_class表主键',
    
                    # FOREIGN KEY: 子表中的字段    REFERENCES: 父表中的字段
    CONSTRAINT t_student_fk foreign key(cno) references t_class(class_no)
)
​
insert into t_class values(3,'浙江省杭州实验小学一班')
insert into t_student values (3,'zhangsan',4)
​
​
当创建外键约束以后，做删除操作的时候要留意
​
比如要删除父表中的某条数据，但某个子表中又有对该条数据的引用，这时就会导致删除失败
​
删除时，可选方式
​
    -- 删除的时候，级联删除掉子表中的所有匹配行，在创建外键时，通过 on delete cascade子句指定外键可级联删除
​
    -- 删除父表中的对应行，会将对应子表中的所有匹配行的外键约束列置为NULL，通过on delete set null子句实施
​
    -- 默认，强制不让删除

检查约束（check）

检查约束：可以确保数据的完整性和一致性，限制满足特定条件的数据插入或更新 就是自定义的，如age这个字段的值定义了取值范围为1-100或某个字段的值定义了默认值

-- MySQL8版本以下不支持检查约束
-- sqlserver、oracle中支持
​
# 检查约束只对新插入的数据和更新的数据进行检查，不会对已存在的数据进行检查。如果需要对已存在的数据进行检查，可以使用ALTER TABLE语句来添加约束，并使用ENABLE VALIDATE选项来验证已存在的数据。
​
# oracle
CREATE TABLE T_T_01(
    ORA_INT NUMBER(8,0) CHECK(ORA_INT >1 AND ORA_INT <10),
    ORA_INT2 NUMBER(3,0) PRIMARY KEY, 
    CONSTRAINT T_T_01_pk PRIMARY KEY(ORA_INT2)
    -- 推荐都定义为表级约束
    -- CONSTRAINT check_value_range CHECK(ORA_INT >1 AND ORA_INT <10)
)
​
​
# sqlserver
create table sql_t_0001 (
    sql_int int, 
    sql_int_02 int ,
    constraint check_value_range check(sql_int_02 > 0),
    constraint sql_pk_01 primary key(sql_int)
    )
    
# mysql
create table new_t_01 (
    my_int int,
    my_int2 int,
    constraint mysql_pk primary key (my_int),
    constraint mysql_unique_value unique(my_int2)
)

 

数据类型区别

MySQL

默认用户：root

默认端口：3306

类型	类型举例
整数类型	tinyint、smallint、mediumint、int或integer、bingint
小数类型	float、double、decimal
位类型	bit(M) 1 <= M <= 64
字符类型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
布尔类型	boolean 实际上是tinyint(1)的别名
日期型	time、date、datetime、timestamp、year
枚举类型	enum
JSON类型	json(MySQL5.7及以上版本支持)
集合类型	set
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

整型以及对应的属性

计算规则：

一个字节（byte）由8个二进制位（bits）组成，每个二进制位可以表示0或1两个状态。

因此，一个字节可以表示 2^8 种状态，2^8 ==>256个不同的二进制组合

有符号位：数据类型的数据大小范围：-2^（字节数*8-1）~ 2^(字节数*8-1)-1

无符号位：2^8 ==> 0-255

整数类型	字节	有符号数取值范围	无符号数取值范围
TINYINT	1	-128~127	0~255
SMALLINT	2	-32768~32767	0~65535
MEDIUMINT	3	-8388608~8388607	0~16777215
INT、INTEGER	4	-2147483648~2147483647	0~4294967295
BIGINT	8	-9223372036854775808~9223372036854775807	0~18446744073709551615

M

显示宽度，M的取值范围是(0, 255)。如int(5)：当数据宽度小于5位的时候在数字前面需要用字符填满宽度。

注：需要配合“ZEROFILL”使用，表示用“0”填满宽度，否则指定显示宽度无效。

CREATE TABLE test_01(
    field_01 INT,
    field_01 INT(5),
    field_02 INT(5) ZEROFILL
)
insert into test_01 values(1,123,123)
​
mysql> select * from test_01;  # 注：可视化工具查看时，0填充被工具处理了
+----------+----------+----------+
| field_01 | field_02 | field_03 |
+----------+----------+----------+
|        1 |      123 |    00123 |
+----------+----------+----------+
​
/*
设置了显示宽度，那么插入的数据宽度超过显示宽度限制，会不会截断或插入失败？
M只是设置显示宽度，不会对插入的数据有任何影响，还是按照类型的实际宽度进行保存，即`显示宽度与类型可以存储的值范围无关`
从MySQL 8.0.17开始，整数数据类型不推荐使用显示宽度属性。
*/

UNSIGNED

无符号类型（非负），所有的整数类型都有一个可选的属性UNSIGNED（无符号属性），无符号整数类型的最小取值为0

CREATE TABLE test_02(
    field_01 INT,
    field_02 INT unsigned
)
​
show create table test_02
​
CREATE TABLE `test_02` (
  `field_01` int(11) DEFAULT NULL, 
  `field_02` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*
int类型默认显示宽度为int(11)
无符号int类型默认显示宽度为int(10)
注：MySQL8中不再显式范围
*/
​

ZEROFILL

0填充,（如果某列是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性），如果指定了ZEROFILL只是表示不够M位时，用0在左边填充

show create table test_01
CREATE TABLE `test_01` (
  `field_01` int(11) DEFAULT NULL,
  `field_02` int(5) DEFAULT NULL,
  `field_03` int(5) unsigned zerofill DEFAULT NULL  # 默认添加了unsigned属性
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

char与varchar类型

字符串(文本)类型	特点	长度	长度范围
CHAR(M)	固定长度	M	1 <= M <= 255
VARCHAR(M)	可变长度	M	1 <= M <= 65535

• char：

  • CHAR(M) 类型一般需要定义字符串长度。如果不指定(M)，长度默认是1个字符。数据长度不足声明值时，在尾部自动填充空格

  • 如：char(255)，插入时仅插入了'阿'则存储时后面自动填充空格，阿

• varchar：

  • 定义时，必须长度M，否则报错。

decimal

decimal(M,D)

(M,D)中 M=整数位+小数位，D=小数位。 D<=M<=255，0<=D<=30。

create table t_test_04 (decimal_01 decimal(5,2))
/*
若存储时
    整数部分超出了精度范围，MySQL就会报错，不允许存这样的值
    小数点部分若超出范围，则进行四舍五入
*/

text类型

在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，

由于实际存储的长度不确定，text类型不支持作为主键

文本字符串类型	特点	长度	长度范围
TINYTEXT	小文本、可变长度	L	0 <= L <= 255
TEXT	文本、可变长度	L	0 <= L <= 65535
MEDIUMTEXT	中等文本、可变长度	L	0 <= L <= 16777215
LONGTEXT	大文本、可变长度	L	0 <= L<= 4294967295（4GB）

二进制字符串类型

BINARY与VARBINARY类型

BINARY和VARBINARY类似于CHAR和VARCHAR，只是存储的是二进制字符串

二进制字符串类型	特点	值的长度
BINARY(M)	固定长度	M （0 <= M <= 255）字节
VARBINARY(M)	可变长度	M（0 <= M <= 65535）字节

BLOB类型

BLOB是一个二进制大对象，可以容纳可变数量的数据。

注：通常不会在MySQL数据库中使用BLOB类型存储大对象数据

 

二进制字符串类型	值的长度	长度范围	占用空间
TINYBLOB	L	0 <= L <= 255 字节	L + 1 个字节
BLOB	L	0 <= L <= 65535 字节	L + 2 个字节
MEDIUMBLOB	L	0 <= L <= 16777215 （相当于16MB）	L + 3 个字节
LONGBLOB	L	0 <= L <= 4294967295（相当于4GB）	L + 4 个字节

日期类型

datetime

格式上为DATE类型和TIME类型的组合，为YYYY-MM-DD HH:MM:SS，存储占8个字节

可使用current_timestamp() 和 now()函数进行插入

timestamp

显示格式与DATETIME类型相同，但存储的时间范围比DATETIME要小很多，只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间

占4个字节

create table t_test_01 (timestamp_01 timestamp)
​
insert into t_test_01 values('2038-06-07 11:56:31')
​
# SQL 错误 [1292] [22001]: Data truncation: Incorrect datetime value: '2038-06-07 11:56:31' for column 'timestamp_01' at row 1

MySQL主键自增

（1）一个表最多只能有一个自增长列

（2）当需要产生唯一标识符或顺序值时，可设置自增长

（3）自增长列约束的列必须是键列（主键列，唯一键列）

（4）自增约束的列的数据类型必须是整数类型

关键字

auto_increment

INNODB主键不是连续的

自增值保存在内存

自增主键auto_increment默认初始值为1，如果大于max(primary key)+1，在MySQL重启后，会重置auto_increment=max(primary key)+1

CREATE TABLE `t_test_03` (
`mysql_id` int(11) NOT NULL AUTO_INCREMENT,
`mysql_varchar` varchar(30) DEFAULT '默认值',
PRIMARY KEY (`mysql_id`)
) ENGINE=InnoDB AUTO_INCREMENT =100 DEFAULT CHARSET=utf8mb4  # 可以指定初始值

 

create table t_test_01 (
    mysql_id int not null auto_increment primary key,
    mysql_varchar varchar(30)
)
​
# 步骤一：插入4条数据
insert into t_test_01 (mysql_varchar) values('数据库重启前'),('数据库重启前'),('数据库重启前'),('数据库重启前')
+----------+---------------+
| mysql_id | mysql_varchar |
+----------+---------------+
|        1 | 数据库重启前  |
|        2 | 数据库重启前  |
|        3 | 数据库重启前  |
|        4 | 数据库重启前  |
+----------+---------------+
​
# 步骤二：删除id为4的记录
delete from t_test_01 where mysql_id = 4
​
# 步骤三：插入一条数据
insert into t_test_01 (mysql_varchar)values('删除id为4的以后，再插入的')
​
mysql> select * from t_test_01;
+----------+-------------------------+
| mysql_id | mysql_varchar           |
+----------+-------------------------+
|        1 | 数据库重启前              |
|        2 | 数据库重启前              |
|        3 | 数据库重启前              |
|        5 | 除id为4的以后，再插入的     |
+----------+-------------------------+
​
# 步骤四：删除id为5的
delete from t_test_01 where mysql_id = 5
​
# 步骤五：重启数据库以后，再插入一条数据
insert into t_test_01 (mysql_varchar) values('删除id-5重启后插入')
​
mysql> select * from t_test_01;
+----------+-------------------------+
| mysql_id | mysql_varchar           |
+----------+-------------------------+
|        1 | 数据库重启前              |
|        2 | 数据库重启前              |
|        3 | 数据库重启前              |
|        4 | 删除id-5重启后插入         |      # 可以看出，自增是以4开始的，并不是从6开始的
+----------+-------------------------+
​
# 在MySQL8版本以前，对于自增主键的分配规则，是由InnoDB数据字典内部一个`计数器`来决定的，而该计数器只在`内存中维护`，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。
​
# MySQL 8.0将自增主键的计数器持久化到redo-log中。每次计数器发生改变，都会将其写入redo-log中。重启的时候依靠redo log恢复重启之前的值
​

MYISALM

MyISAM引擎的子增值是保存在数据文件.myd中的

/*
​
巴拉巴拉...重复以上INNODB的步骤
​
mysql> select * from t_test_02;
+----------+-------------------------+
| mysql_id | mysql_varchar           |
+----------+-------------------------+
|        1 | 数据库重启前              |
|        2 | 数据库重启前              |
|        3 | 数据库重启前              |
|        6 | 删除id-5重启后插入         |
+----------+-------------------------+
​
​
*/

存储引擎：存储文件格式

MYISALM：速度优先，索引也支持 B+tree、锁是表锁

.frm文件:存储表结构

.myd文件:存储表数据

.myi文件:存储索引

同等数据量的数据，从磁盘上获取数据，MyISAM存储引擎速度要快一些

INNODB：事务优先、索引是B+tree、锁是行锁

.frm文件:存储表结构

.bd文件:存储表数据和索引

root@fac859d9d515:/var/lib/mysql/auto_database# ls |grep -e t_test_01
t_test_01.frm
t_test_01.ibd
root@fac859d9d515:/var/lib/mysql/auto_database# ls |grep -e t_test_02
t_test_02.MYD
t_test_02.MYI
t_test_02.frm
​

测试数据

可直接复制，便于测试使用

create

-- 若为5.7以下版本去除josn字段
​
CREATE TABLE `t_test_01` (
  `mysql_id` int(10) NOT NULL AUTO_INCREMENT,
  `mysql_tinyint` tinyint(1) DEFAULT '0' ,
  `mysql_smallint` smallint(20) DEFAULT NULL ,
  `mysql_mediumint` mediumint(20) DEFAULT NULL COMMENT 'mediumint中整数入湖不支持的类型 ',
  `mysql_int` int(10) DEFAULT NULL ,
  `mysql_bigint` bigint(20) DEFAULT NULL COMMENT '极大整数值',
  `mysql_bit` bit(1) DEFAULT NULL COMMENT '接收布尔值',
  `mysql_float` float(6,2) DEFAULT NULL,
  `mysql_decimal` decimal(14,2) DEFAULT NULL ,
  `mysql_double` double(10,5) DEFAULT NULL,
  `mysql_char` char(255) DEFAULT NULL,
  `mysql_varchar` varchar(255) DEFAULT NULL,
  `mysql_tinytext` tinytext COMMENT '短文本字符串',
  `mysql_text` text COMMENT '长文本数据',
  `mysql_mediumtext` mediumtext COMMENT '中等长度文本数据',
  `mysql_longtext` longtext COMMENT '极大文本数据',
  `mysql_date` date DEFAULT NULL COMMENT '年月日 2020-06-07',
  `mysql_time` time DEFAULT NULL COMMENT 'time入湖不支持的类型 如，22:22:22',
  `mysql_datetime` datetime DEFAULT NULL ,
  `mysql_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'YYYY-MM-DD hh:mm:ss',
  `mysql_year` year(4) DEFAULT NULL COMMENT '入湖不支持类型',
  `mysql_tinyblob` tinyblob COMMENT '不超过 255 个字符的二进制字符串',
  `mysql_blob` blob COMMENT '二进制形式的长文本数据',
  `mysql_mediumblob` mediumblob COMMENT '二进制形式的中等长度文本数据',
  `mysql_longblob` longblob COMMENT '二进制形式的极大文本数据',
  `mysql_binary` binary(255) DEFAULT NULL COMMENT '如同CHAR和VARCHAR，不同的是BINARY和VARBINARY的长度是字节长度而不是字符长度',
  `mysql_varbinary` varbinary(255) DEFAULT NULL ,
  `mysql_real` double DEFAULT NULL,
  `mysql_josn` json DEFAULT NULL,
  `mysql_enum` enum('春','夏','秋','冬','unknow') DEFAULT NULL,
  PRIMARY KEY (`mysql_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

insert

INSERT INTO delete_test_01 (mysql_tinyint,mysql_smallint,mysql_mediumint,mysql_int,mysql_bigint,mysql_bit,mysql_float,mysql_decimal,mysql_double,mysql_char,mysql_varchar,mysql_tinytext,mysql_text,mysql_mediumtext,mysql_longtext,mysql_date,mysql_time,mysql_datetime,mysql_timestamp,mysql_year,mysql_tinyblob,mysql_blob,mysql_mediumblob,mysql_longblob,mysql_binary,mysql_varbinary,mysql_real,mysql_josn,mysql_enum) VALUES
     (0,32767,36,3893281,99848283905,1,777.99,888.77,555.55555,'刘备','蜀汉','整弓复中之。tinytext','整弓复中之。ttext','整弓复中之。mysql_mediumtext','整弓复中之。mysql_longtext','2022-12-16','17:38:00',(select NOW()) ,'2023-02-08 09:26:41',2022,'YmluYXJ5IGRhdGE=','哈哈','yaya','lala','YmluYXJ5IGRhdGE=',0x61,100.3537,'{"age": 18, "name": "songhk", "address": {"city": "HangZhou", "province": "HangZhou"}}','春');

 

Oracle

默认用户：system

默认端口：1521

类型	类型举例
整数类型	integer、number(p,s) s为0
小数类型	float、number(p,s) s不为0
字符类型	char、varchar2、nchar、nvarchar、后跟的是字节

字符类型

字符串(文本)类型	特点	长度	长度范围	单位
CHAR(n)	固定长度	n	1 <= n <= 2000	字节
VARCHAR2(n)	可变长度	n	1 <= n <= 4000	字节
NCHAR(n)	固定长度	n	1 <= n <= 1000	字符
NVARCHAR2(	可变长度	n	1 <= n <= 2000	字符

• char 定长字符类型

  • 未达到指定长度时，自动在末尾用空格补全

• nchar 定长字符类型

  • 定长字符类型，自动在末尾用空格补全

• varchar2 变长字符类型

  • 未达到指定长度时，不自动补全空格

• nvarchar2 变长字符类型

  • 未达到指定长度时，不自动补全空格

NUMBER类型

/*
Number 既可以表示数字类型，也可以浮点类型
​
NUMBER(P,S)
P是Precison的缩写，即精度缩写，表示有效数字的位数，最多不能超过38个有效数字
S是Scale的缩写，即标度(小数点位)缩写；表示小数点数字的位数
*/

• number类型的子类型

  • integer或int是number的子类型，等同于number(38)

  • smallint是number的子类型，等同于number(38)

  • decimal是number(p,s)的子类型，可以使用decimal(p,s)，若p，s未指定，等同于number(38)

INTEGER

/*
INTEGER是NUMBER的子类型，等价于NUMBER(38,0),用来存储整数。若插入、更新的数值有小数，则会被四舍五入
*/

float和real类型

• float(b)，数b表示二进制进度，b的取值范围为[1,126]，默认为126

• real是float(b)的子类型，等同于float(63)

long类型与lob大型对象数据类型

Oracle单张表中，仅有一列可以为long或long raw

字符串(文本)类型	特点	存储范围
long	文本类型	2G
long raw	可变长二进制数据	2G
blob	二进制大型对象	4G
clob	字符大型对象	4G
nclob	存储Unicode类型的数据	4G

/*
LONG：存储变长字符串(超长字符串)，最多达2G的字节数据(2GB是指2千兆字节，而不是2千兆字符)
LONG RAW：能存储2GB的原始二进制数据，可存放多媒体图像声音等
RAW:用于存储二进制或字符类型数据，必须指定长度。这种类型存储的数据不会发生字符集转换，可存放多美媒体图像声音等
​
限制：
1、一个表中只能包含一个 LONG 类型的列。
2、不能索引LONG类型列、不能将含有LONG类型列的表作聚簇、不能对LONG类型列加约束条件（NULL、NOT NULL、DEFAULT除外），如：关键字列(PRIMARY KEY)不能是 LONG 数据类型。
4、不能在SQL*Plus中将LONG类型列的数值插入到另一个表格中,如insert into …select。
5、不能在SQL*Plus中通过查询其他表的方式来创建LONG类型列,如create table as select。
6、LONG类型列不能用在Select的以下子句中：where、group by、order by，以及带有distinct的select语句中。
7、LONG类型列不能被SQL函数所改变，如：substr、instr。
*/

日期

date、timestamp、以及带时区的时间类型

• date

  • 存储年月日时分秒，精确到秒

• timestamp

  • 时间戳，精确到纳秒

• 区别

  • 都是表示年月日时分秒，但timestamp类型不仅可以保存日期和时间，还能保存 小数位，小数位可以指定0-9，默认为6位 小数位，小数位可以指定0-9，默认为6位

# 获取当前日期和时间
SELECT SYSDATE FROM dual;
SELECT CURRENT_DATE FROM dual;
SELECT CURRENT_TIMESTAMP FROM dual;  # 带时区  2023-06-08 16:50:56.494 +0800
 
​
-- 使用函数to_date(),to_timestamp()将字符串转换为日期格式
​
UPDATE ORACLE_MAP_MSYQL_01 SET ORACLE_DATE  = TO_DATE('2023-05-15','yyyy-mm-dd')
​
UPDATE ORACLE_MAP_MSYQL_01 SET ORACLE_TIMESTAMP  = TO_TIMESTAMP('2023-05-15 18:21:12','yyyy-mm-dd hh24:mi:ss')

oracle创建用户流程

sys/system用户下才有此权限

-- 1、先查询表空间所在的存储位置 
select * from dba_data_files
​
-- 2、创建永久表空间(permanent online) 永久表空间的引号要去除掉
create tablespace '永久表空间名' datafile '文件路径\文件名.dbf' size "空间大小" 15M autoextend on next 10M permanent online;
-- 例：
create tablespace TEST_USER_SPACE datafile '/u01/app/oracle/oradata/test_user_T_SPACE_01.dbf' size 15G autoextend on next 1G permanent online;
​
​
-- 创建用户并指定表空间
create user "用户名" identified by "密码" default tablespace "表空间"
​
-- 例：
create user test_user identified by 123456 default tablespace TEST_USER_SPACE
-- 给用户授予dba权限（创建用户以后要及时分配权限）
grant dba to TEST_CLOUD;

扩大表空间

# 查询表空间是否开启了自动扩展，autoextensible列 yes开启，no未开启
select * from dba_data_files
​
# 开启自动扩展
alter database datafile '/opt/oracle/oradata/SID/logminer_tbs35.dbf' autoextend on next 10G permanent online;
​
# 查询表空间所在位置
SELECT tablespace_name,file_id,file_name,round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name;
​
# 增加文件数量
alter tablespace 表空间名称 add datafile '/opt/oracle/oradata/SID/logminer_tbs17.dbf' size 5G;
​

 

测试数据

可直接复制，便于测试使用

create

CREATE TABLE "T_AUTO_API_01" 
   (    "ORA_NUMBER_38" NUMBER, 
    "ORA_TINYINT" NUMBER(3,0), 
    "ORA_SMALLINT" NUMBER(*,0), 
    "ORA_MEDUIMINT" NUMBER(7,0), 
    "ORA_INT" NUMBER(10,0), 
    "ORA_BIGINT" NUMBER(20,0), 
    "ORA_BOOLEAN" NUMBER(1,0), 
    "ORA_INTGER" NUMBER(*,0), 
    "ORA_DECIMAL" NUMBER(10,3), 
    "ORA_FLOAT" FLOAT(126), 
    "ORA_REAL" FLOAT(63), 
    "ORA_NUMBER_FLOAT" NUMBER(10,3), 
    "ORA_CHAR" CHAR(100), 
    "ORA_VARCHAR" VARCHAR2(255), 
    "ORA_NCHAR" NCHAR(255), 
    "ORA_NARCHAR2" NVARCHAR2(255), 
    "ORA_DATE" DATE, 
    "ORA_TIMESTAMP" TIMESTAMP (6), 
    "ORA_LONG" LONG, 
    "ORA_RAW" RAW(100), 
    "ORA_BLOB" BLOB, 
    "ORA_CLOB" CLOB, 
    "ORA_NCLOB" NCLOB, 
     CONSTRAINT "T_AUTO_API_01_PK" PRIMARY KEY ("ORA_NUMBER_38")
   );

insert

INSERT INTO "T_AUTO_API_01" (
   "ORA_NUMBER_38",
   "ORA_TINYINT",
   "ORA_SMALLINT",
   "ORA_MEDUIMINT",
   "ORA_INT",
   "ORA_BIGINT",
   "ORA_BOOLEAN",
   "ORA_INTGER",
   "ORA_DECIMAL",
   "ORA_FLOAT",
   "ORA_REAL",
   "ORA_NUMBER_FLOAT",
   "ORA_CHAR",
   "ORA_VARCHAR",
   "ORA_NCHAR",
   "ORA_NARCHAR2",
   "ORA_DATE",
   "ORA_TIMESTAMP",
   "ORA_LONG",
   "ORA_RAW",
   "ORA_BLOB",
   "ORA_CLOB",
   "ORA_NCLOB"
) VALUES (
   1,
   127,
   32767,
   9999999,
   2147483647,
   9223372036854775807,
   1,
   1234567890,
   1234.567,
   1.23456,
   1.23456,
   1234.567,
   'Sample Char Value',
   'Sample Varchar Value',
   'Sample NChar Value',
   'Sample NVarchar Value',
   TO_DATE('2023-06-11', 'YYYY-MM-DD'),
   TO_TIMESTAMP('2023-06-11 12:34:56.000000', 'YYYY-MM-DD HH24:MI:SS.FF'),
   'Sample Long Value',
   '73616D706C65205241572076616C7565',
   HEXTORAW('010111'),
   'sdfsd',
   'sdflksh'
);
​

 

SQL Server

默认用户：sa

默认端口：1433

类型	类型举例
整数类型	tinyint、smallint、int、bingint
小数类型	float、real、decimal(p,s)、numeric(p,s)
位类型	bit(M) 1 <= M <= 64
字符类型	char、varchar、nchar、nvachar、text、ntext
布尔类型	boolean 实际上是tinyint(1)的别名
日期型	time、date、smalldatetime、datetime、datetime2、datetimeoffset
货币类型	money、smallmoney
JSON类型	json(SQL Serever2016(13.x及以上版本支持 )
集合类型	set
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

字符类型

• char(n)

  • 定长字符串

• varchar(n)

  • n为存储字符的最大长度，其取值范围是1~8000

• nchar(n)

  • n个字符的固定长度Unicode字符数据。n值必须在1~4000之间

• nvarchar(n)

  • 与varchar类似，存储可变长度Unicode字符数据。n值必须在1~4000之间

• text

• ntext

货币类型

• money

  • -922,337,203,685,477.5808 到 922,337,203,685,477.5807

• smallmoney

  • -214,748.3648 到 214,748.3647

二进制类型

• binary(n)

  • 长度为n个字节的固定长度二进制数据，其中n是从1~8000的值。存储大小为n个字节。在输入binary值时，必须在前面带0x,可以使用0xAA5代表AA5，如果输入数据长度大于定于的长度，超出的部分会被截断。

• varbinary(n)

  • 可变长度二进制数据。其中n是从1~8000的值，在存储时实际值的长度使用存储空间.

日期

• time

  • 12:35:29.1234567，仅存储时间。精度为 100 纳秒

• date

  • 仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。

• smalldatetime

  • 从 1900 年 1 月 1 日 到 2079 年 6 月 6 日，精度为 1 分钟。

• datetime

  • 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 3.33 毫秒。

• datetime2

  • 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 100 纳秒。

• datetimeoffset

  • 与 datetime2 相同，外加时区偏移。

测试数据

create

CREATE TABLE t_sql_auto_01 (
    SQL_ID int NOT NULL,
    SQL_BIT bit NULL,
    SQL_SMALLINT smallint NULL,
    SQL_TINYINT tinyint NULL,
    SQL_DECIMAL decimal(38,5) NULL,
    SQL_NUMERIC numeric(38,5) NULL,
    SQL_MONEY money NULL,
    SQL_SMALLINTMONEY smallmoney NULL,
    SQL_FLOAT float NULL,
    SQL_REAL real NULL,
    SQL_DATE date NULL,
    SQL_DATETIME datetime NULL,
    SQL_DATETIME2 datetime2(0) NULL,
    SQL_TIMESTAMP timestamp DEFAULT yyyy-mm-dd hh-mm-ss NULL COMMENT '和Oracle的long类型一样，每个表只能有一个 timestamp 变量',
    SQL_UNIQUEIDENTIFIER uniqueidentifier NULL,
    SQL_TIME time(0) NULL,
    SQL_DATIETIMEOFFSET datetimeoffset NULL,
    SQL_CHAR char(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    SQL_VARCHAR varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    SQL_TEXT text COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    SQL_NCHAR nchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    SQL_NVARCHAR nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    SQL_NTEXT ntext COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    SQL_BINARY binary NULL,
    SQL_BIGINT bigint NULL,
    SQL_VARBINARY varbinary(100) NULL,
    SQL_IMAGE image NULL,
    CONSTRAINT t_sql_auto_01_PK PRIMARY KEY (SQL_ID)
);

insert

insert select 复制插入时，删除timestamp字段

INSERT INTO t_sql_auto_01 (SQL_ID,SQL_BIT,SQL_SMALLINT,SQL_TINYINT,SQL_DECIMAL,SQL_NUMERIC,SQL_MONEY,SQL_SMALLINTMONEY,SQL_FLOAT,SQL_REAL,SQL_DATE,SQL_DATETIME,SQL_DATETIME2,SQL_UNIQUEIDENTIFIER,SQL_TIME,SQL_DATIETIMEOFFSET,SQL_CHAR,SQL_VARCHAR,SQL_TEXT,SQL_NCHAR,SQL_NVARCHAR,SQL_NTEXT,SQL_BINARY,SQL_BIGINT,SQL_VARBINARY,SQL_IMAGE) VALUES
     (3,0,100,77,100.23450,77.23450,88.9977,99.9977,10.1234,20.1234,'2023-01-09','2023-01-09 17:35:29.123','2023-01-09 17:35:29.000',newid() ,'17:35:29','2023-05-08 12:35:29.1234567 +12:15','SQL_CHAR','SQL_VARCHAR','SQL_CHAadflkada','sfsfsfsafa','sdfsaagfsgasga','sdflkshakldfklasd',0x30,100,0x3031303130313131,0x303130313031);
​

 

达梦(DM)

默认用户：SYSDBA

默认端口：5236

据说DM 99%兼容Oracle

 

字段类型

DM官网数据类型

类型	类型举例
整数类型	tinyint、smallint、int或integer、bingint、number(p,s)/numeric(p,s)s为0
小数类型	float、real、double、decimal、number(p,s)/numeric(p,s)s不为0
位类型	bit(M) 1 <= M <= 64
字符类型	CHAR、VARCHAR、charcter、text、longvarchar
布尔类型	DM8之前不支持
日期型	time、date、datetime、timestamp、以及带时区的日期
二进制字符串类型	binary、varibinary、blob、 clob、imgae、longvarbinary

测试数据

create

创建表时，若不对字段名增加""，则自动转大写

CREATE TABLE "SYSDBA"."dm_t_01"
(
"DM_NUMERIC" NUMERIC(10,0) NOT NULL,
"DM_NUKMBER" NUMBER(10,0),
"DM_INTEGER" INTEGER,
"DM_INT" INT,
"DM_PLS_INTEGER" INTEGER,
"DM_DECIMAL" DECIMAL(10,0),
"DM_DEC" DEC(3,0),
"DM_BIGINT" BIGINT,
"DM_BIT" BIT,
"DM_TINYINT" TINYINT,
"DM_BYTE" BYTE,
"DM_SMALLINT" SMALLINT,
"DM_FLOAT" FLOAT,
"DM_REAL" REAL,
"DM_DOUBLE" DOUBLE,
"DM_CHAR" CHAR(1),
"DM_CHARACTER" CHARACTER(1),
"DM_VARCHAR" VARCHAR(8188),
"DM_TEXT" TEXT,
"DM_LONGVARCHAR" LONGVARCHAR,
"DM_BLOB" BLOB,
"DM_CLOB" CLOB,
"DM_DATE" DATE,
"DM_TIMESTAMP" TIMESTAMP(6),
"DM_DATETIME" DATETIME(6),
NOT CLUSTER PRIMARY KEY("DM_NUMERIC")) STORAGE(ON "MAIN", CLUSTERBTR) ;

insert

INSERT INTO "SYSDBA"."dm_t_01"
(
    "DM_NUMERIC",
    "DM_NUKMBER",
    "DM_INTEGER",
    "DM_INT",
    "DM_PLS_INTEGER",
    "DM_DECIMAL",
    "DM_DEC",
    "DM_BIGINT",
    "DM_BIT",
    "DM_TINYINT",
    "DM_BYTE",
    "DM_SMALLINT",
    "DM_FLOAT",
    "DM_REAL",
    "DM_DOUBLE",
    "DM_CHAR",
    "DM_CHARACTER",
    "DM_VARCHAR",
    "DM_TEXT",
    "DM_LONGVARCHAR",
    "DM_BLOB",
    "DM_CLOB",
    "DM_DATE",
    "DM_TIMESTAMP",
    "DM_DATETIME"
)
VALUES
(
    12345,
    67890,
    123,
    456,
    789,
    54321,
    987,
    1234567890,
    1,
    2,
    1,
    32767,
    3.14,
    2.71,
    1.618,
    'A',
    'B',
    'This is a VARCHAR value',
    'This is a TEXT value',
    'This is a LONGVARCHAR value',
    EMPTY_BLOB(),
    EMPTY_CLOB(),
    CURRENT_DATE,
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
);
​

人大金仓(KingBase)

默认端口：54321

默认账户：system

字段类型

类型	类型举例
整数类型	tinyint、smallint、int、mediumint、integer、bingint、smallserial、serial、bigserial
小数类型	real、double precision、decimal、numeric
位类型	bit(M) 1 <= M <= 64
字符类型	char、varchar、text、longtext、mediumtext、tinytext
布尔类型	bool
json类型	json
日期型	time、date、datetime、timestamp、以及带时区的日期
二进制大字符串	binary、varibinary、blob、 clob、imgae、longvarbinary

字符类型

• 整型

  整数类型	字节	有符号数取值范围	无符号数取值范围
  TINYINT	1	-128~127	0~255
  SMALLINT	2	-32768~32767	0~65535
  MEDIUMINT	3	-8388608~8388607	0~16777215
  INT、INTEGER	4	-2147483648~2147483647	0~4294967295
  BIGINT	8	-9223372036854775808~9223372036854775807	0~18446744073709551615
  smallserial	2	1到32767(自动增加的小整数)
  serial	4	1到2147483647(自动增加的整数)
  bigserial	8	1到9223372036854775807自动增加的大整数

• char

• varchar

• text

  • 变长字符串类型。其字符串的长度最大为 1G

• longtext

  • 变长字符串类型。其字符串的长度最大为 1G

• mediumtext

  • 变长字符串类型。其字符串的长度最大为 16777215个字符

• tinytext

  • 变长字符串类型。其字符串的长度最大为 255个字符

测试数据

create

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); -- 先创建枚举类型
​
CREATE TABLE test_table_01 (
    id INT,
    tinyint_col tinyint,
    smallint_col smallint,
    mediumint_col int4,
    integer_col integer,
    int4range_col int4range,
    int8range_col int8range,
    numrange_col numrange,
    bigint_col bigint,
    bool_col bool,
    bit_col bit,
    decimal_col decimal(10,2),
    numeric_col numeric(10,2),
    real_col real,
    double_col double precision,
    smallserial_col smallserial,
    serial_col serial,
    bigserial_col bigserial,
    char_col char(100),
    varchar_col varchar(255),
    text_col text,
    money_col money,
    date_col date,
    time_col time,
    timestamp_col timestamp,
    datetime_col datetime,
    tsrange_col tsrange,
    daterange_col daterange,
    blob_col blob,
    clob_col clob,
    nclob_col nclob,
    bytea_col bytea,
    bit_col_2 BIT VARYING(5),
    point_col point,
    line_col line,
    cidr_col cidr,
    inet_col inet,
    macaddr_col macaddr,
    uuid_col uuid,
    xml_col xml,
    josn_col json,
    enum_col mood  -- 此类型为引用创建好的枚举类型
);

 

insert

INSERT INTO
    test_table_01 (
        id,
        tinyint_col,
        smallint_col,
        mediumint_col,
        integer_col,
        int4range_col,
        int8range_col,
        numrange_col,
        bigint_col,
        bool_col,
        bit_col,
        decimal_col,
        numeric_col,
        real_col,
        double_col,
        char_col,
        varchar_col,
        text_col,
        money_col,
        date_col,
        time_col,
        timestamp_col,
        datetime_col,
        tsrange_col,
        daterange_col,
        blob_col,
        clob_col,
        nclob_col,
        bytea_col,
        bit_col_2,
        point_col,
        line_col,
        cidr_col,
        inet_col,
        macaddr_col,
        uuid_col,
        xml_col,
        josn_col,
        enum_col
    )
VALUES
    (
        1,
        12,
        32767,
        100,
        100,
        '(11,20)',
        '(11,13132132132)',
        '(11.1,22.2)',
        113213213165,
        0,
        '1',
        99.12,
        88.39,
        10.223,
        10.6666,
        'dfsf ',
        'dsfsfs',
        'sdfsfa',
        758.07,
        '2023-03-23',
        '12:04:00',
        '2023-03-23 04:04:54.000',
        '2023-03-23 04:04:54.000',
        '("2022-03-24 11:11:52","2023-03-24 11:11:52")',
        '(2022-03-25,2123-03-24)',
        '0x303130313130',
        '010110',
        '110000111011',
        '0x30313031313031313030',
        '101',
        '(1.0,3.0)',
        '{1.0,2.0,3.0}',
        '192.168.100.128/25',
        '192.168.10.124',
        '8c:ec:4b:5a:a2:ee',
        'cb9c0caf-6712-4d7e-b6e9-1203daf2d2d4',
        '
<root>
  <element1 id="1">
    This is the content of element 1.
  </element1>
  <element2 id="2" color="blue">
    This is the content of element 2.
  </element2>
</root>
',
        '{"key1":"value1","key2":"value2"}',
        'happy'
    );

 

SQL语句区别

分组

/*
在一条select语句当中，如果有group by语句的话，select后面只能跟参加分组的字段，以及聚合函数，其它的一律不能跟
*/
create table test_sex_01 (
    ID NUMBER(10,0),
    sex char(5),
    age NUMBER(3,0)
)
​
SELECT sex,max(ID) FROM test_sex_01 GROUP BY sex  
​
SELECT * FROM test_sex_01 GROUP BY sex # mysql可以跟其它的字段，虽然不会报错，但没啥意义

分页

# mysql、人大金仓中使用limit关键字进行分分页
# oracle中使用rownum
# sqlserver使用top
​
​
-- 查询前5条
SELECT * FROM 
(SELECT * FROM AUTO_DATA_HOUSE_001 ORDER BY ORA_NUMBER_ID ) 
WHERE ROWNUM <= 5
​
​
-- 查询后5条
SELECT * FROM 
(SELECT * FROM AUTO_DATA_HOUSE_001 ORDER BY ORA_NUMBER_ID ) 
WHERE ROWNUM <= 5 desc
​
/*
MySQL的分页查询使用的是偏移量（从0开始），而Oracle使用的是行数（从1开始）。此外，MySQL的LIMIT语句在处理大数据量时可能效率较低，而Oracle的分页查询在处理大数据量时可能更高效。
*/
​
select * from auto_api_table_100 limit 5
​
# sqlserver中查询前三条
select  top 3 * from sqlserver_to_oracle_01 
​
# sqlserver中查询后10条
select  top 10 * from sqlserver_to_oracle_01 order by SQL_ID desc 
​
# 达梦(支持limit和rownum)

字符串拼接

-- MySQL、SQL Server中使用concat()函数
-- Oracle、人大金仓、达梦中则可以使用concat()或者"||"进行拼接
SELECT CONCAT(TITLE,NATIONALNO)FROM "RESOURCES"."EMPLOYEE";
SELECT 'A' || 'B'||'C' FROM dual; 
select DM_CHAR || DM_CHARACTER || DM_VARCHAR from "SYSDBA"."dm_t_02"

日期区别

# oracle的日期插入时，需要用到to_date()、to_timestamp()两个函数
# 达梦
    # 1.达梦插入日期时，分别用CURRENT_TIME、CURRENT_DATE、CURRENT_TIMESTAMP
    # 2.日期转字符串，to_date(),to_timestamp()

主键自增关键字

主键自增只能指定整数类型，如decimal(10,0),numerice(10,0)均不能指定为自增

/*
MySQL、人大金仓使用关键字 ：auto_increment 来指定
​
sqlserver、达梦中使用关键字：IDENTITY来指定
​
create table sql_test_t_01 (
    sql_int_1 int IDENTITY(1,1), -- 第一个参数表示起始值，第二个参数表示递增步长
    sql_int_2 int,
    sql_int_3 int,
    constraint sql_test_t_01_ok primary key(sql_int_1)
)
​
在 Oracle 11g 中，需要先创建序列（SQUENCE）再创建一个触发器（TRIGGER）。
在 Oracle 12c 中，只需要使用 IDENTITY 属性就可以了。
​
--oracle 使用序列自动增长：
    create sequence se_id 
    start with 1
    increment by 1
--使用序列实现自动增长：se_id.nextval
​
​
*/

覆盖插入

replace into和insert ignore into

场景：如果一条数据在表中已经存在,对其做update,如果不存在,将新的数据插入,如果不使用Oracle提供的merge语法的话,可能先要上数据库select查询一下看是否存在,然后决定怎么操作,这样的话需要来回数据库两次,同时性能也不好;

两者区别：

replace into：会根据主键或者唯一索引检查数据是否存在，如果存在就先删除在更新。

insert ignore：会忽略数据库中已经存在 的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。

-- replace into
replace into auto_api_table_1036 (id,mysql_tinyint,mysql_smallint,mysql_int,mysql_bigint,mysql_bit,mysql_float,mysql_decimal,mysql_double,mysql_char,mysql_varchar,mysql_tinytext,mysql_text,mysql_mediumint,mysql_date,mysql_time,mysql_datetime,mysql_year) VALUES
     (1,0,32767,101,100,1,777.99,888.77,555.55555,'随机插入积122','sfdsb','akfjba','mysql_text',7,'2022-07-15','17:23:26','2022-07-15 17:23:26',2022);
​
-- insert ignore into
​
insert ignore into auto_api_table_1036 (id,mysql_tinyint,mysql_smallint,mysql_int,mysql_bigint,mysql_bit,mysql_float,mysql_decimal,mysql_double,mysql_char,mysql_varchar,mysql_tinytext,mysql_text,mysql_mediumint,mysql_date,mysql_time,mysql_datetime,mysql_year) VALUES
     (1,0,32767,101,100,1,777.99,888.77,555.55555,'随机插入积122','sfdsb','akfjba','mysql_text',7,'2022-07-15','17:23:26','2022-07-15 17:23:26',2022);

merge into

适用于Oracle、SQL Server、达梦

/*语法格式：
MERGE INTO --要插入的表 别名
USING (
      --查询的SQL
      )别名 ON 
         --(连接条件)
WHEN MATCHED THEN -- 如果符合条件就更新(类似if)
     UPDATE SET 
WHEN NOT MATCHED THEN -- (类似else)
     INSERT ()VALUES();--不符合就插入 
*/
​
create table TEST_NIU_01 (ID number(20),NAME varchar2(20) ); 
​
insert into TEST_NIU_01 values (1, 'test1');  
insert into TEST_NIU_01 values (2, 'test2');
​
MERGE INTO TEST_NIU_01 A   -- 强调oracle不支持没有from的语句，sqlserver中可以直接select 1 as ID
USING (SELECT 1 as ID, 'newtest4' as NAME FROM DUAL) B on (A.ID=B.ID) 
WHEN MATCHED THEN   
UPDATE SET A.NAME=B.NAME  # where 1 = 0
WHEN NOT MATCHED THEN   
INSERT (A.ID, A.NAME) VALUES (B.ID, B.NAME ); 
​

人大金仓实现replace into

MySQL中也支持同样的语法

INSERT INTO t_pri_t_01 (king_int,king_char)VALUES (2,'alkhfd') ON DUPLICATE KEY UPDATE king_char='alkhfd';

 

查看表结构

-- oracle
SELECT DBMS_METADATA.GET_DDL('TABLE','表名') FROM DUAL;
​
-- mysql
show create table 表名;
​
-- sqlserver
方式一：SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '表名';
​
方式二：EXEC sp_columns '表名';
​
​
# 查看表中已存在的索引
​
-- mysql
show index from 表名
desc 表名
​
-- sqlserver
exec sp_helpindex 表名
​
-- 达梦
SELECT DBMS_METADATA.GET_DDL('TABLE','表名称','模式名称') FROM dual;
SELECT TABLEDEF('模式名','表名');
​
-- 人大金仓
select
    a.attnum,
    a.attname as field,
    a.attlen as length,
    a.atttypmod as lengthvar,
    a.attnotnull as notnull,
    b.description as comment
from
    sys_class c,
    sys_attribute a
left outer join sys_description b on
    a.attrelid = b.objoid
    and a.attnum = b.objsubid
where
    c.relname = '表名'
    and a.attnum > 0
    and a.attrelid = c.oid
order by
    a.attnum;

DDL

• 新增字段

  -- MySQL新增字段
  alter table rename_t_01 add 字段名称 字段类型
  alter table rename_t_01 add column 字段名称 字段类型
  ​
  -- sqlserver
  alter table 表名 add 字段名称 字段类型
  ​
  -- Oracle
  alter table 表名 add 字段名称 字段类型

   

• 修改表名/字段名称

  -- MySQL修改表名,to可省略
  alter table 原表名 rename [to] 新表名
  ​
  -- sqlserver修改表名[@newname可省略]
  EXEC sp_rename @objname = '原表名', @newname = '新表名'
  EXEC sp_rename '原表名','新表名'
  ​
  -- oracle修改表名
  alter table 原表名 rename to 新表名
  ​
  ​
  -- MySQL修改字段名称
  alter table 表名 change  原字段名称  新字段名称 字段类型
  alter table rename_t_01 change  old_field_char  new_field_char varchar(255)  # 也可修改字段类型
  ​
  -- sqlserver修改字段名称
  alter table 表名 rename column 原字段名称 to 新字段名称
  ​
  -- Oracle修改字段名称
  alter table 表名 rename column 原字段名称 to 新字段名称

• 修改字段类型

  -- mysql [column]可省略
  alter table rename_t_01 modify [column] 字段名 字段类型
  ​
  -- sqlserver
  alter table 表名 alter column 字段名称 字段类型
  ​
  -- oracle
  alter table 表名 modify 字段名称 字段类型

• 删除字段

  -- MySQL [column]可省略
  alter table 表名 drop 字段名称
  alter table 表名 drop column 字段名称
  ​
  -- SQL Server
  alter table 表名 drop column 字段名称
  ​
  -- Oracle
  ALTER TABLE 表名 DROP COLUMN 字段名称

建表时复制另外一个表的数据

-- mysql 
create table xxx as select * from 要复制的表  # as关键字可省略 慎用：只会拷贝表结构和数据，主键约束和其它约束都不会拷贝到的，字段的默认约束都不会拷贝
​
create table xxx  like 要复制的表  # 推荐使用这种，可以把表结构以及字段约束都拷贝过来
​
-- sqlserver
select * into xxx from 要复制的表
​
-- oracle、 达梦、人大金仓
create table xxx as select * from 要复制的表
​
​
-- 以上除了mysql的create ... like ... 能把约束拷贝过来，其它不支持；

DML

-- MySQL、SQL Server
insert into 表名 (field01,field02,field..) values(value01,value02,value...)
# MySQL中可以一次插入多条
insert into 表名 (field01,field02,field..) values(value01,value02,value...),(value01,value02,value...),(value01,value02,value...)
​
-- SQL Server、Oracle中批量插入(注意分号不可省略！！！)
begin
    INSERT INTO T_ORACLE_01_COPY_AS_NEW (ID,NUMEBER18,ORACLE_CHAR) VALUES (2,2,'2');
    INSERT INTO T_ORACLE_01_COPY_AS_NEW (ID,NUMEBER18,ORACLE_CHAR) VALUES (3,3,'3');
    INSERT INTO T_ORACLE_01_COPY_AS_NEW (ID,NUMEBER18,ORACLE_CHAR) VALUES (4,4,'4');
end;

复制插入

-- mysql
insert into temp_t_sql_01  as select * from t_services_table_01  # as 关键字可省略
​
-- sqlserver，表中不能含有timestamp类型
insert into temp_t_sql_01  select * from t_services_table_01  # 不可使用 as 关键字
​
-- oracle，表中不能含有long类型
INSERT INTO T_ORACLE_01_COPY SELECT ID,NUMEBER18, ORACLE_CHAR FROM T_ORACLE_01  # 不可使用 as 关键字
​
-- 达梦、人大金仓
insert into xxx select * from  bbb  # 不可使用 as 关键字

导入sql文件

MySQL

source

source命令需要在mysql命令行中才可使用

# 1.先把要导入的sql文件拷贝到容器里面，说明：如果容器开启数据卷挂载的话，不需要此步骤
docker cp create01.sql fac859d9d515:/home
# 2.进入mysql容器，连接mysql
mysql -uroot -p123456
# 3.切换到要导入的数据库
use 要切换的库
# 4.source sql文件路径
source /home/create01.sql

导入多个sql文件

场景：排查现场问题时，现场给了很多个sql文件

• 步骤1(先把sql文件依次上传到容器)

• 步骤2：新建一个文件，把每个sql文件所在路径写入到此文件

• 步骤3：连接数据库并切换到对应的数据库，执行导入

   

SQL Server

sqlcmd

# 步骤1.先把要导入的sql文件拷贝到容器里面，说明：如果容器开启数据卷挂载的话，不需要此步骤
​
# 步骤2：进入sqlserver所在容器，找到sqlcmd所在目录，登录sqlserver
./sqlcmd -U 用户名 -P 密码
​
# 步骤3：切换到要到导入的数据库
1> use auto_database
2> go   # sqlserver要以GO去触发sql的执行，大小写皆可
Changed database context to 'auto_database'.
1> :r /home/sqlserver01.sql
2> go

导入多个sql文件

• 步骤1(先把sql文件依次上传到容器)

• 步骤2：新建一个文件，把每个sql文件所在路径写入到此文件

• 步骤3：连接数据库并切换到对应的数据库，执行导入

Oracle

# 1.先把要导入的sql文件拷贝到容器里面，说明：如果容器开启数据卷挂载的话，不需要此步骤
# 2.进入Oracle容器，连接Oracle
sqlplus /nolog
connect sys as sysdba;
根据提示输入对应的密码：
# 3.source sql文件路径
@ /home/create01.sql

导入多个文件

步骤同mysql、sqlserver。文件内容写入：@ /sql文件所在路径

DQL

查询都有哪些数据库

-- MySQL
show databases;
​
-- SQL Server
SELECT name FROM sys.databases;
​
-- Oracle
SELECT * FROM ALL_USERS;

统计表中有多少列

表名不存在，则返回0

-- Oracle
SELECT COUNT(*) AS column_count FROM all_tab_columns WHERE table_name = '表名';  # 表名要大写；
​
-- MySQL/SQL Server
select count(*) as column_count  from information_schema.columns where table_name = '表名';
​
-- SQL Server
SELECT COUNT(*) AS column_count FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '表名';  
​
-- 达梦
select * from all_tab_columns where owner='模式名' and Table_Name='表名'

查看连接数

-- oracle 查看最大连接数
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'processes';
​
-- sqlserver查看当前连接数
SELECT COUNT(*) AS connection_count FROM V$SESSION;
​
-- mysql
show variables like '%connect%'   -- max_connections
​
-- 达梦
select clnt_ip,user_name,state,count(*) from v$sessions group by clnt_ip,user_name,state;

创建视图以及复制

创建视图

小提示：统一跟上as关键字方便记忆

-- Oracle、SQL Serever、达梦 as关键字不可省略
CREATE VIEW  RECEIVE_HANA_01_view  AS SELECT * FROM RECEIVE_HANA_01  # as关键字不可省略
​
-- mysql 的as关键字可省略

创建表，并把表数据也同步过去

-- MySQL
create table xxx as select * from bbb;  # as关键字可省略
​
-- Oracle 复制的表不能含有long类型
-- CREATE TABL T_ORACLE_01_COPY AS select column1,column2... from table_name_old;
CREATE TABLE T_ORACLE_01_COPY  AS SELECT ID,NUMEBER18, ORACLE_CHAR FROM T_ORACLE_01  # as关键字不可省略
​
-- sqlserver 不支持这样创建
​

复制表结构

-- mysql 
create table xxx as select * from 要复制的表  # as关键字可省略 慎用：只会拷贝表结构和数据，主键约束和其它约束都不会拷贝到的，字段的默认约束都不会拷贝
​
create table xxx  like 要复制的表  # 推荐使用这种，可以把表结构以及字段约束都拷贝过来(仅在MySQL当中支持此种语法)
​

执行顺序

    select 
    ...
    from
    ...
    where
    ...
    group by 
    ...
    having
    ...
    order by
    ...
    limit
    ...
​
/*
1.from
2.where
3.group by
4.having
5.select
6.order by 
7.limit,[rownum]
即：
从from某张表中查询数据，先经过where条件筛选出符号条件的数据
对符合条件的数据进行分组
分组之后使用having过滤筛选
select查询
最后排序输出
​
​
1、为什么在写sql语句的时候，聚合函数不能跟在where条件的后面？
2、ordery by后面可以用select field的as别名
从执行可以看出，
1、在where条件的时候，还没有进行分组；所以聚合函数不能直接跟在where子句中使用
2、因为在执行顺序中，先执行select再执行order by，所以order by后面可以用select as别名
*/

案例

/*
案例一：先使用where条件以后再进行分组过滤
    
selct deptno,max(sal) from emp group by deptno having sal > 3000  # 这样先对整张表分组，再判断，效率有点低
​
select deptno,max(sal) from emp where sal > 3000 group by deptno; #先把sal大于3000的过滤出来，再进行分组，已经过滤了一部分数据，再分组就相对效率高点
​
​
案例二：
​
找出每个岗位的平均薪资，要是显示平均薪资大于1500的，除MANAGER岗位职位，要求按照平均薪资降序排
​
select job,avg(sal) as avg_sal from emp group by job having avg(sal) > 1500 and job != 'MANAGER' order by avg_sal desc 
​
select job,avg(sal) as avg_sal from emp where job != 'MANAGER' group by job having avg(sal) > 1500 ordey by avg_sal desc 
​
*/

存储引擎

存储引擎可以分为两部分理解，一部分是存储，一部分是引擎； 存储：是把数据存储到磁盘上

引擎：是为数据存储到磁盘上增加动力的，即：提升数据存储的速度同时也对数据更新提升速度优化I/O

mysql

/*
MYISALM：速度优先，索引也支持 B+tree、锁是表锁
​
•   `.frm文件`:存储表结构
​
•   `.myd文件`:存储表数据
​
•   `.myi文件`:存储索引
​
同等数据量的数据，从磁盘上获取数据，MyISAM存储引擎速度要快一些
​
INNODB：事务优先、索引是B+tree、锁是行锁
​
•   `.frm文件`:存储表结构
​
•   `.bd文件`:存储表数据和索引
*/

root@fac859d9d515:/var/lib/mysql/auto_database# ls |grep -e t_test_01
t_test_01.frm
t_test_01.ibd
root@fac859d9d515:/var/lib/mysql/auto_database# ls |grep -e t_test_02
t_test_02.MYD
t_test_02.MYI
t_test_02.frm
​

sqlserver

/*
.mdf：数据主文件
.ndf：辅助文件也可以存储数据
ndf可以有多个但是mdf只能有一个
.ldf：日志文件
*/

root@3b764e638b67:/var/opt/mssql/data# ls
bd_modle.mdf           cdc_auto_database_log.ldf       cdc_test.mdf      mastlog.ldf   msdbdata.mdf        receive_oracle_log.ldf             sql_auto_database.mdf      templog.ldf
bd_modle_log.ldf       cdc_jdbc_auto_database.mdf      cdc_test_log.ldf  model.mdf     msdblog.ldf         receive_sql_auto_database.mdf      sql_auto_database_log.ldf
cdc_auto_database.mdf  cdc_jdbc_auto_database_log.ldf  master.mdf        modellog.ldf  receive_oracle.mdf  receive_sql_auto_database_log.ldf  tempdb.mdf

oracle

表空间(.bdf)：由数据文件组成.用户的各种模式对象(如表, 索引, 过程, 触发器等) 都是放在表空间中

每个用户都可以指定独立的表空间，若不给用户指定默认的表空间，则用户的默认表空间为USERS表空间

数据文件：物理存储单位，

一个表空间可以由一个或多个和数据文件组成，数据库的数据是存储在表空间中的

Oracle 数据库是数据的物理存储。这就包括（数据文件 ORA 或者 DBF、控制文件、联机日志、参数文件）

CDC(Change Data Capture)

变化数据捕获简称CDC，记录了所有的DDL和DML语句

MySQL

MYSQL 通过开启 binlog 日志，提供 CDC 数据同步能力。CDC 配置工作包含两个操作：

（1）数据库实例开启 binlog 日志，

（2）同步账户授予 CDC 操作权限。

说明：

操作（1）针对的是整个数据库实例，只需操作一次即可，操作完后需要重启数据库才能使配置生效。

操作（2）以同步账户为单位进行配置，在此建议针对每个 MYSQL 实例创建全局统一的同步账户，这样也只需配置一次即可。

/*
步骤说明：若满足1、2步骤则直接执行步骤5，否则依次执行
*/
​
-- 1.检查bin-log是否开启，以管理员账户root登录数据库 
mysql -u用户名 -p密码
show variables like 'log_bin'; -- 未开启：OFF   已开启：ON
​
-- 2.检查相关参数的配置是否符合要求
show variables like '%binlog_format%';   -- 值应为“ROW”
show variables like '%binlog_row_image%'; -- 值应为：“FULL”
​
-- 3.配置文件增加bin-log开启配置
/*
log-bin=binlog
log-bin-index=binlog.index
binlog_format=row
server_id=1
binlog_row_image = full
gtid_mode = on
enforce_gtid_consistency = on
expire_logs_days = 10
​
server-id 的值应为大于 1 的整数，请根据实际规划设置，并且在创建数据集成任务时设置的“Server Id”值需要此处设置的值不同。
expire_logs_days 为 Binlog 日志文件保留时间，超过保留时间的 Binlog 日志会被自动删除，应保留至少2天的日志文件。
“gtid_mode = on”和“enforce_gtid_consistency = on”仅当 MySQL 的版本大于等于5.6.5 时才需要添加，否则删除这两行内容。
*/
​
-- 4.保存配置文件，重启数据库；使配置生效
​
-- 5.创建账户，并授权
CREATE USER '用户名'@'%' IDENTIFIED BY '密码';
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO '用户名'@'%';

SQL Server

-- 1.查询数据库是否开启cdc
select is_cdc_enabled from sys.databases where name='supdam'   -- 0：未开启   1：已开启
​
-- 2.开启数据库cdc(切换到对应的数据库下，如：use test_db)
exec sys.sp_cdc_enable_db
​
-- 3.开启数据表cdc
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo', -- 表所在数据库 scheme
@source_name = '数据表名',
@supports_net_changes = 1, 
@role_name = NULL  -- 访问控制角色名

Oracle

Oracle开启CDC参考文档

 

标签：常用,01,草稿,--,数据库,mysql,SQL,类型,col
From： https://www.cnblogs.com/linastudy/p/17551691.html