Slide MySql

Database course of
Prof. Ing. Rosario Sorbello
Introduction to MySql y q
Slides by Ing. Carmelo Riggio
Overview
Main M i component: t MySQL Community Server Other components and utilities (all downloadable from www.mysql.com) MySQL Workbench (GUI tool) MySQL Query Analyzer MySQL Enterprise MySQL Embedded MySQL Data Warehouse MySQL Cluster MySQL Connectors MySQL Control Center (http://sourceforge.net/projects/mysqlcc )
MySQL Community Server

MySQL Community Server is a freely downloadable version of the world's most popular open source database that is supported by an active community of open source developers and enthusiasts. www.mysql.it/downloads/mysql/
MySQL Workbench (GUI tool)

MySQL Workbench M SQL W kb h provides DBAs and developers an A 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
Installation
Download and install M S C MySql Community Server . S For Windows download a MSI Installer and launch. g y During installation it will be necessary to set a password for the root user For Linux Ubuntu end other Debian based distribution execute apt-get install mysql from shell with root apt get mysql privileges.
How to launch MySQL at command line
First steps
Enter the password set during installation
Firsts commands to creating the first Db

help (list of all MySQL commands) SHOW DATABASES CREATE DATABASE DROP DATABASE USE N.B. all commands except help must be followed by ";" help ;
SHOW DATABASE command

This command shows all databases in MySQL
CREATE DATABASE command
student database was created
USE command
After you create the database, you must select it with the USE command and display tables with "VIEW TABLES;"
execution time of the query the database is still empty py
Create the first table using the standard SQL language
DESCRIBE command shows the t bl t t th table structure
DROP TABLE command
Data types in MySQL: numeric

Integer d t t I t data type:
TINYINT-128 > 127 SMALLINT-32768 > 32767 MEDIUMINT-8388608 > 8388608 INT-2147483648 > 2147483647 BIGINT-9223372036854775808 > 9223372036854775807
Not-Integer d t t N t I t data type:

Float Double Decimal
Numeric modifiers
Automatically increases the value of a column by adding 1 to the maximum value.should be used in combination with NOT NULL It can be applied to all types integers NULL. integers.
Auto_increment : Unsigned:
values
a field marked with UNSIGNED not accept negative
ZEROFILL:
is used to insert a number preceded by a variable amount 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"
Data types in MySQL: String

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 LONGBLOB4.2 Mb
Data type CHAR and VARCHAR

CHAR and VARCHAR types are by far the most used. The difference between these two types is the fact that while VARCHAR is variable length, CHAR fixed length. This means that in a column CHAR (10) all the stored values will be 10 bytes long but made only 5 characters (something that absolutely does not happen with VARCHAR).
TEXT e BLOB (Binary Large OBject)

TEXT and BLOB fields MySQL are dedicated to contain large amounts of data. Up to 4.2 GB with LONGTEXT 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 not exist, being included directly in the database).
Others modifier
BINARY - Can be used with CHAR or VARCHAR DEFAULT - Can be used with all types of data (including digital 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 string) g) UNIQUE - Can be used with all types of data (numeric and string).
Data type Date Date

DATETIME: AAAA-MM-GG HH:MM:SS DATETIME AAAA MM GG HH MM SS DATE: AAAA-MM-GG TIMEHH:MM:SS TIMEHH MM SS YEAR:AAAA TIMESTAMP(14):AAAAMMGGHHMMSS TIMESTAMP(12):AAMMGGHHMMSS TIMESTAMP(10):AAMMGGHHMM TIMESTAMP(8):AAAAMMGG TIMESTAMP(4):AAMM TIMESTAMP(2):AA
Date functions
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)
Some example
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); Add 7 months to January 2009 : h J 2 SELECT PERIOD_ADD(200901,7); Sub S b 7 months to J h January 2009 : SELECT PERIOD_ADD(200901,-7); Find h diff Fi d the difference (in months) between Jan 2009 and Oct 2005: (i h )b J d O 2005 SELECT PERIOD_SUB(200901,200510);

Slide MySql

Uploaded by

Copyright:

Available Formats

Slide MySql

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Slide MySql

Uploaded by

Copyright:

Available Formats

Database course of

Prof. Ing. Rosario Sorbello

MySQL Community Server

MySQL Workbench (GUI tool)

How to launch MySQL at command line

Firsts commands to creating the first Db

SHOW DATABASE command

CREATE DATABASE command

student database was created

execution time of the query the database is still empty py

Create the first table using the standard SQL language

DESCRIBE command shows the t bl t t th table structure

DROP TABLE command

Data types in MySQL: numeric

Not-Integer d t t N t I t data type:

a field marked with UNSIGNED not accept negative

Data types in MySQL: String

Data type CHAR and VARCHAR

TEXT e BLOB (Binary Large OBject)

Data type Date Date

You might also like