Month: February 2017

My simply MySQL Command Line Cheatsheet · GitHub

Jovan Stosic February 23, 2017

MySQL

Getting started:

Related tutorials:

Tools:

Commands

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

Show all databases: show databases;

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

Create new database: create database [database];

Select database: use [database];

Determine what database is in use: select database();

Show all tables: show tables;

Show table structure: describe [table];

List all indexes on a table: show index from [table];

Create new table with columns: CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME);

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

Adding a column with an unique, auto-incrementing ID: ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;

Inserting a record: INSERT INTO [table] ([column], [column]) VALUES ('[value]', [value]');

MySQL function for datetime input: NOW()

Selecting records: SELECT * FROM [table];

Explain records: EXPLAIN SELECT * FROM [table];

Selecting parts of records: SELECT [column], [another-column] FROM [table];

Counting records: SELECT COUNT([column]) 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] WHERE [column] = [value]; (Selectors: <, >, !=; combine multiple selectors with AND, OR)

Select records containing [value]: SELECT * FROM [table] WHERE [column] LIKE '%[value]%';

Select records starting with [value]: SELECT * FROM [table] WHERE [column] LIKE '[value]%';

Select records starting with val and ending with ue: SELECT * FROM [table] WHERE [column] LIKE '[val_ue]';

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

Select with custom order and only limit: SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT [value];(Order: DESC, ASC)

Updating records: UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];

Deleting records: DELETE FROM [table] WHERE [column] = [value];

Delete all records from a table (without dropping the table itself): DELETE FROM [table]; (This also resets the incrementing counter for auto generated columns like an id column.)

Delete all records in a table: truncate table [table];

Removing table columns: ALTER TABLE [table] DROP COLUMN [column];

Deleting tables: 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

Use --lock-tables=false option for locked tables (more info here).

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

Logout: exit;

Aggregate functions

Select but without duplicates: SELECT distinct name, email, acception FROM owners WHERE acception = 1 AND date >= 2015-01-01 00:00:00

Calculate total number of records: SELECT SUM([column]) FROM [table];

Count total number of [column] and group by [category-column]: SELECT [category-column], SUM([column]) FROM [table] GROUP BY [category-column];

Get largest value in [column]: SELECT MAX([column]) FROM [table];

Get smallest value: SELECT MIN([column]) FROM [table];

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

