MSSQL basic syntax and instance operation statement

Ms SQL basic syntax and instance operations

I. Create and initialize a table
======================================
Create database mf2011 -- create a database

Use mf2011 -- use a database

Create table dept -- create a "department" table
(Www.2cto.com
Deptno int primary key, -- department ID (primary key)
Dname nvarchar (30), -- department name
Loc nvarchar (30)-Location of the Department
)
-----

Create table emp -- create an "employee" table
(
Empno int primary key, -- employee ID (primary key)
Ename nvarchar (30), -- employee name
Job nvarchar (30), -- employee's job type
Mgr int, -- employee superior
Hiredate datetime, -- employee induction time (employment time)
Sal numeric (), -- employee's monthly salary
Comm numeric (), -- year-end bonus for employees
Deptno int foreign key references dept (deptno) -- create a foreign key pointing to the Department table (describe which department the employee belongs)
)

--------
Insert into dept values (10, 'accounting', 'New york ')
Insert into dept values (20, 'reasarch ', 'Dallas ')
Insert into dept values (30, 'sales', 'Chicago ')
Insert into dept values (40, 'operations', 'boston ')
-------

Insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values
(7369, 'Michael ', 'cler', 7902, '2017-1-12', 2010, 675.23, 20)

Insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values
(7499, 'allen', 'salesman', 7698, '2017-1-23 ', 2009, 1675.23, 30)

Insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values
(7521, 'ward ', 'salesman', 7698, '2017-1-3 ', 2008, 12675.99, 30)

Insert into emp (empno, ename, job, mgr, hiredate, comm, deptno) values
(7566, 'Jones ', 'manager', 7839, '2017-1-1', 2000, 20)

Insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values
(7654, 'martin ', 'salesman', 7698, '2017-12-31 ', 2007, 1275.99, 30)

Insert into emp (empno, ename, job, mgr, hiredate, sal, deptno) values
(7782, 'bucke', 'manager', 7839, '2017-12-20 ', 2007, 30)

Insert into emp (empno, ename, job, mgr, hiredate, sal, deptno) values
(7788, 'sccot ', 'analyst', 7566, '2017-1-22', 2003, 10)

Insert into emp (empno, ename, job, mgr, hiredate, sal, deptno) values
(7781, 'miller ', 'opreator', 7566, '2017-10-12', 2005, 40)
Www.2cto.com
Insert into emp (empno, ename, job, mgr, hiredate, sal, deptno) values
(7744, 'adamc', 'opreator', 7566, '2017-10-30 ', 2006, 40)

Insert into emp (empno, ename, job, hiredate, sal, deptno) values
(7839, 'King', 'President ', '2017-1-1', 2000, 10)

Insert into emp (empno, ename, job, mgr, hiredate, sal, deptno) values
(1999, 'lxliog ', 'opreator', 7566, '2017-10-30', 2006, 40)
-----
Select * from dept
Select * from emp

-- Primary key: Unique, not empty
-- Foreign key: it can only point to the primary key. It must be consistent with the data type of the primary key to be pointed.

Ii. Exercises
======================================
1. query all columns in the emp table.
Select * from emp -- Liu junzheng

2. query the specified column (for example, name and salary)
Select ename, sal from emp -- Zheng Chaoyang

3. Cancel duplicate rows (query Department numbers from the emp table)
Select distinct (deptno) from emp -- Xu Shaofeng, Hao yanfang, Liu Hui, Ma dongqin

4. query the salaries, jobs, and departments of employees named lxliog from the emp table.
Select sal, job, deptno from emp where 'lxliog '= ename -- Hou Yaowen, Tan Xueling, Li Xiaolong

