Basic functions in Mysql

Basic functions in Mysql
The time function select curdate (); returns, excluding the hour, minute, and second select curtime (); returns 14:13:22, excluding the year, month, and day select now (); returns 10: 46: 17 select unix_timestamp (now (); unix_timestamp (date) returns the UNIX timestamp of date select unix_timestamp ('2017-09-01 '); the Unix timestamp is the number of seconds since January 1, 2013, if you do not consider refreshing the second, you may encounter the 2038 issue. Select from_unixtime (1184134516); returns 14:15:16, which is opposite to unix_timestamp (date. Select week (now (), year (now (); returns 36 and 2014, week (date) returns the week of the year, year (date) returns select hour (now (), minute (now (); -- returns 10 and 57. hour (date) and minute (date) will return the given time hour, minute. Select monthname (now (); returns September, returns the month in English
Format the date value date_format (date, fmt) and format the date value by fmt, so that the date can display the specific format as needed. Select date_format (now (), '% M, % D, % y'); 'September, 12th, 2014' select date_format (now (), '% Y-% m-% d % t'); '2017-09-12 11:06:03' is returned'

Select date_add (now (), INTERVAL 31 day) for the given date difference interval period; '2017-10-13 11:10:17 'is returned, which is the date after 31 days. Select date_add (now (), interval '1 _ 2' year_month); returns 11:10:55, which is the date after 1 year and 2 months select date_add (now (), interval 31 day); with select date_add (now (), interval, '-interval -2'); it can still be expressed as a negative number, query the date 31 days ago and the date one year and two months ago.
Select datediff ('2017-08-08 ', now (); Return-2008

String function connection string select concat ('A', 'bb', 'cc'); -- Return aabbccselect concat ('AA', null); -- return null, null is returned when it is connected to null.
Replace and insert select insert ('zhongguoren', 6,3, 'shance') according to the position; -- returns zhongshanren, starting from 6th characters (including 6th characters ), replace the three consecutive characters with 'shance' select insert ('zhongguoren', 'shanc'); -- returns zhongshanguoren and inserts 'shanc' before the six characters'
Replace select replace ('zhongguo', 'guo', 'shany') based on a specific string; -- Return 'zhongsha'

Lower case select lower ('beijing'), upper ('China ');
Returns the LEFT and RIGHT substrings select LEFT ('beijing2008 ', 7), RIGHT ('beijing2008', 4); -- returns being ing and 2008
Returns the substring select substring ('beijing2008 ', 4th) based on the position. -- returns 'jing2008'. Returns 4th characters (including characters and 8 consecutive characters)
Left and right fill select lpad ('20170101', 20, 'beijinging'), rpad ('beijinging', 20, '20160301'); Return beijingbeijingbe2008 and beijing200820082lpad (str, n, pad) pads The pad string from the left until the entire string reaches n length.
Remove left and right spaces select ltrim ('beijinging'), rtrim ('beijinging'); -- Remove left and right spaces select trim ('beijinging ');
Repeated string select repeat ('mysql', 3); returns 'mysql mysql'
Select strcmp ('abcd', 'dbca '); -- returns-1 STRCMP (s1, s2). If s1 is smaller than s2,-1 is returned, equal return 0, greater than return 1.
Returns the absolute values of select abs (-0.8), abs (0.8); -- returns 0.8.
Ceiling integer select ceil (-0.8), ceil (0.8); -- returns 0 and 1
Floor integer select floor (-0.8), floor (0.8); -- returns-1 and 0
Returns the modulo select mod (5, 3), 5% 3; of x/y. -- returns the same result as 2mod (x, y) and x % y.
Returns the random value select rand () between 0 and 1 ();
If you want to generate a random integer between 0 and 100 select ceil (* rand (); -- Return 84
Returns the rounded value of round (x, y). If y is not specified, y is 0 select round (1.1) by default. -- 1 select round (1.1, 2) is returned) -- returns 1.10 select round (103); -- returns 103 select round (100,-1); -- returns, accurate to 10
Returns the truncation value, which is similar to round (). The difference is that the truncation is not rounded to select truncate (1.235, 2); -- returns 1.23 select truncate (18.235,-1); -- returns 10
Select bin (4); -- returns 100
If (value, t, f) of the process function is true, t is returned. Otherwise, the fif function is returned, which is like the decode select if (role_id = 13, 'distributor' of oracle ', if (role_id = 14, 'delimer', null) as "role type" from upgrade. customer;
Replace the Null Value Function ifnull (v1, v2). If v1 is not empty, v1 is returned. Otherwise, v2 is returned.
Select case when salary <= 2000 then 'low' else 'high' end from salary;
Multi-branch case statement select case salary when 1000 then 'low' when 2000 then 'mid 'else' high 'end from salary;
Other functions database () Return Current database Name version () Return Current version user () Return Current login username inet_aton (ip) return ip address number represents inet_ntoa (num) return the IP address password (str) represented by the number. Return the encrypted str version md5 (str). Return the MD5 value of the str string.
What are the basic function commands of php mysql?

Www.w3school.com.cn/php/php_ref_mysql.asp
// Common functions are listed here.
 
Mysql basic statements

According to their online data, they are all written in the same way. At the same time, many of them are not well written. The following is my summary of mysql usage details, it is also some of my records in the learning process. I hope it will be helpful to you. I have not summarized the stored procedure and other related operations. I will give it to you next time, haha ~~~~~

MySql Study Notes

MySql Overview: MySql is an associated database management system. The so-called associated database stores data in different tables, rather than placing all the data in a large warehouse. This increases the speed and flexibility. MySql is an open source software.

Note: The maximum TimeStamp range supported by MySql is that on 32-bit machines, the supported value range is that the year should not exceed 2030. If the value is on a 64-bit machine, the year can reach 2106, but for date and datetime types, there is no relationship between them. They can all be expressed as 9999-12-31, so pay attention to this. Also, when installing MySql, we generally choose Typical (Typical installation). Of course, if there are other purposes, we 'd better choose Complete (full installation ); during the installation process, you are usually asked to select the Server type. There are three Server types, Developer and Server Machine), Dedicated MySql Server Machine (Dedicated MYSQL Server), select which type of Server will only affect the Configuration Wizard on memory, or other aspects will have no impact; therefore, if we are a developer, you can select the development machine. Then, you can select the database usage dialog box by default;

Connect to and disconnect the server:
Connection: enter a command set similar to the following at a windows Command Prompt: mysql-h host-u user-p
For example, when I use mysql-h localhost-u root-p
Then you will be prompted to enter the user password. At this time, if you have a password, enter the password and press Enter. If there is no password, press enter to access the database client; to connect to mysql on the remote host, run the following command: mysql-h 159.0.45.1-u root-p 123

Disconnect the server: after entering the client, you can directly enter quit and press Enter;

The following describes database commands.
You can enter the following command to perform related operations on the database table or database, which is omitted here and then directly explained in text;

Select version (), current_date; // obtain the current mysql version and date from the server.
Select user (); // obtain all users of the current database
Use databasename; enter the specified database, and then you can operate the tables in the database.
Show databases; // query all databases in the current database and display them;
Create batabase databasename; create a database, for example, Create database manager;
Show tables; // view all tables in the current database;
Create table tablename (colums); create a table and specify related columns for the table, for example, Create table pet (name varchar (20), owner varchar (20 ), species varchar (20), sex char (1), birth date, death date );

Describe tablename; display all the information in the table in detail, for example, descr ...... remaining full text>