Basic SQL statements

// Sort basic SQL statements

// Create a database
Create Database database_name;

// Delete the database
Drop database database_name;

// Creation Mode
Create schema name_of_schema authorization name_of_user;

// Deletion Mode
Drop schema name_of_schema cascade; // you can delete all objects in a row.
Drop schema name_of_schema restrict; // If a subordinate object exists, the deletion operation is denied.

// Create a table
Create Table name_of_table
(
Name_of_column1 column_datatype,
Name_of_column2 column_datatype,
Name_of_column3 column_datatype
);

// Example
Create Table train_times
(
Start_location varchar (75 ),
Destination varchar (75 ),
Departs time,
Arrives time
);

// Modify the basic table
Alter table name_of_table
Add name_of_newcolumn data_type condition;
Drop condition;
Alter column name_of_column data_type condition;

// Delete the basic table
Drop table name_of_table cascade; // all objects with dependencies are deleted.
Drop table name_of_table restrict; // a dependency object is deleted.

Drop table student cascade;

// Data type
Char (n) // a fixed-length string with a length of N
Varchar (n) // variable-length string with the maximum length of N
INT/Integer // long integer
Smallint // short integer
Numeric (p, d) // count, P digit, d decimal
Real // floating point number of machine precision
Double Precision // double-precision floating point number of the machine Length
Float (n) // floating point number, precision at least N
Data // Date YYYY-MM-DD
Time // time hh: mm: SS

// Insert new data
Insert
Table_name (column_name1, column_name2, column_name3 ,....)
Into
Values (data_value1, data_value2, data_value3 ,....);

// Display all data in the table
Select *
From table_name

Create Table memberdatils //
(
Memberid integer,
Firstname varchar (50 ),
Lastname varchar (50 ),
Dateofbirth data,
Street varchar (100 ),
City varchar (75 ),
_ State varchar (75 ),
Zipcode varchar (12 ),
Email varchar (200 ),
Dateofjoining date
);

Insert
Memberdatils
Values
(
1,
'Cid ',
'Smith ',
'2017-01-09 ',
'Main road ',
'Towns ',
'Stateid ',
'123 ',
'Kate @ mail.com ',
'2017-02-23'
);

Create Table Category
(
Categoryid integer,
CATEGORY varchar (190)
);

Insert
Category (categoryid, category)
Values (6, 'historical ');

// Update data
Update table_name
Set column_name = data_value
Where condition

Update memberdetails
Set
Street = '45 upper road ',
City = 'new town ',
_ State = 'new State ',
Zipcode = '000000'
Where memberid = 4;

// Where clause, used as a condition for query and update
// <> Not equal
// Logical operators and or
// Equivalent to & and in C ++ |

Update memberdetils
Set _ state = 'Mega state'
Where
_ State = 'small state'
Or
_ State = 'stateid ';

Update location
Set _ state = 'Mega state'
Where
_ State = 'small state'
Or
_ State = 'stateid ';

// Delete data
Delete from memberdetails // delete all records in the table

Delete from memberdetails // The WHERE clause can be used to specify the condition.
Where memberid = 3;

// Extract information and query
Select column1, column2, columnx
From table_name;

//
Select memberid, firstname
From memberdetails;

// Replace all columns *
Select *
From location;
// Try not to use all columns, and the image processing speed is a waste of memory.

// If you only want to know the unique value in a record, you can use distinct
Select distinct City
From memberdetails;
// Only the unique value appears in the city.

// Use an alias
Select sname name, yearofbirth birth, 2004-sage birthday, lower (sdept) Department
From student;

Select lastname as surname
From memberdetails;

// Use where to filter sub-results
Select firstname + ''+ lastname as [full name]
From memberdetails
Where city = 'Big City ';

// Operator priority
// Parentheses ()
// Not
// And
// All, any, between, in, like, Or, some

// Use parentheses whenever possible to change the priority
// Filter conditions based on true
Select firstname
From memberdetails
Where not state = 'golden state ';

//
Select firstname
From memberdetails
Where State <> 'golden state ';

// Between is used to specify a range.
// The range is between two values.

Select filitiname, rating
From films
Where rating between 3 and 5;

// Like Operator
// Used for String Matching
Select lastname
From memberdetails
Where lastname like 'J % ';

// In some databases, like is case sensitive, such as Oracle
// % Matches one or more characters
// _ Match a character

Select lastname
From memberdetails
Where lastname like 'd _ s ';

// Like is often used with not like

// In Operator
Select City
From memberdetails
Where
City in ('townsvill', 'windy village ', 'dover', 'Big City ');

Select folmname, yearreleased, rating
From films
Where
Yearreleased in (1967,1977, 1987)
And
Rating in (4, 5 );

// Sort the result by order
// The default Ascending Order
Select yearreleased
From films
Order by yearreleased;

Order by yearreleased DESC;

// If there is a where clause, order by should be placed behind the WHERE clause
// Between '2017-01-01 'and '2017-02-03'

// Select data from multiple tables
Select filename, yearreleased, rating
From films inner join category
On films. categoryid = category. categoryid
Where category. categoryid = 6;

Select filename, yearreleased, rating
From films, category
On films. categoryid = categoryid. categoryid
Where category. categoryid = 6;

// Constraints
// Not null
Create Table mytable
(
Column1 int not null,
Column2 varchar (20 ),
Column3 varchar (12) not null
);

Alter table mytable
Modify column2 varchar (20) not null;

// Unique
// Prevent two values from being the same in a specific column
// Ensure data uniqueness of a specific column
Create Table mytable
(
Column1 int unique,
Column2 varchar (20) unique,
Column3 varchar (12) unique
);

// Check Constraints
/// Check the value of each input to the record. If it is false, it violates the constraint and is not input to the table.
Create Table namesages
(
Name varchar (50 ),
Age int check (age> 0)
);

Insert
Namesages (name, age)
Values ('Jim ', 30 );

// Primary key constraint. Each table should have a primary key.
Create Table holidaybookings
(
Customerid int primary key,
Bookingid int,
Destination varchar (50)
);

// In IBM DB2, the primary key column must be not null
Create Table holidaybookings
(
Customerid int not null primary key,
Bookingid int,
Destination varchar (50)
);

//
Create Table holidaybookings
(
Customerid int not null,
Bookingid int not null,
Destination varchar (50 ),
Primary Key (customerid, bookingid)
);

// Foreign key constraint
// The foreign key is the column that accesses the primary key of another table.
Foreign key (name_of_column) References name_of_table (name_of_primarycolumn );

Create Table SC
(
Sno char (9 ),
CNO char (4 ),
Primary Key (SNO, CNO ),

Foreign key (SNO) References student (SNO ),
Foreign key (CNO) References course (CNO)
);

// Index
Create index name_of_index
On name_of_table (column1 ASC, column2 DESC ,...)

Create unique index name_of_index // unique record of an index
Create Cluster Index name_of_index // create a cluster index. data that is frequently updated cannot be used.

// Delete the index

Drop index name_of_table.index_name;

// Well-designed database skills
// Do not design situations that only meet common conditions. cover all possible situations.
// Select a meaningful name for the table and Field
// Make the name as simple as possible
// The name and data type must be consistent.
// Analyze requirements on paper first
// Select the primary key carefully
// Create an index to accelerate the search speed
// Add a multi-column Index
// Avoid using reserved words as the table name or field name

// Consider storage space

// Indicate the source for reprinting