[Huang ah code] Introduction to MySQL - 3. I use select *, and the boss directly rushed me home by train, but I still bought a station ticket

Hello everyone ！ I'm yellow , Learned to DDL Have you finished ？ Let's learn the basic query syntax today , I've seen many programmers in outsourcing organizations who can do everything select *, Then the project ran for a period of time and basically couldn't run , Ask and answer ： The server Insufficient configuration , Add money ！ good heavens , I can't believe this. , Learning 【 Dog head protection 】 If you are maintaining your company's projects , The boss is a little skilled , Do you believe he will hit you ？

Okay , Don't talk nonsense , Let's start with the course catalogue ：

1. SELECT The basic syntax of the query ;
2. How to sort and retrieve data ;
3. When to use SELECT*, How to improve SELECT The query efficiency ？

SELECT The basic syntax of the query

SELECT It can help us query data from one table or multiple tables . We know that a data table consists of columns （ Field name ） And line （ data row ） Composed of , We want to return the data rows that meet the conditions , It needs to be in SELECT Add the column name we want to query , It can be a column , It can also be multiple columns . If you don't know what all the column names have , You can also retrieve all columns .

1、 Retrieve all columns 【 you ： good heavens , So simple , My parents will say I'm smart when they see it 】

select * from user_info;

Na , Didn't you come out ？

2、 Retrieve a single column 【 you ： It's too simple , Look down on how smart I am ？】

select user_name from user_info;

Huang ah code ： but , Your result seems to be in the wrong order , Zhang San shouldn't be at the top .

you ： Uh huh , I don't care , Just get the result .

Huang ah code ： Crack , Crack , Honestly show me the last .

3、 names , To say it is easy to understand is to give it a nickname , For example, your nickname is two dogs , Oh , incorrect , It's pretty 、 Pretty girl

select user_name as u_name from user_info;

you ： Ah size , I am lazy , I don't want to write so much code ;

Huang ah code ： Crack , I know you don't want to write so much , hold as Don't you write two letters less if you remove them ？

Huang ah code ： Is the result the same ？

you ： It's the same , but , however , Can you not hit me next time ？

Huang ah code ： Fighting is pro , Scolding is love , It's uncomfortable not to fight or scold .

you ： good heavens , When I learn something , Let you know what is human love ！

4、 Query constant

SELECT The query can also query constants . Right , Is in the SELECT Add a fixed constant column to the query result . The value of this column is specified by us , Not dynamically extracted from the data table . You might ask why we have to query constants ？SQL Medium SELECT Grammar does provide this function , Generally speaking, we only query data from one table , There is usually no need to add a fixed constant column , But if we want to integrate different data sources , Use the constant sequence as the marker of this table , You need to query constants .

For example, we want to be in user_name Add a column before ‘u_name’ Name of representative , We could write it this way ：

select ' The name of the character ' as u_name,user_name from user_info;

you ： Ah size , Writing like this is a bit like taking off your pants and farting , At first glance, I thought it was the name of the character ;

Huang ah code ： Crack , Crack , Crack , What did you say? ？ Don't you think , I want me to feel , I think this is teaching you something , This three Zhanghong gives you a memory .

But remember , If you change the name of the character to a number , There is no need to add single quotation marks , Directly

you ：666, Ah size is awesome ;

Huang ah code ： Crack , I want you to praise , I'm good. I don't know ？

5、 Remove duplicate lines , such as ：

There are obviously two Zhang San , I want to get everything in this table user_name, And it's unique , What should I do ？

you ： Ah size , That's true. , That's true. ,distinct！distinct！;

Huang ah code ： Who let you say , You will make me lose face , Crack ！

select distinct user_name from user_info;

Here are two points to note ：

DISTINCT Need to precede all column names , If written SELECT user_age, DISTINCT user_name FROM user_info Will report a mistake .

DISTINCT In fact, it is to de duplicate the combination of all the following column names ,

How to sort and retrieve data

The most common way is as follows ：

select * from user_info ORDER BY user_id asc;

you ： Ah size , Is that ok asc Get rid of ;

Huang ah code ： Crack , You are finally a little enlightened , Yes indeed .

you ： Ah size , I'm right , Why did you hit me ;

I ： sorry , It's absolutely easy , Accustomed to the , Crack ;

you ： Cover your face , I dare not to be angry .

Use ORDER BY Clause has the following points to master ：

Sort column names ：ORDER BY It can be followed by one or more column names , If there are multiple column names to sort , It will be sorted first according to the next first column , When the values in the first column are the same , Then sort according to the second column , And so on .

Sort order ：ORDER BY The sorting rules can be indicated later ,ASC Represents an incremental sort ,DESC Represents descending sort . If no sorting rule is specified , By default, it follows ASC Incremental sort . It's easy for us to understand ORDER BY Sorting rules for numeric type fields , But if the sort field type is text data , Need reference database The setting method of , Only in this way can we judge A Is in B Before , Still B after . For example, use MySQL Set to... When creating the field BINARY attribute , It means case sensitive .

Sort non selected columns ：ORDER BY You can use non selection columns for sorting , So even in SELECT This column name is not followed , You can also put ORDER BY Sort later .

ORDER BY The location of ：ORDER BY Usually located in SELECT The last clause of the statement , Otherwise, an error will be reported .

as for WHERE ... GROUP BY ... HAVING .. We are learning later , Huang ARMA is afraid that you will have indigestion if you eat too much , It's me then .

When to use SELECT*, How to improve SELECT The query efficiency ？

If we just practice , Or explore the data table , So it can be used SELECT * Of . Its query efficiency is not much different from that of writing out all the column names and then querying . This will facilitate your overall understanding of the data table . But in the production environment , It is not recommended that you use SELECT * The query , If you want to force 【 Chaoshan dialect ： Hard love 】 There's no way out. , I suggest you go back to the furnace and rebuild .

Okay , Today's lesson learned here , Leave a message if you have questions , We'll see you next time ！

Leave a question at the end , Several appear in this article “ Crack ”, If you count wrong, learn it again ！

I'm yellow , Code of codeword , refund ... refund ... refund ... the ！