1.mysql练习（基础理解）

1.创建表，初始数据

#创建年级表---1
drop table if exists grade; 
create table grade(
  gradeid int auto_increment primary key comment '年级id',
  gradename varchar(10) not null comment '年级名称'
) comment='年级表';

insert into grade(gradename) values('s1');#一次插一条记录`grade`
insert into grade(gradename) values('s2'),('y2');#一次插多条记录
#创建学生表---2
drop table if exists student; 
create table if not exists student(
  studentno int primary key comment'学生号',
  loginpwd varchar(20) not null comment '密码',
  studentname varchar(20) not null comment '学生姓名',
  sex char(1) default '男' not null comment '性别',
  gradeid int not null comment '年级id',
  phone  varchar(50) comment '电话号码',
  address varchar(255) comment'地址',
  borndate datetime not null comment'生日',
  email varchar(50) comment '邮箱',
  identitycard char(18)not null comment'身份证',
  constraint fk_grade_student foreign key(gradeid)
  references grade(gradeid)
)comment='学生表';

insert into student
(studentno,loginpwd,studentname,sex,gradeid,phone,address,
borndate,email,IdentityCard) 
values
(10000,'123123','郭靖','男',1,'12345678901','中国上海','1983-09-03','w@sina.com','1234567890'),
(10001,'123123','李文才',default,1,'12345678901',default,'1984-08-03','w@sina.com','1234567891'),
(10002,'123123','李斯文','男',1,'12345678901',default,'1985-09-03','w@sina.com','1234567892'),
(20011,'123123','张三','男',2,'12345678901',default,'1986-09-03','w@sina.com','1234567893'),
(20012,'123123','张秋丽','女',2,'12345678901',default,'1987-09-03','w@sina.com','1234567894'),
(30021,'123123','欧阳俊雄','男',3,'12345678901',default,'1988-09-03','w@sina.com','1234567895'),
(30022,'123123','梅超风','女',3,'12345678901',default,'2000-09-03','w@sina.com','1234567896');
#创建课程表course   subjectno,subjectname,gradeid,classhour---3
drop table if exists course;
create table if not exists course(
    subjectno int primary key auto_increment not null,
    gradeid int not null,
    subjectname varchar(20) not null,
    classhour int not null,
    constraint fk_grade_course foreign key(gradeid)
    references grade(gradeid)
);
insert into course
(subjectname,gradeid,classhour)
select 'java',1,100 union
select 'c#',1,60 union
select 'winform',1,70 union
select 'jsp',2,80 union
select 'asp.net',3,90;
/*
创建外键
外键表:subject,主键表:grade
*/

#创建成绩表score    studentno,subjectno,studentresult,examdate---4
drop table if exists result;
create table if not exists result(
    studentno int not null,
    subjectno int not null,
    studentresult int not null,
    examdate datetime not null default current_timestamp,
    primary key(studentno,subjectno,examdate),#补考有多个日期
    constraint fk_student_result foreign key(studentno)
    references student(studentno),
    constraint fk_course_result foreign key(subjectno)
    references course(subjectno)
);

/*复合主键*/

#插入subject表数据(union:联合,这是一个标准sql语句)

#向result表插入记录
insert into result
(studentno,subjectno,studentresult,examdate)
values
(10000,1,30,'2011/11/2'),
(10000,1,40,'2011/11/3') ,
(10000,1,55,'2011/11/4') ,
(10000,2,70,'2011/11/5') ,
(10000,4,60,'2011/11/4') ,
(10001,1,47,'2011/11/4') ,
(10002,1,70,'2011/11/4') ,
(20011,5,90,'2011/11/14');

/*
创建触发器,保证插入成绩表时,学员和课程的年级是相同的
*/
delimiter $;
drop trigger if exists t_insert_result$;
create trigger t_insert_result after insert 
on `result`
for each row 
begin
  declare g1 int;
  declare g2 int;
  set g1=(select gradeid from student  where studentno=new.studentno);
  set g2=(select gradeid from course  where subjectno=new.subjectno);
  if g1<>g2 then
    SIGNAL SQLSTATE 'HY000' 
    SET MESSAGE_TEXT ='学员的gradeid和课程的gradeid不匹配';
  end if;
