# mysql -u root -p
Enter password: 

mysql> create database myDatabase;
Query OK, 1 row affected (0.01 sec)

mysql> grant usage on *.* to myUsername@localhost identified by 'myPassword';
Query OK, 0 rows affected (0.10 sec)

mysql> grant all privileges on myDatabase.* to myUsername@localhost;
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye

# mysql -u myUsername -p
Enter password: 

mysql> use eatimeclock;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> show grants;

+———————————————————————————————————————+

+———————————————————————————————————————+

+———————————————————————————————————————+ 2 rows in set (0.01 sec)

mysql> exit
Bye

The first column should be:

• Field: ID_
• Type: INT or a variation of it!
• Null: NOT NULL
• Extra: AUTO_INCREMENT
• a PRIMARY KEY

example:

 CREATE TABLE `patient` (
`id_patient` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
...//Others instructions
) ENGINE = MYISAM CHARACTER SET latin1 COLLATE latin1_swedish_ci

• ENUM

  `gender` ENUM( 'Male', 'Female', 'Unknown' ) NOT NULL

• INT/MEDIUMINT/TINYINT

  `npa` MEDIUMINT UNSIGNED NOT NULL COMMENT 'Postal Code',

• TIMESTAMP

  `lastmodification` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL

1. • Q: MySQL says: “This is not a number”
   • R: VARCHAR needs a “Length/Values”

     `firstname` VARCHAR( 40 ) NOT NULL

mysql> SET time_zone = 'Europe/Zurich';

If set time_zone fails with this error:

ERROR 1298 (HY000): Unknown or incorrect time zone: 'Europe/Zurich'

you need to load the time zone info into mysql with a command like this:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

For more info see http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

mysql> SET time_zone = 'Europe/Zurich';

mysql> SELECT @@session.time_zone;
 
-> Europe/Zurich

Based on MySQL v5.7.

1. The lowest storage size for a row is a byte. The following declarations are not stored in one single bit.

   tinyint(1)
   bit(1)
   bit -- when the number is missing, mysql assuming bit(1)
   bool -- is just an alias for tinyint(1)
   boolean -- idem

   It's exactly the same with a ENUM. If you use a enum with 4 diffents values, don't think mysql will use only 2 bits. An ENUM use 1 byte (1 to 255 values), or 2 bytes (256 to 65535 values, but the practical limit is less than 3000).

2. The optional number between parenthesis after an integer type is called display width.

   tinyint(2)
   int(4)
   bigint(20)

   The display width does not constrain the range of values that can be stored in the column.
   To prove it:

   +-------------+--------------------------+------+-----+---------+----------------+
   | Field       | Type                     | Null | Key | Default | Extra          |
   +-------------+--------------------------+------+-----+---------+----------------+
   | id          | smallint    unsigned     | NO   | PRI | NULL    | auto_increment |
   | value_t_1   | tinyint(1) unsigned      | YES  |     | NULL    |                |
   +-------------+--------------------------+------+-----+---------+----------------+

   insert into test_tiny set value_t_1=123;

   +----+-----------+
   | id | value_t_1 |
   +----+-----------+
   |  1 |       123 |
   +----+-----------+

   But, if you have a

   tinyint (4) unsigned zerofill

   And you put a 3 in the row, you get:

   +----+-----------+
   | id | value_t_1 |
   +----+-----------+
   |  1 |      0003 |
   +----+-----------+

3. There is no rollover. If you insert 256 in a 8 bits unsigned value, Mysql donesn't store 1. If we try this (in the same table):

   mysql> insert into test_tiny set value_t_1=256;

   MySQL give you an error.

   ERROR 1264 (22003): Out of range value for column 'value_t_1' at row 1

If you want more informations, you can read Integer Types, Numeric type attributes and Data type storage.

• MySQL documentation
• Makes your data as small as possible
• Links on R937.com
• Managing Hierarchical Data in MySQL
• MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN
• Mysql timezone