SQL基础——DML语句

最新推荐文章于 2024-07-18 15:45:25 发布

Ashenlex

最新推荐文章于 2024-07-18 15:45:25 发布

阅读量533

点赞数 9

文章标签： sql oracle 数据库

本文链接：https://blog.csdn.net/Ashenlex/article/details/136887316

版权

DML语句

insert into 表名 values(字段值1, 字段值2, .....),(字段值1, 字段值2, .....), ....;

insert into 表名(字段1, 字段2,...) values(字段值1, 字段值2,....), (字段值1, 字段值2,....),.....;

DML之插入语句：

-- DML语言: 数据操作语言, 对于数据库进行增 删 改的操作  (可以理解为数据库的写入操作)
-- DML操作--增
-- 关键字  insert 数据插入时使用的关键字

-- 数据准备
USE test_db;
-- 创建数据表
CREATE TABLE student
(
    id     INT,
    name   VARCHAR(30),
    gender VARCHAR(20)
);

-- 1. 一次插入一条数据记录, 不指定被插入的字段
-- 格式: insert into 表名 value(字段值1, 字段值2, .....);
-- INSERT INTO student VALUE (1, '小明', '男');
-- Incorrect string value: '\xE5\xB0\x8F\xE6\x98\x8E' for column 'name' at row 1
-- 此时报错的原因是 test_db数据库, 没有指定字符集, 默认是latin1编码集, 不能保存中文数据.
INSERT INTO
    student VALUE (1, 'xiaoming', 'man');
-- 如果插入数据为英文字母,则可以插入成功

-- 如果想要插入中文信息 , 可以在编码集为utf8的数据库中插入
-- 数据准备
USE test_db_utf8;
-- 创建数据表
CREATE TABLE student
(
    id     INT,
    name   VARCHAR(30),
    gender VARCHAR(20)
);

-- 插入数据, 内部含有中文记录
INSERT INTO
    student VALUE (1, '小明', '男');

-- 这种插入方式, 字段值的数量要与表中字段的数量完全一致, 不能多也不能少
-- Column count doesn't match value count at row 1
# INSERT INTO student VALUE (1, '小明', '男', '2');
# INSERT INTO student VALUE (1, '小明');

-- 2. 一次插入一条数据记录,指定被插入的字段名, 未指定的默认赋值为null
-- 格式: insert into 表名(字段1, 字段2,...) value(字段值1, 字段值2,....);
INSERT INTO
    student(id, name) VALUE (2, '小芳');

-- 这种方式插入, 指定的字段数量, 要与value中字段值的数量完全一致, 否则会报错
-- Column count doesn't match value count at row 1
# INSERT INTO student(id, name) VALUE (2,'小芳', '男');
# INSERT INTO student(id, name) VALUE (2);

-- 3. 一次插入多条数据, 不指定被插入的字段
-- 格式: insert into 表名 values(字段值1, 字段值2, .....),(字段值1, 字段值2, .....), ....;
INSERT INTO
    student
VALUES
    (3, '小智', '男'),
    (4, '小刚', '男'),
    (5, '小霞', '女');

-- 4. 一次插入多条数据记录, 指定被插入的字段, 未指定的字段填充null (空值的意思)
-- 格式: insert into 表名(字段1, 字段2,...) values(字段值1, 字段值2,....), (字段值1, 字段值2,....),.....;
INSERT INTO
    student(id, name)
VALUES
    (6, '鲁智深'),
    (7, '唐伯虎'),
    (8, '蔡徐坤');

-- 注意事项:
-- 1. 插入数据时, 字段值数量 要与字段数量相匹配否则会报错
-- Column count doesn't match value count at row 1

-- 2. 一般我们在开发中共仅使用values一种形式, 因为values 既可以插入一条数据, 又可以插入多条数据.
INSERT INTO
    student
VALUES
    (9, '张三', '男');

-- 3. 插入数据时, 数据值和字段顺序必须匹配, 否则可能出现错误

