MySQL TABLE

2020-02-16

字数统计: 2.1k字 | 阅读时长≈ 10分

数据库中的数据类型

可通过 help data types; 查询所有支持的数据类型

1	mysql> help data types;

You asked for help about help category: "Data Types"
For more information, type 'help <item>', where <item> is one of the following
topics:
   AUTO_INCREMENT 
   BIGINT
   BINARY
   BIT
   BLOB
   BLOB DATA TYPE
   BOOLEAN
   CHAR
   CHAR BYTE
   DATE
   DATETIME
   DEC
   DECIMAL
   DOUBLE
   DOUBLE PRECISION
   ENUM
   FLOAT
   INT
   INTEGER
   LONGBLOB
   LONGTEXT
   MEDIUMBLOB
   MEDIUMINT
   MEDIUMTEXT
   SET DATA TYPE
   SMALLINT
   TEXT
   TIME
   TIMESTAMP
   TINYBLOB
   TINYINT
   TINYTEXT
   VARBINARY
   VARCHAR
   YEAR DATA TYPE

可通过 help datatype_name; 查询具体的数据类型详细信息

mysql> help int;
Name: 'INT'
Description:
INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. The signed range is -2147483648 to 2147483647.
The unsigned range is 0 to 4294967295.

整型类型

TINYINT
MEDIUMINT
INT
INTEGER   
BIGINT

浮点型

1
2
3

FLOAT
DOUBLE
DECIMAL(M,D) OR DEC(M,D)

日期与时间类型

YEAR
DATE
TIME
DATETIME
TIMESTAMP

日期和时间类型的使用



mysql> CREATE TABLE dt_example(
    -> e_date DATE,
    -> e_datetime DATETIME,
    -> e_timestamp TIMESTAMP,
    -> e_time TIME,
    -> e_year YEAR);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO dt_example VALUES(
    -> CURDATE(),
    -> NOW(),
    -> NOW(),
    -> TIME(NOW()),
    -> YEAR(NOW()));
Query OK, 1 row affected (0.00 sec)

mysql> SELECT *FROM dt_example;
+------------+---------------------+---------------------+----------+--------+
| e_date     | e_datetime          | e_timestamp         | e_time   | e_year |
+------------+---------------------+---------------------+----------+--------+
| 2020-02-15 | 2020-02-15 13:10:07 | 2020-02-15 13:10:07 | 13:10:07 |   2020 |
+------------+---------------------+---------------------+----------+--------+
1 row in set (0.00 sec)

字符和字符串类型

CHAR
VARCHAR
BLOB
TEXT
ENUM
SET

CHAR 类型和 VARCHAR 类型

用法： CHAR(M) VARCAHR(M)

CAHR 类型长度是固定的

VARCAHR 类型长度是变的

其他类型

# 未完待续···

创建表

语法形式如下：

CREATE TABLE tablename(
    属性名 数据类型 [完整性约束条件],
    属性名 数据类型 [完整性约束条件],
    ……
     属性名 数据类型 [完整性约束条件]);

创建表 t_calss

mysql> CREATE TABLE t_class(
    -> classno INT ,
    -> cname VARCHAR(20),
    -> loc VARCHAR(20),
    -> stucount INT);
Query OK, 0 rows affected (0.01 sec)

查看表的定义

DESCRIBE 语句查看表的定义

DESCRIBE 语法句式如下：

1	DESCRIBE tablename; or DESC tablename;

mysql> desc t_class;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| classno  | int(11)     | YES  |     | NULL    |       |
| cname    | varchar(20) | YES  |     | NULL    |       |
| loc      | varchar(20) | YES  |     | NULL    |       |
| stucount | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

SHOW CREATE TABLE 语句查看详细建表定义

语法形式如下：

1	SHOW CREATE TABLE tablename;

