010. Simple query, group statistics query, multi-table connection query (SQL instance)

-------------------------------------day3------------

--Add multiple rows of data:
--
--
--insert [into] table name [(List of columns)]
--select UNION

--select ' HAHA ', 1,32
--union All-show/show duplicate data even if the collection is the same
--union---------Merges the two result sets of the query. Structure must be consistent--common face questions
--select ' HEHE ', 2,33
------Add query results to the list (subquery)
INSERT into LESSON (ccode,cname,cnum)
SELECT ' C001 ', ' C # Programming Basics ', UNION
SELECT ' C002 ', ' C#oop ', UNION
SELECT ' C003 ', ' C#prame ', 36
SELECT *from Lesson

--------------------------------------------------------------------------------------------------------------- ----------------------------------

--Implementing a data backup of the table (copied to another table)

--1 Generate table query, SELECT into

--Prepare: Query

--select* from Info
--SELECT *---Responsible for column display
--from info--source of data
--WHERE--Query conditions Eg:where CN Ame= ' C#oop '
--true display, false
--where birthday> ' 1990-01-01 '
SELECT sex from info
WHERE SEX was not NULL

SELECT SNAME from info
WHERE SNAME like '% Month '

------back up INFO table to Info--bak
--1 Info_bak
-- 2 Insert query Data
Select *into Intfo_bak from INFO-two functions to create a new table Insert query data
--note constraint does not back up identity property backup (Identity)
SELECT * into Info_ba K1 from Info
WHERE 1=2
-Back up the info table for female students to Info_bak

--insert into Intfo_bak (SNAME SEX BIRTHDAY [Addre SS] Tel)
--select sname,sex, BIRTHDAY, [ADDRESS] Tel from INFO WHERE sex=0
---------DML Data Manipulation Language
----SQL Basic Skills

----Insert Increase
--grammar insert [into] tab_name[(Co1name,co2name .... List of columns)]
--(table name)
--VALUES (Val1,val2 .... The list of values matches the list of columns), (), ()---can add multiple lines

--eg (add data for all columns in the table)
--1
Insert into info (Sname,sex,birthday,[address],tel)
VALUES (' Wol ', 0, ' 1982-3-6 ', ' Yongji ', ' 15511122221 ')
SELECT * FROM Info
Insert Info
VALUES (' Rent name ', 1, ' 1980-2-25 ', ' Beijing ', ' 010-13112345 ')
Select *from Info
Insert Info
VALUES (' Leopard ', 1, ' 1970-5-9 ', ' Tianjin ', ' 13412362322 ')
Insert Info
VALUES (' Zhaoyue ', 0, ' 1988-4-26 ', ' Changsha ', ' 03318658888 ')
Insert Lesson
VALUES (' S1001 ', ' C # language program logic and implementation ', 52)
Insert Lesson
VALUES (' S2099 ', '. NET platform in C # language ', 56)
Insert Lesson
VALUES (' S2002 ', ' WinForm programming ', 48)
Insert Lesson
VALUES (' S1015 ', ' C # Object-oriented programming ', 68)
Insert Lesson
VALUES (' S3001 ', null,null)
Select *from Lesson

--Modification
--Syntax: UPDATE tab_name SET colname=val,conlname1=val ....
--[where]---filter rows
--select *from Info
--update info set sex=1,[address]= ' eight group '
--where sex is null and [address] is null

--update Info Set sname = ' Zhang Qingrui ', sex=0,[address]= ' eight Group A brother '
--where snumb=1

--UPDATE Info SET [ADDRESS] = ' Bjhaidian '
--where [address] like ' 10% '
--Remove Delete (row)
--Syntax: DELETE [from] Tab_name
--[WHERE filter Row]--If you do not write all rows are deleted
--EG1 DELETE from INFO
--WHERE SEX = 0 Delete female classmates
--EG2 Delete INFO drop table


---TRUNCATE table truncation tables Process DROP table----> CREATE table
--Syntax: TRUNCATE TABLE INFO

--Common face test
--Q:delete whether the data in the table is logged and whether it can be recovered
--a: The log content is the delete behavior of the row data, can be restored
--q:truncate table whether the log is logged, whether it can be restored
--A: The Oracle Database DDL operation does not log and cannot be recovered;
--The SQL Server database DDL operation is logged, and the operation of the data page is recorded to recover
--q: Emptying massive data DELETE and TRUNCATE TABLE which is high efficiency
--a:truncate TABLE is efficient because he deletes and logs the log as a data page, all relative to the delete
--It's more efficient to operate and record fewer times.


--Query

