C++ 数据库MySQL 学习笔记(1) - 数据库操作

俊诚_CCS

于 2024-06-07 23:13:59 发布

阅读量552

点赞数 9

分类专栏： C++ 学习笔记文章标签：数据库 c++ mysql

本文链接：https://blog.csdn.net/2201_75704074/article/details/139536883

版权

C++ 学习笔记专栏收录该内容

35 篇文章 10 订阅

订阅专栏

C++ 数据库MySQL 学习笔记(1) - 数据库操作

如何登录MySQL

控制台登录MySQL 命令

mysql -h 127.0.0.1 -uroot -p(输入密码)

关系模型

关系数据库是建立在关系模型上的。而关系模型本质上就是若干个存储数据的二维表，可以把它们看作很多Excel表。

在这里插入图片描述

表的每一行称为记录（Record），记录是一个逻辑意义上的数据
表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段
字段定义了数据类型（整型、浮点型、字符串、日期等），以及是否允许为NULL。注意NULL表示字段数据不存在。一个整型字段如果为NULL不表示它的值为0，同样的，一个字符串型字段为NULL也不表示它的值为空串

通常情况下，字段应该避免允许为NULL。不允许为NULL可以简化查询条件，加快查询速度，也利于应用程序读取数据后无需判断是否为NULL。

数据库的操作

像文件一样，我们可以随时对数据库执行如下操作:

创建数据库
查看数据库
选择数据库
删除数据库

创建数据库

创建数据库是指在数据库系统中划分一块空间，用来存储相应的数据，这是进行表操作的基础，也是进行数据库管理的基础。

在MySQL中，创建数据库之前，可以使用SHOW语句来显示当前已经存在的数据库，具体SQL语句为 show databases
创建数据库的SQL语句为 create database *databases_name*;(其中参数database_name表示所要创建的数据库的名称)

查看数据库

查看数据库在上面已提及，SQL语句为 show databases;

选择数据库

在数据库管理系统中一般会存在许多数据库。在操作数据库对象之前，需要先选择一个数据库。

在MySQL中选择数据库可以通过SQL语句USE来实现，其语法形式为 use *databases_name* database_name参数表示所要选择的数据库名字

在选择具体的数据库之前，首先要查看数据库管理系统中已经存在的数据库，然后才能从这些已经存在的数据库中进行选择。如果选择一个不存在的数据库，就会出现错误

删除数据库

在删除数据库之前，首先需要确定所操作的数据库对象已经存在。在MySQL中删除数据库可以通过SQL语句DROP DATABASE来实现, 具体语法形式为drop database *database_name* database_name参数表示所要删除的数据库名字

注:删除数据库时mysql 不会确认，千万要小心！！！

数据表操作

在MySQL数据库中，表是一种很重要的数据库对象，是组成数据库的基本元素，由若干个字段组成，主要用来实现存储数据记录。表的操作包含创建表、查询表、修改表和删除表，这些操作是数据库对象的表管理中最基本也是最重要的操作。

在这里插入图片描述

mysql> create database school;   #创建数据库school

mysql> use school;   #选择数据库school 
mysql> create table class(class_id int, class_name varchar(128), class_teacher varchar(64));         #创建表class
mysql> insert into class values (101, '六年级一班', '马老师');  #往表中插入101班记录
mysql>insert into class values (102, '六年级二班', '潘老师');   #往表中插入102班记录
mysql>select * from class;  #查询表class 中的全部记录
mysql>select * from class where class_id=101;  #查询表class 中的全部记录

表的创建

基本语法:

**CREATE TABLE** <表名> ([表定义选项])[表选项][分区选项];

其中，[表定义选项]的格式为：

<列名1> <类型1> [,…] <列名n> <类型n>

CREATE TABLE 命令语法比较多，其主要是由表创建定义（create-definition）、表选项（table-options）和分区选项（partition-options）所组成的。

mysql> use school;   #选择数据库school 
mysql> create table class1(class_id int, class_name varchar(128), class_teacher varchar(64));         #创建表class

CREATE TABLE 语句的主要语法及使用说明如下:

