Introduction to SQL

SQL is the standard computer language for accessing and working with databases.

What is SQL?

• SQL refers to Structured query language
• SQL gives us the ability to access a database
• SQL is an ANSI standard computer language

Editor's note: ANSI, national standardization organization of the United States

What can SQL do?

• SQL database-oriented query execution
• SQL can retrieve data from the database
• SQL can insert new records in the database
• SQL can update data in a database
• SQL to delete records from the database
• SQL to create a new database
• SQL to create a new table in the database
• SQL can create stored procedures in the database
• SQL to create views in the database
• SQL can set permissions for tables, stored procedures, and views

SQL is a standard-but ...

SQL is an ANSI standard computer language used to access and manipulate database systems. SQL statements are used to retrieve and update data in the database. SQL can work with database programs, such as Ms Access, DB2, Informix, MS SQL Server, Oracle, Sybase, and other database systems.

Unfortunately, there are many different versions of the SQL language, but to be compatible with ANSI standards, they must collectively support some key keywords (such as SELECT, UPDATE, DELETE, INSERT, WHERE, and so on) in a similar way.

Note: In addition to the SQL standard, most SQL database programs have their own private extensions!

Using SQL in your site

To create a Web site that publishes data from a database, you need the following elements:

• RDBMS database programs (such as MS Access, SQL Server, MySQL)
• Server-side scripting language (e.g. PHP or ASP)
• Sql
• Html/css

Rdbms

RDBMS refers to a relational database management system.

The RDBMS is the foundation of SQL and is also the foundation of all modern database systems, such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

Data in an RDBMS is stored in a database object called a table (tables).

A table is a collection of related data items that consists of columns and rows.

Database tables

A database typically contains one or more tables. Each table is identified by a name (for example, "customer" or "order"). The table contains records (rows) with data.

The following example is a table named "Persons":

City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

The table above contains three records (one per person) and five columns (Id, last name, first name, address, and city).

SQL statements

Most of the work you need to do on the database is done by SQL statements.

The following statement selects the data for the LastName column from the table:

SELECT LastName from Persons

The result set looks like this:

LastName
Adams
Bush
Carter

In this tutorial, we'll walk you through a variety of different SQL statements.

Important issues

Be sure to remember thatSQL is not case sensitive !

Semicolon after the SQL statement?

Some database systems require semicolons to be used at the end of each SQL command. Semicolons are not used in our tutorials.

Semicolons are the standard way to separate each SQL statement in a database system so that more than one statement can be executed in the same request to the server.

If you are using MS Access and SQL Server 2000, you do not have to use semicolons after each SQL statement, although some database software requirements must use semicolons.

SQL DML and DDL can divide SQL into two parts: Data manipulation language (DML data manipulation Language) and data definition language (DDL data definition Language Language

SQL (Structured Query language) is the syntax for executing queries. However, the SQL language also contains syntax for updating, inserting, and deleting records.

1, DDL 1-1, DDL overview DDL (data definition Language) is used to manipulate the properties of objects and objects, including the database itself, as well as database objects, such as tables, views, and so on, which DDL The management and definition of objects and attributes are shown on the Create, drop, and alter. Special note: The concept of "object" for DDL operations, "Objects" includes the properties of objects and objects, and the smallest objects are more hierarchical than records. For example: Create creates a data table, alter can change the field of the table, drop can delete the table, from here we can see the height of the DDL station, he will not operate on the specific data.

1-2. DDL main Statement (operation)

Create statement: Some objects of the database and database can be created.               Drop statement: You can delete data tables, indexes, triggers, conditional constraints, and permissions for data tables. Alter statement: Modifies the data table definition and properties.

1-3. DDL Operations Objects (table)

The creation of the concept table of 1-3-1 and table is used for storing data, because of the data we store, we need to define some data types to facilitate management.

1-3-2, Table Properties

Primary KEY properties: The primary key is the PRIMARY KEY constraint, but the name of the primary key is different from the virtual (that is, the description of the event), the PRIMARY KEY constraint name is biased to real (that is, the implementation of the description operation), the same thing is described, the PRIMARY KEY constraint is a property in a table, can have a maximum of one primary key A primary key can be defined in one or more fields; A primary key causes one or more fields to be unique and not NULL, so that a record can be represented by the field or by the value in that group field.

Unique attribute: Only one primary key attribute is allowed in a table, and a unique constraint is proposed for the user of the party table; a unique constraint can be defined on one or more fields; a unique constraint makes the field or the value in that Group field unique and nullable, but cannot be duplicated.

Foreign key attribute: Also called foreign key, also called FOREIGN KEY constraint, the relationship between the primary key and the PRIMARY KEY constraint is the same, the foreign KEY constraint is for the two tables, and if Table A's primary key is a field in table B, then the field is called Table B's foreign key, table A is called the primary table, table B is called from the table, but be aware that you must be aware of this relationship.
Verification, NULL, and Default properties: The verification attribute is also called the verification constraint, the null attribute is also called the null constraint, and the default attribute is also called the default constraint; These names describe one thing, describe a situation, this thing or this case we can certainly do it deliberately (input data is the attention on the line), but, Their intention was to automate, that is, to have the computer do it. (Do you know why indexes are automatically created when you create a primary key and a unique constraint?) and is the only index, think of the index is mostly used in those fields, as well as the role of the index will know. Such operations as primary KEY constraints, unique constraints, non-null constraints, FOREIGN KEY constraints, verification constraints, and default constraints are all attributes of the table, so here I think they are all properties of the table. ）

2, DML 2-1, DML overview DML (data manipulation Language) is used to manipulate the data contained in database objects, that is, the units of operations are records.               2-2. DML's main statement (ACTION) INSERT statement: Inserts a record into the data sheet.   Delete statement: Deletes one or more records from a data table, or deletes all records in the datasheet, but its operands are still records. UPDATE statement: Used to modify the contents of a record in an existing table.

2-3. DML operation Object--record

2-3-1, note When we insert, delete, and update records, it is important to be aware that some of the DDL's operations are important.

The action of the DCL (Data Control Language) is the permission of the database object, and the determination of these operations makes the data more secure.

Main statement of the DCL (operation)

Grant statement: Allows the creator of an object to give certain permissions to a user or to a group or all users (public).

Revoke statement: You can revoke access rights for a user or group or all users

Action object for DCL (user)

The user at this point refers to the database user.

The query and update Directives form the DML portion of SQL:

• SELECT -get data from a database table
• Update-updating data in a database table
• Delete-deletes data from the database table
• INSERT INTO-inserts data into a database table

The Data definition language (DDL) portion of SQL gives us the ability to create or delete tables. We can also define indexes (keys), specify links between tables, and impose constraints between tables.

The most important DDL statement in SQL:

• CREATE database-creating new databases
• alter database-Modify Databases
• CREATE TABLE-Creates a new table
• ALTER TABLE -Change (change) database table
• Drop Table-delete tables
• CREATE index-Creating indexes (search key)
• Drop Index-delete indexes

Introduction to SQL