Notes-SQL from getting started to improving

This note sorts out the video lecture "SQL from getting started to improving" trained by Yang zhongke in Chuanzhi podcast. net. Video related links: http://www.rupeng.com/forum/tj-16325-2477.html Database concepts   1. What is an index? What are the advantages and disadvantages?An index is a separate, physical database structure that sorts the values of one or more columns in a database table.
Advantages:
1) greatly speed up data retrieval;
2) create a unique index to ensure the uniqueness of each row of data in the database table;
3) Accelerate the connection between tables;
4) When you use grouping and sorting clauses to retrieve data, you can significantly reduce the time for grouping and sorting in queries.
Disadvantages:
1) The index needs to occupy physical space;
2) When adding, deleting, and modifying data in a table, indexes must also be dynamically maintained, reducing the Data Maintenance speed. 2. Business primary key and logical primary keyThe business primary key uses fields with business significance as the primary key, such as ID card numbers and bank accounts. The logical primary key uses fields without any business significance as the primary key. It is difficult to ensure that the business primary key will not be repeated (the ID card number is repeated) and will not change (the account is upgraded). Therefore, we recommend that you use the logical primary key. SQL server usage   1. Two common primary key data types of SQL Server1) int (or bigint) + identify column (also known as auto-increment field)
Using the ID column to implement field auto-increment can avoid concurrency and other problems. do not control the auto-increment by developers. You do not need to specify the value of the primary key when inserting a field that identifies a column.
Advantages: small space occupation, no developer intervention, easy to read;
Disadvantage: low efficiency, which is difficult to import and export data.
Settings: "Modify Table"-> select primary key-> "column attribute"-> "Identity specification" and select "yes" 2) uniqueidentifier (also known as guid and UUID)
GUID is an efficient algorithm that can generate unique representations. It is calculated using the NIC Mac, address, nanoseconds, chip ID code, and so on, this ensures that the guid generated each time will never be repeated, whether on the same computer or on different computers. The GUID generated before January 1, 3400 AD is different from any other generated guid. The newid () function that generates the guid in SQL Server ().
Advantages: high efficiency, convenient data import and export;
Disadvantage: it takes up a lot of space and is not easy to read. The industry tends to use guid. SQL Basics   1. DDL (Data Definition Language)1) create a data table:

-- Create a table. The data type of SQL Server. create Table t_person (ID int not null, name nvarchar (50), age int null, primary key (ID); -- create a table and add a foreign key. create Table t_users (studentno char (4), courseno char (4), score int, primary key (studentno), foreign key (courseno) References t_course (courseno ));

2) modify the table structure:

-- Modify the table structure and add the field alter table t_person add nickname nvarchar (50) NULL; -- modify the table structure and delete the field alter table t_person drop nickname;

3) delete a data table:

DROP TABLE T_Person;

4) Create an index:

Create [unique] index <index Name> On <basic table name> (<column name sequence> );

  2. DML (data manipulation language)1) insert statement:

INSERT INTO T_Person(Id, Name, Age) VALUES(1, 'Jim', 20);

2) Update statement:

UPDATE T_Person SET Age=Age+1 where Name='tom' or Age<25;

3) Delete statement:

-- Delete all records in the Table. Delete from t_person; -- delete the specified record in the Table. Delete from t_person where age> 20;

4) query statement:

-- Simple data query. select * From t_employee; -- Query only the required columns. select fnumber from t_employee; -- alias for the column. select fnumber as number, fname as name from t_employee; -- use where to query records that meet the conditions. select fname from t_employee where fsalary <5000; -- sorts table records. select * From t_employee order by Fage ASC, fsalary DESC; -- the order by clause must be placed after the WHERE clause. select * From t_employee where Fage> 23 order by Fage DESC, fsalary DESC; -- logical operators that can be used in where: Or, and, not, < ,>, =,> =, <= ,! =, <> And so on. -- query data that is not associated with any table. select 1 + 1; select newid (); select getdate (); -- fuzzy match, unknown first letter. select * From t_employee where fname like '_ arry'; -- fuzzy match. The preceding and following characters are unknown. select * From t_employee where fname like '% N %'; -- null indicates "unknown", and the computation results involved in null are generally null. -- query whether the data is null. =,! cannot be used ,! = Or <>. select * From t_employee where fname is null; select * From t_employee where fname is not null; -- query data in a certain range. In indicates that it contains. select * From t_employee where Fage in (23, 25, 28); -- the following two statements are equivalent. Select * From t_employee where Fage> = 23 and Fage <= 30; select * From t_employee where Fage between 23 and 30;

Data Group

