您现在的位置是：网站首页> 编程资料编程资料
MYSQL 表的全面总结_Mysql_
2023-05-26
418人已围观
简介 MYSQL 表的全面总结_Mysql_
1、创建表
1.1、创建表基本语法
CREATE TABLE tablename (column_name_1 column_type_1 constraints， column_name_2 column_type_2 constraints ， ……）
column_name 是列的名字column_type 是列的数据类型contraints 是这个列的约束条件
1.1.1、创建一张简单的表
mysql> create table orders (ordername varchar(10),createtime date,ordermoney decimal(10,2),ordernumber int(2)); Query OK, 0 rows affected (0.23 sec)
1.1.2、查看创建表定义
结构化定义：
mysql> desc orders; +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | ordername | varchar(10) | YES | | NULL | | | createtime | date | YES | | NULL | | | ordermoney | decimal(10,2) | YES | | NULL | | | ordernumber | int(2) | YES | | NULL | | +-------------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
表详细定义：
查看详细的表定义：
mysql> show create table orders \G; *************************** 1. row *************************** Table: orders Create Table: CREATE TABLE `orders` ( `ordername` varchar(10) DEFAULT NULL, `createtime` date DEFAULT NULL, `ordermoney` decimal(10,2) DEFAULT NULL, `ordernumber` int(2) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) ERROR: No query specified
由此可以看到表的 ENGINE（存储引擎）是InnoDB
CHARSET（字符集）是Latin1
“\G”选项的含义是使得记录能够按照字段竖着排列，对于内容比较长的记录更易于显示。
2、删除表
命令：
DROP TABLE tablename
删除orders：
mysql> drop table orders -> ; Query OK, 0 rows affected (0.14 sec)
3、修改表
3.1、修改表类型命令
ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
例：修改表 orders 的 name 字段定义，将 varchar(10)改为 varchar(20) ：
mysql> alter table orders modify ordername varchar(20); Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc orders; +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | ordername | varchar(20) | YES | | NULL | | | createtime | date | YES | | NULL | | | ordermoney | decimal(10,2) | YES | | NULL | | | ordernumber | int(2) | YES | | NULL | | +-------------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
3.2、字段改名命令
ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]
例：表 orders 上将ordernumber修改为ordernumbers
mysql> alter table orders change column ordernumber ordernumbers int(4); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc orders; +--------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------+------+-----+---------+-------+ | ordername | varchar(20) | YES | | NULL | | | createtime | date | YES | | NULL | | | ordermoney | decimal(10,2) | YES | | NULL | | | ordernumbers | int(4) | YES | | NULL | | +--------------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
特别说明：change 和 modify 都可以修改表的定义，不同的是 change 后面需要写两次列名，不方便。但是 change 的优点是可以修改列名称，modify 则不能。
3.3、增加表字段命令
ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name]
例：表 orders 上新增加字段 username，类型为 varchar(3) ：
mysql> alter table orders add column username varchar(30); Query OK, 0 rows affected (0.39 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc orders; +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | ordername | varchar(20) | YES | | NULL | | | createtime | date | YES | | NULL | | | ordermoney | decimal(10,2) | YES | | NULL | | | ordernumber | int(2) | YES | | NULL | | | username | varchar(30) | YES | | NULL | | +-------------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
3.4、删除表列字段命令
ALTER TABLE tablename DROP [COLUMN] col_name
例：表 orders 上删除字段 username：
mysql> alter table orders drop column username; Query OK, 0 rows affected (0.53 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc orders; +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | ordername | varchar(20) | YES | | NULL | | | createtime | date | YES | | NULL | | | ordermoney | decimal(10,2) | YES | | NULL | | | ordernumber | int(2) | YES | | NULL | | +-------------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
3.5、表改名命令
ALTER TABLE tablename RENAME [TO] new_tablename
例：表 orders 名字改为goodsorders
mysql> alter table orders rename goodsorders; Query OK, 0 rows affected (0.16 sec) mysql> desc orders; ERROR 1146 (42S02): Table 'ordermanage.orders' doesn't exist mysql> desc goodsorders; +--------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------+------+-----+---------+-------+ | ordername | varchar(20) | YES | | NULL | | | createtime | date | YES | | NULL | | | ordermoney | decimal(10,2) | YES | | NULL | | | ordernumbers | int(4) | YES | | NULL | | +--------------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
4、DML 语句
插入（insert）、查询（select）、更新（update）、删除（delete）
4.1、插入记录 命令
INSERT INTO tablename (field1,field2,……fieldn) VALUES(value1,value2,……valuesn);
例：表 goodsorders 中插入一条记录，ordername 为zhang，createtime为2021-05-12，ordermoney为100.00,ordernumbers为：1
mysql> insert into goodsorders (ordername,createtime,ordermoney,ordernumbers) value
点击排行
本栏推荐
