辉仔日记之学代码第十七期——DML和DDL

目录

一、DML语言

  1.插入：

  2、修改

  3、删除

二、DDL语言

1.库的管理

2.表的管理

3.数据类型

  4.常见约束

5.标识列

---

DML（Data Manipulation Language）：数据操纵语言

1) 插入：INSERT
2) 更新：UPDATE
3) 删除：DELETE

DDL（Date Definition Language）：数据定义语言

数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如：

表 视图 索引 同义词 簇 

CREATE TABLE

VIEW

INDEX

 SYN

 CLUSTER

一、DML语言

  1.插入：

（1）方式一

语法：

Insert into 表名 （字段名，。。。。） values （值，。。）

特点：

     1）要求值的类和字段的类型要一致或兼容

     2）字段的个数和顺序不一定与原始表中的字段个数和顺序一致但必须保证值和字段一一对应。

     3）假如表中有可以为null的字段，注意可以通过以下两种方式插入null值

       ①字段和值都省略

       ②字段协商，值用null

     4）字段和值的个数必须一致

     5）字段名可以省略，默认所有列

（2）方式二

语法： insert into 表名 set 字段= 值，字段=值，。。。

两种方式的区别

1.方式一支持一次性插入多行，语法如下

Insert into 表名【（字段名，。。。。）】values （），（），。。。；

2.方式二支持子查询。语法如下：

Insert into 表名

查询语句；

  2、修改

（1）修改单表的记录

语法：update 表名 set字段=值，字段=值【where筛选条件】

（2）修改多表的记录【补充】

语法：update 表名

Left | right| inner join

on连接条件

 set 字段=值，字段=值【where 筛选条件 】

  3、删除

（1）方式一：delete

     1）删除单表的记录

       语法：

       Delete from 表名

     【wehre 筛选条件】

     【limit 条目数】

       2）级联删除

       语法：

       Delete 别名 1，别名2

       from 表1 别名

       inner| left| right join 表2 别名

On 连接条件

【where 筛选条件】

（2）方式二：truncate

语法：truncate table 表名;

（3）两种方式的区别【面试题】

1.truncate 删除后，如果插入，表识列从1开始

  Delete 删除后，如果插入，标识从断点开始

2.delete可以添加筛选条件

  turncate不可以添加筛选条件

3.truncate效率高

4.truncate没有返回值  delete可以返回受影响的行数

5.truncate不可以回滚  delete可以回滚

---

二、DDL语言

1.库的管理

  （1）创建库

   Create database if not exists 库名 character set 字符集名；

   （2）修改库

   Alter database 库名 character set 字符集名；

   （3）删除库

   Drop database 【if exist 】库名；

2.表的管理

     （1）创建表

Create table [if not exists] 表名 （

字段名 字段类型 【约束】，

字段名 字段类型 【约束】，

。。。

字段名 字段类型 【约束】

）；

     （2）修改表

         1）添加列

Alter table 表名 add column 列名 类型 【first |after 字段名】；

        2）修改列的类型或约束

Alter table 表名 moidfy column 列名 新类型 【新约束】；

        3）修改列名

Alter table 表名 change column 旧列名 新列名 类型；

        4）删除列

Alter table 表名 drop column 列名；

        5）修改表名

Alter table 表名 rename 【to】新表名；

     （3）删除表

Drop table 【if exists】表名；

     （4）复制表

        1）复制表的结构

Create table 表名 like 旧表；

        2）复制表的结构+数据

Create table 表名 like 旧表；

Select 查询列表 from 旧表 【where 筛选】；

3.数据类型

     （1）数值型

          1）整型

tinyint、smallint、mediumint、int/integer、bigint

1                  2                   3                       4              8

         特点：

①都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号

②如果超出范围，会报out of range 异常，插入临界值

③长度可以不指定，默认有一个长度

