CREATE TABLE name (column name 1 data type, column name 2 data type, column name 3 data type)
SELECT ** FROM Persons SELECT ** FROM Persons SELECT ** FROM Persons SELECT ** FROM Persons SELECT ** FROM Persons SELECT ** FROM Persons SELECT ** FROM Persons SELECT ** FROM Persons SELECT ** FROM Persons SELECT ** FROM Persons
SELECT LastName,FirstName FROM Persons SELECT LastName,FirstName FROM Persons
SELECT * FROM Persons SELECT * FROM Persons SELECT * FROM Persons
SELECT * FROM Persons; SELECT * FROM Persons; SELECT * FROM Persons; SELECT * FROM Persons
INSERT INTO table name VALUES (1, 2,….) INSERT INTO Persons VALUES (‘Gates’, ‘Bill’, ‘Xuanwumen 10’, ‘Beijing’)
INSERT INTO Persons (表 1, 表 2, 表 2) VALUES (Value 1, value 2,….)
DELETE FROM Person WHERE LastName = ‘Wilson’ DELETE FROM Person WHERE LastName = ‘Wilson’
DELETE all rows FROM Person or DELETE * FROM Person
UPDATE table name SET column name = new value WHERE column name = new value
Update ‘Person’ with ‘LastName’ as ‘Wilson’ UPDATE Person SET FirstName = ‘Fred’ WHERE LastName = ‘Wilson’ UPDATE Person SET FirstName = ‘Fred’ WHERE LastName = ‘Wilson’
Update ‘Person’ where ‘LastName’ is ‘Wilson’; UPDATE Person SET Address = ‘Zhongshan 23’, City = ‘Nanjing’ WHERE LastName = ‘Wilson’
SELECT * FROM table_name WHERE table_name = ‘SELECT * FROM table_name WHERE table_name =’ SELECT * FROM table_name WHERE table_name = ‘ 【= equal to 】, 【<> not equal to 】, 【> greater than 】, 【>= greater than or equal to 】, 【<= less than or equal to 】, 【BETWEEN in a certain range 】, 【LIKE a certain pattern 】. Note: In some versions of SQL, the <> operator can be written as! =.
SELECT * FROM Persons WHERE City=’Beijing’ SELECT * FROM Persons WHERE City=’Beijing’ SELECT * FROM Persons WHERE City=’Beijing’ SELECT * FROM Persons WHERE City=’Beijing’
Note: SQL uses single quotes to surround text values (most database systems also accept double quotes). For numeric values, do not use quotation marks.
AND AND OR can combine two OR more conditions in a WHERE substatement. If both the first condition AND the second condition are true, the AND operator displays a record. The OR operator displays a record if only one of the first and second conditions is true. SELECT * FROM Persons WHERE FirstName=’Thomas’ AND LastName=’Carter’ SELECT * FROM Persons WHERE FirstName=’Thomas’
SELECT * FROM Persons WHERE firstname=’Thomas’ OR lastname=’Carter’ SELECT * FROM Persons WHERE firstname=’Thomas’ OR lastname=’Carter’
3. We can also combine AND AND OR (using parentheses to form complex expressions) : SELECT * FROM Persons WHERE (FirstName=’Thomas’ OR FirstName=’William’) AND LastName=’Carter’ Select * from Persons where **’Thomas’ or ‘William’** and **’ Carter’**
Sort: The ORDER BY statement is used to sort the result set BY the specified column. If you want to sort the records in descending order, use the DESC keyword. SELECT Company, OrderNumber FROM Orders ORDER BY Company
SELECT Company, OrderNumber FROM Orders ORDER BY Company ** DESC**
SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber SELECT Company, OrderNumber FROM Orders BY Company, OrderNumber
SQL > select * from ‘SQL’; [% replaces one or more characters], [_ replaces only one character], any single character in the [charList] character column], [^charlist], or [! SELECT * FROM Persons WHERE City = ‘Ne%’ SELECT * FROM Persons WHERE City = ‘Ne%’ SELECT * FROM Persons WHERE City = ‘Ne%’
SELECT * FROM Persons WHERE City = ‘%lond%’ SELECT * FROM Persons WHERE City = ‘%lond%’
SELECT * FROM Persons WHERE FirstName = ‘_eorge’ SELECT * FROM Persons WHERE FirstName = ‘_eorge’
SELECT * FROM Persons WHERE LastName = ‘C_r_er’ SELECT * FROM Persons WHERE LastName = ‘C_r_er’
SELECT * FROM Persons WHERE City = ‘[ALN]%’ SELECT * FROM Persons WHERE City = ‘[ALN]%’
SELECT * FROM Persons WHERE City LIKE ‘[!ALN]%’ SELECT * FROM Persons WHERE City LIKE ‘[!ALN]%’
Note: The [charlist] format is similar to regular expressions. If you are interested, check out the regular expressions tutorial here
Let’s write this much first, I usually use is not much, and now ready to switch to apple package SQLite—–>CoreData 😁
One more note: SQL is case insensitive! That is, ‘SELECT’ and ‘SELECT’ are the same, but with ‘SELECT’ is not the same I do not know, did not try, you can try, you are happy 🤔