SQL Basics (iv): SQL command

1.CREATE INDEX Statement

The CREATE INDEX statement is used for creating indexes in the table. Indexes enable database applications to find data faster without reading the entire table.

Index: Creates an index in a table for faster and more efficient querying of data. Users cannot see the index, they can only be used to speed up search/query.

Note: Updating a table that contains an index requires more time than updating a table that does not have an index, because the index itself needs to be updated. Therefore, it is ideal to create indexes only on columns (and tables) that are often searched.

 // sql CREATE INDEX syntax  //  create INDEX index_nameon table_name (column_name)  // sql CREATE UNIQUE INDEX Syntax  //  create UNIQUE INDEX index_nameon table_name (column_name)  //  Note: The syntax used to create an index differs from one database to another. Therefore, check the syntax for creating indexes in your database. 

// Create an index named "Pindex" on the "LastName" column of the "Persons" table: CREATE INDEX Pindexon Persons (LastName) // If you want to index more than one column, you can list the names of these columns in parentheses, separated by commas: CREATE INDEX Pindexon Persons (LastName, FirstName)

2. DROP statement

The drop statement makes it easy to delete indexes, tables, and databases.

The DROP INDEX statement is used to delete indexes in the table.

// DROP INDEX syntax for MS Access: DROP INDEX index_name on table_name // DROP INDEX syntax for MS SQL Server: DROP INDEX table_name.index_name // the DROP INDEX syntax for db2/oracle: DROP INDEX index_name // DROP INDEX syntax for MySQL: ALTER TABLE table_name DROP INDEX index_name

The DROP TABLE statement is used to delete a table. Syntax: DROP TABLE table_name

The DROP DATABASE statement is used to delete databases. Syntax: DROP DATABASE database_name

　　TRUNCATE Table statement: Only the data within the table needs to be deleted, but the table itself is not deleted. Syntax: TRUNCATE TABLE table_name

3. ALTER TABLE Statement

The ALTER table statement is used to add, delete, or modify columns in an existing table .

Grammar:

//to add columns to a table, use the following syntax:ALTER TABLE table_nameadd column_name datatype//to delete a column from a table, use the following syntax (note that some database systems do not allow this way to delete columns in a database table):ALTER TABLE table_namedrop COLUMN column_name//to change the data type of a column in a table, use the following syntax://SQL server/ms Access:ALTER TABLE table_namealter COLUMN column_name datatype//My sql/oracle:ALTER TABLE table_namemodify COLUMN column_name datatype//Oracle 10G Post-release:ALTER TABLE table_namemodify column_name datatype;

Please see the "Persons" table:

Now, we want to add a column named "dateOfBirth" in the "Persons" table. We use the following SQL statement: ALTER TABLE Persons ADD dateofbirth date. The type of the new column "dateOfBirth" is date and can be stored. The data type specifies the type of data that can be stored in the column.

Now, the "Persons" table will look like this:

Now, we want to change the data type of the "dateOfBirth" column in the "Persons" table. We use the following SQL statement: Alter TABLE Persons alter COLUMN dateOfBirth year. The type of the "dateOfBirth" column is now year, which can hold 2-bit or 4-bit formats.

Next, we want to delete the "dateOfBirth" column in the "Person" table. We use the following SQL statement: ALTER TABLE Persons DROP COLUMN dateOfBirth. Now the added dateOfBirth column is deleted.

4. SQL NULL Value

If a column in the table is optional, we can insert a new record or update an existing record without adding a value to the column. This means that the field will be saved with a NULL value.

NULL values are handled differently than other values. NULL is used as a placeholder for unknown or non-applicable values.

Note: NULL and 0 cannot be compared; they are not equivalent.

So how do we test for NULL values? You cannot use a comparison operator to test for NULL values, such as =, <, or <>. We must use the is null and is not NULL operator.

SQL is NULL: How do we just pick a record with a NULL value in the "Address" column? We must use the IS NULL operator:

SELECT lastname,firstname,address from Personswhere Address is NULL // always use is NULL to find null values. 

SQL is not null: How do we just pick a record that does not have a NULL value in the "Address" column? We must use the is not NULL operator:

SELECT lastname,firstname,address from Personswhere Address was not NULL

5. SQL NULL function

　　The ISNULL () function is used to specify how NULL values are handled.

The NVL (), Ifnull (), and coalesce () functions can also achieve the same result.

//here, we want the NULL value to be 0. //below, if "UnitsOnOrder" is null, the calculation is not affected, because if the value is null then ISNULL () returns 0://SQL server/ms AccessSELECT productname,unitprice* (Unitsinstock+isnull (UnitsOnOrder,0) ) from the products//Oracle:oracle has no ISNULL () function. However, we can use the NVL () function to achieve the same result:SELECT productname,unitprice* (UNITSINSTOCK+NVL (UnitsOnOrder,0) ) from the products//Mysql:mysql also has functions similar to ISNULL (). But the way it works is a little different from Microsoft's ISNULL () function. In MySQL, we can use the Ifnull () function as follows:SELECT productname,unitprice* (Unitsinstock+ifnull (UnitsOnOrder,0) ) from the products//or we can use the coalesce () function as follows:SELECT productname,unitprice* (Unitsinstock+coalesce (UnitsOnOrder,0) ) from the products

// If the Alexa column is a null value, give 0, otherwise, take the original value Select id,name,url,ifnull (Alexa,0) from websites; Select id,name,url,coalesce (Alexa,0 from websites;

6. Data type

Check the documentation

SQL Basics (iv): SQL command