【MySQL】データベースカリキュラムの実験
12216 ワード
データ定義
データそうじゅう
データ制約
データベースプログラミング
#mysql --version
#mysql -uroot -p #
show databases; #
#create database test ; # SQL
use test;
drop table if EXISTS JS,Course,SK;
#
create table JS(
Tno CHAR(7),
Tname CHAR(10),
Tsex CHAR(2),
Birthday DATE,
Dept CHAR(20),
Sid CHAR(18)
);
create table Course(
Cno CHAR(10),
Cname CHAR(20),
Credit TINYINT,--
Property CHAR(10),
Hours INT
);
create table SK(
Tno CHAR(7),
Cno CHAR(10),
Hours INT
);
#
insert into JS (Tno,Tname,Tsex,Birthday,Dept,Sid) VALUES ('T001', ' ', ' ', '1971-3-20', ' ', '551021197103203121'),
('T002', ' ', ' ', '1963-7-13', ' ', '32010119630713318X'),
('T003', ' ', ' ', '1973-9-15', ' ', '461031197309153829'),
('T004', ' ', ' ', '1960-2-18', ' ', '560102196002185623'),
('T005', ' ', ' ', '1977-10-11', ' ', '230103197710118632'),
('T006', ' ', ' ', '1962-4-23', ' ', '320104196204237516'),
('T007', ' ', ' ', '1968-8-27', ' ', '321102196808277214'),
('T008', ' ', ' ', '1980-7-10', ' ', '570102198007103452'),
('T009', ' ', ' ', '1981-3-2', ' ', '110102198103024125');
insert into Course (Cno,Cname,Credit,property) VALUES ('01010101', ' 1', 4, ' '),
('01010102', ' 1', 4, ' '),
('01010103', ' 1', 6, ' '),
('01010104', ' ', 2, ' '),
('01010105', ' ', 4, ' ');
insert into SK (Tno,Cno,Hours) VALUES ('T001', '01010105', 64),
('T002', '01010102', 64),
('T009', '01010101', 64),
('T004', '01010104', 32),
('T005', '01010103', 96),
('T006', '01010105', 64),
('T003', '01010101', 64);
alter table SK add Type char(4);
alter table SK change Hours Hours Smallint;
alter table Course drop Hours;
-- 4.1
drop table if EXISTS students;
#
create table students(
SNO CHAR(10) NOT NULL,
SNAME CHAR(8) NOT NULL,
AGE NUMERIC(3,0),
SEX CHAR(2),
BPLACE CHAR(20),
Polity CHAR(20),
primary key(SNO)
);
insert into students (SNO,SNAME,SEX,AGE,BPLACE,Polity) VALUES ('S001', ' ', ' ','22', '1997-3-20', ' '),
('S002', ' ', ' ','20', '1999-7-13', ' '),
('S003', ' ', ' ','22', '1997-9-15', ' '),
('S004', ' ', ' ','19','2000-2-18', ' ');
-- 4.2
alter TABLE JS ADD ADDR char(50);
-- 4.3
--
drop table if EXISTS GRIL,score,js1;
create table GRIL SELECT SNO,SNAME,AGE FROM students;
-- 4.4
create table score(
SNO CHAR(10),
CNO CHAR(10),
score NUMERIC(6,0)
);
insert into score (SNO,CNO,score) VALUES ('S001', '01010105', 64),
('S002', '01010102', 64),
('S003', '01010101', 64),
('S004', '01010104', 32);
-- 5.1
create table js1 SELECT * FROM JS;
create UNIQUE INDEX I_js_sid ON js1(Sid);
-- 5.2
create UNIQUE INDEX I_cource_xf ON Course(Cno,Credit);データそうじゅう
SHOW DATABASE;
USE test;
SELECT SNAME,BPLACE FROM students WHERE SEX=' ';
SELECT Tsex,count(Tno) FROM JS group by Tsex;
SELECT SNO,SNAME,AGE FROM students WHERE AGE>=20 and AGE<=23 ORDER BY AGE;
-- 1-4
SELECT SNAME FROM students WHERE AGE>=(SELECT AVG(AGE) FROM students);
-- 1-5
select SNAME,students.SNO,Course.Cname,score.score
from students,Course,score
where score.score<60 and students.SNO=score.SNO and score.Cno=Course.Cno;
-- 1-6
SELECT JS.Tno,Tname,Tsex,Birthday,Dept,Sid
from JS,SK where JS.Tno=SK.Tno and SK.Cno='01010105';
-- 1-7
SELECT Tname,Birthday,Cname,Hours
from JS,SK,course
where JS.Birthday60*365;
-- score
-- alter table score add foreign key(SNO) REFERENCES students(SNO);
--
alter table score drop foreign key score_ibfk_1;
DELETE from students where SNO='S001';
DELETE from score where SNO='S001';
--
-- CREATE VIEW op_age(Tno,Tname) AS
-- SELECT Tno,Tname FROM JS;
-- DROP VIEW op_age;
-- 1-3
-- SELECT Tno,Tname,DATEDIFF('2019-11-20',Birthday)/365 Age FROM JS
-- WHERE DATEDIFF('2019-11-20',Birthday)>=20*365 and DATEDIFF('2019-11-20',Birthday)<=50*365
-- ORDER BY Birthday DESC;# =
-- 1-4
-- SET @avg=0;
-- SELECT @avg:=AVG(DATEDIFF('2019-11-20',Birthday)/365) Age FROM JS;
-- SELECT Tno,Tname,DATEDIFF('2019-11-20',Birthday)/365 Age FROM JS
-- WHERE DATEDIFF('2019-11-20',Birthday)>=@avg * 365;
データ制約
show databases;
--
alter table js add PRIMARY KEY(Tno);
--
alter table js modify Tname varchar(10) NOT NULL;
--
alter table js add CONSTRAINT c_Tsex CHECK (Tsex in (' ',' '));
--
alter table js add UNIQUE(Sid);
-- Course
alter table course add PRIMARY KEY(Cno);
alter table course modify Cname varchar(20) NOT NULL;
alter table course add CONSTRAINT c_Credit CHECK (Credir>0);
--
alter table course modify property varchar(10) DEFAULT ' ';
-- SK
alter table SK add primary key(Tno,Cno);
--
alter table SK add foreign key(Tno) references js(Tno);
alter table SK add foreign key(Cno) references course(Cno);
alter table sk add CONSTRAINT c_hours CHECK (hours>0);# データベースプログラミング
-- 1-1
SELECT students.SNO,students.SNAME,Course.Cname,score
FROM score,students,Course
where students.SNO=score.SNO and Course.Cno=score.Cno
ORDER BY score DESC limit 1;#
--
SELECT students.SNO,students.SNAME,Course.Cname,score
FROM score,students,Course
where students.SNO=score.SNO and Course.Cno=score.Cno
ORDER BY score ASC limit 1;
--
drop table if EXISTS class,class_student;
create table class(
Bno varchar(4),
Bname varchar(20)
);
create table class_student(
Sno char(10),
Bno varchar(4)
);
insert into students (SNO,SNAME,AGE,SEX,BPLACE,Polity) VALUES ('8103',' ',20,' ','1999-07-07',' '),('8104',' ',21,' ','1998-01-06',' ');
insert into class values('100',' ');
insert into class_student (Sno,Bno) VALUES ('8103','100'),('8104','100');
-- 1-2
select class.Bname, students.SNO,students.SNAME,students.SEX,students.BPLACE,students.Polity
from class,class_student,students
where class.Bno = 100 and class_student.Sno = students.SNO;
-- 1-3
drop table students;
create table students(
SNO CHAR(10) PRIMARY KEY NOT NULL,
SNAME CHAR(8) NOT NULL,
AGE NUMERIC(3,0),
SEX CHAR(2),
BPLACE CHAR(20),
Polity CHAR(20) DEFAULT ' '
);
insert into students (SNO,SNAME,AGE,SEX,BPLACE,Polity) VALUES ('8103',' ',20,' ','1999-07-07',' '),('8104',' ',21,' ','1998-01-06',' ');
insert into students (SNO,SNAME) VALUES ('8101',' '),('8102',' ');
select * from students;
-- 1-4
insert into students(SNO,SNAME,AGE,SEX,BPLACE) values('8105',' ',20,' ','1999-09-08');
insert into score (SNO,CNO,score) values ('8103','01010101',70),('8104','01010101',50);
--
SELECT avg(score) AVG,Course.Cname,class.Bname
FROM score,students,Course,class,class_student
where Course.Cno=score.CNO and class.Bno=class_student.Bno
and class.Bno=100 and class_student.Sno = students.SNO
and class_student.Sno=score.SNO;
-- 1-5
/*
CASE
WHEN 1 THEN 1
WHEN 2 THEN 2
ELSE
END
*/
SELECT score,(CASE score.CNO WHEN 1 THEN Course.Cname ELSE Course.Cname END) Cname,class.Bname
FROM score,students,Course,class,class_student
where Course.Cno=score.CNO and class.Bno=class_student.Bno
and class.Bno=100 and class_student.Sno = students.SNO and class_student.Sno=score.SNO;
-- 1-6
SELECT students.SNAME,
(case
when score < 60 then ' '
when score BETWEEN 60 AND 90 then ' '
else ' '
end) Level,Course.Cname,class.Bname
FROM score,students,Course,class,class_student
where Course.Cno=score.CNO and class.Bno=class_student.Bno
and class.Bno=100 and class_student.Sno = students.SNO and class_student.Sno=score.SNO;
-- 、
alter table students add class INT DEFAULT 1;# ， 1
--
drop procedure if EXISTS stuscoreinfo;
delimiter $$ # ; $$( )
create procedure stuscoreinfo()
BEGIN
select class,SNAME,SEX,Cname,score
from students,course,score
where students.SNO = score.SNO and course.CNO = score.CNO;
END$$
delimiter ; #
# drop procedure p_name;#
--
call stuscoreinfo();
drop procedure if EXISTS stu_info;
delimiter $$
create procedure stu_info(IN s_sno varchar(10))
BEGIN
select * from students
where s_sno = students.SNO;
END$$
delimiter ;
call stu_info('8101');
/*
， ， ， ；
， DELIMITER $$ ;
$$， ， （ mysql） 。
*/
-- 1-3
drop procedure if EXISTS stu_age;
delimiter $$
set @age = 0;
create procedure stu_age(IN s_sno varchar(10),OUT age INT)
BEGIN
select DATEDIFF('2019-11-20',BPLACE)/365 Age from students
where s_sno = students.SNO;
END$$
delimiter ;
call stu_age('8103',@age);
-- 1-4.5.6
call stuscoreinfo();
call stu_info('8101');
call stu_age('8103',@age);
-- 1-7
show procedure status;
-- 1-8
drop procedure stuscoreinfo;
--
insert into class values('101',' ');
insert into class values('102',' ');
insert into class_student (Sno,Bno) VALUES ('S002','101'),('S003','101'),('S004','102');
--
delimiter $$
create procedure fun_sumscores()
BEGIN
select class.Bname,Course.Cname,avg(score) AVG
from class,score,class_student,Course
where class.Bno=class_student.Bno and class_student.Sno=score.Sno and Course.Cno=score.Cno
group by Course.Cname,class.Bno;
END$$
delimiter ;
DELIMITER $$
create function fun_sumscores() returns int deterministic
begin
declare c int;
SELECT avg(score) from score into c;
return c;
END $$
DELIMITER ;
select fun_sumscores();
-- 1-2
delimiter $$
create function fun_sumscores1(in_class VARCHAR(10),in_cno VARCHAR(10)) returns int deterministic
begin
declare c int;
SELECT sum(score) from score,class_student
where cno = in_cno and in_class = class_student.Bno
and class_student.Sno = score.SNO into c;
return c;
END $$
DELIMITER ;·
select fun_sumscores1('100','01010101');
-- 1-3
DELIMITER $$
create function fun_sumscores2(in_sname VARCHAR(10)) returns int deterministic
begin
declare c int;
SELECT count(*) from students where sname=in_sname into c;
return c;
END $$
DELIMITER ;
select fun_sumscores2(' ');
/*
mysql
select `name` from mysql.proc where db = 'xx' and `type` = 'PROCEDURE' //
select `name` from mysql.proc where db = 'xx' and `type` = 'FUNCTION' //
show procedure status; //
show function status; //
show create procedure proc_name;
show create function func_name;
SELECT * from information_schema.VIEWS //
SELECT * from information_schema.TABLES //
SHOW TRIGGERS [FROM db_name] [LIKE expr]
SELECT * FROM triggers T WHERE trigger_name=”mytrigger” \G
*/