Find the SQL keyword and its grammar (if it is good, give 100).

1. data definition language

Data definition language refers to statements used to define and manage databases and various objects in databases, including Create, Alter and Drop statements. In SQL Server 2000, database objects include tables, views, triggers, stored procedures, rules, default and user-defined data types. You can use statements such as create, Alter, and Drop to create, modify, and delete these objects.

2. Data manipulation language

Data manipulation language refers to the statements used to query, add, modify and delete data in the database, including selection, insertion, update and deletion. By default, only members of roles such as sysadmin, dbcreator, db_owner or db_datawriter have the right to execute data manipulation languages.

3. Data control language

Data Control Language (DCL) is a statement used to set or change the permissions of database users or roles. These statements include GRANT, DENY, REVOKE and so on. By default, only members of roles such as sysadmin, dbcreator, db_owner or db_securityadmin have the right to execute data control languages.

GRANT statement is an authorization statement, which can grant statement permission or object permission to other users and roles.

DENY statement is used to deny permissions to users or roles in the current database and prevent users or roles from inheriting permissions through their group or role members.

REVOKE statement is the opposite of GRANT statement. It can delete the permissions previously granted or denied to users or roles in the current database, but it does not affect the permissions that users or roles inherit from other roles as members.

4. System stored procedures

System stored procedures are stored procedures created by SQL Server system. Its purpose is to query information from system tables conveniently, or to complete management tasks related to updating database tables or other system management tasks. System stored procedures can be executed in any database. System stored procedures are created and stored in the system database master, and their names begin with sp_ or xp_.

Sp_addtype: used to define user-defined data types.

Sp_configure: Used to manage server configuration option settings.

Xp_sendmail: used to send e-mail or paging information.

Sp_stored_procedures: used to return the list of stored procedures in the current database.

Sp_help: used to display the parameter list and its data type.

SP _ Dependents: used to display stored procedure-based objects or stored procedure-based objects.

Sp_helptext: used to display the definition text of the stored procedure.

Sp_rename: used to modify the name of the user object in the current database.

3. Data control language

GRANT statement is an authorization statement, which can grant statement permission or object permission to other users and roles.

DENY statement is used to deny permissions to users or roles in the current database and prevent users or roles from inheriting permissions through their group or role members.

4. System stored procedures

Sp_addtype: used to define user-defined data types.

Sp_configure: Used to manage server configuration option settings.

Xp_sendmail: used to send e-mail or paging information.

Sp_stored_procedures: used to return the list of stored procedures in the current database.

Sp_help: used to display the parameter list and its data type.

SP _ Dependents: used to display stored procedure-based objects or stored procedure-based objects.

Sp_helptext: used to display the definition text of the stored procedure.

Sp_rename: used to modify the name of the user object in the current database.

Program code

1.EXEC sp_renamedb 'userdb 1 '，' userdb2 '

Change the name of the database from userdb 1 to userdb2.

2.EXEC sp_rename 'user_log',' user login table'

Change the name of the current database table from user_log to User Login Table.

Execsp _ rename' user _ info.user _ name',' user name',' column'

Rename the User Name column in the user_info table to User Name.

3.EXEC sp_detach_db 'pubs '，' true '

Sp _ detach _ database

Detach the database from the server and selectively run Update STATISTICS on all tables before detaching.

4. Connect the database to the server

EXEC sp_attach_db @dbname = N'pubs '，

@ filename 1 = N ' c:\ Program Files \ Microsoft SQL Server \ MSSQL \ Data \ pubs . MDF '，

@ filename 2 = N ' c:\ Program Files \ Microsoft SQL Server \ MSSQL \ Data \ pubs _ log . ldf '

5. Attach a database with only one data file to the current server.

EXEC sp _ attach _ single _ file _ db @ dbname = ' pubs '，

@ phys name = ' c:\ Program Files \ Microsoft SQL Server \ MSSQL \ Data \ pubs . MDF '

Comments in SQL

In SQL Server, you can use two types of comment characters: one is the ANSI standard comment character "-"for single-line comments; The other is the same program comment symbol as C language, namely "/* */".

Variables in SQL

There are two forms of variables in Transact-SQL language, one is a user-defined local variable, and the other is a global variable provided by the system.

3. Data control language

GRANT statement is an authorization statement, which can grant statement permission or object permission to other users and roles.

DENY statement is used to deny permissions to users or roles in the current database and prevent users or roles from inheriting permissions through their group or role members.

