Basic operation of Sql_server

1.---------------Basic operation of the database

Primary key: 1. Do not repeat 2. NOT NULL
FOREIGN key

1. Cancel duplicate rows (eliminate exactly the same row, leaving one row)
SELECT DISTINCT cloumname1,cloumname2 from tablename

2. Interval query (two types are one meaning)
SELECT * from emp where Sal > and Sal < 2500

SELECT * from EMP where Sal between and 2500 (the efficiency will be a little higher)

3. Fuzzy query
SELECT * from emp where name like '%s% '
(The third of the query name is a row record of S, _ matches any single string)
SELECT * from emp where name like ' __s% '

4. Batch query (two kinds of same, one for many cases, one is for a few cases)
SELECT * from emp where empno = ' 123 ' or empno = ' 234 ' or empno = ' 456 '

SELECT * from EMP where empno in (' 123 ', ' 234 ', ' 456 ')

5. Order
SELECT * from emp ORDER by Empno ASC (Ascending)

SELECT * from emp ORDER by Empno DESC (Descending)
SELECT * from emp ORDER by Empno Desc,sal ASC

6. Ordering aliases

Select Sal*12 as Count_sal from emp order by count_sal

7. Group queries
Select Max (SAL), Deptno from EMP Group by Deptno

Having can filter the results of a grouped query
Select Max (SAL) from EMP Group by DEPTNO have Sal < 2000

8. Multi-Table Query

SELECT * from emp,dept,dept.deptno where dept.dname = ' sun ' and emp.no = dept.no;
(Take an alias query to speed up SQL execution)
SELECT * from emp e,dept d where d.dname = ' sun ' and e.no = d.no;

9. Internal connection
Select Emp.name,dept.name from emp,dept where emp.no = dept.no

10. Single-row subqueries and multiline subqueries
To know that SQL can return more than one record set, you can connect within the time is not loop.
So, to use loops in SQL,
Single-line subquery
SELECT * from emp where job = (select Job from emp where Depton = ' 10 ')

Multi-row subqueries
SELECT * from emp where job in (select Job from emp where Depton = ' 10 ')

Consider the result of the query as a temporary table
Select Emo.ename, Emp.sal tem.myavg from EMP, (select AVG (SAL) Myavg.deptno from EMP Group by DEPTNO) TEM where EMP.DEPTNO = Tem.deptno and Emp.sal > Tem.myavg

11. Paging Query

The first few
Select Top 4 * from emp ORDER by Clownname

Not in the first few of a range
Select Top 4 * from EMP where empno not in (
Select top empno from emp order by Clownname
) Order BY Clownname

12. Delete duplicate statements for a table

SELECT DISTINCT * (choose a field) into (go to a temporary table) #temp from Cat
Delete from Cat
Insert into cat from a select * from #temp
drop table #temp

13. Outer connection (left connection, right connection)
Select W.ename,b.ename from emp W left t join emp b on w.mgr = B.empno
Left outer join refers to: The left table records are all displayed, there are no matching records, no matching records are filled with null

-----------2. Constraints
Not NULL (non-NULL) null and ' not the same
Unique (unique)--and the primary key is not the same, are not allowed to repeat, unique can be emptied (but only one empty), the primary key cannot be emptied
Primary key (primary key) primary key can only have one, but can have a combination of primary key, is the field Union key
Composite PRIMARY Key
Primary KEY (Clownname1,clownname2)

Foreign key (foreign key) defines the relationship between the primary table and the table, and is determined from the table
Check (range) Clownname int ckeck (Clownname > Clownname <25000)
Default (Defaults)

3.-----
Backing Up the database
Backup Database Dataname (DB name) to disk = ' F:/sp.bak ' (the path backed up to)

Deleting a database
Drop Database Dataname

Recovering a Database

Restore Database Dataname (DB name) from disk = ' F:/sp.bak ' (the path backed up to)