运维做开发

这个屌丝很懒，什么也没留下！

热门标签

头歌MySQL数据库实训答案有目录_头歌数据库实训作业答案

作者：运维做开发 | 2024-07-14 11:03:04

踩

头歌数据库实训作业答案

头歌MySQL数据库答案

特别感谢黄副班、小青提供代码，有问题联系公众号【学思则安】留言更正
- 其他作业链接
数据库1-MySQL数据定义与操作实战

特别感谢黄副班、小青提供代码，有问题联系公众号【学思则安】留言更正

其他作业链接

头歌java实训答案集

数据库1-MySQL数据定义与操作实战

MySQL数据库 - 初识MySQL

数据库部分一条一条的写，可鼠标手动粘贴，除特定命令外未分大小写。
第1关：创建数据库

mysql -uroot -p123123 -h127.0.0.1
1

create database MyDb;
1

第2关创建表

mysql -uroot -p123123 -h127.0.0.1
1

create database TestDb;
1

use TestDb;
1

create table t_emp (id int,
name varchar(32),
deptId int,
salary float);
1
2
3
4

第3关：使用主键约束

mysql -uroot -p123123 -h127.0.0.1
1

create database MyDb;
1

use MyDb;
1

create table t_user1(
userId INT PRIMARY KEY,
name VARCHAR(32),
password VARCHAR(11),
phone VARCHAR(11),
email VARCHAR(32));
1
2
3
4
5
6

create table t_user2(
name VARCHAR(32),
phone VARCHAR(11),
email VARCHAR(32),
PRIMARY KEY(name,phone));
1
2
3
4
5

第4关：外键约束

mysql -uroot -p123123 -h127.0.0.1
1

create database MyDb;
1

use MyDb;
1


CREATE TABLE t_class
(
    id INT  PRIMARY KEY,
    name VARCHAR(22) 
);)
1
2
3
4
5
6

CREATE TABLE t_student
(
    id INT  PRIMARY KEY,
    name VARCHAR(22) ,
    classId int,
    CONSTRAINT fk_stu_class1 FOREIGN KEY(classId) REFERENCES t_class(id)
);
1
2
3
4
5
6
7

第5关：添加常用约束

mysql -uroot -p123123 -h127.0.0.1
1

CREATE DATABASE MyDb;

1
2

USE MyDb;
1

CREATE TABLE t_user
(
    id INT  PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(32) NOT NULL UNIQUE,
    sex VARCHAR(4) DEFAULT '男'
)DEFAULT CHARSET=utf8;
1
2
3
4
5
6

MySQL数据库 - 数据库和表的基本操作（一）

第1关：查看表结构与修改表名

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 ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

第2关：修改字段名与字段数据类型

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

第3关：添加与删除字段

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

第4关：修改字段的排列位置

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

第5关：删除表的外键约束

USE Company;

#请在此处添加实现代码
########## Begin ##########

########## delete the foreign key ##########
ALTER TABLE tb_emp DROP FOREIGN KEY emp_dept;


########## End ##########
SHOW CREATE TABLE tb_emp \G;
1
2
3
4
5
6
7
8
9
10
11

MySQL数据库 - 数据库和表的基本操作（二）

第1关：插入数据

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;
########## End ##########
1
2
3
4
5
6
7
8
9
10
11
12
13

第2关：更新数据

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;

########## End ##########

DESCRIBE tb_emp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

第3关：删除数据

USE Company;

#请在此处添加实现代码
########## Begin ##########

########## delete the value ##########
DELETE FROM tb_emp
WHERE Salary>3000;


########## End ##########

SELECT * FROM tb_emp;

########## End ##########

DESCRIBE tb_emp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

MySQL数据库 - 单表查询（一）

第1关：基本查询语句

USE Company;

#请在此处添加实现代码
########## Begin ##########

########## retrieving the Name and Salary ##########
select Name,Salary from tb_emp;

