Differences

This shows you the differences between two versions of the page.

--- fuss:mysql [2016/05/28 18:58] – [Import Multiple SQL Files] office
+++ fuss:mysql [2022/04/19 08:28] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
+====== List Users ======
+<code sql>
+SELECT user FROM mysql.user;
+</code>
+====== Reset Passwords ======
+Sets the password ''secretpassword'' for user ''someuser''.
+<code sql>
+UPDATE mysql.user SET password=PASSWORD('secretpassword') WHERE user='someuser';
+</code>
+====== List the Size of Databases ======
+<code sql>
+SELECT table_schema "Database", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "MB" FROM information_schema.tables GROUP  BY table_schema;
+</code>
+====== Select Random Records ======
+The following query, selects one random entry from the table ''table''.
+<code sql>
+SELECT * FROM table ORDER BY RAND()  LIMIT 1
+</code>
+====== Select Using RegExp ======
+This query will select ''lastname'' and ''firstname'' from the table ''employees'' with the condition that the first character of ''lastname'' match either of the uppercase letters ''M'', ''B'' or ''T'':
+<code sql>
+SELECT lastname, firstname FROM employees WHERE lastname REGEXP  '^(M|B|T)';
+</code>
+====== Comma-separated Values ======
+A structurally simple format is the ''CSV'' format that is supported by many data processing software packages (Excel, etc...) and MySQL can both import and export from ''CSV''.
+===== Export =====
+This line exports data from the table ''account'' to the file ''/tmp/dump.csv'' in a comma-separated value format.
+<code sql>
+SELECT * FROM account INTO OUTFILE '/tmp/dump.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
+</code>
+===== Import =====
+Imports are preformed symmetrically, by preserving the ''FIELD'' and ''LINES'' setting:
+<code sql>
+LOAD DATA INFILE '/tmp/dump.csv' INTO TABLE account FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
+</code>
+====== Dump and Restore a Database ======
+To dump a database to a file, issue:
+<code bash>
+mysqldump -u root -p database > database.sql
+</code>
+to restore a database from a file, issue:
+<code bash>
+mysqldump -u root -p database < database.sql
+</code>
+====== Sum-Up Columns in MySQL Table ======
+Given the following table:
+^ ''Name''       ^ ''Score''  ^
+| ''Amanda Lay'' | ''5''      |
+| ''Ty Tass''    | ''11''     |
+| ''Ty Tass''    | ''2''      |
+| ''Lou Sirr''   | ''7''      |
+| ''Amanda Lay'' | ''4''      |
+| ''Lou Sirr''   | ''20''     |
+we want to find out the total score for ''Ty Ass''. We can then write the SQL query:
+<code sql>
+SELECT Name,SUM(Score) AS Total FROM table WHERE Name='Ty Ass';
+</code>
+which will return the following table:
+^ ''Name''       ^ ''Total''  ^
+| ''Ty Tass''    | ''13''     |
+The same can be done if the scores are distributed on multiple columns:
+^ ''Name''       ^ ''Crouched'' ^ ''Sideways'' ^  ''Laying'' ^
+| ''Amanda Lay'' | ''5''        | ''7''        | ''30''      |
+To find out the total, summative score for ''Amanda Lay'', we use the query:
+<code sql>
+SELECT Name,(Crouched + Sideways + Laying) AS Total FROM table WHERE Name='Amanda Lay';
+</code>
+which will return the table:
+^ ''Name''       ^ ''Total'' ^
+| ''Amanda Lay'' | ''42''    |
+====== Empty a Database Without Dropping ======
+To empty the database ''mydatabase'', issue:
+<code sql>
+SELECT CONCAT('DROP TABLE IF EXISTS ', table_name, ';') FROM information_schema.tables WHERE table_schema = 'mydatabase';
+</code>
+====== Repair MySQL Tables ======
+<code bash>
+mysql -Be "select concat('repair table ', TABLE_SCHEMA, '.', table_name, ';') from information_schema.tables where table_schema<>'information_schema';" | tail -n+2 | mysql
+</code>
+or
+<code bash>
+mysqlcheck --auto-repair -A
+</code>
+====== Reset Root Password ======
+First, stop MySQL using:
+<code bash>
+/etc/init.d/mysql stop
+</code>
+Now we run MySQL by omitting to read the grant tables:
+<code bash>
+mysqld --skip-grant-tables
+</code>
+And we log-in as ''root'':
+<code bash>
+mysql -u root
+</code>
+Then we run a query to replace the root password:
+<code sql>
+FLUSH PRIVILEGES;
+UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';
+FLUSH PRIVILEGES;
+exit
+</code>
+And after that we restart MySQL:
+<code bash>
+/etc/init.d/mysql restart
+</code>
+====== Import Multiple SQL Files ======
+In a directory containing ''.sql'' files, run:
+<code bash>
+ls -1 *.sql | awk '{ print "source",$0 }' | mysql --batch -u username --password=password database
+</code>
+where:
+  * ''username'' is the user name allowed to import
+  * ''password'' is the password for the user
+  * ''database'' is the database to import the data to
+====== Drop all Tables in Database ======
+This can be accomplished with:
+<code bash>
+mysql -u USER --password=PASSWORD -Nse 'SHOW TABLES' DATABASE | while read table; do mysql -u USER --password=PASSWORD -e "DROP TABLE $table" DATABASE; done
+</code>
+where:
+  * ''USER'' is the username to use
+  * ''PASSWORD'' is the password for the user
+  * ''DATABASE'' is the database to drop all the tables in
+====== Truncate all Tables in Database ======
+This can be done with:
+<code bash>
+mysql -u USER --password=PASSWORD -Nse 'show tables' DATABASE | while read table; do mysql -u USER --password=PASSWORD -e "TRUNCATE TABLE $table" DATABASE; done
+</code>
+where:
+  * ''USER'' is the username to use
+  * ''PASSWORD'' is the password for the user
+  * ''DATABASE'' is the database to truncate all the tables in