SQL server 创建 修改表格 及表格基本增删改查 及 高级查询 及 (数学、字符串、日期时间)函数[转]

SQL server 创建 修改表格 及表格基本增删改查 及 高级查询 及 (数学、字符串、日期时间)函数

--创建表格
create table aa
(
    UserName varchar(50) primary key, --建主键.
    Password varchar(20) not null, --不能为空
    Name varchar(20) unique, --唯一键,不能重复
    sex bit default 1, --建默认约束(缺省约束)
    birthday datetime check(birthday>'1990-1-1')--建检查约束
)
create table bb
(
    myname varchar(50)  references aa(username), --外键联系(最好是对应aa表主键)
    mybirthday datetime
    primary key(myname,mybirthday) --联合主键
)

--修改表
alter table aa add money float --添加money列
alter table aa drop column money --删除money列
alter table aa alter column Money Real --修改money列的属性,把原属性改为real属性

use mydb
--增
insert into Fruit(Ids,Name,Price) values('k008','榴莲',9.9)--增加时 主键不能为空,其他项系统默认为 NULL
insert into Fruit values('k009','甜瓜',4.8,'淄博',80,'image/4.gif')--所有列都添加,一个也不能落下
--删
begin tran
delete from Fruit --和begin tran 一块执行 数据能回复(roll back)
rollback
delete from Fruit where Ids='k008' --删除指定行

--修改
update Fruit set Source=null where Ids='k005' --修改指定行的某项数据
update Fruit set Numbers=65 where Ids='k002'  --修改指定行的某项数据
update Fruit set Ids='k008'where Name='甜瓜' and Price='4.80' --修改指定名称和价格的 Ids

--查询
select * from Fruit --查询所有
select Name,Price,Numbers  from Fruit --查询指定列
select Name as'名称',Price as'价格',Source as '产地' from Fruit --查询指定列,并改变 虚拟表 的列名称(数据库中表没变)
select * from Fruit where Ids='k001' --查询指定行
select * from Fruit where Price=2.4 and Numbers=65 --查行内所有信息(列指定条件)
select Name from Fruit where Numbers between 80 and 100--查行内名称(列指定条件)
select name from Fruit where Source in('烟台','广东')--只查指定地区的行内的名称
select distinct numbers from Fruit --去重查询数量(相同数量只显示一次)

select * from News
select * from News where title like '%大龄%'--模糊查询,查带大龄的信息(%代表很多字符)
select * from News where title like '要爱情%'--模糊查询,查以 要爱情开头的
select * from News where title like '%志在必得'--模糊查询,查以志在必得结尾的
select * from News where title like '%农业户_'--模糊查询,查农业户后只有一个字符的
select * from Fruit order by Numbers asc --按照数量升序排列(desc降序)
select * from Fruit order by Numbers desc,Price desc --按照数量降序排列,相同的在按价格降序排列

select * from Fruit --查询所有
select COUNT(*) from Fruit --返回fruit表里有多少条数据,有的企业为了节省资源 搜索 count(1)也可以,返回的是一个数字
select AVG(Price) as '平均价格' from Fruit --查询某一列的平均值,输出的是一个数字,列名用 平均价格 显示 SUM-数据和,MAX-最大值,MIN-最小值
select SUM(Numbers) from Fruit
select *,(price*0.8)as '折后价格' from Fruit --加一列数据库中没有的列,这里是加了一个8折后的价格列,显示为"折后价格"
select numbers,COUNT(1)from Fruit group by Numbers --根据某一列分组,求出该组内成员数量(根据number分组,相同number的被分为一组,并显示组内数量)
select Numbers,COUNT(*) from Fruit group by Numbers having COUNT(*)>1--根据numbers(列)分组,求出每组内成员数量,返回成员数大于1的组

--*******************************************************
use mydb
select * from student
select * from score
select * from teacher
select * from course
select * from car
--********************数学函数***********************
select Degree,SQRT(Degree) from Score--开平方,平方根
select Price,CEILING(Price) from Car--取大于该数的最小整数
select Price,FLOOR(Price) from Car --取小于该数的最小整数
select price,ROUND(Price,2) from Car--四舍五入,保留几位
select ABS(price) from Car--取绝对值
select RAND()*5+RAND()*10 --生成随机数,默认生成0-1之间的数,()内必须为空

