Basic Commands using Structured Query Langauage(SQL)

Introduction to SQL Characteristics of SQL -SQL is an ANSI and ISO standard computer language for creating and manipulating databases. - SQL allows the user to create, update, delete, and retrieve data from a database. - SQL is very simple and easy to learn. - SQL works with database programs like DB2, Oracle, MS Access, Sybase, MS SQL Sever PostgreSQL etc.

Basic Queries Once logged in, you can try some simple queries. For example: mysql> SELECT VERSION(), CURRENT_DATE; +-----------+--------------+ | VERSION() | CURRENT_DATE | +-----------+--------------+ | 3.23.49 | 2002-05-26 | +-----------+--------------+ 1 row in set (0.00 sec) Note that most MySQL commands end with a semicolon (;) MySQL returns the total number of rows found, and the total time to execute the query. 3

Basic Queries Keywords may be entered in any lettercase. The following queries are equivalent: mysql> SELECT VERSION(), CURRENT_DATE; mysql> select version(), current_date; mysql> SeLeCt vErSiOn(), current_DATE; 4

Basic Queries Here's another query. It demonstrates that you can use mysql as a simple calculator: mysql> SELECT SIN(PI()/4), (4+1)*5; +-------------+---------+ | SIN(PI()/4) | (4+1)*5 | +-------------+---------+ | 0.707107 | 25 | +-------------+---------+ 5

Basic Queries You can also enter multiple statements on a single line. Just end each one with a semicolon: mysql> SELECT VERSION(); SELECT NOW(); +--------------+ | VERSION() | +--------------+ | 3.22.20a-log | +--------------+ +---------------------+ | NOW() | +---------------------+ | 2004 00:15:33 | +---------------------+ 6

Multi-Line Commands mysql determines where your statement ends by looking for the terminating semicolon, not by looking for the end of the input line. Here's a simple multiple-line statement: mysql> SELECT -> USER() -> , -> CURRENT_DATE; +--------------------+--------------+ | USER() | CURRENT_DATE | +--------------------+--------------+ | joesmith@localhost | 1999-03-18 | +--------------------+--------------+ 7

Canceling a Command If you decide you don't want to execute a command that you are in the process of entering, cancel it by typing c mysql> SELECT -> USER() -> c mysql> 8

Using a Database To create a new database, issue the “create database” command: ◦ mysql> create database webdb; To the select a database, issue the “use” command: ◦ mysql> use webdb; 10

Database Languages •DDL : Data Definition language • Specification notation for defining the database schema •DML : Data Manipulation language • Language for accessing and manipulating the data organized by the appropriate data model DMSA UNIT 1 11 11

Data Definition Language (DDL) •Specification notation for defining the database schema •Provides facilities to specify consistency constraints. • Example: create table account ( account-number char(10), balance integer); •DDL compiler generates a set of tables stored in a data dictionary which contains metadata. DMSA UNIT 1 12 12

Creating a Table To view all tables in selected database: mysql> show tables; Empty set (0.02 sec) An empty set indicates that no tables yet. 13

Data Manipulation Language (DML) Language for accessing and manipulating the data organized by the appropriate data model. Types of access are: Retrieval, Insertion, Deletion, Modification DML also known as query language SQL is the most widely used query language The portion of DML that involves information retrieval is called query language. DSA UNIT 1 14 14

Domain Types in SQL char(n). Fixed length character string, with user-specified length n. hold up to 255 characters. varchar(n). Variable length character strings, with user-specified maximum length n. hold up to 4000 characters int. Integer (a finite subset of the integers that is machine-dependent). smallint. Small integer (a machine-dependent subset of the integer domain type). numeric(p,d). Fixed point number, with user-specified precision of p digits, with n digits to the right of decimal point. Eg. 14.9 real, double precision. Floating point and double-precision floating point numbers, with machine-dependent precision. float(n). Floating point number, with user-specified precision of at least n digits.

INT - A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from -2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. You can specify a width of up to 11 digits FLOAT(M,D) - A floating-point number that cannot be unsigned. Decimal precision can go to 24 places for a FLOAT. 16

Creating a Table To create a table, use the CREATE TABLE command: mysql> CREATE TABLE TableName ( -> FieldName1 Dataype, -> FieldName2 Dataype, -> FieldName3 Dataype, ->. . . ); 17

Creating a Table To create a table, use the CREATE TABLE command: mysql> CREATE TABLE pet ( -> name VARCHAR(20), -> owner VARCHAR(20), -> species VARCHAR(20), -> sex CHAR(1), -> birth DATE, death DATE); Query OK, 0 rows affected (0.04 sec) 18

Showing Tables To verify that the table has been created: mysql> show tables; +------------------+ | Tables_in_test | +------------------+ | pet | +------------------+ 1 row in set (0.01 sec) 19

