Tutorials

1. How to create a new database?

CREATE DATABASE creates a database with the given name. To use this statement, you need to have the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE.

Syntax:

CREATE {DATABASE SCHEMA} [IF NOT EXISTS] db_name
[create_specification [create_specification] ...] create_specification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

Example:

mysql> create database sig;

Query OK, 1 row affected (0.01 sec)

This creates a new database sig.

2). How to set a database as the active database?


The USE command is used to select a database as the active database.

Syntax: USE db_name

mysql> use sig;

Database changed

This sets the databse sig as the current database,

3). How to create new tables on a database ?


The CREATE TABLE command is used to create a new table.

Syntax:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

(create_definition,...)

[table_option ...]

[partition_options]

Example:

mysql> create table t1(a int);
Query OK, 0 rows affected (0.08 sec)

This creates the table t1 with a integer column labeled a.

4). How to insert rows into a table?


The INSERT command is used to add rows to a table.

Syntax:

INSERT [LOW_PRIORITY
DELAYED
HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr
DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

Example:

mysql> insert t1 set a= 1;
Query OK, 1 row affected (0.03 sec)

This adds a new rown with a = 1 to table t1.

5). How to display the contents of a table?


The SELECT command is used to display the rows of the table.

Syntax:

SELECT
[ALL
DISTINCT
DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE
SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

select_expr, ...
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name
expr
position}
[ASC
DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name
expr
position}
[ASC
DESC], ...]
[LIMIT {[offset,] row_count
row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' export_options
INTO DUMPFILE 'file_name'
INTO @var_name [, @var_name]]
[FOR UPDATE
LOCK IN SHARE MODE]]

SELECT is used to retrieve rows selected from one or more tables, and n include UNION statements and subqueries. See [HELP UNION], and ttp://dev.mysql.com/doc/refman/5.1/en/subqueries.html.

mysql> select * from t1;

+------+
a
+------+
1
2
3
4
+------+

4 rows in set (0.00 sec)

Note: sel * from t1; wouldn’t work on MYSQL.