大家好!我是黄啊码,上一节的知识点你了解多少了,掌握了多少了,别偷懒哦,今天我们就来掌握一下数据定义语言,说得高级点就是Data Definition Language,简称DDL。
很多同学对DDL的了解都是在于数据库的增、删、改、查,那下边我们就具体来讲讲。
1、创建删除数据库
CREATE DATABASE user; // 创建一个名为 user的数据库
DROP DATABASE user; // 删除一个名为 user的数据库
2.对数据表进行定义
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:382bc197-25e0-46b9-b0dd-3b5370f35c7e
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:7a7e6ee7-e1cb-497e-8469-662f01749bd0
CREATE TABLE table_name
比如我们在用户数据库中创建user_info这个表
CREATE TABLE user_info (
user_id int(11) NOT NULL AUTO_INCREMENT,
user_name varchar(255) NOT NULL
);
如上,我们创建了一个叫做user_info的表, 里面有两个字段,一个是 user_id ,它是 int 类型,另一个 user_name 字段是varchar(255)类型。这两个字段都不为空,且 user_id 是递增的,而其中的not null表示非空,切记语句最后要加;【千万要记住】AUTO_INCREMENT代表主键自动增长。有刚入门的同学说什么是主键?>>文章后边我会单纯讲述,别着急。
然而在实际工作中,我们肯定很少写过DDL,这里啊码用Navicat代替,它是一个数据库管理和设计工具,跨平台,支持很多种数据库管理软件,比如 MySQL、Oracle、MariaDB 等,当然你有更好、更符合自己编码的工具也是可以的,不做强制。
我们还可对user_name字段进行索引,索引类型为Unique。使用 Navicat 设置如下:
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:e9addf26-9ddb-4201-beb9-caba375b3292
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:96d5fe3c-affb-4964-aaee-4b94adc0909f
3、操作表结构
在创建表结构之后,我们还可以对表结构进行修改,虽然直接使用 Navicat进行操作,但对于刚入门的你,有必要了解如何使用 DDL 命令来完成表结构的修改。
1、添加字段,比如我在数据表中添加一个 age 字段,类型为int(11)
ALTER TABLE user_info ADD (age int(11));
2、修改字段名,将 age 字段改成user_age
ALTER TABLE user_info RENAME COLUMN age to user_age
3、修改字段的数据类型,将user_age的数据类型设置为float(3,1)
ALTER TABLE user_age MODIFY user_age float(3,1);
我看到不少教程写成这样
ALTER TABLE user_age MODIFY user_age (float(3,1));
然后执行起来就芭比Q了
4、 删除字段, 删除刚才添加的user_age字段
ALTER TABLE user_info DROP COLUMN user_age;
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:c24ce7a6-cf13-4802-bd98-ecbb5729504c
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:ba0e5cfb-0c9b-4c1c-b45c-57518f9712e9
主键约束
表中经常有一个列或多列的组合,其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键,通过它可强制表的实体完整性。当创建或更改表时可通过定义 PRIMARY KEY 约束来创建主键。一个表只能有一个 PRIMARY KEY 约束,而且 PRIMARY KEY 约束中的列不能接受空值。由于 PRIMARY KEY 约束确保唯一数据,所以经常用来定义标识列。
作用:
1)保证实体的完整性;
2)加快数据库的操作速度
3)在表中添加新记录时,DBMS会自动检查新记录的主键值,不允许该值与其他记录的主键值重复。
4)DBMS自动按主键值的顺序显示表中的记录。如果没有定义主键,则按输入记录的顺序显示表中的记录。
外键约束
确保了表与表之间引用的完整性。一个表中的外键对应另一张表的主键。外键可以是重复的,也可以为空。比如 user_id 在 user_info 表中是主键,如果你想设置一个成绩表即 user_score,就可以在 user_score 中设置 user_id 为外键,关联到 user_info 表中。
唯一性约束
唯一性约束表明了字段在表中的数值是唯一的,即使我们已经有了主键,还可以对其他字段进行唯一性约束。需要注意的是,唯一性约束和普通索引(NORMAL INDEX)之间是有区别的。唯一性约束相当于创建了一个约束和普通索引,目的是保证字段的正确性,而普通索引只是提升数据检索的速度,并不对字段的唯一性进行约束。
NOT NULL 约束。对字段定义了 NOT NULL,即表明该字段不应为空,必须有取值。
DEFAULT,表明了字段的默认值。如果在插入数据的时候,这个字段没有取值,就设置为默认值。比如我们将user中的身高 height 字段的取值默认设置为 0.00,即DEFAULT 0.00。
CHECK 约束,用来检查特定字段取值范围的有效性,CHECK 约束的结果不能为 FALSE,比如我们可以对身高 height 的数值进行 CHECK 约束,必须≥0,且<300,但在常见的数据库操作中,我们很多时候都会把这类约束放在前后端一起结合验证
数据表设计原则及三范式
第一范式(1NF)
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:8b40fb52-2f59-43d5-8e83-13d239f10600
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:4a01a666-4bcd-41be-aaf6-f0887ac77141
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:7fb185b8-f480-47cf-b14b-29018594f8c3
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:985c18f3-a1f0-48b7-bf36-6e9343d9db6d
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:32944e48-6260-47ab-9685-638179f5924c
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:5f872385-7460-43b5-b061-1d53e0c64885
第二范式(2NF)
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:5083cc28-eb45-4d9d-9dfd-126ee35d8a32
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:2eb90df4-5e00-4a65-8c5f-4a0395b35bc2
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:5f80f13a-2807-4faf-a9f6-5a44d233a08f
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:24c6a977-c2bb-4c90-a0eb-22d9542235c8
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:bbf8d7d0-a3fb-40a0-9b17-18de0b695121
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:ec9cacd3-58d5-449e-80bd-cc04b3515b12
第三范式(3NF)
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:a33e4d64-5e7d-4888-82e1-1ae853d66fb9
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:339a591b-c740-45ac-bad5-59e9f45ba0de
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:c2e067c0-222c-450f-b064-aeb67e4602d2
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:56ac98a7-2ca7-4b0e-8f5f-59c759f65d34
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:edab47f0-5b5e-4f67-856f-1e4deaa9e643
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:b650c21f-cc92-4446-b335-9b3e74e0c165
【解决办法】(1)提取学生表;(2)提取班级表;
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:ee1e2a34-2cfa-4bea-82be-ca973bb4d0e6
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:20533e95-0690-4521-86bb-504575b9bf0c
遵循范式的优缺点
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:f21c6221-75ed-4dcd-90a5-dce57659d0ad
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:a9fd3553-fa5d-495b-8ac4-a1b7ac15c8dc
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:cadc5cc4-a8b8-4d8a-9e7b-467c122bac72
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:f89670c9-d90c-4916-b61c-44d2951c3be3
冗余较小
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:ad0e06ec-1ac1-4e4d-964d-1ccb0ed2d7df
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:a3f6823e-90e6-4f7f-8bf8-9f513240b472
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:82277ec7-da5c-4745-a83c-55060c3dadac
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:054ee68c-85b1-4d4c-97de-c0dc75f653c6
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:57ba3881-3f21-4129-a168-4a2f91e80a53
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:1a8e8aa2-3258-43b4-8259-ce573f85dbd6
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:c5d48653-918d-468d-96f9-cf47c03f5d5f
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:3769fc82-d330-4266-a18e-99a013cafcbb
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:4ce69327-ae40-4ac4-b38a-33729217578c
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:b59a5427-6bbf-45a9-8d63-e248d3224dc7
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:8c7a1689-4804-419d-bab2-0bacaa24052f
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:2bb8ec14-fe18-4938-93c4-18d373a06f1f
Original: https://www.cnblogs.com/huangama/p/16506688.html
Author: 黄啊码
Title: 【黄啊码】MySQL入门—2、使用数据定义语言(DDL)操作数据库
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/562255/
转载文章受原作者版权保护。转载请注明原作者出处!