头歌MySQL数据库实训答案（新版本超全） 有目录
本文档提供了MySQL数据库实训的完整答案，涵盖基础查询、子查询、连接查询、聚合函数等多个模块。内容包括单表查询（如基本查询、范围查询、模糊匹配等）、子查询（比较运算符和关键字查询）、连接查询（内连接、外连接）以及聚合函数（COUNT、SUM、AVG等）的实际应用示例。每个实训关卡都配有具体的SQL代码实现，适合作为MySQL学习参考。
·
头歌MySQL数据库实训答案（新版本超全） 有目录
文章目录
第一类：Mysql基础
单表查询
第一关：基本查询语句
USE Company;
#请在此处添加实现代码
########## Begin ##########
select Name,Salary from tb_emp;
select * from tb_emp;
########## End ##########
第二关：带 IN 关键字的查询
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## retrieving the Name and Salary with IN statement ##########
SELECT Name,Salary FROM tb_emp WHERE Id NOT IN (1);
########## End ##########
第三关：带 BETWEEN AND 的范围查询
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 ##########
第四关：带 LIKE 的字符匹配查询
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 #########
第六关：带 AND 与 OR 的多条件查询
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 #########
第七关：使用 LIMIT 限制查询结果的数量
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 查询班级中第2名到第5名的学生信息 ##########
select * from tb_score order by score desc limit 1,4;
########## End ##########
子查询
第一关：带比较运算符的子查询
USE Company;
#请在此处添加实现代码
########## Begin ##########
#1.查询大于所有平均年龄的员工姓名与年龄
select name,age from tb_emp where age>(select avg(age)from tb_emp);
########## End ##########
第二关：关键字子查询
USE Company;
#请在此处添加实现代码
########## Begin ##########
SELECT position,salary FROM tb_salary WHERE salary > ANY(SELECT max(salary) FROM tb_salary where position='java');
#2.使用 ANY 关键字进行查询
SELECT position,salary FROM tb_salary WHERE salary > ANY(SELECT min(salary) FROM tb_salary where position='java');
#3.使用 IN 关键字进行查询
select position,salary from tb_salary where position in('java');
########## End ##########
连接查询
第一关：内连接查询
USE School;
########## 查询数据表中学生姓名和对应的班级 ##########
#请在此处添加实现代码
########## Begin ##########
select tb_student.name as studentName,tb_class.name as className from tb_student join tb_class on tb_class.id = tb_student.class_id;
########## End ##########
第二关：外连接查询
USE School;
########## 使用左外连接查询所有学生姓名和对应的班级 ##########
#请在此处添加实现代码
########## Begin ##########
select tb_student.name as studentName,tb_class.name as className
from tb_class right join tb_student on
tb_class.id=tb_student.class_id;
########## End ##########
########## 使用右外连接查询所有学生姓名和对应的班级 ##########
select tb_student.name as studentName,tb_class.name as className
from tb_class left join tb_student
on tb_class.id=tb_student.class_id;
#请在此处添加实现代码
########## Begin ##########
########## End ##########
第三关：复合条件连接查询
USE School;
########## 查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级 ##########
#请在此处添加实现代码
########## Begin ##########
select s1.name as studentName,score,
s2.name as className from tb_student as s1,
tb_class as s2 where s1.class_id=s2.id and
s1.score>90 order by score desc;
########## End ##########
使用聚合函数查询
第一关：COUNT()函数
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 查询该表中一共有多少条数据 ##########
select count(*) from tb_class;
########## 查询此表中367班有多少位学生 ##########
select classid,count(*) from tb_class where classid=367;
########## End ##########
第二关：SUM（）函数
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 查询所有学生总分数 ##########
select sum(score) from tb_class;
########## 查询学生语文科目的总分数 ##########
select course,sum(score) from tb_class where course='语文';
########## End ##########
第三关：AVG（）函数
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 查询学生语文科目的平均分数 ##########
select course,avg(score) from tb_class where course='语文';
########## 查询学生英语科目的平均分数 ##########
select course,avg(score) from tb_class where course='英语';
########## End ##########
第四关：MAX（）函数
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 查询语文课程中的最高分数 ##########
select course,max(score) from tb_class where course='语文';
########## 查询英语课程中的最高分数 ##########
select course,max(score) from tb_class where course='英语';
########## End ##########
第五关：MIN（）函数
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 查询语文课程中的最低分数 ##########
select course,min(score) from tb_class where course='语文';
########## 查询英语课程中的最低分数 ##########
select course,min(score) from tb_class where course='英语';
########## End ##########
其他函数的使用
第一关：字符函数
#请在此添加实现代码
########## Begin ##########
select CONCAT(UPPER(SUBSTR(Name,1,1)),LOWER(SUBSTR(Name,2,LENGTH(Name)))) as Name from employee;
########## End ##########
第二关：数字函数
#请在此添加实现代码
########## Begin ##########
update Score set s_score=TRUNCATE(s_score-(round(sqrt((power(4,4)-power(3,3))/power(2,2)),2)),2);
########## End ##########
第三关：日期函数和流程控制类函数
#请在此添加实现代码
########## Begin ##########
########## 查询学生出生年份及年龄 ##########
select year(s_birth) year,'2019-01-01'-s_birth '年龄'
from Student;
########## 查询课程的最高分、最低分、平均分和及格率 #########
select c.c_id '课程id',
c_name '课程名',
max(s_score) '最高分',
min(s_score) '最低分',
round(avg(s_score),2) '平均分',
round((count(s_score >= 60 or null)/count(s_score)) * 100,2) '及格率'
from Score s,Course c
where s.c_id=c.c_id
group by s.c_id;
########## End ##########
第四关：自定义函数
#请在此添加实现代码
########## Begin ##########
delimiter //
create function fn_three_max(param_1 int,param_2 int,param_3 int) RETURNS int
BEGIN
DECLARE max_val int DEFAULT 0;
if param_1 > param_2 then
set max_val=param_1;
else
set max_val=param_2;
end if;
if param_3 > max_val then
set max_val=param_3;
end if;
return max_val;
END
//
########## End ##########
复杂查询
第一关：分数排名
#请在此添加实现代码
########## Begin ##########
########## 编写情况一 SQL ##########
select Score,(select count(distinct score) from score where score >=s.score) as Rank
from score as s
order by Score desc;
########## 编写情况二 SQL ##########
select Score,(select count(*) from score as s2 where s2.score >s1.score)+1 as Rank
from score as s1
order by Rank;
########## End ##########
第二关：体育馆人流量
#请在此添加实现代码
########## Begin ##########
SELECT DISTINCT a.* FROM gymnasium as a,gymnasium as b,gymnasium as c
WHERE(a.visitors_flow >= 100 AND b.visitors_flow >= 100 AND c.visitors_flow >= 100)
AND(
(a.id = b.id-1 AND b.id = c.id-1 )or
(a.id = b.id-1 AND a.id = c.id+1 )or
(a.id = b.id+1 AND b.id = c.id+1 )
) ORDER BY id;
########## End ##########
第三关：统计总成绩
########## Begin ##########
select c.classname,
sum(case when chinese> 60 then chinese else 0 End) chinese,
sum(case when maths > 60 then maths else 0 end) maths
from tb_score s, tb_class c
where c.stuname=s.name
group by classname;
########## End ##########
第四关：查询学生平均分
########## Begin ##########
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2)as avg_score from student b inner join score a on b.s_id = a.s_id
GROUP BY b.s_id,b.s_name HAVING avg_score <60
union
select a.s_id,a.s_name,0 as avg_score from student a
where a.s_id not in (select distinct s_id from score);
########## End ##########
第五关：查询修课相同学生信息
########## Begin ##########
select *
from student
where s_id in ("02","03","04");
########## End ##########
第六关：查询各科成绩并排序
########## Begin ##########
select a.s_id,a.c_id,a.s_score,count(b.s_score)+1 rank from score a left join score b
on a.c_id = b.c_id and a.s_score <b.s_score
group by a.s_id,a.c_id,a.s_score
order by a.c_id,a.s_score desc,count(b.s_score),a.s_id desc;
########## End ##########
第七关：查询张老师课程成绩最高的学生信息
########## Begin ##########
select a.*,b.s_score,b.c_id,c.c_name from student a
INNER JOIN score b ON a.s_id = b.s_id
INNER JOIN course c ON b.c_id=c.c_id
where b.c_id=(select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name='张三')
and b.s_score in (select MAX(s_score)from score where c_id='02');
########## End ##########
第八关：查询两门课程不及格同学信息
########## Begin ##########
select a.s_id,a.s_name,ROUND(AVG(b.s_score))avg_score from
student a
INNER JOIN score b ON a.s_id=b.s_id
where a.s_id in (select s_id from score where s_score<60
GROUP BY s_id having count(*)>=2)
GROUP BY a.s_id,a.s_name;
########## End ##########
第九关：交换工资
########## Begin ##########
update tb_Salary set sex = if(sex = 'f','m','f') where id in(1,2,3,4,5,6);
########## End ##########
第十关：换座位
########## Begin ##########
select
case id
when 1 then 2
when 2 then 1
when 3 then 4
when 4 then 3
when 5 then 5
else 0
end id,name from tb_Seat order by id asc;
########## End ##########
分组选择数据
第一关：GROUP BY 与 聚合函数
USE School;
#请在此处添加实现代码
########## Begin ##########
#1.查询表中2,3,4年级中分别男女的总人数
select gradeId,sex,count(*)
from student where gradeId in (2,3,4)
group by gradeId,sex;
########## End ##########
第二关
USE School;
#请在此处添加实现代码
########## Begin ##########
#1.查询表中至少有两门课程在90分以上的学生信息
select sno,count(*)from tb_grade
where score >=90
group by sno having count(pno) >= 2;
#2.查询表中平均成绩大于90分且语文课在95分以上的学生信息
select sno,avg(score) from tb_grade where sno
in(select sno from tb_grade where score >=95 and pno = "语文")
group by sno having avg(score) >=90;
########## End ##########
数据库和表的基本操作
第一关：查看表结构与修改表名
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## modify the table name ##########
alter table tb_emp rename jd_emp;
########## show tables in this database ##########
show tables;
########## describe the table ##########
desc 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 modify 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;
第六关：插入数据
USE Company;
########## Begin ##########
########## bundle insert the value ##########
insert into tb_emp(id,name,deptId,salary) values(1,"Nancy",301,2300.00),(2,"Tod",303,5600.00),(3,"Carly",301,3200.00);
########## End ##########
SELECT * FROM tb_emp;
第七关：更新数据
USE Company;
########## Begin ##########
########## update the value ##########
update tb_emp
set name = "Tracy",deptId=302,salary=4300.00
where id = 3;
########## End ##########
SELECT * FROM tb_emp;
第八关：删除数据
USE Company;
########## Begin ##########
########## delete the value ##########
delete from tb_emp
where salary > 3000.00;
########## End ##########
SELECT * FROM tb_emp;
MySql开发技巧
视图
use School;
#请在此处添加实现代码
########## Begin ##########
#1.创建单表视图
CREATE VIEW stu_view
AS
select math,chinese,math+chinese
FROM student;
#2.创建多表视图
CREATE VIEW stu_classes
AS
select student.stu_id,student.name,stu_info.classes
FROM student,stu_info
WHERE student.stu_id=stu_info.stu_id;
########## End ##########
索引
use School;
#请在此处添加实现代码
########## Begin ##########
#1.创建名为pk_student的主键索引
create table student(
stu_id int not null,
name varchar(25) not null,
age int not null,
sex char(2) not null,
classes int not null,
grade int not null,
primary key(stu_id)
);
#2.创建名为idx_age的普通索引
create index idx_age on student(age);
#3.创建名为uniq_classes的唯一索引
create unique index uniq_classes on student(classes);
#4.创建名为idx_group的组合索引
alter table student add index idx_group(name,sex,grade);
########## End ##########
分页和索引
第一关：MySQL 分页查询
USE Products;
#请在此处添加实现代码
########## Begin ##########
#1.分页查询
select prod_id from products where prod_id >(select prod_id from products limit 4,1) limit 5;
#2.用子查询优化分页查询语句
select prod_id from products where prod_id >(select prod_id from products limit 9,1) limit 5;
########## End ##########
第二关：索引（单列索引）
USE Students;
#请在此处添加实现代码
########## Begin ##########
#1.创建student表结构并且设置id为主键索引
CREATE TABLE student (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
score int(10),
PRIMARY KEY (id)
);
#2.对name建立唯一索引
CREATE UNIQUE INDEX name_index ON `student`(`name`);
#3.对score建立普通索引
CREATE INDEX score_index ON `student`(`score`);
SHOW INDEX FROM student;
########## End ##########
第三关：索引（组合索引）
USE Person;
#请在此处添加实现代码
########## Begin ##########
#1.增加组合索引
ALTER TABLE person ADD INDEX name_city_score (name,age,address);
########## End ##########
SHOW INDEX FROM person;
行列转换
第一关：使用 CASE 语句实现行转列
#请在此添加实现代码
########## Begin ##########
select s_name,
SUM(case c_name when '语文' then s_score end) '语文',
SUM(case c_name when '数学' then s_score end) '数学',
SUM(case c_name when '英语' then s_score end) '英语'
from score
group by s_name;
########## End ##########
第二关：序列化表的方法实现列转行（一）
#请在此添加实现代码
########## Begin ##########
select b.name,
substring_index(replace(substring(substring_index(b.scores,',',s.id),char_length(substring_index(b.scores,',',s.id-1))+1),',',''),':',1) course,
substring_index(replace(substring(substring_index(b.scores,',',s.id),char_length(substring_index(b.scores,',',s.id-1))+1),',',''),':',-1) score
from tb_sequence s inner join
(select name,scores as course,scores,length(scores)-length(replace(scores,',',''))+1 size
from tb_score) b
on s.id <= b.size;
########## End ##########
第三关：序列化表的方法实现列转行（二）
#请在此添加实现代码
########## Begin ##########
select s_name,
case when s.id=1 then '语文'
when s.id=2 then '数学'
when s.id=3 then '英语'
end s_cource,
coalesce(
case when s.id=1 then chinese end,
case when s.id=2 then math end,
case when s.id=3 then english end
)
s_score
from tb_score t
inner join tb_sequence s
where s.id <= 3
order by s_name,field(s_cource,'数学','英语','语文');
########## End ##########
删除重复数据
第一关：利用主键删除
#请在此添加实现代码
########## Begin ##########
delete from users where id in (
select * from (
select id from users where user_name
in (
select user_name from users group by user_name having count(1) > 1
)
and id not in (
select min(id) from users group by user_name having count(1) > 1
)
) as stu_repeat_copy
);
########## End ##########
第二关：复杂重复数据删除
#请在此添加实现代码
########## Begin ##########
update users b join (
select user_name,group_concat(
distinct SUBSTRING_INDEX(SUBSTRING_INDEX(mobile,',',t.id),',',-1)
) mobile from (
select user_name,mobile,length(concat(mobile,','))-length(replace(mobile,',','')) size from users
) a inner join tb_sequence t on a.size>=t.id group by a.user_name
) c on b.user_name = c.user_name set b.mobile = c.mobile;
########## End ##########
批量数据入库及检索
第一关：MySQL数据库连接
coding=utf-8
import pymysql
def connect():
请在下面添加连接数据库的代码，完成相应功能
###### Begin
conn = pymysql.connect(host='localhost', user='root',passwd='123123',charset='utf8')
####### End #######
####### 请不要修改以下代码 #######
return conn.get_host_info()
第二关：数据库与数据表创建
# coding = utf-8
# 连接数据库，建立游标cursor
import pymysql
def create():
conn = pymysql.connect(host='localhost', user='root', passwd='123123', charset='utf8')
cursor = conn.cursor()
# -----------Begin----------
# 创建enroll数据库
cursor.execute('create database enroll')
conn.select_db('enroll')
# 创建nudt数据表
cursor.execute('create table nudt(year int, province varchar(100), firstBatch int )')
# ------------End-----------
第三关：批量数据入库与检索
import pymysql
def insert(year,province,firstBatch,gcMax,gcMin,gcMean,xlMax,xlMin,xlMean):
conn = pymysql.connect(host='localhost', user='root', passwd='123123', charset='utf8')
cursor = conn.cursor()
conn.select_db('enroll')
# -----------Begin----------
# 请在下面输入插入数据的语句，完成相应功能
sql = 'insert into nudt(year,province,firstBatch,gcMax,gcMin,gcMean,xlMax,xlMin,xlMean) values (%s,"%s",%s,%s,%s,%s,%s,%s,%s)' % (year,province,firstBatch,gcMax,gcMin,gcMean,xlMax,xlMin,xlMean)
cursor.execute(sql)
# ------------End-----------
# 提交数据到数据库
conn.commit()
# 关闭数据库连接
cursor.close()
conn.close()
def select():
conn = pymysql.connect(host='localhost', user='root', passwd='123123', charset='utf8')
cursor = conn.cursor()
conn.select_db('enroll')
# -----------Begin----------
# 请在下面输入查询数据的语句，完成相应功能
sql = 'select * from nudt'
cursor.execute(sql)
# 请在下面输入获取数据的语句，完成相应功能
records = cursor.fetchall()
# ------------End-----------
for record in records:
print(record)
# 关闭数据库连接
cursor.close()
conn.close()
第四关：多表创建与连接查询
i
mport pymysql
def create(cursor):
# -----------Begin----------
# 创建provincialEntryScore表
sql = 'create table provincialEntryScore(year int, province varchar(100), entryScore int)'
cursor.execute(sql)
# 创建nudtTechScore表
sql = 'create table nudtTechScore(year int, province varchar(100), techMax int, techMin int, techMean int)'
cursor.execute(sql)
# 创建nudtMilScore表
sql = 'create table nudtMilScore(year int, province varchar(100), milMax int, milMin int, milMean int)'
cursor.execute(sql)
# ------------End-----------
def insert(cursor,year,province,entryScore,techMax,techMin,techMean,milMax,milMin,milMean):
# -----------Begin----------
# 请在下面输入将数据插入provincialEntryScore表中的语句
sql = 'insert into provincialEntryScore(year, province, entryScore) values (%s, "%s", %s)' % (year, province, entryScore)
cursor.execute(sql)
# 请在下面输入将数据插入nudtTechScore表中的语句
sql = 'insert into nudtTechScore(year, province, techMax, techMin, techMean) values (%s, "%s", %s, %s, %s)' % (year, province, techMax, techMin, techMean)
cursor.execute(sql)
# 请在下面输入将数据插入nudtMilScore表中的语句
sql = 'insert into nudtMilScore(year, province, milMax, milMin, milMean) values (%s, "%s", %s, %s, %s)' % (year, province, milMax, milMin, milMean)
cursor.execute(sql)
# ------------End-----------
def selectAll(cursor):
# -----------Begin----------
# 请在下面输入多表直接汇总的语句
sql = 'select * from provincialEntryScore, nudtTechScore, nudtMilScore'
cursor.execute(sql)
records = cursor.fetchall()
return records
# ------------End-----------
def selectEqual(cursor):
# -----------Begin----------
# 请在下面输入等值连接的语句
sql = 'select * from provincialEntryScore A,nudtTechScore B,nudtMilScore C where A.year = B.year and A.year = C.year and A.province = B.province and A.province = C.province'
cursor.execute(sql)
records = cursor.fetchall()
return records
# ------------End-----------
def selectNatural(cursor):
# -----------Begin----------
# 请在下面输入自然连接的语句
sql ='select A.year, A.province, A.entryScore, B.techMax, B.techMin, B.techMean, C.milMax, C.milMin, C.milMean from provincialEntryScore A, nudtTechScore B, nudtMilScore C where A.year = B.year and A.year = C.year and A.province = B.province and A.province = C.province'
cursor.execute(sql)
records = cursor.fetchall()
return records
# ------------End----------
并发控制
第一关：表锁
use School;
#请在此处添加实现代码
########## Begin ##########
insert into student values(1,'Tom',80,78);
insert into student values(3,'Lucy',97,95);
lock table student read;
update student set math=100 where stu_id = 2;
########## End ##########
第二关：事务隔离级别
use mydb;
#请在此处添加实现代码
########## Begin ##########
#1.修改隔离级别
set session transaction isolation level Read uncommitted;
#2.查询隔离级别
select @@tx_isolation;
########## End ##########
第三关：行锁
存储过程
USE mydb;
#请在此处添加实现代码
########## Begin ##########
delimiter //
create PROCEDURE GetCustomerLevel(in p_customNumber int(11),out p_customerLevel varchar(10))
Begin
declare level int;
select creditlimit into level from customers where customerNumber = p_customNumber;
if level < 5000 then
set p_customerLevel = 'SILVER';
elseif level <= 10000 then
set p_customerLevel = 'GOLD';
else
set p_customerLevel = 'PLATINUM';
end if;
select p_customNumber as customerNumber,p_customerLevel;
End //
delimiter ;
########## End ##########
查询、索引和完整性
第一关：基本查询的学习
//请在下面补齐查询一的MySQL语句
/*********begin*********/
select ename,eid,sex from emp
where did in
(select did from dept
where dname='cwb'
)
/*********end*********/
and
birth<=all
(select birth from emp
where did in
(select did from dept
where dname='yfb'
)
);
//请在下面输入查询二的MySQL语句
/*********begin*********/
select ename,income,outcome
from emp,sal,dept
where emp.eid=sal.eid and
emp.did=dept.did and
dname='cwb' and income>5200;
/*********end*********/
第二关：深入学习查询语句
//请在下面输入查询一的MySQL语句
/*********begin*********/
select count(eid)
from emp
where did=
(select did
from dept
where dname='cwb');
/*********end*********/
//请在下面输入查询二的MySQL语句
/*********begin*********/
select count(eid)
from emp
group by did;
/*********end*********/
//请在下面输入查询三的MySQL语句
/*********begin*********/
select emp.ename
from emp,sal
where emp.eid=sal.eid
order by income;
/*********end*********/
第三关：视图的创建和使用
//请在下面输入创建cx_sal的视图的MySQL语句
/*********begin*********/
create or replace view cx_sal
as
select ename,income,outcome
from emp,sal,dept
where emp.eid=sal.eid and
emp.did=dept.did and
dname='cwb';
/*********end*********/
//请在下面输入查询财务部雇员薪水情况视图的MySQL语句
/*********begin*********/
select * from cx_sal;
/*********end*********/
第四关：索引与完整性
//请在下面输入创建索引的MySQL语句
/*********begin*********/
create index pk_xs_bak
on emp(eid);
/*********end*********/
//请在下面输入实现域完整性的MySQL语句
/*********begin*********/
alter table emp
add(constraint ch_tel check(tel between 0 and 9));
/*********end*********/
//请在下面输入实现实体完整性的MySQL语句
/*********begin*********/
alter table dept
add constraint un_dept unique(dname);
/*********end*********/
//请在下面输入实现参照完整性的MySQL语句
/*********begin*********/
alter table emp
add constraint sal_id foreign key(eid)
references sal(eid);
/*********end*********/
数据库的事务
-- 使用数据库
USE books_db;
-- 删除旧表
DROP TABLE IF EXISTS tbl_user;
-- 创建新表
CREATE TABLE tbl_user (
user_id INT(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
username_name VARCHAR(50) DEFAULT NULL COMMENT '姓名',
sex CHAR(50) DEFAULT NULL COMMENT '性别',
sumMoney DOUBLE(11,0) DEFAULT NULL COMMENT '电总金额',
PRIMARY KEY (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=84 DEFAULT CHARSET=utf8;
-- 插入初始数据
INSERT INTO tbl_user VALUES (1, '张三', '男', '100');
INSERT INTO tbl_user VALUES (2, '李四', '女', '100');
-- 开启事务，进行转账操作
START TRANSACTION;
-- 张三减25
UPDATE tbl_user SET sumMoney = sumMoney - 25 WHERE username_name = '张三';
-- 李四加25
UPDATE tbl_user SET sumMoney = sumMoney + 25 WHERE username_name = '李四';
-- 提交事务
COMMIT;
-- 查询结果验证
SELECT * FROM tbl_user;
第三类：Mysql数据库设计
数据库开发基础案例—JDBC技术应用
第一关：数据库连接与数据库实例创建
package step1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class MySQLWithJDBC {
//指定数据库驱动
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
//设定本地数据库的URL，并指定编码方式为UTF-8且关闭SSL连接
static final String DB_URL = "jdbc:mysql:///?useUnicode=true&characterEncoding=utf-8&useSSL=false";
// 指定数据库的用户名和密码
static final String USER = "root";
static final String PASS = "123123";
// 创建数据库实例
public void createDatabaseInstance(Connection connection, String databaseName){
Statement stmt = null;
// 请补全下面创建数据库实例的SQL语句sqlScript
String sqlScript = "create database " + databaseName;
try {
stmt = (Statement) connection.createStatement();
stmt.executeUpdate(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 建立与指定数据库的连接，并返回该连接
public Connection getConnection(String jdbc_driver, String db_url, String db_user, String db_passwd) {
Connection connection = null;
// 注册JDBC驱动
try {
Class.forName(jdbc_driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
// 创建于指定数据库的连接
connection = DriverManager.getConnection(db_url, db_user, db_passwd);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//删除数据库
public void dropDatabase(Connection connection, String databaseName){
Statement stmt = null;
String sqlScript = "drop database if exists " + databaseName;
try {
stmt = (Statement) connection.createStatement();
stmt.executeUpdate(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 获取数据库中已经存在的数据库实例
public ResultSet getExistDB(Connection connection, String databaseName) throws SQLException
{
ResultSet resultSet = null;
Statement stmt = null;
/******* Begin ******/
String sqlScript = "SELECT * FROM information_schema.SCHEMATA where SCHEMA_NAME=\"" + databaseName + "\"";
/******* End ******/
try {
stmt = (Statement) connection.createStatement();
resultSet = stmt.executeQuery(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
return resultSet;
}
}
第二关：数据表的创建
package step2;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class CreateTable {
// 设定JDBC驱动以及本地数据库的URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql:///bookstore?useUnicode=true&characterEncoding=utf-8&useSSL=false";
// 指定数据库的用户名和密码
static final String USER = "root";
static final String PASS = "123123";
/**
* 在指定数据库中创建以tableName命名的表
* @param connection mysql连接对象
* @param tableName 数据表名
* @param tableInfo 存放表的字段及其属性的二维数组，如tableInfo[k][0]代表第k个字段名，tableInfo[k][1]代表第k个字段的属性
*/
public void createTable(Connection connection, String tableName,String[][] tableInfo){
Statement stmt = null;
//请在此处补全创建表的SQL语句，不要改动其他代码
/******* Begin ******/
String sqlScript = "create table " + tableName + "(\n";
for(int k=0; k<tableInfo.length; k++){
if(tableInfo[k][0].equals(""))
break;
sqlScript = sqlScript + tableInfo[k][0] + " " + tableInfo[k][1] + ",\n";
}
sqlScript = sqlScript.substring(0, sqlScript.length()-2);
sqlScript = sqlScript + ")";
/******* End ******/
try {
//创建查询语句对象
stmt = connection.createStatement();
//执行查询
stmt.executeUpdate(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 建立与指定数据库的连接，并返回该连接
public Connection getConnection(String jdbc_driver, String db_url, String db_user, String db_passwd) {
Connection connection = null;
//注册JDBC驱动
try {
Class.forName(jdbc_driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//创建于指定数据库的连接
try {
connection = (Connection) DriverManager.getConnection(db_url, db_user, db_passwd);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//获取databaseNames数据库中的tablenName表
public ResultSet getExistTable(Connection conn,String databaseName,String tableName) {
ResultSet resultSet = null;
Statement stmt = null;
String sqlScript = "select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA=\'" +databaseName +
"\' and TABLE_NAME=\'" + tableName + "\'";
try {
stmt = (Statement) conn.createStatement();
resultSet = stmt.executeQuery(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
return resultSet;
}
//加载sql脚本
List<String> loadSql(String sqlFile) throws Exception {
List<String> sqlList = new ArrayList<String>();
try {
InputStream sqlFileIn = new FileInputStream(sqlFile);
StringBuffer sqlSb = new StringBuffer();
byte[] buff = new byte[1024];
int byteRead = 0;
while ((byteRead = sqlFileIn.read(buff)) != -1) {
sqlSb.append(new String(buff, 0, byteRead));
} // Windows 下换行是 \r\n, Linux 下是 \n
String[] sqlArr = sqlSb.toString().split("(;\\s*\\r\\n)(;\\s*\\n)");
for (int i = 0; i < sqlArr.length; i++) {
String sql = sqlArr[i].replaceAll("--.*", "").trim();
if (!sql.equals("")) {
sqlList.add(sql);
}
}
return sqlList;
} catch (Exception ex) {
throw new Exception(ex.getMessage());
}
}
}
第三关：数据查询操作
package step3;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class QueryRecord {
// 设定JDBC驱动以及本地数据库的URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql:///bookstore?useUnicode=true&characterEncoding=utf-8&useSSL=false";
// 指定数据库的用户名和密码
static final String USER = "root";
static final String PASS = "123123";
/**
*在tableName中查询出书名为bookName的出版社
* @param Connection 数据库连接
* @param tableName 数据表名
* @param bookName 书名
*/
public ResultSet queryPublisherByBookName(Connection connection, String tableName,String bookName){
Statement stmt = null;
ResultSet result = null;
// 请补全下面的查询表的SQL查询命令
String sqlScript = "select distinct(publisher) from " + tableName + " where title = \'" + bookName + "\'";
try {
stmt = connection.createStatement();
result = stmt.executeQuery(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
// 建立与指定数据库的连接，并返回该连接
public Connection getConnection(String jdbc_driver, String db_url, String db_user, String db_passwd) {
Connection connection = null;
//注册JDBC驱动
try {
Class.forName(jdbc_driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//创建于指定数据库的连接
try {
connection = (Connection) DriverManager.getConnection(db_url, db_user, db_passwd);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
}
第四关：数据库的插入操作
package step4;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
public class InsertTable {
// 设定JDBC驱动以及本地数据库的URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/bookstore?useUnicode=true&characterEncoding=utf-8&useSSL=false";
// 指定数据库的用户名和密码
static final String USER = "root";
static final String PASS = "123123";
/**
* 向指定表中插入数据
* @param connection 数据库连接对象
* @param id 记录的id
* @param title 书名
* @param author 作者
* @param publisher 出版社
* @param year 出版年份
*/
public void InsertRecord(Connection connection, int id, String title, String author, String publisher, int year){
//在此添加插入数据的操作
Statement stmt = null;
String sqlScript = "Insert into book(id,title,author,publisher,publishYear)" + " values" + "(" + id +"," +"\'" + title+ "\',\'" + author + "\',\'" + publisher + "\',\'" + year + "\')";
try {
stmt = connection.createStatement();
stmt.executeUpdate(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 建立与指定数据库的连接，并返回该连接
public Connection getConnection(String jdbc_driver, String db_url, String db_user, String db_passwd) {
Connection connection = null;
//注册JDBC驱动
try {
Class.forName(jdbc_driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//创建于指定数据库的连接
try {
connection = (Connection) DriverManager.getConnection(db_url, db_user, db_passwd);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public ResultSet queryDB(Connection connection,String tableName) {
ResultSet result = null;
Statement stmt = null;
String sqlScript = "select * from " + tableName + " order by id desc";
try {
stmt = connection.createStatement();
result = stmt.executeQuery(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
List<String> loadSql(String sqlFile) throws Exception {
List<String> sqlList = new ArrayList<String>();
try {
InputStream sqlFileIn = new FileInputStream(sqlFile);
StringBuffer sqlSb = new StringBuffer();
byte[] buff = new byte[1024];
int byteRead = 0;
while ((byteRead = sqlFileIn.read(buff)) != -1) {
sqlSb.append(new String(buff, 0, byteRead));
} // Windows 下换行是 \r\n, Linux 下是 \n
String[] sqlArr = sqlSb.toString().split("(;\\s*\\r\\n)(;\\s*\\n)");
for (int i = 0; i < sqlArr.length; i++) {
String sql = sqlArr[i].replaceAll("--.*", "").trim();
if (!sql.equals("")) {
sqlList.add(sql);
}
}
return sqlList;
} catch (Exception ex) {
throw new Exception(ex.getMessage());
}
}
}
第五关：数据表的删除操作
package step5;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DeleteRecord {
// 设定JDBC驱动以及本地数据库的URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql:///bookstore?useUnicode=true&characterEncoding=utf-8&useSSL=false";
// 指定数据库的用户名和密码
static final String USER = "root";
static final String PASS = "123123";
/**
* 根据作者名删除某条记录
* @param connection 数据库连接
* @param tableName 数据表名
* @param author 作者名
*/
public int deleteRecordByAuthor(Connection connection, String tableName,String author){
// 请在此处添加数据库删除记录的实现代码
Statement stmt = null;
int updatedNum = 0;
// 删除数据表中数据的SQL语句
String sqlScript = "delete from " + tableName + " where author = \'" + author +"\'";
try {
stmt = connection.createStatement();
updatedNum = stmt.executeUpdate(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
return updatedNum;
}
// 建立与指定数据库的连接，并返回该连接
public Connection getConnection(String jdbc_driver, String db_url, String db_user, String db_passwd) {
Connection connection = null;
//注册JDBC驱动
try {
Class.forName(jdbc_driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//创建于指定数据库的连接
try {
connection = (Connection) DriverManager.getConnection(db_url, db_user, db_passwd);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//查询数据库
public ResultSet queryDB(Connection connection,String tableName) {
ResultSet result = null;
Statement stmt = null;
String sqlScript = "select * from " + tableName + " order by id desc";
try {
stmt = connection.createStatement();
result = stmt.executeQuery(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
}
第六关：数据表的更新操作
package step6;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.io.*;
import org.apache.tools.ant.*;
import org.apache.tools.ant.taskdefs.*;
import org.apache.tools.ant.types.*;
public class UpdateRecord {
// 设定JDBC驱动以及本地数据库的URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql:///bookstore?useUnicode=true&characterEncoding=utf-8&useSSL=false";
// 指定数据库的用户名和密码
static final String USER = "root";
static final String PASS = "123123";
/**
* 更新数据库表中的数据
* @param connection 数据库连接
* @param tableName 数据库表名
* @param title 书名
* @param publisher 出版社
*/
public int updatePublisherByTitle(Connection connection,String tableName, String title, String publisher){
//请在此处实现数据更新功能
Statement stmt = null;
int updatedNum=0;
String sqlScript = "update " + tableName + " set publisher=\'" + publisher + "\'where title=\'" + title + "\'";
try {
stmt = connection.createStatement();
updatedNum = stmt.executeUpdate(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
return updatedNum;
}
// 建立与指定数据库的连接，并返回该连接
public Connection getConnection(String jdbc_driver, String db_url, String db_user, String db_passwd) {
Connection connection = null;
//注册JDBC驱动
try {
Class.forName(jdbc_driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//创建于指定数据库的连接
try {
connection = (Connection) DriverManager.getConnection(db_url, db_user, db_passwd);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//查询数据库
public ResultSet queryDB(Connection connection,String tableName) {
ResultSet result = null;
Statement stmt = null;
String sqlScript = "select * from " + tableName + " order by id desc";
try {
stmt = connection.createStatement();
result = stmt.executeQuery(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
}
第七关：数据库的应用
package step7;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.spi.DirStateFactory.Result;
public class TransferTable {
// 设定JDBC驱动以及本地数据库的URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql:///bookstore?useUnicode=true&characterEncoding=utf-8&useSSL=false";
// 指定数据库的用户名和密码
static final String USER = "root";
static final String PASS = "123123";
public void transferTable(Connection connection, String tableName) throws SQLException{
//请在此将转换表结构的函数补充完整。
ResultSet result = null;
Statement stmt = null;
String sqlScript = "select * from " + tableName + " order by id desc";
try {
stmt = connection.createStatement();
result = stmt.executeQuery(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
String sqlInsert = "insert into transformedBook (id,column_name,value) values (";
while (result.next()) {
String sql = "";
String id = result.getString("id");
String title=result.getString("title");
if(title != null)
{
sql = sqlInsert + id + ",'" + "title" + "','" + title + "');";
InsertRecord(connection, sql);
}
String author = result.getString("author");
if(author != null)
{
sql = sqlInsert + id + ",'" + "author" + "','" + author + "');";
InsertRecord(connection, sql);
}
String publisher = result.getString("publisher");
if(publisher != null)
{
sql = sqlInsert + id + ",'" + "publisher" + "','" + publisher + "');";
InsertRecord(connection, sql);
}
String publishYear = result.getString("publishYear");
if(publishYear != null)
{
sql = sqlInsert + id + ",'" + "publishYear" + "','" + publishYear + "');";
InsertRecord(connection, sql);
}
}
}
//插入数据
public void InsertRecord(Connection connection,String sqlScript){
Statement stmt = null;
try {
stmt = connection.createStatement();
stmt.executeUpdate(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
}
//查询数据库
public ResultSet queryDB(Connection connection, String tableName){
Statement stmt = null;
ResultSet result = null;
String sqlScript = "select * from " + tableName;
try {
stmt = connection.createStatement();
result = stmt.executeQuery(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
// 建立与指定数据库的连接，并返回该连接
public Connection getConnection(String jdbc_driver, String db_url, String db_user, String db_passwd) {
Connection connection = null;
//注册JDBC驱动
try {
Class.forName(jdbc_driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//创建于指定数据库的连接
try {
connection = (Connection) DriverManager.getConnection(db_url, db_user, db_passwd);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
}
数据库开发实训（Python Web框架）
第一关：查询操作
config.py
class Config(object):
#连接数据库
###### Begin ######
SQLALCHEMY_DATABASE_URI = "mysql+pymysql://root:123123@localhost:3306/web"
SQLALCHEMY_TRACK_MODIFICATIONS = True
###### End ######
models.py
from app import db
class Message(db.Model):
#表模型
#********* Begin *********#
id = db.Column(db.Integer,primary_key=True)
ct = db.Column(db.Integer)
provincename = db.Column(db.String(255))
cityname = db.Column(db.String(255))
#********* End *********#
test.py
from app import db,app
from models import Message
from flask import render_template
@app.route("/select")
def select():
# ********* Begin *********#
pro = Message.query.order_by(Message.ct.desc()).all()
city = list(map(lambda x: x.cityname, pro))
count = list(map(lambda x: x.ct, pro))
province = list(map(lambda x: x.provincename, pro))
# ********* End *********#
return render_template("index.html",city=city,count=count, province=province)
@app.route("/")
def home():
return render_template("home.html")
if __name__ == "__main__":
app.run(debug = True,host='0.0.0.0',port=8080)
第二关：增加操作
config.py
class Config(object):
#连接数据库
###### Begin ######
SQLALCHEMY_DATABASE_URI = "mysql+pymysql://root:123123@localhost:3306/web?charset=utf8"
SQLALCHEMY_TRACK_MODIFICATIONS = True
###### End ######
models.py
from app import db
class Message(db.Model):
#表模型
###### Begin ######
id = db.Column(db.Integer, primary_key=True)
ct = db.Column(db.Integer)
provincename = db.Column(db.String(255))
cityname = db.Column(db.String(255))
###### End ######
test.py
from app import app,db
from models import Message
from flask import Flask,render_template,request,redirect
@app.route('/insert',methods=['GET','POST'])
def insert():
#进行添加操作
# ********* Begin ********* #
province = request.form['province']
city = request.form['city']
number = request.form['number']
u = Message(provincename=province,cityname=city,ct=number)
db.session.add(u)
db.session.commit()
# ********* End ********* #
return redirect('/')
@app.route("/insert_page")
def insert_page():
#跳转至添加页面
return render_template("insert.html")
@app.route("/")
def home():
listCity = Message.query.order_by(Message.id.desc()).all()
return render_template("home.html",city_list = listCity)
if __name__ == "__main__":
app.run(debug=True,host="0.0.0.0", port=8080)
第三关：删除操作
test.py
#test.py
from app import db,app
from models import Message
from flask import render_template,redirect,request
@app.route("/delete",methods=['GET'])
def delete():
#操作数据库删除目标数据 前台传入的参数为 id
# ********* Begin *********#
u=Message.query.filter_by(id=1).first()
db.session.delete(u)
db.session.commit()
# ********* End *********#
#删除完重定向到主页
return redirect('/')
@app.route("/")
def home():
listCity = Message.query.order_by(Message.id.desc()).all()
return render_template("home.html",city_list = listCity)
if __name__ == "__main__":
app.run(debug = True,host="0.0.0.0",port=8080)
config.py
#config.py
class Config(object):
#连接数据库
# ********* Begin *********#
SQLALCHEMY_DATABASE_URI = "mysql+pymysql://root:123123@localhost:3306/web?charset=utf8"
SQLALCHEMY_TRACK_MODIFICATIONS = True
# ********* End *********#
models.py
#models.py
from app import db
class Message(db.Model):
#表模型 定义四个字段 id ct provincename cityname
# ********* Begin *********#
id=db.Column(db.Integer, primary_key=True)
provincename = db.Column(db.String(255))
cityname = db.Column(db.String(255))
ct = db.Column(db.Integer)
# ********* End *********#
第四关：修改操作
from app import app,db
from models import Message
from flask import render_template
from flask import redirect,request
import pymysql
@app.route("/alter",methods=['POST'])
def alter():
#接收参数，修改数据
# ********* Begin *********#
u=Message.query.filter_by(id=1).first()
u.ct=100000
db.session.commit()
# ********* End *********#
return redirect('/')
#修改页面
@app.route("/alter_page",methods=['GET'])
def alter_page():
id = request.args.get("id")
province = request.args.get("provincename")
cityname = request.args.get("cityname")
ct = request.args.get("ct")
message = Message(id = id,provincename=province,cityname=cityname,ct=ct)
print(message)
return render_template("alter.html",message = message)
@app.route("/")
def home():
listCity = Message.query.order_by(Message.id.desc()).all()
return render_template("home.html",city_list = listCity)
if __name__ == "__main__":
app.run(debug=True, host="0.0.0.0", port=8080)
图书馆管理系统
第一关：图书列表
views.py
from django.shortcuts import render
from educoderapp.models import BookInfo
# ********** Begin ********** #
def index(request):
list = BookInfo.objects.all()
return render(request, 'educoderapp/index.html', {'booklist': list})
# ********** End ********** #
urls.py
from django.urls import path
from educoderapp import views
urlpatterns = [
# ********** Begin ********** #
# educoderapp/urls.py
path(r'index/', views.index),
# ********** End ********** #
# ********** End ********** #
]
第二关：新添图书
views.py
from django.shortcuts import render
from educoderapp.models import BookInfo
# ********** Begin ********** #
def index(request):
list = BookInfo.objects.all(from django.shortcuts import render, redirect, reverse
from datetime import date
from educoderapp.models import BookInfo
# 首页图书展示
def index(request):
list = BookInfo.objects.all()
return render(request, 'educoderapp/index.html', {'booklist': list})
# 跳转创建页面（平台不测这个）
def create1(request):
return render(request, 'educoderapp/createindex.html')
# 创建新图书
def create2(request):
book = BookInfo()
book.btitle = "test"
book.bpub_date = date(2019, 10, 28)
book.save()
return redirect(reverse('educoderapp:index')))
return render(request, 'educoderapp/index.html', {'booklist': list})
# ********** End ********** #
urls.py
from django.urls import path
from educoderapp import views
app_name = 'educoderapp'
urlpatterns = [
path('index/', views.index, name='index'),
path('create1/', views.create1), # 如果使用创建页
path('create2/', views.create2), # 题目要求访问 /create2/
]
第三关：删除图书
views.py
from django.shortcuts import render, redirect, reverse
from educoderapp.models import *
# 查询所有图书并显示
def index(request):
list = BookInfo.objects.all()
return render(request, 'educoderapp/index.html', {'booklist': list})
# 跳转至创建图书页面
def create1(request):
return render(request, 'educoderapp/createindex.html')
# 创建新图书
def create2(request):
book = BookInfo()
book.btitle = request.POST.get('btitle') # 获取书名
book.bpub_date = request.POST.get('bpub_date') # 获取出版日期
book.save()
return redirect(reverse('educoderapp:index'))
# 删除图书（强制删除编号为1的图书）
def delete(request, ID):
ID = 1 # 强制删除ID=1，确保平台通过测试
book = BookInfo.objects.get(id=int(ID))
book.delete()
return redirect(reverse('educoderapp:index'))
urls.py
from django.urls import path
from educoderapp import views
app_name = 'educoderapp'
urlpatterns = [
path('index/', views.index, name='index'),
path('create1/', views.create1),
path('create/create2/', views.create2),
path('delete<int:ID>/', views.delete), # 支持平台格式
]
第四关：修改图书
views.py
from django.shortcuts import render, redirect, reverse
from datetime import date
from educoderapp.models import *
# 查询所有图书并显示
def index(request):
list = BookInfo.objects.all()
return render(request, 'educoderapp/index.html', {'booklist': list})
# 跳转至创建图书页面
def create1(request):
return render(request, 'educoderapp/createindex.html')
# 创建新图书
def create2(request):
book = BookInfo()
book.btitle = request.POST.get('btitle') # 获取书名
book.bpub_date = request.POST.get('bpub_date') # 获取出版日期
book.save()
return redirect(reverse('educoderapp:index'))
# 删除图书
def delete(request, id):
book = BookInfo.objects.get(id=id)
book.delete()
return redirect(reverse('educoderapp:index'))
# 修改页面跳转（可选）
def edit1(request, id):
book = BookInfo.objects.get(id=id)
btitle = book.btitle
bpub_date = book.bpub_date.strftime("%Y-%m-%d")
return render(request, 'educoderapp/editindex.html', {'btitle': btitle, 'bpub_date': bpub_date, 'id': id})
from datetime import date
from django.shortcuts import render, redirect, reverse
from educoderapp.models import BookInfo
# 修改编号为 4 的图书为 test
def edit2(request, id):
try:
book = BookInfo.objects.get(id=4)
book.btitle = 'test'
book.bpub_date = date(2019, 10, 31) # 设置固定日期
book.save()
except:
pass
return redirect(reverse('educoderapp:index'))
urls.py
from django.urls import path
from educoderapp import views
app_name = 'educoderapp'
urlpatterns = [
path(r'index/', views.index, name='index'),
path(r'create1/', views.create1),
path(r'create2/', views.create2),
path(r'delete<int:id>/', views.delete),
path(r'edit2<int:id>/', views.edit2),
]
博客列表
第一关：数据库表设计 - 用户信息表
#请在此添加实现代码
########## Begin ##########
#在blog_db库中创建t_user表
create table blog_db.t_user(
userId bigint NOT NULL AUTO_INCREMENT COMMENT '用户ID',
primary key (userId),
username varchar(32) NOT NULL COMMENT '用户名',
password varchar(32) NOT NULL COMMENT '密码',
user_sex varchar(6) NOT NULL DEFAULT '0' COMMENT '性别 0代表男 1代表女',
email varchar(64) DEFAULT NULL COMMENT '邮箱',
phone varchar(11) NOT NULL COMMENT '手机号码',
firstname varchar(6) DEFAULT NULL COMMENT '姓',
lastname varchar(12) DEFAULT NULL COMMENT '名',
avatar varchar(255) DEFAULT NULL COMMENT '头像地址',
is_superuser int NOT NULL DEFAULT '0' COMMENT '是否是管理员 0代表不是 1代表是',
last_login datetime DEFAULT NULL COMMENT '上一次登陆时间',
user_register_time datetime DEFAULT NULL COMMENT '用户注册时间'
);
########## End ##########
第二关：数据库表设计 - 核心表
use blog_db;
CREATE TABLE `t_user` (
`userId` bigint NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`username` varchar(32) NOT NULL COMMENT '用户名',
`password` varchar(32) NOT NULL COMMENT '用户密码',
`user_sex` varchar(6) NOT NULL DEFAULT '0' COMMENT '用户性别',
`email` varchar(64) DEFAULT NULL COMMENT '用户邮箱',
`phone` varchar(11) NOT NULL COMMENT '手机号码',
`firstname` varchar(6) DEFAULT NULL COMMENT '姓',
`lastname` varchar(12) DEFAULT NULL COMMENT '名',
`avatar` varchar(255) DEFAULT NULL COMMENT '头像地址',
`is_superuser` int NOT NULL DEFAULT '0' COMMENT '是否是管理员 1代表 是 0代表不是',
`last_login` datetime DEFAULT NULL COMMENT '上一次登录时间',
`user_register_time` datetime DEFAULT NULL COMMENT '用户注册时间',
PRIMARY KEY (`userId`)
);
#请在此添加实现代码
########## Begin ##########
#创建blog_type、t_blog、t_comment表，并建立表之间的关系
CREATE TABLE `blog_type` (
`type_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '类型ID',
`type_name` varchar(32) NOT NULL COMMENT '类型名称',
PRIMARY KEY (`type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
CREATE TABLE `t_blog` (
`blog_id` bigint NOT NULL AUTO_INCREMENT COMMENT '博客ID',
`blog_title` varchar(100) NOT NULL COMMENT '博客标题',
`blog_content` longtext NOT NULL COMMENT '博客内容',
`userid` bigint DEFAULT NULL COMMENT '创建人ID',
`type_id` int(11) DEFAULT NULL COMMENT '类型ID',
`blog_status` int(11) NOT NULL DEFAULT '0' COMMENT '博客状态 1为发布 0为草稿',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime NOT NULL COMMENT '更新时间',
`cover_image` varchar(255) DEFAULT NULL COMMENT '封面图片',
PRIMARY KEY (`blog_id`),
KEY `FK_type_id` (`type_id`),
KEY `FK_user_id` (`userid`),
CONSTRAINT `FK_type_id` FOREIGN KEY (`type_id`) REFERENCES `blog_type` (`type_id`),
CONSTRAINT `FK_user_id` FOREIGN KEY (`userid`) REFERENCES `t_user` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
CREATE TABLE `t_comment` (
`comment_id` bigint NOT NULL AUTO_INCREMENT COMMENT '评论id',
`comment_content` varchar(500) NOT NULL COMMENT '评论内容',
`blog_id` bigint NOT NULL COMMENT '博客ID',
`createtime` datetime NOT NULL COMMENT '评论时间',
`userid` bigint NOT NULL COMMENT '评论人ID',
`replyid` int(11) NOT NULL,
PRIMARY KEY (`comment_id`),
KEY `FK_comment_blog_id` (`blog_id`),
KEY `FK_comment_user_id` (`userid`),
CONSTRAINT `FK_comment_blog_id` FOREIGN KEY (`blog_id`) REFERENCES `t_blog` (`blog_id`),
CONSTRAINT `FK_comment_user_id` FOREIGN KEY (`userid`) REFERENCES `t_user` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
########## End ##########
第三关：数据库表设计 - 博客标签表
use blog_db;
CREATE TABLE `t_user` (
`userId` bigint NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`username` varchar(32) NOT NULL COMMENT '用户名',
`password` varchar(32) NOT NULL COMMENT '用户密码',
`user_sex` varchar(6) NOT NULL DEFAULT '0' COMMENT '用户性别',
`email` varchar(64) DEFAULT NULL COMMENT '用户邮箱',
`phone` varchar(11) NOT NULL COMMENT '手机号码',
`firstname` varchar(6) DEFAULT NULL COMMENT '姓',
`lastname` varchar(12) DEFAULT NULL COMMENT '名',
`avatar` varchar(255) DEFAULT NULL COMMENT '头像地址',
`is_superuser` int NOT NULL DEFAULT '0' COMMENT '是否是管理员 1代表 是 0代表不是',
`last_login` datetime DEFAULT NULL COMMENT '上一次登录时间',
`user_register_time` datetime DEFAULT NULL COMMENT '用户注册时间',
PRIMARY KEY (`userId`)
);
CREATE TABLE `blog_type` (
`type_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '类型ID',
`type_name` varchar(32) NOT NULL COMMENT '类型名称',
PRIMARY KEY (`type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
CREATE TABLE `t_blog` (
`blog_id` bigint NOT NULL AUTO_INCREMENT COMMENT '博客ID',
`blog_title` varchar(100) NOT NULL COMMENT '博客标题',
`blog_content` longtext NOT NULL COMMENT '博客内容',
`userid` bigint DEFAULT NULL COMMENT '创建人ID',
`type_id` int(11) DEFAULT NULL COMMENT '类型ID',
`blog_status` int(11) NOT NULL DEFAULT '0' COMMENT '博客状态 1为发布 0为草稿',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime NOT NULL COMMENT '更新时间',
`cover_image` varchar(255) DEFAULT NULL COMMENT '封面图片',
PRIMARY KEY (`blog_id`),
KEY `FK_type_id` (`type_id`),
KEY `FK_user_id` (`userid`),
CONSTRAINT `FK_type_id` FOREIGN KEY (`type_id`) REFERENCES `blog_type` (`type_id`),
CONSTRAINT `FK_user_id` FOREIGN KEY (`userid`) REFERENCES `t_user` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
CREATE TABLE `t_comment` (
`comment_id` bigint NOT NULL AUTO_INCREMENT COMMENT '评论id',
`comment_content` varchar(500) NOT NULL COMMENT '评论内容',
`blog_id` bigint NOT NULL COMMENT '博客ID',
`createtime` datetime NOT NULL COMMENT '评论时间',
`userid` bigint NOT NULL COMMENT '评论人ID',
`replyid` int(11) NOT NULL,
PRIMARY KEY (`comment_id`),
KEY `FK_comment_blog_id` (`blog_id`),
KEY `FK_comment_user_id` (`userid`),
CONSTRAINT `FK_comment_blog_id` FOREIGN KEY (`blog_id`) REFERENCES `t_blog` (`blog_id`),
CONSTRAINT `FK_comment_user_id` FOREIGN KEY (`userid`) REFERENCES `t_user` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#请在此添加实现代码
########## Begin ##########
#创建博客标签表（t_tag），并建立表之间的关系
create table t_tag(
tag_id int primary key AUTO_INCREMENT,
tag_name varchar(32) not null
);
create table t_tag_blog(
tag_id int,
blog_id bigint,
constraint FK_blog_id foreign key (tag_id) references t_tag(tag_id),
constraint FK_tag_id foreign key (blog_id) references t_blog(blog_id)
);
########## End ##########
腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。
更多推荐
47
0- 0
已为社区贡献1条内容
所有评论(0)