MySQL知识点总结(五)---表操作

拼搏现实的明天。 2022-05-17 11:20 312阅读 0赞

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

*   *   *  一.创建表（DDL）
            
             *  1.简单创建
             *  2.给字段添加默认值的创建
             *  3.复制表创建
         *  二.删除表
         *  三.增/删/改表结构（DDL）
            
             *  1.添加字段
             *  2.修改字段
             *  3.删除字段
         *  四.增/删/改表数据（DML）
            
             *  1.insert—增加表数据
                
                 *  普通插入
                 *  将查询结果插入记录
                 *  插入日期
             *  2.update 修改数据
             *  3.delete 删除数据
         *  五.创建表加入约束–Constraint
            
             *  约束的目的
             *  约束的种类
             *  （1）Not null—非空约束
             *  （2）Unique—唯一性约束
             *  （3）Primary key(PK)—主键约束
                
                 *  主键涉及到的术语及其关系
                 *  主键的分类
             *  （4）Foreign key（FK）—外键约束
                
                 *  主键涉及到的术语及其关系
                 *  主键的分类
                 *  父表与子表
             *  添加删除修改表约束
             *  级联更新与删除
                
                 *  on update cascade / on delete cascade

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

### 一.创建表（DDL） ###

#### 1.简单创建 ####

create table 表名{
      列名1 数据类型 长度 ，
      ...
      列名n 数据类型 长度
    };

#### 2.给字段添加默认值的创建 ####

creat table people{
      sex char(2) default '男'，
      mark int（3） default 0 
    };

#### 3.复制表创建 ####

1)全复制
    creat table students_copy as select * from students;
    2）按列复制
    creat table students_copy as select id，name from students;

### 二.删除表 ###

`mysql 法一： drop table 表名 ； 法二（MySql特有）： drop table if exists 表名`

### 三.增/删/改表结构（DDL） ###

#### 1.添加字段 ####

`mysql alter table students add sex char(1);`

#### 2.修改字段 ####

alter table students modify sex char(2);

#### 3.删除字段 ####

`mysql alter table students drop sex;`

### 四.增/删/改表数据（DML） ###

#### 1.insert—增加表数据 ####

##### 普通插入 #####

法一：
    insert into 表名 （列1，列2，...）values（值1，值2，...）;
    法二：（不建议）
    insert into students values (20162516,'仙女男男','SC','1997-06-22');
    //按照默认字段顺序进行赋值（必须写全）

*  字段和值必须一一对应，个数必须相同，数据类型必须一致

##### 将查询结果插入记录 #####

insert into 表1 select * from 表2 where ....
    //必须保证属性列的域相同

##### 插入日期 #####

1)字符串自动转换
    insert into studnets (birth) values ('1997-12-10')； 2）插入当前日期 insert into studnets (birth) values (now（）)； 3）使用 str_to_date insert into students (birth) values (str_to_date('1997年06月22日','%Y-%m-%d'))；

#### 2.update 修改数据 ####

`mysql 格式： update 表名 set 字段名=字段值，字段名=字段值 where 条件 EG1： update students set sex = '女' where id= 20162516; EG2：将所有名字里含有鹏的名字都改为鹏鹏 update students set name = '鹏鹏 'where name like '%鹏%';`

==update 如果没有条件限制，将会把表中所有数据进行更新==

#### 3.delete 删除数据 ####

`mysql delete from 表名 where 条件 EG： delete from students where id = 20162590;`

==如果没有删除操作进行条件限制，将会删除整张表的记录，留下空表==

### 五.创建表加入约束–Constraint ###

#### 约束的目的 ####

对表中的数据进行限制，为了保证表中的记录完整有效

*在 information\_schema中可以查到已经定义的约束信息*

#### 约束的种类 ####

1.  非空约束 not null
2.  唯一性约束 unique
3.  主键约束 primary key 简称：PK
4.  外键约束 foreign key 简称：FK
5.  检查约束 *目前的 MySql不支持，在 Oracle 中可以使用*

#### （1）Not null—非空约束 ####

*  **不可以是null值，必须给定具体的数据**

`mysql creat table s_mark{ name varchar(8) not null， mark int（3） default 0 };`

#### （2）Unique—唯一性约束 ####

*  **具有唯一性不可重复,但是可以为null**

““mysql  
1）列级约束  
creat table s\_mark\{  
id int(8) unique, //!!!  
name varchar(8) not null，  
mark int（3） default 0  
\};

2.1）单字段表级约束–等同于（1）  
creat table s\_mark\{  
id int(8) ,  
name varchar(8) not null，  
mark int（3） default 0  
unique (id) //!!!  
\};  
2.2) 多字段表级约束–表示id与name联合唯一  
creat table s\_mark\{  
id int(8) ,  
name varchar(8) not null，  
mark int（3） default 0  
constraint s\_mark\_Id\_name\_Unique unique (id,name) //!!!  
\};  
““

