SQL from zero to quick mastery of "utility function (2)"

1. Sort the results of the query

Query all student information in the Stu_info table and sort by the highest score, enter the following statement.

SELECT * from Stu_info ORDER by S_score DESC; --ASC is the specified column in ascending order, and DESC is the specified column in descending order.

2. Data Control Statement "authorization, Prohibition and recovery"

(1) Authorization permission operation

Authorization is granted to a user named guest, allowing it to perform update and delete operations on the Stu_info data table, with the following input statement.

Grant Update,delete on Stu_info--update and dalete for allowed operation permissions

To guest with GRANT OPTION

(2) Deny permission action

Prevents the Guest user from updating permissions on the Stu_info table, as entered in the following statement.

DENY UPDATE on Stu_info to guest CASCADE; --cascade here to remember the line, what to say and Cascade has a relationship, is a fixed grammar bar.

(3) Revoke permission operation

Take back the Guest user's delete permission to the Stu_info table, and enter the following statement.

REVOKE DELETE on stu_info from guest;

3.BEGIN ... End Statement "With the SELECT statement if ... else and loop statements while using the "

Define the layout variable @count, if the @count value is less than 10, execute the block of statements in the while loop operation, as follows.

DECLARE @count INT;

SELECT @count = 0;

While @count < 10

BEGIN

PRINT ' count= ' + CONVERT (VARCHAR (8), @count)--convert used to convert the int type to a floating character type VARCHAR

SELECT @[email protected]+1

END

PRINT ' Loop over count = ' + CONVERT (VARCHAR (8), @count);

4.IF ... Else statement

DECLARE @age INT;

SELECT @age =40

IF @age <30

PRINT ' This was a young man! '

ELSE

PRINT ' This is a old man! '

5.CASE statement (Précis-writers: case/when/then/end) "With all possible case statements, very clear"

Use the case statement to judge each student's position in the class according to the student's name and enter the following statement.

Use test_db

SELECT S_id,s_name,

Case S_name

When the ' Jack ' then ' BOSS '

When ' Rose ' then ' BEAUTY '

When ' Osmond ' and ' Miliiniare '

Else ' no '-except for the three names above, all the positions of others are none. If you need this design, case is good, if you do not need this design, you can not add else.

END

As ' JOB '

From Stu_info

6. Use the case statement to evaluate the test scores and enter the following statements.

SELECT S_id,s_name,s_score

Case

When S_score >90 then ' perfect '

When S_score >80 then ' good '

When S_score >70 and then ' OK '

When S_score >60 then ' just Soso '

END

As ' evaluation '

From Stu_info

7.GOTO "To jump"

Use test_db;

BEGIN

SELECT s_name from Stu_info;

GOTO Jump

SELECT S_score from Stu_info;

Jump

PRINT ' Second SELECT statement is not executed ';

END

8.WAITFOR statements

After the delay of 10s executes the print statement, enter the following statement.

DECLARE @name VARCHAR (50);

SET @name = ' admin ';

BEGIN

WAITFOR DELAY ' 00:00:10 ';

PRINT @name;

END;

9.RETURN "Unconditional Exit"

10.ASCII () function

To view the ASCII value of the specified character, enter the following statement.

SELECT ASCII (' s ');

11.CHAR () function

Check the ASCII values for the corresponding characters 115 and 49, and enter the following statements.

SELECT char (+), char (49);

12.LEFT () function

Use the left function to return the character in the string, as follows.

SELECT left (' Football ', 4); "" Foot "" "

13.RIGHT () function

Use the right function to return the character to the left of the string and enter the following statement.

SELECT right (' Football ', 4); "" "" Ball

14.LTRIM () function

Use the LTrim function to remove the space to the left of the string and enter the following statement.

SELECT LTRIM (' book ');

15.RTRIM () function (corresponding to the above)

16.STR () function

Using the STR function to convert numeric data to character data, enter the following statement.

SELECT Str (3141.59,6,1), str (123.45,5,7);

17. Inverse string function reverse (s)

Use the reverse function to reverse the string and enter the following statement.

SELECT REVERSE (' abc ');

18.LEN (str) function

SELECT len (' No '), Len (' Date '), Len (12345);

19. Find the location of a in the data (Advanced Find)

Use the CHARINDEX function to find the beginning of the specified substring in the string, and enter the following statement.

SELECT CHARINDEX (' A ', ' banana '), CHARINDEX (' na ', ' banana ', 4);

20. Content to be taken within the scope

Use the SUBSTRING function to get the substring at the specified position, as in the following input statement.

SELECT

SUBSTRING (' breakfast ' 1,5), SUBSTRING (' Breakfast ', LEN (' Breakfast ')/2,len (' breakfast '));

>>>>>>>>break Akfast

SQL from zero to quick mastery of "utility function (2)"