Introduction to SQL and MySQL

Introduction to SQL

• Structured Query Language (SQL) is a popular query language for RDBMS like MySQL, Oracle, and SQL Server
• Allows efficient creation, storage, retrieval, and manipulation of data in databases
• It uses descriptive English words and is not case sensitive

SQL Capabilities

• Defines the structure of data
• Manipulates data within the database
• Declares constraints
• Retrieves data in various ways

MySQL

• An open source RDBMS software, downloadable from the official website
• After installation, the appearance of mysql> prompt signals readiness for SQL statement input

SQL Rules in MySQL

• SQL commands are case-insensitive
• SQL statements should end with a semicolon (;)
• For multi-line statements, do not use a semicolon (;) after the first line and press Enter to continue on the next line, where the prompt will then change to ->

Data Types in MySQL

• Data types define values an attribute can have and determine operations that can be performed
• Common data types: numeric, date/time, and string (character and byte)

CHAR(n)

• A character data type of fixed length, n
• Length can range from 0 to 255
• Declaring CHAR(10) reserves space for 10 characters
• With data of less than 10 characters, MySQL adds space to the right

VARCHAR(n)

• A character data type of variable length, n
• Length can range from 0 to 65535
• Declaring VARCHAR(30) stores a maximum of 30 characters
• Bytes allocated depend on the length of entered string

INT

• Integer value
• Each INT value occupies 4 bytes of storage
• Ranges from -2147483648 to 2147483647
• BIGINT usage is for values larger than regular INT, occupies 8 bytes

FLOAT

• Numbers with decimal points
• Each FLOAT value occupies 4 bytes

DATE

• Represents Dates in 'YYYY-MM-DD' format
• 'YYYY' is the 4 digit year, 'MM' the 2 digit month, and 'DD' is the 2 digit date
• The supported range is '1000-01-01' to '9999-12-31'

Constraints

• Restrictions on the data values in an attribute
• Ensure data accuracy and reliability
• Defining constraint for each attribute is optional

NOT NULL

• Indicates that a column cannot have NULL values
• A NULL value means missing, unknown, or not applicable

UNIQUE

• All values in a column are distinct/unique

DEFAULT

• A default value assigned to the column if no value is provided

PRIMARY KEY

• Identifies a column that uniquely identifies each row/record in a table

FOREIGN KEY

• A column that references a primary key in another table

Data Definition Language (DDL)

• SQL commands to define, modify, and delete relation schemas
• Specifies relations, including schema, data type for attributes, constraints, security, and access authorizations

CREATE statement

• Used to begin data definition
• Used to create a database and its tables/relations

CREATE Database

• To create a database to store the relations
• CREATE DATABASE databasename;

StudentAttendance

• Example Databasename for storage of student records

Database names are case-sensitive

• Names are case-sensitive in LINUX
• The names in WINDOWS are not case-sensitive
• A good procedure is to write database or table name in the letter cases that were used at the time of their creation

DBMS

• Manages multiple databases on one computer
• Must select the database to use before creating tables or querying data
• USE StudentAttendance;

Empty database

• Intially the databases created are all empty, the Show tables command displays contents
• SHOW TABLES;

CREATE Table

• Defines relations and specifies data types for attributes
• Uses the CREATE TABLE statement

CREATE TABLE statement

• CREATE TABLE tablename(
• attributename1 datatype constraint,
• attributename2 datatype constraint,
• attributenameN datatype constraint);

Aspects of CREATE TABLE statement

• "N" is the degree of the relation, which means there are "N" Columns in the table
• "Attribute name" is the name of the column in the table
• "Datatype" specifies the type of data can hold
• "Constraint" indicates the restrictions must follow. By default can take NULL, except Primary key

Identifying Data Types

• Determine the appropriate data types for table attributes along with any applicable constraints

RollNumber Data

• INT data type is chosen for the RollNumber attribute, assuming a small class size so 3 digits are sufficient

SName Data

• For the student names(SName) VARCHAR(20) is used, assuming maxium characters can differ

SDateofBirth Data

• Use of the DATE datatype is chosen for SDateofBirth
• Where schools use guardian's 12 digit Aadhaar number as GUID

GUARDIAN Data

• GUID Is decared as CHAR(12), since Aadhaar card is fixed length
• No mathematical operation on GUID will be run

Tables and constraints

• Table 8.3, 8.4 and 8.5 show the chosen data type and constraint for each attribute for each relation

Create table examples

mysql> CREATE TABLE STUDENT( -> RollNumber INT, -> SName VARCHAR(20), -> SDateofBirth DATE, -> GUID CHAR(12), -> PRIMARY KEY (RollNumber));

Note statements regarding CREATE TABLE example

• "," is used to seperate two Attributes
• Terminates With semicolon (;)
• The symbol -> indicates continuing on with code
• Example will not complete on a single line

DESCRIBE Table

• View the structure of an already created table
• By using the describe statement with either 'DESCRIBE' or 'DESC' DESCRIBE tablename;

mysql> DESC STUDENT; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | RollNumber | int | NO | PRI | NULL | | | SName | varchar(20) | YES | | NULL | | | SDateofBirth | date | YES | | NULL | | | GUID | char(12) | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+ 4 rows in set (0.06 sec)

MySQL Shell Show TABLES

