mysql drop column 如果存在column

转载

mob6454cc6e1f98 2024-07-16 15:27:31

文章标签 数据库 mysql-存储引擎数据类型 mysql 数据 文章分类 MySQL 数据库

表的基本概念

表是包含数据库中所有数据的数据库对象。其中每一行代表一条唯一的记录，每一列代表记录中的一个字段。
表中的数据库对象包含列、索引和触发器。
列（Columns）：也称属性列，在具体创建表时，必须制定列的名字和数据类型。
索引（Indexes）：是指根据制定的数据库表列建立起来的顺序，提供了快速访问数据的途径。且可监督表的数据，使其索引指向的列的数据不重复；
触发器（triggers）：是指用户定义的命令的集合。当对一个表中的数据进行插入、更新和删除时这组命令就会自动执行。

1.创建表

创建表的语法：

CREATE TABLE table_name(
           属性名    数据类型,
           属性名    数据类型,
           属性名    数据类型,
           .
           .
           .
);

创建一个表，示例：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| DATA_MINING        |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> USE DATA_MINING;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_DATA_MINING |
+-----------------------+
| AAA                   |
| f_test                |
+-----------------------+
2 rows in set (0.00 sec)
**mysql> CREATE TABLE T_DEPT(
    -> ID BIGINT,
    -> NAME VARCHAR(32),
    -> LOC VARCHAR(32)
    -> );**
Query OK, 0 rows affected (0.09 sec)
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_DATA_MINING |
+-----------------------+
| AAA                   |
| T_DEPT                |
| f_test                |
+-----------------------+
3 rows in set (0.00 sec)

2.查看表

查看表的定义信息：DESCRIBE T_DEPT;

mysql> DESCRIBE T_DEPT;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | bigint(20)  | YES  |     | NULL    |       |
| NAME  | varchar(32) | YES  |     | NULL    |       |
| LOC   | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.19 sec)
mysql> DESCRIBE T_DEPT \g
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | bigint(20)  | YES  |     | NULL    |       |
| NAME  | varchar(32) | YES  |     | NULL    |       |
| LOC   | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> DESCRIBE T_DEPT \G
*************************** 1. row ***************************
  Field: ID
   Type: bigint(20)
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 2. row ***************************
  Field: NAME
   Type: varchar(32)
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 3. row ***************************
  Field: LOC
   Type: varchar(32)
   Null: YES
    Key:
Default: NULL
  Extra:
3 rows in set (0.00 sec)
mysql>

查看表结构的详细信息可以通过：SHOW CREATE TABLE TABLE_NAME;

mysql> SHOW CREATE TABLE T_DEPT \G
*************************** 1. row ***************************
       Table: T_DEPT
