MySQL Commands

Once MySQL is installed, login mysql by opening terminal (command prompt), and typing:

mysql -u root -p

To view all existing databases:

show databases;

To look inside a database:

use databaseName;

show tables;

To view table's contents:

SELECT * FROM tableName;

To create database and grant permissions:

create database csc7710;

grant all on csc7710.* To 7710user@localhost identified by '123456';

Create table:

CREATE TABLE shop (article INT(4), dealer CHAR(20), price DOUBLE(16,2));

INSERT INTO shop VALUES (1,'A',3.45), (2,'B',4.0);

View table's schema:

desc myTableName

show create table myTableName

To see all indexes of the table:

SHOW INDEX FROM mytable FROM mydb;

To explain the query:

explain myquery

, where instead of my query you put the query, e.g. SELECT * FROM Triple;

Insert XML into a table:

CREATE TABLE IF NOT EXISTS trees (name VARCHAR(15) UNIQUE, xmldoc BLOB NOT NULL);

INSERT INTO trees VALUES ('name3', '<c>23</c>');

Installed phpmyadmin, and got 404 Error in browser. Solution:

sudo ln -s /usr/share/phpmyadmin/ /var/www

Copy table (schema+all records):

CREATE TABLE student2 SELECT * FROM student

To DELETE from table:

DELETE FROM dplSpecs WHERE name <> 'one' AND name <> 'two' AND name <> 'three' AND name <> 'five' AND name <> 'seven'

To DELETE all rows from table:

DELETE FROM dplSpecs;

To insert all rows from database enginerepo1d0Dmsg1, table specs into current database, table specs (assuming they have the same schema):

INSERT IGNORE INTO specs SELECT * FROM enginerepo1d0Dmsg1.specs;

To insert rows from one table to another with overwriting duplicates (e.g. if both tables have primary keys):

replace into foo select * from foo2;

To completely remove mysql (including the info about users and their passwords) in Ubuntu OS:

sudo apt-get --purge remove mysql-server mysql-common mysql-client

To change root password:

mysqladmin -u root -p'oldPassword' password newPassword

To dump mysql database (from regular command line, not from mysql command client):

mysqldump -u root -p databaseName > someName.sql

To retrieve mysql database from that file (if the file does not contain "create database" statement):

mysql -u root -p dbName < someName.sql

To run a .sql script that contains a "create database" statement:

mysql -u root -p < someName.sql

To dump a particular record(s) from a table into a file:

mysqldump --user=andrey --password=1234 --no-create-info enginerepo1d0 specs --where="name='analyzeText10_3compsF'" > workflow.sql

To add a column to an existing table:

alter table tblName add colName tinyint(1) default 0;