MySQL 数据表管理

最新推荐文章于 2023-08-24 11:14:04 发布

pish7

最新推荐文章于 2023-08-24 11:14:04 发布

阅读量218

点赞数

分类专栏： SQL 文章标签： MySQL SQL 数据库

本文链接：https://blog.csdn.net/pish7/article/details/78701312

版权

SQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

列出数据表

列出表结构

查看数据表的创建信息

show create table [数据库名.]表名;
--------------------
mysql> show create table pri_01\G
*************************** 1. row ***************************
Table: pri_01
Create Table: CREATE TABLE `pri_01` (
`id` int(11) NOT NULL,
`name` varchar(25) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

创建数据表

create table [数据库名.]表名
(
列名1 数据类型 [列级别约束条件] [DEFAULT 默认值],
列名2 数据类型 [列级别约束条件] [DEFAULT 默认值],
列名3 数据类型 [列级别约束条件] [DEFAULT 默认值],
...
[表级别约束条件],
[表级别约束条件],
[表级别约束条件],
...
)[engine=存储引擎][default charset=默认字符集][collate=校对方式];
--------------------
MariaDB [test]> create table Products
-> (
-> prod_id CHAR(10) NOT NULL,
-> vend_id CHAR(10) NOT NULL,
-> prod_name CHAR(254) NOT NULL,
-> prod_price DECIMAL(8,2) NOT NULL,
-> prod_desc VARCHAR(1000) NULL
-> );

MariaDB [test]> create table aaa
-> (
-> prod_id INTEGER NOT NULL DEFAULT 1,
-> vend_id INTEGER NOT NULL DEFAULT 0
-> );

在创建新的表时，指定的表名必须不存在，否则会出错。防止意外覆盖已有的表，SQL要求首先手工删除该表，然后再重建它，而不是简单地用创建表语句覆盖它。
创建表时，列的第三个参数指定了列的值是否可为NULL，这个参数可省略，此时，默认为NULL。也就是说如果不指定，就是NULL。
只有不允许NULL值的列可作为主键，允许NULL值的列不能作为唯一标识。
默认值经常用于日期或时间戳，可以使用函数作为默认值，例如CURRENT_DATE()，这样在不指定值时将默认使用当前时间。

根据一个已存在的表来创建一个新表

create table xxx as select ... from ... ;
--------------------
因为select语句返回的值其实也是一个表格形式，这个表格可以保存到一个真正的表中。这里的select可以使用任何复杂的查询语句，最后都是得到一张表，再进行保存即可。
MariaDB [test]> create table bb as select b1, b2, b1+b2 from b;
MariaDB [test]> select * from bb;
+------+------+-------+
| b1 | b2 | b1+b2 |
+------+------+-------+
| 1 | 100 | 101 |
| 2 | 200 | 202 |
+------+------+-------+
可见，不仅可以复制原始列，也可以复制成计算字段。不过这也说明了一个问题，如果复制整张旧表，实际上新表与旧表之间，并不是完全相同的，因为复制是一个“值复制”，而没有复制出列的其他特征，如上例中的b1+b2，这一列是否可为NULL？是否有默认值？这些信息就算是在复制原始列，也不会复制过来。

如果要复制一个一模一样的表，可以先用同样的创建语法创建出新表，再用insert into ... select语法从旧表复制数据。

指定主键

指定主键可以在列定义处指定，也可以在定义完列之后在表约束处指定。
列约束：primary key
表约束：primary key (列名1,列名2,列名3...)
--------------------
在列定义处指定主键
mysql> create table pri_01 (
-> id INT(11) primary key,
-> name VARCHAR(25)
-> );

在表约束处指定主键
mysql> create table pri_02 (
-> id INT(11),
-> name VARCHAR(25),
-> primary key (id)
-> );

注意，一个表只能定义一个(组)主键，所以一般在表约束处指定主键，这样可以指定一个字段或多个字段为主键，但如果是在列约束处指定主键，则只能有一列为主键，其他列再指定主键将出错。
另外，定义主键时要注意排列顺序，当其他表要使用这里的主键来创建外键关联时，要与这里定义的主键顺序相同，而且当某一个主键定义为自增列时，必须为主键中的第一主键。

指定外键

外键用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表的外键可以是空值，若不为空值，则必须等于另一个表中对应的主键值。定义外键后，不允许删除在另一个表中具有关联关系的行。也就是说，如果表1的某一列为主键，而且被某些表用作外键，那么表1就不能随便删除数据行了。因为删除可能导致某些表出现非法的外键值。
定义外键的表称为子表或从表，提供主键给其他表定义外键的表称为父表或主表。
外键在表约束处定义：
[constraint 约束名] foreign key(外键列名, 外键列名,外键列名) references 关联表(主键列名, 主键列名, 主键列名)
--------------------
mysql> create table tb_dept1 (
-> id int(11) primary key,
-> name varchar(22) not null,
-> location varchar(50)
-> );
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)
-> );

