首页 大杂烩正文

MySQL-表的基本操作

原创
小哥 2年前 (2023-05-17) 阅读数 37 #大杂烩

1、创建数据表

创建一个数据表是指在一个已经创建的数据库创建一个新表。{use 数据库名} 输入指定的数据库表并执行操作。

创建表

语法:

CREATE TABLE <表名> (字段名1， 数据类型 [列级约束] [默认值]， 字段名2， 数据类型 [列级约束] [默认值]，...);

例：

创建bbs库并进入

mysql> create database bbs;
Query OK, 1 row affected (0.00 sec)
mysql> use bbs;
Database changed

创建test表编号int类型,名字是varchar类型、部门int类型,工资是float类型。

int：整数 4个字节

varchar:变量字符串 0~65535

float：浮点数 4个字节

mysql> create table test(
    -> id int,
    -> name varchar(20),
    -> deptid int,
    -> salary float);
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+---------------+
| Tables_in_bbs |
+---------------+
| test          |
+---------------+
1 row in set (0.00 sec)

插入两行数据。null。

mysql> insert into test values(1,zs,10,3000);
Query OK, 1 row affected (0.05 sec)
​
mysql> insert into test (id,name,deptid) values (2,ls,20);
Query OK, 1 row affected (0.00 sec)
​
mysql> select * from test;
+------+------+--------+--------+
| id   | name | deptid | salary |
+------+------+--------+--------+
|    1 | zs   |     10 |   3000 |
|    2 | ls   |     20 |   NULL |
+------+------+--------+--------+
2 rows in set (0.00 sec)

主键约束

主键约束要求:主键列的数据是独一无二的,不能是空的。

（1)单字段主键

语法：

字段名 数据类型 PRIMARY KEY [默认值]

例：

创建file1表，定义id为主键。

mysql> create table file1(
    -> id int primary key,
    -> name varchar(20),
    -> deptid int,
    -> salary float);
Query OK, 0 rows affected (0.01 sec)

插入两行数据,主键id都为1插入第二条数据时发生了错误,表明id它已经存在,不能重复。

mysql> insert into file1 values(1,zs,10,3000);
Query OK, 1 row affected (0.01 sec)
​
mysql> insert into file1 values(1,ls,20,3000);
ERROR 1062 (23000): Duplicate entry 1 for key PRIMARY

（2)指定主键定义所有列

语法：

