What is the difference between access and SQL syntax

Suppose the table game has a field of Gameyuijian for bit fields (SQL SERVER 20005) and yes/No fields (ACCSS databases), which are executed correctly when writing script files

SQL strcmd = "SELECT Top * from game Where Gametuijian = 1 ORDER by Id Desc";

OLE DB strcmd = "SELECT Top * from game Where Gametuijian = True with Id Desc";

In the processing of the time:

SQL Server Statement "SELECT * from Tt1 Where time1> ' 2009-12-01 '"

Access statement "Select * from Tt1 Where time1> #2009 -12-01#" can also be used with the above statement

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

Since there is no "AutoNumber" in the SQL2000, your "AutoNumber" fields will become Non-empty fields, which must be manually modified and labeled "Yes", "1", and "1" for the increment, and

2, in addition, ACCESS2000 converted to SQL2000, the original property is "Yes/no" field will be converted to a non-empty "bit", at this time you have to modify the properties you want;

Some experience in access to SQL Server

Automatic numbering type in 1.ACCESS database when converting, SQL Server does not set it to AutoNumber, we need to add identity to the SQL creation statement to indicate AutoNumber! 2. When converting, date-related fields, SQL Server defaults to smalldatetime type, we'd better turn it into a datetime, because the range of datetime is larger than the smalldatetime type. I met this situation, with the smalldatetime type, conversion failure, but with the DateTime type, conversion success.

3. The SQL statements that operate on the two databases are not identical, for example, when a record is deleted for an Access database: "Delete * from user where id=10", and the SQL Server database is deleted by: "Delete user Where id=10 ".

4. Date functions are not the same, in the Access database processing, you can use the date (), time () and other functions, but the

In SQL Server database processing, you can use functions such as Datediff,dateadd instead of date (), Time (), and so on.

5. In Access database processing, SQL statements can be directly used in some VB functions, such as the CStr () function, but for SQL Server database processing, but not used.

1, you must first install Microsoft Office Access 2003, and SQL Server2000. 2, the old dynamic network database backup, when the backup is complete, open the old database with Access 2003, and a warning appears on the open, ignoring it (security warning), pressing the open key, and pressing the toolbar--database utility--transform database--to 2002-2003 format, Convert the database into a 2003 format.

2, after the conversion is completed and then opened with Access 2003, press the toolbar--database utility--Upsizing Wizard--New database--fill in the SQL database login name, password and the new Dynamic network database (ready to convert to a new dynamic network database), press Next, press "" "key, then press Next, Select all options, and then press next to choose "Do not make any changes to the application" and press finish.

3, open SQL Enterprise Manager--database blowing the reef Zhan Ǖ mu onto tax Sang chiselled 鍪 and a slow hotel--笤, eh? Takezo Script--general--Show all--Write all object scripts--OK (remember where they are stored).

4, with Notepad to open just generated SQL script, in the formula bar--replace--find content for "smalldatetime" replaced with "datetime"--replace all, and then in the formula bar-replace-find content for "nvarchar" replaced by "Varcha"- -Replace all, save exit after completion.

5, open SQL Enterprise Manager--database--click on this database to create a new dynamic network database, and then in the Toolbar--sql Query Analyzer--FILE--open--"just generated SQL script"--query--execution, and then close the window.

6, back to SQL Enterprise Manager-database-click on this database to create a new dynamic network database, and then open the toolbar--Database Transformation Services--import data--next--Data source "Microsoft Access" file name "for the old dynamic network database"-- Next--and next--copy the tables and views from the source data--next--Select all--next--run immediately--next--complete.

7, modify the dynamic network folder two files conn.asp and incconst.asp.

SQL is the abbreviation for Structured quevy Language (Structured Query language). SQL is a set of operational commands designed for databases and is a full-featured database language. When you use it, you only need to issue "what to Do" command, "How to Do" is not considered by users. SQL is powerful, easy to learn, easy to use, and has become the basis for database operations, and now almost all databases support SQL. <br>

# #1 second, SQL database data Architecture <br>

The data architecture of the SQL database is basically a level three structure, but the terminology is different from the traditional relational model terminology. In SQL, relational schemas (schemas) are called basic tables (base table); The storage mode (inside mode) is called "Storage File" (stored file), and the Child Mode (outer mode) is called view, the tuple is called row, and the property is called column ( column). Name symmetry such as ^00100009a^: <br>

# #1 Three, the composition of the SQL language <br>

Before formally learning the SQL language, let's start with a basic understanding of the SQL language, introducing the composition of the SQL language: <br>

1. An SQL database is a collection of tables (table) that is defined by one or more SQL schemas. <br>

2. An SQL table is composed of a rowset, one row is a sequence of columns (a collection), and each column corresponds to a row of data items. <br>

3. A table is either a basic table or a view. A basic table is a table that is actually stored in a database, and a view is the definition of a table consisting of several basic tables or other views. <br>

4. A basic table can span one or more storage files, and one storage file can hold one or more basic tables. Each storage file corresponds to a physical file on the external store. <br>

5. Users can use SQL statements to view and basic tables to query and other operations. In the user's view, the view and basic table are the same, no difference, are relations (table). <br>

6.SQL users can be either an application or an end user. SQL statements can be embedded in host language programs, with host languages such as Fortran,cobol,pascal,pl/i,c and Ada languages. SQL users can also act as independent user interfaces for use by end-users in interactive environments. <br>

# #1 Four, operation of the database <br>

SQL includes all operations on the database, consisting mainly of 4 parts: <br>

1. Data definition: This section, also known as "SQL DDL", defines the logical structure of the database, including the definition of the database, basic tables, views, and 4 parts of the index. <br>

2. Data manipulation: This section is also known as "SQL DML", which includes two broad categories of data query and data update, with data updates including inserts, deletes, and updates of three operations. <br>

3. Data control: The control of user access data has basic table and view authorization, integrity rules description, transaction control statements and so on. <br>

4. Use of Embedded SQL language: rules that specify the use of SQL statements in host language programs. <br>

Below we will introduce separately: <br>

# #2 (i) Data definition <br>

SQL data definition features include defining databases, basic tables, indexes, and views. <br>

First, let's take a look at the basic data types that SQL provides: (e.g. ^00100009b^) <br>

1. Establishment and deletion of database <br>

(1) Establish a database: A database is a dataset that includes a number of basic tables, and its statement format is: <br>

CREATE Database < database name >(other parameters)<br>

Where the,< database name > must be unique in the system, can not be duplicated, otherwise it will lead to data access errors. (other parameters) vary depending on the database implementation system. <br>

For example: to establish a project Management database (Xmmanage), the statement should be: <br>

CREATE DATABASE xmmanage <br>

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

The statement format is: DROP database < database name > <br>

Example: Delete the project Management database (Xmmanage) with the statement: <br>

DROP DATABASE xmmanage <br>

2. Definition and change of basic table <br>

Tables that exist independently of themselves are called basic tables, and a relationship uniquely corresponds to a basic table in the SQL language. The definition of a basic table refers to the establishment of the basic relational schema, while the change refers to the deletion and modification of the basic tables that already exist 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$] ')