- starting with mysql
mysql -u root -p
- starting with mysql
mysql -u root
-p
sudo synaptic
then search for mysql, make sure you have the server selected, click
apply.
command for creating a database create database
;
Example: create database bank;
command for list all the available databases
show databases;
command for selecting particular database
use
;
ex:
use bank;
http://www.tech-recipes.com/category/database/mysql/
A primary key uniquely identify a row in a table. One or more
columns may be identified as the primary key. The values in a single
column used as the primary key must be unique (like a person’s social
security number). When more than one column is used, the combination of
column values must be unique.
When creating the contacts table described in
Create
a basic MySQL table, the column
contact_id can be
made a primary key using
PRIMARY KEY(contact_id) as
with the following SQL command:
CREATE TABLE `test1` (
contact_id INT(10),
name VARCHAR(40),
birthdate DATE,
PRIMARY KEY (contact_id)
);
Additional columns can be identified as part of the primary key with a
comma separated list in the PRIMARY KEY command,
like PRIMARY
KEY (contact_id, name).
A primary key is used to uniquely identify each row in a table. It
can
either be part of the actual record itself , or it can be an
artificial field (one that has nothing to do with the actual record).
A primary key can consist of one or more fields on a table. When
multiple
fields are used as a primary key, they are called a composite key.
Primary keys can be specified either when the table is
created
(using
CREATE
TABLE) or
by changing the existing table structure (using
ALTER TABLE).
Below are examples for specifying a primary key when creating
a table:
MySQL:
CREATE TABLE Customer
(SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (SID));
Oracle:
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));
SQL Server:
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));
Below are examples for specifying a primary key by altering a
table:
MySQL:
ALTER TABLE Customer ADD PRIMARY KEY
(SID);
Oracle:
ALTER TABLE Customer ADD PRIMARY KEY
(SID);
SQL Server:
ALTER TABLE Customer ADD PRIMARY KEY
(SID);
Note: Before using the ALTER TABLE command to add a primary
key, you'll need
to make sure that the field is defined as 'NOT NULL' -- in other
words,
NULL cannot be an accepted value for that field.
This slick MySQL syntax allows you to increment or decrement an
existing number in a table without first having to read the value. This
is a nice way to increment an access counter.
To increment the value ‘counter’ by one for the row in table ‘images’
where ‘image_id’ is ‘15′, use:
UPDATE images SET counter=counter+1 WHERE image_id=15
how to get system time and date in c
http://www.intechgrity.com/automatically-insert-current-date-and/
mysql> insert into main_tab values(1,1,'suni','f','cpgm',CURDATE(),1000.00);
Query OK, 1 row affected (0.00 sec)
mysql> select * from main_tab;
+----+--------+------+--------+----------+------------+---------+
| id | acc_no | name | gender | password | doj | amount |
+----+--------+------+--------+----------+------------+---------+
| 1 | 1 | suni | f | cpgm | 2010-11-19 | 1000.00 |
+----+--------+------+--------+----------+------------+---------+
1 row in set (0.00 sec)
mysql> insert into main_tab values(2,2,'prem','m','welcome',now(),1000.00);Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from main_tab;
+----+--------+------+--------+----------+------------+---------+
| id | acc_no | name | gender | password | doj | amount |
+----+--------+------+--------+----------+------------+---------+
| 1 | 1 | suni | f | cpgm | 2010-11-19 | 1000.00 |
| 2 | 2 | prem | m | welcome | 2010-11-19 | 1000.00 |
+----+--------+------+--------+----------+------------+---------+
2 rows in set (0.00 sec)
add new column in to an existing mysql table
Drop Column:
alter table icecream drop column flavor ;
Drop Column is used to remove an entire column and all its data from a table.
Add Column:
alter table icecream add column flavor varchar (20) ;
mysql> desc deposit;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| acc_no | int(5) | YES | | NULL | |
| amount | float(10,2) | YES | | NULL | |
| ddate | date | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table deposit add column flag char(1);
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc deposit;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| acc_no | int(5) | YES | | NULL | |
| amount | float(10,2) | YES | | NULL | |
| ddate | date | YES | | NULL | |
| flag | char(1) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> desc main_tab;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| acc_no | int(5) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| gender | varchar(6) | YES | | NULL | |
| password | varchar(10) | YES | | NULL | |
| doj | date | YES | | NULL | |
| amount | float(10,2) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> alter table main_tab add column ldate date;
Query OK, 30 rows affected (0.07 sec)
Records: 30 Duplicates: 0 Warnings: 0
mysql> desc main_tab;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| acc_no | int(5) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| gender | varchar(6) | YES | | NULL | |
| password | varchar(10) | YES | | NULL | |
| doj | date | YES | | NULL | |
| amount | float(10,2) | YES | | NULL | |
| ldate | date | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
how to change/modify particular column value in mysql table
mysql> select * from deposit;
+--------+---------+------------+------+
| acc_no | amount | ddate | flag |
+--------+---------+------------+------+
| 1 | 2000.00 | 2010-11-26 | d |
| 1 | 2000.00 | 2010-11-26 | d |
| 2 | 3000.00 | 2010-11-26 | d |
| 3 | 2000.00 | 2010-11-26 | d |
+--------+---------+------------+------+
4 rows in set (0.00 sec)
mysql> update deposit set amount=5000.00 where acc_no=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from deposit;
+--------+---------+------------+------+
| acc_no | amount | ddate | flag |
+--------+---------+------------+------+
| 1 | 2000.00 | 2010-11-26 | d |
| 1 | 2000.00 | 2010-11-26 | d |
| 2 | 5000.00 | 2010-11-26 | d |
| 3 | 2000.00 | 2010-11-26 | d |
+--------+---------+------------+------+
4 rows in set (0.00 sec)