########## retrieving all the table ##########
select * from tb_emp;

########## End ##########
1
2
3
4
5
6
7
8
9
10
11
12

第2关：带 IN 关键字的查询

USE Company;

#请在此处添加实现代码
########## Begin ##########

########## retrieving the Name and Salary with IN statement ##########
SELECT Name,Salary FROM tb_emp WHERE Id NOT IN (1);


########## End ##########

1
2
3
4
5
6
7
8
9
10
11

第3关：带 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 ##########

1
2
3
4
5
6
7
8
9
10
11
12

MySQL数据库 - 单表查询（二)

第1关：带 LIKE 的字符匹配查询

USE Company;

######### Begin #########
SELECT Name,Salary FROM tb_emp WHERE Name LIKE "C%";

######### End #########


1
2
3
4
5
6
7
8

第2关：查询空值与去除重复结果

USE Company;

######### Begin #########
SELECT * FROM tb_emp WHERE DeptId IS NULL;

######### End #########

######### Begin #########
SELECT DISTINCT Name FROM tb_emp;

######### End #########


1
2
3
4
5
6
7
8
9
10
11
12
13

第3关：带 AND 与 OR 的多条件查询

USE Company;

######### Begin #########
SELECT * FROM tb_emp WHERE DeptId=301 AND Salary > 3000;

######### End #########

######### Begin #########
SELECT * FROM tb_emp WHERE DeptId=301 OR DeptId=303;

######### End #########
1
2
3
4
5
6
7
8
9
10
11

MySQL数据库 - 单表查询（三）

第1关：对查询结果进行排序

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询1班同学的所有信息以成绩降序的方式显示结果 ##########
select * from tb_score where class_id = 1 order by score desc;

########## End ##########


1
2
3
4
5
6
7
8
9
10
11

第2关：分组查询

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 对班级名称进行分组查询 ##########
SELECT * FROM tb_class GROUP BY class_id;

########## End ##########
1
2
3
4
5
6
7
8
9

第3关：使用 LIMIT 限制查询结果的数量

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询班级中第2名到第5名的学生信息 ##########
SELECT * FROM tb_score order by score desc LIMIT 1,4;

########## End ##########
1
2
3
4
5
6
7
8
9

MySQL数据库 - 连接查询

第1关：内连接查询

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 ##########


1
2
3
4
5
6
7
8
9
10
11
12

第2关：外连接查询

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 ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

第3关：复合条件连接查询

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 ##########
1
2
3
4
5
6
7
8
9
10

MySQL数据库 - 子查询

第1关：带比较运算符的子查询

USE Company;

#请在此处添加实现代码
########## Begin ##########
#1.查询大于所有平均年龄的员工姓名与年龄
select name,age from tb_emp where age>(select avg(age) from tb_emp);


########## End ##########
1
2
3
4
5
6
7
8
9

第2关：关键字子查询

USE Company;
#请在此处添加实现代码
########## Begin ##########

#1.使用 ALL 关键字进行查询
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 ##########
1
2
3
4
5
6
7
8
9
10
11
12
13

MySQL数据库 - 复杂查询（一)

第1关：交换工资


#请在此添加实现代码
########## Begin ##########
UPDATE tb_Salary
SET
sex = CASE sex WHEN "m"  THEN "f"
ELSE "m"
END;

########## End ##########
1
2
3
4
5
6
7
8
9
10

第2关：换座位

#请在此添加实现代码
########## Begin ##########
SELECT if(Id%2=0,Id-1,if(Id=5,Id,Id+1)) AS id,name
FROM tb_Seat ORDER BY Id;
########## End ##########

1
2
3
4
5
6

第3关：分数排名


#请在此添加实现代码
########## Begin ##########
select Score,(select count(distinct score) from score where score >=s.score) as Rank
from score as s order by Score desc;
select Score,(select count(*) from score as s2 where s2.score >s1.score)+1 as Rank
from score as s1 order by Rank;

