MySQL的表定义语法
表定义
只有成功创建数据库后,才能创建数据表,数据表是字段的集合,在表中数据按行和列的格式存储
创建表
mysql 使用 create table 创建表。其中有多个选择,主要由表创建定义(create definition)、表选项定义(table options) 和区分选项(partition options)等内容构成。
表创建定义:由表列的名字、列的定义集可能的一个空值声明、一个完整性约束或表索引项组成,表索引项主要定义表的索引、主键、外键等。
语法结构:
create[temporary]table tbl_name ( 字段名|数据类型[列级完整性约束条件][默认值] [,字段名2 数据类型[列级完整性约束条件][默认值]] [,....] [,表级完整性约束条件] )[engine=引擎类型]
example:
新建一个客户信息
mysql> use mysql_test database changed mysql> crate table customers ->( -> cust_id int not null auto_increment, -> cust_name char(50) not null, -> cust_sex char(1) not null default 0, -> cust_address char(50) null -> cust_contact char(50) null -> primary key(cust_id) ->) query ok, 0 rows affected(0.11 sec)
临时表与持久表
temporary:表示临时表,如果不选用则位持久表。
持久表一直存在,多个用户或应用程序可同时使用持久表,如果只需临时存放数据可添加 temporary 关键字
临时表只能对创建它的用户可见,断开数据库连接时,表会自动清除
数据类型
数据类型指系统中所允许的数据的类型。每列都应有适当的数据类型,来限制或允许该列的数据。 建表时必须为每列指定正确的数据类型及数据长度 (char(50))
mysql 主要数据类型:
- 数值类型:整型 int、浮点 double、布尔 bool
- 日期和时间类型:日期型、时间戳 timestamp、时间型 time
- 字符串类型:定长字符类型char、可变长字符类型varchrar
- 空间数据类型:单个几何类型 geometry等
关键字 auto_increment
auto_increment: 表中数据类型为整型的列设置自增属性 (++i),从当前指或 1 开始,表中只能有一个 auto_increment。
当一个表列被指定为 auto_increment 后,其值可被覆盖,即可在表数据插入语句中为该列指定一个值(必须唯一),则该值将替换系统自动生成的值,后续增量基于该插入的值
指定默认值
default:用于指定mysql在未给值的情况下默认的值(default 0)
如果未指定默认值,则自动为其分配一个值,如若该列可取值null,则默认null,若定义 not null,则默认取决于该列的类型:
- 一个没有声明 auto_increment 列 为数字类型,默认 0
- 一个 auto_increment 列 默认为顺序中的下一个值
- 对于除 timestamp 以外的日期和时间类型,默认为该类型适当的'零'值、
- 对于表中第一个 timestamp 列,默认值为当前日期和时间
null值
null:没有值或缺值,允许null的列,插入行时可以不给该列的值;不允许null值的列,则该列必须有数据null
和 ''
是不对等的 not null 列中允许''
不允许 null
主键
primary key :指定主键,主键必须唯一且不能为null, 如果是单列,值必须唯一,如果是组合列,则其组合的值必须唯一
更新表
通过使用 alter table 来修改数据库
-
add[column]
:新增表列,可增多列使用逗号分隔即可
example:
mysql> alter table mysqle_test.customers -> add column cust_city char(10) not null default'shenzhen' after cust_sex; query ok,0 rows affected(0.61 sec) records:0 duplicates:0 warning:0
after:将新增的列添加到cut_sexl 列之后
first:将新增的列添加到表的第一列
若使用上述关键字则将新增的列添加至表最后
类似的 可以使用 addprimary key 、addforeign key 、add index 添加对应的 主键、外键、索引
-
change[column]
: 修改表中列的名称或数据类型,可修改多列使用逗号分隔即可
mysql> alter table mysqle_test.customers -> change column cust_sex sex char(1) null default 'm' query ok,0 rows affected(0.66 sec) records:0 duplicates:0 warning:0
如果将数据类型更换,可能会丢失该列原有的数据,如果视图改变的数据类型于原有的数据类型不兼容,则sql命令不会执行,且抛出错误。
再兼容的情况下,该列的数据可能会被截断,如:一列的数据类型为 varchart(10),改为char(1),则该列中的数据'shenzhen'会变为's'
-
alter [column]
: 修改或删除指定列的默认值
mysql> alter table mysqle_test.customers -> alter column cust_city set default 'shanghai' query ok,0 rows affected(0.36 sec) records:0 duplicates:0 warning:0
-
modify [column]
: 修改指定列的数据类型,通过 'first' 或 'after' 修改列的位置
mysql> alter table mysqle_test.customers -> modify column cust_name char(30) first query ok,0 rows affected(0.20 sec) records:0 duplicates:0 warning:0
-
drop [column]
: 删除列,该列所有数据一并删除
mysql> alter table mysqle_test.customers -> drop column cust_city query ok,0 rows affected(0.42 sec) records:0 duplicates:0 warning:0
同样 可使用 drop primary key 、drop foreign key、drop index 删除对应的主键、外键、索引
-
rename[to]
:表重命名
mysql> alter table mysqle_test.customers -> rename to query ok,0 rows affected(0.42 sec)
重命名表
除了 alter table 中的 rename to 修改表名,还可通过 rename table 来修改单张和多张表(以逗号分隔)
mysql> rename table mysql_test.back.customers to mysqle_test.customers
删除表
drop[temporary]table[if exists]
删除一个已存在的表,可以删除多张表,前提操作人必须有权限,但是操作人在该张表上的权限不会被删除
查看表
-
show [full] tables [{from|in}db_name] [like'pattern'|where expr]
: 显示指定数据库中所有表名
example:
mysql> use mysql_test database changed mysql> show tables: tables_in_mysql_test customers 1 row in set <0.01 sec>
-
show [full] columns {from|in}tb_name[{from|in}db_name] 或 {describe|desc} tbl_name[col_name|wild]
: 显示指定数据库表结构。
mysql 支持使用 describe 代替 show columns from 来查看表结构
example:
mysql> desc mysql_test.custormes field type null key default extra cust_id int<11> no pri null auto_increment cust_name char<50> no null cust_sex int<1> no 0 3 row in set <1.56 sec>