SQL basic operation
1. Data type data category is an attribute of data, indicating the type of information indicated by the data. Any computer language defines its own data type. Of course, different programming languages have different characteristics, and all kinds of various types and names of the defined data types are more or less. SQL Server provides 25 data types: · binary [(n)] · varbinary [(n)] · char [(n)] · varchar [(n)] · nchar [(n)] · nvarchar [(n) ] · DateTime · SmallDateTime · Decimal [(p [s])] · Numeric [(p [s])] · FLOAT [(N)] · Real · Int · Smallint · Tinyint · Money · Smallmoney · Bit · Cursor · Sysname · TimeStamp · UNIQUEIDENTIFIER · TEXT · Image · NTEXT (1) binary data type binary data includes binary, varbinary, and image. Binary data types can be either fixed (binary) or variable length. Binary [(n)] is the N-bit fixed binary data. Wherein N value ranges from 1 to 8000. The size of its storage is N 4 bytes. Varbinary [(N)] is binary data for n-bit length. Wherein N value ranges from 1 to 8000. The size of the storage is N 4 bytes, not n bytes. The data stored in the image data type is stored in a bit string, not interpreted by SQL Server, must be explained by the application. For example, the application can store data in the Image data type using BMP, TIEF, GIF, and JPEG format. (2) The type of character data type character data includes char, varchar, and text. Character data is data made from any letters, symbols, and numbers. VARCHAR is a bell-length character data, which does not exceed 8KB. Char is a fixed length character data with a maximum length of up to 8KB. More than 8KB ASCII data can be stored using the Text data type. For example, because the HTML document is all ASCII characters, and in general, the length is more than 8KB, so these documents can be stored in the SQL Server in the TEXT data type. (3) UNICODE data type Unicode data types include nchar, nvarchar, and ntext. In Microsoft SQL Server, traditional non-Unicode data types allow using characters defined by a particular character set. Allow a character set during the SQL Server installation. Use the Unicode data type, you can store any characters defined by the Unicode standard. In the Unicode standard, all characters defined in various character sets are included. Using the Unicode data type, the victory is twice as large as the non-Unicode data type. In SQL Server, UNICODE data is stored in Nchar, NVARCHAR, and NTEXT data types. Columns using this type of character type can store characters in multiple characters. When the length of the column changes, the NVARCHAR character type should be used, and up to 4000 characters can be stored. When the length of the column is fixed, the NCHAR character type should be used, and the same, at this time, up to 4000 characters can be stored. When using the NTEXT data type, the column can store more than 4000 characters. (4) Date and time data type date and time data types include DateTime and SmallDateTime.
Date and time data type consist of valid date and time. For example, effective date and time data include "4/01/98 12: 15: 00: 00: 00 PM" and "1: 28: 29: 15: 01 AM 8/17/98". The previous data type is the date before, the time in the latter data is the time before, the date is behind. In Microsoft SQL Server, the date and time data type includes both types of DateTime and SmallDateTime, the stored date range begins on January 1, 1753, ending from December 31, 9999 (8 each value requires 8 Storage bytes). When using the SmallDateTime data type, the stored date range begins on January 1, 1900, and ends on December 31, 2079 (each value requires 4 storage bytes). The date of the date can be set. Set the date format command as follows: set dateformat {format | @format _var | where Format | @format_var is the order of the date. Effective parameters include MDY, DMY, YMD, YDM, MYD, and DYM. By default, the date format is MDY. For example, after the SET DATEFORMAT YMD is executed, the date is formatted in the date of the year; after the SET DATEFORMAT DMY is executed, the date format is the day of the day (5) Digital data type digital data contains only numbers. Digital data types include positive and negative numbers, decimal (floating point numbers) and integers. Integers consisting of positive integers and negative integers, such as 39, 25, 0-2, and 33967. In MicrSoft SQL Server, the integer stored data type is int, smallint, and tinyint. The INT data type storage data is greater than the scope of the Smallint data type stored data, and the scope of the SmallINT type storage data is greater than the range of Tinyint data types. The range of INT data dogs stored data is from -2 147 483 648 to 2 147 483 647 (4 bytes of storage space each value). When using the Smallint data type, storage data ranges from -32 768 to 32 767 (2 bytes storage space each value). When using the Tinyint data type, the storage data ranges from 0 to 255 (one byte storage space for each value). Accurate smuggling data in SQL Server is Decimal and NuMeric. The storage space occupied by this data is determined based on the number of bits after the data of the data. In SQL Server, the data type of approximate decimal data is Float and Real. For example, one-third of this score is recorded. 333333, when using approximate data types, it can be accurately expressed. Therefore, the data retrieved from the system may not be exactly the same as the data stored in the column. (6) The currency data represents the number of positive or negative currencies. In Microsoft SQL Server, the data type of currency data is Money and SmallMoney. The Money data type requires 8 stored bytes, and the SmallMoney data type requires 4 stored bytes. (7) Special data type Special data types include data types that have not been entered in front. There are three special data types, namely TimeStamp, Bit, and UniqueIdentifier. TimeSTAMP is used to represent the order of SQL Server activities, expressed in the format of binary projection.
TimeStamp data does not matter to insert data or date and time. Bit consists of 1 or 0. When you represent true or false, ON or OFF, use the BIT data type. For example, asking if it is a client request to be accessed, it can be stored in the column of this data type. UniqueIdentifier consists of 16-bytes of hexadecimal numbers, indicating a global unique. The GUID is very useful when the recording line of the table requires the only time. For example, using this data type in the customer identification number can distinguish between different customers. 2. User-defined data type User-defined data types are based on data types provided in Microsoft SQL Server. When the same data type must be stored in several tables, and to ensure that these columns have the same data type, length, and blanket, the user-defined data type can be used. For example, a data type called Postal_Code can be defined, which is based on a CHAR data type. When creating a user-defined data type, three numbers must be provided: the name of the data type, the system data type and the data type of data type. (1) Creating a user-defined data type Creating a user-defined data type You can use the Transact-SQL statement. System stored procedures sp_addtype can create user-defined data types. Its grammar is as follows: sp_addtype {type}, [, system_data_bype] [, 'null_type'] where Type is the name of the user-defined data type. System_data_type is the data type provided by the system, such as Decimal, Int, Char, etc. NULL_TYPE indicates how the data type handles null values, and you must use single quotes, such as 'null', 'not null', or 'Nonull'. Example: Use Cust EXEC SP_ADDTYPE SSN, 'VARCHAR (11)', "NOT NULL 'Creating a user-defined data type SSN, whose system data type is characterized by a character that becomes 11, does not allow empty. Example: USE CUST EXEC SP_ADDTYPE BIRTHDAY, DATECE, 'NULL' Create a user-defined data type Birthday, which is based on DateTime, allowed empty. Example: Use master exec sp_addtype telephone, 'varchar (24),' not null 'eexc sp_addtype Fax, 'varchar (24)', 'Null' Creates two data types, TELEPHONE and FAX (2) Delete user-defined data types can be deleted when the user-defined data type is not required. Delete user-defined data types The command is sp_droptype {'type'}. Example: Use master exec sp_droptype 'ssn' Note: When the column in the table is still using the user-defined data type, or when it is also bound to the default or rule, this The user-defined data type cannot be deleted.
2. Database related to the database 1. How to create a database (1). Creating a database Create Database_name [ON {[Primary] (Name = Logical_file_name, filename = 'os_file_name, filename =' os_file_name, filename = 'os_file_name Size = size] [, MaxSize = max_size]} [, ... n]] [log on} (name = logical_name, filename = 'os_file_name' [, size = size])} [, ... n]] [ For restore] Note: Primary: This option is a keyword to specify files in the primary file group. The main file group is not public contains all the contents in the database system table, and it also contains not included in the user file group. All objects. A database can only have a primary file. By default, the first file listed in the statement is the primary file when there is no specified primary keyword. Name: This option specifies the logical name of the database, this Is the name used in the SQL Server system, is the database in SQL Server. FileName: This option is used to specify the operating system file name and path of the file where the database is located. The path in OS_FILE_NAME must be on the server where SQL Server is located. A folder. This operating system file name is a one-word logical name. SIZE: This option is used to specify the size of the database operating system file. When the file size is specified, you can use the MB unit or use KB unit. If there is no specified unit, then the system default unit is MB. The file is minimum is 1MB, that is, the file where the database is located cannot be less than 1MB. By default, the size of the database data file is 3MB, the database log file The size is 1MB. MaxSize: This option is used to specify the maximum size of the operating system file can grow. When specifying the file growth size, you can use the MB unit or the KB unit. If there is no specified unit, then the system's default unit It is MB. If there is no specified file to grow the maximum size, the system grows is unlimited, which can account for full disk space. FielGrowth: This option is used to specify the increment of the file, of course, this option does not have the maxsize option. Conflict. The data value specified by this option is zero, indicating that the file cannot be increased. This option can be specified by MB, KB, and percentage . Example: Create a CUST database, the logical name of the primary data file of the database is CUST_DATA, the operating system file is Cust.mdf, the size is 15MB, the maximum is 30MB, which is increased at 20%; the logical name of the log file of the database Is Cust_log, the operating system is Cust.ldf, the size is 3MB, the maximum is 10MB, and the speed increases at 1MB.
Create Database Cust On Primary (Name = Cust_Data, FileName = 'D: Cust.mdf', size = 15MB, MaxSize = 30MB, FileGrowth = 20%) log on (Name = Cust_Log, filename = 'D: Cust.ldf', Size = 3MB, MAXSIZE = 10MB, FileGrowTH = 1MB) 2. How to add the size of the database (1). Use the alter database command to increase the size of the database file If you do not have the size of the configure file, then you can use it when you create a database file. The alter database command increases the size of the file. Example: Adjust the size of the data file Cust_Data's data file CUST_DATA to 50MB. ALTER DATABASE CUST MODIFY FILE (Name = 'Cust_Data', SIZE = 50) (2). Add a secondary file using add file to increase the size of the database by adding a secondary data file and log file for the database. Example: ALTER DATABASE CUST ADD FILE (Name = Cust_Data2, filename = 'd: Cust2.mdf', size = 5MB, MaxSize = 10MB, FileGrowth = 10%) 3. Compressed Database and Data File (1) Use dbcc shreendatabase command compression Over the database: Compressing the size of the CUST database to 10% DBCC ShrinkDatabase (Cust, 10) (2) Using the DBCC ShrinkFile command to compress a data file in the database: compress the size of the CUST data file to 5MB DBCC SHRINKFILE (CUST) , 5) 4. Delete Database (1) Use the DROP command to delete the database example: CUST Database DROP DATABASE CUST 3. Table of the table) 1. Create a table (1) Creating a table synchronization with a create table command: create table tabl_name {} column_name as computed_column_expression}} [, ... n]) [ON {FIEGROUP | DEFAULT}] [TextImage_ON {FieGroup | DEFAULT}] Example: Open the CUST database, create a table, the table contains students about information, That is, there are student numbers, name, gender, date of birth, origin, contact number, address, and note information.
Use cust Create Table students (number int not null, name varchar (10) not null, sex char (2) null, birthday datetime null, hometown varchar (30) null, telphone_no varchar (12) null, address varchar (30) null Others VARCHAR (50) NULL) In this table, Number represents the student code, the data type is int, not allowed to be empty; Name means that the student name, the data type is varchar, the length is 10, not allowed; SEX means students Gender, data type is char, length is 2, allowed to be empty; Birthday represents the date of birth, the data type is DateTime, allows empty; Hometown indicates that the student's origin, the data type is VARCHAR, the length is 30, allowed to be empty; Telephone_no said the student's contact computer, the data type is varchar, the length is 12, allowed to be empty; Address represents the address of the student, the data type is varchar, the length is 30, allowed to be empty; Others means the student's note information, the length is 50, Allow empty. 2. Structure of modification table (1) Add and delete a new column syntax using the T-SQL statement: alter table table {ado {[] | colun_name as computed_column_expression | []} [, ... n] | Drop {Column Column } [, ... n]}: Open the CUST database, modify the structure of the table students, add a new field, the field name is ying, the data type is varchar, the length is 10, no default value, full of empty . Use custom ALTER TABLE Students Add Ying Varchar (10) NULL Opens the CUST database, modify the structure of the table students, delete a field, the field name is ying. Use Cust ALTER TABLE Students Drop Column Ying 3. Insert data into the table Withinsert statement syntax as follows: INSERT [INTO] {TABLE_NAME | View_name} [(Column_List)] {VALUES | VALUES_LIST | SELECT_STATEMENT} Note: Insert Data When character data and date data should be caused by quotation marks. Example: Use Cust Insert Into Students Values (11, "Shadow", "Men", "1999-12-12", "Hubei", "83779805", "Wuhan Bridge Hou District", "VB Lovers" to open CUST database, insert data to the Students table (2) When using the Default option, you can use the default option. There are two forms in the DEFAULT option, and a form is default values, the other is default. The DEFAULT VALUES is inserted into the default value for all columns of a row in the table.
Prerequisites Using this form is that all columns in the table must be one of these four types: Identity property, TimeStamp data type, allowed to null, or has a specified default value. Otherwise, it will be wrong. Example: Use Cust Insert Into Students Default Values This example will appear errors because the Number field of the Students table is set to not allow empty. The default form is inserted into the default value for a column in the table. This column to be inserted must have a certain condition, that is, the column either the TimeStamp data type, or allows NULL, or has a specified default value, otherwise an error message will occur. Example: Use Cust Insert Into Students Values (11, "Shadow", Default, Default, Default, Default, Default, Default) cannot be empty by the first 2 fields, so you have to assign a value, otherwise an error will occur, while 6 The field allows empty, so the default can be called by default. (3) Insertion part of the data is inserted into the data using the INSERT statement, and some data can also be inserted, that is, the data can be inserted into the specified part of each line. When inserting part of the data, you should pay attention to the following three questions: ☆ In the Insert clause, specify the column name to insert into the data. ☆ In the VALUES clause, the data corresponding to the column name is listed. The order of the column name and the order of the data should be completely corresponding. ☆ In the Insert clause, the columns that are not listed should have at least one of these four types: IdentTTY attribute, TimeStamp data type, allowed to null, or have a specified default value. Otherwise, an error message will appear. Example: Use Cust Insert Into Students (Number, Name) VALUES (110, "Shadow") Opens the custom, insert a row of data to the Students table Note: If the following, the following statement will have an error, because the Name field is not allowed (40) (4) Insert INTO Students (Number) VALUES (110) (4) Inserting multiple data inserts inserted into data inserting data is inserted into a line of data. Instead, SELECT can also be used in the insert statement and can insert multiple data at a time. The syntax form of using the SELECT statement into the data is as follows: Insert Table_name Select COLUMN_LIST TABLE_LIST WHERE Search_Conditions When inserting data using the SELECT statement, you should pay attention to the following: ☆ When using SELECT in the Insert statement, the table they refer may be the same. It can also be different. ☆ Table to insert data must already exist. ☆ Table to insert data must be compatible with the result set of SELECT. The meaning of compatibility is the number and order of the column. The data type or the same column is or the same, or SQL Server can be converted automatically.
Example: Use Cust Insert Students Select Number, Name, SEX, Birthday, Hometown, Telphone_no, address, Others from students Note: The field behind SELECT is complete, this example is inserting multiple data to yourself (insert yourself to yourself. Allowed) Supplements: You can also add "where name =" shadow "after" from students ", only the Name is plugged into the record, and multiple conditions can be coupled with and OR. (5) Use Select INTO to insert data into a new table with the SELECT statement with the INTO clause to allow the user to define a new table and insert the data into the new table. This method is different from those described above. In the foregoing methods, a common feature is that the table already exists before the data input. The method of using SELECT INTO inserting data is to create a new table during the process of inserting data. The syntax of the Select INTO statement is as follows: Select SELECT_LIST INTO NEW_TABLE_NAME FROM TABLE_LIST WHERE SEARCH_CONDITIONS When using SELECT INTO to insert data, pay attention to the following: ☆ When using SELECT INTO in a database, set the select Into / Bulk of the database. COPY is true. ☆ The new table does not exist, otherwise the error message will be generated. ☆ The columns and rows in the new table are based on the query result set ☆ The data to be inserted is not recorded in the log. ☆ The columns that appear in Select_List should use aliases, otherwise, the columns in the new table are not column names. The table without column name can only be queried by Select * from new_table_name. Therefore, it should be a list of individual names. ☆ This method is mostly used in a variety of calculations to column. Example: Select Number, Name INTO NewCust1 from Students Create a new table newcust1, insert all data in the Number and Name fields in the Students table. Supplement: If you want to insert a record of all fields, "SELECT *" can also be applied after "from students", the method, and the previous example. (6) Modify the data Update statement in the table with the UPDATE statement to modify the data already existing in the table. The UPDATE statement can be modified once, or a number of rows can be modified once, and even all the data in the table can be modified. The UPDATE statement uses the WHERE clause to specify the row to modify, and the new data is given using the SET clause. The new data can be constant or the specified expression, but also the data from other tables using the from clause. The syntax of the UPDATE statement is as follows: update {table_name | View_name} set {colorn_list} = expression [,..] [WHERE CLAUSE] When using the UPDATE statement, if you do not use the WHERE clause, then all the lines in the table modify. If you use the UPDATE statement to modify the data conflict with the data integrity constraint, then modification will not happen, and the entire modification transaction rolls back. For example, this conflict may be that the value entered is an error data type, or the input value violates the rule constraint defined in this column, and so on.
Example: Use customer = name "007" Where number> 100 Open the CUST database, modify the Students table, make the value of NAME of Number> 100 plus "007". 4. Remove the data in the table with the DELETE statement. When the data in the database is not required, it can be deleted. In general, delete data uses the DELETE statement. The delete statement can delete one or more data lines from one table at a table. The grammar of the delete statement is as follows: delete [from] Table_name where search_conditions If the WHERE clause is used in the delete statement, then delete the data line that meets the WHERE clause conditions from the specified table. Example: Use Cust Delete Students Where Number> 100 Delete STUDENTS, Number> 100 Data Supplement: If you do not specify a WHERE clause in the DELETE statement, then remove all the records in the table, that is, the delete students statement delete table All records. You can use the TRUNCATE TABLE statement when all data in the table is deleted. The TRUNCATE TABLE statement and the Delete statement can delete all the data in the table, but the two statements have different characteristics. When the user deletes the data using the DELETE statement, the deleted data is recorded in the log. It does not record the changes in the data in the log. Therefore, using the TRUNCATE TABLE STUDENTS statement to delete the record quickly using the DELETE Students statement to delete the speed recorded in the table. 5. Delete the table with the drop table command When the table in the database is not required, you can delete it. The delete table can use the Drop Table statement. The delete table is the definition of the delete table and the license of all data, indexes, triggers, constraints, and specified the table. When the table is deleted, the table-based view cannot be deleted, and the view must be deleted using the DROP VIEW statement. The syntax of the drop table statement is as follows: Drop Table Table_Name Example: Use Cust Drop Table Students Delete the Students table in the CUST database. Supplement: You cannot use the Drop Table statement to delete the table being confusing reference, must first either delete the foreign key constraint or delete the reference table. The owner of the table can delete the table. When the deletion table is, the rule that is bound to this table is missing the binding, the constraint or trigger of the table is automatically deleted. If you recreate the table, you must resize the corresponding rules and default, recreate the trigger and add the necessary constraints. In addition, the system table cannot be deleted. Delete the license of the table belongs to the owner of the table. However, the Data Owner (DBO), System Administrator (SA), and DLL administrators can delete any objects in the database. 4. Retrieving data 1. Retrieving records using the SELECT clause SELECT clause is the core of each retrieval data. It tells the database engine what field is returned. The common form of the SELECT clause is: SELECT * The meaning of this clause is "Returns all the fields that can be found in the specified record source." This form of order is very convenient, because you don't need to know the name of the field retrieved from the table. However, all columns in the search table are inefficient. Therefore, this can greatly improve the efficiency of the query because it is only retrieved.
2. Specify the recording source from clause using the from clause to describe the record source of the query search record; the record source can be a table or another storage query. You can also retrieve records from multiple tables, which will be introduced in later chapters. Example: Select * from students Retrieves all records in the Students table 3. Describe the condition where clause tells the database engine to define its retrieved records according to one or more conditions provided. The condition is an expression that can have two judgments. Example: Select * from students where name = "Shadow" Returns the list of Name fields in students as shadow, this time the result is not a specific order unless you use the Order By clause. The clause will be introduced in the following chapter. Note: The text string boundary in the WHERE clause is double quoted, and in VB is changed to single quotes, because the definition of strings in VB is double quoted. Supplement: Use and and or logic to connect two or more conditional links together to create a more advanced WHERE clause. Example: Select * from students where name = "Shadow" and number> 100 Returns Name as a list of shadow Number greater than 100. Example: Select * from students where name = "Shadow" and (Number> 100 or Number <50) Returns Name as shadow, Number greater than 100 or less than 50. The operator operator function used in the WHERE clause is
Like "M M [^ C]%" Returns any string of "M" and the second character is not "c". 4. Use the Order By Sort the Order By clause to tell the database engine to sort the records. You can sort any fields, or sort multiple fields, and can be sorted in ascending or roning. After a formal Select query contains an ORDER BY clause, you can explain a sort order after following the fields you want to sort (multiple). Example: Use Cust Select * from students where name Like "%" ORDER BY NUMBER Sort by Number by Number. Sort by sequencing To sort in terms of rhubarin, just use the DESC key after sorting fields. Example: Use Cust Select * from students where name Like "%" ORDER BY NUMBER DESC 5. Display a range of first records or last records using TOP. Use the TOP keyword to display only a few records in front or behind. In the query, the TOP keyword and the sort clause share the result set to a few records or displays a part of the entire result record collection in a percentage. Example: Select Top 3 * from students Returns the top 3 records in the Students table SELECT TOP 10 Percent * from students Return to the 10% record in the back Students table SELECT TOP 3 * from students Order by Number Desc Return to the largest number of Number in the Students table (Final) 3 records 6. Use AS to make an alias for the field names why the field is hit by alias, or rename it. There are two reasons why: ☆ The field name involved is very long. I want to make the field more easily handled in the result set. ☆ The created query produces some computing or total columns, which requires naming it. No matter what reason for the field life, the AS clause can be easily used in SQL. Example: SELECT NUMBER AS number, Name As Name from Students 7. Merge Query Merge Query (Union Query) is used to merge two tables with the same field structure, if you want to display multiple recordings in a result set This is very useful when not related records. Example: Select * from stayents Union Select * from students1 This query result set combines the records in Students and StudentS1 into a result, and its output is exactly the same as the original table archive. Note: By default, the merge query does not return repeated records (if the record archive system does not delete the corresponding record after copying the record to the archive table, then this function is useful), you can add all the buttons Let merge queries display repeated records. Example: SELECT * ALECT * ALECT * ALECT * ALECT * FROM StudentS1 This merge query shows that the contents of the Students table and the StudentS1 table do not process the repeated records: UNION operator allows two or more query results to be merged into a query result concentrated.
If you compare Union and Join two operators, then the Union operator increases the number of rows, while the JOIN operator increases the number of columns. It should be noted when using UNION, the structure of the columns in the two results must match, and the data type must be compatible. Union operator syntax is as follows: Select select_list From clause Where clause Group By clause Having clause Union [All] Select select_list From clause Where clause Group By clause Having clause Order By clause Compute clause Union for operators, the following points should be noted : • By default, UNION operators delete all redundant rows. If you use all options, the redundancy row is not deleted. • All Select_List in the Union statement must have the same number of columns, compatible data types and appear in the same order. · In the result set, the column name is from the first SELECT statement. 8. Connection Query often needs to retrieve data from two tables or two tables in both tables or two tables during actual use. The connection is allowed to retrieve data from two tables or two tables, specifying one or some columns in these tables as connection conditions. In SQL Server, two connection syntax forms can be used, one is an ANSI connection syntax form, which is used in the FROM clause, and the other is in the form of SQL Server connection statement, this is connected in the WHERE clause . Ansi connection syntax as follows: Select table_name.column_name, table_name.column_name, ... From {table_name [join_type] Join table_name On search_conditions} Where [search_conditions] In Ansi grammatical forms, may determine the connection Ansi connection keyword used in the form of . For example: ☆ Using the Inner Join keyword, the result is set only to meet the conditions of the condition. ☆ Use the Cross Join keyword, the result set contains a combination of all rows in two tables. ☆ Using the Outer Join keyword, the result is included both those who meet the conditions, and all of them are all of which. SQL Server connection syntax is as follows: SELECT TABLE_NAME.COLUMN_NAME, TABLE_NAME.COLUMN_NAME, TABLE_NAME.COLUMN_NAME, ...WM [TABLE_NAME, TAB Copyright: UML Software Engineering