Get rounded average value and group by [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 rows from different tables: SELECT * FROM [table1] INNER JOIN [table2] ON [table1].[column] = [table2].[column];

Combine rows from different tables but do not require the join condition: SELECT * FROM [table1] LEFT OUTER JOIN [table2] ON [table1].[column] = [table2].[column]; (The left table is the first table that appears in the statement.)

Rename column or table using an alias: SELECT [table1].[column] AS '[value]', [table2].[column] AS '[value]' FROM [table1], [table2];

Users functions

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

Create new user: CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Grant ALL access to user for * tables: GRANT ALL ON database.* TO 'user'@'localhost';

Find out the IP Address of the Mysql Host

SHOW VARIABLES WHERE Variable_name = 'hostname'; (source)

Source: My simply MySQL Command Line Cheatsheet · GitHub

My simply MySQL Command Line Cheatsheet · GitHub was last modified: July 13th, 2017 by Jovan Stosic

Restoring a MySQL table back to the database – Stack Overflow

Jovan Stosic February 23, 2017

I have a trouble in restoring MySQL table back to the database from command line. Taking backup of a table is working with mysqldump.Taking backup and restoring of a database is also working properly. I have used:mysql -uroot -p DatabaseName TableName < path\TableName.sql

Source: Restoring a MySQL table back to the database – Stack Overflow

Restoring a MySQL table back to the database – Stack Overflow was last modified: July 13th, 2017 by Jovan Stosic

Gil Amelio

Jovan Stosic February 23, 2017

Gilbert Frank “Gil” Amelio (born March 1, 1943) is an American technology executive. Amelio worked at Bell Labs, Fairchild Semiconductor, and the semiconductor division of Rockwell International but is best remembered as a CEO of National Semiconductor and Apple Inc.

Source: Gil Amelio – Wikipedia

Gil Amelio was last modified: September 25th, 2017 by Jovan Stosic

Creating a Favicon « WordPress Codex

Jovan Stosic February 23, 2017

A favicon (short for “favorite icon”) is an icon associated with a website or webpage intended to be used when you bookmark the web page. Web browsers use them in the URL bar, on tabs, and elsewhere to help identify a website visually. Also, it is used as application icon of mobile device.

A favicon is typically a graphic 16 x 16 pixels square and is saved as favicon.ico in the root directory of your server. You can use a favicon with any WordPress site on a web server that allows access to the root directories.

Source: Creating a Favicon « WordPress Codex

Creating a Favicon « WordPress Codex was last modified: July 13th, 2017 by Jovan Stosic

Linear Tape-Open

Jovan Stosic February 23, 2017

Linear Tape-Open (LTO) is a magnetic tape data storage technology originally developed in the late 1990s as an open standards alternative to the proprietary magnetic tape formats that were available at the time. Hewlett Packard Enterprise, IBM and Quantum control the LTO Consortium, which directs development and manages licensing and certification of media and mechanism manufacturers.

The standard form-factor of LTO technology goes by the name Ultrium, the original version of which was released in 2000 and can hold 100 GB of data in a cartridge. The seventh generation of LTO Ultrium was released in 2015 and can hold 6.0 TB in a cartridge of the same size.

Upon introduction, LTO Ultrium rapidly defined the super tape market segment and has consistently been the best-selling super tape format.[1][2] LTO is widely used with small and large computer systems, especially for backup.

Source: Linear Tape-Open – Wikipedia

Linear Tape-Open was last modified: September 25th, 2017 by Jovan Stosic

Polaroid Corporation

Jovan Stosic February 23, 2017

Polaroid is an American company that is a brand licensor and marketer of its portfolio of consumer electronics to companies that distribute consumer electronics and eyewear. It is best known for its Polaroid instant film and cameras.

The company was founded in 1937 by Edwin H. Land, to exploit the use of its Polaroid polarizing polymer.[1]:3 Land ran the company until 1981. Its peak employment was 21,000 in 1978, and its peak revenue was $3 billion in 1991.[2]

When the original Polaroid Corporation was declared bankrupt in 2001,[3][4]its brand and assets were sold off.[5] The “new” Polaroid formed as a result[3][5] itself declared bankruptcy in 2008, resulting in a further sale and the present-day Polaroid Corporation.

Source: Polaroid Corporation – Wikipedia

Polaroid Corporation was last modified: September 25th, 2017 by Jovan Stosic

Mitch Kapor

Jovan Stosic February 23, 2017

Mitchell David “Mitch” Kapor (Listeni/ˈkeɪ.pʊər/ kay-poor),[1] born November 1, 1950,[2][3] is an entrepreneur best known for promoting the first spreadsheet VisiCalc, and later founding Lotus, where he was instrumental in developing the Lotus 1-2-3 spreadsheet. He left Lotus in 1986. In 1990 with John Perry Barlow and John Gilmore, he co-founded the Electronic Frontier Foundation, and served as its chairman until 1994. Kapor has been an investor in the personal computing industry, and supporter of social causes, like the Hidden Genius Project, The College Bound Brotherhood, and Advancement Project. As Partner at Kapor Capital and the Kapor Center for Social Impact, Mitch, along with his wife Freada Kapor Klein, invests in social impact tech startups that seek to narrow gaps in opportunity and access for underrepresented communities and attempt to eliminate barriers to full participation across the tech ecosystem.

Source: Mitch Kapor – Wikipedia

Mitch Kapor was last modified: September 25th, 2017 by Jovan Stosic