MYSQL-SQLZOO

最新推荐文章于 2024-11-06 11:25:59 发布

ZDbiger

最新推荐文章于 2024-11-06 11:25:59 发布

阅读量95

点赞数

分类专栏： SQL 文章标签： mysql

本文链接：https://blog.csdn.net/m0_57485079/article/details/120629240

版权

SQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

本文介绍了在SQL中查找非ASCII字符的语法，使用正则表达式和NOTHEX。还探讨了如何处理单引号，子查询来找到最大或最小值，以及各种JOIN操作，如在JulieAndrews电影中的主演，演员的主演角色数量，以及与特定演员合作过的人。此外，提到了ORDERBY的奇妙用法和自联结示例。

摘要由CSDN通过智能技术生成

非ASCII字符的查找

查找非 ASCII 字符的语法如下：
SELECT * FROM yourTableName WHERE NOT HEX(yourColumnName) REGEXP '^([0-7][0-
9A-F])*$';

当目标元素中有‘’“”如何查询

Find all details of the prize won by EUGENE O'NEILL

Escaping single quotes

You can't put a single quote in a quote string directly. You can use two single quotes within a quoted string.可以用双引号~

select *
from nobel
where winner='EUGENE O\'NEILL';
#用\斜杠也可以欧 ps:借鉴于sql sever

IN 也可以用来表示真假10

The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1.

将某个字段前置OR后缀

将某个字段放在最前面/最后面

两种方法

一、 ORDER BY + IF() 条件排序

二、FIELD()函数

子查询求最大OR最小

To gain an absurdly detailed view of one insignificant feature of the language, read on.

We can use the word ALL to allow >= or > or < or <=to act over a list. For example, you can find the largest country in the world, by population with this query:
SELECT name
  FROM world
 WHERE population >= ALL(SELECT population
                           FROM world
                          WHERE population>0)
You need the condition population>0 in the sub-query as some countries have null for population.

或者你直接加一个is not null也行 :p

More JOIN operations - SQLZOO 第12题 Lead actor in Julie Andrews movies

我居然用循环做了而不是连结。。。

More JOIN operations - SQLZOO 第13题 Actors with 15 leading roles

More JOIN operations - SQLZOO 第13题我居然又用循环做了，但这次一直错，球球你用连结做好不。。。

select name
from actor
where id in(
select actorid
from casting
group by actorid
having count(ord)>=15)
order by name;

More JOIN operations - SQLZOO 第15题LIst all the people who have worked with 'Art Garfunkel'

select name
from actor
join casting
on actor.id=casting.actorid
where movieid in(
select movieid
from actor
join casting
on actor.id=casting.actorid
group by movieid,name
having name='Art Garfunkel')
and name<>'Art Garfunkel'

我还是觉得自己做的有点复杂了，而且有点凑答案的嫌疑。。。

一个奇妙用法 order by 1 (select中column的列数）

sql语句 order by 2_ai932820942的博客-CSDN博客

Self join - SQLZOO 第6题

还挺有意思的，先A1与A2自联结再分别连结相同的表B1和B2。

Self join - SQLZOO 第9题

说实话，我不太李姐为什么下面这个是错的（左）

select s1.name,r2.company,r2.num
from route r1 join route r2 
on (r1.num=r2.num and r1.company=r2.company)
join stops s1 on s1.id=r1.stop
join stops s2 on s2.id=r2.stop
where s2.name='Craiglockhart'

正确：（右）

select s2.name,r2.company,r2.num
from route r1 join route r2 
on (r1.num=r2.num and r1.company=r2.company)
join stops s1 on s1.id=r1.stop
join stops s2 on s2.id=r2.stop
where s1.name='Craiglockhart'

这两个s1,s2在我眼里是一模一样的啊，但答案可以说是大相径庭