5. There are a total of fewer departments in the statistics (two Methods: From emp or from dept)
Select count (deptno) from dept -- novogene
Select count (distinct (deptno) as department count from emp -- novogene

Use mf2011
Select * from dept;
Select * from emp;
-- 6: displays the annual salary of each employee
Select isnull (sal, 0) * 12 + isnull (comm, 0) as annual salary from emp -- zookeeper

7. How to display employee information with a salary higher than 3000
Select * from emp where sal> 3000 -- Deng Wenwen
Www.2cto.com
8. How to find employees who have joined the company after 1982.1.1?
Select * from emp where hiredate> '2014-1-1'

9. How to display the employees with salaries between 2000 and 2500
Select * from emp where sal between 1244 and 100244
Select * from emp where sal> = 2000 and sal <= 250000

10. How to display the employee name and salary whose first letter is S (fuzzy query)
Select ename, sal from emp where ename like's %'

11. How to display the name and salary of all employees whose third letter is 'O' (fuzzy query)
Select ename name, sal as monthly salary from emp where ename like '_ R % ';

12. How to display the employees with empno 123,345,800... (use in the where condition)
Select * from emp where empno in (123,345,800,199 7744 );

13. How to display employees without superiors (using the is null operator)
Select * from emp where mgr is null;

14. query employees whose salaries are higher than 1000 or whose positions are manager,
At the same time, the first letter of their names must be j (using logical operators)
Select * from emp where (sal> 1000 or job = 'manager ')
And ename like 'J % ';

16. How to display employee information in ascending order of salary
(Use the order by clause asc, desc)
Select * from emp order by sal desc;

17. How to display employee information in order of employment (use order by statements asc, desc)
Select * from emp order by hiredate desc;

18. Display employee information in ascending order of Department numbers and in descending order of employees' salaries
(Use the order by clause asc, desc)
Select * from emp order by deptno asc, sal desc;

19. Calculate the annual salary of each person, and sort by column alias in ascending order)
Select isnull (sal, 0) * 12 + isnull (comm, 0) annual salary from emp order by annual salary;

20. How to display the highest salary and minimum wage among employees
Select max (sal) as "max (sal)", min (sal) from emp;

21. How to display the Minimum Wage employee information
Select * from emp where sal = (select min (sal) from emp );
Www.2cto.com
22. display the average and total salaries of all employees
Select avg (sal) as "avg (sal)", sum (sal) as "sum (sal)" from emp;

23. display the name and salary of an employee higher than the average salary
Select ename, sal from emp where sal> (select avg (sal) from emp );

24. calculate the total number of employees
Select count (ename) from emp;

25. How to display the average and maximum salaries of each department
Select avg (sal) as "avg", max (sal) as "max", deptno
From emp group by deptno

26. How to display the average and highest salaries of each department higher than the total average wage
Select avg (sal) as "avg", max (sal) as "max", deptno
From emp where sal> (select avg (sal) from emp) group by deptno

27 shows the average salary 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. shows the average salary of a department number less than 2000 and its average salary
(Having is often used in combination with group by to filter grouping query results)
Select avg (sal), deptno from emp group by deptno having avg (sal) <2000

29. Use the Enterprise Manager to separate and attach Databases
See the procedure

30. Use the Enterprise Manager to perform database backup and recovery operations
See the procedure

31. Use the query analyzer to perform database backup and recovery operations
(1) back up the database
Backup database mf2011 to disk = 'f:/lxliog. bak ';
(2) Delete A Database
Drop database mf2011;
(3) restore the database
Restore database mf2011 from disk = 'f:/lxliog. bak ';

Complex query (Multi-Table query)
32. The location of the sales department and the name of its employee are displayed.
Select d. loc, e. ename from emp e, dept d where d. dname = 'sales' and d. deptno = e. deptno

33. display the employee name, employee salary, and department name (alias used when the field is ambiguous)
Select e. ename, e. sal, d. dname from emp e, dept d where e. deptno = d. deptno
Www.2cto.com
34. The name of the Department with the department Number 10 and the employee name and salary of the Department are displayed.
Select d. dname, e. ename, e. sal from emp e, dept d where d. deptno = 10 and d. deptno = e. deptno

35. display the employee name, employee salary, and department name, 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-join: Query connections in the same table
36. display the name of the superior leadership of an employee (for example, smith)
(1) select mgr from emp where ename = 'adamc'
(2) select ename from emp where empno = (select mgr from emp where ename = 'adamc ')

37. display the name of each employee and his superiors in the company
Select worker. ename, boss. ename from emp worker, emp boss where worker. mgr = boss. empno

Subquery: a select statement embedded in other SQL statements, also known as nested query.
Single Row subquery: returns the subquery Statement of only one row of data.
38. display 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 ')

