char/nchar,varchar/nvarchar char(10)：定长，放五个中文，只放一个字节，还是占用10字节的内存（一个中文两个字节） nchar(10)：定长，放十个中文 varchar(10)：变长，放五个中文，只放一个字节，就只占用一字节的内存。 nvarchar(10)：变长，放十个中文创建数据库，在数据库里面创建表，以及添加表里面的项 create database Library use Library; create table Users( uid int primary key, username nvarchar(20) not null, userpwd nvarchar(30) not null);插入数据 insert into Users (uid,username,userpwd) values (002,’续航’,’fdgdg’), (003,’名人’,’dgf’); OK, 2 rows affected (0.12 sec)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>
4;
连接查询： 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:并不以谁的记录为基础。分页查询： 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;条件查询： 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;获取当前系统时间： 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;删除表中重复的内容： 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)
