MySQL基础知识（二)——数据表和约束

最新推荐文章于 2024-03-05 11:58:25 发布
置顶 最新推荐文章于 2024-03-05 11:58:25 发布
阅读量541 收藏 1
点赞数
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/third_/article/details/85164504
版权

一.数据表

1.概述

数据表（或称表）是数据库最重要的组成部分之一，是其他对象的基础。

2.使用数据库

创建数据表之前要打开数据库，并使用数据库（命令：USE database_name;）

可以使用命令：SELECT  DATABASE(); 展示当前使用的数据库。

3.创建数据表

语法：CREATE TABLE [IF NOT EXISTS] table_name (column_name data_type,...)

# 创建一个简单的用户表
 CREATE TABLE IF NOT EXISTS user(
    username VARCHAR(20), 
    age TINYINT UNSIGNED
  );

4.查看数据表列表

SHOW TABLES [FROM db_name][LIKE 'pattern' | WHERE expr]

# 查看当前数据库下的表
SHOW TABLES;
# 查看mysql数据库下的表，不会切换使用的库
SHOW TABLES FROM mysql;

5.查看数据表结构

  • SHOW COLUMNS FROM tbl_name；
  • DESC  tbl_name；

6.插入数据

语法：INSERT [INTO] tbl_name [(col_name,...)] VALUES (val,...)

# 不写字段名称则必须给所有字段赋值
INSERT USER VALUES ('kim',18);
# 写字段名称可以只赋对应字段值
INSERT USER(username) VALUES ('kimtian');

7.空值与非空

  • NULL，字段值可以为空
  • NOT NULL 字段值禁止为空

8.自动编号AUTO_INCREMENT

  • 自动编号，且必须与主键组合使用
  • 默认情况下，起始值为1，每次的增量为1

       自动编号字段可以是整数或是浮点数，浮点数的小数位一定为0。

       自动编号的字段一定得是主键，但主键不一定是AUTO_INCREMENT。

二.约束

约束可以保证数据的完整性和一致性；约束分为表级约束和列级约束。

1.PRIMARY KEY主键约束

  • 每张数据表只能存在一个主键
  • 主键保证记录的唯一性
  • 主键自动为NOT NULL
# 自增主键，使用 PRIMARY KEY
CREATE TABLE IF NOT EXISTS user4(id INT(2) UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(20) UNIQUE KEY,age TINYINT UNSIGNED,salary FLOAT(9,2) UNSIGNED);
# 自增主键，直接使用 KEY也是OK的，但建议直接使用 PRIMARY KEY，可读性好，比较统一
CREATE TABLE IF NOT EXISTS user5(id INT(2) UNSIGNED AUTO_INCREMENT KEY,username VARCHAR(20) UNIQUE KEY,age TINYINT UNSIGNED,salary FLOAT(9,2) UNSIGNED);
# 非自增主键
CREATE TABLE IF NOT EXISTS user6(id INT(2) UNSIGNED PRIMARY KEY,username VARCHAR(20) UNIQUE KEY,age TINYINT UNSIGNED,salary FLOAT(9,2) UNSIGNED);

创建完成后，可以使用desc tablename;命令，查看表结构，主键是否增加成功。

如图所示可以看到主键创建成功。

  

2.UNIQUE KEY 唯一约束

  • 唯一约束可以保证记录的唯一性；
  • 唯一约束的字段可以为NULL；
  • 每张数据表可以存在多个唯一约束。
# 为username字段 设置唯一约束
CREATE TABLE IF NOT EXISTS user4(id INT(2) UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(20) UNIQUE KEY,age TINYINT UNSIGNED,salary FLOAT(9,2) UNSIGNED);

创建完成后，可以使用命令desc tablename;，查看表结构，唯一约束是否增加成功。

如图所示可以看到唯一约束创建成功。

 

设置唯一约束的元素要有唯一性不能重复。如果username重复数据库会报错：

ERROR 1062 (23000): Duplicate entry 'kimtian' for key 'username'

3.DEFAULT 默认约束 

  • 默认值；
  • 当插入记录时，如果没有明确为字段赋值，则自动赋予默认值。
# 为sex性别字段设置默认值，1表示男，2表示女，3表示保密，不传时候默认为3
CREATE TABLE tbl(id INT(2) UNSIGNED AUTO_INCREMENT PRIMARY KEY,age TINYINT UNSIGNED,sex ENUM('1','2','3') DEFAULT '3');

 向表table中插入一条数据：

