MySQL some of the basic commands

Command

Access monitor: mysql -u [username] -p; (will prompt password)

To display all databases:show databases;

Access database: mysql -u [username] -p [database] (will prompt password)

To create a new database:create database [database];

Select database:use [database];

Determine what database is used:select database();

Show All tables:show tables;

Show Table structure:describe [table];

All indexes of a table in the list:show index from [table];

To create a new table for a column:CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME);

Add a column:ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120);

The Add column has a unique auto-increment ID:ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;

Insert a record:INSERT INTO [table] ([column], [column]) VALUES (‘[value]‘, [value]‘);

mysql function input datetime:NOW()

To view records:SELECT * FROM [table];

Description Record:EXPLAIN SELECT * FROM [table];

Select the Record section:SELECT [column], [another-column] FROM [table];

Count Record:SELECT COUNT([column]) FROM [table];

Count and select Grouped records:SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column];

Select a specific record: SELECT * FROM [table] WHERE [column] = [value]; (selector: < , > , != , merging multiple selections with AND , OR )

Records [value] with Selections :SELECT * FROM [table] WHERE [column] LIKE ‘%[value]%‘;

the first is the selected record [value] :SELECT * FROM [table] WHERE [column] LIKE ‘[value]%‘;

the first is to select records val and End ue :SELECT * FROM [table] WHERE [column] LIKE ‘[val_ue]‘;

Select a range:SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2];

with customer orders and only restricted options: SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT [value]; (sort: DESC , ASC )

Update record:UPDATE [table] SET [column] = ‘[updated-value]‘ WHERE [column] = [value];

To delete a record:DELETE FROM [table] WHERE [column] = [value];

Delete All Records from a table (do not delete the table itself): DELETE FROM [table]; ( This will also reset the auto-generated, just like an ID column column increment counter.) )

To delete all records in a table:truncate table [table];

To delete a table column:ALTER TABLE [table] DROP COLUMN [column];

To delete a table:DROP TABLE [table];

To delete a database:DROP DATABASE [database];

Custom column Output Name:SELECT [column] AS [custom-column] FROM [table];

Export a database dump (more information click here )mysqldump -u [username] -p [database] > db_backup.sql

Use --lock-tables=false the Lock table (more information options here ).

Import a database dump (more information click here )mysql -u [username] -p -h localhost [database] < db_backup.sql

Log out:exit;

Aggregation Functions

Select But not repeat:SELECT distinct name, email, acception FROM owners WHERE acception = 1 AND date >= 2015-01-01 00:00:00

Calculate Total Records:SELECT SUM([column]) FROM [table];

count the totals. [column] by and Groups [category-column] :SELECT [category-column], SUM([column]) FROM [table] GROUP BY [category-column];

get the most value [column] :SELECT MAX([column]) FROM [table];

Get minimum Value:SELECT MIN([column]) FROM [table];

Get average:SELECT AVG([column]) FROM [table];

find rounded averages and groups [category-column] :SELECT [category-column], ROUND(AVG([column]), 2) FROM [table] GROUP BY [category-column];

Multiple Tables

Select from multiple tables:SELECT [table1].[column], [table1].[another-column], [table2].[column] FROM [table1], [table2];

Combine different table rows:SELECT * FROM [table1] INNER JOIN [table2] ON [table1].[column] = [table2].[column];

merge rows from different tables, but no connection conditions are required: SELECT * FROM [table1] LEFT OUTER JOIN [table2] ON [table1].[column] = [table2].[column]; (the left table is the first table that appears in a statement.) )

using rename columns or tables the alias :SELECT [table1].[column] AS ‘[value]‘, [table2].[column] AS ‘[value]‘ FROM [table1], [table2];

User Function

List all users:SELECT User,Host FROM mysql.user;

To create a new user:CREATE USER ‘username‘@‘localhost‘ IDENTIFIED BY ‘password‘;

Grant ALL Access users to * table:GRANT ALL ON database.* TO ‘user‘@‘localhost‘;

find the IP address of the MySQL host

SHOW VARIABLES WHERE Variable_name = ‘hostname‘;

From: Https://gist.github.com/hofmannsven/9164408#commands

MySQL some of the basic commands