ORACLE basic SQL statements

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

One, the basic knowledge of SQL

1, DDL (data definition language)

1)Create table

-- creating a data table

create table Test(Id int not null, Age char(20)),

-- creating a data table

create table T_Person1(Id int not null,

Name nvarchar(50),

Age int null),

To create a table, add foreign key

Create table T_Students(

StudentNo char(4),

CourseNo char(4),

Score int,

Primary key(StudentNo),

Foreign key(CourseNo) References T_Course(CourseNo)

);

2)Modify structure

- modify table structure, add fields

Alter table T_Person add NickName nvarchar(50) null;

- modify table structure, delete the field

Alter table T_Person Drop NickName;

3)Delete table

- delete data table

Drop table T_Person;

- delete data table

drop table test

4)Create index

Create [Unique] Index <Index name > on <the basic table name > (<specify the sequence>);

2, DML (data manipulation language)

1)Insert statement

insert into T_Person1(Id,Name,Age) values(1,'Vicky',20)

Insert a article number, fields and values must be before and after the corresponding

insert into T_Preson1(Id,Name,Age) values(2,'Tom',19)

insert into T_Person1(Id,Name,Age) values(4,'Jim',19)

insert into T_Person1(Id,Name,Age) values(5,'Green',20)

insert into T_Person1(Id,Name,Age) values(6,'Hanmeimei',21)

insert into T_Person1(Id,Name,Age) values(7,'Lilei',22)

insert into T_Person1(Id,Name,Age) values(8,'Sky',23)

insert into T_Person1(Id,Name,Age) values(newid(),'Tom',19)

2)The update statement

- modify columns, the age field all changed to 30

update T_Person1 set age=30

The Age and Name fields all settings for...

update T_Person1 set Age=50,Name='Lucy'

update T_Person1 set Name='Frankie' where Age=30

update T_Person1 set Name=N'Chinese characters' where Age=20

-- Chinese character in front of the best with N, in order to prevent garbled

update T_Person1 set Name=N'Adult' where Age=30 or Age=50

3)Delete statement

delete from T_Person1

- delete all data in the table


delete from T_Person1 where Name='Tom'

-- according to the conditions of the deleted data


4)Query

Query is very powerful, can be found in almost any thing!

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

-- data retrieval.

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

-- query is not associated with any table related data.

SELECT 1+1; Simple operation
Select 1+2 as results


SELECT newid();-- query a GUID character set


select GETDATE() As date -- Query date


-- can query the SQLServer version

select @@VERSION as SQLServer version


-- a query multiple

The results of select 1+1 GETDATE (as), date, @@VERSION version of as, NEWID (as) number



-- simple data query.HelloWorld level

SELECT * FROM T_Employee;


A query needs.

SELECT FNumber FROM T_Employee;


To take the.As keyword column alias

SELECT FNumber AS FName AS FROM T_Employee number, name;


Using WHERE query matching records.

SELECT FName FROM T_Employee WHERE FSalary<5000;


-- on the sort the records, the default collation is ASC

SELECT * FROM T_Employee ORDER BY FAge ASC,FSalary DESC;


The --ORDER BY clause should be put in the WHERE clause.

SELECT * FROM T_Employee WHERE FAge>23 ORDER BY FAge DESC,FSalary DESC;


Logical operators can use in --WHERE: or, and, not, <, > >, =, =,; <=,! =, <> et al.


Fuzzy matching, the first letter is unknown.

SELECT * FROM T_Employee WHERE FName LIKE '_arry';


Fuzzy matching, and many characters of unknown.

SELECT * FROM T_Employee WHERE FName LIKE '%n%';


--NULL said "don't know", and NULL in the computing results are generally is NULL.


-- query data is NULL, not =,! = or <>, to use the IS keyword

SELECT * FROM T_Employee WHERE FName IS NULL;

SELECT * FROM T_Employee WHERE FName IS NOT NULL;


-- Query in a range of data that contains IN, IN, followed by a set

SELECT * FROM T_Employee WHERE FAge IN (23, 25, 28);


- two below query equivalence.

SELECT * FROM T_Employee WHERE FAge>=23 AND FAge<=30;

SELECT * FROM T_Employee WHERE FAge BETWEEN 23 AND 30;


- create a Employee table, this table with the data in the following Demo

- the following in the SQL manager in SQL statements, practice in the T_Employee table

create table T_Employee(FNumber varchar(20),

FName varchar(20),

FAge int,

FSalary Numeric(10,2),

primary key (FNumber)

)


