Six common MySQL queries

6 frequently-used MySQL queries use structured query languages to develop correct and effective database questions and commands to query architectural art. In a SELECT query, you can use JOIN, WHERE, and HAVING clauses to analyze and summarize results rows and columns, and UNION. For the INSERT, DELETE, and UPDATE commands, refer to the connections. INSERT the query results of INSERT... SELECT into another table. Delete the WHERE clause within the specified range. Www.2cto.com 1. If you know the age of birth, you need to calculate the age of the person. Assume that your birth date is: SELECT DATE_FORMAT (FROM_DAYS (TO_DAYS (NOW ()-TO_DAYS (19860525), '% y') + 0; 2. The difference between two dates is found in seconds, minutes, hours, or days. If dt1 and dt2 are datetime values in the form of "YYYY-MM-DD HH: MM: ss', the number of seconds between dt1 and dt2 UNIX_TIMESTAMP (1358498312)-UNIX_TIMESTAMP (517378271); UNIX_TIMESTAMP (dt2) -UNIX_TIMESTAMP (dt1): divide the number of minutes by 60, divided by 3600, and the number of hours in the number of days by 3600*24. 3. The column value is displayed N times. www.2cto.com SELECT id FROM tbl group by id having count (*) = N; 4. Calculate the number between two dates. The simplest supported Number of working days between any two dates is the Boolean filling of all days in the D column and holidays in all possible year calendars. The following query starts AND stops during business days: select count (*) FROM calendar WHERE d BETWEEN Start AND Stop and dayofweek (d) not in) AND holiday = 0; 5. Search for the primary key table SELECT k. column_name FROM information_schema.table_constraints t JOIN information_schema.key_column_usage k USING (constraint_name, table_schema, table_name) WHERE t. constraint_type = 'Primary key' AND t. table_schema = 'db' AND t. table_name = 'tbl' 6. How big is your database SELECT table_schema AS 'db name', ROUND (SUM (data_length + index_length)/1024/1024, 3) AS 'db Size (MB) ', ROUND (SUM (data_free)/1024/1024, 3) AS 'free Space (MB)' FROM information_schema.tables group by table_schema;