--******************字符串函数****************************
select UPPER(Brand) from Car --转大写
select LOWER('WELCOME') --转小写
select LTRIM('        hello') --压缩左边的空格
select UPPER(RTRIM('no          '))--压缩右边的空格
select rtrim(Name)from Car 

select * from News
select LEFT(title,5) from News--从左侧截取字符串,截取n个字符
select RIGHT(title,2) from News--右侧截取(汉字占一个字符空间,符号也占一个字符空间)
select SUBSTRING(title,3,2) from News--从第三个位置开始截取2个字符(含第三个字符)
select REVERSE(title) from News --翻转字符串
select REPLACE(title,'*','xi*') from News--替换字符串,把*替换为xi*
select STUFF(title,5,4,'农村环境') from News--替换指定位置的字符串(从第5个字符(含)开始截取4个字符,替换为'农村环境')
select LEN(title) from News --查字符串长度(汉字,符号各占一个字符空间)

--*********************日期时间函数*************************
select * from Student
select YEAR(Sbirthday) from Student--取日期中的年份(month-月,day-天)
select GETDATE() --取当前系统时间
select DATEPART(YY,Sbirthday) from Student --取日期时间中的一部分

--类型转换
select CAST(0.22 as varchar(50)) --cast(源数据 as 目标类型)

--查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的一个同学成绩的Cno、Sno和Degree,并按Degree从高到低次序排序。(any:只要大于其中最小的数就满足)(all:大于其中最大的数)

select * from Score where Cno='3-105' and Degree >any(
select Degree from Score where Cno='3-245'
) order by Degree desc

--高级查询
use mydb
select * from Info
select * from Nation
select * from Family
select * from student
select * from score
select * from course

--连接查询(表与表)
select * from Info,Nation where Info.Nation=Nation.Code --通过外键连接2表

--join on 内连接
select * from Info join Nation on Info.Nation=Nation.Code

--查哪位同学的哪一门课考了多少分
select Student.Sname,Course.Cname,Score.Degree from Student join Score on Student.Sno=Score.Sno join Course on Course.Cno=Score.Cno

--右(左)连接,右边表必须显示全,如果左边表没有与之对应的信息,则补空值(null) 左连接把right 换成 left
select * from Info right join Nation on Info.Nation=Nation.Code

--全连接,左右两边的表都显示完全
select * from Info full join Nation on Info.Nation=Nation.Code

--联合查询,对于查出的两个或多个(结构相同)的表联合显示
select Code,Name from Info
union
select InfoCode,Name from Family

--子查询
select * from Info
----无关子查询，子查询执行是独立的，和父查询是没有关系的（没有用到父查询的东西）
select * from Info where YEAR(Birthday)=(
select year(Birthday) from Info where Code='p001'
)
--相关子查询
select * from Teacher
--求计算机系和电子工程系不同职称的老师信息(exists返回的是true或false,如果子查询中匹配正确的数量>0,返回结果为true,那么父循环中的该条查询保留)
select * from Teacher t1 where Depart='计算机系' and not exists( 
select * from Teacher t2 where Depart ='电子工程系'and t1.Prof=t2.Prof
)
union
select * from Teacher t1 where Depart='电子工程系'and not exists(
select * from Teacher t2 where Depart = '计算机系' and t1.Prof=t2.Prof
)
--查询除了每门课最高分之外的其他学生信息
select * from Score s1 where Degree not in(
select MAX(Degree) from Score s2 group by Cno having s1.Cno=s2.Cno
)
--分页
select * from Car

select top 5 * from Car --前5条数据,第一页
select top 5 * from Car where Code not in( --第二页数据
select top 5 Code from Car
)
select top 5 * from Car where Code not in( --第三页数据
select top 10 Code from Car
)
select CEILING(COUNT(*)/5) from Car --求总页数