Sometimes it helps to see working syntax. This page contains random snippets of MySQL syntax that we've found to be helpful in the past. They might be a good starting point to help you solve new problems.
Session Management
% mysql <databasename> -p;
mysql> SHOW TABLES;
mysql> SHOW INDEX FROM <tablename>;
mysql> DESCRIBE <tablename>;
CREATE TABLE Examples
# create a table called dvds
mysql> CREATE TABLE dvds (id MEDIUMINT NOT NULL AUTO_INCREMENT, title varchar(255)
not null, release_year int(4), purchase ENUM('o','w','u'), comment TEXT, stars FLOAT(5,3)
DEFAULT 0, PRIMARY KEY (id));
# create a new table called tempfwlog and dump all distinct rows from existing table fwlog
mysql> create table tempfwlog select distinctrow * from fwlog;
ALTER TABLE Examples
mysql> alter table office_hrs drop primary key;
Query OK, 11 rows affected (0.01 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> alter table office_hrs add column idx mediumint not null auto_increment first, add primary key (idx);
Query OK, 11 rows affected (0.01 sec)
Records: 11 Duplicates: 0 Warnings: 0
Referential Integrity:
mysql> ALTER TABLE pi_data ADD FOREIGN KEY (artifact_idx) REFERENCES artifacts(idx) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 41 rows affected (0.02 sec)
Records: 41 Duplicates: 0 Warnings: 0
RENAME a Database
You can't rename a database. You have to dump the SQL of the existing database, then create a new database based on the existing database's definition and data.
% mysqldump -u username -p -v olddatabase > olddbdump.sql
# mysqladmin -u username -p create newdatabase
# mysql -u username -p newdatabase < olddbdump.sql
SELECT Syntax Examples
# Select course instances that have not been updated from live institutional data for more than a week:
mysql> SELECT semester, course_number, dt
FROM inst
WHERE DATE_SUB(NOW(), INTERVAL 1 WEEK) > dt;
mysql> SELECT *
FROM history
WHERE user_name = "cwmiller" AND
acct_session_start_time > 940000000;
mysql> SELECT DISTINCT fwname INTO OUTFILE '/tmp/foobar.txt'
FROM fwlog;
mysql> SELECT COUNT(distinct start, end, fwname, client, username)
FROM tempfwlog;
mysql> SELECT DISTINCT devices.ip
FROM devices
LEFT JOIN devices2 ON devices.ip = devices2.ip
WHERE devices2.ip IS NULL
ORDER BY ip;
mysql> SELECT DISTINCT devices2.ip
FROM devices2
LEFT JOIN devices ON devices.ip = devices2.ip
WHERE devices.ip IS NULL
ORDER BY ip;
mysql> SELECT DISTINCT devices_new.ip, devices_new.mname, devices_new.syscontact
FROM devices_new
LEFT JOIN devices_old ON devices_old.ip = devices_new.ip
WHERE devices_old.ip IS NULL
ORDER BY INET_ATON(devices_new.ip);
mysql> SELECT COUNT(event.model_h) AS sum, HEX(event.model_h), model_name
FROM event
JOIN model
WHERE event.model_h = model.model_h
GROUP BY event.model_h
ORDER BY sum;
mysql> SELECT dev, layer2.ifindex, COUNT(mac) AS MAC_count
FROM layer2
INNER JOIN dev_models ON layer2.dev = dev_models.ip
INNER JOIN dev_types ON dev_models.model = dev_types.model
AND dev_models.owner = dev_types.owner
INNER JOIN dev_ifs ON layer2.dev = dev_ifs.ip
AND layer2.ifindex = dev_ifs.ifindex
WHERE dev_models.owner = "Resnet" AND
dev_types.type != "Hub" AND
"2004-03-02 00:00:00" BETWEEN layer2.orig_dt AND layer2.last_dt AND
ISNULL(dev_ifs.uplink)
GROUP BY dev, ifindex HAVING MAC_count > 1
ORDER BY INET_ATON(dev), layer2.ifindex;
mysql> SELECT FROM_UNIXTIME(Acct_Session_Start_Time) AS start, (Acct_Session_Time/3600) AS
dur_hours, FROM_UNIXTIME(Acct_Session_Start_Time+Acct_Session_Time) AS end
FROM history
WHERE User_Name = "cwmiller"
ORDER BY start;
# select all CSE faculty and staff userids into one space-separated text string
mysql> select group_concat(userid order by userid separator ' ') from facstaff where active=1;
INSERT, UPDATE, and DELETE Examples
mysql> insert into fwlog2 select distinct start, end, fwname, client, username from fwlog;
mysql> insert into fwlog2 (start, end, fwname, client, username) select distinct start, end, fwname, client, username from fwlog;
mysql> delete from history where Acct_Session_Start_Time < UNIX_TIMESTAMP('2001-10-31 23:59:59');
Copy an existing MySQL table to a new table
mysql> CREATE TABLE recipes_new LIKE production.recipes;
mysql> INSERT recipes_new SELECT * FROM production.recipes;
Add an Index field to an Existing Table
# Adding column 'idx' to table 'mytable'
mysql> alter table mytable add column idx tinyint not null first;
mysql> set @i = 0;
mysql> update mytable set idx=@i:=@i+1;
mysql> alter table mytable add primary key (idx);
mysql> alter table mytable change column idx idx tinyint auto_increment;
Change Field Lengths or Types
mysql> use <dbname>;
mysql> alter table hub_data drop primary key;
mysql> alter table hub_data change port port VARCHAR(16) not null;
mysql> alter table hub_data add primary key (hub, port, mac);
mysql> alter table switch_traffic add column ifInOctets INT(4);
mysql> alter table switch_traffic alter ifInOctets SET DEFAULT -1;
mysql> alter table fwlog2 add index fwname (fwname);
mysql> alter table hub_data2 add column orig_dt datetime not null FIRST;
mysql> alter table hub_data2 add column last_dt datetime not null AFTER orig_dt;
Change Database and Table Permissions
mysql> INSERT INTO host VALUES ('rnmaster.resnet.buffalo.edu','%','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user (Host,User,Password) VALUES('%','guest',PASSWORD('foobar123!'));
mysql> UPDATE user SET Select_priv = 'Y' WHERE user='guest';
mysql> UPDATE user set Drop_priv='N' where Host='rnmaster.resnet.buffalo.edu';
Fixing Tables/Indexes
ISD tables and ISM indexes:
% sudo /opt/mysql/bin/isamchk -rvdf /data//
... I also repaired the fwlog table/index with the -o (safe recovey) command:
% sudo /opt/mysql/bin/isamchk -o /data/stats/fwlog
MYD tables and MYI indexes (my data, my index):
% sudo /opt/mysql/bin/myisamchk -rvdf /data//
-r repair, recover
-v verbose
-d short description
-f force
-o safe-recovery
MyISAM tables are more efficient and feature-rich than ISAM tables.
Upgrade tables to MyISAM format where possible.
Use this command:
mysql> ALTER TABLE arp_data TYPE = MYISAM;
mysql> REPAIR TABLE layer2 use_frm; # recreate MYI file based on tablename.frm
- http://dev.mysql.com/doc/refman/5.1/en/
