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

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

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 ;
   

   

2. 创建一个数据库

   CREATE DATABASE [IF NOT EXISTS]  库名 [[DEFAULT] CHARACTER SET <字符集名>] [[DEFAULT] COLLATE <校对规则名>];
   --[]的内容是可写可不写，
   --[IF NOT EXISTS]意思为：为如果不存在。（不加这句话，创建时如果库已经存在则会报错）
   --[[DEFAULT] CHARACTER SET <字符集名>]：指定字符集
   --[[DEFAULT] COLLATE <校对规则名>]:指定字符集的默认校对规则
   

   

3. 查看当前正在使用的数据库

   SELECT DATABASE();
   

4. 删除一个数据库

   DROP DATABASE [if EXISTS] 库名;
   --[]内为：如果存在（不加的话，要删除的库名不存在时，会报错）
   

   

5. 进入其中一个库

   USE `库名`;
   --有冲突时，可以添加``（tab键上面的符号）来和关键字进行区分。
   

   

6. 查看数据库创建的语句

   SHOW CREATE DATABASE 库名;
   

   

7. 在heidisql中创建数据库

   

8. 退出数据库

   exit;
   

   

9. 注释

   -- 单行注释（sql语法的单行注释）
   /*
   	多行注释
   */
   

2、操作数据表

1. 显示库中的所有表

   SHOW TABLES ;
   

   

2. 进入其中一张表，显示数据表的结构

   DESC `表名`;
   

   

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';
   

   

4. 删除一张表 [如果表存在]

   DROP TABLE [if exists] 表名;
   

   

5. 查看某张表的创建语句

   SHOW CREATE TABLE `表名`;
   

   

6. 修改表的名字

   ALTER TABLE `原表名` RENAME AS `新表名`;
   

   

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;
   

   

8. 增加一个字段

   ALTER TABLE  `表名` ADD COLUMN `字段名` 列属性...;
   

   

9. 删除一个字段

   ALTER TABLE `表名` DROP `字段名`;
   

   

​

阿里巴巴《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`)						-
   );