实验3-表定义语句 实验题目： 表定义语句 实验目的： 掌握使用SQL语句和可视化软件创建表、修改表结构、删除表 实验要求： 掌握表的创建、修改和删除操作 实验要求： 掌握表的创建、修改和删除操作 实验设计： （1）利用SQL命令创建名为’’student_sc_2”的数据库 （2）在数据库student_sc_2中使用SQL语句创建4个如果先前不存在的简单表：院系表、专业表、教师表、班级表、学生表、课程信息表、教学任务表、成绩表。对相应关系模型设计字段类型（长度），以及创建主键约束、唯一性约束、外键约束、自增约束、空和非空约束、注释。下划线代表主键 院系表（department）：（dept_id（院系ID）、dept_name（院系名称）） 专业表（major）：（major_id（专业ID）、dept_id（院系ID，添加外键）、major_name（专业名称））引擎默认innoDB、字符集utf8 教师表（teacher）：（teacher_id（教师ID）、teacher_name（教师姓名）、teacher_sex(性别，缺省值为女)、phone（电话）、positional（职称）） 班级表（class）：(class_id（班级ID）、major_id（专业id、添加普通索引）、class_name（班级名称）、teacher_id（辅导员id）、total（总人数）） （3）在数据库student_sc_2中利用可视化软件（navicat）创建4个如果先前不存在的简单表：学生表、课程信息表、教学任务表、成绩表。对相应关系模型设计字段类型（长度），以及创建主键约束、唯一性约束、自增约束、空和非空约束、注释。下划线代表主键 学生表（student）：（student_id（学生ID）、class_id（班级ID）、student_name（学生姓名）、student_sex(性别，缺省值为男)、birthday（出生日期）、identity_card(身份证号)） 课程信息表（course_info）：（course_id（课程ID）、course_name（课程名称）、dept_id（所属院系ID）、major_id（所属专业ID） 教学任务表（assignment）：（assignment_id（任务ID）、teacher_id（分配教师ID）、assignment_name（任务名称）） 成绩表（score）：（score_id（成绩ID）、student_id（学生ID）、course_id（课程ID）、assignment_id（任务ID）、score（分数）） （4）使用SQL语句完成以下内容 score表添加字段credit（学分）字段，字段类型自定义 alter table 表名 add [column] 新字段名 字段类型 [字段属性列表]； alter table score add credit decimal(5,2) comment ‘学分’ 删除teacher表中phone字段 alter table 表名 drop [column] 字段名； 添加clazz表中total字段默认值为0; alter table 表名 alter [column] 字段名 set default 默认值； 删除teacher表teacher_sex字段默认值; alter table 表名 alter [column] 字段名 drop default； 修改course_info表选项引擎为InnoDB、字符集为utf8mb4 添加student表中student_sex普通索引 alter table 表名 add index [索引名] (字段名1[，字段名2,...])； alter table worker add index indexname (salary); 添加student表中student_id主键索引(约束)： alter table 表名 add primary key (字段名1[，字段名2,...])； 添加student表中identity_card唯一索引(约束); alter table 表名 add unique index(字段名1[，字段名2,...])； 删除class表中的dept_id字段的普通索引； alter table 表名 drop index 索引名； 删除score成绩表

<think>我们首先需要明确用户的需求：学习如何使用SQL语句和可视化工具Navicat进行数据库表的创建、修改和删除操作，包括字段类型设计、约束设置以及索引管理。根据引用[1]和引用[2]的内容，我们可以得到一些关于SQL语句设置约束的示例。例如，引用[1]展示了如何添加外键约束，引用[2]展示了如何设置主键自增和默认值约束。接下来，我们将分别从SQL语句和Navicat可视化操作两个方面来讲解。一、使用SQL语句操作数据库表1.创建表（CREATETABLE）创建表时需要指定字段名、字段类型以及各种约束。示例：创建一个名为`user`的表，包含以下字段：userId:主键，整数，自增username:字符串，唯一password:字符串，非空createTime:日期时间，默认值为当前时间email:字符串，可空SQL语句如下：```sqlCREATETABLEuser(userIdINTPRIMARYKEYAUTO_INCREMENT,usernameVARCHAR(50)UNIQUENOTNULL,passwordVARCHAR(50)NOTNULL,createTimeDATETIMEDEFAULTCURRENT_TIMESTAMP,emailVARCHAR(100));```再创建一个成绩表`score`，其中包含一个外键引用`user`表的`userId`：```sqlCREATETABLEscore(scoreIdINTPRIMARYKEYAUTO_INCREMENT,userIdINTNOTNULL,scoreValueINT,subjectVARCHAR(50),FOREIGNKEY(userId)REFERENCESuser(userId));```2.修改表（ALTERTABLE）(1)添加字段：向表中添加新字段```sqlALTERTABLEuserADDCOLUMNageINT;```(2)修改字段类型：修改表中已有字段的类型```sqlALTERTABLEuserMODIFYCOLUMNpasswordVARCHAR(100)NOTNULL;```(3)删除字段：删除表中的字段```sqlALTERTABLEuserDROPCOLUMNage;```(4)添加约束：例如添加主键、外键、唯一约束等-添加主键（如果表没有主键）```sqlALTERTABLEuserADDPRIMARYKEY(userId);```-添加外键（如引用[1]所示）```sqlALTERTABLEscoreADDCONSTRAINTfk_uidFOREIGNKEY(userId)REFERENCESuser(userId);```-添加唯一约束```sqlALTERTABLEuserADDUNIQUE(email);```-添加默认值约束```sqlALTERTABLEuserALTERCOLUMNcreateTimeSETDEFAULTCURRENT_TIMESTAMP;```(5)删除约束：例如删除外键约束```sqlALTERTABLEscoreDROPFOREIGNKEYfk_uid;--MySQL中删除外键约束```3.删除表（DROPTABLE）```sqlDROPTABLEscore;DROPTABLEuser;```4.索引管理(1)创建索引：可以创建普通索引和唯一索引（注意：唯一约束会自动创建唯一索引）-普通索引```sqlCREATEINDEXidx_usernameONuser(username);```-唯一索引（如果字段需要唯一性，通常使用唯一约束，但也可以显式创建唯一索引）```sqlCREATEUNIQUEINDEXidx_emailONuser(email);```(2)删除索引```sqlDROPINDEXidx_usernameONuser;```二、使用Navicat进行可视化操作1.创建表：打开Navicat，连接到数据库，右键点击“表”->“新建表”。在打开的界面中，可以添加字段，设置字段类型、长度，并在“栏位”选项卡下方设置约束（如主键、自增、非空、默认值等）。在“索引”选项卡可以添加索引（普通索引或唯一索引）。在“外键”选项卡可以添加外键约束。2.修改表：右键点击表名，选择“设计表”，即可进入类似创建表的界面，进行字段的增删改，约束的修改等。3.删除表：右键点击表名，选择“删除表”。4.管理索引：在表设计界面，切换到“索引”选项卡，可以添加或删除索引。添加索引时，可以设置索引名、字段、索引类型（如BTREE）和索引种类（普通索引、唯一索引等）。注意：不同的数据库管理系统（如MySQL、PostgreSQL等）在语法和功能上可能有细微差别，以上示例以MySQL为例。三、补充说明1.字段类型：常见的有整数类型（INT,BIGINT等）、浮点类型（FLOAT,DOUBLE）、字符串类型（VARCHAR,CHAR）、日期时间类型（DATE,DATETIME,TIMESTAMP）、文本类型（TEXT）等。2.约束：-主键（PRIMARYKEY）：唯一标识，非空-外键（FOREIGNKEY）：引用其他表的主键或唯一键-唯一（UNIQUE）：字段值必须唯一-非空（NOTNULL）：字段值不能为空-自增（AUTO_INCREMENT）：通常用于整数主键，自动递增（MySQL中使用，其他数据库如PostgreSQL使用SERIAL类型）-默认值（DEFAULT）：当插入数据时未指定该字段值，则使用默认值3.索引：用于加快查询速度。主键和唯一约束会自动创建索引。普通索引需要手动创建，但注意索引会占用空间并影响插入、更新、删除的速度。四、示例操作根据引用[2]的示例，创建表Ta1：```sqlCREATETABLETa1(PidINTPRIMARYKEYAUTO_INCREMENT,NameVARCHAR(50)DEFAULT'Unknown',InputDateDATE,CONSTRAINTchk_dateCHECK(InputDate<=CURDATE())--MySQL中CHECK约束在8.0.16之前可能被忽略，但语法支持);```注意：在MySQL中，虽然可以写CHECK约束，但在8.0.16之前不会强制执行（但不会报错）。从8.0.16开始支持CHECK约束。如果使用其他数据库如PostgreSQL，则完全支持CHECK约束。插入数据：```sqlINSERTINTOTa1(Name,InputDate)VALUES('John','2023-01-01');```使用Navicat操作时，可以在表设计器的“检查”选项卡添加CHECK约束（如果数据库支持）。最后，建议用户在实际操作中结合具体的数据库系统（如MySQL、PostgreSQL、SQLServer等）的文档，因为不同数据库的语法和特性可能有差异。相关问题：1.如何选择数据库表的字段类型？2.数据库索引有哪些类型？如何根据查询需求创建合适的索引？3.在Navicat中如何设计表之间的关系图（ER图）？4.数据库表设计中有哪些常见的规范？5.如何优化数据库表的性能？</think>### SQL语句与Navicat操作指南 #### 一、创建数据库表 **SQL语句示例**： ```sql -- 创建带约束的用户表 CREATE TABLE user ( userId INT PRIMARY KEY AUTO_INCREMENT, -- 主键自增 username VARCHAR(50) UNIQUE NOT NULL, -- 唯一+非空 password VARCHAR(100) NOT NULL, email VARCHAR(100) DEFAULT 'N/A', -- 默认值 regDate DATETIME DEFAULT CURRENT_TIMESTAMP ); -- 创建带外键的成绩表 CREATE TABLE score ( scoreId INT PRIMARY KEY AUTO_INCREMENT, userId INT NOT NULL, subject VARCHAR(50), score INT CHECK(score BETWEEN 0 AND 100), -- 检查约束 FOREIGN KEY (userId) REFERENCES user(userId) -- 外键约束 ); ``` **Navicat操作**： 1. 右键数据库 → 新建表 2. 设计字段：设置类型、长度、默认值 3. 约束设置： - 主键：勾选"主键"和"自动递增" - 唯一约束：索引类型选`UNIQUE` - 外键：外键选项卡添加关联 - 检查约束：`选项`选项卡添加表达式 #### 二、修改表结构 **SQL语句示例**： ```sql -- 添加新字段 ALTER TABLE user ADD COLUMN phone VARCHAR(15); -- 修改字段类型 ALTER TABLE user MODIFY COLUMN email VARCHAR(150); -- 添加外键约束（引用[1]） ALTER TABLE score ADD CONSTRAINT fk_user FOREIGN KEY (userId) REFERENCES user(userId); -- 删除字段 ALTER TABLE user DROP COLUMN phone; ``` **Navicat操作**： 1. 右键表 → 设计表 2. 字段管理：添加/删除列，修改属性 3. 外键管理：外键选项卡编辑关联 4. 约束管理：选项选项卡编辑检查约束 #### 三、索引管理 **SQL语句示例**： ```sql -- 创建普通索引 CREATE INDEX idx_username ON user(username); -- 创建唯一索引 CREATE UNIQUE INDEX uni_email ON user(email); -- 删除索引 DROP INDEX idx_username ON user; ``` **Navicat操作**： 1. 设计表 → 索引选项卡 2. 添加索引：设置名称、字段、类型（INDEX/UNIQUE） 3. 删除索引：右键索引 → 删除 #### 四、删除表 ```sql -- 删除表（谨慎操作！） DROP TABLE IF EXISTS score; DROP TABLE user; ``` **Navicat操作**：右键表 → 删除表 ### 关键概念说明 1. **字段类型**： - 整数：`INT`, `BIGINT` - 字符串：`VARCHAR(长度)`, `TEXT` - 日期：`DATE`, `DATETIME` - 浮点数：`DECIMAL(总位数,小数位)` 2. **约束类型**： - 主键：`PRIMARY KEY`（唯一标识） - 外键：`FOREIGN KEY`（引用其他表主键）[^1] - 唯一：`UNIQUE`（列值不可重复） - 非空：`NOT NULL`（强制必须有值） - 默认值：`DEFAULT '值'` - 检查：`CHECK(条件)`（如日期约束[^2]） 3. **索引优化**： - 普通索引：加速查询，允许重复值 - 唯一索引：强制值唯一性 - 主键自动创建聚簇索引 > 提示：Navicat的`SQL预览`功能可实时查看可视化操作生成的SQL语句，是学习SQL的好工具。