- CREATE TABLE：用于创建给定名称的表，必须拥有表CREATE的权限。
<表名>：指定要创建表的名称，表名称可以被指定为 db_name.tbl_name，以便在特定的数据库中创建表。无论是否有当前数据库，都可以通过这种方式创建。在当前数据库中创建表时，可以省略 db-name。有些数据库可以使用加引号的识别名，例如，‘mydb’.‘mytbl’ 是合法的，但 mysql 是不合法

mysql> create table school.class2(class_id int, class_name varchar(128), class_teacher varchar(64));         #创建表class2 
mysql> create table ‘school’.’class3’(class_id int, class_name varchar(128), class_teacher varchar(64));     #Error! 会创建失败

<表定义选项>：表创建定义，由列名（col_name）、列的定义（column_definition, 或者叫类型定义）以及可能的空值说明、完整性约束或表索引组成,如下图所示

mysql> use school;   #选择数据库school 
mysql> create table class4(class_id int PRIMARY KEY, class_name varchar(128) NOT NULL, class_teacher varchar(64) UNIQUE);         #创建表class3

注意:

默认的情况是，表被创建到当前的数据库中。若表已存在、没有当前数据库或者数据库不存在，则会出现错误
提示：使用 CREATE TABLE 创建表时，必须注意以下信息:
- 要创建的表的名称不区分大小写，不能使用SQL语言中的关键字，如DROP、ALTER、INSERT等
- 数据表中每个列（字段）的名称和数据类型，如果创建多个列，要用逗号隔开

列名

1）采用26字母和0-9的自然数加上下互相 ‘’ 组成，命名简洁明确，多个单词用下划线 ‘’ 隔开

2）全部小写命名，尽量避免出现大写

3）字段必须填写描述信息

4）禁止使用数据库关键字

5）字段名称一般采用名词或动宾短语

6）采用字段的名字必须是易于理解，一般不超过三个英文单词

7）在命名表的列时，不要重复表的名称（如：在 user 表中，出现 user_name 字段）

8）字段命名使用完整名称

列的类型定义

整形类型

整数类型是数据库中最基本的数据类型。标准SQL中支持INTEGER和SMALLINT这两种数据类型。MySQL数据库除了支持这两种类型以外，还扩展支持了TINYINT、MEDIUMINT和BIGINT。下表从不同整数类型的字节数、取值范围等方面进行对比。

mysql> use school;   #选择数据库school 
mysql> create table class5(class_id int , class_name varchar(128), class_teacher varchar(64) );         #创建表class5

整数类型的附带属性:

类型名称后面的小括号 指定显示宽度（并不是该类型占用字节数）。如果不显示指定宽度则默认为tinyint(3)、 smallint(5)、 mediumint(8)、 int(11) 和 bigint(20)。一般配合 zerofill 使用，顾名思义，zerofill 就是用“0”填充的意思，也就是在数字位数不够的空间用字符“0”填满。

mysql> use school;   #选择数据库school 
mysql> create table class6(class_id integer(5) zerofill, class_name varchar(128), class_teacher varchar(64) );         #创建表class6

UNSIGNED（无符号） 如果需要在字段里面保存非负数或者需要较大的上限值时，可以用此选项，它的取值范围是正常值的下限取 0，上限取原值的 2 倍，例如，tinyint 有符号范围是-128～+127，而无符号范围是 0～255。如果一个列指定为 zerofill，则 MySQL 自动为该列添加 UNSIGNED 属性。
```
mysql> use school;   #选择数据库school 
mysql> create table class6(id integer unsigned , name varchar(128), teacher varchar(64) );         #创建表class6 
mysql> create table class7(id integer zerofill , name varchar(128), teacher varchar(64) );         #创建表class7, id类型为 int unsigned
```
AUTO_INCREMENT 在需要产生唯一标识符或顺序值时，可利用此属性，这个属性只用于整数类型。AUTO_INCREMENT 值一般从 1 开始，每行增加 1。一个表中最多只能有一个 AUTO_INCREMENT列。对于任何想要使用 AUTO_INCREMENT 的列，应该定义为 NOT NULL，并定义为 PRIMARY KEY 或定义为 UNIQUE 键。例如，可按下列任何一种方式定义 AUTO_INCREMENT 列：