4. System stored procedures

Sp_addtype: used to define user-defined data types.

Sp_configure: Used to manage server configuration option settings.

Xp_sendmail: used to send e-mail or paging information.

Sp_stored_procedures: used to return the list of stored procedures in the current database.

Sp_help: used to display the parameter list and its data type.

SP _ Dependents: used to display stored procedure-based objects or stored procedure-based objects.

Sp_helptext: used to display the definition text of the stored procedure.

Sp_rename: used to modify the name of the user object in the current database.

Program code

1.EXEC sp_renamedb 'userdb 1 '，' userdb2 '

Change the name of the database from userdb 1 to userdb2.

2.EXEC sp_rename 'user_log',' user login table'

Change the name of the current database table from user_log to User Login Table.

Execsp _ rename' user _ info.user _ name',' user name',' column'

Rename the User Name column in the user_info table to User Name.

3.EXEC sp_detach_db 'pubs '，' true '

Sp _ detach _ database

Detach the database from the server and selectively run Update STATISTICS on all tables before detaching.

4. Connect the database to the server

EXEC sp_attach_db @dbname = N'pubs '，

@ filename 1 = N ' c:\ Program Files \ Microsoft SQL Server \ MSSQL \ Data \ pubs . MDF '，

@ filename 2 = N ' c:\ Program Files \ Microsoft SQL Server \ MSSQL \ Data \ pubs _ log . ldf '

5. Attach a database with only one data file to the current server.

EXEC sp _ attach _ single _ file _ db @ dbname = ' pubs '，

@ phys name = ' c:\ Program Files \ Microsoft SQL Server \ MSSQL \ Data \ pubs . MDF '

Comments in SQL

Variables in SQL

There are two forms of variables in Transact-SQL language, one is a user-defined local variable, and the other is a global variable provided by the system.

Local variable: a local variable is an object that can have a specific data type, and its scope of action is limited to the inside of the program. Local variables can be used as counters to count or control the number of loop executions. In addition, local variables can also be used to store data values tested by control flow statements and data values returned by stored procedures. When a local variable is referenced, the @ symbol should be added before its name, and it must be defined with the DECLARE command before using it.

Global variables: Global variables are variables used within the SQL Server system, and their scope of action is not limited to a program, and any program can be called at any time. Global variables usually store some configuration settings and statistics of SQL Server. Users can use global variables in the program to test the setting value of the system or the state value after executing Transact-SQL commands.

When using global variables, you should pay attention to the following points:

① Global variables are not defined by user programs, they are defined at the server level.

② Users can only use predefined global variables.

③ When referring to global variables, you must start with the mark @ @.

④ The names of local variables cannot be the same as those of global variables, otherwise unpredictable results will appear in the application.

Functions in SQL

In Transact-SQL language, functions are used to perform some special operations to support the standard commands of SQL Server. Transact-SQL programming language provides three functions:

Rowset functions: Rowset functions can be used as table references in Transact-SQL statements.

Two aggregate functions: Aggregate functions are used to perform calculations on a set of values and return a single value.

Three scalar functions: scalar functions are used to process and calculate one or more parameter values passed to them and return a single value.

1. string function

String functions include:

Basic string functions: up, down, space, copy, fill, inversion, LTRIM, RTRIM.

String lookup function: CHARINDEX, PATINDEX.

Length and analysis function: DATALENGTH, SUBSTRING, RIGHT.

Conversion function: ASCH, character, string, sound, difference.

Basic string function

UPPER: converts lowercase character data into uppercase characters.

LOWER: Converts uppercase character data to lowercase characters.

SPACE: Returns a string consisting of repeated spaces. For example, space(2) returns two spaces.

Copy: Repeat the character expression a specified number of times.

Syntax: REPLICATE (character_e? xpression，integer_e？ xpression)

For example, select replicate (au _ fname, 2) from authors, and then repeat each author's name twice.

STUFF: Delete a character of a specified length and insert another set of characters at the specified starting point.

Grammar: things (character_e? Expression, start, length, character? xpression)

(start is an integer value that specifies the starting position of deletion and insertion. If start or length is negative, an empty string is returned. If start is better than the first character _e? Xpression is long, an empty string is returned.

Length is an integer that specifies the number of characters to delete. If the length is greater than the first character _e? Xpression is very long and will be deleted to the last character at most. The last character of xpression. )