精华内容
下载资源
问答
  • AlterID(认证工具)

    2019-11-14 14:00:44
    TeamViewer14认证工具AlterID,修改windows下的TeamViewer。
  • AlterID2.zip

    2019-07-02 16:13:35
    AlterID2 zip
  • ALTER TABLE Time: 36.544 ms postgres=# alter table tmp_t4 alter column memo2 drop default; ALTER TABLE Time: 59.798 ms add column set default postgres=# alter table tmp_t4 add column memo3 varchar...

    os: centos 7.4
    db: postgresql 10.11

    创建表后,有时需要对表进行 set default 或者 drop default 设置。

    版本

    # cat /etc/centos-release
    CentOS Linux release 7.4.1708 (Core) 
    # 
    # su - postgres
    $
    $ psql -c "select version();"
                                                     version                                                  
    ----------------------------------------------------------------------------------------------------------
     PostgreSQL 10.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
    (1 row)
    
    

    create table

    $ psql
    psql (10.11)
    Type "help" for help.
    
    postgres=# \timing on
    Timing is on.
    postgres=# 
    postgres=# drop table if exists tmp_t4;
    DROP TABLE
    Time: 313.287 ms
    
    postgres=# create table tmp_t4( 
    id    int8,
    name  varchar(100),
    memo1 varchar(100),
    memo2 varchar(100)
    );
    CREATE TABLE
    Time: 360.530 ms
    
    postgres=# insert into tmp_t4 
    select id,
           md5(id::varchar),
           md5(id::varchar),
    	   null 
      from generate_series(1,10000000) as id;
    
    INSERT 0 10000000
    Time: 58346.187 ms (00:58.346)
    
    postgres=# select * from tmp_t4 limit 5;
    
     id |               name               |              memo1               | memo2 
    ----+----------------------------------+----------------------------------+-------
      1 | c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849b | 
      2 | c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c | 
      3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | 
      4 | a87ff679a2f3e71d9181a67b7542122c | a87ff679a2f3e71d9181a67b7542122c | 
      5 | e4da3b7fbbce2345d7772b0674a318d5 | e4da3b7fbbce2345d7772b0674a318d5 | 
    (5 rows)
    
    Time: 0.796 ms
    
    postgres=# \d+ tmp_t4
                                              Table "public.tmp_t4"
     Column |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
    --------+------------------------+-----------+----------+---------+----------+--------------+-------------
     id     | bigint                 |           |          |         | plain    |              | 
     name   | character varying(100) |           |          |         | extended |              | 
     memo1  | character varying(100) |           |          |         | extended |              | 
     memo2  | character varying(100) |           |          |         | extended |              | 
    
    postgres=# select oid,relname,relfilenode,relkind,relfrozenxid 
    from pg_class pc where pc.relname='tmp_t4';
    
      oid   | relname | relfilenode | relkind | relfrozenxid 
    --------+---------+-------------+---------+--------------
     123886 | tmp_t4  |      123886 | r       |       406624
    (1 row)
    
    Time: 0.579 ms
    
    

    alter column set default

    对列中不含null进行 set default 设置

    postgres=# alter table tmp_t4 alter column memo1 set default 'aaa';
    ALTER TABLE
    Time: 1129.620 ms (00:01.130)
    
    postgres=# \d+ tmp_t4
                                                       Table "public.tmp_t4"
     Column |          Type          | Collation | Nullable |         Default          | Storage  | Stats target | Description 
    --------+------------------------+-----------+----------+--------------------------+----------+--------------+-------------
     id     | bigint                 |           |          |                          | plain    |              | 
     name   | character varying(100) |           |          |                          | extended |              | 
     memo1  | character varying(100) |           |          | 'aaa'::character varying | extended |              | 
     memo2  | character varying(100) |           |          |                          | extended |              | 
     
    postgres=# select oid,relname,relfilenode,relkind,relfrozenxid 
    from pg_class pc where pc.relname='tmp_t4';
    
      oid   | relname | relfilenode | relkind | relfrozenxid 
    --------+---------+-------------+---------+--------------
     123886 | tmp_t4  |      123886 | r       |       406624
    (1 row)
    
    Time: 0.443 ms
    
    

    对列中含null进行 default 设置

    postgres=# alter table tmp_t4 alter column memo2 set default 'aaa';
    ALTER TABLE
    Time: 128.971 ms
    
    

    alter column drop default

    postgres=# \d+ tmp_t4
                                                       Table "public.tmp_t4"
     Column |          Type          | Collation | Nullable |         Default          | Storage  | Stats target | Description 
    --------+------------------------+-----------+----------+--------------------------+----------+--------------+-------------
     id     | bigint                 |           |          |                          | plain    |              | 
     name   | character varying(100) |           |          |                          | extended |              | 
     memo1  | character varying(100) |           |          | 'aaa'::character varying | extended |              | 
     memo2  | character varying(100) |           |          | 'aaa'::character varying | extended |              | 
    
    
    postgres=# alter table tmp_t4 alter column memo1 drop default;
    ALTER TABLE
    Time: 36.544 ms
    
    
    postgres=# alter table tmp_t4 alter column memo2 drop default;
    ALTER TABLE
    Time: 59.798 ms
    
    

    add column set default

    postgres=# alter table tmp_t4 add column memo3 varchar(100) default 'aaa';
    ALTER TABLE
    Time: 73482.788 ms (01:13.483)
    
    postgres=# select * from tmp_t4 limit 5;
     id |               name               |              memo1               | memo2 | memo3 
    ----+----------------------------------+----------------------------------+-------+-------
      1 | c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849b |       | aaa
      2 | c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c |       | aaa
      3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 |       | aaa
      4 | a87ff679a2f3e71d9181a67b7542122c | a87ff679a2f3e71d9181a67b7542122c |       | aaa
      5 | e4da3b7fbbce2345d7772b0674a318d5 | e4da3b7fbbce2345d7772b0674a318d5 |       | aaa
    (5 rows)
    
    Time: 0.619 ms
    
    postgres=# select oid,relname,relfilenode,relkind,relfrozenxid 
    from pg_class pc where pc.relname='tmp_t4';
    
      oid   | relname | relfilenode | relkind | relfrozenxid 
    --------+---------+-------------+---------+--------------
     123886 | tmp_t4  |      123894 | r       |       406631
    (1 row)
    
    Time: 0.834 ms
    
    

    relfilenode 由之前 123886 变为 123894

    add column set default 确实要慎重

    参考:
    http://postgres.cn/docs/10/sql-altertable.html

    展开全文
  • clickhouse的SQL参考——(三)ALTER

    千次阅读 2020-11-30 14:23:42
    大多数ALTER查询都会修改表设置或数据: COLUMN PARTITION DELETE UPDATE ORDER BY INDEX CONSTRAINT TTL 大多数* ALTER查询仅支持* MergeTree表以及Merge和Distributed。 ALTER设置会修改基于角色的访问...

    参考资料

    https://clickhouse.tech/docs/en/sql-reference/statements/alter/

    版本:v20.11

    目录

    总览

    突变(Mutations)

    ALTER同步性

    列操作(Column Manipulations)

    ADD COLUMN

    DROP COLUMN

    CLEAR COLUMN

    COMMENT COLUMN

    MODIFY COLUMN

    限制

    操作分区(Manipulating Partitions and Parts)

    DETACH PARTITION

    DROP PARTITION

    DROP DETACHED PARTITION|PART

    ATTACH PARTITION|PART

    ATTACH PARTITION FROM

    REPLACE PARTITION

    MOVE PARTITION TO TABLE

    CLEAR COLUMN IN PARTITION

    FREEZE PARTITION

    CLEAR INDEX IN PARTITION

    FETCH PARTITION

    MOVE PARTITION|PART

    如何设置分区表达式

    删除操作(ALTER TABLE … DELETE)

    更新操作(ALTER TABLE … UPDATE)

    ORDER BY表达式(Manipulating Key Expressions)

    SAMPLE BY表达式(Manipulating Sampling-Key Expressions)

    INDEX(Manipulating Data Skipping Indices)

    CONSTRAINT(Manipulating Constraints)

    TTL(Manipulations with Table TTL)

    ALTER USER

    ALTER QUOTA

    ALTER ROLE

    ALTER ROW POLICY

    ALTER SETTINGS PROFILE


    总览

    大多数ALTER查询都会修改表设置或数据:

    大多数* ALTER查询仅支持* MergeTree表以及Merge和Distributed。

    ALTER设置会修改基于角色的访问控制有关的实体:

    突变(Mutations)

    更改表数据的ALTER是通过一种称为“突变”的机制实现的。最值得注意的是 ALTER TABLE … DELETE 和 ALTER TABLE … UPDATE.。它们是异步后台进程,类似于MergeTree表中的合并,生成一种新的“突变”版本。

    对于* MergeTree表,通过重写整个数据部分来执行变异。没有原子性,一部分数据准备好后,它们就会立即替换为突变的部分。在突变期间执行SELECT语句将会看到突变部分和未突变部分的数据。

    突变完全按照创建顺序应用到各个数据部分,突变还通过INSERT INTO查询进行了部分排序:突变前插入表中的数据将发生突变,之后插入的数据将不会发生突变。请注意,突变不会以任何方式阻止插入。

    在突变查询被提交后将立即开始突变(对于ZooKeeper是复制表,对于文件系统是非复制表)。

    突变本身使用系统配置文件设置异步执行。要跟踪突变的进度,可以使用system.mutations表。

    即使ClickHouse服务器重新启动,成功提交的突变如果没有执行完也会继续执行。

    突变一旦提交就没有办法回滚。如果出现突变过程中卡住的情况, 可以使用KILL MUTATION来将其取消。

    完成变异的条目不会立即删除(保留条目的数量由finish_mutations_to_keep存储引擎参数确定)。 较旧的突变条目将被删除。

    ALTER同步性

    对于非复制表,所有ALTER查询都是同步执行的。 对于复制的表,查询仅向ZooKeeper添加适当操作的指令,并且这些操作本身将尽快执行。 查询可以等待所有副本上完成这些操作。

    对于ALTER ... ATTACH|DETACH|DROP 查询,可以使用replication_alter_partitions_sync来设置等待。 可能的值:0 –不等待; 1 –仅等待自己执行(默认); 2 –等待所有。

    对于ALTER TABLE ... UPDATE | DELETE查询,其同步性是由mutations_sync设置定义的。

    列操作(Column Manipulations)

    允许更改表结构的查询。

    ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN ...
    

    在查询中,指定一个或多个逗号分隔操作的列表。
    每个动作都是对列的操作。

    支持以下操作:

    ADD COLUMN

    ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [codec] [AFTER name_after]
    

    给表添加一个新列,指定名称,类型,压缩类型codec,默认表达式。

    如果包含IF NOT EXISTS子句,那么创建时遇到相同名称的列不会报错。如果指定AFTER name_after(另一列的名称),则该列将添加到列表中指定的列之后。否则,该列将添加到表的末尾。  请注意,无法将列添加到表的开头。对一系列的操作,name_after可以是之前的操作插入的列名。

    添加列只会对表结构进行操作,不会对数据做操作。如果给列制定了默认表达式,ALTER后,数据也不会立刻出现落在磁盘上,合并数据后才会出现。

    这种方法使我们能够立即完成ALTER查询,而无需增加旧数据量。

    ALTER TABLE visits ADD COLUMN browser String AFTER user_id
    

    DROP COLUMN

    DROP COLUMN [IF EXISTS] name
    --举例
    ALTER TABLE visits DROP COLUMN browser
    
    

    删除名称为name的列。 如果指定了IF EXISTS子句,则该列不存在时查询将不会返回错误。

    从文件系统中删除数据。 由于此操作会删除整个文件,因此查询几乎立即完成。

    CLEAR COLUMN

    CLEAR COLUMN [IF EXISTS] name IN PARTITION partition_name
    --举例
    ALTER TABLE visits CLEAR COLUMN browser IN PARTITION tuple()

    重置指定分区的列中的所有数据。 在如何指定分区表达式部分中阅读有关设置分区名称的更多信息。How to specify the partition expression.

    如果指定了IF EXISTS子句,则该列不存在时查询将不会返回错误。

    COMMENT COLUMN

    COMMENT COLUMN [IF EXISTS] name 'comment'
    --举例
    ALTER TABLE visits COMMENT COLUMN browser 'The table shows the browser used for accessing the site.'
    

    在该列中添加评论。 如果指定了IF EXISTS子句,则该列不存在时查询将不会返回错误。

    每列可以有一个注释。 如果该列已存在注释,则新注释将覆盖先前的注释。

    注释存储在DESCRIBE TABLE查询返回的comment_expression列中。

    MODIFY COLUMN

    --此查询更改名称列属性:
    --类型、默认表达、TTL
    MODIFY COLUMN [IF EXISTS] name [type] [default_expr] [TTL]
    --有关列TTL修改的示例,请参见
    --[Column TTL](../../engines/table_engines/mergetree_family/mergetree.md#mergetree-column-ttl).
    
    ALTER TABLE visits MODIFY COLUMN browser Array(String)
    
    
    

    如果指定了IF EXISTS子句,则该列不存在时查询将不会返回错误。

    更改类型时,将转换值,就像将toType函数应用于它们一样。 如果仅更改默认表达式,则查询不会做任何复杂的事情,并且可以立即完成。

    更改列类型是唯一的复杂操作,它将更改带有数据的文件的内容。 对于大表,这可能需要很长时间。

    有几个处理阶段:

    • 准备具有已修改数据的临时(新)文件。
    • 重命名旧文件。
    • 将临时(新)文件重命名为旧名称。
    • 删除旧文件。

    只有第一阶段需要时间。 如果在此阶段出现故障,则不会更改数据。如果在连续的一个阶段中发生故障,则可以手动恢复数据。但是有例外,如果旧文件已从文件系统中删除,但新文件的数据未写入磁盘,那么数据就会丢失。

    更改列的ALTER查询也会被复制。指令被保存在ZooKeeper中,然后每个副本进行应用。所有ALTER查询均以相同顺序运行。所有的操作将异步执行。

    查询会等待其他副本上完成操作。 但是,更改复制表中的列的查询可能会中断 。

    限制

    ALTER查询使您可以在嵌套数据结构中创建和删除单独的元素(列),但不能在整个嵌套数据结构中创建和删除它们。 要添加嵌套数据结构,可以添加名称,如name.nested_name,类型为Array(T)的列。嵌套数据结构等效于,在点之前具有相同前缀的名称的多个数祖列。

    不支持删除主键或采样键(ENGINE表达式中使用的列)中的列。

    仅当更改不会导致数据修改(例如,允许您将值添加到Enum或将类型从DateTime更改为UInt32)时,才可以更改主键中的列的类型。

    如果您对标的修改不能满足您的需求,您可以创建一个新表,使用INSERT SELECT查询将数据复制到该表,然后使用RENAME查询切换表并删除旧表。 您可以将clickhouse-copier用作INSERT SELECT查询的替代方法。

    ALTER查询阻止该表的所有读取和写入。换句话说,ALTER查询会等待正在运行的SELECT,新的SELECT也会等待ALTER完成。

    对于本身不存储数据的表(例如“Merge”和“Distributed”),ALTER只会更改当前表结构,而不会更改从属表的结构。 例如,在对分布式表运行ALTER时,还需要在所有远程服务器上对表运行ALTER。

    操作分区(Manipulating Partitions and Parts)

    可以对分区执行以下操作:

    DETACH PARTITION

    ALTER TABLE table_name DETACH PARTITION partition_expr
    ALTER TABLE visits DETACH PARTITION 201901
    

    将指定分区的所有数据移动到分离目录。服务器会忘记分离的数据分区,就好像它不存在一样。 在您进行ATTACH查询之前,服务器将不知道此数据。

    执行查询后,您可以对分离目录中的数据执行任何所需的操作-从文件系统中删除它,或者保留它。

    该查询会用于复制表(is replicated)——它将数据移动到所有副本上的分离目录中。您只能在领导者副本上执行此查询。 若要确定副本是否为领导者,请对system.replicas表执行SELECT查询。更容易在所有副本上进行DETACH查询-除领导者副本之外,所有副本都会引发异常。

    DROP PARTITION

    ALTER TABLE table_name DROP PARTITION partition_expr
    

    从表中删除指定的分区。 该查询将分区标记为非活动状态,并大约在10分钟内完全删除数据。

    该查询会用于复制表(is replicated)——删除所有副本上的数据。

    DROP DETACHED PARTITION|PART

    ALTER TABLE table_name DROP DETACHED PARTITION|PART partition_expr
    

    从分离目录中删除指定分区的指定部分或所有部分。

    ATTACH PARTITION|PART

    ALTER TABLE table_name ATTACH PARTITION|PART partition_expr
    ALTER TABLE visits ATTACH PARTITION 201901;
    ALTER TABLE visits ATTACH PART 201901_2_2_0;
    

    将数据从分离目录添加到表中。 可以为整个分区或单独的部分添加数据。 

    该查询会用于复制表(is replicated)——复制启动器检查分离目录中是否有数据。 如果数据存在,查询将检查其完整性。 如果一切正确,则查询会将数据添加到表中。 所有其他副本都从副本启动器下载数据。

    因此,您可以将数据放在一个副本上的分离目录中,并使用ALTER ... ATTACH查询将其添加到所有副本上的表中。

    ATTACH PARTITION FROM

    ALTER TABLE table2 ATTACH PARTITION partition_expr FROM table1
    

    该查询将数据分区从表1复制到表2,将数据添加到表2中。 请注意,数据不会从表1中删除。

    为了使查询成功运行,必须满足以下条件:

    • 两个表必须具有相同的结构。
    • 两个表必须具有相同的分区键。

    REPLACE PARTITION

    ALTER TABLE table2 REPLACE PARTITION partition_expr FROM table1
    

    该查询将数据分区从表1复制到表2,并替换表2中的现有分区。 请注意,数据不会从表1中删除。

    为了使查询成功运行,必须满足以下条件:

    • 两个表必须具有相同的结构。
    • 两个表必须具有相同的分区键。

    MOVE PARTITION TO TABLE

    ALTER TABLE table_source MOVE PARTITION partition_expr TO TABLE table_dest
    

    此查询通过删除table_source中的数据将数据分区从table_source移到table_dest。

    为了使查询成功运行,必须满足以下条件:

    • 两个表必须具有相同的结构。
    • 两个表必须具有相同的分区键。
    • 两个表都必须是相同的引擎系列(已复制或未复制)。
    • 两个表必须具有相同的存储策略。

    CLEAR COLUMN IN PARTITION

    ALTER TABLE table_name CLEAR COLUMN column_name IN PARTITION partition_expr
    ALTER TABLE visits CLEAR COLUMN hour in PARTITION 201902
    

    重置分区中指定列中的所有值。 如果在创建表时确定了DEFAULT子句,则此查询会将列值设置为指定的默认值。

    FREEZE PARTITION

    ALTER TABLE table_name FREEZE [PARTITION partition_expr]
    

    该查询创建指定分区的本地备份。 如果省略PARTITION子句,则查询将立即创建所有分区的备份。

    在不停止服务器的情况下执行整个备份过程。

    请注意,对于旧式表,您可以指定分区名称的前缀(例如'2019')-然后查询将为所有相应分区创建备份。

    如何设置分区表达式:How to specify the partition expression.

    在执行时,对于数据快照,查询将创建到表数据的硬链接。硬链接放置在目录/var/lib/clickhouse/shadow/N/...中:

    • /var/lib/clickhouse/是配置中指定的有效ClickHouse目录。
    • N是备份的增量编号。

    如果将一组磁盘用于表中的数据存储,则shadow / N目录会出现在每个磁盘上,在磁盘中存储与PARTITION表达式匹配的数据部分。

    会在/ var / lib / clickhouse /中创建相同的目录结构作为备份。 该查询对所有文件执行“ chmod”,禁止写入文件。

    创建备份后,可以将数据从/ var / lib / clickhouse / shadow /复制到远程服务器,然后从本地服务器删除。 注意,ALTER t FREEZE PARTITION查询不会被复制。 它仅在本地服务器上创建本地备份。

    该查询会立即创建备份(但首先它等待对相应表的当前查询完成运行)。

    ALTER TABLE t FREEZE PARTITION仅复制数据,而不复制表元数据。 要备份表元数据,请复制文件/var/lib/clickhouse/metadata/database/table.sql

    要从备份还原数据,请执行以下操作:

    • 如果表结构不存在,则创建表结构 。要查看查询,请使用.sql文件(将ATTACH替换为CREATE)。
    • 将数据从data/database/table/目录复制到 /var/lib/clickhouse/data/database/table/detached/目录
    • 运行ALTER TABLE t ATTACH PARTITION将数据添加回表中。

    从备份还原不需要停止服务器。

    有关备份和还原数据的更多信息,请参见“ Data Backup ”部分。

    CLEAR INDEX IN PARTITION

    ALTER TABLE table_name CLEAR INDEX index_name IN PARTITION partition_expr
    

    该查询的工作方式类似于CLEAR COLUMN,但是它重置索引而不是列数据。

    FETCH PARTITION

    ALTER TABLE table_name FETCH PARTITION partition_expr FROM 'path-in-zookeeper'
    ALTER TABLE users FETCH PARTITION 201902 FROM '/clickhouse/tables/01-01/visits';
    ALTER TABLE users ATTACH PARTITION 201902;
    

    从另一台服务器下载分区。 该查询仅适用于复制表。

    该查询执行以下操作:

    • 从指定的分片(clickhouse数据库实例)下载分区。 您必须在‘path-in-zookeeper’指定ZooKeeper中的分片路径。
    • 然后查询将下载的数据放入table_name表的分离目录中。 使用ATTACH PARTITION | PART查询将数据添加到表中。

    注意:

    • ALTER ... FETCH PARTITION查询不会应用于复制表, 它仅将分区放置在本地服务器分离目录中。
    • ALTER TABLE ... ATTACH查询会应用于复制表,它将数据添加到所有副本。数据会添加到副本之一的分离目录,其他副本会进行复制。

    在下载之前,系统会检查分区是否存在并且表结构是否匹配。然后从正常副本中自动选择最合适的副本。

    尽管查询称为ALTER TABLE,但它不会更改表结构,也不会立即更改表中的可用数据。

    MOVE PARTITION|PART

    将分区或数据部分移动到MergeTree引擎表的另一个卷或磁盘上。参见Using Multiple Block Devices for Data Storage.

    ALTER TABLE table_name MOVE PARTITION|PART partition_expr TO DISK|VOLUME 'disk_name'
    ALTER TABLE hits MOVE PART '20190301_14343_16206_438' TO VOLUME 'slow'
    ALTER TABLE hits MOVE PARTITION '2019-09-01' TO DISK 'fast_ssd'
    

    对于该查询:

    不会复制到复制表。因为不同的副本可以具有不同的存储策略。

    如果未配置指定的磁盘或卷,则返回错误。 如果无法应用存储策略中指定的数据移动条件,查询也会返回错误。

    当要移动的数据已经被后台进程移动时,并发执行 ALTER TABLE t MOVE,或者后台数据合并,在这种情况下会返回错误,用户不应执行任何其他操作。

    如何设置分区表达式

    您可以通过不同的方式在ALTER ... PARTITION查询中指定分区表达式:

    • 取 system.parts 表中partition列的值,比如:ALTER TABLE visits DETACH PARTITION 201901.
    • 作为来自表列的表达式,也支持常量和常量表达式。比如:ALTER TABLE visits DETACH PARTITION toYYYYMM(toDate('2019-01-25')).
    • 使用分区ID。 分区ID是分区的字符串标识符(如果可能的话,是可读的),用作文件系统和ZooKeeper中分区的名称。必须在PARTITION ID子句中指定分区ID,用单引号引起来。比如,ALTER TABLE visits DETACH PARTITION ID '201901'.
    • 在ALTER ATTACH PART和DROP DETACHED PART查询中,要指定part的名称,使用system.detached_parts表中name列的值。比如:ALTER TABLE visits ATTACH PART '201901_1_1_0'.

    指定分区时引号的用法取决于分区表达式的类型。 例如,对于String类型,您必须在引号(')中指定其名称。 对于Date和Int *类型,不需要引号。

    以上所有规则对于OPTIMIZE查询也适用。 如果在优化非分区表时仅需要指定分区,请设置表达式PARTITION tuple()。 例如:OPTIMIZE TABLE table_not_partitioned PARTITION tuple() FINAL;

    ALTER ... PARTITION查询,在测试项00502_custom_partitioning_local和00502_custom_partitioning_replicated_zookeeper中进行了演示。

    删除操作(ALTER TABLE … DELETE)

    ALTER TABLE [db.]table [ON CLUSTER cluster] DELETE WHERE filter_expr
    

    允许删除与指定过滤表达式匹配的数据。 使用mutation.

    ALTER TABLE前缀使该语法不同于大多数其他支持SQL的系统。 旨在表明,与OLTP数据库中的类似查询不同,这是一项繁重的操作,并非为频繁使用而设计。

    filter_expr必须为UInt8类型。该查询会删除表中那些表达式非零的行。

    一个查询可以包含多个用逗号分隔的命令。

    查询处理的同步性是由mutations_sync设置定义的。 默认情况下,它是异步的。

    相关:

    更新操作(ALTER TABLE … UPDATE)

    ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE filter_expr
    

    允许处理与指定过滤表达式匹配的数据。使用mutation.

    ALTER TABLE前缀使该语法不同于大多数其他支持SQL的系统。 旨在表明,与OLTP数据库中的类似查询不同,这是一项繁重的操作,并非为频繁使用而设计。

    filter_expr必须为UInt8类型。该查询会更新表中那些表达式非零的行。

    使用CAST运算符将值强制转换为列类型。用于计算主键或分区键的列不支持更新。

    一个查询可以包含多个用逗号分隔的命令。

    查询处理的同步性是由mutations_sync设置定义的。 默认情况下,它是异步的。

    相关:

    ORDER BY表达式(Manipulating Key Expressions)

    ALTER TABLE [db].name [ON CLUSTER cluster] MODIFY ORDER BY new_expression
    

    该命令将表的排序键更改为new_expression(一个表达式或表达式的元组)。 主键保持不变。

    该命令是轻量级的,因为它仅更改元数据。 

    为了保持数据属性,数据部分的行由排序键表达式排序,您不能将包含现有列的表达式添加到排序键(仅能使用在ALTER查询中由ADD COLUMN命令添加的列)。

    它仅适用于MergeTree系列中的表(包括复制表)。

    SAMPLE BY表达式(Manipulating Sampling-Key Expressions)

    ALTER TABLE [db].name [ON CLUSTER cluster] MODIFY SAMPLE BY new_expression
    

    该命令将表的采样键更改为new_expression(表达式或表达式元组)。

    该命令是轻量级的,因为它仅更改元数据。 主键必须包含新的示例键。

    它仅适用于MergeTree系列中的表(包括复制表)。

    INDEX(Manipulating Data Skipping Indices)

    提供以下操作:

    • ALTER TABLE [db].name ADD INDEX name expression TYPE type GRANULARITY value AFTER name [AFTER name2] - 将索引描述添加到表元数据。

    • ALTER TABLE [db].name DROP INDEX name - 从表元数据中删除索引描述,并从磁盘中删除索引文件。

    • ALTER TABLE [db.]table MATERIALIZE INDEX name IN PARTITION partition_name - 该查询将在分区partition_name中使用mutation重建索引。

    前两个命令是轻量级的,从某种意义上说,它们仅更改元数据或删除文件。

    同样,它们会被应用到复制表,并通过ZooKeeper同步索引元数据。

    它仅适用于MergeTree系列中的表(包括复制表)。

    CONSTRAINT(Manipulating Constraints)

    可以使用以下语法添加或删除约束:

    ALTER TABLE [db].name ADD CONSTRAINT constraint_name CHECK expression;
    ALTER TABLE [db].name DROP CONSTRAINT constraint_name;
    

    查看更多关于 constraints 的信息。

    查询将在表中添加或删除有关约束的元数据,因此将立即对其进行处理。

    修改约束不会对现有数据执行。

    复制表上的所有更改都将广播到ZooKeeper,并且也将应用于其他副本。

    TTL(Manipulations with Table TTL)

    您可以使用以下格式的请求来更改 table TTL :

    ALTER TABLE table-name MODIFY TTL ttl-expression
    

    ALTER USER

    更改ClickHouse用户帐户。

    ALTER USER [IF EXISTS] name [ON CLUSTER cluster_name]
        [RENAME TO new_name]
        [IDENTIFIED [WITH {PLAINTEXT_PASSWORD|SHA256_PASSWORD|DOUBLE_SHA1_PASSWORD}] BY {'password'|'hash'}]
        [[ADD|DROP] HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
        [DEFAULT ROLE role [,...] | ALL | ALL EXCEPT role [,...] ]
        [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]
    

    要使用ALTER USER,您必须具有ALTER USER特权。通过设置users.xml中<access_management>为1.

    --将角色role1,role2设置为默认角色:
    ALTER USER user DEFAULT ROLE role1, role2
    --如果角色没有预先分配给用户,则ClickHouse会引发异常。
    
    --将所有分配的角色设置为默认:
    ALTER USER user DEFAULT ROLE ALL
    --如果将来将角色分配给用户,它将自动成为默认角色。
    
    --将所有分配的角色设置为默认角色,除了role1和role2:
    ALTER USER user DEFAULT ROLE ALL EXCEPT role1, role2

    ALTER QUOTA

    更改配额。

    ALTER QUOTA [IF EXISTS] name [ON CLUSTER cluster_name]
        [RENAME TO new_name]
        [KEYED BY {'none' | 'user name' | 'ip address' | 'client key' | 'client key or user name' | 'client key or ip address'}]
        [FOR [RANDOMIZED] INTERVAL number {SECOND | MINUTE | HOUR | DAY | WEEK | MONTH | QUARTER | YEAR}
            {MAX { {QUERIES | ERRORS | RESULT ROWS | RESULT BYTES | READ ROWS | READ BYTES | EXECUTION TIME} = number } [,...] |
            NO LIMITS | TRACKING ONLY} [,...]]
        [TO {role [,...] | ALL | ALL EXCEPT role [,...]}]
    

    ALTER ROLE

    更改角色

    ALTER ROLE [IF EXISTS] name [ON CLUSTER cluster_name]
        [RENAME TO new_name]
        [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]
    

    ALTER ROW POLICY

    更改行策略

    ALTER [ROW] POLICY [IF EXISTS] name [ON CLUSTER cluster_name] ON [database.]table
        [RENAME TO new_name]
        [AS {PERMISSIVE | RESTRICTIVE}]
        [FOR SELECT]
        [USING {condition | NONE}][,...]
        [TO {role [,...] | ALL | ALL EXCEPT role [,...]}]
    

    ALTER SETTINGS PROFILE

    更改设置配置文件。

    ALTER SETTINGS PROFILE [IF EXISTS] TO name [ON CLUSTER cluster_name]
        [RENAME TO new_name]
        [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | INHERIT 'profile_name'] [,...]
    

     

    展开全文
  • SQL Server中alter table一些用法.doc
  • alter table添加表约束

    2019-12-06 14:51:11
    alter table添加表约束 翻阅了一下网上关于alter table添加表约束的资料,学习下,然后供自己以后使用。 仅仅供自己使用。。。 总结alter table ### add constraint ## 使用方法 添加表约束 首先看下alter table的...

    alter table添加表约束
    翻阅了一下网上关于alter table添加表约束的资料,学习下,然后供自己以后使用。

    仅仅供自己使用。。。

    总结alter table ### add constraint ## 使用方法

    添加表约束

    首先看下alter table的定义(百度百科):

    Alter table,网络程序及编程中所用的术语。通过更改、添加、除去列和约束,或者通过启用或禁用约束和触发器来更改表的定义。

    数据库SQL语言的修改语句,可以用来修改基本表,其一般表示格式为:

    ALTER TABLE<表名>[改变方式]

    改变方式:

    · 加一个栏位: ADD “栏位 1” “栏位 1 资料种类”

    · 删去一个栏位: DROP “栏位 1”

    · 改变栏位名称: CHANGE “原本栏位名” “新栏位名” “新栏位名资料种类”

    · 改变栏位的资料种类: MODIFY “栏位 1” “新资料种类”

    由上可以看出,修改基本表提供如下四种修改方式:

    (1)ADD方式:用于增加新列和完整性约束,列的定义方式同CREARE TABLE语句中的列定义方式相同,其语法格式:

    ALTER TABLE <表名> ADD <列定义>|<完整性约束>。由于使用此方式中增加的新列自动填充NULL值,所以不能为增加的新列指定NOT NULL约束。

    (2)DROP方式:用于删除指定的完整性约束条件,或删指定的列,其语法格式为:

    ALTER TABLE<表名> DROP [<完整性约束名>]

    ALTER TABLE<表名> DROP COLUMN <列名>

    注释:某些数据库系统不允许这种在数据库表中删除列的方式 (DROP COLUMN <列名>)。

    (3)CHANGE方式,用于修改某些列,其语法格式:

    ALTER TABLE [表名] CHANGE <原列名> TO <新列名><新列的数据类型>

    (4)MODIFY方式,用于修改某些列的数据类型,其语法格式:

    ALTER TABLE [表名] MODIFY [列名] [数据类型]

    先创建的库个表 如下 : 下面示例用到表结构

    create database Text_DB

    go

    use Text_DB

    go

    create table Teacher

    (

    TeaId int not null ,

    TeaName varchar(20) not null,

    TeaAge int ,

    TeaAddress varchar(50)

    )–为了更直观的看到效果 这里就先不添加约束

    一.Alter 主要作用针对是给已经创建的表进行添加和修改删除约束 的操作

    1.给已经创建的表添加约束

    分为两种方式,一种是在创建约束 并同时给所创约束定义一个名字 这样可以再次进行操作,比如删除时候要用到

    另一种方式是直接创建约束 不命名的,但是再次操作时候是不方便的

    第一种:

    Alter table 表名 add constraint 约束 字 约束类型(列名)

    例:

    -----添加约束(命名)-----------

    alter table Teacher add constraint PK_1 primary key (TeaId)–主键约束

    alter table Teacher add constraint UN_1 unique(TeaName)–唯一约束

    alter table Teacher add constraint CK_1 check (TeaAge>0)–范围约束

    alter table Teacher add constraint DE_1 default ‘123’ for TeaAddress --默认约束

    –外键约束,这里就不一一举例了,语法如下

    alter table 表名 add constraint 约束名 foreign key(关联字段) references 主表(关联字段)

    第二种:

    Alter table 表名 add 约束(列名)

    例: 分别给 Teacher表中添加 主键 唯一 和范围约束

    alter table Teacher add primary key (TeaId)–主键

    alter table Teacher add unique(TeaName)–唯一

    alter table Teacher add check (TeaAge>0)–范围约束

    alter table Teacher add default ‘123’ for TeaAddress --默认约束

    alter table 表名 add foreign key(列名) references 主表(列名)–外键

    2.删除约束: 语法

    alter table 表名 drop constraint 约束名–删除约束

    例:(删除Teacher表中的约束)

    alter table Teacher drop constraint PK_1 --删除主键约束

    alter table Teacher drop constraint UN_1 --删除唯一约束

    alter table Teacher drop constraint CK_1 --删除范围约束

    alter table Teacher drop constraint DE_1 --删除默认约束

    二. Alter table 用法 针对表结构进行修改,比如添加列删除列等等…

    1.添加列 语法

    Alter table 表名 ADD 列名 数据类型

    例: (给Teacher 表中添加一列 notes 数据类型为varchar(200))

    alter table Teacher add notes varchar(200)

    2.删除表中的列 语法

    Alter table 表名 drop column 列名

    例:(删除表中的notes列)

    alter table Teacher drop column notes

    3.修改某一列中的数据类型语法

    Alter table 表名 alter column 列名 数据类型

    例:(将Teacher 表中的notes列更改为int类型)

    alter table Teacher alter column notes int

    ALTER TABLE ADD增加多个字段外键约束

    /* 创建表 及设置 主键 */

    CREATE TABLE TABLE_USER

    (USER_ID INT IDENTITY(1,1) NOT NULL,

    USER_NAME NVARCHAR(40) NOT NULL,

    LAST_UPDATED_BY NVARCHAR(15),

    LAST_UPDATED_DATE DATETIME,

    CONSTRAINT USER_PK PRIMARY KEY (USER_ID)

    );

    /*ALTER TABLE 增加多个字段 */

    ALTER TABLE TABLE_USER ADD

    DEPARTMENT_ID INT NOT NULL,COMPANY_ID INT NOT NULL,TEMP_COL NVARCHAR(10);

    /*ALTER TABLE 增加多个外键 */

    ALTER TABLE TABLE_USER ADD

    CONSTRAINT USER_DEPARTMENT_FK FOREIGN KEY(DEPARTMENT_ID) REFERENCES TABLE_DEPARTMENT(DEPARTMENT_ID) ,CONSTRAINT USER_COMPANY_FK FOREIGN KEY(COMPANY_ID) REFERENCES TABLE_COMPANY(COMPANY_ID) ;

    /*ALTER TABLE 删除字段 */

    ALTER TABLE TABLE_USER DROP COLUMN TEMP_COL;

    –删除约束 --先查出约束名 EXEC sys.sp_helpconstraint @objname = N’bulkinserttest’ --表名 ALTER TABLE dbo.bulkinserttest DROP CONSTRAINT ck_bulkinsert

    例子:

    向表中增加一个 varchar 列:

    ALTER TABLE distributors ADD address varchar(30);

    从表中删除一个字段:

    ALTER TABLE distributors DROP COLUMN address RESTRICT;

    在一个操作中修改两个现有字段的类型:

    ALTER TABLE distributors

    ALTER COLUMN address TYPE varchar(80),

    ALTER COLUMN name TYPE varchar(100);

    使用一个 USING 子句, 把一个包含 UNIX 时间戳的 integer 字段转化成 timestamp with time zone:

    ALTER TABLE foo

    ALTER COLUMN foo_timestamp TYPE timestamp with time zone

    USING

    timestamp with time zone ‘epoch’ + foo_timestamp * interval ‘1 second’;

    对现存字段改名:

    ALTER TABLE distributors RENAME COLUMN address TO city;

    更改现存表的名字:

    ALTER TABLE distributors RENAME TO suppliers;

    给一个字段增加一个非空约束:

    ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

    从一个字段里删除一个非空约束:

    ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;

    给一个表增加一个检查约束:

    ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);

    删除一个表和它的所有子表的检查约束:

    ALTER TABLE distributors DROP CONSTRAINT zipchk;

    向表中增加一个外键约束:

    ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses(address) MATCH FULL;

    给表增加一个(多字段)唯一约束:

    ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);

    给一个表增加一个自动命名的主键约束,要注意的是一个表只能有一个主键:

    ALTER TABLE distributors ADD PRIMARY KEY (dist_id);

    把表移动到另外一个表空间:

    ALTER TABLE distributors SET TABLESPACE fasttablespace;

    1. 主键约束

    要对一个列加主键约束的话,这列就必须要满足的条件就是非空。因为主键约束:就是对一个列进行了约束,约束为(非空、不重复)。

    【格式】alter table 表格名称 add constraint 约束名称 增加的约束类型 (列名)

    例子:要对一个列加主键,列名为id,表名为emp

    alter table emp add constraint ppp primary key (id)

    1. check约束

    就是给一列的数据进行了限制

    【格式】alter table 表名称 add constraint 约束名称 增加的约束类型 (列名)

    例子:年龄列的数据都要大于20,表名(emp) 列名(age)

    alter table emp add constraint xxx check(age>20)

    某一列(sex)取值只能为“男”和“女”的约束表达式:

    ALTER TABLE 表的名称 ADD CONSTRAINT 约束的名称 CHECK (sex in (‘男’,‘女’))

    1. unique约束

    这样的约束就是给列的数据追加的不重复的约束类型

    【格式】alter table 表名 add constraint 约束名称 约束类型(列名)

    例子:给ename列加个unique,让ename列的数据不重复

    alter table emp add constraint qwe unique(ename)

    1. 默认约束

    意思很简单,就是让此列的数据默认为一定的数据

    【格式】alter table 表名称 add constraint 约束名称 约束类型 默认值 for 列名

    例子:emp表中的gongzi列默认为10000

    alter table emp add constraint jfsd default 10000 for gongzi

    1. 外键约束

    这个有点难理解了,外键其实就是引用。

    因为主键实现了实体的完整性,外键实现了引用的完整性,应用完整性规定,所引用的数据必须存在!其实就是个引用。

    【格式】alter table 表名 add constraint 约束名称 约束类型 (列名) references 被引用的表名称 (列名)

    例子:

    一个表名称叫dept 里面有2列数据 一列是ID一列是ENAME

    id:表示产品的编号

    ename:表示产品的名称

    另外一个表格名称是emp 里面有2列数据,一列是ID 一列是DID

    id:表示用户号

    did:表示购买的产品号

    要让emp表中的did列去引用dept表中的id

    可以用下面的方法

    alter table emp add constraint jfkdsj foreign key (did) references dept (id)

    转自:https://www.cnblogs.com/zz-blog/p/8250779.html

    展开全文
  • SQL Server ALTER TABLE ADD列概述

    千次阅读 2020-07-24 14:39:54
    In this article, we will explore SQL Server ALTER TABLE ADD Column statements to add column(s) to an existing table. We will also understand the impact of adding a column with a default value and ...

    In this article, we will explore SQL Server ALTER TABLE ADD Column statements to add column(s) to an existing table. We will also understand the impact of adding a column with a default value and adding and updating the column with a value later on larger tables.

    在本文中,我们将探索SQL Server ALTER TABLE ADD Column语句,以将列添加到现有表中。 我们还将理解在较大的表上添加具有默认值的列以及添加和更新具有值的列的影响。

    As a database developer, you need to add columns to the existing tables too offen. You would think that adding a column to the SQL Server database table would not be a major deal. Sometimes you might be adding the column from the SQL Server Management Studio itself. Well, that would be fine for a small table or a table which does not have a large number of transactions. Let’s go ahead and see how we can add columns to an existing table and also understand the reasons and the best practices to add a column to a table that is large in size.

    作为数据库开发人员,您还需要向现有表中添加列。 您可能会认为向SQL Server数据库表中添加列并不是一件大事。 有时您可能是从SQL Server Management Studio本身添加该列。 好吧,这对于较小的表或没有大量事务的表会很好。 让我们继续前进,看看如何将列添加到现有表中,并了解向大型表添加列的原因和最佳实践。

    样品表 (Sample Table)

    Let us create a sample table with sufficient data set from the following script.

    让我们用以下脚本创建一个具有足够数据集的示例表。

    IF EXISTS (SELECT 1 FROM Sys.tables where Name = 'SampleTable')
    DROP TABLE SampleTable
     
    CREATE TABLE dbo.SampleTable (
       ID BIGINT IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED,
       DateTime4 DATETIME DEFAULT GETDATE(),
       Column1 CHAR(1000) DEFAULT 'MMMMMMMMMMMMMMMMM',
       Column2 CHAR(2000) DEFAULT 'YYYYYYYYYYYYYYYYY'
    )
    

    The above script will create a sample table called SampleTable. Data fields are added so that the large size table will be created. Next, a large number of records were added by executing the following query multiple times.

    上面的脚本将创建一个名为SampleTable的示例表。 添加了数据字段,以便创建大型表。 接下来,通过多次执行以下查询来添加大量记录。

    SET NOCOUNT ON
    INSERT INTO SampleTable
    (DateTime4, Column1, Column2)
    VALUES
    (GETDATE(),'XXXX','YYYY')
    GO 500000
    

    After the above query is executed, 500,000 records are updated to the SampleTable. After executing the above query following is the table size and other parameters for the table.

    执行以上查询后,将500,000条记录更新到SampleTable 。 执行上述查询后,下面是表的大小和该表的其他参数。

    This can be retrieved by sp_spaceused ‘SampleTable’

    可以通过sp_spaceused'SampleTable'进行检索

    Table size and other parameters

    The following is the database size for data and the log file.

    以下是数据和日志文件的数据库大小。

    Database file sizes, transaction log file.

    Let’s quickly go over the syntax of adding one column to an existing table by using ALTER TABLE ADD statement as shown below.

    让我们快速遍历使用ALTER TABLE ADD语句向现有表中添加一列的语法,如下所示。

    ALTER TABLE tbl_name
    ADD Col_name  data_type  col_constraint;
    

    You can use the below statement to add column NewColumn1 to our table SampleTable.

    您可以使用以下语句将NewColumn1列添加到我们的表SampleTable中。

    ALTER TABLE SampleTable
    ADD NewColumn1 varchar(200)
    

    Also, you can add multiple columns to a table using the single SQL Server ALTER TABLE statement as below.

    另外,您可以使用单个SQL Server ALTER TABLE语句将多个列添加到表中,如下所示。

    ALTER TABLE SampleTable
    ADD NewColumn2 varchar(20),
    NewColumn3 varchar(50)
    

    When adding a column to a large table, typically you want to add the column and fill the added column with a default value. For example, if you want to add a status column, next is to fill the column with a default value.

    将列添加到大型表时,通常需要添加该列,并使用默认值填充添加的列。 例如,如果要添加状态列,则下一步是用默认值填充该列。

    添加具有默认约束的列 (Adding a Column with a Default Constraints)

    To achieve the above objective, the easiest way is to add a column with a default constraint. When the default column is added, the default value will be added to the table. The following script makes use of SQL Server ALTER TABLE ADD Column (Status in our case) statement to add a column named Status with default constraint.

    为了实现上述目的,最简单的方法是添加具有默认约束的列。 添加默认列后,默认值将添加到表中。 以下脚本利用SQL Server ALTER TABLE ADD Column(在本例中为Status)语句添加具有默认约束的名为Status的列。

    ALTER TABLE SampleTable 
    ADD Status CHAR(5000) 
    DEFAULT 'INC' WITH VALUES
    

    When the column is added as above, the Status column will be added with the Value INC for all records.

    当按上述方式添加列时,将为所有记录添加“状态”列和“值INC”。

    From the profiler following statics are captured via SQL Profiler during the column addition with default values.

    在添加默认值的列期间,通过SQL事件探查器从事件探查器捕获以下静态信息。

    CPU

    0

    Reads

    185

    Writes

    0

    Duration Mille Seconds

    65

    中央处理器

    0

    185

    0

    持续时间毫秒

    65

    You can see that the column is added to the table even less than one second and operation is very minimal cost.

    您可以看到该列添加到表中的时间甚至不到一秒钟,并且操作成本非常低。

    The following are the locking stats during the column that are added with a constraint.

    以下是在列期间添加了约束的锁定状态。

    Resource Type

    Resource Subtype

    Request Mode

    Records Count

    DATABASE

    S

    1

    DATABASE

    DDL

    S

    1

    KEY

    X

    11

    METADATA

    DATA_SPACE

    Sch-S

    1

    OBJECT

    IX

    6

    OBJECT

    Sch-M

    2

    资源类型

    资源子类型

    请求模式

    记录数

    数据库

    小号

    1个

    数据库

    DDL

    小号

    1个

    X

    11

    元数据

    DATA_SPACE

    Sch-S

    1个

    目的

    6

    目的

    Sch-M

    2

    Please note that the following query should be executed in an open transaction in order to capture the above locking statistics.

    请注意,以下查询应在打开的事务中执行,以捕获上述锁定统计信息。

    SELECT
    resource_type,
    resource_subtype,
    request_mode,
    COUNT(*) FROM sys.dm_tran_locks
    WHERE request_session_id = @@SPID
    GROUP BY resource_type,
    resource_subtype,
    request_mode
    

    This shows that Table (Object) has intended Exclusive lock which means that the table is not exclusively locked during the addition of the column. Also, adding a column with default value has not taken even a one minute though it has 500,000 records.

    这表明表(对象)具有预期的排他锁,这意味着在添加列期间表未排它锁。 同样,添加具有默认值的列即使有500,000条记录,也花了一分钟的时间。

    Let us see the table size.

    让我们看看桌子的大小。

    Table size after adding a column with default constraint using SQL Server ALTER TABLE clause.

    You will see that nothing has changed.

    您会看到什么都没有改变。

    Let us see the file sizes of the database.

    让我们看看数据库的文件大小。

    database size and transaction log file after adding a column with default constraint using SQL Server ALTER TABLE Add statement.

    Nothing much has changed to the data file as well as for the log file. All of these results indicate that adding a column with a default constraint will result in only a metadata change.

    数据文件和日志文件没有任何变化。 所有这些结果表明,添加具有默认约束的列只会导致元数据发生变化。

    用值更新 (Update with a Value)

    Let us update the same column with a different value and let us get the same stats.

    让我们用不同的值更新同一列,让我们获得相同的统计信息。

    Resource Type

    Request Mode

    Records Count

    DATABASE

    S

    1

    OBJECT

    X

    1

    资源类型

    请求模式

    记录数

    数据库

    小号

    1个

    目的

    X

    1个

    The above table shows that the table is exclusively locked which means that the table is not accessible during the update.

    上表显示该表被独占锁定,这意味着该表在更新期间不可访问。

    Let us look at the table size.

    让我们看看表的大小。

    Table size after adding and updating the column,

    The table has grown by some value as shown in the above figure.

    该表已增长了一些值,如上图所示。

    The following are the details for the query expenses captured from the SQL Profiler.

    以下是从SQL事件探查器捕获的查询费用的详细信息。

    CPU

    29,781

    Reads

    9,188,986

    Writes

    527,436

    Duration Mille Seconds

    1,113,142

    中央处理器

    29,781

    9,188,986

    527,436

    持续时间毫秒

    1,113,142

    Evidently, when updating a column for a large table, resource consumption is high.

    显然,当更新大表的列时,资源消耗很高。

    The following is the database file sizes when the column is added and updated the values.

    以下是添加和更新列时的数据库文件大小。

    database size and transaction log file after adding a column and updating the column.

    These stats show that there is a remarkable difference between adding a column with a default value and adding a column and updating the column with a value later. During the column update, the transaction log will grow and exclusive locking will be placed on the table prohibiting any reads or writes to the table.

    这些统计数据表明,添加具有默认值的列与添加列以及稍后更新具有值之间的区别显着。 在列更新期间,事务日志将增长,并且排他锁将放置在表上,以禁止对该表进行任何读取或写入。

    What is the difference in these scenarios? Prior to the SQL Server 2012, when adding a column with default value will cause the same behavior. In SQL Server 2012 and onwards, this is no longer the situation, the column is added online to the table and no update occurs and it is only a metadata change.

    在这些情况下有什么区别? 在SQL Server 2012之前,添加具有默认值的列将导致相同的行为。 在SQL Server 2012及更高版本中,这种情况不再存在,该列已在线添加到表中,并且没有更新发生,而只是元数据更改。

    如何做到这一点 (How This is Achieved)

    This is achieved by a somewhat very simple but novel approach. sys.system_internals_partition_columns DMV has two additional columns named has_default and default_value as shown below.

    这是通过某种非常简单但新颖的方法来实现的。 sys.system_internals_partition_columns DMV具有另外两个名为has_defaultdefault_value的列,如下所示。

    sys.system_internals_partition_columns dmv

    So when the column added with a default value, it will not update the data page instead it will update this system table. When a row is updated, then the default value will be pushed to the table even if the default value column is not updated.

    因此,当添加默认值的列时,它将不会更新数据页,而是会更新此系统表。 当更新一行时,即使默认值列未更新,默认值也会被推送到表中。

    Now, the next question is what if the Default constraint is dropped just after it is created. In that scenario, still, the above setting will prevail hence the previously set default value will be kept.

    现在,下一个问题是如果在创建默认约束后立即删除默认约束。 在这种情况下,仍然会使用上述设置,因此将保留先前设置的默认值。

    结论
    (Conclusion
    )

    We covered the basic syntax of SQL Server ALTER TABLE in this article and implemented it to add columns to an existing table.

    我们在本文中介绍了SQL Server ALTER TABLE的基本语法,并实现了将其添加到现有表中的列。

    Before SQL Server 2012, there was no difference between adding a column with the default and adding a column and updating it. Therefore, in the prior SQL Server 2012 era, rather than adding a column with a default constraint, it is better to add a column and updating with batches so that the table is not exclusively locked.

    在SQL Server 2012之前,添加具有默认值的列与添加列并对其进行更新之间没有区别。 因此,在以前SQL Server 2012时代,与其添加具有默认约束的列,不如添加一个列并进行批处理更新,以使该表不被排他地锁定,这更好。

    However, from SQL Server 2012 onwards, the approach has changed so that adding a column with default constraints is much better.

    但是,从SQL Server 2012起,此方法已更改,因此添加具有默认约束的列要好得多。

    翻译自: https://www.sqlshack.com/sql-server-alter-table-add-column-overview/

    展开全文
  • Mysql 常见ALTER TABLE操作

    千次阅读 2021-01-14 11:26:09
    alter table table_name drop col_name; 增加列(单列) alter table table_name add col_name col_type comment 'xxx'; 增加列(多列) alter table table_name add col_name col_type comment 'xxx', add col_name...
  • Hive:Alter Table/Partition/Column

    千次阅读 2019-03-11 17:29:31
    Alter Table/Partition/Column Alter Table Rename Table Alter Table Properties Alter Table Comment Add SerDe Properties Alter Table Storage Properties Alter Table Skewed or Stored as Directories A...
  • ALTER TABLE Time: 319.677 ms postgres=# postgres=# alter table tmp_t4 alter column memo2 drop not null; ALTER TABLE Time: 82.202 ms 参考: http://postgres.cn/docs/10/sql-altertable.html
  • MYSQL ALTER命令

    千次阅读 2018-08-31 17:54:05
    当需要修改数据表名或者修改数据表字段时,就需要使用到MySQL ALTER命令。 修改表名 语法格式:ALTER TABLE old_table_name RENAME TO new_table_name 查看表结构 SHOW COLUMNS FROM table_name 查看数据表...
  • 以下的文章主要介绍的是MySQL ALTER语法的实际运用,我们大家都知道MySQL ALTER语法在实际应用中的比例还是很大的,所以了解一下MySQL ALTER语法的实际运用可以很好的选择。 MySQL ALTER语法中ALTER [IGNORE] TABLE ...
  • ALTER TABLE 语句

    千次阅读 2019-07-04 16:11:58
    ALTER TABLE 语句用于在已有的表中添加、修改或删除列。 1 . ALTER TABLE table_name ADD column_name datatype 2. ALTER TABLE table_name DROP COLUMN column_name 3. ALTER TABLE table_name ALTER COLUMN column...
  • 最近看到这样的案例:1、应用需要为现有的表添加列2、应用执行ALTER TABLE ADD COLUMN语句3、其他每个查询都需要被阻塞几分钟甚至更长时间为什么出现这种情...
  • HiveQL(三):修改表ALTER TABLE

    千次阅读 2019-05-04 17:40:03
    大多数的表属性可以通过ALTER TABLE语句来进行修改。这种操作会修改元数据,但不会修改数据本身,比如修改表模式中出现的错误、改变分区路径等。 1.1 表重命名 使用以下语句可将表log_messages重命名为logmsgs: ...
  • MySQL的ALTER命令

    千次阅读 2018-11-11 15:30:14
    当我们需要修改数据表名或者修改数据表字段时,就需要使用到MySQL ALTER命令。 官方文档:https://dev.mysql.com/doc/refman/5.6/en/alter-table.html 1:删除列 ALTER TABLE 【表名字】 DROP 【列名称】 2:增加列 ...
  • MySQL ALTER命令

    千次阅读 2017-04-12 10:21:28
    当需要修改数据表名或者修改数据表字段时,就需要使用到MySQL ALTER命令。 我们先创建一张表,表名为:testalter_tbl。 MariaDB [RUNOOB]> create table testalter_tbl -> ( -> i INT, -> c CHAR(1) -> );...
  • oracle ALTER 用法

    千次阅读 2019-07-11 09:55:16
    alter语句的用法 alter语句的用法 需要修改结构的,就用到alter语句,方法如下: ALTER TABLE语句用于修改已经存在的表的设计。 语法:ALTER TABLE table ADD COLUMN field type[(size)] [NOT NUL...
  • ALTER语句

    千次阅读 2019-01-30 21:56:32
    alter table test_a rename to sys_app; 2.修改表注释 alter table sys_application comment ‘系统信息表’; 二:修改字段信息 1.修改字段类型和注释 alter table sys_application modify column app_name varchar...
  • SQL的alter用法总结

    万次阅读 多人点赞 2019-09-01 08:31:49
    ALTER TABLE 表名 DROP COLUMN 列名 2:增加列 ALTER TABLE 表名 ADD COLUMN 列名 属性 【约束】 3:修改列的类型信息 ALTER TABLE 表名 CHANGE COLUMN 列名 【新】列名 新属性 4:重命名列 ALTER TABLE 表名字 ...
  • alter 语句

    千次阅读 2018-07-28 16:21:48
     alter table TbStudent alter column stuGender nchar (1 )  --4.添加主键约束  alter table TbStudent add constraint PK TbStudent stuId primary key (stuId)  --5.添加唯一性约束  alter table ...
  • MySQL使用ALTER USER修改密码...alter user test identified by '123456'; 使用user()可以获取当前用户名 alter user user() identified by 'aaaaaa'; 参考链接: https://www.hxstrive.com/article/728.htm ...
  • alter与alert区别

    2020-09-09 09:26:08
    alter: sql语法:修改 alert:js 弹出框
  • Mysql数据库alter修改表

    千次阅读 2020-04-02 18:29:48
    如果你想要修改表的信息,你会发现alter很强大。 我们可以看到这样一张表。 CREATE TABLE `score` ( `student_id` int(10) unsigned NOT NULL, `event_id` int(10) unsigned NOT NULL, `score` int(11) DEFAULT ...
  • SQL alter操作

    千次阅读 2018-07-10 22:40:09
    ALTER TABLE tal_name ALTER [COLUMN] col_name {SET DEFAULT literal|DROP DEFAULT} #添加默认约束 ALTER TABLE t_user ALTER user_name SET DEFAULT 'zhangsan'; #删除默认约束 ALTER TABLE t_user ALTER ...
  • PostgreSQL ALTER TABLE 命令 在 PostgreSQL 中,ALTER TABLE命令用于添加,修改,删除一张已经存在表的列。 另外你也可以用ALTER TABLE命令添加和删除约束。 语法 用 ALTER TABLE 在一张已存在的表上添加列的...
  • postgresql 修改表结构:修改t_cash表list_id 字段类型为varchar(19) alter table t_cashalter column list_Id type varchar(19);
  • MYSQL ALTER TABLE命令用于修改表结构,例如添加/修改/删除字段、索引、主键等等,本文章通过实例向大家介绍MYSQL ALTER TABLE语句的使用方法,  MySQL ALTER语法如下: ALTER [IGNORE] TABLE tbl_name alter_spec...
  • 3、alter table语句的用法

    千次阅读 2020-09-17 17:04:27
    一、alter table对列的操作 二、 alter table是对已有的表中的列进行添加、删除修改 #在表中添加列 alter table 表名 add 列名 指定列表数据类型 #在表中删除列 alter table 表名 drop column 列名 (禁用) #...
  • 修改表和约束(alter语句)

    千次阅读 2019-09-11 19:49:14
     alter table t_user  add birthday date;  / /删除表的某列 drop  alter table t_user  drop column birthday;  //给表中的列添加约束 add constraint  //这个约束相当于之前的表级约束  ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 637,977
精华内容 255,190
关键字:

alter