end$;
delimiter ;
/*测试*/
insert into result
(studentno,subjectno,studentresult)
values(10000,5,80);#学员的gradeid和课程的gradeid不匹配
insert into result
(studentno,subjectno,studentresult)
values(80000,3,80);#Cannot add or update a child row: a foreign key constraint fails (`myschool`.`result`, CONSTRAINT `fk_student_result` FOREIGN KEY (`studentno`) REFERENCES `student` (`studentno`))

/*编写SQL语句实现从学生表提取##############     创建表
姓名、手机号两列数据存储到通讯录表(phoneList)中*/
#方式一:根据select的结果创建表
drop table if exists phoneList;
create table if not exists phoneList(
select studentname,phone from student);
#方式二:表结构首先要建好,使用insert into...select
drop table if exists phoneList;
create table phoneList
(
    stuname varchar(20) not null primary key,
    mobilephone varchar(50) 
);
insert into phoneList(stuname,mobilephone)
select studentname,phone from student;

#2创建种类表
CREATE TABLE IF NOT EXISTS category(
     categoryId int(10)  auto_increment  primary key,
     categoryName   varchar(32) not null ,
     pid  int(10)#父节点id
);
#插入数据
insert into category(categoryName,pid)
values
('软件开发',null),
('美术设计',null),
('数据库基础',1),
('PHP基础',1),
('一起学JAVA',1),
('photoshop基础',2),
('色彩搭配学',2);

#3创建卡表
create table card
(
    id int not null auto_increment primary key,
    passWord varchar(100) not null
);
insert into card(passWord)
values
('aaaaii11ii00bboocc'),('hhhhhh00oooiiijjj111');

#4创建销售记录表
drop table if exists sellRecord;
create table sellRecord
(
    id int not null auto_increment primary key,
    ListNumber varchar(100) not null
);
insert into sellRecord(ListNumber)
values
('13-1'),('13-2'),('14-1'),('14-11'),('13-3'),('13-10'),('13-100'),
('13-108'),('13-18'),('13-11'),('13-15'),('14-2'),('2-1');

2.查询表

    /*####            表组1     grade student course   result  
  #方法1
  触发器相关，不能插入
  #方法2
                                                                                  */
use myschool
#表   phonelist
#'李斯文'和'张秋丽'的地址改成'中国上海',密码改成'654321'
update student set address='中国上海',identitycard='654321' where studentname='李斯文' or studentname='张秋丽';
/*修改学号（StudentNo）为10000的学生记录
邮箱修改为student1013@bdqn.cn
密码（LoginPwd）修改为000000
*/
update student set email='student1013@bdqn.cn',loginpwd='000000' where studentno=10000;
/*将数据表subject中ClassHour
大于80且GradeID为1的课时都减少10
*/
 update course set classhour=classhour-10 where classhour>80 and gradeid=1;
#删除数据记录
#删除phoneList中'郭靖'的电话记录
delete from phoneList where studentname='郭靖';
#删除phoneList中姓张的电话记录
delete from phoneList where studentname like '张%';#------------------studentname='张%'   不报错离谱
#TRUNCATE语句删除全部电话记录
delete from phoneList;
#或
truncate phoneList;
#删除phoneList表对象
drop table if exists phoneList;

/*查询所有学生信息(所有列,效率低)*/
select * from student
/*可指定查询的结果数据列,如只查询student表中的学号、姓名、电话*/
select studentno,studentname,phone from student
/*区分连接查询时两个表有同名的字段,指定表的限定名,
连接本质上是个笛卡尔乘积(查询学生号,学生名,考试成绩)*/
#7*8=56行记录
select student.studentno,result.studentno,
studentname,studentresult from
student,result

    #8行效果
    #老写法
select student.studentno,result.studentno,
studentname,studentresult from
student,result where student.studentno=result.studentno;
    #新写法
select student.studentno,result.studentno,
studentname,studentresult from
student inner join result
on student.studentno=result.studentno;
/*AS子句作用:*/
#为列取别名(as也可以省略) 比如查询:姓名,生日
select studentname as `姓名`,borndate as `生日` from student
#为表取别名(as也可以省略) 比如查询:学生号,学生名,考试成绩
select student.studentno,result.studentno,
studentname,studentresult from
student as `学生` inner join result as `成绩`
on student.studentno=result.studentno;
#可把经计算或总结的结果用另外一个新名称来代替,比如("姓名-生日-电话"):