-- 插入顺序错误时,会进行自动数据类型转换, 转换成功则插入错误数据, 转换失败则报错
-- Incorrect integer value: '张三' for column 'id' at row 1
-- INSERT INTO student VALUES ('张三', 10, '男');
-- INSERT INTO student VALUES (10, '男', '张三');

-- null 是空值, 证明没有被赋值, 而不是赋值为0的意思

DML之数据的修改和删除操作

update 表名 set 字段名 = 值 [where 筛选条件];

delete from 表名 where [筛选条件];

USE test_db_utf8;
-- 1. 修改数据记录
-- 关键字: update 修改已有数据记录中的指定字段的值.
-- 格式: update 表名 set 字段名 = 值 [where 筛选条件];
-- 需求1: 将id = 4的学员 性别修改为女
UPDATE student
SET
    gender = '女'
WHERE
    id = 4;

-- 需求2: 将id = 5的学员 性别修改为男, 姓名修改为李大;
UPDATE student
SET
    name   = '李大',
    gender = '男'
WHERE
    id = 5;

-- 需求3: 将所有性别为男的学员的性别修改为女
UPDATE student
SET
    gender = '女'
WHERE
    gender = '男';

-- 注意1: 修改数据记录时, 被修改的字段的值必须与原数据的数据类型一致
-- ncorrect integer value: '男' for column 'id' at row
UPDATE student
SET
    id = '男'
WHERE
    id = 1;

-- 注意2: 在修改时 一般都有where 如果没有则对于所有的数据记录进行修改(慎用)
UPDATE student
SET
    gender = '男';

-- 2. 删除数据记录
-- delete , 删除表中的数据记录,  记住不是删除表.
-- 格式: delete from 表名  where [筛选条件];
-- 需求: 删除表中id = 5的数据记录
DELETE
FROM
    student
WHERE
    id = 5;

-- 需求: 删除表中id=6的gender字段的值
UPDATE student
SET
    gender = NULL
WHERE
    id = 6;

-- 注意1: delete 需要一次删除整条数据记录, 如果需要删除的为某一个字段的值 依然需要使用update

-- 注意2: 如果想删除所有的数据记录, 可以不写where 或者直接给该where条件一个恒成立的条件即可
DELETE
FROM
    student;

DELETE
FROM
    student
WHERE
    1 = 1;

-- 清空表数据的另外一种方法 truncate
TRUNCATE TABLE student;

primary key 主键约束

主键约束,就是规定该列数据,既不重复,也不为空.

为什么要定义主键约束呢?

每一条主键都对应唯一的一条数据,每一条数据也有唯一一个主键相对应.

此时我们查找数据时,只要查找到主键信息,就可以获取该条记录的全部能容, 可以提升查询效率

什么样的字段可以作为主键出现呢?

主键一般都是无意义的数据.
主键一般不会自己手动输入,而是借助计算机进行自增
主键一般为数值型(整数)
主键不会修改
每一张表都建议有一个主键, 且只能有一个主键约束字段

-- 0.使用数据库
USE test_db_utf8;

-- 1. 数据准备, 在创建数据表时,增加主键约束
CREATE TABLE person
(
    id     INT PRIMARY KEY,
    name   VARCHAR(30),
    gender VARCHAR(20)
);

-- 使用desc 查看字段详情, 里边详细说明了哪个字段为主键
DESC person;

-- 2. 每张表, 建议有, 且仅能有一个主键字段.
-- 报错: Multiple primary key defined
-- 提示我们定义了重复主键, 此时,每张表最多有一个主键.
CREATE TABLE person1
(
    id     INT PRIMARY KEY,
    name   VARCHAR(30) PRIMARY KEY,
    gender VARCHAR(20)
);

-- 3. 主键可以修饰各种数据类型,  不一定时整型,但是官方建议我们使用整数类型数据, 作为主键.
CREATE TABLE person2
(
    id     INT,
    name   VARCHAR(30) PRIMARY KEY,
    gender VARCHAR(20)
);

DESC person2;

