Welcome to MySQL Workbench:MySQL ALTER命令

Bol5261

已于 2024-12-14 11:09:14 修改

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

于 2020-11-17 12:25:01 首次发布

本文链接：https://blog.csdn.net/blog_programb/article/details/109740100

Sql(SQL Server Database) 同时被 3 个专栏收录

520 篇文章

订阅专栏

Mysql(MySQL AB RDBMS)

218 篇文章

订阅专栏

Index(A new Index code rule)

112 篇文章

订阅专栏

本文介绍了MySQL的ALTER命令，它是修改现有数据库表的强大工具。文中给出了ALTER TABLE命令的常见用法示例，如添加、删除或修改列，创建或删除索引等。同时提醒使用时要小心，修改现有数据前最好备份。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

欢迎来到MySQL Workbench！MySQL的ALTER命令是一个强大的工具，用于修改现有的数据库表。使用ALTER TABLE命令，您可以添加、删除或修改列，创建或删除索引，更改现有数据类型，以及执行其他许多操作。
以下是一些常见的ALTER TABLE用法示例：

添加新列:

ALTER TABLE table_name ADD column_name datatype;

例如：

ALTER TABLE employees ADD address VARCHAR(100);

删除列:

ALTER TABLE table_name DROP COLUMN column_name;

例如：

ALTER TABLE employees DROP COLUMN address;

修改列数据类型:

ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;

例如：

ALTER TABLE employees MODIFY COLUMN address VARCHAR(200);

重命名列:

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

例如：

ALTER TABLE employees RENAME COLUMN address TO contact_address;

添加主键:

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

删除主键:

ALTER TABLE table_name DROP PRIMARY KEY;

添加索引:
为了提高查询性能，您可以为表中的列添加索引。这可以通过以下命令完成：

ALTER TABLE table_name ADD INDEX index_name (column_name);

删除索引:
如果不再需要索引，您可以使用以下命令删除它：

ALTER TABLE table_name DROP INDEX index_name;

更改表名:
如果您想重命名表，可以使用以下命令：

ALTER TABLE old_table_name RENAME TO new_table_name;

添加外键约束: 外键是一个列的值，它引用另一个表的主键。这有助于保持数据的完整性和一致性。例如：

ALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES other_table(other_column);

这些是ALTER TABLE的一些基本示例。请注意，使用这些命令时必须小心，特别是当您正在修改现有数据时。在执行任何此类操作之前，最好备份您的数据。
在这里插入图片描述

MySQL ALTER命令：
当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。

CREATE TABLE test_NO1(
test_NO1_id INT NOT NULL AUTO_INCREMENT,
test_NO1_title VARCHAR(100) NOT NULL,
test_NO1_author VARCHAR(40) NOT NULL,
test_NO1_date DATE,
PRIMARY KEY ( test_NO1_id )
)ENGINE=InnoDB;

drop table test_no1;


INSERT INTO test_NO1 
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb", "welcome to programb", NOW());
    
    
select * from test_NO1;


INSERT INTO test_NO1 
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb2", "welcome to programb2", NOW());
    
INSERT INTO test_NO1 
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb3", "welcome to programb3", NOW());
    
    

SELECT * from test_NO1  WHERE test_NO1_title='programb2';

SELECT * from test_NO1;

update test_NO1 SET test_NO1_title='programb100' WHERE test_NO1_id=1;

SELECT * from test_NO1 where test_NO1_id=1;

SELECT * from test_NO1;

DELETE FROM test_NO1 WHERE test_NO1_id=1;

SELECT * from test_NO1;

 SELECT * from test_NO1  WHERE test_NO1_title LIKE '%programb2';
 
 INSERT INTO test_NO1 
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb2", "welcome.to.programb2", NOW());
 
 SELECT * from test_NO1  WHERE test_NO1_author LIKE '%programb2';
 
 SELECT * from test_NO1  WHERE test_NO1_author LIKE '%to%';
 
 SELECT * from test_NO1  WHERE test_NO1_author LIKE 'welcome%';
 
 SELECT * from test_NO1;
 
 CREATE TABLE test_NO2(
test_NO1_id INT NOT NULL AUTO_INCREMENT,
test_NO1_title VARCHAR(100) NOT NULL,
test_NO1_author VARCHAR(40) NOT NULL,
test_NO1_date DATE,
PRIMARY KEY ( test_NO1_id )
)ENGINE=InnoDB;

 INSERT INTO test_NO2
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb2", "welcome.to.programb2", NOW());
    
 SELECT * from test_NO2;
 
 SELECT test_NO1_author FROM test_no1
 UNION
 SELECT test_NO1_author FROM test_no2
 ORDER BY test_NO1_author; 
 
  SELECT test_NO1_author FROM test_no1
 UNION all
 SELECT test_NO1_author FROM test_no2
 ORDER BY test_NO1_author; 
 
 
