SQL Server review notes 04

-- Five, compute by, grouping based on the columns after compute by and order by, select * From book1order by title desccompute AVG (pricing) by title -- 6. group byselect number, count (title) from book1group by title/* error message 8120, level 16, status 1, row 1st Select column 'book1. the number 'is invalid because the column is not included in the aggregate function or group by clause. Note: The column selected must be the */select title, count (title) as 'number of books 'from book1group by title -- seven. having, which has the same meaning as where, but where is more efficient, but where cannot use aggregate functions, while select and having can -- books with a parity price greater than 60 can only use the name of havingselect here, AVG (pricing) as 'average price' from book1group by title having AVG (pricing)> 60 -- as a result, we can see that where is a concern for the original record, having is used to overwrite the query results. nested query. Note: Order by cannot be used in subqueries. It can only be used to sort the final query results; the subquery returns only one column of Data select * From book1where pricing> (select AVG (pricing) from book1)/* 9. union, Two rules: 1. The order of the columns and columns in all queries must be the same. column N in Table 1 corresponds to column N in Table 2. Column 2 must be compatible with the corresponding data types, that is, either the data type must be the same, or SQL Server can be converted from one data type to another. the column name is based on table 1, therefore, you must change the column name in Table 1 */select number as 'book1 number and book2 title 'from book1unionselect title from book2order by title/* message 104, level 16, status 1, if the statement contains the Union, intersect, or except t operator, the order by item must appear in the selection list. Obviously, here 0 rder by is not followed by the column in table 2 */select number as 'book1 number and book2 title 'from book1unionselect title from book2order by number -- 10, multi-Table query and connection. 1. internal Connections: equivalent connections and natural connections; 2. outer connection: left outer connection, right outer connection, and full connection; 3. cross join -- 1. cartesian product select * From book1, teacher -- 2. the connection condition, in general, N table connections need to have N-1 conditions, multiple tables or views can be in the same database, you can also disable -- insert data into the teacher table exec sp_help teacherinsert into teacher values ('000000', 'zhangsan', 'mal', 35, '000000', '000000 ') -- insert into teacher values ('123456', 'lily', 'male', 35, '123456', '10 172 ') -- insert into teacher values into the complete table structure ('000000', 'small 7', 'female', 35, '000000', '000000 ') -- insert into teacher values into the complete table structure ('123456', '5', 'female ', 35, '123456', '1234568 ') -- insert into teacher values into the complete table structure ('20170101', 'hedong ', 'female', 35, '20160301', '20160301') Select * from teacher -- (1) equivalent join. If the join column appears twice, select book1. number, book1. name, book1. name, book1. price, and teacher will appear. title from book1, teacherwhere book1. No. = teacher. number -- when multiple tables have the same column name, you must use [table_name]. [Colum Select * From book1, teacher where book1. No. = teacher. no. -- (2) Native join. You can remove duplicate columns, that is, on the equivalent join, use the select clause to specify the column name in the result table select book1. *, author name, title, contact number from book1, teacherwhere book1. No. = teacher. no. -- (3) Unequal connection select book1. *, author name, title, contact number from book1, teacherwhere book1. No. <> teacher. select book1. No., book1. title, book2. No., book2. title from book1, book2where book1. No. <> book2. No. -- (4) Self-join, a table is connected by itself, in this way, we can associate rows in a table with rows in select. ID,. press, A. title,. pricing, B. no. As B No. From book1 as A, book1 as bwhere. press = B. press and. no. <> '20180101' and B. number = '000000' -- (5) left Outer Join, which is equivalent to the first line of natural join, and then add the rows that do not match in the left table to the result set of natural join, fill in the right table with a null value: Select book1. *, author name, title, contact number from book1, teacherwhere book1. no. * = teacher. /* error: Message 4147, Level 15, status 1, 2nd rows. This query uses not the ANSI external join operator ("* =" or "= *"). If you want to run this query without modification, use the set compatibility_level option of alter database to set the compatibility level of the current database to 80. We strongly recommend that you use the ANSI external join operator (left Outer Join and right Outer Join) to override this query. In future SQL Server versions, non-ANSI join operators are not supported even in backward compatible mode. Modify as follows: */select book1. *, author name, title, contact number from book1 left Outer Join teacheron book1. No. = teacher. number -- (6) Right Outer Join, which is equivalent to the first line of natural join, then add the rows that do not match in the right table to the natural join result set, fill the left table with a null value select book1 .*, author name, title, contact number from book1 right Outer Join teacheron book1. No. = teacher. no. -- (7) Full outer join. The null values in both left and right tables are filled in select book1. *, author name, title, and contact number from book1 full join teacheron book1. No. = teacher. no. -- (8) cross join. The number of columns is the sum of the columns in the two tables. The number of rows is the product of the two tables. Select * From book1, teacher -- 10. exists and inselect numbers, title, pricing from book1where exists (select number from teacher) Select * From teacherselect number, title, pricing from book1where number in (select number from teacher)