MySQL

 
 
MySQL is a fast, multi-threaded, multi-user open source SQL database server.

The MySQL database is released under the GNU General Public License (GPL). MySQL can be used for free when you are not distributing any part of the MySQL system. For example: If you run a commercial web site using the MySQL Database Software as long as MySQL is not distributed.

Please read the MySQL manual for situations where a commercial license is needed.

More information about MySQL can be found at: https://www.mysql.com

MySQL online manual can be found at: https://dev.mysql.com/doc/index.html

The latest MySQL version can be downloaded from: https://dev.mysql.com/downloads/index.html







MySQL commands.



Information
none

Operating system used
Windows XP Home Edition Version 5.1 SP 2

Software prerequisites
MySQL 3.23 or higher

Description Command
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