Learning Tables | Queries | MYSQL
1 Like
Write down all the commands we discussed today.
**Show **: it will show all database and also table too.
Create: it will create database and also same command for table too.
Syntax: Create database {Database_name}
example: Create database employee;
Same as for table:
Syntax: Create database {Table_name}
example: Create database employee ( id int, name varchar(20),salary int);
Use: this comand for using database.
Syntax : use employee;
Describe: it’s showing the structure of table means complete details of the table.
Syntax: describe employee;
Insert: insert command is used for inserting the data inside the table.
Syntax: insert into employee(id , name , salary) values( 101 , "Suraj" , 1000) (102 , "Krishna", 5000) (104 , "Shantanu", 4000);
Where: this is one of the important command that is used for specify the places where do you need to operation in the table.
Select: it is used for showing the all record of the data. it is simply used for reteriving data.
Syntax : select * from employee;
*: it is used for showing all records
Attribute : we can use attribute name in place of * .
operator : we can use operator also like(<,>,<=,>=,not, and, or coloumn name(id,salary ,name),between,in(2000,300,4000 ) , like (it is used as suggesting name).
Example: select name, salary from employee;(it's for coloumn)
for operator: select * from employee where salary{= ,>,<}9000;
for and: select * from employee where id = 106 {and(both value should be true) , or(any one
value should be true)} salary=6000;
for between: select * from employee where salary between 1000 and 6000
* Intresting thing in between it will give you range value and 1000 , 6000 also
for in(): select * from employee where salary in(1000,2000,3000);
* it will print only these provided value.
for like: it is suggesting or pattern matching thing like google when we write in then it is suggested as india ,indonesia,indian like this
select * from employee where name like 'ha_ s%' ;
here we use two operator one is modulo(%) that says after this anything and second is blank space (_) it says blank thing here can be anything.
Update: it is used for updating our table.
Syntax : update employee set salary = 60 where id =112
updation is done after set here we can do any operation just like adding ,subtracting,or percenting
we can update whole coloumn by using :
update employee set salary = salary + 1 ;
or
update employee set salary = salary+2 where {true value (1>0(this is always true))}
Alter: it is used for adding extra coloumn in the table
Syntax: alter table employee add coloumn isActive boolean;
this will add one extra coloumn if you want to add value then you have to update;
update employee set isActive= true;
it will print 1 in every row in the isActive coloumn
we can use where also ,
update employee set isActive= true where id = 112;
if we add coloumn then we should delete also this coloumns,
alter table employee drop coloumn isActive;
Limit : it print only limited value that we set.it’s also use in selecting command.
Syntax: select * from employee limit 5;
Offset: it’s leave those value(means row) whoever you provided.
Syntax : select * from employee limit 2 offset 2;
Count: it’s count all the rows.
Syntax : select count (*) from employee;
Deleting: it’s delete table content and also use drop for one by one deleting.
Syntax: delete from employee where id=120;
for deleting whole content from table we use
delete from employee;
or
delete from employee where 1>0;
Drop: it's also use for deleting:
drop table employee;
drop database employee;
4 Likes
- Command for Create Database:-
create database student; (here student is database name)
========================> - Command for Create Table:-
create table student(id int, name varchar(20), rollno int);
=======================>
Command for inserting values in the Table:-
insert into student(id int, name varchar(20), rollno int)values(101,“abhishek”,24);
========================> - command to select working database:-
use database;
========================>
*command to show create table:-
show tables;
========================>
*command to show structure of your table:-
describe student;(here student is your database name)
========================>
*command to get all data from table:-
select * from student;
==========================>
*command to get selected data from table like (name):
select name from student;
=========================>
use of constraints commands:-
select * from student where id=101;
=====>
use of operator commands:-
select * from student where id=101 and rollno=112;
In and operator both condition should be true;
=======>
select * from student where id=101 or rollno=112;
In or operator one condition must be true;
========>
select * from student limit 2;
In limit operator according to your requirement you can use limit , how many records you want
here i will take 2 records
=============>
select * from student limit 2 offset 1;
Offset operator define after which record you want
here offset 1 means after one record and limit will be 2
============>
update student set id=105 where name=“abhishek”;
here set operator is use to update the record
===========>
- How to add column
alter table student add column age int;
============> - How to delete column
alter table student drop column age ;
===========>
how to delete your database
delete from student(database name)
1 Like
here are some of commands which we discuss yesterday class ===>
create = by using this command we create new table , new database store.
describe = by using this command we can see all details of our database.
insert = this command for inserting something.
select = select for retrieving data.
where = this command for specify place
update= its for updating information in database or table.
alter= its for add more column.
limit = its shows those value we select.
use database= select working database.
count = its count all rows.
deleting = its for deleting content.
show table= for show our created table.
1 Like
(1) Show:- To show list of databases
show databases;
(2) Create:- To create one or multiple new databases.
SYNTAX: create database DataBaseName;
Example: create database employee;
(3) Create:- To create one or more table
SYNTAX: create database TableName(properties);
Example: create database employee(id int, name varchar(20), salary int);
(4) Use:- This command is to use our created database.
SYNTAX: use DataBaseName;
Example: use employee;
(5) Insert:- This command is used for inserting a data into the table.
SYNTAX: insert into TableName(properties);
Example: insert into employee(id, name, salary)values(101,'Monisha',1000);
(6) Insert multiple elements:-
SYNTAX: insert into TableName(properties)values(),(),();
Example: insert into employee(id, name, salary)values(101,'Monisha',1000),(102,'Mythili',2000);
(7) Select:- It is used to show all the records from table by retrieve/fetch it.
attribute is used to retrieve only particular attribute name in place of *
* is used to view all inserted rows
SYNTAX: select * from TableName;
EXAMPLE: select * from employee; // Retrieve entire records of table
select name, salary from employee; // Atrribute name (column)
(8) Where:- This command is used to take decision based on its condition.
→ Logical operators (=, >, <, <=, >=) can be used in ‘Where’ command’s condition.
→ Commands: not, or, and, between, in, like etc.
- between command is used to gives ranges in condition
- in command is used to retrieve only matched records.
- and command is used when we need both conditions to be true.
- or command is used when we need anyone condition satisfies retrieve data.
- like command is used to match records as per the given pattern. (PATTERN MATCH)
→ Sometimes when condition not satisfies it returns as ‘Empty set’.
SYNTAX: select * from TableName where condition;
EXAMPLE: select * from employee where id=105;
select * from employee where id=106 and salary=6000;
select * from employee where salary between 4000 and 9000;
select * from employee where id=106 and salary=9000; // Empty set
select * from employee where salary in(2000,3000,4000);
select * from employee where name like 'har_hi%';
- If there is missing keyword, then we can use underscore and % for after hi can be anything.
(9) Describe:- To show current table’s structure gives complete details inside a table.
We get Field, Type, Null, Key, Default & Extra as attributes/Column Names.
SYNTAX: describe TableName;
EXAMPLE: describe employee;
(10) Update:- It is used to update the created table.
Here where condition is not mandatory to provide all times use it whenever neccessary.
SYNTAX: update TableName set condition1 where condition2;
EXAMPLE: update employee set salary=5000 where id=112;
(11) Limit :- To get first few (5) records.
SYNTAX: select * from TableName limit numberOfRecords;
EXAMPLE: select * from employee limit 5;
(12) Offset:- This command leaves (how many records we need to not taken into account) & provides records after that according to limit of records.
SYNTAX: select * from TableName limit noOfRecords offset collectRecordsAfter;
EXAMPLE: select * from employee limit 2 offset 2;
(13) Count:- To know the count of records inside a table. Here * means how many rows we have currently in this table.
SYNTAX: select count (*) from TableName;
EXAMPLE: select count (*) from employee;
(14) Alter:- To change (add/delete) the records from table.
SYNTAX: alter table TableName add column ColumnName NewColumnType; => To add a column
alter table TableName drop column ColumnName; => To delete a column
EXAMPLE: alter table employee add column isActive boolean; // To add a new column in table
update employee set isActive= true; [OR] update employee set isActive= true where id = 112;
alter table employee drop column isActive; // To delete a new column from table.
(15) Delete: To delete table’s content / entire content of a table.
SYNTAX: delete from TableName where (particular record's property with its value);
EXAMPLE: delete from employee where id=120;
delete from employee; // To delete entire table
(16) Drop: To delete entire table and database from list of databases.
SYNTAX: drop table TableName;
drop database DatabaseName;
EXAMPLE: drop table employee;
drop database employee;
1 Like
Nice Notes Everyone 