how do i get mysql rows from 24-48 hours ago?
Use: SELECT * FROM YOUR_TABLE t WHERE t.datetime_column < DATE_SUB(NOW(), INTERVAL 24 HOUR) AND t.datetime_column > DATE_SUB(NOW(), INTERVAL 48 HOUR)
sql
How many tables can be created in a mysql database?
How many tables can be created in a mysql database ? MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of tables. Individual storage engines may impose engine-specific constraints. InnoDB permits up to 4 billion tables. And how many columns can be created in … Read more
Delete oldest records from database
If you use an auto-increment field, you can easily write this to delete the oldest 100 records: DELETE FROM mytable WHERE id IN (SELECT id FROM mytable ORDER BY id ASC LIMIT 100) Or, if no such field is present, use ROWID: DELETE FROM mytable WHERE ROWID IN (SELECT ROWID FROM mytable ORDER BY ROWID … Read more
Connecting to SQL Server Express – What is my server name?
Instead of giving: ./SQLEXPRESS //in the Server Name I put this: .\SQLEXPRESS //which solved my problem
SWITCH with LIKE inside SELECT query in MySQL
Mysql supports two variants of case, the one you use in query 2 is less flexible but supports only equality on a single variable. The other version specifies no variable after case and then conditions need not be only equality: select id_tag, case when tag LIKE “%class%” then “class” when tag LIKE “%new%” then “new” … Read more
Calling Scalar-valued Functions in SQL
Are you sure it’s not a Table-Valued Function? The reason I ask: CREATE FUNCTION dbo.chk_mgr(@mgr VARCHAR(50)) RETURNS @mgr_table TABLE (mgr_name VARCHAR(50)) AS BEGIN INSERT @mgr_table (mgr_name) VALUES (‘pointy haired boss’) RETURN END GO SELECT dbo.chk_mgr(‘asdf’) GO Result: Msg 4121, Level 16, State 1, Line 1 Cannot find either column “dbo” or the user-defined function or … Read more
Finding all related tables to a given table
If your database supports the information schema views (most do), then you can run this query: SELECT c.CONSTRAINT_NAME, cu.TABLE_NAME AS ReferencingTable, cu.COLUMN_NAME AS ReferencingColumn, ku.TABLE_NAME AS ReferencedTable, ku.COLUMN_NAME AS ReferencedColumn FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu ON cu.CONSTRAINT_NAME = c.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku ON ku.CONSTRAINT_NAME = c.UNIQUE_CONSTRAINT_NAME This will output a list of … Read more
SQL Inner join 2 tables with multiple column conditions and update
UPDATE T1 SET T1.Inci = T2.Inci FROM T1 INNER JOIN T2 ON T1.Brands = T2.Brands AND T1.Category= T2.Category AND T1.Date = T2.Date
MySQL disable & enable keys
You definitely have to pick your approach based on the engine type… optimizing for MyISAM or for InnoDB. We recently ran a benchmark comparing different ways to insert data and measured the time from before insertion and until all indices are fully restored. It was on an empty table, but we used up to 10 … Read more
Index all columns
It is difficult to imagine real-world scenarios where indexing every column would be useful, for the reasons mentioned above. The type of scenario would require a bunch of different queries, all accessing exactly one column of the table. Each query could be accessing a different column. The other answers don’t address the issues during the … Read more