SQL and SQL tutorials

SQL and SQL tutorials

At last, I had time to continue playing the Alibaba Data Mining competition. I had to refresh the database with the limited tools for the second season. The following notes are summarized from the Web tutorial.

SQL Introduction

• SQL refers to the structured query language.
• SQL is an ANSI standard computer language with different versions, but different versions support some common keywords.

SQL syntax

• A database usually contains one or more tables.
• SQL is case insensitive

• Some database systems require the use of semicolons at the end of each SQL command

• SQL DML and DDL

  SQL is divided into two parts: data operation language (DML) and Data Definition Language (DDL)

  • The query and update commands constitute the DML part of SQL:

    SELECT-get data from database table UPDATE-UPDATE data in database table DELETE-DELETE data from database table insert into-INSERT data INTO database table

  • The most important DDL statements in SQL:

    Create database-CREATE new DATABASE-modify database create table-CREATE new TABLE-change (Change) database table drop table-delete table create index-create index (search key) drop index-delete an INDEX

SELECT statement

    SELECT column_name1,column_name2 FROM table_name    SELECT * FROM table_name

Select distinct statement

The keyword DISTINCT is used to return a unique value.

SELECT DISTINCT column_name FROM table_name

WHERE clause

To SELECT data from a table with conditions, you can add the WHERE clause to the SELECT statement.

SELECT column name FROM table name WHERE column operator Value

AND & OR operator

And or can combine two OR more conditions in the WHERE substatement.

SELECT * FROM table_name WHERE column_name1='XXX' AND column_name2='XXX'SELECT * FROM table_name WHERE (column_name1='XXX' OR column_name2='XXX') AND column_name3='XXX'

Order by clause

The order by statement is used to sort the result set based on the specified column. Records are sorted in ascending order by default. If you want to sort records in descending ORDER, you can use the DESC keyword.

SELECT column_name1, column_name2 FROM table_name ORDER BY column_name1 [DESC]

Insert into statement

The insert into statement is used to INSERT a new row INTO the table.

Insert into table name VALUES (value 1, value 2 ,....)

You can also insert data only in the specified column. Leave the following blank when no data is inserted:

Insert into table_name (column 1, column 2,...) VALUES (value 1, value 2 ,....)

UPDATE statement

The Update statement is used to modify data in a table.

UPDATE table name SET column name 1 = new value 1, column name 2 = new value 2 WHERE column name = A Value

DELETE statement

The DELETE statement is used to DELETE rows in a table.

Delete from table name WHERE column name = Value

TOP clause

The TOP clause is used to specify the number of records to be returned.

• SQL Server Syntax:

  SELECT TOP number|percent column_name(s) FROM table_name

• MySQL syntax

  SELECT column_name(s) FROM table_name LIMIT number

LIKE Operator

The LIKE operator is used to search for the specified mode in the WHERE clause.

SELECT column_name(s)FROM table_nameWHERE column_name LIKE pattern

Example:

SELECT * FROM PersonsWHERE City LIKE 'n' % '-- "%" can be used to define wildcards (letters missing in the Mode)

Wildcard

% Replace one or more characters

_ Replace only one character

IN Operator

The IN operator allows us to specify multiple values IN the WHERE clause.

SELECT column_name(s)FROM table_nameWHERE column_name IN (value1,value2,...)

BETWEEN Operator

Used in the WHERE clause to select a data range between two values.

SELECT column_name(s)FROM table_nameWHERE column_nameBETWEEN value1 AND value2

JOIN

Join is used to query data from two or more tables based on the relationship between columns.

SELECT table1.column1, table1.column2, table2.columnFROM table1 INNER JOIN table2ON table1.column = table2.column

• In addition to inner join, we can also use several other connections. The following lists the possible JOIN types and their differences.

  JOIN: if there is at least one match in the table, the returned rows (inner join and JOIN are the same .) Left join: returns all rows from the LEFT table even if there is no matching in the RIGHT table. right join: returns all rows from the RIGHT table even if there is no matching in the LEFT table. full join: if one of the tables matches, the row is returned.