########## End ##########
1
2
3
4
5
6
7
8
9

第4关：体育馆的人流量

#请在此添加实现代码
########## Begin ##########
select distinct a.* from gymnasium a,gymnasium b,gymnasium 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 a.id;

########## End ##########
1
2
3
4
5
6
7
8
9
10
11
12
13

第5关：统计总成绩

#请在此添加实现代码
########## Begin ##########
select t1.classname,t1.chinese,t2.maths
from(select c.classname classname,sum(s.chinese)
chinese from tb_class c,tb_score s where c.stuname=
s.name and s.chinese>=60 group by c.classname)t1,
(select c.classname classname,sum(s.maths)maths from tb_class c,tb_score s
where c.stuname=s.name and s.maths>=60 group by c.classname)t2
where t1.classname=t2.classname;

########## End ##########

1
2
3
4
5
6
7
8
9
10
11
12

MySQL数据库 - 复杂查询（二）

第1关：查询学生平均分


#请在此添加实现代码
########## 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 ##########




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

第2关：查询修课相同学生信息


#请在此添加实现代码
########## Begin ##########
create view temp as(select s_id,group_concat(c_id)as c from score group by s_id);
select * from student where s_id in(select s_id from temp where c=(select c from temp where s_id="01")and s_id<>"01");
########## End ##########




1
2
3
4
5
6
7
8
9
10

第3关：查询各科成绩并排序

#请在此添加实现代码
########## Begin ##########
select a.*,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.c_id,a.s_id
order by a.c_id,count(b.s_score);

########## End ##########
1
2
3
4
5
6
7
8

第4关：查询张老师课程成绩最高的学生信息


#请在此添加实现代码
########## 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 ##########
1
2
3
4
5
6
7
8
9
10

第5关：查询两门课程不及格同学信息


#请在此添加实现代码
########## 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 ##########
1
2
3
4
5
6
7
8
9
10
11
12

MySQL数据库 - 使用聚合函数查询

第1关：COUNT( )函数

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询该表中一共有多少条数据 ##########
select count(*) from tb_class;

########## 查询此表中367班有多少位学生 ##########
select classid,count(*) from tb_class where classid=367;

########## End ##########
1
2
3
4
5
6
7
8
9
10
11
12

第2关：SUM( )函数

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询所有学生总分数 ##########
select sum(score) from tb_class;

########## 查询学生语文科目的总分数 ##########
select course,sum(score) from tb_class where course="语文";


########## End ##########
1
2
3
4
5
6
7
8
9
10
11
12
13

第3关：AVG( )函数

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询学生语文科目的平均分数 ##########
select course,avg(score)from tb_class where course="语文";


########## 查询学生英语科目的平均分数 ##########
select course,avg(score) from tb_class where course="英语";


########## End ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14

第4关：MAX( )函数

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询语文课程中的最高分数 ##########
select course,max(score) from tb_class where course="语文";


########## 查询英语课程中的最高分数 ##########
select course,max(score) from tb_class where course="英语";


########## End ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14

第5关：MIN( )函数

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询语文课程中的最低分数 ##########
select course,min(score) from tb_class where course="语文";


########## 查询英语课程中的最低分数 ##########
select course,min(score) from tb_class where course="英语";


########## End ##########

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

MySQL数据库 - 其他函数的使用

第1关：字符函数


#请在此添加实现代码
########## Begin ##########
select CONCAT(UPPER(SUBSTR(Name,1,1)),LOWER(SUBSTR(Name,2,LENGTH(Name)))) as Name from employee;
########## End ##########
1
2
3
4
5

第2关：数学函数


#请在此添加实现代码
########## Begin ##########
update Score set s_score=TRUNCATE(s_score-(round(sqrt((power(4,4)-power(3,3))/power(2,2)),2)),2);
########## End ##########
1
2
3
4
5

