Favorite common SQL statements & lt; 1 & gt; and SQL statements

Favorite common SQL statements <1> and SQL statements
MSSQL Query

Select * from class; query all columns in the class of the table.

Select * from class; query all columns in the class of the table. select class, teacher from class; queries the column class in the table class, teacherSelect count (*) as count_all from class; returns the total number of rows in the table class to the result set. select sum (studentnumber) as all_student_number from class: returns the total number of studentnumbers in the class table to the result set select avg (studentnumber) as avg_student_number from class: returns the average value of studentnumber in class to Select max (studentnumber) as max_studentnumber from class: calculates the maximum and minimum values of a field. min is the minimum value. select * from class where studentnumber = (Select max (studentnumber) from class): the maximum value can be referenced as a condition. select * from class where studentnumber = 50 (> 50,> = 50, <50, <= 50, <> 50): Return studentnumber = 50 (> 50,> = 50, <50, <= 50, not equal to 50): Select * from class where studentnumber <> 50 and teacher = 'lil' two query conditions are expressed with and, or indicates or. select * from class where studentnumber in (44,55): in indicates that studentnumber is the possible value listed in brackets. select * from class where class in (select class from student): The content in can be the result of another query statement. select distinct class from student: the value of the query field is not repeated select * from class order by studentnumber (asc, desc): sorts the query results in ascending order, or in descending order.

Continued 1

Select class, count (*) from student group by class: the query result is grouped by group. select class, count (*) from student group by class having count (*) = 5: filter the results of the group using conditions select * from student where id <2 UNION (ALL) select * from student where age> 19: UNION: Put the query results of the two query statements together. If there are duplicate rows, delete them. If union all: duplicate rows are not deleted. fuzzy match query: select * from student where name like '% ang %' Integer. The range of the date field can be specified. use betweenselect * from student where born between '2017-05-04 'and '2017-10-18' select *, 12 add a column in the returned result from student, and the value is 12. select RTRIM (class) + RTRIM (teacher) AS name1, studentnumber from class: delete spaces at the end of the two fields and connect them to return. where: The name of the joined field is returned as name1Select class. *, student. * from class, student where class. class = student. class: the content of two tables can be combined for query. fields can also be implemented using the JOIN clause: select * from class JOIN student on class. class = student. classJOIN is divided into internal connections, external connections, left outer connections, and right outer connections. For details, see the relevant database manual.

Add, delete, modify, and delete operations

Insert: Insert into class (class, studentnumber, teacher) values ('gao', 55, 'abc') to specify the field name and value '); insert into class values ('chuyi', 'abc', 55) without specifying the field name; insert multiple records at a time: only multiple Insert statements can be executed. to insert data from another table into the current table, first create a new table: select * into class_bak from class where 1 = 2 insert into class_bak select * from class where class = 'gao' modify: update class set class = 'shanghaier' where class = 'shanghaier' Delete: Delete from class where class = 'shanghaier'

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.