Linux Mysql advanced statement

Keywords: Linux Operation & Maintenance Tomcat

catalogue

Linux Mysql advanced statement

1, SQL advanced statement

Import file to database

 select 

 distinct

 where

  And and   Or or

 in

 between

  like wildcard

 order by 

  function

  Aggregate function

replace 

concat 

substr

 group by

 having

  alias

  join query

  inner join

  left join

  right join

  Subquery

 EXISTS

Linux Mysql advanced statement

1, SQL advanced statement

Import file to database

#Import the script into source plus the file path
mysql> source /backup/test.sql;

 select 

Displays all information in one or more fields in the table

#Syntax:
select Field name  from Table name;
Example 1:
select * from students;

Example 2:
select name,age from students;

 distinct

Query non duplicate records

#Syntax:
select distinct field from Table name﹔
#Example 1: remove duplicate in age field
select distinct age from students;

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

 where

where conditional query

#Syntax:
select 'field' from Table name  where condition
#Example: display name and age, and find age less than 20
select name,age from students where age < 20;

  And and   Or or

#Syntax:
select Field name  from Table name where Condition 1 (and|or) Condition 2 (and|or)Condition 3;
Example 1: display name and age And to find age Greater than 20 but less than 30
select name,age from students where age >20 and age <30;

 in

Show known value data

#Syntax:
select Field name  from Table name where field in ('Value 1','Value 2'....);
#Example 1: display student records with student numbers 1, 2, 3 and 4
select * from students where StuID in (1,2,3,4);

#Example 2: show student records for classes 1 and 3
select * from students where ClassID in (1,3);

 between

Displays data in the range of two values

#Syntax:
select Field name  from Table name where field between  'Value 1' and 'Value 2';
include and Values on both sides
#Example 1: show student records with student names in Ding Dian and Hua Rong
select * from students where name between 'ding dian' and 'Hua Rong';

#Example 2: display information with student number id in 2-5
select * from students where stuid between 2 and 5;

#Example 2: display the information of students aged 20-35. There is no need to have this field in the table. Only the existing information from 20 to 25 will be displayed
select * from students where age between 20 and 25;

  like wildcard

#Syntax:
select Field name  from Table name where field like pattern

wildcard                                      meaning
%                             Represents zero, one or more characters
_                              The underscore indicates a single character
A_Z                          All strings starting with a and ending with z 'ABZ', 'acz', 'accccz' are not in the range. The underscore only indicates                               One character AZ contains a space z
ABC%                      All strings beginning with ABC ABCD ABCD
%CBA                      All CBA terminated strings WCBA CBACBA
%AN%                     All strings containing an los angeles
_ AN%                      All strings with the second letter A and the third letter N

#Example 1: find a student record whose name starts with s
select * from students where name like 's%';

#Example 3: find the student record with the second letter of u and the third letter of a
select * from students where name like '_ua%';

 order by 

order by

#Syntax:
select Field name  from Table name where condition order by field [asc,desc];
asc : Forward sort
desc : Reverse sort
 The default is forward sort
#Example 1: displays the age and name fields in a forward sort by student's age
 select age,name from students order by age;

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

#Example 3: display the data of name, age and classid fields, and only those with classid field 3 and sorted by age field
select age,name,classid from students where classid=3 order by age;

  function

Digital function

function                                      meaning
abs(x)                        Returns the absolute value of X
rand()                         Returns a random number from 0 to 1
mod(x,y)                     Returns the remainder of X divided by y
power(x,y)                  Returns the Y power of X
round(x)                     Returns the integer closest to X
round(x,y)                Keep the Y decimal places of X and the value after rounding
sqrt(x)                       Returns the square root of X
truncate(x,y)               Returns the value of the number x truncated to y decimal places
ceil(x)                         Returns the smallest integer greater than or equal to X
floor(x)                       Returns the largest integer less than or equal to X
greatest(x1,x2.....)      Returns the largest value in the collection
least(x1,x2..........)       Returns the smallest value in the collection

#Example 1: return the absolute value of - 2
select abs(-2);

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

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

#Example 4: returns the remainder of 7 divided by 2
select mod(7,2);

#Example 5: returns the third power of 2
select power(2,3);

#Example 6: return the number closest to 2.6
select round(2.6);
 
#Returns the number closest to 2.4

#Example 7: keep the 3 decimal places of 2.335321 and round the value
select round(2.335321,2);

#Example 8: return the number 2.335321 truncated to 2 decimal places
select truncate(2.335321,2);

#Example 9: return the smallest integer greater than or equal to 2.335321
select ceil(2.335321);

#Example 10: return the maximum integer less than or equal to 2.335321
 select floor(2.335321);

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

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

  Aggregate function

functionmeaning
avg()Returns the average value of the specified column
count()Returns the number of non NULL values in the specified column
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 for the specified column
#Example 1: average the ages in the table
select avg(age) from students;

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

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

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