第3关：日期时间函数和流程控制类函数



#请在此添加实现代码
########## 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 ##########

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

第4关：自定义函数

#请在此添加实现代码
########## 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 ##########

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

MySQL数据库 - 分组选择数据

第1关：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 ##########

1
2
3
4
5
6
7
8
9
10
11
12

第2关：使用 HAVING 与 ORDER BY

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 ##########

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

数据库2-MySQL数据管理技术实战

MySQL开发技巧 - 视图

第1关：视图

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 ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

MySQL开发技巧 - 索引

第1关：索引

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 ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

MySQL开发技巧 - 分页和索引

第1关：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 ##########
1
2
3
4
5
6
7
8
9
10

第2关：索引（单列索引）

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 ##########

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

第3关：索引（组合索引）

USE Person;
#请在此处添加实现代码
########## Begin ##########

#1.增加组合索引
ALTER TABLE person ADD INDEX name_city_score (name,age,address);


########## End ##########
SHOW INDEX FROM person;
1
2
3
4
5
6
7
8
9
10

MySQL开发技巧 - 存储过程

第1关：存储过程

USE mydb;
#请在此处添加实现代码
########## Begin ##########
drop procedure if exists mydb.GetCustomerLevel;
delimiter $$
create PROCEDURE GetCustomerLevel(in p_customNumber int(11),out p_customerLevel varchar(10))
Begin
declare levels int;
select creditlimit into levels from customers where customerNumber=p_customNumber;
if levels <5000 then
    set p_customerLevel = 'SILVER';
elseif levels <10000 then
    set p_customerLevel = 'GOLD';
 else
    set p_customerLevel = 'PLATINUM';
end if;
select p_customNumber as customerNumber,p_customerLevel;
End $$
delimiter ;
########## End ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

MySQL开发技巧 - 事务

第1关：事务

USE mydb;
#请在此处添加实现代码
########## Begin ##########
# 修改存储过程 ———— 向 t_emp 表中插入数据（注意请勿修改提供的代码框架） 
drop procedure if exists mydb.proc_insert;
delimiter $$
create procedure proc_insert()
Begin 
	

	#开启事务
	start transaction ;
    insert into t_emp values(1,'Nancy',301,2300);
    insert into t_emp values(2,'Tod',303,5600);
	insert into t_emp values(3,'Carly',301,3200);
	#事务提交
	commit;
END $$
delimiter ;

########## End ##########



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

MySQL开发技巧 - 并发控制

第1关：表锁

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 ##########
1
2
3
4
5
6
7
8

第2关：事务隔离级别

use mydb;

#请在此处添加实现代码
########## Begin ##########
#1.修改隔离级别
set session transaction isolation level Read uncommitted;
#2.查询隔离级别
select @@tx_isolation;
########## End ##########

1
2
3
4
5
6
7
8
9
10

第3关：行锁

mysql -uroot -p123123 -h127.0.0.1
1

source /data/workspace/myshixun/src/step3/table.sql；
1

begin;

1
2

select * from account for update;
1

update account set money=0 where name='A';
1

update account set money=0 where name='B';
1

commit;
1

MySQL开发技巧 - 行列转换

第1关：使用 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 ##########



1
2
3
4
5
6
7
8
9
10
11
12
13

第2关：序列化表的方法实现列转行（一）


#请在此添加实现代码
########## 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 ##########



1
2
3
4
5
6
7
8
9
10
11
12
13
14

第3关：序列化表的方法实现列转行（二）


#请在此添加实现代码
########## 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 ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

MySQL开发技巧 - 删除重复数据

第1关：利用主键删除


#请在此添加实现代码
########## 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 ##########



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

第2关：复杂重复数据删除


#请在此添加实现代码
########## 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 ##########
1
2
3
4
5
6
7
8
9
10
11

MySQL开发技巧 - 批量数据入库及检索

