Mysql数据库
什么是数据库？
数据库：存储数据的仓库
定义：高效的存储和处理数据的介质
数据库的分类
关系型数据库（sql）
大型
Oracle：属于Oracle（甲骨文）公司，主要用于java开发
DB2:属于IBM公司，主要用金融，电信…..
中型
SqlServer:属于微软，一般用于微软自己的语言
Mysql :由AB公司初创，后被sun收购，sun被Oracle收购主要用于java开发
小型
Access
非关系型数据库(Nosql:not only sql)
MongoDB、redis、memcached
两种数据库区别
关系型数据库数据存储在磁盘上,数据安全,但是效率低;
非关系型数据库数据存储在内存上,数据不安全,断电会丢失数据,但是效率高;
此外,关系型数据库存储结构是二维表,比较浪费空间
关系型数据库
什么是关系型数据库？
一种建立在关系模型（数学模型）上的数据库
关系模型包含三个方面：
1、数据结构：二维表，解决数据如何存储
2、操作指令集合：所有的SQL语句，解决如何处理数据
3、完整性约束：表内数据约束（字段与字段），表与表之间约束（外键）
数据语言
SQL(Structured Query Language),结构化查询语言,在数据库中90%以上的操作是查询操作
SQL分为三个部分
数据定义语言(DDL Data Defined Language)
用于维护存储数据的结构
create table | database |…
alter table | database | …
drop table | database |…
数据操作语言(DML Data Manipution Language)
用来对数据进行操作
Insert Update Delete
内部单独分了一个DQL(Data Query Language),数据查询指令：select
数据控制语言(DCL Data Control Language)
主要负责用户权限管理
Grant Revoke Commit deny
数据库的使用
Mysql数据库的安装
参见安装文档
Mysql的登录与退出
方式一：mysql -h 主机名 –u用户名 –p密码
-h:该命令用于指定客户端所要登录的MYSQL主机名，登录当前机器，该参数可以省略
-u:表示用户，一般为root
-p:表示密码，一般为root
方式二：mysql –u用户名 –p+enter
输入密码
退出Mysql
方式一：\q或者quit
方式二：exit
该方式不仅可以退出终端，还可以关闭终端窗口
Mysql自带的数据库
查询库的命令：show databases;
infromation_schema:存放当前数据库软件的一些配置
mysql:是mysql的核心库
performance_schema:存放一些预信息
test:系统信息和windows交互的信息
注意：对于终端中已经存在的数据库，不要做任何操作，特别是mysql数据库
创建数据库
案例：在数据库中新建一个数据库，以自己名字的首字母命名数据库
语法：create database 数据库名 [数据库选项];
数据库选项
数据库选项可选，用来约束数据库。
数据库选项分为两个
- 字符集character set 或 charset 字符集名称（通常为gbk或utf8）
- 校对集（校对集是数据比较规则，字符集设置好了就会有设置对应的校对集），collate 校对集名称
create database jsyunsi character set gbk;
在数据库的data文件夹下会产生一个该数据库名称的文件夹,里面有一个opt文件,里面有默认的字符集和校对集
create database 中国;
在数据库中可以新建以中文名字命名的数据库，但是发现在数据库的Data包中是乱码，所以一般不会新建以中文名字命名的数据库
修改数据库
数据库名称不可以被修改，只有数据库选项可以修改，而选项中校对集又依赖于字符集,所以修改字符集时校对集会自动修改
语法：alter database 数据库名称 数据库选项 数据库选项值
Alter database jsyunsi charset utf8;
删除数据库
语法：drop database 数据库名；
drop database 中国;
创建数据库表
Mysql数据库表设计
数据库中存储数据的方式都是以二维表(由行和列组成表格)的方式
如：老师在教室给学生上课，需要设计几张表？
教师teacher表：
姓名、性别、年龄、所教课程、工号………
教室classroom表：
教室编号、教室类型…….
学生student表：
姓名、性别、年龄、专业、学号……..
课程lession表：
…….
例：student表
| 姓名 | 性别 | 专业 | 爱好 | 学号 |
| 王俊凯 | 男 | 表演 | 唱歌 | 001 |
| 易洋千玺 | 男 | 表演 | 跳舞 | 002 |
| 王源 | 男 | 表演 | 吹b | 007 |
Mysql的数据类型
为什么数据要分类型:
一是存储数据时更好与显示对应起来,不同字段存储的数据类型不一样
二是可以节省空间,针对某种类型的数据,就分配对应的内存空间用于存储
SQL中数据类型分为三大类:数值型,字符串型,时间日期型
整型： int（默认为有正负符号的，除非声明为unsigned）
BIT、tinyint
小数型：SQL将小数数据分为两类：浮点型和定点型
浮点型：精度有限，超出精度范围会进行四舍五入，丢失精度
定点型：精度固定，不会丢失精度
浮点型：
Float:单精度，占用4个字节存储数据，精度范围7位左右
Double:双精度，占用8个字节存储数据，精度范围15位左右
定点型：
decimal(7,3) 1234.567 7代表去掉小数点后总的位数 3代表小数位
字符型： char(M) 长度固定 N<M 仍然占用M个字节 浪费存储空间 存取速度快
varchar(M) 长度不固定 N<M 占用N 个字节 节省存储空间 存取速度慢
Text 文章等类型 查询不区分大小写
ENUM 枚举类型 单项 Clon ENUM(‘f’,’m’)
SET 枚举类型 多项’a,b,c’ 可添加 ‘abc’要加单引号
布尔型： MySql里面是没有布尔型的,在mysql中用tinyint(1)表示boolean型,1表示 true,0表示false
日期型： date（’2017-03-03’） time（’10：10：10’）
Datetime（’2017-03-03 10：10：10’）
year（4位数’2017’ 2 位数’17’）
Timestamp:时间戳从1970年开始，格式与datetime一致
什么时候用char()？什么时候用varchar()?
如果存储的数据长度固定，用char,比如手机号码，性别，同一所大学学生学号
如果数据长度不固定，用varchar()
创建数据库表
案例：创建一张学生表
student
学号（stuNo） varchar(20)
姓名（name） varchar(20)
性别（sex/gender） char(4)
年龄（age） int
专业（major） varchar(30)
爱好（hobby） varchar(50)
体重（weight） double
是否婚配（isMarry） boolean
入学时间 （goSchoolTime） date
创建表的语法
create table 表名（
字段名1 字段类型1,
字段名2 字段类型2,
字段名3 字段类型3,
……..
字段名n 字段类型n
);
发现报错：原因没有选择该表创建的数据库
在创建表之前必须指明该表创建在哪个数据库
语法：use 数据库名
use jsyunsi;
create table student(
stuNo varchar(20),
name varchar(20),
sex char(4),
age int,
major varchar(30),
hobby varchar(50),
weight double,
isMarry boolean,
goSchoolTime date
);
Mysql中的注释
1、多行注释
/*
这是多行注释
和java中多行注释一样
*/
- 单行注释
方式一：-- 这是单行注释
方式二：# 这也是单行注释
创建一张dog表
姓名name
性别sex
年龄age
/*
创建一张dog表
这是多行注释
*/
Create table dog(
#狗狗的姓名，这是单行注释
Name varchar(20),
-- nihao
Sex char(4),
Age int
)engine=innodb default charset gbk;
为了防止乱码问题，在创建表时也需要规定字符集
查看数据库中的表
Show tables;
删除数据库中的表
语法：drop table 表名；
Drop table dog;
对表结构操作（了解）
显示表结构
方式一：
desc/describe 表名；
Field:表示表中字段（表头）
Type：表中字段的类型以及大小
Null:如果该列是YES,表示该字段可以没有数据，但是在添加时，会自动填充默认值
Default:默认值，在null列为yes并且用户没有给该列填值时，填充default对应的值
Key:表示键，主键，外键，唯一键…..
Extra：备注，一般最常用自增长
方式二：
Show columns from 表名；
方式三：
show create table 表名
关键字
在数据库中关键字不能直接作为表名，数据库名，字段名，如果需要使用关键命名，那么可以用返单引号包裹
返单引号可以用于将关键字变成普通字段，但是在今后一般不使用关键字命名
返单引号按键：在esc下的那个键
修改表名
语法：Rename table 原 表名 to 新表名
rename table student to t_student;
语法alter table 原表名 rename to 新表名
alter table t_student rename to student;
添加字段
Create table dog(
Name varchar(20),
Sex char(4),
Age int
)engine=innodb default charset gbk;
语法：alter table 表名 add 字段名 字段类型 位置
案例：在dog表中添加一个type字段
alter table dog add type varchar(30);
如果在添加语句后不添加位置，那么默认添加在最后一个
案例：在dog表中添加一个字段weight
alter table dog add weight double first;
如果需要添加的字段在第一个位置，那么就在位置处填写first
案例：在age字段后添加一个字段height
alter table dog add height double after age;
只有在第一个位置添加的时候用first,其余位置都可以用after
删除字段
语法：alter table 表名 drop 字段名
alter table dog drop weight;
修改字段
修改字段类型
语法;alter table 表名 modify 字段名 修改后的字段类型
alter table dog modify name varchar(40);
修改字段位置
语法;alter table 表名 modify 字段名 字段类型 位置
alter table dog modify height double first;
alter table dog modify height double after name;
修改表中字段名
语法：alter table 表名 change 原字段名 修改后字段名 字段类型
alter table dog change height weight double;
数据库的备份与还原
- MySQL数据库的备份，在数据库外：
语法：mysqldump -u用户名 -p密码 数据库名 > 备份地址
如：mysqldump -uroot -proot jsyunsi > D:\student.sql
导出数据库中的表结构及表中的数据。不包含该数据库。
2、MySQL数据库的还原
还原此种方式备份的数据时，必须在该数据库服务器中创建一个数据库，并指 定将该数据备份文件拷贝到该数据库中。
第一种，在数据库外。
mysql -uroot –proot jsyunsi< D:\student.sql
第二种，进入数据库,use jsyunsi后
source d:/student.sql；
对表中数据操作
创建一张学生表，包含以下字段：
CREATE TABLE stu(
sno int,
Name varchar(20),
age int,
gender char(4),
address varchar(20),
salary double,
tel varchar(20),
Birthday date
)engine = Innodb default charset gbk;
添加数据
方式一：
语法：insert into 表名(字段名1，字段名2…..字段名n)values (值1，值2…..值n);
Insert into stu (sno,name,age,gender,address,salary,tel,birthday) values ('1', '王永', '23', '男', '北京', '1500','110', '1990-1-19');
insert into stu (stuno) values ('003');
此种方式添加数据，没有赋值的字段填充默认值，用于正规项目
方式二：
语法：insert into 表名values (值1，值2…..值n);
注意：此种方式必须表中有多少个字段，就添加多少个数据，并且需要一一对应
一般用于测试数据
Insert into stu values ('2', '张雷', '25', '男', '辽宁', '2500','110452', '1992-11-1');
批量添加
insert into 表名 （字段名1，字段名2…..字段名n values (值1，值2…..值n)，(值1，值2…..值n)，(值1，值2…..值n)，(值1，值2…..值n)……;
Insert into stu values ('2', '张雷', '25', '男', '辽宁', '2500','110452', '1992-11-1'),('3', '李强', '22', '男', '北京', '3500','110245', '1993-12-1'), ('4', '宋永合', '25', '男', '北京', '1500','3402', '2001-11-17'), ('5', '叙美丽', '23', '女', '北京', '1000','63465', '1995-1-1'), ('6', '陈宁', '22', '女', '山东', '2500','46346', '1998-1-1');
初级查询
语法：select *（全部）或者字段名 from 表名；
Select * from stut;-------------查询所有,查询出来的是事实存在的表
select name,gender,age from stu;------查询需要的字段-----查询出来的是虚表
%与_
语法：select */字段名 from 表名 where 条件
案例：查询出姓王的学生信息
%：表示零个或多个字符
_:表示一个字符
Like：表示像、类似于
Select * from stu where name like ‘王%’;
案例：查询stu表中名字中带有’小’字的学生姓名
Select name from stu where name like ‘%小%’；
案例：查询出姓王并且名字为一个字的学生信息
select * from stu where name like '王_';
转义字符
在数据库中添加如下数据
姓名以r_开头的学生信息
案例：查询出姓名以r_开头的学生信息
select * from stu where name like 'r_%';
此种方式表示r开头，后面跟一个字符，然后在跟零个或多个字符，此处的下划线不代表下划线，
用转义字符表示下划线
select * from stu where name like 'r\_%';
运算符
> < >= <= != =
案例：查询表中性别为男的学生年龄和性别
select age,gender from stu where gender = '男';
案例：查询stu表中年龄小于20且性别为男的学生信息
select * from stu where age < 20 and gender = '男';
And和or
And（&&）:表示与
select * from stu where age < 20 && gender = '男';
Or（||）：表示或
案例：查询表中年龄小于20或address为北京的学生姓名
select age,address,name from stu where age<20 || address = '北京';
select age,address,name from stu where age<20 or address = '北京';
is
案例：查询表中性别为null的学生信息
select * from stu where gender is null;
Between……and
用于查询连续区间的数据，是闭区间
案例：查询年龄在18到22之间的学生信息
select * from stu where age between 18 and 22;
select * from stu where age >= 18 and age <= 22;
in
用于查询离散区间
案例：查询学号为3，13，23，33的学生信息
select * from stu where sno=3 or sno=13 or sno=23 or sno=33;
select * from stu where sno in (3,13,23,33);
Not
案例：查询学号不为3，13，23，33的学生信息
select * from stu where sno not in (3,13,23,33);
算数运算符
+ - * /
案例：计算学生的年薪
select salary*12 from stu;
此种方式查出的数据都是虚表，数据库中数据没有发生改变
修改数据
语法：update 表名 set 字段名1 = 修改后的值，字段名2=修改后的值……字段名n=修改后的值
update student set stuno = '007',age = 18;
此种方式会修该所有的数据
语法：update 表名 set 字段名1 = 修改后的值，字段名2=修改后的值……字段名n=修改后的值 where 条件
update student set stuno = '006',age = 23 where name = 'james';
update student set name = 'aa',sex = 'nv' where isMarry = false;
删除数据
语法：delete from 表名；
delete from student;----此种方式会将数据全部删除
delete from 表名 where 条件
delete from stu where name like '%小%';
查询可以使用的条件，在修改和删除中都可以使用
数据控制语言
查看数据库用户
Use mysql------show tables------select * from user;
此时可以看到用户名和密码，但是密码是通过md5加密技术加密
创建用户
语法：create user 用户名 identified by 密码
create user james identified by '123456';
注意：创建用户，用户名不需要用单引号包裹，但是密码需要用单引号包裹
修改密码
语法：set password for "用户名" = password("密码");
set password for "james" = password("111111");
删除用户
语法：drop user 用户名；
drop user james;
赋予权限
授权：root通过mysql命令给指定的用户授予特定的权利
权利包含DDL、DML、DQL而且细致入微
语法：grant 权限 on 数据库名.表名 to 用户
grant select on jsyunsi.stu to james;
grant insert on jsyunsi.stu to james;
grant all on jsyunsi.* to james;
将所有的权限赋予给james子用户，子用户可以对jsyunsi数据库中的所有表进行操作
显示用户权限
语法：show grants for 用户名；
show grants for james;
回收权限
如果按照顺序授权，那么授权过程的权限允许叠加，
但是回收权限也要回收所有的层次的权限回来
语法：revoke 权限 on 数据库名.表名 from 用户名
revoke all on jsyunsi.stu from james;
刷新权限
flush privileges;
把所有的权限都收回来
revoke all on *.* from "james";
约束
非空约束 not null
默认值约束 default
唯一键约束 unique
主键约束 primary key
外键约束 foreign key
自增长 auto_increment
非空约束
create table test(
name varchar(20) not null,
password varchar(20) not null
)engine=innodb default charset gbk;
如果字段后添加了非空约束，那么在添加数据时，该字段必须赋值
默认值约束
create table test(
name varchar(20) not null,
sex char(4) default '男'
)engine=innodb default charset gbk;
唯一键约束
Unique:表示唯一，唯一键约束的字段可以为空，并可以有多个为空
一张表中可以有多个唯一键
方式一：在字段类型后添加
create table test(
name varchar(20) unique,
password varchar(30) unique
)engine = innodb default charset gbk;
方式二：联合唯一键
在字段添加完成之后，添加唯一键
create table test(
name varchar(20),
password varchar(30),
unique key(name,password)
)engine = innodb default charset gbk;
此种方式只有两个数据完全一样，才算重复，有一个一样，不算重复
方式三
在表创建完成以后，添加唯一键
create table test(
name varchar(20),
password varchar(30)
)engine = innodb default charset gbk;
alter table bb add unique key(name);
删除唯一键
语法：alter table 表名 drop index 下标名
alter table test drop index name;
主键约束
Primary key :每张表只能有一个主键且每张表必须要有主键，主键表示一张表的唯一标识,主键不能为空
什么样的字段可以作为主键？
在表中存在真实意义的字段，部门编号、姓名
可以定义一个不具备意义的字段作为主键 如id
方式一：在字段后添加
create table test(
id int primary key,
name varchar(20),
password varchar(30)
)engine = innodb default charset gbk;
注意：以后查看表结构用show create table 表名
因为 主键标识 和 唯一键加非空约束 效果一样
方式二：在字段最后添加主键,此种方式可以添加联合主键
联合主键表示一个主键
create table test(
id int,
name varchar(20),
password varchar(30),
primary key(id,name)
)engine = innodb default charset gbk;
方式三：在表创建完成以后添加主键
create table test(
id int,
name varchar(20),
password varchar(30)
)engine = innodb default charset gbk;
alter table test add primary key(id);
删除主键
Alter table test drop primary key;
自增长
Auto_increment:自动增加相应的幅度
一般和主键配套使用
添加自增长的字段，在key一栏中必须有值
自增长不会自动补齐，而且通过show create table 表名方式可以看到下一次增长的数是几
create table test(
id int primary key auto_increment,
name varchar(20),
password varchar(30)
)engine = innodb default charset gbk;
create table test(
sno int primary key,
name varchar(20),
password varchar(30)
)engine = innodb default charset gbk;
alter table tests modify sno int auto_increment;
删除自增长
alter table test modify sno int；
修改自增长
1.如果是修改自增长字段,必须先将原字段的自增长去除,再给新的字段添加自增长
如果是表格创建时,忘记给id设置自增长,插入一些值(比如1,2,3,4)数据后,才想起要设置自增长,需要先将自增长偏移设置成5( set auto_increment_offset = 5)才行,否则默认自增长从1开始,会与已经创建好的数据发生冲突,添加自增长会报错,虽然这样的修改是会话级别,但是最好还是在添加自增长后手动将自增长偏移值修改回默认的1(set auto_increment_increment = 1)
2.修改自增长的值:修改必须是往大的数字修改,不能往小的数字修改
为什么自增长的起始值是1,每次自增长步长也是1呢?
那是因为mysql服务器默认设置是这样.可以用show variables like ‘auto_increment%’查看
可以对自增长初始值和步长进行修改
但是要注意,修改是对整个数据库进行修改,不仅仅是对单张表进行修改,但是通常不修改(如果作出了修改,修改是会话级别,只对当次连接有效)
Set auto_increment_increment = 5;
外键
Foreign key :外键
两张表一旦建立主外键关系，如果需要删除表，那么必须先解除主外键关系
一张表中可以拥有多个外键
create table class(
classid int primary key auto_increment,
name varchar(20)
)engine = innodb default charset gbk;
create table students(
stuid int primary key auto_increment,
name varchar(20),
classid int,
foreign key(classid) references class(classid)
)engine = innodb default charset gbk;
删除外键
删除外键语法：alter table 表名 drop foreign key 索引值
alter table students drop foreign key students_ibfk_1;
create table class(
classid int primary key auto_increment,
name varchar(20)
)engine = innodb default charset gbk;
create table students(
stuid int primary key auto_increment,
name varchar(20),
classid int
)engine = innodb default charset gbk;
create table lianxi(
id int primary key auto_increment,
classid int,
stuid int)engine=innodb default charset gbk;
alter table 表名 add (CONSTRAINT 索引名) foreign key(外键值) references 表名（主键）
alter table lianxi add foreign key(classid) references class(classid);
alter table lianxi add constraint aa foreign key(stuid) references students(stuid);
外键作用
外键约束对父表(外键指向的主键所在表)和子表(外键字段所在表)分别有不同的约束
对子表
在子表中添加或修改数据时,如果新记录外键字段的值不属于父表主键的值,也就是说在父表中没有对应的引用,那么操作失败
对父表
在父表中删除或更新数据时,如果操作会导致已经被子表引用的主键字段值消失,也就是说本来已经建立好的引用关系会消失,那么操作失败
创建外键的要求
1.外键字段的数据类型必须与父表中的主键类型一致
2.添加外键的数据表的存储引擎必须是innodb,否则外键添加成功,也不具备外键约束作用
3.一张表中不能出现同名外键
4.当外键字段中有值后,再添加外键时,已有的值必须在父表主键中有,否则无法创建成功
外键约束
前面提到的外键的作用是默认的外键约束,外键约束事实上可以根据需求指定
三种类型外键约束,都是针对父表的约束
1.strict:严格外键约束(默认的),父表中不能删除或更新一个已经被子表引用的数据.
严格外键约束下,要想删除父表中的记录,必须先删除子表记录,然后才能删除父表中对应的记录,需要注意删除先后顺序.
2.cascade:级联约束,父表对数据进行更新或者删除,子表也跟着更新和删除
3.set null:置空约束,父表中数据删除后,子表中的数据置空
通常建立外键时约束模式会选择:父表删除操作选置空约束,更新操作选级联约束,即:
Foreign key(外键字段) references 父表(主键字段) on delete set null on update cascade
注意:
将外键设置成删除置空约束的前提是外键字段允许为空,否则在创建外键时无法成功
另外,外键虽然约束作用很好,但是如果有多张表与同一张父表的主键建立了外键联系,对父表主键进行操作会影响到多张表格,这会造成对数据操作结果的不确定性,所以使用外键时要格外小心
索引
所有的索引都是建立在字段之上
有了索引,数据库系统会根据算法生成单独索引文件,可以实现快速查找数据
给字段增加索引的情况:
1.因为索引文件比较大,可能比数据本身还打,非常占用磁盘空间,如果某个字段经常作为查询字段使用,那么该字段就应该加上索引
2.如果要保证某个字段的唯一性,那么就应该加上主键/唯一键索引约束数据的有效性
Mysql中提供了多种类型的索引
1.主键索引:primary key
2.唯一键索引:unique key
3.全文索引:fulltext key
4.普通索引:index
范式
数据库的设计范式是数据库设计所需要满足的规范,满足这些规范的数据库是简洁的、结构明晰的.在关系数据库中,这种规范就是范式.
关系型数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、Boyce-Codd范式（BCNF）、第四范式（4NF）和第五范式（5NF）.
最低要求的范式是第一范式（1NF）.在第一范式的基础上进一步满足更多要求的称为第二范式（2NF）,其余范式以次类推.一般说来,数据库只需满足第三范式（3NF）就行了.
第一范式（1NF）
第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项,同一列中不能有多个值,值具有不可分割性
表中代课时间字段就不满足第一范式,因为如果想要获得某位老师的开始代课时间,还需要将代课时间进行拆分
解决方案：将代课时间字段设计成两个字段”开始上课时间”和”结束上课时间”
第二范式(2NF)
当表中存在复合主键,而且存在某些字段仅仅有复合主键中单独字段决定,不是又复合主键中字段共同决定,即部分依赖现象,这种设计就不满足第二范式.
| 学号(pri) | 姓名 | 年龄 | 课程(pri) | 学分 | 分数 |
|
|
|
|
|
|
|
这个表格中运用了复合主键,唯一性约束是一个学号对应的学生只能选某个课程一次,避免了同一学生重复选某个课程的情况.
决定关系是:(学号,课程) → (姓名,年龄,成绩,学分)
但是该表格不满足第二范式,因为存在下面的决定关系
(学号)->(姓名,年年龄)
(课程)->(学分)
由于不满足第二范式,表格会出现下面的问题
(1) 数据冗余：
当同一门课程由n个学生选修,"学分"就重复n-1次；同一个学生选修了m门课 程,姓名和年龄就重复了m-1次.
(2) 更新异常：
若调整了某门课程的学分,数据表中所有行的"学分"值都要更新,很耗费时间,否 则会出现同一门课程学分不同的情况.
(3) 插入异常：
假设要开设一门新的课程,暂时还没有人选修.由于还没有"学号"关键字,课程名 称和学分也无法记录入数据库
(4) 删除异常：
假设一批学生已经完成课程的选修,这些选修记录就应该从数据库表中删除.但 是,与此同时,课程名称和学分信息也被删除了.很显然,这也会导致插入异常.
| Id(pri) | 学号(unique) | 姓名 | 年龄 | 课程(unique) | 学分 | 分数 |
|
|
|
|
|
|
|
|
解决办法:去除复合主键,就不存在部分依赖关系了
Id = 学号+课程
没有复合主键了,也就不存在部分依赖了,也就满足了第二范式
第三范式(3NF)
表设计满足第二范式还不够,还需要满足第三范式
当一张表中存在B字段依赖主键A字段,C字段不直接依赖于主键A字段,而是通过依赖B字段间接依赖主键A字段,这种传递依赖就不符合第三范式,第三范式要求表设计不能存在传递依赖
| Id(pri) | 学号(unique) | 姓名 | 年龄 | 课程(unique) | 学分 | 分数 |
|
|
|
|
|
|
|
|
上表虽然满足第二范式,学号和课程依赖于主键id,但是姓名/年龄/学分/分数这些不直接依赖于主键id,这些字段是通过学号和课程间接依赖于主键,存在传递依赖,不满足第三范式
解决办法:拆分成三个表:学生信息表,课程信息表,学生选课表
学生信息表
| Id(pri) | 姓名 | 学号(unique) | 年龄 |
| 1 | 张三 | 1101 |
|
| 2 | 李四 | 1102 |
|
Id = 学号
课程信息表
| Id(pri) | 课程(unique) | 学分 |
| 1 | 大学英语 |
|
| 2 | 高等数学 |
|
Id = 课程
学生选课表
| 学生id(pri) | 课程id(pri) | 成绩 |
| 1 | 2 | 78 |
| 2 | 2 | 80 |
在学生表和课程信息表里面多添加一个id字段作为逻辑主键,将其设置为自增长,真正的业务主键是学号和课程,一个id对应这一个学号或课程,为了防止学号和课程重复存储,额外用唯一键约束,这样做以后,在选课表中就不用按照学号和课程名来找成绩了,只需要根据学生id和课程id就可以找到对应成绩,避免重复存储西湖好和课程名,用存储数据来代替,从而节省磁盘空间
逆规范化
拿上面的学生选课表来说,如果我们需要经常查询某位学生选的课程的成绩,因为在选课表中存的是id信息,需要用到多表查询,通过id找到学生名和课程名(效率低),由于是经常查询,所以我们会不按照规范设计表格,直接在选课表中存储学生姓名和课程名信息,虽然会牺牲一点磁盘空间,但换来的是查询效率
学生选课表
| 学生id(pri) | 课程id(pri) | 成绩 |
| 1 张三 | 2 高等数学 | 78 |
| 2 李四 | 2 高等数学 | 80 |
高级查询
Select [select选项] 字段名/* form 数据源 where…..group by…..having….order by…..limit…
注意：如果这些选项存在，那么就必须按照该顺序填写
select选项
案例:查询emp表的信息
Select * from emp;
Select all * from emp;
如果select选项为distinct，那么查询出来的数据可以去重
案例：请查询出emp表中领导的编号
Select distinct mgr from emp;
group by
Group by :按照…..分组
案例：将emp表中的员工按照job进行分组
Select * from emp group by job;
发现查询出的数据存在问题：查询出的都是每个job的第一条数据
原因：数据库中的group by主要用于统计，一般和count()函数一起使用
Count()函数：用于统计数量
括号中可以填写*也可以填写字段名
select count(*) from emp;
select count(mgr) from emp;
如果填写的是*，那么就统计表中所有的行数
如果填写的是字段名，那么就统计该字段下的非空字段数量
案例：将emp表中的员工按照job进行分组
Select count(*),job from emp group by job;
Select count(*),job,group_concat(ename) from emp group by job;
having
Having的功能和where一模一样，但是where是从硬盘中取真实数据，而having是从内存中取的虚表数据
案例：查询emp表中按照job分组后人数大于3人的分组信息
select count(*),job from emp group by job having count(*) > 3;
别名
Select 字段 as 别名 from 表名
案例：查询表中员工的年薪
select sal*12 as money,ename from emp;
案例：查询表中员工年薪大于20000的员工信息
select sal*12 as money,ename from emp having money > 20000;
Where是从实表中取数据，在emp表中没有money字段，所以条件判断不能用where
而having可以从虚表中取数据，所以此处可以使用having
order by
Order by :按照…..顺序
顺序：asc
逆序（倒序）：desc
案例：将emp表中的员工信息按照工资顺序展示
select * from emp order by sal asc;
案例：将emp表中的员工信息按照工资顺序展示，如果工资相同，按照名字逆序排列
select * from emp order by sal asc,ename desc;
limit
Limit:分页
Limit 数字：表示展示几条数据
select * from emp limit 3;
Limit 数字1，数字2
数字1表示数字1后的一条数据开始展示
数字2表示展示几条数据
select * from emp limit 8,3;
初级函数
最大值：max()
最小值：min()
平均值：avg()
求和：sum()
案例：计算emp表中员工的最高薪资，最低薪资，总工资，平均薪资
select max(sal),min(sal),sum(sal),avg(sal) from emp;
案例：计算每个部门的平均薪资
select avg(sal),deptno from emp group by deptno;
案例：计算每个员工的年薪
select sal*12+comm as money from emp;
此种方式发现有很多员工的年薪为空------因为comm为空
如何解决？-----------ifnull(可能出现空的字段，代替空字段的值)
select sal*12+ifnull(comm,0) as money from emp;
案例：在stu表中添加两个字段，身高和体重，并随机赋值
Rand（）随机函数--------[min,max）----rand()*（max-min）+min
Ceil()向上取整
Floor()向下取整
alter table stu add height double;
alter table stu add weight double;
update stu set weight =floor(rand()*80+120),height =ceil(rand()*20+175) where gender='男';
update stu set weight =floor(rand()*20+80),height =ceil(rand()*20+150) where gender='女';
date函数
返回当前日期
select CURDATE();
返回当前时间
select CURTIME();
返回当前具体时间
select SYSDATE();
select now();
一年的第几个月 参数是当前时间
select MONTH(now());
一年的第几周 从0开始
SELECT WEEK(SYSDATE());
一周的第几天
select DAYOFWEEK(now());
高级函数
函数分为系统函数和自定义函数
系统函数
系统函数是系统已经定义好的函数,直接调用就可以
任何函数执行结果都有返回值,调用函数都是通过select语句
字符串操作函数
Mysql中字符串操作的基本单位(大多数情况是以字符为基本单位)
1、Substring(string,offset,length):字符串截取子串
2、char_length(string):返回字符串按字符个数
3、length(string):返回字符串占字节个数
4、instr(str1,str2):查找str2在str1中是否存在,如果存在,则返回第一次出现的位置,如果不存在则返回0
5、lpad(str,len,padstr):左填充,用padstr内容向左填充str字符串至指定长度len
6、insert(str,pos,len,newstr):替换插入,将str字符串中从pos位置开始,长度为len的字符串体替换成newstr,返回替换后的字符串
7、strcmp(str1,str2):字符串比较,如果st1大于str2返回1,等于返回0,小于返回-1
自定义函数
新增函数
函数要素:函数名,参数列表,返回值类型,函数体
语法:
Create function 函数名([形参列表]) returns 返回值类型
Begin -- 当函数体只有一条return语句时,可以省略begin和end
函数体
Return 返回值; -- 必须与定义函数时指定类型相同
End
查看函数
查看所有函数
Show function status [like ‘pattern’];
查看函数的创建语句
Show create function 函数名;
删除&修改函数
函数不能修改,只能先删除,然后新增
删除函数
Drop function 函数名;
函数参数
函数参数分为形参(定义函数时使用)和实参(调用函数时使用,实参可以是值也可以是变量)
形参必须指定参数类型
Create function 函数名(形参 字段类型) returns 数据类型
在函数内部用@自定义的函数也可以访问
变量作用域
全部变量可以在任何地方使用,局部变量只能在函数内部使用
全局变量:使用set关键字定义,使用@符号标识,可以在任何地方使用
局部变量:使用declare关键字定义,没有@符号,所有局部变量的定义必须在函数开始之前
数据库关系
现实生活中实体与实体之间的关系反映到数据库设计上面来,关系指的是不同表格中记录之间的关系
一对一
一个表格A中一条记录对应另一个表格B中一条记录
举例：
| Id(Pri) | 姓名 | 性别 | 年龄 | 籍贯 | 婚否 | 住址 |
|
|
|
|
|
|
|
|
学生表
一个学生有很多信息,但是除了姓名,性别,年龄,这些经常查询的信息外,后面三个信息不是经常查询,如果显示出来反而会产生干扰,所以一般会把信息分为经常查询和不经常查询两个表
学生常用信息表A
| Id(Pri) | 姓名 | 性别 | 年龄 |
|
|
|
|
|
不常用信息表B,表格A和表格B无联系.解决办法:在表格B中添加字段Id与表格A对应
| Id(pri) | 籍贯 | 婚否 | 住址 |
|
|
|
|
|
这样表格A和B中的一条记录就对应对方中的一条id相同的记录
一对多
一对多指的是一张表A中对应着另外一张表B中的多条记录,但是表B中的一条记录只能对应表A中的一条记录
举例:
妈妈与孩子,一个妈妈可以有多个孩子,但是一个孩子只能有一个妈妈
妈妈表A
| M_Id(pri) | 姓名 | 年龄 | 联系方式 |
|
|
|
|
|
孩子表
| C_Id(pri) | 姓名 | 年龄 | 联系方式 | M_ID |
|
|
|
|
|
|
两张表分别表示妈妈与孩子的信息,但是妈妈与孩子之间没有建立起联系不知道孩子的妈妈是哪位.解决方法:在孩子表中添加M_ID字段
妈妈表中一条记录可以对应孩子表中的多条记录,即一位妈妈可以有多个孩子;而孩子表中一条记录只能对应妈妈表中一条记录,即一个孩子只能有一位妈妈.
多对多
多对多关系:一个表(A)中的一条记录可以对应着另外一张表(B)中的多条数据,同时B表中的一条记录可以对应A表中的多条记录
举例:
老师和学生之间的关系,一位老师可能教过多名学生,一名学生可能上过多位老师的课
老师表
| T_Id(pri) | 姓名 | 年龄 | 联系方式 |
|
|
|
|
|
学生表
| S_Id(pri) | 姓名 | 年龄 | 联系方式 |
|
|
|
|
|
这样设计两张表没有什么问题,但是老师和学生之间没有建立起联系,如果向上面一样通过添加字段的方法,在老师表中添加学生S_id字段,那么每一条记录,该字段的值会是一大长串,这是不符合数据库的设计原则的.解决办法是单独新建一个表,将老师和学生关联起来
老师学生关联表
| Id(pri) | T_id | S_id |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
这样涉及就可以实现多对多的关系,老师1教过两位学生1,2;学生1上过两位老师1,2的课程
找学生上过哪些老师的课:学生id->关联表中获取多位老师id->每个老师id在老师表中有一条记录
找老师教过哪些学生:老师id->关联表获取多位学生id->每个学生id在学生表中有一条记录
嵌套查询(子查询)
案例：查询哪些人的工资在平均工资之上
第一步：计算平均工资
select avg(sal) from emp;
第二步：完成需求
Select ename,sal from emp where sal >( select avg(sal) from emp);
案例：查询员工工资最高的员工姓名
第一步：查询最高工资
Select max(sal) from emp;
第二步：完成需求
Select ename,max(sal) from emp where sal = (Select max(sal) from emp);
案例：查询出在销售部工作的员工姓名
第一步，查询销售部的部门编号
Select deptno from dept where dname = ‘sales’;
第二步：完成需求
Select ename from emp where deptno = (Select deptno from dept where dname = ‘sales’);
案例：查询king所在部门名称
第一步：查询king所在部门编号
Select deptno from emp where ename= ‘King’；
第二步：完成需求
Select dname from dept where deptno = (Select deptno from emp where ename= ‘King’);
联合查询
概念
联合查询是可合并多个相似的选择查询的结果集，等同于将一个表追加到另外一个表，从而实现将两个表的查询组合到一起，使用谓词为UNION或NUION ALL
本质就是将多个查询额结果合并到一起（纵向合并）：字段数不变，多个查询记录数合并
应用场景
1、将同一张表中不同的结果（需要对应多条查询语句来实现），合并到一起展示
2、在数据量大的情况下，会对表进行分表操作，需要对每一张表进行部分数据统计。使用联合查询将数据存放到一起
语法
select 语句
Union [union 选项]
select 语句
union选项，与select选项基本一样
Distinct:去重，去除完全重复的数据（默认）
All:保存所有结果
案例
1、测试union选项
SELECT * FROM emp
UNION
SELECT * FROM emp;
因为默认去重，所以只查询出14条数据
2、字段不需要匹配
注意细节：union理论上只要保证字段数一样，不需要每次拿到的数据对应的字段类型一致
SELECT ename,deptno,sal FROM emp WHERE deptno = 10
UNION
SELECT sal,ename,deptno FROM emp WHERE deptno = 30;
当两条查询语句字段不相同时，永远只保留第一个select语句对应的字段名字。
order by 使用
1、在联合查询中如果需要使用order by 那么对应的select语句需要用括号括起来
2、order by 在联合查询中如果要有效果，必须加limit
limit后面数值一般使用一个较大的值，大于对应表的记录数
3、查询30部门sal升序排序，10部门sal降序排序
(SELECT * FROM emp WHERE deptno = 10 ORDER BY sal DESC LIMIT 20)
UNION
(SELECT * FROM emp WHERE deptno = 30 ORDER BY sal ASC LIMIT 20);
4、查询job为salesman的hiredate升序排序，manager的hiredate降序排序
(SELECT * FROM emp WHERE job = 'salesman' ORDER BY hiredate DESC LIMIT 20)
UNION
(SELECT * FROM emp WHERE job = 'manager' ORDER BY hiredate ASC LIMIT 20);
连接查询
什么是连接查询？
连接查询就是将多张表连到一起进行查询（会导致记录数行和字段数列发生改变）
连接查询的意义？
在关系型数据库设计过程中，实体与实体之间是存在很多联系的，在关系型数据库表的设计过程中，遵循着关系来设计。一对一，一对多，多对多，通常在实际操作过程中，需要利用这层关系来保证数据的完整性。
内连接
内连接Inner join，从一张表中取出所有的记录去另外一张表中匹配，利用匹配条件进行匹配，成功了则保留，失败了就放弃
原理
1、从第一张表取出数据，然后去另外一张表中进行匹配
2、利用匹配条件进行匹配
匹配成功则保留，继续向下匹配
匹配失败则向下继续执行
如果全表匹配失败，结束
语法
Select 需要查询的字段
From 表1
Inner join 表2
On 建立连接的条件
查询条件
案例
select * from emp e
inner join dept d
on d.deptno = e.deptno;
此种方式是拿emp表中的数据去匹配dept表中的数据，所以emp表数据在前
select * from dept d
inner join emp e
on d.deptno = e.deptno;
此种方式是拿dept表中的数据去匹配emp表中的数据，所以dept表数据在前
对应的40部门由于没有数据，所以没有打印出数据,只用匹配成功才会显示数据
1、查询king所在部门名称
Select d.dname
From dept d
Inner join emp e
On e.deptno = d.deptno
Where e.ename = ‘king’;
2、查找每个部门的人数，部门名称，部门编号
Select count(*),d.dname,e.deptno
from emp e
inner join dept d
on e.deptno = d.deptno
Group by e.deptno;
3、显示至少有4个员工的部门，显示部门编号，名称，位置，人数
Select count(*) num,d.dname,e.deptno,d.loc
from emp e
inner join dept d
on e.deptno = d.deptno
Group by e.deptno
Having num > 4;
4、查询comm不为null的部门名称
select d.dname,e.ename,e.comm from emp e
inner join dept d
on e.deptno = d.deptno
where e.comm is not null;
5、列出受雇日期早于直接上级的员工编号，姓名，部门名称
第一步：列出受雇日期早于直接上级的员工的编号和姓名
Select d.deptno,d.empno,d.ename
From emp e
Inner join emp d
On e.empno = d.mgr
Where e.hiredate> d.hiredate;
第二步
select d.deptno,d.empno,d.ename,dept.dname
From emp e
Inner join emp d
On e.empno = d.mgr
Inner join dept
On dept.deptno = d.deptno
Where e.hiredate> d.hiredate;
应用
内连接一般是在对数据有精确要求的地方使用，必须保证两张表中都能进行数据匹配。
外连接
外连接：outer join，按照某一张表作为主表（表中所有记录在最后都会保留），根据条件去连接另外一张表，从而得到目标数据
外连接分为：
左外连接（left join）:左表是主表，数据都会保留
右外连接（right join）:右表是主表，数据都会保留
原理
1、确定连接主表：左连接就是left join 左边的表为主表，right join就是右边为主表
2、拿主表的每一条记录去匹配另外一张表（从表）的每一条记录
3、如果满足匹配条件，保留，不满足，不保留
4、如果主表记录在从表中一条都没有匹配成功，那么也要保留，从表对应字段值都为null
语法
左外连接 left join
Select 字段
From 主表
left join 从表
On 条件;
右外连接 right join
Select 字段
From 从表
right join 主表
On 条件;
Left join 以左表为参照物去查询数据
Right join以右表为参照物去查询数据
案例
求出员工及其经理人
左外连接 left join
Select e.ename,d.ename
From emp e
left join emp d
On e.empno = d.mgr;
左连接以左表为主表，拿左表中的数据去匹配从表中的数据
拿e表中的empno去匹配d表中的empno相当于以上级为主去匹配下属，一个上级可以拥有多个下属，因此查询出来的数据会超过数据库的记录数
右外连接 right join
Select e.ename,d.ename
From emp e
right join emp d
On e.empno = d.mgr;
右连接以右表为主表，拿右表中的数据去匹配从表中的数据
拿d表中的empno去匹配e表中的empno相当于以拿员工去匹配上级，一个员工肯定存在一个上级（CEO除外），所以查询出来的数据与记录数一样
1、求部门平均薪水的等级
第一步：求出每个部门的平均薪水
select deptno,avg(sal) as avg_sal from emp group by deptn
第二步：将虚表和salgrade表连接
条件：虚表中avg_sal between 最低工资 and 最高工资
select deptno,avg_sal,grade from
(select deptno,avg(sal) as avg_sal from emp group by deptno)t1
inner join
salgrade s
on (t1.avg_sal between s.losal and s.hisal);
2、求部门平均的薪水等级
第一步:求出每个人的薪水等级
select deptno,empno,grade
from emp e
inner join salgrade s
on (e.sal between s.losal and s.hisal)
第二步:分组
select t.deptno,avg(t.grade) as avg_grade
from
(select deptno,empno,grade
from emp e
inner join salgrade s
on (e.sal between s.losal and s.hisal))t
group by t.deptno;
3、平均薪水最高的部门编号与名称
第一步：每个部门的平均薪水
select avg(sal) as avg_sal ,deptno from emp group by deptno;
第二步：最高的平均薪水
select max(t1.avg_sal), t1.deptno from
(select avg(sal) as avg_sal ,deptno from emp group by deptno) t1;
C:求出结果
select t2.money,d.deptno,d.dname
from dept d
inner join
(select max(t1.avg_sal) as money, t1.deptno from
(select avg(sal) as avg_sal ,deptno from emp group by deptno) t1) t2
On t2.deptno = d.deptno;
4、平均薪水等级最低的部门编号和名称
select p.deptno,p.dname,min(s_grade)
from ( select s.grade s_grade, t.dname,t.deptno
from salgrade s
inner join (select d.dname,avg(sal) as avg_sal,e.deptno
from dept d
inner join emp e
on d.deptno=e.deptno
group by d.deptno) t
on(t.avg_sal between s.losal and s.hisal) ) p;
事务
什么是事务?
事务是指一串连续的操作
事务安全是指,保证这一系列操作同时实现的安全机制
例如:有2个银行账户,A账户要向B账户转账1000元,要分2个操作:
1.A账户减少1000元
2.B账户增加1000元
但是当A减少1000元时,突然断电了,结果是A账户减少了1000元,但B没有增加1000元
事务安全就是要保证1,2这两个操作构成的事务都完成后才将数据存储到数据表中
面试题：事务的四大特性（ACID）：
A(atomic)：原子性,事务中的全部操作是一个整体,要么全部成功,要么全部失败；
C(consistency)：一致性：事务开启后,数据表中的数据状态没有变化,因为还没有提交；
I(Isolation)：隔离型,多个事务操作是相互隔离,互不影响的；
D(Durability):持久性,事务一旦提交,数据表就发生永久改变.
事务安全分为两种:
一是自动事务(默认的,每此操作就自动写到表中了)
二是手动事务,可以保证多个操作完成后才将结果写到表中
事务操作
验证自动事务:jack向mary转1000元
操作一:jack账户减少1000元
操作二:mary增加1000元
但是完成操作一时突然断电.由于默认是自动事务,操作一步,结果就会被存到数据表中
我们希望的是要等jack账户余额减少,mary账户余额增加这两步操作都成功完成,才将两部操作的记录保存到表格中,需要用到手动事务
验证手动事务:jack向mary转1000元
1.开启手动事务
2.jack账户减少1000元
减少1000元后,虽然jack账户显示余额减少,但实际上这个结果没有写到数据表中,用另外一个客户端查询结果如右图所示
3.mary账户增加1000元
操作的客户端显示mary余额增加1000元,但实际上也没有写到数据表中,用另外一个客户端查询结果如右图所示
4.提交事务结果,上面操作的结果是准确的,所以提交,写到数据表中
提交后事务操作结果才被写到数据表中
如果事务操作有误,那么可以不选择提交(commit;),选择回滚事务(rollback;)(清空事务操作的日志),这样操作结果就不会写到数据表中
注意:
使用事务解决安全问题的前提是存储引擎必须是innodb!
事务操作原理
开启事务后,所有操作都会保存在事务日志中,只有当执行commit命令后,才会将日志同步到表中,事务对数据的操作才会在表中生效,其他清空都会清空事务日志,事务操作无效(rollback,断电,断开数据库连接等)
回滚点
回滚点:在某个操作成功后,后续操作可能成功也可能失败,但是不管是成功还是失败,前面的操作已经成功了,我们可以在已经成功的操作处设置一个点,可供后续操作失败时返回该点,而不是返回所有的操作,这个就叫做回滚点
设置回滚点语法:savepoint 回滚点名字;
返回回滚点语法:rollback to 回滚点名字;
混滚点运用场景举例:去取钱,插卡输入密码成功,点击取款,跳转到请输入取款金额,卡里有4000元,输入5000元,操作失败,如果退卡让用户重新输入密码显然不合适,应该返回重新输入取款金额才对,这里就用到了回滚点
回滚点演示
自动事务
在mysql中,默认是自动事务,用户操作完就会自动提交数据到表格中
自动提交是通过变量autocommit来控制
Show variables like ‘autocommit’;
关闭自动提交
Set autocommit = off/0;
关闭自动提交后,对表格进行数据操作就不会自动同步到数据表中,需要commit提交才行
通常我们会让事务处于默认提交,多数操作我们是希望一操作就同步到数据库中的,只有涉及到事务安全(尤其是涉及到钱的操作)的时候,就会借助start transaction来开启临时手动事务
自动体检autocommit的默认是on,修改成off是会话级别的,下次连接会恢复成默认的设置
事务锁机制
Innodb引擎生成的表中默认是行锁,事务操作过程中,如果没有使用到索引字段,那么会进行全表检索,自动升级为表锁
行锁:只有当前行被锁住,其他客户端不能操作
表锁:整张表被锁住,其他客户端不能操作
行锁演示
表锁演示
有事务锁的时候,一个客户端会等一段时间等另外一个客户端将事务处理完,即执行commmit或rollback指令释放资源后,才能够对表格或行进行操作.如果等待时间过长就会放弃本次操作
隔离级别
事实上,上面事务锁反应是多线程隔离级别的一种体现
当多个客户端连接数据库,对同一表格进行操作时,一个客户端开启事务对表格进行操作,另一个表格读取表格中的数据,数据库设置成不同的隔离级别,读取的结果会不一样
MySql里面有4种隔离级:
read uncommitted,readcommitted,repeatable,serializable
1.读未提交:read uncommitted,一个客户端设置的隔离级别为读未提交,可以读取到其他客户端未提交的事务操作结果
2.读已提交:read committed,一个客户端设置隔离级别为读已提交,只能读取到其他客户端已经提交的事务操作结果
3.可重复读:repeatable read,一个客户端设置隔离级别为可重复读,当次连接上时就决定了读取表格数据的结果是什么,即使后来另一个客户端修改了表格数据,可重复读的这个客户端读取表格数据结果仍然是未修改的结果
4.序列化:serializable,一个客户端在开启手动事务操作某个表格时,其他客户端不能操作,需要等事务提交或rollback后才能操作
Mysql默认隔离级别是可重复读repeatable,不要修改它,一般不会对事务隔离级别进行修改.
视图
视图是一种有结构(有行有列)但是没有结果(结构中不真实存放数据)的虚拟表.视图结构不是自定义创建,而是来自基表
创建视图
基本语法:
Create view 视图名 as select语句；
select 语句可以普通查询,可以是联接查询;也可以是组合查询和子查询
单表视图:基表是一张表
多表视图:基表是两张或两张以上表
查看视图
视图是一个虚拟表,表的查看操作都适用
Show tables 视图名；
desc 视图名；
show create table 视图名；
使用视图
使用视图主要是为了查询数据
使用视图查询的好处是在创建视图时将查询语句结果生成了一张虚拟表,以后再查,直接可以从视图虚拟表中查,而不用再写那么长的查询语句
修改视图
视图本身是不可以修改的,但是视图的来源是可以修改的,修改视图也就是修改视图的来源select语句
基本语法:
Alter view 视图名 as 新的select语句;
删除视图
基本语法:
Drop view 视图名
视图的意义
1.提高查询语句的复用率:将一条复杂的查询语句结构创建为视图,以后只需要对视图进行操作就可以了
2.数据安全:视图是虚拟表,主要用来查询,就算删除视图也不会影响到基表中的真实数据
3.视图一般是在大项目中使用,设计到多方系统,对外提供视图可以有选择性地隐藏不愿意提供的字段信息
4.视图可以更好的进行权限控制
视图数据操作
视图可以进行写操作,但是存在很多限制
新增数据
1.多表视图不能新增数据
2.可以向单表视图新增数据,但是要求视图中包含基表中所有不允许为空且没有默认值的字段,否则给视图新增数据时,如果基表中该字段数据允许为NULL或有默认值就没有问题,但是如果不允许为NULL且没有默认值,那么这条记录在基表中无法生成,而给视图新增数据实际上是给基表,所以新增数据失败
删除数据
多表视图不能删除数据:多表视图里面的数据来自多张表格,如果删除多表视图里面的某条记录,势必会关系到多长表格里面的数据,删除操作会发生不可控结果
单表视图可以删除数据
虽然视图里面的数据可以被删除,但很少会这么做,视图主要还是用来查询的
更新数据
多表视图和单表视图都可以进行更新操作,但是如果存在更新限制,更新后的数据要满足限制条件才能成功
更新没有更新限制的视图
演示一下多表视图,多表视图都能更新,单表视图肯定可以更新
更新限制:with check option
创建一个有更新限制的视图
修改数据,满足更新限制的可以更新成功,不满足更新限制的无法成功
注意:视图里面没有的字段肯定是不能更新的,只能操作视图中能够看到的数据
视图算法
视图算法是对视图进行select查询时,系统对select语句的解析方式.因为视图本身是来自一天select语句,当外部对视图进行select查询时,就涉及到谁先执行的问题.
前面我们有个需求:找出每个班级中身高最高的学生,用的是表子查询,下面分别用表子查询和视图查询来做一次,视图有表子查询的子查询语句建立
视图算法有两种:
1.temptable:临时表算法,将先执行生成视图的select子句,然后执行外面的select查询语句,往往我们要的就是这个效果,保险起见,我定义视图的时候会将算法也定义好
2.merge:合并算法,将会把生成视图的select子句与外面的语句先合并成一条语句,然后再执行,这会导致语句执行顺序与我们期望的不符,但是如果没有定义视图算法,系统往往会默认按照这种算法执行,因为合并成一条查询语句后效率高
数据备份与还原
数据表中的数据,我们往往会做备份,以防被盗或误操作,导致重要数据丢失.
数据备份与还原有三种类型:数据表备份,单表数据表备份,SQL备份,增量备份
数据表文件备份
数据表备份是直接进入数据表所在文件夹,将数据表结构文件,数据文件进行复制,存储到其他位置的一种备份,需要还原的时候再复制回来就可以完成
对于不同存储引擎,这种数据备份有不一样的效果
Mysql中存储引擎主要是innodb(免费)和myisam(免费),两者存储数据的方式不一样
Innodb:只存储数据结构,只要是使用这个存储引擎的表格的数据都存储在ibdata1这个文件夹里
Myisam:表,索引,数据,三个文件单独存储
这就造成一个结果:
Myisam存储引擎生成的表,会产生三个文件,备份操作只需要备份三个文件即可,还原只需要将三个文件移动到任意数据库文件夹中都可以使用
先向里面添加点数据,然后将三个文件复制到任意数据库文件夹中,都可以使用,以复制到test数据库来演示
Innodb存储引擎生成的表,只产生结构文件frm,备份操作可以备份frm,但是还原时如果不是移动到原来创建时那个数据库中,是无法使用的,因为ibdata1文件中记录的数据就指示数据应该对应的是在原来创建的那个数据库中的frm文件
演示:将在mydatabase数据库里面使用innodb引擎创建的my_class表的结构文件复制到test数据库中,test数据库中可以查看到有该表,但是无法查看数据
综上:这种直接复制表文件的存储方式只适合用myisam存储引擎创建的表
使用场景:要求数据迁移很方便时,可以提前将存储引擎设置为myisam,直接复制粘贴就可以完成备份与还原
单表数据备份
特点:每次只能备份一张表,而且只备份表中的数据,不备份表结构,还原数据的时候要依赖表结构,如果表结构文件被删除,那就无法恢复数据
使用场景:只需要表数据,将表数据导出到外部文件中
备份:从表中根据需要选部分或所有字段数据导出到外部文件中
Select */字段列表 into outfile 文件路径 from 数据源; -- 要求文件路径指示的文件不存在
导出数据可以自定义行和段的处理方式
Select */字段列表 into outfile 文件路径 fields 字段处理 lines 行处理from my_class;
字段处理:
Enclosed by:用什么符号包裹字段,默认是’’,空字符串
Terminated by:用什么符号间隔字段,默认’\t’,制表符
Escaped by:特殊符号用什么方式处理,默认是’\\’,反斜杠转义
行处理:
Starting by:用什么开启每行,默认是’’,空
Terminated by:用什么结束每行,默认是’\r\n’,换行符
数据还原:将保存在外部的数据恢复到对应的表中,如果表结构文件不存在,就无法恢复
Load data infile 文件所在路径 into table 表名[(字段列表)] fields 字段处理 lines 行处理; -- 怎么备份的,就怎么还原
演示:把my_class表中的数据删除,但是表没删除,即表结构在,然后用高级备份class1.txt还原数据
SQL备份
SQL备份将表结构以及存储的数据进行处理,转换成对应的SQL指令,然后进行备份,还原的时候只需要执行SQL指令就可以还原数据(主要针对表结构)
表备份
SQL备份需要借助mysql提供的一个客户端软件:mysqldump.exe
Mysqldump是客户端,要操作服务器,必须连接认证
Mysqldump –hPup 数据库名称 [数据表名1[数据表名2]..] > 存储路径
整库备份
Mysqldump –hPup 数据库名称 > 存储路径
还原数据
方案一：通过mysql客户端还原
Mysql –hPup 数据库名 < 备份文件路径
演示:先删除mydatabase里面的my_student表,然后通过mysql客户端还原
方案二:SQL指令还原
Source 备份文件路径 – 注意,调用sql指令整库还原,那就要先建一个库,进入该库环境后执行还原语句,否则会报错,因为备份的sql指令都是创建数据表的指令
演示:删除mydatabase整个数据库,然后还原.
-- 删除数据库
-- 重新创建数据库并进入数据库环境
-- sql指令整库恢复数据
-- 恢复表格数据成功
增量备份
增量备份不是既不是备份表数据,也不是备份sql指令,而是备份mysql服务器日志文件
增量备份对指定时间段的服务器日志进行备份,不会重复备份,也就不会浪费空间,指定时间段所有操作都会被完整备份下来
大型项目一般会采用增量备份.
备份方式比较
1.数据表文件备份:依赖存储引擎,当存储引擎是myisam时才方便备份与还原,可以直接通过数据表文件的复制粘贴完成备份与还原
2.单表数据备份:一次只能备份一张数据表,而且只能备份表中的数据,不能同时备份表结构
,恢复数据要依赖原表结构才在
3.SQL备份:可以实现整库,多表备份,一次备份,将表结构,表数据都备份好了.用得还是比较多,需借助mysqldump.exe程序.
4.增量备份:备份mysql服务器日志文件,所有的操作都会被保留下来
变量
变量分为系统变量和自定义变量
系统变量
查看系统变量
查看所有系统变量
Show variables;
-- 270多行变量,很多!
查看具体某条变量
Select @@变量名;
修改系统变量
修改系统变量有两种:全局级别修改,会话级别修改
会话级别修改
会话级别修改系统变量只对本客户端本次连接有效,对其他客户端或本次连接断开后失效
Set 系统变量名 = 值 / set @@系统变量名 = 值;
全局级别修改
全局级别修改对本客户端以及修改后连接上的客户端都生效,在修改前连接上的其他客户端无效
Set global 系统变量名 = 值;
通常系统变量不会去修改它,就算是修改,也是会话级别修改,几乎不会使用全局级别修改
自定义变量
Mysql中,为了将自定义变量与系统变量区分开,自定义变量前面带一个@
定义自定义变量
Set @自定义变量名 = 值;
查看自定义变量
Select @自定义变量名;
Mysql中等号其实更多的意义是比较是否相等,复制又给了一个符号’:=’
Mysql中允许从数据表中获取数据赋值给自定义变量:两种方式
方式一:边赋值,边查看结果(这种方式基本不用)
Select @变量名 := 字段名 from 数据源 [where条件] -- 变量最终的值是字段最后一条记录的值
方式二:只赋值,不看结果
要求严格:从表中获取的数据记录只能有一行,mysql不支持数组
Select 字段列表 from 数据源 [where条件] into 自定义变量列表;
所有的自定义变量都是会话级别的,只对当前客户端有效,可以跨数据库使用,只要是当前客户端当次连接就行
触发器
触发器(trigger)：事先为某张表绑定好一段代码,当表格记录发生变化（增删改）时,会触发系统自动执行这段代码
触发器由3部分组成：
1.触发时间：事件发生前或后,before/after
2.触发事件类型：增/删/改,insert/delete/update
3.触发对象：表中的每一行记录(表中任意一行发生变化都会触发代码)
注意：一张表中同一触发时间下同一触发事件的触发器只能有一个,也就是说,一张表中最多可以有6个触发器
创建触发器
基本语法:
临时修改语句结束符
Delimiter 自定义符号 -- 因为触发器里面的代码以;结束,需要临时更改sql指令结束符
Create trigger 触发器名称 触发时间 触发事件类型 on 表名 for each row
Begin
要触发的代码;
End-- 语句结束符
自定义的语句结束符
-- 恢复默认的语句结束符
Delimiter ;
演示:创建一张库存表,一张订单表,要求实现向订单表中添加一定数量某种商品时,库存表中该商品数量相应的减少.这里订单表是触发器监听对象
1.创建商品库存表和订单表,在商品库存表中添加好数据
2.在订单表上添加触发器
3.测试触发器
查看触发器
查看所有触发器
Show triggers;
查看触发器创建语句
Show create trigger 触发器名;
所有触发器都会保存在一张表中,information_schema
查看information_shema里保存的触发器
Select * from information_schema.triggers;
触发器记录
对被触发器监听的表格执行触发操作(增/删/改),操作会涉及到某条记录,当触发操作准备执行时,系统会将该操作前的记录状态和操作后的记录状态分别保存在触发器记录old和new里面,供触发器使用
Old代表旧状态,new代表新状态
新增操作没有旧状态,只有新状态,就是新增的那条记录
删除操作没有新状态,只有旧状态,就是被删除的那条记录
修改操作有新状态和旧状态,旧状态是修改前的记录状态,新状态是修改后的记录状态
记录使用方式:
记录里面包含数据和字段,old.字段名 或 new.字段名
删除&修改触发器
同一种类型的触发器不能被修改,只能先删除,然后再添加
删除触发器
Drop trigger 触发器名;
修改/新增触发器
之前定义的触发器将触发代码写固定了,现在借助触发器记录,来实现新增订单记录里面是买多少数量的某种商品,商品库存表里面就减少多少某种商品
检测触发器是否生效
代码执行结构
代码执行结构有三种:顺序结构,分支结构,循环结构
顺序结构不需要讲,就是代码一条一条执行
分支结构
Mysql中只有if这种分支结构
基本语法:
If 条件判断 then
-- 满足条件时执行的代码;
[Else
-- 不满足条件时执行的代码] -- 可以没有else
End if;
演示:触发器结合if条件判断,向商品订单表里面新增记录,如果下单数量超过了库存数量,则不生成订单.
循环结构
While循环(mysql没有for循环)
基本语法:
While 循环条件 do
循环体;
End while;
循环控制:mysql里面没有continue和break,而是分别用iterate和leave替代
用法:Iterate/leave 循环名称;
带循环控制的循环写法:
循环名称:while 循环条件 do
循环体;
循环控制语句;
End while;
存储过程
过程一系列步骤的封装,与函数类似,但过程没有返回值
创建过程
Create procedure 过程名([形参列表])
Begin
过程体
End
注意：当过程体只有一条语句的时候可以省略begin,end
调用过程
过程没有返回值,不是用select调用,而是用关键字call调用
Call 过程名（实参列表）;
查看过程
查看过程和查看函数几乎一样,就是把关键字换成了procedure
产看创建了哪些过程
Show procedure status;
查看过程创建语句
Show create procedure 过程名;
删除过程
Drop procedure 过程名;
过程参数
过程参数比函数参数更严格,除了要定义数据类型,还要加特有的类型限定
In:传入实参可以是值和变量,并且只使用它的值,不会对实参产生任何影响
Out:传入实参只能是变量,并且会将变量值清空为NULL(不使用外部数据),相当于只传入一个空变量,过程操作完后传入的变量中会装有值(可以给外部使用)
Inout:传入的实参只能是变量,变量值可以供过程内使用,过程执行完后,变量值也可以供外部使用
基本语法:create procedure 过程名(in 形参 数据类型, out 形参 数据类型, inout 形参 数据类型)
演示一:证明out类型参数,传入实参变量的值会被置空
报错,out和inout类型限定的参数处必须传入变量
魔鬼三张表
创建学生表——学生sid int，学生sname String，学生sage int，学生ssex char
命令：
create table student(
sid int primary key,
sname varchar(20) not null,
sage int,
ssex varchar(6)
);
insert into student(sid,sname,sage,ssex) values("002","李四",12,"女");
insert into student(sid,sage,sname) values("003","12","王五");
创建课程表——课程cid int，课程cname String，课时timenumber int
命令：
create table course(
cid int primary key,
cname varchar(20) not null,
ctimenumber int
);
创建选课表——学生sid int，课程cid int 成绩score int
分析：
业务逻辑，一个学生可以选多门课，每一门课又可以被很多学生选
怎么确定一个学生的一行选课信息？
1）单独的主键，比如说订单号除了标识交易，没有其他用途
优点是简
单，缺点是数据浪费
E182739817831820180115xxxx230
2）设置联合主键，学生学号和选课的课程号作为主键，
优点是避免数据浪费，缺点是耗时
命令：
create table selectcourse(
sid int primary key,
cid int,
score int
);
4131
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
