ASP. NET database Surface questions (Basic)

Today to a company written test, database test more, but to tell the truth, the test is also a relatively basic. Now while the memory is up, the database knowledge is simply organized as follows:

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 (15)

)

Second, add columns, delete columns, modify columns

1. Add Column Stel

Alter table Student ADD Stel Char (12)

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 name of the course selected by all students:

Select Sno, Enrolls.cno, CNAME, grade from enrolls, courses WHERE ENROLLS.CNO = Courses.cno

Five, single-table query, remove duplicate rows

such as querying the names of all the departments in the student table, removing duplicate rows

Select DISTINCT department from student

Vi. common conditional expression operators In,not in;between,and,not like.

In the above student and enrolls tables, the number and name of the score above 80 are queried.

Select Sno, sname from Student where Sno in (select Sno from enrolls where grade > 80)

The SQL statement above is also a nested query.

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.

Viii. Inserting data

1, single-line insertion, such as in the above student table inserted students Wang Qiang information.

Insert into Student (sno,sname,ssex,sage,sdept)

Values (' 2005012 ', ' Wang Qiang ', ' Male ', 18, ' computer ')

2, multirow insertion, for example, each student to repair the operating system C2 This course, will choose the course information into the table enrolls.

INSERT Intto enrolls (SNO,CNO)

SELECT Sno, ' C2 ' from Student

Ix. Modification of data

For example, to the enrolls this table elective operating system of the course students to revise the results of 60 points.

UPDATE enrolls

SET Grade = 60

Wher CNO in

(SELECT CNO from courses WHERE CNAME = ' operating system ')

X. Deletion of data

For example, delete a student who is 20 years old or older in the student table.

Delete from Student where Sage > 20

Delete data from the entire table delete from Student

Xi. stored procedure (two parameters, parameters returned to output after querying good data based on input 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 (10),

@DepartmentName nvarchar () output

As

Begin

Select @DepartmentName = Departmentname

From Department D, Student s

where D.departmentid = S.departmentid and

S.sno = @sno

End

12, database commonly used data types and functions.

First big class: Integer data

The Bit:bit data type represents 0,1 or null, which means true,false. Consumes 1byte.

int: A positive negative number is stored in 4 bytes. The storage range is: -2^31 to 2^31-1.

smallint: A positive negative number is stored in 2 bytes. Storage range: -2^15 to 2^15-1.

Tinyint: is the smallest integer type, with only 1 bytes, range: 0 to ^8-1.

Second class: Accurate numerical data

Numeric: The number represented can reach 38 bits, and the number of bytes used to store the data varies with the number of bits used for use.

Decimal: Similar to numeric.

Class Three: Approximate floating-point numeric data

Float: Use 8 bytes to store data. Up to 53 bits. Range: -1.79E+308 to 1.79E+308.

Real: The number of bits is 24, with 4 bytes, and the numeric range: -3.04E+38 to 3.04E+38.

Fourth Category: Date-time data

Datatime: Indicates that the time range can be expressed from 1753/1/1 to 9999/12/31, time can be expressed to 3.33/1000 seconds. Use 8 bytes.

SmallDateTime: Represents a time range that can be represented from 1900/1/1 to 2079/12/31, using 4 bytes.

Category five: String data

Char: Length is set, immutable. The minimum is 1 bytes, and the maximum is 8,000 bytes. The insufficient length is blank.

VARCHAR: the length is variable. The shortest is 1 bytes, the longest is 8,000 bytes, and the trailing blanks are removed.

Text: Long-width is also set, the longest can be stored 2G of data.

Category six: Unincode string data

NCHAR: The length is set, the shortest is 1 bytes, and the maximum is 4,000 bytes. The insufficient length is blank, and storing one character requires 2 bytes.

Nvarchar: length is set, variable. The minimum is 1 bytes and the maximum is 4,000 bytes. The trailing blanks are removed. It takes 2 bytes to store a single character.

ntext: The length is set, the shortest is 1 bytes, the longest is 2G. The trailing blanks are removed and 2 bytes are required to store a single character.

Seventh Category: Currency data types

Money: The recorded amount ranges from 92233720368577.5808 to 92233720368577.5807. Requires 8 bytes.

SmallMoney: The recorded amount ranges from 214748.3648 to 214748.36487. Requires 4 bytes.

Eighth Class: Tag data

Timestamp: This data type is unique in every table! When one record in the table changes, the timestamp field of the record is automatically updated.

uniqueidentifier: The only record used to identify many tables within a database.

Nineth Category: Binary character string data

Binary: fixed-length binary string field with a minimum of 1 and a maximum of 8000.

varbinary: Unlike binary, which has a data tail of 00 o'clock, varbinary will remove it.

Image: A variable-length binary string with a maximum size of 2G.

ASP. NET database Surface questions (Basic)