-- 4. 主键字段中插入的数据值不能为空
-- 4.1 插入一个空值数据, 无法存储
-- 报错: Column 'id' cannot be null
INSERT INTO
    person
VALUES
    (NULL, '小明', '男');

-- 4.2 插入数据时如果主键字段你没有默认值, 则不能不给主键字段赋值
-- Field 'id' doesn't have a default value
INSERT INTO
    person(name, gender)
VALUES
    ('小明', '男');

-- 5. 主键字段中插入不能插入重复的值
-- Duplicate entry '1' for key 'PRIMARY'
INSERT INTO
    person
VALUES
    (1, '小明', '男');
INSERT INTO
    person
VALUES
    (1, '小芳', '女');

-- 6. 创建表后添加主键约束 (了解)
-- 创建没有主键约束的表
CREATE TABLE person3
(
    id     INT,
    name   VARCHAR(30),
    gender VARCHAR(20)
);

-- 建表后添加主键约束
ALTER TABLE person3 ADD PRIMARY KEY (id);
-- 查看主键约束是否添加完成
DESC person3;

-- 7. 删除主键约束 (了解)
-- 需求: 删除person2表中的主键约束
ALTER TABLE person2 DROP PRIMARY KEY ;
-- 查看主键约束是否删除完成
-- 注意: 删除主键约束后, 非空约束依然存在.

auto increment自动增长

自动增长并不是一个约束,而是一个扩展extra

自动增长的主要作用,就在不传值,或者传值为null 的时候,实现记录的自增赋值工作

自动增长只能修饰键,且只能修饰整型

下方表中, key列有值,就是一个键, 否则就不是键. (键的最基本要求: 不能重复)

-- 自动增长 : 是一个扩展功能, 在我们给数据传值时,如果该字段是自动增长字段,则会在不传值, 传0 或null 时自增后自动赋值
-- 关键字  auto_increment
-- 0. 使用数据库
USE test_db_utf8;

-- 1. 创建一个数据表, 给字段增加自动增长约束
CREATE TABLE heros
(
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(20),
    skill VARCHAR(30)
);

-- 查看表结构, 验证自动增长是否添加成功
DESC heros;

-- 2. 添加自动增长的字段必须为键(在查询字段详情时, key列有值)
-- 报错: there can be only one auto column and it must be defined as a key
CREATE TABLE heros1
(
    id    INT AUTO_INCREMENT,
    name  VARCHAR(20),
    skill VARCHAR(30)
);

-- 3. 添加自动增长字段的列 必须为整数类型.
-- 报错:Incorrect column specifier for column 'name'
CREATE TABLE hero2
(
    id    INT PRIMARY KEY,
    name  VARCHAR(30) AUTO_INCREMENT,
    skill VARCHAR(30)
);

-- 4. 插入数据时, 给自动增长列插入null将会自动增长
-- 数据从0开始自增
INSERT INTO heros VALUES (null, '梅超风', '九阴白骨爪');

-- 5. 插入数据时, 给自动增长列插入0将会自动增长
INSERT INTO heros VALUES (0, '虚竹', '天山折梅手');

-- 6. 插入数据时, 不给自动增长字段插入数据, 将会默认插入一个自动增长值
INSERT INTO heros(name, skill) VALUES ('乔峰', '降龙十八掌');

-- 7. 如果我插入的数据为指定数值, 则保存相关数据.
INSERT INTO heros VALUES (9, '杨过', '养雕');

-- 8. 再次触发自动增长机制, 则根据哪一个数字进行自增呢?
-- 自动增长机制: 在当前列的最大值基础上自增1 添加到数据列中,  如果表中无任何数据, 则默认从0开始自增.
INSERT INTO heros VALUES (0, '段誉', '六脉神剑');

我们向自增字段中添加null, 0 或者不添加数据,都会触发主键自增.

主键自增字段可以插入指定值,但是不能重复

主键自增的计算规则是在当前列最大值的基础上加1

自增只能修饰整型键.