创建表时可以定义多个外键约束，每个外键约束可以指定1个或多个列，注意外键列必须与主键列数量相同，而且主键列必须全是主键。主键列的第一个主键必须是主表的第一主键，而且排列顺序必须是主表中主键的顺序相同。也就是说，如果主表有主键(a,b,c)，则其他表要与该表建立外键关联时，必须指定为(a)/(a,b)/(a,b,c)，而(b)/(a,c)/(b,c)等都是非法的。
当同时使用多个列来指定外键时，这些值的"组合"必须与相应主键的组合的值相同。假期为s表创建如下外键约束：
constraint fk_sm foreign key(ia,ib) references m(ia,ib)
而且m表的ia和ib列的值包括："1,1"/"2,2"/"3,3"
那么向s表插入数据时，s表中的ia和ib列的值可以是"1,1"/"2,2"/"3,3"，但不能是"1,2"/"2,3"等。

如果有这样3个表：
ma-maa/mab/mac maa和mab为主键
mb-mba/mbb/mbc mba和mbb为主键
sa-saa/sab
合法的外键关系包括：
-- 非法，同一个主表(ma)只能有一个第1主键
saa->maa
saa->mab
-- 合法，但maa和mba都必须是相应表的第1主键，这种情况下saa必须同时符合两个主键的约束
saa->maa
saa->mba
-- 合法，只要maa是第一主键
saa->maa
sab->maa
-- 非法，同一个主表(ma)只能有一个第1主键
saa->maa
sab->mab
-- 合法，只要maa和mba都是第一主键
saa->maa
sab->mba

非空约束

NULL/NOT NULL
非空约束为列约束，可指定为"not null"或"null"，其中"null"为默认值，所以当不明确指定此约束时，默认为null。

唯一性约束

列约束：unique
表约束：[constraint 约束名] unique(列名，列名，列名)
--------------------
此约束要求任意两行之间的某个或某些列值不得相同。与主键的唯一性不同的是，唯一性约束的列可以为NULL，只是最多只能有一个NULL。
mysql> create table mytest1 (
-> id int(11),
-> ic int(11),
-> ia int(11) unique,
-> ib int(11) unique,
-> primary key (id, ic)
-> );

