sql server数据库基本操作语句复习

数据库基本操作语句复习

建立数据库
Create database labdb
ON
(
name=labdb,
filename=‘e:\sql_db\labdb’,
size=10MB, maxsize=50MB,
filegrowth=10MB
)
LOG ON
(
name=labdb_ldf,
filename=‘e:\sql_db\labdb_ldf’,
size= 10MB, maxsize=50MB,
filegrowth=10%
)
建立表格：
Create table workshop
(
workshop_no char(4) not null primary key,
workshop_manager varchar(16) not null ,
workshop_addr varchar(60) not null ,
workshop_phone varchar(11) not null,
workshop_cost char(8) not null,
factory_no char(4) not null,
foreign key (factory_no) references factory(factory_no)
)
插入数据：
insert into tb_stu(stu_id,stu_name,stu_sex,stu_age)
select ‘1003’,‘小明’,‘男’,‘12’ union
select ‘1004’,‘小飞’,‘女’,‘43’ union
select ‘1005’,‘小华’,‘女’,‘15’ union
select ‘1006’,‘小婷’,‘女’,‘31’
修改数据：
UPDATE tb_stu SET gender = ‘男’ WHERE id = 4
建立视图：
create view [productVeiw] as
select *
from product
where product_cost <=100 and product_price >=200
建立索引：
create index productIndex
on product(product_no desc,product_size,product_price desc,workshop_no desc)

基本查询语句：

如：查询选修了课程代码是045239且成绩在80分(含80分)以上的学生的学号、姓名、性别、课程名、年龄、成绩。

SELECT s.sno,sname,sex,course_name,year(getdate())-year(birthday) AS age,score
FROM student AS s,course AS c,select_course AS sc
WHERE s.sno=sc.sno AND sc.course_no=c.course_no AND sc.course_no='045239' AND score>=80

如： 查询选修了高等数学所有同学的学号、姓名、性别、院系名称、专业、出生日期和年级，并在查询结果中将列名显示为：学号、姓名、性别、系名、专业、出生日期、年级。

SELECT s.sno 学号,sname 姓名,sex 性别,department 系名,major_name 专业,birthday 出生日期,class 年级
FROM student AS s,major AS m,select_course AS sc,course AS c
WHERE s.major_no=m.major_no AND sc.course_no=c.course_no AND s.sno=sc.sno AND c.course_name LIKE '高等数学%'

如：查询至少选修了两门以上课程的学生的学号、姓名和所在班级。

SELECT s.sno,sname,class
FROM student AS s,course AS c,select_course AS sc
WHERE s.sno=sc.sno AND c.course_no=sc.course_no
GROUP BY s.sno,sname,class HAVING count(c.course_no)>=2

查询平均成绩在70分以上(含70分) 的学生学号、选课门数、最高成绩、
最低成绩和平均成绩，并在查询结果中将列名显示为：学号、选课门数、最高成绩、最低成绩、平均成绩。

use sql_db
select sno '学号',count(course_no) '选课门数', max(score) '最高成绩',avg(score) '平均成绩' from select_course
group by sno having(avg(score)>=70) 

//group :分组 having：用来过滤由GROUP BY语句返回的记录集。

嵌套查询：

查询软件工程专业选修了指定课程且成绩在80分及以上的学号、姓名、性别、级别、年龄、成绩。（提示：指定课程代码通过局部变量实现）

declare @cname char(8)
set @cname='无机化学'
SELECT s.sno,sname,sex,s.school_year,year(getdate())-year(birthday) AS 年龄,score
FROM student AS s,select_course AS sc,major AS m
WHERE s.sno=sc.sno AND s.major_no=m.major_no AND major_name LIKE '软件工程%' AND score>=80 AND course_no IN(
	SELECT course_no
	FROM course
	WHERE course_name=@cname
)

筛选相同记录下日期最小的一条：

use Test 
select * from tb_data as a 
where a.时间 in (select min(b.时间) from data as b where b.姓名=a.姓名 and b.公司=a.公司)