Shulou(Shulou.com)06/01 Report--

Memory ideas: SQL DQL language select query command.

from which table where select queries which columns by what criteria, order by whether to sort based on a field, limit #output how many rows.

I. Single table query

1. Common query syntax:

SELECT output display field FROM table name unconditional query syntax: SELECT specified output column FROM table name; restrict output syntax: SELECT specified output column FROM table name LIMIT display number of records; conditional query syntax: SELECT specified output column FROM table name WHRER query condition; conditional query reordering: SELECT specified output column FROM table name WHRER query condition order by specified sorting field [desc| asc]; conditional query and limit output syntax: SELECT display output column FROM table name WHRER query condition LIMIT display number of records;

2. SELECT filter output column:

Example 1: Output all rows and columns of the table,[Represents all columns]

SELECT FROM students; query all the contents of the students table Example 2: Output Display fields with aliases Output: [field name as alias] Note that as can be omitted

SELECT name as Name,age as Age FROM vmlab;

SELECT name,age FROM vmlab;

3. WHERE filter output lines: filter conditions by where

Arithmetic operators: +, -, *, /, % Logical operators: NOT, AND, OR, XOR

Comparison operator: =,(equal or both empty), , != (non-standard SQL), >, >=, =2 and id 30 and classid >3;

II. Multi-table query

Sub-query: another query statement is embedded in the query statement, and the result of the sub-query statement is used as the filter condition or input result of the parent statement.

select name,age from students where age >(select avg(age)from students); vertical merge: union union queries are used to realize vertical merge of multiple tables. By default, there is de-duplication function. If you don't want to de-duplicate, you can use union all.

Required conditions: The data type of the merged fields among multiple tables must be the same, and the writing order of the output fields after select must be consistent.

select * from teachers union select Stuid,name,age,Gender from students; Horizontal merge: train of thought to determine which is the main table first.

a. The number of fields is the sum of two table selection fields

b. The number of records is the Cartesian product of two table records, that is, the multiplication of two table records, the cross merger of all record fields, and the merger of each record in the main table and each record in the auxiliary table.

select from students cross join teachers;

select from students,teachers;

select students.name, teachers.name,students. classified from students,teachers limit 10; output specified columns, you must specify which table columns to output.

select students.name as name,teachers.name as teachers,students.classid class from students,teachers limit 10; customize the output header with field aliases.

select st.name as name,te.name as teacher,st.classid class from students as st,teachers as te limit 10; It is simpler to specify an alias for the table and then enter the output field, which must be used once the alias is specified. Inner join: Take the intersection of two tables

Logic: Merge two tables horizontally based on one or more common characteristics of the two tables, so that records in the two tables are associated based on some condition. If you don't add the merge condition, you're doing Cartesian product merge.

Examples:

select from students inner join teachers on students.teacherid=teachers.tid; new input

select from students as s,teachers as t where s.teacherid=t.tid order by stuid; old-style input

select s.Stuid, s.name, t.name,s.classid from students as s inner join teachers as t on s.teacherid=t.tid order by stuid; Customize output fields based on table aliases.

select s.Stuid as student number,s.name as name,t.name as teacher,s.classid as class from students as s inner join teachers as t on s.teacherid=t.tid order by stuid; Define aliases for output fields to reach the directory of customized output headers.

Composite conditional merge:

select from students as s inner join teachers as t on s.teacherid=t.tid and s.stuid >10;

select from students as s inner join teachers as t on s.teacherid=t.tid where s.stuid >10;

External connections:

Logic: Left and right are relative concepts, so determine which table is the main table before merging. All records in the main table will be output. The annexed table is the auxiliary table, and the records in the auxiliary table will be merged horizontally into the main table.

Left-outside connection: Set the left side as the main table, all records will be output, and the right side as the auxiliary table. The two tables will be merged horizontally. The records in the auxiliary table (right side table) that meet the merger conditions will be filled in the corresponding records in the main table (left side table), and the remaining records in the main table that do not meet the conditions will be customized to output the display fields according to requirements.

select from students as s left outer join teachers as t on s.teacherid=t.tid;

Left outer connection special case: set filter condition with where, exclude records with common characteristics in left and right tables, and horizontally merge and output records without common characteristics in main table (left side).

select from students as s left outer join teachers as t on s.teacherid=t.tid where t.tid is null;

Right external connection: Set the right as the main table, all records will be output, the left as the auxiliary table, the two tables will be merged horizontally, and the left will be

select from students as s right outer join teachers as t on s.teacherid=t.tid;

Right outer join special case: set filter condition with where to exclude records with common characteristics in left and right tables, and horizontally merge and output records without common characteristics in main table (right side).

select from students as s right outer join teachers as t on s.teacherid=t.tid where s.teacherid is null;

Complete outer join: the two tables are not divided into primary and secondary, all records of the two tables are output, the fields of the two tables are merged, when a record in the left table has no value to fill in, the corresponding field is empty, and when the record in the right table has no value to fill in, the corresponding field is also empty, thus merging the two tables and outputting all records of the two tables, which is equivalent to the complementary and complete combination of the two tables to form a rectangle.

Complete outer join exception: exclude the intersection of two tables, keep the rest, that is, inverse intersection

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.