MSSQL Basic grammar and instance operation statement _mssql

MS SQL basic syntax and instance operation

One: Create a table and initialize it
============================
Create DATABASE mf2011--Creating databases

Use mf2011--Using a database

CREATE TABLE Dept--Creating the Departments table
(www.jb51.net
Deptno int PRIMARY KEY,--department number (primary key)
Dname nvarchar (30),--Department name
Loc nvarchar (30)--Location of the department
)
-----

CREATE TABLE EMP--Creating the Employees table
(
empno int PRIMARY KEY,--employee number (primary key)
ename nvarchar (30), name of employee
Job nvarchar (30)--type of work for the employee
Mgr Int,--employee's superior
HireDate datetime, Employee's entry time (appointment time)
Sal Numeric (10,2), employee's monthly salary
Comm Numeric (10,2)--year-end bonuses for employees
DEPTNO int foreign key references dept (DEPTNO)--Create a foreign key point to the department table (describe which department the employee belongs to)
)

--------
INSERT INTO Dept values (' Accounting ', ' New York ')
INSERT INTO Dept values (' Reasarch ', ' Dallas ')
INSERT INTO Dept values (' Sales ', ' Chicago ')
INSERT INTO Dept values (, ' Operations ', ' Boston ')
-------

INSERT into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values
(7369, ' Michael ', ' Clerk ', 7902, ' 2010-1-12 ', 675.23,300,20)

INSERT into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values
(7499, ' Allen ', ' salesman ', 7698, ' 2009-1-23 ', 1675.23,322.50,30)

INSERT into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values
(7521, ' Ward ', ' salesman ', 7698, ' 2008-1-3 ', 12675.99,399.50,30)

INSERT into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values
(7566, ' Jones ', ' manager ', 7839, ' 2000-1-1 ', 8675.99,20)

INSERT into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values
(7654, ' Martin ', ' salesman ', 7698, ' 2007-12-31 ', 1275.99,999.00,30)

INSERT into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values
(7782, ' Blake ', ' manager ', 7839, ' 2007-12-20 ', 1275.99,30)

INSERT into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values
(7788, ' Sccot ', ' analyst ', 7566, ' 2003-1-22 ', 1275.99,10)

INSERT into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values
(7781, ' Miller ', ' Opreator ', 7566, ' 2005-10-12 ', 1275.99,40)
Www.jb51.net
INSERT into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values
(7744, ' ADAMC ', ' opreator ', 7566, ' 2006-10-30 ', 1244.0,40)

INSERT into EMP (EMPNO,ENAME,JOB,HIREDATE,SAL,DEPTNO) values
(7839, ' king ', ' president ', ' 2000-1-1 ', 100244.0,10)

INSERT into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values
(1999, ' Lxliog ', ' opreator ', 7566, ' 2006-10-30 ', 1244.0,40)
-----
SELECT * FROM Dept
SELECT * FROM emp

--PRIMARY key: Unique, Non-empty
--FOREIGN key: only point to primary key, must be consistent with the primary key data type pointed to

Two: Practice
============================
1, Query the EMP table all columns
SELECT * FROM EMP--Liu

2, the query specifies the column (for example: Name, salary two columns)
Select Ename,sal from emp--Zhengxiang

3, Cancel duplicate rows (query the department number from the EMP table)
SELECT DISTINCT (DEPTNO) from EMP--Xu Shao, Yan Fang, meter, Ma Dongqin

4, from the EMP table query name for LXLIOG employee's salary, work, department
Select Sal,job,deptno from emp where ' Lxliog ' =ename-Yaowen, Tan Xieling, Li Xiaolong

5, there are a total of fewer departments (two ways: from EMP or from dept)
Select COUNT (deptno) from dept-Nobuchairen
Select COUNT (Distinct (DEPTNO)) as department total from EMP--Nobuchairen

