mysql_commands.rst

MySQL Sample Commands

Sample database can be found in the sql directory of this repository. You can install xampp software to run Apache and MySQL server on your machine.

Show all the existing databases

SHOW DATABASES;

Create Database testDB if it does not exist

CREATE DATABASE testDB IF NOT EXISTS;

Delete Database testDB if it exists

DROP DATABASE IF EXISTS testDB;

Begin using the database name testDB

USE testDB;

Show system status

SHOW STATUS;

Show MySQL environment variables

SHOW VARIABLES;

Select all the records (rows) from Country table

SELECT *
FROM Country

Select specific columns from the Country table

SELECT Name, Code, Population
FROM Country

Create Alias (alternate names) a column

SELECT Name AS 'country-name', Population as 'country-population'
FROM country;

Order the rows by Name column in ascending order

SELECT *
FROM country
ORDER BY Name ASC;

Order the rows by Name column in descending order

SELECT *
FROM country
ORDER BY Name DESC;

First order by Continent and then Name within each Continent

SELECT Name, Continent
FROM country
ORDER BY Continent, Name DESC;

Order by content name in descending order and then name in ascending order

SELECT Name, Continent
FROM country
ORDER BY Continent DESC, Name;

Limit clause to limit the number of rows returned

SELECT *
FROM Country
LIMIT 5;

Offset

SELECT * from Country ORDER BY Name LIMIT 5 OFFSET 5;

Where clause

SELECT Name,Code,population from Country WHERE population > 100000000 ORDER BY population;

Null clause

SELECT Name,Code,population
FROM Country
WHERE population > 100000000 OR population IS NULL
ORDER BY population;

OR clause

SELECT Name,Code,population
FROM Country
WHERE population > 100000000 OR population IS NULL
ORDER BY population;

NOT Null clause

SELECT Name,Code,population
FROM Country
WHERE population > 100000000 OR population IS NOT NULL
ORDER BY population;

Null clause

SELECT Name,Code,population
FROM Country
WHERE population > 100000000 AND continent = 'Asia'
ORDER BY population;

Where clause with string match

SELECT Name,Code from Country WHERE Code = 'USA’;

Like clause

SELECT Name, continent, population
FROM Country
WHERE name like 'ind%’;

Like clause with single character

SELECT Name, continent, population
FROM Country
WHERE name like ‘_a%’;

IN clause

SELECT Name, continent, population
FROM Country
WHERE continent IN ('Asia', 'Europe')
ORDER BY continent;

Regex

SELECT Name, continent, population
FROM Country
WHERE Name REGEXP '^.[a-d].*’;

Create table

CREATE TABLE test (a INT, b TEXT, C TEXT);

Insert row

INSERT INTO test
VALUES (1, 'First Value’, ’Second Value')

Insert row specifying the columns

INSERT INTO test
(a,b) VALUES (1, 'First Value')

Insert row by Selecting rows from other table

INSERT INTO test
(a, b, c)
SELECT id, name, description FROM item;

Updating a row

UPDATE test SET c = ’Something else’
WHERE a = 1;

Delete a row with only its first occurrance

DELETE
FROM test
WHERE a=1
LIMIT 1;
SELECT * FROM test;

Delete a table

DROP TABLE test

Describe table <— MySQL specific

DESCRIBE test;

Verbose table structure

SHOW TABLE STATUS;

Index while creating table

INDEX(a)

Show index

SHOW INDEXES FROM test;

Modify the table at a later stage

ALTER TABLE test ADD d VARCHAR(10);

Remove a column

ALTER TABLE test DROP d;

Add a column with more options

ALTER TABLE test
ADD d VARCHAR(10)
AFTER a DEFAULT ’something’;

Timezone

SHOW VARIABLES LIKE ‘%time_zone%’;
     SELECT NOW()
     SET TIMEZONE = ‘US/Eastern’

String functions - Length of a value <— counts the bytes

SELECT Name, LocalName, Length(LocalName) AS len FROM Country where Continent = 'Europe' ORDER BY len;

CHAR_LENGTH counts characters

Left 3 characters (similarly for Right and Mid):

Left(Name, 3)

Concatenation

CONCAT(Name, LocalName)

Char position

LOCATE(’string’, ‘bigString’)

Case

UPPER(Name) or LOWER(Name)

Reverse a string

REVERSE(Name)

Algebra

SELECT 5+5;

Others

POWER(2,3), ABS(-5), SIGN, CONV (to convert base), ROUND, TRUNCATE, RAND

Date and Time

NOW(), UNIX_TIMESTAMP(), DAYOFMONTH, MONTHNAME

GROUP BY

SELECT Continent, COUNT(*) as count from Country GROUP BY Continent ORDER BY count DESC;

Scan the table and get the count of distinct values

COUNT(DISTINCT NAME)

Maintaining the integrity of database

COMIT and ROLLBACK

TRIGGER

CREATE TRIGGER …

SELECT statement to be used as data for another SELECT statement

SUBSELECT

View if you would want to use a query multiple times

CREATE VIEW viewName

Check the current user

SELECT USER();

Show all the users, hosts and passwords

Create user

Delete user

Grant all permissions to a user

Grant some select permissions to a user

Export everything to a .csv file

Inspired by MySQL Essential Training