UNION and union all operators

The UNION operator is used to merge the result sets of two or more SELECT statements.
Note that the SELECT statement inside the UNION must have the same number of columns. Columns must also have similar data types. In addition, the columns in each SELECT statement must be in the same order.

SELECT column_name(s) FROM table_name1UNIONSELECT column_name(s) FROM table_name2

By default, the UNION operator selects different values. If repeated values are allowed, use union all:

SELECT column_name(s) FROM table_name1UNION ALLSELECT column_name(s) FROM table_name2

Result of concatenating two select statements by Column

Select into statement

The select into statement selects data from one table and inserts the data INTO another table.
The select into statement is often used to create backup copies of a table or archive records.

SELECT *INTO new_table_name [IN externaldatabase] FROM old_tablename

Or insert only the expected columns into the new table:

SELECT column_name(s)INTO new_table_name [IN externaldatabase] FROM old_tablename

Create database statement

CREATE DATABASE database_name

Create table statement

The create table statement is used to CREATE tables in the database.

Create table Name (column name 1 data type, column name 2 data type, column name 3 data type ,....)

The data type (data_type) specifies the data type that a column can accommodate. The following table contains the most common data types in SQL:

Integer (size) int (size) smallint (size) tinyint (size) only contains integers. Specifies the maximum number of digits in parentheses. Decimal (size, d) numeric (size, d) contains digits with decimals. "Size" specifies the maximum number of digits. "D" specifies the maximum number of digits to the right of the decimal point. Char (size) is a string of a fixed length (which can contain letters, numbers, and special characters ). Specify the length of the string in parentheses. Varchar (size) can contain variable-length strings (including letters, numbers, and special characters ). Specify the maximum length of a string in parentheses. Date (yyyymmdd) contains the date.

Not null Constraint

The not null constraint forces columns to NOT accept NULL values.
Not null constraints force fields always contain values. This means that a new record or update record cannot be inserted without adding a value to the field.

Primary key constraint

The primary key constraint uniquely identifies each record in the database table.
The primary key must contain a unique value.
The primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only one primary key.

The following SQL statement creates a PRIMARY KEY constraint in the "Id_P" column when creating the "Persons" table:

CREATE TABLE Persons(Id_P int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),PRIMARY KEY (Id_P))

Foreign key constraint

The foreign key in one table points to the primary key in another table.

CHECK Constraints

The CHECK constraint is used to limit the range of values in a column.

The following SQL statement creates a CHECK constraint for the "Id_P" column when the "Persons" table is created. The CHECK constraint specifies that the "Id_P" column must contain only integers greater than 0.

CREATE TABLE Persons(Id_P int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),CHECK (Id_P>0))

DEFAULT Constraint

The DEFAULT constraint is used to insert a DEFAULT value to a column.

The following SQL statement creates a DEFAULT constraint for the "City" column when the "Persons" table is created:

CREATE TABLE Persons(Id_P int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255) DEFAULT 'Sandnes')

Create index statement

The create index statement is used to CREATE an INDEX in a table.
Without reading the entire table, the index enables database applications to search for data faster.

You can create indexes in a table to query data more quickly and efficiently.
Users cannot see indexes. They can only be used to accelerate search/query.
Note: It takes more time to update an index-containing table than to update an index-free table because the index itself also needs to be updated. Therefore, it is ideal to create an index only on columns (and tables) that are frequently searched.

• Create a simple index on the table. Repeated values are allowed:

  Create index index_nameON table_name (column_name) can have multiple columns

• Create a unique index on the table. A unique index means that two rows cannot have the same index value.

  CREATE UNIQUE INDEX index_nameON table_name (column_name1，column_name2)

DROP

DROP statement, which can easily Delete indexes, tables, and databases.

• Use the drop index command to delete indexes in a table.

  DROP INDEX index_name ON table_name

