DDL：数据库定义语言

• 创建数据库
• 本文基于MySQL8所编写

CREATE：创建数据库、数据表

创建数据库

• 语法：create database databasename;
• 如：创建一个名字为recode的数据库，且编码格式为utf8格式，其代码为create database recode charset=utf8;

创建数据表

需求：创建3个表，课程表Course、选课表SC、学生表Student

• 课程表有课程号Cno、课程名Cname、学分Credit、季度Semester，Course(Cno,Cname,Credit,Semester)

• 选课表有课程号Sno、学号Cno、成绩Grade，SC(Sno,Cno,Grade)
• 学生表有学号Sno、姓名Sname、学生idSID、性别Ssex、年龄Sage、系别Sdept，Student(Sno,Sname,SID,Ssex,Sage,Sdept)

一下的3个表是我从数据库管理工具中导出的sql文件

character set utf8是将编码格式设置为utf8格式
comment '注释内容'对sql语句添加注释
char(10)、varchar(255)、tinyint(1)等表示字段的类型和长度
PRIMARY KEY ('字段1')设置‘字段1’为主键

创建课程表Course

DROP TABLE IF EXISTS `course`;--删除已经存在的表 CREATE TABLE `course` ( `Cno` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程号，主键', `Cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程名', `Credit` decimal(5, 0) NOT NULL COMMENT '学分', `Semester` tinyint(1) NOT NULL COMMENT '学期', PRIMARY KEY (`Cno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; 

创建选课表SC

DROP TABLE IF EXISTS `sc`; CREATE TABLE `sc` ( `Sno` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `Cno` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `Grade` tinyint(5) NULL DEFAULT NULL, PRIMARY KEY (`Sno`, `Cno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; 

创建学生表Student

DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `Sno` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `Sname` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `SID` char(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `Ssex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `Sage` tinyint(4) NULL DEFAULT NULL, `Sdept` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`Sno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; 

ALTER：修改数据或结构

添加字段

对sc表添加一列（一个字段）名为test_column

格式：ALTER TABLE 表名 ADD 列名 类型[(长度 )]

• ALTER TABLE sc ADD test_column int;

对sc表删除名为test_column 的列

格式：LTER TABLE 表名 DROP 列名;

• ALTER TABLE sc DROP test_column;

DROP：删除数据库或数据表

删除数据库

• 删除数据库一般需要root权限
  如删除一个名为test的数据库，其sql语句为：DROP database test;

删除数据表

• 如删除一个名为test_drop的表，其sql语句为：DROP TABLE test_drop;

TRUNCATE：截断表内容

删除表中的所有数据

• 格式：TRUNCATE tableName
• 使用TRUNCATE删除test表，其sql语句：TRUNCATE test

• 与drop的区别
  1. • drop 删除的表数据+表结构
     • truncate 删除只表数据
  2. • drop删除的速度比truncate速度慢
  3. • truncate 删除表是有记录的，而drop没有记录

COMMENT：为数据表添加备注

• 创建表时候添加注释，用数据库管理工具就可以进行查看
• 查看表Course的注释，其sql语句：show full columns from course;

以上是数据库定义语言的基本操作，如果哪里写的有问题，请告知一下，万分感谢

下面是表中的基础数据—基于mysql8的哦

• Crouse表–课程表

/*  Navicat Premium Data Transfer   Source Server         : 数据库的回顾  Source Server Type    : MySQL  Source Server Version : 80016  Source Host           : localhost:3306  Source Schema         : recode   Target Server Type    : MySQL  Target Server Version : 80016  File Encoding         : 65001   Date: 13/06/2020 16:00:42 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `Cno` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程号，主键', `Cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程名', `Credit` decimal(5, 0) NOT NULL COMMENT '学分', `Semester` tinyint(1) NOT NULL COMMENT '学期', PRIMARY KEY (`Cno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of course -- ---------------------------- INSERT INTO `course` VALUES ('C001', '高等数学', 4, 1); INSERT INTO `course` VALUES ('C002', '大学英语', 3, 1); INSERT INTO `course` VALUES ('C003', '大学英语', 3, 2); INSERT INTO `course` VALUES ('C004', '计算机文化学', 2, 2); INSERT INTO `course` VALUES ('C005', 'java', 2, 3); INSERT INTO `course` VALUES ('C006', '数据库基础', 4, 5); INSERT INTO `course` VALUES ('C007', '数据结构', 4, 4); INSERT INTO `course` VALUES ('C008', '计算机网络', 4, 4); SET FOREIGN_KEY_CHECKS = 1; 

• SC表–选课表

/*  Navicat Premium Data Transfer   Source Server         : 数据库的回顾  Source Server Type    : MySQL  Source Server Version : 80016  Source Host           : localhost:3306  Source Schema         : recode   Target Server Type    : MySQL  Target Server Version : 80016  File Encoding         : 65001   Date: 13/06/2020 16:01:31 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for sc -- ---------------------------- DROP TABLE IF EXISTS `sc`; CREATE TABLE `sc` ( `Sno` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `Cno` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `Grade` tinyint(5) NULL DEFAULT NULL, PRIMARY KEY (`Sno`, `Cno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of sc -- ---------------------------- INSERT INTO `sc` VALUES ('0811101', 'C001', 96); INSERT INTO `sc` VALUES ('0811101', 'C002', 80); INSERT INTO `sc` VALUES ('0811101', 'C003', 84); INSERT INTO `sc` VALUES ('0811101', 'C005', 62); INSERT INTO `sc` VALUES ('0811102', 'C001', 92); INSERT INTO `sc` VALUES ('0811102', 'C002', 90); INSERT INTO `sc` VALUES ('0811102', 'C004', 84); INSERT INTO `sc` VALUES ('0821102', 'C001', 76); INSERT INTO `sc` VALUES ('0821102', 'C004', 85); INSERT INTO `sc` VALUES ('0821102', 'C005', 73); INSERT INTO `sc` VALUES ('0821102', 'C007', NULL); INSERT INTO `sc` VALUES ('0821103', 'C001', 50); INSERT INTO `sc` VALUES ('0821103', 'C004', 80); INSERT INTO `sc` VALUES ('0831101', 'C001', 50); INSERT INTO `sc` VALUES ('0831101', 'C004', 80); INSERT INTO `sc` VALUES ('0831102', 'C007', NULL); INSERT INTO `sc` VALUES ('0831103', 'C004', 78); INSERT INTO `sc` VALUES ('0831103', 'C005', 65); INSERT INTO `sc` VALUES ('0831103', 'C007', NULL); SET FOREIGN_KEY_CHECKS = 1; 

• student表–学生表

 /*  Navicat Premium Data Transfer   Source Server         : 数据库的回顾  Source Server Type    : MySQL  Source Server Version : 80016  Source Host           : localhost:3306  Source Schema         : recode   Target Server Type    : MySQL  Target Server Version : 80016  File Encoding         : 65001   Date: 13/06/2020 16:01:39 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `Sno` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `Sname` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `SID` char(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `Ssex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `Sage` tinyint(4) NULL DEFAULT NULL, `Sdept` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`Sno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES ('0811101', '李勇', '1', '男', 21, '计算机系'); INSERT INTO `student` VALUES ('0811102', '李晨', '2', '男', 21, '计算机系'); INSERT INTO `student` VALUES ('0811103', '王敏', '3', '女', 21, '计算机系'); INSERT INTO `student` VALUES ('0811104', '张小红', '4', '女', 21, '计算机系'); INSERT INTO `student` VALUES ('0821101', '张立', '5', '男', 21, ' 信息管理系'); INSERT INTO `student` VALUES ('0821102', '吴宾', '6', '男', 21, '信息管理系'); INSERT INTO `student` VALUES ('0821103', '张海', '7', '男', 21, '信息管理系'); INSERT INTO `student` VALUES ('0831101', '钱小平', '8', '女', 21, '通信工程系'); INSERT INTO `student` VALUES ('0831102', '王大力', '9', '男', 21, '通信工程系'); INSERT INTO `student` VALUES ('0831103', '张珊珊', '10', '女', 21, '通信工程系'); SET FOREIGN_KEY_CHECKS = 1; 

以上就是数据库定义语言的一些基本操作

一下是数据库其他的一些基本操作
数据库操作语言（DML）
数据库控制语言（DCL）