mysql> SHOW CREATE TABLE t_class;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                              |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_class | CREATE TABLE `t_class` (
  `classno` int(11) DEFAULT NULL,
  `cname` varchar(20) DEFAULT NULL,
  `loc` varchar(20) DEFAULT NULL,
  `stucount` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

删除表

语法形式如下：

1	DROP TABLE tablename;

删除表 t_calss

1 2	mysql> DROP TABLE t_class; Query OK, 0 rows affected (0.01 sec)

检验是否删除成功

1 2	mysql> DESC t_class; ERROR 1146 (42S02): Table 'school.t_class' doesn't exist

修改表

修改表名

语法形式如下：

1	ALTER TABLE oldTablename RENAME [TO] newTablename;

将表t_calss的名字改为tab_class

1 2	mysql> ALTER TABLE t_class RENAME TO tab_class; Query OK, 0 rows affected (0.00 sec)

检验是否修改成功

mysql> DESC t_class;
ERROR 1146 (42S02): Table 'school.t_class' doesn't exist

mysql> DESC tab_class;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| classno  | int(11)     | YES  |     | NULL    |       |
| cname    | varchar(20) | YES  |     | NULL    |       |
| loc      | varchar(20) | YES  |     | NULL    |       |
| stucount | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

增加字段

语法形式如下：

1
2
3

ALTER TABLE tablename ADD propName propType; # propName 字段名 propType 字段类型 字段会添加代最后一个位置
ALTER TABLE tablename ADD propName propType FIRST; #字段在表的第一个位置
ALTER TABLE tablename ADD propName propType AFTER pNameOld;#添加字段在 已存在字段pNameOld之后

在表t_calss中添加advisor字段

mysql> DESC t_class;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| classno  | int(11)     | YES  |     | NULL    |       |
| cname    | varchar(20) | YES  |     | NULL    |       |
| loc      | varchar(20) | YES  |     | NULL    |       |
| stucount | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE t_class ADD advisor VARCHAR(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t_class;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| classno  | int(11)     | YES  |     | NULL    |       |
| cname    | varchar(20) | YES  |     | NULL    |       |
| loc      | varchar(20) | YES  |     | NULL    |       |
| stucount | int(11)     | YES  |     | NULL    |       |
| advisor  | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

在表t_calss的第一个位置添加advisor字段

mysql> ALTER TABLE t_class ADD advisor VARCHAR(20) FIRST;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t_class;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| advisor  | varchar(20) | YES  |     | NULL    |       |
| classno  | int(11)     | YES  |     | NULL    |       |
| cname    | varchar(20) | YES  |     | NULL    |       |
| loc      | varchar(20) | YES  |     | NULL    |       |
| stucount | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

在表t_calss的cname后添加advisor字段

mysql> ALTER TABLE t_class ADD advisor VARCHAR(20) AFTER cname;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t_class;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| classno  | int(11)     | YES  |     | NULL    |       |
| cname    | varchar(20) | YES  |     | NULL    |       |
| advisor  | varchar(20) | YES  |     | NULL    |       |
| loc      | varchar(20) | YES  |     | NULL    |       |
| stucount | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除字段

语法形式如下：

mysql> ALTER TABLE t_class DROP advisor;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t_class;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| classno  | int(11)     | YES  |     | NULL    |       |
| cname    | varchar(20) | YES  |     | NULL    |       |
| loc      | varchar(20) | YES  |     | NULL    |       |
| stucount | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改字段

根据创建表的语法，字段是由字段名和字段类型定义的。修改字段除了可以修改字段名，还可以修改字段的数据类型。由于表中拥有很多字段，因此还可以修改字段顺序。

语法形式如下：

ALTER TABLE tablename MODIFY propNmae propType;
ALTER TABLE tablename CHANGE pNameOld pNameNew pTypeOld;
ALTER TABLE tablename CHANGE pNameOld pNameNew pTypeNew;
ALTER TABLE tablename MODIFY propName1 propType FIRST|AFTER propNmae2;

修改classno

mysql> ALTER TABLE t_class CHANGE classno classid VARCHAR(20);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t_class;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| classid  | varchar(20) | YES  |     | NULL    |       |
| cname    | varchar(20) | YES  |     | NULL    |       |
| loc      | varchar(20) | YES  |     | NULL    |       |
| stucount | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

将表t_calss中classno调整到字段cname之后

mysql> ALTER TABLE t_class MODIFY classno INT AFTER cname;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t_class;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| cname    | varchar(20) | YES  |     | NULL    |       |
| classno  | int(11)     | YES  |     | NULL    |       |
| loc      | varchar(20) | YES  |     | NULL    |       |
| stucount | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改字段数据类型

mysql> desc t_class;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| cname    | varchar(20) | YES  |     | NULL    |       |
| classno  | int(11)     | YES  |     | NULL    |       |
| loc      | varchar(20) | YES  |     | NULL    |       |
| stucount | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE t_class MODIFY classno DOUBLE ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_class;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| cname    | varchar(20) | YES  |     | NULL    |       |
| classno  | double      | YES  |     | NULL    |       |
| loc      | varchar(20) | YES  |     | NULL    |       |
| stucount | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

操作标的约束

完整性约束条件

约束条件	说明
PRIMARY KEY	标识该属性为该标的主键，可以唯一标识对应的元组
FOREIGN KEY	标识该属性为该标的外键，与之联系的是某表的主键
NOT NULL	标识该属性不能为空
UNIQUE	标识该属性的值是唯一的
AUTO_INCREMENT	标识该属性得值自动增加，这是MySQL语句的特色
DEFAULT	为该属性设置默认值

设置表字段的非空约束（NOT NULL , NK)

CREATE TABLE tablename(
    porpNmae porpType NOT NULL,
    ……
)；

设置表字段的默认值（DEFAULT)