第1关：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()



1
2
3
4
5
6
7
8
9
10
11
12

第2关：数据库与数据表创建

# 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-----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

第3关：批量数据入库与检索

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()


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

第4关：多表创建与连接查询

import 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-----------

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

数据库3-MySQL数据库系统设计实战

MySQL开发技巧 - 查询、索引和完整性

第1关：基本查询的学习

//请在下面补齐查询一的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*********/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

第2关：深入学习查询语句

//请在下面输入查询一的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*********/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

第3关：视图的创建和使用

//请在下面输入创建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*********/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

第4关：索引与完整性

//请在下面输入创建索引的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*********/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

数据库查询 - 选课系统

第1关：数据库数据的插入

USE School;

#请在此添加实现代码
########## Begin ##########
########## 插入学生表（Student）相应数据 ##########
insert into student(Sno,Sname,Ssex,Sage,Sdept) values(9512101,'李勇','男',19,'计算机系'),
        (9512102,'刘晨','男',20, '计算机系'),
        (9512103,'王敏', '女',20,'计算机系'),
        (9521101,'张立','男',22,'信息系'),
        (9521102,'吴宾','女',21,'信息系'),
        (9521103,'张海','男',20,'信息系'),
        (9531101,'钱小平','女',18,'数学系'),
        (9531102,'王大力','男',19,'数学系');
########## 插入课程表（Course）相应数据 ##########
insert into course(Cno,Cname,Ccredit,Semster,Period) values('C01','计算机文化学',3,1,41),
        ('C02','VB',2,3,61),
        ('C03','计算机网络',4,7,14),
        ('C04','数据库基础',6,6,24),
        ('C05','高等数学',8,2,19),
        ('C06','数据结构',5,4,55);
########## 插入学生选课表（DBSC）相应数据 ##########
insert into dbsc(ScID,Sno,Cno,Grade,isTec) values(1,9512101,'c01',90,'必修'),(2,9512101,'c02',86,'选修'),(3,9512101,'c06',45,'必修'),
        (4,9512102,'c02',78,'选修'),(5,9512102,'c04',66,'必修'),
        (6,9521102,'c01',82,'选修'),(7,9521102,'c02',75,'选修'),(8,9521102,'c04',92,'必修'),(9,9521102,'c05',50,'必修'),
        (10,9521103,'c02',68,'选修'),(11,9521103,'c06',56,'必修'),
        (12,9531101,'c01',80,'选修'),(13,9531101,'c05',95,'必修'),
        (14,9531102,'c05',85,'必修');
########## End ##########
########## 查询表数据 ##########
SELECT * FROM student;
SELECT * FROM course;
SELECT * FROM dbsc;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

第2关：简单查询

#********* Begin *********#
echo "
select Sname, Sdept from student where Sdept = '计算机系';

select Sno from dbsc where Grade < 60;

select Sname, Sdept, Sage from student where Sage >=20 and Sage <=23 and Sdept = '信息系';

select Sno, Grade from dbsc where Cno = 'c02' order by Grade desc;

select count(*) from student;

"
#********* End *********#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

第3关：进阶查询

#********* Begin *********#
echo "
select student.* from student where Sname like '张%';

select Sname, Ssex, Sdept from student where Sdept in ('计算机系','信息系','数学系');

select Cno, count(*) from dbsc where isTec= '选修' group by Cno;

select Sno from dbsc group by Sno having count(*) > 3;

select Sname,Cno,Grade from dbsc left join student on student.Sno=dbsc.Sno where student.Sdept='计算机系';

"
#********* End *********#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

第4关：复杂查询

#********* Begin *********#
echo "
select distinct dbsc.Sno, student.Sname from dbsc join student on student.Sno=dbsc.Sno where dbsc.isTec = '选修';

select Sname, count(*), avg(Grade) from dbsc join student on student.Sno=dbsc.Sno  group by dbsc.Sno;

