first, the establishment of the table instructions For example, create a student table student, it consists of the number SNO, name sname, sex ssex, age sage, the department sdept five attributes. Where the number cannot be null, the value is unique, and the name value is unique. CREATE TABLE Student (Sno CHAR (Ten) is not NULL UNIQUE, Sname CHAR (a) UNIQUE, Ssex char (2), Sage INT, sdept char (.) )
second, add columns, delete columns, modify columns 1. Add Column Stel Alter table Student
ADD Stel Char (a) 2. Delete Column Stel
Alter Table Student
DROP COLUMN Stel 3. Modify Column Sdept alter Table Student
alter COLUMN sdept char (8) Sno char (8)
Iii. Creating and deleting Indexes 1. Establish an index of sage ascending by age in table student index:
CREATE INDEX S_index on Student (Sage) 2. Delete Index DROP INDEX Student s_index
four, connection query. when connecting to a table, the most commonly used join condition is an equivalent connection, which is the connection made to make the corresponding column equal in two tables, usually one column is the primary key of the table, the other column is the primary key or foreign key of the table, and only such an equivalent connection is meaningful. For example, there are two tables, the courses table (Cno,cname,credit) and the enrolls table (Sno,cno,grade), respectively. Check the course name selected by all students: Select Sno, Enrolls.cno, CNAME, grade from enrolls, courses WHERE
enrolls.cno = Cour Ses.cno Seven, there is a need to have the words, the specific questions forget. Having
clause that filters out only the groups that meet the specified criteria. Note that
the clause can only be used in conjunction with the GROUP BY clause to filter out the grouped information that meets the criteria. Similar topics are as follows: Query the student table for each department with more than three students. Select Department from Student
Group by department has COUNT (*) >= 3.
11, stored procedures (two parameters, according to the input parameters to query the good data returned to the output parameters) For example, create a stored procedure procgetdepname, which has 1 input parameters @sno, also with 1 output parameters @departmentname, function: According to the input of the school number, find the student's department, the output of the department name. CREATE PROCEDURE Procgetdepname @sno nvarchar (ten), @DepartmentName nvarchar () output
as
begin Select @DepartmentName = Departmentname From Department D, Student s where D.departmentid = S.departmentid and S.sno = @sno
End
Partial Questions in database