insert into T_Employee(FNumber,FName,FAge,FSalary) values('DEV001','Tom',25,8300)

insert into T_Employee(FNumber,FName,FAge,FSalary) values('DEV002','Jerry',28,2300.83)

insert into T_Employee(FNumber,FName,FAge,FSalary) values('SALES001','Lucy',25,5000)

insert into T_Employee(FNumber,FName,FAge,FSalary) values('SALES002','Lily',25,6200)

insert into T_Employee(FNumber,FName,FAge,FSalary) values('SALES003','Vicky',25,1200)

insert into T_Employee(FNumber,FName,FAge,FSalary) values('HR001','James',23,2200.88)

insert into T_Employee(FNumber,FName,FAge,FSalary) values('HR002','Tom',25,5100.36)

insert into T_Employee(FNumber,FName,FAge,FSalary) values('IT001','Tom',28,3900)

insert into T_Employee(FNumber,FAge,FSalary) values('IT002',25,3800)


-- began to carry out various operations on the T_Employee table

- search all fields

select * from T_Employee


A specific field

select FName,FAge from T_Employee


- retrieval with filtering conditions

select * from T_Employee

where FSalary<5000


-- to change the display name of the keyword as, alias as—

Select FName as FAge as name, age, salary FSalary as from T_Employee



Two, the SQL data types in Server

1, Exact numeric types

bigint

int

smallint

tinyint

bit

money

smallmoney

2, Character data type, the MS recommended by VarChar (max) instead of Text

Char

VarChar

Text

3, Approximate numeric types

Decimal

Numeric

Real

Float

4, The Unicode string types

Nchar

NvarChar

Ntext

5, Binary data types, MS suggested that VarBinary (Max) instead of the Image data type, max=231-1

Binary(n) Binary data stored in fixed length

VarBinary(n) Binary data storage variable length, in the range of n~(1,8000)

Image stores the image information

6, Date and time data types, different scope, different accurately.

DateTime

SmallDateTime

7, Application specific data types

Cursor

Sql-variant

Table

TimeStamp

UniqueIdentifier

XML


Three, the built-in function in SQL


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

Summary - - data aggregation function.

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

-- query data in the T_Employee table number

select COUNT(*) from T_Employee


The highest paid people -- Query

select MAX(FSalary) as Top1 from T_Employee


The minimum wage people -- Query

select Min(FSalary) as Bottom1 from T_Employee


-- Query average wage

select Avg(FSalary) As average from T_Employee


All salaries and

select SUM(FSalary) As from T_Employee of total wages


The total number of staff wages -- Query over 5K

select COUNT(*) as total from T_Employee

where FSalary>5000



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

Sort - - Data

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

By age sort ascending, the default is ascending

select * from T_Employee

order by FAge ASC


-- multiple criteria sorting, what, what, in a pre condition is the same, are arranged according to a condition

--where in order by

select * from T_Employee

order by FAge ASC, FSalary DESC



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

Fuzzy matching. "

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

-- wildcard query

--1. single character wildcard_

--2. multiple character wildcard%

In arbitrary string DEV.

select * from T_Employee

where FNumber like 'DEV%'


-- beginning with a character, OM terminated strings

select * from T_Employee

where FName like '_om'


- retrieval contains M characters in the name

select * from T_Employee

where FName like '%m%'



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

- processing empty value.

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

--null said they did not know, is not without value

--null and other value calculation result is null

select null+1


-- query name is null data

select * from T_Employee

where FName is null


-- query name not empty null data

select * from T_Employee

where FName is not null


- age is 23,25,28 employee

select * from T_Employee

where FAge=23 or FAge=25 or FAge=28



-- or use in integrated query

- age is 23,25,28 employee

select * from T_Employee

where FAge in (23,25,28)


-- between the ages of 20 to 25 of the employee information

select * from T_Employee

where FAge>20 and FAge<25


-- between the ages of 20 to 25 employees, including 25

select * from T_Employee

where FAge between 20 and 25



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

- data packet.

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

Select FAge,COUNT(*) from T_Employee

group by FAge

--1. were grouped according to age

--2. then take out the number of groups of age


- note: the field does not appear in the group by clause in the select statement, does not appear in the list of column names (except in the aggregate functions)

--group by must appear in the where.

Select FAge,AVG(FSalary),COUNT(*) from T_Employee

group by FAge


-- wrong usage

