Proficient in SQL structured queries---Learn NOTE 2

1. The provisions of the table in sql:
* The table name of each table must start with a letter and a maximum length of 30 characters.
* A table can consist of several columns. In the same table, column names are unique, and column names are also referred to as property names or fields.
* Data in the same column must have the same data type.
* Each column value in the table must be an indivisible base data item.
* A row in a table is called a tuple, which is equivalent to a record.
2. SQL data Type
2.1. Character Type: Includes fixed length and variable long character variable, fixed length character variable is less than defined length, the database will automatically fill the right of the character with a space to 20 characters; variable length strings can store characters of any length depending on the data type and database management system used
Fixed-length character variables have the following two advantages over variable-length character variables:
The 1> DBMS handles fixed-length character variables much more efficiently than variable-length character variables when sorting or processing characters.
2> Many DBMS are not allowed to create indexes for variable-length character variables, but only for fixed-length character variables.
Char a fixed-length string type. The maximum number of bytes cannot exceed 255.
Varchar variable-length string.
The NChar char type extends to support multibyte and Unicode characters.
The NVarchar varchar type extends to support multibyte and Unicode characters.
2.2. Digital type Data
Bit single bit data, can only be value ' 0 ' or ' 1 '. If you enter 0 or 1, the unexpected value will be treated as 1. The bit type cannot be defined as a null value.
TinyInt 1-bit integer with a value range of 0~255
SmallInt 2-bit integer with a value range of -32768~32767
int or Interger 4-bit integer, numeric range -2147483648~2147483647
Real 4-bit floating point number, you can specify precision, but no range.
Double Precision floating-point number.
Float can be accurate to the 15th decimal place, each float type of data occupies 8 bytes of storage space, you can know the precision, without specifying a range.
Number can hold real and integral types,
Note: The data types supported by different database management systems vary, as in SQL Server, the number data type is written as decimal or nurneric.
2.3. Date data type
Date uses 10 characters to hold a 4-digit year, a two-digit month, and a day value of two-digit days, in the format: 2014-01-01.
Time value, which refers to the values of timing, minutes, and seconds. Seconds do not contain decimals unless specified, for example, Time (3) indicates that seconds contain 3 decimal places.
TIMESTAMP includes both date and time. Use 26 characters, where 10 characters are used to save the date, followed by a space, in 15 characters representing the time, including the default 6 decimal digits less than 1 seconds. By adding parameters,

such as timestamp (3), you can specify a different number of decimals.
The information specified by time zone is the same as the time data type, except that the value also includes information that is specific to UTC and timezone.
The TIMESTAMP with time zone includes UTC and timezone information on a TIMESTAMP basis.
In an Oracle database, date data is represented in 7 bytes, and each date type data contains the following:
1> Century Century
2> year
3> Month
4> Day
5> Hour Hours
6> Minute min
7> Second sec

2.4. Binary data type
Binary fixed-length binary data with a maximum length ranging from 255 to 8,000 bytes, depending on the specific DBMS definition.
VARBINARY variable Long binary data with a maximum length ranging from 255 to 8,000 bytes, depending on the definition of the DBMS.
RAW fixed-length binary data with a maximum length of 255 bytes.
Varraw variable Long binary data up to 2GB.
3. Create a table
You can specify column-level constraints when you create a table, such as not NULL, DEFAULT, unique, and so on.
A subquery statement as the data source for creating a table
CREATE TABLE destion_table as SELECT * from source_table;
4. Modification of the table
Modify table name: ALTER table Old_table_name RENAME to New_table_name;
--Add columns
ALTER TABLE test_tab1 ADD column_1 number;
--Modify columns (including data type, default value, etc.)
ALTER TABLE test_tab1 MODIFY column_1 number (5);
--Delete Columns
ALTER TABLE test_tab1 DROP COLUMN column_1;
--Adding constraints
ALTER TABLE test_tab1 ADD CONSTRAINT p_key PRIMARY key (column_1);
--Add a non-null constraint
ALTER TABLE test_tab1 MODIFY column_1 not NULL;
--Delete Constraint
ALTER TABLE test_tab1 DROP CONSTRAINT p_key;

5. Index
An index is a separate, physical database structure that is a list of values contained in a table in a database that indicates where each value of the table is stored. The index is based on the table and provides a way to arrange the data in the table;
The index is divided into two categories, cluster index and non-clustered index; The cluster index sorts the data in the physical data page of the table, and then stores it on disk, that is, the cluster index is mixed with the data, and the actual data is stored in its leaf nodes. The clustered index sorts the data, so finding the data with a clustered index is fast.
A table's data rows can only be stored on disk in one sort, so a table can have only one clustered index.
A non-clustered index has a completely detached structure from the table's data; a table can have up to 249 non-clustered indexes.

