A database system.
$ mysql
or
$ mysql -u username -p
Enter password:
It's a good practice to setup different users with access to specific databases. N.B. Don't use the root account for user databases.
Let's assign a unique password to the root user:
$ mysql -u root -p
mysql> SET PASSWORD FOR root@localhost = PASSWORD('root');
See SET PASSWORD for further details.
# Showing the databases
mysql> SHOW DATABASES;
# Creating a database
mysql> CREATE DATABASE blog;
# Using a specific database
mysql> USE blog;
# Showing the tables in the database
mysql> SHOW TABLES;
# Creating a table
mysql> CREATE TABLE users(
-> id INT AUTO_INCREMENT,
-> first_name varchar(50) NOT NULL,
-> last_name varchar(50) NOT NULL,
-> email_address varchar(100) NOT NULL,
-> PRIMARY KEY (id)
-> );
# Viewing the structure of a table, i.e. it's schema
mysql> DESCRIBE users;
# Selecting all rows from a table
mysql> SELECT * FROM users;
mysql> INSERT INTO users VALUES(null, 'John', 'Doe', 'john@doe.com');
or
mysql> INSERT INTO users (first_name, last_name, email_address) VALUES('John', 'Doe', 'john@doe.com');
mysql> SELECT email_address FROM users;
mysql> SELECT first_name, last_name FROM users;
Ordering your selections:
mysql> SELECT * FROM users ORDER BY last_name ASC;
mysql> SELECT * FROM users ORDER BY last_name DESC;
Limiting the number of rows returned:
mysql> SELECT * FROM users LIMIT 2;
mysql> DELETE FROM users WHERE [condition];
mysql> DELETE FROM users WHERE id = 123;
mysql> DELETE FROM users WHERE last_name = 'Crooks';
To update a row in a table we use the UPDATE query.
mysql> UPDATE users SET last_name = 'Alexander' WHERE first_name = 'Jane';
To change a column name and type in a table use the ALTER query.
mysql> ALTER TABLE users CHANGE last_name surname varchar(80);