Friday, April 15, 2011

MySQL Handy Commands

This is a list of handy MySQL commands that I use time and time again. At the bottom are statements, clauses, and functions you can use in MySQL. Below that are PHP and Perl API functions you can use to interface with MySQL. To use those you will need to build PHP with MySQL functionality. To use MySQL with Perl you will need to use the Perl modules DBI and DBD::mysql.

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> drop database [database name];

To delete a table.

mysql> drop table [table name];

Show all data in a table.

mysql> SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

Show certain selected rows with the value "whatever".

mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";

Show all records containing the name "Bob" AND the phone number '3444444'.

mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.

mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;

Show all records starting with the letters 'bob' AND the phone number '3444444'.

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';

Show all records starting with the letters 'bob' AND the phone number '3444444' limit to records 1 through 5.

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' 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.

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

Join tables on common columns.

mysql> 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;

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 db (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 -ppassword 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');

MYSQL Statements and clauses

  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

String Functions

  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

Date and Time Functions

  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

Mathematical and Aggregate 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 functions and methods built into the 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
  errstr
  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_info
  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 Handles

PHP API - using functions built into 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
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)