9数据库 增删改除 bit类型怎么查数据 ？
--------------- 数据查询 查询所有数据 select * from student
查询指定的列： select sNmae, sage from student
给咧起别名 1. select sname as '姓名'，sage as '年龄' ，Sbrithday as ‘生日’ from student as可以省略
2. select ‘姓名’=sname, '年龄'=sage from student
3. select sname='姓名'，sage=‘年龄’，fromstudent where ssex='男'，and sage》=20
4. select 1+1 as ‘列’
5，select getdate() 这是一个函数
6,select '平均成绩'+60 （错误）
7.top 和distinct select top 3 * fron student select top 1 sage ,sname fron student sllect top 10 percent sname ,sage from student 小数点后有数据则进1 ，不是4舌5如 distinct 去除重复的数据， 只有查询的列的内容全部相同时才会去掉,2列只有完全不同时才去除 select distinct * from student select distinct sname ,sage ssex from student 先执行distinct 后执行top
8，聚合函数 select count (*) from student select count(sid) as '总计数据' from student selext avg(tsalary) from teacher select max(tsalary) from teacher select min(tsalary) from teacher select sum(tsalary) from teacher slect avg(english) avg(math) from score selext sum(math)/count(sid) from score 如果数据有空值即null，则count和avg都不会统计
10 select max(sbirthday) min(sbrithday) from student 最大值里现在最近的日期 最小值是求离现在最远的日期
11 带条件查询 select * from student where (asge>20and sage<30) and ssex='男' select * from score where english20 and sage20 and sAge<30) and sSex='男' select * from Score where english6 求男学生个数超过女的个数 select scalssid,count(*) from student where ssex='男' group by .
15联合结果集 union 去除重复项，并且排序，2表数据个数，类型必须一致。
select sname,sage，sclassid from student union select tname,tage from teacher
----union all 数据不拍序 ，不去除重复项 select max(english) as '最高成绩' ，min（english） avg(english) from score
把一个表插入到一个新表中： 把一个表的数据插入到一个旧表中（表必须存在） insert into backup
1，select * fron emplayee
2 select * from employee where salary>2000;
3select * from em where etime between '2006-01-01 and 2008-12-31 3. select top 2 * from em order by eititime desc
4 select avg(esalary) ,min(esalary) from em
5 delect copunt(*) from em
6select edepid, count(*) from dp ogroup by edepid
7.select edepid,avg(esalary) from em group by edepid
8.select edepid,avg(esalary) from em where esex='nan' group by eid select * from studenrt where len(sname)=2 select lower('abcdASD') select lower('abcdSDF') SELECT left('我爱北京题难吗',1) 我 SELECT left('我爱北京题难吗', 3,2) 北京 索引从1开始 select replace('我爱你'，'爱'，‘恨’) 日期函数 select dateadd(day,1,getdate()) 取去明天的时间
quarte 季度 dayofyear 一年中的第几天 week ,第几周 weekday 一周中的第几天 select datepart (weekday,'2012-3-4') datenaem 返回字符串 datepart 返回整数 统计不同入学年数的学生个数 类型转换 cast select cast(19.6 as int)
19 slect '平均成绩' +cast(88 as varchar(2)) select convert(int,19.6) 19 round数学函数4舍5入 select cast(round(19.6 ,0) as int) 20.0 ceiling 有小数就进1 select ceiling （19.1）
20 18是有效数字，2是小数点后的位数 select cast（81.2467884 as numeric (18,2)） select CONVERT(varchar(100)，getdate(),23) select CONVERT(varchar(10)，getdate(),20) select convert (date,getdate()) 牛亮 16:00:47 (多人发送) CREATE TABLE [CallRecords] ( [Id] [int] NOT NULL identity(1,1), [CallerNumber] [nvarchar](50), --三位数字 [TelNum] [varchar](50), [StartDateTime] [datetime] NULL, [EndDateTime] [datetime] NULL --结束时间要大于开始时间,默认当前时间 ) --主键约束 alter table [CallRecords] add constraint PK_CallRecords primary key(id) --检查约束 alter table [CallRecords] add constraint CK_CallRecords check(CallerNumber like '[0-9][0-9][0-9]') alter table [CallRecords] add constraint CK_CallRecords_EndDateTime check(EndDateTime > StartDateTime) --默认约束 alter table [CallRecords] add constraint DF_CallRecords default(getdate()) for EndDateTime 输出所有数据中通话时间最长的5条记录。orderby datediff select top 5 * ,from callrecords order by datediff( ss, starttiem ,endtime, ) desc 输出所有数据中拨打长途号码（对方号码以0开头）的总时长。like、sum select sum(endtime-starttime) from callstable where telnumber like '0%' 输出本月通话总时长最多的前三个呼叫员的编号。 输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*) --输出所有数据中拨打长途号码（对方号码以开头）的总时长。like、sum select SUM(DATEDIFF(SS,[StartDateTime],[EndDateTime])) from [CallRecords] where [TelNum] like '0%' --输出本月通话总时长最多的前三个呼叫员的编号。 select * from [CallRecords] select top 3 [CallerNumber],SUM(DATEDIFF(SS,[StartDateTime],[EndDateTime])) from [CallRecords] where DATEDIFF(MONTH,[StartDateTime],'2010-07-08') = 0 group by [CallerNumber] order by SUM(DATEDIFF(SS,[StartDateTime],[EndDateTime])) desc --输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*) select top 3 [CallerNumber],COUNT(*) from [CallRecords] where DATEDIFF(MONTH,[StartDateTime],'2010-07-08') = 0 group by [CallerNumber] order by COUNT(*) desc