mysql> use school;   #选择数据库school 
mysql> create table class8(id integer auto_increment PRIMARY KEY , name varchar(128), teacher varchar(64) );         #创建表class8， id 具有自增长属性
mysql> create table class9(id integer auto_increment UNIQUE , name varchar(128), teacher varchar(64) );         #创建表class9， id 具有自增长属性
mysql> insert into class6 (name, teacher) values ('Form 5 Izora', 'Jack'); #如果要让id自适应不断+1 的话,若需要调夺第一个输入id的数据需要写成这样

浮点类型和定点数类型

数据表中用浮点数类型和定点数类型来表示小数。浮点数类型包括单精度浮点数（FLOAT型）和双精度浮点数（DOUBLE型）。定点数类型就是DECIMAL型。下面从这三种类型的字节数、取值范围等方面进行对比，如下表所示:

在这里插入图片描述

三种类型的区别:

float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值，float和double都是浮点型，而decimal是定点型；

mysql> use school;   #选择数据库school 
mysql> create table class10 (f1 float, do1 double, de1 decimal);  #创建表class10

MySQL 浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度，如float(7,4)的可显示为-999.9999，MySQL保存值时进行四舍五入，如果插入999.00009，则结果为999.0001。对于decimal,M是最大位数（精度），范围是1到65。可不指定，默认值是10。D是小数点右边的位数（小数位）。范围是0到30，并且不能大于M，可不指定，默认值是0。
FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0,即(10, 0)。

· 忠告当我们需要存储小数，并且有精度要求，比如存储金额时，通常会考虑使用DECIMAL字段类型!!!

日期和时间类型

日期与时间类型是为了方便在数据库中存储日期和时间而设计的，数据库有多种表示日期和时间的数据类型。其中，YEAR类型表示年，DATE类型表示日期，TIME类型表示时间，DATETIME和TIMESTAMP表示日期和时间。下面从这5种日期与时间类型的字节数、取值范围和零值等方面进行对比，如下表所示。

在这里插入图片描述

每种日期与时间类型都有一个有效范围。如果插入的值超过了这个范围，系统就会报错，并将零值插入到数据库中。不同的日期与时间类型均有不同的零值.

插入日期时间时，日期年月日和时间时分秒可以使用 “: - _ / ”中的任意字符来分隔,如果单独插入时间，则会报错！！！

mysql> use test;   #选择数据库test 
mysql> create table date_example (e_date date, e_datetime datetime, e_timestamp timestamp, e_time time, e_year year);  #创建表dt_example
mysql> insert into date_example values('2020-5-9', '2020-5-9 15:01:00', '2020-05-09 15:01:00', '15:56:01', 2011);
mysql> insert into date_example values('2020_5/9', '2020:5-9 15/01-00', '2020:05/09 15-01_00',  '15:56:01', '2011');   #效果同上面插入语句

也可以通过mysql 本身提供的时间函数获取：

CURDATE() - 获得当前的DATE, 可直接插入DATE 类型中.

NOW() - 获得当前的DATETIME, 可直接插入DATETIME 和TIMESTAMP类型中.

TIME() - 获取参数给定时间串中的时分秒，可直接插入TIME 类型中.

YEAR() - 获取参数给定时间串中的年份，可直接插入YEAR类型中.

MONTH() 、DAY()、HOUR()、MINUTE()、SECOND() 获取参数给定时间串中的月、日、时、分、秒值.

mysql> use test;   #选择数据库test 
mysql> insert into date_example values(CURDATE(), NOW(), NOW(), time(NOW()), YEAR(NOW()));

字符串类型

CHAR和 VARCHAR类型

其中，“字符串类型”参数指定了数据类型为CHAR类型还是VARCHAR类型；M参数指定了该字符串的最大长度为M。例如，CHAR(4)就是数据类型为CHAR类型，其最大长度为4。

CHAR类型的长度是固定的，在创建表时就指定了。其长度可以是0~255的任意值。例如，CHAR(100)就是指定CHAR类型的长度为100。CHAR 存储值时，它们会用空格右填充到指定的长度。

VARCHAR类型的长度是可变的，在创建表时指定了最大长度。定义时，其最大值可以取0~65535之间的任意值。指定VARCHAR类型的最大值以后，其长度可以在0到最大长度之间。例如，VARCHAR(100)的最大长度是100，但是不是每条记录都要占用100个字节，而是在这个最大值范围内使用多少就分配多少。VARCHAR类型实际占用的空间为字符串的实际长度加1或2，这样即可有效节约系统的空间。

