Database query

DQL(Database Query Language)

The select statement

• Query all data

  select * fromThe name of the tableCopy the code

• Query a specified field

  selectfield1, the field2 fromThe name of the tableCopy the code

• Alias the specified field (you can alias the field or the table)

  selectfield1 asThe alias1, the field2 asThe alias2..fromThe name of the tableasTable aliasCopy the code

• Use the function concat(a,b) : concatenate strings

  select concat('a'.'b') from dual //ab
  Copy the code

• Deduplication: Deletes the queried fields. Keyword: DISTINCT

  select distinctfieldfromThe name of the tableCopy the code

• Expressions: Expressions in MySQL can be columns, text values, null, functions, system variables, computed expressions, etc

  selectexpressionfrom 表
  Copy the code

SELECT full syntax

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1]][,table.field2[asalias2]][,...] }FROM table_name [as table_alias] 
[left | right | inner join table_name2] SQL > alter table associative query
[WHERE. ]-- Specify the query criteria
[GROUP BY. ]-- Specify grouping conditions
[HAVING ..] 	-- Secondary conditions that must be met by filtering grouped data
[ORDER BY. {ASC | DESC}]	-- Specify sorting conditions
[LIMIT {[offset,]row_count | row_countOffset offSet}]
Copy the code

The WHERE condition clause

• Function: Retrieves qualified values from data
• The search criteria consist of one or more expressions that result in a Boolean value
• Logical operator

• Fuzzy query: Comparison operator

  The operator	grammar	describe
  is null	a is null	If a is null, the result is true
  is not null	a is not null	A is not null and the result is true
  between … and …	a between b and c	A is between B and C, and the result is true, including b and C
  like	a like b _ : indicates a character % : indicates 0 or any character	SQL match, if A matches B, the result is true A like b_ : INDICATES that A matches two characters starting with B A like B % : INDICATES that A matches 0 or any characters starting with B
  in	a in (a1,a2,a3…)	If a1, A2, and A3 contain a, the result is true
  not in	a not in (a1,a2,a3…)	A1, A2,a3 do not contain a, then the result is true

Even the table query

Paging and sorting

• Order: ==order by keyword, ASC – ascending no default ascending, DESC descending ==

select a,b,c fromThe name of the tablewhereconditionsorder by a desc //Indicates that the final query results are sorted in descending order according to field ACopy the code

• Paging: limit([n],m) n: indicates the number of rows (n starts from 0). M indicates the number of rows to be queried. N can be ignored, indicating that m rows are queried directly from the first row

select a,b,c fromThe name of the tablewhereconditionsorder by a desc limit (n,m) //Paging query: queries M pieces of data starting from the NTH row. If n is omitted, M pieces of data are directly queried from the first rowCopy the code

The subquery

• Essence: To put another SELECT clause in a WHERE clause or select statement

• Case study:

  Select top 5 student id from c language 1
  SELECT
  	studentno,
  	studentname
  FROM
  	student where studentno in (
  		select studentno FROM result where subjectno = (
  			select subjectno from subject where subjectname = 'C语言-1'))Copy the code

Grouping and filtering

• GROUP BY: Query data can be grouped using the GROUP BY clause

• HAVING: The HAVING condition clause can be used to filter the grouped statements

• Note: The WHERE clause cannot be used with aggregate functions, and only the HAVING clause is required for filtering conditions

• case

  -- Query course average >= 80 for total, average, maximum and minimum scores
  SELECT
  	sub.subjectname,
  	SUM( r.studentresult ) as sum,
  	AVG( r.studentresult ) as avg,
  	MAX(r.studentresult) as max,
  	MIN(r.studentresult) as min
  	from
  	result r inner join `subject` sub on r.subjectno = sub.subjectno
  	group by r.subjectno
  having avg > = 80
  
  Copy the code

MySQL function

• Document www.mysqlzh.com/doc/113.htm…

The general function

• Mathematical operations

  function	grammar	describe
  ABS()	select ABS(-99)	Take the absolute value
  Between () or CEIL ()	Select between (1.2) / / 2 The select CEIL (1.2) / / 1	Take up the whole
  FLOOR()	The select FLOOR (2.2)	Take down the whole
  RAND()	select RAND()	Returns a random number between 0 and 1
  SIGN(99)	select SIGN(99)	To determine the sign of a number, negative numbers return -1, 0 returns 0, and positive numbers return 1

• String function

  • All string subscripts in MySQL start with 1

  function	grammar	describe
  CHAR_LENGTH(str)	Select CHAR_LENGTH(‘ hello world ‘) // 4	Returns the character length of the string
  LENGTH(str)	Select LENGTH(‘ Hello world ‘) // 12	Returns the Unicode encoding length of a string
  CONCAT(str1,str2,str3…)	Select CONCAT(‘ Hello ‘,’ my love ‘,’ world ‘)	Concatenate string to concatenate all parameter strings into one string in sequence
  INSERT(str,pos,len,newstr)	Select INSERT(‘ hello world ‘,1,2,’ beautiful ‘) // beautiful world	Replace the string with newstr to replace len strings of length starting at STR’s pos position
  LOWER(str)	select LOWER(‘A’) // a	Converts all uppercase letters in STR to lowercase
  UPPER(str)	select UPPER(‘a’) // A	Converts all lowercase letters in STR to uppercase
  INSTR(str,substr)	Select INSTR(‘ hello world ‘,’ you ‘) // 1	Returns the position at which the substr substring first appears in the STR string
  REPLACE(str,from_str,to_str)	Elect REPLACE(‘ Hello world ‘,’ hello ‘,’ goodbye ‘) // Goodbye world	String substitution, using to_str to replace from_str appearing in STR
  SUBSTR(str,pos,len) SUBSTR(str,pos) Equivalent to the SUBSTRING ()	Select SUBSTR(‘ hello world ‘,1,2) // hello Select SUBSTR(‘ Hello world ‘,1) // Hello world	String interception, intercepting a string of length len starting at pos in STR string; If len is not available, the end of the STR string is truncated
  REVERSE(str)	SELECT REVERSE(‘ hello ‘) // Hello	Flip string

• Time and date functions

  function	grammar	describe
  NOW()	select NOW()	Returns the current date and time, including date and time
  CURRENT_TIME()	select CURRENT_TIME()	Returns the current time (minutes, seconds)
  CURRENT_DATE()	select CURRENT_DATE()	Returns the current date (year month day)
  CURRENT_TIMESTAMP()	select CURRENT_TIMESTAMP()	Returns the current date and time, including the date and time

Aggregate functions (common)

MD5 encryption function