mysql基本语句：DDL（数据定义语言)

系统管理员 2024-04-08 12:42 101阅读 0赞

**目录**

1、创建数据库

2、查看数据库

3、选择或使用数据库

4、删除数据库

1、mysql的数据类型

2、字段属性

3、创建数据表

4、查看表

5、删除表

5、表类型

1、查看表类型

2、设置表的表类型

3、设置表的字符集

6、修改表

1、修改表名

2、添加字段

3、修改字段

4、删除字段

7、数据完整性

1、 在数据表上添加约束

2、设置主键约束

3、设置外键约束

4、删除约束

--------------------

### 1、创建数据库 ###

CREATE  DATABASE [IF NOT  EXISTS] 数据库名;
    //实例
    CREATE  DATABASE IF NOT  EXISTS student；

IF NOT EXISTS：如果这个数据库不存在，才执行创建命令，否则不执行这条语句

### 2、查看数据库 ###

SHOW  DATABASES ;

### 3、选择或使用数据库 ###

USE  数据库名 ;

### 4、删除数据库 ###

DROP DATABASE  [IF EXISTS] 数据库名; 
    //实例
    DROP DATABASE IF EXISTS student；

**MySQL中的常用的两种注释**

单行注释：\#......

多行注释：/\*……\*/

#### 1、mysql的数据类型 ####

数值类型：

<table style="width:500px;"> 
 <tbody> 
 <tr> 
 <td>类型</td> 
 <td>说明</td> 
 <td>取值范围</td> 
 <td>储存需求</td> 
 </tr> 
 <tr> 
 <td>TINYINT[(M)]</td> 
 <td>非常小的数据</td> 
 <td>有符值： -27 ~ 27-1 无符号值：0 ~ 28-1</td> 
 <td>1字节</td> 
 </tr> 
 <tr> 
 <td> SMALLINT[(M)] </td> 
 <td>较小的数据</td> 
 <td>有符值： -215 ~ 215-1 无符号值： 0 ~ 216-1</td> 
 <td>2字节</td> 
 </tr> 
 <tr> 
 <td>MEDIUMINT[(M)]</td> 
 <td>中等大小的数据</td> 
 <td>有符值： -223 ~ 223-1 无符号值： 0 ~ 224-1</td> 
 <td>3字节</td> 
 </tr> 
 <tr> 
 <td>INT[(M)]</td> 
 <td>标准整数</td> 
 <td>有符值： -231 ~ 231-1 无符号值：0 ~ 232-1</td> 
 <td>4字节</td> 
 </tr> 
 <tr> 
 <td>BIGINT[(M)]</td> 
 <td>较大的整数</td> 
 <td>有符值： -263 ~263-1 无符号值：0 ~264-1</td> 
 <td>8字节</td> 
 </tr> 
 <tr> 
 <td>FLOAT[(M)]</td> 
 <td>单精度浮点数</td> 
 <td>±1.1754351e -38</td> 
 <td>4字节</td> 
 </tr> 
 <tr> 
 <td>DOUBLE[(M)]</td> 
 <td>双精度浮点数</td> 
 <td>±2.2250738585072014e -308</td> 
 <td>8字节</td> 
 </tr> 
 <tr> 
 <td>DECIMAL</td> 
 <td>字符串形式的浮点数</td> 
 <td>decimal(m, d)</td> 
 <td>m个字节</td> 
 </tr> 
 </tbody> 
</table>

字符串类型

<table> 
 <thead> 
 <tr> 
 <th>类型</th> 
 <th>说明</th> 
 <th>最大长度</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td>CHAR[(M)]</td> 
 <td>固定长字符串，检索快但费空间， 0 &lt;= M &lt;= 255</td> 
 <td>M个字符</td> 
 </tr> 
 <tr> 
 <td>VARCHAR[(M)]</td> 
 <td>可变字符串 0 &lt;= M &lt;= 65535</td> 
 <td>可变长度</td> 
 </tr> 
 <tr> 
 <td>TINYTEXT</td> 
 <td>微型文本串</td> 
 <td>0-255个字符</td> 
 </tr> 
 <tr> 
 <td>TEXT</td> 
 <td>文本串</td> 
 <td>0-65535个字符</td> 
 </tr> 
 </tbody> 
