What are the differences between access and SQL syntax?

What are the differences between access and SQL syntax?

Assume that the following fields in table game are bit fields (SQL SERVER 20005) and "yes/no" fields (ACCSS database ).

SQL strCmd = "Select Top 14 * From game Where gameTuiJian = 1 Order By Id Desc ";

OleDb strCmd = "Select Top 14 * From game Where gameTuiJian = true Order By Id Desc ";

Processing at time:

SQL server statement "Select * From tt1 Where time1> '2017-12-01 '"

The Access statement "Select * From tt1 Where time1> #2009-12-01 #" can also be used

SQL Server statement "Select * From tt1 Where DateDiff (day, time1, getdate ())

Access statement "Select * From tt1 Where DateDiff ('D', time1, now)

Access and SQL SERVER database Conversion

Because SQL2000 does not contain "auto number", all fields you set with "auto number" will become non-empty fields, which must be manually modified, and select "yes" for his "label", the seed is "1", and the increment is "1 ",

2. In addition, after ACCESS2000 is converted to SQL2000, the field with the original attribute "yes/no" will be converted to a non-empty "bit ", at this time, you must modify it to the desired attribute;

Experience in converting ACCESS to SQL SERVER

1. When the automatic numbering type in the ACCESS database is converted, SQL server does not set it to the automatic numbering type. We need to add the identity in the SQL creation statement to indicate the automatic numbering! 2. During conversion, SQL SERVER defaults to the smalldatetime type for date-related fields. We recommend that you change it to the datetime type because the datetime type has a larger range than the smalldatetime type. In this case, if the smalldatetime type is used, the conversion fails. If the datetime type is used, the conversion is successful.

3. the SQL statements used to operate these two databases are not all the same. For example, when you delete a record in an ACCESS database, use: "delete * from user where id = 10 ", to delete an SQL SERVER database, use: "delete user where id = 10 ".

4. date functions are different. In the ACCESS database processing, functions such as date () and time () can be used,

In SQL SERVER database processing, only functions such as datediff and dateadd can be used, instead of functions such as date () and time.

5. For ACCESS database processing, some VB functions can be used in SQL statements, such as cstr () functions, but not in SQL SERVER database processing.

1. You must first install Microsoft Office Access 2003 and SQL Server. 2. Back up the old dynamic network database. After the backup is complete, use Access 2003 to open the old dynamic network database. A warning will appear when it is opened, so ignore it (security warning ), press the open key and press the toolbar> database utility> convert database to 2002-2003 format to convert the database to 2003 format.

2. After the conversion is complete, use Access 2003, go to the toolbar, choose database utility> --> sqlsql--> enter the sqldatabase logon name, password, and the database to be created (to be converted to a new database), and click Next, press the ">" key, then press next, select all options, then press next, select "do not make any changes to the application", and then press finish.

3. Open the SQL Enterprise Manager-The database blows the reef. Zhan Zheng, Lu, Mu, Hong Kong, and Hong Kong, and he has been very busy. Why? /FONT> SQL script -- General -- display all -- write all object scripts -- OK (remember the storage location ).

4. Use notepad to open the generated SQL script. In the editing bar -- replace -- Replace "smalldatetime" with "datetime" -- replace all; then, in the edit column -- replace -- Replace "nvarchar" with "varcha" -- replace all. Save and exit.

5. Open SQL Enterprise Manager-database-click the database to create a new dynamic network database, then, go to the toolbar -- SQL query analyzer -- file -- open -- "generated SQL script" -- Query -- run, and close the window.

6. Return to SQL Enterprise Manager-database-click the database to create a new dynamic network database, then open the toolbar -- database conversion service -- import data -- next -- Data Source "Microsoft Access" file name "is the old dynamic network database" -- next -- copy the table and view from the source data -- next -- select all -- next -- run now -- next -- complete.

7. Modify the two files conn. asp and inc \ const. asp in the dynamic network folder.

SQL is the abbreviation of Structured Quevy Language (Structured Query Language. SQL is a set of operation commands for databases. It is a fully functional database language. When using it, you only need to issue the "what to do" command, and "how to do" is unnecessary for the user to consider. SQL is powerful, easy to learn, and easy to use. It has become the foundation for database operations, and almost all databases now support SQL. <Br>

#1 II. SQL database data architecture <br>

The data architecture of SQL database is basically a three-level structure, but the terms used are different from those of traditional relational models. In SQL, the relational mode (mode) is called the "base table", the storage mode (internal mode) is called the "storage file" (stored file), and the Sub mode (external mode) it is called "view"; tuples are called "row"; attributes are called "columns ). The name is symmetric, for example, ^ 0020.9a ^: <br>

#1 III. Composition of the SQL language <br>

Before learning the SQL language, let's first have a basic understanding of the SQL language and introduce the composition of the SQL language: <br>

1. a SQL database is a set of tables defined by one or more SQL modes. <Br>

2. An SQL table consists of row sets. A row is a sequence of columns (SET), and each column corresponds to a data item of the row. <Br>

3. A table, a basic table, or a view. A basic table is a table actually stored in a database, and a view is a definition of a table composed of several basic tables or other views. <Br>

4. A basic table can store one or more basic tables across one or more storage files. Each storage file corresponds to a physical file on the external storage. <Br>

5. You can use SQL statements to query views and basic tables. From the user's perspective, the view is the same as the basic table, and there is no difference between them. They are all Relational Tables ). <Br>

6. SQL users can be applications or end users. SQL statements can be embedded in programs in the host language. The host languages include FORTRAN, COBOL, PASCAL, PL/I, C, and Ada. SQL users can also act as independent user interfaces for end users in the interactive environment. <Br>

#1 4. operate databases <br>

SQL includes all database operations and consists of four parts: <br>

1. Data Definition: This part is also called "SQL DDL". It defines the logical structure of the database, including the database, basic table, view, and index. <Br>

2. Data manipulation: This part is also called "SQL DML", which includes two types of operations: Data Query and data update. Data update includes insert, delete, and update operations. <Br>

3. Data Control: the control of user access data includes authorization of basic tables and views, description of integrity rules, and transaction control statements. <Br>

4. Embedded SQL language usage rules: rules for using SQL statements in programs in the host language. <Br>

Next we will introduce them separately: <br>

#2 (1) Data Definition <br>

The SQL data definition function includes defining databases, basic tables, indexes, and views. <Br>

First, let's take a look at the basic data types provided by SQL: (for example, ^ 0000009b ^) <br>

1. database creation and deletion <br>

(1) Create a database: the database is a dataset that contains multiple basic tables. The statement format is: <br>

Create database <DATABASE Name> [other parameters] <br>

The <Database Name> must be unique in the system and cannot be repeated. Otherwise, data access errors may occur. [Other parameters] vary depending on the database implementation system. <Br>

For example, to create a project management database (xmmanage), the statement should be: <br>

Create database xmmanage <br>

(2) Delete A Database: Delete the database and all its contents from the system. <Br>

Statement format: drop database <DATABASE Name> <br>

For example, to delete the project management database (xmmanage), the statement should be: <br>

Drop database xmmanage <br>

2. Definition and change of basic tables <br>

A table that exists independently is called a basic table. In SQL, a relation uniquely corresponds to a basic table. The definition of a basic table refers to the establishment of a basic relationship mode, while a change refers to the deletion and modification of existing basic tables in the database. <Br>

Insert into mobile SELECT mobileID, ''' + mobilephone from openrowset ('Microsoft. jet. OLEDB.4.0 ', 'excel 8.0; Database = D: \ Mobile.xls', 'select * FROM [Sheet1 $] ')