How to manage MySQL user account, update records, insert records, login into SSH MySQL, and another SSH command for help to learn MySQL in SSH.
List of all commands used in SSH for creating a new database, insert the record into the selected database, Alter the table column, login database by SSH client (Putty).
Below mention basic and useful commands to maintaining SSH Client (Putty).
For the first day of class, start by login using SSH client, putty for learning and access the Shell putty line commands,
SSH Client (Putty) Commands.
For access the MySQL and open the SSH SQL Monitor/ Terminal.
mysql -u [username] -p;
or
sudo mysql -u [username] -p;
Why use Sudo. [Read Below].
[username] used when login the ssh or connect with FTP.
for Example
sudo mysql -u root -p;
Select a specifed database with SSH command and login to MySQL.
mysql -u [username] -p [database];
or
sudo mysql -u [username] -p [database];
Include Sudo in MySQL command to allow a permitted user to execute a command as the superuser or another user.
Create a new database.
create database [database_name];
and create a database with Character set utf8mb4
CREATE DATABASE wordpress CHARACTER SET utf8mb4;
MySQL Create Database keyword use for creating a specific database with a unique name in the SQL server.
Grant all permission to the particular database in MySql
grant all on database_name.* to 'database_user'@'localhost' identified by 'password';
Select database.
use [database_name];
MySQL use keyword used to select any existing (All Ready Created) database in SQL Server.
Select database() determine and find the selected name of the database which we working currently .
select database();
It helps to find out in the SSH terminal which database is currently selected and which of database user push the SSH command on database
After opening the database determine all tables, use command.
show tables;
Show tables line command use for the list of schema, name, owner, type, and row count for the table.
Show database table structure.
describe [table_name];
MySQL describes keyword use for show table information in detail. Every column and row detail will show on your screen.
List all indexes on a table:
show index from [table_name];
Create a new table with columns in the database.
CREATE TABLE [table_name] ([column_name] VARCHAR(120), [another-column] DATETIME);
Add a column in a table with the selected database.
ALTER TABLE [table_name] ADD COLUMN [column_name] VARCHAR(120);
Add a column into table with a unique, auto-incrementing ID.
ALTER TABLE [table_name] ADD COLUMN [column_name] int NOT NULL AUTO_INCREMENT PRIMARY KEY;
Insert a new record in the table by the selected database.
INSERT INTO [table_name] ([column_name], [column_name]) VALUES ('[value_defined]', '[value_define]');
Show and select all records.
SELECT * from [table_name];
Select and show some particular column in the database.
Select [column_name], [column_name] from [table_name];
Count the rows record enter in a database table.
Select COUNT([column_name]) from table;
Counting and selecting grouped records.
SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column];
Selecting specific records.
SELECT * FROM [table_name] WHERE [column] = [value]; (Selectors: <, >, !=; combine multiple selectors with AND, OR);
Select records containing the specified values.
SELECT * FROM [table_name] WHERE [column] LIKE '%[value]%';
Select records starting with [value].
SELECT * FROM [table_name] WHERE [column_name] LIKE '[value]%';
Select records starting with value and ending with the specified value.
SELECT * FROM [table_name] WHERE [column] LIKE '[val_ue]';
Select a range.
SELECT * FROM [table_name] WHERE [column] BETWEEN [value1] and [value2];
Select with a custom order and only limit.
SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT [value]; (Order: DESC, ASC)
Updating records in the table.
UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];
Deleting records entry in the table.
DELETE FROM [table] WHERE [column] = [value];
Delete all records from a table.
DELETE FROM [table];
(This also resets the incrementing counter.)
Delete all records in a table.
truncate table [table];
Removing table columns.
ALTER TABLE [table] DROP COLUMN [column];
Deleting tables in single Database.
DROP TABLE [table];
Deleting databases.
DROP DATABASE [database];
Custom column output names.
SELECT [column] AS [custom-column] FROM [table];
Export a database dump (more info here).
mysqldump -u [username] -p [database] > db_backup.sql;
Import a database dump (more info here).
mysql -u [username] -p -h localhost [database] < db_backup.sql;
Import the database into the selected database.
[database_name] < sql_file.sql;
Logout the SSH MySQL
exit;
Update the wp_options value where option value define in home and siteurl fields
first, select and open your database in putty.
UPDATE wp_options SET option_value = replace(option_value, 'http://olddomain.com', 'http://newdomain.com') WHERE option_name = 'home' OR option_name = 'siteurl';