DDL :
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE IF NOT EXISTS Employees (
EmployeeID INTEGER PRIMARY KEY,
FirstName TEXT NOT NULL,
LastName TEXT NOT NULL,
Email TEXT UNIQUE,
DepartmentID INTEGER,
FOREIGN KEY(DepartmentID) REFERENCES Departments(DepartmentID)
);
DROP TABLE database_name.table_name;
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00;
DELETE FROM COMPANY WHERE ID = 7; delete table's tuble
权力:
GRANT SELECT, INSERT ON table1 TO user1 WITH GRANT OPTION;
REVOKE SELECT, INSERT ON table1 FROM user1 cascade;
select :
CREATE VIEW v10BestSellingArtists AS
SELECT tmp.Artist as Artist, count(tmp. AlbumID) as TotalAlbum, IFNULL(sum(tmp.TotalTrackSales),0) as TotalTrackSales
FROM (
SELECT artists.ArtistID as ID,artists.Name as Artist,IFNULL(sum(invoice_items.Quantity),0) as TotalTrackSales,
albums.AlbumID
FROM tracks left join albums ON tracks.AlbumID=albums.AlbumID
LEFT JOIN artists ON albums.ArtistID=artists.ArtistID
LEFT JOIN invoice_items ON tracks.TrackID=invoice_items.TrackID
GROUP BY albums.ArtistID,albums.AlbumID
) as tmp
GROUP BY tmp.ID
// having
ORDER BY TotalTrackSales DESC LIMIT 10; //ASC