</table>

日期和时间类型

<table> 
 <thead> 
 <tr> 
 <th>类型</th> 
 <th>说明</th> 
 <th>取值范围</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td>DATE</td> 
 <td>YYYY-MM-DD，日期格式</td> 
 <td>1000-01-01~ 9999-12-31</td> 
 </tr> 
 <tr> 
 <td>TIME</td> 
 <td>Hh:mm:ss ，时间格式</td> 
 <td>-838:59:59~838:59:59</td> 
 </tr> 
 <tr> 
 <td>DATETIME</td> 
 <td>YY-MM-DD hh:mm:ss</td> 
 <td>1000-01-01 00:00:00 至9999-12-31 23:59:59</td> 
 </tr> 
 <tr> 
 <td>TIMESTAMP</td> 
 <td>YYYYMMDDhhmmss格式表示的时间戳</td> 
 <td>197010101000000 ~2038年的某个时刻</td> 
 </tr> 
 <tr> 
 <td>YEAR</td> 
 <td>YYYY格式的年份值</td> 
 <td>1901~2155</td> 
 </tr> 
 </tbody> 
</table>

MySQL允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符，如”16-06-16” ”16.06.16” ”16/06/16”都可以写入数据表

#### 2、字段属性 ####

<table> 
 <thead> 
 <tr> 
 <th>字段属性</th> 
 <th>说明</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td>UNSIGNED</td> 
 <td>无符号的，声明该数据列不允许负数。</td> 
 </tr> 
 <tr> 
 <td>ZEROFILL</td> 
 <td>1. 0填充的，不足位数的用0来填充，如 int(3),5则为 005； 2. 使用zerofill会默认加unsigned。</td> 
 </tr> 
 <tr> 
 <td>AUTO_INCREMENT</td> 
 <td>1. 自动增长的，每添加一条数据，自动在上一个记录数上加1； 2. 通常用于设置主键，且为整数类型； 3. 可定义起始值和步长</td> 
 </tr> 
 <tr> 
 <td>NULL 和 NOT NULL</td> 
 <td>1. 默认为NULL,即没有插入该列的数值； 2. 如果设置为NOT NULL，则该列必须有值；</td> 
 </tr> 
 <tr> 
 <td>DEFAULT</td> 
 <td>1. 默认的，用于设置默认值； 2. 例如，性别字段，默认为“男”，否则为“女”；若无指定该列的值，则默认为“男”的值</td> 
 </tr> 
 </tbody> 
</table>

#### **3、创建数据表** ####

CREATE   TABLE [ IF NOT EXISTS ]    `表名`   (
        
            `字段名1`   列类型 [ 字段属性 | 约束]  [ 索引 ] [注释] ,
            `字段名2`   列类型 [ 字段属性 | 约束]  [ 索引 ] [注释] , 
             … …    
             `字段名n`   列类型 [字段属性 | 约束]  [ 索引 ] [注释] 
    
    )  [  表类型 ] [ 表字符集 ] [注释] ;

**注意：**

1：反引号用于区别MySQL保留字（全部大写）与普通字符而引入的。

2：多个字段（也称为列）之间使用逗号分隔，最后一个字段后无须逗号。

