MySQL

mysql.rc.pdx.edu

MySQL Overview

"MySQL powers the most demanding Web, E-commerce, SaaS and Online Transaction Processing (OLTP) applications. It is a fully integrated transaction-safe, ACID compliant database with full commit, rollback, crash recovery and row level locking capabilities. MySQL delivers the ease of use, scalability, and performance to power Facebook, Google, Twitter, Uber, and Booking.com."

How to Connect

1. Open the terminal on your machine (or remote connection to RC System) and run the following command (substituting the appropriate user and database):

$ mysql -h mysql.rc.pdx.edu -u username -p databaseName

Enter password:

...

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 386255

Server version: 5.5.62 MySQL Community Server (GPL) by Remi


Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

...

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql>

Note: If the command hangs (does NOT prompt for password), you probably need to connect to the VPN first.

      • Full Tunnel VPN: vpn.pdx.edu/full is required for certain systems.

Basic Commands and Usage

Some commands have MySQL specific syntax (management, navigation, etc.).

General SQL commands (i.e. queries) should be very similar, if not identical across database management systems (i.e. PostgreSQL).


Database Management (MySQL commands)

    • Show all databases:

mysql> show databases;

    • Choose a database to use (to change databases, or if not specified in connection command)

mysql> use databaseName;

    • Show all tables in database:

mysql> show tables;

    • Describe a table:

mysql> describe tableName;

Table Management (SQL Commands)

CREATE, INSERT, UPDATE, DELETE, TRUNCATE, DROP

    • Create a new table (in current database):

CREATE TABLE products (

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

item TEXT NOT NULL,

quantity INT,

weight FLOAT

);

    • Insert new row(s) into table:

INSERT INTO products(item, quantity, weight)

VALUES('apple', 25, 0.3),

('orange', 12, 0.5),

('lemon', 30, 0.2);

    • Update row(s) in table:

UPDATE products SET weight = 0.4

WHERE item = 'apple';

    • Delete row(s) in table:

DELETE FROM products WHERE id = 1;

    • Remove all rows from table:

TRUNCATE TABLE products;

    • Delete entire table from database:

DROP TABLE products;

Common Queries (SQL Commands)

SELECT, WHERE, ORDER BY, LIMIT

The basic SELECT command is formatted like this:

SELECT column, another-column, third-column

FROM tableName

WHERE some-column = value;

Examples:

    • Show all rows/columns in table:

SELECT * FROM products;

    • Show specific attributes (columns):

SELECT id, item FROM products;

    • Count rows in table:

SELECT count(*) FROM products;

    • Sort rows by column (ascending):

SELECT * FROM products ORDER BY weight ASC;

    • Sort rows by column (descending):

SELECT * FROM products ORDER BY weight DESC;

    • Limit results (number of rows):

SELECT * FROM products LIMIT 2;

    • Filter results using predicates (WHERE clause):

SELECT * FROM products WHERE weight > 0.25;

WORK IN PROGRESS (check back soon)

Advanced queries

      • join 2 tables

      • group by

      • aggregates

Views

      • create view

      • select view

change password (admin only in MySQL)

Related Articles

MySQL Command Cheat sheets

SQL Practice Tutorials

GUI Tools (Don't want to use the command line? Try these graphical applications.)