Some of the most important SQL commands
SELECT-Extract data from the database
1. Select syntax
Select Column_name,column_name Drom table_name
Or
SELECT * FROM table_name (select all columns)
2. Select DISTINCT is used to return only different values
Syntax: SELECT DISTINCT column_name,column_name form table_name
3. Where words are used to filter records
Syntax: Select column_name,column_name form table_name where column_name operator value;
Update-Updates the data in the database
UPDATE table_name set column1=value1,column2=value2,... where some_column=some_value; (WHERE clause specifies which record or which record needs to be updated.) If omitted, all the product will be updated)
Delete-Deletes data from the database
Syntax: Delect from table_name where some_column=some_value;
INSERT INTO-inserts new data into the database
There are two forms of writing:
1. INSERT INTO table_name values (VALUE1,VALUE2,VALUE3,...);
2. INSERT INTO table_name (COLUMN1,COLUMN2,COLUMN3,...) VALUES (Value1,value2,value3,...);
Create database-Creating new databases
CREATE DATABASE dbname;
ALTER DATABASE-Modify databases
CREATE table-Creates a new table
SQL CREATE TABLE Syntax
CREATE TABLE table_name
(
Column_name1 data_type (size),
Column_name2 data_type (size),
Column_name3 data_type (size),
....
);
The column_name parameter specifies the name of the column in the table.
The Data_type parameter specifies the data type of the column (for example, varchar, integer, decimal, date, and so on).
The size parameter specifies the maximum length of the column in the table.
In SQL, we have the following constraints:
Not null-Indicates that a column cannot store a NULL value.
Unique-guarantees that each row of a column must have a unique value.
PRIMARY Key-not NULL and a UNIQUE combination. Ensuring that a column (or a combination of more than two columns) is uniquely identified helps make it easier and faster to find a particular record in a table.
FOREIGN KEY-ensures that data in one table matches the referential integrity of values in another table.
CHECK-guarantees that the values in the column meet the specified criteria.
Default-Specifies that no value is assigned to the column.
ALTER TABLE-Change (change) database table
drop table-Delete tables
Create index-Creating indexes (search key)
SQL CREATE INDEX Syntax
Create a simple index on the table. Allowed to use duplicate values:
CREATE INDEX index_name
On table_name (COLUMN_NAME)
SQL CREATE UNIQUE INDEX syntax
Creates a unique index on the table. Duplicate values are not allowed: A unique index means that two rows cannot have the same index value. Creates a unique index on a table. Duplicate values is not allowed:
CREATE UNIQUE INDEX index_name
On table_name (COLUMN_NAME)
Drop INDEX-Delete indexes
ORDER BY keyword
Used to sort the result set
Syntax: SELECT Column_name,column_name form table_name ORDER BY Column_name,column_name asc| DESC;
Inner JOIN returns a row when there is at least one match in the table
Syntax: SELECT COLUMN_NAME (s) from table1 inner joins table2 on Table1.column_name=table2.column_name
The SELECT into statement copies the data from one table and then inserts the data into another new table.
We can copy all the columns into the new table:
SELECT *
Into newtable [in Externaldb]
from table1;
Or just copy the columns you want into the new table:
SELECT column_name (s)
Into newtable [in Externaldb]
from table1;
The INSERT INTO SELECT statement copies data from a table, and then inserts the data into an existing table. Any existing rows in the target table will not be affected.
SQL INSERT into SELECT syntax
We can copy all the columns from one table into another existing table:
INSERT into table2
SELECT * FROM table1;
Or we can just copy the desired column into another existing table:
INSERT into table2
(column_name (s))
SELECT column_name (s)
from table1;
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
SQL Common Code
