Evergrande mighty! Some basic SQL knowledge review _ MySQL

Evergrande mighty! Some basic knowledge about SQL review bitsCN.com

First, Evergrande Weiwu!

Start.

Relational algebra:

Currently, mainstream relational databases are built on relational algebra, that is, their mathematical support is relational algebra.

Relational algebra mainly includes the following binary operations: union, intersection, difference, and Cartesian Product.

It should be noted that Cartesian product is strictly a Cartesian product in the broad sense, because the Cartesian product element in a relational database is a tuples. The so-called tuples are simply a row in a database, each row is a tuples.

There are several relational operations in the database: selection, projection, link join, and pision.

SQL:

SQL is the standard language of structured query language and relational database. However, each vendor has its own set of implementations based on this standard. For example, oracle and mysql.

SQL has three major parts: DDL, DML, and DQL.

• • DDL: Data definition language, including create and drop
  • DML: data operation language, including insert, update, and delete
  • DQL: data query language, including select, where, order by, etc.

DDL:

Database creation: create database name

Revoke database: drop database name [cascade | restrict]

• • Cascade mode: directly deletes the database and all its tables, views, indexes, and other elements.
  • Restrict mode: This mode is deleted only when the database does not contain any elements. Otherwise, the operation is denied.

Create a data table: create table name column

Modify data table: alter table name [add new column name] [drop integrity constraint] [alter column name]

Delete table: drop table name [cascade | restrict]

When a table is deleted, not only the table data and definitions are deleted, but also the view index triggers are deleted.

　　DQL:

Select statement:

Select column from table where condition expression group by column having condition expression order by column name asc | desc

And or can be connected to multiple query conditions. and has a higher priority than or, but the priority can be changed using brackets.

Aggregate functions:

Count ([distinct | all] * | column name) count

Sum ([distinct | all] column name) sum

Avg ([distinct | all] column name) calculate average

Max ([distinct | all] column name) returns the maximum value.

Min ([distinct | all] column name) for minimum value

If distinct is specified, duplicate values in the column are canceled during calculation. if distinct is not specified or all is specified (all by default), duplicate values are not canceled.

Grouping statement:

Grouping statements are mainly group by clauses and having clauses.

For example, query the student ID of three or more courses:

Select sno from SC group by sno having count (*)> 3

The group by clause groups query results by specified columns or multiple columns. the group with the same values is one group.

It should be noted that after grouping, the clustering function will act on each group, that is, each group has a function value. In the preceding example, count is performed on the group after each group.

Having clauses are used together with the groupby clause to filter groups based on custom conditions to obtain groups that meet the conditions.

The difference between a having clause and a where clause is that different objects are used. a where clause is used to filter records in a table, that is, tuples that meet the conditions, the having clause is used to filter groups that meet the conditions.

Multi-table join query:

　　Two table join:

Select * from s, SC where s. no = SC. no

　　Self-connection:A table is connected to itself.

Query the indirect preference of each course, that is, the preference of the preference.

Select first. cno, second. cpno from c first, c second where first. cpno = second. cno

　　External connection:

Normal join operations only output the tuples that meet the conditions. if you want to output the tuples that do not meet the conditions in the left and right tables of the join operation, if the blank value is null in the null property, this connection is called an external connection. Keyword join | out join

Left outer join: lists all the tuples in the left table, which is called left outer join. The keyword is left join. | left out join.

Right outer join: lists all the tuples in the right table, which is called the right outer join. The keyword is right join. | right out join.

Example:

Select * from s left out join SC on (s. sno = SC. sno)

Select * from s left out join SC using (sno)

Nested connection query:

Write several examples of nested queries and you will understand them after reading them.

• • • Select name from s where sno in (select sno from SC where cno = '2 ')
    • Select sno, cno from SC x where grade> = (select avg (grade) from SC y where y. sno = x. sno)

　　ANY (SOME) ALL keyword:

When a subquery returns a single value, the comparison operator can be used. for example,> = above. if a multi-value is returned, the comparison operator cannot be used. you must use the ANY (some systems are some) or all keywords, these two keywords must be connected with the comparison operator to express semantics. any indicates a certain keyword, and all indicates all. the main semantics is as follows:

> Any is greater than a value in the subquery result.

> All is greater than all values in the subquery results.

Write several examples:

Query the names and ages of a student younger than a computer student in other systems.

Select name, age from s where age 'CS'

Query the names and ages of students younger than all students in other computer systems.

Select name, age from s where age 'CS'

　　Subquery of exists predicates

The subquery of the exists predicate does not return any data, but generates logical true and logical false.

For example, query the names of all students who have taken Course 1:

Select name from s where exists (select * from SC where sno = s. sno and cno = '1 ')

If the subquery result is not empty, the where clause is true. Otherwise, the where clause is false.

Set operation:

The result of a select statement is a set of tuples. Therefore, multiple select statements can perform a set operation. The set operation mainly involves sum union, intersection intersect, and difference limit T.

Write several examples:

Select from s where dept = 'CS 'union select from s where age <19

Select from s where dept = 'CS 'intersect select from s where age <19

DML:

　Insert:

Normal insert: insert into s (a, B, c) values (1, 2, 3)

Insert a subquery: insert into s (a, B) select a, B from y. nest the subquery in the insert statement to generate the batch data to be inserted.

　　Delete:

Normal deletion: delete from s where a = '20140901'

Delete in query: delete from SC where 'CS '= (select dept from s where s. sno = SC. sno) delete course selection records for all students in computer science

　　Update:

Normal modification: update s set age = 22 where sno = '201312'

Modify the subquery: update SC set grade = 0 where 'CS '= (select dept from s where s. sno = SC. sno). set the score of a computer student to 0.

Zhou Tian's home Code provides some basic SQL knowledge and details. For more information, click here. haha.

At the end, it was Evergrande mighty!

BitsCN.com