#Example 5: find the number of class field non empty records in the table
select count(classid) from students;
 
count(Explicit field): empty records will not be ignored

#Example 6: how many records are there in the table
 select count(*) from students;
 
 count(*)If there is an empty field, the empty record will be ignored

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

  String function

function                              describe
trim()                       Returns a value in the specified format
concat(x,y)               Splice the supplied parameters X and Y into a string
substr(x,y)               Gets the string starting from the y-th position in the string x, which is the same as the substring () function
substr(x,y,z)             Gets a string of length Z starting from the y-th position in string X
length(x)                   Returns the length of the string X
replace(x,y,z)           Replaces string y in string x with string Z
upper(x)                    Turns all letters of string x into uppercase letters
lower(x)                    Turns all letters of string x into lowercase letters
left(x,y)                     Returns the first y characters of string X
right(x,y)                   Returns the last y characters of string X
repeat(x,y)                 Repeat the string x y times
space(x)                     Return x spaces
strcmp(x,y)                 Comparing X and y, the returned value can be - 1,0,1
reverse(x)                   Invert string x

trim

Syntax:
select trim (Position the string to remove from character string)
 
Where the value of the position can be 
leading((start) 
trailing(ending)
both(Beginning and end)
 
#Case sensitive
 String to remove:A string that is removed from the beginning, end, or beginning and end of a string. By default, it is a space.
#Example 1: remove the Sun display in Sun Dasheng from the beginning of the name
select trim(leading 'Sun' from 'Sun Dasheng');

#Example 2: remove spaces
select trim(both  from ' zhang san      ');

#Syntax:
select length(Fields) from Table name;
#Example: calculate the character length of the record in the field
 select name,length(name) from students;

replace 

#Syntax:
select replace(Field,'Original character''Replace character') from Table name;
select replace(name,'ua','hh') from students;

concat 

#Syntax:
select concat(Field 1, field 2) from Table name
#Example 1: concatenate the name and classid fields into a string
select concat(name,classid) from students;

#Example 2: only the name and classid of 3ban are displayed as a combined record
select concat(name,classid) from students where classid=3;

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

substr

#Syntax:
select substr(Field, start intercepting character, intercepting length)  where field='Intercepted string' 
#Example 1: intercept the 6th character
select substr(name,6) from students where name='Yue Lingshan';

#Example 2: intercept two characters after the sixth character
select substr(name,6,2) from students where name='Yue Lingshan';

 group by

The query results of the fields behind group by are summarized and grouped, which are usually used in combination with aggregation functions

Group by has a principle that in all columns after select, columns that do not use aggregate functions must appear after group by

#Syntax:
select Field 1, sum(Field 2) from Table name group by Field 1;
#Example 1: sum the ages of each class
 select classid,sum(age) from students group by classid;

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

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

 having

having: used to filter the record set returned by the group by statement. It is usually used in combination with the group by statement

The existence of having statement makes up for the deficiency that where keyword can not be used in combination with aggregate function. If only the function column is selected, the GROUP BY clause is not required

To specify criteria based on the fields in the new table

#Syntax:
SELECT Field 1, SUM("field")FROM Table name GROUP BY Field 1 having(Function condition);
#Example: view classes with an average age of more than 30 in each class
select classid,avg(age) from students group by classid having avg(age) > 30;

  alias

Field alias table alias

v#Syntax:
SELECT "Table alias"."Field 1" [AS] "Field alias" FROM "Table name" [AS] "Table alias";
#Example: set the alias of the table name as f, count the total age of each class based on the class number, and sum (age) defines the alias as total age
select f.classid,sum(age) 'total age' from students as f group by f.classid;

  join query

  inner join

Returns only rows with equal join fields in two tables

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

  left join

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

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

  right join

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

  Subquery

Join the table and insert another SQL statement in the WHERE clause or HAVING clause

Syntax:
SELECT "Field 1" FROM "Table 1" WHERE "Field 2"	[Comparison operator]			
#External query
(SELECT "Field 1" FROM "Table 1" WHERE "condition");	
#Example: query the total score of student ID 1
select sum(score) from scores where stuid in (select stuid from students where stuid=1);

 EXISTS

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

If so, the system will execute the SQL statements in the external query. If not, the entire SQL statement will not produce any results

 
#Syntax:
SELECT "Field 1" FROM "Table 1" WHERE EXISTS (SELECT * FROM "Table 2" WHERE "condition");
#Example 1: first check whether there are students with stuid 1 in the students table. If so, sum the scores in the scores table
select sum(score) from scores  where exists (select * from students where stuid=1);

#Example 2: first check whether there are students with stuid 88 in the students table. If so, sum the scores in the scores table
select sum(score) from scores  where exists (select * from students where stuid=88);

Posted by IchBin on Sun, 05 Dec 2021 01:11:11 -0800