# 只给年龄赋值了
insert tbl (age) VALUES (18);

我们没并没有给性别赋值。查看表数据，可以看到性别默认被填写了3。

4.NOT NULL 非空约束

# area地域字段不能为空
CREATE TABLE tbl1(id INT(2) UNSIGNED AUTO_INCREMENT PRIMARY KEY,age TINYINT UNSIGNED,sex ENUM('1','2','3') DEFAULT '3',area varchar(20) NOT NULL);

area地域字段不能为空，所以我们在插入数据时候，必须给area字段赋值，否则会报错：ERROR 1364 (HY000): Field 'area' doesn't have a default value

同样如果给area赋值NULL也会报这个错误：

5.FOREIGN KEYp 外键约束

  • 保证数据一致性，完整性；
  • 实现一对一或一对多关系。

创建外键约束的要求：

  • 父表和子表必须使用相同的存储引擎，而且禁止使用临时表；
  • 数据表的存储引擎只能为InnoDB；
  • 外建列和参照列必须具有相似的数据类型。其中数字长度或是否有符号位必须相同，而字符的长度则可以不同；
  • 外键列和参照列必须创建索引，如果外键列不存在索引的话，MySQL将会自动创建索引。

如下，创建两张表，一张省份表，一张城市表，城市表有个外键pid，参照省份表的id列。

# 省份表--参照表，也称作父表，id为参照列。
CREATE TABLE provinces(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,pname VARCHAR(20) NOT NULL);
# 城市表--子表，pid为外键列。
CREATE TABLE citys(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,cityname VARCHAR(20) NOT NULL,pid SMALLINT UNSIGNED, FOREIGN KEY (pid) REFERENCES provinces(id));

外键约束的参照条件：

CASCADE从父表删除或更新时，自动删除和更新子表中匹配的行。
SET NULL从父表删除或更新行时，设置子表中的外键列为NULL，如果使用该选项，必须保证子表外键列没有指定NOT NULL。
RESTRICT拒绝对父表的更新或删除操作。
NO ACTION标准SQL的关键字，在MySQL中与RESTRICT相同。
默认拒绝对父表的更新或删除操作。

用法如下： 

# ON DELETE CASCADE 删除时，自动删除和更新子表中匹配的行。
CREATE TABLE citys1(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,cityname VARCHAR(20) NOT NULL,pid SMALLINT UNSIGNED, FOREIGN KEY (pid) REFERENCES provinces(id) ON DELETE CASCADE);

使用命令SHOW CREATE TABLE tbl_name;

可以查看到以下信息：

（1）ENGINE=InnoDB引擎是InnoDB。

（2）KEY `pid` (`pid`)。外键列pid已经被自动创建了索引。

（3）CONSTRAINT `citys_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`)外键索引被成功创建，外键索引名称为citys_ibfk_1。

 也可以使用命令SHOW INDEX FROM tab_name;查看表中索引情况。

 

6.表级约束与列级约束

表级约束列级约束
对多个数据列建议的约束，称为表级约束。对一个数据列建立的约束，称为列级约束。
只能在列定义后生命可以在列定义时声明，也可以在列定义后声明。

很少使用。

常用。

只支持外键约束，主键约束，唯一约束。不支持非空约束 和 默认约束。

外键约束，主键约束，唯一约束，非空约束，默认约束全都支持。

 三.修改数据表

1.添加和删除列

（1）添加单列

语法：ALTER TABLE tbl_name ADD [COLUMN] col_name column_definaition [FIRST | AFTER col_name]

如果后面[FIRST | AFTER col_name]这部分省略，则新增字段默认加在所有列的后面。

#testalter1字段加在了最前面
ALTER TABLE citys ADD testalter1 varchar(20) FIRST;
#testalter2字段加在了cityname字段的后面
ALTER TABLE citys ADD testalter2 varchar(20) AFTER cityname ;
#后面省略默认加在所有列的最后面
ALTER TABLE citys ADD testalter3 varchar(20);

再查看表结构可以看到新增了3个字段，位置和我们要求的一致。

（2）添加多列

语法：ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definaition,...)

# 多列不能指定位置，默认加在所有列的最后面
ALTER TABLE citys ADD (testalter4 varchar(20),testalter5 varchar(20)  );

（3）删除列

语法：ALTER TABLE tbl_name DROP [COLUMN] col_name 

# 删除单列
ALTER TABLE citys DROP testalter1;
# 删除多列
ALTER TABLE citys DROP testalter2, DROP testalter3, DROP testalter4;
# 删除某列，并增加num列
ALTER TABLE citys DROP testalter5 , ADD num int(20) AFTER cityname;

