Scribd
Upload
37 views

Mysql Commands

Uploaded by

shelo berman
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
37 views

Mysql Commands

Uploaded by

shelo berman
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 3

mysql> select * from personal p join city c

-> on p.city=c.c_id order by p.name;


+----+--------+-----+--------+------+------+-----------+
| id | name | age | gender | city | C_ID | City_Name |
+----+--------+-----+--------+------+------+-----------+
| 1 | honey | 19 | M | 5 | 5 | Nagda |
| 2 | Satish | 22 | m | 4 | 4 | jaipur |
| 3 | yash | 20 | m | 1 | 1 | Agra |
+----+--------+-----+--------+------+------+-----------+

create table student( student_id int, name varchar (20), major varchar(20),primary
key (student_id);

describe student;

drop table_name;

add another column: alter table student add/drop gpa decimal(3,2);

insert into student values(1,"jack","Biology"),(2,"kate","sociology");

select * from student ;

insert into student(student_id,name ) values(1,"jack");

create table student( student_id int,


name varchar (20) not null,
major varchar(20) default 'undecided',
primary key (student_id);

update (tablename) set major = 'bio' where major= 'biology';

delete from student where student_id=5;

select * from student order by name asc/desc


limit 2;

start------------------------->>>>>>>>>>>>>>>>

mysql> alter table employee


-> add foreign key(branch_id)
-> references branch(branch_id)
-> on delete set null;

mysql> alter table employee


-> add foreign key (super_id)
-> references employee(emp_id)
-> on delete set null;

mysql> create table client (


-> client_id int primary key,
-> client_name varchar(40),
-> branch_id int,
-> foreign key(branch_id) references branch(branch_id) on delete set null);

mysql> create table branch_supplier(


-> branch_id int ,
-> supplier_name varchar(40),
-> supplier_type varchar(40),
-> primary key (branch_id,supplier_name),
-> foreign key(branch_id) references branch(branch_id) on delete cascade
-> );

mysql> insert into employee value(100,'david','wallace','1947-11-


20','M',25000,null,null);

insert into employee values(102,'tokyo','vanis','1998-12-14','M',45000,100,1)


,(103,'sannu','shami','1948-12-14','M',40000,102,2)
,(104,'john','abrahim','1988-12-14','F',25000,102,2)
,(105,'amigo','falsos','1968-12-14','M',65000,102,2)
,(106,'Satish','lakhara','1798-12-14','F',41000,102,2)
,(107,'Daniel','zorrik','1988-12-14','M',40060,100,3)
,(108,'Vemosh','markin','1798-12-14','F',75000,106,3)
,(109,'sanket','zore','1990-12-14','M',13000,106,3)

insert into client values(400, 'dunmore highschool', 2),


(401, 'lackwana country', 2),
(402, 'Fedex', 2),
(403, 'John daily law,llc', 2),
(404, 'scranton whitepages', 2),
(405, 'times newspaper', 2),
(406, 'Fedex', 2);

mysql> select count(emp_id) from employee;

mysql> select count(emp_id) from employee where sex='F' and birthday > '1973-04-
22';

mysql> select first_name ,last_name from employee where emp_id in(


-> select emp_id from works_with where total_sales >90000);
mysql> select * from employee join branch on employee.emp_id= branch.mgr_id;

* if and case statement*


1. select id, name, percentage, if(percentage>=33, "pass","fail") as result from
student;

mysql> select id, name, percentage,


case
when percentage>=80 and percentage<=100 then "Merit"
when percentage>=60 and percentage < 80 then "1st division"
when percentage>=45 and percentage<60 then "2nd division"
when percentage>=35 and percentage < 45 then "3rd division"
when percentage < 35 then 'fail'
else "not correct %"
end as grade
from student;

mysql> update student set percentage = (case id when 3 then 456 when 4 then 78 end)
where id in(3,4);

* arithmatic function *
mysql> select id,name,(percentage +5) as PERCENTAGE from student;
mysql> select id,name,(percentage * 5) as PERCENTAGE from student;
mysql> select pi(),floor(4.5),round(4.6),ceil(6.7),abs(78.34)gives absolute value;
mysql> select pi(),sqrt(16);
mysql> select round(rand() *10);
mysql> select floor(7 + rand()*100);

* string function *
mysql> select name,id,character_length(name) as characters from student;
mysql> select id ,concat(name,percentage) as name from student;
mysql> select concat_ws(' ','hem','raj','lak','hara') as name ;
mysql> select ltrim(' hemraj ') as name ;
+-------------------------------+
| name |
+-------------------------------+
| hemraj |
+-------------------------------+
mysql> select instr('yahoo jaba java','jav') as name;
+------+
| name |
+------+
| 12 |
+------+
mysql> select left ("yahoo baba",3) as name;
+------+
| name |
+------+
| yah |
+------+
mysql> select format(3.454332,4) as digit;
+--------+
| digit |
+--------+
| 3.4543 |
+--------+
----------------------Alter commands-----------------------
mysql> alter table student add grade varchar(10);{ to add new column}
mysql> alter table student_data add grade varchar(10) AFTER STUDENT_NAME;
mysql> alter table student drop column grade ;{to drop the existed column}
mysql> alter table student modify name char(20);{ modify column datatype}
mysql> alter table student_data add unique(id);
mysql> alter table student change name STUDENT_NAME VARCHAR(20);{RANAME COLUMN
NAME}
mysql> ALTER TABLE STUDENT RENAME STUDENT_DATA;{TO CHANGE THE TABLE NAME}

You might also like