The following are the list of commands used in mysql to create an data base and manage it for Linux web server.
Start by logging-in using a SSH client, like PUTTY, to access the shell command line.
Before entering this command make sure your will be in
mysql >
And at the same time put semi colon (; ) in the last.
mysql -u [username] -p; (will prompt for password)
Access monitor-this command will access by entering the username and password which is already created.
show databases;
Show all databases-this command will show or list all the database which is created or present.
mysql -u [username] -p [database];
(will prompt for password)
Access database-it will access the database by entering the database name and its password.
create database [database];
Create new database-this will create a new database with assigned name.
use [database];
Select database-this will select the particular database.
select database();
This command is used to determine what database is in use.
grant all privileges on database name.* to username@localhost;
flush privileges;
This command is used to Give a user privileges for a database.
show tables;
Show all tables.
describe [table];
Show table structure.
show index from [table];
List all indexes on a table.
CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME);
This will create new table(assign name) with columns and create with date and time.
ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120);
This will add an another column in the existing table.
ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;
It is used for Adding a column with an unique, auto-incrementing ID.
INSERT INTO [table] ([column], [column]) VALUES ('[value]', [value]');
It will insert a new record into the table.
NOW()
MySQL function for date time input.
SELECT * FROM [table];
This command is used to Selecting records in the table.
EXPLAIN SELECT * FROM [table];
This command will Explain records which is present in the selected table.
SELECT [column], [another-column] FROM [table];
It is used for Selecting parts of records which is present in the table..
SELECT COUNT([column]) FROM [table];
This command is used for Counting records in the table.
SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column];
This command is used for Counting and selecting grouped records.
SELECT * FROM [table] WHERE [column] LIKE '%[value]%';
This command is used to Select records containing [value] .
SELECT * FROM [table] WHERE [column] LIKE '[value]%';
It is used to Select records starting with [value].
SELECT * FROM [table] WHERE [column] LIKE '[val_ue]';
Select records starting with val and ending with ue.
SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2];
This is used to Select a range in the table.
UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];
This commands is used for Updating records.
DELETE FROM [table] WHERE [column] = [value];
This command is used for Deleting records.
DELETE FROM [table];
(This also resets the incrementing counter for auto generated columns like an id column.)
Delete all records from a table (without dropping the table itself)
truncate table [table];
This command will Delete all records in a table
ALTER TABLE [table] DROP COLUMN [column];
Removing table columns.
DROP TABLE [table];
Deleting tables.
DROP DATABASE [database];
Deleting databases
SELECT [column] AS [custom-column] FROM [table];
Custom column output names.
mysqldump -u [username] -p [database] > db_backup.sql
Export a database dump (more info here).
mysql -u [username] -p -h localhost [database] < db_backup.sql
Import a database dump (more info here)
exit;
This command is used to Logout or quit from the mysql.
