BASIC-DDL-AND-DML-STATEMENT.pptx

BASIC DDL AND DML STATEMENTS IT 206 – ADVANCE DATABASE SYSTEM BASIC SQL Structured Query Language Considered one of the major reasons for the commercial success of relational databases Statements for data definitions, queries, and updates Both DDL and DML Core specification...

BASIC DDL AND DML STATEMENTS IT 206 – ADVANCE DATABASE SYSTEM BASIC SQL Structured Query Language Considered one of the major reasons for the commercial success of relational databases Statements for data definitions, queries, and updates Both DDL and DML Core specification plus specialized extensions. Terminology: Syntax notes: Some interfaces require each statement to end with a semicolon. SQL is not case-sensitive. INTRODUCTION TO SQL What is MY SQL?  MY SQL is a database system used for developing web- based software applications.  MY SQL used for both small and large applications.  MY SQL is a relational database management system (RDBMS).  MY SQL is fast, reliable, and flexible and easy to use.  MySQL was developed by Michael Widenius and David Axmark in 1994.  MySQL is presently developed, distributed, and supported by Oracle Corporation. MAIN FEATURES OF MYSQL  MySQL server design is multi-layered with independent modules.  MySQL is fully multithreaded by using kernel threads. It can handle multiple CPUs if they are available.  MySQL provides transactional and non-transactional storage engines.  MySQL has a high-speed thread-based memory allocation system.  MySQL supports in-memory heap table.  MySQL Handles large databases.  MySQL Server works in client/server or embedded systems.  MySQL Works on many different platforms. BASIC DATA TYPES Numeric data types Integer numbers: INT, INTEGER, SMALLINT, BIGINT, TINYINT, MEDIUMINT Floating-point (real) numbers: REAL, DOUBLE , FLOAT Fixed-point numbers: DECIMAL(M,[D]), DEC(M,[D]), NUMERIC(M,[D]), NUM(M,[D]) Character-string data types Fixed length: CHAR(n), CHARACTER(n) Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n), LONG VARCHAR NUMERIC DATA TYPES INTEGER NUMBERS: Table 1. Required Storage and Range for Integer Types Supported by MySQL NUMERIC DATA TYPES FLOATING-POINT NUMBERS: MySQL permits a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, (M,D) means that values can be stored with up to M digits in total, of which D digits may be after the decimal point. For example, a column defined as FLOAT(7,4) is displayed as -999.9999. Try to answer: if you insert 999.00009 into a FLOAT(7,4) column. the approximate result is 999.0001. Take Note: As of MySQL 8.0.17, the nonstandard FLOAT(M,D) and DOUBLE(M,D) syntax is deprecated, and you should expect support for it to be removed in a future version of MySQL. NUMERIC DATA TYPES FIXED-POINT NUMBERS (EXACT VALUE): The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data. In MySQL, NUMERIC is implemented as DECIMAL, so the following remarks about DECIMAL apply equally to NUMERIC. For example, a column SALARY defined as DECIMAL (5,2) is displayed as 999.99. Try to answer: if you insert 990 into a DECIMAL (7,4) column. the approximate result is 990.0000. In standard SQL, the syntax DECIMAL(M) is equivalent to DECIMAL(M,0). Similarly, the syntax DECIMAL is equivalent to DECIMAL(M,0), where the implementation is permitted to decide the value of M. MySQL supports both variant forms of DECIMAL syntax. The default value of M is 10. If the scale is 0, DECIMAL values contain no decimal point or fractional part. BASIC DATA TYPES Large object data types Characters: CLOB, CHAR LARGE OBJECT , CHARACTER LARGE OBJECT Bits: BLOB, BINARY LARGE OBJECT Boolean data type Values of TRUE or FALSE or NULL DATE data type Ten positions Components are YEAR, MONTH, and DAY in the form YYYY-MM-DD MORE DATA TYPES Additional data types TIMESTAMP data type Includes the DATE and TIME fields Plus, a minimum of six positions for decimal fractions of seconds Optional WITH TIME ZONE qualifier INTERVAL data type Specifies a relative value that can be used to increment or decrement an absolute value of a date, time, or timestamp CREATE DATABASE COMMAND The Create Database statement is used to create a new SQL database. Syntax: CREATE DATABASE [database_name] Example: CREATE DATABASE sampledb; CREATE DATABASE COMMAND  CREATE SCHEMA is a synonym for CREATE DATABASE. Syntax: CREATE SCHEMA [database_name] Example: CREATE SCHEMA sampledb; MORE ON CREATE DATABASE COMMAND CREATE DATABASE with IF NOT EXISTS command. Syntax: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name Example: CREATE DATABASE IF NOT EXISTS sampledb; Note: An error occurs if the database exists, and you did not specify IF NOT EXISTS. USE DATABASE COMMAND  It is used to switch from the default database master to preferred database. Syntax: USE [databasename] Example: USE sampledb; Data Definition Language IT 206 – ADVANCE DATABASE SYSTEM CREATE TABLE COMMAND The CREATE TABLE statement is used to create a new table in a database. Can optionally specify schema: SYNTAX: CREATE TABLE DB_NAME.TBL_NAME… or CREATE TABLE TBL_NAME... CREATE TABLE COMMAND SYNTAX: CREATE TABLE table_name (column1 datatype, column2 datatype, column3 datatype,....); Include information for each column (attribute) plus constraint Column name Column type (domain) Key, uniqueness, and null constraints Columns can be declared to be NOT NULL Columns can be declared to have a default value Assigned to column in any tuple for which a value is not specified CREATE TABLE COMMAND EXAMPLE: CREATE TABLE STUDENT( StudNo int not null primary key, LName varchar(15), FName varchar(15), Course varchar(5) , YrLevel int); SPECIFYING KEY CONSTRAINTS PRIMARY KEY clause: Specifies one or more attributes that make up the primary key of a relation Primary key attributes must be declared NOT NULL. DROP TABLE COMMAND The DROP TABLE statement is used to drop an existing table in a database. SYNTAX: DROP TABLE table_name[,tbl_name]…; or DROP TABLE [IF EXISTS] table_name; EXAMPLE: DROP TABLE table1; DROP DATABASE COMMAND The DROP DATABASE statement is used to drop an existing SQL database and all of the tables. SYNTAX: DROP {DATABASE|SCHEMA} db_name; or DROP DATABASE [IF EXISTS] db_name; EXAMPLE: DROP DATABASE dbtodelete; ALTER Statement – ADD Keyword To add a column in a table, use the following syntax: ALTER TABLE table_name ADD column_name datatype; EXAMPLE: ALTER TABLE STUDENT ADD TFee int; ALTER Statement – ADD-AFTER Keyword To add specific place of column in a table, use the following syntax: ALTER TABLE table_name ADD column_name datatype AFTER column_name; EXAMPLE: ALTER TABLE STUDENT ADD MName VARCHAR(15) AFTER FName; ALTER Statement – DROP Keyword To drop a column in a table, use the following syntax: ALTER TABLE table_name DROP column_name; EXAMPLE: ALTER TABLE STUDENT DROP MName; ALTER Statement – CHANGE Keyword To alter a column to change BOTH ITS NAME AND DEFINITION, use CHANGE, specifying the old and new names and the new definition. ALTER TABLE table_name CHANGE old_col_name new_col_name [column definitions(including data_type key constraint]; EXAMPLE: ALTER TABLE STUDENT CHANGE LName Sname VARCHAR(15); ALTER Statement – CHANGE Keyword To change a column definition but NOT ITS NAME, the syntax requires two column names, so you must SPECIFY THE SAME NAME TWICE to leave the name unchanged. ALTER TABLE table_name CHANGE old_col_name oldcol_name new_col_definition; EXAMPLE: ALTER TABLE STUDENT CHANGE FName FName VARCHAR(20) NOT NULL; ALTER Statement – MODIFY Keyword MODIFY is more convenient to change the definition without changing the name because it requires the column name only once: ALTER TABLE table_name MODIFY col_name new_col_definition; EXAMPLE: ALTER TABLE STUDENT MODIFY FName VARCHAR(15); ALTER Statement – CHANGE Keyword To change a column name but NOT ITS DEFINITION, the syntax requires a column definition, so to leave the definition unchanged. ALTER TABLE table_name CHANGE old_col_name newcol_name old_col_definition; EXAMPLE: ALTER TABLE STUDENT CHANGE YrLevel YearLevel int; ALTER Statement – RENAME COLUMN-TO Keyword RENAME COLUMN is more convenient to change the name without changing the definition because it requires only the old and new names: ALTER TABLE table_name RENAME COLUMN old_col_name TO newcol_name; EXAMPLE: ALTER TABLE STUDENT RENAME COLUMN TFee TO TuitionFee; Data Manipulation Language IT 206 – ADVANCE DATABASE SYSTEM INSERT INTO Statement The INSERT INTO statement is used to insert new records in a table. Can optionally specify schema: SYNTAX: INSERT INTO DB_NAME.TBL_NAME… or INSERT INTO TBL_NAME... INSERT INTO Statement COMPLETE SYNTAX: INSERT INTO table_name VALUES (ColumnValue1, ColumnValue2, columnValue3,...); Or (insertion with specific ColumnNames) INSERT INTO table_name(ColumnName1, ColumnName2, ColumnName3,… ColumnNameN) VALUES (ColumnValue1, ColumnValue2, columnValue3,... ColumnValueN); Or (insertion with specific DatabaseName) INSERT INTO db_name.table_name VALUES (ColumnValue1, ColumnValue2, columnValue3,...); INSERT INTO Statement EXAMPLE: INSERT INTO STUDENT VALUES(20191254, ‘Castro’, ‘Macy’, ‘BSIT’, 3); INSERT INTO sampledb.student(StudNo,Sname,FName,Course,YearL evel) VALUES(110351,"Ragos","Ryan","BSIT",1); INSERT INTO Statement EXAMPLE: INSERT INTO STUDENT VALUES(20191254, ‘Castro’, ‘Macy’, ‘BSIT’, 3); INSERT INTO sampledb.student(StudNo,Sname,FName,Course,YearLevel) VALUES(110351,"Ragos","Ryan","BSIT",1); INSERT INTO student(StudNo,Sname,FName,Course,YearLevel) VALUES (000003,"Test","Sample","BSIT",1) , (000004,"Test2","Sample2","BSIT",1); UPDATE-SET Statement The UPDATE statement is used to modify the existing records in a table. SYNTAX: UPDATE table_name SET column1 = value1, column2 = value2,...WHERE condition; UPDATE Multiple Records It is the WHERE clause that determines how many records will be updated. UPDATE-SET Statement The UPDATE statement is used to modify the existing records in a table. EXAMPLE: UPDATE STUDENT SET SName = "Reyes", FName = "AJ" WHERE StudNo = ‘1’; SELECT-FROM Statement The SELECT statement is used to select data from a database. SYNTAX: SELECT column1, column2,... FROM table_name; (for selecting specific fields) EXAMPLE: SELECT StudNo, Sname FROM STUDENT; SELECT-FROM Statement SYNTAX: SELECT * FROM table_name; (for selecting all the fields available) EXAMPLE: SELECT *FROM STUDENT; DELETE-FROM Statement The DELETE statement is used to delete existing records in a table. SYNTAX: DELETE FROM table_name WHERE condition; (for deleting specific fields/records/row) EXAMPLE: DELETE FROM STUDENT WHERE StudNo = 4; DELETE-FROM Statement It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact. SYNTAX: DELETE FROM table_name; (for deleting all records) EXAMPLE: DELETE FROM dbtodelete;