Handy MySQL Commands
Description
Command
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;
Count rows.
SELECT COUNT(*) FROM [table name];
Join tables on common columns.
select lookup.illustrationid, lookup.personid,person.birthday from lookup Show 13.7.7.14 SHOW DATABASES Statement You see only those databases for which you have some kind of privilege, unless you have the global If the server was started with the MySQL implements databases as directories in the data directory, so this statement simply lists directories in that location. However, the output may include names of directories that do not correspond to actual databases. Database information is also available from the Caution Because any static global privilege is considered a privilege
for all databases, any static global privilege enables a user to see all database names with How do I view a specific database?This statement returns the database whose schema name starts with "s": mysql>SELECT schema_name FROM information_schema.. mysql> SHOW DATABASES LIKE pattern;. mysql> SHOW DATABASES WHERE expression;. How do I switch to a specific database in MySQL?You can use the SQL command use to select a database.. Example. Here is an example to select a database called TUTORIALS − [root@host]# mysql -u root -p Enter password:****** mysql> use TUTORIALS; Database changed mysql> ... . Syntax. mysqli_select_db ( mysqli $link , string $dbname ) : bool. ... . Example. ... . Output.. How do I list a database in MySQL?To list all databases on a MySQL server host, you use the SHOW DATABASES command as follows:. SHOW DATABASES; ... . >mysql -u root -p Enter password: ********** mysql>. How do I select a specific database in SQL?The use command is used when there are multiple databases in the SQL and the user or programmer specifically wants to use a particular database. Thus, in simple terms, the use statement selects a specific database and then performs operations on it using the inbuilt commands of SQL.
|
