美文网首页
SQL基本操作语句

SQL基本操作语句

作者: __小波__ | 来源:发表于2019-05-31 09:24 被阅读0次

    新建表:

    create table [表名]

    (

    [自动编号字段] int IDENTITY (1,1) PRIMARY KEY ,

    [字段1] nVarChar(50) default \'默认值\' null ,

    [字段2] ntext null ,

    [字段3] datetime,

    [字段4] money null ,

    [字段5] int default 0,

    [字段6] Decimal (12,4) default 0,

    [字段7] image null ,

    )

    删除表:

    Drop table [表名]

    插入数据:

    INSERT INTO [表名] (字段1,字段2) VALUES (100,\'51WINDOWS.NET\')

    删除数据:

    DELETE FROM [表名] WHERE [字段名]>100

    更新数据:

    UPDATE [表名] SET [字段1] = 200,[字段2] = \'51WINDOWS.NET\' WHERE [字段三] = \'HAIWA\'

    新增字段:

    ALTER TABLE [表名] ADD [字段名] NVARCHAR (50) NULL

    删除字段:

    ALTER TABLE [表名] DROP COLUMN [字段名]

    修改字段:

    ALTER TABLE [表名] ALTER COLUMN [字段名] NVARCHAR (50) NULL

    重命名表:(Access 重命名表,请参考文章:在Access数据库中重命名表)

    sp_rename \'表名\', \'新表名\', \'OBJECT\'

    新建约束:

    ALTER TABLE [表名] ADD CONSTRAINT 约束名 CHECK ([约束字段] <= \'2000-1-1\')

    删除约束:

    ALTER TABLE [表名] DROP CONSTRAINT 约束名

    新建默认值

    ALTER TABLE [表名] ADD CONSTRAINT 默认值名 DEFAULT \'51WINDOWS.NET\' FOR [字段名]

    删除默认值

    ALTER TABLE [表名] DROP CONSTRAINT 默认值名

    删除Sql Server 中的日志,减小数据库文件大小

    dump transaction 数据库名 with no_log

    backup log 数据库名 with no_log

    dbcc shrinkdatabase(数据库名)

    exec sp_dboption \'数据库名\', \'autoshrink\', \'true\'

    \\\'添加字段通用函数

    Sub AddColumn(TableName,ColumnName,ColumnType)

    Conn.Execute(\"Alter Table \"&TableName&\" Add \"&ColumnName&\" \"&ColumnType&\"\")

    End Sub

    \\\'更改字段通用函数

    Sub ModColumn(TableName,ColumnName,ColumnType)

    Conn.Execute(\"Alter Table \"&TableName&\" Alter Column \"&ColumnName&\" \"&ColumnType&\"\")

    End Sub

    \\\'检查表是否存在

    sql=\"select count(*) as dida from sysobjects where id = object_id(N\'[所有者].[表名]\') and OBJECTPROPERTY(id, N\'IsUserTable\') = 1\"

    set rs=conn.execute(sql)

    response.write rs(\"dida\")\'返回一个数值,0代表没有,1代表存在

    判断表的存在:

    select * from sysobjects where id = object_id(N\'[dbo].[tablename]\') and OBJECTPROPERTY(id, N\'IsUserTable\') = 1

    某个表的结构

    select * from syscolumns where id = object_id(N\'[dbo].[你的表名]\') and OBJECTPROPERTY(id, N\'IsUserTable\') = 1

    create table student(

    Sno int not null primary key,

    Sname char(10)not null,

    Ssex bit not null,

    Sage tinyint not null,

    Sdept char(20) not null)

    create table course(

    Cno int not null primary key,

    Cname char(20)not null,

    Cpno int not null,

    Ccredit tinyint not null)

    create table sc(

    Sno int not null,

    Cno int not null,

    Grade tinyint not null

    foreign key(Sno)references student(Sno)

    foreign key(Cno)references course(Cno)

    )

    (1)

    seleCt top 1 S.sno,sname

    from SC,S

    where Cno='C2' and SC.sno=S.sno

    order by grade desC;

    (2)

    seleCt sname,age

    from Student,SC

    where SC.sno not in(

    seleCt SC.sno

    from SC

    where Cno='C2' )and SC.sno=S.sno;

    (3)

    seleCt sno, avg(grade) as average

    from SC

    group by sno

    having(avg(grade)>80);

    (3)法二

    seleCt sno, avg(grade) ' average'

    from SC

    group by sno

    having(avg(grade)>80);

    (4)

    delete from SC

    where SC.sno in(

    seleCt sno

    from S

    where sname='S5');

    (5)

    seleCt sname

    from S

    where sdept='英语'and sex='男';

    (6)

    seleCt SC.sno,avg(grade) as average

    from S,SC

    where S.sno=SC.sno

    group by SC.sno;

    (7)

    相关文章

      网友评论

          本文标题:SQL基本操作语句

          本文链接:https://www.haomeiwen.com/subject/nbwitctx.html