长度代表显示的宽度，如果不够则左边用0填充，但需要搭配zerofill，并且默认变为无符号整型

         2）浮点型

 浮点数：float(M,D)    ||    double(M,D)

 定点数：dec(M,D)     ||    decimal(M,D)

         特点：

        ①M和D   M:代表整数部位+小数部位的总长度

                        D:小数部位

                        M和D都可以省略，如果是decimal ，则默认是（10,10）

                        float和double则默认是插入数值的精度来转换

        ②定点型的精确度较高，如果要求插入数值的精度较高，如货币运算

     （2）字符型

          1）较短的文本

                char

                varchar

                特点：写法

                char（M）可以省略       最大最多的字符数         不可变字符 效率高

                varchar（M) 不可以省略  最大最多的字符数         可变字符   效率低

          2）较长的文本

                text

                blob（较大的二进制文件）

                其他：

                        binary和varbinary用于保存较短的二进制

                        emum用于保存枚举 

                        CREATE TABLE tab_char(

                          c1 ENUM('a','b','c')#只能插abc字符);

                        set用于保存集合

        （3）字符型

             1）分类：

date 只保存日期

time 只保存时间

year 只保存年

datetime 保存日期+时间

timestamp 保存日期+时间

             2）特点：

                                      字节             范围           时区影响

                datetime           8        大1000-9999           受

                timestamp        4        小1970-2038          不受

  4.常见约束

     （1）含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确性和可靠性

分类：六大约束

    not null ：非空，用于保证该字段的值不能为空
                    比如姓名、学号等
    defaullt ：默认，保证该字段有默认值
                    比如性别
    primary key：主键 ，用于保证该字段的值具有唯一性，并且非空
                            比如学号，员工编号
    unique：唯一性，用于保证该字段的值具有唯一性，可以为空。
                    比如座位号
    check： 检查,[mysql不支持]
    
    foreign key： 外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。

    （2）添加约束的时机：
            1.创建表时
            2.修改表时
    （3）约束的添加分类：
         列级约束：
        六大约束都可以支持，但外键约束没有效果
        
    表级约束：
        除了非空、默认，其他的都支持
        
        
主键和唯一的大对比
                  

1.                                         主键                                                                   唯一
2. 保证唯一性                         √                                                                          √ 
3. 是否允许为空                       X                                                                        √     
4.  一个表可以有多少个         至多1个                                                可以有多个唯一性的列
5.  是否允许组合   primary key(列名，列名)组合成一个主键                    允许，不推荐
6.                                 允许 不推荐

外键：
    1、要求在从表设置的外键关系
    2.从表的外键列的类型和主要的关联列的类型要去一致或兼容，名称无要求
    3.要求主表的关联列必须是一个key
    4.插入数据时，先插入主表，在插入从表
      删除数据时，先删除从表，再删除主表

CREATE TABLE 表名(
    字段名 字段类型 列级约束
    表及约束

);

（4）创建表时添加约束
添加列级约束

语法：
直接在字段名和类型后面追加约束的类型即可
 

#查看good 表中所有的索引，包括主键、外键、唯一
SHOW INDEX FROM good;

（5）添加表级约束

语法：各个字段的最下面
【constraint 约束名】约束类型（字段名）

CREATE TABLE good(
    id INT,
    stuname VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorid INT,
    
    CONSTRAINT pk PRIMARY KEY(id),#主键
    CONSTRAINT uq UNIQUE(seat),#唯一键
    CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
    
);

通用的写法
CREATE TABLE IF NOT EXISTS good (
    id INT PRIMARY KEY,#主键
    stuName VARCHAR(20) NOT NULL,#非空
    gender CHAR(1) CHECK (gender='男' OR gender = '女'),#检查
    seat INT UNIQUE,#唯一
    age INT DEFAULT 18,#默认
    majorID INT,
    
    CONSTRAINT fk_good_major FOREIGN KEY (majorID) REFERENCES major(id) 

);

（6）添加唯一
①列级约束
ALTER TABLE good MODIFY COLUMN seat INT UNIQUE;

②表级约束
ALTER TABLE good ADD UNIQUE (seat);

（7）添加外键
ALTER TABLE good ADD CONSTRAINT fk_good_major FOREIGN KEY (majorID) REFERENCES major(id);

（8）修改表时删除约束

①删除非空约束
ALTER TABLE good MODIFY COLUMN stuName VARCHAR(20) NULL;
 
②删除默认约束
 ALTER TABLE good MODIFY COLUMN age INT;
 
 ③删除主键
 ALTER TABLE good DROP PRIMARY KEY;
 
 ④删除唯一
 ALTER TABLE good DROP INDEX seat;
 
 ⑤删除外键
 ALTER TABLE good DROP FOREIGN KEY;
 

5.标识列

AUTO_INCREMENT,
又称为自增长列
（1）含义：可以不用手动的插入值，系统提供默认的序列值

（2）特点：
    1.标识列可以和拥有key的搭配 比如主键和unique
    2.一个表至多有一个标识列
    3.标识列的类型只能是数值型
    4.标识列可以通过
      SET AUTO_INCREMENT = 3;设置布长
      也可以手动插入起始值
 

例子：
   创建表时设置标识列
TRUNCATE TABLE jyp;

CREATE TABLE IF NOT EXISTS jyp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR (20)

);

INSERT INTO jyp VALUES (NULL,'john');
INSERT INTO jyp (NAME) VALUES ('john');

可以设置每次增加的大小
SET AUTO_INCREMENT = 3;

（3）修改表时设置标识列

ALTER TABLE jyp MODIFY COLUMN id IF PRIMARY KEY AUTO_INCREMENT;

（4）
ALTER TABLE jyp MODIFY COLUMN id INT;