SQL Basics
I. Introduction to SQL
SQL is a standard language for accessing and manipulating databases
RDBMS
-
RDBMS stands for Relational Database Management System.
-
RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
-
The data in RDBMS is stored in database objects called tables.
-
A table is a collection of related data entries and it consists of columns and rows
1. Database Tables: A database most often contains one or more tables. Each table is identified by a name (e.g. “Customers” or “Orders”). Tables contain records (rows) with data
2. SQL Statements Most of the actions you need to perform on a database are done with SQL statements. SQL keywords are NOT case sensitive: select is the same as SELECT
SQL Operations
Connecting MySQL with XAMPP command-line
Connecting MySQL with XAMPP commndline
cd C:\xampp\mysql\bin
c:
mysql.exe -u root -p
root
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| mysql |
| student |
| test |
+--------------------+
8 rows in set (0.00 sec)
MariaDB [(none)]> use student
Database changed
MariaDB [student]> show tables;
+--------------------+
| Tables_in_student |
+--------------------+
| student |
| user |
+--------------------+
Some of the Most Important SQL Commands
1.CREATE DATABASE - creates a new database
CREATE DATABASE `jdbc`
2.DROP DATABASE - modifies a database
DROP DATABASE `jdbc`
3.CREATE TABLE - creates a new table
CREATE TABLE `student` (
`sno` INT NOT NULL,
`name` VARCHAR(50) NULL,
`address` VARCHAR(50) NULL,
PRIMARY KEY (`sno`)
);
4.ALTER TABLE - modifies a table
ALTER TABLE `student` ADD COLUMN `city` INT(11) NOT ;
5.DROP TABLE - deletes a table
DROP TABLE `student;
6.CREATE INDEX - creates an index (search key)
ALTER TABLE `student` ADD UNIQUE INDEX `city` (`city`);
7.DROP INDEX - deletes an index
ALTER TABLE `student` DELETE UNIQUE INDEX `city` (`city`);
8.INSERT INTO - inserts new data into a database
INSERT INTO `student` (`name`, `address`) VALUES ('Ravi', 'HYD');
9.SELECT - extracts data from a database
10.UPDATE - updates data in a database
11.DELETE - deletes data from a database