Database basics-Basic SQL operations on Data

Structured Query Language. The main function of SQL is to establish contact and communicate with various databases. According to ANSI (American National Standards Association), SQL is used as the standard language for relational database management systems. SQL statements can be used to perform various operations, such as updating data in the database and extracting data from the database. Currently, most popular relational database management systems, such as Oracle, Sybase, Microsoft SQL Server, and access, use SQL language standards.

 

SQL provides commands for many tasks, including:

● Query data

● Insert, modify, and delete records in a table

● Create, modify, and delete data objects

● Control access to data and data objects

● Ensure Database Consistency and integrity

 

There are many types of executable SQL statements. With SQL, You can execute any function: from a simple table query, to creating tables and stored procedures, to setting user permissions. In general operations, we often use the following commands:

● Selcet

● Insert

● Update

● Delete

● Create

Common SQL commands

Command	Category	Description
Select	Data Query Language	Retrieve rows or columns from one or more tables
Insert	Data manipulation language	Add rows to a table
Update	Data manipulation language	Update the columns of existing rows in the table.
Delete	Data manipulation language	Delete rows from a table
Create	Data Definition Language	Create a new table
Drop	Data Definition Language	Delete a table

 

 

1. SELECT statement syntax

 

Select syntax

Components	Description
Select	Indicates the data column to be retrieved
From	Specify the data tables to be retrieved
Where	Specify search criteria
Group	Indicates that the returned column data is grouped by certain conditions.
Having	Specify the criteria that must be met for the returned collection
Order	Specify the order of returned rows

　

Create a student table in the database, including the following fields:

 

Studentname

Sex

Address

Phone

 

● A simple SELECT statement:

Select studentcode, studentname from student

The result of this simple query is the data of the selected column returned by the student table. In the same search case, if you want to narrow down the scope, for example, you only want to know some information about the male students, you can use the following statement:

 

● Add a where clause

Select * from student where sex = 'male'

When retrieving table rows, the where clause can use multiple columns as the constraint content. Of course, you can add the and or constraints to the search constraint content to implement multiple constraints. In addition, in the SELECT statement, we can change the constraints by changing the comparison operator to achieve the search purpose we need. Let's look at another example:

 

● Complete restricted Query

Select * from student where studentname = 'zhang san' and phone = '000000'

The query results return information of students whose names are Michael Jacob and whose phone number is 1234567.

Comparison operators supported in select statements

Operator	Description
=	Equal
>	Greater
<	Less
> =	Greater than or equal
<=	Small and equal
<>	Not equal
In	Located in the specified list value or in the specified subquery result
Between...	Between two values
Like	Contains the same mode as the specified string. This mode will be compared with one or more wildcard strings

● Add the order by clause

Select * from student order by studentcode ASC

The results returned by this SQL statement are sorted in the ascending order of the studentcode column.

2. Insert statement syntax

 

Insert statement syntax

Components	Description
Insert	Indicates the table to which a row is to be added and the columns to be added.
Values	Fill value of each column in the list by name
Select	The Return Value of the SELECT statement is added to biaozho.

　　

● A simple insert statement:

Insert into student values ('123', 'lily', 'female, 'shanxi, '123'). In this way, a record is added to the table.

 

We can also specify the columns used for filling:

 

● A simple insert statement:

Insert into student (studentcode, studentname, sex) values ('20140901', 'Tom ', 'male ')

 

Using the methods in the above two examples, we can add content to the database. note that the value of values should be provided according to the column sequence specified in the insert into section. When the column list is omitted, the values list provides the values of each column in the column sequence defined in the table.

 

3. Update statement syntax

　　

Update statement syntax

Components	Description
Update	Name of the table to be updated
Set	Indicates the columns to be updated and the new values assigned to those columns.
From	The table to be processed by the update statement.
Where	The condition that the data to be updated must meet

　　

● Set the column to a definite value:

Update student set sex = 'male' where studentname = 'zhang san'

With this statement, we can change the value of the sex column of the record whose studentname is "Zhang San" in the database to "male ".

　　

4. Delete statement syntax

　

Delete statement syntax

Components	Description
Delete from	Name of the table to be deleted
Where	The condition that the row to be deleted meets

● Delete all values in a table: the execution result of the delete from student statement is to delete all rows in the student table. ● Delete the specified row in a table. The execution result of the delete from student where studentcode = '000000' statement is the end of the studencode 123 record in the table!