SQL (1)

SQL

Structured Query Language (struceured query ANG) has always been a standard language for relational database management systems (RDBMS ).

 

Integrates DDL, DML, and DCL,

 

Data Query and data manipulation

Select [All | distinct] <target expression> [, target expression]...

From <Table Name> [, table name]

Where <conditional expression>

Order by <column Name> [ASC | DESC]

 

Single Table query

Create Database studentinfo,

 

Create a database table student (student basic info table)

Create fields (columns), SnO, sname, ssex, sage, sdept

 

Create a database table SC (student selection table)

Create field, ID, SnO, CNO, grade

 

Query several columns in a table

1. query the specified Column

Example 1 query the student ID and name of all students.

Select SnO, sname from student

Example 2 query the name, student ID, and Department of all students

Select studentname, studentno, sdepartment from student;

Example 3 query detailed records of all students

Select * from student

Equivalent

Select * SnO, sname, ssex, sage, sdept from student

Example 4 query the name and year of birth of all students

Select sname, 2006-sage from student;

 

Query several tuples in a table

 

1. eliminate duplicate data

 

Query all student ID of an optional course

Select SnO from SC

How can we eliminate repeated data?

Use distinct

Select distinct SnO from SC

 

Query data that meets the conditions

 

Comparison =,>, <, >=, <= ,! =, <>,!> ! <; Not + comparison operator

The value range is between and, not between and.

Determine the set in, not in

Character match like, not like

Null is null, is not null

Multiple conditions and, or

 

Example 5 query the list of all students in the computer department

Select sname, from student where sdept = 'cs ';

Example 6: query the names and ages of students under 20

Select sname, sage, from student where sage <20;

Determined range: IE

Between... And and not .... And

 

For example 7, query the name, department, and age of a student between the ages of 20 and 23.

Select sname, sdept, sage from student where sage between 20 and 23

 

Determine set

In can be used to query data whose values belong to the specified set.

 

Example 8 name and gender of students in the Information Department (is) and computer department (CS)

 

Select sname, ssex from student where sdept in ('is, 'cs ');

 

Character matching

Like and not like are used for string matching. It can contain wildcards _ and %

% Represents any length

_ Represents a character

Example 9 query all the data of students whose student ID is 06001

Select * from student where SnO like '123 ';

Example 10 query the names, student IDs, and gender of all students surnamed Liu.

Select sname, SnO, ssex where sname like 'Liu %'

Example 11: query the names of students with the surname "Ouyang" and three Chinese Characters

Select sname from student where sname like 'ouyang __'

 

Multi-condition Query

And and or can be used to connect multiple query conditions.

Example 12: Query all students under the age of 20 in Computer Science

Select sname frome student where sdept = 'cs 'and sage <20;

Example 13: query the names and gender of all students in the computer or information system.

Select sname, ssex from student where sdept = 'cs 'or sdept = 'ais'

 

Order)

Order by sorts the query results in ascending or descending order (DESC) of a field. The default value is ascending.

 

Example 14: query the student ID and score of course 3. the query results are sorted in descending order of scores.

Select SnO, grade from SC where CNO = '3' order by grade DESC;

 

Use Functions

Count count

Sum calculates the sum of values in a column.

AVG calculates the average value of a column

Max calculates the maximum value of a field.

Min calculates the minimum value of a field.