当前位置:网站首页>Don't know how to learn MySQL? It's enough to finish the 50 questions of Niuke! (Part IV)
Don't know how to learn MySQL? It's enough to finish the 50 questions of Niuke! (Part IV)
2022-07-21 04:09:00 【18-year-old hates Java】
This content is divided into ten chapters , Five questions each time , Other chapters are on the homepage Mysql Column
Preface
Are you still worrying SQL Where to start , Or learned SQL I want to find a place to practice my hand ? What a coincidence , Recently, after work, I boarded Cattle guest , Found out Cattle guest I don't know when it went online SQL Will know The exercises of .
《SQL Will know 》 As MIT 、 Erie Reference textbooks of many universities such as neu University , From simple to deep SQL The basic concepts and grammar of . Involving data Sort 、 Filtering and grouping , And the watch 、 View 、 coupling 、 Subquery 、 The cursor 、 Stored procedures and triggers . Examples abound , Convenient access , It can be said that as a CRUD BOY/GIRL A must read list .
Just think about brushing it again , And then put yourself Brush problem To sum up , So here's today's article , I hope I can give a little help to my little friend in need
SQL16 Retrieve product name and description ( One )
describe
Products surface
prod_name | prod_desc |
---|---|
a0011 | usb |
a0019 | iphone13 |
b0019 | gucci t-shirts |
c0019 | gucci toy |
d0019 | lego toy |
problem
To write SQL sentence , from Products Retrieve the product name from the table (prod_name) And description (prod_desc), Returns only the description that contains toy Product name of the word
Sample results
Return product name and product description
prod_name | prod_desc |
---|---|
c0019 | gucci toy |
d0019 | lego toy |
Example
1 2 3 4 5 6 7 8 9 10 |
|
answer
Fuzzy query , The main product description contains toy
The word" , You need to return, and you need to use the fuzzy query keyword LIKE
Then there are wildcards , There is no clear explanation in the title toy
How many characters does it contain before and after , So use %
.
1 |
|
SQL17 Retrieve product name and description ( Two )
describe
Products surface
prod_name | prod_desc |
---|---|
a0011 | usb |
a0019 | iphone13 |
b0019 | gucci t-shirts |
c0019 | gucci toy |
d0019 | lego toy |
problem
To write SQL sentence , from Products Retrieve the product name from the table (prod_name) And description (prod_desc), Return only if... Does not appear in the description toy The word product , Finally according to the ” The product name “ Proceed with the result Sort .
Sample results
Return product name and product description
prod_name | prod_desc |
---|---|
a0011 | usb |
a0019 | iphone13 |
b0019 | gucci t-shirts |
Example
1 2 3 4 5 6 7 8 9 10 |
|
answer
This is the problem 16 The opposite of the question , Then we just have to be right 16 Just reverse the query statement of , Add a keyword NOT
that will do . Besides , You also need to press “ The product name ” For the result Sort , Use keywords ORDER BY
that will do . But we need to pay attention WHERE
and ORDER BY
Order of keywords .
1 |
|
SQL18 Retrieve product name and description ( 3、 ... and )
describe
Products surface
problem
To write SQL sentence , from Products Retrieve the product name from the table (prod_name) And description (prod_desc), Returns only those that appear simultaneously in the description toy and carrots Products . There are several ways to do this , But for this challenge , Please use AND And two LIKE Compare .
prod_name | prod_desc |
---|---|
a0011 | usb |
a0019 | iphone13 |
b0019 | gucci t-shirts |
c0019 | gucci toy |
d0019 | lego carrots toy |
Sample results
Return product name and product description
prod_name | prod_desc |
---|---|
d0019 | lego carrots toy |
Example
1 2 3 4 5 6 7 8 9 10 |
|
answer
The title clearly requires the use of LIKE
and AND
keyword , Then you can split the condition into two fuzzy queries , One is that the description contains toy
Products , One is that the description contains carrots
Products , Then use keywords AND
Just concatenate the two conditions .
1 |
|
SQL19 Retrieve product name and description ( Four )
describe
Products surface
prod_name | prod_desc |
---|---|
a0011 | usb |
a0019 | iphone13 |
b0019 | gucci t-shirts |
c0019 | gucci toy |
d0019 | lego toy carrots |
problem
To write SQL sentence , from Products Retrieve the product name from the table (prod_name) And description (prod_desc), Returns only those that appear simultaneously in the description in sequential order toy and carrots Products . Tips : You only need to use with three % The symbol of LIKE that will do .
Sample results
Return product name and product description
prod_name | prod_desc |
---|---|
d0019 | lego toy carrots |
Example
1 2 3 4 5 6 7 8 9 10 |
|
answer
Tips have been given in the title , To query the product description, it appears in sequence at the same time toy
and carrots
Products , But I didn't say the characters between them and toy
Front and carrots
The characters contained in the back , Then you need to use wildcards %
, Get the final result %toy%carrots%
.
1 |
|
SQL20 Alias
describe
A common use of aliases is to rename the column fields of the table in the retrieved results ( In order to meet specific reporting requirements or customer needs ). Have a watch Vendors On behalf of supplier information ,vend_id supplier id、vend_name Name of supplier 、vend_address Supplier address 、vend_city Supplier city .
vend_id | vend_name | vend_address | vend_city |
---|---|---|---|
a001 | tencent cloud | address1 | shenzhen |
a002 | huawei cloud | address2 | dongguan |
a003 | aliyun cloud | address3 | hangzhou |
a003 | netease cloud | address4 | guangzhou |
problem
To write SQL sentence , from Vendors Search for vend_id、vend_name、vend_address and vend_city, take vend_name Rename it to vname, take vend_city Rename it to vcity, take vend_address Rename it to vaddress, Ascending the results by supplier name Sort .
Sample results
return vend_id supplier id、vname Name of supplier 、vaddress Supplier address 、vcity Supplier city .
vend_id | vname | vaddress | vcity |
---|---|---|---|
a003 | aliyun cloud | address3 | hangzhou |
a002 | huawei cloud | address2 | dongguan |
a003 | netease cloud | address4 | guangzhou |
a001 | tencent cloud | address1 | shenzhen |
Example
1 2 3 4 5 6 7 8 9 10 |
|
answer
SQL in , To alias a column , You need keywords AS
, The format is as follows :
1 |
|
By aliasing the three fields in the title , And then use ORDER BY
keyword , The results are sorted in ascending order according to the supplier name column Sort .
1 |
|
Besides ,AS
In fact, you can't , You can also write in the following way , Just follow the alias after the column name , The effect is consistent with the above statement .
1 |
|
边栏推荐
- 解决错误:could not find ‘xxxTest‘
- Combined with the source code, see "cocos2dx-3.0 as I understand" - particle system
- Model thesis of urban planning and design
- One bite of Stream(5)
- BeanUtils.copyProperties()和JSONObject.parseObject()是什么拷贝类型(浅拷贝或者深拷贝)
- 使用优炫数据库时,如何查看系统是否支持某组件
- JS构造链表
- Topic of graduation thesis on Urban Rail Transit Engineering
- jenkins设置语言为中文
- Chapter003 FPGA learning PWM LED breathing lamp
猜你喜欢
【深度学习】使用yolov5对数据进行预标注
Unity实现人物移动和镜头跟随
学习记录十三
Model thesis of radio, film and television program production
"Dry goods experiment" Huawei DHCP + single arm routing experiment
JS构造二叉树
UGUI——Graphic
Pytorch realizes handwritten digit recognition | MNIST data set (fully connected neural network)
力扣打卡总结之动态规划
Mysql-CVE-2012-2122漏洞&Redis漏洞
随机推荐
Yolo target detection identification box does not display text labels (solved)
Understanding and using unity2d custom scriptable tiles (I) -- understanding the tilebase class
[Unity脚本优化] Optimizing garbage collection in Unity games
cocos2dx 引擎中的一些技巧
【STM32F103RCT6】CAN通信
Unity2D 自定义Scriptable Tiles的理解与使用(一)——了解TileBase类
随机数(random头文件)
数据库笔记
One bite of Stream(3)
好玩的猜数游戏(不是二分查找!四位数)
什麼是渲染管道,怎麼繪制3D物體
力扣总结之 “N”数之和
UGUI官方优化文档翻译
Model thesis of urban planning and design
Optimizing graphics rendering in Unity games
Ugui official optimization document translation
香蕉派 BPI-M5折腾记录(2)—— 编译u-boot
豆瓣评分9.4!跟邱锡鹏教授一起学「神经网络与深度学习」,7月19日开课
jupyter notebook 添加kernel的方法
Unity实现人物移动和镜头跟随