【黑马程序员】SQL笔记（三）

 

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

--新建一个表，名称为“laowai”；PS:必须像以下一次性添加两列，如果需要列，就一添加列，之后在执行，
--否则，执行后，再添加列会出错
create table laowai
(
laowainame varchar(10),
laowainame2 varchar(10),
laowainame3 varchar(10)
)
--删除表格名称为“laowai”的表；
drop table laowai
select * from laowai  --查看表
select laowainame from laowai --查看表


--给表中laowainame和laowainame2两个表起别名，并且两列合为一列
select laowainame+'?'+laowainame2 as'老外姓名' from laowai
       效果            


--表增加数据
insert table1 values('A013','貂蝉','女','20','不明')

--给表的列名，改别名
select id as'id号',syname as'姓名',sydex as'性别',age as'年龄',address as'地址' from table1
 
 
--给表中laowainame和laowainame2两个表起别名，并且还有添加了列，和列的值；ps:有点不理解
select laowainame as'老外',laowainame2 as'老外','美国佬'='法国老','非洲老'='黑人','法国老'='美国佬' from laowai
     


--查询table1表中age为"空"，或者age是"null"的数据
select * from table1 where age='' or age is null

--查询table1表中age是"null"或者address为“空”的数据
select * from table1 where age is null or address=''


--截取table1表中，以行为整体的百分之行的数据，“top”代表从顶端开始截取，也就是从第一条
select top 20 percent * from table1




--字符串函数

--获取字符串位置（不是索引），charindex(),里面有三个参数：
--第个参数是：需要查找的字符；第个是：被查找的字符串；第个是：从第几个开始查找
--ps:如果查找的字符，字符串中没有的，就返回“”
select charindex('are','how are you?',1)  --返回“”
select charindex('v','aabbccdd',1)  --返回“”

select charindex('C#',Mainbody,0) from zhutie
↓结果↓
 
select * from zhutie where charindex('C#',Mainbody,0)=1
select * from zhutie where charindex('C#',Mainbody,0)=0

--获取字符串的长度（不是索引），没一个字符，或者汉字，都为一个！
select len('这里有多少个字？hahaha!...')

--大写转换成小写
select lower('ABCDEFG')

--小写转换成大写
select upper('abcdefg')

--清除字符串左边的空格（中间和后面的保留）
select ltrim('   刘鹏威  刘鹏威  ')

--清除字符串左右边的空格（中间和前面的保留）
select rtrim('   刘鹏威  刘鹏威  ')

--从右边开始截取字符串；right()有两个参数：第个是“被截取的字符”；第个是：截取多少个
select right('我你他',2)

--替换字符串：replace()有三个参数：
--第是“被替换的字符”
--第是“需要替换的字符”
--第是“替换上去的字符”
--ps:当被替换的字符串有多个相同的字符，将会被全部替换掉；例如这里的“止”将全部替换成“哈”
select replace('树欲静止，而风不止止止止止','止','哈')

--删除并插入函数：stuff()有四个参数：
--第个是：原字符串
--第个是：从第几个位置开始删除
--第个是：删除多少个字符
--第个是：插入的字符
select stuff('ABCDEFG',2,3,'我插')

--日期函数
--获取当前计算机日期，时间
select getdate()


--使指定时间加时间；dateadd()有三个参数：
--第一个为：年，月，日，时，分，秒
--第二个为：需要加多少，或者减多少
--第三个为：指定的年，月，日
--ps:时间，日期是累加的，如加的秒数秒，那么分钟将会自动加，如此类推
select dateadd(yy,2,'2011-1-1')  --年(yy)
select dateadd(mm,-2,'2011-1-1')  --月(mm)
select dateadd(dd,50,'2011-1-1')  --日(dd)
select dateadd(hh,2,'2011-1-1')  --小时(hh)
select dateadd(minute,2,'2011-1-1')  --分(minute)
select dateadd(second,2,'2011-1-1')  --秒(second)


--返回两个时间之差，计算后面的时间比前面的时间“多，多少”，或者“少，多少”
select datediff(second,'2011-1-1','2012-1-1')  --秒(second)
select datediff(minute,'2011-1-1','2012-1-1')  --分(minute)
select datediff(hh,'2011-1-1','2012-1-1')  --小时(hh)
select datediff(dd,'2011-1-1','2012-1-1')  --日(dd)
select datediff(mm,'2011-1-1','2012-1-1')  --月(mm)
select datediff(yy,'2011-1-1','2012-1-1')  --年(yy)



--返回指定日期的“星期几”
select datename(dw,'2010-1-1')

--返回指定日期的“数”
select datepart(yy,'2010-1-31')  --返回年“”
select datepart(mm,'2010-1-31')  --返回月“”
select datepart(dd,'2010-1-31')  --返回日“”
select datepart(hh,'2010-1-31')  --返回时“”
select datepart(minute,'2010-1-31')  --返回分“”
select datepart(second,'2010-1-31')  --返回秒“”




--数学函数

--取数值表达式的绝对值 (不懂)
select abs(-43.5)

