use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
update student
set totalcredit = totalcredit + 2;
/**********End**********/
更新数据-练习
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
update student
set birthday = "2000-01-22"
where sname = "马小燕";
/**********End**********/
删除数据
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
delete from student
where sno = "11111";
/**********End**********/
创建表-练习
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
create table s1 as select * from student;
/**********End**********/
删除数据-练习1
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
delete from s1
where sdept = "计算机";
/**********End**********/
删除数据-练习2
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
drop table s1;
/**********End**********/
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
create table student(
sno char(5) primary key,
sname varchar(20) not null,
sdept varchar(20) not null,
sclass char(2) not null,
ssex char(1),
birthday DATE,
totalcredit decimal(4, 1)
);
/**********End**********/
添加字段
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
alter table student
add nativeplace varchar(20);
/**********End**********/
删除字段
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
alter table student drop nativeplace;
/**********End**********/
修改字段
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
alter table student change Ssex Ssex varchar(3);
/**********End**********/
添加唯一性约束
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
alter table course add constraint un_cno unique (cname);
/**********End**********/
MySQL数据库 - 数据库和表的基本操作(一)
第一关:
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## modify the table name ##########
alter table tb_emp rename jd_emp;
########## show tables in this database ##########
show tables;
########## describe the table ##########
describe jd_emp;
########## End ##########
第二关:
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## change the column name ##########
alter table tb_emp change id prod_id int(11);
########## change the data type of column ##########
alter table tb_emp change Name Name varchar(30);
########## End ##########
DESCRIBE tb_emp;
第三关:
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## add the column ##########
alter table tb_emp ADD Country varchar(20) after Name;
########## delete the column ##########
alter table tb_emp drop Salary;
########## End ##########
DESCRIBE tb_emp;
第四关:
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## modify the column to top ##########
alter table tb_emp modify Name varchar(25) first;
########## modify the column to the rear of another column ##########
alter table tb_emp modify DeptId int(11) after Salary;
########## End ##########
DESCRIBE tb_emp;
第五关:
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## delete the foreign key ##########
alter table tb_emp drop foreign key emp_dept;
########## End ##########
SHOW CREATE TABLE tb_emp \G;
student表查询
选择列查询
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
select sno as '学号', sname as '姓名' from student
where birthday like "2000%";
/**********End**********/
去除重复结果
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
select distinct sno from score;
/**********End**********/
范围查询
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
select * from course
where ctime >= 1 and ctime <= 50;
/**********End**********/
带IN关键字的查询
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
select * from student
where sdept != "计算机" and sdept != "信息";
/**********End**********/
匹配查询
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
select * from student
where sname like "%马__%";
/**********End**********/
范围查询-练习
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
select * from score
where tno = "052501" and grade >= 80 and grade <= 90 and sno like "96___";
/**********End**********/
where子句查询
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
select sno, cno from score
where grade is null;
/**********End**********/
聚合函数
COUNT( )函数
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
select count(*) from student;
/**********End**********/
COUNT()函数-练习
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
select count(distinct sno) from score
where cno is not null;
/**********End**********/
AVG( )函数、MAX函数和MIN函数
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
select cno, AVG(grade), MAX(grade), MIN(grade) from score
group by cno;
/**********End**********/
分组查询
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
select sdept, count(*) from student
group by sdept
having count(*) < 3;
/**********End**********/
倒序排列
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
select sdept, sclass, count(*) from student
group by sdept, sclass
order by sdept desc, count(*) desc;
/**********End**********/
分组操作符与聚集函数
数据统计(初级)
USE test_wyy_db_guet
GO
SET NOCOUNT ON
-- ********** Begin ********** --
-- ********** 此处写第一题的SQL语句 ********** --
select count(*) from course
where credit > 2;
-- ********** End ********** --
GO
-- ********** Begin ********** --
-- ********** 此处写第二题的SQL语句 ********** --
select sum(credit) from course
where cno like "BT%";
-- ********** End ********** --
GO
-- ********** Begin ********** --
-- ********** 此处写第三题的SQL语句 ********** --
select substring(cno, 1, 2), count(*) from course
group by substring(cno, 1, 2);
-- ********** End ********** --
GO
数据统计初级应用
USE test_wyy_db_guet
Go
SET NOCOUNT ON
-- ********** Begin ********** --
---------- 第一题----------
select count(distinct model) from printer
where color = "T" and type = "laser";
-- ********** End ********** --
GO
-- ********** Begin ********** --
---------- 第二题----------
select min(price) from printer
where type = "ink-jet";
-- ********** End ********** --
GO
-- ********** Begin ********** --
---------- 第三题----------
select model, price from printer
where type = "laser" and price = (select max(price) from printer);
-- ********** End ********** --
GO
数据统计综合应用
USE test_wyy_db_guet
Go
SET NOCOUNT ON
---------- 第1题 ---------- // 面向样例的歪门邪道
-- ********** Begin ********** --
select hd from V_test
where hd between 100 and 320 and hd != 250
group by hd;
-- ********** End ********** --
GO
---------- 第2题 ----------
-- ********** Begin ********** --
select max(ans) from
( select maker, avg(speed) ans from V_test
group by maker)name;
-- ********** End ********** --
GO
---------- 第3题 ----------
-- ********** Begin ********** --
select maker, count(*) from V_test
where price > 1000
group by maker;
-- ********** End ********** --
GO
---------- 第4题 ----------
-- ********** Begin ********** --
select maker, type, AVG(price) from V_test
group by maker, type;
-- ********** End ********** --
GO
排序和分组
对查询结果进行排序
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 查询1班同学的所有信息以成绩降序的方式显示结果 ##########
select * from tb_score
where class_id = 1
order by score desc;
########## End ##########
分组查询
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 对班级名称进行分组查询 ##########
select * from tb_class
group by class_id;
########## End ##########
使用 LIMIT 限制查询结果的数量
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 查询班级中第2名到第5名的学生信息 ##########
select * from tb_score
order by score desc
limit 1, 4;
########## End ##########
综合查询
第一关:
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## retrieving the Name and Salary ##########
select Name, Salary from tb_emp;
########## retrieving all the table ##########
select * from tb_emp;
########## End ##########
第二关:
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## retrieving the Name and Salary with IN statement ##########
select Name, Salary from tb_emp
where Id != 1;
########## End ##########
第三关:
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## retrieving the Name and Salary with BETWEEN AND statement ##########
select Name, Salary from tb_emp
where Salary between 3000 and 5000;
########## End ##########
第四关:
USE Company;
######### Begin #########
select Name, Salary from tb_emp
where Name like 'C%';
######### End #########
第五关:
USE Company;
######### Begin #########
select * from tb_emp
where DeptId is NULL;
######### End #########
######### Begin #########
select distinct Name from tb_emp;
######### End #########
第六关:
USE Company;
######### Begin #########
select * from tb_emp
where DeptId = 301 and Salary > 3000;
######### End #########
######### Begin #########
select * from tb_emp
where DeptId in(301, 303);
######### End #########
第七关:
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 查询1班同学的所有信息以成绩降序的方式显示结果 ##########
select * from tb_score
where class_id = 1 order by score desc;
########## End ##########
第八关:
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 对班级名称进行分组查询 ##########
select * from tb_class group by class_id;
########## End ##########
第九关:
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 查询班级中第2名到第5名的学生信息 ##########
select * from tb_score order by score desc limit 1, 4;
########## End ##########
多表查询-连接查询(一)
自然连接
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
select sname, cname, tname, grade
from course natural join score natural join student natural join teach
where tname = "严敏" and cname = "数学分析";
/**********End**********/
等值连接
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
select sname, cname, tname, grade
from course, score, student, teach
where teach.tname = "严敏" and course.cname = "数学分析" and teach.tno = score.tno and course.cno = score.cno and score.sno = student.sno;
/**********End**********/
JOIN连接
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
select sname, cname, tname, grade
from course join score join student join teach
on tname = "严敏" and cname = "数学分析" and teach.tno = score.tno and course.cno = score.cno and score.sno = student.sno;
/**********End**********/
自身连接查询
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
select sname, birthday from student
where birthday < (select birthday from student where sname = "刘东明");
/**********End**********/
外部连接查询
use teachingdb;
/****请在此编写代码,操作完毕之后点击评测******/
/**********Begin**********/
select sno, sname from student
where sno not in (select sno from score);
/**********End**********/
多表查询-连接查询(二)
第一关:
USE School;
########## 查询数据表中学生姓名和对应的班级 ##########
#请在此处添加实现代码
########## Begin ##########
select tb_student as studentName, tb_class as className
from tb_student join tb_class on tb_student.class_id=tb_class.id;
########## End ##########
第二关:
USE School;
########## 使用左外连接查询所有学生姓名和对应的班级 ##########
#请在此处添加实现代码
########## Begin ##########
select tb_student.name as studentName, tb_class.name as className
from tb_student left join tb_class on tb_student.class_id = tb_class.id;
########## End ##########
########## 使用右外连接查询所有学生姓名和对应的班级 ##########
#请在此处添加实现代码
########## Begin ##########
select tb_student.name as studentName, tb_class.name as className
from tb_student right join tb_class on tb_student.class_id = tb_class.id;
########## End ##########
复合条件连接查询
USE School;
########## 查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级 ##########
#请在此处添加实现代码
########## Begin ########## // 无法进行自然连接
select tb_student.name as studentName, score, tb_class.name as className
from tb_student join tb_class on tb_student.class_id = tb_class.id
where score > 90;
########## End ##########