mysql2012更改表名_怎样用SQL语句修改表名与表中的列名?

展开全部

|ALTER TABLE table

{ [ ALTER COLUMN column_name

{ new_data_type [ ( precision [ , scale ] ) ]

[ COLLATE < collation_name > ]

[ NULL |62616964757a686964616fe4b893e5b19e31333231393634 NOT NULL ]

| {ADD | DROP } ROWGUIDCOL }

]

| ADD

{ [ < column_definition > ]

| column_name AS computed_column_expression

} [ ,...n ]

| [ WITH CHECK | WITH NOCHECK ] ADD

{ < table_constraint > } [ ,...n ]

| DROP

{ [ CONSTRAINT ] constraint_name

| COLUMN column } [ ,...n ]

| { CHECK | NOCHECK } CONSTRAINT

{ ALL | constraint_name [ ,...n ] }

| { ENABLE | DISABLE } TRIGGER

{ ALL | trigger_name [ ,...n ] }

}

< column_definition > ::=

{ column_name data_type }

[ [ DEFAULT constant_expression ] [ WITH VALUES ]

| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]

]

[ ROWGUIDCOL ]

[ COLLATE < collation_name > ]

[ < column_constraint > ] [ ...n ]

< column_constraint > ::=

[ CONSTRAINT constraint_name ]

{ [ NULL | NOT NULL ]

| [ { PRIMARY KEY | UNIQUE }

[ CLUSTERED | NONCLUSTERED ]

[ WITH FILLFACTOR = fillfactor ]

[ ON { filegroup | DEFAULT } ]

]

| [ [ FOREIGN KEY ]

REFERENCES ref_table [ ( ref_column ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

[ NOT FOR REPLICATION ]

]

| CHECK [ NOT FOR REPLICATION ]

( logical_expression )

}

< table_constraint > ::=

[ CONSTRAINT constraint_name ]

{ [ { PRIMARY KEY | UNIQUE }

[ CLUSTERED | NONCLUSTERED ]

{ ( column [ ,...n ] ) }

[ WITH FILLFACTOR = fillfactor ]

[ ON { filegroup | DEFAULT } ]

]

| FOREIGN KEY

[ ( column [ ,...n ] ) ]

REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

[ NOT FOR REPLICATION ]

| DEFAULT constant_expression

[ FOR column ] [ WITH VALUES ]

| CHECK [ NOT FOR REPLICATION ]

( search_conditions )

}

示例

A. 更改表以添加新列

下例添加一个允许空值的列，而且没有通过 DEFAULT 定义提供值。各行的新列中的值将为 NULL。

CREATE TABLE doc_exa ( column_a INT)

GO

ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL

GO

EXEC sp_help doc_exa

GO

DROP TABLE doc_exa

GO

B. 更改表以除去列

下例修改表以删除一列。

CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL)

GO

ALTER TABLE doc_exb DROP COLUMN column_b

GO

EXEC sp_help doc_exb

GO

DROP TABLE doc_exb

GO

C. 更改表以添加具有约束的列

下例向表中添加具有 UNIQUE 约束的新列。

CREATE TABLE doc_exc ( column_a INT)

GO

ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL

CONSTRAINT exb_unique UNIQUE

GO

EXEC sp_help doc_exc

GO

DROP TABLE doc_exc

GO

D. 更改表以添加未验证的约束

下例向表中的现有列上添加约束。该列中存在一个违反约束的值；因此，利用 WITH NOCHECK 来防止对现有行验证约束，从而允许该约束的添加。

CREATE TABLE doc_exd ( column_a INT)

GO

INSERT INTO doc_exd VALUES (-1)

GO

ALTER TABLE doc_exd WITH NOCHECK

ADD CONSTRAINT exd_check CHECK (column_a > 1)

GO

EXEC sp_help doc_exd

GO

DROP TABLE doc_exd

GO

E. 更改表以添加多个带有约束的列

下例向表中添加多个带有约束的新列。第一个新列具有 IDENTITY 属性；表中每一行的标识列都将具有递增的新值。

CREATE TABLE doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE)

GO

ALTER TABLE doc_exe ADD

/* Add a PRIMARY KEY identity column. */

column_b INT IDENTITY

CONSTRAINT column_b_pk PRIMARY KEY,

/* Add a column referencing another column in the same table. */

column_c INT NULL

CONSTRAINT column_c_fk

REFERENCES doc_exe(column_a),

/* Add a column with a constraint to enforce that */

/* nonnull data is in a valid phone number format. */

column_d VARCHAR(16) NULL

CONSTRAINT column_d_chk

CHECK

(column_d IS NULL OR

column_d LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" OR

column_d LIKE

"([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),

/* Add a nonnull column with a default. */

column_e DECIMAL(3,3)

CONSTRAINT column_e_default

DEFAULT .081

GO

EXEC sp_help doc_exe

GO

DROP TABLE doc_exe

GO

F. 添加具有默认值的可为空的列

下例添加可为空的、具有 DEFAULT 定义的列，并使用 WITH VALUES 为表中的各现有行提供值。如果没有使用 WITH VALUES，那么每一行的新列中都将具有 NULL 值。

ALTER TABLE MyTable

ADD AddDate smalldatetime NULL

CONSTRAINT AddDateDflt

DEFAULT getdate() WITH VALUES

G. 禁用并重新启用一个约束

下例禁用用于限制可接受的薪水数据的约束。WITH NOCHECK CONSTRAINT 与 ALTER TABLE 一起使用，以禁用该约束并使正常情况下会引起约束违规的插入操作得以执行。WITH CHECK CONSTRAINT 重新启用该约束。

CREATE TABLE cnst_example

(id INT NOT NULL,

name VARCHAR(10) NOT NULL,

salary MONEY NOT NULL

CONSTRAINT salary_cap CHECK (salary < 100000)

)

-- Valid inserts

INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)

INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)

-- This insert violates the constraint.

INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)

-- Disable the constraint and try again.

ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap

INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)

-- Reenable the constraint and try another insert, will fail.

ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap

INSERT INTO cnst_example VALUES (4,"Eric James",110000)

H. 禁用并重新启用触发器

下例使用 ALTER TABLE 的 DISABLE TRIGGER 选项来禁用触发器，以使正常情况下会违反触发器条件的插入操作得以执行。然后下例使用 ENABLE TRIGGER 重新启用触发器。

CREATE TABLE trig_example

(id INT,

name VARCHAR(10),

salary MONEY)

go

-- Create the trigger.

CREATE TRIGGER trig1 ON trig_example FOR INSERT

as

IF (SELECT COUNT(*) FROM INSERTED

WHERE salary > 100000) > 0

BEGIN

print "TRIG1 Error: you attempted to insert a salary > $100,000"

ROLLBACK TRANSACTION

END

GO

-- Attempt an insert that violates the trigger.

INSERT INTO trig_example VALUES (1,"Pat Smith",100001)

GO

-- Disable the trigger.

ALTER TABLE trig_example DISABLE TRIGGER trig1

GO

-- Attempt an insert that would normally violate the trigger

INSERT INTO trig_example VALUES (2,"Chuck Jones",100001)

GO

-- Re-enable the trigger.

ALTER TABLE trig_example ENABLE TRIGGER trig1

GO

-- Attempt an insert that violates the trigger.

INSERT INTO trig_example VALUES (3,"Mary Booth",100001)

GO

本回答被提问者采纳

已赞过

已踩过<

你对这个回答的评价是？

评论

收起