การใช้ฟังก์ชันภายใน ของ มายเอสคิวแอล
Northwind | HTML | JavaScript | Function | Datatype
SQL Function ฟังก์ชันภายในของมายเอสคิวแอล คือ คำสั่งที่ถูกจัดเตรียมเพื่อดำเนินการกับข้อมูลในระบบฐานข้อมูลเกี่ยวกับ ข้อความ (String) ตัวเลข (Numeric) วันที่ (Date) และ ระดับสูง (Advanced) ซึ่งบางฟังก์ชันใช้ดำเนินการกับเขตข้อมูลในตาราง เช่น max(customerid) หรือ length(customername) แต่บางฟังก์ชันใช้เพื่ออ่านค่าจากระบบ เช่น user() หรือ curdate()
SQL function list by w3schools.com
ตัวอย่าง
จงเขียนคำสั่ง sql เพื่อแสดงรหัสแอสกี้ของตัวอักษร A และค่าสัมบูรณ์ของ -22/7 และวันที่ปัจจุบัน และเลขฐานสองของจำนวน 15 และรหัสผู้ใช้และหมายเลขโฮส และจำนวนตัวอักษรในเขตข้อมูลชื่อลูกค้าระเบียนแรก
หรือ จงเขียนคำสั่ง sql เพื่อแสดงรหัสลูกค้าที่มากที่สุด
1
2
3
4
5
6
7
8
9
10
11
SELECT ASCII("A"),ABS(-22/7),-22/7,CURDATE(),BIN(15),USER(), length(customername) from Customers limit 1;
Result :
  ASCII("A") = 65
  ABS(-22/7) = 3.1429
  CURDATE() = 2021-11-03
  BIN(15) = 1111
  USER() = guest@35.192.20.199
  length(customername) = 19
หรือ
SELECT max(customerid) as m FROM Customers;
  m = 91
SQL Function #
MySQL String Functions
No.FunctionDescription
1ASCII Returns the number code that represents the specific character
2CHAR_LENGTH Returns the length of the specified string (in characters)
3CHARACTER_LENGTH Returns the length of the specified string (in characters)
4CONCAT Concatenates two or more expressions together
5CONCAT_WS Concatenates two or more expressions together and adds a separator between them
6FIELD Returns the position of a value in a list of values
7FIND_IN_SET Returns the position of a string in a string list
8FORMAT Formats a number as a format of "#,###.##", rounding it to a certain number of decimal places
9INSERT Inserts a substring into a string at a specified position for a certain number of characters
10INSTR Returns the position of the first occurrence of a string in another string
11LCASE Converts a string to lower-case
12LEFT Extracts a substring from a string (starting from left)
13LENGTH Returns the length of the specified string (in bytes)
14LOCATE Returns the position of the first occurrence of a substring in a string
15LOWER Converts a string to lower-case
16LPAD Returns a string that is left-padded with a specified string to a certain length
17LTRIM Removes leading spaces from a string
18MID Extracts a substring from a string (starting at any position)
19POSITION Returns the position of the first occurrence of a substring in a string
20REPEAT Repeats a string a specified number of times
21REPLACE Replaces all occurrences of a specified string
22REVERSE Reverses a string and returns the result
23RIGHT Extracts a substring from a string (starting from right)
24RPAD Returns a string that is right-padded with a specified string to a certain length
25RTRIM Removes trailing spaces from a string
26SPACE Returns a string with a specified number of spaces
27STRCMP Tests whether two strings are the same
28SUBSTR Extracts a substring from a string (starting at any position)
29SUBSTRING Extracts a substring from a string (starting at any position)
30SUBSTRING_INDEX Returns the substring of string before number of occurrences of delimiter
31TRIM Removes leading and trailing spaces from a string
32UCASE Converts a string to upper-case
33UPPER Converts a string to upper-case

MySQL Numeric Functions
No.FunctionDescription
34ABS Returns the absolute value of a number
35ACOS Returns the arc cosine of a number
36ASIN Returns the arc sine of a number
37ATAN Returns the arc tangent of a number or the arc tangent of n and m
38ATAN2 Returns the arc tangent of n and m
39AVG Returns the average value of an expression
40CEIL Returns the smallest integer value that is greater than or equal to a number
41CEILING Returns the smallest integer value that is greater than or equal to a number
42COS Returns the cosine of a number
43COT Returns the cotangent of a number
44COUNT Returns the number of records in a select query
45DEGREES Converts a radian value into degrees
46DIV Used for integer division
47EXP Returns e raised to the power of number
48FLOOR Returns the largest integer value that is less than or equal to a number
49GREATEST Returns the greatest value in a list of expressions
50LEAST Returns the smallest value in a list of expressions
51LN Returns the natural logarithm of a number
52LOG Returns the natural logarithm of a number or the logarithm of a number to a specified base
53LOG10 Returns the base-10 logarithm of a number
54LOG2 Returns the base-2 logarithm of a number
55MAX Returns the maximum value of an expression
56MIN Returns the minimum value of an expression
57MOD Returns the remainder of n divided by m
58PI Returns the value of PI displayed with 6 decimal places
59POW Returns m raised to the nth power
60POWER Returns m raised to the nth power
61RADIANS Converts a value in degrees to radians
62RAND Returns a random number or a random number within a range
63ROUND Returns a number rounded to a certain number of decimal places
64SIGN Returns a value indicating the sign of a number
65SIN Returns the sine of a number
66SQRT Returns the square root of a number
67SUM Returns the summed value of an expression
68TAN Returns the tangent of a number
69TRUNCATE Returns a number truncated to a certain number of decimal places

