create database student;
use student;
--建立学生表
create table student(
sno char(8) not null primary key, --学生学号
sname char(8) not null, --学生姓名
ssex char(2) null, --学生性别
sage smallint null, --学生出生日期
sdept char(10) default '软件系', --学生所在院系，默认为‘软件系’
classno char(4) null, --班级号
home varchar(40) null, --学生家庭住址
tel varchar(40) null --学生联系电话
);
--删除一个表
drop table student;
-- 删除一列
--刚开始有默认值约束，所以得先把约束删掉，再执行
-- sp_help student; --找到默认约束名
--alter table student drop DF__student__sdept__00551192; --删除默认约束
alter table student drop column sdept; -- 删除一列
select * from student;
insert into student values('s012','jack','女',22,null,null,'888888',null,'jingguan');
alter table student add sdept char(10) default '软件系';--新增之后默认值为空，不能用insert，该用update
--insert into student (sno,sdept) values('s001','Ruanjian');
update student set sdept='jsjs' where sno='s004'
select * from student
select sno,sname from student where sdept is null --查询空的系别
select sno,sname from student where sdept is not null --查询非空的系别
update student set sdept='Ruanjian' where sno='s001'
update student set sdept='Ruanjian' where sno='s002'
update student set sdept='Ruanjian' where sno='s003'
update student set sdept='Ruanjian' where sno='s005'
update student set sdept='Ruanjian' where sno='s006'
update student set sdept='Ruanjian' where sno='s007'
update student set sdept='Ruanjian' where sno='s008'
update student set sdept='Ruanjian' where sno='s009'
update student set sdept='Ruanjian' where sno='s010'
update student set sdept='Ruanjian' where sno='s011'
--转换
select lower (sdept) as 系 from student where sno='s001'
select upper (sdept) as 系 from student where sno='s001'
--建立课程表
create table course(
cno char(10) not null primary key, --课程编号
cname char(20) not null, --课程名称
experient smallint null, --实验学时
lecture smallint null, --授课学时
cpno char(10) null , --先修课（外键）
semester smallint null, --开课学期
credit smallint null --课程学分
);
--对course表的cpno增加外键约束
alter table course add constraint fkey foreign key (cpno) references course(cno);
alter table course drop fkey;
--建立选课表
create table student_course(
sno char(8) not null , --学生学号（外键）
cno char(10) not null, --课程编号（外键）
grade smallint null, --学生成绩
primary key(sno,cno),
foreign key(sno) references student(sno),
foreign key(cno) references course(cno)
);
-- 1）向student表增加“辅导员”列, 其数据类型为char(10)；
alter table student add 辅导员 char(10);
-- 2）增加课程名称必须取唯一值的约束条件；
alter table course add constraint unq unique(cname);
-- 3）删除所在系的默认值约束；go，你可以把它看作一个句号，当上面这一段代码与下面的代码没有任何关系的时候，可以用一个go，表示这一段代码的终止
--use 数据库名
--go
--sp_help 表名
--go
--先删除约束，在添加默认约束
alter table course drop unq;
alter table course add unique(cname);
alter table course drop UQ__course__08EA5793;
-- 4）增加所在系必须取“信息系”、“软件系”或“计算机系”的约束；
alter table student add constraint unq check(sdept in ('信息系','软件系','计算机系'));
alter table student drop unq;
-- 5）更改列名和表名； exec与execute一样吗？
execute sp_rename student2,student;
-- 6）为course表按开课学期建立索引；
create index cus on course(cpno);
-- 7）删除索引 DROP INDEX <索引名> ON <表名>；
drop index cus on course ;
--向student表中插入10条数据
insert into student values('s001','miao','男',20,'软件系','s1','puyang','15518509475','daoyuan');
insert into student values('s002','miao','男',20,'软件系','s1','puyang','15518509475','daoyuan');
insert into student values('s003','miao','男',20,'软件系','s1','puyang','15518509475','daoyuan');
insert into student values('s004','miao','男',20,'软件系','s1','puyang','15518509475','daoyuan');
insert into student values('s005','miao','男',20,'软件系','s1','puyang','15518509475','daoyuan');
insert into student values('s006','miao','男',20,'软件系','s1','puyang','15518509475','daoyuan');
insert into student values('s007','miao','男',20,'软件系','s1','puyang','15518509475','daoyuan');
insert into student values('s008','miao','男',20,'软件系','s1','puyang','15518509475','daoyuan');
insert into student values('s009','miao','男',20,'软件系','s1','puyang','15518509475','daoyuan');
insert into student values('s010','miao','男',20,'软件系','s1','puyang','15518509475','daoyuan');
select * from student;
update student set sdept='信息系' where sno='s002';
update student set sdept='体育系' where sno='s003';
update student set sdept='数学系' where sno='s004';
update student set sdept='音乐系' where sno='s005';
update student set sdept='舞蹈系' where sno='s006';
update student set sdept='播音系' where sno='s007';
update student set sdept='主持系' where sno='s008';
update student set sdept='财经系' where sno='s009';
update student set sage=21 where sno='s001';
update student set sage=22 where sno='s002';
update student set sage=23 where sno='s003';
update student set sage=24 where sno='s004';
update student set sage=25 where sno='s005';
update student set sage=26 where sno='s006';
update student set sage=27 where sno='s007';
update student set sage=28 where sno='s008';
update student set sage=29 where sno='s009';
update student set sage=30 where sno='s010';
--向course表插入8条数据
insert into course values('c001','Richard',10,20,'离散',1,2);
insert into course values('c002','Richard1',11,21,'离散',1,3);
insert into course values('c003','Richard2',12,22,'离散',1,4);
insert into course values('c004','Richard3',13,23,'离散',1,5);
insert into course values('c005','Richard4',14,24,'离散',1,6);
insert into course values('c006','Richard5',15,25,'离散',1,7);
insert into course values('c007','Richard6',16,26,'离散',1,8);
insert into course values('c008','Richard7',17,27,'离散',1,9);
select * from course;
--向学生选课成绩表插入30条数据
insert into student_course (sno,cno,grade) values('s001','c001',33);
insert into student_course (sno,cno,grade) values('s002','c002',20);
insert into student_course (sno,cno,grade) values('s003','c003',30);
insert into student_course (sno,cno,grade) values('s004','c004',40);
insert into student_course (sno,cno,grade) values('s005','c005',50);
insert into student_course (sno,cno,grade) values('s006','c006',60);
insert into student_course (sno,cno,grade) values('s007','c007',70);
insert into student_course (sno,cno,grade) values('s008','c008',80);
select * from student_course;
--选择所有的学号
select all sno from student;
select * from student;
insert into student values('s011','miaozai','女',22,'信息系','s2','puy','111112222','jack');
select all sdept from student;
select distinct classno,sdept from student; --去重
select sage,sname,sno from student where sage='22';
select 2013-sage from student;
select 2013-sage as birthday from student;
select 2013-sage birthday from student;
select sno from student order by sno; --默认升序 把空看成无穷大
select sno from student order by sno desc;、
select count(sno) mm from student;
select count( sdept) mm from student; --默认是all
select count(distinct sdept) mm from student;
--查询经过计算的值
select sname,2013-sage as 出生年月 from student;
select sname,2013-sage birthday,lower(sdept) 系别 from student;
--查询全体学生的学号与姓名；
select sno,sname from student;
--查询全体学生的姓名、学号、所在系；
select sname,sno,sdept from student;
--查询全体学生的详细记录；
select * from student;
-- 查询全体学生的姓名及其出生年份；
select sname,2013-sage birthday from student;
--查询选修了课程的学生学号；
select sno from student_course;
--查询信息系全体学生的姓名；
select sname from student where sdept='信息系';
-- 查询所有年龄在25岁以下的学生姓名及其年龄；
select sname,sage from student where sage<25;
-- 查询考试成绩有不及格的学生的学号；
select sno,grade from student_course where grade<60;
--查询年龄在20~25岁（包括20岁和25岁）之间的学生的姓名、所在系和年龄；
select sname,sdept,sage from student where sage between 20 and 25;
-- 查询年龄不在20~25岁（包括20岁和25岁）之间的学生的姓名、所在系和年龄；
select sname,sdept,sage from student where sage<20 or sage>25;
-- 查询信息系、软件系和计算机系学生的姓名和性别；
select sname,ssex,sdept from student where sdept='信息系' or sdept='软件系' or sdept='计算机系';
-- 查询既不是信息系、软件系, 也不是计算机系的学生的姓名和性别；
select sname,ssex,sdept from student where sdept not in('信息系' ,'软件系','计算机系');
-- 查询学号为xxxxxxx的学生的详细情况；
select * from student where sno='s001';
-- 查询所有姓m学生的姓名、学号和性别； 一定要用like匹配
select sname,sno,ssex from student where sname like 'm___';
select sname,sno,ssex from student where sname like 'm%';
select * from student;
-- 查询所有不姓m的学生姓名；
select sname,sno,ssex from student where sname not like 'm___';
-- 查询名字中第2个字为"i"字的学生的姓名和学号；
select sname,sno from student where sname like '_i%';
-- 查询所有学生的信息，查询结果按学号降序排列；
select * from student order by sno desc;
-- 查询选课学生人数；
select count(sno) from student_course;
-- 查询学号为s002的学生的平均成绩；
select avg(grade ) 平均成绩 from student_course where sno='s002';
select * from student_course;
select avg(grade ) 平均成绩 from student_course;--总体的平均成绩
-- 查询选修003号课程的学生最高分数；
select max(grade) 最高分from student_course where sno='s002';
-- 求各个课程号及相应的选课人数；
select cno,count(*) from student_course group by cno;
select * from student_course;
-- 求每个（学生）学号及其选课门数；
select sno,count(*) from student_course group by sno;
-- 查询选修了2门以上课程的学生学号； 凡是经过计算的用having，不需要的用where,
--HAVING短语与WHERE子句的区别: 作用对象不同
--WHERE子句作用于基表或视图, 从中选择满足条件的元组.
--HAVING短语作用于组, 从中选择满足条件的组.
select sno from student_course group by sno having count(*)>1;
--查询有4门以上课程在90分以上的学生学号。
select sno from student_course where grade>=10 group by sno having count(*)>1;
select grade from student_course;
--SELECT [ALL|DISTINCT] <目标列表达式> [, <目标列表达式>] …
--FROM <表名或视图名>[, <表名或视图名> ] …
--[ WHERE <条件表达式> ]
--[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
--[ ORDER BY <列名2> [ ASC|DESC ] ]；
alter table student add constraint notnull check(tel is not null); --增加一个非空约束
alter table student drop notnull --删除约束
alter table student add constraint notnull default '1111' for tel; --增加一个默认值约束
alter table student drop notnull;--删除约束
create index suoyin on student(tel); --创建一个索引
drop index suoyin on student; --删除一个索引
--连接查询
select * from student
select * from student_course
--两个表连接
select student.*,student_course.* from student,student_course where student.sno = student_course.sno;
--三个表连接
select student.*,student_course.*,course.* from student,student_course,course where student.sno = student_course.sno and student_course.cno = course.cno;
--查询选修了课程名Richard1的学生学号和姓名
select student.sno,sname
from student,course,student_course
where student.sno = student_course.sno
and student_course.cno = course.cno
and course.cname='Richard1';
--如果有group by 子句，那么select后面一定要跟group by 属性 或 集函数（max，min，avg。。。）
--Employees(EmployeeID,RirstName,LastName,Title,Address)
--Customers(CustomerID,CompanyName,PostalCode,Country)
--Orders(OrderID,CustomerID,EmployeeID,Freight)
--查询每个职位为sales的雇员名字及订单数
--select LastName,count(OrderID) from EmployeeID,Orders where title='sales' and Employees.EmployeeID=Orders.EmployeeID group by LastName;
--如果有group by 子句，那么select后面一定要跟group by 属性 或 集函数（max，min，avg。。。）
--查询每个顾客所在国家为‘UK’的雇员编号、公司名称及订单运费平均值
--select EmployeeID,CompanyName,avg(Freight) --第三步
--from Customers,Orders ,Employees --第一步
--where Employees.EmployeeID=Orders.EmployeeID --第二步
-- and Orders.CustomerID=Customers.CustomerID
-- and Country='UK'
-- group by EmployeeID,CompanyName --第四步