select * from test_no1 order by test_NO1_date asc;

INSERT INTO test_NO1 
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb4", "welcome to programb4", NOW());
    
INSERT INTO test_NO1 
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb5", "welcome to programb5", NOW());
    
select * from test_no1 order by test_NO1_date asc;

select * from test_no1 order by test_NO1_date desc;

select test_NO1_title, count(*) from test_no1 group by test_NO1_title;

select test_NO1_title, sum(test_NO1_id)  from test_no1 group by test_NO1_title;

select test_NO1_title, avg(test_NO1_id)  from test_no1 group by test_NO1_title;

SELECT * from test_NO1;

SELECT * from test_no2;

INSERT INTO test_NO2 
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb4", "welcome to programb4", NOW());
    
INSERT INTO test_NO2 
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb2", "welcome to programb2", NOW());
    
SELECT * from test_no2;

select t1.test_NO1_title, t2.test_NO1_date from test_no1 t1, test_no2 t2 where t1.test_NO1_date=t2.test_NO1_date;

select t1.test_NO1_title, t2.test_NO1_date from test_no1 t1 left join test_no2 t2 on t1.test_NO1_date=t2.test_NO1_date;

select t1.test_NO1_title, t2.test_NO1_date from test_no1 t1 right join test_no2 t2 on t1.test_NO1_date=t2.test_NO1_date;


SELECT * from test_no2;

INSERT INTO test_NO2 
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb2", "welcome", NOW());

SELECT * from test_no2;

select * from test_no2 where test_NO1_author is null;


INSERT INTO test_NO2 
    (test_NO1_title, test_NO1_author )
    VALUES
    ("programb2", "welcome");

INSERT INTO test_NO2 
    (test_NO1_title, test_NO1_author )
    VALUES
    ("programb2", "welcome");

INSERT INTO test_NO2 
    (test_NO1_title, test_NO1_author )
    VALUES
    ("programb2", "welcome");

SELECT * from test_no2;

select * from test_no2 where test_NO1_date is null;

select * from test_no2 where test_NO1_date is not null;

select * from test_no1 where test_NO1_title regexp '^pro';

select * from test_no1 where test_NO1_title regexp '2$';

select * from test_no1 where test_NO1_title regexp 'gra';

select * from test_no1 where test_NO1_title regexp '^[pro]|5$';

begin;

insert into  test_NO1(test_NO1_title, test_NO1_author ) value("pro","welcome");
insert into  test_NO1(test_NO1_title, test_NO1_author ) value("pro","welcome");
insert into  test_NO1(test_NO1_title, test_NO1_author ) value("pro","welcome");
insert into  test_NO1(test_NO1_title, test_NO1_author ) value("pro","welcome");
insert into  test_NO1(test_NO1_title, test_NO1_author ) value("pro","welcome");
insert into  test_NO1(test_NO1_title, test_NO1_author ) value("pro","welcome");

commit;

SELECT * from test_NO1;


show columns from test_no1;

alter table test_no1 alter test_NO1_author set default 1000;

alter table test_no1 modify test_NO1_author  varchar(100);

show columns from test_no1;

alter table test_no1 drop  test_NO1_author;

show columns from test_no1;

alter table test_no1 add test_NO1_author int first;

show columns from test_no1;

alter table test_no1 drop  test_NO1_author;

alter table test_no1 add test_NO1_author int;

show columns from test_no1;

alter table test_no1 drop  test_NO1_author;

alter table test_no1 add test_NO1_author varchar(100);


show columns from test_no1;

alter table test_no1 alter test_NO1_author set default 1000;

alter table test_no1 modify test_NO1_author  varchar(100);

show columns from test_no1;

alter table test_no1 drop  test_NO1_author;

show columns from test_no1;

alter table test_no1 add test_NO1_author int first;

show columns from test_no1;

alter table test_no1 drop  test_NO1_author;

alter table test_no1 add test_NO1_author int;

show columns from test_no1;

alter table test_no1 drop  test_NO1_author;

alter table test_no1 add test_NO1_author varchar(100);