Common SQL statements and SQL statements

Common SQL statements and SQL statements
Create a table:

create table course(    id INT auto_increment,    title TEXT  NOT NULL,    period INT,    description TEXT,    primary key(id)) ENGINE=INNODB CHARSET=utf8;

Insert data to a table in three ways ):

The id field is auto-incrementing and can be left blank:

Insert into course (title, period, description) VALUES ('economic basics ', 320, 'useless, love doesn't learn ');

No specific fields are provided, so the values must be written one by one, including id:

Insert into courseVALUE (2, 'marxist philosophical ', 330, 'this test can be fooled ');

The field order can also be reversed:

Insert into course (period, description, title) VALUE (340, 'difficult to take this test', 'advanced mathematics ');

If a field is not set with a default value, the database sets its value to null:

Insert into course (title, period) VALUE ('required course ', 350 );

 

Delete table data

Syntax:

Delete from table_name WHERE condition expression

The conditional expressions are as follows:

Operator	Description	Example of table course
=	Equal	Title = 'advanced mathematics'
<>	Not equal	Period <> 30
>	Greater	Period> 300
<	Less	Period <1, 300
> =	Greater than or equal	Period> = 320
<=	Less than or equal	Period <= 320
Between	Between two numbers	Period between 200 and 400
Like	Fuzzy match	Title like''
In	Whether it is in the collection	Title in ('economics basics ', 'marxist philosophical ')
Is null	Judge whether it is empty	Description is null
And	And connects multiple conditional expressions.	Period> 300 and description like '% Required Course %'
Or	Or, used to connect multiple conditional expressions	Title = 'advanced mathematics 'or title = 'basic economics'

Priority of a Multi-condition expression:

The priority of the and operation is higher than or, that is, the and operation is performed before or. You can also use parentheses to specify the priority. The expressions in parentheses are first computed.

Delete a course with the id of 1:

DELETE FROM courseWHERE id=1;

Delete A Course whose name starts with 'economical 'and whose class size is less than 200, and delete a course whose class size is greater than 600:

Delete from courseWHERE title like 'economical % 'and period <200 or period> 600;

Delete A Course whose name starts with 'economical 'and whose duration is less than 200 or greater than 600:

Delete from courseWHERE title like 'economical % 'and (period <200 or period> 600 );

 

Modify Table Data

Syntax:

UPDATE table_nameSET field name 1 = new value 1, field name 2 = new value 2... WHERE condition expression

Change the age of all students to 18:

Note: This operation is very dangerous in actual application, so we must specify the conditions for it;

UPDATE studentSET age=18;

Add 1 to the Age of all students whose parent is null, and set the parent to 'unknown ':

UPDATE studentSET age=age+1WHERE parent IS NULL;

Query data

Syntax:

Top is used to specify the maximum number of returned rows. distinct is only used to specify to exclude duplicate items when only one column is returned.

SELECT [distinct | top] field name 1, field name 2 ..... FROM table_name [WHERE condition expression] [group by group column [HAVING grouping filter expression] [order by field name 1 [ASC | DESC], field name 2 [ASC | DESC]

Query all records in the course table:

SELECT * FROM course;

Query all lessons and exclude repeated numbers:

SELECT distinct period FROM course;

Query the courses with a class greater than 200. A maximum of two courses can be returned:

SELECT top 2 * FROM courseWHERE period>200;

Note: The top syntax exists in postgreSQL; there is no top syntax in mysql, and limit can be used in MySQL:

SELECT * FROM courseWHERE period>200LIMIT 1,2;

Group by is used to GROUP data for summary calculation. HAVING is an option of group by to filter the summary results. Aggregate calculation refers to the number of statistics and the average value,

Calculate the average lessons of all courses:

SELECT AVG(period) FROM course;

Group by class period, count the number of courses in each class, and only return the class with the number of courses greater than 3:

SELECT period, COUNT(*) FROM courseGROUP BY periodHAVING count(*) >3;

Common Aggregate functions:

Aggregate functions	Description
COUNT (*)	Count records
AVG (column)	Calculate the average value of a column
MAX (column)	Find the maximum value of a column
MIN (column)	Find the minimum value of a column
VAR (column)	Calculate the variance of a column
FIRST (column)	Returns 1st values of a column.
LAST (column)	Returns the last value of a column.

Order by is used to specify that the returned results are sorted BY the values of one or more fields. BY default, ASC is sorted in ascending ORDER, and DESC is sorted in descending ORDER:

SELECT * FROM courseORDER BY id  desc;

 

Multi-table join SQL statement

Create more tables:

create table student(    id INT auto_increment,    name TEXT NOT NULL,    age INT,    parent TEXT,    primary key(id)) ENGINE=INNODB CHARSET=utf8;create table enroll(    student_id INT,    course_id INT,    primary key(student_id, course_id),    CONSTRAINT FOREIGN KEY(student_id) REFERENCES student(id),    CONSTRAINT FOREIGN KEY(course_id) REFERENCES course(id));create table teacher(    id INT auto_increment,    name TEXT NOT NULL,    gender BOOLEAN,    address TEXT,    course_id INT,    primary key(id),    CONSTRAINT FOREIGN KEY(course_id) REFERENCES course(id));

Because the data of the entire system is distributed in different tables, developers often need to query data from two or more tables to obtain complete results, in this case, you need to use the JOIN keyword in the FROM clause to JOIN multiple tables. The related syntax is:

SELECT column name 1, column name 2... FROM table_name1 JOIN table_name2 on join condition expression WHERE...

The JOIN Operation has multiple types, as shown in the following table:

Keywords	Description
INNER JOIN	Obtain the connection records that match the query keywords in two tables.
LEFT JOIN	Returns all records that are not connected to the left table based on the records returned by inner join.
RIGHT JOIN	Based on the records returned by inner join, all records not recorded in the right table are returned.
FULL JOIN	Returns a collection of inner join, left join, and right join results.

Although one JOIN can only JOIN two tables, multiple joins can be used at the same time to connect multiple tables:

Query all the male teachers who teach higher mathematics:

SELECT teacher. * FROM teacher inner join course on teacher. course_id = course. idWHERE teacher. gender = True and course. title = 'advanced mathematics'

Query the courses selected by all 18-year-olds

SELECT distinct course.titleFROM course INNER JOIN enroll ON course.id=enroll.course_id    INNER JOIN student ON enroll.student_id=student.idWHERE student.age=18;

 

Other problems

As mentioned above, if the where condition is not specified during data update, it is very dangerous because it will change the values of all records in the entire table. To prevent this event, we can use the-U parameter when starting mysql.