My SQL Soha Notes
Uploaded by
sohasaj idMy SQL Soha Notes
Uploaded by
sohasaj idMY SQL:
Commands:
DDL: data definition lang
DML: Data modification lang
DDL DML
Data Definition Language Data Manipulation Language
Deals with structure of the data Deals with actual data
Changes done with DDL cannot be Change done with DML can be rolle
rolled back back
Commands Commands
create insert
alter select
use delete
drop Update
Desc
● Null values: not arithmetic value and always returns null
when operated on, mans no value allotted
● Create
○ Create database databasename;
○ Create table tablename(coloumname datatype, …);
● Show
○ Show databases;
○ Show tables;
● Desc
○ Desc tablename;
● Drop
○ •Drop database databasename
● Use
○ •use databasename;
● Truncate:
○ TRUNCATE TABLE is a Data Definition Language (DDL)
command used to remove all rows from a table quickly
and efficiently, while keeping the table structure
(columns, indexes, etc.) intact.
○ TRUNCATE TABLE table_name;
● ALTER COMMAND:
○ Add, delete, modify columns (not rows)
○ Add coloumn:
■ ALTER TABLE table_name ADD column_name
datatype;
○ Modify column:
■ ALTER TABLE table_name MODIFY column_name
column_definition;
■ ALTER TABLE vehicles MODIFY note VARCHAR(100)
NOT NULL;
○ Rename table
■ Alter table tablename rename to newtablename;
○ Rename column:
■ Alter table tablename change column oldname
newname
■ REQUIRES US TO TYPE COLOUMN
■ ALTER TABLE users
■ CHANGE COLUMN username user_name
VARCHAR(50);
○ Delete column
■ Alter table tablename drop column coloumname:
■ REQUIRES US TO TYPE COLOUMN
○ Add/change constraints
■ Alter table add primary key(persons id);
■ ALTER TABLE table_name
■ ADD CONSTRAINT constraint_name PRIMARY
KEY (column_name);
■ ALTER TABLE table_name
■ MODIFY column_name data_type NOT NULL;
DATA MODIFICATION LANG DML:
● Insert:
○ •Only values
○ •(column order should match)
■ •INSERT INTO table_name VALUES (value1,
value2, value3,…);
○ •Column names and values both
○ •(column order need not match)
■ •INSERT INTO table_name (column1, column2,
column3,..) VALUES ( value1, value2, value3,..);
○ Columns not filled contain NULL
○ Implicit insertion of Null values:
■ Insert into student(Rno,Name) values
(1,”Ankit”);
○ Explicit insertion of Null values:
■ Insert into student
values(1,”Ankit”,NULL,NULL);
● SELECT:
○ Make queries
○ QUERY: commands in sql used to retrieve certain
specified info from the database/tables
○ Eg:
■ SELECT <Column-list>
■ FROM <table_name>
■ [Where <condition>]
■ [GROUP BY <column_list>]
■ [Having <condition>]
■ [ORDER BY <column_list [ASC|DESC ]>]
○ All:
■ Select * from tablename;
○ Where:
■ Select coloumn1name, coloumn2name,
coloumn3name from tablename where city =
“delhi”;
■ Where:
● > < = != >= <=
● And Or Not
● Between In Like
● Is Null Exists
● •SELECT * FROM EMPLOYEE WHERE
CITY=‘GURGAON’ OR CITY=‘DELHI’;
● •SELECT * FROM EMPLOYEE WHERE PHONE IS
NULL;
● •SELECT * FROM EMPLOYEE WHERE NOT CITY
= ‘DELHI’;
● •Select * from employee where salary between
2000 and 3000;
○ Use column alias:
■ •Select salary, 100*salary AS ‘hike’ from
employee;
■ •Select * from emp where city IN
(‘Delhi’,’Gurgaon’,’Noida’);
■ •Select distinct(age) from employee
○ Like:
■ % matches 0 or more characters
■ _ matches exactly 1 character
■ •Select * from employee where name like
‘_a%’
○ Order by:
■ •Select * from employee order by age asc;
■ selec t * from employee order by age asc, name
desc;
○ Aggregate FUNCTIONS:
■ Only come with select statements
● .
■ SELECT name
■ FROM employee
■ WHERE salary = (SELECT MAX(salary) FROM
employee);
● DELETE:
○ •DELETE FROM tablename WHERE
search_conditions;
● UPDATE:
○ •UPDATE loan SET rate=rate+1.5;
○ •UPDATE customer SET f_name=‘Thomas’
WHERE Code=‘E123’;
FUNCTIONS:
● date/day/time:
●
● Character functions:
●