2.添加和删除主键约束

（1）添加主键约束

ALTER TABLE tbl_name ADD [CONSTARAINT [symbol]] PRIMARY KEY [index_type] (index_col_name)

# [CONSTARAINT [symbol]] 表示约束的名称
# [index_type]表示索引的类型，有hash树和B树索引，现在默认是B树 
# 创建user表，未给任何约束
CREATE TABLE IF NOT EXISTS user(id INT(2) UNSIGNED ,username VARCHAR(20),age TINYINT UNSIGNED,salary FLOAT(9,2) UNSIGNED);
# 添加主键约束
ALTER TABLE user ADD CONSTRAINT pk_user_id PRIMARY KEY(id);

使用命令desc user;查看表结构，可以看到id字段加了PRIMARY KEY.

（2）删除主键约束

语法：ALTER TABLE tbl_name DROP PRIMARY KEY

# 删除主键约束，并不用指定名称，因为任何一个数据表有且只有一个主键
ALTER TABLE user DROP PRIMARY KEY;

再次使用命令desc user;查看表结构，可以看到id字段的主键约束已经被删除。

3.添加和删除唯一约束

（1）添加唯一约束

ALTER TABLE tbl_name ADD [CONSTARAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type](index_col_name,...)

# 添加唯一约束，唯一约束可以有多个
ALTER TABLE user ADD UNIQUE(username);

使用命令desc user;查看表结构，可以看到username字段加了UNIQUE KEY. 

（2）删除唯一约束

ALTER TABLE tbl_name DROP {INDEX | KEY} index_name

# 删除唯一约束，唯一约束要指定名称，因为可能有多个唯一约束
ALTER TABLE user DROP INDEX username;

4.添加和删除外键约束

（1）添加外键约束

ALTER TABLE tbl_name ADD [CONSTARAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

# 创建一个城市表
CREATE TABLE citys(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,cityname VARCHAR(20) NOT NULL,pid SMALLINT UNSIGNED);
# 添加外键约束
ALTER TABLE citys ADD FOREIGN KEY(pid) REFERENCES provinces(id);

使用命令SHOW CREATE TABLE citys ;可以看到加上了外键约束。

（2）删除外键约束

ALTER TABLE tbl_name DROP  FOREIGN KEY fk_symbol

# 删除外键约束
ALTER TABLE citys DROP FOREIGN KEY citys_ibfk_1;
# 删除索引（外键约束删除后MySQL自动加的索引依然存在，需要的话可以继续删除索引）
ALTER TABLE citys DROP INDEX pid;

5.添加和删除默认约束

（1）添加默认约束

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

# 添加默认约束，给age字段默认值为15
ALTER TABLE user ALTER age SET DEFAULT 15;

使用命令desc user;查看表结构，可以看到age字段加了Default默认值15。

（2）删除默认约束

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

# 删除默认约束
ALTER TABLE user ALTER age DROP DEFAULT;

使用命令desc user;查看表结构，可以看到age字段的默认值被删除。

6.修改列定义

（1）修改列定义

ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definaition [FIRST | AFTER col_name]

# 列名字不存在问题，但定义类型和顺序有问题使用MODIFY
# 修改位置，字段类型不变
ALTER TABLE user MODIFY salary FLOAT(9,2) UNSIGNED AFTER username;
# 修改类型.要注意大类型改到小类型有可能造成数据的丢失。
ALTER TABLE user MODIFY age INT(2) UNSIGNED AFTER id;

7.数据表和数据列更名

尽量少使用数据表更名和数据列名更名。因为创建了索引，或曾经使用过视图和存储过程的情况下，修改可能会导致某些视图和存储过程无法正常工作。

（1）修改列名称

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definaition [FIRST | AFTER col_name]

# 修改列定义和列名称
ALTER TABLE user CHANGE id user_id TINYINT(2) FIRST;

（2）修改表名称

方法一：

ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name

方法二：

RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name]

# 修改表名称--方法一
ALTER TABLE user RENAME user10;
# 修改表名称--方法二,可以多张表更名
RENAME TABLE user1 TO user11,user2 TO user12;

 

数据库之表的约束
你又来看我了，一起学习吧
07-26 536
       真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业 务逻辑角度保证数据的正确性。比如有一个字段是email，要求是唯一的。 1. 空属性 null/not null 两个值：null（默认的）和not null（不为空） 数据库默认字段基本都是字段为空，但是实际开...