Use mf2011
SELECT * FROM dept;
SELECT * from EMP;
--6, showing the annual salary of each employee
Select IsNull (sal,0) *12+isnull (comm,0) as yearly salary from EMP--Zasidoj

7. How to show employee information with a salary above 3000
SELECT * FROM emp where sal>3000-Deng Wenwen
Www.jb51.net
8, how to find the 1982.1.1 post-entry staff
SELECT * from emp where hiredate> ' 1982-1-1 '

9. How to show employee status between 2000 and 2500 salary
SELECT * from EMP where Sal between 1244 and 100244
SELECT * from emp where sal>=2000 and sal<=250000

10, how to display the first letter S employee name and salary (fuzzy query)
Select Ename, sal from EMP where ename like ' s% '

11, how to display the name and salary of all employees with the third letter ' O ' (fuzzy query)
Select Ename name, Sal as monthly salary from the EMP where ename like ' __r% ';

12, how to display the employee status of the Empno 123,345,800 ... in the Where condition (using in)
SELECT * from EMP where empno in (123,345,800,1999,7400,7744);

13, how do i show an employee without a superior (operator with IS null)
SELECT * from emp where Mgr is null;

14, query employees who pay more than 1000 or post as manager,
and also to satisfy their initials as J (using logical operators)
SELECT * from emp where (sal>1000 or job= ' manager ')
and ename like ' j% ';

16, how to display the employee's information in order of salary from low to high
(using the ORDER BY clause ASC, DESC)
SELECT * from emp order BY Sal Desc;

17, how to display the employee's information in the Order of entry (using the ordering BY clause ASC, DESC)
SELECT * from EMP hiredate desc;

18, display employee information in descending order by department number and employee's wages
(using the order by phrase Asc,desc)
SELECT * from emp ORDER by DEPTNO Asc,sal desc;

19, count each person's annual salary, and sort from lowest to highest (using column alias sort)
Select IsNull (sal,0) *12+isnull (comm,0) annual salary from the EMP order by annual salary;

20. How to show the employee's maximum wage and minimum wage
Select Max (sal) as "Max (sal)", Min (sal) from EMP;

21, how to display the minimum wage of employee information
SELECT * from emp where sal = (select min (sal) from EMP);
Www.jb51.net
22, showing the total wage and salary of all employees
Select AVG (SAL) as "avg (SAL)", Sum (sal) as "sum (SAL)" from EMP;

23, the name of the employee above the average wage and his salary show
Select Ename,sal from the EMP where sal> (select AVG (SAL) from EMP);

24, calculate the total number of employees
Select COUNT (ename) from EMP;

25, how to show the average wage and the maximum wage in each department
Select AVG (SAL) as "avg", Max (SAL) as "Max", Deptno
From EMP GROUP BY Deptno

26. How to show average wage and maximum wage in each department above the total average wage
Select AVG (SAL) as "avg", Max (SAL) as "Max", Deptno
From the EMP where sal> (select AVG (SAL) from EMP) GROUP by Deptno

27 show average wage and minimum wage for each position in each department
Select AVG (sal), Min (sal), deptno,job from EMP Group by Deptno,job
ORDER BY Deptno

28, showing average wage less than 2000 department number and its average wage
(having is often used in conjunction with group by to filter the results of grouped queries)
Select AVG (SAL), Deptno from EMP Group BY DEPTNO have avg (SAL) <2000

29, using Enterprise Manager to manipulate the separation and addition of the database
See Operation Steps

30, use Enterprise Manager to operate the database backup and restore operation
See Operation Steps

31, using the Query Analyzer to manipulate the backup and restore operations of the database
(1) Backing Up the database
Backup database mf2011 to disk= ' F:/lxliog.bak ';
(2) Delete database
Drop database mf2011;
(3) Restore the database
Restore database mf2011 from disk= ' F:/lxliog.bak ';

Complex queries (multiple-table queries)
32, showing the position of the sales department and the names of its employees
Select D.loc,e.ename from emp e,dept d where d.dname= ' sales ' and D.deptno=e.deptno