3、注意区分反引号（\`）和单引号（'）的区别

4、字段属性是有序的

创建表实例：

![ca0a556cd46e4f3c8b242efdff6eb8e7.png][]

CREATE TABLE IF NOT EXISTS `STUDENTS`(
    `STUDENTNO`INT(4) UNSIGNED NOT NULL COMMENT'学号',
    `LOGINPWD` VARCHAR(20) NOT NULL COMMENT'密码',
    `STUDENTNAME` VARCHAR(50) NOT NULL COMMENT'学生密码',
    `SEX`CHAR(1) NOT NULL DEFAULT '男' COMMENT'性别',
    `GRADEID` INT(4) UNSIGNED COMMENT'年纪编号',
    `PHONE` VARCHAR(50)COMMENT'联系电话',
    `ADDRESS`VARCHAR(255) NULL DEFAULT'地址不详' COMMENT'家庭地址',
    `BORNDATE` DATE COMMENT'出生日期',
    `EMAIL` VARCHAR(50) COMMENT'邮箱账号',
    `IDENTITYCARD` VARCHAR(18) COMMENT'身份证号'
    )COMMENT='学生表';

#### 4、查看表 ####

创建表后，如果行看表是否创建成功，可以使用查看命令

SHOW TABLES;

创建表之后，如果需要查看一下该表的定义，可以使用如下的SQL命令。

DESCRIBE  表名或DESC 表名

实例：

create table if not exists user(
      id int,
      name VARCHAR(20),
      age int,
      birth date,
      address VARCHAR(25)
    )COMMENT = '用户表';
    show tables;

#### 5、删除表 ####

DROP TABLE [IF EXISTS] 表名
    例如：
    DROP table if EXISTS user;

### 5、表类型 ###

MySQL属于关系型数据库管理系统，其他包括数据库以及用于数据库访问管理的接口系统。数据库负责存储数据，接口系统负责管理数据库。

由于不同用户对数据的容量、访问速度、数据安全性有不同的要求，为了满足不同用户的业务需求，MySQL数据库采用多种存储引擎进行数据存储。

表的存储引擎也称为表类型。

MySQL5.5支持的存储引擎有MyISAM、InnoDB 、HEAP、BOB、CSV等。

**查看系统所支持的存储引擎**

SHOW ENGINES；

**常用的存储引擎：****MyISAM****和****InnoDB** **。**

**MySQL5.5****以上版本默认的存储引擎是****InnoDB****。**

#### **1、查看表类型** ####

语法：

SHOW VARIABLES LIKE 'storage_engine%'

<table> 
 <tbody> 
 <tr> 
 <td style="background-color:#1293cd;border-color:#215968;text-align:center;vertical-align:middle;width:147pt;"> 名称 </td> 
 <td style="background-color:#1293cd;border-color:#215968;text-align:center;vertical-align:middle;width:218pt;"> MyISAM </td> 
 <td style="background-color:#1293cd;border-color:#215968;text-align:center;vertical-align:middle;width:192pt;"> InnoDB </td> 
 </tr> 
 <tr> 
 <td style="background-color:#e9f9fd;border-color:#215968;text-align:center;vertical-align:middle;width:147pt;"> 事务处理 </td> 
 <td style="background-color:#e9f9fd;border-color:#215968;text-align:center;vertical-align:middle;width:218pt;"> 不支持 </td> 
 <td style="background-color:#e9f9fd;border-color:#215968;text-align:center;vertical-align:middle;width:192pt;"> 支持 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#215968;text-align:center;vertical-align:middle;width:147pt;"> 数据行锁定 </td> 
 <td style="border-color:#215968;text-align:center;vertical-align:middle;width:218pt;"> 不支持 </td> 
 <td style="border-color:#215968;text-align:center;vertical-align:middle;width:192pt;"> 支持 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#e9f9fd;border-color:#215968;text-align:center;vertical-align:middle;width:147pt;"> 外键约束 </td> 
 <td style="background-color:#e9f9fd;border-color:#215968;text-align:center;vertical-align:middle;width:218pt;"> 不支持 </td> 
 <td style="background-color:#e9f9fd;border-color:#215968;text-align:center;vertical-align:middle;width:192pt;"> 支持 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#215968;text-align:center;vertical-align:middle;width:147pt;"> 全文索引 </td> 
 <td style="border-color:#215968;text-align:center;vertical-align:middle;width:218pt;"> 支持 </td> 
 <td style="border-color:#215968;text-align:center;vertical-align:middle;width:192pt;"> 不支持 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#e9f9fd;border-color:#215968;text-align:center;vertical-align:middle;width:147pt;"> 表空间大小 </td> 
 <td style="background-color:#e9f9fd;border-color:#215968;text-align:center;vertical-align:middle;width:218pt;"> 较小 </td> 
 <td style="background-color:#e9f9fd;border-color:#215968;text-align:center;vertical-align:middle;width:192pt;"> 较大,约2倍 </td> 
 </tr> 
 </tbody> 
</table>

适用场合：

•  使用 MyISAM :  节约空间及相应速度，适合以访问为主的应用。

•  使用 InnoDB :  安全性，事务处理及多用户操作数据表。

#### 2、**设置表的表类型** ####

DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student`(
    ……) ENGINE =  InnoDB
    
    DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student`(
    ……) ENGINE =  MyISAM

#### **3、设置表的字符集** ####

**可为数据库、数据表、数据列设定不同的字符集。**

方法1：创建时通过命令来设置。例如：

CREATE TABLE  表名(
    	#省略一些代码
    )CHARSET  =  utf8;

方法2：修改配置文件my.ini（Windows系统）中的参数。例如：

character-set-sever = utf8

### 6、修改表 ###

#### 1、修改表名 ####

语法：

ALTER TABLE 旧表名  RENAME AS  新表名

#### 2、添加字段 ####

语法：

ALTER TABLE 表名   ADD 字段名   列类型 [ 属性 ]

#### 3、修改字段 ####

语法：

ALTER TABLE 表名   MODIFY 字段名   列类型 [ 属性 ]  #修改列的数据类型

ALTER TABLE 表名  CHANGE 旧字段名 新字段名  列类型 [ 属性 ]#修改列的数据类型及名称

#### 4、删除字段 ####

语法：

ALTER TABLE 表名   DROP  字段名

实例：

#修改表名
    alter table user RENAME AS admin;
    #添加字段
    alter table admin add phone VARCHAR(11) not null COMMENT '手机号';
    #修改字段名
    alter table admin MODIFY age VARCHAR(3);
    #修改字段名且添加属性
    alter table admin change age stuage int not null COMMENT '年龄';
    #删除字段
    alter table admin drop address;

### **7、数据完整性** ###

数据库中的数据是从外界输入的，而数据的输入由于种种原因，会发生输入无效或错误信息。保证输入的数据符合规定，成为了数据库系统，尤其是多用户的关系数据库系统首要关注的问题。

它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

数据完整性=数据的精确性+数据的可靠性

数据完整性主要分为三类：**实体完整性、域完整性、引用完整性。**

**实体完整性：**实体完整性是指保证表中所有的行唯一。

**域完整性：**域完整性是指数据库表中的列必须满足某种特定的数据类型或约束。其中约束又包括取值范围、精度等规定。

**引用完整性：**引用完整性是指保证主关键字（被引用表）和外部关键字（引用表）之间的参照关系。它涉及两个或两个以上表数据的一致性维护。

使用约束实现数据完整性：数据库采用多种方法来保证数据完整性，包括约束、规则和触发器。

<table> 
 <tbody> 
 <tr> 
 <td style="background-color:#009ada;border-color:#000000;vertical-align:middle;width:85pt;"> &nbsp;约束类型 </td> 
 <td style="background-color:#009ada;border-color:#000000;vertical-align:middle;width:142pt;"> 关键字 </td> 
 <td style="background-color:#009ada;border-color:#000000;vertical-align:middle;width:408pt;"> 说明 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#f0f3f8;border-color:#000000;vertical-align:middle;width:85pt;"> 非空约束 </td> 
 <td style="background-color:#f0f3f8;border-color:#000000;vertical-align:middle;width:142pt;"> NOT NULL </td> 
 <td style="background-color:#f0f3f8;border-color:#000000;vertical-align:middle;width:408pt;"> 如果字段不允许为空，则需要设置NOT NULL约束。 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#f2f2f2;border-color:#000000;vertical-align:middle;width:85pt;"> 默认约束 </td> 
 <td style="background-color:#f2f2f2;border-color:#000000;vertical-align:middle;width:142pt;"> DEFAULT </td> 
 <td style="background-color:#f2f2f2;border-color:#000000;vertical-align:middle;width:408pt;"> 赋予某字段默认值，如果该字段没有赋值，则其值为默认值。 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#f0f3f8;border-color:#000000;vertical-align:middle;width:85pt;"> 唯一约束 </td> 
 <td style="background-color:#f0f3f8;border-color:#000000;vertical-align:middle;width:142pt;"> UNIQUE KEY </td> 
 <td style="background-color:#f0f3f8;border-color:#000000;vertical-align:middle;width:408pt;"> 设置该字段的值唯一的，允许为空。 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#f2f2f2;border-color:#000000;vertical-align:middle;width:85pt;"> 主键约束 </td> 
 <td style="background-color:#f2f2f2;border-color:#000000;vertical-align:middle;width:142pt;"> PRIMARY KEY </td> 
 <td style="background-color:#f2f2f2;border-color:#000000;vertical-align:middle;width:408pt;"> 设置该字段为表的主键，可以作为该表记录的唯一标识。 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#f2f2f2;border-color:#000000;vertical-align:middle;width:85pt;"> 外键约束 </td> 
 <td style="background-color:#f2f2f2;border-color:#000000;vertical-align:middle;width:142pt;"> FOREIGN KEY </td> 
 <td style="background-color:#f2f2f2;border-color:#000000;vertical-align:middle;width:408pt;"> 用于在两表之间建立关系，需要引用主表的哪一字段。 </td> 
 </tr> 
 </tbody> 
</table>

#### 1、 **在数据表上添加约束** ####

**语法：**

ALTER TABLE 表名  
    ADD CONSTRAINT 约束名  约束关键字  具体的约束说明

**约束名的取名推荐采用：约束类型\_约束字段**

**主键（****Primary Key）****约束：如** **PK\_stuNo**

**唯一（****Unique ）****约束：如** **UQ\_stuID**

**外键****(****Foreign Key)****约束：如** **FK\_stuNo**

**设置唯一约束**  
语法：

ALTER TABLE 表名  
    ADD CONSTRAINT 约束名  UNIQUE(列名)
    例如
    #设置唯一约束
    ALTER TABLE admin add CONSTRAINT Uk_id UNIQUE(id);

**主键：****PRIMARY KEY**

数据库主键：指的是一个列或多列的组合，其值能唯一地标识表中的每一行，通过它可强制表的实体完整性。

**数据库主键的作用**

1、保证实体的完整性;

2、加快数据库的操作速度；

3、添加新记录时，DBMS会自动检查新记录的主键值，不允许该值与其他记录的主键值重复；

4、DBMS自动按主键值的顺序显示记录。如果没有定义主键，则按输入记录的顺序显示记录。

**数据库主键的选择规则**

1、最少性是指列数最少的键。如果可以从单个主键和复合主键中选择，应该选择单个主键，这是因为操作一列比操作多列要快。当然该规则也有例外，例如，两个整数类型的列的组合比一个很大的字符类型的列操作要快。

2、稳定性是指列中数据的特征。由于主键通常用来在两个表之间建立联系，所以主键的数据不能经常更新。理想情况下，应该永远不变。

#### **2、设置主键约束** ####

**语法：**

ALTER TABLE 表名  
    ADD CONSTRAINT 约束名  PRIMARY KEY(列名1,列名2,…列名n)

**使用场景：如果一个字段即要求唯一又不能为****null****，可使用主键约束**

如果两列或多列组合起来唯一地标识表中的每个行，则该主键叫做“复合主键”。

#设置主键
    ALTER table admin add CONSTRAINT pk_name PRIMARY key (name);
    #设置多个主键
    Alter table admin add CONSTRAINT pk_name PRIMARY key (id,name);

#### 3、设置外键约束 ####

ALTER TABLE 从表名 ADD CONSTRAINT 约束名  FOREIGN KEY(从表的列名)
    REFERENCES 主表名(主表的列名) 
    
    例如：
    ALTER TABLE result
    ADD CONSTRAINT fk_studentno FOREIGN KEY(studentno)
    REFERENCES student(studentno)

提示：

1、主表中的字段必须是主键

2、从表中的外键列和主表中的主键列的数据类型要保持一致，和字段名无关

3、主表和从表的表类型为InnoDB

#### 4、删除约束 ####

ALTER TABLE 表名  DROP PRIMARY KEY;   #删除主键约束
    ALTER TABLE 表名  DROP KEY 唯一约束名;  #删除唯一约束
    ALTER TABLE 表名  DROP FOREIGN KEY 外键约束名;  #删除外键约束

ALTER TABLE student 
    DROP FOREIGN KEY fk_studentno

[ca0a556cd46e4f3c8b242efdff6eb8e7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/b28182f394ad42dbbe666c261a8ba8ff.png