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

CREATE TABLE employees (

User, Database, Table, Index

DROP TABLE employees;

id INTEGER PRIMARY KEY, first_name CHAR(50) NULL,

);

last_name CHAR(75) NOT NULL,

ALTER TABLE sink ADD bubbles INTEGER;

dateofbirth DATE NULL

ALTER TABLE sink DROP COLUMN bubbles;

Create Table Syntax CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(definition_create,...)] [options_table] [sentence_select] CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(] LIKE old_tbl_name [)]; Definition_create columns_definition | [CONSTRAINT [symbol]] PRIMARY KEY (index_column_name,...) | KEY [index_name] (nombre_col_index,...) | INDEX [index_name] (index_col_name,...) | [CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] [index_type] (nombre_col_index,...) | [FULLTEXT|SPATIAL] [INDEX] [nombre_index] (nombre_col_index,...) | [CONSTRAINT [símbolo]] FOREIGN KEY [nombre_index] (nombre_col_index,...) [definición_referencia] | CHECK (expr) columns_definition column_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'string'] [reference_definition] reference_definition REFERENCES table_name [(index_col_name,...)] [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETE reference_options] [ON UPDATE reference_options] reference_options RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

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 Control Language (DCL) 

Syntax in MySQL:  start

transaction | begin [work]  commit [work]  rollback [work]  set autocommit={0 | 1} begin command1 command2 :

commandn commit | rollback

Data Manipulation Language (DML) 

SQL (Structured Query Language ) 





Insert 

INSERT INTO table_name (column1, [column2,...]) VALUES (value1, [value2,...])



INSERT INTO table_name [(column1, [column2,...]) ] SELECT column1, [column2,…] FROM table_name WHERE condition

Delete 



SELECT column1, [column2,…] FROM table_name WHERE condition

DELETE FROM table_name [WHERE condition]

Update 

UPDATE table_name SET column1 = value1 [, column2 = value2,...] [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 1: Creating a database and a user… user@server:~> mysql –u root –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> create database prueba; Mysql> use prueba; mysql> create user ‘user1’@’localhost’ identified by ‘user1’; mysql> grant all on prueba.* to user1; mysql> show databases; mysql> exit;

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 userk 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 3b: Transaction control… Logging as userk by using two different terminals  Initiate a transaction  Delete all data of table A using terminal 1  Check table content in terminal 1 and 2  Rollback the transaction in terminal 1  Check table content in terminal 1 

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 



MySQL Website 



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

http://www.mysql.com/

MySQL certification 

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