mysql数据表的基本操作

数据库之扑朔迷离 2020-04-20

1.创建数据表
在创建完数据库之后,接下来我们就需要创建数据表。创建数据表是指在已经创建好的数据库中建立新表。创
建数据表的过程是规定数据列的属性的过程,同时也是实施数据完整性约束的过程。
2.创建表的语法形式
数据表属于数据库,在创建数据表之前,应该使用语句“USE <数据库名>” 指定操作是从哪个数据库中进行,如
果没有选择数据库,会报错
语法:
CREATE TABLE <表名> ( 字段名1, 数据类型 [列级别约束条件] [默认值], 字段名2, 数据类型 [列级
别约束条件] [默认值], ... ... ):

例如:

mysql> CREATE TABLE tb_emp1
 -> (
 -> id INT(11),
 -> name VARCHAR(25),
 -> deptId INT(11),
 -> salary FLOAT
 -> );
Query OK, 0 rows affected (0.08 sec)
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| tb_emp1 |
+----------------+
1 row in set (0.05 sec)

1.1.使用主键约束
主键约束要求主键列的数据唯一,并且不允许为空。

  1. 单字段主键
    语法: 字段名 数据类型 PRIMARY KEY [默认值]
    例如:
    mysql> CREATE TABLE tb_emp2
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptID INT(11),
    -> salary FLOAT
    -> );
    Query OK, 0 rows affected (0.05 sec)
  2. 在定义完所有列之后指定主键
    语法:
    [CONSTRAINT <约束名>] PRIMARY KEY [字段名]
    例如:
    mysql> CREATE TABLE tb_emp3
    -> (
    -> id INT(11),
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(id)
    -> );
    Query OK, 0 rows affected (0.01 sec)
  3. 多字段联合主键
    语法:
    PRIMARY KEY [字段1,字段2,....]
    例如:
    mysql> CREATE TABLE tb_emp4
    -> (
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(name,deptId)
    -> );
    Query OK, 0 rows affected (0.00 sec)

    1.2.使用外键约束
    外键用来在两个表数据之间建立连接,它可以是一列或者多列

    语法:
    [CONSTRAINT<外键名>] FOREIGN KEY [字段名1,字段名2...] REFERENCES<主表名> 主键列1[主键列
    2...]
    例如:
    mysql> CREATE TABLE tb_dept1
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22) NOT NULL,
    -> location VARCHAR(50)
    -> );
    Query OK, 0 rows affected (0.00 sec)
    mysql> CREATE TABLE tb_emp5
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
    -> );
    Query OK, 0 rows affected (0.06 sec)

    1.3. 使用非空约束
    非空约束指字段的值不能为空。

    语法:
    字段名 数据类型 not null
    例如:
    mysql> CREATE TABLE tb_emp6
    -> (
    -> id INT(11) PRIMARY KEY ,
    -> name VARCHAR(25) NOT NULL,
    -> deptId INT(11),
    -> salary FLOAT
    -> );
    Query OK, 0 rows affected (0.06 sec)
    或者 语法:
    [CONSTRATIN <约束名>] UNIQUE (<字段名>)
    例如:
    mysql> CREATE TABLE tb_dept3
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22),
    -> location VARCHAR(50),
    -> CONSTRAINT STH UNIQUE(name)
    -> );
    Query OK, 0 rows affected (0.00 sec)

    1.4.使用默认约束
    默认约束指定某列的默认值。

    语法:
    字段名 数据类型 DEFAULT 默认值
    例如:
    mysql> CREATE TABLE tb_emp7
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25) NOT NULL,
    -> deptId INT(11) DEFAULT 1111,
    -> salary FLOAT,
    -> info VARCHAR(50)
    -> );
    Query OK, 0 rows affected (0.00 sec)

    1.5.设置表的属性值自动增加

    语法:
    字段名 数据类型 AUTO_INCREMENT
    例如:
    mysql> CREATE TABLE tb_emp8
    -> (
    -> id INT(11) PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(25) NOT NULL,
    -> deptId INT(11),
    -> salary FLOAT
    -> );
    Query OK, 0 rows affected (0.00 sec)

    插入数据验证:

    mysql> INSERT INTO tb_emp8(name,salary)
    -> VALUES(‘lucy‘,1000),(‘lura‘,1200),(‘kevin‘,1500);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3 Duplicates: 0 Warnings: 0

    查看:

    mysql> SELECT * FROM tb_emp8;
    +----+-------+--------+--------+
    | id | name | deptId | salary |
    +----+-------+--------+--------+
    | 1 | lucy | NULL | 1000 |
    | 2 | lura | NULL | 1200 |
    | 3 | kevin | NULL | 1500 |
    +----+-------+--------+--------+
    3 rows in set (0.00 sec)

    3.查看数据表结构
    3.1. 查看表基本结构语句DESCRIBE

    语法:
    DESCRIBE 表名; 或 DESC 表名;
    例如:
    mysql> DESCRIBE tb_dept1;
    +----------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | id | int(11) | NO | PRI | NULL | |
    | name | varchar(22) | NO | | NULL | |
    | location | varchar(50) | YES | | NULL | |
    +----------+-------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)

    或者

    mysql> DESC tb_dept1;
    +----------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | id | int(11) | NO | PRI | NULL | |
    | name | varchar(22) | NO | | NULL | |
    | location | varchar(50) | YES | | NULL | |
    +----------+-------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)

    3.2.查看表详细结构语句

    语法:
    SHOW CREATE TABLE <表名\G>
    mysql> SHOW CREATE TABLE tb_emp1\G
    *************************** 1. row ***************************
    Table: tb_emp1
    Create Table: CREATE TABLE `tb_emp1` (
    `id` int(11) DEFAULT NULL,
    `name` varchar(25) DEFAULT NULL,
    `deptId` int(11) DEFAULT NULL,
    `salary` float DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

    3.3. 修改数据表 3.3.1 修改表名

    语法:
    ALTER TABLE <旧表名> RENAME [TO] <新表名>.
    例如:
    mysql> SHOW TABLES;
    +----------------+
    | Tables_in_test |
    +----------------+
    | tb_dept1 |
    | tb_dept3 |
    | tb_emp1 |
    | tb_emp2 |
    | tb_emp3 |
    | tb_emp4 |
    | tb_emp5 |
    | tb_emp7 |
    | tb_emp8 |
    +----------------+
    9 rows in set (0.00 sec)
    mysql> ALTER TABLE tb_dept3 RENAME tb_deptment3;
    Query OK, 0 rows affected (0.05 sec)
    mysql> SHOW TABLES;
    +----------------+
    | Tables_in_test |
    +----------------+
    | tb_dept1 |
    | tb_deptment3 |
    | tb_emp1 |
    | tb_emp2 |
    | tb_emp3 |
    | tb_emp4 |
    | tb_emp5 |
    | tb_emp7 |
    | tb_emp8 |
    +----------------+
    9 rows in set (0.00 sec)

    3.4.修改字段的数据类型

    语法:
    ALTER TABLE <表名> MODIFY <字段名> <数据类型>
    例如:
    mysql> DESC tb_dept1;
    +----------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | id | int(11) | NO | PRI | NULL | |
    | name | varchar(22) | NO | | NULL | |
    | location | varchar(50) | YES | | NULL | |
    +----------+-------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
    mysql> ALTER TABLE tb_dept1 MODIFY name VARCHAR(30);
    Query OK, 0 rows affected (0.08 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> DESC tb_dept1;
    +----------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | id | int(11) | NO | PRI | NULL | |
    | name | varchar(30) | YES | | NULL | |
    | location | varchar(50) | YES | | NULL | |
    +----------+-------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)

    3. 5.修改字段名

    语法:
    ALTER TABLE <表名> CHANGE<旧字段名><新字段名> <新数据类型>
    例如:
    mysql> ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);
    Query OK, 0 rows affected (0.10 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> DESC tb_dept1;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id | int(11) | NO | PRI | NULL | |
    | name | varchar(30) | YES | | NULL | |
    | loc | varchar(50) | YES | | NULL | |
    +-------+-------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)

    3.6. 添加字段

    语法:
    ALTER TABLE <表名> ADD <新字段名><数据类型> [约束条件] [FIRST|AFTER 已存在字段名]
    例如:
    mysql> DESC tb_dept1;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id | int(11) | NO | PRI | NULL | |
    | name | varchar(30) | YES | | NULL | |
    | loc | varchar(50) | YES | | NULL | |
    +-------+-------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
    mysql> ALTER TABLE tb_dept1 ADD column1 VARCHAR(12) not null;
    Query OK, 0 rows affected (0.02 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> DESC tb_dept1;
    +---------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +---------+-------------+------+-----+---------+-------+
    | id | int(11) | NO | PRI | NULL | |
    | name | varchar(30) | YES | | NULL | |
    | loc | varchar(50) | YES | | NULL | |
    | column1 | varchar(12) | NO | | NULL | |
    +---------+-------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)

    或者

    mysql> ALTER TABLE tb_dept1 ADD column2 INT(11) FIRST;
    Query OK, 0 rows affected (0.02 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> DESC tb_dept1;
    +---------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +---------+-------------+------+-----+---------+-------+
    | column2 | int(11) | YES | | NULL | |
    | id | int(11) | NO | PRI | NULL | |
    | name | varchar(30) | YES | | NULL | |
    | loc | varchar(50) | YES | | NULL | |
    | column1 | varchar(12) | NO | | NULL | |
    +---------+-------------+------+-----+---------+-------+
    5 rows in set (0.00 sec)

    或者

    mysql> ALTER TABLE tb_dept1 ADD column3 INT(11) AFTER name ;
    Query OK, 0 rows affected (0.09 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> DESC tb_dept1;
    +---------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +---------+-------------+------+-----+---------+-------+
    | column2 | int(11) | YES | | NULL | |
    | id | int(11) | NO | PRI | NULL | |
    | name | varchar(30) | YES | | NULL | |
    | column3 | int(11) | YES | | NULL | |
    | loc | varchar(50) | YES | | NULL | |
    | column1 | varchar(12) | NO | | NULL | |
    +---------+-------------+------+-----+---------+-------+
    6 rows in set (0.00 sec)

    3.7.删除字段

    语法:
    ALTER TABLE <表名> DROP <字段名>
    例如:
    mysql> ALTER TABLE tb_dept1 DROP column2;
    Query OK, 0 rows affected (0.03 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> DESC tb_dept1;
    +---------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +---------+-------------+------+-----+---------+-------+
    | id | int(11) | NO | PRI | NULL | |
    | name | varchar(30) | YES | | NULL | |
    | column3 | int(11) | YES | | NULL | |
    | loc | varchar(50) | YES | | NULL | |
    | column1 | varchar(12) | NO | | NULL | |
    +---------+-------------+------+-----+---------+-------+
    5 rows in set (0.00 sec)

    3.8.修改字段的排列位置

    语法:
    ALTER TABLE <表名> MODIFY <字段名> <数据类型> FIRST | AFTER <字段2> 例如:
    mysql> ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) FIRST;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> DESC tb_dept1;
    +---------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +---------+-------------+------+-----+---------+-------+
    | column1 | varchar(12) | YES | | NULL | |
    | id | int(11) | NO | PRI | NULL | |
    | name | varchar(30) | YES | | NULL | |
    | column3 | int(11) | YES | | NULL | |
    | loc | varchar(50) | YES | | NULL | |
    +---------+-------------+------+-----+---------+-------+
    5 rows in set (0.01 sec)

    或者

    mysql> ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) AFTER loc;
    Query OK, 0 rows affected (0.06 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> DESC tb_dept1;
    +---------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +---------+-------------+------+-----+---------+-------+
    | id | int(11) | NO | PRI | NULL | |
    | name | varchar(30) | YES | | NULL | |
    | column3 | int(11) | YES | | NULL | |
    | loc | varchar(50) | YES | | NULL | |
    | column1 | varchar(12) | YES | | NULL | |
    +---------+-------------+------+-----+---------+-------+
    5 rows in set (0.00 sec)

    3.9.更改表的存储引擎

    语法:
    ALTER TABLE <表名> ENGINE=<更改后的存储引擎>
    例如:
    mysql> SHOW CREATE TABLE tb_deptment3\G
    *************************** 1. row ***************************
    Table: tb_deptment3
    Create Table: CREATE TABLE `tb_deptment3` (
    `id` int(11) NOT NULL,
    `name` varchar(22) DEFAULT NULL,
    `location` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `STH` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
    mysql> ALTER TABLE tb_deptment3 ENGINE=MyISAM;
    Query OK, 0 rows affected (0.03 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> SHOW CREATE TABLE tb_deptment3\G
    *************************** 1. row ***************************
    Table: tb_deptment3
    Create Table: CREATE TABLE `tb_deptment3` (
    `id` int(11) NOT NULL,
    `name` varchar(22) DEFAULT NULL,
    `location` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `STH` (`name`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

    3.10. 删除表的外键约束

    语法:
    ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>
    mysql> CREATE TABLE tb_emp9
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept1(id)
    -> );
    Query OK, 0 rows affected (0.04 sec)
    mysql> SHOW CREATE TABLE tb_emp9\G
    *************************** 1. row ***************************
    Table: tb_emp9
    Create Table: CREATE TABLE `tb_emp9` (
    `id` int(11) NOT NULL,
    `name` varchar(25) DEFAULT NULL,
    `deptId` int(11) DEFAULT NULL,
    `salary` float DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `fk_emp_dept` (`deptId`),
    CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
    mysql> ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp_dept;
    Query OK, 0 rows affected (0.08 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> SHOW CREATE TABLE tb_emp9\G
    *************************** 1. row ***************************
    Table: tb_emp9
    Create Table: CREATE TABLE `tb_emp9` (
    `id` int(11) NOT NULL,
    `name` varchar(25) DEFAULT NULL,
    `deptId` int(11) DEFAULT NULL,
    `salary` float DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `fk_emp_dept` (`deptId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

    4.删除数据表
    4.1. 删除没有被关联的表

    语法:
    DROP TABLE [IF EXISTS]表1,表2...
    例如:mysql> DROP TABLE IF EXISTS tb_dept2;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    mysql> SHOW TABLES;
    +----------------+
    | Tables_in_test |
    +----------------+
    | tb_dept1 |
    | tb_deptment3 |
    | tb_emp1 |
    | tb_emp2 |
    | tb_emp3 |
    | tb_emp4 |
    | tb_emp5 |
    | tb_emp7 |
    | tb_emp8 |
    | tb_emp9 |
    +----------------+
    10 rows in set (0.00 sec)

    4.2.删除被其他表关联的主表

    先创建表tb_dept2
    mysql> CREATE TABLE tb_dept2
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22),
    -> location VARCHAR(50)
    -> );
    Query OK, 0 rows affected (0.08 sec)
    创建表tb_emp
    mysql> CREATE TABLE tb_emp
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> CONSTRAINT fk_emp_dept FOREIGN KEY(deptId) REFERENCES tb_dept2(id)
    -> );
    Query OK, 0 rows affected (0.09 sec)
直接删除父表tb_dept2
mysql> DROP TABLE tb_dept2;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint
fails

可以看到在外键约束时,主表不能直接删除。
mysql> ALTER TABLE tb_emp DROP FOREIGN KEY fk_emp_dept;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
解除关联子表tb_dept的外键约束
mysql> DROP TABLE tb_dept2;
Query OK, 0 rows affected (0.05 sec)
表就可以被删除

相关推荐