SQL master: add, modify, delete and query four sentence patterns

SQL statement: a general standard for structured query statements in relational databases.
Relational database (table): Mysql, Oracle, DB2, SqlServer, etc.

(1) INSERT add statement

(1.1) insert all fields

Note: the added data must be consistent with the order, number and type of table structure fields

INSERT INTO table VALUES(1,'sam',20,'male');(Standard grammar)
INSERT INTO person VALUES(1,'sam',20,'male');
INSERT INTO person VALUES(2,'jack',21,'female');
INSERT INTO person VALUES(3,'jim',22,'male');

(2.2) specified field insertion

Note: the added data must be consistent with the order, number and type of the specified fields

INSERT INTO table(id, name) VALUES(4, 'ste');(Standard grammar)
INSERT INTO person(id, name) VALUES(4, 'ste');
INSERT INTO person(id, age) VALUES(5, 22);
INSERT INTO person(name, id, sex) VALUES('amy', 6, 'female');
INSERT INTO person(id,name) VALUES(6,'');

(2) UPDATE modify statement

Note: if you do not specify a condition, all records in the table will be modified, so you usually need to add a where condition

UPDATE person SET age = 23 WHERE id = 4;
UPDATE person SET name = 'dam' WHERE id = 5;
UPDATE person SET sex = 'male', age = 21 WHERE name = 'ste';

(3) DELETE delete statement

Note: if you do not specify a condition to delete all records, you usually need to specify a where condition

DELETE FROM person WHERE id = 6;
DELETE FROM person WHERE name = 'dam';

Exercise:
(1) Add the following records
101, Zhang San, 30
102. Li Si, female
103. Xiao Zhang
(2) Modify the following records
Zhang San's age was changed to 33
Xiao Zhang changed his gender to male
The name with id 102 is changed to Xiao Wang and the age is changed to 25
(3) Delete the following records
Delete record with id 103

(4) SELECT query statement (6 keywords)

SELECT((column name)
FROM(Table name)
WHERE((define condition)
GROUP BY(Group)
HAVING((conditions after grouping)
ORDER BY(Sort)

(4.1)SELECT column name

SELECT * FROM person;
SELECT id, name FROM person;
SELECT id i, name n FROM person;

(4.2)FROM table name

SELECT name FROM person;
SELECT person.name FROM person;
SELECT p.name, p.age FROM person p;

(4.3)WHERE conditions

Comparison operators (6, pay attention to the writing of unequal)

select sth from Table name where condition

SELECT * FROM person WHERE age > 21;
SELECT * FROM person WHERE age >= 21;
SELECT * FROM person WHERE age < 21;
SELECT * FROM person WHERE age <= 21;
SELECT * FROM person WHERE age = 21;//Note the difference between "equal" and Java
SELECT * FROM person WHERE age != 21;

Logical operators (3)

Logic and and Indicates and (two conditions must be met at the same time)
SELECT * FROM person WHERE age >= 20 AND age <= 22;
Logical or or Indicates or (at least one of the two conditions is satisfied)
SELECT * FROM person WHERE age = 21 OR age = 22;
Logical non not operation

Other operators (4)

Query interval (numeric interval operator) (between... and...)

SELECT * FROM person WHERE age BETWEEN 20 AND 22;
SELECT * FROM person WHERE age NOT BETWEEN 20 AND 22;

Query set (set operator) (in/not in)

SELECT * FROM person WHERE age IN(21, 22);(Indicates that the age is 21 and 22, that is, all age It's information for people 21 and 22)
SELECT * FROM person WHERE age NOT IN(21, 22);//Logical negation

Fuzzy query: only for the search function of [character type]

%Match any [zero or more] characters (common)

Match [one] any character (not commonly used)

SELECT * FROM person WHERE name LIKE '%a%';(In the name a (yes)
SELECT * FROM person WHERE name NOT LIKE '%a%';(Not in the name a (yes)
SELECT * FROM person WHERE name LIKE 'a%';(The first is a (yes)
SELECT * FROM person WHERE name LIKE '%a';(At the end is a (yes)
SELECT * FROM person WHERE name LIKE 'ja_';(a (followed by only one byte)

Empty keyword: NULL means unknown

SELECT * FROM person WHERE name = '';(Represents an empty character, a blank)
SELECT * FROM person WHERE name IS NULL;((indicates unknown)
SELECT * FROM person WHERE name IS NOT NULL;(Indicates non unknown. (i.e. known)

(4.4)GROUP BY

Understanding: the same is a group. Display by group. Several records are displayed in groups.

For example, there are men and women in gender. If they are grouped according to sex, there are two groups in total, and a total of two records are displayed.

Note: if you group or select by a field, you can only write group name or aggregate function.

Aggregate function: number of count(*), max(age) maximum, min(age) minimum, avg(age) average

SELECT sex, count(*) number, max(age) Maximum, min(age) minimum value, avg(age) average value
FROM person
GROUP BY sex
(This code means: from person Table, grouped by gender, query gender)

(4.5)HAVING grouping conditions

Note: This is the condition filter after grouping.

SELECT age, count(*) c
FROM person
WHERE age > 18
GROUP BY age
HAVING c >= 2;

(4.6)ORDER BY sorting

SELECT * FROM person ORDER BY age;
SELECT * FROM person ORDER BY age ASC(Order: from small to large);
SELECT * FROM person ORDER BY age DESC(Flashback: from big to small);
SELECT * FROM person ORDER BY id DESC;

SELECT age, count(*) c
FROM person
WHERE age > 18
GROUP BY age
HAVING c >= 2
ORDER BY c DESC;

(5) limit keyword

Function 1: top-n analysis (must be used with order by)

SELECT * FROM person ORDER BY age DESC LIMIT 3;

Function 2: paging

Note: count from zero.
First parameter: starting position.
The second parameter: returns the number of records.

SELECT * FROM person LIMIT 0, 1;// Start with the 0th data in your table and read only one
SELECT * FROM person LIMIT 2, 2;// Start with the second data in your table and only read the second data (excluding the second data)
SELECT * FROM person LIMIT 1, 3;// Start with the first data in your table and read only three (excluding the first data)

(6) DISTINCT keyword

Note: filter out duplicate records

SELECT DISTINCT age FROM person;

SELECT age, count(*) c
FROM person
WHERE age > 18
GROUP BY age
HAVING c >= 2
ORDER BY c DESC;
LIMIT 3;