CREATE TABLE tablename(
    porpNmae porpType DEFAULT defaultValue,
    ……
)；

设置表字段的唯一约束（UNIQUE)

CREATE TABLE tablename(
    porpNmae porpType UNIQUE,
    ……
)；

设置表字段的主键约束（PRIMARY KEY)

主键是表的特殊字段，能唯一标识表中的每条信息

单字段主键


CREATE TABLE tablename(
    porpNmae porpType PRIMARY KEY,
    ……
)；

多字段主键

主键是由多个属性组合而成时，在属性定义完之后统一设置主键。

CREATE TABLE tablename(
    porpNmae1 porpType1,
    porpNmae2 porpType2,
    ……
    [CONSTRAINT PK_NAME] PRIMARY KEY(porpNmae1,porpName2)
)；

设置表字段的外键约束（FOREIGN KEY, FK)

外建是表的一个特殊字段，外键约束是为了保证多个表（通常是两个表）之间的参照完整性，即构造两个表的字段之间的参照关系。

设置外键约束的两个表之间具有父子关系，即子表中的某个字段的取值范围由父表决定。

在具体设置FK约束时，设置FK约束的字段必须依赖于数据库中以经存在的父表的主键，同时外键可以为空（NULL).

CREATE TABLE tablename_1(
    porpNmae1_1 porpType1_1,
    porpNmae1_2 porpType1_2,
    ……
    CONSTRAINT FK_prop FOREIGN KEY(porpNmae1_1)
    REFERENCES tablename_2(porpName2_1)
)；

tablename_1 参数是要设置外键的表名

porpName1_1 参数是设置外键的字段

tablename_2 是父表的名称

porpName2_1 是父表中设置主键约束的字段名

mysql> CREATE TABLE t_class(
    -> classno INT PRIMARY KEY,
    -> cname VARCHAR(20),
    -> loc VARCHAR(20),
    -> stucount INT);
Query OK, 0 rows affected (0.01 sec)

mysql> DESC t_class;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| classno  | int(11)     | NO   | PRI | NULL    |       |
| cname    | varchar(20) | YES  |     | NULL    |       |
| loc      | varchar(20) | YES  |     | NULL    |       |
| stucount | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> CREATE TABLE t_student(
    -> stuno INT PRIMARY KEY ,
    -> sname VARCHAR(20),
    -> sage INT ,
    -> sgender VARCHAR(4),
    -> classno INT ,
    -> CONSTRAINT fk_classno FOREIGN KEY(classno)
    -> REFERENCES t_class(classno)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> DESC t_student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stuno   | int(11)     | NO   | PRI | NULL    |       |
| sname   | varchar(20) | YES  |     | NULL    |       |
| sage    | int(11)     | YES  |     | NULL    |       |
| sgender | varchar(4)  | YES  |     | NULL    |       |
| classno | int(11)     | YES  | MUL | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

使用架构设计器查看的效果（SQLyog)

本文作者： Coderyy
本文链接： https:/github.com/iforeverhz/2020/02/16/MySql表相关操作/
版权声明： 本博客所有文章除特别声明外，均采用 MIT 许可协议。转载请注明出处！