comp

• Structured Query Language (SQL) – flexible language that you can use to communicate with a relational database management system such as MariaDB

  • Simply tell the database management system what you want
  • industry-standard language specifically designed to enable people to create databases, add new data to database, maintain the data, and retrieve selected parts of the data
  • ends in (;)
• SELECT query – used to retrieve information from the database
• Query – an inquiry into the database using the SELECT statement in order to extract data from the database
• SQL COMMANDS:

  Data Definition Language (DDL)

  Data Manipulation Language (DML)

  Data Control Language (DCL)
• DATA DEFINITION LANGUAGE (DDL) –  includes SQL commands used to create a database, modify its structure and destroy it

  • CREATE DATABASE, CREATE TABLE, DROP DATABASE, DROP TABLE
• DROP – destroy either a table or database

  “destroy a table” = delete not just the records but also the structure of the given table

  • “destroy a database” = delete not only the tables of the given database but also the database itself
• DATA MANIPULATION LANGUAGE (DML) –  includes SQL commands used to manage the contents of the database

  • Commands that are used to add new records, edit existing records, delete existing records, or display records
  • Used to manipulate the contents of the database
  • INSERT, UPDATE, DELETE, SELECT
• INSERT command – adding new record in our profile table
• UPDATE command – modify an existing record
•  

  Update clause - specify the table to be updated
• Set clause – new values to be assigned to certain fields
• Where clause – specify the conditions that the records must satisfy in order to be included in the UPDATE process
• DELETE command – remove an existing record

  • if you really want to delete all records, don’t specify a condition for example, to delete all records from the profile table, the SQL command is
• SELECT command – display records from a table that satisfies a given criteria
• LIKE operator – used in a WHERE clause to search for a specific pattern in a field

  Ex: email field ending in “@yahoo.com”
• % SQL wildcard – substitute for one or more characters when searching for data in a database
• DATA CONTROL LANGUAGE (DCL) – includes SQL commands that are used to protect your database from becoming corrupt

  • Restricting access to the database only to authorize users
  • Commands that grant and revoke privileges are examples of DCL
•  

  GRANT command – permit access to the database
• REVOKE command –   remove access
• Java DataBase Connectivity (JDBC) – java software that you can use to connect to a database

  • A database interface that allows java programs to access a database
• JDBC Driver – software that talks to java on one end and then to a specific type of database on the other end

  • Freely available and downloadable over the internet

  Ex: MariaDB Connector/J