--返回大于或等于所给数字表达式的最小整数
--也就是说，这里的“.1”返回“”,无论小数点后面的值是多少，都进一。
--如果无小数，就返回自身整数
select ceiling(45.1)

--取小于或等于指定表达式的最大整数
--也就是相当于：舍弃小数点
select floor(45.11119)


--取数值表达式的幂值 如下：
select power(5,2)  --5*5=25
select power(5,3)  --5*5*5=125
select power(6,5)  --6*6*6*6*6=7776
select power(9,6)  --9*9*9*9*9*9=531441

--将数值表达式四舍五入为指定精度
select round(45.4555,1)
select round(45.4555,2)
select round(45.5555,3)
--需要注意以下两条有区别
select round(45.9555,1)
select round(45.9555,2)


--对于正数返回+1，对于负数返回-1，对于则返回
select sign(0)  --返回
select sign(10) --返回
select sign(-1) --返回-1

--取浮点表达式的平方根  如下：
select sqrt(9)  --返回
select sqrt(6)  --返回.4494897427831779
select sqrt(49) --7
select sqrt(36) –



--系统函数
--将字符串，转换成数字型，而，如果varchar(5),12345)中的varchar(5)参数改为，那么就返回“”
select convert(varchar(5),12345)

--返回当前用户的名称 返回：你登录的用户名
select current_user

--返回用于指定表达式的字节数
select datalength('甲A甲a')  --返回


--返回当前用户所登录的计算机名字
select host_name()

--返回当前计算机所登录的用户名称
select system_user

--从给定的用户I D返回用户名  （有不明白的地方）
select user_name(0)
select user_name(1)
select user_name(2)
select user_name(3)


--把ccc表中的password列内部数据为小写‘o’替换成‘’和小写‘i’替换成‘’
update ccc set password=replace(replace(lower(password),'o','0'),'i','1')


--排序
select number from record order by convert(int,left(number,2)),
convert(int,Stuff(number,1,charindex('-',number,1),''))


ps:使用order by 后面还可以选择为升序（asc）或者降序（desc）如无填写参数则默认使用升序（asc）
↓参考格式↓
select top 3 datediff(minute,Begintime,Endtime)as'时间之差' from record order by 时间之差 desc

--排序
select * from record order by left(number,2),convert(int,right(number,len(number)-3))











--模糊查询

--使用like通配符模糊查询
select * from card where ID like'00[4-5][5][_][a-zA-Z]%'


--使用in模糊查询
select * from card where userName in('张一','张五','张二')

--查询beifen1表中age为‘null’的数据
--ps:已经把表中address这列中的两条数据删除了
--这个“能”查找出这已经删除了address这列的数据
select * from beifen1 where address=''
--这个“不能”找出删除了address这列的数据
select * from beifen1 where address is null

--查询某个字段，在某个范围的值使用between与and组合使用,这这里是查询到之间的值
select * from table1 where age between 25 and 40






--聚合函数

--求列的总和
select sum(age)as'此列总和' from table1
select sum(age)as'此列总和',此列总和=sum(age+age) from table1
select sum(WritenExam) as '笔试总成绩',机试总成绩=sum(labExam) from stumarks

--求列的平均数
select avg(age)as'平均数' from table1
select avg(age)as'平均数',平均数=avg(age+age) from table1
select avg(WritenExam) as '笔试平均成绩',机试平均成绩=avg(labExam) from stumarks

--求列最大值，条件是，该列中小于的最大数
select max(age)  from table1 where age<28

--求列最小值，条件是，该列中大于的最小数
select min(age)  from table1 where age>28

--计数，计算达成条件的有多少条记录，这里的条件是找出，小于的，大于的记录就多少条
select count(*) from table1 where age<20 or age>40



--以stuID这列为分组，求出每个分组中的平均分
--这里是求出stuID这列的平均分
select stuID,avg(score)as'各科平均分' from chengji group by stuID
 

↓结果↓
 

--找出stuid为分组中的carserID分组中的最大值
--也就是说，先在stuid这里分组，而在stuid每个小组中又进行carserID的分组，而只要carserID中的最大值
select stuid,carserID,max(score)as'科目最后成绩' from chengji group by  carserID,stuid
      结果→     



--找出carserID为分组中的stuid分组中的最大值
--也就是说，先在carserID这里分组，而在carserID每个小组中又进行stuid的分组，而只要stuid中的最大值
select stuid,carserID,max(score)as'科目最后成绩' from chengji group by stuid, carserID
  结果→  






--表连接

--内联连接
select C.id,R.begintime,R.price from Computer C
 inner join
 record R on C.ID=R.computerID
--内联连接（三表）
select C.id,R.begintime,R.price,D.UserName from Computer C
 inner join
 record R on C.ID=R.computerID
 inner join 
 Card D on D.ID=R.cardID
--内联连接（不等于号），这里类似于交叉连接，但是，去除了自身等于的那几条
select C.id,R.begintime,R.price from Computer C
 inner join
 record R on C.ID<>R.computerID

--左连接，
select C.id,R.begintime,R.price from Computer C
 left join
 record R on C.ID=R.computerID

select * from record
select * from computer
--右连接
select C.id,R.begintime,R.price from Computer C
 right join
 record R on C.ID=R.computerID

