Working with MySQL Database
Create a database with a specified name if it does not exist in database server
|
1
|
CREATE DATABASE [IF NOT EXISTS] database_name
|
Use database or change current database to another database you are working with
|
1
|
USE database_name
|
Drop a database with specified name permanently. All physical file associated with the database is no longer exists.
|
1
|
DROP DATABASE [IF EXISTS] database_name
|
Show all available databases in the MySQL database server
|
1
|
SHOW DATABASES
|
Working with Table
Lists all tables in a database.
|
1
|
SHOW TABLES
|
Create table statement that defines the structure of table .
|
1
2
3
4
5
6
|
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name(
key type(size) NOT NULL PRIMARY KEY AUTO_INCREMENT,
c1 type(size) NOT NULL,
c2 type(size) NULL,
…
) Engine= [InnoDB|MyISAM]|…]
|
Altering table structure
Actions can be one of the following actions:
|
1
|
ADD [COLUMN]
|
Add a new column into a table
|
1
|
DROP [COLUMN]
|
Drop an existing column in a table
|
1
|
ADD INDEX [name](column_name, …)
|
Add index with a specific name to a table on a column
|
1
|
DROP INDEX index_name Drop an index from a table
|
Add primary key into a tables
|
1
|
ADD PRIMARY KEY (column_name,…)
|
Remove primary key from a table
|
1
|
DROP PRIMARY KEY
|
Deleting table permanently
|
1
2
|
DROP TABLE [IF EXISTS] table_name [, name2, …]
[RESTRICT | CASCADE]
|
Get information about the table or column.
|
1
2
|
DESCRIBE table [column_name]
DESC table [column_name
|
Working with Index
Creating an index with the specified name on a table
|
1
2
|
CREATE [UNIQUE|FULLTEXT] INDEX index_name
ON table (column_name,…)
|
Removing a specified index from table
|
1
|
DROP INDEX index_name
|
Querying Data
Query complete data in a database table
|
1
|
SELECT * FROM table_name
|
Query specified data which is shown in the column list from a database table
|
1
2
|
SELECT column_name, column_name2….
FROM table_name
|
Query unique records
|
1
2
|
SELECT DISTINCT (column_name)
FROM table_name
|
Query data from multiples table using join
|
1
2
3
|
SELECT *
FROM table_name1
INNER JOIN table_name2 ON conditions
|
|
1
2
3
|
SELECT *
FROM table_name1
LEFT JOIN table_name2 ON conditions
|
|
1
2
|
SELECT *
FROM table_name1
|
Counting number of rows in a database table
|
1
2
|
SELECT COUNT (*)
FROM table_name
|
Sorting ascending or descending based on one or more columns:
|
1
2
3
|
SELECT column_name, column_name2….
FROM table_name
ORDER BY column_name ASC [DESC], column_name2 ASC [DESC],…
|
Group the retrieved rows data:
|
1
2
3
|
SELECT *
FROM table_name
GROUP BY column_name
|
Matching Data based on a pattern
Matching data using LIKE operator:
|
1
2
|
SELECT * FROM table_name
WHERE column_name LIKE ‘%value%’
|
Matching data using regular expression
|
1
2
|
SELECT * FROM table_name
WHERE column_name RLIKE ‘regular_expression’
|