在这里插入图片描述

mysql> use test;   #选择数据库test 
mysql> create table char_example(e_char char(5), v_char varchar(5)); #创建数据库表
mysql> insert into char_example values('12345','12345');  #正常插入数据
mysql> insert into char_example values('1 2  ','1 2  ');   #char类型会屏蔽后面隐藏的空格，varchar 不会
mysql>select concat('(',e_char, ')'), concat('(',v_char, ')')  from char_example ; #让char 后面屏蔽的空格原型毕露

实战建议:

char一定会使用指定的空间，varchar是根据数据来定空间
char的数据查询效率比varchar高：varchar是需要通过后面的记录数来计算
如果确定数据一定是占指定长度，那么使用char类型；
如果不确定数据到底有多少，那么使用varchar类型；
如果数据长度超过255个字符而在65535之内，直接使用varchar
如果字符串尾部要保留空格，必须选择varchar

TEXT类型

TEXT类型是一种特殊的字符串类型，包括TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT，其长度和存储空间的对比下表所示:

在这里插入图片描述

各种TEXT类型的区别在于允许的长度和存储空间不同。因此，在这几种TEXT类型中，根据需求选取既能满足需要又节省空间的类型即可。

特别注意:

以上各类型无须指定长度！
允许的长度是指实际存储的字节数，而不是实际的字符个数，比如假设一个中文字符占两个字节,那么TEXT 类型可存储 65535/2 = 32767 个中文字符，而varchar(100)可存储100个中文字符，实际占200个字节，但varchar(65535) 并不能存储65535个中文字符，因为已超出表达范围.

mysql> use test;   #选择数据库test 
mysql> create table text_example(e_text tinytext, v_char varchar(255)); #创建数据库表，e_text 可存储255个字节，v_char可存储255个字符   
mysql> insert into char_example values(90个中文字符,90个中文字符);  #插入失败，utfmb4 用3个字节表示一个中文汉字,会超出tinytext 保存范围
mysql> insert into char_example values(80个中文字符,100个中文字符);  #插入成功

实战建议:

char长度固定，即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定。超过255字节的只能用varchar或者text
varchar可变长度，可以设置最大长度；适合用在长度可变的属性。
text不设置长度，当不知道属性的最大长度时，适合用text，能用varchar的地方不用text；
如果都可以选择，按照查询速度： char最快， varchar次之，text最慢。

Enum 和 Set 类型

Enum类型

ENUM类型又称为枚举类型。在创建表时，ENUM类型的取值范围以列表的形式指定，其基本形式如下：

属性名 ENUM(‘值1’, ‘值2’, …, ‘值n’)

其中，“属性名”参数指字段的名称，“值n”参数表示列表中的第n个值。ENUM类型的值只能取列表中的一个元素。其取值列表中最多能有65535个值。如果数据值列表在255个以内，那么一个字节就够，如果超过255但是小于65535，那么系统采用两个字节保存。列表中的每个值独有一个顺序排列的编号，MySQL中存入的是这个编号，而不是列表中的值。默认编号从1开始！

Enum(‘男’,’女’,‘选择保密’) # ‘男’=>1 ‘女’=>2 ‘选择保密’=> 3

mysql> use test;   #选择数据库test 
mysql> create table enum_example (e_enum enum('男','女','选择保密') ); #创建表 
mysql> insert into enum_example values('男');  #插入记录,必须是enum 选项中的值
mysql> insert into enum_example values(1);  #插入记录可以用数值表示
mysql>select e_enum + 0  from enum_example; #查询enum 选项对应的整数值

如果ENUM类型加上了NOT NULL属性，其默认值为取值列表的第一个元素。如果不加NOT NULL属性，ENUM类型将允许插入NULL，而且NULL为默认值。

SET 类型

在创建表时，SET类型的取值范围就以列表的形式指定了，其基本形式如下：

属性名 SET(‘值1’, ‘值2’, …, ‘值n’)

其中，属性名参数指字段的名称，“值n”参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除。其基本形式与ENUM类型一样。SET类型的值可以取列表中的一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开。SET类型的值最多只能是由64个元素构成的组合。

