| Login to MySQL monitor |
..\mysql\bin\mysql -u[username] -p[password]
Example:
..\mysql\bin\mysql -uroot -pmysecret
|
| Show all databases |
SHOW DATABASES;
|
| Create a database called demodb. |
CREATE DATABASE [database_name]
Example:
CREATE DATABASE demodb
|
| Select a database |
USE [database_name]
Example:
USE demodb
|
| Create a table called user |
CREATE TABLE [table_name] (
[column_name1] INT AUTO_INCREMENT,
[column_name2] VARCHAR(30) NOT NULL,
[column_name3] ENUM('guest', 'customer', 'admin') NULL,
[column_name4] DATE NULL,
[column_name5] VARCHAR(30) NOT NULL,
[column_name6] DATETIME NOT NULL,
[column_name7] CHAR(1) NULL,
[column_name8] BLOB NULL,
[column_name9] TEXT NOT NULL,
UNIQUE(username),
PRIMARY KEY (column_name1)
);
Example:
CREATE TABLE user (
userid INT AUTO_INCREMENT,
username VARCHAR(30) NOT NULL,
group_type ENUM('guest', 'customer', 'admin') NULL,
date_of_birth DATE NULL,
password VARCHAR(30) NOT NULL,
registration_date DATETIME NOT NULL,
account_disable CHAR(1) NULL,
image BLOB NULL,
comment TEXT NOT NULL,
UNIQUE(username),
PRIMARY KEY (userid)
);
|
| Insert two records in table user. |
INSERT INTO [table_name] (
[column_name2], [column_name3],
[column_name4], [column_name5],
[column_name6], [column_name7],
[column_name8], [column_name9])
VALUES ('johndoe', 'guest',
'1970-06-30', 'mysceret',
NOW(), 'n',
null, 'no comment'
);
Example record 1:
INSERT INTO user (
username, group_type,
date_of_birth, password,
registration_date, account_disable,
image, comment)
VALUES ('johndoe', 'guest',
'1970-06-30', 'mysceret',
NOW(), 'n',
null, 'no comment'
);
Example record 2:
INSERT INTO user (
username, group_type,
date_of_birth, password,
registration_date, account_disable,
image, comment)
VALUES ('jimsmith', 'admin',
'1969-03-15', 'bigsceret',
NOW(), 'n',
null, 'no comment'
);
|
| Update a record in table user. |
UPDATE [table_name]
SET [column_name4] = '1980-06-30',
[column_name3] = 'customer'
WHERE [column_name2] = 'johndoe';
Example:
UPDATE user
SET date_of_birth = '1980-06-30',
group_type = 'customer'
WHERE username = 'johndoe';
|
| Add a new column "male" in table user. |
ALTER TABLE [table_name]
ADD COLUMN [column_name]
CHAR(1) NOT NULL;
Example:
ALTER TABLE user
ADD COLUMN male
CHAR(1) NOT NULL;
|
|
Change column name "male" into "gender" in table user and
change the type to VARCHAR(3) and allow NULL values.
|
ALTER TABLE [table_name]
CHANGE [old_column] [new_column]
VARCHAR(3) NULL;
Example:
ALTER TABLE user
CHANGE male gender
VARCHAR (3) NULL;
|
| Change the size of column "gender" from 3 to 6 in table user. |
ALTER TABLE [table_name]
MODIFY [column_name] VARCHAR(6);
Example:
ALTER TABLE user
MODIFY gender VARCHAR(6);
|
| Remove column "gender" in table user. |
ALTER TABLE [table_name]
DROP COLUMN [column_name];
Example:
ALTER TABLE user
DROP COLUMN gender;
|
|
All values in column "username" are unique in table user.
Remove the uniqueness of this column.
|
ALTER TABLE [table_name]
DROP INDEX [column_name];
Example:
ALTER TABLE user
DROP INDEX username;
|
| Delete table user. |
DROP TABLE [table_name];
Example:
DROP TABLE user;
|
| Delete database demodb. |
DROP DATABASE [database_name];
Example:
DROP DATABASE demodb;
|
| Show all tables in the selected database. |
SHOW TABLES;
|
| Show field formats of the selected table. |
DESCRIBE [table_name];
Example:
DESCRIBE mos_menu;
|
| Show all records of the selected table. |
SELECT *
FROM [table_name];
Example:
SELECT *
FROM mos_menu;
|
|
Show all records from mos_menu table containing name "Home".
|
SELECT *
FROM [table_name]
WHERE [field_name]=[value];
Example:
SELECT *
FROM mos_menu
WHERE name = "Home";
|
|
Show all records from mos_menu table containing name "Home" and id number 11.
|
SELECT *
FROM [table_name]
WHERE [field_name1]=[value1]
AND [field_name2]=[value2];
Example:
SELECT *
FROM mos_menu
WHERE name = "Home"
AND id=11;
|
Show all records from mos_menu table containing menutype "mainmenu" and
not containing type "components".
The displayed records are ordered by the name field.
|
SELECT *
FROM [table_name]
WHERE [field_name1]=[value1]
AND [field_name2]!=[value2]
ORDER BY [field_name3];
Example:
SELECT *
FROM mos_menu
WHERE menutype="mainmenu"
AND type !="components"
ORDER BY name;
|
|
Show all records from mos_menu table where name starts with the letters "Mambo".
|
SELECT *
FROM [table_name]
WHERE [field_name] LIKE "[value1]";
Example:
SELECT *
FROM mos_menu
WHERE name LIKE "Mambo%";
|
|
Rename database
|
- cd C:\Tools\mysql\bin
- Backup the old database:
mysqldump --allow-keywords --opt -uroot -pmysecret db_old > db_old.sql
- Create the new database:
mysqladmin -uroot -pmysecret create db_new
- Restore backup:
mysql -uroot -pmysecret db_new < db_old.sql
|