/*查询成绩表中的所包含的课程编号
(distinct:去掉SELECT查询返回的记录结果中重复的记录)*/
select distinct(subjectno) from course;
#在SQL语句中使用表达式
  #返回MySQL版本,100*3的计算结果,当前时间
select version(),100*3,now();
select version(),100*3,current_time;
  #给返回结果中的课时都加10个课时
select subjectno,subjectname,classhour+10 from course;
/*查询课程表（subject）的所有记录，返回数据
要求
返回字段名称使用别名
返回课程名称 (SujectName) 
课时(classHour)
返回7天上完课程的均课时(ClassHour/7)
*/
select subjectno
subjectname as 课程名称,
classhour as 课时,
floor(classhour/7) as 均课时
from course;

/*
案例  查询在80-90分之间的所有成绩记录
*/ 
select * from result where studentresult between 80 and 90;
#等同于
select * from result where studentresult>=80 and studentresult<=90;
#查询包含'a'的所有课程
select * from course where subjectname like '%a%';
#查询所有姓名为'张**'三个字的学生信息
select * from student where studentname like '张__';
#查询课时为60或80的课程名称
select subjectname from course where classhour=60 or classhour=80;
#等同于
select subjectname from course where classhour in(60,80);
/*
案例 查找地址不为null的学生信息
*/ 
select * from student where address is not null;`student`
#表连接
/*输出(姓名,课程名称,课程所在年级名称,考试成绩,考试时间)*/
#方式一  and  老，不推荐
select studentname,subjectname,gradename,studentresult,examdate
from student,course,grade,result
where student.studentno=result.studentno 
and course.subjectno=result.subjectno
and course.gradeid=grade.gradeid;
#方式二  join,推荐使用,inner可略 
select studentname,subjectname,gradename,studentresult,examdate
from student inner join result
on student.studentno=result.studentno 
join course
on course.subjectno=result.subjectno
join grade
on course.gradeid=grade.gradeid;

/*查询所有姓“李”的学生所有成绩
(学生编号,学生姓名,课程名称,课程所属年级名称,成绩)*/
select student.studentno,studentname,subjectname,gradename,studentresult
from student inner join result
on student.studentno=result.studentno 
join course
on course.subjectno=result.subjectno
join grade
on course.gradeid=grade.gradeid
where studentname like '李%';
/*列出所有的学生的考试成绩,没有参加过考试的已要列出信息
(学生姓名,学生成绩,考试时间)
左连接*/#以左边的表为主  outer可略
select studentname,studentresult,examdate
from student left outer join result
on student.studentno=result.studentno;
#查询没有参加过考试的学生姓名
#1.子查询-----

#2.表连接
select * from student left outer join result
on student.studentno=result.studentno
where studentresult is null;

select studentname from student left outer join result
on student.studentno=result.studentno
where result.studentno is null;
#查询出生日期在1987年之后的S2的学生姓名和生日
select studentname,borndate from student join grade
on student.gradeid=grade.gradeid
where borndate>='1987-01-01' and gradename='S2';
/*查询参加了日期为2011-11-04的'java'科目考试的成绩信息
(学生姓名,学生成绩)*/
select studentname,studentresult from student join result
on student.studentno=result.studentno join course
on result.subjectno=course.subjectno
where examdate='2011-11-04' and subjectname='java';

#查询年龄超过30周岁的Y2的学生信息
select studentname,year(current_timestamp)-year(borndate)from student join grade
on student.gradeid=grade.gradeid
where gradename='y2' and year(current_timestamp)-year(borndate)>30;
#查询8月份过生日的学生信息
select studentname from student where month(borndate)=8;
#查询今天过生日的学生姓名及所在年级名称
select studentname,gradename from student join grade
on student.gradeid=grade.gradeid
where month(borndate)=month(current_timestamp)
and day(borndate)=day(current_timestamp);
#查询学号为10000的学生Email的域名
select substring(email,locate('@',email)+1) from student where studentno=10000;
#等同
select insert(email,1,locate('@',email),'') from student where studentno=10000;

#查询单列排序
#升序(查询学员考试成绩) asc,为默认
select * from result order by studentresult;
#降序(查询学员考试成绩)  
select * from result order by studentresult desc; 
#按多列排序(按课程名称升序,考试成绩降序),
#输出(姓名,课程名称,考试成绩,考试时间)
select studentname,studentresult,examdate from student
join result
on student.studentno=result.studentno 
join course
on course.subjectno=result.subjectno
order by subjectname,studentresult desc;