--创建表格

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,'hujintao','xijinping') from News--替换字符串,把hujintao替换为xijinping

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 --求总页数