SQL
DATABASE:
- Database is a container that holds Tables.
- The information inside the database is organised into "tables".
 |
| Database Table Structure |
- Column is a piece of data stored by your table.
- Row is a single set of columns which describes attributes of single thing.
SQL
SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database.
SQL is a language for accessing and manipulating database.
Create & Use Database
> CREATE DATABASE my_list; // Create new Database
> USE my_list; // Use database
- The SQL language requires all tables to be inside of database.
- The semi column is there to indicate that the command has ended.
CRUD operation
Create, Retrieve (Read), Update and Delete (CRUD) refers to the 4 major functions implemented in database applications.
For instance, a simple student database table adds (creates) new student details, access(reads) existing student details, modify (updates) existing student data such as email and deletes student detailswhen student leaves the school.
Often, simply reading an SQL statement out loud will give you a very good idea of what the command is intended to do.
For instance, a simple student database table adds (creates) new student details, access(reads) existing student details, modify (updates) existing student data such as email and deletes student detailswhen student leaves the school.
Often, simply reading an SQL statement out loud will give you a very good idea of what the command is intended to do.
ex:
> SELECT * FROM student WHERE class=2; // Here '*' represents all
|
Create Table
> CREATE TABLE table_name (
> column_name data_type(size),
> column_name data_type(size)
> );
- Attributes are specifying with names, data types to specify its domain and values.
- Data types parameter specifies what type of data column can hold (For ex. varchar, integer, decimal, mediumtext, etc.)
- Size specifies the maximum length of column
- SQL have 2 types of attributes - Primary Key, Foreign Key
- Attributes are specifying with names, data types to specify its domain and values.
- Data types parameter specifies what type of data column can hold (For ex. varchar, integer, decimal, mediumtext, etc.)
- Size specifies the maximum length of column
- SQL have 2 types of attributes - Primary Key, Foreign Key
Primary Key
- Primary key is uniquely identifier
- Prevents insertion of duplicate rows
> CREATE TABLE course (
|
> c_id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
|
> name VARCHAR(20),
|
> department VARCHAR(30)
|
> );
|
- NOT NULL indicates that that data can not be null.
- AUTO_INCREMENT indicates auto increment of id (can specify the increment value - by default its 1)
- Its a column in a table that references the primary key of another table.
> CREATE TABLE student (
|
> id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
|
> name VARCHAR(20),
|
> email VARCHAR(30),
|
> phone INT(12),
|
> class VARCHAR(10),
|
> c_id int(11) FOREIGN KEY REFERENCES (c_id)
|
> );
|
To check how created table looks like,
> DESC student ; // Describe the table
|
Select From Table
The select statement used to select data from database.
> SELECT column_name
> FROM table_name
> WHERE conditions;
For ex:
> SELECT name
|
> FROM student
|
> WHERE class=12;
|
-It will fetch the row id 2 data.
Update Data from Table
The update statement used to update records in a table.
> UPDATE table_name
> SET column_name
> WHERE condition;
For ex:
> UPDATE student
|
> SET email='robert@gmail.com'
|
> WHERE id=2;
|
//It will update the row id 2 email field.
Delete Table:
> DROP TABLE orders; // It will delete full table with its properties
|
Delete entries from table
- Delete all or specific rows from the table based on given condition.
> DELETE FROM studentes; // It will delete all rows from table
|
> DELETE FROM studentes WHERE id=3;//It will delete row with student id 3
|
