MySQL
mysql.rc.pdx.edu
mysql.rc.pdx.edu
"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."
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.
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).
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;
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;
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;
Advanced queries
join 2 tables
group by
aggregates
Views
create view
select view
change password (admin only in MySQL)
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)