MySQL (Basics)

#MySQL (Basics)

MySQL is an open source RDBMS used for storing and querying data.

• create database db;
• show databases;
• drop database db;
• use db;
• show tables;
• create table table_name ( id int not null auto_increment, c1 float(10, 3), c2 date, c3 datetime, c4 timestamp, c5 varchar(20), c6 text, primary key (id) );
• describe table_name;
• drop table table_name;
• alter table table_name add c7 bigint;
• alter table table_name drop column c3;
• alter table table_name modify column c2 year;
• insert into table_name ( c1, c5 ) values ( 54.564456, "Gaurav" );
• select * from table_name;
• select c1, c2 from table_name where id = 1;
• update tmp set c6 = "Chandak" where id = 1;
• delete from table_name where id = 2;
• select * from table_name limit 5;
• select * from table_name where c1 <= 100 and c5 = 'Gaurav';
• select * from table_name order by c1 desc, c5 asc;
• select * from table_name where c5 in ('Gaurav', 'tmp1', 'tmp2');
• select * from table_name where c1 not between 10 and 100;
• select count(c1), max(c1), sum(c1), avg(c1) from table_name group by c7 having count(c1) > 5;
• select upper(c5) from table_name;
• select round(c1, 1) from table_name;
• update table_name set c3 = now();
• select length(c6) from table_name;
• select mid(c6, 1, 3) from table_name;
• select substring(c6, 1, 3) from table_name;
• select * from table_name where c5 is not null;
• select distinct c6 from table_name;
• select * from tmp where c5 like "%av";
• select * from tmp where c5 like "Gau_av";
• select * from tmp where c5 regexp "^[GHT].*[^asd]$|a{5}bcd+"; (Starting with either G/H/T followed by any characters and ending with anything apart from a/s/d) or (aaaaabc followed by 1 or more b)
• select a.tutorial_id, a.tutorial_author, b.tutorial_count from tutorials_tbl a, tcount_tbl b where a.tutorial_author = b.tutorial_author;
• select a.tutorial_id, a.tutorial_author, b.tutorial_count from tutorials_tbl a left join tcount_tbl b on a.tutorial_author = b.tutorial_author;
• create unique index author_index on tutorials_tbl (tutorial_author desc)