Post by ed on Jul 8, 2019 21:54:11 GMT
Some tips on MySQL usage! (ripped straight from the Nixon that was textbook shared in Mastodon)
All commands must end with a semicolon. You’ll be in a “>” prompt until you do.
Formally, columns are called fields, rows are called records.
Command to log in with default user:
$ mysql -u root
subsequent logins can just do:
$ mysql
showing all databases:
$ SHOW databases;
Backslash Commands
\c Cancel input while typing it
\q (or EXIT or Ctrl-C) Exit
\h (or \? or HELP) Display help
\s (or \s or STATUS) Display the current status
Common database commands
Create a database called "publications"
$ CREATE DATABASE publications;
To use the database
$ USE publications;
To delete a database
$ DROP DATABASE publications;
To create a user and also grant access privileges. pseudo cmd:
$ GRANT <privileges> ON <database>.<table> TO <username>@<hostname> IDENTIFIED BY '<password>';
Omit "IDENTIFIED BY ‘<password>’" to not set a pw.
a user who can access all a db's objects would use a glob. same for all dbs "*.*":
$ GRANT ALL ON publications.* TO 'jim'@'localhost' IDENTIFIED BY 'mypasswd';
To test this, try 'quit' then log back in MySQL as user:
$ mysql -u jim -p
Remember "\s" for status.
you can GRANT and REVOKE permissions
To create a table called “classics” with various columns. The engine is optional:
`
CREATE TABLE classics (
author VARCHAR(128),
title VARCHAR(128),
type VARCHAR(16),
year CHAR(4)
) ENGINE InnoDB;
`
To see what tables are in a db:
$ SHOW TABLES IN publications;
Describe a table with:
$ DESCRIBE classics;
or DESC
Rename a table:
$ ALTER TABLE classics RENAME pre1900;
Data Types
Numerical Data Types
KEY
NOT NULL
and
AUTO_INCREMENT
Remove the column “id”:
$ ALTER TABLE classics DROP id;
Manually add some data to the table "classics", in this case books:
$ ...
INSERT INTO classics(author, title, type, year)
VALUES('Mark Twain','The Adventures of Tom Sawyer','Fiction','1876');
$ INSERT INTO classics(author, title, type, year)
VALUES('Jane Austen','Pride and Prejudice','Fiction','1811');
$ INSERT INTO classics (author, title, type, year)
VALUES ('Frank Herbert', 'Dune', 'Fiction', '1965');
Display all of the table’s contents:
$ SELECT * FROM classics;
Changing the data type of a column with ALTER and MODIFY. change the column "year" from CHAR(4) to SMALLINT:
$ ALTER TABLE classics MODIFY year SMALLINT;
Adding a new column called “pages”, value up to 65,535:
$ ALTER TABLE classics ADD pages SMALLINT UNSIGNED;
Renaming a column from "type" to "category":
$ ALTER TABLE classics CHANGE type category VARCHAR(16);
you need to specify the field type again
Removing a column:
$ ALTER TABLE classics DROP pages;
Deleting a table. let’s make a new table, verify, then delete it:
$ CREATE TABLE disposable(trash INT);
$ DESCRIBE disposable;
$ DROP TABLE disposable;
$ SHOW TABLES;
To select just the columns 'author' and 'title' from 'classics':
$ SELECT author, title FROM classics;
to select 'title' and 'isbn' from 'classics':
$ SELECT title, isbn FROM classics;
To delete all tables (beware!):
$ DROP TABLES;