MySQL Date Functions
No.FunctionDescription
70ADDDATE Returns a date after a certain time/date interval has been added
71ADDTIME Returns a time/datetime after a certain time interval has been added
72CURDATE Returns the current date
73CURRENT_DATE Returns the current date
74CURRENT_TIME Returns the current time
75CURRENT_TIMESTAMP Returns the current date and time
76CURTIME Returns the current time
77DATE Extracts the date value from a date or datetime expression
78DATEDIFF Returns the difference in days between two date values
79DATE_ADD Returns a date after a certain time/date interval has been added
80DATE_FORMAT Formats a date as specified by a format mask
81DATE_SUB Returns a date after a certain time/date interval has been subtracted
82DAY Returns the day portion of a date value
83DAYNAME Returns the weekday name for a date
84DAYOFMONTH Returns the day portion of a date value
85DAYOFWEEK Returns the weekday index for a date value
86DAYOFYEAR Returns the day of the year for a date value
87EXTRACT Extracts parts from a date
88FROM_DAYS Returns a date value from a numeric representation of the day
89HOUR Returns the hour portion of a date value
90LAST_DAY Returns the last day of the month for a given date
91LOCALTIME Returns the current date and time
92LOCALTIMESTAMP Returns the current date and time
93MAKEDATE Returns the date for a certain year and day-of-year value
94MAKETIME Returns the time for a certain hour, minute, second combination
95MICROSECOND Returns the microsecond portion of a date value
96MINUTE Returns the minute portion of a date value
97MONTH Returns the month portion of a date value
98MONTHNAME Returns the full month name for a date
99NOW Returns the current date and time
100PERIOD_ADD Takes a period and adds a specified number of months to it
101PERIOD_DIFF Returns the difference in months between two periods
102QUARTER Returns the quarter portion of a date value
103SECOND Returns the second portion of a date value
104SEC_TO_TIME Converts numeric seconds into a time value
105STR_TO_DATE Takes a string and returns a date specified by a format mask
106SUBDATE Returns a date after which a certain time/date interval has been subtracted
107SUBTIME Returns a time/datetime value after a certain time interval has been subtracted
108SYSDATE Returns the current date and time
109TIME Extracts the time value from a time/datetime expression
110TIME_FORMAT Formats a time as specified by a format mask
111TIME_TO_SEC Converts a time value into numeric seconds
112TIMEDIFF Returns the difference between two time/datetime values
113TIMESTAMP Converts an expression to a datetime value and if specified adds an optional time interval to the value
114TO_DAYS Converts a date into numeric days
115WEEK Returns the week portion of a date value
116WEEKDAY Returns the weekday index for a date value
117WEEKOFYEAR Returns the week of the year for a date value
118YEAR Returns the year portion of a date value
119YEARWEEK Returns the year and week for a date value

MySQL Advanced Functions
No.FunctionDescription
120BIN Converts a decimal number to a binary number
121BINARY Converts a value to a binary string
122CASE Lets you evaluate conditions and return a value when the first condition is met
123CAST Converts a value from one datatype to another datatype
124COALESCE Returns the first non-null expression in a list
125CONNECTION_ID Returns the unique connection ID for the current connection
126CONV Converts a number from one number base to another
127CONVERT Converts a value from one datatype to another, or one character set to another
128CURRENT_USER Returns the user name and host name for the MySQL account used by the server to authenticate the current client
129DATABASE Returns the name of the default database
130IF Returns one value if a condition is TRUE, or another value if a condition is FALSE
131IFNULL Lets you to return an alternate value if an expression is NULL
132ISNULL Tests whether an expression is NULL
133LAST_INSERT_ID Returns the first AUTO_INCREMENT value that was set by the most recent INSERT or UPDATE statement
134NULLIF Compares two expressions
135SESSION_USER Returns the user name and host name for the current MySQL user
136SYSTEM_USER Returns the user name and host name for the current MySQL user
137USER Returns the user name and host name for the current MySQL user
138VERSION Returns the version of the MySQL database
rspsocial
ใช้เวลาโหลดเว็บเพจ = 209 มิลลิวินาที สูง = 3378 จุด กว้าง = 1264 จุด
Thaiall.com