catalogue
Linux Mysql advanced statement
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
| function | meaning |
|---|---|
| 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);