select avg(Grade),count(*) from dbsc join student on student.Sno=dbsc.Sno group by dbsc.Sno having count(*) >= 4;

select student.Sname, dbsc.Cno, dbsc.Grade from student left join dbsc on student.Sno=dbsc.Sno
where student.Sdept='信息系' and dbsc.isTec='选修' and Cno='C02';

update dbsc set Grade=Grade+5 where Grade < 60;

"
#********* End *********#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

数据库设计 - 博客系统

第1关：数据库表设计 - 用户信息表

#请在此添加实现代码
########## 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 ##########

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

第2关：数据库表设计 - 核心表

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 ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

第3关：数据库表设计 - 博客标签表

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 ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

数据库开发基础案例 - JDBC 技术应用

第1关：数据库连接与数据库实例创建

    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;
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

第2关：数据表的创建`

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());
          }
      }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

第3关：数据查询操作

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;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

第4关：数据库的插入操作

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());
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90

第5关：数据表的删除操作

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;
        }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

第6关：数据表的更新操作

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;
        }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

第7关：数据库的应用`

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;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

数据库开发中级案例 - PythonWeb框架应用

本关有部分试题有出现多个代码界面，主义需要切换界面
第1关：查询操作
step 1/config.py

class Config(object):
    #连接数据库
    ###### Begin ######
    SQLALCHEMY_DATABASE_URI = "mysql+pymysql://root:123123@localhost:3306/web" 
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    ###### End ######
1
2
3
4
5
6

step 1/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 *********#
1
2
3
4
5
6
7
8
9

step1/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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

第2关：增加操作
step2/config.py

class Config(object):
    #连接数据库
    ###### Begin ######
    SQLALCHEMY_DATABASE_URI = "mysql+pymysql://root:123123@localhost:3306/web?charset=utf8" 
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    ###### End ######
1
2
3
4
5
6

step 2/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 ######
1
2
3
4
5
6
7
8
9

step2/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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

第3关：删除操作
step3/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():
    #操作数据库得到目标数据，before_number表示删除之前的数量，after_name表示删除之后的数量
    # ********* Begin *********#
    id = request.args.get("id")
    message = Message.query.filter_by(id=id).first()
    db.session.delete(message)
    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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

step3/config.py

class Config(object):
    #连接数据库
    ###### Begin ######
    SQLALCHEMY_DATABASE_URI = "mysql+pymysql://root:123123@localhost:3306/web?charset=utf8" 
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    ###### End ######
1
2
3
4
5
6

step3/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 *********#
1
2
3
4
5
6
7
8
9

第4关：修改操作

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 *********#
    id = request.form["id"]
    province = request.form['province']
    city = request.form['city']
    number = request.form['number']
    message = Message.query.filter_by(id = id).first()
    message.provincename = province
    message.cityname = city
    message.ct = number
    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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

数据库开发中级案例 -ORM框架应用

第1关：使用Flask进行数据库开发

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import datetime
import warnings
warnings.filterwarnings("ignore")
app = Flask(__name__)
# 请在此处添加代码，设置数据库连接
# 用户：root
# 密码:123123
# 连接地址:127.0.0.1
# 数据库:flask_table
#********** Begin *********#
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:123123@127.0.0.1:3306/flask_table'
db = SQLAlchemy(app)
#********** End **********#
# 定义模型
class entry_Form(db.Model):
    # 请根据左侧表格字段格式完成模型的编写
    # ********** Begin *********#
    __tablename__='entry_form'
    ID = db.Column(db.Integer,primary_key=True,autoincrement=True)
    company_name = db.Column(db.String(255),nullable=False)
    eduLevel_name = db.Column(db.String(255))
    Entry_time = db.Column(db.Date,default=datetime.date(2019,1,1))
    jobName = db.Column(db.String(255))
    salary = db.Column(db.Integer)
    # ********** End **********#
    # 根据题目要求，请在此处此函数中添加代码，完成函数的编写
    def createTable(self):
        # ********** Begin *********#
        # 删除原有数据库表
        db.drop_all()
        #根据模型类型创建表
        db.create_all()
        # ********** End **********#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

