1. To login into mysql server.
mysql –u ____ -p _____
--> -u username, -p password
e.x. mysql –u root –p sarita
2. To create database
create database <database_name>
e.x. create database company
3. to use database
use <database_name>
e.x. use company
4. to create table in database to store data
create table emp_data (name varchar (30), city varchar (30), salary int (50)
);
5.to describe table
---it will only show columns and it’s data type, size, key etc and not record/row of table.
desc <table_name>
e.x. desc emp_data
6.to view all database name and tables name
--> to show all databases
show databases;
--> to show all tables
show tables;
7.to use integer and decimal data type
Column_name int (size);
e.x. roll int (50);
Column_name decimal (size,p);
e.x. price decimal (4,2);
p is number of digit after decimal point.
8. To use char, varchar and text data type
Column_name char (size);
-->255 (fix size mention in parenthesis)
e.x. name char (50);
Column_name varchar (size);
-->255 (variable size)
e.x. name varchar (50);
-->65,535
Column_name text;
e.x. address text;
9. to use date, datetime and timestamp data type
Column_name date;
--> yy-mm-dd format
e.x. age date;
Column_name datetime;
--> yy-mm-dd hh : mm :ss format
e.x. join_date datetime;
Column_name timestamp;
--> displays date and time
e.x. login_dt timestamp;
10. to insert new record/row/tuple in table.
Insert into table_name(col1,col2…)
Values(val1,val2…);
e.x.
Insert into emp_data(name,city,salary)
Values(‘sarita’,’surat’,15000),
(‘smruti’,’ranchi’,25000);
11. to insert record without column name
Insert into table_name Values (val1, val2…);
e.x. Insert into emp_data
Values(‘sarita’,’surat’,15000),(‘smruti’, ’ranchi’,25000);
12. to select data from database and retrieve information.
select * from table_name;
--> * means all column
e.x:- select * from emp_data;
select column_name from table_name;
--> to select name column only
e.x. select name from emp_data;
-->to select name and city column
e.x. select name,city from emp_data; ---
13. to select data with some limit.
select column_name from table_name
limit record_number;
e.x. select id from emp_data limit 3;
e.x. – 2
select * from emp_data limit 1, 5;
--> it will show record from 2 and total 5
14. to search for specific data
--> where clause
select * from table_name where column_name operator value;
e.x.
select * from emp_data where name = ‘sarita’;
select column_name from table_name
where column_name operator value;
e.x.
select name,salary from emp_data
where salary > 50000;
e.x.
select * from emp_data where salary is not null;
15. And and Or operator
select * from table_name where column_name = value and column_name = value;
e.x.
select * from emp_data where name = ‘sarita’ and salary = 50000;
select * from table_name where column_name = value or column_name = value;
e.x.
select * from emp_data where name = ‘sarita’ or salary = 50000;
e.x. :- 2
select * from emp_data where name = ‘sarita’ and (city = ‘ranchi’ or salary = 50000);
16. In and Not In operator -- to choose from list
select * from table_name where column_name in (value, value….);
e.x.
select * from emp_data where name in (‘sarita’,’smruti’);
--> Not In
e.x.
select * from emp_data where name not in (‘sarita’,’smruti’);
17. To select value within range
--> between
select * from table_name where column_name between value1 and value2;
e.x.
select * from emp_data where salary between 50000 and 200000;
e.x. - 2
select * from emp_data where name between ‘sarita’ and divya’;
–-> NOT BETWEEN
e.x.
select * from emp_data where salary not between 50000 and 200000;
18. between with IN
select * from table_name where (column_name between value1 and value2) and column_name in (value1, value2);
e.x.
select * from emp_data where (salary between 5000 and 20000) and city in (‘surat’,’ranchi’);
--> NOT IN
e.x.
select * from emp_data where (salary between 50000 and 200000) and city not in (‘surat’,’ranchi’);
19. to search for specified pattern
-> LIKE operator
select * from table_name where column_name like ‘pattern’ ;
e.x.
select * from emp_data where name like ‘%a’;
e.x. - 2
select * from emp_data where name like ‘_%r%_’;
select * from table_name where column_name not like ‘pattern’ ;
e.x.
–> NOT LIKE
select * from emp_data where name not like ‘s%_’;
wild cards for LIKE operator
1. % - zero or more character
‘a%’ – all starting with a
‘%a’ – all ending with a
‘%a%’ – all containing with a
2. _ - one single character
‘sarit_’ – starting with sarit then any one char
‘_arita’ – any one char then arita
‘_a_’ – any chr then a then any char
21. this is use to short the records.
-> asc – ascending (by default )
-> desc – descending
select * from table_name order by column_name desc;
e.x.
select * from emp_data order by name desc;
22. NOT NULL – compulsory to add value in such field
create table emp_data (name varchar (30), city varchar (30), salary int (50) not null );
23. UNIQUE KEY – uniquely identifies each records
create table emp_data (name varchar (30) unique key city varchar (30),salary int (50) );
24. PRIMARY KEY
–uniquely identifies each records, unique values, can not contain null value, only one per table
create table emp_data (name varchar (30) not null primary key city varchar (30), salary int (50) unique key);
25. AUTO INCREMENT
– generate unique num when new record inserted
create table emp_data (id int not null auto_increment primary key, city varchar (30), salary int (50) );
To change auto increment to new value
alter table emp_data auto_increment = 10
26. aliases are used to temporarily rename table name
Select column_name from table_name as alias_name;
e.x.
--> for table
select name from emp_data as new_data;
--> for column
select name as new_name from emp_data
27. to display distinct
(different ---only one from many same) value
select distinct column_name from table_name;
e.x.
select distinct name from emp_data;
28. to add/change/modify/drop existing structure of table.
Add column
Enable/disable constraints
Change column
Modify column
Drop column
Add column :- to add new column to table without constraints
alter table table_name add column column_name datatype (size);
Ex:-
alter table emp_data add column pin int (10);
Ex:- 2 -- multiple column adding
alter table emp_data add column pin int (10), add column m_num int (11);
Add column by position
Last(by default)
First
after
Ex:- 1 --- column adding at first
alter table emp_data
add column pin int (10) first;
Ex:- 1 --- column adding after ___
required column
alter table emp_data
add column pin int (10) after city;
Ex:- 2 --- column adding with constraint
alter table emp_data add column pin int (10) not null primary key;
Ex:- 3 --- column with constraint and position
alter table emp_data add column pin int (10) not null auto_increment primary key first;
Ex:- 4 --- adding constraint
alter table emp_data add constraint unique key (name);
change column :- to change name and data type of existing column.
alter table table_name change column old_col_name new_col_name new_data_type(size);
Ex:-
alter table emp_data change column pin pin_code int (8);
Ex:-change multiple column
alter table emp_data change column pin pin_code int (8), change column name emp_name varchar (50);
Ex:-change with constraint
alter table emp_data change column pin_code int (8) not null, add primary key(pin_code);
modify column :-
to modify size of data type or data type without changing column name.
alter table emp_data modify column name char (40) not null;
Drop column :-
to delete column from table.
alter table emp_data drop column name
--> to delete constraint from column.
alter table emp_data
drop primary key(id);
--> to delete table from database.
drop table emp_data;
29. Truncate table
--> to delete data of table and not table.
truncate table table_name;
Ex :-
truncate table emp_data;
30. Rename table
--> to rename one or more table.
rename table old_table_name to new_table_name;
Ex :-
rename table emp_data to data;
31. alter database
--> to change overall characteristics of database.
alter database database_name;
Ex :-
alter database science;
32. delete database
-->to delete database.
drop database database_name;
Ex :-
drop database science;
33. show columns
-->to show all column of table and their data types.
show columns from table_name;
Ex :-
show columns from emp_data;
34. show create database
--> it shows commands, you have written while creating database.
show create database database_name;
Ex :-
show create database science;
35. update
--> to update existing records in table.
update table_name set column1 = value1 where some_column = some_value;
Ex
update emp_data set name = ‘siya’, city = ‘ranchi’, salary = 50000 where id = 1;
36. delete
--> to delete records in table.
delete from table_name where some_column = some_value;
Ex :-
delete from emp_data where name = ‘sarita’ and salary = ‘50000’;
37. delete
--> to delete all records of table.
delete from table_name; or delete * from table_name;
Ex :-
delete from emp_data; or delete * from emp_data;
38. copy old_table to new_table.
Within same database
create table new_table like old_table;
insert new_table select * from old_table;
Ex :-
create table frnd like friends;
insert frnd select * from friends;
39. copy old_table to new_table. In different database
create table new_table like old_db.old_table;
insert new_table select * from old_db.old_table;
Ex :-
create table frnd like science.friends;
insert frnd select * from science.friends;
40. Min, Max, Sum and Avg and sqrt function in database.
select min(column_name) from table_name;
Ex :-
select min(salary) from emp_data;
select max(salary) from emp_data;
select sum(salary) from emp_data;
select avg(salary) from emp_data;
select sqrt(salary) from emp_data;
with alias name -
select min(salary) as lowest_sal from emp_data;
41. decimal data type column_name decimal(t,d);
t = total digit 1 – 65
d = digit after decimal 0-30 and must not more than t
Ex :-
price decimal(7,2); or
price decimal(t)
create table product(id int not null primary key auto_increment, price decimal(7,2) not null;
zerofill – it fills zero at remaining digit to make total
Ex :-
create table product(id int not null primary key auto_increment, price decimal(8,4) zerofill;
if price is 250.65 --- it will add 2 digit(0) at last ---so total is 7 ---but we
mentioned 8 as total so it will add 1 digit(0) at first.
---- 0250.6500 -- red are zerofill
41. Round function in database.
select * , round(price,1) from table_name;
42. count function :- returns the number of values of specified column.
select count(column_name) from table_name;
select count(salary) from emp_data;
--> returns number of record in table.
select count(*) from table_name;
select count(*) from emp_data;
--> returns number of distinct value in specified column.
select count(distinct column_name) from table_name;
select count(distinct salary) from emp_data;
43. converts the value of field to uppercase and lower case.
select upper(column_name) or ucase (column_name);
Ex:- select upper(name) from emp_data;
select lower(column_name) or lcase (column_name);
Ex:- select lower(name) from emp_data;
44. to extract specified character from text field.
mid(column_name,start,length) or substring(column_name,start,length)
Ex:- select mid(name,1,3) from emp_data;
45. it return length of the value in text field.
length(column_name);
Ex:- select length(name) from emp_data;
46. it joins two column.
concat(column_name1, column_name2);
Ex:- select name, concat(name,city) from emp_data;
Select name, concat(name, (select mid(city, 1, 4))) from emp_data;
47. it reverse the order of letter in string.
reverse(column_name1);
Ex:- select name, reverse(name) from emp_data;
48. it returns the current system date and time.
now();
Ex:- select name, now() from emp_data;
49. it returns the group according to given function and given column.
Group by();
Ex:- select name, min(salary) from emp_data group by name;
Having();
Ex:- select name, min(salary) from emp_data group by name having min(salary) > 2500;
Ex2:- select name, count(*) from emp_data group by name having count(*) > 2;
50. Foreign key relation.
Create table department( Id int not null primary key auto_increment, E_name varchar (50), E_id int, Constraint employe_eid_fk foreign key(e_id) references employ(emp_id) );
Ex :-
create table tweet(Id int not null primary key auto_increment, tweet varchar (250), );
create table reply (r_id int not null primary key auto_increment, reply varchar(250), parent int, constraint tweet_r_id foreign key(parent) references tweet(id) );
51. to find constraint name
select * from info_schema.table_constraints
where table_name = ‘tweet’;
52. to drop foreign key from table.
Alter table table_name drop foreign key key_name;
Ex :-
alter table reply drop foreign key reply_parent;
53. to add foreign key constraint in table.
Alter table reply Add column r_id int ,
add constraint reply_parent
foreign key(r_id) references tweet(id);
54. to add on delete cascade clause in foreign key.
create table reply (r_id int not null primary key auto_increment, reply varchar(250), parent int, constraint tweet_r_id foreign key(parent) references tweet(id) on delete cascade;
);
55. to add on delete set null clause in foreign key.
create table reply (r_id int not null primary key auto_increment, reply varchar(250), parent int, constraint tweet_r_id foreign key(parent) references tweet(id) on delete set null
);
56. to add on delete restrict clause in foreign key.
create table reply (r_id int not null primary key auto_increment, reply varchar(250), parent int, constraint tweet_r_id foreign key(parent) references tweet(id) on delete restrict);
57. to add on update cascade clause in foreign key.
create table reply (r_id int not null primary key auto_increment, reply varchar(250), parent int, constraint tweet_r_id foreign key(parent) references tweet(id) on update cascade);
58. composite key.
create table reply (r_id int, reply varchar(250), parent int, primary key(r_id,reply) );
59. cross join/Cartesian join/ Cartesian product/cross product
the cross join returns every row from one table crossed with every row from second.
select * from table1 cross join table2;
select * from emp_data cross join frnd;
or
select id,name from emp_data cross join frnd;
or
select * from emp_data,frnd
60. Inner join
Equijoin :-
Select column from table1 Inner join table2 On Column_name = column_name;
61. Inner join
Non-Equijoin :-
Select column from table1 Inner join table2 On Column_name <> column_name;
62. Natural join
Select column_name from table1
Natural join table2;
63. left outer join
Select column_name from table1
left join table2 on column_name = column_name;
