Basic usage in MySQL, and conversion of dates

1, MySQL int (10)
The int type is 4 bytes long, approximately represents the 2^32 number, 10 represents the display length, and the general and fillzero constraints are used together, if the length is not reached, the padding 0
2-->000000002
MySQL varchar maximum length can be set to 65535, but is generally set to 255
MySQL Double,float (5,2) represents a total length of 5, and the decimal point is two lengths
2. Data import and Export
SQL format XLS format
Make the queried data into a table:
Select Name,salary from worker into outfile ' C:/abc.xls ';
3. How to configure your MySQL server to allow remote connections.
Method One: The method
Find the MySQL database, find the UESR table, and find the User=root,host=locahost record.
Change to host as%, restart MySQL service
Method Two: Through SQL statements
Grant all privileges on * * to [email protected] '% ' identified by ' root ';//Assign Permissions
Flush priviledges;//Refresh Permissions
4, Temporary table intermediate table dual
Select 7*8;==select 7*8 from dual;
The function is only for the sake of syntax, the table in Oracle cannot be omitted.
5. Three Paradigms of database design
1) Each field in the first Paradigm table is a separate, indivisible unit
User
ID Name
1 Sam Fung | Zhang San
Modified to ID name ex_name "used name"
2) The second paradigm meets the former embodiment of the first paradigm
A table can only represent one meaning, and each field must have a dependency on the primary key
Employee
ID Name age Sex salary {Deptname DeptNo} violated the second paradigm
ID Name Age Sex salary
Dept Department table
ID DeptNo deptname
Technical department is now called Research and Development Department
3) The former embodiment of the third paradigm in satisfying the second paradigm
A table can only represent one meaning, except for the primary key, each field can only have a dependency on the primary key.
Employee
ID Name age Sex salary {Deptname DeptNo} violated the second paradigm
ID name age Sex salary DeptID deptname third paradigm design and second paradigm can be combined to understand
Dept Department table
ID DeptNo deptname
Technical department is now called Research and Development Department

The three paradigms of database design: Just design specifications, reference

6. Joint Inquiry
Union: Merging result sets to remove duplicate data

UNION ALL: Do not go heavy
The number of columns in the previous query and the subsequent result set are equal and the recommended types are the same
7. Usage of any and some and all
The effect of any and some is the same as any of the parentheses, equal to or use
All refers to multiple data in parentheses, equivalent to the use of and
8, the date of birth to find out the age
SELECT year (From_days-DateDiff (now (), birthdate)); ==>birthdate is the date of birth
9, find out the current date: Now ()
(1) current_timestamp () Current_timestamp

(2) localtime () localtime

(3) Localtimestamp () Localtimestamp
These three kinds are all to find out the current date, is equivalent to now ();

Basic usage in MySQL, and conversion of dates