650) this.width=650; "Width=" 789 "height=" 383 "title=" 5.png "style=" width:324px;height:247px;float:right; "src=" http ://s3.51cto.com/wyfs02/m00/59/51/wkiom1tpfezq4eh2aahduaus6k0371.jpg "alt=" wkiom1tpfezq4eh2aahduaus6k0371.jpg "/ >650) this.width=650; "Width=" 718 "height=" 480 "title=" 4.png "style=" width:369px;height:231px;float:left; "src=" Http://s3.51cto.com/wyfs02/M01/59/4E/wKioL1TPfXHyVP3bAAHoJdO5sJo575.jpg "alt=" Wkiol1tpfxhyvp3baahojdo5sjo575.jpg "/>

Examples of using non-clustered indexes:
SELECT * from Studentinfo;

650) this.width=650; "Width=" 926 "height=" 413 "title=" 6.png "style=" width:596px;height:320px; "src="/HTTP/ S3.51cto.com/wyfs02/m02/59/4e/wkiol1tpfgeqneqoaanfunhluvm402.jpg "alt=" Wkiol1tpfgeqneqoaanfunhluvm402.jpg "/>

5.1. Create an index
CREATE INDEX Name_index on Studentinfo (sname);
Forcing some sort of index on a query
SELECT * from Studentinfo with (INDEX (Name_index))

Unique: A uniqueness index that does not allow rows in a table to take the same value on an indexed column. If the same value exists, the system gives the relevant information and does not build this index.
Clustered/nonclustered: Clustered and nonclustered indexes, if CLUSTERED, are clustered, that is, the tuples in the table are sorted by the values of the index entries and clustered together. Only one clustered index can be built on a basic table. Nonclustered indicates that the index created is a nonclustered index. By default, a nonclustered index is created.
Asc/desc: The sort order of the Index table's total index value, which is ASC (incremented) by default.

Tables that use the clustered index consume the smallest amount of disk space, because the DBMS automatically reuses the space previously allocated to the deleted rows when inserting new rows.
When querying on column values based on cluster index, there is a faster execution speed because all values are close to each other on the physical disk.
A column based on a clustered index displays a data query in ascending order, and the ORDER BY clause is no longer required because the table's data itself is sorted in the required output order.

Delete Index
DROP INDEX index_name;

5.3, the principle of using the index:
1> does not need to be indexed for small data tables, because it does not improve any retrieval performance.
2> when the data for the field that the user is retrieving contains many data values or many null values, creating an index for that field can greatly improve the retrieval efficiency.
3> If the user retrieves less than 25% of the total data, using the index can significantly improve query efficiency, whereas the index is less useful.
4> index columns should be used frequently in the WHERE clause.
5> the data first, and then builds the index. For most tables, there is always a batch of initial data that needs to be loaded. This principle says that when a table is established, the initial data is loaded into the table and then indexed, which speeds up the initial data entry. If an index is built after the key table, the index is maintained every time a record is inserted when the initial data is entered.
6> indexes increase the speed of data retrieval, but also reduce the speed of data updates. If you want to make a large number of updates to the data in a table, it is better to destroy the index first, and then build the index after the data has been updated, which increases efficiency.
7> index to occupy database space. When designing a database, take into account the required index space.
8> try to keep the table and its index on separate disks, which can improve query speed.

6. View
6.1, the advantages and disadvantages of the view, mainly in the simplification of operations, custom data, merge separated data, security and so on.
There are two main drawbacks to using a view:
Affects query efficiency, update limits (SQL currently restricts updatable views to queries based on a single table and does not have a GROUP by or HAVING clause). In addition, to make the view updatable, the view cannot use the clustered function, the computed column, or the SELECT DISTINCT clause.
6.2. After the view is created, only the view definition is stored in the data dictionary, where the SELECT statement is not executed. The data is removed from the base table as defined by the view only when the user is working on the image.
6.3. Create and delete views
CREATE VIEW Studentinfo_view as SELECT * from Studentinfo;
Because the view is a "virtual table", the view created by the table, or the view of the career, is unavailable when the table is deleted.
DROP VIEW view_name;

Proficient in SQL structured queries---Learn NOTE 2