33, showing the employee's name, employee's salary and the name of the department (alias when the field is ambiguous)
Select E.ename,e.sal,d.dname from emp e,dept D where e.deptno=d.deptno
Www.jb51.net
34, Show department Number 10 department name, and the department's employee name and salary
Select D.dname, E.ename, e.sal from emp e,dept D where d.deptno=10 and D.deptno=e.deptno

35, showing the employee's name, employee's salary and the name of the department, sorted by department name
Select E.ename, E.sal, d.dname from emp e,dept D where e.deptno=d.deptno order by d.dname

Self-Connection: Connection query on the same table
36, showing the name of an employee's superior leader (e.g. Smith)
(1) Select Mgr from emp where ename= ' ADAMC '
(2) Select ename from emp where empno= (select Mgr from emp where ename= ' ADAMC ')

37, showing the name of each employee and his superior
Select Worker.ename, boss.ename from EMP worker, EMP boss where worker.mgr=boss.empno

Subquery: A SELECT statement embedded in another SQL statement, also called a nested query.
Single-line subquery: A subquery that returns only one row of data.
38, showing all employees in the same department as Jones
(1) Select Deptno from emp where Ename= ' Jones '
(2) SELECT * from emp where deptno= (select Deptno from emp where ename= ' Jones ')

Multiple-line subquery: A subquery that returns multiple rows of data.
39, the name, position, salary and department number of the employee with the same job as the department Number 10. (Note to use in, cannot use =)
(1) SELECT distinct job from EMP where deptno=20
(2) Select Ename,job,sal,deptno from emp where job in (select distinct job from EMP where deptno=20);

40, on the basis of the results of the above query to exclude 20 department employees
Select Ename,job,sal,deptno from emp where job in (select distinct job from EMP where deptno=20) and
Deptno not in (20);
or select Ename,job,sal,deptno from emp where job in (select distinct job from EMP where deptno=20) and Deptno <>20;

Using subqueries in the FROM clause
41, showing the staff of each department higher than the average wage in the department information
(1) First find out the average wage of each department www.jb51.net
Select AVG (SAL), Deptno from EMP Group by Deptno
(2) Treat the above table as a temporary table
Select E.ename,e.sal,tem.myavg,e.deptno
From EMP E, (select AVG (SAL) Myavg,deptno from EMP Group by DEPTNO) TEM
where E.deptno=tem.deptno and E.sal>tem.myavg

Note: (1) When a subquery is used in the FROM clause, the subquery is treated as a temporary table.
(2) When using subqueries in the FROM clause, you must assign an alias to the subquery

Paging query: Remove 5th to 10th record in ascending order by employee's HireDate property
42, show 5th to 10th record
(1) Show 1th to 4th Records
Select Top 4 empno from emp ORDER by HireDate
(2) display the following 6 records (5th to 10th Records)
Select Top 6 * from EMP where empno
Not in (select Top 4 empno from emp Order by HireDate)
Order BY HireDate;

43, displaying information for persons 11th to 13th entry (IBID.)

44, showing the 5th to 9th person's information, sorted by salary in descending order (similar to the wording)
Select Top 5 * from EMP where EMPNO does not
(select Top 4 empno from emp ORDER BY sal Desc)
ORDER BY Sal Desc;
45, data replication in the same table
(1) Create a temporary table: identity (1,1) indicates that the TestID field is increased, starting from 1 each time +1
CREATE TABLE Test (
TestID int PRIMARY key identity (1,1),
TestName varchar (30),
Testpass varchar (30)
)
(2) Inserting a piece of data
INSERT into Test (testname, Testpass) VALUES (' Zhangsan ', ' 123456 ');
(3) Copy data
INSERT into Test (testname, Testpass) (select Testname,testpass from Test);

46, query TestID for 第10000-10009 data to see performance.
Select top * from test where TestID does not
(select top 9999 TestID from Test order by TestID)
ORDER BY TestID

