MySQL databases
STARTING THE COMMAND-LINE INTERFACE
o In cmd, type cd\
o Cd xampp\mysql\bin
o MySQL –uroot –p
After logging in
Type show databases;
To view all the databases.
MySQL prompt and meaning
MySQL prompt Meaning
-> Waiting for the next line of a command
‘> Waiting for the next line of a string started
with a single quote
“> Waiting for the next line of a string started
with a double quote
‘> Waiting for the next line of a string started
with a back tick
/*> Waiting for the next line of a comment
started with /*
MySQL> MySQL is ready and waiting for a
command
Canceling a command
Use \c
E.g..
‘Meaningless gibberish to mysql’ \c
Selection of mysql commands
command Parameter(s) Meaning
ALTER Database, table Alter database or table
BACKUP Table Backup table
CREATE Database, table Create database or
table
\c Cancel input
DELETE Expression with table Delete row from table
and row
DESCRIBE table Describe the table’s
columns
DROP Database, table Delete database or
table
continue
EXIT (Ctrl-C) Exit
GRANT User details Change user privileges
HELP (\h, \?) item Display help on item
INSERT Expression with data Insert data
LOCK Table(s) Lock table(s)
QUIT (\q) Same as Exit
RENAME table Rename table
SHOW Too many items to list List item’s details
Continue…
SOURCE filename Execute commands from
filename
STATUS (\s) Display current status
TRUNCATE table Empty table
UNLOCK Table(s) Unlock table(s)
UPDATE Expression with data Update an existing
record
USE database Use database
Creating a database
CREATE DATABASE database Name;
Creating users
CREATE USER ‘user1’@’localhost’ IDENTIFIED BY ‘password1’;
Granting privileges to users
GRANT ALL ON database.object TO ‘username’@’hostname’ IDENTIFIED BY
‘password’;
E.g. GRANT ALL ON student_system.student TO ‘user1’@’localhost’ IDENTIFIED BY
‘password1’;
Parameters for the GRANT command
Arguments Meaning
*.* All databases and all their objects
Database.* Only the databases called database
and all its objects
Database.object Only the database called database
and its object called object
Creating a table
Create table called classics
CREATE TABLE classics (author VARCHAR(128),
title VARCHAR(128),
type VARCHAR(128),
year CHAR(4)) ENGINE MyISAM;
Checking a new table
DESCRIBE classics
MySQL CHAR data types
MySQL BINARY data types
MySQL BLOB data types
Numeric data types
Date and time
Adding the autoincrementing column
id
ALTER TABLE classics ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY;
More on data types
Adding the autoincrement id column at
the creation
Adding data into a table
Viewing data from the table
SELECT * FROM classics;
Renaming a table
ALTER TABLE classics RENAME classics1;
Changing the data type of a column
ALTER TABLE classics MODIFY year SMALLINT;
Adding a new column
ALTER TABLE classics ADD pages SMALLINT UNSIGNED;
Renaming a column
ALTER TABLE classics CHANGE type category VARCHAR(16);
Removing a column
ALTER TABLE classics DROP pages;
Deleting a table
DROP TABLE classics;
Creating, viewing, and deleting a
table