Multi-row subquery: A subquery that returns multiple rows of data.
39. query the names, positions, salaries, and department numbers of employees with the same job number as 10. (Use in, not =)
(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. exclude employees from 20 departments based on the above query results
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;

Use subquery in the from clause
41. display information of employees whose salaries are higher than the average salaries of each department
(1) first check the average salary of each department www.2cto.com
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 subqueries are used in the from clause, an alias must be specified for the subquery.

Paging query: 5th to 10th records are retrieved in ascending order based on the hiredate attribute of an employee.
42. Display 5th to 10th records
(1) display 1st to 4th records
Select top 4 empno from emp order by hiredate
(2) display the last 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. display information of 11th to 13th new recruits (same as above)

44. display the information of 5th to 9 people in descending order of salary (similar writing)
Select top 5 * from emp where empno not in
(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 () indicates that the testId field is auto-incrementing, starting from 1 each time + 1
Create table test (
TestId int primary key identity (1, 1 ),
TestName varchar (30 ),
TestPass varchar (30)
)
(2) Insert a piece of data
Insert into test (testName, testPass) values ('hangsan', '123 ');
(3) Copying data
Insert into test (testName, testPass) (select testName, testPass from test );

46. query the data whose testId is 2017-10009 to check the performance.
Select top 10 * from test where testId not in
(Select top 9999 testId from test order by testId)
Order by testId

Create a new table using the query results (a quick table creation method)
47. Syntax: select * (field can be selected here) into another table from
Select testName, testPass into mytest from test where testId <8
The table mytest has been created in the preceding statement and the data has been initialized.
And set testId as the primary key: alter table test01 ADD primary key (testId)
Www.2cto.com
48. Delete duplicate data from a table
(1) create table cat (
CatId int,
CatName varchar (40)
)
(2) insert into cat values (1, 'A'); // repeated execution times
Insert into cat values (2, 'bb'); // repeated execution times
(3) select distinct * into # temp from cat; // Insert the result of the cat record distinct into the temporary table # temp
Delete from cat; // clear records in the cat table
Insert into cat select * from # temp; // insert the data in the # temp table (no duplicate data) to the cat table
Drop table # temp; // delete a table # temp3

Left Outer Join and right Outer Join
Left Outer Join: All query data in the left table is displayed. If no matching data exists in the right table, use null to fill it.
Right outer join: All query data in the right table is displayed. If no matching data exists in the left table, use null to fill it.
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 join: select e. ename employee name, B. ename superior name
From emp e right join emp B on e. mgr = B. empno;

Common constraints:
(1) not null, not empty
(2) unique, unique. A null value is allowed.
(3) primary key, primary key, unique, non-empty
(4) foreign key, foreign key, defining the association between the master table and the slave table
(5) check, check, and force data to meet defined conditions, such as sal int check (sal> = 2000 and sal <= 3000)
(6) default, default value, used for data integrity, for example, birthday datetime default getdate (),

50. The composite primary key can only be defined at the table level.
Example: create table cat (
CatId int,
CatName varchar (40 ),
CatAge int,
Primary key (catId, catName)
)
Www.2cto.com
51. Product Sales System Table design case: an existing store database records customers and their shopping conditions. It consists of the following three tables: product (goods), customer (customer ), purchase (purchase)
Product goods (Product NO. goodsId, product name goodsName, unit price unitPrice, product category, supplier provider );
Customer (customer ID customerId, name, address, email, sex, ID card cardId );
Purchase (customer number customerId, product number goodsId, number of purchased nums );
(1) To create a table, declare in the definition:
Ü primary keys and Foreign keys for each table;
Ü the customer's name cannot be blank;
Ü the unit price must be greater than 0, and the purchased quantity must be between 1 and 10
Ü the email cannot be repeated.
Ü the customer's gender must be: male or female. The default is male.
Ü the product category is: food, daily necessities

---- Goods table
Create table goods (
Goodsid nvarchar (50) primary key,
Goodsnamd nvarchar (80) 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 (50) primary key,
Cusname nvarchar (50) not null,
Address nvarchar (100 ),
Email nvarchar (50) unique,
Sex nchar (1) check (sex in ('male', 'female ') default 'male ',
Cardid nvarchar (18)
) Www.2cto.com
---- Purchase
Create table purchase (
Customerid nvarchar (50) foreign key references customer (Customerid ),
Goodsid nvarchar (50) foreign key references goods (goodsid ),
Nums int check (nums> 0 and nums <10 ),
Primary key (customerid, goodsid)
)

Author qq395740774