sqlite 如何添加新列并用随机值填充?

q5lcpyga  于 2022-11-24  发布在  SQLite
关注(0)|答案(1)|浏览(197)

如何向现有表中添加列并插入随机正整数?

SELECT id, views, name
FROM buysell_product
WHERE views >= 30
ORDER BY views DESC

数据来源:
| 标识符|检视|姓名|
| - -|- -|- -|
| 三个|七十二|优秀的现代creta sx医生与财务使用|
| 2个|四十一|卓越2013款福特ecosport太空车pushstar(福特)|
| 五个|三十九|iPhone 11 128 gb含增值税和保险|
| 九个|三十七|大众波罗1.2 GT AMT 2017款|
| 五十七|三十五|iPhone 13和14|
| 四十四|三十四|Usupso按摩防滑拖鞋-尺寸40/41|
我想添加列random_views

idv4meu8

idv4meu81#

带有表buysell_product的原始SQLite数据库

SELECT id, views, name
FROM buysell_product
WHERE views >= 30
ORDER BY views DESC

输出

id  views   name
3   72  Excellent Hyundai creta 
2   41  Excellent 2013 ford 
5   39  iPhone 11 128gb 
9   37  Volkswagen Polo 1.2 GT AMT 2017
57  35  Iphone 13 and 14
44  34  Usupso Massage Anti Skid Slippers

添加新列对表使用ALTER方法

使用ADD COLUMN添加名为random_views的新列

ALTER TABLE buysell_product 
ADD COLUMN random_views;

现在使用UPDATE更新表
SET来设置新列的值,
I相加的值是1到100之间的正整数

UPDATE buysell_product 
SET random_views = abs(random()) % (100) + 1

现在查看更新的表

SELECT id, views, random_views, name
FROM buysell_product
WHERE random_views >= 50
ORDER BY random_views DESC

输出

id  page_views  random_views    name
54  7   98  Fully Furnished Office Space Available
8   19  97  Nissan magnite
47  28  92  Ortega Guitars Ukulele, Right - Rufire
9   37  91  Volkswagen Polo 1.2 GT AMT 2017
53  12  91  Scorpio
51  19  86  Thrift Earrings
44  34  79  Usupso Massage Anti Skid Slippers
7   12  77  Excellent Hyundai creta 
56  6   75  Samsung S22
46  13  70  Meditating Smoke Ganesha Fountain
60  24  70  Headphone
58  11  68  Vector X exercise ball
5   39  67  iPhone 11 128gb 
67  15  67  Universal Touch Screen Capacitive Stylus

相关问题