mysql> use test;   #选择数据库test 
mysql> create table set_example (interest set('足球','追剧','篮球','撩妹') ); #创建表 
mysql> insert into set_example values( '足球,撩妹' ); #插入记录,必须是enum 选项中的值
mysql> insert into enum_example values(9);  #插入相应位效果等同，9 =>1001 选择1,4
mysql> insert into enum_example values(4);  #插入相应位效果等同，9 =>0101 选择的是1,3! 为什么?因为需要把0101反转变1010!
mysql>select interest+0 from set_example;  #以整数的方式查询
mysql>select bin(interest+0) from set_example;  #以整数的方式查询

二进制类型

二进制数据和文本数据在mysql 中的最大区别在于：

二进制类型存储原始的二进制数据(如图片，视频，exe文件等)。文本类型(TEXT)用来存储字符字符串(如由英文字符、中文字符或其它语言字符组成的字符串)。
二进制类型没有字符集，并且排序和比较基于列值字节的数值。而TEXT类型有字符集，并且根据字符集的校对规则对值进行排序和比较.

二进制类型是存储二进制数据的数据类型，包括BINARY、VARBINARY、BIT、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。二进制类型之间的对比如下表所示。

在这里插入图片描述

BINARY和VARBINARY类型

BINARY类型和VARBINARY类型都是在创建表时指定最大长度，其基本形式如下:

类型 (M)

其中，“字符串类型”参数指定数据类型为BINARY类型还是VARBINARY类型；M参数指定该二进制数的最大字节长度为M。这与CHAR类型和VARCHAR类型相似。例如，BINARY(10)就是指数据类型为BINARY类型，其最大长度为10。

BINARY类型的长度是固定的，在创建表时就指定了，不足最大长度的空间由″\0″补全。例如，BINARY(50)就是指定BINARY类型的长度为50。
VARBINARY类型的长度是可变的，在创建表时指定了最大的长度，其长度可以在0到最大长度之间，在这个最大值范围内使用多少就分配多少

mysql> use test;   #选择数据库test 
mysql> create table bin_example(e_bin  binary(5),e_varbin varbinary(5)); #创建表 
mysql> insert into bin_example values( 'ab','ab'); #插入记录,可以是普通字符串
mysql> insert into bin_example values( b'0110000101100010',b'0110000101100010'); #插入记录,可以是二进制，与上例等同
mysql> select * from bit_example ;  #以十六进制的方式显示 
mysql>insert into bin_example values('ab\0','ab\0');
mysql> select * from bin_example where e_bin='ab';  #不会显示任何的数值
mysql> select * from bin_example where e_bin='ab\0\0\0';  #这样才会显示数值

BIT类型

BIT类型在创建表时指定最大长度，其基本形式如下：

BIT（M）

其中，“M”指定该二进制数的最大存储长度为M，M的最大值为64。例如，BIT(4)就是指数据类型为BIT类型，长度为4。若字段的类型BIT(4)存储的数据是0~15，因为变成二进制之后15的值为1111，则其长度为4。如果插入的值为16，其二进制数为10000，长度为5，超过了最大长度，因此大于16的数是不能插入BIT(4)类型字段中的。

操作要点

插入数据时，使用 **b’位串’**的方式插入相应值!
查询时,可以使用 bin() 、oct() 、hex() 函数讲字段的值转成相应的二进制、八进制和十六进制.