Select FAge,FName,COUNT(*) from T_Employee

group by FAge


-- group by clause with where

--group by must appear in the where.

Select FAge,AVG(FSalary),COUNT(*) from T_Employee

where FAge>=25

group by FAge



--Having cannot contain a field that can't be found, only include aggregate functions and the query related field

select FAge,COUNT(*) from T_Employee

group by FAge

Having COUNT(*)>1


select FAge,COUNT(*) from T_Employee

where FSalary>2500

group by FAge


Column'T_Employee.FSalary'in the --HAVING clause is invalid, because the column is not contained in an aggregate function or the GROUP BY clause

--Having is the group of information filtering, can use the column and select column is the same.

Therefore, having can not replace where

select FAge,COUNT(*) from T_Employee

group by FAge

Having FSalary>2500



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

- determine the result set rows.

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

-- take out all the staff information, according to the salary desc

select * from T_Employee

order by FSalary DESC


- removed before the three employee information, according to the salary desc

select top 3 * from T_Employee

order by FSalary DESC


-- according to the wage out in 6-8 ranking of the employee information, according to the falling wages arrangement

select top 3 * from T_Employee

where FNumber not in

(select top 5 FNumber from T_Employee order by FSalary DESC)

order by FSalary DESC




- modify, add fields, update the value of the field operation.

alter table T_Employee add FSubCompany varchar(20)

alter table T_Employee add FDepartment varchar(20)

update T_Employee set FSubCompany='Beijing',FDepartment='Development'

where FNumber='DEV001';

update T_Employee set FSubCompany='ShenZhen',FDepartment='Development'

where FNumber='DEV002';

update T_Employee set FSubCompany='Beijing',FDepartment='HumanResource'

where FNumber='HR001';

update T_Employee set FSubCompany='Beijing',FDepartment='HumanResource'

where FNumber='HR002';

update T_Employee set FSubCompany='Beijing',FDepartment='InfoTech'

where FNumber='IT001';

update T_Employee set FSubCompany='ShenZhen',FDepartment='InfoTech'

where FNumber='IT002'

update T_Employee set FSubCompany='Beijing',FDepartment='Sales'

where FNumber='SALES001';

update T_Employee set FSubCompany='Beijing',FDepartment='Sales'

where FNumber='SALES002';

update T_Employee set FSubCompany='ShenZhen',FDepartment='Sales'

where FNumber='SALES003';


select * from T_Employee


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

- get rid of repeated data.

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

All the staff of the departments of information --

select Distinct FDepartment from T_Employee;



select FDepartment,FSubCompany

from T_Employee


Comparison of the above two examples combined, Distinct is aimed at the entire row comparison

select Distinct FDepartment,FSubCompany

from T_Employee




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

The result set, combined with Union.

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

- create a test table T_TempEmployee, and insert data

Create Table T_TempEmployee(FIdCardNumber varchar(20),FName varchar(20),FAge int,Primary key(FIdCardNumber));

insert into T_TempEmployee(FIdCardNumber,FName,FAge) values('1234567890121','Sarani',33);

insert into T_TempEmployee(FIdCardNumber,FName,FAge) values('1234567890122','Tom',26);

insert into T_TempEmployee(FIdCardNumber,FName,FAge) values('1234567890123','Yamaha',38);

insert into T_TempEmployee(FIdCardNumber,FName,FAge) values('1234567890124','Tina',36);

insert into T_TempEmployee(FIdCardNumber,FName,FAge) values('1234567890125','Konkaya',29);

insert into T_TempEmployee(FIdCardNumber,FName,FAge) values('1234567890126','Foortia',29);


select * from T_TempEmployee


The --Union keyword, and 2 Results

The 2 query results with 1 query results

Requirements: - field 2 queries on (a number, name, type compatibility) must be the same

select FName,Fage from T_TempEmployee

union

select FName,Fage from T_Employee



select FNumber, FName,Fage,FDepartment from T_Employee

union

select FIdCardNumber,FName,Fage,'Temporary workers, no department' from T_TempEmployee


---Union All: cannot merge duplicate data

--Union: Merge duplicate data

select FName,FAge from T_Employee

union all

select FName,FAge from T_TempEmployee


select FAge from T_Employee

union

select FAge from T_TempEmployee


- note: Union because of the need for repeated scanning, so the efficiency is low, so if not sure to merge duplicate, then use Union all


For example: -

select 'The maximum age for formal employees',MAX(FAge) from T_Employee

