List of MySQL commands

Working with MySQL, but do not always have a list of MySQL commands available, we have put a list together of handy MySQL commands that we use time and time again. At the bottom are statements, clauses, and functions you can use in MySQL. Below that are PHP functions you can use to interface with MySQL. To use those you will need to build PHP with MySQL functionality.

Below when you see # it means from the UNIX shell. When you see mysql> it means from a MySQL prompt after logging into MySQL.

To login (from unix shell) use -h only if needed.

# [mysql dir]/bin/mysql -h hostname -u root -p

Create a database on the sql server.

mysql> create database [databasename];

List all databases on the sql server.

mysql> show databases;

Switch to a database.

mysql> use [db name];

To see all the tables in the db.

mysql> show tables;

To see database’s field formats.

mysql> describe [table name];

To delete a db.

mysql> SELECT * FROM [table name] WHERE name like “Lisa%” AND phone_number = ‘123456’;

Show all records starting with the letters ‘lisa’ AND the phone number ‘123456’ limit to records 1 through 5.

mysql> SELECT * FROM [table name] WHERE name like “Lisa%” AND phone_number = ‘123456’ limit 1,5;

Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a.

mysql> SELECT * FROM [table name] WHERE rec RLIKE “^a”;

Show unique records.

mysql> SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Return number of rows.

mysql> SELECT COUNT(*) FROM [table name];

Sum column.

Join tables on common columns.

mysql> select lookup.userid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary user id;

Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,’username’,PASSWORD(‘password’));
mysql> flush privileges;

Change a users password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’

Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

# mysql -u root -p
mysql> SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);
mysql> flush privileges;

Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop
# mysqld_safe –skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Set a root password if there is on root password.

# mysqladmin -u root password newpassword

Update a root password.

# mysqladmin -u root -p oldpassword newpassword

Allow the user “bob” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by ‘passwd’;
mysql> flush privileges;

Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,’databasename’,’username’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’);
mysql> flush privileges;  

or  

mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

To update info already in a table.

mysql> UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;

Delete a row(s) from a table.

mysql> DELETE from [table name] where [field name] = ‘whatever’;

Update database permissions/privilages.

mysql> flush privileges;

Delete a column.

mysql> alter table [table name] drop column [column name];

Add a new column to db.

mysql> alter table [table name] add column [new column name] varchar (20);

Change column name.

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupes.

mysql> alter table [table name] add unique ([column name]);

Make a column bigger.

mysql> alter table [table name] modify [column name] VARCHAR(3);

Delete unique from table.

mysql> alter table [table name] drop index [colmn name];

Load a CSV file into a table.

mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' (field1,field2,field3);

Dump all databases for backup. Backup file is sql commands to recreate all db’s.

# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql

Dump one database for backup.

# [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql

Dump a table from a database.

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.

# [mysql dir]/bin/mysql -u username -p password databasename < /tmp/databasename.sql

Create Table Example 1.

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

Create Table Example 2.

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');

ALTER DATABASE  ALTER TABLE  ALTER VIEW  ANALYZE TABLE  BACKUP TABLE  CACHE INDEX  CHANGE MASTER TO  CHECK TABLE  CHECKSUM TABLE  COMMIT  CREATE DATABASE  CREATE INDEX  CREATE TABLE  CREATE VIEW  DELETE  DESCRIBE  DO  DROP DATABASE  DROP INDEX  DROP TABLE  DROP USER  DROP VIEW  EXPLAIN  FLUSH  GRANT  HANDLER  INSERT  JOIN  KILL  LOAD DATA FROM MASTER  LOAD DATA INFILE  LOAD INDEX INTO CACHE  LOAD TABLE...FROM MASTER  LOCK TABLES  OPTIMIZE TABLE  PURGE MASTER LOGS  RENAME TABLE  REPAIR TABLE  REPLACE  RESET  RESET MASTER  RESET SLAVE  RESTORE TABLE  REVOKE  ROLLBACK  ROLLBACK TO SAVEPOINT  SAVEPOINT  SELECT  SET  SET PASSWORD  SET SQL_LOG_BIN  SET TRANSACTION  SHOW BINLOG EVENTS  SHOW CHARACTER SET  SHOW COLLATION  SHOW COLUMNS  SHOW CREATE DATABASE  SHOW CREATE TABLE  SHOW CREATE VIEW  SHOW DATABASES  SHOW ENGINES  SHOW ERRORS  SHOW GRANTS  SHOW INDEX  SHOW INNODB STATUS  SHOW LOGS  SHOW MASTER LOGS  SHOW MASTER STATUS  SHOW PRIVILEGES  SHOW PROCESSLIST  SHOW SLAVE HOSTS  SHOW SLAVE STATUS  SHOW STATUS  SHOW TABLE STATUS  SHOW TABLES  SHOW VARIABLES  SHOW WARNINGS  START SLAVE  START TRANSACTION  STOP SLAVE  TRUNCATE TABLE  UNION  UNLOCK TABLES  USE  

