MySQL advanced statement (I)

_ Rui_ ao 2022-02-13 07:07:05 阅读数:661

mysql advanced statement

MySQL High level statements ( One )

One 、SQL High level statements

1. Import file to database

# First create a folder , Then import the prepared file 
[[email protected] ~]# mkdir /backup
[[email protected] ~]# cd /backup/
[[email protected] backup]# rz -E
rz waiting to receive.
[[email protected] backup]# ls
test.sql
# After entering the database, import the file
[[email protected] backup]# mysql -u root -p
mysql> source /backup/test.sql;

2.select

Displays all the information in one or more fields in the table

# grammar
select Field name from Table name ;
# Example 1
select * from students;

image-20211223090341170

# Example 2
select name,age from students;

image-20211223090523854

3.distinct

Query does not duplicate records

# grammar :
select distinct Field from Table name ﹔
# Example 1: Remove duplicates in the age field
select distinct age from students;

image-20211223090647522

# Example 2: Find gender
select distinct gender from students;

image-20211223090723174

4.where

where Conditional inquiry

# grammar :
select ' Field ' from Table name where Conditions
# Example : Show name and age And find age Less than 20
select name,age from students where age < 20;

image-20211223090829103

5.and;or

and And ; or or

# grammar :
select Field name from Table name where Conditions 1 (and|or) Conditions 2 (and|or) Conditions 3;
 Example 1: Show name and age And find age Greater than 20 Less than 30
select name,age from students where age >20 and age <30;

image-20211223090940635

6.in

Display data of known values

# grammar :
select Field name from Table name where Field in (' value 1',' value 2'....);
# Example 1: The student number displayed is 1,2,3,4 Student records of
select * from students where StuID in (1,2,3,4);

image-20211223091040649

# Example 2: Show class as 1 and 3 Student records of
select * from students where ClassID in (1,3);

image-20211223091111790

7.between

Displays data in two ranges

# grammar :
select Field name from Table name where Field between ' value 1' and ' value 2';
Include and Values on both sides
# Example 1: Show student names in Ding Dian and Hua Rong Student records in
select * from students where name between 'ding dian' and 'Hua Rong';

image-20211223091224014

# Example 2: Show student number id stay 2-5 Information about
select * from students where stuid between 2 and 5;

image-20211223091251936

# Example 3: Displays the student's age at 20-35 Information between , You don't need to have this field in the table , Only will 20 To 25 The existing ones are displayed
select * from students where age between 20 and 25;

image-20211223091331261

8.like wildcard

Wildcards are usually and like Use it together

# grammar :
select Field name from Table name where Field like Pattern
wildcard meaning
% Represents zero , One or more characters
_ The underline represents a single character
A_Z All with A start Z a null-terminated string ‘ABZ’ ‘ACZ’ 'ACCCCZ’ Out of range Underscores represent only one character AZ contain a Space z
ABC% All with ABC Starting string ABCD ABCABC
%CBA All with CBA a null-terminated string WCBA CBACBA
%AN% All contain AN String los angeles
_AN% all The second letter is A Third letter by N String
# Example 1: Find a name to s The student record at the beginning
select * from students where name like 's%';

image-20211223091513479

# Example 2: Find names that contain ong Student records of
select * from students where name like '%ong%';

image-20211223091541389

# Example 3: Find the second letter of the name as u, The third letter is a Student records of
select * from students where name like '_ua%';

image-20211223091612154

9.order by

order by Sort by keyword

# grammar :
select Field name from Table name where Conditions order by Field [asc,desc];
asc : Forward order
desc : Reverse sorting
The default is forward sort
# Example 1: Displays the age and name fields in a positive sort by student's age
select age,name from students order by age;

image-20211223091705408

# Example 2: Displays the age and name fields in reverse order by student's age
select age,name from students order by age desc;

image-20211223091730785

# Example 3: Show name、age and classid Field data And only show classid Field is 3 Of And take age Field sorting
select age,name,classid from students where classid=3 order by age;

image-20211223091756892

10. function

10.1 Mathematical functions

function meaning
abs(x) return x Of The absolute value
rand() return 0 To 1 The random number
mod(x,y) return x Divide y The remainder after
power(x,y) return x Of y Power
round(x) Return from x The nearest integer
round(x,y) Retain x Of y The rounded value of a decimal place
sqrt(x) return x The square root of
truncate(x,y) Return to digital x Truncated to y A decimal value
ceil(x) Returns greater than or equal to x Minimum integer of
floor(x) Returns less than or equal to x Maximum integer for
greatest(x1,x2…) Returns the largest value in the collection
least(x1,x2…) Returns the smallest value in the set
# Example 1: return -2 The absolute value of
select abs(-2);

