MySQL: Difference between revisions
(→Basic Commands: Added DROP DATABASE) |
(→CREATE / ALTER Tables: Added further ALTER examples) |
||
Line 107: | Line 107: | ||
ALTER TABLE hware MODIFY COLUMN mem INT UNSIGNED; # Modify column type | ALTER TABLE hware MODIFY COLUMN mem INT UNSIGNED; # Modify column type | ||
ALTER TABLE hware CHANGE COLUMN cpu_num cpu_sock TINYINT UNSIGNED; # Change column name | ALTER TABLE hware CHANGE COLUMN cpu_num cpu_sock TINYINT UNSIGNED; # Change column name | ||
ALTER TABLE hware DROP COLUMN cpu_sock; # Drop (delete/remove) column from table | |||
</source> | </source> | ||
Revision as of 12:14, 14 January 2011
See the official MySQL documentation for further info.
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 |
DROP DATABASE <db_name>; |
Drop (delete) 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;
To give user privileges to a user coming from a specific host;
GRANT USAGE on <database>.* to '<user>'@'<host>' identified by '<password>' with grant option;
Delete Users
To remove an existing user use the following...
DROP USER '<user>'@'<host>';
- You need to specify the full
user@host
entry (or MySQL assumes the wild-card host%
) - Existing users sessions are not dropped, but will be unable to re-establish
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
Change User Password
SET PASSWORD FOR 'user'@'%' = PASSWORD('newpass');
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);
Numbers
BOOL
and BOOLEAN
are synonyms for TINYINT(1)
.
Type | Bytes | Min | Max |
---|---|---|---|
TINYINT |
1 | -128 | 127 |
TINYINT UNSIGNED |
1 | 0 | 255 |
SMALLINT |
2 | -32768 | 32767 |
SMALLINT UNSIGNED |
2 | 0 | 65535 |
MEDIUMINT |
3 | -8388608 | 8388607 |
MEDIUMINT UNSIGNED |
3 | 0 | 16777215 |
INT |
4 | -2147483648 | 2147483647 |
INT UNSIGNED |
4 | 0 | 4294967295 |
BIGINT |
8 | -9223372036854775808 | 9223372036854775807 |
BIGINT UNSIGNED |
8 | 0 | 18446744073709551615 |
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.
CREATE / ALTER Tables
CREATE
CREATE TABLE hware (hid INT AUTO_INCREMENT, make VARCHAR(64), model VARCHAR(64), cpu_num TINYINT UNSIGNED, mem SMALLINT UNSIGNED, sn VARCHAR(64), PRIMARY KEY (hid));
CREATE TABLE notes (nid INT UNSIGNED AUTO_INCREMENT, note VARCHAR(256), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (nid));
CREATE TABLE hware2notes (hid INT, nid INT, PRIMARY KEY (hid, nid)); # Primary key prevent duplicate rows
RENAME
RENAME TABLE hware to hardware;
ALTER
ALTER TABLE hware ADD UNIQUE KEY hid; # Add key (using existing column)
ALTER TABLE hware ADD COLUMN cpu_core TINYINT UNSIGNED AFTER cpu_num; # Add new column
ALTER TABLE hware MODIFY COLUMN mem INT UNSIGNED; # Modify column type
ALTER TABLE hware CHANGE COLUMN cpu_num cpu_sock TINYINT UNSIGNED; # Change column name
ALTER TABLE hware DROP COLUMN cpu_sock; # Drop (delete/remove) column from table
DELETE
DELETE FROM hware; # Delete the contents if the hware table
INSERT / UPDATE Rows
Basic Examples
INSERT INTO hosts (name, ping_ok) VALUES ('ServerA', 1) ON DUPLICATE KEY UPDATE ping_ok=1;
INSERT IGNORE INTO hware2note (hid, nid); # Ignores errors returned if insert would violate primary/unique key duplication
UPDATE hosts SET ping_ok=0, reason='Time Out' WHERE name='ServerA';
INSERT ... SELECT
Used when you want you want include data from another table in an INSERT
statement (eg you want to reference a unique ID in another table)
INSERT INTO table (id, make, model) SELECT id, 'DELL', 'PE1950' FROM hosts WHERE ip=INET_ATON('159.104.7.171');
UPDATE ... SELECT
The syntax is not UPDATE ... SELECT
, but it performs the function you'd expect from such a query, although its a little mis-leading. In the example below the plat
table is updated from the os
table.
UPDATE plat,os SET plat.osid=os.osid WHERE os.os_supplied='Microsoft(R) Windows(R) Server 2003, Standard Edition';
SELECT Rows
AS
SELECT INET_NTOA(ip) AS ip, name FROM hosts;
ORDER BY
SELECT INET_NTOA(ip) AS ip, name FROM hosts ORDER BY ip;
NULL's can be a bit of pain as they tend to end up at the top, to force then to the bottom insert an additional ISNULL(column)
for column your sorting by which has NULL values...
SELECT INET_NTOA(ip) AS ip, name FROM hosts ORDER BY ISNULL(ip), ip;
JOIN
SELECT make, model FROM table JOIN hosts ON table.id=hosts.id WHERE hosts.ip=INET_ATON('10.10.255.253');
Different types of join will yield differing results, depending how different rows match up see http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html
Migrate Databases
There are a number of GUI tools provided to assist in migrating data from one server to another, and these are well worth a look (eg MySQL Migration Toolkit, MySQL Workbench). However I have found that on occasion I can't get these to work (normally due to apparent permissioning issues, despite having full rights), and revert to the old fashioned manual way.
This procedure works for Windows and Unix systems alike, the mysql
and mysqldump
commands need to be run from the normal Unix shell or Windows command line prompts, not from with the mysql software.
- Export the database from the source machine (from either Unix shell / Windows command prompt as your sourec machine dictates), eg for database called
sourcedb
mysqldump -u root -p sourcedb > sourcedb.sql
- Copy the file to your destination database server
- Create an empty database on the destination database server
CREATE DATABASE destdb;
- Import the database dump
mysql -u root -p destdb < sourcedb.sql
- Give you client server access to the new database
GRANT ALL PRIVILEGES on destdb.* to 'destdbuser'@'clientsvr' IDENTIFIED BY 'password';