Sql Server 常用Sql语句

1 表创建

   

CREATE TABLE [hr].[personal] (
  [person_id] int IDENTITY(1,1) NOT NULL,
  [first_name] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [last_name] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [gender] char(1) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [nick] varchar(100) COLLATE Chinese_PRC_CI_AS NULL
)

 

 2 表删除

drop table [hr].[personal]

 

3  添加列

ALTER TABLE  hr.personal Add aaa VARCHAR(50) DEFAULT 'aaa'  not null;

 

4 修改列

ALTER TABLE  hr.personal  ALTER COLUMN aaa VARCHAR(60) NOT NULL;

 

5 删除列

ALTER TABLE hr.personal DROP COLUMN  aaa;

 

6 截断表

TRUNCATE TABLE hr.personal 

 

7 插入数据

a:INSERT INTO table_name (column_list) VALUES (value_list);

b:INSERT INTO table_name (column_list)
  VALUES  (value_list_1),
                (value_list_2),
                ...
                (value_list_n);

c:INSERT INTO sales.addresses (street, city, state, zip_code)

    SELECT street, city, state, zip_code FROM sales.customers

 

8  更新数据

UPDATE table_name SET c1 = v1, c2 = v2, ... cn = vn [WHERE condition]

 

9 查询数据

 SELECT * from  hr.personal;

 

10.1 分页查询

SELECT
   *
FROM
    hr.personal
ORDER BY  person_id 
OFFSET 10 ROWS 
FETCH NEXT 10 ROWS ONLY;

 

10.2 分页查询

SELECT* FROM (

SELECT*,ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber FROM Users ) as b

where RowNumber BETWEEN 0 and 3