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:
    • mysql -u root -p dbName < 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;