-- Operation Sequence of the following statement: group by age, then explicitly show the age of each group, and count the number of rows in each group. -- Note: The aggregate function is used to calculate the group operation results. select Fage, count (*) from t_employee group by Fage; -- The following statement is incorrect because the fnames of each row in a group are different, -- fields not in group by cannot be used, except for Aggregate functions. select Fage, fname, count (*) from t_employee group by Fage; -- it can be written as follows, because the maximum wage in a group is only one. select Fage, max (fsalary), count (*) from t_employee group by Fage; -- group by should be placed behind where, and aggregate functions cannot appear in where statements, -- Where is a condition operation on the records of the original table. Order Grouping is performed after the conditional operation. -- having is placed behind order by to filter the result set of grouping operations. -- having is the clause of order by and cannot replace where, the field in having must appear in group. -- Operation Sequence of the following statement: group by age, and then filter groups with the number of trips greater than 1. Select Fage, count (*) from t_employee group by Fage having count (*)> 1; -- The following statement is incorrect because fsalary is not unique in each group, cannot be used to identify a group. -- having can use the same columns as those used in select. Select Fage, count (*) from t_employee group by Fage having fsalary> 2000;

Limit result set

-- Limit the number of rows in the result set: Select top 5 * From t_employee order by fsalary DESC; -- The following statement is intended: sort by salary from high to low, retrieve the information of three people from the sixth place. -- Operation sequence: from the inner layer to the outer layer -- first query the numbers of the first five in descending order, and then exclude the first five numbers. -- the obtained data starts from the sixth name, finally, the top three from the sixth place are retrieved in descending order. 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; -- remove duplicate data, the records are completely duplicated, that is, all fields are removed from the records. -- Query department information of all employees. Duplicate department information. Select fdepartment from t_employee; -- to query the number of departments in the company, duplicate Department Information in the result set. Duplicate department information can be eliminated by adding the keyword distinct. Select distinct fdepartment from t_employee;

Union result set

-- The query result set before and after union is regarded as a result set. The number of returned results fields must be the same and each field type must be compatible. -- By default, the Union result set automatically removes duplicates. If you do not want to remove duplicate data, add all after union. Select fname, Fage, 'temporary worker, no department 'from t_tempemployeeunionselect fname, Fage, fdepartment from t_employee; -- duplicate Union result set is not removed. select fname, Fage, 'temporary worker, no department 'from t_tempemployeeunion allselect fname, Fage, fdepartment from t_employee; -- use the Union result set for report. select 'maximum age of formal employees', max (Fage) from t_employeeunion allselect 'minimum age of formal employees', min (Fage) from t_employeeunion allselect 'maximum age of temporary workers ', max (Fage) from t_tempemployeeunion allselect 'minimum temporary worker age', min (Fage) from t_tempemployee; -- use the Union result set to query the total employees' salaries. select fnumber, fsalary from t_employeeunionselect 'salary total', sum (fsalary) from t_employee;

SQL Server Functions 1. mathematical functionsABS (): returns the absolute value.
Ceiling (): round to the maximum integer (upper limit ). Ceiling (3.33) = 4, ceiling (-3.33) =-3
Floor (): round to the smallest integer (lower limit ). Floor (3.33) = 3, floor (-3.33) =-4
Round (): rounding. Round to "number nearest to my radius ". Round (3.1415, 3) = 3.142 2. String FunctionsLen (): Evaluate the string length. Len ('abc') = 3
Lower () and Upper (): lowercase and upper case
Ltrim (): removes the left space of the string.
Rtrim (): Remove spaces on the right of the string
Substring (string, start_position, length): substring. The string parameter is the main string. start_position is the starting position of the substring in the main string, and length is the maximum length of the substring. 3. Date FunctionsGetdate (): Get the current date and time
Dateadd (datepart, number, date): Calculate the date after the addition. The date parameter is the date to be calculated, and the datepart parameter is the unit of measurement (year, YY, month, mm, day, DD, etc.) dateadd (day, 3, date) it is the three days after the date calculation, and dateadd (month,-8, date) is the date before 8 months of the date calculation.
Datediff (datepart, startdate, enddate): calculate the difference between two dates. Datepart is the unit of measurement.
Datepart (datepart, date): returns a specific part of a date.

