Mssql is Microsoft's SQL Server, running on windows 2000, 2003 and other platforms.
Mysql is an open source database server, which can run on windows platform and unix\\linux platform. The standard version is free, so you can visit www.mysql.com.
Asp\\php is just an explanatory language. It is not necessarily that mssql cannot use php, and mysql does not have to use php, but asp-mssql and php-mysql are common combinations.
SQL Database Complete Manual _ 1
SQL is the abbreviation of structured query language. SQL is a set of operation commands specially established for database, and it is a fully functional database language. When using it, you only need to issue the command of "what to do", and "how to do it" is not within the user's consideration. SQL is powerful, easy to learn and use, and has become the basis of database operation. Now almost all databases support SQL.
## 1 Second, the data architecture of SQL database
The data schema of SQL database is basically a * * * structure, but the terminology used is different from the traditional relational model terminology. In SQL, relational schema is called "base table"; The storage mode (internal mode) is called "storage file"; The sub-mode (outer mode) is called "view"; A tuple is called a row; This property is called a column. This name is as symmetrical as 00100009a:
## 1 III. Composition of SQL Language
Before learning the SQL language formally, let's have a basic understanding of the SQL language and introduce the composition of the SQL language:
1.SQL database is a collection of tables defined by one or more SQL schemas.
2.SQL table consists of a set of rows, one row is a series of columns, and each column corresponds to a data item.
3. A table can be a basic table or a view. A basic table is a table actually stored in a database, while a view is the definition of a table composed of several basic tables or other views.
4. A basic table can span one or more storage files, and a storage file can also store one or more basic tables. Each storage file corresponds to the last physical file stored externally.
5. Users can query views and basic tables with SQL statements. From the user's point of view, views and basic tables are the same, and there is no difference. It's all relationships (tables).
6.SQL users can be applications or end users. SQL statements can be embedded in programs of host languages, such as FORTRAN, COBOL, PASCAL, PL/I, C and Ada. In an interactive environment, SQL users can also be used as an independent user interface for end users.
## 1 Fourth, operate the database.
SQL includes all operations on the database, which is mainly composed of four parts:
1. Data definition: This part is also called "SQL DDL" and defines the logical structure of the database, including defining the database, basic tables, views and indexes.
2. Data operation: this part is also called "SQL DML", which includes two operations: data query and data update, among which data update includes three operations: insert, delete and update.
3. Data control: The control of users accessing data includes authorization of basic tables and views, description of integrity rules, transaction control statements, etc.
4. Rules for the use of embedded SQL language: rules for using SQL statements in programs in the host language.
Below we will introduce respectively:
##2 (1) data definition
SQL data definition functions include defining databases, basic tables, indexes and views.
First, let's learn about the basic data types provided by SQL: (for example, 00100009b).
Establishment and deletion of 1. database
(1) Establish a database: A database is a data set containing several basic tables, and its sentence format is:
Create database < database name > [other parameters]
Among them, it must be unique in the system and cannot be repeated, otherwise it will lead to data access errors. [Other parameters] vary depending on the specific database implementation system.
For example, build a project management database (xmmanage), and its statement should be:
Create database xmmanage
(2) Delete the database: delete the database and all its contents from the system.
Its statement format is: drop database.
Example: Delete the project management database (xmmanage) with the following statement:
Delete database xmmanage
2. Definition and change of basic table
A table that exists independently is called a basic table. In SQL language, a relationship corresponds to only one basic table. The definition of basic table refers to the establishment of basic relational schema, and the change refers to the deletion and modification of existing basic tables in the database.
(1) Definition of basic table: a basic table is a non-export relationship, and its definition involves table name, column name, data type, etc. Its statement format is:
Create table "< database name >. ?& lt； Table name >
(< column name > data type [default value] [not empty/empty]
(viii), < column name > data type [default value] [not empty/empty]] ......
[,UNIQUE (column name [,column name] ...)]
[,primary key].
[,foreign key (column name [,column name] ...) reference (list name [,list name] ....................................................................................................................................................................
[,check (condition)] [other parameters])
Among them,. > indicates that the newly established table will be stored in the database;
The newly created table consists of two parts: one part is the table and a set of column names, and the other part is the actually stored data (that is, the data can be directly stored in the table while defining the table);
Column names are user-defined and easy to understand, and spaces cannot be used in column names;
Data types are several standard data types introduced above;
[[〔NOT NULL/NULL〕] indicates whether the column is allowed to store null values. SQL language supports the concept of null value. Null values are "unknown" or "meaningless" values. It is worth noting that the data "0" and spaces are not null values, and the system generally allows null values by default, so when null values are not allowed, not null must be explicitly used;
[,UNIQUE arranges the columns in the specified order. If the sorting order is not specified, the columns are arranged in the defined order;
[Primary Key] is used to specify the primary key of the table (that is, the primary attribute in the relationship). Entity integrity constraint stipulates that the primary key must be unique and not empty;
[,foreign key (column name [,column name] ...) Reference (column name [,column name] ...) is used to specify the foreign key referential integrity constraint, and the foreign key specifies the related column as a foreign key, and its reference object is the specified column of another table, that is, the column in the imported appearance. If no external column name is specified, the system will use the same column name as the reference key by default. It should be noted that references must be used when using foreign keys, and foreign keys of other data refer to integrity constraints.
[,check] is used to check the data stored in the table under the specified conditions to determine its legitimacy and improve the security of the data.
For example, establish a student.
Create a table student // Create a basic table student.
(st_class CHAR(8),//defines the column ST _ classclass, and the data type is an 8-bit fixed-length string.
St_no CHAR( 10) NOT NULL,//defines the student number of St_no column, with the type of 10-bit fixed-length string, which is not empty.
St_namechar (8) is not empty.//Defines the name of column ST _ name, which is an 8-bit fixed-length string and is not empty.
St_sex CHAR(2),//defines the gender of column St_sex, and the type is a 2-bit fixed-length string.
St_age SMALLINT,//defines the age of column St_age, and the type is short integer.
Primary key (st_no))// Define st_no as the primary key.
Example: Establishing Course Setting Table (Subject)
Create a table theme//Create a basic table theme.
(su_no CHAR(4) NOT NULL,//defines the class number of column su_no, which is a 4-bit fixed-length string and is not empty.
Su _ subjectchar (20) is not empty,//defines the course name of column su_subject, which is a 20-bit fixed-length string and is not empty.
Su_credit INTEGER,//column that defines Su_credit credit, with the type of long integer.
Su_period INTEGER,//When defining column Su_period, the type is long integer.
Su_preno CHAR(4),//defines the number of su _ prenochar, which is a 4-bit fixed-length string.
PRIMARY KEY(su_no))// Define the su_no class number as the primary key.
Example: Establishing Student Course Selection Table (Score)
Create table score//Create basic table score.
(st_no CHAR( 10),//defines the student number of st_no column, and the type is 10-bit fixed-length string.
Su_no CHAR(4),//defines the class number of column Su_no, and the type is a 4-bit fixed-length string.
Sc_score integer NULL,//defines the column sc _ score, which is a long integer and can be null.
The foreign key (st_no) refers to the student.//The foreign key ST _ no is introduced from the table student to ensure the association and synchronization between this table and the table student.
The foreign key (suno) refers to the subject)// The foreign key su_no is introduced from the table subject to ensure the association and synchronization between this table and the table subject.
(2) Delete basic table: used to delete a basic table and all its contents from the database. Its sentence format is:
DROP TABLE」& lt； Database name >. ] table name
For example, delete all the tables established above.
Students, subjects, scores
(3) Modification of the basic table: After the basic table is established and used for a period of time, it may be necessary to modify the structure of the basic table according to actual needs, that is, add new attributes or delete attributes.
The format of the statement used to add an attribute is:
Change table "< database name >. ] table name addition
(< column name > data type [default value] [not empty/empty]
(viii), < column name > data type [default value] [non-empty/empty]] ......
[,UNIQUE (column name [,column name] ...)]
[,primary key].
[,foreign key (column name [,column name] ...) reference (list name [,list name] ....................................................................................................................................................................
[,check (condition)] [other parameters])
For example, add the birth DATE of the stborn column to the basic table student, where the data type is date and cannot be null.
Change Table Student Add (Date of Birth is not empty)
The statement format for deleting an attribute is:
Change table "< database name >. ] table name deletion
(< column name > data type [default value] [not empty/empty]
(viii), < column name > data type [default value] [not empty/empty]] ...)
For example, delete the column st_age in the basic table student.
Change table student deletion (st_age)
3. View definition and deletion
In SQL, view is the basic unit of external schema 1 level data structure. It is a table derived from one or several basic tables, and it is a "special table" for users by extracting several subsets from the existing basic tables. This construction method must be realized by using the SELECT statement in SQL. When defining a view, only the definition is stored in the data of the system, and the corresponding data of the view is not directly stored, and the corresponding data is not obtained until the user uses the view.
Definition of (1) view: Defining a view can be achieved by using the CREATE VIEW statement, the format of which is:
Create the view name as a SELECT statement.
To export a view from a base table:
Example: Export a view containing only female students from the basic table student.
Create the view WOMANVIEW as//Create the view WOMANVIEW.
SELECT st_class, st_no, st_name, st_age // Select the columns st_class, st_no, st_name, st_age to be displayed.
Introduce from students//from basic table students
Where st _ sex =' female'//The introduction condition is that the gender is' female'. Note that all character variables are enclosed in single quotation marks.
Export views from multiple basic tables:
For example, a view with only female students and a score higher than 60 comes from the basic tables student and Score.
CREATEVIEW WOMAN_SCORE AS // define the view WOMANSCORE.
SELECT student.st_class, student.st_no, student.st_name, student.st_age, score.sc_score // to selectively display related columns.
FROM student.score // Introduced from the basic tables Student and Score.
Among them, student.st _ sex =' female' and score.sc _ score & gt = 60 and student.st _ no = score.st _ no//Selection criteria: gender is "female", and the score is above 60. And use st_no to link the two tables.
If you apply this view in the future, just use the statement.
SELECT * FROM WOMAN_SCORE // where "*" is a wildcard, representing all elements.
(2) Delete View: used to delete views that are no longer used. Its statement format is as follows:
Delete view view name
Example: delete the WOMAN_SCORE view established above.
DROP VIEW woman _SCORE
4. Definition and deletion of indexes
Index belongs to the concept of physical storage, not logical concept. In SQL, the concept of index is abandoned and the concept of primary key is used directly. It is worth mentioning that some relational DBMS include both indexing mechanism and primary key mechanism. The primary key mechanism is recommended here because it takes up less system resources and is more efficient.
Definition of (1) index: the index is established on a basic table, and its statement format is:
Create a [unique] index name on.
で& lt； Database name > table name (column name [ASC/DESC] [,column name [ASC/DESC]]. ...
The reserved word UNIQUE here means that the index values in the basic table are not allowed to be repeated. If it is the default value, it means that the index values in the table are allowed to be repeated; DESC means sorting by index key in descending order, if default or ASC means sorting in ascending order.
Example: index st_no and st_age in the basic table student in ascending and descending order respectively, and the index values cannot be repeated.
Create a unique index STINDEX on // Create index STINDEX.
Student(st_no ASC, st_age DESC)// Index st_no and st_age in the student.
(2) Delete the index:
Delete index name
Example: Delete the index STINDEX established above.
Discard index STINDEX
##2 (2) Data query
SQL is a language with powerful query function. As long as the data exists in the database, it can always be found from the database by appropriate methods. There is only one query statement in SQL: SELECT, which can cooperate with other statements to complete all query functions. The complete syntax of a SELECT statement can have six clauses. The complete syntax is as follows:
Select the column name of the target table or a collection of column expressions.
From the base table or (and) view set
[[〔WHERE conditional expression]
[[Group by column name set]
[[Expression with group conditions]]
[[ORDER BY column name [set] ...]
The semantics of the whole statement are as follows: FROM the table listed in the from clause, SELECT tuples that meet the conditional expression given in the WHERE clause, then group them according to the values of the columns specified in the GROUPBY clause (grouping clause), then extract those groups that meet the group conditional expression in the HAVING clause, and evaluate the output according to the column name or column expression given in the select clause. ORDER clause (sorting clause) reorders the output target table, and ASC (ascending order) or DESC (descending order) can be attached.
Seeking adoption is a satisfactory answer.