Advanced 1. Direct query

– single field in query table –
select sNo from student;

– single field in query table –
select sNo, sName from student;

– query all fields in the table
select * from student;

Note: which library to use when querying all fields

2. Query constant value

select 100; select 'male';

3. Query expression value

select 100%98;

4. Query function

select version();

5. Alias fields

1. Easy to understand
2. Easy to distinguish (If the fields to be queried have duplicate names, you can distinguish them)

Method 1

select 100%98 as result;
select sNo as Student number, sName as name from student; #Improve readability
 

Method 2

select sNo student number, sName name from student;

– in special cases, you want to make sNo the alias of out put –
select sNo as out put from student; – err because out is also a keyword
select sNo as 'out put' from student;

8. Re query all classes involved in the student table

select distinct deptment from student;

9. The '+' sign is used to query the student name and class into a field

C In language '+' Function of No:
1. Operator, both operands are numeric
2. Connector, as long as one operand is a string

Mysql Medium:
There is only one function: operator



select 100+90; # Both are numerical operation results 190
	select '123'+90; # One party is character type, trying to convert character type to numeric type
	                  # If the conversion is successful, continue the addition operation
	select 'dzj'+90;	# If the conversion fails, the character value is converted to 0 and then calculated
	select null+90;   # If one result is null, the operation result is null

Select concat (sname, depth) as class name from student;
concat is a function, which will be described later

Advanced 2. Condition query

Syntax:
	select Query list from Table name
	where Screening conditions;	

Classification:
	1, Filter by conditional expression
	Simple conditional operators:>  <  =  !=(<>)  >=  <=
		
	2, Filter by logical expression
	Action: used to join conditional expressions
		
	Logical expression:&&  ||  !
	 mysql:	and or  not
				 
	&&and and: Both conditions are true, The result is true, On the contrary false
	||or or: As long as one condition is true，The result is true，On the contrary false
	!or not: If the connection condition itself is false，The result is true，On the contrary false
		
		
	Three, fuzzy query
		like
		between and
		in
		is null

1, Filter by conditional expression

– screening students over 165 in height –

select * from student
where hight > 170;

– screen names and classes of students who are not online 131 –

select sName, deptment from student
where deptment != 'Network 131';

2, Filter by logical expression

– screen names and classes of students between 170-180 in height –

select sName, deptment from student
where hight >= 170    and	hight<=180;

– screen the information of students whose height is not between 170-180 or whose height is greater than 180 –

select * from student 
where not(hight >= 160 and hight <= 180) or hight > 180;

1. like

like
1. Generally used with wildcards
 Wildcard:
	% Any number of characters
	_ Any single character

– screen the information of students surnamed 'Wang' –

select * from student
where sName like '%king%';

– select the student information whose third digit is 1 and the tenth digit is 4 –

select * from student
where sNo like '__1______4%';  # If you encounter the filter conditions, there are_ The escape character '\' can be used

Between and

matters needing attention:
	1.use between and Mention the brevity of the statement, but include boundary values
	2.Do not reverse the threshold

– screen the information of students between 170-180 in height –

1. Old method

	select * from student
	where hight>= 170 and hight <= 180; # That's what it says

2. New methods

	select * from student
	where hight between 170 and 180;

3. in

Meaning: judge whether the value of a field belongs to in An item in the list
 characteristic:
	1.Improve sentence conciseness
	2.in()The value types inside must be unified
	3.in use in The conditions in () should be written completely

– screening the classmate information of software 131 or network 131 –

1. Old method

	select * from student
	where deptment='Software 131' or deptment='Network 131';

2. in method

	select * from student
	where deptment in('Software 131', 'Network 131');

4. is null

=or<>Cannot be used for judgment null value
is null or is not null Can judge null value

– screen the information of students who are not class committee members –

select * from student
where remark is null;

5. Safety equals: < = >

select * from student
where remark <=> null;

– screen the names, gender and class of students with height of 173 –

select sName, sex, deptment from student
where hight <=> 173;

Advanced 3. Sorting Query

– height from low to high –

select * from student
order by hight asc;

– height from top to bottom –

select * from student
order by hight desc;

– those with height greater than 160 are sorted by age [add filter add] –

select * from student 
where hight>=160
order by birthday;

– display classmate information by 2 times of height [sort by expression] –

select *, hight*2 Twice as tall from student
order by  hight*2;

– display students' information by twice their height [sort by alias]

select *, hight*2 Twice as tall from student
order by  Twice as tall;

– display the names of students by the length of their names and class numbers [sort by function]

select  length(sName) Byte length, sName, deptment, sNo from student
order by length(sName);

– first in ascending order by height, then in descending order by name length [sort by multiple fields] –

select * from student
order by hight asc, length(sName)  desc;

Let people of the same height rank according to the length of their names from high to low
 Such as Tian Cuiping and Zhou Wei