The most common MySQL commands

Here is a list of the most common commands that are used from time to time.

To log in (from a Unix shell) use -h only if necessary.

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

Create a database on a SQL server.

create database [databasename];

List all the databases on the SQL server.

show databases;

Change the database.

use [db name];

To see all the tables in the Database.

show tables;

To see the field formats.

describe [table name];

To delete a database.

drop database [database name];

To delete a table.

drop table [table name];

To see all the information of a table.

SELECT * FROM [table name];

Return the information of the columns and the respective columns of the specified table.
show columns from [table name];

See specific fields with the value 'anything'.

SELECT * FROM [table name] WHERE [field name] = "anything";

See all entries that contain the name "Manuel" and the phone number.
3444444

SELECT * FROM [table name] WHERE name = "Manuel" AND phone_number =

3444444

View all entries containing the name 'José' and the phone number '3444444'.
ordered by the phone field.

SELECT * FROM [table name] WHERE name != "José" AND phone_number = '3444444'
order by phone_number;

View all entries that start with the letters 'joão' and by phone '3444444'.

SELECT * FROM [table name] WHERE name like 'joão%' AND phone_number =
3444444

Use a regular expression to find entries. Use 'REGEXP'

"BINARY" to enforce case sensitivity. In this case, it searches for any entry.
started by a.

SELECT * FROM [table name] WHERE rec RLIKE "^a$";

Show unique entries.

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

Show selected entries in ascending (asc) or descending (desc) order.

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

Returns the number of columns.

SELECT COUNT(*) FROM [table name];

Sum the columns.

SELECT SUM(*) FROM [table name];

Join the tables on common columns.

select lookup.illustrationid, lookup.personid, person.birthday from lookup
left join person on lookup.personid = person.personid = statement to join birthday in person
table with primary illustration id;

Switch to the mysql db. Create a new user.

INSERT INTO [table name] (Host, User, Password)

VALUES('%', 'user', PASSWORD('password'));

Change the password. (of a unix console).

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

Change a user password. (from the MySQL command line).

SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');

Authorize the user 'zé' to connect to the local host server using the password.
password

grant usage on *.* to zé@localhost identified by 'passwd';

Switch to a MySQL database. Grant privileges to a user on a database.

INSERT INTO [table name]

Host
VALUES (’%',’databasename’,'username’,'Y’,'Y’,'Y’,'Y’,'Y’,'N’);

or

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

To update the information of a table.

UPDATE [table name] SET Select_priv = 'Y', Insert_priv = 'Y', Update_priv = 'Y' where
user

Delete rows from a table.

DELETE from [table name] where [field name] = 'whatever';

Update permissions and privileges of a database.

FLUSH PRIVILEGES;

Turn off a column.

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

Add a column to a Database.

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

Change the name of a table.

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

Create a single column.

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

Create a larger column.

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

Delete a single entry from a table.

alter table [table name] drop index [column name];

Load a CSV file into a table.

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

Make a backup of all databases.

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

Make a backup of just one database.

[mysql dir]/bin/mysqldump -u username -ppassword --databases databasename

/tmp/databasename.sql

Backup a table from a database.

[mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename >

/tmp/databasename.tablename.sql

Restore a database (or table) from a backup.

[mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

Create Example Table 1.

CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3),
VARCHAR(35)
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.

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

Clauses and Declarations MYSQL

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

Functions of Strings

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

ESRVER

RIGHT
 RPAD

RTRIM

SHA

SHA1

SOUNDEX

SPACE

STRCMP

SUBSTRING

SUBSTRING_INDEX

TRIM

UCASE

UNCOMPRESS

UNCOMPRESSED_LENGTH

UNHEX

UPPER

Functions of date and time

ADDDATE

ADDTIME

CONVERT_TZ

CURDATE

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

CURTIME

DATE

DATE_ADD

DATE_FORMAT

DATE_SUB
 DATEDIFF

DAY

DAYNAME

DAY OF MONTH

DAYOFWEEK

DAY OF YEAR

EXTRACT

FROM_DAYS

FROM_UNIXTIME

GET_FORMAT

HOUR

LAST_DAY

LOCALTIME

LOCALTIMESTAMP

MAKE DATE

MAKETIME

MICROSECOND

MINUTE

MONTH

MONTH NAME

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

WEEK OF YEAR

YEAR

YEARWEEK

Mathematical and aggregation functions

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

Flow control functions

CASE

IF

IFNULL

NULLIF

Command line utilities

comp_err

isamchk

make_binary_distribution

msql2mysql

my_print_defaults

myisamchk

myisamlog

myisampack

mysqlaccess

mysqladmin

mysqlbinlog

mysqlbug

mysqlcheck

mysqldump

mysqldumpslow
 mysqlhotcopy

mysqlimport

mysqlshow

perror

Perl API - Using built-in functions and methods in Perl DBI with MySQL

available_drivers

begin_work

bind_col

bind_columns

bind_param

bind_param_array

bind_param_inout

can

clone

column_info

commit

connect

connect_cached

data_sources

disconnect

do

dump_results

err

error string

execute

execute_array

execute_for_fetch

fetch
fetchall_arrayref

fetchall_hashref

fetchrow_array

fetchrow_arrayref

fetchrow_hashref

finish

foreign_key_info

func

get_info

installed_versions

last_insert_id

looks like number

neat

neat_list

parse_dsn

parse_trace_flag

parse_trace_flags

ping

prepare

prepare_cached

primary_key

primary key information

quote

quote_identifier

rollback

rows

selectall_arrayref

selectall_hashref
 selectcol_arrayref

selectrow_array

selectrow_arrayref

selectrow_hashref

set_err

state

table_info

table_info_all

tables

trace

trace_msg

type_info

type_info_all

Attributes for management

PHP API - Using built-in functions in PHP with MySQL

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