Create Table: CREATE TABLE `T_DEPT` (
  `ID` bigint(20) DEFAULT NULL,
  `NAME` varchar(32) DEFAULT NULL,
  `LOC` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>

3.删除表

删除一张表时，会直接删除表中所保存的所有数据，因此在删除表时应非常小心。

删除表的命令：DROP TABLE TABLE_NAME;

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_DATA_MINING |
+-----------------------+
| AAA                   |
| T_DEPT                |
| f_test                |
+-----------------------+
3 rows in set (0.00 sec)
mysql> DROP table f_test
    -> ;
Query OK, 0 rows affected (0.11 sec)
mysql> show tables;
+-----------------------+
| Tables_in_DATA_MINING |
+-----------------------+
| AAA                   |
| T_DEPT                |
+-----------------------+
2 rows in set (0.00 sec)
mysql>

3.修改表

mysql数据库提供”ALTER TABLE“语句来实现修改表结构。

修改表名：ALTER TABLE OLD_TABLE_NAME RENAME NEW_TABLE_NAME;

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_DATA_MINING |
+-----------------------+
| AAA                   |
| T_DEPT                |
+-----------------------+
2 rows in set (0.01 sec)
mysql> ALTER TABLE T_DEPT RENAME t_dept;
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;
+-----------------------+
| Tables_in_DATA_MINING |
+-----------------------+
| AAA                   |
| t_dept                |
+-----------------------+
2 rows in set (0.00 sec)
mysql> describe t_dept;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | bigint(20)  | YES  |     | NULL    |       |
| NAME  | varchar(32) | YES  |     | NULL    |       |
| LOC   | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

增加字段（经常将列称为字段）：

在表的最后一个位置增加字段：
ALTER TABLE TABLE_NAME ADD 属性名属性类型

mysql> DESC t_dept;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | bigint(20)  | YES  |     | NULL    |       |
| NAME  | varchar(32) | YES  |     | NULL    |       |
| LOC   | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE t_dept ADD DESCRI VARCHAR(20);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESCRIBE t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ID     | bigint(20)  | YES  |     | NULL    |       |
| NAME   | varchar(32) | YES  |     | NULL    |       |
| LOC    | varchar(32) | YES  |     | NULL    |       |
| DESCRI | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

在表的第一个位置增加字段：
ALTER TABLE TABLE_NAME ADD COLUMN_NAME COLUMN_ATTR FIRST;

mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ID     | bigint(20)  | YES  |     | NULL    |       |
| NAME   | varchar(32) | YES  |     | NULL    |       |
| LOC    | varchar(32) | YES  |     | NULL    |       |
| DESCRI | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> ALTER TABLE t_dept ADD ATTR VARCHAR(32) FIRST;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ATTR   | varchar(32) | YES  |     | NULL    |       |
| ID     | bigint(20)  | YES  |     | NULL    |       |
| NAME   | varchar(32) | YES  |     | NULL    |       |
| LOC    | varchar(32) | YES  |     | NULL    |       |
| DESCRI | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

在表的指定字段之后增加字段
ALTER TABLE TABLE_NAME ADD column_name column_attr AFTER column_name;

mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ATTR   | varchar(32) | YES  |     | NULL    |       |
| ID     | bigint(20)  | YES  |     | NULL    |       |
| NAME   | varchar(32) | YES  |     | NULL    |       |
| LOC    | varchar(32) | YES  |     | NULL    |       |
| DESCRI | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> ALTER TABLE t_dept ADD ATTR1 VARCHAR(32) AFTER
    -> ID;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC TABLE t_dept;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE t_dept' at line 1
mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ATTR   | varchar(32) | YES  |     | NULL    |       |
| ID     | bigint(20)  | YES  |     | NULL    |       |
| ATTR1  | varchar(32) | YES  |     | NULL    |       |
| NAME   | varchar(32) | YES  |     | NULL    |       |
| LOC    | varchar(32) | YES  |     | NULL    |       |
| DESCRI | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

删除字段
ALTER TABLE TABLE_NAME
DROP column_name;

mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ATTR   | varchar(32) | YES  |     | NULL    |       |
| ID     | bigint(20)  | YES  |     | NULL    |       |
| ATTR1  | varchar(32) | YES  |     | NULL    |       |
| NAME   | varchar(32) | YES  |     | NULL    |       |
| LOC    | varchar(32) | YES  |     | NULL    |       |
| DESCRI | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> ALTER TABLE t_dept DROP ATTR;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ID     | bigint(20)  | YES  |     | NULL    |       |
| ATTR1  | varchar(32) | YES  |     | NULL    |       |
| NAME   | varchar(32) | YES  |     | NULL    |       |
| LOC    | varchar(32) | YES  |     | NULL    |       |
| DESCRI | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改字段

修改字段的数据类型
ALTER TABLE TABLE_NAME
MODIFY column_name 数据类型

mysql> DESC T_DEPT;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ID     | bigint(20)  | YES  |     | NULL    |       |
| ATTR1  | varchar(32) | YES  |     | NULL    |       |
| NAME   | varchar(32) | YES  |     | NULL    |       |
| LOC    | varchar(32) | YES  |     | NULL    |       |
| DESCRI | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> ALTER TABLE MODIFY ATTR1 VARCHAR(20);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ATTR1 VARCHAR(20)' at line 1
mysql> ALTER TABLE T_DEPT MODIFY ATTR1 VARCHAR(20);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC T_DEPT;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ID     | bigint(20)  | YES  |     | NULL    |       |
| ATTR1  | varchar(20) | YES  |     | NULL    |       |
| NAME   | varchar(32) | YES  |     | NULL    |       |
| LOC    | varchar(32) | YES  |     | NULL    |       |
| DESCRI | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改字段的名称和数据类型：
ALTER TABLE TABLE_NAME
CHANGE 旧属性名新属性名数据类型

mysql> DESC T_DEPT;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ID     | bigint(20)  | YES  |     | NULL    |       |
| ATTR1  | varchar(20) | YES  |     | NULL    |       |
| NAME   | varchar(32) | YES  |     | NULL    |       |
| LOC    | varchar(32) | YES  |     | NULL    |       |
| DESCRI | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> ALTER TABLE T_DEPT ATTR1 ATTR VARCHAR(32);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ATTR1 ATTR VARCHAR(32)' at line 1
mysql> ALTER TABLE T_DEPT CHANGE ATTR1 ATTR VARCHAR(32);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC T_DEPT;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ID     | bigint(20)  | YES  |     | NULL    |       |
| ATTR   | varchar(32) | YES  |     | NULL    |       |
| NAME   | varchar(32) | YES  |     | NULL    |       |
| LOC    | varchar(32) | YES  |     | NULL    |       |
| DESCRI | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改字段的顺序
ALTER TABLE TABLE_NAME
MODIFY 属性名1 数据类型 FIRST|AFTER 属性名2

4.操作表的约束
对于已经创建好的表，虽然字段的数据类型决定了所能存储的数据类型，但表中存储的数据是否合法并没有进行检查。在具体使用mysql时，如果想针对表中的数据做一些完整性的检查操作，可以通过约束来完成。

常用五类约束：
not null：非空约束，指定某列不为空
unique：唯一约束，指定某列和几列组合的数据不能重复
primary key：主键约束，指定某列的数据不能重复、唯一
foreign key：外键，指定该列记录属于主表中的一条记录，参照另一条数据
check：检查，指定一个表达式，用于检验指定数据
AUTO_INCREMENT：约束字段的值为自动增加。
注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果；
根据约束数据列限制，约束可分为：
单列约束：每个约束只约束一列
多列约束：每个约束约束多列数据

mysql 中不支持check约束，即可以使用check约束但是却没有任何效果。

设置非空约束：

非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。
Null类型特征：所有的类型的值都可以是null，包括int、float等数据类型空字符串””是不等于null，0也不等于null
create table temp(
id int not null,
name varchar(255) not null default ‘abc’,
sex char null
)
上面的table加上了非空约束，也可以用alter来修改或增加非空约束
增加非空约束
alter table temp modify sex varchar(2) not null;
取消非空约束
alter table temp modify sex varchar(2) null;
取消非空约束，增加默认值
alter table temp modify sex varchar(2) default ‘abc’;

设置字段的默认值：

设置唯一约束（UNIQUE,UK）：
唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。虽然唯一约束不允许出现重复的值，但是可以为多个null，同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同。 MySQL会给唯一约束的列上默认创建一个唯一索引；
create table temp (         
id int not null,         
name varchar(25),         
password varchar(16), 
constraint uk_name_pwd unique(name, password) 
); 
表示用户名和密码组合不能重复 
添加唯一约束 
alter table temp add unique(name, password); 
修改唯一性约束
alter table temp modify name varchar(25) unique; 
删除约束 
alter table temp drop index name;

建表时设置唯一约束（UNIQUE,UK）

mysql> CREATE TABLE D_DEPT(
    -> ID BIGINT,
    -> NAME VARCHAR(32),
    -> ATTR VARCHAR(32)
    -> ,
    -> CONSTRAINT UK_NAME UNIQUE(NAME)
    -> );
Query OK, 0 rows affected (0.06 sec)
mysql> DESC D_DEPT;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | bigint(20)  | YES  |     | NULL    |       |
| NAME  | varchar(32) | YES  | UNI | NULL    |       |
| ATTR  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

设置主键约束

mysql> CREATE TABLE AAB( DEPTNO INT PRIMARY KEY, DNAME VARCHAR(20) );
Query OK, 0 rows affected (1.01 sec)
mysql>

设置字段值自增列

mysql> CREATE TABLE AAD( DEPTNO INT PRIMARY KEY AUTO_INCREMENT, DNAME VARCHAR(20) );
Query OK, 0 rows affected (2.82 sec)
mysql> DESC AAD;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| DEPTNO | int(11)     | NO   | PRI | NULL    | auto_increment |
| DNAME  | varchar(20) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

设置外键约束（FOREIGN KEY,FK）
外键约束构建多个表两个字段之间的参照关系。设置外键约束的两个表之间会有父子关系，即子表中某个字段的取值范围由父表所决定。
例如：
表示一种部门和雇员之间的关系。
部门表

部门编号    部门名 主要业务
1         数据库    111
2         总线       222
3         采集       333
雇员表：
雇员编号    名字  所属部门
1     liyang    1
2      zcw      1
3      wy       1
4      wrl      2

部门表的部门编号字段就是雇员表的外键。
创建语法请参见mysq数据库从入门到精通
关键语法：
CONSTRAINT FK_DEPTNO FOREIGN KEY(列名)
REFERENCES 表名(列名)

索引

索引的操作

数据库对象表时存储和操作数据的逻辑结构，而索引则是一种有效组合数据的方式，通过索引对象可以快速查询到数据库对象表中的特定记录，是一种提高性能的常用方法。

一个索引会包含表中按照一定顺序排列的一列或多列字段。索引的操作包括创建索引、修改索引和删除索引。
数据库对象的索引其实与书的目录非常相似，主要是为了提高从表中检索数据的速度。由于数据存储在数据库表中，所以索引是创建在数据库表对象上的，由表中的一个字段或多个字段生成的键组成。这些键存储在数据结构（B树或哈希表）中，根据索引的存储类型可以将索引分为B型树索引和哈希索引。
InnoDB和MyISAM存储引擎支持BTREE类型索引，MEMORY存储引擎支持HASH类型索引，默认为前者索引。

mysql支持六种索引：普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引。

创建索引可以提高查询效率，但过多的创建索引则会占据许多的磁盘空间。

以下情况下适合创建索引：

经常被查询的字段，即在WHERE子句中出现的字段；
在分组的字段，即在GROUP BY子句中出现的字段；
存在依赖关系的子表和父表之间的联合查询，即主键或外键字段；
设置唯一完整性约束的字段；
以下的情况不适合创建索引：
在查询中很少被使用的字段；
拥有许多重复值的字段。

创建索引的命令：

CREATE TABLE T_DEPT(
DEPT  INT,
DNAME   VARCHAR(20),
INDEX index_deptno(DEPTNO)//INDEX 【索引名】(属性名 【长度】 【ASC|DESC】)
);

查看创建的索引：SHOW CREATE TABLE T_DEPT \G

检验索引是否被使用：
EXPLAIN
SELECT * FROM T_DEPT WHERE DEPTNO=1 \G;

在已经存在的表上创建普通索引：
CREATE INDEX 索引名
ON 表名(属性名【(长度)】【ASC|DESC】)

通过语句创建索引
ALTER TABLES TABLE_NAME
ADD INDEX | KEY 索引名(属性名【(长度)】【ASC|DESC】)

创建唯一性索引就是在INDEX关键字之前加上UNIQUE

全文索引主要关联在数据类型为CHAR和VARCHAR和TEXT的字段上。

删除索引
DROP INDEX index_name
ON table_name

路漫漫其修远兮，吾将上下而…

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：extractall函数 python

下一篇：逻辑回归训练多分类逻辑回归怎么做多分类

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

mysql drop column 如果存在column

mysql drop column 如果存在column

51CTO博客