mysql> use test;   #选择数据库test 
mysql> create table bit_example (b bit(8) ); #创建表 
mysql> insert into bit_example values( b'10110111' ); #插入记录,可以是二进制位
mysql> insert into bit_example values( 'a' ); #插入记录,可以是字符，但不能超出字节长度
mysql> insert into bit_example values( b'101 );
mysql> select bin(b) from bit_example ;  #以二进制的方式显示字段值

BLOB类型

在这里插入图片描述

以上各类型无须指定长度！
允许的长度是指实际存储的字节数，不用考虑字符编码。

实战建议:

binary长度固定，即每条数据占用等长字节空间；保存长度不超过255字节的二进制数据；
varbinary可变长度，可以设置最大长度,最大长度65535；适合用在长度可变的二进制数据;
blob不设置长度，当不知道属性的最大长度时，适合用blob，能用varbinary的地方不用blob；
如果都可以选择，按照查询速度： binary最快， varbinary次之，blob最慢。

列的完整性约束

完整性约束条件是对字段进行限制，要求用户对该属性进行的操作符合特定的要求。如果不满足完整性约束条件，数据库系统将不再执行用户的操作。MySQL中基本的完整性约束条件如表所示。下表是完整性约束条件:

在这里插入图片描述

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

主键是表的一个特殊字段，能唯一标识该表中的每条信息。主键和记录的关系，如同身份证和人的关系。主键用来标识每个记录，每个记录的主键值都不同。身份证用来表明人的身份，每个人都具有唯一的身份证号。设置表的主键是指在创建表时设置表的某个字段为该表的主键。

主键的主要目的是帮助数据库管理系统以最快的速度查找到表的某一条信息。主键必须满足的条件就是主键必须是唯一的，表中任意两条记录的主键字段的值不能相同，并且是非空值。主键可以是单一的字段，也可以是多个字段的组合。

单字段主键

CREATE TABLE tablename(
    propName propType PRIMARY KEY, ......
);

CREATE TABLE tablename(
    propName propType , ......
PRIMARY KEY(propType）
);

创建表school，设置id字段为PK约束，再查看class表信息，SQL语句如下

mysql> create database school;   #创建数据库school
mysql> use school;   #选择数据库school 
mysql> create table class(id int PRIMARY KEY, name varchar(128), teacher varchar(64)); #创建表class
mysql> desc class ;  #查询表class 的定义, describe class 效果等同
mysql> show create table class ;  #同样可以实现查询表class 的定义
mysql> insert into class VALUES(1,'一班','martin');  #插入成功
mysql> insert into class VALUES(1,'二班','rock');    #因主键约束，插入失败

Mysql 支持给主键设置名字：

Mysql 支持给主键设置名字：
CREATE TABLE tablename(
    propName propType , ......
CONSTRAINT pk_name PRIMARY KEY(propType）
);

给id字段的PK约束设置一个名字，可以执行SQL语句CONSTRAINT。创建表class:

mysql> create database school;   #创建数据库school
mysql> use school;   #选择数据库school 
mysql> create table class(id int , name varchar(128), teacher varchar(64), CONSTRAINT id_pk PRIMARY KEY (id));         #创建表class
mysql> desc class ;  #查询表class 的定义, describe class 效果等同
mysql> show create table class ;  #同样可以实现查询表class 的定义
mysql> insert into class VALUES(1,'一班','martin');  #插入成功
mysql> insert into class VALUES(1,'二班','rock');    #因主键约束，插入失败

多字段主键

主键是由多个属性组合而成时，在属性定义完之后统一设置主键。语法规则如下：

CREATE TABLE tablename(  
    propName1 propType , 
    propName2 propType , 
    ......                     
[CONSTRAINT pk_name]PRIMARY KEY(propName1, propName2)
);

多字段主键的设置

mysql> create database school;   #创建数据库school
mysql> use school;   #选择数据库school 
mysql> create table class3(id int, name varchar(128), teacher varchar(64), CONSTRAINT id_pk PRIMARY KEY(id,name));         #创建表class3,设置联合主键
mysql> desc class3 ;  #查询表class3 的定义, describe class3 效果等同
mysql> insert into class VALUES(1,'一班','martin');  #插入成功
mysql> insert into class VALUES(1,'一班','rock');    #因联合主键约束，插入失败

设置表字段的外键约束（FOREIGN KEY，FK）

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

设置外键约束的两个表之间具有父子关系，即子表中某个字段的取值范围由父表决定。例如，表示一个班级和学生关系，即每个班级有多个学生。首先应该有两个表：班级表和学生表，然后学生表有一个表示班级编号的字段no，其依赖于班级表的主键，这样字段no就是学生表的外键，通过该字段班级表和学生表建立了关系。

一个班级表：

在这里插入图片描述

每一行对应着一个班级，而一个班级对应着多个学生，所以班级表和学生表的关系就是“一对多”：

在这里插入图片描述

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

设置表中某字段的FK约束非常简单，可以在MySQL数据库管理系统中通过SQL语句FOREIGN KEY来实现，其语法形式如下：

CREATE TABLE tablename_1(  
    propName1 propType , 
    propName2 propType , 
    ......                     
[CONSTRAINT fk_name]FOREIGN KEY(propName1) REFERENCES table2_name(table2_column)
);

其中,tablename_1参数是要设置外键的表名，propName1参数是要设置外键的字段，table2_name是父表的名称，table2_column是父表中设置主键约束的字段名。

mysql> use school;   #选择数据库school 
mysql> create table class(id int PRIMARY KEY, name varchar(128), teacher varchar(64));         #创建表class 
mysql> desc class ;  #查询表class 的定义, describe class 效果等同
mysql> create table student (id int PRIMARY KEY AUTO_INCREMENT, name varchar(64) NOT NULL, class_id int, sex enum('M','F'), FOREIGN KEY(class_id) REFERENCES class(id) );  #创建表student, class_id 为表class id 字段的外键 
mysql> insert into student(name, class_id, sex) values('小红', 2, 'M');   #插入记录，主键自增长 
mysql> insert into student(name, sex) values('小军', 'M');   #插入记录，允许外键为空

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

当数据库表中的某个字段上的内容不希望设置为NULL时，可以使用NK约束进行设置。NK约束在创建数据库表时为某些字段上加上“NOT NULL”约束条件，保证所有记录中的该字段都有值。如果在用户插入的记录中该字段为空值，那么数据库管理系统会报错。

设置表中某字段的NK约束非常简单，查看帮助文档可以发现，在MySQL数据库管理系统中是通过SQL语句NOT NULL来实现的，其语法形式如下：

CREATE TABLE tablename(
    propName propType NOT NULL , ......
);

mysql> create database school;   #创建数据库school
mysql> use school;   #选择数据库school 
mysql> create table class(id int, name varchar(128) NOT NULL, teacher varchar(64));         #创建表class
mysql> desc class ;  #查询表class 的定义, describe class 效果等同 
mysql> show create table class ;  #同样可以实现查询表class 的定义

检验数据库school中的class表中字段classno是否被设置为NK约束，执行SQL语句DESCRIBE，具体如下:

在这里插入图片描述

**设置表字段唯一约束（UNIQUE，UK)

当数据库表中某个字段上的内容不允许重复时，可以使用UK约束进行设置。UK约束在创建数据库时为某些字段加上“UNIQUE”约束条件，保证所有记录中该字段上的值不重复。如果在用户插入的记录中该字段上的值与其他记录中该字段上的值重复，那么数据库管理系统会报错。

设置表中某字段的UK约束非常简单，可以在MySQL数据库管理系统中通过SQL语句UNIQUE来实现，其语法形式如下：

CREATE TABLE tablename(
    propName propType UNIQUE, ......
);

mysql> create database school;   #创建数据库school
mysql> use school;   #选择数据库school 
mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64));         #创建表class
mysql> desc class ;  #查询表class 的定义, describe class 效果等同 
mysql> show create table class ;  #同样可以实现查询表class 的定义

