SQL之-建库、建表、建约束、关系、部分T-sql语句 SQL基本语句大全

stuinfo (stuno,stuname) select stuno,stuname from stumarks
insert into stuinfo(stuno) select stuno from stumarks

---2）从现有的表中取出数据，创建一个新表，将数据插入到新表中
select [列名] into <表名> from <源表名>
select id as '编号',[name] into newtable from outtable
select cast(id as varchar(4))+cast([name] as nvarchar(4)) as '编号及姓名' ,id into newtable from outtable
select * from newtable
drop table newtable

---3)union 现有的多个表中取数据放入现有的表3中
insert into<表名3> [列名]
select * from 表1 union
select * from 表2

--3.更新语句 update <表名> set <列名=更新值> [where <更新条件>]
---注意：一般where不要省略,如不写将修改整个表

--4truncate删除数据
--比delete只能全部删除数据，不能部分删除，删除的效率高，可以重置自增长列

select * from stumarks
select * from stuinfos

--给考试成绩各提5分,100分封顶。
update stumarks set writtenexam=100
where writtenexam>95
update stumarks set labexam=100
where labexam>95

update stumarks
set writtenexam=writtenexam+5
where writtenexam+5<=100

update stumarks
set labexam=labexam+5
where labexam+5<=100



select examno,stuno,writtenexam+5 as 笔试,labexam+5 as 机试 from stumarks where writtenexam+5<=100 and labexam+5<=100

create table t
(
id int ,
name nchar(4),
dt datetime,
age int,
score int
)

insert t values (1,'a',getdate(),20,50)
insert t values (2,'b',getdate(),21,60)
insert t values (3,'c',getdate(),21,100)
insert t values (4,'d',getdate(),23,80)

select * from t
select top 2 * from t
select top 60 percent * from t

select top 5 * from products
select top 5 percent * from products

--启别名：两种方式 As（可省略） 、=
select productid as '编号' from products
select productid '编号' from products
select '编号'=productid from products
select employeeid from employees
--declare @a nvarchar(6)
--set @a='员工编号为：'
select N'员工编号为：'+cast(employeeid as nvarchar(2)) from employees

select distinct country from suppliers order by country
select * from t
select * from products
select * from products order by 4 asc,6 desc
select * from products where unitprice>16 and productname like 'T%' or productid=16

select * from suppliers where country in('japan','italy')
(1)char、varchar、text和nchar、nvarchar、ntext
char和varchar的长度都在1到8000之间，它们的区别在于char是定长字符数据，而var
char是变长字符数据。所谓定长就是长度固定的，当输入的数据长度没有达到指定的长度时将自动以英文空格在其后面填充，使长度达到相应的长度；而变长字符数据则不会以空格填充。text存储可变长度的非Unicode数据，最大长度为2^31-1(2,147,483,647)个字符。
后面三种数据