编程知识 cdmana.com

MySQL practice

 Recently, when I'm free, I have to brush mysql The topic is ,sqlzoo The voice of Zhihu is quite high , I went to do it , I feel like I'm not practicing enough …
 Specific table structure and so on or want to practice , Please move to the official website https://sqlzoo.net/
 What's important to practice :
1. Read the questions carefully 
2. I really didn't expect to see a hint ,eg:XX function 
3. Some questions may not be sql The sentence is wrong , It's an error caused by the wrong order of the display fields , Troubleshooting can see the difference between the execution result and the correct result .

 Here are some unexpected topics and knowledge points .
Q:  Find out the names of all the capitals and their countries , And the capital should have the name of the country . 

#concat Function can be used to combine two or more strings .
SELECT capital,name
  FROM world
WHERE  capital like concat('%',name, '%')
Q:"Monaco-Ville" It's the name of the merging country  "Monaco"  And extensions "-Ville".
 Show country name , And its extension , For example, the capital is an extension of the name of the country .
 You can use SQL Functions  REPLACE  or  MID.

#REPLACE ( ' To be searched string' , ' To be found string' , ' Replace string' )
select name , replace(capital,name,'')
from world
where capital like concat(name,'_%')

#MID(column_name,start[,length])--column_name,start  Required 
# as follows sql Report errors :execute command denied to user 'scott'@'localhost' for routine 'gisq.LEN'
# When searching on the Internet, the problem of authority ? Why? 
select name , MID(capital,len(name)+1)
from world
where capital like concat(name,'_%')
Q: List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order. 

# Sorting multiple fields , Separated by commas , The default is asc
select winner,yr,subject from nobel 
where winner like 'Sir%' 
order by yr desc , winner asc
Q:Germany (population 80 million) has the largest population of the countries in Europe.
Austria (population 8.5 million) has 11% of the population of Germany.
Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

#concat function   Splicing 2 More than strings 
#ROUND function    The numeric field is rounded to the specified number of decimal places .eg:round( Numeric fields , Decimal digit )
select name , concat(round(population*100/(select population from world where name ='Germany'),0),'%') 
from world where continent ='Europe'
Q: Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values) 

#ALL The operator is a logical operator , It compares a single value to a single column set of values returned by a subquery . Must start with a comparison operator 
SELECT GDP 
  FROM world
 WHERE GDP >= ALL(SELECT GDP 
                           FROM world
                          WHERE GDP >0 and continent='Europe')
Q:List each continent and the name of the country that comes first alphabetically.

# LIMIT Clause is used to restrict by  SELECT  The number of data returned by statements .
#limit  and  offset  The difference between  :https://blog.csdn.net/cnwyt/article/details/81945663
#limit y  A clause means :  Read  y  Data 
#limit x, y  A clause means :  skip  x  Data , Read  y  Data 
#limit y offset x  A clause means :  skip  x  Data , Read  y  Data 

select continent,name from world w1
where w1.name=
(select w2.name from world w2 where w2.continent=w1.continent order by name limit 1)
Q:Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.

#ALL function 
select name,continent from world w1
where w1.population > ALL(
select 3*population from world w2
where w2.continent = w1.continent and w1.name <> w2.name
)

 The problem was wrong at the beginning ... Later I read the official website about group by  and  having  An introduction to :

GROUP BY and HAVING
By including a GROUP BY clause functions such as SUM and COUNT are applied to groups of items sharing values. When you specify GROUP BY continent the result is that you get only one row for each different value of continent. All the other columns must be "aggregated" by one of SUM, COUNT ...

The HAVING clause allows use to filter the groups which are displayed. The WHERE clause filters rows before the aggregation, the HAVING clause filters after the aggregation.( a key , mean where Clauses are executed in order before the aggregate function ,having The order in which clauses are executed is after the aggregate function )

If a ORDER BY clause is included we can refer to columns by their position.

 add :group by  Multiple fields , Field order has no effect on the query result data , It's just that the order of the result set is different .eg:

 in addition  Insert in  You know  See the picture :( Infringement and deletion )

Q: Use COALESCE to print the mobile number. Use the number '07986 444 2266' if there is no number given. Show teacher name and mobile number or '07986 444 2266'

#COALESCE function : The return includes expression The first nonempty expression of all the parameters in .
#COALESCE(expression,value1,……):expression If it is empty, judge value1 Is it empty .


select name,COALESCE(mobile,'07986 444 2266')
from teacher 
Q:Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2, show 'Art' if the teacher's dept is 3 and 'None' otherwise.

#case

select name ,
case when dept =1 or dept =2 then 'Sci'
     when dept =3 then 'Art'
else 'None'
end
from teacher
Q:Find the routes involving two buses that can go from Craiglockhart to Lochend.
Show the bus no. and company for the first bus, the name of the stop for the transfer,
and the bus no. and company for the second bus.

Hint
Self-join twice to find buses that visit Craiglockhart and Lochend, then join those on matching stops.

# The general meaning of the title is Craiglockhart  To  Lochend  I need a transfer , List all the options : The first bus number 、 The bus company 、 Transfer station 、 The second bus number 、 The bus company .

SELECT m.num,m.company,name,n.num,n.company
FROM (SELECT a.num,a.company,b.stop FROM route a 
JOIN route b ON a.company=b.company AND a.num=b.num AND a.stop!=b.stop WHERE a.stop=(SELECT id FROM stops WHERE name='Craiglockhart')) AS m
JOIN (SELECT d.num,d.company,c.stop FROM route c JOIN route d ON c.company=d.company AND c.num=d.num AND c.stop!=d.stop WHERE d.stop=(SELECT id FROM stops WHERE name='Lochend')) AS n
ON  m.stop=n.stop
JOIN stops 
ON m.stop=stops.id
ORDER BY m.num,stops.name,n.num;
 Now I still don't know much about it :
1.join  No ?left join  Just gave ?

2.  List 1978 The number of movie titles and roles in the first film of the year , According to this number, arrange at least from the most . 
 Probably compared the answers , Discovery is the problem of ranking results , How to solve ?
# my sql( No !!)
select title,COUNT(actorid) as num  from casting
join movie on movieid = id and yr = 1978
group by title 
order by num desc

 follow-up : Change the title to English , I finally know what the problem is ……
Q:List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

# After that !
select title,COUNT(actorid) as num  from casting
join movie on movieid = id and yr = 1978
group by title 
order by num desc,title

 

 

 

版权声明
本文为[Luo Luo Luo Luo Luo]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201225011438631m.html

Scroll to Top