Scribd
Upload
44 views

Mysql Commands

The document provides information about MySQL including what a database is, the need for databases, basic terminologies used in databases like entities and relations. It also describes features of SQL like portability and security. It lists various SQL commands like create, select, update and delete and operators used in SQL queries.

Uploaded by

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

Mysql Commands

The document provides information about MySQL including what a database is, the need for databases, basic terminologies used in databases like entities and relations. It also describes features of SQL like portability and security. It lists various SQL commands like create, select, update and delete and operators used in SQL queries.

Uploaded by

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

MYSQL

 A Database is an organized collection of structured


information, or data, typically stored electronically in a
computer system. A database is usually controlled by
a database management system (DBMS). ...
Most databases use structured query language (SQL) for
writing and querying data.

 A Database is a data structure that stores organized


information. ... For example, a company database may
include tables for products, employees, and financial
records. Each of these tables would have different fields that
are relevant to the information stored in the table.

 NEED OF DATABASE :
Database makes complex memory management easy to
handle .In filee systems ,files are indexed in place of objects.
It increase organizational accessibility of data which in trurn
helps the end user share data quickly and effectively.

 RDBMS – RELATIONAL DATABASE MANAGEMENT


SYSTEM it provide excellent feature for creating
,storing,maintain and accessing data stored in the form of
database.
MYSQL

 SQL IS A STRUCTURED QUERY LANGUAGE FOR ACCESSING AND


MANIPULATING RELATIONAL DATABASE.

 FEATURES OF SQL –
PORTABILITY,SECURITY,CONNECTIVITY,QUERY LANGUAGE

 ADVANTAGE OF SQL – HIGH SPEED,NO CODING,DEFINED


STANDARDS,PORTABILITY,INTERACTIVE LANGUAGE,MULTIPLE
DATA VIEW.
BASIC TERMINOLOGIES

Entity – It becomes the name of the table

Relation- A table is a collection of logically related entity.

Tuples , Cardinality – Row of the table(Alternate name)

Degree,Attributes –Column of the table(Alternate name)

Domain – It define the kind of data represented by the attributes.


CONSTRAINTS:

NOT NULL: Ensure column cannot contain null values.

UNIQUE : Ensure all values in column are different.

PRIMARY KEY :uniquely identify each row.

CANDIDATE KEY : It has capable to become primary key.

ALTERNATE KEY : Apart from primary key other column is know as alternate
key.

CHECK: All column satisfy the specific condition

DEFAULT : Set Default value for all column.

INDEX : Used to create and retrieve data from database.

EXAMPLE :

Create table students(Id int NOT NULL,Name PRIMARY KEY,city varchar


default(Delhi));
DATA TYPES
 INTEGER – only NUMBER…..Ex : INT(10) …. 12,56,78
 CHAR – only letters(a-z)…. Ex: CHAR(20) …“RAM”
 DECIMAL – DECIMALNUMBER…EX : DECIMAL(10)…
20.3,78.2
 VARCHAR – Number and letters… Ex : VARCHAR(25)
…”12A01”
 DATE AND TIME – DATE… Ex : DATE…“2021-11-19”

CLASSIFICATION OF SQL
SQL STATEMENT

DATA DEFINITION DATA MANIPULATION


DATA CONTROL
LANGUAGE (DDL) LANGUAGE (DML)
LANGUAGE(DCL)

 CREATE  INSERT
 ALTER  UPDATE
 DROP  DELETE
SQL COMMANDS

 DATABASE :Create database school;

 ACCESS :Use school;

 SHOW ENTIRE DATABASE : show databases;

 TABLE CREATING : Create table students( ID int(20),Rollno


varchar(20),Name char(25),Gender char(25),DOB DATE,marks
decimal(20));

 DESCRIBE THE DATATYPES :Desc students;

 INSERT NEW ROWS :Insert into students


values(1,”12A01”,”swathi”,”female”,”2000-10-02”,89.6);

 Insert into students values(2,”12B02”,”Reena”,”female”,”2000-09-


04”,78.9);

 Insert into students values(3,”12C03”,”Ram”,”male”,”2000-07-01”,82);

 Insert into students values(4,”12D04”,”Sam”,”male”,”2000-02-17”,67);

 Insert into students values(5,”12E05”,”mehra patel”,”male”,”2000-03-


18”,56);

 TO VIEW THE ENTIRE OUTPUT:Select * from students;


 TO VIEW SINGLE ROW :Select * from students where ID=1;

 UPDATE SINGLE DATE FROM TABLE :Update students set Name


=”veena” where ID=2;

 DELETE SINGLE DATE FROM TABLE :Delete from students where


Rollno=”12A01”;

 ADD NEW COLUMN IN THE TABLE :Alter table students add marks
decimal(25);

 RENAME THE COLUMN NAMEIN THE TABLE :Alter table students


change marks total decimal(25);

 DELETE THE COLUMN IN THE TABLE :Alter table students drop


marks decimal(25);

 TO DELTE REPEATING DATA FROM THE TABLE:Select Distinct


gender from students;

 AND OPERATOR :Select * from students where marks>80 and


gender=”m”;

 OR OPERATOR :Select Name,Gender from students where


Gender=”female” or Gender=”male”;

 NOT OPERATOR :Select * from students where not Gender=”male”;


 SORT THE COLUMN IN ASCENDING ORDER:Select * from students
order by name asc;

 SORT THE COLUMN IN DESCENDING ORDER Select * from


students order by name desc;

 LIKE OPEARTOR ( TO FETCH ENDING LETTER IN THE


COLUMN) :Select Name,Gender from student where name like “%m”;

 LIKE OPEARTOR ( TO FETCH STARTING LETTER IN THE


COLUMN)Select Name,Gender from student where name like “s%”;

 LIKE OPEARTOR ( TO FETCH SINGLE WORD IN THE


COLUMN)Select Name,Gender from student where name like “%patel%”;

 BETWEEN OPEARTOR :Select Name ,Gender from students where


marks between 56 and 88;

 IN OPEARTORSelect Name ,Gender from students where marks IN(56,


88);

 AGGREGATE FUNCTION
 SUM :
 Select sum (marks) from students;
 MAX :
 Select max(marks) from students;
 MIN:
 Select min(marks) from students;
 AVERAGE:
 Select avg (marks) from students;
 COUNT:
 Select count (marks) from students;
 COUNT(*):
 Select count (distinct (marks)) from students;
 GROUP BY HAVING CLAUSE:
 Select marks,ID from students group by marks having min(marks>67);

 JOINS

Example Table:

TABLE :STUDENT

ROLLNO NAME TABLE :FEES


1 Reena ROLLNO NAME
2 Jaya 4 4500
3 Teena 2 5500
3 5000
4 Diksha
1 8777

EQUIJOIN :Select A. ROLLNO,NAME,FEES from STUDENTS A,FEES B


where A. ROLLNO =B. ROLLNO;

OUTERJOIN : Select A. ROLLNO,NAME,FEES from STUDENTS A,FEES


B where A. ROLLNO =B.ROLLNO order by A.ROLLNO DESC;

INNERJOIN : Select A. ROLLNO,NAME,FEES from STUDENTS A,FEES B


where A. ROLLNO =B.ROLLNO order by A.ROLLNO;

CARTESIAN JOINS : Select Name,gname from students,games

You might also like