MYSQL NOTES

To download MYSQL V5.7 CLICK HERE

Dbms-database management system
r-relational by e.f.codd
data is different from information
mysql is not case sensitive for keywords

Commands:
show databases;
show tables;
use <database name>;
create database <database name>;

drop table <table name>;

drop database <database name>;

create database if not exists database_name

create table <table name> no semicolon press enter

(<column name> <data type> default "default value <quotes as per data type>",

<column name> <data type> not null primary key,
<column name> <data type> check(col_name >100) );
# column name no spaces

#create from existing table:

Create table table2_name

// a select query in table 1.eg

Select * from table 1 where marks>90;

describe <table name> ;describe or desc
# desc-show the structure of database

insert into <table name>  #no semicolon press enter
values(data1,data2,data3...);
#custom insertion
insert into <table name>
(col1,col3,col5)
values(data1,data3,data5)

#multiple insertion 
Insert into table_name
Values(set1), (set2) ;

select * from <table name>;

select <column_1>*12 as <new name>,<column_2>,... from <table name>;
select * from <table name> where <column name> =/>/</<=/>=/<> condition ;or and not and<> not equal to ... between

select * from <table name> where <column name> in/not in (argument1,argument2) ;

# arguments with inverted commas as per data type

(arguments) are called domain == valid set of values

select * from <table name> where <column name> like wildcard ;

select * from <table name> where <column name> is/is not NULL

select * from <table name> order by <column_1> desc,<column_2> desc, column_3 asc ... ;

#can put multiple condition in order of execution by the order in which we enter the col. names

# by default it is ascending and we need not write asc

select distinct <column_1> from <table name>; 

select * from <table name> where <column name> between data1 and data2;

# data1 and data2 are both inclusive

Selection - choosing records to display with help of where clause

Projection--choosing which attributes /fields to display from a relation /table

#select "text"... Will display the text as header and value of a new field

Aggregate function- apply on whole table and return a single output or one result per group when used with group by

sum,avg,max,min,count

Min, max, avg, sum, count (column name) ignore null values

Count(*) include null values 
select count(*/col_name/distinct col_name...) from <table name>;
select * from table_name group by col_name;
// group by : divides a table in multiple group and perform aggreagate function as per condition

# syntax of using order by:

Select, where, group by, having, order by 

# where applies on whole table before grouping while having is applied to filter in each individual group

# if a column is select on which on aggregate function is applied with group by then it display the corresponding value from the record which comes first in that group  or table

**NOTE: aggregate functions don't work with where clause if a field without the aggregate function is called except also with group by, in which it applies to each group formed

Eg select sum(price), name from product where qty>100; is invalid but

select sum(price), max(qty) from product where qty>100; is valid

Operators:

Arithmetic : +, -, /, *, %

Relational : =, <, >, <=, >=, <>

Logical- and, or, not

Special: between.. And.. , is null, in, like

Other functions(single row functions) :

They return 1 output per record

String - upper(col_name), lower() 

Mathematical

Date-time

update <table name>
set <column_1>=<new data>
where <column_2>=<existing data>;
update <table name>
set <column_1>=<new data>; all records

Update table-name

Set col1=new-data col2=new-data, 

Where col3 in (1,2,3,4);

Note in where condition : or, in, and can be used over 1 or many columns 

delete from table_name; all records
delete from student
where col_name = value;

delete:-to delete records but the structure of table remain
not free the memory

alter table <table name>
add <column name> data_type constraints;

alter table table_name
add primary key(col_name);

<optional> after previous col_name;

Also the keyword 'constraint' can be used after the word add in above case

For other constraints replace 'primary key' with the constraint name

Adding foreign key:

Alter table table_name
Add foreign key(col_name) references table2_name(col_name of table2) 

alter table table_name
drop col_name;

alter table table_name
drop primary key;

alter table table_name 
change old_col new_col type constarint;  or 

alter table table_name 
change COLUMN old_col new_col type constarint;

alter table table_name
modify col_name new_type constraint; 

alter table table_name
rename to new_table_name; not checked
alter table table_name
rename old_co to new_col; not working

truncate table <table name>;

truncate-delete all the records but the tables strucure will remain same
can't appy where condition with truncate
free the memory

joining tables:
create table2:
(....,
..,
col_name data_type references table1_name(col_name));

natural join:common column don't repeats
**we make filter on the columns**
select table1.col_name , ..  from table1,table2
where table1.col1=table2.col1 ;

equi join: common column repeats-inner ,outer (left right ) 

Inner:
select * from table1,table2
where table1.col1=table2.col1 ;

Right outer:

select * from table1 right join table On

where table1.col1=table2.col1 ;

cross join / cartesian product- create
joining 2 table without condition
columns add, records multiply
select * from table1,table2 ; 

Comments:

1.Single line:

#text

--[single space]text

2.Multi line:

/*.......... 

........... */

aliasing
select A.empif as "hello" from table1 A ;

Column alias, table alias

#alias can also be done on expressions:

Select 22/7 as pi;

expression:
select 5+7;
output == 12 (dual table)
dual  = defaut table of mysql. It has one row with value X and one column 'dummy' defined as varchar(2)

CONSTRAINTs:
primary key
default
not null
unique
check
foreign key (references)# work only in server version
#primary key = unique+not null

(space is used for assigning values and not equal to symbol)

#using default - name char(5) default 'Hello' ;

#2 ways /methods of adding constraints in create table command :

1.) Column method 

col_name type constraint(s) 

In the above constraints are added at the time of defining a column

2.) table method 

After all the columns have field defined with data types, at the end constraints are added

Col1..., 

Col2...., 

Col3....., 

...... 

.... 

Col1 not null default "Hello", 

primary key(Col2, Col3)) ;

#Using table method we can assign a constraint to multiple fields in a single statement and also create composite primary key like in the above example 

Data types:
int / integer 
char(size) fixed length string
varchar(size) variable length string
float 
date
# date--"yyyy-mm-dd"
NULL in capital without quotes
# <database name>.<table name> is location path

Char -1 byte per character

Vardhan-1 byte per character +2 to store length

Wildcards / pattern matching :
ab% starting with ab variable length
ab__ starting with ab fixed length

scalar expression-simple math expression in mysql
eg col1*col2
dual table= default table
degree of table ==no. of fields/attributes
cardinality==no. of tuples

Keys:
primary
foreign
candidate
alternative(candidate - primary key)
compostive

Sql command categories:
1.)data defination commands
->structure of table
eg.)create,alter,drop,truncate
2.)data manipulation command
command related to records of table
eg.) select , insert , delete , update

3.) Dcl - grant , revoke

4.) tcl - savepoint,rollback,commit