3 relational database standard language SQL

I. Features of the SQL language

SQL structure Query Language

1. Integrated and unified;

2. Highly non-procedural: No need to specify a storage path

3. Set-oriented operation mode

4. Two ways to use the same syntax : standalone language, embedded language

5. Simple language, easy to learn and use

Second, review the function of database management system

1. Database establishment and maintenance functions;

2. data definition function;

3. Data manipulation function;

4. Management of database operation.

Section II Data definition

First, the basic table

1. Creating a Create

CREATE TABLE Student (name char (5) Nut Null,sname char (20),

Unique, Ssex char (1), Sage int, sdept char (15));

Constraint: Nut null; Unique Primary key; Foregin Key

2. Modify the ALTER including

Alter Table < table name >[add < new column name >< data type >[column-level integrity constraints ][  Drop < column name >< integrity constraints >][modifu< column name >< data type >] [Change< original column name >< new column name >< new data type >]

Add Delete Drop change modify modify the data type, change the whole content of a column to a new column

3. Delete Drop

Drop Table < table name >; Drop Table Student;

Second, the index

Meaning: A database object built on a basic table that can speed up queries

1. Establish

Create[Unique] [Cluster] Index <Index name> on<Table name>(<Column Name 1>[< order >][,< Column name 2>[< order >]...);CreateClusterIndexInd_name onStudent (Sname);

The default is ascending ASC, and in addition, descending is desc

2. Delete

Drop Index < index name >;

Section Three data query

First, format

Select [all|distinct] <Target column Expression>[,< target column expression >]... from <Table name or view name>[,< table name or view name >]...[where< Conditional Expressions >][Group by< Column name 1>[having < conditional expression >]][Order by< Column name 2>[asc|desc]]

1. Target column expression */table name. */count (*)/Field name expression

2. Conditional expressions

Second, single-table query

1. Select several columns in the table

1 Select  from Student; 2 Select *  from Student; 3 Select Sname,a- from Student;

2. Select several tuples in the table

1 SelectSname fromStudentwhereSdept='CS';2 SelectSname fromSwhereSage Beteween -  and  -;3 SelectSname fromSwhereSageinch('CS','MA',' is');4 Select *  fromSwhereSno like '200215121';5 Select *  fromSwhereSno='200215121'If the like is not followed by a wildcard character, as can be equal to =6 SelectSname fromSwhereSname like 'Liu%'; Liubeli7 SelectSname fromSwhereSanme like 'Liu _'; Liu Beliuming8 Select *  fromCoursewhereCname like 'db\_%i__'Escape'\'query starts with Db_, and the 3rd character is the class I9 SelectSno fromScwhereGrade is NULL; is cannot be written=Tenand above or

3. Find out the search results

3 relational database standard language SQL