COMP 5531 Introduction to MySQL

SQL • SQL is a standard language for accessing and managing databases.

• SQL stands for Structured Query Language.

What can SQL do?

• • • • • • Retrieve data from a database.

Insert records in a database.

Update existing records.

Delete records.

Create new database.

Create new tables etc.

SQL Components • SQL has three main parts.

– DDL (Data Definition Language) • CREATE TABLE, DROP TABLE, ALTER TABLE – DML (Data Manipulation Language) • SELECT, INSERT, UPDATE, DELETE – DCL (Data Control Language) • GRANT, REVOKE

MySql • Based on RDBMS. (Relational Database Management System) • The most popular Open Source SQL.

• Download link – http://dev.mysql.com/downloads/

Manage Your Database • • • • • CREATE DATABASE database_name; SHOW DATABASES; USE database_name; DROP DATABASE database_name; SHOW TABLES;

Create table CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, ....

);

Data types • Kind of values it can represent

Type

INT DECIMAL CHAR VARCHAR DATE TIME DATETIME

Description

A standard integer A fixed-point number A fixed-length non-binary string A variable-length non-binary string A date value in ‘YYYY-MM-DD’ format A time value in ‘hh:mm:ss’ format ‘YYYY-MM-DD hh:mm:ss’ format

An example of Create Table CREATE TABLE mytable (Student_Id int, LastName varchar(255), FirstName varchar(255), GPA decimal(3,2), DOB date);

Managing the table • • DESC mytable; DROP TABLE mytable;

Inserting data into table INSERT INTO table_name VALUES (value1,value2,value3,...); Or INSERT INTO table_name (column1,column2,...) VALUES (value1,value2,...);

Example of inserting data insert into mytable values (6246893,'Krishnan','Giri',3.44,'1987-03-03');

Viewing data in the table SELECT column_name,column_name FROM table_name;

And

SELECT * FROM table_name;

Example of viewing data • select * from mytable; • select student_id, gpa from mytable;

View data with a WHERE clause • SELECT column_name,column_name FROM table_name WHERE column_name operator value • select * from mytable where student_id = 6246893;

Operators in the WHERE clause Operator = <> Description Equal Not equal > Greater than < >= Less than Greater than or equal <= Less than or equal BETWEEN Between an inclusive range LIKE IN Search for a pattern To specify multiple possible values for a column

Select DISTINCT data • SELECT DISTINCT column_name,column_name FROM table_name; •

select distinct firstname from mytable;

SQL Constraints • • • • • • NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK DEFAULT