mysql数据库实训分享记录
/*在student表中插入数据*/
INSERT INTO student VALUES ('201215121','李勇','男',20,'cs'),
?? ??? ??????????????????????????? ?????????????????('201215122','刘晨','女',19,'cs'),
? ? ? ? ? ? ? ? ? ? ? ? ? ??? ??? ? ????????????????('201215123','王敏','女',18,'ma'),
? ? ? ? ? ? ? ? ? ? ? ? ? ?? ??? ????????????????? ?('201215125','张立','男',19,'is');
/*创建course表*/
CREATE TABLE course (
? cno char(4) NOT NULL,
? cname char(40) DEFAULT NULL,
? ccredit smallint DEFAULT NULL,
? teacher char(20) DEFAULT NULL,
? PRIMARY KEY (cno)
);
/*在course表中插入数据*/
INSERT INTO course VALUES ('1','数据库',4,'杨老师'),
? ? ? ? ? ? ? ? ? ? ? ? ? ????????????????????????? ('2','数学',2,'贾老师'),
? ? ? ? ? ? ? ? ? ? ? ? ????????????????????????? ? ('3','信息系统',4,'孙老师'),
? ? ? ? ? ? ? ? ? ? ? ? ????????????????????????? ? ('4','操作系统',3,'李老师'),
? ? ? ? ? ? ? ? ? ? ? ????????????????????????? ? ? ('5','数据结构',4,'吴老师'),
? ? ? ? ? ? ? ? ? ? ? ? ????????????????????????? ? ('6','数据处理',2,'杨老师'),
? ? ? ? ? ? ? ? ? ? ? ? ? ????????????????????????? ('7','PASCAL语言',4,'于老师');
/*创建sc表*/
CREATE TABLE sc (
? sno char(9) NOT NULL,
? cno char(4) NOT NULL,
? grade smallint DEFAULT NULL,
? PRIMARY KEY (sno,cno),
? KEY fk2_idx (cno),
? CONSTRAINT fk1 FOREIGN KEY (sno) REFERENCES student (sno),
? CONSTRAINT fk2 FOREIGN KEY (cno) REFERENCES course (cno)
) ;
/*在sc表中插入数据*/
INSERT INTO sc VALUES ('201215121','1',92),
?? ??? ????????????????????????????????????('201215121','2',85),
? ? ? ? ? ? ? ? ? ? ? ??? ?????????????????('201215121','3',88),
? ? ? ? ? ? ? ? ? ? ? ?? ??????????????????('201215122','2',90),
? ? ? ? ? ? ? ? ? ? ? ?? ??????????????????('201215122','3',80);
/*在student表中添加数据类型为日期型*/
ALTER TABLE student ADD s_entrance date;
/*在年龄的数据类型改为int*/
alter table student modify column sage int;
/*添加课程名称必须取唯一性的约束条件*/
alter table course add unique(cname);
/*在course表中添加cpno字段*/
ALTER TABLE `scm`.`course`?
ADD COLUMN `cpno` CHAR(4) NULL AFTER `teacher`;
/*在course表中添加cpno字段的数据*/
UPDATE `scm`.`course` SET `cpno` = '5' WHERE (`cno` = '1');
UPDATE `scm`.`course` SET `cpno` = '1' WHERE (`cno` = '3');
UPDATE `scm`.`course` SET `cpno` = '6' WHERE (`cno` = '4');
UPDATE `scm`.`course` SET `cpno` = '7' WHERE (`cno` = '5');
UPDATE `scm`.`course` SET `cpno` = '6' WHERE (`cno` = '7');
drop table sc;
drop table student;
drop table course;
/*查询出学生表中全部学生信息*/
select * from student;
/*查询所有学生的姓名和所在系*/
select sname,sdept from student;
/*查询全体学生的姓名及其出生年份（假设当时为2020年）*/
select sname,2020-sage as 出生年份 from student;
/*查询选修了课程的学生学号，查询结果要求去掉重复的行（去掉重复学号）*/
select distinct sno from sc;
/*查询CS系的全体学生的姓名*/
select sname from student where sdept='cs';
/*查询CS系的年龄在20岁以下的女生的学号、姓名和年龄*/
select sno,sname,sage from student where sdept='cs' and sage<20 and ssex='女';
/* 查询年龄在18-19岁（包括18岁和19 岁）之间的学生的姓名、系别和年龄*/
select sname,sdept,sage from student where sage between 18 and 19;
/*查询所有姓刘学生的姓名、学号和性别*/
select sname,sno,ssex from student where sname like '刘%';
/*查询没有先行课的课程名称（即Cpno 为NULL）*/
select cname from course where cpno is null;
/*查询选修了课程号为’3’课程的学生的学号及其成绩，查询结果按成绩降序排列*/
select sno,grade from sc where cno='3' order by grade desc;
/*查询学生总人数*/
select count(*) from student;
/* 查询选修1号课程的学生最高分数（用MaxGrade命名）和最低分（用MinGrade命名）*/?
select max(grade) as MaxGrade, min(grade) as MinGrade from sc where cno='1';
/*查询男生和女生的平均年龄*/
select ssex,avg(sage) as ?平均年龄 from student group by ssex;
/*查询平均成绩大于等于90分的学生学号和平均成绩*/
select sno,avg(grade) as 平均成绩 from sc group by sno having avg(grade)>=90;
/*查询有选课的学生及其选修课程的情况*/
select student.*,sc.* from student,sc where student.sno=sc.sno;
/*查询选修2号课程且成绩在80分以上的所有学生的学号、姓名、课程号、成绩*/
select student.sno,sname from student,sc?
?? ??? ??? ??? ??? ??? ?where student.sno=sc.sno and sc.cno='2' and sc.grade>80;
/*查询每一门课的直接先修课的名称*/
select first.cname,second.cname from course first,course second
?? ??? ??? ??? ??? ??? ?where first.cpno=second.cno;
/* 查询每个学生的学号、姓名、选修的课程名及成绩*/
select student.sno,sname,cname,grade from student,course,sc
?? ??? ??? ??? ?where student.sno=sc.sno and sc.cno=course.cno;
/*查询所有学生及选修课程的情况。 */
select student.*,cno,grade?
from student left outer join sc on?
(student.sno=sc.sno);
/* 查询与“刘晨”在同一个系学习的学生学号、姓名、所在系*/
select sno,sname,sdept from student
where sdept in(
?? ?select sdept from student where sname='刘晨');
/*查询选修了课程名为“信息系统”的学生学号和姓名，请分别用以下两种方式表达： */
/*（1）嵌套查询 */
select sno,sname from student
where sno in(select sno from sc where cno in
(select cno from course where cname='信息系统')
);
/*（2）连接查询 ?*/
select student.sno,sname from student,sc,course
where student.sno=sc.sno?
and sc.cno=course.cno?
and course.cname='信息系统';
/*找出每个学生超过他选修课程平均成绩的课程号,要求投影出学号、课程号、成绩。*/
select sno,cno,grade from sc x
where grade >=(
select avg(grade) from sc y where y.sno=x.sno);
/*查询非CS系中比CS系任意一个学生年龄小的学生姓名和年龄，请分别用以下两种方式表达*/
/*用< ?any谓词的子查询*/
select sname,sage from student
where sage < any?
(select sage from student where sdept='cs')
and sdept <> 'cs';
/*用聚集函数*/
select sname,sage from student
where sage < (select max(sage) from student where sdept = 'cs')
and sdept <> 'cs';
/*查询没有选修1号课程的学生姓名*/
select sname from student
where not exists
(select * from sc where sno=student.sno and cno='1');
/*查询选修了全部课程的学生姓名。 */
select sname from student where not exists?
?? ??? ??? ??? ??? ??? ??? ?(select * from course where not exists?
? ? ? ? ? ? ? ? ? ? ? ? ? ? (select * from sc where sno=student.sno and cno=course.cno)
? ? ? ? ? ? ? ? ? ? ? ? ? ? );
?? ??? ?
SELECT Student.Sno,Sname,Course.Cname,Sc.Grade?
from student,sc,course where
student.sno=sc.sno and sc.cno=course.cno
and cname='数据库' and grade>=90;
/*将一个新学生元组（sno：201215128; sname：陈冬; ssex：男; sage：18）插入到Student表中。 */
insert into student(sno,sname,ssex,sage) values('201215128','陈冬','男',18);
/*插入一条选课记录（ '201215128','1 '）到SC表中*/
insert into sc(sno,cno) values('201215128','1');
/*对每一个系，求学生的平均年龄，并把结果存入数据库ST的表DEPT_age中*/
create table dept_age
?? ??? ?(sdept char(15),
? ? ? ? avg_age smallint);
/*将学生201215128 的年龄改为22岁。 */
update student set sage=22 where sno='201215128';
/*将所有学生的年龄增加1岁。 */
update student set sage=sage+1;
/*将CS系全体学生的成绩置0分。 */
update sc set grade = 0
?? ?where sno in (select sno from student where sdept ='cs');
/*从Student表中找出漏填了数据的学生信息； */
select * from student?
?? ??? ?where sname is null?
? ? ? ? or ssex is null?
? ? ? ? or sage is null?
? ? ? ? or sdept is null;
? ? ? ??
/*查询选修了课程但是缺考（成绩为空null）的选课记录； */
select * from sc where grade is null;
/*删除CS系所有学生的选课记录*/
delete from sc where sno in (select sno from student where sdept='cs');
/*删除SC表中所有的学生选课记录。 */
delete from sc;
/* 删除学号为‘201215128’的学生记录。 */
delete from student where sno='201215128';
/* 建立信息系（IS系）学生的视图IS_Student*/
create view is_student as select * from student where sdept='is';
create view is_student1 as select sno,sname,sage from student where sdept='is';
/* 建立IS系选修了1号课程的学生的视图（包括学号、姓名、成绩）IS_S1； */
create view is_s1(sno,sname,grade) as
select student.sno,sname,grade from student,sc
where sdept='is' and sc.cno='1';
/* 建立信息系选修了1号课程且成绩在90分以上的学生的视图IS_S2； */
create view is_s2 as select sno,sname,grade from is_s1 where grade>=90;
/*定义一个反映学生出生年份的视图BT_S； */
create view bt_s(sno,sname,sbirth) as select sno,sname,2023-sage from student;
/*删除视图BT_S和IS_S1； */
drop view bt_s;
drop view is_s1 cascade;
/*通过视图IS_Student找出信息系年龄小于20岁的学生； */
select sno,sage from is_student where sage<20;
/*通过视图IS_Student查询选修了1号课程的信息系学生。 */
select is_student.sno,sname from is_student,sc where is_student.sno=sc.sno and sc.cno='1';
/* 通过视图IS_Student将信息系学生学号”201215122”的学生姓名改为”刘辰” */
update is_student set sname='张莉' where sno='201215125';
/*通过视图IS_Student插入一个新的学生记录，其中学号为”201215129”，姓名为”赵新”，年龄为20岁 */
insert into is_student1 values('201215129','赵新',20);
/* 通过视图IS_Student删除信息系学生学号为”201215129”的记录。 */
delete from is_student where sno='201215129';
/*创建用户*/
create user 'zhangsan'@'localhost' identified by '123';
create user 'lisi'@'localhost','wangwu'@'localhost','zhaoliu'@'localhost';
/*查看用户表 ? 表的结构*/
use mysql;
desc user;
select * from mysql.user;
select host,user from mysql.user;
/*查看数据库*/
show databases;
/*刷新权限*/
flush privileges;
/* 创建连接本地主机localhost的用户“zhangsan”，密码为“123”*/
create user 'zhangsan'@'localhost' identified by '123';
/*同时创建连接本地主机localhost的三个用户“lisi”、“wangwu”、“zhaoliu”，都无密码*/
create user 'lisi'@'localhost','wangwu'@'localhost','zhaoliu'@'localhost';
/* 把查询Student表权限授给用户zhangsan*/
grant select on table scm.student to 'zhangsan'@'localhost';
/* 把查询Student表和修改学生姓名的权限授给用户lisi*/
grant update(sname),select on table student to 'lisi'@'localhost';
/*把对Student表的全部权限授予用户wangwu*/
grant all privileges on table student to 'wangwu'@'localhost';
/*分别查看用户zhangsan、lisi、wangwu、zhaoliu的授权表如下信息列： db,table_name,table_priv,column_priv。*/
select db,user,table_name,table_priv,column_priv from mysql.tables_priv?
where user='zahngsan' or user='lisi' or user='wangwu' or user='zhaoliu';
/*把用户lisi修改学生姓名的权限收回。 */
revoke update(sname) on table student from 'lisi'@'localhost';
/*把用户wangwu对Student表的所有权限收回*/
revoke all privileges on table student FROM 'wangwu'@'localhost';
/*删除用户zhaoliu*/
drop user 'zhaoliu'@'localhost';
create database scm; ? ?/*创建数据库*/
use scm; ? ? ? /*使用该数据库*/
/*1、 分别用两种方法将Student表中的Sno属性定义为码: */
/*（1） 在列级定义主码 */
create table student
?? ??? ?(sno char(9) primary key,
? ? ? ? sname char(20) not null,
? ? ? ? ssex char(2),
? ? ? ? sage smallint,
? ? ? ? sdept char(20)
? ? ? ? );
? ? ? ??
/*（2） 在表级定义主码 */
create table student1
?? ??? ?(sno char(9),
? ? ? ? sname char(20) not null,
? ? ? ? ssex char(2),
? ? ? ? sage smallint,
? ? ? ? sdept char(20),
? ? ? ? primary key (sno)
? ? ? ? );
drop table student1;
/*2、 分别用两种方法将Course表中的Cno属性定义为码: */
/*（1） 在列级定义主码 */
create table course
?? ??? ?(cno char(4) primary key,
? ? ? ? cname char(40),
? ? ? ? cpno char(4),
? ? ? ? ccredit smallint
? ? ? ? );
/*（2） 在表级定义主码 */
create table course1
?? ??? ?(cno char(4),
? ? ? ? cname char(40),
? ? ? ? cpno char(4),
? ? ? ? ccredit smallint,
? ? ? ? primary key (cno)
? ? ? ? );
drop table course1;
/*在表级定义SC表的实体完整性（定义主码）和参照完整性（定义外码）。 */
create table sc
?? ??? ?(sno char(9) not null,
? ? ? ? ?cno char(9) not null,
? ? ? ? ?grade smallint,
? ? ? ? ?primary key(sno,cno),
? ? ? ? ?foreign key(sno) references student(sno),
? ? ? ? ?foreign key(cno) references course(cno)
? ? ? ? ?);
drop table sc;
/*
4、 定义SC表参照完整性的违约处理：?
?（1） 删除或更新Student中相关元组时级联删除或更新SC表中相关元组；?
? （2） 删除Course中相关元组造成SC表中不一致时拒绝删除，
? ? ? ? 当更新course表中的 cno 时，级联更新SC表中相关的元组。
*/
create table sc
?? ??? ?(sno char(9) not null,
? ? ? ? cno char(4) not null,
? ? ? ? grade smallint,
? ? ? ? primary key(sno,cno),
? ? ? ? foreign key (sno) references student(sno)
? ? ? ? on delete cascade?
? ? ? ? on update cascade,
? ? ? ? foreign key(cno) references course(cno)
? ? ? ? on delete no action
? ? ? ? on update cascade
? ? ? ? );
/*
二、有如下员工管理数据库ED，
?? ??? ?数据库中包含以下两张关系表：?
?? ??? ?员工登记表：Employee(No, Name, Sex, Age, Deptno)?
?? ??? ?部门表DEPT: (Deptno , Dname)?
?? ??? ?请写出以下查询的SQL代码并上机运行。
*/
create database ed;
use ed;
/*1、 建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码。 */
create table dept
?? ??? ?(deptno char(20) primary key,
? ? ? ? deptname char(50) not null,
? ? ? ? mname char(50)
? ? ? ? );
/*2、 建立员工登记表Employee，要求工号No在90000~99999之间，姓名不能取空值，
?? ??? ?年龄小于30，性别只能是“男”或“女”。?
*/
create table employee
?? ??? ?(no numeric(6) constraint c1 check (no between 90000 and 99999),
? ? ? ? name char(20) constraint c2 check (name is not null),
? ? ? ? age numeric(3) constraint c3 check (age<30),
? ? ? ? sex char(2) constraint c4 check (sex in ('男','女')),
? ? ? ? constraint empkey primary key(no)
? ? ? ? );
? ? ? ??
/*查看数据库*/
show databases;
/*查看表*/
use information_schema;
show tables;
select * from TABLE_CONSTRAINTS;
select * from TABLE_CONSTRAINTS where table_name='employee';
/*3、 去掉Employee表中对年龄的限制。 */
use ed;
alter table employee drop constraint c3;
/*修改employee表中对年龄的限制。*/
alter table employee add constraint c3 check (age<40);
?