Manipulate Data in Database

Create table

To create a table to store data

CREATE TABLE tbl_users(userID int(10), firstName char(20), lastName char(20), userName char(20), password char(50));

Insert values(data) into the new table

INSERT INTO tbl_users VALUE(1, "Peter", "Bitshop", "Peter.B", "12345678");

Show the values in the table

SELECT * FROM tbl_user;

Delete a set of value from the table

DELETE FROM tbl_user WHERE userID = 1;
or
DELETE FROM tbl_user WHERE firstName = "Peter";

Update value for a field

UPDATE tbl_user SET password="88888888", firstName = "Walter" WHERE userID=1;

.
.

A more appropriate way to create table and insert data

Create Table

CREATE  TABLE `dbName`.`tbl_users` (

`id` INT NOT NULL AUTO_INCREMENT ,

`firstname` VARCHAR(45) NULL DEFAULT 'firstname' ,

`lastname` VARCHAR(45) NULL DEFAULT 'lastname' ,

`loginname` VARCHAR(45) NOT NULL ,

`password` VARCHAR(45) NOT NULL ,

`email` VARCHAR(45) NOT NULL ,

PRIMARY KEY (`id`, `loginname`) ,

UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,

 UNIQUE INDEX `loginname_UNIQUE` (`loginname` ASC) );

Insert data

INSERT INTO tbl_users SET firstname='Mac', lastname='Tayler', loginname='mac.tayler', password='12345678', email='mactayler@domain.com';

ALTER table column

ALTER TABLE `dbname`.`tbl_table` ADD COLUMN `disable` TINYINT NOT NULL DEFAULT 0  AFTER `email` ;