SQL——DML对表中数据的操作

# 创建数据库
create database if not exists db_BigData
default character set gb2312
default collate gb2312_chinese_ci;
# 创建表
create table if not exists db_BigData.stu (id      int         auto_increment      primary key         comment '主键ID',name    varchar(20)                                         comment '姓名',idnum   int         unique              not null            comment '身份证号',gender  int                             not null            comment '1.男 2.女',phone   int                                                 comment '手机号',email   varchar(30) unique                                  comment '邮箱',prof    varchar(20)                                         comment '职业',age     int                             not null            comment '年龄'
)comment '学生基本信息表';use db_BigData;
show tables ;
# 查询表
select * from stu;
# 插入表
insert into stu(name, idnum, gender, phone, email, prof, age) values('绫波零', '20220111',2,00001,'[e-mail]','驾驶员',14),('明日香', '20220222',2,00002,'[e-mail]','驾驶员',15),('雷姆', '202203333',2,00003,'[e-mail]','女仆',17),('修比', '202204444',2,00004,'[e-mail]','战士',631),('三笠', '202205555',2,00005,'[e-mail]','士兵',18),('白', '2022066666',2,00006,'[e-mail]','玩家',12),('伊雷娜', '20220777',2,00007,'[e-mail]','魔女',17),('法夫塔', '20220888',2,00008,'[e-mail]','深渊公主',10),('娜娜奇', '20220999',2,00009,'[e-mail]','探窟家',11),('我妻由乃', '20221000',2,00010,'[e-mail]','半神',16);# 修改表的默认字符集 ALTER TABLE tb_user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
# 修改列的字符集
ALTER TABLE stu MODIFY name VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;insert into stu values (null , '柊舞缇娜', '20221001',2,00011,'[e-mail]','反派',14);# 更新数据
update db_BigData.stu set prof = '魔法少女-反派' where name = '柊舞缇娜';
update db_BigData.stu set prof = '反派IL',phone = 100012 where name = '柊舞缇娜' and id = 11;
ALTER TABLE stu modify phone int(20) unique ;# 删除数据
delete from stu where id = 1;
delete from stu where id <= 5;# 清空整张表
# delete：用于删除表中的数据，一行一行删除
delete from stu;
# truncate：用于清空整张表的数据(更高效)
truncate table stu;# 区别
# delete：DML命令，一条一条删除
# truncate：DDL命令，类似于将整张表删除，然后重新创建一张一样的空表