{"id":108,"date":"2019-12-04T10:13:34","date_gmt":"2019-12-04T10:13:34","guid":{"rendered":"http:\/\/tiruppurwebhosting.com\/blog\/?p=108"},"modified":"2019-12-04T10:26:30","modified_gmt":"2019-12-04T10:26:30","slug":"list-of-all-commands-used-in-mysql-ssh-linux-shell-in-putty","status":"publish","type":"post","link":"https:\/\/tiruppurwebhosting.com\/blog\/list-of-all-commands-used-in-mysql-ssh-linux-shell-in-putty\/","title":{"rendered":"List of all commands used in Mysql SSH Linux Shell in PUTTY"},"content":{"rendered":"

The following are the list of commands used in mysql to create an data base and manage it for Linux web server.<\/p>\n

Start by logging-in using a SSH client, like PUTTY, to access the shell command line.<\/p>\n

Before entering this command make sure your will be in<\/p>\n

mysql ><\/pre>\n
And at the same time put semi colon (; ) in the last.<\/p>\n
mysql -u [username] -p; (will prompt for password)<\/pre>\n
Access monitor-this command will access by entering<\/a> the username and password which is already created.<\/p>\n
show databases;<\/pre>\n
Show all databases-this command will show or list all the database which is created or present.<\/p>\n
mysql -u [username] -p [database];<\/pre>\n
(will prompt for password)
\nAccess database-it will access the database by entering the database name and its password.<\/p>\n
create database [database];<\/pre>\n
Create new<\/a> database-this will create a new database with assigned name.<\/p>\n
use [database];<\/pre>\n
Select database-this will select the particular database.<\/p>\n
select database();<\/pre>\n
This command is used to determine what database is<\/a> in use.<\/p>\n
grant all privileges on database name.* to username@localhost;<\/pre>\n
flush privileges;<\/pre>\n
This command is used to Give a user privileges for a database.<\/p>\n
show tables;<\/pre>\n
Show all tables.<\/p>\n
describe [table];<\/pre>\n
Show table structure.<\/p>\n
show index from [table];<\/pre>\n
List all indexes on a table.<\/p>\n
CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME);<\/pre>\n
This will create new table(assign name) with columns and create with date and time.<\/p>\n
ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120);<\/pre>\n
This will add an another column in the existing table.<\/p>\n
ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;<\/pre>\n
It is used for Adding a column with an unique, auto-incrementing ID.<\/p>\n
INSERT INTO [table] ([column], [column]) VALUES ('[value]', [value]');<\/pre>\n
It will insert a new record into the table.<\/p>\n
NOW()<\/p>\n
MySQL function for date time input.<\/p>\n
SELECT * FROM [table];<\/pre>\n
This command is used to Selecting records in the table.<\/p>\n
EXPLAIN SELECT * FROM [table];<\/pre>\n
This command will Explain records which is present in the selected table.<\/p>\n
SELECT [column], [another-column] FROM [table];<\/pre>\n
It is used for Selecting parts of records which is present in the table..<\/p>\n
SELECT COUNT([column]) FROM [table];<\/pre>\n
This command is used for Counting records in the table.<\/p>\n
SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column];<\/pre>\n
This command is used for Counting and selecting grouped records.<\/p>\n
SELECT * FROM [table] WHERE [column] LIKE '%[value]%';<\/pre>\n
This command is used to Select records containing [value] .<\/p>\n
SELECT * FROM [table] WHERE [column] LIKE '[value]%';<\/pre>\n
It is used to Select records starting with [value].<\/p>\n
SELECT * FROM [table] WHERE [column] LIKE ‘[val_ue]’;<\/p>\n
Select records starting with val and ending with ue.<\/p>\n
SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2];<\/pre>\n
This is used to Select a range in the table.<\/p>\n
UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];<\/pre>\n
This commands is used for Updating records.<\/p>\n
DELETE FROM [table] WHERE [column] = [value];<\/pre>\n
This command is used for Deleting records.<\/p>\n
DELETE FROM [table];<\/pre>\n
(This also resets the incrementing counter for auto generated columns like an id column.)<\/p>\n
Delete all records from a table (without dropping the table itself)<\/p>\n
truncate table [table];<\/pre>\n
This command will Delete all records in a table<\/p>\n
ALTER TABLE [table] DROP COLUMN [column];<\/pre>\n
Removing table columns.<\/p>\n
DROP TABLE [table];<\/pre>\n
Deleting tables.<\/p>\n
DROP DATABASE [database];<\/pre>\n
Deleting databases<\/p>\n
SELECT [column] AS [custom-column] FROM [table];<\/pre>\n
Custom column output names.<\/p>\n
mysqldump -u [username] -p [database] > db_backup.sql<\/pre>\n
Export a database dump (more info here).<\/p>\n
mysql -u [username] -p -h localhost [database] < db_backup.sql<\/pre>\n
Import a database dump (more info here)<\/p>\n
exit;<\/pre>\n
This command is used to Logout or quit from the mysql.<\/p>\n","protected":false},"excerpt":{"rendered":"
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<\/p>\n","protected":false},"author":1,"featured_media":110,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,9,3],"tags":[],"class_list":["post-108","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-basics","category-linux","category-linux-tutorials"],"_links":{"self":[{"href":"https:\/\/tiruppurwebhosting.com\/blog\/wp-json\/wp\/v2\/posts\/108","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tiruppurwebhosting.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tiruppurwebhosting.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tiruppurwebhosting.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tiruppurwebhosting.com\/blog\/wp-json\/wp\/v2\/comments?post=108"}],"version-history":[{"count":0,"href":"https:\/\/tiruppurwebhosting.com\/blog\/wp-json\/wp\/v2\/posts\/108\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/tiruppurwebhosting.com\/blog\/wp-json\/wp\/v2\/media\/110"}],"wp:attachment":[{"href":"https:\/\/tiruppurwebhosting.com\/blog\/wp-json\/wp\/v2\/media?parent=108"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tiruppurwebhosting.com\/blog\/wp-json\/wp\/v2\/categories?post=108"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tiruppurwebhosting.com\/blog\/wp-json\/wp\/v2\/tags?post=108"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}