MySQL Queries

User Administration

Syntax:

1
CREATE USER '<username>'@'<ip address>' IDENTiFIED BY '<password>'

Example:

1
CREATE USER 'root'@'192.168.1.10' IDENTIFIED BY 'mysecretpass'

Give all access:

1
GRANT ALL PRIVILEGES ON *.* TO '<username>'@'<ip>' IDENNTIFIED BY '<password>'

Give access to specific DB:

1
GRANT ALL PRIVILEGES ON <dbname>.* TO '<username>'@'<ip>' IDENTIFIED BY '<password>'

Database Queries

To show all the databases:

1
SHOW DATABASES;

Creating a database:

1
CREATE DATABASE database_name;

Delete database:

1
DROP DATABASE db_name;

Selecting the database:

1
USE database_name;

List all the table in a DB:

1
SHOW TABLES;

Table Queries

Show all the information of a table:

1
DESCRIBE tb_name;

Creating a table:

Syntax:

1
CREATE TABLE tb_name(column1_name column1_type, column2_name column2_type, ...);

Types:

  • CHAR(size) - Character column | Holds 255 chars.
  • VARCHAR(size) - String | Holds 255 chars.
  • TEXT - String | Larger than 255.
  • INT(size) - 32 bit integer | size in the number of digits.
  • UNSIGNED INT - 32 bit unsigned integer.
  • FLOAT(size, d) - Floating number | size is the number of digits | d is the number of digits after decimal point.
  • DOUBLE(size, d) - Same as FLOAT but 64 bit.
  • DATE() - YYYY-MM-DD
  • DATETIME() - YYYY-MM-DD HH:MI:SS
  • TIMESTAMP - YYYY-MM-DD HH:MI:SS

Example:

1
CREATE TABLE movies(id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), rating FLOAT(5), cast VARCHAR(255), story TEXT, TIMESTAMP);

Inserting records in a table:

1
INSERT INTO tb_name(column1, column2, ...) VALUES(val1, val2, ...);

Changing existing record of a table:

1
UPDATE tb_name SET column1 = value1, column2 = value2 WHERE condition;

Count number of rows/records in a table:

1
SELECT COUNT(*) FROM table_name;

Delete records from table:

1
2
DELETE FROM tb_name WHERE condition;
DELETE FROM tb_name;	--Will delete all records

Altering the table data(adding new column, deleting column, changing data type of a column):

1
2
3
ALTER TABLE tb_name ADD column_name data_type;
ALTER TABLE tb_name DROP column_name;
ALTER TABLE tb_name MODIFY column_name new_data_tyep;

Delete all the table data but not the table:

1
TRUNCATE TABLE tb_name;

Delete table:

1
DROP TABLE tb_name;

Fetching Data From a Table

Selecting records from table:

1
2
3
4
5
SELECT column1, column2, ... FROM tb_name;
SELECT * FROM tb_name;
SELECT DISTINCT column1, column2, ... FROM tb_name; --Will return only uniqe values
SELECT * FROM tb_name WHERE condition;
SELECT CAST(float_column as DECIMAL(30, 16)) FROM tb_name;