Advanced queries are most frequently used in databases and are also the most widely used. For more information about how to learn sqlserver, see.
Advanced queries are most frequently used in databases and are also the most widely used. For more information about how to learn sqlserver, see.
Ø basic common Query
-- Select
Select * from student;
-- All query all
Select all sex from student;
-- Duplicate distinct Filtering
Select distinct sex from student;
-- Count statistics
Select count (*) from student;
Select count (sex) from student;
Select count (distinct sex) from student;
-- Top: obtain the first N records
Select top 3 * from student;
-- Rename the alias column name column
Select id as number, name 'name', sex gender from student;
-- Rename the alias table name table
Select id, name, s. id, s. name from student s;
-- Column operation
Select (age + id) col from student;
Select s. name + '-' + c. name from classes c, student s where s. cid = c. id;
-- Where condition
Select * from student where id = 2;
Select * from student where id> 7;
Select * from student where id <3;
Select * from student where id <> 3;
Select * from student where id> = 3;
Select * from student where id <= 5;
Select * from student where id!> 3;
Select * from student where id! <5;
-- And
Select * from student where id> 2 and sex = 1;
-- Or
Select * from student where id = 2 or sex = 1;
-- Between... and... is equivalent to and
Select * from student where id between 2 and 5;
Select * from student where id not between 2 and 5;
-- Like fuzzy query
Select * from student where name like '% a % ';
Select * from student where name like '% [a] [o] % ';
Select * from student where name not like '% a % ';
Select * from student where name like 'ja % ';
Select * from student where name not like '% [j, n] % ';
Select * from student where name like '% [j, n, a] % ';
Select * from student where name like '% [^ ja, as, on] % ';
Select * from student where name like '% [ja_on] % ';
-- In subquery
Select * from student where id in (1, 2 );
-- Not in is not in it.
Select * from student where id not in (1, 2 );
-- Is null
Select * from student where age is null;
-- Is not null
Select * from student where age is not null;
-- Order by sorting
Select * from student order by name;
Select * from student order by name desc;
Select * from student order by name asc;
-- Group by group
Grouping statistics by age
Select count (age), age from student group by age;
Grouping statistics by gender
Select count (*), sex from student group by sex;
Group and sort by age and gender
Select count (*), sex from student group by sex, age order by age;
Records grouped by gender and whose IDs are greater than 2 are sorted by gender.
Select count (*), sex from student where id> 2 group by sex order by sex;
Query data with IDs greater than 2 and group and sort the results after computation.
Select count (*), (sex * id) new from student where id> 2 group by sex * id order by sex * id;
-- Group by all Groups
Group by age, all ages
Select count (*), age from student group by all age;
-- Having grouping filtering Condition
Filters out data with an empty age by age group, and counts the number of groups and actual age information.
Select count (*), age from student group by age having age is not null;
Groups by age and cid. The filtering condition is records with a cid greater than 1.
Select count (*), cid, sex from student group by cid, sex having cid> 1;
Grouping by age, the filtering condition is that the number of records after grouping is greater than or equal to 2
Select count (*), age from student group by age having count (age)> = 2;
Group by cid and gender. The filtering condition is that cid is greater than 1, and cid is greater than 2.
Select count (*), cid, sex from student group by cid, sex having cid> 1 and max (cid)> 2;
Ø nested subquery
A subquery is a query nested in a select, insert, update, or delete statement, or other subqueries. Subqueries can be used wherever expressions are allowed. A subquery is also called an internal query or an internal choice, and a statement containing a subquery is also an external query or an external choice.
# From (select... Table) Example
Query a table as a new table.
Select * from (
Select id, name from student where sex = 1
) T where t. id> 2;
The statement in the brackets above is the subquery Statement (internal query ). An external query is used. An external query can contain the following statements:
1. General select query containing the general selection list component
2. A regular from statement that contains one or more table or view names
3. Optional where clause
4. Optional group by clauses
5. Optional having clause
# Example
Query the class information and count the life of the class students
Select *, (select count (*) from student where cid = classes. id) as num
From classes order by num;
# In, not in Clause query example
Query the student information of these classes whose class IDs are greater than or equal
Select * from student where cid in (
Select id from classes where id> 2 and id <4
);
Query student information that is not in the class
Select * from student where cid not in (
Select id from classes where name = '2 class'
)
The results returned by the clause after in and not in must be one column, and the results of this column will be used as the query condition corresponding to the previous condition. For example, the id of the cid clause;
# Exists and not exists clause query examples
Query student information with class id
Select * from student where exists (
Select * from classes where id = student. cid and id = 3
);
Query student information without assigned classes
Select * from student where not exists (
Select * from classes where id = student. cid
);
Exists and not exists queries require an association condition between the internal query and the external query. If this condition is not found, all information is queried. For example, id equals to student. id;
# Some, any, all clause query example
Query the age information of the students older than the class.
Select * from student where cid = 5 and age> all (
Select age from student where cid = 3
);
Select * from student where cid = 5 and age> any (
Select age from student where cid = 3
);
Select * from student where cid = 5 and age> some (
Select age from student where cid = 3
);
Ø Aggregate Query
1. Remove duplicate data from distinct
Select distinct sex from student;
Select count (sex), count (distinct sex) from student;
2. Summary query of compute and compute
Summarize those older
Select age from student
Where age> 20 order by age compute sum (age) by age;
Group and summarize age information for those older than age by gender
Select id, sex, age from student
Where age> 20 order by sex, age compute sum (age) by sex;
Group by age
Select age from student
Where age> 20 order by age, id compute sum (age );
Group by age, age summary, and find the maximum value of id
Select id, age from student
Where age> 20 order by age compute sum (age), max (id );
Compute summarizes the query results, and the following result set is the summary information. You can add multiple aggregate expressions in the compute clause. The information you can add is as follows:
A. Optional by keyword. It calculates the specified row aggregation for each column.
B. Name of the row aggregate function. Including sum, avg, min, max, count, etc.
C. columns for which the aggregate function is to be executed
Compute by is suitable for businesses that are grouped first and then summarized. The columns after compute by must be columns in order.
3. cube Summary
The summary of cube is similar to that of compute, but the syntax is concise and a result set is returned.
Select count (*), sex from student group by sex with cube;
Select count (*), age, sum (age) from student where age is not null group by age with cube;
The cube should be combined with the group by statement to complete group summary.
Ø sorting Function
Sorting is required in many places. You need to sort the query results and give the sequence number. For example:
1. Sort a table. The sequence numbers must be incremental and not repeated.
2. Sort the scores of the students to obtain the rankings. The rankings can be tied together, but the ranking numbers are continuously increasing.
3. In some sort cases, the sequence number must be skipped, although it is a parallel
Basic syntax
Sorting function over ([grouping statement] sorting clause [desc] [asc])
Sort clause order by column name, column name
Group clause partition by group column, group column
# Row_number Function
Returns an incremental continuous sequence number based on the sort clause.
Sort by name in ascending order
Select s. id, s. name, cid, c. name, row_number () over (order by c. name) as number
From student s, classes c where cid = c. id;
# Rank function
The ascending sequence number is given according to the sort clause, but there is a parallel and skip null
Ascending Order
Select id, name, rank () over (order by cid) as rank from student;
Skip the same increment
Select s. id, s. name, cid, c. name, rank () over (order by c. name) as rank
From student s, classes c where cid = c. id;
# Dense_rank Function
The ascending sequence number is given based on the sort clause, but there is a column that does not skip null.
Skip without directly increasing
Select s. id, s. name, cid, c. name, dense_rank () over (order by c. name) as dense
From student s, classes c where cid = c. id;
# Partition by grouping clause
The partition by function can be used together with the preceding three functions.
Use partition by to group by class name and sort student IDs
Select s. id, s. name, cid, c. name, row_number () over (partition by c. name order by s. id) as rank
From student s, classes c where cid = c. id;
Select s. id, s. name, cid, c. name, rank () over (partition by c. name order by s. id) as rank
From student s, classes c where cid = c. id;
Select s. id, s. name, cid, c. name, dense_rank () over (partition by c. name order by s. id) as rank
From student s, classes c where cid = c. id;
# Ntile average sorting Function
The data to be sorted is evenly divided and then sorted by equals. The parameter in ntile indicates the number of equals points.
Select s. id, s. name, cid, c. name,
Ntile (5) over (order by c. name) as ntile
From student s, classes c where cid = c. id;
Set Operations
Perform intersection, union, and subtraction operations on the two sets of query results.
1. union and union all operations
-- Union
Select id, name from student where name like 'ja %'
Union
Select id, name from student where id = 4;
-- Union and repetition
Select * from student where name like 'ja %'
Union all
Select * from student;
2. intersect intersection calculation
-- Intersection (same part)
Select * from student where name like 'ja %'
Intersect
Select * from student;
3. Reduce set operation with distinct T
-- Subtract set (except for the same part)
Select * from student where name like 'ja %'
Except
Select * from student where name like 'jas % ';
Ø expression of a public table
When querying a table, sometimes the intermediate table needs to be used repeatedly. These subqueries are called by repeated queries, which is not only inefficient but also easy to understand. Then the expression of the Public table can solve this problem.
We can regard a public table expression (CET) as a temporary result set and define it within the execution range of select, insert, update, delete or create view statements.
-- Expression
With statNum (id, num)
(
Select cid, count (*)
From student
Where id> 0
Group by cid
)
Select id, num from statNum order by id;
With statNum (id, num)
(
Select cid, count (*)
From student
Where id> 0
Group by cid
)
Select max (id), avg (num) from statNum;
Ø connection Query
1. Simplified connection Query
-- Simplified join query
Select s. id, s. name, c. id, c. name from student s, classes c where s. cid = c. id;
2. left join
-- Left join
Select s. id, s. name, c. id, c. name from student s left join classes c on s. cid = c. id;
3. right join
-- Right join
Select s. id, s. name, c. id, c. name from student s right join classes c on s. cid = c. id;
4. inner join
-- Internal Connection
Select s. id, s. name, c. id, c. name from student s inner join classes c on s. cid = c. id;
-- Inner can be omitted
Select s. id, s. name, c. id, c. name from student s join classes c on s. cid = c. id;
5. cross join
-- Cross join query. The result is the product of a flute.
Select s. id, s. name, c. id, c. name from student s cross join classes c
-- Where s. cid = c. id;
6. Self-join (query connections for the same table)
-- Self-connection
Select distinct s. * from student s, student s1 where s. id <> s1.id and s. sex = s1.sex;
Ø Functions
1. Aggregate functions
Max maximum, min minimum, count statistics, avg average, sum, var variance
Select
Max (age) max_age,
Min (age) min_age,
Count (age) count_age,
Avg (age) avg_age,
Sum (age) sum_age,
Var (age) var_age
From student;
2. Date and Time Functions
Select dateAdd (day, 3, getDate (); -- add day
Select dateAdd (year, 3, getDate (); -- add year
Select dateAdd (hour, 3, getDate (); -- add hour
-- Returns the number of date boundary and time boundary between two specified dates.
Select dateDiff (day, '2017-06-20 ', getDate ());
-- Difference in seconds
Select dateDiff (second, '2017-06-22 11:00:00 ', getDate ());
-- Hours of difference
Select dateDiff (hour, '2017-06-22 10:00:00 ', getDate ());
Select dateName (month, getDate (); -- current month
Select dateName (minute, getDate (); -- current minute
Select dateName (weekday, getDate (); -- current week
Select datePart (month, getDate (); -- current month
Select datePart (weekday, getDate (); -- current week
Select datePart (second, getDate (); -- current number of seconds
Select day (getDate (); -- returns the number of days of the current date.
Select day ('2014-06-30 '); -- returns the number of days of the current date.
Select month (getDate (); -- returns the current date month
Select month ('2014-11-10 ');
Select year (getDate (); -- returns the current date year.
Select year ('1970-11-10 ');
Select getDate (); -- current system date
Select getUTCDate (); -- utc date
3. mathematical functions
Select pi (); -- PI Function
Select rand (100), rand (50), rand (), rand (); -- Random Number
Select round (rand (), 3), round (rand (100), 5); -- exact decimal places
-- Exact number of digits. A negative number indicates the first decimal point.
Select round (123.456, 2), round (254.124,-2 );
Select round (123.4567, 1, 2 );
4. Metadata
Select col_name (object_id ('student '), 1); -- returns the column name.
Select col_name (object_id ('student '), 2 );
-- Data Type length of the column
Select col_length ('student ', col_name (object_id ('student'), 2 ));
-- Data Type length of the column
Select col_length ('student ', col_name (object_id ('student'), 1 ));
-- Return type name and type id
Select type_name (type_id ('varchar '), type_id ('varchar ');
-- Return column type Length
Select columnProperty (object_id ('student '), 'name', 'precision ');
-- Returns the index location of the column.
Select columnProperty (object_id ('student '), 'sex', 'columnid ');
5. String Functions
Select ascii ('A'); -- converts ascii values
Select ascii ('A ');
Select char (97); -- ascii value conversion character
Select char (65 );
Select nchar (65 );
Select nchar (45231 );
Select nchar (32993); -- unicode conversion character
Select unicode ('A'), unicode ('中'); -- returns the unicode encoding value.
Select soundex ('hello'), soundex ('World'), soundex ('word ');
Select patindex ('% A', 'ta'), patindex ('% ac %', 'jack'), patindex ('dex % ', 'dexjack '); -- match Character index
Select 'A' + space (2) + 'B', 'C' + space (5) + 'D'; -- output space
Select charIndex ('O', 'Hello World'); -- query the index
Select charIndex ('O', 'Hello world', 6); -- query the index
Select quoteName ('abc [] def '), quoteName ('2017] 45 ');
-- Exact number
Select str (123.456, 2), str (123.456, 3), str (123.456, 4 );
Select str (123.456, 9, 2), str (123.456, 9, 3), str (123.456, 6, 1), str (123.456, 9, 6 );
Select difference ('helloworld', 'helloworld'); -- compare strings with the same
Select difference ('hello', 'World ');
Select difference ('hello', 'llo ');
Select difference ('hello', 'hel ');
Select difference ('hello', 'Hello ');
Select replace ('abcedef ', 'E', 'E'); -- replace the string
Select stuff ('Hello world', 3, 4, 'abc'); -- specifies the position to replace the string
Select replicate ('abc # ', 3); -- repeat the string
Select subString ('abc', 1, 1), subString ('abc', 1, 2), subString ('Hello Wrold ', 7, 5); -- truncates a string
Select len ('abc'); -- Return Length
Select reverse ('sqlserver '); -- reverse the string
Select left ('leftstring', 4); -- returns the string on the left.
Select left ('leftstring', 7 );
Select right ('leftstring', 6); -- Take the right string
Select right ('leftstring', 3 );
Select lower ('abc'), lower ('abc'); -- lower case
Select upper ('abc'), upper ('abc'); -- uppercase
-- Remove spaces on the left
Select ltrim ('abc'), ltrim ('# abc #'), ltrim ('abc ');
-- Remove spaces on the right
Select rtrim ('abc'), rtrim ('# abc #'), rtrim ('abc ');
6. Security Functions
Select current_user;
Select user;
Select user_id (), user_id ('dbo'), user_id ('public'), user_id ('guest ');
Select user_name (), user_name (1), user_name (0), user_name (2 );
Select session_user;
Select suser_id ('sa ');
Select suser_sid (), suser_sid ('sa '), suser_sid ('sysadmin'), suser_sid ('serveradmin ');
Select is_member ('dbo'), is_member ('public ');
Select suser_name (), suser_name (1), suser_name (2), suser_name (3 );
Select suser_sname (), suser_sname (0x01), suser_sname (0x02), suser_sname (0x03 );
Select is_srvRoleMember ('sysadmin'), is_srvRoleMember ('serveradmin ');
Select permissions (object_id ('student '));
Select system_user;
Select schema_id (), schema_id ('dbo'), schema_id ('guest ');
Select schema_name (), schema_name (1), schema_name (2), schema_name (3 );
7. system functions
Select app_name (); -- Name of the application in the current session
Select cast (2011 as datetime), cast ('10' as money), cast ('0' as varbinary); -- type conversion
Select convert (datetime, '20140901'); -- type conversion
Select coalesce (null, 'A'), coalesce ('201312', 'A'); -- return the first non-empty expression in the parameter.
Select collationProperty ('traditional _ Spanish_CS_AS_KS_WS ', 'codepage ');
Select current_timestamp; -- current Timestamp
Select current_user;
Select isDate (getDate (), isDate ('abc'), isNumeric (1), isNumeric ('A ');
Select dataLength ('abc ');
Select host_id ();
Select host_name ();
Select db_name ();
Select ident_current ('student '), ident_current ('class'); -- returns the maximum value of the primary key id.
Select ident_incr ('student '), ident_incr ('class'); -- increment value of id
Select ident_seed ('student '), ident_seed ('class ');
Select @ identity; -- the last auto-increment Value
Select identity (int, 1, 1) as id into tab from student; -- create a tab in/1 auto-increment form
Select * from tab;
Select @ rowcount; -- affects the number of rows
Select @ cursor_rows; -- returns the number of currently limited rows of the cursor opened on the connection.
Select @ error; -- error number for the T-SQL
Select @ procid;
8. Configure Functions
Set datefirst 7; -- set the first day of each week, indicating Sunday
Select @ datefirst as 'Day of the Week', datepart (dw, getDate () AS 'Today is Week ';
Select @ dbts; -- returns the unique timestamp of the current database.
Set language 'Italian ';
Select @ langId as 'language id'; -- returns the Language ID.
Select @ language as 'language name'; -- returns the Name of the current Language.
Select @ lock_timeout; -- returns the current lock timeout setting for the current session (MS)
Select @ max_connections; -- returns the maximum number of user connections allowed by the SQL Server instance at the same time.
Select @ MAX_PRECISION AS 'max Precision '; -- returns the Precision level used by the decimal and numeric data types.
Select @ SERVERNAME; -- Name of the SQL Server's local Server
Select @ SERVICENAME; -- service name
Select @ SPID; -- id of the current session Process
Select @ textSize;
Select @ version; -- current database version information
9. System statistical functions
Select @ CONNECTIONS; -- number of CONNECTIONS
Select @ PACK_RECEIVED;
Select @ CPU_BUSY;
Select @ PACK_SENT;
Select @ TIMETICKS;
Select @ IDLE;
Select @ TOTAL_ERRORS;
Select @ IO_BUSY;
Select @ TOTAL_READ; -- number of disk reads
Select @ PACKET_ERRORS; -- number of network packet errors
Select @ TOTAL_WRITE; -- number of disk writes executed by sqlserver
Select patIndex ('% soft %', 'Microsoft SqlServer ');
Select patIndex ('soft % ', 'Software SqlServer ');
Select patIndex ('% soft', 'sqlserver microsoft ');
Select patIndex ('% so_gr %', 'jsonisprogram ');
10. User-Defined Functions
# View all functions of the current database
-- Query all created functions
Select definition, * from sys. SQL _modules m join sys. objects o on m. object_id = o. object_id
And type in ('fn ', 'if', 'tf ');
# Create a function
If (object_id ('fun _ add', 'fn ') is not null)
Drop function fun_add
Go
Create function fun_add (@ num1 int, @ num2 int)
Returns int
With execute as caller
As
Begin
Declare @ result int;
If (@ num1 is null)
Set @ num1 = 0;
If (@ num2 is null)
Set @ num2 = 0;
Set @ result = @ num1 + @ num2;
Return @ result;
End
Go
Call a function
Select dbo. fun_add (id, age) from student;
-- Custom function, string connection
If (object_id ('fun _ append', 'fn ') is not null)
Drop function fun_append
Go
Create function fun_append (@ args nvarchar (1024), @ args2 nvarchar (1024 ))
Returns nvarchar (2048)
As
Begin
Return @ args + @ args2;
End
Go
Select dbo. fun_append (name, 'abc') from student;
# Modifying Functions
Alter function fun_append (@ args nvarchar (1024), @ args2 nvarchar (1024 ))
Returns nvarchar (1024)
As
Begin
Declare @ result varchar (1024 );
-- Coalesce returns the first non-null value
Set @ args = coalesce (@ args ,'');
Set @ args2 = coalesce (@ args2 ,'');;
Set @ result = @ args + @ args2;
Return @ result;
End
Go
Select dbo. fun_append (name, '# abc') from student;
# Return table functions
-- Returns the table object function.
Select name, object_id, type from sys. objects where type in ('fn ', 'if', 'tf') or type like' % f % ';
If (exists (select * from sys. objects where type in ('fn ', 'if', 'tf') and name = 'fun _ find_stuRecord '))
Drop function fun_find_stuRecord
Go
Create function fun_find_stuRecord (@ id int)
Returns table
As
Return (select * from student where id = @ id );
Go
Select * from dbo. fun_find_stuRecord (2 );
