@[TOC] Query using the database file download

1. Rename operation

  • old-name as new-name:

    • Rename attributes:

      • select name as instructor_name,course_id
from instructor,teaches
where instructor.ID=teaches.ID;
Copy the code

    • Renaming relationships and their causes:

      • For ease of use

        select T.name, S.course
from instructor as T,teaches as S
where T.ID=S.ID;
Copy the code

      • In order to apply to cases where you need to compare primitives in the same relation: in this case, a relation needs to be cartesian with itself, and it is impossible to distinguish one primitives from other primitives without renaming

        select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';
Copy the code

      Description: Identifiers such as T and S that are used to rename relationships are called correlation names in the SQL standard, but are also commonly called table alias, or correlation variable, or tuple variable;

2. String operations

  • SQL uses a pair of single quotes to identify strings (or double quotes if single quotes are part of a string)
  • In the SQL standard, the string equality operator is case-sensitive (MySQLwithSQL ServerDoes not distinguish between)
  • String functions: concatenation, extraction of substrings, calculation of character length, case conversion, remove space after string (string function set and name details refer to the database system used);
  • Pattern matching:
    • percentpercent(%) : matches any substring;
    • The underlineunderscore(_) : matches any character.
    • The schema is case sensitive;
    • Example:
      • ‘Intro%’ matches any string beginning with “Intro”;
      • ‘%Comp%’ matches a string containing a “Comp” substring;
      • ‘___’ matches a string of only three characters;
      • ‘___%’ matches a string of at least three characters;
  • like
    • Use like for pattern matching
      • select dept_name,building from department where building like ‘%atson’;

    • Use the not Like comparison operator to search for mismatches;
  • escape
    • SQL allows definitionEscape character(using theescapeDefined) :
      • select dept_name,building from department where building like ‘_%a%’ escape ‘_’;

3. Arrange the display order of primitives

  • The order by:

    • select name from instructor where dept_name=’Physics’ order by name; (Default: ascending)

  • Desc descending order, ASC ascending order;

  • Sorting can be done on multiple attributes:

    • select * from instructor order by salary desc, name asc;

4. Where clause predicate

  • between … and …

    • select name from instructor where salary between 9000 and 10000;

  • not between … and …

    • select name from instructor where salary not between 9000 and 10000;

  • And: Find the names of all teachers in the Biology department who are teaching and the names of the courses they are teaching

select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID and depat_name = "Biology";
Copy the code

select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology')
Copy the code

References: [1] Abraham Silberschatz, Henry F Korth, S Sudarshan． Database System Concepts. New York: McGraw-Hill, 2010 Database System Concepts