MySQL Command Line

Connect to MySQL:

c:\> mysql -h [hostname] -u [username] -p [password]

Connect to database:

mysql> show databases;

mysql> use [one database name];

e.g. mysql> use test;

To view Tables and Table Structure:

mysql> show tables;

mysql> desc [tablename]; = show columns from [tablename];

To view index:

mysql> show index from test.zoo; (= show keys from test.zoo; )

Using AUTO_INCREMENT

AUTO_INCREMENT is used to generate a unique identity for new rows:

mysql> CREATE TABLE zoo (
-> id MEDIUMINT NOT NULL AUTO_INCREMENT,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (id)
-> ) auto_increment = 100;

mysql> INSERT INTO zoo (name) VALUES
-> ('dog'),('cat'),('penguin'),
-> ('lax'),('whale'),('ostrich');

mysql> SELECT * FROM zoo;
+-----+---------+
id name
+-----+---------+
100 dog
101 cat
102 penguin
103 lax
104 whale
105 ostrich
+-----+---------+

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
will start your records at 100.

* * ---------------------------------------- * * ---------------------------------------- * * ---------------------------------------- * * ---------------------------------------- * *
On current db, we could access the table on another db:

mysql> use mysql;
Database changed
mysql> select * from test.zoo;
+-----+---------+
id name
+-----+---------+
100 dog
101 cat

MySQL find duplicate repords example

select address, count(address) as cnt
from mailing_list
group by address
having cnt > 1
order by cnt;

Created_tmp_tables

The number of in-memory temporary tables created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.

On my.cnf :
set-variable = tmp_table_size=128M

Chang the Mysql client prompt

Option 1: Changing the Mysql client prompt: (Mysql 4.0.2 and higher)

on my.cnf :

----------------------------

[mysql]

prompt=[\\u@\\h\\d]>\\_

----------------------------

It will show who is connecting which db on which host.

Option 2: use --prompt option on the command line to mysql

c:\> mysql --prompt="(\u@\h) [\d]> " -u root

MySQL Explain example

mysql> EXPLAIN SELECT * from zoo;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
id select_type table type possible_keys key key_len ref rows Extra
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 SIMPLE zoo ALL NULL NULL NULL NULL 6
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.02 sec)

built-in MySQL functions

mysql> select version();
+---------------------+
version()
+---------------------+
5.0.67-community-nt
+---------------------+

mysql> select database();
+------------+
database()
+------------+
test
+------------+

mysql> SELECT PASSWORD("mypass");
+-------------------------------------------+
PASSWORD("mypass")
+-------------------------------------------+
*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4
+-------------------------------------------+

mysql> SELECT PASSWORD("test");
+-------------------------------------------+
PASSWORD("test")
+-------------------------------------------+
*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29
+-------------------------------------------+

mysql> SELECT PASSWORD("");
+--------------+
PASSWORD("")
+--------------+

+--------------+

mysql> select md5('test');
+----------------------------------+
md5('test')
+----------------------------------+
098f6bcd4621d373cade4e832627b4f6
+----------------------------------+

MySQL INSERT SELECT example

insert into user (id, name, email, passwd, ref, joindate) select idm, user_name, email, pass, refferal, sysdate() from db.members;

Listing tables and their structure

mysql> use test;
Database changed

mysql> show tables;
+----------------+
Tables_in_test
+----------------+
animals
house
meds
patients
tree
zoo
+----------------+
6 rows in set (0.00 sec)

mysql> desc zoo; (= SHOW COLUMNS FROM zoo; )
+-------+--------------+------+-----+---------+----------------+
Field Type Null Key Default Extra
+-------+--------------+------+-----+---------+----------------+
id mediumint(9) NO PRI NULL auto_increment name char(30) NO NULL
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> show index from test.zoo; (= show keys from test.zoo; )
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
zoo 0 PRIMARY 1 id A 2 NULL NULL BTREE
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

Create a new database:

c:\ > mysql -u root -p password -h servername

mysql> CREATE DATABASE jp;

Create a new table:

CREATE TABLE customer (
cust_id int(5) DEFAULT '0' NOT NULL auto_increment,
surname varchar(50) NOT NULL,
firstname varchar(50) NOT NULL,
initial char(1),
title varchar(10),
addressline1 varchar(50) NOT NULL,
addressline2 varchar(50),
addressline3 varchar(50),
city varchar(20) NOT NULL,
state varchar(20),
zipcode varchar(5),
country varchar(20) DEFAULT 'Australia',
phone varchar(15),
fax varchar(15),
email varchar(30) NOT NULL,
salary int(7),
birth_date date( ),
PRIMARY KEY (cust_id),
KEY names (surname,firstname));

Add/Drop index:

ALTER TABLE customer ADD INDEX cities (city);

ALTER TABLE customer DROP INDEX names;

Log statements and their output by using the --tee option

mysql> \T c:\output.txt

Logging to file 'c:\output.txt'

mysql> select * from zoo;

+-----+---------+

id name

+-----+---------+

100 dog

101 cat

+-----+---------+

mysql> \t

Outfile disabled.