This is an old revision of the document!
Table of Contents
Some Words about MySQL
# 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;
+———————————————————————————————————————+
Grants for myDatabase@localhost |
+———————————————————————————————————————+
GRANT USAGE ON *.* TO 'myUsername'@'localhost' IDENTIFIED BY PASSWORD '*XXXXXXX' | |
GRANT ALL PRIVILEGES ON `myDatabase`.* TO 'myUsername'@'localhost' WITH GRANT OPTION |
+———————————————————————————————————————+ 2 rows in set (0.01 sec)
mysql> exit Bye
Creating Tables
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
Types
Q&A
- Q: MySQL says: “This is not a number”
Mysql timezone
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
Reflexions and tests about storage size
Based on MySQL v5.7.
- 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).
- 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 | +----+-----------+
If you want more informations, you can read Integer Types, Numeric type attributes and Data type storage.