Raw Sql Queries to interact with MySql Database via Command Line
In this article you are going to learn how to use 'command line' tool for interacting with MySql, supposing we are lacking a user interface type like "PhpMyAdmin" or such. Well, it's a broad topic but focus of this article will be "Using MySql with web-Interfaces, websites, web applications".
- Install the MySQL,
- Go to Start -> Programs -> MySql -> MySQL command line client and a black window appears.
- Type your password that you've set while installing.
Now you will see the command line with this words printed "MySql>" i.e. it is waiting for user input (Queries you are going to type to interact with MySql Database). Some of the basic queries include Show, Create, Alter, Select, Insert, update, delete types.
After creating databases with 'create type' statement explained below we need to execute any of these statement for proper functionality:
or after selecting database you can type SHOW TABLES; to get the list of tables.
For more help you can type '\h' in command line without inverted commas... Hope you will enjoy while working with database.
Below are the queries you can type in command line:
Create Type: These Types of queries are used to Create database and tables in Database, for example:
CREATE DATABASE my_database_name; CREATE TABLE my_table_name
( id INT NOT NULL AUTO_INCREMENT, field TEXT);
Alter Type: These type of queries are used to alter tables mainly, for example:
ALTER TABLE my_table_name ADD field_name INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (field_name);
Select Type: These type of queries are used mainly for the purposes of data retrieval, For example:
SELECT * FROM my_table_name WHERE field_name='Some_Field_Value';
Here Field_value is referencing to a value that is already stored in database for that Field name.
Insert Type: These type are used for inserting data, for example:
INSERT INTO my_table_name(id,field) VALUES ('Integer_values','Some_text');
Update Type: These type of queries are used for updating data (rows) which are already in database, for example:
UPDATE my_table_name SET field='some_text' WHERE id='integer_value';
Delete Type: These type of queries are used for deleting rows (data) from the database, for example:
DELETE FROM my_table_name WHERE id='some integer';