DBMS day-4 working with tables

Today i will be explaining some DBMS syntax and concepts based on my experience with mysql.
Read my previous post before reading further to have better understanding.
So for the messaging module we have created a good schema. Now it's time for creating tables and inserting values into them.

Creating tables:

create table messages (msg_id int auto_increment primary key, msg_text text,sender_id int, sent_time datetime not null, sent_type enum('public','private') default 'private',delete_status boolean  default 0,foreign key(sender_id) references users(user_id)) type='innodb';

create table messages_users( msg_id int , rcvr_id int,read_status boolean default 0, delete_status boolean default 0,foreign key(msg_id) references messages(msg_id), foreign key(rcvr_id) references users(user_id)) type='innodb';

the above two statements create messages and message_users table with required properties.
It is to be noted that unless you use innoDB as storage engine you can’t use foreign key constraints !

Inserting values:

insert into messages values (default,'Hi Raj',1,current_timestamp,'private',default);

Here note the usage of the word default. It's usage allows the default value for that field to be substituted automatically.
And current_timestamp automatically gets the current date and time and inserts in the field having type datetime.

Here i give description about the two tables am querying,

messages table:
+---------------+--------------------------+------+-----+---------+----------------+
| Field         | Type                     | Null | Key | Default | Extra          |
+---------------+--------------------------+------+-----+---------+----------------+
| msg_id        | int(11)                  | NO   | PRI | NULL    | auto_increment |
| msg_text      | text                     | YES  |     | NULL    |                |
| sender_id     | int(11)                  | YES  | MUL | NULL    |                |
| sent_time     | datetime                 | NO   |     | NULL    |                |
| sent_type     | enum('public','private') | YES  |     | private |                |
| delete_status | tinyint(1)               | YES  |     | 0       |                |
+---------------+--------------------------+------+-----+---------+----------------+


messages_users table:

+---------------+------------+------+-----+---------+-------+
| Field         | Type       | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+-------+
| msg_id        | int(11)    | YES  | MUL | NULL    |       |
| rcvr_id       | int(11)    | YES  | MUL | NULL    |       |
| read_status   | tinyint(1) | YES  |     | 0       |       |
| delete_status | tinyint(1) | YES  |     | 0       |       |
+---------------+------------+------+-----+---------+-------+


RETRIEVE ALL PUBLIC MESSAGES:

select first_name,msg_text,sent_time from messages inner join users on messages.sender_id=users.user_id and sent_type='public';

The above query uses inner join. Usage of inner queries or nested queries is not preferred since mysql makes good optimisations when using joins. So usage of join (like the above query) is better.

RETRIEVE ALL ALIVE MESSAGES(messages that are not deleted):

select msg_text, sent_time from messages inner join messages_users on messages.msg_id=messages_users.msg_id and messages.delete_status=0 and messages_users.delete_status=0;

USING LEFT JOIN (selects public messages also) :

select msg_text from messages left join messages_users on messages.id=messages_users.msg_id where messages_users.msg_id is NULL or (messages.delete_status=0 and messages_users.delete_status=0);

RETRIEVE ALL USER MESSAGES:

(all messages except public messages)
select msg_text from messages inner join messages_users on messages.msg_id=messages_users.msg_id and (sender_id=2 or rcvr_id=2);

(including public messages)

select msg_text from messages inner join messages_users on messages.msg_id=messages_users.msg_id and (sender_id=4 or rcvr_id=4) or sent_type='public' group by messages.msg_id;

USING LEFT JOIN

select msg_text from messages left join messages_users on messages.id=messages_users.msg_id WHERE messages_users.msg_id is NULL or (messages.user_id=2 or messages_users.user_id=2);

(only received with public messages using left join)

select msg_text from messages LEFT join messages_users on (messages.id=messages_users.msg_id) where messages_users.msg_id is NULL  or messages_users.user_id=1;

Select all read messages and unread messages user_specific.

select msg_text from messages inner join messages_users on messages.msg_id=messages_users.msg_id and messages_users.read_status=0 and rcvr_id=1;

This query gets all unread/read msgs of an user.

Receiver has read ur messages or not?

select msg_text,read_status from messages inner join messages_users on messages.msg_id=messages_users.msg_id and sender_id=1;

Conversation:

select msg_text,sent_time as Time from messages inner join messages_users on messages.msg_id=messages_users.msg_id and (sender_id between 1 and 2) and (rcvr_id between 1 and 2) order by sent_time;

Select messages for today alone:

select count(*) from messages where sent_time rlike current_date;


The above queries can be taken as examples for learning joins, and clause , or clause , count , NULL , NOT NULL , like , order by and few other database concepts.


I will explain more concepts which are not dealt in examples above here,


LIMIT --> used to limit the number of records being selected.
For example, select * from table1 limit 10;
This will display only first 10 records of the table.


OFFSET --> used to set the starting point for limit.
For example, select * from table2 LIMIT 10 OFFSET 5
This will display 10 records after the 5th record.


It is to be noted that , select * from table1 LIMIT 5,10 does the same as above example. (notice the change in order of numbers!)


UNION --> Union combined result set of two sql queries.
For example, select * from t1 union select * from t2.
For this to happen t1 and t2 must have same number of columns and the data type of columns from t1 should match with t2.
It is to be noted that union automatically removes duplicate values. Only distinct values are shown.

SUB QUERIES --> They are very helpful in certain situations. It involves usage of select query inside some other query.
For example, You have a employee table and club member table. You need to find the employees who are member of the club.
select * from employee where employee_id in (select id from club);
This query does the job easily.
However it is not advised to use sub queries when other methods are possible since they are inefficient. The above query can be replaced by using a single join query.


That’s it for today see you in next blog !


Trained @ Sourcebits