数据库 查询 修改 删除 增加 排序 等命令

数据库查询修改删除增加排序等命令

insert into Register values('张三') //添加语句

select*from Register //查询语句查询整个表

delete from Register where Loadname='张三' //删除Register 表中的Loadname字段=‘张三’的行信息

select*from UserLoad order by id desc/*利用ID进行排序desc降序排序将UserLoad中的所有id都进行排序

select*from UserLoad order by id asc利用ID进行排序asc 升序排序

alter table UserLoad drop column Text 删除Userload表中

Text字段这列

select count(*)from Register where sex='男' 查找Register 表中sex=‘男’的所有人数

select BrithDay,Phone from Register where Loadname='张三' 在Register表中查询Loadname='张三'的BrithDay和Phone

update UserLoad set Text='11111'where id='1' 修改UserLoad表中ID=’1’的Text文本内容

select Passworld from Register where Loadname='张三' 在Register表中查找Loadname=张三的密码

id int identity(1,1)//使ID值由1自动增加1

当字段的长度大于5个字符在其后加．．．．

select case when len(FCatalog)>5 then

LEFT(FCatalog,5)+'.....' else FCatalog end

from Fiction

多表查询

/*查询所有选课学生的学号，姓名，课程号，成绩*/ select stu.sno,sname,cno,score from stu inner join sc on stu.sno=sc.sno

/*查询所有选课学生的姓名，课程名，成绩*/ select sname,cname,score from stu inner join sc inner join course

on https://www.360docs.net/doc/bc2128279.html,o=https://www.360docs.net/doc/bc2128279.html,o

on sc.sno=stu.sno

1.查询姓名为jack的学生选修的课程号。

/*1.多表查询*/

select cno from sc,stu

where sc.sno=stu.sno and sname='jack'

/*2.子查询*/

select cno from sc where sno=

(select sno from stu where sname='jack')

思考：查询姓名为jack的学生选修的课程名

/*1.多表查询*/

select cname from course,sc,stu

where sc.sno=stu.sno and https://www.360docs.net/doc/bc2128279.html,o=https://www.360docs.net/doc/bc2128279.html,o

and sname='jack'

/*2.子查询*/

select cname from course where cno in

(select cno from sc where sno =

(select sno from stu where sname='jack'))

2.查询所有选课学生的学号、姓名、选课名称及成绩。

3.查询每个选课学生的学号、姓名、平均成绩。

select stu.sno,sname,avg(score) as avg_score

from stu,sc where stu.sno=sc.sno

group by stu.sno,sname

4.查询每门课程的课程名和考试平均成绩

select cname,avg(score) as 平均成绩 from course,sc where https://www.360docs.net/doc/bc2128279.html,o=https://www.360docs.net/doc/bc2128279.html,o

group by cname

5.求选修C1课程学生的平均年龄

select avg(age) as 平均年龄 from stu

where sno in(select sno from sc where cno='c1') 6.求LI老师所授课程的每门课程的课程名和平均成绩

select cname,avg(score) as 平均成绩 from course,sc where https://www.360docs.net/doc/bc2128279.html,o=https://www.360docs.net/doc/bc2128279.html,o and teacher='li'

group by cname

7. 查询与jack同学年龄相同的学号、姓名。

where age=(select age from stu where sname='jack')

8.查询学号比mary同学大，而年龄比他小的学生姓名select sname from stu

where sno>(select sno from stu where sname='mary') and age<(select age from stu where sname='mary')

9.求年龄大于女同学平均年龄的男同学姓名和年龄

select sname,age from stu

where sex='M' and age>

(select avg(age) from stu where sex='W')

10.查询和Rom同学选修了一样课程的学生的学号，姓名。select sno,sname from stu where sno in

(select sno from sc where cno in

(select cno from sc where sno=

(select sno from stu where sname='Rom'))

group by sno having count(cno)=

(select count(cno) from sc where sno=

(select sno from stu where sname='Rom'))

)

11.查询没有被选修课程的课程号和课程名。

select cno,cname from course

where cno not in (select cno from sc)

查询没有选课的学生的学号和姓名

where sno not in(select sno from sc)

设置约束条件

Constraint pk_表名_字段名 primary key (字段名)

Constraint uk_表名_字段名 unique (字段名)

Constraint ck_表名_字段名check (字段>数and 字段<数)

Default 只能用于列级约束

Constraint def_表名_字段 default ‘值’

查询

where 字段（no）in (1,9)

Where 字段 (no) between 1 and 9

Select 字段，字段 from 表名Where 字段>1 and 字段< 9

Where 字段 >1 or 字段 < 1

Where 字段1<>10 and 字段2 <>20字段1不是10并且字段2不是20

Where 字段 like ‘%’查找所有的

Select * from 表名Where 字段 like ‘%s%’含有s的内容

where 字段 like '[A-D]%s__' 首字符是A-D任意数并且倒数第三个数要是s select * from 表名order by 字段

排序 select * from 表名order by 字段1，字段2 desc

select * from 表名order by 字段1 asc

select top 5 字段 from 表名前5条记录

显示条数 select top 5 percent 字段 from 表名order by字段desc 按5%的比例显示