MySQL: Difference between revisions

From vwiki
Jump to navigation Jump to search
m (Added "Basic Commands")
m (→‎Data Types: Added NULL)
Line 28: Line 28:


== Data Types ==
== Data Types ==
=== NULL ===
NULL means "no data", it doesn't mean zero.  Therefore 0 <> NULL in an numerical field, and "NULL" <> NULL in a string field.
To set a field to NULL, use NULL without any quotes eg...
* <code> INSERT INTO table (col1, col2) VALUES ('data1', NULL); </code>
=== IP Addresses ===
=== IP Addresses ===
IP addresses are most efficiently stored as an UNSIGNED INT, though obviously this isn't particularly human readable.   
IP addresses are most efficiently stored as an UNSIGNED INT, though obviously this isn't particularly human readable.   

Revision as of 08:30, 23 December 2009

Basic Commands

Command Description
SHOW DATABASES; Show databases on server
USE <db_name>; Use / go into a database
SHOW TABLES; Show tables in current database
DESCRIBE <tbl_name>; Show the format of the table
CREATE DATABASE <db_name>; Create a database


User Accounts

Create Users

To give full privileges to a user coming from any location use;

  • GRANT ALL PRIVILEGES on <database>.* to '<user>'@'%' identified by '<password>' with grant option;

Display Users

To display all configured users;

  • SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user

Then use the displayed lines to see the detail of each user

Data Types

NULL

NULL means "no data", it doesn't mean zero. Therefore 0 <> NULL in an numerical field, and "NULL" <> NULL in a string field.

To set a field to NULL, use NULL without any quotes eg...

  • INSERT INTO table (col1, col2) VALUES ('data1', NULL);

IP Addresses

IP addresses are most efficiently stored as an UNSIGNED INT, though obviously this isn't particularly human readable. MySQL will do the conversion between INT and dotted quad using the INET_ATON and INET_NTOA functions. For example;

  • SELECT INET_NTOA(ip) from ips;
  • INSERT INTO ips SET ip=INET_ATON('10.1.2.3');

Alternatively, use VARCHAR(15) to store as text.