Shulou(Shulou.com)06/01 Report--

Basic statement of SQL server database

1. Experimental requirements:

1. Query the information of all students in the student table

Select * from student

As shown in the figure

2. Query the columns of "name", "class" and "grade" in the student table.

Select name, class, grade from student

As shown in the figure

3. Query the names of students in Class 7 in the student table.

Select name from student class = 7

As shown in the figure

4. Query all the information of the students with scores of 90-100 in the student table

Select * from student where score between 90 and 100

As shown in the figure

5. Query all the information of students whose scores are lower than 90 or higher than 95 in student table.

Select * from student where score 95

As shown in the figure

2. Query all the information of the students whose scores are 89 and 90 in student table.

Select * from student where score in (89 ~ 90)

As shown in the figure

3. Query all the information of the student surnamed Liu in the student table

Select * from student where name like 'Liu%'

As shown in the figure

4. Query the student information of Class 1 named Zhang Hong in the student table.

Select * from student where name = 'Zhang Hong' and class = 1

As shown in the figure

5. Query all the information of students whose comments are not empty in the student table.

Select * from student where remarks is not null

As shown in the figure

10. Query the data of the first three rows in the student table

Select top 3 * from student

As shown in the figure

11. Query the two columns of "name" and "× × ×" in the student table. The name of the "name" column is displayed as "name", and the column name of "× × ×" is displayed as "idcard". (name is an alias for the name, and idcard is an alias for the × × number).

Select name as name, × × number as idcard from student

As shown in the figure

12. Query the total scores of all students in the student table, and the column name is displayed as "Total score" (Note: the total score is only an individual name that can be set to any such as: asdas,zc,. Can only be in English or Chinese, not numbers)

Select sum (grade) total score * from student

As shown in the figure

13. Query all the student information in the student table, and display the query results according to the score from high to low

Select * from student order by score desc

As shown in the figure

14. Query the average scores of all students in the student table (Note the average score here is only a name and can be set to any Chinese or English)

Select AVG grade point average (from student)

As shown in the figure

15. Query the highest and lowest scores of all students in the student table (Note: the highest or lowest scores here can be named arbitrarily)

The highest score of select MAX and the lowest score of from student of MIN

As shown in the figure

16. Query the number of rows in the student table

Select count (*) Total number of rows (zh) from student

As shown in the figure

17. Query the total score of each class in the student table

Select class, sum (grade) total grade from student group by class

As shown in the figure

18. Query the classes with a total score of more than 181in the student table

Select class, sum (grade), zcj from student group by class having sum (grade) > 181,

As shown in the figure

19. Save the student information of Class 1 in the student table in Table student_ 1

Select * into zc from student where class = 1

As shown in the figure

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.