Handy MySQL Commands – Cheatsheet 2018

Designing the database is a key step, largely because changes to the database at a later date have far larger implications and potential complications than changing any other aspect of the site. Adding functionality through database changes is a steep challenge and fixing database flaws is excruciating, so make every effort you can to get the database design right the first time.

Even if your Web site will not be storing the most dangerous customer information – their credit card data – the database needs to be thoroughly protected, as the breach of any customer information is a huge business liability.

Suggested Read:

The front line of database defense is MySQL’s access privileges system. MySQL allows you to create specific users that have limited permissions on only particular databases. Users are identified by the combination of their name, password, and host (that is, which computer the user is on). To start, create unique, secure usernames with unique, extremely secure passwords. And, as with pretty much everything, it’s really best to change those passwords regularly. Also, be certain to change the root user’s password on a new MySQL installation.

Next, every MySQL user should only be able to connect to MySQL from localhost or 127.0.0.1 (that is, from the same server).

To login from a UNIX shell, the command is:

Command: mysql -h localhost -u root -p

To create a new database, the syntax is:

Command: create database [database name];

To view all databases, the command is:

Command: show databases;

You can easily switch to any database by typing the following command:

Command: use [database name];

Suppose you want a table of columns (name, email and password) then the command is:

Command: CREATE TABLE users (name VARCHAR(20), email VARCHAR(20), password VARCHAR(20));

And in case, if you want to create a column with primary key then the command is:

Command: CREATE TABLE records (id int(11) not null auto_increment primary key, firstname VARCHAR(240), middlename VARCHAR(240), lastname VARCHAR(240));

To see all tables in a particular DB, the command is:

Command: show tables;

To see all database field formats, the syntax is:

Command: describe [table name];

To delete any database, then the syntax is:

Command: drop database [database name];

And to delete any table, the command is:

Command: drop table [table name];

To insert data in particular table, the command is:

Command: INSERT into [table name] (column1, column2, column3) VALUES (‘value1’, ‘value2’, ‘value3’);

Show all data in a table, the command is:

Command: select * from [table name];

Returns the columns and column information pertaining to the designated table, the command is:

Command: Select [column name] from [table name];

Show certain selected rows with the value “whatever”, the command is:

Command: SELECT * FROM [table name] WHERE [field name] = “whatever”;

If you want to display the results which contains two values then you can use AND operator as shown below:

Command: SELECT * FROM [table name] WHERE [field name] = “whatever” AND [field name] = “something”;

To display all the records except one field value, then the command is:

Command: SELECT * FROM [table name] WHERE [field name] != “value”;

Show all the records starting from a particular name, then you can use LIKE operator:

Command: SELECT * FROM [table name] WHERE [field name] LIKE “jo%”;

To show unique records, then you can use DISTINCT operator instead of * symbol:

Command: SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in Ascending or Descending order, then the command is:

Command: SELECT [column name], [column name] FROM [table name] ORDER BY [column name] DESC;

If you want to return the total number of rows then the command is:

Command: SELECT COUNT(*) FROM [table name];

You can even perform mathematical operations inside the SQL query:

Command: SELECT SUM(id) FROM [table name];

To update any record, the command is:

Command: UPDATE [table name] SET [field name] = ‘value’ WHERE [field name] = ‘whatever’;

In case, if you want to delete any particular row then the command is:

Command: DELETE FROM [table name] WHERE [field name] = value;

And if you want to delete any particular column, then you can use ALTER command:

Command: ALTER TABLE [table name] DROP COLUMN [column name];

And if you want to add any new column in an existing table, then the command is:

Command: ALTER TABLE [table name] ADD COLUMN [column name] VARCHAR(240);

Similarly, if you want to change anything in an existing column, then the command is:

Command: ALTER TABLE [table name] CHANGE COLUMN [old column name] [new column name] VARCHAR (240);

To increase the value of any existing column, then the command is:

Command: ALTER TABLE [table name] MODIFY [column name] VARCHAR (245);

Here you can see, the VARCHAR value of column name firstname was 240 but after alteration, its 245.

To download or dump a particular database, the command is:

Command: mysqldump -u root -p –databases [database name] > /location/db.sql

To dump any particular table, then the command is:

Command: mysqldump -u root -p [database name] [table name] > /location/table.sqll

You may also like:

Related Posts