 |
| SQL - MySQL All Commands PDF |
SQL - MySQL All Commands PDF
MySQL Commands
- SELECT — extracts data from a database. ...
- UPDATE — updates data in a database. ...
- DELETE — deletes data from a database. ...
- INSERT INTO — inserts new data into a database. ...
- CREATE DATABASE — creates a new database. ...
- ALTER DATABASE — modifies a database. ...
- CREATE TABLE — creates a new table. ...
- ALTER TABLE — modifies a table.
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.
View all in PDF file
PDF Link:
Post a Comment