• The drop table statement is used to delete a TABLE (the TABLE's structure, attributes, and indexes will also be deleted)

  Drop table Name

• The drop database statement is used to delete a DATABASE:

  Drop database name

Alter table statement

• To add columns to a table, use the following syntax:

  ALTER TABLE table_nameADD column_name datatype

• To delete columns in a table, use the following syntax:

  ALTER TABLE table_name DROP COLUMN column_name

• To change the data type of columns in a table, use the following syntax:

  ALTER TABLE table_nameALTER COLUMN column_name datatype

Date function NULL Value

NULL is the missing unknown data.
By default, table columns can store NULL values.
Is null and is not null operators.

SQL Server-RDBMS

Modern SQL servers are built on RDBMS.

• DBMS-Database Management System)

The database management system is a computer program that can access data in the database.
DBMS enables us to extract, modify, or store information in the database.
Different DBMS provides different functions for querying, submitting, and modifying data.

• RDBMS-Relational Database Management System)

Relational Database Management System (RDBMS) is also a database management system. Its database organizes and accesses data based on the relationship between data.
In early 1970s, IBM invented RDBMS.
RDBMS is the foundation of SQL and also the basis of all modern database systems such as Oracle, SQL Server, IBM DB2, Sybase, MySQL and Microsoft Access.

SQL Functions

SQL has many built-in functions for counting and computing.

The syntax for built-in SQL functions is:

SELECT function (column) FROM table

• AVG Functions

The AVG function returns the average value of the value column. NULL values are not included in calculation.

SELECT AVG(column_name) FROM table_name

• COUNT () function

The COUNT () function returns the number of rows matching the specified condition. NULL is not counted.

SELECT COUNT(column_name) FROM table_name

The SQL COUNT (*) syntax returns the number of records in the table:

SELECT COUNT(*) FROM table_name

The COUNT (DISTINCT column_name) syntax returns the number of different values in the specified column:

SELECT COUNT(DISTINCT column_name) FROM table_name

• FIRST () function

The FIRST () function returns the value of the FIRST record in the specified field.

SELECT FIRST(column_name) FROM table_name

• LAST () function

The LAST () function returns the value of the LAST record in the specified field.

SELECT LAST(column_name) FROM table_name

• MAX () function, MIN () function

Returns the maximum and minimum values in a column. NULL values are not included in calculation.

SELECT MIN(column_name) FROM table_name

• SUM () function

The SUM function returns the total number (total) of numeric columns ).

SELECT SUM(column_name) FROM table_name

• Group by statement

The group by statement is used in combination with the aggregate function to GROUP result sets based on one or more columns.

SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name

• HAVING clause

The HAVING clause is added to SQL because the WHERE keyword cannot be used with the aggregate function.

SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator value

Example:

Now we want to find the customer "Bush" or "Adams" with more than 1500 of the total order amount.

SELECT Customer,SUM(OrderPrice) FROM OrdersWHERE Customer='Bush' OR Customer='Adams'GROUP BY CustomerHAVING SUM(OrderPrice)>1500

• UCASE () and LCASE () Functions

The UCASE function converts the field value to uppercase.

The LCASE function converts the field value to lowercase.

SELECT LCASE(column_name) FROM table_name

• MID () function

The MID function is used to extract characters from text fields.

SELECT MID(column_name,start[,length]) FROM table_name

• LEN () function

The LEN function returns the length of the median in a text field.

SELECT LEN(column_name) FROM table_name

• ROUND () function

The ROUND function is used to ROUND a numeric field to a specified decimal place.

SELECT ROUND(column_name,decimals) FROM table_name

Decimals specifies the number of decimal places to be returned.

• NOW () function

The NOW function returns the current date and time.

SELECT NOW() FROM table_name

• FORMAT () function

The FORMAT function is used to FORMAT the field display.

SELECT FORMAT(column_name,format) FROM table_name

Format