SQL Query : Part 1

You are probably thinking that this is going to be another sql query tutorial. Well the answer is both yes and no. I am tired of searching and reading all those sql tutorials again and again where each of this tutorials explain all those concept from top to bottom, which I don't need. We programmers easily forgot sql query syntax but not the concept. So I created my own list to save a lot of searching time.

I am not posting all queries in a single post. I had divided all queries into several parts. This is the first part.

I am using mysql for the RDMS. I also includes some common mysql command along with sql queries.

This post only contains queries which includes database creation, table creation, view data from table.

Start mysql server

mysql -u root -p

This command will prompt for password,  enter the password you set for your local mysql server.

Resetting root user password for mysql server

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

View all databases

SHOW DATABASES;

 Create a new database

CREATE DATABASE name_of_db;

Use that newly created database

USE name_of_db;

Delete database

DROP DATABASE name_of_db;

Show all tables of the database

SHOW TABLES;

Create a table in database

CREATE TABLE
(
   column_name1 dataType1,
   column_name2 dataType2,
   ....
   ....
   column_nameN dataTypeN
);

Setting table columns to have not null values

CREATE TABLE
(
   column_name1 dataType1 NOT NULL,
   column_name2 dataType2 NOT NULL,
   ....
   ....
   column_nameN dataTypeN NOT NULL
);

Setting table columns to hold a default value

CREATE TABLE
(
   column_name1 dataType1 NOT NULL DEFAULT default_value1,
   column_name2 dataType2 NOT NULL DEFAULT default_value2,
   ....
   ....
   column_nameN dataTypeN NOT NULL DEFAULT default_valueN
);

View the description of table

DESC name_of_table;

Delete a table of database

DROP TABLE name_of_table;

Insert a row in a table

INSERT INTO table_name
(column_name1, column_name2, ......., column_nameN)
VALUES
(value1, value2, ........, valueN);

Variation on an Insert statement

Changing the order of columns

INSERT INTO table_name
(column_name3, column_name10, ....., column_nameN, column_name5)
VALUES
(value3, value10, ......., valueN, value5);

You can change the order of your column names as long as the matching values for each column come in the same order.

Omitting column names

INSERT INTO table_name
VALUES
(value1, value2, ........., valueN);

You can leave out the list of columns , but the values must be all there and the same order that you initialised when creating your table.

Leaving some columns out

INSERT INTO table_name
(column_name1, column_name5, column_nameN)
VALUES
(value1, value5, valueN);

You can insert a few columns and leave some out.

View all data in a table

SELECT * FROM table_name;

I will post another set of sql queries in part 2 , hopefully next week. Till then be happy and enjoy yourself.