Databases

For example, if you have a table with 100 rows in it and you need to get 3 random rows then this could come in handy. 

SELECT * FROM ads ORDER BY RAND() LIMIT 3

We’ve used the RAND() function to return random table rows

Use the following command in the Command Line Interface

mysqldump --no-data --tables -uMYSQL_USER_NAME -pMYSQL_PASSWORD MYSQLDATABASE >> FILE_NAME.sql

Replace MYSQL_USER_NAME with your username,  MYSQL_PASSWORD with your password, MYSQLDATABASE with the database name you want to export andFILE_NAME.sql with the file that you wish to export the schema to.

NOTE: Please note that this is done in the Command Line Interface. First login to the mySql server using root. mysql -uroot -pROOT_PASSWORD Replace ROOT_PASSWORD with the password for the root user. Once logged in we create a new database called TEST_DB replace with your database name create database TESTDB; Query OK, 1 row affected (0.00 sec) We grant access privileges to user MYUSER to connect...

You will need to know the current root password in order to change it
In the command line type in and execute the following command

mysqladmin -u root -pOLD_PASSWORD password NEW_PASSWORD

Replace OLD_PASSWORD with your old password and NEW_PASSWORD with your new password.

Error: 1194 SQLSTATE: HY000: means ER_CRASHED_ON_USAGE In the mySql command line you should run the following command to repair the table repair table mytable; //Replace mytable with your table name REPAIR TABLE  repairs a possibly corrupted table. To read more about the REPAIR TABLE command click here. NOTE: If you do not have access to the command line then phpMyAdmin should have a repair table...

RENAME TABLE oldTable TO newTable;

Replace oldTable with the table’s name that you want to rename and newTable with the new table name.

Login to the MySql command line using something like the following

mysql -uUSERNAME -pPASSWORD

Then execute the following command to Drop the desired Database

DROP TABLE MYDATABASE;

Replace MYDATABASE with the Database name that you want to Drop
Note: Please note that this can’t be undone, so please create a backup before proceeding.

After your database has been selected using the use command eg.

use database;

you can type the show tables command to list all tables within the selected databaseeg.

show tables;

Type in the following command

SELECT id,clientid,company FROM config INTO OUTFILE '/tmp/List.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

The command above will output the results from the MySql query into a Comma Separated File called List.csv, it will split the columns by using a comma “,” and will terminate every new line with the line break character \n

The code below will do the trick, this line can be modified to accomodate your needs. A where clause can also be added

UPDATE databaseTableName SET tableColumnName = replace(tableColumnName,"findValue","replaceValue");

The CONCAT function can be used to concatenate two strings to form a single string or two columns to form a single column. Say for Example you have a table users with columns name and surname and you would like to display both as one name you would use the CONCAT function to join the two fields as in the Example below SELECT CONCAT(name," ",surname) AS fullname FROM users; The above code will...

Select your database via the command line:

USE your_database;

Then execute the following two commands:

show variables like "character_set_database";

and

show variables like "collation_database";

Solution 1 Upgrade your MySql server. After the upgrade reboot the server mysql_upgrade -u root -p --force systemctl restart mysqld Solution 2 This solution is not permanent. After the next reboot of the MySql server all changes will be lost. mysql -u app -p mysql> set @@global.show_compatibility_56=ON; Solution 3 This solution is permanent. Locate and edit your MySql config file: my.cnf Under...