union all

select 'The minimum age for formal employees',MIN(FAge) from T_Employee

union all

select 'Temporary maximum age',MAX(FAge) from T_TempEmployee

union all

select 'Temporary minimum age',MIN(FAge) from T_TempEmployee


-- Query employees each information, including number, salary, and together in the last line and staff wages

select FNumber,FSalary from T_Employee

union all

select 'The total amount of wages',SUM(FSalary) from T_Employee



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

- SQL other built-in functions.

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


--1. math function


--ABS():Absolute value

--CEILING():Rounding to the maximum integer

--FLOOR():Rounding to the minimum integer

--ROUND():Four to five homes in


select ABS(-3)


select CEILING(3.33)


select CEILING(-3.61)


select FLOOR(2.98)


select FLOOR(-3.61)


select ROUND(-3.61,1)Second parameters are accuracy, number of digits after the decimal point


select ROUND(-3.61,0)


select ROUND(3.1415926,3)


The --2. string functions

--LEN():String length

--LOWER(),UPPER():Transfer case

--LTRIM():Remove the string left blank

--RTRIM():Remove the string right spaces

--SUBSTRING(string,start_positoin,length):


From the beginning of 1.


select SUBSTRING('abc111',2,3)The result is: BC1


select FName, SUBSTRING(FName,2,2) from T_Employee


select LEN('abc') The results are: 3


select FName, LEN(FName) from T_Employee


No can not only get rid of the left and right space, remove spaces (TRIM) built-in function, so the first left and right of the TRim, of course, you can also be the first left right after

select LTRIM(' abc '),RTRIM(' abc '),LEN(LTRIM(RTRIM(' abc ')))




--3. date function

--GETDATE():Get the current date and time


--DATEADD(datepart,numbre,date):Date added later,

- parameter date be calculated date; parameter number increments; parameter datepart as the unit of measurement, time interval unit,


--DATEDIFF(datepart,startdate,enddate):The difference between 2 dates calculated


--DATEPART(datepart,date):Returns a date certain parts, such as the date, hour and so on.


/*

Values (Sql Server) abbreviation (Access and ASP) that

Year Yy yyyy 1753 ~ 9999

Quarter Qq Q Season 1 ~ 4

Month Mm M 1 ~ 12

Day of year Dy y the number of days a year, a year in the first few days 1-366

Day Dd D, 1-31

Weekday Dw w days a week, a week in the first few days of the 1-7

Week Wk WW week, a year in the first 0 weeks ~ 51

Hour Hh h 0 ~ 23

Minute Mi n minutes 0 ~ 59

Second Ss S 0 seconds ~ 59

Millisecond Ms - MS 0 ~ 999

*/


select DATEADD(DAY,3,getdate())


select DATEADD(MONTH,-3,getdate())


select DATEADD(HOUR,8,getdate())


select DATEDIFF(YEAR,'1989-05-01',GETDATE())


select DATEDIFF(HH,GETDATE(),DATEADD(DAY,-3,GETDATE()))


-- Query employees length of service, for the year   

select FName,FInDate,DATEDIFF(year,FInDate,getdate()) As service from T_Employee



Draw out every year -- the number of employees in V1

select DATEDIFF(year,FInDate,getdate()),COUNT(*)

from T_Employee

group by DATEDIFF(year,FInDate,getdate())


Draw out every year -- the number of employees in V2

select DATEPART(YEAR,FInDate), COUNT(*)

from T_Employee

group by DATEPART(YEAR,FInDate)



select DATEPART(YEAR,GETDATE())


select DATEPART(MONTH,GETDATE())


select DATEPART(DAY,GETDATE())


select DATEPART(HH,GETDATE())


select DATEPART(MINUTE,GETDATE())


select DATEPART(SECOND,GETDATE())



--4. type conversion functions

--CAST(expression as data_type)

--CONVERT(data_type,expression)


select CAST('123' as int),CAST('2010-09-08' as datetime),

CONVERT(datetime,'2010-09-08'),CONVERT(varchar(20),123)


--5. null processing function isNull

--ISNULL(expression,value)

select ISNULL(FName,'Anonymous') as name from T_Employee


--6.CASE function usage:

--1. single value judgment: equivalent to switch.case

--CASE expression

--WHEN value1 then returnvalue1

--WHEN value2 then returnvalue2

--WHEN value3 then returnvalue3

--ELSE default_return_value

--END


-- to determine customer types


select FName,

