MySQL deep ——— database functions
MySQL user interface:
The MySQL user interface software was downloaded yesterday, and then the software was used to practice writing database functions.
software name is navicat for MySQL, resources I will not send, we can go to < download bar > download.
user interface:
then connected to the database, a few days ago I wrote a DOS teacher table can be seen on the software:
the next step is to get to the chase:
MySQL Drill down--the practice of database functions:
1. Aggregation Functions
Select AVG (age) from teacher;/* average: Select AVG (field name) from table name */
Select COUNT (age) from teacher;/* count function: Select count (field name) from table name */
Select Min, max (age) from teacher;/* maximum minimum: select min (field name), max (field name) from table name */
select SUM (age) from teacher;/* summation function: Select sum (field name) from table name */
2. Process Control Functions
Select Case 3 if 1 Then ' value is 1 ' When 2 Then ' value is 2 ' else ' value for other ' end;/*case syntax: Select Case value when value1 then RESULT1 When value2 then result2 else RESULT3 end as Alias */
Select if (1<2, ' true ', ' false ');/*if syntax: if (expression, result 1, result 2) */
Select Ifnull (null,2);/*ifnull Syntax: parameter 1 if NULL returns parameter 2, otherwise returns the parameter 1*/
Select Nullif (2,2);/*NULLIF syntax: returns null if parameter 1 is equal to parameter 2, otherwise returns a parameter 1*/
3. String Functions
Select Char_length (' AAA database ');/*char_length (): Calculates the string length */
Select Length (' AAA database ');/*length (): Returns the string byte length */
Select Format (3.1415926,4);/*format (): Rounding method retain decimal after D bit, return string */
Select Insert (' ABCDEFGH ', 2,4, ' zzz '),/*insert (): Replace/with (parameter 4) after (parameter 2) (parameter 3) bit
Select InStr (' Abcdefgaa ', ' def ');/* Returns the first position in (parameter 1) (parameter 2) */
Select Left (' Abcdefghi ', 5);/* Returns a substring of the unit (parameter 2) in the start from the leftmost */
Select Right (' Abcdefghi ', 5);/* Returns a substring of the unit (parameter 2) starting from the left */
Select LTrim (' abc ');/* Delete the left space of the string */
Select RTrim (' abc ');/* Delete the right space of the string */
Select Trim (' abc ');/* Delete string before and after space */
Select trim (Leading ' + + + ' from ' +++abc+++ ');/* Remove specific characters to the left */
Select Trim (both ' + + + ' from ' +++abc+++ ');/* Delete left and right characters */
Select trim (trailing ' + + + ' from ' +++abc+++ ');/* Delete the specific character on the right */
Select strcmp (4,3);/* Compare two expressions, return 0 for equality, greater than return 1, less than return -1*/
Select strcmp (' ABCA ', ' ABCD ');/* When comparing strings, compare the ASCII code of the characters */
Select Concat (' A ', ' B ', ' C ');/* Stitch string, if any of them are null, return null*/
Select substring (' ABCDEFG ', 3);/* intercepts a string starting at (parameter 2) position */
Select substring (' ABCDEFG ', 3,4);/* from intercept (parameter 3) characters */
Select substring (' ABCDEFG ' from 3);/* standard format with 28 lines */
Select substring (' ABCDEFG ' from 3 for 4);/* standard format with 29 lines */
4. Date-time Functions
Select DayOfWeek (' 2016-4-26 '); */* The day of the week */
Select DayOfYear (' 2016-4-26 ');/* The Day of the Year */
Select DayOfMonth (' 2016-4-26 '); */* the day of January */
Select Weekday (' 2016-4-26 ');/* Weekday index: Monday is 0, one analogy */
Select year (' 2016-4-26 ');/* Returns the corresponding years */
Select month (' 2016-4-26 ');/* Returns the corresponding month */
Select quarter (' 2016-4-26 ');/* return to the corresponding season */
Select Hour (' 22:14:57 ');/* Returns the corresponding hour */
Select minute (' 22:14:57 ');/* Returns the corresponding minute */
Select second (' 22:14:57 ');/* returns corresponding seconds */
Select To_days (' 2016-4-26 ');/* Number of days from A.D. to present */
Select From_days (70000);/* Time from A.D. to specified number of days */
select Curdate ();/* Returns the current date */
select Curtime ();/* Returns the current time */
Select Now ()/* Returns the current date time */
More resources:
Database functions ppt
MySQL drill-down-database functions