AES_DECRYPT  AES_ENCRYPT  ASCII  BIN  BINARY  BIT_LENGTH  CHAR  CHAR_LENGTH  CHARACTER_LENGTH  COMPRESS  CONCAT  CONCAT_WS  CONV  DECODE  DES_DECRYPT  DES_ENCRYPT  ELT  ENCODE  ENCRYPT  EXPORT_SET  FIELD  FIND_IN_SET  HEX  INET_ATON  INET_NTOA  INSERT  INSTR  LCASE  LEFT  LENGTH  LOAD_FILE  LOCATE  LOWER  LPAD  LTRIM  MAKE_SET  MATCH    AGAINST  MD5  MID  OCT  OCTET_LENGTH  OLD_PASSWORD  ORD  PASSWORD  POSITION  QUOTE  REPEAT  REPLACE  REVERSE  RIGHT  RPAD  RTRIM  SHA  SHA1  SOUNDEX  SPACE  STRCMP  SUBSTRING  SUBSTRING_INDEX  TRIM  UCASE  UNCOMPRESS  UNCOMPRESSED_LENGTH  UNHEX  UPPER  

ADDDATE  ADDTIME  CONVERT_TZ  CURDATE  CURRENT_DATE  CURRENT_TIME  CURRENT_TIMESTAMP  CURTIME  DATE  DATE_ADD  DATE_FORMAT  DATE_SUB  DATEDIFF  DAY  DAYNAME  DAYOFMONTH  DAYOFWEEK  DAYOFYEAR  EXTRACT  FROM_DAYS  FROM_UNIXTIME  GET_FORMAT  HOUR  LAST_DAY  LOCALTIME  LOCALTIMESTAMP  MAKEDATE  MAKETIME  MICROSECOND  MINUTE  MONTH  MONTHNAME  NOW  PERIOD_ADD  PERIOD_DIFF  QUARTER  SEC_TO_TIME  SECOND  STR_TO_DATE  SUBDATE  SUBTIME  SYSDATE  TIME  TIMEDIFF  TIMESTAMP  TIMESTAMPDIFF  TIMESTAMPADD  TIME_FORMAT  TIME_TO_SEC  TO_DAYS  UNIX_TIMESTAMP  UTC_DATE  UTC_TIME  UTC_TIMESTAMP  WEEK  WEEKDAY  WEEKOFYEAR  YEAR  YEARWEEK  

ABS  ACOS  ASIN  ATAN  ATAN2  AVG  BIT_AND  BIT_OR  BIT_XOR  CEIL  CEILING  COS  COT  COUNT  CRC32  DEGREES  EXP  FLOOR  FORMAT  GREATEST  GROUP_CONCAT  LEAST  LN  LOG  LOG2  LOG10  MAX  MIN  MOD  PI  POW  POWER  RADIANS  RAND  ROUND  SIGN  SIN  SQRT  STD  STDDEV  SUM  TAN  TRUNCATE  VARIANCE  

CASE  IF  IFNULL  NULLIF  

comp_err  isamchk  make_binary_distribution  msql2mysql  my_print_defaults  myisamchk  myisamlog  myisampack  mysqlaccess  mysqladmin  mysqlbinlog  mysqlbug  mysqlcheck  mysqldump  mysqldumpslow  mysqlhotcopy  mysqlimport  mysqlshow  perror 

mysql_affected_rows  mysql_change_user  mysql_client_encoding  mysql_close  mysql_connect  mysql_create_db  mysql_data_seek  mysql_db_name  mysql_db_query  mysql_drop_db  mysql_errno  mysql_error  mysql_escape_string  mysql_fetch_array  mysql_fetch_assoc  mysql_fetch_field  mysql_fetch_lengths  mysql_fetch_object  mysql_fetch_row  mysql_field_flags  mysql_field_len  mysql_field_name  mysql_field_seek  mysql_field_table  mysql_field_type  mysql_free_result  mysql_get_client_info  mysql_get_host_info  mysql_get_proto_info  mysql_get_server_info  mysql_info  mysql_insert_id  mysql_list_dbs  mysql_list_fields  mysql_list_processes  mysql_list_tables  mysql_num_fields  mysql_num_rows  mysql_pconnect  mysql_ping  mysql_query  mysql_real_escape_string  mysql_result  mysql_select_db  mysql_stat  mysql_tablename  mysql_thread_id  mysql_unbuffered_query