Scribd
Upload
61 views

Handy Mysql Commands Description Command: Main Menu Blog About

This document provides a summary of common MySQL commands for: - Logging in and connecting to databases - Creating, listing, selecting, and deleting databases and tables - Querying, inserting, updating, and deleting data from tables - Joining tables, setting permissions, and backing up databases The commands are organized by function for easy reference.

Uploaded by

Hari C
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
61 views

Handy Mysql Commands Description Command: Main Menu Blog About

This document provides a summary of common MySQL commands for: - Logging in and connecting to databases - Creating, listing, selecting, and deleting databases and tables - Querying, inserting, updating, and deleting data from tables - Joining tables, setting permissions, and backing up databases The commands are organized by function for easy reference.

Uploaded by

Hari C
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

Main Menu Blog About

__ __ ____ ___ _
| \/ |_ _/ ___| / _ \| |
| |\/| | | | \___ \| | | | |
| | | | |_| |___) | |_| | |___
|_| |_|\__, |____/ \__\_\_____|
|___/

Handy MySQL Commands


Description Command
To login (from unix
shell) use -h only if [mysql dir]/bin/mysql -h hostname -u root -p
needed.
Create a database on
create database [databasename];
the sql server.
List all databases on
show databases;
the sql server.
Switch to a database. use [db name];
To see all the tables in
show tables;
the db.
To see database's field
describe [table name];
formats.
To delete a db. drop database [database name];
To delete a table. drop table [table name];
Show all data in a
SELECT * FROM [table name];
table.
Returns the columns
and column
show columns from [table name];
information pertaining
to the designated table.
Show certain selected
rows with the value SELECT * FROM [table name] WHERE [field name] = "whatever";
"whatever".
Show all records
containing the name SELECT * FROM [table name] WHERE name = "Bob" AND phone_number =
"Bob" AND the phone '3444444';
number '3444444'.
Show all records not
containing the name
"Bob" AND the phone SELECT * FROM [table name] WHERE name != "Bob" AND phone_number =
number '3444444' '3444444' order by phone_number;
order by the
phone_number field.
Show all records
starting with the letters SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number =
'bob' AND the phone '3444444';
number '3444444'.
Use a regular SELECT * FROM [table name] WHERE rec RLIKE "^a$";
expression to find
records. Use
"REGEXP BINARY"
to force case-
sensitivity. This finds
any record beginning
with a.
Show unique records. SELECT DISTINCT [column name] FROM [table name];
Show selected records
sorted in an ascending
SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
(asc) or descending
(desc).
Count rows. SELECT COUNT(*) FROM [table name];
select lookup.illustrationid, lookup.personid,person.birthday from lookup
Join tables on common
left join person on lookup.personid=person.personid=statement to join birthday in
columns.
person table with primary illustration id;
Switch to the mysql INSERT INTO [table name] (Host,User,Password)
db. Create a new user. VALUES('%','user',PASSWORD('password'));
Change a users
password.(from unix [mysql dir]/bin/mysqladmin -u root -h hostname.blah.org -p password 'new-password'
shell).
Change a users
password.(from SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
MySQL prompt).
Switch to mysql INSERT INTO [table name]
db.Give user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
privilages for a db. VALUES ('%','db','user','Y','Y','Y','Y','Y','N');
To update info already UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y'
in a table. where [field name] = 'user';
Delete a row(s) from a
DELETE from [table name] where [field name] = 'whatever';
table.
Update database
FLUSH PRIVILEGES;
permissions/privilages.
Delete a column. alter table [table name] drop column [column name];
Add a new column to
alter table [table name] add column [new column name] varchar (20);
db.
Change column name. alter table [table name] change [old column name] [new column name] varchar (50);
Make a unique column
alter table [table name] add unique ([column name]);
so you get no dupes.
Make a column bigger. alter table [table name] modify [column name] VARCHAR(3);
Delete unique from
alter table [table name] drop index [colmn name];
table.
Load a CSV file into a LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name]
table. FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);
Dump all databases for
backup. Backup file is
[mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql
sql commands to
recreate all db's.
Dump one database [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename
for backup. >/tmp/databasename.sql
Dump a table from a [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename >
database. /tmp/databasename.tablename.sql
Restore database (or [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
database table) from
backup.
CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial
VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),
Create Table Example
officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email
1.
VARCHAR(35),phone VARCHAR(25), groups
VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
create table [table name] (personid int(50) not null auto_increment primary
Create Table Example
key,firstname varchar(35),middlename varchar(50),lastname varchar(50) default
2.
'bato');

You might also like