设置表字段值自动增加（AUTO_INCREMENT）

AUTO_INCREMENT是MySQL唯一扩展的完整性约束，当向数据库表中插入新记录时，字段上的值会自动生成唯一的ID。在具体设置AUTO_INCREMENT约束时，一个数据库表中只能有一个字段使用该约束，该字段的数据类型必须是整数类型。由于设置AUTO_INCREMENT约束后的字段会生成唯一的ID，因此该字段也经常会同时设置成PK主键。

设置表中某字段值的自动增加约束非常简单，可以在MySQL数据库管理系统中通过SQL语句AUTO_INCREMENT来实现，其语法形式如下:

CREATE TABLE tablename(
    propName propType AUTO_INCREMENT, ......
);

在上述语句中，tablename参数表示所要设置非空约束的字段名字，propName参数为属性名，propType为属性类型，propName字段要设置自动增加约束。默认情况下，字段propName的值从1开始增加，每增加一条记录，记录中该字段的值就会在前一条记录(或已存在的最大值（包括曾经存在的）)的基础上加1。


mysql> create database school;   #创建数据库school
mysql> use school;   #选择数据库school 
mysql> create table class(id int PRIMARY KEY AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64));         #创建表class,设置字段id为自增长
mysql> show create table class ;  #查询表class 的定义

