rdbms-intro dml, ddl, dcl, Exercises of Relational Database Management Systems (RDBMS)

Download rdbms-intro dml, ddl, dcl and more Exercises Relational Database Management Systems (RDBMS) in PDF only on Docsity!

A Very Fast Introduction

to DL, DML & DCL

Faculty of Computer Science, BUAP

David Pinto, PhD

March, 2009

Data Definition Language (DDL)

• CREATE

• DROP

• ALTER

User, Database, Table, Index CREATE TABLE employees ( id INTEGER PRIMARY KEY, first_name CHAR(50) NULL, last_name CHAR(75) NOT NULL, dateofbirth DATE NULL ); DROP TABLE employees; ALTER TABLE sink ADD bubbles INTEGER; ALTER TABLE sink DROP COLUMN bubbles;

MySQL column types

TINYINT[( length )] [UNSIGNED] [ZEROFILL] | SMALLINT[( length )] [UNSIGNED] [ZEROFILL] | MEDIUMINT[( length )] [UNSIGNED] [ZEROFILL] | INT[( length )] [UNSIGNED] [ZEROFILL] | INTEGER[( length )] [UNSIGNED] [ZEROFILL] | BIGINT[( length )] [UNSIGNED] [ZEROFILL] | REAL[( length,decimals )] [UNSIGNED] [ZEROFILL] | DOUBLE[( length,decimals )] [UNSIGNED] [ZEROFILL] | FLOAT[( length,decimals )] [UNSIGNED] [ZEROFILL] | DECIMAL( length,decimals ) [UNSIGNED] [ZEROFILL] | NUMERIC( length,decimals ) [UNSIGNED] [ZEROFILL] | DATE | TIME | TIMESTAMP | DATETIME | CHAR( length ) [BINARY | ASCII | UNICODE] | VARCHAR( length ) [BINARY] | TINYBLOB | BLOB | MEDIUMBLOB | LONGBLOB | TINYTEXT | TEXT | MEDIUMTEXT | LONGTEXT | ENUM(value1,value2,value3,...) SET(value1,value2,value3,...) | spatial_type

DDL syntax in MySQL

create table parent ( id varchar(30), PRIMARY KEY (id) ) engine=innodb; create table child ( child_desc int(3), parentid varchar(30) NOT NULL REFERENCES parent(id) ) engine=innodb; create table child ( child_desc int(3), parentid varchar(30) NOT NULL default '', foreign key (parentid) references parent(id) on update cascade ) engine=innodb; Secure transaction tables with locking service at level of rows and foreign keys.

Data Manipulation Language (DML)

 SQL (Structured Query Language )

 (^) SELECT column 1 , [ column 2 ,…] FROM^ table_name^ WHERE^ condition

 Insert

 (^) INSERT INTO table_name ( column 1 , [ column 2 ,...]) VALUES ( value 1 , [ value 2 ,...])  (^) INSERT INTO table_name [ ( column 1 , [ column 2 ,...]) ] SELECT column 1 , [ column 2 ,…] FROM table_name WHERE condition

 Delete

 (^) DELETE FROM table_name [WHERE condition ]

 Update

 (^) UPDATE table_name SET column 1 =^ value 1 [,^ column 2 =^ value 2 ,...] [WHERE '' columnN = valueN ]

Select syntax in MySQL

SELECT [ALL | DISTINCT | DISTINCTROW] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr,... [INTO OUTFILE 'file_name' export_options] | INTO DUMPFILE 'file_name'] [FROM table_references [WHERE where_definition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_definition] [ORDER BY {col_name | expr | position} [ASC | DESC] ,...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [FOR UPDATE | LOCK IN SHARE MODE] ]

Exercise 2: Logging into MySQL and creating tables… user@server:~> mysql –u user1 –p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 104 Server version: 5.0.51a SUSE MySQL RPM Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show databases; mysql> use prueba; mysql> show tables; mysql> create table parent (id varchar(30), PRIMARY KEY (id) ) engine=innodb; mysql> show tables; mysql> desc parent; mysql> create table child (child_desc int(3), parentid varchar(30) NOT NULL default '', foreign key (parentid) references parent(id) on update cascade ) engine=innodb; mysql> show tables; mysql> desc child;

Exercise 3a: Transaction control…  (^) Logging as user

k

by using two different terminals  (^) Initiate a transaction  Introduce data in one table using terminal 1  (^) Check table content in terminal 1 and 2  Commit the transaction in terminal 1  (^) Check table content in terminal 2

Exercise 4. Create your own data definition in MySQL  Use the table definitions of your course project.  Populate tables with sample data

Links



DDL, DML and DCL syntax for MySQL

 http://mysql.conclase.net/curso/index.php?tab=Sentencias



MySQL Website

 http://www.mysql.com/



MySQL certification

 http://www.mysql.com/training/