黑马自学_SQL

---------------------- Windows Phone 7手机开发、.Net培训、期待与您交流！ ----------------------

 

 

drop table t_person                     删除表格

create table QQShop                    创建表格                                       
(
   Id int primary key identity(1,1) not null,        自动增长主键                   
   name nvarchar(10),                                    可能出现中文用nvarchar
   age int,
   nickname varchar(10),                           不会出现中文用varchar
   salary int
)

insert into QQShop values('张三',18,'young man',1200)           插入数据

alter table QQShop add addres nvarchar(50)                    修改表结构 增加字段address

update t_person set addres='未知' where addres is null       更新数据

select * from t_Person order by age asc,salary desc               年龄升序，收入降序排列

select COUNT(*) as 'peoples',                                               统计函数
       max(age) as 'maxage',                                                    最大值
       avg(age) as 'avgage',                                                    平均值
       min(age) as 'minage',                                                  最小值
       SUM(age) as 'sumage' from t_person                      求和

select *from t_person where name like'%n%'                           多个字符模糊查询         “ _ ” 单个字符
select *from t_person where age>=18 and age<=21              18到20之间
select *from t_person where age in(18,21)                                要么18  要么21

select age,COUNT(*) as '个数'from t_person
  where age>11 GROUP by age having COUNT(*)>1                分组查询：根据age分组（where不能出现聚合函数）所以用到having

select distinct *from t_person                                                过滤整行信息都重复的数据
--union all --合并表信息，字段个数和类型要匹配。all 不消除重复信息

 

------- sql函数 ----------
select ABS(-3) as'绝对值',                                                                                 绝对值'
    CEILING(-3.14) as'舍入最大整数',                                                         舍入最大整数
    FLOOR(-3.14) as'舍入最小整数',                                                            舍入最小整数'
    ROUND(3.1415926,3) as'四舍五入'--取小数点后3位                           四舍五入'--取小数点后3位
select LEN(name) as'字符串长度',                                                           字符串长度'
       LOWER(name) as'转小写',                                                                 转小写'
       UPPER(name) as'转大写',                                                                   转大写'
       RTRIM('bb  ') as'右空格',                                                                      消除右空格
       LTRIM('  bb') as'左空格',                                                                         消除左空格
       SUBSTRING(name,2,3) as'截取字符串'                                          截取字符串'
       from t_person 

 

       --日期处理函数
select GETDATE() as'当前日期',                                                                    当前日期
    DATEADD(HH,2,GETDATE()) as'两小时以后',                                       
    DATEDIFF(QQ,convert(datetime,'2011-2-19 16:20:31'),GETDATE()) as'季度差',
    DATEPART(YEAR,GETDATE()) as'今年年份'

 

小技巧：复杂的查询语句先分解一步一步的完成。

 

---------------------- Windows Phone 7手机开发、.Net培训、期待与您交流！ ----------------------