注意:mysql8 中,AUTO_INCREMENT 必须设为键(主键、外键和唯一键均可)！否则会报错！

设置表字段值自动增加（AUTO_INCREMENT）

当为数据库表中插入一条新记录时，如果没有为某个字段赋值，数据库系统就会自动为这个字段插入默认值。为了达到这种效果，可通过SQL语句关键字DEFAULT来设置。

设置数据库表中某字段的默认值非常简单，可以在MySQL数据库管理系统中通过SQL语句DEFAULT来实现，其语法形式如下：

CREATE TABLE tablename(
    propName propType DEFAULT defaultvalue, ......
);

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

设置数据库表中某字段的默认值非常简单，可以在MySQL数据库管理系统中通过SQL语句DEFAULT来实现，其语法形式如下:

CREATE TABLE tablename(
    propName propType DEFAULT defaultvalue, ......
);

注意： 如果没有设置，则默认为空

调整列的完整性约束

主键PK、外键FK和唯一键UK

新增

alter table [table_name] add constraint [constraint_name] [unique key| primary key|foreign key] ([column_name])

删除

通过如下命令查询键值的约束名:

show index或keys from 表名;
执行如下命令删除:

主键:alter table 表名 drop primary key;

外键或唯一键: alter table 表名 drop index 约束名;

修改

按照上述步骤先执行删除然后再新增即可！

修改默认值****DEFAULT、自增长和非空NK

使用如下语法重新定义列即可：

alter table 表名 modify 列名类定义;

mysql> create database school;   #创建数据库school
mysql> use school;   #选择数据库school 
mysql> create table class(id int PRIMARY KEY AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64));         #创建表class,设置字段id为自增长
mysql> alter table class modify teacher varchar(64) DEFAULT ‘Martin’ NOT NULL ;  #设置默认值和非空
mysql> alter table class modify teacher varchar(64) ; #取消默认值和非空

create table test1(id int PRIMARY KEY, name varchar(64) NOT NULL);
insert into test1 values (1, 'CCS');
select * from test1;

alter table test1 drop PRIMARY KEY;
alter table test1 add PRIMARY KEY(id);
show keys from test1;

alter table test1 add UNIQUE(name);
desc test1;
show keys from test1;

alter table test1 drop index name;
desc test1;

alter table test1 drop name;
desc test1;

alter table test1 add teacher varchar(128);
desc test1;

alter table test1 modify teacher varchar(64) DEFAULT 'No teacher';
desc test1;

alter table test1 modify id int auto_increment;
desc test1;

实战练习

创建一个用户信息表，用来存储用户的如下信息：姓名，手机号码，家庭地址，个人简介，性别，年龄，身份证号。数据库表名可定义为userinfo, 同时增加一个列id 用来作为每一条记录的唯一标识，并设为主键，自加！

mysql> use test;   #选择数据库test 
mysql> create table userinfo (
 id int(11) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘主键’,
 name   varchar(64) DEFAULT NULL COMMENT ‘姓名’,
 mobile  char(11)  DEFAULT NULL COMMENT ‘手机号码’,
 address varchar(128) DEFAULT NULL COMMENT ‘居住地址’,
 description text  DEFAULT NULL COMMENT ‘个人简介-不知道具体的范围，不常更新用text’,
 sex     char(1)  DEFAULT NULL COMMENT ‘性别 - 男或女’, #也可使用enum
 age     tinyint unsigned DEFAULT 0 COMMENT ‘年龄’,
 idno    char(18) DEFAULT NULL COMENT ‘身份证号码’
);

俊诚_CCS

关注

9
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
C++ 数据库MySQL 学习笔记(1) - 数据库操作

整数类型是数据库中最基本的数据类型。标准SQL中支持INTEGER和SMALLINT这两种数据类型。MySQL数据库除了支持这两种类型以外，还扩展支持了TINYINT、MEDIUMINT和BIGINT。下表从不同整数类型的字节数、取值范围等方面进行对比。#选择数据库school#创建表class5整数类型名称后面的小括号指定显示宽度（并不是该类型占用字节数）。如果不显示指定宽度则默认为tinyint(3)、 smallint(5)、 mediumint(8)、 int(11) 和 bigint(20)
复制链接

扫一扫