Finance

Sep 25 2017

MySQL Cheat Sheet #sql #absolute #value


#

(My)SQL Cheat Sheet

CREATE TABLE table (
column1type [[NOT] NULL]
[AUTO_INCREMENT],
column2type [[NOT] NULL]
[AUTO_INCREMENT],
.
other options ,
PRIMARY KEY (column(s) ) );

CREATE TABLE Students (
LastName varchar(30) NOT NULL,
FirstName varchar(30) NOT NULL,
StudentID int NOT NULL,
Major varchar(20),
Dorm varchar(20),
PRIMARY KEY (StudentID)

INSERT INTO table VALUES
(list of values );
INSERT INTO table SET
column1 =value1 ,
column2 =value2 ,
.
columnk =valuek ;
INSERT INTO table (column1 ,column2. )
VALUES (value1 ,value2. );

INSERT INTO Students VALUES
(‘Smith’,’John’,123456789,’Math’,’Selleck’);
INSERT INTO Students SET
FirstName=’John’,
LastName=’Smith’,
StudentID=123456789,
Major=’Math’;
INSERT INTO Students
(StudentID,FirstName,LastName)
VALUES (123456789,’John’,’Smith’);

INSERT INTO table (column1 ,column2. )
SELECT statement ;
(See below)

INSERT INTO Students
(StudentID,FirstName,LastName)
SELECT StudentID,FirstName,LastName
FROM OtherStudentTable;
WHERE LastName like ‘%son’;

DELETE FROM table
[WHERE condition(s) ];

(Omit WHERE to delete all data)

DELETE FROM Students
WHERE LastName=’Smith’;
DELETE FROM Students
WHERE LastName like ‘%Smith%’;
AND FirstName=’John’;
DELETE FROM Students;

UPDATE Students SET
LastName=’Jones’ WHERE
StudentID=987654321;
UPDATE Students SET
LastName=’Jones’, Major=’Theatre’
WHERE StudentID=987654321 OR
(MAJOR=’Art’ AND FirstName=’Pete’);

ALTER TABLE table ADD COLUMN
columntypeoptions ;

ALTER TABLE Students ADD COLUMN
Hometown varchar(20);


Written by admin


Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: