How does mysql query records for a period of time
Mysql query method for a period of time records: 1. Query records within N days, the code is [WHERE TO_DAYS(NOW()) – TO_DAYS(time field) <= N]; 2. Query today’s records, code It is [where date (time field) = date (now ())]. More related free learning recommendation: mysql tutorial (video) mysql query section Time recording method: Record within 24 hours (ie 86400 seconds) $sql=”SELECT video_id, count(id) as n FROM `rec_down` WHERE UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(add_time)<=86400 group by video_id order by n desc "; $sql="select a.id,a.title,b.n from video_info a,(".$sql.")b where a.id=b.video_id order by n desc limit 20"; Record within N days WHERE TO_DAYS(NOW()) – TO_DAYS(time field) <= N Today’s record where date(time field)=date(now()) or where to_days(time field) = to_days(now()); Query a week: select * from table where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(column_time); Query One month: select * from table where DATE_SUB(CURDATE(), INTERVAL INTERVAL 1 MONTH) <= date(column_time); pre>The query selects all records with date_col values within the last 30 days. mysql> SELECT something FROM tbl_name WHERE TO_DAYS(NOW()) – TO_DAYS(date_col) <= 30; //It's really convenient, I used to write it by myself, but I didn't know it, so it failed. DAYOFWEEK(date) Returns the day of the week index of date (1 = Sunday, 2 = Monday,…
What is the command to delete a table in SQL language?
In the sql language, the command to delete a table is “DROP”, and the specific syntax format is “DROP TABLE [IF EXISTS] table name”. The “DROP TABLE” command can delete multiple tables at the same time, as long as the table names are written one after the other, separated by commas. (Recommended tutorial: mysql video tutorial) In the SQL language, the command to delete a table is ( ). A． DELETE B． DROP C． CLEAR D． REMOVE Answer:B． DROP Answer analysis In the SQL language, the command to create a table is CREATE, the command to modify the table is ALTER, delete The command for a table is DROP, and the inserting, deleting and querying commands for data in the table are INSERT, UPDATE and SELECT respectively. Extended information: In the MySQL database, for data tables that are no longer needed, we can delete them from the database. While deleting the table, the structure of the table and all the data in the table will be deleted, so it is best to back up the table before deleting the data table to avoid irreparable losses. Use the DROP TABLE statement to delete one or more data tables, the syntax is…
Finally understand that MySQL index needs to use B+tree, and it is so fast
The mysql tutorial column introduces the B+tree for understanding indexes. Tip : When there are two middle keywords, the left keyword is usually split up. Delete The delete operation will be more troublesome than the search and insert, because the keyword to be deleted may be on the leaf node, or It may not be there, and the deletion may cause the imbalance of the B-tree, and operations such as merging and rotation are required to maintain the balance of the whole tree. Take a random tree (level 5) as an example The above is the final understanding that the MySQL index uses B+tree, and it is so fast. For more details, please pay attention to other related articles on 1024programmer.com !
What to do if the mysql database service cannot be found
The solution to the failure of the mysql database service: first open cmd, switch to the mysql bin directory [D:\Program Files\MySQL5.1\bin>]; then enter [mysqld.exe -install] Order. Mysql database service can not find the solution: After installing the MySQL database, in the control The MySQL service startup item cannot be found in the service list of the panel If we use the green free installation version of the mysql database, sometimes there will be a problem that the mysql database cannot be found Solution : Open cmd, switch to the mysql bin directory D:\Program Files\MySQL5.1\bin>, and enter the mysqld.exe -install command If it prompts Service successfully installed. (note: if the result is not Service successfully installed; but Install/Remove of the Service Denied !, why this result: because the permissions are not enough, the solution is to open cmd as an administrator: Input cmd in the input box, cmd under the program in the displayed list Right-click on .exe and select run as administrator) More related free learning recommendation: mysql tutorial (video) The above is the mysql database service I can’t find the detailed content of what to do, please pay attention to other related articles on 1024programmer.com for more information!
How to write the cursor in mysql
The method of cursor writing in mysql: first declare the cursor; then open the cursor, the code is [OPEN cursor_name]; then capture the cursor; finally close the cursor, the code is [CLOSE cursor_name]. More related free learning recommendation: mysql tutorial (video) mysql cursor Writing method: 1, what is a cursor In a stored procedure or function, you can use the cursor to cycle through the result set . The following is my personal opinion, the cursor is similar to the auto-increment variable i in the java loop. 2, the use of the cursor The use of the cursor includes the following three steps: 1, declare the cursor Format: DECLARE cursor_name CURSOR FOR select_statement; cursor_name: Cursor name, set by the user, it is best to know the name. select_statement: The complete query statement, the column name in the query table (detailed explanation of the following cases). 2. Turn on the cursor cursor_name: the cursor name at the time of declaration. Format: OPEN cursor_name; 3, capture cursor Format: FETCH cursor_name INTO var_name…; (…indicates that there can be more than one) cursor_name: the cursor name at the time of declaration. var_name: Custom variable name. (Detailed explanation of the following cases) 4. Close the…
MySQL database executes analyze to collect information
MySQL TutorialThe column executes analyze to collect information. Introduction to the fault Before, a developer came to me and asked about a certain function of the application It was much slower than before, so the development provided slow SQL statements. I went to the corresponding MySQL database to look at the execution plan and found that the execution plan was incorrect. The first reaction was that the statistical information of one of the tables was inaccurate, resulting in the SQL statement The execution plan is wrong, from efficient query SQL to slow SQL. After locating the problem, it is natural to analyze and collect information again. At this time, all the selects on the analyze table suddenly get stuck and do not return any results. Then the application explodes, and various alarm messages are sent. Fault recovery At that time, the analyze operation was performed by a slave library, which was basically affected by the select query, so the query is simulated here operate. Create a simulation table mysql> select * from t_test_1; +—-+——–+——-+——–+ | id | name | name2 | status | +—-+——–+——-+——–+ | 1 | name1 | 1001 | 0 | | 2 | name1 | 1002 |…
Summarize the difference between float, double and decimal in MySQL
Mysql Video TutorialThe column summarizes the difference between the three floating point types in MySQL The storage size and range of each floating point type are planned in the following table: type size range (signed) range (unsigned) use ==float== 4 bytes (-3.402 823 466 E+38, -1.175 494 351 E-38), 0, (1.175 494 351 E -38, 3.402 823 466 351 E+38) 0, (1.175 494 351 E-38, 3.402 823 466 E+38) Single-precision floating-point value ==double== 8 bytes (-1.797 693 134 862 315 7 E+308, -2.225073858507 2014E-308), 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308) 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308) Double precision floating point Value decimal is for decimal(M,D), if M>D, it is M+2 Otherwise D+2 Depends on the value of M and D Depends on the value of M and D Decimal value Then these three types in MySQL are floating point types and what is the difference between them? ? float floating point type is used to represent==single precision floating point==value, double floating point type is used to represent==double precision floating point Point == value Some friends here must ask what is single precision…
What is the function of stored procedure in database
The role of stored procedures in the database: 1. Stored procedures can accept parameters, output parameters, return single or multiple result sets and return values; 2. Stored procedures are relatively stable, and there will not be too many Error; 3. The stored procedure is mainly run on the server, reducing the pressure on the client. Extended information: Advantages of stored procedures: 1. The ability of stored procedures greatly enhances SQL The power and flexibility of the language. 2. Data security and integrity can be guaranteed. 3. Through stored procedures, users without authority can indirectly access the database under control, thereby ensuring data security. 4. Through stored procedures, related actions can occur together, so that the integrity of the database can be maintained. 5. Before running the stored procedure, the database has analyzed its syntax and syntax, and provided an optimized execution plan. This compiled process can greatly improve the performance of SQL statements. 6. It can reduce the traffic of the network. 7. Put the calculation program embodying enterprise rules into the database server for centralized control. More related free learning recommendations: mysql tutorial (video) The above is the detailed content of the role of the stored procedure in the…
What is the difference between inner join and outer join?
Difference: the inner join query operation lists the data rows that match the join conditions; the outer join, returns to the query result set not only contains the rows that meet the join conditions, but also includes the left table (left outer join) , all data rows in the right table (right outer join), or both edge join tables (full outer join). The connection of two tables is established by linking one or more columns in one table with columns in the other table. The expressions used to join two tables form the join condition. When the connection is successful, the data in the second table is connected with the first table, and a composite result set is formed-including the plan of the data rows in the two tables. Simply put, there are subsets of the two tables, although only temporarily There are two basic types of connections, inner and outer connections. The main difference between the two types is that an outer join will return rows in the result set even if the join conditions are not satisfied, while an inner join will not return rows in the result set class When the external connection does not meet the…