• Commands now return the table student

mysql> SHOW TABLES; +------------------------------+ | Tables_in_studentattendance | +------------------------------+ | student | +------------------------------+ 1 row in set (0.00 sec)

ALTER Table

ALTER TABLE tablename ADD/Modify/DROP attribute1, attribute2,..

• Add/remove an attribute
• To modify the datatype of an existing attribute
• To add constraint in attribute
• In such cases, change or alter the structure of the table by using the alter statement

Adding a Primary Key

Adds a primary key to the GUARDIAN relation

mysql> ALTER TABLE GUARDIAN ADD PRIMARY KEY (GUID); Query OK, 0 rows affected (1.14 sec) Records: 0 Duplicates: 0 Warnings: 0

Adding a Primary Key To ATTENDANCE relation

Adds primary key to the ATTENDANCE relation where The primary key of this relation is a composite key made up of two attributes — AttendanceDate and RollNumber.

mysql> ALTER TABLE ATTENDANCE -> ADD PRIMARY KEY(AttendanceDate, -> RollNumber); Query OK, 0 rows affected (0.52 sec) Records: 0 Duplicates: 0 Warnings: 0

Add a foreign key to an existing relation

mysql> ALTER TABLE STUDENT -> ADD FOREIGN KEY(GUID) REFERENCES -> GUARDIAN(GUID); Query OK, 0 rows affected (0.75 sec) Records: 0 Duplicates: 0 Warnings: 0

A foreign key can reference a attribute in a table existing already

Adding A unique constraint

• Add constraint UNIQUE to an existing Atribute
• the GPhone attribute has a constraint UNIQUE Which means no two values in that colum should be the same

Syntax Add A unique constraint

ALTER TABLE table_name ADD UNIQUE (attribute name);

ALTER TABLE GUARDIAN -> ADD UNIQUE(GPhone);

Adding an attribute

ALTER TABLE table_name ADD attribute_name DATATYPE;

• Adds an "income" column with INT ALTER TABLE GUARDIAN -> ADD income INT;

Modify datatype attribute example

ALT ER TABLE table_name MODIFY attribute DATATYPE;

• The MySQL code would to modify attributes is listed: ALTER TABLE GUARDIAN -> MODIFY GAddress VARCHAR(40);

Modify Constraint of attribute example

ALTER TABLE table_name MODIFY attribute DATATYPE NOT NULL;

Not null Example

• We Write the following code to associate the NOT NULL constraint With SName attribute

ALTER TABLE STUDENT -> MODIFY SName VARCHAR(20) NOT NULL;

Add Default Value to Attribute

ALTER TABLE table_name MODIFY attribute DATATYPE DEFAULT default_value;

Add Default Value

• To set default value of SDateofBirth of STUDENT to 15th May 2000, write the following statement:

mysql> ALTER TABLE STUDENT
   -> MODIFY SDateofBirth DATE DEFAULT
   -> 2000-05-15;

Remove an Attribute

ALTER TABLE table_name to drop

To remove an attribute income from the table GUARDIAN (8.4), the following MySQL code is used

mysql> ALTER TABLE GUARDIAN DROP income;

Remove A Primary Key

ALTER TABLE table_name DROP Primary Key;

DROP existing primary keys with the new primary keys: SQL statement: ALTER TABLE GUARDIAN DROP PRIMARY KEY;

The DROP Statement

The remove database command DROP statement is listed and used with:

 DROP TABLE table_name;

Drop a database with the next code:

DROP DATABASE database_name;

Cautions for this command is that:

• Drop statement will remove all tables too from the database.
• The tables dropped can only be completed at the end of Chapter completion

Data Manipulation Language(DML)

• Insert,update and delete data form a table Data insert
• INSERT INTO tablename VALUES(value 1, value 2,....);

INSERT Example

• Inserts the GURDIAN data with INSERT into the table

   mysql> INSERT INTO GUARDIAN
       -> VALUES (444444444444, 'Amit Ahuja',
       -> 5711492685, 'G-35,Ashok vihar, Delhi' );
  

SELECT * from table statement with GUI statements

The GUI statements are show from inside the console by selecting ALL

Note

• Text and Dates need to be in '' (single quotes)

Missing Data

 INSERT INTO GUARDIAN(GUID, GName, GAddress)
    -> VALUES (333333333333, 'Danny Dsouza',
    -> 'S -13, Ashok Village, Daman' );

INSERTS into multiple students details

Here the details needed are to put the dates and correct numbers for the student

  mysql> INSERT INTO STUDENT
      -> VALUES(1,'Atharv Ahuja','2003-05-15',
      -> 444444444444);
  Query OK, 1 row affected (0.11 sec)

To the query the student details is listed with insert, this is done by:

     mysql> INSERT INTO STUDENT (RollNumber, SName,
         -> SDateofBirth, GUID)
             -> VALUES (1,'Atharv Ahuja','2003-05-15',
             -> 444444444444);
     Query OK, 1 row affected (0.02 sec)

Note

 The date needs to be in a ('''' single quoted) YYYY_MM_DD format.

SQL Update

The Data Manipulation is listed with 2 functions, either to insert Data OR update it

The correct SQL code is : "UPDATE tablename"

Note

• Text and Date need to be in a single or Double quote.