sql小测试 - 增删改查_测试sql是否包含删除-优快云博客

本文展示了如何在MySQL中创建数据库和表，包括向表中插入数据、查询表的内容、添加新列、更新记录以及删除特定条件的数据行。

创建DB

创建DB：test_db

create database test_db;

创建表格

创建两张测试表：test_v1_tab、test_v2_tab

create table test_v1_tab(
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) COLLATE utf8mb4_unicode_ci ,
  `sex` varchar(256) COLLATE utf8mb4_unicode_ci,
  `country` varchar(256) COLLATE utf8mb4_unicode_ci,
  `dt` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=105445 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='test';

create table test_v2_tab(
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `age` bigint(20) unsigned NOT NULL,
  `extinfo` varchar(256) COLLATE utf8mb4_unicode_ci,
  `region` varchar(256) COLLATE utf8mb4_unicode_ci,
  `dt` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=105445 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='test';

新增数据

向表里插入几条数据

insert into test_v1_tab (id, name, sex, country, dt) value
(1, 'aaa', 'F', 'CN', 20220101),
(2, 'aaa', 'F', 'SG', 20220101),
(3, 'bbb', 'M', 'CL', 20220101),
(4, 'ccc', 'F', 'BR', 20220102),
(5, 'ddd', 'M', 'TH', 20220102),
(6, 'eee', 'F', 'CO', 20220103);

insert into test_v2_tab (id, age, extinfo, region, dt) value
(1, 10, 'test21', 'CN', 20220103),
(2, 20, 'test22', 'SG', 20220101),
(3, 30, 'test23', 'CL', 20220102),
(4, 20, 'test24', 'BR', 20220102);
(9, 10, 'test21', 'CN', 20220103);

展示数据

显示表里的数据

select * from test_v1_tab;

在这里插入图片描述

select * from test_v2_tab;

在这里插入图片描述

修改数据

添加列：向tab 2 表新的列address
alter table test_v2_tab add address varchar(256);
更新表里的值：将tab2中的address赋值为shenzhen

update test_v2_tab set address = 'shenzen';

在这里插入图片描述

更新连表的值:
20岁的aaa address换成了beijing

update test_v2_tab as t join(
select t1.id, t1.name, t1.country from test_v1_tab as t1
left join test_v2_tab as t2 
on t1.id = t2.id where t1.name = 'aaa' and t2.age = 20) as temp
on t.id = temp.id
set t.address = 'beijing';