--完全连接

select C.id,R.begintime,R.price from Computer C
 full join
 record R on C.ID=R.computerID

--交叉连接
select C.id,R.begintime,R.price from Computer C
 cross join
 record R  --on C.ID=R.computerID --ps:交叉连接不需要用“on C.ID=R.computerID”这段语句



--怎么查看奇数行或偶数行的某列数值的总数？
--第一，从新插入一列自动增长列，需要用备份方式插入。
--这里的into后面“into #TBL1 from  TBL”是把TBL这个表备份到#TBL1(自动新建)这个表中
select A,identity(int,1,1)as '钱' into #TBL1 from  TBL

select * from #TBL1 --查询

--之后利用新建的那个自动增长列来用‘%’取模函数来取自动增长列
select sum(钱)as'偶数只和' from #TBL1 where 钱%2=0
select sum(钱)as'奇数只和' from #TBL1 where 钱%2=1




--从一个表中，插入另外一个表的指定数据，利用表连接，利用备份方法
 insert ka(CardID,Score)
select Y.CardID,2 from ka K
 right join 
 yh Y on K.CardID=Y.CardID 
 where score is null



--查询表中syName ‘诸’后面任意长度的字符串使用“%”
select * from table1 where syName like'诸%'

--查询表中syName ‘诸’带有一个字符的字符串使用“_”
select * from table1 where syname like'诸_'

--查询表中syName 诸葛瑾这三个字符前面包含到之间其中一个字符“[]”的用法
select * from table1 where syname like'[1-3]诸葛瑾'

--查询表中syName 以诸葛开头，后面为[]中的字符，这里为查找诸葛后面的“[瑾,亮,1]”“[]”的用法
select * from table1 where syname like'诸葛[瑾,亮,1]'--这[]里面的“,”逗号都可以省略

--ps:[]能查找的数量只能为个，如“诸[葛瑾]”这查“诸”后面为“葛瑾”两个数量的字符时，是无法查找的到的;
--如果需要查找两个字符，就如下：用多一个“[]”
select * from table1 where syname like'诸[葛][瑾]'

--查询表中syName 以诸葛开头，后面为[]中，不为“或的字符”使用“[^]”
select * from table1 where syname like'诸葛[^12]'--这里的和之间没有用逗号分隔开来，实际上已经是分开了；


--增加数据的方法：
insert into table1 values('A003','诸葛亮','男','24','蜀汉')
insert into table1 values('A012','孙尚香','女','18','蜀汉')

--增加数据的方法：
insert table1(id,syname,sydex,age,address)values('A009','张飞','男','20','蜀汉')
--方法呢，可以部分增加如下： ps:但是，没有赋值的必须可以为空才行！！
insert table1(id,syname,sydex,address)values('A010','张苞','男','蜀汉')

--备份方式：

--第一种方式（备份的数据表名不允许存在）（会自动新建数据表）

--将table1中的数据，备份到beifen1中去，而beifen1这个会自动新建出来；
select * into beifen1 from table1
select * from beifen1--查询

--这里是带条件的备份，将table1中sydex为女的，备份到beifen2中去，而beifen2这个会自动新建出来；
select * into beifen2 from table1 where sydex='女'
select * from beifen2--查询

--这里是带条件的备份,条件为：age>25
select * into beifen3 from table1 where age>25
select * from beifen3 --查询

--第二种方式（备份的数据表名必须存在）

--将table1中的数据备份到beifen2中去，而beifen2中的数据也不会被删除，如果有重复的就重复出现；
--仅仅是备份数据，没有任何约束，所以重复出现，也不会出现错误，如ID号的重复；
insert into beifen2 select * from table1
select * from beifen2

--类型
insert into beifen3 select ID,syName,sydex,age,address from table1
select * from beifen3


--增加一个自动增长列；   "identity"自动增长列的关键字
--“into beifen4 from beifen3”下面的这里也是新建了一个beifen6来存放。
select ID,syname,sydex,age,address,identity(int,1,1)as'id列' into beifen6 from beifen3
select * from beifen6


--一次增加多个数据，格式如下，需要在没一行数据后面加上union连接下一句，最后一句不用！
--如果有自动增长列，就把自动增长列省略
insert beifen5
select'A013','女','18','蜀汉'union
select'A014','男','15','蜀汉'union
select'A015','女','16','蜀汉'union
select'A016','女','12','蜀汉'

select * from beifen5--查询

--将beifen5表中的address列的值更换成‘蜀’
update beifen5 set address='蜀'

--带条件的的更新数据：将beifen5表中的address列的值更换成‘蜀汉’条件是“sydex='女'”中为‘女’的项，其他不变
--一般的更新都带条件的更新
update beifen5 set address='蜀汉' where sydex='女'

--限制数字在一定的范围
alter table ForumUser
 add constraint CK_NickName check(NickName between 2 and 10)




--删除约束
alter table tbl
 drop constraint CK_name

--创建数据库
create database bbb

 

 

 

---------------------- Windows Phone 7手机开发、.Net培训、期待与您交流！ ----------------------详细请查看：http://net.itheima.com/