(

case FLevel

when 1 then 'Ordinary customers'

when 2 then 'Member'

when 3 then 'VIP'

else 'Unknown type of customer'

End

) As customer types

from T_Customer


Income level query

select FName,

(

case

when FSalary <2000 then 'Low-income'

when FSalary >= 2000 and FSalary <=5000 then 'Middle income'

else 'High-income'

end

)As income level

from T_Employee


- here is a CASE usage on interview questions

- table has a ABC three T column, using SQL statement: when the A column is greater than the B column A column, or select the B column,

When B is greater than C when the column column B column, or select the C column.

select

(

case

when a > b then a else b

end

),

(

case

when b>c then b else c

end

)

from T



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

select FNumber,

(

case

when FAmount>0 then FAmount

else 0

end

) As income,

(

case

when FAmount<0 then ABS(FAmount)

else 0

end

) As spending

from T

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


Teams that problem

A table T_Scroes, recording performance:


--Date Name Scroe

--2008-8-8 Bayern wins

--2008-8-9 wizards victory

--2008-8-8 the Lakers win

Bayern --2008-8-10 negative

Bayern --2008-8-8 negative

--2008-8-12 wizards victory


The output format -- Requirements:

--Name - negative

- Bayern 12

-- the Lakers' 10

20 - Washington


- note: plus N in Chinese string before, such as N'wins'

create table T_Scores(

[Date] datetime null collate

[Name] nvarchar(50)

)

CREATE TABLE [T_Scores]( [Date] [datetime] NULL,

[Name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,

[Score] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL

);

INSERT [T_Scores] ([Date], [Name], [Score]) VALUES (CAST(0x00009AF200000000 AS DateTime), N'Bayern ', N' wins');

INSERT [T_Scores] ([Date], [Name], [Score]) VALUES (CAST(0x00009AF300000000 AS DateTime), N'The Wizards', N'wins');

INSERT [T_Scores] ([Date], [Name], [Score]) VALUES (CAST(0x00009AF300000000 AS DateTime), N'The Lakers' victory, N'');

INSERT [T_Scores] ([Date], [Name], [Score]) VALUES (CAST(0x00009AF400000000 AS DateTime), N'Bayern ', N' negative');

INSERT [T_Scores] ([Date], [Name], [Score]) VALUES (CAST(0x00009AF200000000 AS DateTime), N'Bayern ', N' negative');

INSERT [T_Scores] ([Date], [Name], [Score]) VALUES (CAST(0x00009AF600000000 AS DateTime), N'The Wizards', N'wins');


select * from T_Scores


The first table - List

Statistical results of each team

select Name,

(

case Score

when N'Sheng' then 1

else 0

end

) As wins,

(

case Score

when N'Negative' then 1

else 0

end

) As negative

from T_Scores



select Name,

sum

(

case Score

when N'Sheng' then 1

else 0

end

) As wins,

sum

(

case Score

when N'Negative' then 1

else 0

end

) As negative

from T_Scores

group by Name

Judge the outcome -- according to each team's field number





- 5) create table, record phone calls' work flow, call number, record the number, call start time, end time., .

- create a table T_Callers, recording phone call 'work flow, call number, record number, call start time, end time. Create table, insert data finally write the SQL statement.

-- Requirements:

-- 1) The output of all data calls 5 records for the longest time.

-- 2) All the data in the output dial long-distance number (the other numbers beginning with 0) total length.

-- 3) The output of this month. The total length of the top three most call member number.

-- 4) Output this month call number before the three call member number.

-- 5) The output of all data dial-up water, and add in the last line of the total call duration.

-- recording the call number, number of the other side, call duration

-- ......

-- summary [city number total length to the total length of long distance number].



--Id CallerNumber TellNumber StartDateTime EndDateTime

--1 001 02088888888 2010-7-10 10:01 2010-7-10 10:05

--2 001 02088888888 2010-7-11 13:41 2010-7-11 13:52

--3 001 89898989 2010-7-11 14:42 2010-7-11 14:49

--4 002 02188368981 2010-7-13 21:04 2010-7-13 21:18

--5 002 76767676 2010-6-29 20:15 2010-6-29 20:30

--6 001 02288878243 2010-7-15 13:40 2010-7-15 13:56

--7 003 67254686 2010-7-13 11:06 2010-7-13 11:19

--8 003 86231445 2010-6-19 19:19 2010-6-19 19:25

--9 001 87422368 2010-6-19 19:25 2010-6-19 19:36