[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

创建file2表,定义了一个主键。

mysql> create table file2(
    -> id int,
    -> name varchar(20),
    -> deptid int,
    -> salary float,
    -> PRIMARY KEY(id));
Query OK, 0 rows affected (0.01 sec)

插入两行数据,主键id都为1插入第二条数据时发生了错误,表明id它已经存在,不能重复。

mysql> insert into file2 values(1,zs,10,3000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into file2 values(1,ls,20,3000);
ERROR 1062 (23000): Duplicate entry 1 for key PRIMARY

（3)多领域联合主键

语法：

PRIMARY KEY [字段1，字段2，....]

创建file3表,定义多个主键结束。

mysql> create table file3(
    -> name varchar(20),
    -> deptid int,
    -> salary float,
    -> PRIMARY KEY(name,deptid));
Query OK, 0 rows affected (0.01 sec)

插入多行数据的主键name和deptid”,这表明可能有多个zs”或多个”10“但不能有两个名称”zs”和“10”。

mysql> insert into file3 values(zs,10,3000),(ls,20,4000),(ww,30,5000);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into file3 values(zs,40,6000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into file3 values(ls,10,6000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into file3 values(zs,10,6000);
ERROR 1062 (23000): Duplicate entry zs-10 for key PRIMARY

外键约束

一个外键是用来建立一个连接两个表的数据,可以一个或多个列。

语法：

[CONSTRAINT<外键名>] FOREIGN KEY [字段名1，字段名2...] REFERENCES<主表名> 主键列1[主键列2...]

创建两个表外键名称dep_id字段名是deptid外键的定义是dept1表中的id列。

mysql> create table dept1(
    -> id int PRIMARY KEY,
    -> name varchar(20) not null,
    -> location varchar(50));
Query OK, 0 rows affected (0.05 sec)

mysql> create table emp1(
    -> id int PRIMARY KEY,
    -> name varchar(20),
    -> deptid int,
    -> salary float,
    -> CONSTRAINT dep_id FOREIGN KEY(deptid) REFERENCES dept1(id));
Query OK, 0 rows affected (0.02 sec)

dept1创建三个表中的数据行

mysql> insert into dept1 values(1,zs,bj),(2,ls,hd),(3,ww,tz);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

emp1创建第一个表中数据项是正常的,但第二个数据项的创建失败(因为它是创建的dept1表中的id列没有“4”，所以emp1表deptid列不能被创造。dept1表中的id列没有数据,emp1表的deptid无法创建列。

mysql> insert into emp1 values(1,zs,1,3000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp1 values(2,ls,4,3000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (bbs.emp1, CONSTRAINT dep_id FOREIGN KEY (deptid) REFERENCES dept1 (id))
mysql> insert into emp1 values(2,ls,3,3000);
Query OK, 1 row affected (0.00 sec)

非空约束

一个非空约束是指一个字段的值不能是空的。

语法：

字段名 数据类型 not null

例1：

创建test1表,列名称不能是空的。

mysql> create table test1(
    -> id int PRIMARY KEY,
    -> name varchar(20) NOT NULL,
    -> deptid int,
    -> salary float);
Query OK, 0 rows affected (0.02 sec)

插入数据,成功插入第一项,第二项除外name插入所有列和提供反馈结果"name"列报错。

mysql> insert into test1 values(1,zs,10,3000);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test1(id,deptid,salary) values(1,10,3000);
ERROR 1364 (HY000): Field name doesnt have a default value

唯一约束

使用unique指定一个列,它表明其独特性和不能有可重复性。

语法：

[CONSTRATIN <约束名>] UNIQUE （<字段名>）

创建test2表，unique指定name列。

mysql> create table test2(
    -> id int PRIMARY KEY,
    -> name varchar(20),
    -> location varchar(50),
    -> CONSTRAINT STH UNIQUE(name));
Query OK, 0 rows affected (0.01 sec)

指定name后列,列名不能具有相同的名称。

mysql> insert into test2 values(1,zs,bj);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test2 values(2,zs,hd);
ERROR 1062 (23000): Duplicate entry zs for key sth

默认约束

默认约束指定列的默认值。

语法：

字段名 数据类型 DEFAULT 默认值

例：

创建test3表，指定deptid列默认为666。

mysql> create table test3(
    -> id int PRIMARY KEY,
    -> name varchar(20) NOT NULL,
    -> deptid int DEFAULT 666,
    -> salary float,
    -> info varchar(50));
Query OK, 0 rows affected (0.02 sec)

插入两行数据,除了deptid列,其他所有列被写入数据。test3表，deptid列的默认值666(默认状态NULL）。

mysql> insert into test3(id,name,salary,info) values(1,zs,3000,bjcp);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test3(id,name,salary,info) values(2,ls,3000,bjcp);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test3;
+----+------+--------+--------+------+
| id | name | deptid | salary | info |
+----+------+--------+--------+------+
|  1 | zs   |    666 |   3000 | bjcp |
|  2 | ls   |    666 |   3000 | bjcp |
+----+------+--------+--------+------+
2 rows in set (0.00 sec)

自动增加的属性值

想象一下,id或类似的列,这就需要每次输入数据插入。

语法：

字段名 数据类型 AUTO_INCREMENT

例：

创建test4表，指定id为主键、自动增加的属性值。

mysql> create table test4(
    -> id int PRIMARY KEY AUTO_INCREMENT,
    -> name varchar(20) not null,
    -> deptid int,
    -> salary float);
Query OK, 0 rows affected (0.01 sec)

插入三行数据,指定插入name、salary。

mysql> insert into test4(name,salary) values(zs,1000),(ls,2000),(ww,3000);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看id自动添加值列。

mysql> select * from test4;
+----+------+--------+--------+
| id | name | deptid | salary |
+----+------+--------+--------+
|  1 | zs   |   NULL |   1000 |
|  2 | ls   |   NULL |   2000 |
|  3 | ww   |   NULL |   3000 |
+----+------+--------+--------+
3 rows in set (0.00 sec)

2、查看表结构

视图基本结构

语法：

DESCRIBE 表名； 或 DESC 表名；

例：

查看test1表结构。

mysql> describe test1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(20) | NO   |     | NULL    |       |
| deptid | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

或desc这两种观点都是一样的结果。

mysql> desc test1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(20) | NO   |     | NULL    |       |
| deptid | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

查看详细结构

语法：

SHOW CREATE TABLE <表名>;

例：

查看test1详细的表结构信息。

mysql> show create table test1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE test1 (
  id int(11) NOT NULL,
  name varchar(20) NOT NULL,
  deptid int(11) DEFAULT NULL,
  salary float DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

3、修改数据表

修改表名

语法：

ALTER TABLE <旧表名> RENAME [TO] <新表名>;

例：

查看所有表,修改test1表改名为file1。

mysql> show tables;
+---------------+
| Tables_in_bbs |
+---------------+
| test1         |
+---------------+
1 row in set (0.01 sec)

mysql> alter table test1 rename file1;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+---------------+
| Tables_in_bbs |
+---------------+
| file1         |
+---------------+
1 row in set (0.00 sec)

修改字段的数据类型

语法：

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

例：

查看file1表结构、修改name列数据类型varchar(50)。

mysql> desc file1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(20) | NO   |     | NULL    |       |
| deptid | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.09 sec)

mysql> alter table file1 modify name varchar(50);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc file1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| deptid | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改字段名

语法：

ALTER TABLE <表名> CHANGE<旧字段名><新字段名> <新的数据类型>

例：

修改file1表name列名为new_name、数据类型是varchar(20);

mysql> alter table file1 change name new_name varchar(20);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc file1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| new_name | varchar(20) | YES  |     | NULL    |       |
| deptid   | int(11)     | YES  |     | NULL    |       |
| salary   | float       | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

添加字段

语法：

ALTER TABLE <表名> ADD <新字段名><数据类型> [约束条件] [FIRST|AFTER 字段名称已经存在]

例：

在file1添加表的最后location列。

mysql> desc file1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| new_name | varchar(20) | YES  |     | NULL    |       |
| deptid   | int(11)     | YES  |     | NULL    |       |
| salary   | float       | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table file1 add location varchar(50);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc file1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| new_name | varchar(20) | YES  |     | NULL    |       |
| deptid   | int(11)     | YES  |     | NULL    |       |
| salary   | float       | YES  |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

在file1添加表的第一行mail列。

mysql> alter table file1 add mail float first;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc file1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| mail     | float       | YES  |     | NULL    |       |
| id       | int(11)     | NO   | PRI | NULL    |       |
| new_name | varchar(20) | YES  |     | NULL    |       |
| deptid   | int(11)     | YES  |     | NULL    |       |
| salary   | float       | YES  |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
| email    | float       | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

删除字段

语法：

ALTER TABLE <表名> DROP <字段名>

例：

mysql> alter table file1 drop mail;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc file1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| new_name | varchar(20) | YES  |     | NULL    |       |
| deptid   | int(11)     | YES  |     | NULL    |       |
| salary   | float       | YES  |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改安排位置

语法：

ALTER TABLE <表名> MODIFY <字段名> <数据类型> FIRST | AFTER <字段2>

例：

修改location列第一行。

mysql> alter table file1 modify location varchar(50) first;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc file1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| location | varchar(50) | YES  |     | NULL    |       |
| id       | int(11)     | NO   | PRI | NULL    |       |
| new_name | varchar(20) | YES  |     | NULL    |       |
| deptid   | int(11)     | YES  |     | NULL    |       |
| salary   | float       | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改location列到new_name列下。

mysql> alter table file1 modify location varchar(20) after new_name;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc file1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| new_name | varchar(20) | YES  |     | NULL    |       |
| location | varchar(20) | YES  |     | NULL    |       |
| deptid   | int(11)     | YES  |     | NULL    |       |
| salary   | float       | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改存储引擎

语法：

ALTER TABLE <表名> ENGINE=<修改存储引擎>

例：

查看表结构,引擎ENGINE=InnoDB修改后的表引擎ENGINE=MyISAM。

mysql> show create table file1;
#省略一些内容
| file1 | CREATE TABLE file1 (
  id int(11) NOT NULL,
  new_name varchar(20) DEFAULT NULL,
  location varchar(20) DEFAULT NULL,
  deptid int(11) DEFAULT NULL,
  salary float DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
#省略一些内容
1 row in set (0.01 sec)
mysql> alter table file1 engine=myisam;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table file1;
#省略一些内容
| file1 | CREATE TABLE file1 (
  id int(11) NOT NULL,
  new_name varchar(20) DEFAULT NULL,
  location varchar(20) DEFAULT NULL,
  deptid int(11) DEFAULT NULL,
  salary float DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
#省略一些内容
1 row in set (0.01 sec)
###如果没有其他需求变化innodb才可以进行下面的操作
mysql> alter table file1 engine innodb;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除表的外键约束

语法：

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束的名字>

例：

没有外键,需要首先创建。innodb只有这样,外键可以创建。file2表结构,表明file2表中的deptid列外键约束file1表中的id列;

mysql> create table file2(
    -> id int primary key,
    -> name varchar(20),
    -> deptid int,
    -> constraint file_key foreign key (deptid) references file1(id));
Query OK, 0 rows affected (0.01 sec)
mysql> show create table file2;
#省略一些内容
| file2 | CREATE TABLE file2 (
  id int(11) NOT NULL,
  name varchar(20) DEFAULT NULL,
  deptid int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY file_key (deptid),
  CONSTRAINT file_key FOREIGN KEY (deptid) REFERENCES file1 (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
#省略一些内容
1 row in set (0.02 sec)
mysql> alter table file2 drop foreign key file_key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table file2;
#省略一些内容
| file2 | CREATE TABLE file2 (
  id int(11) NOT NULL,
  name varchar(20) DEFAULT NULL,
  deptid int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY file_key (deptid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
#省略一些内容
1 row in set (0.00 sec)

删除唯一约束

alter table 表名 drop index 约束名称;

或

drop index 约束名称 on 表名;

4、删除数据表

删除表不相关

语法：

DROP TABLE [IF EXISTS]表1，表2...

例：

根据上面的命令创建test1表、test2所有表和删除表、视图test1表、test2表。

mysql> show tables;
+---------------+
| Tables_in_bbs |
+---------------+
| file1         |
| file2         |
| test1         |
| test2         |
+---------------+
4 rows in set (0.00 sec)
mysql> drop table if exists test1,test2;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+---------------+
| Tables_in_bbs |
+---------------+
| file1         |
| file2         |
+---------------+
2 rows in set (0.01 sec)

删除相关的主数据表

或创建两个test表，test2表的deptid列指定test1表的id列。

mysql> create table test1(
    -> id int primary key,
    -> name varchar(20),
    -> location varchar(50));
Query OK, 0 rows affected (0.01 sec)

mysql> create table test2(
    -> id int primary key,
    -> name varchar(20),
    -> deptid int,
    -> salary float,
    -> constraint test_key foreign key(deptid) references test1(id));
Query OK, 0 rows affected (0.01 sec)

删除主表test1发现删除失败。

mysql> drop table test1;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

在这一点上,必须首先释放子表test2约束,然后删除主表test1现在你可以删除它。

mysql> alter table test2 drop foreign key test_key;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

总结

创建表

create table 表名(字段名1 字段类型1 [约束条件1],字段名2 字段类型2 [约束条件2]);

约束条件

主键约束

PRIMARY KEY

外键约束

CONSTRAINT [外键名] FOREIGN KEY [字段名] REFERENCES [主表名] 主键列1

非空约束

NOT NULL

唯一约束

[CONSTRATIN <约束名>] UNIQUE （<字段名>）

默认约束

DEFAULT 默认值

自动添加值

AUTO_INCREMENT

表结构

查看表结构

基本结构

DESCRIBE 表名;

基本结构

DESC 表名;

详细结构

SHOW CREATE TABLE 表名;

修改表

ALTER TABLE <旧表名> RENAME [TO] <新表名>;

修改数据表

修改表名

RENAME [TO]

修改字段的数据类型

MODIFY

修改字段名

CHANGE

添加字段

ADD [FIRST|AFTER 字段名称已经存在]

删除字段

DROP

修改安排位置

MODIFY

修改存储引擎

ENGINE

删除表的外键约束

DROP FOREIGN KEY

版权声明

所有资源都来源于爬虫采集,如有侵权请联系我们,我们将立即删除

上一篇：shell脚本应用（二） 下一篇：群集搭建【LNMP+负载均衡+高可用+跳板机】
相关文章
小哥

小哥

作者文章
浏览更多
热门
最新文章
标签列表
粤ICP备19068341号
所有资源都来源于爬虫采集,如有侵权请联系我们,我们将立即删除
Powered By Z-BlogPHP Theme By 编程老白