Common SQL code and SQL code

Common SQL code and SQL code
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 a unique value.
Syntax: select distinct column_name, column_name form table_name
3. The where clause is used to filter records.
Syntax: select column_name, column_name form table_name where column_name operator value;


UPDATE-UPDATE data in the database
Update table_name set column1 = value1, column2 = value2,... where some_column = some_value; (the where clause specifies which record or which record needs to be updated. If ignored, all the products will be updated)


DELETE-DELETE data from the database
Syntax: delect from table_name where some_column = some_value;


Insert into-INSERT new data INTO the database
There are two writing modes:
1. insert into table_name values (value1, value2, value3 ,...);
2. insert into table_name (column1, column2, column3,...) values (value1, value2, value3 ,...);


Create database-CREATE a new DATABASE
Create database dbname;




Alter database-modify DATABASE

Create table-CREATE 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 a column (such as varchar, integer, decimal, and date ).


The size parameter specifies the maximum length of a column in the table.


In SQL, we have the following constraints:


Not null-indicates that a column cannot store NULL values.
UNIQUE-ensure that each row of a column must have a UNIQUE value.
The combination of primary key-not null and UNIQUE. Make sure that a column (or the combination of two or more columns) has a unique identifier to help you locate a specific record in the table more quickly.
Foreign key-ensure the integrity of the reference when data in one table matches the values in another table.
CHECK-ensure that the values in the column meet the specified conditions.
DEFAULT-specifies the DEFAULT value when the column is not assigned a value.


Alter table-Change Database TABLE
Drop table-delete a TABLE
Create index-create index (search key)
SQL CREATE INDEX syntax


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


Create index index_name
ON table_name (column_name)
SQL CREATE UNIQUE INDEX syntax


Create 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 are not allowed:


Create unique index index_name
ON table_name (column_name)


Drop index-delete an INDEX


Order by keyword
Used to sort result sets
Syntax: select column_name, column_name form table_name order by column_name, column_name ASC | DESC;


Inner join returns a row if at least one matching exists in the table.
Syntax: select column_name (s) from table1 inner join table2 on table1.column _ name = table2.column _ name


The select into statement copies data from one table and inserts the data INTO another new table.
We can copy all columns and insert them into the new table:


SELECT *
INTO newtable [IN externaldb]
FROM table1;
Or copy the expected columns and insert them to the new table:


SELECT column_name (s)
INTO newtable [IN externaldb]
FROM table1;


The insert into select statement copies data from a table and 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 columns from one table and insert them to another existing table:


Insert into table2
SELECT * FROM table1;
Alternatively, you can copy the expected columns and insert them to another existing table:


Insert into table2
(Column_name (s ))
SELECT column_name (s)
FROM table1;

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.