mysql> show create table mytest1\G
*************************** 1. row ***************************
Table: mytest1
Create Table: CREATE TABLE `mytest1` (
`id` int(11) NOT NULL,
`ic` int(11) NOT NULL,
`ia` int(11) DEFAULT NULL,
`ib` int(11) DEFAULT NULL,
PRIMARY KEY (`id`,`ic`),
UNIQUE KEY `ia` (`ia`),
UNIQUE KEY `ib` (`ib`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
注意，主键被自动定义为"not null"约束。

mysql> insert into mytest1(id,ic,ia,ib) values(1,1,1,1);
Query OK, 1 row affected (0.06 sec)
mysql> insert into mytest1(id,ic,ia,ib) values(1,2,2,2);
Query OK, 1 row affected (0.06 sec)
mysql> insert into mytest1(id,ic,ia,ib) values(2,2,1,2);
ERROR 1062 (23000): Duplicate entry '1' for key 'ia'
可见，对于主键的唯一性约束，指的是整体唯一性，而unique唯一性约束，则不是整体约束，而是每个列独自要求唯一性。

将列值设置为自动递增

auto_increment
--------------------
能自动递增的列必须是主键的一部分（且为第一主键），类型必须为整数，而且一个表中最多只能有一列实现自动递增。
在MySQL中，auto_increment的初始值是1，每新增一条记录，字段自动加1。
mysql> create table tb_emp8 (
-> id int(11) primary key auto_increment,
-> name varchar(25) not null,
-> deptId int(11),
-> salary float
-> );

删除表

drop table [数据库名.]表名;
--------------------
MariaDB [test]> drop table aaa;

如果删除的表为主表，即其中有主键与其他表的外键存在关联，则不能直接删除，必须先删除从表，或删除相应的外键约束，之后才能删除。

重命名（移动）表

rename table
[数据库名.]原表名 to [数据库名.]新表名,
[数据库名.]原表名 to [数据库名.]新表名,
[数据库名.]原表名 to [数据库名.]新表名;
alter table [数据库名.]原表名 rename [to] [数据库名.]新表名;
--------------------

注意，加上库名，可实现表在不同库之间移动（可重命名或不重命名），如果不添加库名，实际上就是重命名数据表。

mysql> alter table test_db.mytest1 rename to test.mytest;
Query OK, 0 rows affected (0.18 sec)

mysql> show tables from test;
+----------------+
| Tables_in_test |
+----------------+
| mytest |
+----------------+
1 row in set (0.00 sec)

mysql> rename table test.mytest to test_db.mytest1;
Query OK, 0 rows affected (0.17 sec)

插入行

insert into [数据库名.]表名 values (xxx, xxx, xxx), (xxx, xxx, xxx), (xxx, xxx, xxx)... ;
insert into [数据库名.]表名(列名, 列名, 列名) values (xxx, xxx, xxx), (xxx, xxx, xxx), (xxx, xxx, xxx)... ;
insert into [数据库名.]表名(列名, 列名, 列名) select ...;
--------------------
如果插入时不指定列名，那么values()中的值必须严格与表定义时的列顺序对应，不能多一个也不能少一个。这种方式缺点是非常明显的，必须顺序正常，而且必须输入所有列值（NULL或具有默认值的列也必须输入）。
如果指定了列名，则可以指定部分列名即可（值可为NULL的或具有默认值的列可以不指定），而且values()中的列值与列出的列名对应即可。

在实践中，应注意不要使用没有明确给出列的INSERT语句。给出列能使SQL代码继续发挥作用，即使表结构发生了变化。

注意，插入是按位置顺序插入的，所以使用select插入时，也是对返回的行按列顺序插入到(列名, 列名, 列名)中，并不要求select返回数据的列名与插入的列名相同。

更新行

update 表名 set 列名=新值, 列名=新值, 列名=新值 where ...
--------------------
在update语句中，where子句用于筛选出要更新的行，注意，如果没有where子句，则会更新表的所有行，这往往不是我们想要的。
UPDATE语句中也可以使用子查询，使得能用SELECT语句检索出的数据更新列数据。
如果列值允许为NULL，则可以通过设置NULL来删除某个列的值。

删除行

delete from 表名 where ...
--------------------
在delete语句中，where子句用于筛选出要删除的行，注意，如果没有where子句，则会删除表的所有行，这往往不是我们想要的。
如果想从表中删除所有行，不要使用DELETE。要使用TRUNCATE TABLE语句，它完成相同的工作，而速度更快。

使用update和delete from的注意事项：
1.除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
2.保证每个表都有主键，尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。
3.在UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录。
4.使用强制实施引用完整性的数据库，这样DBMS将不允许删除其数据与其他表相关联的行。
5.有的DBMS允许数据库管理员施加约束，防止执行不带WHERE子句的UPDATE或DELETE语句。如果所采用的DBMS支持这个特性，应该使用它。

创建和删除视图

create view 视图名 as select ... ;
drop view 视图名;
--------------------
视图使用select语句进行创建。

视图实际上就是一段查询规则，而查询的结果可以理解为一张表（多行数据，每行可以有指定的列），这张表尽管不是物理存在的，但在逻辑上完全可以接受这样的一张表，这便是视图。
视图也可以理解为一个预定义的select语句，用来包含部分查询条件，特别是在查询很复杂时，比如一个查询包含很多个条件，而且其中有一部分条件固定不变，另一部分条件经常变动，这时便可以将固定不变的那些条件定义到一个视图中，以后只需使用经常变动的那部分条件对这个视图进行查询即可。
作为视图，它不包含任何列或数据，包含的是一个查询。

创建视图之后，可以用与表基本相同的方式使用它们。可以对视图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加和更新数据（添加和更新数据存在某些限制）。

一个最觉的视图应用是隐藏复杂的SQL，例如在涉及复杂联结时：
create view ProductCustomers as
select cust_name, cust_contact, prod_id
from Customers, Orders, OrderItems
where Customers.cust_id = Orders.cust_id and
OrderItems.order_num = Orders.order_num;
利用视图，可一次性编写基础的SQL，然后根据需要多次使用。通常创建不绑定特定数据的视图，这样视图实现了对表的联结处理以及一定的过滤后，可再通过对视图进行查询来进一步过滤数据。

当然，视图也可以直接使用，比如对于一些经常要使用的复杂select查询，创建视图，相当于是给这样的查询创建别名（或理解为函数）。以后可以使用 select * from view 直接得到结果。

添加数据列

alter table 表名 add 新列名数据类型 [约束条件] [first]|[after 已存在字段];
--------------------
first用于指定将插入的列成为表的第一列，after用于指定将插入的列插入后指定已存在列的后面。如果这两个选项都不指定，则插入后默认成为表的最后一列。

删除数据列

alter table 表名 drop 列名;
--------------------

修改主键

alter table 表名 drop primary key;
alter table 表名 add primary key (列名1, 列名2, 列名3);
alter table 表名 drop primary key, add primary key(列名1, 列名2, 列名3);
--------------------
drop primary key 用于清除主键，如果未设置主键，可直接用 add primary key 设置主键，如果已经设置了主键并且需要修改，则应全部删除并重新设置： drop primary key, add primary key ...

修改外键约束

alter table 表名 drop foreign key 约束名;
alter table 表名 add constraint 约束名 foreign key (列名) references 表名(主键);
alter table 表名 drop foreign key 约束名,
add constraint 约束名 foreign key (列名) references 表名(主键);
--------------------

修改唯一性约束

alter table 表名 drop index 约束名;
alter table 表名 add unique index 约束名 (列名1, 列名2, 列名3);
alter table 表名 drop index 约束名, add unique index 新约束名 (列名1, 列名2, 列名3);
--------------------
drop index 用于约束，如果未设置相应约束，可直接用 add unique index 设置约束，如果已经设置了约束并且需要修改，则应全部删除并重新设置： drop index xxx, add unique index ...

修改列属性，包括列名、数据类型、是否可为null、默认值、位置

alter table 表名 change[ column] 列名新列名数据类型 [(not)null] [default] [first] [after xxx]
--------------------
column可以省略
change可理解为重新定义列，指定待修改列名后，后面跟着新的列定义，所以总是必须指出新列名，就算不改变列名，也必须指出新列名（与旧列名相同即可）。

mysql> alter table tb_dept1 change location loc varchar(50);
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0

注意，这个语句必须同时指定新列名和新数据类型，如果数据类型不需要修改，则保持与原类型一样即可。同样，如果不需要修改列名，则列名保持与原列名相同即可（实现为modify）。

使用change命令时，应注意为列指定完整的定义，否则约束将会重定义成默认值：
mysql> show create table md\G
*************************** 1. row ***************************
Table: md
Create Table: CREATE TABLE `md` (
`mda` int(10) NOT NULL,
`mdb` int(10) NOT NULL,
`mdc` int(10) NOT NULL DEFAULT '10',
PRIMARY KEY (`mdc`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table md change mdc mdc int(10) not null;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table md\G
*************************** 1. row ***************************
Table: md
Create Table: CREATE TABLE `md` (
`mda` int(10) NOT NULL,
`mdb` int(10) NOT NULL,
`mdc` int(10) NOT NULL,
PRIMARY KEY (`mdc`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

本例中，通过以下语句重定义列mdc：
alter table md change mdc mdc int(10) not null;
结果是此列原设置"default 10"丢失了。