--10 004 40045862245 2010-6-19 19:50 2010-6-19 19:59



Create table

create table T_CallRecords(

id int not null,

CallerNumber varchar(3),

TellNumber varchar(13),

StartDateTIme datetime,

EndDateTime datetime,

Primary key(Id)

);


Insert data

insert into T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTIme)

values(1,'001','02088888888','2010-7-10 10:01','2010-7-10 10:05');

INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

VALUES (2,'002','02088888888', '2010-7-11 13:41','2010-7-11 13:52');

INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

VALUES (3,'003','89898989', '2010-7-11 14:42', '2010-7-11 14:49');

INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

VALUES (4,'004','02188368981', '2010-7-13 21:04', '2010-7-13 21:18');

INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

VALUES (5,'005','76767676', '2010-6-29 20:15', '2010-6-29 20:30');

INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

VALUES (6,'006','02288878243', '2010-7-15 13:40', '2010-7-15 13:56');

INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

VALUES (7,'007','67254686', '2010-7-13 11:06', '2010-7-13 11:19');

INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

VALUES (8,'008','86231445', '2010-6-19 19:19', '2010-6-19 19:25');

INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

VALUES (9,'009','87422368', '2010-6-19 19:25', '2010-6-19 19:36');

INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

VALUES (10,'010','40045862245', '2010-6-19 19:50', '2010-6-19 19:59');


- modify call number

UPDATE T_CallRecords SET CallerNumber='001' WHERE Id IN (1,2,3,6,9);

UPDATE T_CallRecords SET CallerNumber='002' WHERE Id IN (4,5);

UPDATE T_CallRecords SET CallerNumber='003' WHERE Id IN (7,8);

UPDATE T_CallRecords SET CallerNumber='004' WHERE Id=10;


Data collection

select * from T_CallRecords


- 1): the output of all data calls 5 records for the longest time.

--@Calculation of talk time,

--@Press the call time desc,

--@The first 5 records.

select top 5 CallerNumber,DATEDIFF(SECOND,StartDateTime,EndDateTime) As total length

from T_CallRecords

order by DATEDIFF(SECOND,StartDateTime,EndDateTime) DESC


- 2): all the data in the output dial long-distance number (the other numbers beginning with 0) total length

--@Query dialing number record,

--@Calculate the number of dial long distance call duration,

--@The number of long-distance calls for summation.

select SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) The total length of the from T_CallRecords as

where TellNumber like '0%'


- 3): output this month call time of the top three call member number.

--@Grouped according to the call number,

--@The call operator call time,

--@According to the call time descending order,

--@ID call 'former record 3.

select datediff(month,convert(datetime,'2010-06-01'),convert(datetime,'2010-07-22'))Test


select CallerNumber,TellNumber,datediff(month,StartDateTime,EndDateTime)

from T_CallRecords


select top 3 CallerNumber from T_CallRecords

where datediff(month,StartDateTime,getdate())=12-- a year ago.

group by CallerNumber

order by SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) DESC


- 4) output this month dials the phone number of the top three call member number.

--@Grouped according to the call number,

--@Calculation of call member call number,

--@Descending sort for according to the number of call call.,

--@ID call 'former record 3.

select top 3 CallerNumber,count(*)

from T_CallRecords

where datediff(month,StartDateTime,getdate())=12-- a year ago.

group by CallerNumber

order by count(*) DESC


- 5) output all data dial-up water, and add in the last line of the total call duration:


-- recording the call number, number of the other side, call duration

-- ......

-- summary [city number total length to the total length of long distance number].


--@Calculation of call every record length,

--@Query contains no number 0, that is the number of records,

--@Calculation of the number of call time,

--@Query contains the number 0, namely the toll number record,

--@Calculation of long distance number call time,

--@Joint inquiry.

select 'Summary 'as summary,

convert(varchar(20),

sum((

case

when TellNumber not like '0%' then datediff(second,StartDateTime,EndDateTime)

else 0

end

))) As local connection,

sum((

case

when TellNumber like '0%' then datediff(second,StartDateTime,EndDateTime)

else 0

end

)) As long distance call

from T_CallRecords

union all

select CallerNumber,TellNumber,datediff(second,StartDateTime,EndDateTime) As talk time

from T_CallRecords



Customer and order form training

To build a customer table

create table T_Customers(

id int not null,

name nvarchar(50) collate chinese_prc_ci_as null,

age int null

);

