Slide MySql

7/31/2019 Slide MySql

1/22

Database course ofProf. Ing. Rosario Sorbello

Introduction to M S l

Slides by Ing. Carmelo Riggio


2/22

a n componen :

MySQL Community Server

Other components and utilities (all downloadable from www.mysql.com)

MySQL Workbench (GUI tool)

MySQL Query Analyzer MySQL Enterprise

MySQL Data Warehouse

MySQL Cluster MySQL Connectors

MySQL Control Center (http://sourceforge.net/projects/mysqlcc )


3/22

MySQL Community Server is a freely downloadable

version of the world's most popular open sourcedatabase that is supported by an active community

of open source developers and enthusiasts.

www.m s l.it downloads m s l


4/22

My L Wor enc provides DBAs and developers an

integrated tools environment for: Database Design & Modeling

SQL Development (replacing MySQL Query

Browser) Database Administration (replacing MySQL

Administrator)

dev.mysql.com/downloads/workbench/Note: DBA is DataBase Administrator


5/22

Download and install MySql Community Server .

For Windows download a MSI Installer and launch.

Durin installation it will be necessar to set a

password for the root user

For Linux Ubuntu end other Debian based distribution

- privileges.


6/22


7/22

Enter the password set during installation


8/22

help (list of all MySQL commands)

SHOW DATABASES

CREATE DATABASE

USE

" "


9/22

This command shows all databases in MySQL


10/22

student database

was created


11/22

After you create the database, you must select it with the USE

command and display tables with "VIEW TABLES;"

execution time

the database is still em t


12/22

Create the first table using the

standard SQL language

DESCRIBE command shows

e a e s ruc ure


13/22


14/22

n eger a a ype:

TINYINT-128 > 127

-

MEDIUMINT-8388608 > 8388608

INT-2147483648 > 2147483647

BIGINT-9223372036854775808 > 9223372036854775807

o - n eger a a ype: Float

Decimal


15/22

Auto_increment : Automatically increases the value of a

column by adding 1 to the maximum value.should be used in. .

Unsigned: a field marked with UNSIGNED not accept negative

ZEROFILL: is used to insert a number preceded by a variableamount of zeros on the basis of the scale of the column. If for

example you declare an INT field (10) ZEROFILL and you want to

store the value "1234", it will be saved as "0000001234"


16/22

CHAR(n)255 byte

VARCHAR(n)255 byte TINYTEXT255 byte

TINYBLOB255 byte

TEXT65535 byte BLOB65535 byte

MEDIUMTEXT1.6 Mb

MEDIUMBLOB1.6 Mb LONGTEXT4.2 Mb


17/22

CHAR and VARCHAR types are by far the most

used. The difference between these two types isthe fact that while VARCHAR is variable length,

CHAR fixed length.

This means that in a column CHAR (10) all the storedvalues will be 10 bytes long but made only 5

characters (something that absolutely does not

happen with VARCHAR).


18/22

TEXT and BLOB fields MySQL are dedicated to

contain large amounts of data. Up to 4.2 GB withLONGTEXT and LONGBLOB.

The second in particular, the field of type BLOB lets

you save entire file in binary format.Note:useful for hiding files behind your username and

password, so without being able to trace the

physical location of the file (which in fact does notexist, being included directly in the database).


19/22

BINARY - Can be used with CHAR or VARCHAR

DEFAULT - Can be used with all types of data (includingdigital ones) except TEXT and BLOB.Specifies the

default value of the field.

NULL / NOT NULL - Can be used with all types of data(numeric and string)

PRIMARY KEY - Can be used with all types of data

numeric and strinUNIQUE - Can be used with all types of data (numeric


20/22

: - - : :

DATE: AAAA-MM-GG : :

YEAR:AAAA

:

TIMESTAMP(12):AAMMGGHHMMSS

TIMESTAMP(8):AAAAMMGG

TIMESTAMP(2):AA


21/22

NOW():AAAA-MM-GG HH:MM:SS

CURDATE():AAAAA-MM-GG CURTIME()HH:MM:SS

DATE_ADD()DATE_ADD(date, INTERVAL expression type)

DATE_SUB()DATE_SUBB(date, INTERVAL expression type) PERIOD_ADD()PERIOD_ADD(Period, Months)

(the period is expressed as AAAAMM or AAMM)

PERIOD_SUBB()PERIOD_SUB(Period_1, Period_2)(the period is expressed as AAAAMM oppure AAMM)


22/22

Sum 30 days to the current date:

SELECT DATE_ADD(CURDATE(),INTERVAL 30 DAYS);Sub 3 months by a specific date:

SELECT DATE_SUB('2009-01-13',INTERVAL 3 MONTHS);

A 7 mont s to January 009 :

SELECT PERIOD_ADD(200901,7);

u mont s to anuary :

SELECT PERIOD_ADD(200901,-7);

n t e erence n mont s etween an an ct :

SELECT PERIOD_SUB(200901,200510);

Slide MySql

Documents

Transcript of Slide MySql