Scribd Logo
Upload

Welcome to Scribd!

  • 1 views

    Sessions

    Uploaded by

    saifamrrr123123

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Sessions

    Uploaded by

    saifamrrr123123
    1 views9 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    1 views9 pages

    Sessions

    Uploaded by

    saifamrrr123123

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 9

    SQL Database

    Let’s Start:
     MySQL is a database system used on the web
     MySQL is a database system that runs on a server
     MySQL is ideal for both small and large applications
     MySQL is very fast, reliable, and easy to use
     MySQL uses standard SQL
     MySQL compiles on a number of platforms
     MySQL is free to download and use
     MySQL is developed, distributed, and supported by Oracle Corporation
     MySQL is named after co-founder Monty Wideness’s daughter: My

    The data in a MySQL database are stored in tables.


    A table is a collection of related data, and it consists of columns and rows.

    SQL vs MYSQL:
    SQL: It is a structured query programming language that manages the
    relational database management system
    MYSQL: It is a relational database management system that uses SQL.

    A query is a question or a request.


    Query: Database Code.
    Database:
    CREATE DATABASE databasename;
    DROP DATABASE databasename;

    Table:
    - CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ....
    );
    - DROP TABLE table_name;
    - TRUNCATE TABLE table_name;
    CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
    );

    Data Types:
    - VARCHAR(size)
    - TEXT(size)
    - BOOL
    - INT(size)
    - INTEGER(size)
    - FLOAT(size, d)
    - DATE
    Constraints:
    CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
    );

     Not Null:
    o ID int NOT NULL,

     UNIQUE:
    o ID int NOT NULL UNIQUE,
     Primary Key:
    o ID int NOT NULL PRIMARY KEY,
    o CONSTRAINT PK_Person PRIMARY KEY (ID)
     FOREIGN KEY:
    o FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
     Check:
    o Age int CHECK (Age>=18)
    o CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sand
    nes')
     DEFAULT:
    o City varchar(255) DEFAULT 'Sandnes'
    o OrderDate date DEFAULT GETDATE()
     AUTO INCREMENT:
    o Personid int NOT NULL AUTO_INCREMENT,

    CREATE TABLE posts (

    id int(10) ,

    post TEXT NOT NULL,

    user_id int(10),

    CONSTRAINT pk_posts PRIMARY KEY(id),

    CONSTRAINT fk_post_user FOREIGN KEY (user_id) REFERENCES


    users(id)
    Alter Table:
     ALTER TABLE table_name
    ADD column_name datatype;
    o ALTER TABLE Customers
    ADD Email varchar(255);

     ALTER TABLE table_name


    DROP COLUMN column_name;
    o ALTER TABLE Customers
    DROP COLUMN Email;

     ALTER TABLE table_name


    RENAME TO new_table_name;
    o ALTER TABLE Student RENAME TO Student_Details;

     ALTER TABLE table_name


    RENAME COLUMN old_name to new_name;
    o ALTER TABLE Student RENAME Column name TO
    FIRST_NAME;
    Insert Into Table:
     INSERT INTO table_name (column1, column2, column3, ...)
    VALUES (value1, value2, value3, ...);
     INSERT INTO table_name
    VALUES (value1, value2, value3, ...);
    o INSERT INTO Customers (CustomerName, City, Country)
    VALUES ('Cardinal', 'Stavanger', 'Norway');

    Update Statement:
     UPDATE table_name
    SET column1 = value1, column2 = value2, ...
    WHERE condition;
    o UPDATE Customers
    SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
    WHERE CustomerID = 1;
    o UPDATE Customers
    SET ContactName='Juan'
    WHERE Country='Mexico';
    o UPDATE Customers
    SET ContactName='Juan';

    Delete Statement:
     DELETE FROM table_name WHERE condition;
    o DELETE FROM Customers WHERE
    CustomerName='Alfreds Futterkiste';
     DELETE FROM table_name;
    o DELETE FROM Customers;

    Drop Table:
     DROP TABLE Customers;
    Select Data From Database:
     SELECT column1, column2, ...
    FROM table_name;
    o SELECT CustomerName, City FROM Customers;

     SELECT * FROM Customers;


    o return all columns

    DISTINCT:
     SELECT DISTINCT column1, column2, ...
    FROM table_name;
    o SELECT DISTINCT Country FROM Customers;
    o SELECT Country FROM Customers;

    WHERE Clause: used to filter records

     SELECT column1, column2, ...


    FROM table_name
    WHERE condition;
    o SELECT * FROM Customers
    WHERE Country='Mexico';
    o SELECT * FROM Customers
    WHERE CustomerID=1;
    o SELECT * FROM Customers
    WHERE CustomerID > 80;

    Operators that can be used in the where clause:


    Ex)
    Between:
     SELECT column_name(s)
    FROM table_name
    WHERE column_name BETWEEN value1 AND value2;
    o SELECT * FROM Products
    WHERE Price BETWEEN 10 AND 20;
    o SELECT * FROM Products
    WHERE Price NOT BETWEEN 10 AND 20

    IN:
     SELECT column_name(s)
    FROM table_name
    WHERE column_name IN (value1, value2, ...);
    o SELECT * FROM Customers
    WHERE Country IN ('Germany', 'France', 'UK');
    o SELECT * FROM Customers
    WHERE Country NOT IN ('Germany', 'France', 'UK');

    AND:
     SELECT column1, column2, ...
    FROM table_name
    WHERE condition1 AND condition2 AND condition3 ...;
    o SELECT * FROM Customers
    WHERE Country = 'Germany'
    AND City = 'Berlin'
    AND PostalCode > 12000;

    OR:
     SELECT column1, column2, ...
    FROM table_name
    WHERE condition1 OR condition2 OR condition3 ...;
    o SELECT *
    FROM Customers
    WHERE Country = 'Germany' OR Country = 'Spain';
    NOT
     SELECT column1, column2, ...
    FROM table_name
    WHERE NOT condition;
    o SELECT * FROM Customers
    WHERE NOT Country = 'Spain';

    LIKE:
     SELECT column1, column2, ...
    FROM table_name
    WHERE columnN LIKE pattern;

     The percent sign % represents zero, one, or multiple characters


     The underscore sign _ represents one, single character

    o SELECT * FROM Customers


    WHERE CustomerName LIKE 'a%';
    o SELECT * FROM Customers
    WHERE city LIKE 'L_nd__';
    o SELECT * FROM Customers
    WHERE city LIKE '%L%';
    o SELECT * FROM Customers
    WHERE CustomerName LIKE 'La%';
    o SELECT * FROM Customers
    WHERE CustomerName LIKE '%a';
    o SELECT * FROM Customers
    WHERE CustomerName LIKE 'b%s';
    o SELECT * FROM Customers
    WHERE CustomerName LIKE '%or%';
    o SELECT * FROM Customers
    WHERE CustomerName LIKE 'a%' OR CustomerName LIKE 'b
    %';
    here

    You might also like