Unique key CREATE TABLE Student2 (Stud_ID int UNIQUE, Name varchar(45),Email varchar(45),Age int,City varchar(25)); insert into Student2 (stud_id) values(1); select * from Student2; insert into Student2 (stud_id) values(1); // not allowed insert into Student2 (stud_id) values(NULL); //allowed select * from Student2;

Not NULL CREATE TABLE Student2 (Stud_ID int UNIQUE, Name varchar(45) Not NULL, Email varchar(45),Age int,City varchar(25)); insert into Student2 (stud_id, Name) values(1,'Aryan’); select * from Student2; insert into Student2 (stud_id,Name ) values(NULL,NULL); // Error

IN operator (if equal to any one value of many) Stud_ID Name Email Age City 1 Aryan abc@gmail.com 20 Pune 2 Armaan qsc@gmail.com 21 Mumabi 3 Pooja hjk@gmail.com 23 Mysuru 4Jeevana mko@gmail.com 22 Bengaluru 5select * from Student2 where Stud_ID IN (2,4) ; 6OUTPUT : 7Stud_ID Name Email Age City 82 Armaan qsc@gmail.com 21 Mumabi 94 Jeevana mko@gmail.com 22 Bengaluru

Deleting a Table To delete an entire table along with structure, use the DROP TABLE command: Syntax : DROP TABLE TableName mysql> drop table pet; Query OK, 0 rows affected (0.02 sec) 24

Loading Data Use of INSERT statement to enter data into a table. Syntax: INSERT INTO TableName VALUES (Value1, Value2, Value3...); INSERT INTO pet VALUES ('Fluffy','Harold','cat','f',‘2008-02-07’, NULL); 25

SQL Select The SELECT statement is used to pull information from a table. The general format is: SELECT what_to_select FROM which_table Or SELECT column_name(s), FROM tablename(s) Eg Select * from Pets; Will retrive all records. 26

Selecting All Data The simplest form of SELECT retrieves everything from a table mysql> select * from pet; +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1999-02-04 | NULL | | Claws | Gwen | cat | f | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1999-08-27 | NULL | | Bowser | Diane | dog | m | 1998-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+--------+---------+------+------------+------------+ 8 rows in set (0.00 sec) 27

Selecting Particular Rows To find all animals born after 1998 SELECT * FROM pet WHERE birth >= "1998-1-1"; To find all female dogs, use a logical AND SELECT * FROM pet WHERE species = "dog" AND sex = “M"; To find all snakes or birds, use a logical OR SELECT * FROM pet WHERE species = "snake" OR species = "bird"; 29

Selecting Particular Columns Only Few columns contents can be seen. For example, To see pets name and birthdate, select the name and birth columns. select field1,filed2 from tableName(s); 30

Selecting Particular Columns mysql> select name, birth from pet; +----------+------------+ | name | birth | +----------+------------+ | Fluffy | 1999-02-04 | | Claws | 1994-03-17 | | Buffy | 1989-05-13 | | Fang | 1999-08-27 | | Bowser | 1998-08-31 | | Chirpy | 1998-09-11 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | +----------+------------+ 8 rows in set (0.01 sec) 31

Basic Deletion of Tuples Syntax Delete FROM TableName; Will delete all records For specific records to delete use WHERE clause Delete FROM pet where name =‘Fluffy’;

Sorting Data To sort a result, use an ORDER BY clause. For example, to view animal birthdays, sorted by date: 33 mysql> SELECT name, birth FROM pet ORDER BY birth; +----------+------------+ | name | birth | +----------+------------+ | Buffy | 1989-05-13 | | Claws | 1994-03-17 | | Slim | 1996-04-29 | | Whistler | 1997-12-09 | | Bowser | 1998-08-31 | | Chirpy | 1998-09-11 | | Fluffy | 1999-02-04 | | Fang | 1999-08-27 | +----------+------------+ 8 rows in set (0.02 sec)

Sorting Data To sort in reverse order, add the DESC (descending keyword) 34 mysql> SELECT name, birth FROM pet ORDER BY birth DESC; +----------+------------+ | name | birth | +----------+------------+ | Fang | 1999-08-27 | | Fluffy | 1999-02-04 | | Chirpy | 1998-09-11 | | Bowser | 1998-08-31 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | | Claws | 1994-03-17 | | Buffy | 1989-05-13 | +----------+------------+ 8 rows in set (0.02 sec)

Drop and Alter Table Constructs The drop table command deletes all information about the dropped table from the database. DROP table TableName; The alter table command is used to add attributes to an existing table: alter table T add column A datatype where A is the name of the attribute to be added to table T . All tuples in the table are assigned null as the value for the new attribute. The alter table command can also be used to drop attributes of a table: alter table T drop column A where A is the name of an attribute of table T Dropping of attributes not supported by many databases

Truncate TRUNCATE Table tablename; Will delete all the rows from specified Table . indexes and privileges will also be removed. No DML triggers will be fired..

Difference between Truncate and Delete 1.TRUNCATE is a DDL command whereas DELETE is a DML command. 2.TRUNCATE is much faster than DELETE. 3.You can't rollback in TRUNCATE but in DELETE you can rollback. TRUNCATE removes the record permanently. 4.In case of TRUNCATE ,Trigger doesn't get fired. But in DML commands like DELETE Trigger get fired. 5.You can't use conditions(WHERE clause) in TRUNCATE. But in DELETE you can write conditions using WHERE clause

Nulls Represents a value for an attribute that is currently unknown or is not applicable for this tuple. Different approaches for null value. Represents the absence of a value and is not the same as zero or spaces, which are values. Entity Integrity In a base relation no attribute of a primary key can be null. DMSA UNIT 1 38

Working with NULLs NULL means missing value or unknown value. To test for NULL, you cannot use the arithmetic comparison operators, such as =, < or <>. Rather, you must use the IS NULL and IS NOT NULL operators instead. 39

Working with NULLs For example, to find all your dead pets mysql> select name from pet where death IS NOT NULL; +--------+ | name | +--------+ | Bowser | +--------+ 1 row in set (0.01 sec) 40

Pattern Matching SQL Pattern matching: To perform pattern matching, use the LIKE or NOT LIKE comparison operators By default, patterns are not case sensitive. Special Characters: _ Used to match any single character. % Used to match an arbitrary number of characters. 41

Pattern Matching Example To find names beginning with ‘b’: mysql> SELECT * FROM pet WHERE name LIKE "b%"; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ 42

Pattern Matching Example To find names ending with `fy': mysql> SELECT * FROM pet WHERE name LIKE "%fy"; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+ 43

Pattern Matching Example To find names containing a ‘w’: mysql> SELECT * FROM pet WHERE name LIKE "%w%"; +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+ 44

Pattern Matching Example To find names containing exactly five characters, use the _ pattern character: mysql> SELECT * FROM pet WHERE name LIKE "_____"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 45

Foreign Keys A relation schema may have an attribute that corresponds to the primary key of another relation. The attribute is called a foreign key. ◦ E.g. customer_name and account_number attributes of depositor are foreign keys to customer and account respectively. ◦ Only values occurring in the primary key attribute of the referenced relation may occur in the foreign key attribute of the referencing relation. DSA UNIT 1 46

Counting Rows Databases are often used to answer the question, "How often does a certain type of data occur in a table?" For example, One might want to know how many pets are there in table. Counting the total number of animals you have is the same question as “How many rows are in the pet table?” because there is one record per pet. The COUNT() function counts the number of non-NULL results. 47

Counting Rows Example A query to determine total number of pets: mysql> SELECT COUNT(*) FROM pet; +----------+ | COUNT(*) | +----------+ | 9 | +----------+ 48

Aggregate Functions select Max(Age) from Student2; select Min(Age) from Student2; select Avg(Age) from Student2; select Sum(Marks) from Student;

Data Definition Language (DDL) Data dictionary contains metadata (i.e., data about data) Database schema Integrity constraints  Domain constraints  Referential integrity (references constraint in SQL)  Assertions Authorization DSA UNIT 1 50 50

Integrity Constraints Integrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the database do not result in a loss of data consistency. ◦ A checking account must have a balance greater than $10,000.00 ◦ A salary of a bank employee must be at least $4.00 an hour ◦ A customer must have a (non-null) phone number

User-Defined Types create type construct in SQL creates user-defined type create type Name as object (FirstName varchar(10), MiddleName varchar(10), LastName varchar(10)); Create table student (sid int primary key, Sname Name);

The check clause check (P ), where P is a predicate Example: Declare branch_name as the primary key for branch and ensure that the values of assets are non-negative. create table branch (branch_name char(15), branch_city char(30), assets integer, primary key (branch_name), check (assets >= 0))

SQL Operators Arithmetic Operators :+,-,*,/ Logical Operator :AND ,OR ,NOT Relational Operator:<, >, =, <=, >=, <>

Summary SQL provides a structured language for querying/updating multiple databases. The more you know SQL, the better. The most important part of SQL is learning to retrieve data. ◦ selecting rows, columns, boolean operators, pattern matching, etc. Keep playing around in the MySQL Shell. 55

Basic Commands using Structured Query Langauage(SQL)

More Related Content

Similar to Basic Commands using Structured Query Langauage(SQL)

Recently uploaded

Basic Commands using Structured Query Langauage(SQL)