Introduction to SQL Server (SQL Statements)

Data type:

char (fixed length) varchar (length varies with input)
When the length of the string is fixed, use char because char is fast.

nchar and nvarchar adopt Unicode coding on the basis of the former
 Unicode encoding is characterized by bytes and Chinese characters occupying the same space ah

Bit (no need) int bigint floa t numeric (more precise, better control)

datetime	timestamp
 getdate() gets the current time
create table aaa
(shijian datetime)
Insert AAA values (getdate () -- insert system time
 insert aaa values('2019/1/23') - The insert time will / become the default format-
select * from aaa
drop table aaa

Picture type image and video type binary are typically placed on dedicated servers because they take up too much bandwidth
 Put it directly into the database only when security is needed, and as small as possible.

Basic operation

–It's spelling in annotation format.  
–New database  
create database shuguo  
–Simultaneously creating fields for tables  
create table renwu  
( bianhao int,  
xingming nvarchar(5),  
xingbie nchar(1),  
zhiwu nvarchar(10),  
nianling int,  
gongzi numeric(6,2) --Six places, two decimal places  
)  
–Delete tables (including fields) drop Delete only tables and databases  
drop table renwu

select * from renwu  
–alias  
select xingming Full name,zhiwu post from renwu  
–Add Records  
insert into renwu values(1,'Liu Bei','male','Principal',40,9999)  
insert into renwu (bianhao,xingming,xingbie,zhiwu) values(2,'Mrs. Gan','female','Madam')  
insert into renwu values(3,'Zhuge Liang','male','Military adviser',37,8000)  
insert into renwu values(4,'Guan Yu','male','General',38,6000)  
insert into renwu values(5,'Zhang Fei','male','General',35,5000)  
–Delete all records,Undeleted field  
delete from renwu  
–Delete records conditionally  
delete from renwu where bianhao=2  
delete from renwu where xingming='Mrs. Gan'  
–Conditional query  
select * from renwu where gongzi>7000  
select xingming,xingbie,zhiwu,gongzi from renwu where gongzi>7000  
–Increase the wage below 8000 by 2%  
update renwu set gongzi=gongzi*1.02 where gongzi<8000

–Primary keys can be changed. As long as they are not repeated and some data are inserted, the primary keys must be inserted.  
create table gsyg  
( bianhao int primary key,  
xingming nvarchar(5),  
nianling char(2)  
)  
insert gsyg values(1,'Tang Seng','78')  
insert gsyg values(2,'Sun WuKong','50')  
insert gsyg values(3,'Zhu Bajie','57')  
insert gsyg values(4,'Monk Sha','23')  
insert gsyg values(5,'White Dragon Horse','19')  
insert gsyg (bianhao,xingming) values(6,'Buddha')  
update gsyg set nianling=24,xingming='Guanyin Bodhisattva',bianhao=7 where bianhao=6  
update gsyg set nianling=13 where nianling is null --When the data is empty  
delete from gsyg where bianhao=7 and nianling=13  
delete from gsyg where bianhao=7 or nianling=24  
select * from gsyg

Data query

Water Margin Example:  
create database shuihu

create table yuangong  
( ygbianhao int primary key,  
xingming nvarchar(5),  
zhiwu nvarchar(8),  
shangji int,  
rzshijian datetime,  
gongzi numeric(6,1),  
buzhu numeric(5,1),  
bmbianhao int foreign key references bumen(bmbianhao)  
)

insert into yuangong (ygbianhao,xingming,zhiwu,rzshijian,gongzi,buzhu,bmbianhao) values (101,'Song Jiang','Stockaded village owner','2002/3/15',20000,3000,1)  
insert into yuangong values(102,'Lu Junyi','Stockaded village owner',101,'2002/6/16',18000,3000,1)  
insert into yuangong values(103,'Wu Yong','Military adviser',101,'2002/8/19',17000,2800,2)  
insert into yuangong values(104,'Gongsun Sheng','Military adviser',103,'2002/9/22',16000,2600,2)  
insert into yuangong values(105,'Guan Sheng','General',102,'2003/2/15',15000,2300,3)  
insert into yuangong (ygbianhao,xingming,zhiwu,shangji,rzshijian,gongzi,bmbianhao) values(106,'Xu Ning','Young general',105,'2005/7/9',14000,3)  
insert into yuangong values(107,'Lu Zhishen','General',102,'2003/4/22',13000,2000,4)  
insert into yuangong values(108,'Wu Song','General',107,'2003/9/8',12000,1800,4)  
insert into yuangong (ygbianhao,xingming,zhiwu,shangji,rzshijian,gongzi,bmbianhao) values(109,'Li Kui','Young general',107,'2005/4/11',10000,4)  
insert into yuangong values(110,'Li Jun','General',102,'2005/1/6',13000,3000,5)  
insert into yuangong (ygbianhao,xingming,zhiwu,shangji,rzshijian,gongzi,bmbianhao) values(111,'Ruan Xiaoer','Young general',110,'2005/6/24',13000,5)  
insert into yuangong (ygbianhao,xingming,zhiwu,shangji,rzshijian,gongzi,bmbianhao) values(112,'Ruan Xiaoqi','Young general',110,'2005/6/15',12000,5)  
insert into yuangong values(113,'Chai Jin','Main',101,'2004/3/5',11000,1000,6)  
insert into yuangong values(114,'Li Ying','Main',113,'2006/3/10',10000,600,6)  
insert into yuangong (ygbianhao,xingming,zhiwu,shangji,rzshijian,gongzi,bmbianhao) values(115,'Time move','Little Leader',113,'2007/11/19',8000,6)
create table bumen
(
	bmbianhao int primary key,
	mingcheng nvarchar(8),
	didian nvarchar(8)
)
insert into bumen values(1,'Chief Executive','Juyi Hall')  
insert into bumen values(2,'Civilian post','Qianshan')  
insert into bumen values(3,'Ma Jun','Zuo Shan')  
insert into bumen values(4,'Bu Jun','Right mountain')  
insert into bumen values(5,'Navy','at the foot of the hill')  
insert into bumen values(6,'logistics','Hou Shan')

–Inquire about Lu Zhishen's salary, position, department number  
select xingming,gongzi,zhiwu,bmbianhao from yuangong where xingming='Lu Zhishen'  
–There are several departments in the inquiry.(Duplicate removal)  
select distinct bmbianhao from yuangong  
–Display the annual salary of each employee and display fields in Chinese  
select xingming Full name,gongzi_12 Annual salary from yuangong  
–Four operations for calculating annual gross income and null value  
select xingming Full name,gongzi\_12+isnull(buzhu,0)\_12 Total annual income from yuangong  
–Search for employees who have been employed since 2004  
select xingming,rzshijian from yuangong where rzshijian>'2004-1-1'  
–Search for employees with salaries ranging from 13,000 to 1,800  
select xingming Full name,gongzi wages from yuangong where gongzi>13000 and gongzi<=18000  
–Search for Employees surnamed Li  
select xingming Full name from yuangong where xingming like 'plum%'  
–The second word of the query is the incoming employee.  
select xingming Full name from yuangong where xingming like '_enter%'  
–Batch Query  
select xingming Full name,ygbianhao Employee Number from yuangong where ygbianhao in (101,105,108,114)  
–Show the name and position of the leader  
select xingming Full name,zhiwu post from yuangong where shangji is null  
–Sort default ascending order desc Chinese in descending order according to phonetic alphabet and tone  
select xingming Full name,zhiwu post,gongzi wages from yuangong order by gongzi desc  
select xingming Full name from yuangong order by xingming  
–Search for gross and average wages  
select sum(gongzi) Gross wage,avg(gongzi) average wage from yuangong  
–Query for maximum and minimum wages  
select max(gongzi) Maximum wage,min(gongzi) minimum wage from yuangong  
–Query the name and salary of the owner of the highest wage  
select xingming Full name,gongzi wages from yuangong where gongzi=(select max(gongzi) from yuangong)  
–Query the names and salaries of employees above average wages  
select xingming Full name,gongzi wages from yuangong where gongzi>(select avg(gongzi) from yuangong)  
select xingming Full name,gongzi wages,(select avg(gongzi) from yuangong) average wage from yuangong where gongzi>(select avg(gongzi) from yuangong)  
–Increase by department number and decrease by salary  
select xingming Full name,gongzi wages,bmbianhao from yuangong order by bmbianhao,gongzi desc  
–Calculate annual gross income by aliasing and arrange it in descending order  
select xingming Full name,gongzi\_12+isnull(buzhu,0) Total annual income from yuangong order by gongzi\_12+isnull(buzhu,0) desc  
select xingming Full name,gongzi_12+isnull(buzhu,0) Total annual income from yuangong order by Total annual income desc  
–How many records are counted  
select count(*) from yuangong  
–Statistics of average and total wages in each department group by The latter field must appear in the display field  
select bmbianhao,sum(gongzi) Gross wage,avg(gongzi)average wage from yuangong group by bmbianhao  
–Query the ranking of average wage and minimum wage for each position in each department in descending order  
select bmbianhao,avg(gongzi) average wage,min(gongzi)minimum wage,zhiwu post from yuangong group by bmbianhao,zhiwu order by bmbianhao  
–Department numbers and average wages showing average wages below 15,000  
select bmbianhao Department Number,avg(gongzi) average wage from yuangong group by bmbianhao having avg(gongzi) <15000  
–Cartesian Set Phenomenon(Primary foreign keys do not correspond)  
select * from yuangong,bumen  
select * from yuangong,bumen where bumen.mingcheng='Navy' and yuangong.bmbianhao=bumen.bmbianhao  
select * from yuangong,bumen where yuangong.bmbianhao=bumen.bmbianhao  
–Inquiry name and department and number  
select xingming Full name,mingcheng department,yuangong.bmbianhao Department Number from yuangong,bumen where yuangong.bmbianhao=bumen.bmbianhao  
–Search for Department name, staff name and salary of department number 4  
select mingcheng department,xingming Full name,gongzi wages from yuangong,bumen where yuangong.bmbianhao=bumen.bmbianhao and bumen.bmbianhao=4  
–Search for employee name, department name and salary, sorted by Department  
select xingming,mingcheng,gongzi from yuangong,bumen where bumen.bmbianhao=yuangong.bmbianhao order by bumen.bmbianhao desc  
–Query Li Kui's superiors  
select xingming from yuangong where ygbianhao=(select shangji from yuangong where xingming='Li Kui')  
–Query the names of all employees and their superiors (self-connected)  
select a.xingming staff,b.xingming Leader from yuangong a,yuangong b where a.shangji=b.ygbianhao  
–Sub-query (nested query) Single-line sub-query: A sub-query that returns a single-line result is called a single-line sub-query.  
–Show employees in the same department as Lu Zhishen  
select xingming from yuangong where bmbianhao=(select bmbianhao from yuangong where xingming='Lu Zhishen')  
–Multi-row sub-query: A sub-query that returns multiple rows is called a multi-row sub-query.  
–If a multi-line sub-query cannot be written correctly at one time, write two lines. First, write a sub-query, then write the main query.  
–Inquire about the name, position, salary and department number of the staff with the same position as Department 5  
select xingming Full name,zhiwu post,gongzi wages,bmbianhao Department Number from yuangong where zhiwu in  
(select distinct zhiwu from yuangong where bmbianhao=5) and bmbianhao!=5  
–Query the names and salaries of employees who are above the average salary of the department, the number of the Department and the average salary of the department.  
–First get the average wage for each department.  
select avg(gongzi) from yuangong group by bmbianhao  
select xingming Full name,gongzi wages,yuangong.bmbianhao Department Number,pjgz average wage from yuangong,(select avg(gongzi) pjgz,bmbianhao from yuangong group by bmbianhao) gzb where gongzi>pjgz and gzb.bmbianhao=yuangong.bmbianhao  
–Paging query  
–Query first to fifth employees  
select top 5 xingming Full name,zhiwu post,rzshijian Initiation time from yuangong order by rzshijian  
–Query the 6th to 13th employees  
select top (13-6) xingming Full name,zhiwu post,rzshijian Initiation time from yuangong where ygbianhao not in (select top 5 ygbianhao from yuangong order by rzshijian) order by rzshijian  
–Internal connection  
select a.xingming staff,b.xingming Leader from yuangong a,yuangong b where a.shangji=b.ygbianhao  
–Left outer connection  
–All the data in the left table are displayed, while those on the right are displayed, and if they are not, they are empty.  
select a.xingming staff,b.xingming Leader from yuangong a left join yuangong b on a.shangji=b.ygbianhao

Delete duplicate data

–Delete duplicate data  
create table xiaobaio  
(bh int,  
mc nvarchar(5))  
insert into xiaobaio values (1,'Name 1')  
insert into xiaobaio values (2,'Name 2')  
select * from xiaobaio

select distinct * into lsb from xiaobaio  
delete from xiaobaio  
insert into xiaobaio select * from lsb  
drop table lsb  
select * from xiaobaio

constraint

Constraints ensure that data meets the required conditions
- Constraints are divided into not null (non-empty) unique (unique) primary key foreign key check (custom) default (default)
create table biao
( dbbh int primary key,
dbxm varchar(20) unique,
dbmm varchar(20) not null,
nianling int check(nianling>=20 and nianling<=30) default 18,
bmbianhao int foreign key references bumen(bmbianhao) )

Database Separation

Hide the database to prevent misoperation:
Database Separation: Right-click the database you want to separate --- Task --- Separation --- Direct Point Determination --- Open the database to store the full path --- Copy the database (.mdf) and its log file (_log)
Database Recovery: Copy data to its full storage path - right-click database - add - add - determine
Database Backup: Right-click the database you want to separate - Task - Backup
Database Restore: Right-click Database - Restore Database - Find Backup Path (Backup File. bak)

Database backup: backup database sss to disk ='E:/sss.bak'
Database Restore: restore database sss from disk ='E:/sss.bak'
Storage path of database:
C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA

Tags: Database encoding SQL

Posted on Mon, 05 Aug 2019 02:28:37 -0700 by sean72