sql增删改查

1. char/nchar,varchar/nvarchar
   char(10)：定长，放五个中文，只放一个字节，还是占用10字节的内存（一个中文两个字节）
   nchar(10)：定长，放十个中文
   varchar(10)：变长，放五个中文，只放一个字节，就只占用一字节的内存。
   nvarchar(10)：变长，放十个中文
2. 创建数据库，在数据库里面创建表，以及添加表里面的项
   create database Library
   use Library;
   create table Users(
   uid int primary key,
   username nvarchar(20) not null,
   userpwd nvarchar(30) not null);
3. 插入数据
   insert into Users
   (uid,username,userpwd)
   values
   (002,’续航’,’fdgdg’),
   (003,’名人’,’dgf’);
   OK, 2 rows affected (0.12 sec)
4. select语句用法
   select * from Users;
   select username from Users where uid=’001’;
   select * from Users order by uid desc;
   select * from Users where uid>3;
   select * from Users where uid=’3’ and userpwd=’dfg’;
   select * from Users where uid in(4,5,6);
   select * from Users where uid>=2 and uid<=6 order by uid desc;
   select * from Users where uid between 2 and 6 order by uid desc;
   select * from Users where username like ‘梦%’; 以梦开头
   select * from Users where username like ‘%梦%’; 含有梦
   select * from Users where username like ‘%梦’; 以梦结尾
   select count(*) as account from Users; 选出表中所有的记录数
   select min(uid) from Users; 最小值
   select max(uid) from Users; 最大值
   select avg(uid) from Users; 平均值
   select sum(uid) from Users; 总和
   select top 5 * from Users; 查询出前5条的记录信息
   select * from Products where Unitprice>(select avg(Unitptice) form Products);
   update Users set username=’柯南’,Age=21 where uid=’001’;
   delete from Users where username=’李梦臣’;
   注意：删除之前别忘了进行数据备份，删除之后别忘了加条件
   有一个student表（学号，姓名，系名，课程名，成绩），查询至少修了四门课程的学生学号，姓名以及平均成绩的SQL语句。
   select stu,sname,avg(score)
   from students
   group by stu,sname
   having count(*)>=4;
   select distinct username from Usres //查出Users中不重复的username
   select count(distinct username) from Users; //查出Users中不重复username的数量。
   select count(*) as UserCount,sex from UserInfor group by sex;

  select count(*) as UserCount,sex from UserInfor 
  group by sex having count(*)>4;

1. 连接查询：
   select UI.UserId,UI.UserName,UI.Age,UI.QQ,UI.Tel,US.ScoreId,US.EnglishScore
   from UserInfor UI left join UserScore Us on
   UI.UserId=US.UserId;
   left join:以左边表记录（显示左边表记录所有项，右边表记录显示项以左边为基础）为基础，right join:以右边表记录为基础，inner join:并不以谁的记录为基础。
2. 分页查询：
   select top 10(pagesize) *
   from(
   select row_number() over(order by UserId) as rownumber,* from UserInfor) A
   where rownumber>30((pageindex-1)*pagesize);
   一页显示多少条数据 pagesize;
   当前是第几页 pageindex;
3. 条件查询：
   select UserName,RealName,Age,
   case
   when Age<20 then ‘大一’
   when Age>=20 and Age<=23 then ‘大二’
   when Age>23 and Age<=25 then ‘大三’
   else ‘大四’
   end as Grade from UserInfor;
4. 获取当前系统时间：
   select year(getdate()); //获取当前系统的年；
   select month(getdate()); //获取当前系统的月;
   select day(getdate()); //获取当前天；
   select * from UserInfor where year(Birthday)=’1988’;
   select dateadd(yy,100,getdate())//当天加上100年的时间，
   getdate()也可以换成具体的某一天比如写成:’2108/12/31’
   select dateadd(mm,1,getdate())//当天加上1个月的时间
   select dateadd(dd,100,getdate())//当天加上100天的时间
   select datediff(yy,getdate(),’2108/12/31’)//当天距离2108/12/31还有多少年
   select datediff(mm,getdate(),’2108/12/31’)
   select datediff(dd,getdate(),’2108/12/31’)
   select title,content,isnull(categoryID,0) from news //为null的categoryID用0显示出来
   select * from UserInfor where UserId in(1,4,6);
   select * from UserInfor where UserId not in(1,4,6);
   select distinct UserName from UserInfor;
5. 删除表中重复的内容：
   delete UserInfor where UserId not in(
   select min(UserId) from UserInfor group by UserName having count(*)>1)
   and UserId not in (
   select min(UserId) from UserInfor group by UserName having count(*)=1)