MySQL Commands (UNIX)

Viewing / Using / Logging On

• Log in as Root and Use the MyDQL DB:
  • shell> mysql --user=root mysql
  • shell> mysql -u root@<hostname>
• Show Databases:
  • mysql> show databases;
  • shell> mysqlshow -u root@<hostname>
• Use a Database:
  • mysql> use <databasename>;
• Show all Tables in the Database:
  • mysql> show tables;
• Describe column details of a Table:
• mysql> describe <tablename>;
• Show Records in Table:
• mysql> select * from mytable;

Database: Users

• Show MySQL users:
  • mysql> use mysql;
  • mysql> select host,user,password from user;
• Add Users:
  • mysql> all privileges on *.* to '<mysqlusername>'@'creativelogichome.ipowermysql.com' identified by 'mypassword' with grant option;

Backups / Restores

• Dump Database:
  • shell> mysqldump -u <mysqlusername> -p <databasename> > mydb.sql
• Import Database SQL Dump into a New Database:
  • shell> mysql -u <mysqlusername> -p <databasename> < /bacups/mydb.sql
• Backup Select Rows to a Text File:
  • shell> select * INTO OUTFILE "myBackupTable.txt" from <tablename>;
• Advanced Backup Select Rows to a Text File (.csv):
  • select * INTO OUTFILE "/tmp/myBackupTable.csv"
  • fields terminated by "," optionally enclosed by '"'
  • lines terminated by '\r\n'
  • from <tablename>;

Creating

• Create a New Database:
  • mysql> create database <dbname>;
    
• Create a New Table in the Database:
  • mysql> create table <tablename> (field1 type(len), field2 type(len));
  • ex. mysql> create table phonebook (name varchar(20), phone varchar(11));
    
• Insert a new Record in this New Table:
  • mysql> insert into phonebook values ("Homer Simpson", "555-5594");
• Insert data from one Table into another Table:
  • mysql> insert into <targetTable> select <column> from <sourceTable>;
    
• Alter: Add a Column to a Table:
  • mysql> alter table <tablename> add column <newcolumn> char(25);

Importing Files Into a Database

• Import a notepad created .csv file into a Table:
  • mysql> load data local infile "/phone.csv" into table phonebook fields terminated by "," lines terminated by "\r\n";

Deleting

• Remove a Database:
  • mysql> mysqladmin -u <username> -p drop <databasename>;
• Remove all Records from a Table:
  • mysql> truncate table <tablename>;
• Remove a Table from a Database:
  • mysql> drop table <tablename>;
• Remove a Row from a Table:
  • mysql> delete from <tablename> where <field> like "<target>";
  • ex. mysql> delete from phonebook where name not like "Marge Simp%";
• Remove a Column from a Table:
  • mysql> alter table <tablename> drop column <columnname>;

Update / Replace

• Update / Modify a Record:
  • mysql> update phonebook set phone="555-5593" where phone="555-5594";
• Search and Repalce Text in a Record (ex. phone=555-5593 to 555.5593):
  • mysql> update phonebook set phone=replace('-','.');

Other

• Count Records: mysql> select count(*) from <tablename>;
• Select Random Records From a Table: mysql> select * from <tablename> order by rand() limit 1;
• Show Only Unique Values: mysql> select distinct * from <tablename>;

MySQL

• Show Version and Proc:
  • shell> mysqladmin -u root -p version proc
  • mysql> select version();
• MySQL Help, List Server-Side Help:
  • mysql> help contents

Multiple Tables

• Select: Select from 2 Tables:
  • mysql> select employees.firstname, employees.lastname, vehicles.car from employees, vehicles;

Empty

• Empty

Empty

• Empty