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='[email protected]';
ALTER table column
ALTER TABLE `dbname`.`tbl_table` ADD COLUMN `disable` TINYINT NOT NULL DEFAULT 0 AFTER `email` ;