关于Modify和嵌入式SQL的问题

谢谢你的帮助，这样写果然可以了，但是必须改掉modify吗？

alter table yourtable alter column colnmae 新的类型

修改列是用
alter table tbname alter column columnname xxx 的再仔细看看alter的语法
语法
ALTER TABLE table
{ [ ALTER COLUMN column_name
    { new_data_type [ ( precision [ , scale ] ) ]
        [ COLLATE < collation_name > ]
        [ NULL | NOT NULL ]
        | {ADD | DROP } ROWGUIDCOL }
    ]
    | ADD
        { [ < column_definition > ]
        | column_name AS computed_column_expression
        } [ ,...n ]
    | [ WITH CHECK | WITH NOCHECK ] ADD
        { < table_constraint > } [ ,...n ]
    | DROP
        { [ CONSTRAINT ] constraint_name
            | COLUMN column } [ ,...n ]
    | { CHECK | NOCHECK } CONSTRAINT
        { ALL | constraint_name [ ,...n ] }
    | { ENABLE | DISABLE } TRIGGER
        { ALL | trigger_name [ ,...n ] }
}< column_definition > ::=
    { column_name data_type }
    [ [ DEFAULT constant_expression ] [ WITH VALUES ]
    | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
        ]
    [ ROWGUIDCOL ]
    [ COLLATE < collation_name > ]
    [ < column_constraint > ] [ ...n ]< column_constraint > ::=
    [ CONSTRAINT constraint_name ]
    { [ NULL | NOT NULL ]
        | [ { PRIMARY KEY | UNIQUE }
            [ CLUSTERED | NONCLUSTERED ]
            [ WITH FILLFACTOR = fillfactor ]
            [ ON { filegroup | DEFAULT } ]
            ]
        | [ [ FOREIGN KEY ]
            REFERENCES ref_table [ ( ref_column ) ]
            [ ON DELETE { CASCADE | NO ACTION } ]
            [ ON UPDATE { CASCADE | NO ACTION } ]
            [ NOT FOR REPLICATION ]
            ]
        | CHECK [ NOT FOR REPLICATION ]
            ( logical_expression )
    }< table_constraint > ::=
    [ CONSTRAINT constraint_name ]
    { [ { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        { ( column [ ,...n ] ) }
        [ WITH FILLFACTOR = fillfactor ]
        [ ON { filegroup | DEFAULT } ]
        ]
        |    FOREIGN KEY
            [ ( column [ ,...n ] ) ]
            REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
            [ ON DELETE { CASCADE | NO ACTION } ]
            [ ON UPDATE { CASCADE | NO ACTION } ]
            [ NOT FOR REPLICATION ]
        | DEFAULT constant_expression
            [ FOR column ] [ WITH VALUES ]
        |    CHECK [ NOT FOR REPLICATION ]
            ( search_conditions )
    }

参考
c语言中嵌入式SQL是的使用exapmlemain()
{
exec sql include sqlca
exec sql begin declare section
  char prname[20];
  char productno[8];exec sql end declare sectionprintf("Input producno:");
scanf("%s",&producno);exec sql select prname
         from product
         where productno=:productno
         into :prname;printf("Productname: %s\n",prname);exit();}  注意sql语句中调用c的变量前要加:
用exec sql来表示将执行sql语句你在SQL 2000的帮助里找ESQL/C就可以找到。
有例程
我贴一点给你
int main(int argc, char *argv[])
    {
    LOGINREC*   login;      // login rec pointer
    DBPROCESS*  dbproc;     // SQL Server connection structure pointer    char        cmd[150];   // command buffer
    char        server[30]; // server name buffer
    int         x = 1;      // command line counter
    STATUS      retc;       // return code    const char* sqlversion; // pointer for version string    *server = '\0';         // null start these two buffers
    *cmd = '\0';    if(argc == 1)           // if no server name, request it
        {
    printf("Enter Server Name: ");
    gets(server);
        }
    else                    // else it was input as first arg
       strcpy(server,argv[1]);    if(argc < 2)            // if no login id, request it
        {
        printf("Enter User Name: ");
        gets(cmd);
        }
    else                    // otherwise it was input as second arg.
        strcpy(cmd,argv[2]);    // check to see if communications layer was loaded (DOS ONLY)
    if((sqlversion = dbinit()) == (BYTE *)NULL)
        {
        // DOS TSR (DBNMPIPE.EXE) is not loaded, don't bother going any farther
        printf("Error in DB-Library initialization, exiting\n");
        return 1;
        }
    else
        printf("DB-Library version: %s\n",sqlversion);  // print dblib version    dbsettime(30);          // set timeouts to 30 seconds

    // set error/msg handlers for this program
    dbmsghandle((DBMSGHANDLE_PROC)msg_handler);
    dberrhandle((DBERRHANDLE_PROC)err_handler);    login = dblogin();      // get a login rec    DBSETLUSER(login,cmd);  // set login id
    DBSETLHOST(login,"SQL EXAMPLE");    // set host name for sp_who
    DBSETLVERSION(login, DBVER60);
    // To use secure, or trusted, connection, uncomment the following line.
    // DBSETLSECURE (login);    // open connection to requested server.  Pass null server name for local
    // connection, if name not entered.
    if((dbproc = dbopen(login,(*server) ? server : (char *)NULL)) == (DBPROCESS *)NULL)
        {
        // no one answered, so couldn't connect or error occurred
        printf("Login failed\n");
        return 1;
        }
    else
        {
        // loop on command input until quit or exit appears in first 4 bytes.
        while((strnicmp(cmd,"quit",4) != 0) && (strnicmp(cmd,"exit",4)!=0))
            {
            printf("%d> ", x++);                // print command prompt
            gets(cmd);                          // get command
            if(strnicmp(cmd,"go",2) == 0)       // is it go
                {
                if(dbsqlexec(dbproc) == FAIL)   // execute command
                    {
                    // problem occurred, just try another command
                    printf("Error in executing command batch!\n");
                    x = 1;
                    continue;
                    }
                // command executed correctly, get results information
                while((retc = dbresults(dbproc)) != NO_MORE_RESULTS)
                    {
                    if (retc == FAIL)           // if error get out of loop
                        break;                    // headers and data could be printed here with only two
                    // function calls, dbprhead(dbproc), and dbprrow(dbproc),
                    // which would output the headers, and all the data to
                    // standard output.  However, that isn't very informative
                    // toward understanding how this data is obtained and
                    // processed, so I do it the hard way, one column at a time.                    PrintHeaders(dbproc);       // print header data                    // loop on each row, until all read
                    while((retc= dbnextrow(dbproc))!=NO_MORE_ROWS)
                        {
                        if(retc == FAIL)        // if fail, then clear
                            {                   // connection completely, just
                            dbcancel(dbproc);   // in case.
                            break;
                            }
                        else
                            PrintRow(dbproc);   // else print the current row
                        }                    if (DBCOUNT(dbproc) == 1L)  // print the row count
                        printf("(1 row effected)\n");
                    else
                        printf("(%ld rows effected)\n",DBCOUNT(dbproc));                    }                           // end while(dbresults())                x = 1;                      // reset command line counter
                }
            else
                {
                strcat(cmd," ");            // go not detected, so put space
                dbcmd(dbproc,cmd);          // between each command and set in
                }                           // dbproc.            } // end while()        dbclose(dbproc);                // quit/exit input, close connection        // print adios and exit.
        printf("SQL Server Connection to %s closed, bye bye.\n",server);
        return 0;
        }
    }/*

a good article in MSDN:
Setting Up the nsqlprep Precompiler
Before you run the nsqlprep precompiler, do the following:The nsqlprep precompiler uses your compiler to process header files Set the INCLUDE environment variable to include the full path where the Sqlca.h and Sqlda.h ESQL/C header files are located and set the LIB environment variable to include the full path where the library files are located. There are several ways to accomplish this, including either:
Issuing a SET statement at the command prompt, such as
SET INCLUDE = C\Mssql17\DevTools\INCLUSE; %include%SET LIB = C\Mssql17\DevTools\LIB; %LIB%At the command prompt, first running Vcvar32.bat (in the \Program Files\Microsoft Visual Studio\VC98\Bin directory) and then running setenv.bat (in \Mssql17\DevTools\Samples\Esqlc directory).
The nsqlprep precompiler automatically includes these header files in the C programs it creates. Do not explicitly include them (by using #include) in an Embedded SQL program. To enable communication with Microsoft® SQL Server™ 2000, ensure that an appropriate Net-Library is loaded or available on the path when precompiling with the /DB and /PASS options. For example, the Named Pipes Net-Library for the Microsoft Windows NT® operating system (Intel® platform) is Dbnmpntw.dll, and the Named Pipes Net-Library for the Microsoft MS-DOS® operating system is the Dbnmpipe.exe TSR.

ALTER TABLE Student drop not null(scome);
这句怎么改呢？

alter table yourtable alter column colnmae 新的类型
具体看联机帮助.

--加
ALTER TABLE table2 ADD row_id bigint--删
ALTER TABLE table2 DROP COLUMN row_id--改
ALTER TABLE 你的表 ALTER COLUMN 列名你的类型 null

麻烦各位，我是想删除学生姓名必须取唯一值的约束，不是删掉该列也不是更改类型怎么写？书中是
alter table student drop unique（sname）；
用drop在SQL Server2000中怎么改呢？

alter table student drop constraint 约束名

你必须知道该唯一约束的约束名，然后使用语句：
alter table student drop constraint 约束名关于嵌入式SQL的使用：
先摘录我编著的《数据库原理》一书中的章节：前面第二章介绍SQL语句时，都是作为独立的数据语言，以交互的方式使用的。而实际开发应用系统时，为了缩短开发周期、美化用户界面，应用系统的开发常常借助于面向对象的前台开发工具，如目前流行的Visual Basic、PowerBuilder、Delphi、Visual C＋＋等，他们使用的是某种高级语言（例如：Basic、PbScript、PASCAL、 C等）。但是，当需要在程序中完成对后台数据库的处理时，又使用了能高效处理数据的SQL语言（包括后台DBMS的某些函数）。这种方式下使用的SQL语言称为嵌入式SQL（Embedded SQL），其中传统的高级语言称为宿主语言（或主语言）。
由于SQL是非过程的、面向集合的数据操纵语言，它大部分语句的使用都是独立的，与上下文条件无关的。在事务处理中，常常需有流程控制，即需要程序根据不同的条件执行不同的任务，如果单单使用SQL语言，很难实现这类应用。另一方面，高级语言在涉及数据库操作时，不能高效地进行数据的存取。所以，嵌入式SQL的使用，结合了高级语言的过程性和SQL语言的数据操纵能力，可提高数据库应用程序的效率。
......在交互式和嵌入式两种不同的使用方式下，SQL语言的语法结构基本相同。
各个DBMS在实现嵌入式SQL时，对不同的宿主语言，所用的方法基本上是相同的。但由于宿主语言的差异，在实现时也各有特点。
在图7-5中，预编译器不能够检查宿主语言的语法合法性，它所能做的是查找表示“嵌入式SQL从这里开始”或“嵌入式SQL在这里结束”的信号。所以嵌入式SQL一般都具有一个前缀和一个结束符。一般的前缀是EXEC SQL，结束符是分号一般地，对嵌入的SQL语句加前缀EXEC SQL，而结束标志则随宿主语言的不同而不同：
Ada： EXEC SQL┅；
C : EXEC SQL┅；
COBOL: EXEC SQL┅ END EXEC
FORTRAN: EXEC SQL┅<no end>(即，没有显式结束符)
MUMPS: &SQL(┅)
PASCAL: EXEC SQL┅；
PL/I: EXEC SQL┅；

例如：删除学生姓名必须取唯一值的约束
这个语句怎么写？在表student中，学生姓名是sname。约束unique，用你们所说的alter table student drop constraint 约束名，怎么写？

alter table student drop constraint 约束名如果约束名不知道的话select name from sysobjects where id in
(
select constid from
sysconstraints a join sysobjects b on a.id=b.id
and b.name='tablename '
)

调试易

关于Modify和嵌入式SQL的问题

解决方案 »