MySQL Notes
-
Good mysql cheat sheet: http://nparikh.freeshell.org/unix/mysql.php
-
Escape reserved words in field names in SQL queries with back ticks (below the tilde), NOT like MSSQL which uses square braces. Ugh. SQL "Standards"?
-
From command line:
-
mysql - login to mysql
-
mysql -u [username] -p -> to specify the user to login to the database and prompt for password
-
mysql -D [database to use]
-
(options above can be combined, of course)
-
show databases; -- lists all dbs
-
"use mysql" -- use the system database
-
"show tables" -- lists all tables in selected db
-
-
"SELECT db FROM db" -- list all db's in mysql (or SELECT *)
-
"DESCRIBE [tablename]" -- get table definition
-
"show variables" -- list the current MySQL configuration variables like buffer and cache sizes
-
SHOW FULL PROCESSLIST; -- list current queries
-
CREATE DATABASE xdb; GRANT ALL ON xdb.* TO xuser@xuser.com IDENTIFIED BY 'xpass'; REVOKE ALL ON xdb.* FROM xuser;
-
Import .sql files to a mysql server, you just use the following command: shell% mysql < mysql-backup.sql
-
-
Get number of records of all tables:
SELECT TABLE_NAME, TABLE_ROWS FROM `information_schema`.`tables` WHERE `table_schema` = 'YOUR_DB_NAME';
-
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2, ...]
-
[WHERE where_definition] [ORDER BY ...] [LIMIT #]
-
From: http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Data_Manipulation
-
-
You can use: "WHERE col_name is null" in sql.
-
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
-
[INTO] tbl_name [(col_name,...)] VALUES ((expression | DEFAULT),...),(...),...
-
[ ON DUPLICATE KEY UPDATE col_name=expression, ... ]
-
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name,...)]
SELECT ...
-
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
-
[INTO] tbl_name
-
SET col_name=(expression | DEFAULT), ...
-
[ ON DUPLICATE KEY UPDATE col_name=expression, ... ]
-
Repair a mysql table with errors in it: http://www.modwest.com/help/kb6-230.html
-
-
Database Size (I think this requires MySQL 5):
SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY table_schema;
-
Add an Index: Add an Index: ALTER TABLE table ADD INDEX product_id (product_id);
tags: mysql, db, database, sql