sql小测试 - 增删改查

创建DB

1. 创建DB：test_db

create database test_db;

创建表格

2. 创建两张测试表：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';

新增数据

3. 向表里插入几条数据

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);

展示数据

4. 显示表里的数据

select * from test_v1_tab;

select * from test_v2_tab;

修改数据

5. 添加列：向tab 2 表新的列address
   alter table test_v2_tab add address varchar(256);
   

6. 更新表里的值：将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';

5. 删除一行：删除年纪大于29的商品
   delete from test_v2_tab where age > 29;;