MYSQL基础01 - 表的操作

关注

原创最新推荐文章于 2026-03-26 09:15:37 发布 1.1k 阅读 AI 写同款· GEO 优化 ›

手把手教你学Simulink——基于Simulink的输入输出线性化精确转矩控制由于IOFL控制律中实时引入了当前状态的反馈（id,iq,ωe），电感的变化被非线性项自动补偿，转矩仅出现微小幅度的波动（< 2%），展现出极强的参数鲁棒性。：电压方程中的耦合项（ωeLqiq和 −ωeLdid）仅通过前馈补偿，当转速 ωe较高时，微小的前馈误差会导致显著的d-q轴电流交叉耦合；其中，ud,uq为定子电压，id,iq为定子电流，Rs为定子电阻，Ls为定子电感，ωe为电角速度，ψf为永磁体磁链，p为极对数。（步长 Ts=10μs，即100ns？阅读详情

标题：表的基本操作
包含内容：
创建表
创建主键，外键，非空，唯一，默认约束
设置表列属性自动增长
查看表结构
修改表名，字段数据类型，字段名，添加删除字段，修改字段位置
更改表的存储引擎
删除外键约束
删除表


--创建主键约束索引
mysql> create table t1(
    -> col varchar(8) primary key,
    -> col2  int
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc t1
    -> ;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col   | varchar(8) | NO   | PRI | NULL    |       |
| col2  | int(11)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> 
mysql> drop table t1;
Query OK, 0 rows affected (0.03 sec)

--设置非空约束
mysql> create table t1(
    -> col int not null
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col   | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(
    -> col1 int primary key,
    -> col2 varchar(8)
    -> );
Query OK, 0 rows affected (0.03 sec)

--设置T2表的外键约束（与T1相关），key值显示为"MUL"
mysql> create table t2(
    -> col1 int,
    -> col2 int
    -> ,
    -> constraint fk_t2 foreign key(col1) references t1(col1)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | YES  | MUL | NULL    |       |
| col2  | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

--设置唯一性约束
mysql> create table t3(
    -> col1 int unique key
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc t3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | YES  | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

--设置默认值约束
mysql> create table t4(
    -> col1 varchar(8) default 'xxxx',
    -> col2 int
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t4(col2) values(1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t4;
+------+------+
| col1 | col2 |
+------+------+
| xxxx |    1 |
+------+------+
1 row in set (0.00 sec)

--设置自动增长列
mysql> create table t5(
    -> col1 int primary key auto_increment,
    -> col2 varchar(8)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc t5;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| col1  | int(11)    | NO   | PRI | NULL    | auto_increment |
| col2  | varchar(8) | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into t5 values (2,'cc');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t5;
+------+------+
| col1 | col2 |
+------+------+
|    2 | cc   |
+------+------+
1 row in set (0.00 sec)

mysql> insert into t5(col2) values('xxx');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t5;
+------+------+
| col1 | col2 |
+------+------+
|    2 | cc   |
|    3 | xxx  |
+------+------+
2 rows in set (0.00 sec)

mysql> show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `col1` int(11) NOT NULL,
  `col2` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> desc t5;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| col1  | int(11)    | NO   | PRI | NULL    | auto_increment |
| col2  | varchar(8) | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

--修改表名
mysql> alter table t5 rename to t55;
Query OK, 0 rows affected (0.04 sec)

mysql> desc t5;
ERROR 1146 (42S02): Table 'zbk_db.t5' doesn't exist
mysql> desc t55;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| col1  | int(11)    | NO   | PRI | NULL    | auto_increment |
| col2  | varchar(8) | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

--修改列的数据类型
mysql> alter table t55 modify col1 int(12);
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

--修改列的名称
mysql> alter table t55 change col1 col11 int(11);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc t55;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col11 | int(11)    | NO   | PRI | 0       |       |
| col2  | varchar(8) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

--添加表的列
mysql> alter table t55 add col3 varchar(8);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc t55;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col11 | int(11)    | NO   | PRI | 0       |       |
| col2  | varchar(8) | YES  |     | NULL    |       |
| col3  | varchar(8) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

--添除表的列
mysql> alter table t55 drop column col3;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc t55;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col11 | int(11)    | NO   | PRI | 0       |       |
| col2  | varchar(8) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

--修改表中列的位置
mysql> alter table t55 modify col2 first;
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 'first' at line 1
mysql> alter table t55 modify col2 varchar(8) first;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc t55;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col2  | varchar(8) | YES  |     | NULL    |       |
| col11 | int(11)    | NO   | PRI | 0       |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table t55 \G
*************************** 1. row ***************************
       Table: t55
Create Table: CREATE TABLE `t55` (
  `col2` varchar(8) DEFAULT NULL,
  `col11` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`col11`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

--修改表的存储引擎
mysql> alter table t55 engine=myisam;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table t55 \G
*************************** 1. row ***************************
       Table: t55
Create Table: CREATE TABLE `t55` (
  `col2` varchar(8) DEFAULT NULL,
  `col11` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`col11`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | YES  | MUL | NULL    |       |
| col2  | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col1  | int(11)    | NO   | PRI | NULL    |       |
| col2  | varchar(8) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show constraint from t1 \G
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 'constraint from t1' at line 1
mysql> show index from t1 \G
*************************** 1. row ***************************
        Table: t1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: col1
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

--查看约束索引名
mysql> show index from t2 \G
*************************** 1. row ***************************
        Table: t2
   Non_unique: 1
     Key_name: fk_t2
 Seq_in_index: 1
  Column_name: col1
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

--删除外键约束
mysql> alter table t2 drop foreign key fk_t2;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t2 \G
*************************** 1. row ***************************
        Table: t2
   Non_unique: 1
     Key_name: fk_t2
 Seq_in_index: 1
  Column_name: col1
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

--可以看到，虽然删除了约束，但索引仍存在，需要再次删除索引
mysql> show create table t2 \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  KEY `fk_t2` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

--删除索引
mysql> alter table t2 drop index fk_t2;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2 \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> desc t2
    -> ;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | YES  |     | NULL    |       |
| col2  | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)