MySQL语法之DDL数据定义语言（操作数据、数据表）

雨点打透心脏的1/2处 2024-03-26 13:47 51阅读 0赞

> [Java知识点总结：想看的可以从这里进入][Java]

#### 目录 ####

*   *   *  3.4、SQL语言
         *   *  3.4.1、DDL数据定义
             *   *  1、操作数据库
                 *  2、操作数据表
                 *  3、约束

#### 3.4、SQL语言 ####

SQL是结构化查询语言（Structured Query Language）的缩写，是一门标准的计算机语言，主要是用来访问和操作数据库，包括数据定义、数据操作、数据控制。SQL现在是RDBMS的标准语言，但是不同的数据库所用的SQL语言有一定的差异，都是在SQL原有的基础上进行了一些扩展。

按照功能的不同SQL语句大致分为4类：

*  DDL（数据定义语言）（Data Definition Language）：用于数据库、表、视图、索引等的建立、删除。
    
    主要的语句关键字： CREATE 、 DROP 、 ALTER 等。
 *  DML（数据操纵语言）（Data Manipulation Language）：用于添加、删除和修改表中的数据。
    
    主要的语句关键字：INSERT 、 DELETE 、 UPDATE 等
 *  DCL（数据控制语言）（Data Control Language）：用于定义数据库、表、字段、用户的访问权限和安全级别
    
    主要的语句关键字： GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。
 *  DQL（数据查询语言）（Data Query Language）：数据库的基本功能，对数据进行查询
    
    主要关键字select，也是数据库最重要的部分

需要注意的是MySQL在 Windows 环境下是大小写不敏感的，在 Linux 环境下是大小写敏感的。所有在写法上需要进行统一：数据库名、表名、表别名、字段名、字段别名等都小写，而SQL 关键字、函数名、绑定变量等都大写

##### 3.4.1、DDL数据定义 #####

用于数据库、表、视图等的建立、删除。

> 命名规则：
> 
> 1、数据库、表名不得超过30个字符，变量名限制为29个，只能包含 A–Z, a–z, 0–9, \_共63个字符
> 
> 2、数据库名、表名、字段名等对象名中间不要包含空格
> 
> 3、同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
> 
> 4、保证你的字段没有和保留字、数据库系统或常用方法冲突。如果需要使用，需要用 \`\` 引起来
> 
> 5、保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。

###### 1、操作数据库 ######

win+r输入cmd打开命令行

1.  查看所有的数据库
    
        SHOW DATABASES ;
    
    ![image-20230223143022477][]
2.  创建一个数据库
    
        CREATE DATABASE [IF NOT EXISTS]  库名 [[DEFAULT] CHARACTER SET <字符集名>] [[DEFAULT] COLLATE <校对规则名>];
        --[]的内容是可写可不写，
        --[IF NOT EXISTS]意思为：为如果不存在。（不加这句话，创建时如果库已经存在则会报错）
        --[[DEFAULT] CHARACTER SET <字符集名>]：指定字符集
        --[[DEFAULT] COLLATE <校对规则名>]:指定字符集的默认校对规则
    
    ![image-20230223144334147][]
3.  查看当前正在使用的数据库
    
        SELECT DATABASE();
4.  删除一个数据库
    
        DROP DATABASE [if EXISTS] 库名;
        --[]内为：如果存在（不加的话，要删除的库名不存在时，会报错）
    
    ![image-20230223144536022][]
5.  进入其中一个库
    
        USE `库名`;
        --有冲突时，可以添加``（tab键上面的符号）来和关键字进行区分。
    
    ![image-20230223144721837][]
6.  查看数据库创建的语句
    
        SHOW CREATE DATABASE 库名;
    
    ![image-20230223145035447][]
7.  在heidisql中创建数据库
    
    ![image-20220418172945553][]
8.  退出数据库
    
        exit;
    
    ![image-20210419150918874][]
9.  注释
    
        -- 单行注释（sql语法的单行注释）
        /*
        	多行注释
        */

###### 2、操作数据表 ######

1.  显示库中的所有表
    
        SHOW TABLES ;
    
    ![image-20230223145330424][]
2.  进入其中一张表，显示数据表的结构
    
        DESC `表名`;
    
    ![image-20230223145400316][]
3.  创建一张表
    
        create table [if not exists] `表名`(
         	`字段名` 字段的数据类型 [属性] [索引] [注释],
            `字段名` 字段的数据类型 [属性] [索引] [注释],
            ......
            primary key(`id为主键`)
        )[表类型] [字符集编码] [注释] 
        
        -- 创建数据表时尽量写上字符编码：CHARSET = UTF8，MySQL默认的字符集编码Latin1，不支持中文
    
        CREATE TABLE `userinfo` (
        	`pk_id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '主键id', -- int类型，不能为空，自增，备注为主键id
        	`username` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名', -- 不为空，默认值为匿名，备注为姓名
        	`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
            `birthday` DATETIME DEFAULT NULL COMMENT '生日',	  	   -- datetime类型，默认值为null，备注生日
        	PRIMARY KEY (`pk_id`)						-- 设置的主键为pk_id字段
        )
        COMMENT='用户表'
        COLLATE='utf8mb4_0900_ai_ci';
    
    ![image-20230223150538702][]
4.  删除一张表 \[如果表存在\]
    
        DROP TABLE [if exists] 表名;
    
    ![image-20230223150626695][]
5.  查看某张表的创建语句
    
        SHOW CREATE TABLE `表名`;
    
    ![image-20230223150721421][]
6.  修改表的名字
    
        ALTER TABLE `原表名` RENAME AS `新表名`;
    
    ![image-20230223150840254][]
7.  修改字段
    
        -- 修改字段名字
        ALTER TABLE `表名` 
        	CHANGE [COLUMN] `旧字段名` `新字段名` 新数据类型 新类型长度  新默认值  新注释;
    
        ALTER TABLE `newuserinfo`
        	CHANGE COLUMN `pk_id` `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '主键id' FIRST,
        	DROP PRIMARY KEY,
        	ADD PRIMARY KEY (`id`) USING BTREE;
    
    ![image-20230223151626997][]
