Mysql

1
Mysql

• Basi di Dati
• Università di Enna A.A. 2006/07

2
TIPI DI DATI

• Many column types
• signed/unsigned integers 1, 2, 3, 4, and 8 bytes
  long
• FLOAT, DOUBLE
• CHAR, VARCHAR
• TEXT
• BLOB
• DATE, TIME, DATETIME, TIMESTAMP, YEAR
• SET, ENUM
• Fixed-length and variable-length records

3
TIPI DI DATI

• TINYINT (M) UNSIGNED ZEROFILL
• A very small integer.
• The signed range is -128 to 127.
• The unsigned range is 0 to 255.
• M size (bit)
• BOOL, BOOLEAN
• These types are synonyms for TINYINT(1).

4
TIPI DI DATI

• SMALLINT (M) UNSIGNED ZEROFILL
• A small integer. The signed range is -32768 to
  32767.
• The unsigned range is 0 to 65535.
• MEDIUMINT(M) UNSIGNED ZEROFILL
• A medium-sized integer.
• The signed range is -8388608 to 8388607.
• The unsigned range is 0 to 16777215.

5
TIPI DI DATI

• INT(M) UNSIGNED ZEROFILL
• A normal-size integer.
• The signed range is -2147483648 to 2147483647.
• The unsigned range is 0 to 4294967295.
• INTEGER is a synonym for INT
• BIGINT(M) UNSIGNED ZEROFILL
• A large integer. The signed range is
  -9223372036854775808 to 9223372036854775807.
• The unsigned range is 0 to 18446744073709551615.

6
TIPI DI DATI

• FLOAT(M,D) UNSIGNED ZEROFILL
• A small (single-precision) floating-point number.
  
• Allowable values are -3.402823466E38 to
  -1.175494351E-38, 0, and 1.175494351E-38 to
  3.402823466E38.
• The actual range might be slightly smaller
  depending on your hardware or operating system.
• M is the total number of decimal digits and D is
  the number of digits following the decimal point.
  
• UNSIGNED, if specified, disallows negative
  values.

7
TIPI DI DATI

• DOUBLE(M,D) UNSIGNED ZEROFILL
• A normal-size (double-precision) floating-point
  number.
• M is the total number of decimal digits and D is
  the number of digits following the decimal point.
• UNSIGNED, if specified, disallows negative
  values.

8
TIPI DI DATI

• DECIMAL(M,D) UNSIGNED ZEROFILL
• A packed exact fixed-point number.
• M is the total number of decimal digits (the
  precision) and D is the number of digits after
  the decimal point (the scale). If D
  is 0, values have no decimal point or fractional
  part.
• UNSIGNED, if specified, disallows negative
  values.

9
TIPI DI DATI

• DATE
• A date.
• The supported range is '1000-01-01' to
  '9999-12-31'.
• MySQL displays DATE values in 'YYYY-MM-DD'
  format.

10
TIPI DI DATI

• DATETIME
• A date and time combination.
• The supported range is '1000-01-01 000000' to
  '9999-12-31 235959'.
• MySQL displays DATETIME values in 'YYYY-MM-DD
  HHMMSS' format.

11
TIPI DI DATI

• TIME
• A time.
• The range is '-8385959' to '8385959'.
• MySQL displays TIME values in 'HHMMSS' format.

12
TIPI DI DATI

• YEAR(24)
• A year in two-digit or four-digit format.
• The default is four-digit format. In four-digit
  format, the allowable values are 1901 to 2155,
  and 0000.
• In two-digit format, the allowable values are 70
  to 69, representing years from 1970 to 2069.

13
TIPI DI DATI

• CHAR(M) BINARY ASCII UNICODE
• A fixed-length string that is always right-padded
  with spaces to the specified length when stored.
• M represents the column length.
• The range of M is 0 to 255 characters.

14
TIPI DI DATI

• CHAR(M) BINARY ASCII UNICODE
• A fixed-length string that is always right-padded
  with spaces to the specified length when stored.
• M represents the column length.
• The range of M is 0 to 255 characters.
• CHAR
• This type is a synonym for CHAR(1).

15
TIPI DI DATI

• VARCHAR(M) BINARY
• A variable-length string.
• M represents the maximum column length.
• In MySQL 5.0, the range of M is 0 to 255 before
  MySQL 5.0.3, and 0 to 65,535 in MySQL 5.0.3 and
  later.

16
TIPI DI DATI

• BINARY(M)
• The BINARY type is similar to the CHAR type, but
  stores binary byte strings rather than non-binary
  character strings.
