mysql 数据库系统导论源代码




C:\Program Files\MySQL\MySQL Server 5.7\bin


//建表


CREATE TABLE student08sqt(
Sno CHAR(4)PRIMARY KEY,
Sname CHAR(40)UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);




CREATE TABLE SC08sqt(
Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY(Sno,Cno),
FOREIGN key(Sno)REFERENCES student08sqt(Sno),
FOREIGN KEY(Cno)REFERENCES course08sqt(Cno)
);
CREATE TABLE course08sqt(
Cno CHAR(4)PRIMARY KEY,
Cname CHAR(40)NOT NULL,
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN key(Cpno)REFERENCES course08sqt(Cno)
);




//添加记录
INSERT INTO student08sqt(Sno,Sname,Ssex,Sage,Sdept) VALUES(3,'xom','x',21,620);
INSERT INTO student08sqt(Sno,Sname,Ssex,Sage,Sdept) VALUES(1,'aom','l',20,621);
INSERT INTO student08sqt(Sno,Sname,Ssex,Sage,Sdept) VALUES(2,'bom','x',22,622);


INSERT INTO student08sqt(Sno,Sname,Ssex,Sage,Sdept) 
VALUES(4,'1om','x',21,620),(5,'2om','x',21,620),(6,'3om','x',21,620);


INSERT INTO course08sqt() 
VALUES(1,'高数',1,100),(2,'线代',2,100),(3,'数据库',2,100);


INSERT INTO sc08sqt()
VALUES(1,1,85),(2,3,88),(3,2,78)(2,2,98);




//修改 删除


//修改列的数据类型
ALTER TABLE student08sqt
 MODIFY COLUMN Sage tinyint;


//修改某一个值
UPDATE sc08sqt set Grade=89
 WHERE Sno=6 AND Cno=3;




//删除表中的某一列
alter table student08sqt
drop column Sage;


//删除某一个值
delete from student08sqt 
where Sno='5'


//唯一约束
alter table student add unique key(Sname);


//约束默认值为男
alter table student08sqt alter Ssex set default '男';


//grade>0and grade<100
alter table sc08sqt add  check (Grade>0 and Grade<100);


//添加一列出生日期
alter table student08sqt add column deta Sdate not null;


//查询 
//查询某一列 *表示全部
1.SELECT Sno 
FROM student08sqt 
 
//查询年龄小于25岁的
2.SELECT Sname
  FROM student08sqt WHERE Sage<25; 


//出生日期1990年之前
  SELECT Sname,Sage,2018-Sage
  FROM student08sqt WHERE 2018-Sage<1998;


//查询1990年以前出生的女生信息
  SELECT Sname,Sdeta,Ssex  
  FROM student08sqt  WHERE YEAR(Sdeta)>1997 AND Ssex= 'l';


//COUNT（统计） SUM AVG MAX MIN  函数 来求值
  SELECT Max(Grade) FROM sc08sqt WHERE Cno=2;


//隐士链接
  SELECT student08sqt.*,sc08sqt.*
  FROM student08sqt,sc08sqt WHERE student08sqt.Sno=sc08sqt.Sno;




//查询学生学号、姓名、课程号和成绩（包括未选修课程的学生的信息）。
3.SELECT student08sqt.Sno,Sname,Cno,Grade
  FROM student08sqt LEFT OUTER JOIN sc08sqt
  ON(student08sqt.Sno=sc08sqt.Sno)


//查询学生学号、姓名、课程号和成绩（只包括选修了课程的学生的信息）。
4.SELECT student08sqt.Sno,Sname,Cno,Grade
  FROM student08sqt , sc08sqt
  WHERE(student08sqt.Sno=sc08sqt.Sno);


//查询学生学号、姓名、课程名和成绩。
5.SELECT student08sqt.Sno,Sname,Grade,Cname
  FROM student08sqt,sc08sqt,course08sqt
  WHERE student08sqt.Sno=sc08sqt.Sno AND sc08sqt.Cno=course08sqt.Cno






//查询所有与“数据库”有关的课程的信息。//选择从哪几个表里找
6.SELECT *
  FROM student08sqt,sc08sqt,course08sqt
  HAVING Cname ='数据库'








//按学分降序显示课程表中的信息。降序：select * from 表名order by表中的字段desc
7.SELECT *
  FROM course08sqt
  ORDER BY Ccredit DESC




//统计选课成绩表中每门课的平均成绩和选课总人数。
8. SELECT Cno, AVG(Grade),COUNT(Sno)
   FROM sc08sqt
   GROUP BY Cno;  //GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组




//查询未选修课程的学生的学号和姓名
9.SELECT Sno,Sname
  FROM student08sqt
  WHERE NOT EXISTS
    (SELECT*
     FROM sc08sqt
     WHERE Sno= student08sqt.Sno
    );


