MySql Queries

  • Create database on the sql server
    mysql> create database database_name;
  • List all the database on the sql server
    mysql> show databases;
  • Switch database
    mysql> use database_name;
  • List all the tables in the database
    mysql> show tables;
  • Show database field formats
    mysql> desc table_name; / describe table_name;
  • To delete a database
    mysql> drop database database_name;
  • To delete a table
    mysql> drop table table_name;
  • Show all data in a table
    mysql> select * from table_name;
  • Gives columns and column information related to designated table
    mysql> show columns from table_name;
  • Show certain selected rows with the value "whatever".
    mysql> select * from table_name where field_name = "whatever";
  • Show all records containing the name "peter" and deptno '121'
    mysql> select * from table_name where name="peter" and deptno=121;
  • Show all records containing the name "peter" and deptno=121 order by the deptno field
    mysql> select * from table_name where name="peter" and deptno=121 order by deptno;
  • Show all records not containing the name "peter"
    mysql> select * from table_name where name !="peter";
  • Show all records starting with letter "p"
    mysql> select * from table_name where name like "p%";
  • Show all records starting with letter "p" limit to record 1 through 5
    mysql> select * from table_name where name like "p%" limit 1,5;
  • Show unique records
    mysql> select distinct column_name from table_name;
  • Show selected records sorted in an ascending (asc) or descending(desc).
    mysql> select column1, column2 from table_name order by column1 desc;
  • Return number of rows
    mysql> select COUNT(*) from table_name;
  • Sum column
    mysql> select SUM(*) from table_name;
  • Set a root password if there is on root password.
    # mysqladmin -u root password newpassword
  • Update a root password.
    # mysqladmin -u root -p oldpassword newpassword
  • Update data already in table
    mysql> update table_name set name="xyz" where name="peter";
  • Delete row from a table
    delete from table_name where field_name = "whatever";
  • Delete column from a table
    mysql> alter table table_name  drop column column_name;
  • Add a new column to table
    mysql> alter table user add column new_column_name varchar(30);
  • Make a unique column so you get no duplicate
    mysql> alter table table_name add unique column_name;
  • Make a column bigger.
    mysql> alter table table_name modify column_name varchar(10);
  • Delete unique from table.
    mysql> alter table table_name drop index colmn_name;
  • Load a CSV file into a table
    mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE table_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' 
    (field1,field2,field3);
  • Dump all databases for backup. Backup file is sql commands to recreate all databases
    # [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/databases.sql
  • Dump one database for backup.
    # [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/database.sql
  • Dump a table from a database.
    # [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
  • Restore database (or database table) from backup.
    # [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
  • Create Table
    mysql>create table table_name (id int(20) not null auto_increment primary key, firstname VARCHAR(20), lastname VARCHAR(30), officeid 
    VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp 
    time);
  • Table join
    SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
    FROM Orders
    INNER JOIN Customers
    ON Orders.CustomerID=Customers.CustomerID;