SQL query BASICS (syntax and concept) Full Version

Note: The following content is executed in the sqlserver2000 query analyzer, which is a comment.

You can save the following content as a text file suffixed with SQL, and then open it in the sqlserver2000 query analyzer,

This article focuses on some SQL query syntax and concepts, so some queries may not have any practical significance, just to illustrate the query syntax. For some mathematical basics, you can refer to the article "relational algebra (theory) of databases" in the previous two days. If you still want to know how the database operates during the Query Process, please refer to other books for your reference. I will post them after my research. I hope you will study hard and study hard every day. So will I. I will study hard every day !!

File Content:

-- Show all columns
-- Select * from employees;

-- Display certain columns in order
-- Select employeeid, lastname, firstname, birthdate from employees;

-- Specifies the column alias (two methods)
-- Select employeeid as employee number, birthdate as birthday from employees;
-- Select employee ID = employeeid, Birthday = birthdate from employees;

-- Limit the number of returned rows
-- Select top 5 * from employees;
-- Select top 5 percent * from employees;

-- Select different columns
-- Different countries
-- Select distinct country as country from employees;
-- The country and the name must be at least one different.
-- Select distinct country as country, titleofcourtesy as called from employees;

-- Sort query results
/*
Select employeeid as employee ID, lastname + ''+ firstname as employee name, birthdate as birth date, city as City
From employees
Where city = 'London 'order by birthdate;
 
-- Sort different columns
Select employeeid as employee ID, lastname + ''+ firstname as employee name, birthdate as birth date, city as City
From employees
Where city = 'London 'order by firstname, birthdate DESC;
*/
-- Calculate the query result
--
-- Use pubs;
-- Select title as bibliography, price as unit price, ytd_sales as sales volume, price * ytd_sales as sales volume from titles;
-- Queries with conditions
/* Select employeeid as employee ID, lastname + ''+ firstname as employee name, birthdate as birth date, city as City
From employees
Where city = 'London ';

Select employeeid as employee ID, lastname + ''+ firstname as employee name, birthdate as date of birth, city as city, country as country
From employees
Where country like 'us % ';
 
Select employeeid as employee ID, lastname + ''+ firstname as employee name, birthdate as date of birth, city as city, country as country
From employees
Where country in ('USA ', 'uk ');
 
-- For Complex Condition queries, pay attention to the logical relationship. brackets should be added. The priority of or in the computer is higher than that of and.
Select employeeid as employee ID, lastname + ''+ firstname as employee name, postalcode as zip code, country as country
From employees
Where
(
Postalcode = '20140901'
Or postalcode = '20140901'
Or postalcode = 'sw1 8jr'
)
And Country = 'usa ';
 
*/

-- Use internal connection Query
-- Query the product table and supplier table to obtain the corresponding products of the company and the company;
-- Use northwind;
/* Select P. supplierid as supplier no., S. companyName as company name, P. productname as product name, P. unitprice as price
From products P, suppliers s where P. supplierid = S. supplierid;
*/
-- If you want to query the books written by the author and the author, you can find that there is no association between the two tables, but the table titleauthor is connected to two tables, generally, when the relationship between the two is many to many, the third table is used,
/* Use pubs;
Select a. au_lname + ''+ A. au_fname as author, T. Title As title from authors A, titles T, titleauthor Ta
Where a. au_id = TA. au_id and T. title_id = TA. title_id;
 
*/

-- Use external connection
-- Left Outer Join. Here we only introduce the syntax. In fact, the following query is the same as the above, because the supplierid of the Products table and suppliers table is one-to-one,
/* Use northwind;
Select P. supplierid as supplier no., S. companyName as company name, P. productname as product name, P. unitprice as price
From products P
Left Outer Join suppliers s
On P. supplierid = S. supplierid;
*/
 
-- Right Outer Join
/* Use northwind;
Select P. supplierid as supplier no., S. companyName as company name, P. productname as product name, P. unitprice as price
From products P
Right Outer Join suppliers s
On P. supplierid = S. supplierid;
*/

-- All external connections
/* Use northwind;
Select P. supplierid as supplier no., S. companyName as company name, P. productname as product name, P. unitprice as price
From products P
Full outer join suppliers s
On P. supplierid = S. supplierid;
*/
 

/*
Joint Query
The Union operator can combine the query result sets of two or more select statements into a result set for display, that is, to execute a union query.
The syntax format of union is: select_statement Union [all] selectstatement [Union [all] selectstatement] [… N]
Selectstatement is the SELECT query statement to be combined. The all option combines all rows into the result set. If this item is not specified,
Duplicate rows in the Union query result set retain only one row.
Note: Since there are no suitable examples, I will not give them an example. I will tell you later
*/
 
/*
Cross Query
The cross join clause does not contain the WHERE clause. It returns the Cartesian product of all data rows in the two joined tables,
The number of rows returned to the result set is equal to the number of rows that meet the query conditions in the first table multiplied by the number of rows that meet the query conditions in the second table.
*/

/*
Self-connection
Connect itself

*/

/*
Subquery
In fact, a subquery is the result of a query as the input for the next query.
*/