Common SQL statements------CRUD

Review the text before the SQL statement, the previous class is the teacher in the above, we play below, do not put this in mind, until the test when the temporary learning assault, you can smoothly over 60, but now not, I want to re-learn SQL, Should be the most basic sentence and grammar are all learning, each case to be executed in the database, we need these basic knowledge points.

1: Create TABLE, modify table, add column

/* Create a basic table Student*/create table Student (           Sno char (5) NOT null unique,/        * The number defined cannot be empty and is a unique value. */   Sname char (+),   Ssex char (2),   Sage int,   sdept char (15));/* Modify base table student*//     * Add new column scome*/     ALTER TABLE Student ADD scome DATE;     /* Modify the data type */ALTER TABLE Student MODIFY Sage SMALLINT; /* Delete the unique identifier of the study number * * * ALTER TABLE Student drop unique (Son);/* Delete table */drop tables student;/* CREATE INDEX */creation    CLUSTER index Stusname On Student (Sname);    /* Delete the index */    DROP index stusname;

2: Basic Query

/* Query */          select sname,sage from Student;  Select Sname,sage,sdept from Student;  SELECT * from Student;  Select Sname,1996-sage from Student;  Select Sname, ' Year of Birth: ', 1996-sage,lower (sdept) from Student;  Select Sname NAME, ' Year of Birth: ' Birth,1996-sage birthday,lower (sdept) DEPARTMENT from Student;    /* Create an alias for the column */

3: Complex queries

/* Eliminate duplicate lines */select DISTINCT Sno from Student; /* Query for tuples that meet the criteria */select Sname from Student where sdept= "CD"; Select Sname,sage from Student where sage>=20; ALTER TABLE Student add Grade int; Select DISTINCT Sno from Student where grade<60; /* Determine the range-----------between and */select sname,sage,sdept from Student where Sage between and 23; /* Determine the collection-----------in (', ', ') */select Sname,ssex from Student where sdept in (' Is ', ' MA ', ' CS '); /* Character match-----------like+++ (%: for any length) (_: Represents a single character) */select Sname,sage from Student where Sname like ' A% '; SELECT Sname, Sage from Student where Sname like ' A_ '; /* Character matches the escape character------------escape ' \ ': means \ is the escape character *//* the query condition is "A_" at this time _ does not represent a character, but simply represents the underscore. Because the statement is preceded by an escape character. */select Sname from Student where Sage like ' a\__ ' ESCAPE ' \ ';  /* queries involving NULL values */select Sname,sage from Student where Grade is NULL; /* Search for students with empty grades */

4: more complex queries

/* Multi-condition Query */   Select Sname from Student where sdept= ' CD ' and sage>20;   Select Sname from Student where sdept= ' CD ' or sage>20;/* to sort query results */   Select Sname Grade from Student where sage>2 0 ORDER by Sage DESC;   SELECT * from Student ORDER by Sage desc;/* Use Set function *   /SELECT COUNT (*) from Student;                 /* Total number *   /SELECT COUNT (Distinct Sno) from Student;   Select AVG (Sage) from Student where sname= ' Ahui ';     /*AVG: Averaging *   /select Sname,count (Sage) from Student Group by Sname;   Select MAX (Sage) from Student where sno= ' 1 ';    /* Maximum value */

5: Connection Query

/* Connection Query *//     * Equivalent query */      Select student.*,sc.* from STUDENT,SC where STUDENT.SNO=SC. Sno; /* Natural connection of two tables */select STUDENT.SNO,SNAME,SSEX,SAGE,SDEPT,CNO,SC. Grade from STUDENT,SC where STUDENT.SNO=SC. Sno; /* Rename the table student to two names, thus making a query to itself */select FIRS. Sage,seco. Sno from Student firs,student SECO where FIRS. Sno=seco. Sage; /* External connection */select STUDENT.SNO,SNAME,SAGE,SDEPT,SSEX,CNO,SC. Grade from STUDENT,SC where STUDENT.SNO=SC. Sno (*);/* Compound conditional join--------is to use and to operate the */select Student.sno,sname from STUDENT,SC where STUDENT.SNO=SC. Sno and SC. Cno= ' 2 ' and student.sage=2;

6: Nested Query

This query is to query the results of the innermost query as a condition of the outside query, the execution order is executed from inside to outside. Note: The innermost query (the ORDER BY clause cannot be used in a subquery's SELECT statement, and the ORDER BY clause can only sort the results of the final query)

   /* Nested query---is to query the result of one query as a condition of another query *      /select Sname from Student where Sno in         (   select Sno from SC where cno= ' 2 /*--01: Sub-query with in, *  /select sno,sname,sdept from Student where sdept in (  select Sdept from Student where Sname= ' a Hui ');       /*--02: Subquery with comparison operator *  /select sno,sname,sdept from Student where sdept= (  select sdept from Student where Sname= ' Ahui ');

7: Collection Query

Each SELECT statement can have one or a set of tuples, to combine the results of multiple SELECT statements into a single result, which can be done using a collection operation. Collection operations mainly include and operate (UNION) cross-operation (INTERSECT), differential operation (minus).

Using union to combine multiple query results to form a complete query result, the system will automatically remove the duplicate tuples;

Note that the number of data items that participate in the Union operation must be the same, and the data type of the corresponding item must be the same.

     sage<=19; Select Sno from SC where cno= ' 1 ' UNION select Sno from SC where cno= ' 2 ';

8: Data Update

/* Inserting data */INSERT INTO  Student values (' ", ' aaa ', ' male ', ' + ', ' 3 ', ' 2015-11-26 ', ' 3 ');  Insert into SC (SNO,CNO) VALUES (' ', ' 3 ');  Insert into Deptage (sdept,avgage) Select Sdept,avg (Sage) from Student GROUP by sdept;  /* Modify Data */     update Student set sage=233 where sno= ' n ';   Update Student Set sage=sage+1;   Update SC set grade=1 where ' 1 ' = (select Sdept from Student where STUDENT.SNO=SC. SNO);/* Delete data */delete from   Student where sno= ' 01 ';

Common SQL statements------CRUD