learnSQL/sql.md

Create Table

CREATE TABLE `testing` (
  `PID` int(11) NOT NULL,
  `Name` tinytext NOT NULL,
  `Position` tinytext NOT NULL,
  `Pay` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Generically:

CREATE TABLE `<tablename>` (
  `<colname>` <type> NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Example

CREATE TABLE `Employee` (
  `EmpID` int(11) NOT NULL,
  `EmpName` tinytext NOT NULL,
  `DeptID` int(11) NOT NULL
) DEFAULT CHARSET=utf8

Insert Data

INSERT INTO `Employee` (`EmpID`, `EmpName`, `DeptID`) VALUES ('1234', 'Becky', '1');

Generically:

INSERT INTO `<tablename>` (
  `<colID`, `colID`, `<colID`
) VALUES (
  '1234', 'Becky', '1'
);

Example

INSERT INTO `Employee` (
  `EmpID`, `EmpName`, `DeptID`
) VALUES (
  '2345', 'Molly', '2'
);
INSERT INTO `Employee` (
  `EmpID`, `EmpName`, `DeptID`
) VALUES (
  '1324', 'Ted', '2'
);

EmpID	EmpName	DeptID
1234	Becky	1
2345	Molly	2
1324	Ted	2

Multiple Inserts

INSERT INTO `Employee` (`EmpID`, `EmpName`, `DeptID`) VALUES
(1234, 'Becky', 1),
(2345, 'Molly', 2),
(1324, 'Ted', 2);
COMMIT;

View Table

SELECT * FROM `Employee`

Generically:

SELECT * FROM `<tablename>`