image-20211223091911999

# Example 2: Randomly generate a number
select rand (1);

image-20211223091943590

# Example 3: Randomly generated sort
select * from students order by rand();

image-20211223092019198

# Example 4: return 7 Divide 2 The remainder after
select mod(7,2);

image-20211223092043213

# Example 5: return 2 Of 3 Power
select power(2,3);

image-20211223092110061

# Example 6: Return from 2.6 The latest number
select round(2.6);
# Return from 2.4 The latest number

image-20211223092134879

image-20211223092210294

# Example 7: Retain 2.335321 Of 3 The rounded value of a decimal place
select round(2.335321,2);

image-20211223092233156

# Example 8: Return to digital 2.335321 Truncated to 2 A decimal value
select truncate(2.335321,2);

image-20211223092251246

# Example 9: Returns greater than or equal to 2.335321 Minimum integer of
select ceil(2.335321);

image-20211223092316976

# Example 10: Returns less than or equal to 2.335321 Maximum integer for
select floor(2.335321);

image-20211223092354380

# Example 11: Returns the maximum value in the collection
select greatest(1,4,3,9,20);

image-20211223092418265

# Example 12: Returns the smallest value in the set
select least(1,4,3,9,20);

image-20211223092445928

10.2 Aggregate functions

function meaning
avg() Returns the average value of the specified column
count() Returns the value of the specified column NULL The number of values
min() Returns the minimum value of the specified column
max() Returns the maximum value of the specified column
sum(x) Returns the sum of all values in the specified column
# Example 1: Find the average age in the table
select avg(age) from students;

image-20211223092537603

# Example 2: Sum the ages in the table
select sum(age) from students;

image-20211223092559888

# Example 3: Find the maximum age in the table
select max(age) from students;

image-20211223092620835

# Example 4: Find the minimum age in the table
select min(age) from students;

image-20211223092643037

# Example 5: Find the number of class field non empty records in the table
select count(classid) from students;
count( Clear fields ): Empty records will be ignored

image-20211223092705567

# Example 6: Find out how many records there are in the table
select count(*) from students;
count(*) Contains an empty field , Empty records will not be ignored

image-20211223092949892

# Example 7: See if the space field will be matched
insert into students values(26,' ',28,'f',1,8);

image-20211223093038378

10.3 String function

function describe
trim() Returns a value with the specified format removed
concat(x,y) The parameters that will be provided x and y Concatenate into a string
substr(x,y) Get from string x No y A string starting at a position , Follow substring() Functions work the same
substr(x,y,z) Get from string x No y The starting length of a position is z String
length(x) Return string x The length of
replace(x,y,z) The string z Alternative string x String in y
upper(x) The string x All of the letters of the alphabet become capital letters
lower(x) The string x All of the letters of the are changed into lower case letters
left(x,y) Return string x Before y Characters
right(x,y) Return string x After y Characters
repeat(x,y) The string x repeat y Time
space(x) return x A space
strcmp(x,y) Compare x and y, The value returned can be -1,0,1
reverse(x) The string x reverse

1)trim

 grammar :
select trim ( Location The string to remove from character string )
Where the value of the position can be
leading( Start )
trailing( ending )
both( The beginning and the end )
# Case sensitive
The string to remove : Start from the beginning of the string 、 End or start and end removed string , The default is space .
# Example 1: Start at the beginning of the name , remove Sun Dasheng Medium Sun Show
select trim(leading 'Sun' from 'Sun Dasheng');

image-20211223093325683

# Example 2: Remove the space
select trim(both from ' zhang san ');

image-20211223093345931

The name itself has a space , After removing the space , Name freeze frame display

2)length

# grammar :
select length( Field ) from Table name ;
# Example : Calculate the character length of the record in the field
select name,length(name) from students;

image-20211223093454341

3)replace

# grammar :
select replace( Field ,' Original character '' Replace character ') from Table name ;
# Check that the name contains ua The record of
select name from students where name like '%ua%';
# Will these ua All replaced with hh Show
select replace(name,'ua','hh') from students;

image-20211223093711115