• VARBINARY(M)
• The VARBINARY type is similar to the VARCHAR
  type, but stores binary byte strings rather than
  non-binary character strings

17
TIPI DI DATI

• TINYBLOB
• A BLOB column with a maximum length of 255 (28
  1) bytes.
• TINYTEXT
• A TEXT column with a maximum length of 255 (28
  1) characters.
• BLOB(M)
• A BLOB column with a maximum length of 65,535
  (216 1) bytes.
• TEXT(M)
• A TEXT column with a maximum length of 65,535
  (216 1) characters.

18
TIPI DI DATI

• MEDIUMBLOB
• A BLOB column with a maximum length of 16,777,215
  (224 1) bytes.
• MEDIUMTEXT
• A TEXT column with a maximum length of 16,777,215
  (224 1) characters.
• LONGBLOB
• A BLOB column with a maximum length of
  4,294,967,295 or 4GB (232 1) bytes.
• LONGTEXT
• A TEXT column with a maximum length of
  4,294,967,295 or 4GB (232 1) characters.

19
TIPI DI DATI

• ENUM('value1','value2',...)
• An enumeration. A string object that can have
  only one value, chosen from the list of values
  'value1', 'value2', ...
• An ENUM column can have a maximum of 65,535
  distinct values. ENUM values are represented
  internally as integers.
• SET('value1','value2',...)
• A set. A string object that can have zero or more
  values, each of which must be chosen from the
  list of values 'value1', 'value2', ...
• A SET column can have a maximum of 64 members.
  SET values are represented internally as
  integers.

20
MATHEMATICAL FUNCTIONS

• ABS(X)
• ACOS(X)
• ASIN(X)
• ATAN(X)
• ATAN2(Y,X)
• CEIL(X)
• Returns the smallest integer value not less than
  X.

21
MATHEMATICAL FUNCTIONS

• COT(X)
• CRC32(expr)
• Computes a cyclic redundancy check value and
  returns a 32-bit unsigned value.
• The result is NULL if the argument is NULL.
• The argument is expected to be a string and (if
  possible) is treated as one if it is not.
• RAND() - Returns a random floating-point value v
  between 0 and 1 inclusive (that is, in the range
  0 lt v lt 1.0).
• SIGN(X) - Returns the sign of the argument as -1,
  0, or 1, depending on whether X is negative,
  zero, or positive.

22
MYSQLADMIN (crea database)

• shellgt mysqladmin create prova_enna u root p

23
MYSQLADMIN (drop database)

• shellgt mysqladmin drop prova_enna u root p

24
MYSQL - client

• shellgt mysql u root p lt esempio.sql

25
MYSQL - client

• shellgt mysql u root p lt esempio.sql

26
Creazione di un nuovo utente

• mysqlgt create user corso_db
• mysqlgt quit
• shellgt mysql u corso_db
• mysqlgt set passwordpassword(new_pass)
• E possibile rinominare un utente nel modo
  seguente
• RENAME USER old_user TO new_user

27
Cancellare un utente

• shellgt mysql u root p
• password
• mysqlgt drop user corso_db
• mysqlgt quit

28
GRANT (Esempi)

• Diamo tutti i privilegi allutente corso_db sul
  database azienda
• mysql gt grant ALL on azienda to corso_db
• Revoca di ogni privilegio allutente corso_db
  sul database azienda
• mysqlgt revoke all on azienda from corso_db
• Diamo il permesso di select allutene
  corso_db sulla tabella
• catalogo del database azienda
• mysql gt grant SELECT on table azienda.catalogo to
  corso_db

29
ALTER

• ALTER TABLE t1 RENAME t2
• To change column a from INTEGER to TINYINT NOT
  NULL (leaving the name the same), and to change
  column b from CHAR(10) to CHAR(20) as well as
  renaming it from b to c
• ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE
  b c CHAR(20)

30
ALTER (2)

• To add a new TIMESTAMP column named d
• ALTER TABLE t2 ADD d TIMESTAMP
• To remove column c
• ALTER TABLE t2 DROP COLUMN c
• To add a new AUTO_INCREMENT integer column named
  c
• ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL
  AUTO_INCREMENT, ADD PRIMARY KEY (c)

31
BACKUP DEL DATABASE

• shellgt mysqldump azienda gt azienda.sql u root
  p

32
BACKUP DEL DATABASE
33
BACKUP DEL DATABASE

• shellgt mysql azienda lt azienda.sql u root p