Once you get connected with the MySQL server, it is required to select a database to work with. This is because there might be more than one database available with the MySQL Server. Show Selecting MySQL Database from the Command PromptIt is very simple to select a database from the mysql> prompt. You can use the SQL command use to select a database. ExampleHere is an example to select a database called TUTORIALS − [root@host]# mysql -u root -p Enter password:****** mysql> use TUTORIALS; Database changed mysql> Now, you have selected the TUTORIALS database and all the subsequent operations will be performed on the TUTORIALS database. NOTE − All the database names, table names, table fields name are case sensitive. So you would have to use the proper names while giving any SQL command. Selecting a MySQL Database Using PHP ScriptPHP uses mysqli_select_db function to select the database on which queries are to be performed. This function takes two parameters and returns TRUE on success or FALSE on failure. Syntaxmysqli_select_db ( mysqli $link , string $dbname ) : bool
ExampleTry the following example to select a database − Copy and paste the following example as mysql_example.php − <html> <head> <title>Selecting MySQL Database</title> </head> <body> <?php $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'root@123'; $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Could not connect: ' . mysqli_error($conn)); } echo 'Connected successfully<br />'; $retval = mysqli_select_db( $conn, 'TUTORIALS' ); if(! $retval ) { die('Could not select database: ' . mysqli_error($conn)); } echo "Database TUTORIALS selected successfully\n"; mysqli_close($conn); ?> </body> </html> OutputAccess the mysql_example.php deployed on apache web server and verify the output. Database TUTORIALS selected successfully 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 How do I find my local MySQL database?In order to access your MySQL database, please follow these steps:. Log into your Linux web server via Secure Shell.. Open the MySQL client program on the server in the /usr/bin directory.. Type in the following syntax to access your database: $ mysql -h {hostname} -u username -p {databasename} Password: {your password}. How do I view all SQL databases?To view a list of databases on an instance of SQL Server. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance. To see a list of all databases on the instance, expand Databases.
|