-- Query the number of years of employment of an employee. select fname, findate, datediff (year, findate, getdate () from t_employee; -- query the number of employees of each number of years of employment. select datediff (year, findate, getdate (), count (*) from t_employee group by datediff (year, findate, getdate ());

4. type conversion functionsCast (expression as date_type)
Convert (date_type, expression)

-- Convert a string to an integer select cast ('20170901' as integer); -- convert a string to a datetime type select cast ('2017-08-08 'As datetime), convert (datetime, '2017-08-08 '); select datepart (year, cast ('2017-08-08' As datetime); -- convert a number to a string select convert (varchar (50), 2008 ); select fidnumber, right (fidnumber, 3) as the last three digits, cast (right (fidnumber, 3) as integer) as the last three digits of the integer form, cast (right (fidnumber, 3) as integer) + 1 as the last three digits plus one, convert (integer, right (fidnumber, 3)/2 as the last three digits divided by 2 from t_person;

5. null value processing functionsIsnull (expression, value): If expression is not empty, expression is returned; otherwise, value is returned.

-- When the fname field in the record is null, the explicit "Alias Name" select isnull (fname, 'Alias name') as name from t_employee;

5. Stream Control FunctionsSingle-value judgment, equivalent to switch-case
Case expression
When value1 then return_value1
When value2 then return_value2
When value3 then return_value3
Else default_return_value
End

Select fname, (Case flevel when 1 then 'vip customer' when 2 then' advanced customer' when 3 then' normal customer' else 'customer type error' end) as flevelname from t_customer; -- select fname for range determination, (case when fsalary <2000 then 'low-income 'when fsalary> = 2000 and fsalary <= 5000 then' medium-income 'else' high income 'end) from t_employee;

Exercise

-- There is a t_scroes table that records the competition results: -- Date name scroe ---8 Bayern wins ---9 qicai wins ---8 Lakers wins ---Bayern losses ---8 Bayern losses ---12 qicai wins -- output the following format: -- name wins and negative -- Bayern 1 2 -- Lakers 1 0 -- wizards 2 0 -- Note: add N in front of a Chinese string, for example, 'n' wins '-- The following Operation Order: -- execute the inner layer query to obtain the intermediate result set as follows: -- name wins and losses -- Bayern 1 0 -- wizards 1 0 -- Lakers 1 0 -- Bayern 0 1 -- Bayern 0 1 -- wizards 1 0 -- then groups the intermediate result set by team name, finally, the sum of the "wins" and "negative" Fields in each group is calculated. Select name, sum (Case score when n' wins 'then 1 else 0 end) as wins, sum (Case score when n' negative 'then 1 else 0 end) as negative from t_scroes group by name;

-- Create a table t_callers to record the call operator's working flow, record the caller id, the caller's number, the caller's number, the call start time, and the call end time. Create tables, insert data, and write SQL statements by yourself. -- Requirement: -- 1) output the five records with the longest session time for all data. -- 2) output the total duration of dialing a long-distance number (the recipient's number starts with 0) in all data. -- 3) output the number of the first three callers with the maximum call duration this month. -- 4) Number of the first three callers with the maximum number of calls this month. -- 5) output the dialing history of all data and add the total call duration in the last line. -- Record the caller id, recipient's number, and call duration --...... -- sum up [total length of city number] [total length of long-distance number] -- id callernumber tellnumber startdatetime enddatetime -- 1 001 02088888888 -- 2 001 02088888888 -- 3 001 89898989 -- 4 002 02188368981 -- 5 002 76767676 -- 6 001 02288878243 -- 7 003 67254686 -- 8 003 86231445 -- 9 001 87422368 -- 10 004 40045862245 -- 10 004 -- create a data table named t_callers (id int not null, callernumber varchar (3), tellnumber varchar (13), startdatetime datetime, enddatetime datetime, primary key (ID); -- insert data. insert into t_callers (ID, callernumber, tellnumber, startdatetime, enddatetime) values (1, '001', '123', '2017-7-10 1 ', '2017-7-10 05 '); insert into t_callers (ID, callernumber, tellnumber, startdatetime, enddatetime) values (2, '002', '123 ', '2017-7-11 ', '2017-7-11'); insert into t_callers (ID, callernumber, tellnumber, startdatetime, enddatetime) values (3, '003 ', '000000', '2017-7-11', '2017-7-11 '); Insert into t_callers (ID, callernumber, tellnumber, startdatetime, enddatetime) values (4, '004', '000000', '2017-7-13 ', '2017-7-13 '); insert into t_callers (ID, callernumber, tellnumber, startdatetime, enddatetime) values (5, '005', '123 ', '2014-6-29 ', '2014-6-29'); insert into t_callers (ID, callernumber, tellnumber, startdatetime, enddatetime) values (6, '006 ', '123', '2014-7-15 40', '2014-7-15 56'); insert into t_callers (ID, callernumber, tellnumber, startdatetime, enddatetime) values (7, '007 ', '000000', '2017-7-13', '2017-7-13 '); insert into t_callers (ID, callernumber, tellnumber, startdatetime, enddatetime) values (8, '008 ', '20170901', '2017-6-19 19:19', '2017-6-19 19:25 '); insert into t_callers (ID, callernumber, tellnumber, startdatetime, enddatetime) Values (9, '009', '20140901', '1970-6-19 ', '1970-6-19'); insert into t_callers (ID, callernumber, tellnumber, startdatetime, enddatetime) values (10, '010 ', '000000', '2017-6-19', '2017-6-19 '); -- modify the caller ID. update t_callers set callernumber = '001' where ID in (, 9); Update t_callers set callernumber = '002' where ID in ); update t_callers set callernumber = '003 'where ID in (7,8 ); Update t_callers set callernumber = '004 'Where id = 10; -- Question 1): -- @ calculate the call time; -- @ sort by call time in descending order; -- @ obtain the first five records. Select top 5 * From t_callers order by datediff (second, startdatetime, enddatetime) DESC; -- Question 2): -- @ query the records of long-distance dialing numbers; -- @ calculates the call duration of each call. -- @ calculates the call duration of each call. Select sum (datediff (second, startdatetime, enddatetime) as total long distance from t_callers where tellnumber like '0% '; -- Question 3 ): number of the first three callers with the maximum call duration for this month. -- @ group by caller ID; -- @ calculate the total call duration of each caller; -- @ sort by total call duration in descending order; -- @ query the number of the caller in the first three records. Select top 3 callernumber from t_callers group by callernumber order by sum (datediff (second, startdatetime, enddatetime) DESC; -- question 4) number of the first three callers with the maximum number of calls this month. -- @ group by caller ID; -- @ calculates the number of calls made by callers; -- @ sorts the number of calls made by callers in descending order; -- @ query the number of the caller in the first three records. Select top 3 callernumber from t_callers group by callernumber order by count (*) DESC; -- question 5) output the dialing history of all data and add the total call duration in the last line: -- Record the caller id, recipient's number, and call duration --...... -- sum up [total length of the city number] [total length of long-distance number] -- @ calculate the session length of each record; -- @ query contains no number 0, that is, the number record in the city; -- @ calculate the total call duration of the city number; -- @ query records that contain the number plus 0, that is, the long-distance number; -- @ calculate the total call duration of the long-distance number; -- @ Union query. (Select callernumber as caller number, tellnumber as peer number, datediff (second, startdatetime, enddatetime) as call duration from t_callers) Union (select 'summary ', cast (sum (datediff (second, startdatetime, enddatetime) as varchar), cast (select sum (datediff (second, startdatetime, enddatetime )) from t_callers where tellnumber like '0% ') as varchar) from t_callers where tellnumber not like '0%'); -- another method for querying Summary: Select 'summary ', sum (Case When tellnumber not like '0% 'Then datediff (second, startdatetime, enddatetime) else 0 end), sum (case when tellnumber like '0%' Then datediff (second, startdatetime, enddatetime) else 0 end) from t_callers; -- Question 1): output the five records with the longest session time for all data. -- @ calculate the call time; -- @ sort the call time in descending order; -- @ obtain the first five records. Select top 5 * From t_callers order by datediff (second, startdatetime, enddatetime) DESC) the total length of time. -- @ query the result set of a long-distance call number. -- @ calculates the call duration of each long-distance call number. -- @ calculates the call duration of each long-distance call number. Select sum (datediff (second, startdatetime, enddatetime) as total long distance from t_callers where tellnumber like '0% '; -- Question 3 ): number of the first three callers with the maximum call duration for this month. -- @ group by caller ID; -- @ calculate the total call duration of each caller; -- @ sort by total call duration in descending order; -- @ query the number of the caller in the first three records. Select top 3 callernumber from t_callers group by callernumber order by sum (datediff (second, startdatetime, enddatetime) DESC; -- question 4) number of the first three callers with the maximum number of calls this month. -- @ group by caller ID; -- @ calculates the number of calls made by callers; -- @ sorts the number of calls made by callers in descending order; -- @ query the number of the caller in the first three records. Select top 3 callernumber from t_callers group by callernumber order by count (*) DESC; -- question 5) output the dialing history of all data and add the total call duration in the last line: -- Record the caller id, recipient's number, and call duration --...... -- sum up [total length of the city number] [total length of long-distance number] -- @ calculate the session length of each record; -- @ query contains no number 0, that is, the number record in the city; -- @ calculate the total call duration of the city number; -- @ query records that contain the number plus 0, that is, the long-distance number; -- @ calculate the total call duration of the long-distance number; -- @ Union query. (Select callernumber as caller number, tellnumber as peer number, datediff (second, startdatetime, enddatetime) as call duration from t_callers) Union (select 'summary ', cast (sum (datediff (second, startdatetime, enddatetime) as varchar), cast (select sum (datediff (second, startdatetime, enddatetime )) from t_callers where tellnumber like '0% ') as varchar) from t_callers where tellnumber not like '0%'); -- another method for querying Summary: Select 'summary ', sum (case when tellnumber not like '0% 'Then datediff (second, startdatetime, enddatetime) else 0 end), sum (case when tellnumber like '0%' Then datediff (second, startdatetime, enddatetime) else 0 end) from t_callers;