8.  增加一个字段
    
        ALTER TABLE  `表名` ADD COLUMN `字段名` 列属性...;
    
    ![image-20230223152029087][]
9.  删除一个字段
    
        ALTER TABLE `表名` DROP `字段名`;
    
    ![202302231521177.png][]

> 阿里巴巴《Java开发手册》MySQL字段命名
> 
> 【 强制 】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
> 
> 【 强制 】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。
> 
> 【 强制 】表必备三字段：id, gmt\_create, gmt\_modified。说明：其中 id 必为主键，类型为BIGINT UNSIGNED、单表时自增、步长为 1。gmt\_create，gmt\_modified 的类型均为 DATETIME 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新
> 
> 【 推荐 】表的命名最好是遵循 “业务名称\_表的作用”。
> 
> 【 推荐 】库名与应用名称尽量一致。
> 
> 【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

###### 3、约束 ######

有以下六种约束:

1.  NOT NULL ：非空约束，规定某个字段不能为空，用于确保当前列的值不为空值，非空约束只能出现在表的列上
    
        -- 创建NOT NULL 约束
        CREATE TABLE `test_table` (
        	`pk_id` INT(10) NOT NULL , 
        	`username` VARCHAR(20) NOT NULL, 
        	`password` VARCHAR(20) NULL 						
        )
        COMMENT='测试数据'
        COLLATE='utf8mb4_0900_ai_ci';
    
        -- 增加 not null 约束：
        ALTER TABLE test_table MODIFY password VARCHAR(20) NOT NULL;
    
        -- 取消 not null 约束
        ALTER TABLE test_table MODIFY password VARCHAR(20) NULL;
