数据库（简单sql语句）sqlserver

数据库：独立的学科 很庞大的概念

基本sql语句

//添加两种写法；
insert into table values()
insert into table() values()

//删除
delete from table where id=""

//更新
update table set 表内名字=“++” where id=“+id+”;

//查询
select * from table where ID=4


//and且
select * from table where Name='张三' and Age=20 and ID=18


//or或
select * from table where Name='张三' or Age=20 or ID=4

//between and 进行区间查询
select * from table where createtime between '1998-10-31' and '2008-12-01'

//排序
//asc升序（默认）   desc降序
select * from table where createtime between '2019-10-31' and '2019-12-01'  order by age 

//模糊查询
//语法：1)字段 like '%高' 查询最后一个字是高
//2)字段like '高%' 查询第一个字为高
//3)字段 like '%高%' 查询带高的，不管是开头的还是结尾的
select * from StudentInfo where Name like '%高'
select * from StudentInfo where Name like '高%'
select * from StudentInfo where Name like '%高%'

//聚合函数
//语法：语法：count(列)
select COUNT(id) from table
select max(age) as Num from table
select min(age) as Num from table
select avg(age) as Num from table
select sum(age) as Num from table

//获取查询结果中的前N条记录
select top 5 * from table

//in和not in   in表示包含 not in 不包含
select * from table where ID  in (1,2,3,4,5)

//联合语句
//省市地
select s.*,(p.ProvinceName+c.CityName+d.DistrictName) as Addr from StudentInfo as s
inner join  S_District as  d  on  d.DistrictID=s.DistrictID
inner join S_City as c on c.CityID=d.CityID
inner join S_Province as p on p.ProvinceID=c.ProvinceID

//union：合并，重复的只显示一个
select name from StudentInfo union  select provinceid from  s_province