[TOC]
这个主要是将本学期,的实验报告进行一个汇总
一、实验目的
1.安装配置MySQL workbench或者sqlyog客户端,并实现服务的连接。
2.学习mysql workbench 的操作。
3.使用mysql workbench创建school表格,并熟悉对简单数据的录入。
二、实验要求
1.成功运行workbench。
2.创建表格并实现对数据的录入。
三、实验过程
1.运行workbench。
新建一个数据库:在左侧的界面中点击schemas,在空白处右键,创建一个新的数据库。
可以命名为school。新建表,点击apply,即为成功创建一个新的表。在左侧菜单内可以找到自己新建的表。打开表,可以在表格中输入自己想要的数据。
其中,数据库名,表名,表头名均不允许用中文。数据库中表格数据的大写选项解释如下:
NN:not null 非空 UQ:unique 唯一索引 不许重复 BIN:binary 二进制数据(比text更大的二进制数据) UN:unsigned 无符号 整数(非负数) ZF:zero fill 填充0 例如字段内容是1 int(4), 则内容显示为0001 AI:auto increment 自增 G:generated column 生成列
一、【实验目的】
1.学会建立简单的数据库表格,并对常见出现的错误进行试错与分析。
2.学会限制数据的取值范围。
二、【实验要求】
1.进行对表格数据的试错。
2.认识在试错时候出现的错误。(数据类型错误,key键的是否唯一,数据的取值是否在规定的范围内等)
3.学会用外部键处理数据。
三、【实验过程】
一、数据类型错误,key不是唯一,或数据超过规定的长度:都是常见的数据库错误。
二. 限制数据的范围:
用外部键进行数据取值的限制,在sqlyog中实现这一操作。关系外键一般的取名原则为:fk(代表外键)_引用列名_被引用列名。
一、实验目的
二、实验要求
(1) 使用MySQL创建数据库表
(2) 简单使用Data Export,Data import 练习导入和导出数据库
(3) 尝试使用sqlyog对数据库进行cheak约束。
三、实验过程
四、实验反思:
在实验的过程中,我使用的sqlyog版本并不支持,check约束,我无法添加check约束,在解决这个问题的过程中,我查找这些问题,在他人的博客上,说check约束,在MySQL中只是作为一般的注释,对数据库没有约束功能。但是在借用同学计算机使用后,发现check语句能够对MySQL数据库进行约束,在通过了查阅很多资料后。
确认了在不同版本的sqlyog中对这一操作的兼容不同,存在不少的Mysql版本并不支持执行约束。
比如MySQL5.7 可以使用check约束,但check约束对数据验证没有任何作用。添加数据时,没有任何错误或警告,但是MySQL 8.0中可以使用check约束了。
一、实验目的
二、实验要求
三、实验过程
DROP DATABASE `school` ;
-- 删除原有数据库 school
CREATE SCHEMA `school`
DEFAULT CHARACTER SET utf8
COLLATE utf8_bin ;
-- 创建新数据库 school
use school;
-- 选择使用school数据库,避免修改默认数据库sys
CREATE TABLE student (
sno CHAR(10) NOT NULL,
cno VARCHAR(45) NOT NULL,
sage INT NULL DEFAULT 18,
PRIMARY KEY (sno))
COMMENT = '这是一个学生数据库';
-- 创建第一张student表
create table course(
cno char(4) not null primary key,
cname varchar(45) not null,
cpno char(4) references cno,
credit tinyint
);
-- 创建第二张course表
create table sc(
sno char(10),
cno char(4),
score int,
PRIMARY key(sno,cno),
foreign key(sno) references student(sno),
foreign key(sno) references course(cno)
);
-- 创建第三张sc表并设置两个外键
四、拓展学习:
MySQL 外键(FOREIGN KEY)用法:
表与表的记录之间存在着三种关系:一对多、多对多、一对一的关系。处理表之间关系问题就会利用到FOREIGN KEY
约束1:在创建表时,先建被关联的表dep,才能建关联表emp
约束2:在插入记录时,必须先插被关联的表dep,才能插关联表emp
约束3:更新与删除都需要考虑到关联与被关联的关系。
2.MySQL中支持的编码方式:
整形:
tinyint(m) | 1个字节表示(-128~127) |
---|---|
smallint(m) | 2个字节表示(-32768~32767) |
mediumint(m) | 3个字节表示(-8388608~8388607) |
int(m) | 4个字节表示(-2147483648~2147483647) |
bigint(m) | 8个字节表示(+-9.22*10的18次方) |
浮点型:
float(m,d) | 单精度浮点型,8位精度(4字节),m是十进制数字的总个数, d是小数点后面的数字个数。 |
---|---|
double(m,d) | 双精度浮点型,16位精度(8字节) |
字符型:
char(n) | 固定长度的字符串,最多255个字符 |
---|---|
varchar(n) | 固定长度的字符串,最多65535个字符 |
tinytext | 可变长度字符串,最多255个字符 |
text | 可变长度字符串,最多65535个字符 |
mediumtext | 可变长度字符串,最多2的24次方-1个字符 |
longtext | 可变长度字符串,最多2的32次方-1个字符 |
char和varchar:
1.均可以经过指定n,来限制存储的最大字符数长度,char(20)和varchar(20)将最多只能存储20个字符,超过的字符将会被截掉。n必须小于该类型容许的最大字符数。
2.char类型指定了n以后,若是存入的字符数小于n,后面将会以空格补齐,查询的时候再将末尾的空格去掉,因此char类型存储的字符串末尾不能有空格,varchar不受此限制。
\3. 内部存储的机制不一样。char是固定长度,char(4)无论是存一个字符,2个字符或者4个字符(英文的),都将占用4个字节,varchar是存入的 实际字符数+1个字节(n<=255)或2个字节(n>255),因此varchar(4),存入一个字符将占用2个字节,2个字符占用3个 字节,4个字符占用5个字节。
4.char类型的字符串检索速度要比varchar类型的快。
varchar和text:
1.都是可变长度的,最多能存储65535个字符。
2.varchar可指定n,text不能指定,内部存储varchar是存入的实际字符数+1个字节(n<=255)或2个字节(n>255),text是实际字符数+2个字节。
3.text类型不能有默认值。
4.varchar可直接建立索引,text建立索引要指定前多少个字符。查询速度varchar要快于text,在都建立了索引的状况下,text的索引好像没起做用,参见这篇文章:http://forums.mysql.com/read.php?24,105964,105964
日期时间类型(date,time,datetime,timestamp)
date | 日期’2008-12-2’ |
---|---|
time | 时间’12:25:36’ |
datetime | 日期时间’2008-12-2 22:06:44’ |
timestamp | 不固定 |
二进制数据(xxxBlob):
定点数(decimal):
除此之外还有,空间数据类型如: Geometry
:可以存储所有的几何类型,Point
:简单点,LINESTRING
: 简单线,POLYGON
:简单面,MULTIPOINT
:多点,MULITILINESTRING
: 多线,MUILITIPOLYGON
: 很多方面,GEOMETRYCOLLECTION
:任何几何集合
一、实验目的
1.使用mysql语句实现完整性约束。
二、实验要求
1.学习了解MySQL的各种约束
2.学会添加删除约束。
三、实验过程
默认值=缺省值=default value
id int default 1
使用列级约束:把约束的内容定义在列声明之后
表级约束:把约束定义放在所有列声明之后。
约束种类:
按照约束定义的种类来划分:
表级和列级约束。两者声明的方式不同,在所有列级约束声明完以后,在最后一行声明表级约束。
按照约束的内容来划分:
default: 默认约束, 域完整性 not null: 非空约束,域完整性 unique: 唯一约束,实体完整性 primary key: 主键约束,实体完整性 foreign key: 外键约束,参照完整性 check: 检查约束(MySQL不支持),域完整性 auto_increment: 自增长约束 unsigned: 无符号约束 zerofill: 零填充约束
按照作用的范围来划分:
域完整性(对数据表中字段属性的约束) 实体完整性(通过主键约束和候选键位数来实现的) 参照完整性(不仅管理自己表的范围,而且涉及到跟别的表的范围,范围很大,MySQL的外键)
复合约束:两个值不能同时一样。a引用b先创建b,先创建被引用的表。 实验七:SQL语句-数据查询
一、实验目的
掌握利用sql语句进行数据的查询操作
二、实验要求
三、实验过程
#SQL:结构化的查询语言 #交互式SQL,嵌入式SQL #select 目标表达式列表 #from 目标关系列表 #where 目标条件列表 -> 条件子句 #order by 排序子句 #group by 分组子句
use school;
select * from student;
select sno,sname from student;
#重命名
select sno as '学号', sname as '姓名' from student;
#as和单引号可省略,单引号使用防止字符有空格
select sno 学号,sname as '姓 名' from student;select 2022-sage '出生年份' from student;
#不加'出生年份'以'2022-sage'作为列名
select 2022-sage from student;select cno from course;
#可跟函数
select lower(cno) from course;select upper(cno) from course;
#聚集函数/统计查询
select count(*) from student;select count(*) as '班级人数' from student;
#单条件查询where
select sno,sname,ssex from student where ssex='女';select sno,sname,ssex,sage from student where sage<=30 and ssex='男';
select sno,sname,ssex,sage from student where sage<=30 or ssex='男';
#'男'不要加空格,虽然有些软件可以自动去两边空格,仅侥幸
select * from student where sdept='计算机系' or sdept='历史系';
#减短查询:查询集合
inselect * from student where sdept in ('计算机系','音乐系','历史系');select * from student where sage>=23 and sage<25;
#[23,25)
select * from student where not sage>=23 and sage<25;select * from student where sage between 23 and 25;
#并,闭区间[23,25],多条件无法实现
#一个for语句遍历所有的行,where为真true才被选中
#数据库中第三种状态:除了真假,还有未知
select * from course;select * from course where not cpno='C001';
#为空未被选中
select * from course where cpno!='C001';
select * from course where cpno=null;
#无法比较……得到未知
select * from course where cpno is null;
select * from course where cpno is not null;
#方式一select * from course where not (cpno is null);
#方式二#只限制前三条查询:起始下标0,偏移量3(网页分页查询使用)
select * from student where ssex='男' limit 0,3;
#所有的人按系科排序(默认:升序排序asc)-> 字符集utf8 排序规则
utf8_binselect * from student order by sdept desc;
select * from student order by sdept desc, sage asc;
#放最后执行mysql
一、实验目的
二、实验要求
三、实验过程
实验代码:
use school;
select * from student order by sdept asc,sage desc;
select * from course where cpno=null;
#null和任何数字比较得到的结果都是unknown。
select * from course where cpno is null;
select upper(cno),cname from course;
#查阅mysql手册查看各种功能函数
#统计查询
select count(*) from student
group by sdept having 最小年龄>=20;
#having是对分组进行筛选alter,having必须在group by后面。可以使用别名进行筛选,但是应该用变量名
#如果重命名后的变量名有空可以用一对``包括
#模糊查询
select * from student where sname like '刘%';
select * from course where cname like 'C__程序设计%';
select * from course where cname like 'C\_ _程序设计%';
#\的作业是用来转义的,可以使用escape,规定转义字符,如果在查询内容中有\,就应该使用两个\\进行转义
select * from course where cname like 'C#__程序设计%'
escape "#";
一、实验目的
1.多表查询的连接查询
2.多表查询的嵌套查询:不相关子查询
二、实验要求
学习多表查询的用法,并自主拓展学习
三、实验过程
#一、单表查询:单表查询,条件查询,
#3.is null,is not null,
#4.between and ,
#5.count(*),min(*)
#6.目标表达式:重命名,列名、运算、函数;
#7.分组查询 group by 与having子句
#8.模糊查询 like escape ""
#9.排序 order by someone asc/desc
#select 目标表达式列表
#from 目标关系列表
#where 目标条件列表
#group by 分组列列表
#order by 排序列列表
#多表查询 #1.连接查询 #最简单链接:笛卡尔积(交叉连接)cross join #内连接inner join…on:一般连接,等值连接,自然连接(公共属性上的等值连接)nature join #外连接:左外连接left join…on,右外连接right join…on,全外连接full join #2.嵌套查询:不相关子查询(干完就跑路)、相关子查询(交错互通,内层查询都要看一下外层查询,外层查询都要看一下内层查询) #不相关子查询(干完就跑路)in(…) #一个值 ‘=’;多个值 ‘in’或’=ANY’ #sname<>”刘备”:*不包括刘备 #”> ALL” == > max 大于所有的 #”> ANY” == >min 大于某一个 P92
#3.集合查询
待续
use school;
#单表查询 9':
#简单查询、条件查询、空值查询(is null/is not null)、
#between and、count(*) min(*),目标表达式(列名、重名、运算、函数)、
#分组查询(group by...having)、模糊查询(like,escape,"\\")、排序字句(order by...asc/desc)
#select 目标表达式列表 from 目标关系列表 where 目标条件列表(and,or,not分割) group by 分组列 order by 排序依据列+排序方式
select * from sc;
select sno from sc where cno="C003";
#找出选修C001也选修C003的同学学号
select sno,cno from sc where cno="C001" and cno="C003";#错的
(select sno,cno from sc where cno="C001") union (select sno,cno from sc where cno="C003");
select sno from sc where cno="C001" and
sno in (select sno from sc where cno="C003");
select sc.sno,sname,sc.cno,cname,grade from student,course,sc where student.sno=sc.sno and sc.cno=course.cno and sc.cno="C003";
#多表查询
#1.连接查询
select * from student,sc where student.sno=sc.sno;
select sno,sname from student,sc where student.sno=sc.sno and cno="C003";#Error Code: 1052. Column 'sno' in field list is ambiguous
select student.sno,sname from student,sc where student.sno=sc.sno and cno="C003";
select sc.sno,sname from (student inner join sc on student.sno=sc.sno) where cno="C003";
#最简单链接:笛卡尔积(交叉连接)cross join
select * from student,sc;
select * from student cross join sc;
#内连接inner join...on:一般连接,等值连接,自然连接(公共属性上的等值连接)nature join,
select * from student inner join sc;
select sc.sno,sname from (student inner join sc on student.sno=sc.sno) where cno="C003";
select * from student natural join sc;#只剩一行sno
select * from student natural join student;#Error Code: 1066. Not unique table/alias: 'student'
select * from student A natural join student B;#自己与自己自然连接,先相等再去掉一份
#外连接:左外连接left join...on,右外连接right join...on,全外连接full join
select * from student left join sc on student.sno=sc.sno;
select * from sc right join student on student.sno=sc.sno;
select * from student full join sc on student.sno=sc.sno;#Error Code: 1054. Unknown column 'student.sno' in 'on clause'
select * from student full join sc ;
#2.嵌套查询:不相关子查询(干完就跑路)、相关子查询(交错互通,内层查询都要看一下外层查询,外层查询都要看一下内层查询)
#不相关子查询(干完就跑路)
select sno,sname from student where
sno in (select cno from sc where cno="C003");
select sname from student where sdept=(select sdept from student where sname="刘备");#一个值 ‘=’;多个值 ‘in'或'=ANY'
select sname from student where sname<>"刘备" and sdept=(select sdept from student where sname="刘备");#sname<>"刘备" 不包括刘备
select * from student where sdept<>"计算机系" and sage>ANY (select sage from student where sdept="计算机系");
#"> ALL" == > max 大于所有的
#"> ANY" == >min 大于某一个 P92
select * from student where sage>=(select max(sage) from student where sdept ='计算机系');
#等价于
select * from student where sage>=all(select sage from student where sdept ='计算机系');
select * from student where sage<any(select sage from student where sdept ='计算机系');
#小于any等价于小于最大值
#3.集合查询
(select sno,cno from sc where cno="C001") union (select sno,cno from sc where cno="C003");
一、实验目的
1.多表查询的嵌套查询:相关子查询
二、实验要求
学习多表查询的用法,并自主拓展学习
三、实验过程
#嵌套查询
#1.不相关子查询
#2.相关子查询(本节课任务)
相关子查询(交错互通,内层查询都要看一下外层查询,外层查询都要看一下内层查询),相关子查询的时间复杂度远大于不相关子查询。
select sname from student where sname="刘备";
select * from student where sdept=(select sdept from student where sname="刘备") and sname !="刘备";
select * from student a ,student b where a.sname="刘备" and b.sname<>"刘备" and a.sdept=b.sdept;
select b.sname from student a ,student b where a.sname="刘备" and b.sname<>"刘备" and a.sdept=b.sdept;
select * from student X where sname<>"刘备" and exists(select * from student Y where sname ="刘备" and Y.sdept=X.sdept);
-- 以这一查询语句为例使用相关子查询的时间复杂度为n2+n(可以认为为n2的时间复杂度),而实现相同工能的不相关子查询子需要n的时间复杂度
#EXISTS() 存在谓词,存在为真,反之为假
select sname as "姓名" from student where exists (select * from sc where sno=student.sno and cno ="C003");
#查询选修了全部课程的学生的信息
select * from student where not exists(
select * from course where not exists(
select * from sc where student.sno =sc.sno and sc.cno=course.cno)
);
#查询选修了全部学分为2的课程的学生的信息
select * from student where not exists(
select * from course where credit =2 and not exists(
select * from sc where student.sno =sc.sno and sc.cno=course.cno)
);
#查询选修了学号为202006001选修的全部课程的学生的信息
select sno from sc X where not exists(
select * from sc Y where Y.sno="202006001" and not exists(
se lect * from sc Z where X.sno =Z.sno and Y.cno=Z.cno)
);
#关系代数在除法的运算在SQL语句中没有对应运算,通过存在谓词表示
一、实验目的
二、实验要求
三、实验过程
INSERT INTO `school`.`student` (`sno`, `sname`, `ssex`, `sage`, `sdept`) VALUES ('202006018', '司马懿', '男', '22', '数学系')
第一次执行该程序,该条数据被正确的传入数据库,而在已经插入一次数据之后再次执行会显示
Error Code: 1062. Duplicate entry ‘202006018’ for key ‘student.PRIMARY’
这里的显示数据其实是,因为主码约束的结果,主码发生冲突。
DELETE FROM `school`.`student` WHERE (`sno` = '202006018');
将刚刚输入的数据进行删除,第一次执行删除语句,没有出现问题,继续执行该语句删除操作的返回时间发生改变,返回时间为0,但是删除语句成功执行完成
在SC表中插入司马懿的关联数据
INSERT INTO `school`.`sc` (`sno`, `cno`, `grade`) VALUES ('202006018', 'C007', '90'),('202006018', 'C006', '86'),('202006018', 'C005', '89');
再次执行删除语句
反馈为Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`school`.`sc`, CONSTRAINT `fk_sno` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`))
因为有外键约束的存在,sc表中的数据sno依赖于student表中的sno,在这种情况下,数据没有成功被删除
DELETE FROM `school`.`sc` WHERE (`sno` = '202006018') and (`cno` = 'C005');
DELETE FROM `school`.`sc` WHERE (`sno` = '202006018') and (`cno` = 'C006');
DELETE FROM `school`.`sc` WHERE (`sno` = '202006018') and (`cno` = 'C007');
删除表中关于202006018号学生的信息,再次执行删除语句,数据被成功删除
在将学生表在的202006018号学生信息删除后,再次执行
INSERT INTO `school`.`sc` (`sno`, `cno`, `grade`) VALUES ('202006018', 'C007', '90'),('202006018', 'C006', '86'),('202006018', 'C005', '89');
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`school`.`sc`, CONSTRAINT `fk_sno` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`))
因为外键约束的存在,student表中没有该学号存在,DBMS会拒绝插入语句的执行。
数据更新相关
update sc set grade=grade+10;
发生MySQL发生报错,错误信息为Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
根据报错信息上网搜索相关原因
报错原因:现在是在mysql的safe-updates模式中,如果where后跟的条件不是主键,就会出现这种错误。
知道问题,解决方法就简单了,要么where后面加上主键的条件,要么就更改模式:
SET SQL_SAFE_UPDATES = 0;
在语句前面增加 SET SQL_SAFE_UPDATES = 0;语句,增加该语句之后之前的语句可以正常执行了,多次执行之后发现数据超过100,估计是一开始数据库建立的时候没有进行约束。
一、实验目的
二、实验要求
三、实验过程
select * from student where sdept="计算机系" and sage > 20;
-- 为其定义视图,as作为关键字必不可少,select语句作为视图的数据来源
create view cs_view as select * from student where sdept="计算机系" and sage > 20;
-- 查询视图,对视图的查询实际上是执行定义中基本表的查询
select * from cs_view;
-- 一个基本表可定义多个视图
create or replace view cs_view as select * from student where sdept ="计算机系";
-- Select 语句中引用了不可更新的视图Where子句中的子查询,引用from子句中的表;视图与表的区别视图是从一个或多个表(或视图)导出的虚拟表。而表有时也称为基本表——base table。即视图所对应的数据不进行实际存储,数据库中只存储视图的的定义,对视图的数据进行操作时,系统根据视图的定义去操作与视图相关的基本表。在information_schema数据库的tables表中有记录。
select * from cs_view;
-- create view cs_course_view as select sc.sno,sname,sc.cno, from student,sc,course
create view sage_sdept_view as select avg(sage) ,sdept from student group by sdept;
drop view sage_sdept_view;
select * from sage_sdept_view;
select avg(sage) ,sdept from student group by sdept;
INSERT INTO `school`.`cs_view` (`sno`, `sname`, `ssex`, `sage`, `sdept`) VALUES ('202006011', '赵云', '男', '20', '计算机系');
INSERT INTO `school`.`cs_view` (`sno`, `sname`, `ssex`, `sage`, `sdept`) VALUES ('202006019', '黄忠', '男', '20', '物理系');
select * from cs_view;
create or replace view cs_view as select * from student where sdept ="计算机系" with check option;
INSERT INTO `school`.`cs_view` (`sno`, `sname`, `ssex`, `sage`, `sdept`) VALUES ('202006019', '黄忠', '男', '20', '军事系');
INSERT INTO `school`.`cs_view` (`sno`, `sname`, `ssex`, `sage`, `sdept`) VALUES ('202006011', '赵云', '男', '20', '计算机系');
可更新视图要通过视图更新基本表的数据,必须保证视图是可更新的,既可以在insert update等语句中当中使用它们。对于可更新的视图,在视图中的行为和基本表中的行之间具有一对一的关系。还有一些特定的其他结构,这类结构会使得视图不可更新。
使用视图有以下优点:
1为用户集中数据,简化用户的数据查询、修改、删除和更新,2屏蔽数据库的复杂性3简化用户的权限管理,4便于数据共享,5可以重新组织数据以便输出到其他应用程序中;
一、实验目的
二、实验要求
三、实验过程
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。
创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件),实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
CREATE INDEX indexName ON student (ssex);
-- 创建索引
DROP INDEX indexName ON student;
-- 删除索引
CREATE TABLE mytable(
ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX indexName (username)
);
-- 创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
-- 唯一索引 它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一
-- CREATE UNIQUE INDEX indexName ON table_name(column_name) ;
-- 可以使用 SHOW INDEX 命令来列出表中的相关的索引信息
SHOW INDEX FROM student;
-- 使用ALTER 命令添加和删除索引
-- 有四种方式来添加数据表的索引:
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):
# 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):
# 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): #添加普通索引,索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):
# 该语句指定了索引为 FULLTEXT ,用于全文索引。
显示索引信息
你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。
一、实验目的
二、实验要求
三、实验过程
知识导入:
存储过程,函数和触发器之间的区别:
三者的特点:
USE school ;
DELIMITER $$
CREATE PROCEDURE insert_student (
p_sno CHAR(10),
p_sname VARCHAR (50),
p_sex VARCHAR (1),
p_age INT,
p_dept VARCHAR (20)
-- 在存储过程中定义的变量跟表中数据没有直接联系
-- 在数据定义时声明长度可以大于原数据库的数据长度,不会报错,但是在数据输入时弱数据大于数据库要求会造成截断。
)
BEGIN
# 声明变量info,以表明插入是否成功。
DECLARE info VARCHAR (20) DEFAULT '插入成功' ;
# 异常处理
DECLARE CONTINUE HANDLER FOR 1062 SET info = '插入失败,不能插入重复的数据' ;
INSERT INTO student
VALUES
(p_sno, p_sname, p_sex, p_age, p_dept) ;
SELECT
info ;
END $$
DELIMITER ;
SET @sno = '202006020' ;
SET @sname = '魏延' ;
SET @ssex = '男' ;
SET @sage = 25 ;
SET @sdept = '计算机系' ;
CALL insert_student (@sno, @sname, @ssex, @sage, @sdept) ;
SELECT * FROM student ;
-- select @@version 查询版本 系统变量
-- 查询存储过程 触发器 函数 游标的区别与联系
DELIMITER $$
CREATE PROCEDURE search_sc(
p_sno CHAR(10),
p_cno CHAR(10),
OUT p_grade INT
)
BEGIN
DECLARE info VARCHAR(30) DEFAULT '查找成功';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET info='查找失败';
SELECT grade INTO p_grade FROM sc WHERE sno=p_sno AND cno=p_cno;
SELECT info;
END $$
DELIMITER ;
SET @vsno='202006001';
SET @vcno='C001';
CALL search_sc(@vsno,@vcno,@p_grade);
SELECT @p_grade;
# 函数:
SHOW VARIABLES LIKE '%func%';
SET GLOBAL log_bin_trust_function_creators = 1;
-- 第一次使用MySQL的函数功能发生报错1418解决方法如下
-- 1.先查看函数功能是否开启:show variables like '%func%';
-- 2.开启:SET GLOBAL log_bin_trust_function_creators = 1;
-- 3.关闭:SET GLOBAL log_bin_trust_function_creators = 0;
USE school;
DELIMITER $$
DROP FUNCTION IF EXISTS `get_avgscore`;
-- 在MySQL中在已有定于函数之后,我们无法直接在进行操作,我们需要把原有函数删除。
CREATE FUNCTION get_avgscore(f_sno CHAR(10))
RETURNS FLOAT
BEGIN
RETURN (SELECT ROUND(AVG(grade), 2)
FROM SC WHERE sno=f_sno);
END $$
DELIMITER ;
SELECT get_avgscore('202006001');
一、实验目的
二 、实验要求
三、实验过程
知识导入:
触发器是特殊的存储过程,存储过程需要程序调用,而触发器会自动执行。
代码内容:
触发器与游标相关:
delimiter $$
create function get_avgscore(f_sno int)
returns float
begin
return(select round(avg(score),2)
from sc
where sno = f_sno);
end $$
delimiter ;
use school;
delimiter $$
create trigger trg_student_delete2
before delete on student
for each row
begin
delete from sc where sno = old.sno;
end $$
delimiter ;
show triggers from school;
select * from student;
delete from student
where sno = '202006002';
select * from sc;
select * from student;
delimiter $$
create function get_avgscore(f_sno char(2))
returns float
begin
return(select round(avg(score),2)
from sc where sno=f_sno);
-- return后面括号内部的语句最后一条不加分号,类似于select后面的元素,return后面整体的加分号
end $$
delimiter ;
delimiter $$
create function get_avgscore(f_sno char(3))
returns float
begin
return(select round(avg(score),2)
from sc where sno=f_sno
);
end $$
delimiter ;
drop function if exists get_avgscore; -- 删除时函数名后面不加括号
drop function if exists get_avgscore;
drop function if exists get_avgscore;
drop procedure if exists get_avgscore;
drop procedure if exists get_avgscore;
delimiter $$
create procedure sum()
begin
declare sums3 int;
declare sums5 int;
if(sums3 > sums5) then
select 's3的总成绩大于s5的总成绩';
elseif(sums3 < sums5) then
select 's3的总成绩小于s5的总成绩';
else
select 's3的总成绩等于s5的总成绩';
end $$
delimiter ;
use school;
create table if not exists `store` (
'id' int(11) not null auto_increment,
'name' varchar(20) not null,
'count' int(11) not null default '1',
primary key ('id')
) engine=innodb default charset=latin1 auto_increment=7;
二、用户操作
# 1.查看所有用户信息。
SELECT USER,HOST FROM mysql.user;
# 2.创建用户user1,user3,user4密码自定,主机形式分别为localhost、%和你的主机IP地址,并再次查看用户是否创建成功。。
CREATE USER 'user1'@'localhost' IDENTIFIED BY '1';
CREATE USER 'user3'@'%' IDENTIFIED BY '3';
CREATE USER 'user4'@'192.168.82.41' IDENTIFIED BY '4';
SELECT USER,HOST FROM mysql.user;
# 3.修改用户user4的名字为user5,并修改其密码(自定),其他不变。
RENAME USER 'user4'@'192.168.82.41' TO 'user5'@'192.168.82.41';
ALTER USER 'user5'@'192.168.82.41' IDENTIFIED WITH mysql_native_password BY '5';
RENAME USER 'user5'@'localhost' TO 'user5'@'192.168.82.41'; -- 主机名也可以修改,但本题没要求
SELECT USER,HOST FROM mysql.user;
# 4.删除用户user5。
DROP USER user5@192.168.82.41;
SELECT USER,HOST FROM mysql.user;
– 二、权限操作
SELECT HOST,USER,create_priv,create_user_priv ,grant_priv FROM mysql.user;
GRANT CREATE ON *.* TO user1@localhost;
GRANT CREATE USER ON *.* TO user1@localhost;
SELECT HOST,USER,create_priv,create_user_priv,grant_priv FROM mysql.user;
在db1数据库中创建t1和t2表,结构和内容自定。测试能否向表t1中插入数据和查询数据,为什么?
– 以下代码在user1的连接下书写并执行:
CREATE DATABASE db1;
CREATE TABLE t1
( tid SMALLINT PRIMARY KEY,
tname VARCHAR(10));
CREATE TABLE t2
( tid2 SMALLINT PRIMARY KEY,
tname2 VARCHAR(10));
INSERT INTO t1 VALUES(1,'Tom'); -- 没有该权限
SELECT * FROM t1; -- 没有该权限
CREATE USER 'user2'@'localhost' IDENTIFIED BY '2';
– 以下代码在root的连接下书写并执行,去验证是否有user2用户。
SELECT USER,HOST FROM mysql.user; -- 查看是否有user2用户
查看user1的相关权限
SELECT HOST,USER,select_priv,insert_priv FROM mysql.db;
GRANT INSERT ON db1.* TO user1@localhost;
GRANT SELECT ON db1.* TO user1@localhost WITH GRANT OPTION;
SELECT HOST,USER,select_priv,insert_priv FROM mysql.db; -- 查看是否授权成功
SELECT * FROM t1;
SELECT * FROM t2;
GRANT GRANT OPTION ON db1.* TO user1@localhost;
GRANT SELECT ON db1.t1 TO user2@localhost;
SELECT * FROM mysql.tables_priv;
SELECT * FROM t1;
SELECT * FROM t2;
回收权限
#1在root的连接下,回收user1查询db1数据库中所有表的权限授,回收完成后再次查看user1的权限。
REVOKE SELECT ON db1.* FROM user1@localhost;
SELECT HOST,USER,select_priv,insert_priv FROM mysql.db;
#2在user1的连接下,测试其是否还能查询t1和t2表的内容。
SELECT * FROM t1;
SELECT * FROM t2;
#3在user2的连接下,测试其是否还能查询t1表的内容。这说明什么问题?
SELECT * FROM t1;
三、角色管理
CREATE ROLE role1;
SELECT USER,HOST FROM mysql.user;
GRANT UPDATE ON db1.t1 TO role1;
SELECT * FROM mysql.tables_priv;
GRANT role1 TO user2@localhost;
SELECT * FROM mysql.tables_priv; -- user2还没有这个权限
SELECT CURRENT_ROLE(); -- 结果为空
SET ROLE role1;
SELECT CURRENT_ROLE(); -- 结果有了role1角色
SELECT * FROM t1;
UPDATE t1 SET tname='Marry' WHERE tid=1; -- 修改成功
SELECT * FROM t1;
REVOKE role1 FROM user2@localhost;
-- 在user2的连接下,测试是否还能修改t1表
UPDATE t1 SET tname='Marry2' WHERE tid=1; -- 不成功
DROP ROLE role1;