MySQL——基础知识总结超详细版本（一）
weixin_45699237的博客
04-08 5320
1、数据库概述以及常见概念的理解 存储数据用文件就可以了，为什么还要弄个数据库? 文件保存数据有以下几个缺点： 文件的安全性问题 文件不利于数据查询和管理 文件不利于存储海量数据 文件在程序中控制不方便 内存中的数据：断电后不保留 硬盘中的数据：持久化后的数据 需求出现：开发使用软件程序时，需要进行硬盘数据的管理 前提：硬盘中数据的管理要比内存中数据的管理复杂 导致：如果让程序员们完全自由地管理硬盘中的数据变得困难 所以：数据库管理软件就诞生了,由这些软件进行科学地、高效地管理硬盘中
数据表约束
qq_42309685的博客
10-20 124
表约束 对表中的数据进行限定：保证数据的正确性、有效性和完整性。 分类 主键约束：primary key 非空约束：not null 唯一约束：unique 外键约束：foreign key 非空约束：not null 写法：create table stu(id int, name varchar(20) not null); 删除非空约束：alter table stu modidy name varchar(20); 唯一约束：unique(值不能重复)
数据表的约束
weixin_44819178的博客
03-14 6943
数据表的约束 为防止错误的数据被插入到数据表，MySQL中定义了一些维护数据库完整性的规则；这些规则常称为表的约束。常见约束如下： 约束条件 说明 PRIMARY KEY 主键约束，用于唯一标识对应的记录 FOREIGE KEY 外键约束 NOT NULL 非空约束 UNIQUE 唯一性约束 DEFAULT 默认值约束，用于设置字段的默认值 以上五种约束条件针对表中字段进行限制从而保证数据表中数据的正确性和唯一性。换句话说，表的约束实...
MySQL（四）—— 数据表的约束（外键约束，主键约束等）
weixin_52148548的博客
03-24 3623
文章目录数据表的约束主键约束 数据表的约束 为防止错误的数据被插入到数据表，MySQL中定义了一些维护数据库完整性的规则；这些规则常称为表的约束。常见约束如下： 以上五种约束条件针对表中字段进行限制从而保证数据表中数据的正确性和唯一性。换句话说，表的约束实际上就是表中数据的限制条件。 下面对这五种约束进行详细介绍： 主键约束 主键约束（primary key）：每一行记录的唯一标识。 被标识为主键的数据在表中是唯一的且不为空。 在创建数据表时设置主键约束，既可以为表中的一个字段设置主键，也可以为表中多个字
(第四章)数据表的约束
LuiChun
05-02 552
数据表约束——设置非空和默认值 实体完整性：主属性值不能为null且不能有相同值 参照完整性：两个表的主键和外键的数据应对应一致 禁止从表中插入包含主表中不存在的关键字的数据行。 禁止删除从表中对应记录的主表记录。 自定义完整性：针对某个特定关系数据库的约束条件 完整性约束关键字 非空 含义 not null 非空 约束字段的值不能为空 default 默认值 设置字段的默认值 unique key(uk) 唯一 约束字段的值唯一 auto_increment 自增 约束字段的值
MySQL——数据库表.rar
08-04
创建表时，我们需要定义列名（字段名）和数据类型，例如INT、VARCHAR、DATE等，以及约束条件如主键、外键、唯一性约束等。 2. **数据类型**： MySQL支持多种数据类型，包括整数类型（TINYINT、SMALLINT、MEDIUMINT...
MySQL学习Day26——事务基础知识
最新发布
m0_50460160的博客
03-05 2069
保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式，当在一个事务中执行多个操作时，要么所有的事务都被提交(commit),这些修改永久的保存下来,要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。如果事务执行了一部分而变为失败的状态，那么就需要把已经修改的事务中的操作还原到事务执行前的状态。隔离性是指一个事务的执行不能被其他事务干扰，也就是一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰。
MySQL基础(二)——约束以及数据表操作
theScoreONE的博客
03-30 336
《一》、约束一、foreign key 外键约束 ：   保持数据一致性，完整性，一对一或一对多关系 1.父表和子表(具有PRIMARY KEY 外键列的表)必须使用相同的引擎存储，而且禁止使用临时表 2.数据表的存储引擎只能为InnoDB 3.外键列和参照列额必须具有相似的数据类型。其中数字的长度或是否有符号必须相同；而字符型下的长度则可以不同 4.外键列和参照列必须创建索引。如果外键不
面试准备——MySql基础篇
12-15
MySQL是世界上最流行的关系型数据库管理系统之一，对于面试者来说，掌握其基础知识至关重要。本文将深入讲解MySQL的基础概念，包括客户端连接、SQL语句、数据库事务以及索引。 首先，配置MySQL环境变量后，在Mac...
MySQL学习（4） - 数据表的约束
qq_38870503的博客
12-02 177
参考：https://blog.csdn.net/baidu_41388533/article/details/108127513 约束的作用 对表中的数据进行限制，保证数据的正确性、有效性和完整性。 一个表如果添加了约束，不正确的数据将无法插入到表中。 约束在创建表的时候添加比较合适。 约束种类 主键 作用 用来唯一标识数据库中的每一条记录 特征 通常不用业务字段作为主键，单独给每张表设计一个 id 的字段，把 id 作为主键。 主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含
SQLServer数据库约束(1)_表内约束(单表的五类约束)_附自增属性以及全局唯一性标识符类型作为主键
( ゜- ゜)つロ 乾杯~
02-27 1683
非空约束： --建表时设置 if exists(select name from sysobjects where name='tablename' and xtype='u') drop table tablename go create table tablename ( rowname1 int not null, rowname2 int, ) go --此类约束不适合在建表后追加
MySQL数据库中数据表的约束条件
m0_60981735的博客
03-31 7417
MySQL数据库中数据表的约束条件
MySQL中修改数据表(增删约束)
aaa333qwe的博客
07-19 354
1、添加主键约束 创建数据表： mysql> CREATE TABLE users2(     -> username VARCHAR(10) NOT NULL,     -> pid SMALLINT UNSIGNED); Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE users2 ADD id smallint
MySQL 表约束超详细
热门推荐
qq_57570052的博客
08-07 1万+
在MySQL里，“约束”指的是对表中数据的一种限制约束，它能够确保数据库中数据的准确性和有效性比如有的数据是必填项，就像身份认证的时候，或者填注册信息的时候，手机号身份证这种就不能空着，所以就有了非空约束；又有的数据比如用户的唯一id，不能跟其他人的一样，所以就需要使用唯一约束等等。在MySQL中主要有6种约束：主键约束、外键约束、唯一约束、检查约束、非空约束和默认值约束。在建立数据表的时候，一般情况下，为了方便更快地查找表中的记录，都会要求在表中设置一个“主键”。
Mysql基础（2）--数据表的查询和约束
учёба
08-23 213
DQL表数据的查询 条件查询 -- 条件查询 SELECT * FROM product WHERE stock > 20; SELECT * FROM product WHERE NAME='冰袖'; SELECT * FROM product WHERE price >= 400 AND price <= 6000; SELECT * FROM product WHERE price BETWEEN 4000 AND 6000; SELECT * FROM product WH
MySQL教程之二——约束
Dev-L的博客
06-15 377
本节重点 not null与default unique primary key auto_increment foreign key 约束的概念 约束条件与数据类型的宽度一样，都是可选参数 作用：用于保证数据的完整性和一致性 主要分为： PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录 FOREIGN KEY (FK) 标识该字段为该表...
MySQL扩展知识3——约束维护（添加、修改、删除）
lihaoen666的博客
04-20 153
添加、修改 基本语法 ALTER TABLE tbl_name ADD 字段类型（字段名） // 添加 MODIFY 字段名 字段类型 // 修改（重新定义字段） 例子 1）客户号设为主键 alter table customer2 add primary key(customer_id); 2）客户姓名不能为空 alt...
【MySQL】表约束和表设计
weixin_51143430的博客
03-17 2529
MySQL的CRUD操作数据库约束表约束非空约束 NOT NULL唯一约束 UNIQUE主键约束PRIMARY KEFOREIGN KEYCHECK三级约束类型 数据库约束 表约束 表约束是在创建表的时候，设计一些表的约束条件,用来保证数据的合法性和数据的正确性。 常见表约束： 约束 说明 NOT NULL ⾮空约束，指示某列不能存储 NULL 值 UNIQUE 唯⼀约束，保证某列的每⾏必须有唯⼀的值 PRIMARY KEY 主键约束，确保某列（或两个列多个列的结合）有唯⼀标识，有助
【MySQL基础】MySQL表的七大约束
小杨的博客
01-07 5567
MySQL表的七大约束大详解，带你掌握MySQL七大约束，即默认约束，非空约束，唯一约束，主键约束，自增约束，检索约束，外键约束。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值