2.  UNIQUE 唯一约束，规定某个字段在整个表中是唯一的，同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同，MySQL会给唯一约束的列上默认创建一个唯一索引
    
        -- 表示用户名和密码组合不能重复
        CREATE TABLE test_table(
        	`pk_id` INT(10) NOT NULL , 
        	`username` VARCHAR(20) NOT NULL, 
        	`password` VARCHAR(20) NULL,	
        	CONSTRAINT uk_name_pwd UNIQUE(`username`,`password`)
        );
    
        -- 添加唯一约束
        -- 不给名字
        ALTER TABLE test_table ADD UNIQUE(`username`,`password`);
        -- 给名字
        ALTER TABLE test_table ADD CONSTRAINT uk_name_pwd UNIQUE(`username`,`password`);
        -- 给一个列唯一约束
        ALTER TABLE test_table MODIFY `username` VARCHAR(20) UNIQUE;
    
        -- 删除约束
        ALTER TABLE test_table DROP INDEX uk_name_pwd;
3.  PRIMARY KEY 主键(非空且唯一)：主键约束列不允许重复，也不允许出现空值，如果是多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。MySQL的主键名总是PRIMARY
    
        CREATE TABLE `test_table` (
        	`pk_id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '主键id', 
        	`username` VARCHAR(20) NOT NULL  COMMENT '姓名', 
        	`password` VARCHAR(20) NOT NULL COMMENT '密码',
        	PRIMARY KEY (`pk_id`)	
        );
    
        -- 添加主键约束
        ALTER TABLE test_table ADD PRIMARY KEY(`pk_id`);
    
        -- 修改主键约束
        ALTER TABLE test_table MODIFY `pk_id` INT AUTO_INCREMENT  PRIMARY KEY;
    
        -- 删除主键约束
        ALTER TABLE test_table DROP PRIMARY KEY;
4.  FOREIGN KEY 外键：外键约束是保证一个或两个表之间的完整性，是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列。
    
        -- 表1 表2
        CREATE TABLE test1(
            id INT AUTO_INCREMENT PRIMARY KEY,
            `name` VARCHAR(20)
        );
        CREATE TABLE test2(
            id INT AUTO_INCREMENT PRIMARY KEY,
            `name` VARCHAR(20),
            test1_id INT
        );
        
        -- 添加外键
        ALTER TABLE `test2` ADD CONSTRAINT `FK_test2_test1` FOREIGN KEY (`test1_id`) REFERENCES `test1` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION;
        
        -- 删除外键约束：
        ALTER TABLE test2 DROP FOREIGN KEY FK_test2_test1;
5.  CHECK 检查约束（MySQL不支持）
6.  DEFAULT ：给字段设置默认值
    
        CREATE TABLE `userinfo` (
        	`username` VARCHAR(20) NOT NULL DEFAULT '匿名', -- 不为空，默认值为匿名，备注为姓名
        	`password` VARCHAR(20) NOT NULL DEFAULT '123456',
        	PRIMARY KEY (`pk_id`)						-
        );

[Java]: https://blog.csdn.net/yuandfeng/article/details/114333525
[image-20230223143022477]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/4d4e8c74d89047d4a7c8bf4f69a8e712.png
[image-20230223144334147]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/54c0032a32e24f49a4dc0ee512fc17e0.png
[image-20230223144536022]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/529af164711e476f8366e4db2fa8a541.png
[image-20230223144721837]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/13dbf806b0d1486586a2e842b54e85ec.png
[image-20230223145035447]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/0d224acf22924f3094bde10f959354d5.png
[image-20220418172945553]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/1a753312687a4440ad0fa927763184f8.png
[image-20210419150918874]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/9675e100b8634114a93c16b29a370256.png
[image-20230223145330424]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/a3d8dacebef941a19b5ee512403c083d.png
[image-20230223145400316]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/4bf123a72b8d46318416e24ddf7f954a.png
[image-20230223150538702]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/9df7d49969af44559a828b14d17272ac.png
[image-20230223150626695]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/fffea6a76e4d459c834a64775511132c.png
[image-20230223150721421]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/3745c5d6e2e643bcbea8567dd469be35.png
[image-20230223150840254]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/a0fc4f5e113f4696af5d79e1e17100fd.png
[image-20230223151626997]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/6e314d7c3e1a4abc9d4321027ffb8a5b.png
[image-20230223152029087]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/140195cd46fd4d54bd464ad3cabbb564.png
[202302231521177.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/51eb607eb03e4493bf9ada963c4b8f37.png