User Tools

Site Tools


programming:mysql:mysql

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”
    • R: VARCHAR needs a “Length/Values”
      `firstname` VARCHAR( 40 ) NOT NULL

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
programming/mysql/mysql.txt · Last modified: 2016/07/25 14:23 by mferreira