Mysql commands

 

CREATE DATABASE IF NOT EXISTS database_name;
SHOW DATABASES;
USE database_name;
DROP DATABASE IF EXISTS database_name;

 

CREATE TABLE IF NOT EXISTS table_name (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(45) DEFAULT NULL,
date DATE DEFAULT NULL,
date_time DATETIME DEFAULT NULL,
description text DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE INDEX id_UNIQUE (id ASC)
) ENGINE=engine_name

column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT]

 

ALTER :

Alter Column:

syntax :

ALTER TABLE table_name CHANGE COLUMN column_name column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT];

Example :

ALTER TABLE table_name
CHANGE COLUMN id id INT(11) NOT NULL AUTO_INCREMENT

Add Column :

Syntax :

ALTER TABLE table_name ADD COLUMN column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT] AFTER existing_column_name;

Example :

ALTER TABLE table_name ADD COLUMN price DECIMAL(6,2) NULL AFTER description;

Drop Column :

syntax :

ALTER TABLE table_name DROP COLUMN column_name;

Example :

ALTER TABLE table_name DROP COLUMN price;

Rename Table :

syntax :

ALTER TABLE table_name RENAME TO new_table_name;

 

DROP TABLE :

syntax :

DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name1] … [RESTRICT | CASCADE]

Example :

DROP TABLE IF EXISTS table1, other_table;
DROP TABLE LIKE ‘%pattern%’

 

INDEX :

Create:

syntax:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name USING [BTREE | HASH | RTREE] ON table_name (column_name [(length)] [ASC | DESC],…)

Example :

CREATE INDEX name ON table_name(name);

Drop:

Syntax :

DROP INDEX index_name ON table_name

Example :

DROP INDEX name ON table_name

 

SELECT :

Syntax :

SELECT a.column_1,b.column_2…
FROM table_name AS a
[INNER | LEFT |RIGHT] JOIN table_name1 AS b ON join_conditions
WHERE conditions
GROUP BY group
HAVING group_conditions
ORDER BY column_1 [ASC | DESC]
LIMIT offset, row_count;

 

ORDER BY :

SELECT orderNumber, status
FROM orders
ORDER BY FIELD(status, ‘In Process’,
‘On Hold’,
‘Cancelled’);

 

 

 

 

 

 

 

http://www.pantz.org/software/mysql/mysqlcommands.html

http://www.mysqltutorial.org

 

Use ; to exit the from a query/command

Leave a Reply

Your email address will not be published. Required fields are marked *