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