SQL is the abbreviation of structure query language, which is the RDBMS application language.
In the early 1970s, Edgar Frank Codd (also known as Ted Codd) of San Jose, California Research Laboratory of IBM, published the application principle of data into tables (Codd’s relational algebra). In 1974, d.d.chamberlin and r.f. Boyce of the same laboratory developed a set of standard language – sequential (Structured English Query Language) in the development of relational database management system R for Codd’s relational algebra, and published a new version of SQL (called sequential / 2) on IBM Journal of R & D in November 1976. It was renamed SQL in 1980.
In 1979 Oracle first provided commercial SQL, and IBM also implemented SQL in DB2 and SQL / DS database systems.1
Ted Codd is the absolute database creator
relation modelTuring Award, 1981. In the relationship model
Boyce-Codd Normal From(between the third paradigm and the fourth paradigm) also comes from his name.
System RIt is also absolutely important in the history of database. Especially when reading books, they are constantly mentioned.
In October 1986, American ANSI adopted SQL as the standard language of relational database management system (ANSI X3. 135-1986), which was later adopted as an international standard by international organization for Standardization (ISO).
In 1989, American ANSI adopted the SQL standard language of relational database management system defined in the report of ANSI x3.135-1989, called ANSI SQL 89, which replaces the version of ANSI x3.135-1986. This standard is adopted by the following organizations:
International Organization for Standardization (ISO), report “database language SQL with integrity enhancement” for ISO 9075-1989
The federal information processing standard publication (FIPS pub) 127
At present, all the major relational database management systems support some forms of SQL, and most of the databases comply with the ANSI sql89 standard at least.
ANSI SQL92 standard adds external connection on top of cross join and internal connection, and supports writing connection expression in from clause. Supports Union and intersection operations of sets. Case (SQL) expressions are supported. Check constraints are supported. Create a temporary table. Support cursor.
Support transaction isolation.1
About this article
This article uses PostgreSQL’s SQL syntax. Focus we focus on
select...from...whereThis read operation analyzes query (analytical query).
Datasets can be used directly at https://hyper-db.de/interface. In addition, write operations are not allowed on this page:
deleteTransactional query like this. Of course
drop tableAnd not allowed.
Schma and most SQL statements come from pro. Alfons Kemper, Ph.D.’s courseware and books.
- Search for all the professors whose rang is C4:
select persnr, name from professoren where rang = 'C4'
- For all professors, first rank rank in reverse order, and then rank name in positive order:
select persnr, name, rang from professoren order by rang desc, name asc
- Get all the possibilities of professional’s rank:
select distinct rang from professoren
- Search for a professor in maeeutik:
select p.name, v.titel from professoren p, vorlesungen v where p.persnr = v.gelesenvon and titel = 'Maeeutik'
- List the student’s name and the class the student has attended:
select s.name, v.titel from studenten s, hoeren h, vorlesungen v where s.matrnr = h.matrnr and h.vorlnr = v.vorlnr
- Search how many students listen to each class, consider the classes that no one listens, and sort them in reverse order:
select v.vorlnr, v.titel, count(h.matrnr) as num from vorlesungen v left outer join hoeren h on v.vorlnr = h.vorlnr group by v.vorlnr, v.titel order by num desc
- Search for the union of the name of assistenten and the name of professional:
(select name from assistenten) union (select name from professoren)
- Search for professors who don’t have any classes:
-- correlated sub-query select p.name from professoren p where not exists( select * from vorlesungen v where v.gelesenvon = p.persnr ) -- un-correlated sub-query --Not in: comparison of sets select p.name from professoren p where p.persnr not in ( select v.gelesenvon from vorlesungen v )
- Search for students who don’t have any classes:
-- correlated sub-query select s.name from studenten s where not exists( select * from hoeren h where h.matrnr = s.matrnr ) -- un-correlated sub-query --Not in: comparison of sets select s.name from studenten s where s.matrnr not in ( select h.matrnr from hoeren h )
- Students who listen to 3 or more courses
select s.matrnr, s.name from studenten s, hoeren h where s.matrnr = h.matrnr group by s.matrnr, s.name having count(*) >= 3
- Search for semester’s largest students:
select s.name from studenten s where s.semester >= all ( select semester from studenten )
- Find the average semester of student:
select avg(semester) from studenten
- Search for SWS > = 4 and act as vorgaanger for at least two other courses:
select v.vorlnr, v.titel from vorlesungen v, voraussetzen vor where v.vorlnr = vor.vorgaenger and v.sws >= 4 group by v.vorlnr, v.titel having count(*) >= 2
- Search the sum of SWS for each professor’s class:
select v.gelesenvon, sum(v.sws) from vorlesungen v group by v.gelesenvon
- Search for professionals with rank C4 whose total SWS is more than 3:
select v.gelesenvon, p.name, sum(v.sws) from vorlesungen v, professoren p where v.gelesenvon = p.persnr and rang = 'C4' group by v.gelesenvon, p.name having avg(v.sws) >= 3
You need to pay attention to the aggregation operation here.
A tuple is generated for each group. So for those with
selectClause can only contain
group byThe attribute value and aggregation operation mentioned in clause.
- Syntax sugar for comparison
SELECT * FROM studenten WHERE semester >= 1 AND semester <= 4; SELECT * FROM studenten WHERE semester between 1 and 4; SELECT * FROM studenten WHERE semester in (1,2,3,4);
- String comparison:
SELECT * FROM studenten WHERE name like 'T%eophrastos'; SELECT DISTINCT s.name FROM vorlesungen v, hoeren h, studenten s WHERE s.matrnr = h.matrnr AND h.vorlnr = v.vorlnr AND v.titel LIKE '%thik%';
SELECT matrnr, (case when note <= 1.5 then 'sehr gut' when note <= 2.5 then 'gut' when note <= 3.5 then 'befriedigend' when note <= 4.0 then 'ausreichend' else 'nicht bestanden' end) FROM pruefen;
left outer join:
SELECT p.persnr, p.name, f.persnr, f.note, f.matrnr, s.matrnr, s.name FROM professoren p left outer join pruefen f left outer JOIN studenten s ON f.matrnr = s.matrnr ON p.persnr = f.persnr;
right outer join:
SELECT p.persnr, p.name, pf.persnr, pf.note, pf.matrnr, s.matrnr, s.name FROM professoren p right outer JOIN pruefen pf right outer JOIN studenten s ON pf.matrnr = s.matrnr ON p.persnr = pf.persnr;
full outer join:
SELECT p.persnr, p.name, pf.persnr, pf.note, pf.matrnr, s.matrnr, s.name FROM professoren p full outer JOIN pruefen pf full outer JOIN studenten s ON pf.matrnr = s.matrnr ON p.persnr = pf.persnr;
- Make a multiplication table
-- precompute look up table with mtable as (values ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10), ( 2, 4, 6, 8, 10, 12, 14, 16, 18, 20), ( 3, 6, 9, 12, 15, 18, 21, 24, 27, 30), ( 4, 8, 12, 16, 20, 24, 28, 32, 36, 40), ( 5, 10, 15, 20, 25, 30, 35, 40, 45, 50), ( 6, 12, 18, 24, 30, 36, 42, 48, 54, 60), ( 7, 14, 21, 28, 35, 42, 49, 56, 63, 70), ( 8, 16, 24, 32, 40, 48, 56, 64, 72, 80), ( 9, 18, 27, 36, 45, 54, 63, 72, 81, 90), ( 10, 20, 30, 40, 50, 60, 70, 80, 90, 100) ) select * from mtable
with mtable_column(a, b, c, d, e, f, g, h, i, k) as (values (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) -- it's actually written in one line ), mtable_row(x) as (values (1) , (2), (3), (4), (5), (6), (7), (8), (9), (10) -- actually written as a column ) select a * x, b * x, c * x, d * x, e * x, f * x, g * x, h * x, i * x, k * x from mtable_column, mtable_row
- SQL Wikipedia. Https://zh.wikipedia.org/wiki A kind of