--1 Simple Query
--Syntax: SELECT display content
-From Data source (table, view, result set)
--[WHERE condition]--Filter rows
--[GROUP by group]
--[having group conditions]
--[Order by Sort Ascending (default) ASC Descending desc]
--Basics: SELECT (read-only operation read from data file, temporarily stored in memory)
--Query efficiency: (15seconds)
--SELECT Focus: Efficiency
--1.select * (all columns) is not recommended, only some columns are required, * the underlying needs to be parsed (
--knowing which columns are covered--it takes time to influence efficiency.
--2.where condition (bool type) defaults to the way full table traversal
--<,>,=,<=,>=,!=
--and or not
-Is null, was NOT NULL
--like &,_,[],[^]
--between and, not between and
-In (), not in ()
--3. Order by Sort ascending ASC Descending desc
--1. Sorting occurs in memory, sorting the query result set, as little as possible in
--Sort on the server, you don't have to use it. It is best to queue at the client
--Preface
--Sort by salary
SELECT * from EMP
ORDER by SAL DESC

--Sort by an expression
SELECT * from EMP
ORDER by sal+ ISNULL (comm,0) DESC
--by multi-criteria sorting principle: If the first condition is sorted with the same data, you can
--Continue sorting by the second condition
SELECT * from EMp
ORDER by SAL DESC, Hiredata ASC
--4.top the first n of the result set
Select Top 3 * from EMP
Select top percent * from emp
--eg Query 5 Employees with the highest wages
SELECT TOP 5* from EMP
ORDER by sal+ ISNULL (comm,0) DESC
--5. DISTINCT Hide Duplicate data
SELECT DISTINCT JOB from EMP
--6. Alias
SELECT ename as name, job job, Upper =mgr,hiredate entry time from EMP
--7. Display by format: Employees: 7369 Name: Smith job is: Clerk
SELECT ' Employees: ' +cast (empno as VARCHAR (10)), ' Name: ' +ename, ' work is: ' +job from EMP
---Turning data into a specified type
--2 Group Statistics Query:

------Statistical functions: Aggregate functions
--max (), MIN (), SUM (), AVG (), COUNT ()
--Average salary SELECT COUNT (*), AVG (SAL), MAX (SAL) from EMP
----COUNT (*), Count (column)
--The number is not counted when the column has null and COUNT (*) is NOT NULL
--max (), Min (), count (), can SUM (), AVG () can not
--No, the where statement is followed by the condition of the bool variable
--if there is an aggregate function after the SELECT statement, the other fields cannot be displayed after the Select,
--Unless the field is written in an aggregate function



-----------Grouping statements

SELECT * from EMP

---------count several employees in each department
SELECT Deptno,count (EMPNO) number from EMP
GROUP by DEPTNO
Having COUNT (EMPNO) >3-----Filter group conditions, must follow GROUP by

-----------EG: Statistics on the number of people who pay more than 2000 per department and show that the number of persons at least two departments

SELECT Job,count (EMPNO) number from EMP
WHERE sal>2000
GROUP by JOB
Having COUNT (EMPNO) >=2

-----Filter Order WHERE (cannot be followed by aggregate function)/group by/having (often with aggregate functions, rarely followed by fields)

-------------------SELECT can be followed by grouping fields and aggregate functions

--------------------can be grouped by multiple groups of columns, separated by


------------------------------------------------Multi-Table connection query
--Two table connection queries do not necessarily have a primary foreign key relationship
--You need to combine the data of multiple tables, you need to query the multi-table connection
--------1. Cartesian product (Base (relational algebra)) also known as: Cross join result set

SELECT * from Emp,dept
SELECT *from EMP Cross JOIN DEPT

---------2. Inner connection ([inner] join)

--Two rows with equal fields in the table

------EG 1.
SELECT * from Emp,dept
WHERE EMP. Deptno=dept. DEPTNO-----------> Internal connection

SELECT * from emp JOIN DEPT on EMP. Deptno=dept. DEPTNO and job= ' clerk '

--info LESSON Score
--1 Check the exam information of all the students who took the exam (candidate name, reference subject, exam results)
--2 a further inquiry into passing the exam results
SELECT Sname,cname,score from Info,lesson,score
WHERE Info.snumb=score.snumb and Lesson.ccode=score.ccode

SELECT Sname,cname,score
From score JOIN INFO
On Info.snumb=score.snumb
JOIN Lesson
On Lesson.ccode=score.ccode

---------3. External connection
-------------LEFT OUTER join: Left table full display, right table shows rows equal to left table associated fields
-------------right outer joins: the right table is fully displayed, and the left table shows the rows that are equal to the right table associated fields

--eg
SELECT *from EMP left JOIN DEPT
On EMP. Deptno=dept. DEPTNO
-------------Full Connection (principle: Left table is fully displayed, the right table is fully displayed, the same part of the association is displayed only once)
SELECT * from EMP full JOIN DEPT
On EMP. Deptno=dept. DEPTNO
--------4. Self-linking (common written questions)
--1 how to tell the self-connected what situation to use self-connected
-------when the value of the same column is compared to each other by Venus
--2 self-connected usage syntax (connect yourself, be sure to have an alias)
SELECT * from EMP as a,emp as B
--------------------

010. Simple query, group statistics query, multi-table connection query (SQL instance)