第2关：查询操作

from task import entry_Form
class Test():
    def select_Table(self):
        # 请在此处填写代码，并根据左侧编程要求完成本关考核
        # ********** Begin *********#
        ap = entry_Form()
        pro = ap.query.filter(entry_Form.company_name == "阿里", entry_Form.eduLevel_name == "硕士",
                      entry_Form.salary >= 20000, entry_Form.salary <= 25000,
                      entry_Form.Entry_time >= "2019-06").all()
        return pro
        # ********** End **********#
1
2
3
4
5
6
7
8
9
10
11

第3关：添加操作

import pandas as pd
from  task import  db,entry_Form
class Message:
    def update_table(self):
        # 请根据左侧编程要求完成相应的代码填写
        # 文件路径为"data.csv"  模型类 (已实现)：entry_Form
        # 数据库表已创建 只需要完成添加操作即可
        # ********** Begin *********#
        data = pd.read_csv(r"data.csv", encoding="utf8", sep="\t")
        list = []
        for index, row in data.iterrows():
            user_info = entry_Form(ID = row['ID'],company_name=row['company_name'], eduLevel_name=row['eduLevel_name'],
                                   Entry_time=row['Entry_time'], jobName=row['jobName'],
                                   salary=row['salary'])
            list.append(user_info)
        # 添加多条数据
        db.session.add_all(list)
        # # db.session.add(data2)
        db.session.commit()
        #********** End **********#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

第4关：删除操作

from operator import or_
from task import db,entry_Form
class Demo:
    def del_col(self):
        # 请在此处填写代码，根据左侧编程要求完成数据的批量删除
        # ********** Begin *********#
        user_info = entry_Form()
        user_info.query.filter(entry_Form.company_name =="华为",or_(entry_Form.jobName =="Java工程师",entry_Form.jobName=="Python工程师")).delete()
        db.session.commit()
        # ********** End **********#
1
2
3
4
5
6
7
8
9
10

第5关：修改操作

from task import db,entry_Form
class Demo:
    # 更新数据函数
    def update_Date(self):
        # 根据左侧相关知识，完成相关代码实现
        # ********* Begin *********#
        user = entry_Form.query.filter_by(ID=10).first()
        user.salary = 30000
        db.session.commit()
        # ********* End *********#
1
2
3
4
5
6
7
8
9
10

数据库开发综合案例 - 仓库管理系统设计

第1关：数据库表设计 - 核心表创建

#请在此添加实现代码
########## Begin ##########
#在warehouse_db库中创建warehouse表
use warehouse_db;
CREATE TABLE `warehouse`  (
    `warehouseId` int(11) NOT NULL,
    `area` int(11) NOT NULL,
    `phone` int(11) NOT NULL,
    PRIMARY KEY (`warehouseId`)
);
#在warehouse_db库中创建component表
CREATE TABLE `component`  (
    `componentId` int(11) NOT NULL,
    `componentName` varchar(20) NOT NULL,
    `standard` varchar(255) NOT NULL,
    `price` double(10, 2) NOT NULL,
    `describe` varchar(255) NOT NULL,
    PRIMARY KEY (`componentId`)
);
#在warehouse_db库中创建supplier表
CREATE TABLE `supplier`  (
    `supplyId` int(11) NOT NULL,
    `name` varchar(20) NOT NULL,
    `address` varchar(255) NOT NULL,
    `phone` int(11) NOT NULL,
    `account` bigint(18) NOT NULL,
    PRIMARY KEY (`supplyId`)
);
########## End ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

第2关：数据库表设计 - 项目职员表