主键自增扩展

-- 扩展: 有主键自增的数据列, 删除主键约束
-- 需求: 删除heros表中id列的主键约束
-- there can be only one auto column and it must be defined as a key
ALTER TABLE heros DROP PRIMARY KEY ;

-- 删除主键约束后, id列不再是键, 此时自动增长则修饰了一个非键列, 所以不允许修改.
-- 如果想要修改,需要先删除自增扩展, 再删除主键约束
ALTER TABLE heros CHANGE id id INT;

-- 查询表结构
DESC heros;
 
-- 删除主键约束
ALTER TABLE heros DROP PRIMARY KEY ;

-- 查询表结构
DESC heros;

not null 非空约束

非空约束修饰的字段不能为空

-- 非空约束 关键字: not null   此列数据,不能填写空值.
-- 0. 使用数据库
USE test_db_utf8;

-- 1. 建表时,添加非空约束
CREATE TABLE students (
    id INT PRIMARY KEY ,
    name VARCHAR(20) NOT NULL ,
    gender VARCHAR(20)
);

-- 2. 查看表结构, 查看所有的字段详情
DESC students;

-- 3. 非空约束修饰的字段, 不能存在null值
-- Column 'name' cannot be null
-- INSERT INTO students VALUES (1, null, '男');

-- Field 'name' doesn't have a default value
-- INSERT INTO students(id, gender) VALUES (1, '男');

-- 4. 非空约束的字段, 可以重复
INSERT INTO students VALUES (1, '小强', '男');
INSERT INTO students VALUES (2, '小强', '女');

-- 扩展: 删除非空约束的方法
ALTER TABLE students CHANGE name name VARCHAR(30);

-- 查看字段详情
DESC students;

unique 唯一约束

唯一约束修饰的字段,不能重复

-- 唯一约束 UNIQUE 在唯一约束修饰的列中, 不能存放重复值.
-- 0. 使用数据库
USE test_db_utf8;

-- 1. 创建数据表, 添加唯一约束
CREATE TABLE device (
    id INT PRIMARY KEY ,
    price DOUBLE ,
    description VARCHAR(20) UNIQUE
);

-- 2. 查看表的字段详情, 检验唯一约束添加成功
-- 唯一约束的列 也是一个键.
DESC device;

-- 3. 唯一约束不能插入重复的值
INSERT INTO  device VALUES (1, 20.8, '这个机器真便宜');
-- Duplicate entry '这个机器真便宜' for key 'description'
INSERT INTO  device VALUES (2, 6000000.4, '这个机器真便宜');

-- 4. 唯一约束可以插入空值, 且空值不计入重复数据中, 也就是可以有无数个空值.
INSERT INTO device VALUES (3, 12, NULL);
INSERT INTO device(id, price)  VALUES (4, 15);

default 默认值

-- 默认值不是一个约束条件, 不能限制我们插入数据.
-- 添加默认值后, 如果我们没有给该数据赋值,则自动使用默认值. 如果没有添加默认值, 则自动赋值为null
-- 关键字 : default
-- 0. 使用数据库
USE test_db_utf8;

-- 1. 在建表时添加默认值
CREATE TABLE student_info
(
    id     INT PRIMARY KEY,
    name   VARCHAR(30) DEFAULT '无名氏',
    gender VARCHAR(20)
);

-- 2. 查看字段详情, 验证默认值添加成功
DESC student_info;

-- 3. 当未给name字段赋值时, 默认使用其默认值进行填充
INSERT INTO
    student_info(id, gender)
VALUES
    (1, '男');

-- 4. 当给name赋值时,将会把用户传入的数据填充到该字段位置.
INSERT INTO
    student_info
VALUES
    (2, '小芳', '女');

-- 5. 填写0 或者 null 等 不会触发默认值.
INSERT INTO
    student_info
VALUES
    (3, NULL, '男');
INSERT INTO
    student_info
VALUES
    (4, 0, '男');

-- 自动增长本质上就是一个特殊规则的默认值.