insert T_Customers(id,name,age) values(1,N'tom',10);

insert T_Customers(id,name,age) values(2,N'jerry',15);

insert T_Customers(id,name,age) values(3,N'john',22);

insert T_Customers(id,name,age) values(4,N'lily',18);

insert T_Customers(id,name,age) values(5,N'lucy',18);


select * from T_Customers


-- establish a sales table

create table T_Orders(

id int not null,

billno nvarchar(50) collate chinese_prc_ci_as null,

customerid int null);


insert T_Orders(id,billno,customerid)values(1,N'001',1)

insert T_Orders(id,billno,customerid)values(2,N'002',1)

insert T_Orders(id,billno,customerid)values(3,N'003',3)

insert T_Orders(id,billno,customerid)values(4,N'004',2)

insert T_Orders(id,billno,customerid)values(5,N'005',2)

insert T_Orders(id,billno,customerid)values(6,N'006',5)

insert T_Orders(id,billno,customerid)values(7,N'007',4)

insert T_Orders(id,billno,customerid)values(8,N'008',5)


select * from T_Orders


select o.billno,c.name,c.age

from T_Orders as o join T_Customers as c on o.customerid=c.id

-- Query order number, customer name, customer age


select o.billno,c.name,c.age

from T_Orders as o join T_Customers as c on o.customerid=c.id

where c.age>15

- show the age more than 15 years of age and order number, customer name


select o.billno,c.name,c.age

from T_Orders as o join T_Customers as c on o.customerid=c.id

where c.age>(select avg(age) from T_Customers)

Older than average age -- show the customer name, age and order No.


- sub query practice

-- a new database, called BookShop

Create database BookShop


Create table -- 4

create table T_Reader(FId INT NOT NULL,FName varchar(50),FYearOfBirth INT,FCity varchar(50),FProvince varchar(50),FYearOfJoin INT);

create table T_Book(FId int not null,FName varchar(50),FYearPublished int,FCategoryId int);

create table T_Category(FId int not null,FName varchar(50));

create table T_ReaderFavorite(FCategoryId int,FReaderId int);


- were 4 tables into data

insert into T_Category(FId,FName) values(1,'Story');

insert into T_Category(FId,FName) values(2,'History');

insert into T_Category(FId,FName) values(3,'Theory');

insert into T_Category(FId,FName) values(4,'Technology');

insert into T_Category(FId,FName) values(5,'Art');

insert into T_Category(FId,FName) values(6,'Philosophy');


insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin) values(1,'Tom',1979,'TangShan','Hebei',2003);

insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin) values(2,'Sam',1981,'LangFang','Hebei',2001);

insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin) values(3,'Jerry',1966,'DongGuan','GuangDong',1995);

insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin) values(4,'Lily',1972,'JiaXing','ZheJiang',2005);

insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin) values(5,'Marry',1985,'BeiJing','BeiJing',1999);

insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin) values(6,'Kelly',1977,'ZhuZhou','HuNan',1995);

insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin) values(7,'Tim',1982,'YongZhou','HuNan',2001);

insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin) values(8,'King',1979,'JiNan','ShanDong',1997);

insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin) values(11,'John',1979,'QingDao','ShanDong',2003);

insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin) values(9,'Lucy',1978,'LuoYang','HeNan',1996);

insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin) values(10,'July',1983,'ZhuMaDian','HeNan',1999);

insert into T_Reader(FId,FName,FYearOfBirth,FCity,fProvince,FyearOfJoin) values(12,'Fige',1981,'JinCheng','ShanXi',2003);


insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(1,'About J2EE',2005,4);

insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(2,'Learning Hibernate',2003,4);

insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(3,'Tow Cites',1999,1);

insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(4,'Jane Eyre',2001,1);

insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(5,'Oliver Twist',2002,1);

insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(6,'History of China',1982,2);

insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(7,'History of England',1860,2);

insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(8,'History of America',1700,2);

insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(9,'History of The Vorld',2008,2);

insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(10,'Atom',1930,3);

insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(11,'RELATIVITY',1945,3);

insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(12,'Computer',1970,3);

insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(13,'Astronomy',1971,3);

insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(14,'How To singing',1771,5);

insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(15,'DaoDeJing',2001,6);

insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(16,'Obedience to Au',1995,6);



insert into T_ReaderFavorite(FCategoryId,FReaderId) values(1,1);