==表级约束通常要起名字，为了可以对表级约束进行操作==

*  **not null 与 unique 可以联合使用**
    
    > 字段 属性 not null unique
    
    表：非空并且唯一

#### （3）Primary key(PK)—主键约束 ####

==一张表只能有一个==

##### 主键涉及到的术语及其关系 #####

1.主键约束 2.主键字段 3.主键值

*  表中某个字段被添加主键约束后，该字段被称为主键字段，主键字段中的数据都被称为主键值
 *  **添加主键约束后，该字段不能重复也不能为空**
 *  效果类似于 not null unique 但本质不同。除了做到not null unique 的功能外，还==会默认添加索引==
 *  一张表应该有主键字段，若没有，表示这张表是无效的，主键值 是当前行数据的唯一标识

##### 主键的分类 #####

按字段数分类：单一主键 复合主键

按性质分类： 自然主键（主键值是个自然数，且和当前的表的业务没有任何关系）—建议使用

业务主键（主键值与表中业务紧密相关，当业务数据发生改变的时候，主键值通常会受到影响）·

1.1)列级单一主键
    create table people { id int(8) primary key , //列级定义方式 name varchar(10) not null };
    1.2）表级单一主键
    create table people { id int(8) , name varchar(10) not null, primary key(id) //或者可命名 constraint p_id_pk primary key(id) };
    2）符合主键
    create table people { id int(8) , name varchar(10) not null, constraint id_name_pk primary key (id ,name);
    };

*  在MySql中提供了一个自增的数字，专门用来自动生成主键值，主键值不需要用户维护，也不需要用户提供，自动生成。这个数字，默认从1开始，以1递增。—- **auto\_increment**

creat table people(
     id int(10) primary key auto_increment,
     name varchar(10)
    );
    
    insert into people （name） values （'PP'）;//id自动是1
    insert into people （name） values （'PP'）;//id自动是2

#### （4）Foreign key（FK）—外键约束 ####

==一张表可以有多个==

##### 主键涉及到的术语及其关系 #####

1.外键约束 2.外键字段 3.外键值

*  使用外键就是对多对一数据的纵向分离,通常在多的一方做外键
 *  通常都是为了减少数据冗余，将表数据分离，用外键来确定在另一张表**唯一性**的数据（不一定是主键）
 *  外键字段可以为空

##### 主键的分类 #####

按字段数分类：单一外键 复合外键

##### 父表与子表 #####

*  链接时，**引用的**外键表为子表，**被引用**的主键表为父表

<table> 
 <thead> 
  <tr> 
   <th align="center">表</th> 
   <th align="center">主键</th> 
   <th align="center">外键</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">学生表</td> 
   <td align="center">ID</td> 
   <td align="center">ClassNum</td> 
  </tr> 
  <tr> 
   <td align="center">班级表</td> 
   <td align="center">ClassNum</td> 
   <td align="center"></td> 
  </tr> 
 </tbody> 
</table>

在这里父表是班级，子表是学生表。

==插入时，先插父表再插子表==

==删除时，先删子表再删父表==

格式：
    create table 表名（ 字段1 属性 约束， ... constraint 外键名 foreign key (外键字段) references 表名2（唯一性字段） ）； EG： create table class( classnum int (1) primary key, classname varchar(20) not null unique );
    
    create table students( id int(8) primary key, classnum int(1) , constraint FK foreign key (classnum) references class(classnum) );
    
    insert into class (classnum,classname) values (2,'lala');
    insert into students(id,classnum) values ('鹏鹏',2);
    
    delete from students ;
    delete from class;

==MySql里的外键没有列级的表达方式==，

#### 添加删除修改表约束 ####

1)删除约束
    alter table students drop foreign key FK;
    2)添加约束
    alter table students add constraint FK foreign key(classnum) references class(classnum) 3)修改约束 )

#### 级联更新与删除 ####

*  添加级联操作的应该在外键的后面添加
 *  在删除父表中的数据的时候，级联删除子表中的数据 on delete cascade
 *  在更新父表数据的时候，级联更新子表的数据 on update cascade
 *  级联更新和删除要谨慎使用，因为级联更新会导致数据的丢失

##### on update cascade / on delete cascade #####

格式：
    create table 表名（ 字段1 属性 约束， ... constraint 外键名 foreign key (外键字段) references 表名2（唯一性字段） on update cascade ）； 1)创建级联外键 create table students ( id int(8) primary key, classnum int(1) not null, constraint FK foreign key (classnum) references class(classnum) on update cascade );
    2)删除级联外键
    alter table students drop foreign key FK;
    3)添加级联外键
    alter table students add constraint FK foreign key(classnum) references class(classnum) on update cascade

“