4)concat

# grammar :
select concat( Field 1, Field 2)from Table name
# Example 1: take name,classid Fields are spliced into a string
select concat(name,classid) from students;

image-20211223093846087

# Example 2: Display only 3 Ben's name and classid For a combined record
select concat(name,classid) from students where classid=3;

image-20211223093922529

# Example 3: Add a tab in the middle
select concat(name,'\t',classid) from students where classid=3;

image-20211223094046282

4)substr

# grammar :
select substr( Field , Start intercepting characters , Intercept length ) where Field =' Intercepted string '
# Example 1: Cut off the 6 Characters later
select substr(name,6) from students where name='Yue Lingshan';

image-20211223094126818

# Example 2: Cut off the 6 Two characters after two characters
select substr(name,6,2) from students where name='Yue Lingshan';

image-20211223094150341

11.group by

 Yes group by The query results of the following fields are summarized and grouped , Usually used in combination with aggregate functions
group by There is a principle , Namely select In all the following columns , Columns that do not use aggregate functions must appear in group by Behind .
# grammar :
select Field 1,sum( Field 2) from Table name group by Field 1;
# Example 1: Find the total age of each class
select classid,sum(age) from students group by classid;

image-20211223094308712

# Example 2: Find the average age of each class
select classid,avg(age) from students group by classid;

image-20211223094404818

# Example 3: Check the number of people in each class according to their age
select classid,count(age) from students group by classid;

image-20211223094430601

12.having

having: Used to filter by group by Statement returned recordset , Usually with group by Statements are used in conjunction with
having The existence of sentences makes up for where Keywords cannot be combined with aggregate functions . If you are SELECT Only the function bar , Then there is no need for GROUP BY Clause .
According to the fields in the new table , To specify conditions
# grammar :
SELECT Field 1,SUM(" Field ")FROM Table name GROUP BY Field 1 having( Function conditions );
# Example : Check that the average age of each class is 30 The above classes
select classid,avg(age) from students group by classid having avg(age) > 30;

image-20211223094525657

13. Alias

Field alias Form alias

# grammar :
SELECT " Table alias "." Field 1" [AS] " Field alias " FROM " Table name " [AS] " Table alias ";
# Example : Set the table name alias to f, Count the total age of each class based on the class number ,sum(age) Define the alias as total age
select f.classid,sum(age) 'total age' from students as f group by f.classid;

image-20211223094625112

14. Link query

select * from students;
select * from scores;

image-20211223094758096

image-20211223094816992

1)inner join( It's equivalent )

Return only rows with equal join fields in two tables

SELECT * FROM students A INNER JOIN scores B on A.stuid = B.stuid;

image-20211223094936764

2)left join( Left connection )

Returns records that include all records in the left table and records with equal join fields in the right table

select * from scores A left join students B on A.stuid = B.stuid;

image-20211223095112188

3)right join( The right connection )

select * from scores A right join students B on A.stuid = B.stuid;

image-20211223095224774

15. Subquery

Connect tables , stay WHERE Clause or HAVING Clause to insert another SQL sentence

 grammar :
SELECT " Field 1" FROM " form 1" WHERE " Field 2" [ Comparison operator ]
# External query
(SELECT " Field 1" FROM " form 1" WHERE " Conditions ");
# Example : The student's student number is 1 The sum of the scores
select sum(score) from scores where stuid in (select stuid from students where stuid=1);

image-20211223095358660

16.EXISTS

Used to test whether the internal query produces any results, such as whether the Boolean value is true

If any , The system will execute the SQL sentence . If not , The whole SQL Statement will not produce any results .

 # grammar :
SELECT " Field 1" FROM " form 1" WHERE EXISTS (SELECT * FROM " form 2" WHERE " Conditions ");
# Example 1: First look at students Is it in the table stuid by 1 Of the students , If so, execute scores In the table score Sum up
select sum(score) from scores where exists (select * from students where stuid=1);

image-20211223095626306

because students Table has stuid by 1 The record of , Then execute the statement of the outer loop

# Example 2: First look at students Is it in the table stuid by 88 Of the students , If so, execute scores In the table score Sum up
select sum(score) from scores where exists (select * from students where stuid=88);

image-20211223095741722
because students Not in the table stuid by 88 Of , Therefore, the statement of out of line loop is not executed , return NULL value

copyright:author[_ Rui_ ao],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130707027224.html