MYSQL-IT is RDBMS (relational data base management system).
IT is most popular
it is free
It is open source.
both small as well as large app.
it is ver fast ,reliable and easy to use.
it is cross-platform.
it was first released in 1995.
it is compliant with ANSI (American National Std Institute) which is used for
accessing & manipuating database sys.
it is used to retrive & update data in the database .
Note- It works with database prg. like DB2,MySQl,PostgreSql,ORacle,sqlite, MSAcess
etc.
Uses MySQL-
websites like Fb,twitter,github,youtube etc.
web developer .
RDBMS-
it is used to maintain a relational database .
It uses sql query to access the data in the database
It is easier to locate and access specific values in the database .
Relatinal str. makes it possible to run queries across multiple table at once .
Features of RDBMS ;
every piece of information stored in the form of table .
It has primary keys for unique identification .
it has foreign keys to ensure data integrity .
It provides SQL for data access.
It uses indexes for data retrival.
It gives access privilage to ensure data security .
RDBMS VS traditional approach - RDBMS stores data in tabular form whereas
Traditional approach in the form of files .
Constructs of SQL ;
Queries - Retrives data against some criteria .
Statements ; Control transactions , program flow , connections ,sessions or
diagnostics.
Clauses ; components of queries and statement .
Expression : Combination of symbols ,operators and key part of SQL elements .
Predicates ; Specifies condition .
SQl statement group in 5 categories ;
DATA MANIPULATION ; It is subset of sql used to add update & delete data.
Data Definition : It is used to manage table & index ,Create,ALTER,RENAME,DROP &
TRUNCATE .
Data Control : It is used to set permisssion to users & group of users .
Transaction :It contains no of sql statement . at the end changes are made at the
associated tables.
Procedures : tt creates which contains source code for performing repetitive task .
Most important SQL cmmands :
select - extracts data from database
Update - Updates data in a database
Delete - delete data from database
Insert into - inserts new data in the database
create database - creates a new database
alter database - modifies a database
Create table : creates a new table
Alter table : modifies a table
Drop table : deletes a table
Create index : creates an index
drop index : deletes the index
SQL DATA TYPES : Numeric , character/string ,date/time ,miscellaneous
SQL Constraints
Not null - Ensure there is no null value
Default : provides default value
Unique : all the values are unique in that column
primary - identifies each row/column record unique
Check : it ensures value in column satisfy certain conditions
Index :
SQL FILTERS:
WHERE CLAUSE: TO FIND SPECIFIES RECORD WE ARE USING WHERE COMMAND.
HAVING CLAUSE : IT PERFORMS ON AGGREGATE FUNCTIONS DUE TO ITS NON PERFORMANCE IN
WHERE CLAUSE
SQL OPERATORS ;SPECIAL
BETWEEN , LIKE , IS NULL, IN,DISTINCT
SQL OPERATORS : AGGREGATE
AVG , COUNT , MAX,MIN ,SUM
SQL ORDER BY CLAUSE ;IT SORTS OUT THE DATA ORDER BY COLUMN NAME ,DESC;
create table students (
studentID int ,
lastname Varchar (250),
firstname varchar (250),
Address varchar (250)
) ;
DROP DATABASE IF EXISTS `sql_invoicing`;
CREATE DATABASE `sql_invoicing`;
USE `sql_invoicing`;
alter table students
DROP index idx_pname;
create index idx_pname
on students (lastname,firstname);
CREATE TABLE `invoices` (
`invoice_id` int(11) NOT NULL,
`number` varchar(50) NOT NULL,
`client_id` int(11) NOT NULL,
`invoice_total` decimal(9,2) NOT NULL,
`payment_total` decimal(9,2) NOT NULL DEFAULT '0.00',
`invoice_date` date NOT NULL,
`due_date` date NOT NULL,
`payment_date` date DEFAULT NULL,
PRIMARY KEY (`invoice_id`),
KEY `FK_client_id` (`client_id`),
CONSTRAINT `FK_client_id` FOREIGN KEY (`client_id`) REFERENCES `clients`
(`client_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `invoices` VALUES (1,'91-953-3396',2,101.79,0.00,'2019-03-09','2019-03-
29',NULL);
INSERT INTO `invoices` VALUES (2,'03-898-6735',5,175.32,8.18,'2019-06-11','2019-07-
01','2019-02-12');
INSERT INTO `invoices` VALUES (3,'20-228-0335',5,147.99,0.00,'2019-07-31','2019-08-
20',NULL);
INSERT INTO `invoices` VALUES (4,'56-934-0748',3,152.21,0.00,'2019-03-08','2019-03-
28',NULL);
