Common SQL statements

Create a student table

 Create TableStudent (SnoChar（6）Primary Key，/*column-Level integrity constraints, SNO is the primary key*/SnameChar（Ten）Unique，/*column-level integrity constraints, sname unique*/SsexChar（2）Check(Ssexinch（'male','female')), Sagesmallint, Sdate date not NULL);

Changes to the data table

eg. Add a column to the student table

Alter Table Add Char(+);

eg. Will age data type this text long integer type

Alter Table Alter column int;

Deletion of data tables

(After the basic table is deleted, the data in the table and the indexes created on this table, views, triggers, etc. are automatically deleted, so be careful to delete them)

Drop Table Student;

Indexing (speeding up queries, increasing efficiency) and deleting

Set the number of the student table to be indexed in ascending order the number of the student table is in ascending order, and the result is a unique index based on descending.

Create Unique Index  on /* if not stated, the default is ascending */ Create Unique Index  on ASC desc);

Deletion of indexes

To delete the index of a student table

Drop Index Stusno;

Inquire:

Query student All the information

Check name, and age

Select *  from Student; Select  from Student;

Set up a SC table with the number SNO Course Number Cno score Grade

In this SC table, check the number of students who have selected the course and eliminate the repetition number.

Select distinct  from SC;

Check the student number and name of all the students in the computer department

Select  from where = ' CS ';

Check the names of students aged between 20-30 years

Select  from where between  -  and ;

String match

Find out the name and gender of the student whose name is named Zhang Wei

Select  from where  like ' Zhang _  _ wei%';

Exists

Select  from where exists (Select*fromwhere Sno=student.sno);

Common SQL statements