精华内容
下载资源
问答
  • 1.基础的增删改 A.增:alter table [表名] add [字段名] 字段属性 default 缺省值 default 是...注:自增不能直接修改,必须将原有ID删除,然后重新添加具有identity属性的ID字段。比如你要修改的字段名为I...

    1.基础的增删改
    A.增:alter table [表名] add [字段名] 字段属性 default 缺省值 default 是可选参数
    B.删:alter table [表名] drop 字段名
    C.改:alter table [表名] alter [字段名] 字段属性
    注:自增列不能直接修改,必须将原有ID列删除,然后重新添加一列具有identity属性的ID字段。比如你要修改的字段名为ID:
    alter table 表名 drop column ID
    alter table 表名 add ID int identity(1,1)

    2.修改表:
    A.重命名表:EXEC sp_rename ‘oldname’,‘newname’
    B.修改列属性:alter table 学生信息 alter column 姓名 varchar(20) not null
    C.添加列:alter table 学生信息 add 家庭住址 nvarchar(20) null
    D.删除列:alter table 学生信息 drop column 家庭住址
    E.修改列名:exec sp_rename ‘表名.[字段原名]’,‘字段新名’,‘column’

    3.复制表:
    A. 复制整张表:select * into new_table from old_table
    B. 复制表结构:select * into new_table from old_table where 1=2
    C. 复制表内容:insert into new_tab select * from old_table

    4.再展示如何将添加的列获取另一列的值
    A.update 表2 set (要插入的列名)= select 表1.某一列 from 表1 left jion 表2 on 表1和表2的关联 where …
    B.update 表1 set 表1.列=表2.列 from 表2 where 表2.id=表1.id
    C.update _a set 列=_b.列 from 表1 _a join 表2 _b on _a.id=_b.id

    如果将一个表中的一列复制到另一列中,可以这样
    update 表1 set 表1.列2=表1.列2 from 表1
    小编参考的资料路径
    笑笑小白
    NET未来之路

    展开全文
  • SQL语句–中添加列、删除列、改数据类型 中添加列: alter table 表名 add 字段名 数据类型 eg: alter table Student add PhoneNumber char(11) 表中删除列: alter table 表名 drop column 字段名 eg: ...

    SQL语句–在表中添加列、删除列

    在表中添加列:
    alter table 表名 add 字段名 数据类型
    eg:
    alter table Student add PhoneNumber char(11)
    
    在表中删除列:
    alter table 表名 drop column 字段名
    eg:
    alter table Student drop column PhoneNumber
    
    展开全文
  • Dear Users,How to add a new column between the existing columns using T-SQL Statements in SQL Server 2005.For Example in my existing table i have the columns Id,Name,AgeI need to insert Password colum...

    Dear Users,

    How to add a new column between the existing columns using T-SQL Statements in SQL Server 2005.

    For Example in my existing table i have the columns Id,Name,Age

    I need to insert Password column between Name and Age column using transact SQL statement.

    解决方案You don''t need to, you can always do a select in the order you want:

    select ID, name, password, age from tablename

    Mehdi Gholam is answer is good, but you need some explonation.

    You can do it and you can insert column between existing coulmns, but it needs to re-create table. By default new column is inserted as a last column in the column collection. As Mehdi Gholam wrote, you can always select data (columns) in order you want.

    More at: http://msdn.microsoft.com/en-us/library/ms190273(v=sql.90).aspx[^]

    If you still want to insert new column between existing columns, i recommend you to do these steps:

    1) create new table NewTable with desired columns

    2) copy data from ExistingTable into NewTable with default [Password].

    3) remove ExistingTable

    In the beginning ...

    --CREATE SCHEMA [cpqa]

    CREATE TABLE[cpqaAnswers].[cpqa].[tblSFThree] (

    [intId][int],

    [strName][nvarchar](81),

    [intAge][int]

    )

    SELECT * FROM [cpqaAnswers].[cpqa].[tblSFThree]

    No data, just a table ...

    DECLARE @ph [nvarchar](20)

    SET @ph = ''nomanshallpass''

    SELECT [intId], [strName], @ph As [strPass], [intAge] FROM [cpqaAnswers].[cpqa].[tblSFThree]

    While there''s no table field called [strPass], it''s possible, in TSQL, to name such a thing on-the-fly as a variable using a DECLARE statement.

    CREATE TABLE [cpqaAnswers].[cpqa].[tblSFFour] (

    [intId][int],

    [strName][nvarchar](81),

    [strPass][nvarchar](20),

    [intAge][int]

    )

    SELECT [intId], [strName], [strPass], [intAge] FROM [cpqaAnswers].[cpqa].[tblSFFour]

    But to create another field, some more typing is required. Now there''s a formal placeholder

    SELECT * FROM [cpqaAnswers].[cpqa].[tblSFFour]

    When I say there''s just a table, I mean "no NULLs are allowed" so think of NULL as everything and therefore nothing.

    展开全文
  • DECLARE @i int set @i=0 WHILE @i BEGIN alter table test add lie+@i int ...@i是控制循环的次数,也可以理解为要添加数 ************ 补充: ************ 1、建一个表,保存你要追加的字段名和
    DECLARE @i int
    set @i=0
    WHILE @i<10
    BEGIN
    alter table test add lie+@i int
    set @i=@i+1
    END
    就可以添加lie1,lie2,lie3,lie4……
    @i是控制循环的次数,也可以理解为要添加的列数

    ************
    补充:
    ************
    1、建一个表,保存你要追加的字段名和字段类型
    如:create table t_tmp(id int IDENTITY(1,1) not null,col_name varchar(100),col_type varchar(100));

    2、把你想要动态追加的字段和类型全写入表中
    如:
    insert into t_tmp(col_name,col_type) values('t1','int');
    insert into t_tmp(col_name,col_type) values('t2','date');
    insert into t_tmp(col_name,col_type) values('t3','varchar(100)');
    insert into t_tmp(col_name,col_type) values('t4','char(100)');

    3、生成动态建表语句,并创建表
    declare @str varchar(2000)
    set @str='create table ttt('
    select @str=@str + col_name+' '+col_type+',' from t_tmp
    set @str=substring(@str,1,len(@str)-1)+')'
    select @str
    exec(@str)

    ------------------------------------------------------------------------------------------------------------

    declare @min varchar(4),@max varchar(4), @sqlstr varchar(8000)
    set @sqlstr = 'select FItemID'
    select @min = min(years), @max = max(years) from #Happen3
    while @min <= @max
    begin
    set @sqlstr = @sqlstr+',
    sum(case when years = '+@min +' then(case when Months=1 then (FAmount / FQty)end)end) as Y'+@min +'P1Price,
    sum(case when years = '+@min +' then(case when Months=2 then (FAmount / FQty)end)end) as Y'+@min +'P2Price,
    sum(case when years = '+@min +' then(case when Months=3 then (FAmount / FQty)end)end) as Y'+@min +'P3Price,
    sum(case when years = '+@min +' then(case when Months=4 then (FAmount / FQty)end)end) as Y'+@min +'P4Price,
    sum(case when years = '+@min +' then(case when Months=5 then (FAmount / FQty)end)end) as Y'+@min +'P5Price,
    sum(case when years = '+@min +' then(case when Months=6 then (FAmount / FQty)end)end) as Y'+@min +'P6Price,
    sum(case when years = '+@min +' then(case when Months=7 then (FAmount / FQty)end)end) as Y'+@min +'P7Price,
    sum(case when years = '+@min +' then(case when Months=8 then (FAmount / FQty)end)end) as Y'+@min +'P8Price,
    sum(case when years = '+@min +' then(case when Months=9 then (FAmount / FQty)end)end) as Y'+@min +'P9Price,
    sum(case when years = '+@min +' then(case when Months=10 then (FAmount / FQty)end)end) as Y'+@min +'P10Price,
    sum(case when years = '+@min +' then(case when Months=11 then (FAmount / FQty)end)end) as Y'+@min +'P11Price,
    sum(case when years = '+@min +' then(case when Months=12 then (FAmount / FQty)end)end) as Y'+@min +'P12Price'
    set @min = @min+1
    end
    set @sqlstr = @sqlstr +' into ##Happen4 from #Happen3 where Not(FQty=0) group by FItemID'
    exec(@sqlstr)

    展开全文
  • 使用SQL语句向数据表中添加列,我想大家都清楚该如何做,示例如下: use test go alter table testadd add testid int not null default(1) 但是如果想将添加的列制定到某一列后呢,该如何实现呢? 今天我...
  • <p>This question already has an answer here: ... <a href="/questions/23356923/how-to-add-dynamic-column-to-an-existing-table" dir="ltr">How to add dynamic column to an existing table ...
  • 使用SQL语句向数据表中添加列,我想大家都清楚该如何做,示例如下: use test go alter table testadd  add testid int not null default(1) 但是如果想将添加的列制定到某一列后呢,该如何实现呢?   今天...
  • SQL添加到表

    万次阅读 2015-12-03 20:37:04
    有如下数据库表: tb_studentselect * from tb_student(1)tb_student表额外添加 L3VPN,并且该L3VPN无需含有默认值ALTER TABLE tb_student ADD l3vpn VARCHAR(10) (2)tb_student表额外添加 L3VPN,...
  • sql 查询中添加

    2013-05-29 09:58:44
    现有表 tb1 字段 aa ,bb,我想查询的时候显示多出来一CC ,而且不改变tb1的表结构,别说用alter tb1 add , 用select 语句,小弟基础没怎么学好,求老师们解答一下.
  • 如何查询结果中添加表示记录的行数? 要求是增加一显示行数
  • SQLServr添加数据

    2018-08-21 08:53:40
    数据表中添加或者多步骤相同 1、连接数据库,选择数据表-》右键点击-》选择设计。 2、新打开的窗口中输入中-》输入列名,数据类型,是否可空-》下面输入注释等属性-》点击保存按钮(或者ctrl+s)。 3...
  • mysql给表中添加列的方法:1、使用“ALTER TABLE 表名 ADD 新字段名 数据类型 [约束条件];”末尾添加列;2、使用“ALTER TABLE 表名 ADD 新字段名 数据类型 [约束条件] FIRST;”开头添加列。(推荐教程:mysql...
  • 我有这张桌子: ID | name | result | -------------------- 1 | A | 1 | -------------------- 2 | B | 2 | -------------------- 3 | C | 1 | ------... 2 | 80 | ------------------------------- 如何在SQL中查询?
  • ; margin-right:0cm">已知:修改表结构和update更新操作不能存在于同一个事务。 ; margin-right:0cm">需求:...怎么避免因新增和update更新操作同一个事务而报错 的问题?</p>
  • <p>I have two columns in a MySQL table, one called "Total Experience" and ...<p>Is there a way to select and add up Steve and Jack's Total Experience in a SQL query, as they have the same id? </div>
  • mysqlroot密码忘记了,修改root密码1,停止MYSQL服务,CMD打开DOS窗口,输入 net stop mysql 2,CMD命令行窗口,进入MYSQL安装目录 比如E:\Program... 项目遇到这样的要求,一个Button用一个Adorner装饰,这个Adorner上...
  • 如果想查询出这个表的信息,并添加连续自增的ID,可用如下查询语句: SELECT Row_Number() over ( order by getdate() ) as init , * FROM 表名   解决办法《二》: 使用关键字IDENTITY创建临时表 ...
  • 1.若要向表中添加列,请使用以下语法: ALTER TABLE table_name ADD column_name datatype 2.若要删除表中的列,请使用以下语法(请注意,一些数据库系统不允许这样删除数据库表中的列): ALTER TABLE table...
  • select中添加排名SQL语句

    千次阅读 2011-03-27 16:45:00
    4此表的排名应该如何做? SELECT   COUNT (  *  )  FROM  user_test  WHERE  scores  >=  (  SELECT  scores  FROM  user_test  WHERE   user   = 4  )  -- 参数说明: -- sc 表名 -- name ...
  • I have Table A with 5 columns: TableA -- Name Tel Email Address I ... Altrough MSDN[^] says, that this can't be done from T-SQL, but from SQL Management Studio. By the way, wha't wrong with that option?

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,751
精华内容 1,100
关键字:

在sql中添加列