#请在此添加实现代码
########## Begin ##########
#在warehouse_db库中创建project表
use warehouse_db;
CREATE TABLE `project`  (
    `projectId` int(11) NOT NULL,
    `projectBudget` double(10, 0) NOT NULL,
    `commenceDate` datetime NOT NULL,
    PRIMARY KEY (`projectId`)
);
 
#在warehouse_db库中创建employee表
CREATE TABLE `employee`  (
    `employeeId` int(11) NOT NULL,
    `name` varchar(20) NOT NULL,
    `age` int(3) NOT NULL,
    `designation` varchar(20) NOT NULL,
    `warehouseId` int(11) NOT NULL,
    `leaders` varchar(20) NOT NULL,
    PRIMARY KEY (`employeeId`),
    INDEX `FK_employee_warehouseId`(`warehouseId`),
    CONSTRAINT `FK_employee_warehouseId` FOREIGN KEY (`warehouseId`) REFERENCES `warehouse` (`warehouseId`)
);
########## End ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

第3关：数据库表设计 - 关联表

#请在此添加实现代码
########## Begin ##########
#在warehouse_db库中创建supply表
use warehouse_db;
CREATE TABLE `supply` (
    `supplyId` int(11) NOT NULL,
    `projectId` int(11) NOT NULL,
    `componentId` int(11) NOT NULL,
    `supplyCount` int(11) NOT NULL,
    PRIMARY KEY (`supplyId`),
    INDEX `FK_supply_supplyId`(`supplyId`),
    CONSTRAINT `FK_supply_supplyId` 
    FOREIGN KEY (`supplyId`) REFERENCES `supplier` (`supplyId`),
    INDEX `FK_supply_projectId`(`projectId`),
    CONSTRAINT `FK_supply_projectId` FOREIGN KEY (`projectId`) REFERENCES `project` (`projectId`),
    INDEX `FK_supply_componentId`(`componentId`),
    CONSTRAINT `FK_supply_componentId` FOREIGN KEY (`componentId`) REFERENCES `component` (`componentId`)
);
#在warehouse_db库中创建repertory表
CREATE TABLE `repertory`(
    `warehouseId` int(11) NOT NULL,
    `componentId` int(11) NOT NULL,
    `repertoryCount` int(11) NOT NULL,
    PRIMARY KEY (`warehouseId`),
 
    INDEX `FK_repertory_warehouseId`(`warehouseId`),
    CONSTRAINT `FK_repertory_warehouseId` FOREIGN KEY (`warehouseId`) REFERENCES `warehouse` (`warehouseId`),
    INDEX `FK_repertory_component_componentId`(`componentId`),
    CONSTRAINT `FK_repertory_component_componentId` FOREIGN KEY (`componentId`) REFERENCES `component` (`componentId`)
);
########## End ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

数据库开发综合案例 - 图书管理系统设计

第1关：数据库表设计 - 图书表

#请在此添加实现代码
########## Begin ##########
#在library_db库中创建books表
use library_db;
CREATE TABLE `books` (
    `bookId` int(11) NOT NULL,
    `bookName` varchar(255) NOT NULL,
    `publicationDate` datetime NOT NULL,
    `publisher` varchar(255) NOT NULL,
    `bookrackId` int(11) NOT NULL,
    `roomId` int(11) NOT NULL,
    PRIMARY KEY (`bookId`)
);
########## End ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14

第2关：数据库表设计 - 读者表

#请在此添加实现代码
########## Begin ##########
#在library_db库中创建reader表
use library_db;
CREATE TABLE `reader` (
    `borrowBookId` int(11) NOT NULL,
    `name` varchar(20) NOT NULL,
    `age` int(11) NOT NULL,
    `sex` varchar(2) NOT NULL,
    `address` varchar(255) NOT NULL,
    PRIMARY KEY (`borrowBookId`)
);
########## End ##########
1
2
3
4
5
6
7
8
9
10
11
12
13