MySQL Knowledge Base

MySQL Commands (part deux)

Querying between 2 dates 6
SELECT * FROM `objects` WHERE (date_field BETWEEN '2010-01-30' AND '2010-09-29')
To select by month or year from date type
select * from stocks__mine where MONTH(purchase_date) = 9;
To display raw date (nontabular output) 4
$ mysql -s -r -u user_name -p
Get MySQL engine (and other info) for a specific table 1
SHOW TABLE STATUS WHERE Name = 'xxx'
Delete all data in a MySQL table and reset auto increment field to zero 2
TRUNCATE TABLE tablename;
Delete all data in a MySQL table but keep auto increment seed value 2
DELETE FROM tablename;
Copy a range of rows from one table to another 3
INSERT INTO 2018_1023_to_2019_0514 SELECT * FROM windsor_group__seventh WHERE id BETWEEN 12 AND 41;
Export MySQL rercords to csv file
SELECT date, quarters, dimes, nickles FROM table_name INTO OUTFILE '/var/lib/mysql-files/file_name.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
Insert information from one table into another table
INSERT INTO all_items (item_name, expiry_date) SELECT name__condiment, expiry_date FROM condiments;
Show MySQL Tables from the Command Line 5
mysql -u user -p -e 'SHOW TABLES FROM database_name;'
Get a COUNT Against a Grouped Column 7
SELECT section, COUNT(*) as how_many FROM hash_table GROUP BY section ORDER BY how_many DESC

+---------+----------+
| section | how_many |
+---------+----------+
|      13 |       50 |
|      12 |       44 |
|       8 |       26 |
|      14 |       26 |
|      11 |       25 |
|      10 |       23 |
|       9 |       16 |
|       7 |       12 |
|       4 |        8 |
|       5 |        1 |
|       6 |        1 |
|      18 |        1 |
+---------+----------+
Update Column to CURRENT_TIMESTAMP when Row is Updated in MySQL 8
ALTER TABLE employee MODIFY 
COLUMN empLoginTime 
TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP;
Add CURRENT_TIMESTAMP Column 10
ALTER TABLE table_name ADD recordDateTime DATETIME NOT NULL DEFAULT NOW();
Add CURRENT_TIMESTAMP Column that Updates When Changed (1) 10
ALTER TABLE table_name ADD recordDateTime DATETIME DEFAULT NOW() ON UPDATE NOW();
Add CURRENT_TIMESTAMP Column that Updates When Changed (2) 10
ALTER TABLE table_name ADD recordDateTime DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW();
Count Distinct Values 9
SELECT COUNT(DISTINCT client_number) as DistinctValues FROM DistinctDemo;
How to Get the Size of a Table 10, 11
SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
    AND table_name = "$TABLE_NAME";
NULL
NULL