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.)
DataGrip (FREE for students/faculty - Get education license here)