### Mysql Quick Glance ##
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.
create database [databasename];
List all databases on the sql server.
show databases;
Switch to a database.
use [db name];
To see all the tables in the db.
show tables;
To see database's field formats.
describe [table name];
To delete a db.
drop database [database name];
To delete a table.
drop table [table name];
Show all data in a table.
SELECT * FROM [table name];
Returns the columns and column information pertaining to the designated table.
show columns from [table name];
Show certain selected rows with the value "whatever".
SELECT * FROM [table name] WHERE [field name] = "whatever";
Show all records containing the name "Bob" AND the phone number '3444444'.
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.
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'.
SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';
Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.
SELECT * FROM [table name] WHERE rec RLIKE "^a$";
Show unique records.
SELECT DISTINCT [column name] FROM [table name];
Show selected records sorted in an ascending (asc) or descending (desc).
SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
Return number of rows.
SELECT COUNT(*) FROM [table name];
Sum column.
SELECT SUM(*) FROM [table name];
Join 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 a users password.(from unix shell).
[mysql dir]/bin/mysqladmin -u root -h hostname.blah.org -p password 'new-password'
Change a users password.(from MySQL prompt).
SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
Allow the user "bob" to connect to the server from localhost using the password "passwd"
grant usage on *.* to bob@localhost identified by 'passwd';
Switch to mysql db.Give user privilages for a db.
INSERT INTO [table name](Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES
('%','databasename','username','Y','Y','Y','Y','Y','N');
or
grant all privileges on databasename.* to username@localhost;
To update info already in a table.
UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] =
'user';
Delete a row(s) from a table.
DELETE from [table name] where [field name] = 'whatever';
Update database permissions/privilages.
FLUSH PRIVILEGES;
Delete a column.
alter table [table name] drop column [column name];
Add a new column to db.
alter table [table name] add column [new column name] varchar (20);
Change column name.
alter table [table name] change [old column name] [new column name] varchar (50);
Make a unique column so you get no dupes.
alter table [table name] add unique ([column name]);
Make a column bigger.
alter table [table name] modify [column name] VARCHAR(3);
Delete unique from table.
alter table [table name] drop index [colmn 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);
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.
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.
create table [table name] (personid int(50) not null auto_increment primary key,firstname
varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');
Connection
$ mysql -h localhost -u user_name -ppassword
$ mysql -h localhost -u user_name -p
$ ******
$ mysql -h193.0.0.13 -u user_name -p db_name
$ ******
How to avoid this typing?
$ vi $HOME/.my.cnf ( create this file in your ! home directory )
[client]
host=localhost
user=your_login
password=your_password
:wq (save file)
$ chmod 400 $HOME/.my.cnf
$ mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 112 to server ....
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> _
Some Help and Show Commands
$ mysql --help
less
$ mysqld --help
$ mysqlshow --help
less
$ mysqldump --help
less
$ mysqlshow - show all databases.
$ mysqlshow db_name - all tables in particular database.
$ mysqlshow db_name BA* - all tables which start from BA letters.
mysql> \?
mysql> use db_name;
mysql> show databases;
mysql> show databases like 'ba%'
mysql> show tables;
mysql> describe table_name;
mysql> select user(), now(), version(), database();
+---------------+---------------------+----------------+------------+
user()
now()
version()
database()
+---------------+---------------------+----------------+------------+
gyani@localhost
2003-01-05 21:24:27
4.0.1-alpha-nt
test
+---------------+---------------------+----------------+------------+
mysql> show tables from db_name
mysql> show tables from db_name like '__ab%'
mysql> show columns from table_name
mysql> show columns from table_name from db_name
mysql> show grants for user_name
mysql> show index from table_name
mysql> show index from table_name from db_name
mysql> show processlist
mysql> show status
mysql> show table status from db_name
mysql> show variables
Creating Table
mysql> drop table if exists my_table;
mysql> create table my_table (ID int not null primary key, l_name
-> varchar(20) not null, f_name varchar(20));
Query OK, 0 rows affected (0.08 sec)
mysql> describe my_table;
+--------+-------------+------+-----+---------+-------+
Field
Type
Null
Key
Default
Extra
+--------+-------------+------+-----+---------+-------+
ID
int(11)
PRI
0
l_name
varchar(20)
f_name
varchar(20)
YES
NULL
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)
mysql> insert into my_table values (1,"Frost",'Robert');
Query OK, 1 row affected (0.03 sec)
mysql> insert into my_table (ID,l_name) values (2,'Smith');
Query OK, 1 row affected (0.00 sec)
mysql> select * from my_table;
+----+--------+--------+
ID
l_name
f_name
+----+--------+--------+
1
Frost
Robert
2
Smith
NULL
+----+--------+--------+
2 rows in set (0.01 sec)
mysql> update my_table set f_name='Linda' where l_name='Smith'
-> and ID=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from my_table;
+----+--------+--------+
ID
l_name
f_name
+----+--------+--------+
1
Frost
Robert
2
Smith
Linda
+----+--------+--------+
2 rows in set (0.00 sec)
mysql> delete from my_table where ID=1;
mysql> select * from my_table;
+----+--------+--------+
ID
l_name
f_name
+----+--------+--------+
2
Smith
Linda
+----+--------+--------+
1 row in set (0.01 sec)
mysql> create table table_name select * from bank.BANK_WORKER;
(copy table bank.BANK_WORKER into table_name)
Creatint Table using Script
$ vi create_table.sql ( open new file )
---------------------
drop table if exists my_table;
create table my_table (ID int not null primary key, l_name
varchar(20) not null, f_name varchar(20));
insert into my_table values (1,"Frost",'Robert');
insert into my_table (ID,l_name) values (2,'Smith');
select * from my_table;
:wq ( save file )
-----------------
$ mysql db_name < create_table.sql
$ mysql db_name -t < create_table.sql > output.sql
mysql> \. create_table.sql
Some Useful Commands
$ mysqldump db_name my_table
$ mysqldump db_name my_table > output.sql
$ mysqldump --no-data db_name my_table > dump_table_name.sql
$ mysqldump --add-drop-table db_name my_table > dump_table_name.sql
$ mysqladmin create db_name
$ mysqladmin drop db_name
$ mysqladmin flush-privileges
$ mysqladmin ping
$ mysqladmin reload
$ mysqladmin kill ps_id,ps_id...
$ mysqladmin --user=root shutdown
$ mysqladmin variables
$ mysqlimport db_name file_name ...
$ safe_mysqld
$ myisamchk table_name.MYI
mysql> GRANT ALL ON db_name TO user_name@localhost IDENTIFIED BY 'password'
mysql> GRANT ALL ON db_name TO user_name@'%' IDENTIFIED BY 'password'
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.
create database [databasename];
List all databases on the sql server.
show databases;
Switch to a database.
use [db name];
To see all the tables in the db.
show tables;
To see database's field formats.
describe [table name];
To delete a db.
drop database [database name];
To delete a table.
drop table [table name];
Show all data in a table.
SELECT * FROM [table name];
Returns the columns and column information pertaining to the designated table.
show columns from [table name];
Show certain selected rows with the value "whatever".
SELECT * FROM [table name] WHERE [field name] = "whatever";
Show all records containing the name "Bob" AND the phone number '3444444'.
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.
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'.
SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';
Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.
SELECT * FROM [table name] WHERE rec RLIKE "^a$";
Show unique records.
SELECT DISTINCT [column name] FROM [table name];
Show selected records sorted in an ascending (asc) or descending (desc).
SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
Return number of rows.
SELECT COUNT(*) FROM [table name];
Sum column.
SELECT SUM(*) FROM [table name];
Join 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 a users password.(from unix shell).
[mysql dir]/bin/mysqladmin -u root -h hostname.blah.org -p password 'new-password'
Change a users password.(from MySQL prompt).
SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
Allow the user "bob" to connect to the server from localhost using the password "passwd"
grant usage on *.* to bob@localhost identified by 'passwd';
Switch to mysql db.Give user privilages for a db.
INSERT INTO [table name](Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES
('%','databasename','username','Y','Y','Y','Y','Y','N');
or
grant all privileges on databasename.* to username@localhost;
To update info already in a table.
UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] =
'user';
Delete a row(s) from a table.
DELETE from [table name] where [field name] = 'whatever';
Update database permissions/privilages.
FLUSH PRIVILEGES;
Delete a column.
alter table [table name] drop column [column name];
Add a new column to db.
alter table [table name] add column [new column name] varchar (20);
Change column name.
alter table [table name] change [old column name] [new column name] varchar (50);
Make a unique column so you get no dupes.
alter table [table name] add unique ([column name]);
Make a column bigger.
alter table [table name] modify [column name] VARCHAR(3);
Delete unique from table.
alter table [table name] drop index [colmn 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);
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.
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.
create table [table name] (personid int(50) not null auto_increment primary key,firstname
varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');
Connection
$ mysql -h localhost -u user_name -ppassword
$ mysql -h localhost -u user_name -p
$ ******
$ mysql -h193.0.0.13 -u user_name -p db_name
$ ******
How to avoid this typing?
$ vi $HOME/.my.cnf ( create this file in your ! home directory )
[client]
host=localhost
user=your_login
password=your_password
:wq (save file)
$ chmod 400 $HOME/.my.cnf
$ mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 112 to server ....
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> _
Some Help and Show Commands
$ mysql --help
less
$ mysqld --help
$ mysqlshow --help
less
$ mysqldump --help
less
$ mysqlshow - show all databases.
$ mysqlshow db_name - all tables in particular database.
$ mysqlshow db_name BA* - all tables which start from BA letters.
mysql> \?
mysql> use db_name;
mysql> show databases;
mysql> show databases like 'ba%'
mysql> show tables;
mysql> describe table_name;
mysql> select user(), now(), version(), database();
+---------------+---------------------+----------------+------------+
user()
now()
version()
database()
+---------------+---------------------+----------------+------------+
gyani@localhost
2003-01-05 21:24:27
4.0.1-alpha-nt
test
+---------------+---------------------+----------------+------------+
mysql> show tables from db_name
mysql> show tables from db_name like '__ab%'
mysql> show columns from table_name
mysql> show columns from table_name from db_name
mysql> show grants for user_name
mysql> show index from table_name
mysql> show index from table_name from db_name
mysql> show processlist
mysql> show status
mysql> show table status from db_name
mysql> show variables
Creating Table
mysql> drop table if exists my_table;
mysql> create table my_table (ID int not null primary key, l_name
-> varchar(20) not null, f_name varchar(20));
Query OK, 0 rows affected (0.08 sec)
mysql> describe my_table;
+--------+-------------+------+-----+---------+-------+
Field
Type
Null
Key
Default
Extra
+--------+-------------+------+-----+---------+-------+
ID
int(11)
PRI
0
l_name
varchar(20)
f_name
varchar(20)
YES
NULL
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)
mysql> insert into my_table values (1,"Frost",'Robert');
Query OK, 1 row affected (0.03 sec)
mysql> insert into my_table (ID,l_name) values (2,'Smith');
Query OK, 1 row affected (0.00 sec)
mysql> select * from my_table;
+----+--------+--------+
ID
l_name
f_name
+----+--------+--------+
1
Frost
Robert
2
Smith
NULL
+----+--------+--------+
2 rows in set (0.01 sec)
mysql> update my_table set f_name='Linda' where l_name='Smith'
-> and ID=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from my_table;
+----+--------+--------+
ID
l_name
f_name
+----+--------+--------+
1
Frost
Robert
2
Smith
Linda
+----+--------+--------+
2 rows in set (0.00 sec)
mysql> delete from my_table where ID=1;
mysql> select * from my_table;
+----+--------+--------+
ID
l_name
f_name
+----+--------+--------+
2
Smith
Linda
+----+--------+--------+
1 row in set (0.01 sec)
mysql> create table table_name select * from bank.BANK_WORKER;
(copy table bank.BANK_WORKER into table_name)
Creatint Table using Script
$ vi create_table.sql ( open new file )
---------------------
drop table if exists my_table;
create table my_table (ID int not null primary key, l_name
varchar(20) not null, f_name varchar(20));
insert into my_table values (1,"Frost",'Robert');
insert into my_table (ID,l_name) values (2,'Smith');
select * from my_table;
:wq ( save file )
-----------------
$ mysql db_name < create_table.sql
$ mysql db_name -t < create_table.sql > output.sql
mysql> \. create_table.sql
Some Useful Commands
$ mysqldump db_name my_table
$ mysqldump db_name my_table > output.sql
$ mysqldump --no-data db_name my_table > dump_table_name.sql
$ mysqldump --add-drop-table db_name my_table > dump_table_name.sql
$ mysqladmin create db_name
$ mysqladmin drop db_name
$ mysqladmin flush-privileges
$ mysqladmin ping
$ mysqladmin reload
$ mysqladmin kill ps_id,ps_id...
$ mysqladmin --user=root shutdown
$ mysqladmin variables
$ mysqlimport db_name file_name ...
$ safe_mysqld
$ myisamchk table_name.MYI
mysql> GRANT ALL ON db_name TO user_name@localhost IDENTIFIED BY 'password'
mysql> GRANT ALL ON db_name TO user_name@'%' IDENTIFIED BY 'password'