Create a new table with query results (a quick way to build a table)
47, Syntax: SELECT * (You can select a field here) into another surface from table
Select Testname,testpass into MyTest from test where testid<8
Table MyTest has been created in the above statement, and initialization of the data
and set the TestID as the primary key: ALTER TABLE test01 ADD primary KEY (TestID)
Www.jb51.net
48, delete duplicate data from a table
(1) CREATE TABLE Cat (
CatId int,
CatName varchar (40)
)
(2) insert into cat values (1, ' AA '); Repeated execution several times
INSERT into cat values (2, ' BB '); Repeated execution several times
(3) distinct * into #temp the result of the recording distinct of cat is inserted into a temporary table #temp
Delete from cat;//clears the records of the cat table
INSERT into the cat select * FROM #temp//Inserts the #temp table data (no duplicate data) into the Cat table
drop table #temp;//Delete Tables #temp3

Left outer and right outer joins
Left OUTER join: The query data on the left table is all displayed, and the table on the right is filled with null if there is no matching data
Right outer join: The query data for the right table is all displayed, and the table in the left is filled with null if there is no matching data
49, display the names of all employees and their superiors in the EMP table (see the difference)
(1) LEFT OUTER join: Select E.ename Employee Name, B.ename Superior name
From EMP e-LEFT JOIN EMP B on e.mgr=b.empno;
(2) Right outer connection: Select E.ename employee Name, B.ename Superior name
From the EMP e right join the EMP B on E.mgr=b.empno;

Common constraints:
(1) Not NULL, Non-empty
(2) Unique, unique, allowing the occurrence of a null
(3) Primary key, primary key, unique, Non-empty
(4) Foreign key, foreign key, define the main table and the association relationship from the table
(5) Check, checking, mandatory data must meet the defined conditions, such as: Sal int check (sal>=2000 and sal<=3000)
(6) Default, defaults, for data integrity, for example: Birthday datetime default GETDATE (),

50, compound primary key can only be defined with table level
For example: CREATE TABLE Cat (
CatId int,
CatName varchar (40),
Catage int,
Primary KEY (CatId, CatName)
)
Www.jb51.net
51, Merchandise Sales System table Design case: The existing store database, record customers and their shopping, composed of the following three tables: merchandise (goods), customer (Customers), buy (purchase)
Commodity goods (commodity number GOODSID, commodity name Goodsname, Unit price UnitPrice, product category category, supplier provider);
Client Customer (Customer number CustomerID, name, address, email, gender sex, id cardid);
Purchase Purchase (Customer number CustomerID, commodity number Goodsid, purchase quantity nums);
(1) The establishment of a table, in the definition of requirements to declare:
• Primary and foreign keys for each table;
U customer's name cannot be empty;
Your unit price must be greater than 0 and the purchase quantity must be between 1 and 10
U e-mail cannot be repeated
U customer's gender must be: male or female, default is male
The categories of goods are: food, daily necessities

----Goods Table
Create Table Goods (
Goodsid nvarchar () primary key,
GOODSNAMD nvarchar () NOT NULL,
UnitPrice Numeric (10,2) Check (unitprice>0)
Category nvarchar (3) Check (category in (' Food ', ' commodity ')),
Provider nvarchar (50)
)
----Customer
Create Table Customer (
Customerid nvarchar () primary key,
Cusname nvarchar (m) not NULL,
Address nvarchar (100),
Email nvarchar Unique,
Sex nchar (1) Check (Sex in (' Male ', ' female ')) default ' male ',
Cardid nvarchar (18)
) www.jb51.net
----Purchase
Create Table Purchase (
Customerid nvarchar foreign key references customer (Customerid),
Goodsid nvarchar foreign key references goods (GOODSID),
nums int Check (nums>0 and nums<10),
Primary KEY (CustomerID, GOODSID)
)

Author qq395740774