//查询02号课程的成绩比03号课程高的学生的学号
10.  SELECT a.Sno
	FROM sc08sqt a,sc08sqt b
	WHERE a.grade>b.grade  //a.Sno = b.Sno
     	and a.Cno='2'
    	and b.Cno='3';




//视图
1.创建视图显示学生的学号、姓名、课程号、成绩
    CREATE  VIEW VIEW_student
	AS SELECT
	student08sqt.Sno,Sname,sc08sqt.Cno,Grade	
        FROM student08sqt,sc08sqt
	WHERE student08sqt.Sno=sc08sqt.Sno




2.视图显示学号、姓名、课程名、成绩
    CREATE  VIEW VIEW_student1
	AS SELECT
	student08sqt.Sno,Sname,course08sqt.Cname,sc08sqt.Grade
	FROM student08sqt,sc08sqt,course08sqt
	WHERE student08sqt.Sno=sc08sqt.Sno 
			AND sc08sqt.Cno=course08sqt.Cno
3.视图修改001号张三02课程的成绩为88.
	UPDATE VIEW_student
	SET Grade='88'
	WHERE Sno='1' AND Cno='2';			//AND Sname'张三'


4.创建另一视图只显示学号和姓名
	CREATE VIEW view_student2
	AS 
	SELECT student08sqt.Sno,Sname
5.删除视图
	DROP VIEW view_student2；




//安全


查看mysql数据库
  	 use student08sqt;
查看用户权限
    	select host,user from user ;
1.创建用户sqt 
	create user sqt IDENTIFIED by '123456';   
2.给用户sqt授与studb中student表的查询、修改的权限。
	grant select,update,delete on student08sqt.student08sqt to sqt;
3.查看用户权限
	show grants for sqt;


4.命令更新
	flush privileges;
5.进入sqt用户
	mysql -usqt -p;  //123456


7.测试能否显示studb之外的数据库，能否删除student表中的数据。
	serelct * from student08sqt; ok 
	serlect * from sc08sqt ; no 
   7.1删除student表中的数据
	delete 
	from student08sqt 
	where Sno = '5';


8.进入用户root 更改用户名为张三
	rename user sqt to zhansan;
9.更改密码
	1.)set password for zhansan =password('111111');
	2.)update  mysql.user  set  password=password('111111') 
	   where user='zhansan';
10.回收zhansan的delete权限
	revoke  delete  on  student08sqt.student08sqt  from  zhansan;  //如果权限不存在会报错


11.退出用户
 	mysql > exit;
	mysql > quit;
	mysql > \q;








//存储原理
1.创建存储过程查看学号为‘001’的学生的所有信息（不带参数）
	CREATE PROCEDURE sp_name()
	BEGIN 
	SELECT * FROM student08sqt.student08sqt;
	END;


2． 调用该存储过程//只能查看全部学生信息
	CALL sp_name;


3． 创建存储过程，只要输入任一学号，就可显示该生的所有信息（带参数）
	CREATE PROCEDURE sp_namepara(IN psno CHAR(6))
	BEGIN 
	SELECT * FROM student08sqt.student08sqt
	WHERE Sno=psno;
	END


4． 调用该存储过程 
	CALL sp_namepara('1');




//SQL事务


1.开始事物，提交，update成功。
	SELECT * FROM student08sqt;
	BEGIN;#开始事务
	UPDATE student08sqt SET Sname='bbbbbb'
	WHERE Sname='aom';
	COMMIT;#确认事务
	SELECT *FROM student08sqt;




2..开始事物，回滚rellbake，撤销update。
	SELECT * FROM student08sqt;
	BEGIN;#开始事务
	UPDATE student08sqt SET Sname='2om'
	WHERE Sname='tom';
	ROLLBACK; #回滚操作到begin
	//可以从表里查看结果，




//触发器
涉及一个表
1.创建一个触发器，对成绩进行0-100之间的约束
	CREATE TRIGGER sc_grade
	AFTER update on sc08sqt
	for each ROW
	BEGIN
	DECLARE MSG VARCHAR(200);
   	IF new.grade>100
   	THEN set MSG='成绩超出范围';
   	signal sqlstate 'HY000' SET MESSAGE_TEXT=MSG;
  	END IF;
	END;


涉及两个表之间的数据
2.创建一个触发器，每添加一条数据，自动更新 scount中的数据；
（1）先创建一个表
	CREATE TABLE sccount
	( sccount INT);
（2）更新的值在sccount		
	CREATE trigger trig_count
	after insert  
	on sc08sqt
	for EACH row
	begin
	delete from sccount;
	insert into sccount
	select Cno,count(*)
	from sc08sqt
	group by Cno;
	end