Relational databases
SQL

SQL statements can be categorized into five types:

CategoryPurpose
1. Query statements They make the search of the information possible
  • SELECT
  • 2. Data manipulation statements Allow you to change the contents of the tables
  • INSERT
  • UPDATE
  • DELETE
  • 3. Data definition statements Allow you to change the design of the database
  • CREATE
  • ALTER
  • DROP
  • RENAME
  • TRUNCATE
  • 4. Transaction control statements They make the saving or cancelling of the change in the contents of records possible
  • COMMIT
  • ROLLBACK
  • SAVEPOINT
  • 5. Data control statements) Allow you to change the user priviledges
  • GRANT
  • REVOKE
  • Comments

    Usually all products(except Access) interpret the line as comment if it begins with "--". In other words so:
    -- Commentline
    Also the following comment is possible in most of the products:
    /* comment begins
    and comment ends */

    In MySQL the # sign at the beginning of the line also will signify the comment.

    Create table

    Tables are created with the CREATE TABLE statement. After that there will be the name of the new table end inside brackets the field names and their types.

    Example
      CREATE TABLE person(
      id_person SMALLINT PRIMARY KEY,
      firstname CHAR(15) NOT NULL,
      lastname CHAR(15) NOT NULL,
      city CHAR(10),
      birthyear SMALLINT,
      salary DOUBLE) ENGINE=InnoDB;
    
    Primary Key
    It is defined by adding the text primary key after the name of the field

    Example creating a table person in which the field id_person is defined as a primary key:
      CREATE TABLE person(
      id_person SMALLINT primary key
      ...
    
    The same is achieved also as follows:
      CREATE TABLE person(
      id_person SMALLINT,
      ...
      PRIMARY KEY(id_person)
      ...
    

    If a primary key consists of more fields, the latter one will be used
    Example
      PRIMARY KEY (id_project, id_person)
    
    Indexes

    Index in sql is created to tables to retrieve the rows quickly. When there are thousands of records in a table, retrieving information will take a long time. Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the data and then it assigns a ROWID for each row.

    When you create an index on the index defines whether duplicates or not. If duplicate values are allowed, the index is created with keyword INDEX.

    Example
      CREATE TABLE person(
      id_person SMALLINT PRIMARY KEY,
      firstname CHAR(15) NOT NULL,
      lastname CHAR(15) NOT NULL,
      city CHAR(10),
      birthyear SMALLINT,
      salary DOUBLE,
      INDEX lastnameIndex (lastname));
    
    And if you want to disallow duplicates on the index fields, use Unique
      CREATE TABLE person(
      id_person SMALLINT PRIMARY KEY,
      firstname CHAR(15) NOT NULL,
      lastname CHAR(15) NOT NULL,
      city CHAR(10),
      birthyear SMALLINT,
      salary DOUBLE,
      UNIQUE lastnameIndex (lastname) );
    
    Also you can make an index which includes several fields:
      CREATE TABLE person(
      id_person SMALLINT PRIMARY KEY,
      firstname CHAR(15) NOT NULL,
      lastname CHAR(15) NOT NULL,
      city CHAR(10),
      birthyear SMALLINT,
      salary DOUBLE,
      INDEX nameIndex (firstname,lastname) );
    

    If you don't give any name to the index, MySQL names the index according to the first index field.
    Example INDEX (fi,si) -> index will be named fi.

    You can also add indexes later, when the table already exists. Then you can use below code:

         CREATE INDEX lastnameIndex ON person(lastname);
      

    You can check what indexes the table has with below command:

        show indexes from person\G
      

    You can delete indexes with below command:

        DROP INDEX lastnameIndex ON person;
      

    Datatypes in SQL

    Data types vary slightly in different systems, so you should check the product manual. Most products has below types:

    • CHAR(max)
    • VARCHAR(max)
    • SMALLINT
    • INTEGER
    • FLOAT
    • REAL
    • DOUBLE
    • DATE
    • TIME

    Referential integrity - Foreign keys

    The purpose of referential integrity is to take care that all rows in child-table has a parent row in parent table.

    In order to create table named hour to MySQL, you can use below code

      CREATE TABLE hour (
      id_project CHAR(5) NOT NULL,
      id_person SMALLINT NOT NULL,
      workhour INT,
      PRIMARY KEY (id_project,id_person),
      CONSTRAINT project_hour
      FOREIGN KEY (id_project) REFERENCES project (id_project)
        ON DELETE RESTRICT
        ON UPDATE CASCADE) Engine=InnoDB;
    
    In order to execute above code, you have to first create a table named project which has field id_project. And now if you update some value of id_project int the project table(parent-table), the same changes will be made automatically to hour table(child table). And you can not delete rows from project table if there is child rows in hour table.
    Alter Table

    The SQL ALTER TABLE command is used to modify the definition (structure) of a table by modifying the definition of its columns. The ALTER command is used to perform the following functions.

    1. Add, drop, modify table columns
    2. Add and drop constraints
    3. Enable and Disable constraints
    4. Change the engine in MySQL
    Examples
      ALTER TABLE person
      ADD COLUMN test CHAR(20);
    
      ALTER TABLE person
      MODIFY COLUMN test CHAR(40);
    
      ALTER TABLE person
      DROP COLUMN test;
    
      ALTER TABLE person
      ADD INDEX ind_name (lastname);
    
    Drop Table
    You can delete tables from database with the drop table command. Note that the foreign keys might disallow you to drop the parent table, if the child table still exists.
    Example
      DROP TABLE hour;
    
      DROP TABLE hour,person;
    
    Select Queries

    Select Queries will produce a view (return data).

    Select queries consists of The following elements :
    SELECT which columns will be retrieved
    FROM from what table/tables or a view/views is it searched for
    WHERE which rows will be included in view
    GROUP BY how is it grouped
    HAVING what of the lines which have been created as a result of the grouping are shown
    ORDER BY how is it sorted

    Examples

    --this will return id_person and lastname
    SELECT id_person, lastname
    FROM person;
    
    --this will return all fields from the table
    SELECT *
    FROM person;
    
    Distinct

    You can use DISTINCT parameter of the SELECT statement to return only those lines as a result which have differences.

    SELECT DISTINCT city
    FROM person;
    
    prints all city-names only once.
    As

    The lastname of the field which is outputted in the view can be determined with AS parameter.

    Example
    SELECT lastname AS Name
    FROM person;
    
    Literals
    Example
    SELECT name, 'Some text'
    FROM person;
    
    CONCAT

    You can concatenate several fields and literals together

    Example
    SELECT CONCAT(firstname," ", lastname) AS Name
    FROM person;
    
    Substring
    In MySQL a subset of the character string can be printed with the SUBSTRING-function

    Example
    SELECT SUBSTRING(lastname,2,4) FROM person;
    
    This will print 4 characters from name starting from the second character.
    Where

    You can use Where statement to choose which rows will be included to the view.

    Examples
    SELECT *
    FROM person
    WHERE city ='TURKU';
    
    SELECT *
    FROM person
    WHERE NOT city='TURKU';
    
    SELECT *
    FROM person
    WHERE city <>'TURKU';
    
    SELECT *
    FROM person
    WHERE salary=5000;
    
    IN

    If there is several option, it is shorter to use IN query

    SELECT *
    FROM person
    WHERE city in ('Turku', 'Tampere', Oulu);
    
    BETWEEN

    If you need to return values between some values, you can use below example

    SELECT *
    FROM person
    WHERE salary between 4000 AND 5000;
    
    IS NULL

    Missing information can be found with below code:

    SELECT *
    FROM person
    WHERE city IS NULL;
    
    Like (SECTION CHARACTER STRING SEARCH)

    Example

    WHERE city LIKE 'T%'
    
    So it shows those records in which the city begins with the letter, T
    WHERE lastnameLIKE '_i%'
    
    shows those records which has i as a second letter in the name-field

    Wildcard Characters in MySQL
    _ : any character (only one character)
    % : any character or string (also several characters)
    Aggregate Functions

    The Aggregate Functions are:

    • AVG
    • MIN
    • MAX
    • COUNT
    • SUM
    • GROUP_CONCAT
    Example
    SELECT AVG(priorit)
    FROM project;
    
    Group By
    Used with aggregate functions, when the SELECT has also some other field/fields than the aggregate function
    SELECT city, count(id_person)
    FROM person
    GROUP BY city;
    
    Example
    SELECT city, GROUP_CONCAT(lastname) AS peoples
    FROM person
    GROUP BY city;
    
    the following result is obtained:
    +----------+----------------------+
    | city     | peoples              |
    +----------+----------------------+
    | HELSINKI | River,Simpson        |
    | TAMPERE  | Daniels              |
    | TURKU    | Morrison,Smith,Jones |
    +----------+----------------------+
    
    Having
    Example
    place is picked up at the project table if the city has two or more records
    SELECT place, count(*)
    FROM project
    GROUP BY place
    HAVING COUNT (*) >= 2;
    
    Join

    SQL join clause combines records from two or more tables in a database. You can combine the parent and child table with JOIN and then return fields from both tables. MySQL has three kind of JOINS:

    • INNER JOIN
    • LEFT OUTER JOIN (or LEFT JOIN)
    • RIGHT OUTER JOIN (or RIGHT JOIN)
    Inner Join
    Inner join is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row
    The join is made in FROM clause. For example Table1 and Table2 both have a field called id (in most cases id is the primary key in one table and foreign key in the other table). The inner joint is created between them as follows:

    FROM Table1 INNER JOIN Table2 ON Table1.id=Table2.id

    Example Creating an inner join between tables hour and project.
    select project.id_project,pname,work_hour
    FROM project inner JOIN hour ON project.id_project = hour.id_project;
    
    And the result is this
    +------------+-------------+-----------+
    | id_project | pname       | work_hour |
    +------------+-------------+-----------+
    |        101 | Bookkeeping |       300 |
    |        101 | Bookkeeping |       200 |
    |        101 | Bookkeeping |       200 |
    |        101 | Bookkeeping |       100 |
    |        101 | Bookkeeping |       100 |
    |        101 | Bookkeeping |       400 |
    |        102 | Billing     |       300 |
    |        102 | Billing     |       400 |
    |        103 | Store       |       200 |
    |        104 | Selling     |       300 |
    |        104 | Selling     |       200 |
    |        104 | Selling     |       400 |
    +------------+-------------+-----------+
    
    Outer Join
    If you want to view all the records from another table (usually the parent-table), Although another table has not all matching records, the outer join is used.

    Example
    FROM Table1 LEFT OUTER JOIN Table2 ON Table1.id=Table2.id
    
    Is the same as:
    FROM Table2 RIGHT OUTER JOIN Table1 ON Table2.id=Table1.id
    
    N.B.! The word OUTER is often omitted

    Example
    select project.id_project,pname,work_hour
    FROM project left JOIN hour ON project.id_project = hour.id_project;
    
    And the result is this
    +------------+-------------+-----------+
    | id_project | pname       | work_hour |
    +------------+-------------+-----------+
    |        101 | Bookkeeping |       300 |
    |        101 | Bookkeeping |       200 |
    |        101 | Bookkeeping |       200 |
    |        101 | Bookkeeping |       100 |
    |        101 | Bookkeeping |       100 |
    |        101 | Bookkeeping |       400 |
    |        102 | Billing     |       300 |
    |        102 | Billing     |       400 |
    |        103 | Store       |       200 |
    |        104 | Selling     |       300 |
    |        104 | Selling     |       200 |
    |        104 | Selling     |       400 |
    |        105 | Customers   |      NULL |
    |        106 | Statistics  |      NULL |
    +------------+-------------+-----------+
    
    Joining Several Tables
    The three tables of Project database can be joined using below code
    FROM person INNER JOIN hour ON person.id_person=hour.id_person
      INNER JOIN project ON hour.id_project=project.id_project;
    
    Union

    You can retrieve records from several tables using Union Query

    Example
    The persons which salary is over 40 and/or which belong to the project 102

    SELECT lastname as Name,"Salary is over 40"
    FROM person
    WHERE salary>40
    UNION
    SELECT lastname,"Belongs to 102"
    FROM person INNER JOIN hour ON person.id_person = hour.id_person
    WHERE id_project='102';
    
    The results are
    +----------+-------------------+
    | Name     | Salary is over 40 |
    +----------+-------------------+
    | Morrison | Salary is over 40 |
    | Jones    | Salary is over 40 |
    | Morrison | Belongs to 102    |
    | River    | Belongs to 102    |
    +----------+-------------------+
    
    You can also make Union queries like this:
    SELECT lastname as Name,"Salary is over 40"
    FROM person
    WHERE salary>40
    UNION 
    SELECT lastname as Name,"Salary is not over 40"
    FROM person
    WHERE salary<=40;
    
    The results are
    +----------+-----------------------+
    | Name     | Salary is over 40     |
    +----------+-----------------------+
    | Morrison | Salary is over 40     |
    | Jones    | Salary is over 40     |
    | Smith    | Salary is not over 40 |
    | River    | Salary is not over 40 |
    | Simpson  | Salary is not over 40 |
    | Daniels  | Salary is not over 40 |
    +----------+-----------------------+
    
    You can also replace previous Union with IF like this
    SELECT lastname as Name, 
      IF(salary>40, "Salary is over 40", "Salary is not over 40") as "Explanation"
    FROM person;
    


    Example Printing lastname and text: 'Less than 39', 'Exactly 39', 'Over 39'.
      SELECT lastname,"Less than 39"
      FROM person
      WHERE salary<39
      UNION
      SELECT lastname,"Over 39"
      FROM person
      WHERE salary>39
      UNION SELECT lastname,"Exactly 39"
      FROM person
      WHERE salary=39
      ORDER BY 2;
    
    And the result is
    +----------+--------------+
    | lastname | Less than 39 |
    +----------+--------------+
    | Smith    | Exactly 39   |
    | River    | Exactly 39   |
    | Simpson  | Less than 39 |
    | Daniels  | Less than 39 |
    | Morrison | Over 39      |
    | Jones    | Over 39      |
    +----------+--------------+
    
    Union ALL
    Please Note! Union query is like SELECT-statement has the DISTINCT configuration. If you want to allow double values, use UNION ALL instead of UNION.

    Example
    SELECT column_name(s) FROM table1
    UNION ALL
    SELECT column_name(s) FROM table2

    IF statement

    You can use also IF in SELECT statement. The syntax is IF(condition, Option1, Option2). So, if the condition is true, Option1 will be executed otherwise Option2.

    Example

    SELECT pname, IF(priorit<2, "Very Important", "Normal priorit") as Cathegory
    FROM project;
    

    SubQuery

    Subquery or Inner query or Nested query is a query inside a query. A subquery is usually added in the WHERE Clause of the sql statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value.

    Example All the person are fetched who were born in the same year as Morrison

    SELECT lastname
    FROM person
    WHERE birth_year=
    ( SELECT birth_year
      FROM person
      WHERE lastname='Morrison');
    
    Example
    The Query shows all, which salary is at least two times bigger than that of the one which has the smallest salary
    SELECT lastname, salary
    FROM person
    WHERE salary >
    ( SELECT min(salary)*2
      FROM person);
    
    If a subquery returns more than one records, there is a need for an IN subquery

    Example Fetch the person which have worked for at least 400 hour in some project
    SELECT lastname
    FROM person
    WHERE id_person IN
    ( SELECT id_person
      FROM hour
      WHERE work_hour>=400);
    
    ANY- and ALL-SubQueries

    If we use word ANY like this

    WHERE salary > ANY ("SubQuery") 
    
    In this case, the condition is fulfilled when the salary is greater than any of the return values of the subquery.

    and if we use word ALL, like this

    WHERE salary > ALL ("SubQuery")
    
    In this case, the condition is fulfilled when the salary is greater than all of the return values of the subquery.

    Example, we have this data

    +----------+--------+
    | city     | salary |
    +----------+--------+
    | HELSINKI |     39 |
    | HELSINKI |     36 |
    | TAMPERE  |     34 |
    | TURKU    |     44 |
    | TURKU    |     39 |
    | TURKU    |     44 |
    +----------+--------+
    
    Then with this query
    select city, salary from person where salary <
    	any(select salary from person where city='Turku');
    
    we will get this data (all rows where salary < 44)
    +----------+--------+
    | city     | salary |
    +----------+--------+
    | TURKU    |     39 |
    | HELSINKI |     39 |
    | HELSINKI |     36 |
    | TAMPERE  |     34 |
    +----------+--------+
    
    And with this query
    select city, salary from person where salary <
    	all(select salary from person where city='Turku');
    
    we will get this data (all rows where salary < 39)
    +----------+--------+
    | city     | salary |
    +----------+--------+
    | HELSINKI |     36 |
    | TAMPERE  |     34 |
    +----------+--------+
    

    Delete rows

    Delete records, which meets on a condition

    DELETE
    FROM project
    WHERE id_project=2145;
    
    Update Data
    Example
    Salary of all the person who lives is Turku is raised 20%

      UPDATE person
      SET salary=salary*1.2
      WHERE city='TURKU';
    
    Insert Data
    Table test has fields:
    • id SMALLINT
    • name VARCHAR(50)
    • height SMALLINT
    You can insert data to table test like this:
      INSERT INTO test
      VALUES(101,'Matt',180);
    
    or like this:
      INSERT INTO test
      VALUES(102,'Liisa',NULL);
    
    In the previous examples the information must be given in that order as the fields are in the table and to every field a value must be fed(at least NULL.)
    If you also give the field list about those values that you are going to insert, you can use the following syntax
      INSERT INTO test (name,id)
      VALUES('Liisa',103);
    
    Add data from another table

    You can also read data from one table and insert that data to a new table which does not already exist. The new table gets the same field definitions as in that table at which the data is picked up.

    Example
    Below code will read data from project-table and create a new table named helsinki with that data.

      CREATE TABLE helsinki
      SELECT *
      FROM project
      WHERE place='Helsinki';
    
    If you already have table named helsinki and you want to insert data from project, you can use below code
      INSERT INTO helsinki
      SELECT *
      FROM project
      WHERE place ='Helsinki';
    
    Stored Procedures

    Stored Procedures are SQL-scipts which will be stored in the database. Every RDBMS has they own syntax for procedures.

    Below examples are only for MySQL.

    Next example will create an procedure named searchPerson:
      DELIMITER //
      CREATE PROCEDURE searchPerson(IN sname CHAR(50))
      BEGIN
      SELECT * FROM person WHERE lastname=sname;
      END //
      DELIMITER ;
      
    Then we can call this procedure with this command:
    CALL searchPerson('Morrison');
    We can check what procedures we have in a certain database:
    SHOW PROCEDURE STATUS WHERE db='database' \G
    We can check the procedure syntax:
    SHOW CREATE PROCEDURE searhPerson \G
    We can delete the procedure:
    DROP PROCEDURE searhPerson;

    NOTE : In above example the query was so simple that it is not reasonable to save it as a procedure.

    Variables in Procedures

    In the parameter list you will create variables (like sname in previous example ), but you can also save the results from SQL to variables. You can create two kind of variables :

    • User-defined variables (name starts with symbol @)
    • Local variables (using the keyword DECLARE)
    The difference between local variables and user-defined variable is that local variable is reachable only inside the procedure. The user-defined variable is reachable until the user ends the session.

    Another difference between local variables and user-defined variable is that local variable is reinitialized with NULL value each time whenever stored procedure is called while user-defined variable is not reinitialized with NULL.

    Example about User-defined variable
      SELECT firstname INTO @chosenFname FROM person WHERE lastname=sname;
    
      SELECT * FROM person WHERE firstname = @chosenFname;
      
    Example about local variable
      DECLARE chosenFname VARCHAR(50);
      SELECT firstname INTO chosenFname FROM person WHERE lastname=sname;
    
      SELECT * FROM person WHERE firstname = chosenFname;
      
    IF THEN ELSE END

    Lets assume that you want to check, if some rows exists. So, you can do it like this

    DECLARE test INT DEFAULT 0;
    SET test = (SELECT COUNT(*) ...);
    
    Then you can use the test variable in IF ELSE like this
    IF test THEN
        ... 
    ELSE 
    END IF;
    

    Stored Procedures have several benefits:

    • Creating several applications will be easier (ex. Web-appliation, Mobile-application), because we can call the same procedures
    • User priviledges: we can give priviledges to call Procedures, without any other priviledges
    • Security: no risk for SQL-injection
    • Less network traffic: one Procedure can make several things

    Delimiter

    Delimiter is the "string" that tells to mysql-client that you want to execute your script. The default is ;. When you write procedures, you need to add ; inside your procedure code. So, before you start to write the procedure, you should change the delimiter to something else (typically // or $$), so that your client does not execute it immediatelly. And when your procedure is ready, you can change the delimiter back to default.

    Transaction

    Transaction means a subset of several operations to database. If one operation fails, the whole transaction will be cancelled.

    COMMIT makes the changes (caused by transaction) permanent. And Rollback will cancel them (if they are not committed yet).

    Example

    Create a table with below code

      CREATE TABLE bank_account(
      idaccount INT PRIMARY KEY,
      balance double,
      credit_limit double
      );
      
    And another table with below code
      CREATE TABLE action (
      idaction int PRIMARY KEY AUTO_INCREMENT,
      idaccount int,
      action varchar(45),
      action_time timestamp,
      amount double
      );
      
    Then we will create two accounts, which both have balance 500.
      INSERT INTO bank_account VALUES(1,500,1000);
      INSERT INTO bank_account VALUES(2,500,2000);
      
    Then we will create an Procedure which will move money from one account to another account
    DELIMITER //
    CREATE PROCEDURE debit_transfer(IN first_id INT, IN second_id INT, IN amount DOUBLE )
      BEGIN
      DECLARE test1,test2 INT DEFAULT 0;
      START TRANSACTION;
      UPDATE bank_account SET balance=balance-amount WHERE idaccount=first_id AND balance >= amount;
      SET test1=ROW_COUNT();
      UPDATE bank_account SET balance=balance+amount WHERE idaccount=second_id;
      SET test2=ROW_COUNT();
        IF (test1 > 0 AND test2 >0) THEN   
          COMMIT;    
          INSERT INTO action(idaccount,action,amount,action_time) VALUES(first_id,'withdrawal',amount,NOW());
          INSERT INTO action(idaccount,action,amount,action_time) VALUES(second_id,'deposit',amount,NOW());
        ELSE
          ROLLBACK;
      END IF;
      END //
    DELIMITER ;
    
      
    Explanation:
    • If first account does not exists or there is not enough money, variable test1 = 0
    • If second account does not exists, variable test2 = 0
    • If both test1 and test2 are bigger than 0, we will execute commit, otherwise we will execute rollback
    You can test the Procedure with below commands:
      CALL debit_transfer(1,2,100);
      SELECT * FROM bank_account;
    
      and
    
      CALL debit_transfer(1,3,100);
      SELECT * FROM bank_account;
    
      and
    
      CALL debit_transfer(3,2,2000);
      SELECT * FROM bank_account;
      
    Above transaction is suitable for bank_accounts where is not allowed that the balance is negative.
    If it is allowed that the balance is negative as far as is not more than the credit_limit, we can use below transaction:
    DELIMITER //
    CREATE PROCEDURE credit_transfer(IN first_id INT, IN second_id INT, IN amount DOUBLE )
    BEGIN
      DECLARE test1, test2 INT DEFAULT 0;
      START TRANSACTION;
      UPDATE bank_account SET balance=balance-amount WHERE idaccount=first_id AND balance + credit_limit >= amount;
      SET test1=ROW_COUNT();
      UPDATE bank_account SET balance=balance+amount WHERE idaccount=second_id;
      SET test2=ROW_COUNT();
        IF (test1 > 0 AND test2 >0) THEN
          COMMIT;
          INSERT INTO action(idaccount,action,amount,action_time) VALUES(first_id,'withdrawal',amount, NOW());
          INSERT INTO action(idaccount,action,amount,action_time) VALUES(second_id,'deposit',amount, NOW());
        ELSE
          ROLLBACK;
      END IF;
    END //
    DELIMITER ;
    
    Triggers

    Triggers are little modules stored on database. These modules are executed automatically, when certain SQL-commands are executed. We can define the triggers start when UPDATE-, INSERT-, or DELETE-command is executed.

    Example
    Let's create tables Customers and Logs
      CREATE TABLE Customers(
      idCustomers INT primary key auto_increment,
      customerName VARCHAR(20)
      );
    
      CREATE TABLE Logs(
      idLogs INT primary key auto_increment,
      customerName VARCHAR(20),
      idCustomers INT,
      updateType VARCHAR(50) DEFAULT NULL,
      updateTime TIMESTAMP
      );
      
    Then we will create an trigger, which will add the previous name of the Customer to the Customer-table, when we update the name. Also we will add the time and explanation "update".
      DELIMITER $$
      CREATE TRIGGER updateCustomer
          BEFORE UPDATE ON Customers
          FOR EACH ROW
      BEGIN
      INSERT INTO Logs
        SET updateType = 'update',
        idCustomers = OLD.idCustomers,
        customerName = OLD.customerName,
        updateTime = NOW();
      END$$
      DELIMITER ;
      
    Also we can create trigger, which will save the name of the Customer, to Logs-table, if we delete the Customer. Also the time and explanation "delete" will be saved.
      DELIMITER $$
      CREATE TRIGGER deleteCustomer
          BEFORE DELETE ON Customers
          FOR EACH ROW
      BEGIN
      INSERT INTO Logs
        SET updateType = 'delete',
        idCustomers = OLD.idCustomers,
        customerName = OLD.customerName,
        updateTime = NOW();
      END$$
      DELIMITER ;
      
    Events

    You can also schedule some actions in MySQL Server, using events. Here is one simple example which will update the value in database after 1 minute.

    Create a test database and a table with below code

    create database eventtest;
    
    use eventtest;
    
    create table atest(
    id_atest int primary key auto_increment,
    amount int);
    
    insert into atest(amount) values(5);
    
    And create the event with below code
    CREATE EVENT myevent
        ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
        DO
          UPDATE atest SET amount=amount + 1;
    
    If you check the data after in a minute, you should see that the value om amount has been updated.

    You can check what kind of event your database has with below command

    SHOW events;
    
    Note! above event will be deleted after it has been executed. If you want to make an event which will be executed every 1 minute, you can use below code
    DELIMITER //
    CREATE EVENT permanent_event
      ON SCHEDULE EVERY 1 MINUTE
      DO
        BEGIN
          UPDATE atest SET amount=amount + 1;
        END //
    DELIMITER ;
    
    If you now check the data once in a minute, you should see that the value om amount will be updated. You can remove the event with the command
    DROP EVENT permanent_event;
    

    SQL-injection

    If you are going to make an application (web-application, Java-application etc), you should take care that there is no risk for sql-injection.

    Example here is a dangerous php-code

    1. You have a web-form where you have a textinput named id
    2. You concatenate the value from your textbox to php-variable, like this:
      $sql = 'SELECT * FROM person WHERE person_id='.$_POST['id'] ;
    3. So, now the user can write to the textbox something like 'delete from user', and your app will execute it!

    There is very good tutorial in w3school what SQL-injection means:
    http://www.w3schools.com/sql/sql_injection.asp



    Toggle Menu