insert into T_ReaderFavorite(FCategoryId,FReaderId) values(5,2);

insert into T_ReaderFavorite(FCategoryId,FReaderId) values(2,3);

insert into T_ReaderFavorite(FCategoryId,FReaderId) values(3,4);

insert into T_ReaderFavorite(FCategoryId,FReaderId) values(5,5);

insert into T_ReaderFavorite(FCategoryId,FReaderId) values(1,6);

insert into T_ReaderFavorite(FCategoryId,FReaderId) values(1,7);

insert into T_ReaderFavorite(FCategoryId,FReaderId) values(4,8);

insert into T_ReaderFavorite(FCategoryId,FReaderId) values(6,9);

insert into T_ReaderFavorite(FCategoryId,FReaderId) values(5,10);

insert into T_ReaderFavorite(FCategoryId,FReaderId) values(2,11);

insert into T_ReaderFavorite(FCategoryId,FReaderId) values(2,12);

insert into T_ReaderFavorite(FCategoryId,FReaderId) values(1,12);

insert into T_ReaderFavorite(FCategoryId,FReaderId) values(3,1);

insert into T_ReaderFavorite(FCategoryId,FReaderId) values(1,3);

insert into T_ReaderFavorite(FCategoryId,FReaderId) values(4,4);


select * from T_Book


select * from T_Category


select * from T_Reader


select * from T_ReaderFavorite


- parallel query

select 1 as f1,2,(select MIN(FYearPublished) from T_Book),

(select MAX(FYearPublished) from T_Book) as f4


-- Query date in 2001 or 2003 to reader information

select * from T_Reader

where FYearOfJoin in (2001,2003)


With between... And

select * from T_Reader

where FYearOfJoin between 2001 and 2003


-- query book year membership information of readers

select * from T_Reader

where FYearOfJoin in

(

select FYearPublished from T_Book

)


--SQL Server 2005 version of the built-in function: ROW_NUMBER (), known as the window function, can the paging operation.

select ROW_NUMBER() over(order by FSalary DESC) as Row_Num,

FNumber,FName,FSalary,FAge from T_Employee

-- in particular, the windowing function (row_number) can only be used with select or order in the by clause, can not be used in the where clause


-- Query third to fifth lines of data

select * from

(

select ROW_NUMBER() over(order by FSalary DESC) as Row_Num,

FNumber,FName,FSalary,FAge from T_Employee

) as e1

where e1.Row_Num>=3 and e1.Row_Num<=5



Four other concepts, SQL

Index

1, What is an index? What are the advantages and disadvantages?

The index is on the database table column or columns in a separate physical value, sort the database structure.

Advantage:

 1) Greatly accelerate the speed of data retrieval,

 2) Create unique index, ensure that each row of data in a database table only.,

 3) The connection between the accelerometer and the table,

 4) In the use of grouping and sorting clause for data retrieval, can significantly reduce the query grouping and sorting time.

Shortcomings:

 1) The index needs to be accounted for in physical space,

 2) To increase, delete and modify the data in a table, maintaining the index to dynamic data, reduce the maintenance rate.

Create index, right click on the column, write a name, the selected column can be.

2, The primary key and a logical primary key business

Business is business sense to use primary key field for the primary key, such as identity card number, bank account number, etc.,

A logical primary key field is used without any business sense to do a primary key. Because it is difficult to guarantee the business primary key (ID number does not repeat repeat, will not change (account) ISBN), it is recommended to use a logical primary key.


3, SQL Server two key common data types

1) Int (or bigint) + identity columns (also called automatic growth field)

With the implementation of auto increment identity column fields can avoid concurrency control problem, since the increase not developers. Field use identity columns without specifying a primary key value in the Insert time.

Advantages: small occupied space, without the development of staff intervention, Yi Du,

Disadvantages: low efficiency, data import and export is very painful.

Setting: "modify" -> -> "column properties selected primary key;" -> "logo; specification" choice "is"

2) Uniqueidentifier (also called GUID, UUID)

GUID algorithm is an efficient algorithm can be produced only representation, it uses MAC network card, address, nanosecond time, chip ID code algorithm, so that the GUID never repeated, either the same or different computer computer. In the 3400 BC GUID with any other over GUID are not the same.

Generate GUID function newid SQL Server().

Advantages: high efficiency, convenient for data import and export,

Disadvantages: large space occupation, not readable.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Mandel at November 30, 2013 - 8:50 PM